grid-filter.php
3.45 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
<?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
));
?>