interface/main/finder/dynamic_finder_ajax.php

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