Beispiel #1
0
 public static function getGridServiceResponse($source, $filter, $class = NULL)
 {
     global $db;
     $fld = isset($filter["fields"]) ? array_values($filter["fields"]) : array('id');
     $fldAs = array_keys($fld);
     //
     $source = trim($source);
     //
     $tbl = strpos($source, ' ') > 0 ? "({$source}) AS GridServiceSourceTable" : $source;
     // limit results
     $limit = GridSql::LIMIT(array("enabled" => isset($_POST['iDisplayStart']), "start" => $_POST['iDisplayStart'], "length" => $_POST['iDisplayLength']));
     // sort by results
     $fieldToOrder = array();
     $fieldSortable = array();
     $fieldDirection = array();
     for ($i = 0; $i < (int) $_POST['iSortingCols']; $i++) {
         $fieldIndex = $_POST['iSortCol_' . $i];
         $fieldSortable[$i] = $_POST['bSortable_' . $fieldIndex];
         $fieldDirection[$i] = $_POST['sSortDir_' . $i];
         $fieldToOrder[$i] = $fieldIndex;
     }
     $orderby = GridSql::ORDERBY(array("enabled" => isset($_POST['iSortCol_0']), "fieldToOrder" => $fieldToOrder, "fieldSortable" => $fieldSortable, "fieldDirection" => $fieldDirection, "fieldName" => $fld, "fieldAs" => $fldAs));
     // fields in select
     $fields = GridSql::FIELDS(array("enabled" => isset($filter["fields"]), "fieldArray" => $filter["fields"]));
     // where cond
     for ($i = 0; $i < count($filter["fields"]); $i++) {
         $fieldSearchable[$i] = @$_POST['bSearchable_' . $i];
     }
     $where = GridSql::WHERE(array("enabled" => isset($_POST['sSearch']), "search" => @$_POST['sSearch'], "fieldName" => $fld, "fieldAs" => $fldAs, "whereCondition" => @$filter["where"], "fieldSearchable" => $fieldSearchable));
     // the queries
     $sql_fetch = "SELECT {$fields} FROM {$tbl} {$where} {$orderby} {$limit}";
     //return self::handleGridServiceResponse($tbl, $where, $sql_fetch, $class);
     //$dbx = Context::getInstance()->getDB();
     $dbx = $db;
     // prepare response $res
     $res = new \stdClass();
     $res->sEcho = $_POST["sEcho"];
     $sql_count = "SELECT COUNT(id) FROM {$tbl} {$where}";
     $sql_total = "SELECT COUNT(id) FROM {$tbl}";
     // fetch rows
     $res->SQL = $sql_fetch;
     $dat = $dbx->getResults($sql_fetch);
     // apply filter to encode special chars
     if ($class && $dat) {
         foreach ($dat as &$row) {
             foreach ($row as &$value) {
                 $value = mb_convert_encoding($value, 'UTF-8');
             }
         }
     }
     // apply Set/Get filter to parse and prepare data for visualization
     if ($class && $dat) {
         foreach ($dat as &$row) {
             //DBUtils::applyGetFilter($row,$class);
         }
     }
     // apply html template or functions for fields
     if ($dat) {
         foreach ($dat as &$row) {
             foreach ($row as $k => &$v) {
                 $v = GridService::applyTemplate($v, $k, $filter['templates'][$k], $row);
                 $v = GridService::applyFunction($v, $k, $filter['functions'][$k], $row);
             }
         }
     }
     // prepare aaData with $dat
     $res->aaData = array();
     if ($dat) {
         foreach ($dat as $row0) {
             /*
                             $row1 = new \stdClass;
             				$i = 0;
                             foreach ($row0 as $key => $val) {
                                 if ($key == 'DT_RowClass') {
                                     $row1->{'DT_RowClass'} = $val;
                                 } else if ($key == 'DT_RowClass') {
                                     $row1->{'DT_RowId'} = $val;
                                 } else {
                                     $row1->{''.$i} = $val;
                                     $i++;
                                 }
                             }*/
             $res->aaData[] = $row0;
             //array_values((array)$row0);
         }
     }
     // manage total
     $res->iTotalRecords = (int) $dbx->getValue($sql_total);
     $res->iTotalDisplayRecords = (int) $dbx->getValue($sql_count);
     // return response
     return $res;
 }
 public static function getGridServiceResponse($tbl, $filter, $class = null)
 {
     global $db;
     $fld = isset($filter['fields']) ? array_values($filter['fields']) : ['id'];
     $fldAs = array_keys($fld);
     #var_dump($fld);
     // limit results
     $limit = GridSql::LIMIT(['enabled' => isset($_POST['iDisplayStart']), 'start' => $_POST['iDisplayStart'], 'length' => $_POST['iDisplayLength']]);
     // sort by results
     $fieldToOrder = [];
     $fieldSortable = [];
     $fieldDirection = [];
     for ($i = 0; $i < (int) $_POST['iSortingCols']; $i++) {
         $fieldIndex = $_POST['iSortCol_' . $i];
         $fieldSortable[$i] = $_POST['bSortable_' . $fieldIndex];
         $fieldDirection[$i] = $_POST['sSortDir_' . $i];
         $fieldToOrder[$i] = $fieldIndex;
     }
     $orderby = GridSql::ORDERBY(['enabled' => isset($_POST['iSortCol_0']), 'fieldToOrder' => $fieldToOrder, 'fieldSortable' => $fieldSortable, 'fieldDirection' => $fieldDirection, 'fieldName' => $fld, 'fieldAs' => $fldAs]);
     // fields in select
     $fields = GridSql::FIELDS(['enabled' => isset($filter['fields']), 'fieldArray' => $filter['fields']]);
     // where cond
     for ($i = 0; $i < count($filter['fields']); $i++) {
         $fieldSearchable[$i] = @$_POST['bSearchable_' . $i];
     }
     $where = GridSql::WHERE(['enabled' => isset($_POST['sSearch']), 'search' => @$_POST['sSearch'], 'fieldName' => $fld, 'fieldAs' => $fldAs, 'whereCondition' => @$filter['where'], 'fieldSearchable' => $fieldSearchable]);
     // the queries
     $sql_fetch = "SELECT {$fields} FROM {$tbl} {$where} {$orderby} {$limit}";
     //return self::handleGridServiceResponse($tbl, $where, $sql_fetch, $class);
     //$dbx = Context::getInstance()->getDB();
     $dbx = $db;
     ## prepare response $res
     $res = new stdClass();
     $res->sEcho = $_POST['sEcho'];
     $sql_count = "SELECT COUNT(Id) FROM {$tbl} {$where}";
     $sql_total = "SELECT COUNT(Id) FROM {$tbl}";
     ## fetch rows
     $res->SQL = $sql_fetch;
     $dat = $dbx->get_results($sql_fetch);
     ## apply filter to encode special chars
     if ($class && $dat) {
         foreach ($dat as &$row) {
             foreach ($row as &$value) {
                 $value = mb_convert_encoding($value, 'UTF-8');
             }
         }
     }
     ## apply Set/Get filter to parse and prepare data for visualization
     if ($class && $dat) {
         foreach ($dat as &$row) {
             //DBUtils::applyGetFilter($row,$class);
         }
     }
     ## apply html template or functions for fields
     if ($dat) {
         foreach ($dat as &$row) {
             foreach ($row as $k => &$v) {
                 $v = self::applyTemplate($v, $k, $filter['templates'][$k], $row);
                 $v = self::applyFunction($v, $k, $filter['functions'][$k], $row);
             }
         }
     }
     ## prepare aaData with $dat
     $res->aaData = [];
     if ($dat) {
         foreach ($dat as $row0) {
             $res->aaData[] = array_values((array) $row0);
         }
     }
     ## manage total
     $res->iTotalRecords = (int) $dbx->get_var($sql_total);
     $res->iTotalDisplayRecords = (int) $dbx->get_var($sql_count);
     ## return response
     return $res;
 }