protected function buildResponseFromSql($sql, $filterFields = array(), $groupSQL = "", $simpleQuery = true, $noLimit = false) { $this->request->defineParams(array('start' => array('type' => 'int', 'default' => 0), 'limit' => array('type' => 'int', 'default' => 20))); if (is_array($groupSQL)) { return $this->buildResponseFromSql2($sql, $filterFields, $groupSQL, is_array($simpleQuery) ? $simpleQuery : array(), $noLimit); } if ($this->getParam('query') && count($filterFields) > 0) { $filter = $this->db->qstr('%' . trim($this->getParam('query')) . '%'); foreach ($filterFields as $field) { if ($simpleQuery) { $likes[] = "`{$field}` LIKE {$filter}"; } else { $likes[] = "{$field} LIKE {$filter}"; } } $sql .= " AND ("; $sql .= implode(" OR ", $likes); $sql .= ")"; } if ($groupSQL) { $sql .= "{$groupSQL}"; } if (!$noLimit) { if (stristr($sql, "SELECT * FROM")) { $response["total"] = $this->db->GetOne(str_ireplace("SELECT * FROM", "SELECT COUNT(*) FROM", $sql), $args); } else { $response["total"] = $this->db->Execute($sql, $args)->RecordCount(); } } // @TODO replace with simple code (legacy code) $s = $this->getParam('sort'); if (!is_array($s)) { $s = json_decode($this->getParam('sort'), true); } if (is_array($s)) { $sorts = array(); if (count($s) && !is_array($s[0])) { $s = array($s); } foreach ($s as $param) { $sort = preg_replace("/[^A-Za-z0-9_]+/", "", $param['property']); $dir = in_array(strtolower($param['direction']), array('asc', 'desc')) ? $param['direction'] : 'ASC'; $sorts[] = "`{$sort}` {$dir}"; } $sql .= " ORDER BY " . implode($sorts, ','); } else { if ($this->getParam('sort')) { $sort = preg_replace("/[^A-Za-z0-9_]+/", "", $this->getParam('sort')); $dir = in_array(strtolower($this->getParam('dir')), array('asc', 'desc')) ? $this->getParam('dir') : 'ASC'; $sql .= " ORDER BY `{$sort}` {$dir}"; } } if (!$noLimit) { $start = $this->getParam('start'); $limit = $this->getParam('limit'); $sql .= " LIMIT {$start}, {$limit}"; } $response["success"] = true; $response["data"] = $this->db->GetAll($sql); return $response; }