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 require_once($GLOBALS['srcdir']."/options.inc.php");
  12
  13 $popup = empty($_REQUEST['popup']) ? 0 : 1;
  14
  15 // With the ColReorder or ColReorderWithResize plug-in, the expected column
  16 // ordering may have been changed by the user.  So we cannot depend on
  17 // list_options to provide that.
  18 //
  19 $aColumns = explode(',', $_GET['sColumns']);
  20
  21 // Paging parameters.  -1 means not applicable.
  22 //
  23 $iDisplayStart  = isset($_GET['iDisplayStart' ]) ? 0 + $_GET['iDisplayStart' ] : -1;
  24 $iDisplayLength = isset($_GET['iDisplayLength']) ? 0 + $_GET['iDisplayLength'] : -1;
  25 $limit = '';
  26 if ($iDisplayStart >= 0 && $iDisplayLength >= 0) {
  27     $limit = "LIMIT " . escape_limit($iDisplayStart) . ", " . escape_limit($iDisplayLength);
  28 }
  29 // Search parameter.  -1 means .
  30 //
  31 $searchMethodInPatientList = isset($_GET['searchType' ]) && $_GET['searchType' ]==="true" ?  true : false;
  32
  33 // Column sorting parameters.
  34 //
  35 $orderby = '';
  36 if (isset($_GET['iSortCol_0'])) {
  37     for ($i = 0; $i < intval($_GET['iSortingCols']); ++$i) {
  38         $iSortCol = intval($_GET["iSortCol_$i"]);
  39         if ($_GET["bSortable_$iSortCol"] == "true") {
  40             $sSortDir = escape_sort_order($_GET["sSortDir_$i"]); // ASC or DESC
  41             // We are to sort on column # $iSortCol in direction $sSortDir.
  42             $orderby .= $orderby ? ', ' : 'ORDER BY ';
  43             //
  44             if ($aColumns[$iSortCol] == 'name') {
  45                 $orderby .= "lname $sSortDir, fname $sSortDir, mname $sSortDir";
  46             } else {
  47                 $orderby .= "`" . escape_sql_column_name($aColumns[$iSortCol], array('patient_data')) . "` $sSortDir";
  48             }
  49         }
  50     }
  51 }
  52
  53 // Global filtering.
  54 //
  55 $where = '';
  56 if (isset($_GET['sSearch']) && $_GET['sSearch'] !== "") {
  57     $sSearch = add_escape_custom(trim($_GET['sSearch']));
  58     foreach ($aColumns as $colname) {
  59         $where .= $where ? "OR " : "WHERE ( ";
  60         if ($colname == 'name') {
  61             if (!$searchMethodInPatientList) { // exact search
  62                 $where .=
  63                     "lname LIKE '$sSearch' OR " .
  64                     "fname LIKE '$sSearch' OR " .
  65                     "mname LIKE '$sSearch' ";
  66             } else {
  67                 $where .= // like search
  68                     "lname LIKE '$sSearch%' OR " .
  69                     "fname LIKE '$sSearch%' OR " .
  70                     "mname LIKE '$sSearch%' ";
  71             }
  72         } elseif (!$searchMethodInPatientList) {
  73             $where .= "`" . escape_sql_column_name($colname, array('patient_data')) . "` LIKE '$sSearch' ";
  74         } else { // exact search
  75             $where .= "`" . escape_sql_column_name($colname, array('patient_data')) . "` LIKE '$sSearch%' ";
  76         }
  77     }
  78
  79     if ($where) {
  80         $where .= ")";
  81     }
  82 }
  83
  84 // Column-specific filtering.
  85 //
  86 for ($i = 0; $i < count($aColumns); ++$i) {
  87     $colname = $aColumns[$i];
  88     if (isset($_GET["bSearchable_$i"]) && $_GET["bSearchable_$i"] == "true" && $_GET["sSearch_$i"] != '') {
  89         $where .= $where ? ' AND' : 'WHERE';
  90         $sSearch = add_escape_custom($_GET["sSearch_$i"]);
  91         if ($colname == 'name') {
  92             if (!$searchMethodInPatientList) { // like search
  93                 $where .= " ( " .
  94                     "lname LIKE '$sSearch%' OR " .
  95                     "fname LIKE '$sSearch%' OR " .
  96                     "mname LIKE '$sSearch%' )";
  97             } else {  // exact search
  98                 $where .= " ( " .
  99                     "lname LIKE '$sSearch' OR " .
 100                     "fname LIKE '$sSearch' OR " .
 101                     "mname LIKE '$sSearch' )";
 102             }
 103         } elseif (!$searchMethodInPatientList) {
 104             $where .= " `" . escape_sql_column_name($colname, array('patient_data')) . "` LIKE '$sSearch%'"; // like search
 105         } else {
 106             $where .= " `" . escape_sql_column_name($colname, array('patient_data')) . "` LIKE '$sSearch'"; // exact search
 107         }
 108     }
 109 }
 110
 111 // Compute list of column names for SELECT clause.
 112 // Always includes pid because we need it for row identification.
 113 //
 114 $sellist = 'pid';
 115 foreach ($aColumns as $colname) {
 116     if ($colname == 'pid') {
 117         continue;
 118     }
 119
 120     $sellist .= ", ";
 121     if ($colname == 'name') {
 122         $sellist .= "lname, fname, mname";
 123     } else {
 124         $sellist .= "`" . escape_sql_column_name($colname, array('patient_data')) . "`";
 125     }
 126 }
 127
 128 // Get total number of rows in the table.
 129 //
 130 $row = sqlQuery("SELECT COUNT(id) AS count FROM patient_data");
 131 $iTotal = $row['count'];
 132
 133 // Get total number of rows in the table after filtering.
 134 //
 135 $row = sqlQuery("SELECT COUNT(id) AS count FROM patient_data $where");
 136 $iFilteredTotal = $row['count'];
 137
 138 // Build the output data array.
 139 //
 140 $out = array(
 141     "sEcho"                => intval($_GET['sEcho']),
 142     "iTotalRecords"        => $iTotal,
 143     "iTotalDisplayRecords" => $iFilteredTotal,
 144     "aaData"               => array()
 145 );
 146
 147 // save into variable data about fields of 'patient_data' from 'layout_options'
 148 $fieldsInfo = array();
 149 $quoteSellist = preg_replace('/(\w+)/i', '"${1}"', str_replace('`', '', $sellist));
 150 $res = sqlStatement('SELECT data_type, field_id, list_id FROM layout_options WHERE form_id = "DEM" AND field_id IN(' . $quoteSellist . ')');
 151 while ($row = sqlFetchArray($res)) {
 152     $fieldsInfo[$row['field_id']] = $row;
 153 }
 154
 155 $query = "SELECT $sellist FROM patient_data $where $orderby $limit";
 156 $res = sqlStatement($query);
 157 while ($row = sqlFetchArray($res)) {
 158     // Each <tr> will have an ID identifying the patient.
 159     $arow = array('DT_RowId' => 'pid_' . $row['pid']);
 160     foreach ($aColumns as $colname) {
 161         if ($colname == 'name') {
 162             $name = $row['lname'];
 163             if ($name && $row['fname']) {
 164                 $name .= ', ';
 165             }
 166
 167             if ($row['fname']) {
 168                 $name .= $row['fname'];
 169             }
 170
 171             if ($row['mname']) {
 172                 $name .= ' ' . $row['mname'];
 173             }
 174
 175             $arow[] = attr($name);
 176         } else {
 177             $arow[] = isset($fieldsInfo[$colname]) ? attr(generate_plaintext_field($fieldsInfo[$colname], $row[$colname])) : attr($row[$colname]);
 178         }
 179     }
 180
 181     $out['aaData'][] = $arow;
 182 }
 183
 184 // error_log($query); // debugging
 185
 186 // Dump the output array as JSON.
 187 //
 188 // Encoding with options for escaping a special chars - JSON_HEX_TAG (<)(>), JSON_HEX_AMP(&), JSON_HEX_APOS('), JSON_HEX_QUOT(").
 189 echo json_encode($out, 15);