/**
  * Echo in a jqGrid compatible format the data requested by a grid.
  *
  * @param RepositoryInterface $dataRepository
  *	An implementation of the RepositoryInterface
  * @param  array $postedData
  *	All jqGrid posted data
  * @return string
  *	String of a jqGrid compatible data format: xml, json, jsonp, array, xmlstring, jsonstring.
  */
 public function encodeRequestedData(RepositoryInterface $Repository, $postedData)
 {
     // $page = $postedData['page']; // get the requested page
     // $limit = $postedData['rows']; // get how many rows we want to have into the grid
     // $sidx = $postedData['sidx']; // get index row - i.e. user click to sort
     // $sord = $postedData['sord']; // get the direction
     if (isset($postedData['page'])) {
         $page = $postedData['page'];
         // get the requested page
     } else {
         $page = 1;
     }
     if (isset($postedData['rows'])) {
         $limit = $postedData['rows'];
         // get how many rows we want to have into the grid
     } else {
         $limit = null;
     }
     if (isset($postedData['sidx'])) {
         $sidx = $postedData['sidx'];
         // get index row - i.e. user click to sort
     } else {
         $sidx = null;
     }
     if (isset($postedData['sord'])) {
         $sord = $postedData['sord'];
         // get the direction
     }
     if (isset($postedData['filters']) && !empty($postedData['filters'])) {
         $filters = json_decode(str_replace('\'', '"', $postedData['filters']), true);
     }
     if (!$sidx || empty($sidx)) {
         $sidx = null;
         $sord = null;
     }
     if (isset($filters['rules']) && is_array($filters['rules'])) {
         foreach ($filters['rules'] as &$filter) {
             switch ($filter['op']) {
                 case 'eq':
                     //equal
                     $filter['op'] = '=';
                     break;
                 case 'ne':
                     //not equal
                     $filter['op'] = '!=';
                     break;
                 case 'lt':
                     //less
                     $filter['op'] = '<';
                     break;
                 case 'le':
                     //less or equal
                     $filter['op'] = '<=';
                     break;
                 case 'gt':
                     //greater
                     $filter['op'] = '>';
                     break;
                 case 'ge':
                     //greater or equal
                     $filter['op'] = '>=';
                     break;
                 case 'bw':
                     //begins with
                     $filter['op'] = 'like';
                     $filter['data'] = $filter['data'] . '%';
                     break;
                 case 'bn':
                     //does not begin with
                     $filter['op'] = 'not like';
                     $filter['data'] = $filter['data'] . '%';
                     break;
                 case 'in':
                     //is in
                     $filter['op'] = 'is in';
                     break;
                 case 'ni':
                     //is not in
                     $filter['op'] = 'is not in';
                     break;
                 case 'ew':
                     //ends with
                     $filter['op'] = 'like';
                     $filter['data'] = '%' . $filter['data'];
                     break;
                 case 'en':
                     //does not end with
                     $filter['op'] = 'not like';
                     $filter['data'] = '%' . $filter['data'];
                     break;
                 case 'cn':
                     //contains
                     $filter['op'] = 'like';
                     $filter['data'] = '%' . $filter['data'] . '%';
                     break;
                 case 'nc':
                     //does not contains
                     $filter['op'] = 'not like';
                     $filter['data'] = '%' . $filter['data'] . '%';
                     break;
             }
         }
     } else {
         $filters['rules'] = array();
     }
     $count = $Repository->getTotalNumberOfRows($filters['rules']);
     if (empty($limit)) {
         $limit = $count;
     }
     if (!is_int($count)) {
         throw new Exception('The method getTotalNumberOfRows must return an integer');
     }
     if ($count > 0) {
         $totalPages = ceil($count / $limit);
     } else {
         $totalPages = 0;
     }
     if ($page > $totalPages) {
         $page = $totalPages;
     }
     if ($limit < 0) {
         $limit = 0;
     }
     $start = $limit * $page - $limit;
     if ($start < 0) {
         $start = 0;
     }
     $limit = $limit * $page;
     if (empty($postedData['pivotRows'])) {
         $rows = $Repository->getRows($limit, $start, $sidx, $sord, $filters['rules']);
     } else {
         $rows = json_decode($postedData['pivotRows'], true);
     }
     if (!is_array($rows) || isset($rows[0]) && !is_array($rows[0])) {
         throw new Exception('The method getRows must return an array of arrays, example: array(array("column1"  =>  "1-1", "column2" => "1-2"), array("column1" => "2-1", "column2" => "2-2"))');
     }
     if (isset($postedData['exportFormat'])) {
         $this->Excel->create($postedData['name'], function ($Excel) use($rows, $postedData) {
             foreach (json_decode($postedData['fileProperties'], true) as $key => $value) {
                 $method = 'set' . ucfirst($key);
                 $Excel->{$method}($value);
             }
             $Excel->sheet($postedData['name'], function ($Sheet) use($rows, $postedData) {
                 $columnCounter = 0;
                 foreach (json_decode($postedData['model'], true) as $a => $model) {
                     if (isset($model['hidden']) && $model['hidden'] !== true) {
                         $columnCounter++;
                     }
                     if (isset($model['hidedlg']) && $model['hidedlg'] === true) {
                         continue;
                     }
                     if (empty($postedData['pivot'])) {
                         foreach ($rows as $b => &$row) {
                             if (isset($model['hidden']) && $model['hidden'] === true) {
                                 unset($row[$model['name']]);
                             } else {
                                 if (isset($model['label'])) {
                                     $row = array_add($row, $model['label'], $row[$model['name']]);
                                     unset($row[$model['name']]);
                                 } else {
                                     $temp = $row[$model['name']];
                                     unset($row[$model['name']]);
                                     $row = array_add($row, $model['name'], $temp);
                                 }
                             }
                         }
                     }
                     if (isset($model['align']) && isset($model['hidden']) && $model['hidden'] !== true) {
                         $Sheet->getStyle($this->num_to_letter($columnCounter, true))->getAlignment()->applyFromArray(array('horizontal' => $model['align']));
                     }
                 }
                 foreach (json_decode($postedData['sheetProperties'], true) as $key => $value) {
                     $method = 'set' . ucfirst($key);
                     $Sheet->{$method}($value);
                 }
                 $Sheet->fromArray($rows);
                 $Sheet->row(1, function ($Row) {
                     $Row->setFontWeight('bold');
                 });
             });
         })->export($postedData['exportFormat']);
     } else {
         echo json_encode(array('page' => $page, 'total' => $totalPages, 'records' => $count, 'rows' => $rows));
     }
 }
 /**
  * Echo in a jqGrid compatible format the data requested by a grid.
  *
  * @param RepositoryInterface $dataRepository
  *	An implementation of the RepositoryInterface
  * @param  array $postedData
  *	All jqGrid posted data
  * @return string
  *	String of a jqGrid compatible data format: xml, json, jsonp, array, xmlstring, jsonstring.
  */
 public function encodeRequestedData(RepositoryInterface $Repository, $postedData)
 {
     // $page = $postedData['page']; // get the requested page
     // $limit = $postedData['rows']; // get how many rows we want to have into the grid
     // $sidx = $postedData['sidx']; // get index row - i.e. user click to sort
     // $sord = $postedData['sord']; // get the direction
     if (isset($postedData['page'])) {
         $page = $postedData['page'];
         // get the requested page
     } else {
         $page = 1;
     }
     if (isset($postedData['rows'])) {
         $limit = $postedData['rows'];
         // get how many rows we want to have into the grid
     } else {
         $limit = null;
     }
     if (isset($postedData['sidx'])) {
         $sidx = $postedData['sidx'];
         // get index row - i.e. user click to sort
     } else {
         $sidx = null;
     }
     if (isset($postedData['sord'])) {
         $sord = $postedData['sord'];
         // get the direction
     }
     if (isset($postedData['filters']) && !empty($postedData['filters'])) {
         $filters = json_decode(str_replace('\'', '"', $postedData['filters']), true);
     }
     if (isset($postedData['nodeid'])) {
         $nodeId = $postedData['nodeid'];
     } else {
         $nodeId = null;
     }
     if (isset($postedData['n_level'])) {
         $nodeLevel = $postedData['n_level'];
     } else {
         $nodeLevel = null;
     }
     if (isset($postedData['exportFormat'])) {
         $exporting = true;
     } else {
         $exporting = false;
     }
     if (!$sidx || empty($sidx)) {
         $sidx = null;
         $sord = null;
     }
     if (isset($filters['rules']) && is_array($filters['rules'])) {
         foreach ($filters['rules'] as &$filter) {
             switch ($filter['op']) {
                 case 'eq':
                     //equal
                     $filter['op'] = '=';
                     break;
                 case 'ne':
                     //not equal
                     $filter['op'] = '!=';
                     break;
                 case 'lt':
                     //less
                     $filter['op'] = '<';
                     break;
                 case 'le':
                     //less or equal
                     $filter['op'] = '<=';
                     break;
                 case 'gt':
                     //greater
                     $filter['op'] = '>';
                     break;
                 case 'ge':
                     //greater or equal
                     $filter['op'] = '>=';
                     break;
                 case 'bw':
                     //begins with
                     $filter['op'] = 'like';
                     $filter['data'] = $filter['data'] . '%';
                     break;
                 case 'bn':
                     //does not begin with
                     $filter['op'] = 'not like';
                     $filter['data'] = $filter['data'] . '%';
                     break;
                 case 'in':
                     //is in
                     $filter['op'] = 'is in';
                     break;
                 case 'ni':
                     //is not in
                     $filter['op'] = 'is not in';
                     break;
                 case 'ew':
                     //ends with
                     $filter['op'] = 'like';
                     $filter['data'] = '%' . $filter['data'];
                     break;
                 case 'en':
                     //does not end with
                     $filter['op'] = 'not like';
                     $filter['data'] = '%' . $filter['data'];
                     break;
                 case 'cn':
                     //contains
                     $filter['op'] = 'like';
                     $filter['data'] = '%' . $filter['data'] . '%';
                     break;
                 case 'nc':
                     //does not contains
                     $filter['op'] = 'not like';
                     $filter['data'] = '%' . $filter['data'] . '%';
                     break;
                 case 'nu':
                     //is null
                     $filter['op'] = 'is null';
                     $filter['data'] = '';
                     break;
                 case 'nn':
                     //is not null
                     $filter['op'] = 'is not null';
                     $filter['data'] = '';
                     break;
             }
         }
     } else {
         $filters['rules'] = array();
     }
     $count = $Repository->getTotalNumberOfRows($filters['rules']);
     if (empty($limit)) {
         $limit = $count;
     }
     if (!is_int($count)) {
         throw new Exception('The method getTotalNumberOfRows must return an integer');
     }
     if ($count > 0) {
         $totalPages = ceil($count / $limit);
     } else {
         $totalPages = 0;
     }
     if ($page > $totalPages) {
         $page = $totalPages;
     }
     if ($limit < 0) {
         $limit = 0;
     }
     $start = $limit * $page - $limit;
     if ($start < 0) {
         $start = 0;
     }
     $limit = $limit * $page;
     if (empty($postedData['pivotRows'])) {
         $rows = $Repository->getRows($limit, $start, $sidx, $sord, $filters['rules'], $nodeId, $nodeLevel, $exporting);
     } else {
         $rows = json_decode($postedData['pivotRows'], true);
     }
     if (!is_array($rows) || isset($rows[0]) && !is_array($rows[0])) {
         throw new Exception('The method getRows must return an array of arrays, example: array(array("column1"  =>  "1-1", "column2" => "1-2"), array("column1" => "2-1", "column2" => "2-2"))');
     }
     if ($exporting) {
         $this->Excel->create($postedData['name'], function ($Excel) use($rows, $postedData) {
             foreach (json_decode($postedData['fileProperties'], true) as $key => $value) {
                 $method = 'set' . ucfirst($key);
                 $Excel->{$method}($value);
             }
             $Excel->sheet($postedData['name'], function ($Sheet) use($rows, $postedData) {
                 $groupingView = json_decode($postedData['groupingView'], true);
                 $groupHeaders = json_decode($postedData['groupHeaders'], true);
                 $columnsPositions = array();
                 $groupFieldName = '';
                 $columnCounter = 0;
                 foreach (json_decode($postedData['model'], true) as $a => $model) {
                     if (!empty($groupingView) && $groupingView['groupField'][0] == $model['name']) {
                         if (isset($model['hidden']) && $model['hidden'] === true) {
                             $groupFieldHidden = true;
                         } else {
                             $groupFieldHidden = false;
                         }
                         $groupFieldName = $model['name'];
                         if (isset($model['label'])) {
                             $groupFieldLabel = $model['label'];
                         } else {
                             $groupFieldLabel = $model['name'];
                         }
                     }
                     if (isset($model['hidden']) && $model['hidden'] !== true) {
                         $columnCounter++;
                         $columnsPositions[$model['name']] = $columnCounter;
                     }
                     if (isset($model['hidedlg']) && $model['hidedlg'] === true) {
                         continue;
                     }
                     if (empty($postedData['pivot'])) {
                         foreach ($rows as $index => &$row) {
                             if (isset($model['hidden']) && $model['hidden'] === true && $model['name'] != $groupFieldName) {
                                 unset($row[$model['name']]);
                             } else {
                                 if (isset($model['label'])) {
                                     $row[$model['label']] = $row[$model['name']];
                                     unset($row[$model['name']]);
                                 } else {
                                     $temp = $row[$model['name']];
                                     unset($row[$model['name']]);
                                     $row[$model['name']] = $temp;
                                 }
                             }
                         }
                     }
                     if (isset($model['align']) && isset($model['hidden']) && $model['hidden'] !== true) {
                         $Sheet->getStyle($this->num_to_letter($columnCounter, true))->getAlignment()->applyFromArray(array('horizontal' => $model['align']));
                     }
                 }
                 foreach (json_decode($postedData['sheetProperties'], true) as $key => $value) {
                     $method = 'set' . ucfirst($key);
                     $Sheet->{$method}($value);
                 }
                 if (!empty($groupingView)) {
                     $groupedRows = $groupedRowsNumbers = array();
                     $rowCounter = 0;
                     foreach ($rows as $index => &$row) {
                         if ($rowCounter == 0) {
                             $currentgroupFieldValue = $row[$groupFieldLabel];
                             if ($groupFieldHidden) {
                                 unset($row[$groupFieldLabel]);
                             }
                             $firstColumnName = key($row);
                             $groupedRow = $row;
                             foreach ($groupedRow as $label => &$cell) {
                                 if ($firstColumnName == $label) {
                                     $cell = $currentgroupFieldValue;
                                 } else {
                                     $cell = '';
                                 }
                             }
                             $rowCounter = 2;
                             if (!empty($groupHeaders)) {
                                 $rowCounter++;
                             }
                             array_push($groupedRows, $groupedRow);
                             array_push($groupedRowsNumbers, $rowCounter);
                         } else {
                             if ($row[$groupFieldLabel] != $currentgroupFieldValue) {
                                 $currentgroupFieldValue = $groupedRow[$firstColumnName] = $row[$groupFieldLabel];
                                 $rowCounter++;
                                 array_push($groupedRows, $groupedRow);
                                 array_push($groupedRowsNumbers, $rowCounter);
                             }
                             if ($groupFieldHidden) {
                                 unset($row[$groupFieldLabel]);
                             }
                         }
                         array_push($groupedRows, $row);
                         $rowCounter++;
                     }
                     $lastCellLetter = $this->num_to_letter($columnCounter, true);
                     foreach ($groupedRowsNumbers as $index => $groupedRowsNumber) {
                         $Sheet->mergeCells("A{$groupedRowsNumber}:{$lastCellLetter}{$groupedRowsNumber}");
                     }
                     $rows = $groupedRows;
                 }
                 if (empty($groupHeaders)) {
                     $Sheet->fromArray($rows);
                 } else {
                     $headers = $firstHeader = array();
                     for ($i = 0; $i < count($columnsPositions); $i++) {
                         $firstHeader[$i] = '';
                     }
                     foreach ($groupHeaders as $index => $groupHeader) {
                         $firstHeader[$columnsPositions[$groupHeader['startColumnName']] - 1] = $groupHeader['titleText'];
                         $Sheet->mergeCells($this->num_to_letter($columnsPositions[$groupHeader['startColumnName']], true) . '1:' . $this->num_to_letter($columnsPositions[$groupHeader['startColumnName']] + $groupHeader['numberOfColumns'] - 1, true) . '1');
                     }
                     array_push($headers, $firstHeader);
                     array_push($headers, array_keys($rows[0]));
                     $Sheet->fromArray(array_merge($headers, $rows), null, 'A1', false, false);
                     $Sheet->row(2, function ($Row) {
                         $Row->setFontWeight('bold');
                     });
                 }
                 $Sheet->row(1, function ($Row) {
                     $Row->setFontWeight('bold');
                 });
             });
         })->export($postedData['exportFormat']);
     } else {
         echo json_encode(array('page' => $page, 'total' => $totalPages, 'records' => $count, 'rows' => $rows));
     }
 }