<?php
|
|
function getDB() {
|
$dbFile = "filter-demo.db";
|
$hasDB = file_exists($dbFile);
|
|
$db = new SQLiteDatabase($dbFile);
|
if (!$hasDB) {
|
$db->query(readCreateSql());
|
}
|
return $db;
|
}
|
|
function readCreateSql() {
|
$filename = "grid-demo.sql";
|
$file = fopen($filename, 'r');
|
$data = fread($file, filesize($filename));
|
fclose($file);
|
return $data;
|
}
|
|
// collect request parameters
|
$start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
|
$count = isset($_REQUEST['limit']) ? $_REQUEST['limit'] : 50;
|
$sort = isset($_REQUEST['sort']) ? json_decode($_REQUEST['sort']) : null;
|
$filters = isset($_REQUEST['filter']) ? $_REQUEST['filter'] : null;
|
|
$sortProperty = $sort[0]->property;
|
$sortDirection = $sort[0]->direction;
|
|
// GridFilters sends filters as an Array if not json encoded
|
if (is_array($filters)) {
|
$encoded = false;
|
} else {
|
$encoded = true;
|
$filters = json_decode($filters);
|
}
|
|
$where = ' 0 = 0 ';
|
$qs = '';
|
|
// loop through filters sent by client
|
if (is_array($filters)) {
|
for ($i=0;$i<count($filters);$i++){
|
$filter = $filters[$i];
|
|
// assign filter data (location depends if encoded or not)
|
if ($encoded) {
|
$field = $filter->field;
|
$value = $filter->value;
|
$compare = isset($filter->comparison) ? $filter->comparison : null;
|
$filterType = $filter->type;
|
} else {
|
$field = $filter['field'];
|
$value = $filter['data']['value'];
|
$compare = isset($filter['data']['comparison']) ? $filter['data']['comparison'] : null;
|
$filterType = $filter['data']['type'];
|
}
|
|
switch($filterType){
|
case 'string' : $qs .= " AND ".$field." LIKE '%".$value."%'"; Break;
|
case 'list' :
|
if (strstr($value,',')){
|
$fi = explode(',',$value);
|
for ($q=0;$q<count($fi);$q++){
|
$fi[$q] = "'".$fi[$q]."'";
|
}
|
$value = implode(',',$fi);
|
$qs .= " AND ".$field." IN (".$value.")";
|
}else{
|
$qs .= " AND ".$field." = '".$value."'";
|
}
|
Break;
|
case 'boolean' : $qs .= " AND ".$field." = ".($value); Break;
|
case 'numeric' :
|
switch ($compare) {
|
case 'eq' : $qs .= " AND ".$field." = ".$value; Break;
|
case 'lt' : $qs .= " AND ".$field." < ".$value; Break;
|
case 'gt' : $qs .= " AND ".$field." > ".$value; Break;
|
}
|
Break;
|
case 'date' :
|
switch ($compare) {
|
case 'eq' : $qs .= " AND ".$field." = '".date('Y-m-d',strtotime($value))."'"; Break;
|
case 'lt' : $qs .= " AND ".$field." < '".date('Y-m-d',strtotime($value))."'"; Break;
|
case 'gt' : $qs .= " AND ".$field." > '".date('Y-m-d',strtotime($value))."'"; Break;
|
}
|
Break;
|
}
|
}
|
$where .= $qs;
|
}
|
|
$query = "SELECT * FROM demo WHERE ".$where;
|
$query .= " ORDER BY ".$sortProperty." ".$sortDirection;
|
$query .= " LIMIT ".$start.",".$count;
|
|
$db = getDB();
|
$count = $db->singleQuery("SELECT COUNT(id) FROM demo WHERE ".$where);
|
$result = $db->query($query);
|
$rows = Array();
|
while($row = $result->fetch(SQLITE_ASSOC)) {
|
array_push($rows, $row);
|
}
|
echo json_encode(Array(
|
"total"=>$count,
|
"data"=>$rows
|
));
|
|
?>
|
