interface/main/finder/dynamic_finder_ajax.php

   1 <?php
   2 // Copyright (C) 2012 Rod Roark <rod@sunsetsystems.com>
   3 // Sponsored by David Eschelbacher, MD
   4 //
   5 // This program is free software; you can redistribute it and/or
   6 // modify it under the terms of the GNU General Public License
   7 // as published by the Free Software Foundation; either version 2
   8 // of the License, or (at your option) any later version.
   9
  10 require_once("../../globals.php");
  11
  12 $popup = empty($_REQUEST['popup']) ? 0 : 1;
  13
  14 // With the ColReorder or ColReorderWithResize plug-in, the expected column
  15 // ordering may have been changed by the user.  So we cannot depend on
  16 // list_options to provide that.
  17 //
  18 $aColumns = explode(',', $_GET['sColumns']);
  19
  20 // Paging parameters.  -1 means not applicable.
  21 //
  22 $iDisplayStart  = isset($_GET['iDisplayStart' ]) ? 0 + $_GET['iDisplayStart' ] : -1;
  23 $iDisplayLength = isset($_GET['iDisplayLength']) ? 0 + $_GET['iDisplayLength'] : -1;
  24 $limit = '';
  25 if ($iDisplayStart >= 0 && $iDisplayLength >= 0) {
  26     $limit = "LIMIT " . escape_limit($iDisplayStart) . ", " . escape_limit($iDisplayLength);
  27 }
  28
  29 // Column sorting parameters.
  30 //
  31 $orderby = '';
  32 if (isset($_GET['iSortCol_0'])) {
  33     for ($i = 0; $i < intval($_GET['iSortingCols']); ++$i) {
  34         $iSortCol = intval($_GET["iSortCol_$i"]);
  35         if ($_GET["bSortable_$iSortCol"] == "true") {
  36             $sSortDir = escape_sort_order($_GET["sSortDir_$i"]); // ASC or DESC
  37       // We are to sort on column # $iSortCol in direction $sSortDir.
  38             $orderby .= $orderby ? ', ' : 'ORDER BY ';
  39       //
  40             if ($aColumns[$iSortCol] == 'name') {
  41                     $orderby .= "lname $sSortDir, fname $sSortDir, mname $sSortDir";
  42             } else {
  43                     $orderby .= "`" . escape_sql_column_name($aColumns[$iSortCol], array('patient_data')) . "` $sSortDir";
  44             }
  45         }
  46     }
  47 }
  48
  49 // Global filtering.
  50 //
  51 $where = '';
  52 if (isset($_GET['sSearch']) && $_GET['sSearch'] !== "") {
  53     $sSearch = add_escape_custom($_GET['sSearch']);
  54     foreach ($aColumns as $colname) {
  55         $where .= $where ? "OR " : "WHERE ( ";
  56         if ($colname == 'name') {
  57             $where .=
  58             "lname LIKE '$sSearch%' OR " .
  59             "fname LIKE '$sSearch%' OR " .
  60             "mname LIKE '$sSearch%' ";
  61         } else {
  62             $where .= "`" . escape_sql_column_name($colname, array('patient_data')) . "` LIKE '$sSearch%' ";
  63         }
  64     }
  65
  66     if ($where) {
  67         $where .= ")";
  68     }
  69 }
  70
  71 // Column-specific filtering.
  72 //
  73 for ($i = 0; $i < count($aColumns); ++$i) {
  74     $colname = $aColumns[$i];
  75     if (isset($_GET["bSearchable_$i"]) && $_GET["bSearchable_$i"] == "true" && $_GET["sSearch_$i"] != '') {
  76         $where .= $where ? ' AND' : 'WHERE';
  77         $sSearch = add_escape_custom($_GET["sSearch_$i"]);
  78         if ($colname == 'name') {
  79             $where .= " ( " .
  80             "lname LIKE '$sSearch%' OR " .
  81             "fname LIKE '$sSearch%' OR " .
  82             "mname LIKE '$sSearch%' )";
  83         } else {
  84             $where .= " `" . escape_sql_column_name($colname, array('patient_data')) . "` LIKE '$sSearch%'";
  85         }
  86     }
  87 }
  88
  89 // Compute list of column names for SELECT clause.
  90 // Always includes pid because we need it for row identification.
  91 //
  92 $sellist = 'pid';
  93 foreach ($aColumns as $colname) {
  94     if ($colname == 'pid') {
  95         continue;
  96     }
  97
  98     $sellist .= ", ";
  99     if ($colname == 'name') {
 100         $sellist .= "lname, fname, mname";
 101     } else {
 102         $sellist .= "`" . escape_sql_column_name($colname, array('patient_data')) . "`";
 103     }
 104 }
 105
 106 // Get total number of rows in the table.
 107 //
 108 $row = sqlQuery("SELECT COUNT(id) AS count FROM patient_data");
 109 $iTotal = $row['count'];
 110
 111 // Get total number of rows in the table after filtering.
 112 //
 113 $row = sqlQuery("SELECT COUNT(id) AS count FROM patient_data $where");
 114 $iFilteredTotal = $row['count'];
 115
 116 // Build the output data array.
 117 //
 118 $out = array(
 119   "sEcho"                => intval($_GET['sEcho']),
 120   "iTotalRecords"        => $iTotal,
 121   "iTotalDisplayRecords" => $iFilteredTotal,
 122   "aaData"               => array()
 123 );
 124 $query = "SELECT $sellist FROM patient_data $where $orderby $limit";
 125 $res = sqlStatement($query);
 126 while ($row = sqlFetchArray($res)) {
 127   // Each <tr> will have an ID identifying the patient.
 128     $arow = array('DT_RowId' => 'pid_' . $row['pid']);
 129     foreach ($aColumns as $colname) {
 130         if ($colname == 'name') {
 131             $name = $row['lname'];
 132             if ($name && $row['fname']) {
 133                 $name .= ', ';
 134             }
 135
 136             if ($row['fname']) {
 137                 $name .= $row['fname'];
 138             }
 139
 140             if ($row['mname']) {
 141                 $name .= ' ' . $row['mname'];
 142             }
 143
 144             $arow[] = $name;
 145         } else if ($colname == 'DOB' || $colname == 'regdate' || $colname == 'ad_reviewed' || $colname == 'userdate1') {
 146             $arow[] = oeFormatShortDate($row[$colname]);
 147         } else {
 148             $arow[] = $row[$colname];
 149         }
 150     }
 151
 152     $out['aaData'][] = $arow;
 153 }
 154
 155 // error_log($query); // debugging
 156
 157 // Dump the output array as JSON.
 158 //
 159 echo json_encode($out);