ajax table
[dcaas.git] / data.json.php
blob:a/data.json.php -> blob:b/data.json.php
--- a/data.json.php
+++ b/data.json.php
@@ -2,15 +2,36 @@
 include("config.php");
 header("Content-Type: application/json");
 $result = Array();
-$page = filter_input(INPUT_GET, 'page',FILTER_SANITIZE_NUMBER_INT);
-$perPage     = filter_input(INPUT_GET, 'perPage',FILTER_SANITIZE_NUMBER_INT);
-$offset   = filter_input(INPUT_GET, 'offset',FILTER_SANITIZE_NUMBER_INT);
-$query = "Select acnabn, service_name from dcaas_services order by acnabn";
+$page = filter_input(INPUT_GET, 'page', FILTER_SANITIZE_NUMBER_INT);
+$perPage = filter_input(INPUT_GET, 'perPage', FILTER_SANITIZE_NUMBER_INT);
+$offset = filter_input(INPUT_GET, 'offset', FILTER_SANITIZE_NUMBER_INT);
+$search = preg_replace("/[^a-zA-Z0-9]+/", "", filter_var($_GET['queries']['search'], FILTER_SANITIZE_STRING));
+$sortby = 'acnabn';
+if (isset($_GET['sorts'])) {
+    $keys = array_keys($_GET['sorts']);
+    if (in_array($keys[0], array('trading_or_business_name', 'acnabn', 'service', 'service_category', 'service_sub_category'))) {
+        $sortby = preg_replace("/[^a-zA-Z0-9_]+/", "", $keys[0]);
+        if ($sortby == 'service') $sortby = 'service_name';
+    }
+}
+$select_columns = 'Select trading_or_business_name, acnabn, service_name, service_category, service_sub_category ';
+$query = " from dcaas_services "
+    . ($search != "" ? " where service_name like '%$search%'" : "");
 $total = $db->query("select count(*) from dcaas_services")->fetch(PDO::FETCH_NUM);
-foreach ($db->query($query." limit $offset,".($page*$perPage), PDO::FETCH_ASSOC) as $row) {
-    $result['records'][]  = $row;
+$query_total = $db->query("select count(*) ".$query)->fetch(PDO::FETCH_NUM);
+
+/* http://www.sqlite.org/lang_select.html
+Instead of a separate OFFSET clause, the LIMIT clause may specify two scalar expressions separated by a comma.
+In this case, the first expression is used as the OFFSET expression and the second as the LIMIT expression.
+This is counter-intuitive, as when using the OFFSET clause the second of the two expressions is the OFFSET
+and the first the LIMIT. This is intentional - it maximizes compatibility with other SQL database systems.*/
+$limit = "limit $offset, $perPage";
+
+foreach ($db->query($select_columns .$query . " order by $sortby $limit", PDO::FETCH_ASSOC) as $row) {
+    $row['service'] = '<a href="view.php?acnabn=' . $row['acnabn'] . "&service_name=" . $row['service_name'] . '">' . $row['service_name'] . "</a>";
+    $result['records'][] = $row;
 }
-$result['queryRecordCount'] = intval($total[0]);
+$result['queryRecordCount'] = intval($query_total[0]);
 $result['totalRecordCount'] = intval($total[0]);
 echo json_encode($result);
 ?>