interface/reports/patient_research.php

   1 <?php
   2         // Copyright (C) Medical Information Integration, LLC
   3         // This program is free software; you can redistribute it and/or
   4         // modify it under the terms of the GNU General Public License
   5         // as published by the Free Software Foundation; either version 2
   6         // of the License, or (at your option) any later version.
   7
   8         //SANITIZE ALL ESCAPES
   9         $sanitize_all_escapes=true;
  10
  11         //STOP FAKE REGISTER GLOBALS
  12         $fake_register_globals=false;
  13
  14         require_once("../globals.php");
  15         require_once("$srcdir/patient.inc");
  16         require_once("$srcdir/options.inc.php");
  17         require_once("../drugs/drugs.inc.php");
  18
  19         $report_by = htmlspecialchars($_POST['report_by'], ENT_QUOTES);
  20         $facility_id = htmlspecialchars($_POST['form_facility'], ENT_QUOTES);
  21         $provider_id = htmlspecialchars($_POST['form_provider'], ENT_QUOTES);
  22         $txtSearch = htmlspecialchars($_POST['txtSearch'], ENT_QUOTES);
  23
  24         //billing.date = date of services
  25         $sqlBindArray = array();
  26
  27         if ($_POST['form_refresh'])
  28         {
  29                 $sql = "SELECT
  30                                 concat(patient_data.fname, ' ', patient_data.lname) AS patient_name,
  31                                 patient_data.pid AS pid,
  32                                 patient_data.sex,
  33                                 patient_data.DOB,
  34                                 patient_data.city,
  35                                 patient_data.city as county";
  36
  37                 if($report_by == 'ICD9' || $report_by == 'CPT4')
  38                 {
  39                         $sql .= ",
  40                                 form_encounter.encounter AS encounter_id,
  41                                 form_encounter.date AS date_of_visit ";
  42                         $sql .= ", codes.code AS code, codes.code_text AS code_text,
  43                                                 concat(users.lname, ', ', users.fname) AS provider,
  44                                                 concat(users.fname, ' ', users.lname) AS provider2 FROM form_encounter
  45                                 INNER JOIN patient_data ON patient_data.pid = form_encounter.pid
  46                                 INNER JOIN billing ON billing.encounter = form_encounter.encounter
  47                                 LEFT OUTER JOIN codes ON codes.code = billing.code
  48                                 LEFT OUTER JOIN users ON users.id = billing.provider_id
  49                                 LEFT OUTER JOIN facility ON facility.id = users.facility_id ";
  50
  51                         $where_str = "";
  52
  53                         if($report_by == 'ICD9')
  54                         {
  55                                 $where_str = " WHERE billing.code_type = 'ICD9'";
  56                         }
  57                         else if($report_by == 'CPT4')
  58                         {
  59                                 $where_str = " WHERE billing.code_type = 'CPT4'";
  60                         }
  61
  62                         if(strlen($txtSearch) > 0)
  63                         {
  64                                 $where_str .= " AND codes.code LIKE ?";
  65                                 array_push($sqlBindArray, "%".$txtSearch."%");
  66                         }
  67
  68                         if(strlen($facility_id) > 0)
  69                         {
  70                                 $where_str .= " AND facility.id = ?";
  71                                 array_push($sqlBindArray, $facility_id);
  72                         }
  73
  74                         if(strlen($provider_id) > 0)
  75                         {
  76                                 $where_str .= " AND users.id = ?";
  77                                 array_push($sqlBindArray, $provider_id);
  78                         }
  79
  80                         $sql .= $where_str;
  81                 }
  82                 else if($report_by == 'Drugs')
  83                 {
  84                         $sql .= ", prescriptions.date_added AS date_of_visit,
  85                                         prescriptions.drug AS drug,
  86                                         concat(users.lname, ', ', users.fname) AS provider,
  87                                         concat(users.fname, ' ', users.lname) AS provider2
  88                                         FROM prescriptions
  89                                 INNER JOIN patient_data ON patient_data.pid = prescriptions.patient_id
  90                                 LEFT OUTER JOIN users ON users.id = prescriptions.provider_id
  91                                 LEFT OUTER JOIN facility ON facility.id = users.facility_id";
  92
  93                         $where_str = "";
  94
  95                         if(strlen($txtSearch) > 0)
  96                         {
  97                                 $where_str = " WHERE prescriptions.drug LIKE ?";
  98                                 array_push($sqlBindArray, "%".$txtSearch."%");
  99                         }
 100
 101                         if(strlen($facility_id) > 0)
 102                         {
 103                                 if(strlen($where_str) > 0)
 104                                 {
 105                                         $where_str .= " AND facility.id = ?";
 106                                         array_push($sqlBindArray, $facility_id);
 107                                 }
 108                                 else
 109                                 {
 110                                         $where_str = " WHERE facility.id = ?";
 111                                         array_push($sqlBindArray, $facility_id);
 112                                 }
 113                         }
 114
 115                         if(strlen($provider_id) > 0)
 116                         {
 117                                 if(strlen($where_str) > 0)
 118                                 {
 119                                         $where_str .= " AND users.id = ?";
 120                                         array_push($sqlBindArray, $provider_id);
 121                                 }
 122                                 else
 123                                 {
 124                                         $where_str = " WHERE users.id = ?";
 125                                         array_push($sqlBindArray, $provider_id);
 126                                 }
 127                         }
 128
 129                         $sql .= $where_str;
 130                 }
 131                 else if($report_by == 'Referral')
 132                 {
 133                         $sql = "SELECT
 134                                         concat(patient_data.fname, ' ', patient_data.lname) AS patient_name,
 135                                         patient_data.pid AS pid,
 136                                         patient_data.sex,
 137                                         patient_data.DOB,
 138                                         patient_data.city,
 139                                         patient_data.city as county,
 140                                         transactions.refer_related_code AS code,
 141                                         concat(users.lname, ', ', users.fname) AS provider,
 142                                         concat(users.fname, ' ', users.lname) AS provider2,
 143                                         transactions.date AS date_of_visit
 144                                         FROM transactions
 145                                         INNER JOIN patient_data ON transactions.pid = patient_data.pid
 146                                         INNER JOIN users ON transactions.user = users.username
 147                                         LEFT OUTER JOIN facility ON facility.id = users.facility_id
 148                                         WHERE transactions.title = 'Referral'";
 149
 150                         if(strlen($facility_id) > 0)
 151                         {
 152                                 $sql .= " AND facility.id = ?";
 153                                 array_push($sqlBindArray, $facility_id);
 154                         }
 155
 156                         if(strlen($provider_id) > 0)
 157                         {
 158                                 $sql .= " AND users.id = ?";
 159                                 array_push($sqlBindArray, $provider_id);
 160                         }
 161                 }
 162         }
 163
 164         if ($_POST['form_refresh'] == "export")
 165         {
 166                 $result = sqlStatement($sql, $sqlBindArray);
 167
 168                 if($report_by == 'ICD9' || $report_by == 'CPT4')
 169                 {
 170                         $out = "Code, ICD9/CPT4 Description, Patient Name, Date of Birth, Patient ID, Encounter ID, Date of Visit, Provider\n";
 171                 }
 172                 else if($report_by == 'Drugs')
 173                 {
 174                         $out = "Drugs, Patient Name, Date of Birth, Patient ID, Date of Visit, Provider\n";
 175                 }
 176                 else if($report_by == 'Referral')
 177                 {
 178                         $out = "Code, ICD9/CPT4 Description, Patient Name, Date of Birth, Patient ID, Date of Visit, Provider\n";
 179                 }
 180
 181                 while($row = sqlFetchArray($result))
 182                 {
 183                         if($report_by == 'Referral')
 184                         {
 185                                 $current_code = $row['code'];
 186                                 $code_desc = '';
 187
 188                                 if(strlen($current_code) > 0)
 189                                 {
 190                                         $current_code_arr = explode(":", $current_code);
 191                                         $result_search = sqlStatement("SELECT * FROM codes WHERE code = ?", array($current_code_arr[1]));
 192
 193                                         if(sqlNumRows($result_search) > 0)
 194                                         {
 195                                                 $row_search = sqlFetchArray($result_search);
 196                                                 $code_desc = $row_search['code_text'];
 197                                         }
 198                                 }
 199                         }
 200
 201                         if($report_by == 'ICD9' || $report_by == 'CPT4')
 202                         {
 203                                 $out .= "{$row['code']}, {$row['code_text']}, {$row['patient_name']}, {$row['DOB']}, {$row['pid']}, {$row['encounter_id']}, {$row['date_of_visit']}, {$row['provider2']}\n";
 204                         }
 205                         else if($report_by == 'Drugs')
 206                         {
 207                                 $out .= "{$row['drug']}, {$row['patient_name']}, {$row['DOB']}, {$row['pid']}, {$row['date_of_visit']}, {$row['provider2']}\n";
 208                         }
 209                         else if($report_by == 'Referral')
 210                         {
 211                                 $out .= "{$row['code']}, {$code_desc}, {$row['patient_name']}, {$row['DOB']}, {$row['pid']}, {$row['date_of_visit']}, {$row['provider2']}\n";
 212                         }
 213                 }
 214
 215                 $dt_str = date("Ymd");
 216
 217                 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 218                 header("Content-Length: " . strlen($out));
 219                 header("Content-type: text/x-csv");
 220                 header("Content-type: application/csv");
 221                 header("Content-Disposition: attachment; filename=patient_research_".$dt_str.".csv");
 222                 echo $out;
 223                 exit;
 224         }
 225 ?>
 226 <html>
 227 <head>
 228 <?php html_header_show();?>
 229 <title>
 230 <?php echo htmlspecialchars(xl('Clinical Reports'), ENT_QUOTES) ?>
 231 </title>
 232 <script type="text/javascript" src="../../library/overlib_mini.js"></script>
 233 <script type="text/javascript" src="../../library/textformat.js"></script>
 234 <script type="text/javascript" src="../../library/dialog.js"></script>
 235 <script type="text/javascript" src="../../library/js/jquery.1.3.2.js"></script>
 236 <script language="JavaScript">
 237
 238  var mypcc = '<?php echo $GLOBALS['phone_country_code'] ?>';
 239
 240  // The OnClick handler for receipt display.
 241  function show_receipt(payid) {
 242   // dlgopen('../patient_file/front_payment.php?receipt=1&payid=' + payid, '_blank', 550, 400);
 243   return false;
 244  }
 245
 246 </script>
 247 <link rel='stylesheet' href='<?php echo $css_header ?>' type='text/css'>
 248 <style type="text/css">
 249 /* specifically include & exclude from printing */
 250 @media print {
 251 #report_parameters {
 252         visibility: hidden;
 253         display: none;
 254 }
 255 #report_parameters_daterange {
 256         visibility: visible;
 257         display: inline;
 258 }
 259 #report_results table {
 260         margin-top: 0px;
 261 }
 262 }
 263
 264 /* specifically exclude some from the screen */
 265 @media screen {
 266 #report_parameters_daterange {
 267         visibility: hidden;
 268         display: none;
 269 }
 270 }
 271
 272 .optional_area {
 273         <?php
 274         if($report_by == 'Referral')
 275         {
 276         ?>
 277         display: none;
 278         <?php
 279         }
 280         ?>
 281 }
 282 </style>
 283 <script language="javascript" type="text/javascript">
 284         function checkType() {
 285                 if($('#report_by').val() == 'Referral') {
 286                         $('.optional_area').css("display", "none");
 287                 }
 288                 else {
 289                         $('.optional_area').css("display", "inline");
 290                 }
 291         }
 292 </script>
 293 </head>
 294
 295 <body class="body_top">
 296
 297 <!-- Required for the popup date selectors -->
 298 <div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
 299 <span class='title'>
 300 <?php echo htmlspecialchars(xl('Report - Facility/Prov Patient Research Reports'), ENT_QUOTES) ?>
 301 </span>
 302 <!-- Search can be done using age range, gender, and ethnicity filters.
 303 Search options include diagnosis, procedure, prescription, medical history, and lab results.
 304 -->
 305 <div id="report_parameters_daterange"> <?php echo date("d F Y", strtotime($form_from_date)) ." &nbsp; to &nbsp; ". date("d F Y", strtotime($form_to_date)); ?> </div>
 306
 307 <form method='post' name='theform' id='theform' action='<?php echo $_SERVER['REQUEST_URI'] ?>'>
 308 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 309
 310 <div id="report_parameters">
 311 <table>
 312  <tr>
 313   <td width='550px'>
 314         <div style='float:left'>
 315
 316         <table class='text'>
 317                 <tr>
 318                         <td class='label'>
 319                                 <?php echo htmlspecialchars(xl('Facility'), ENT_QUOTES) ?>:
 320                         </td>
 321                         <td>
 322                                 <?php
 323                                  // Build a drop-down list of facilities.
 324                                  //
 325                                  $query = "SELECT id, name FROM facility ORDER BY name";
 326                                  $fres = sqlStatement($query);
 327                                  echo "   <select name='form_facility'>\n";
 328                                  echo "    <option value=''>-- " . htmlspecialchars(xl('All Facilities'), ENT_QUOTES) . " --\n";
 329                                  while ($frow = sqlFetchArray($fres)) {
 330                                   $facid = $frow['id'];
 331                                   echo "    <option value='$facid'";
 332                                   if ($facid == $facility_id) echo " selected";
 333                                   echo ">" . htmlspecialchars($frow['name'], ENT_QUOTES) . "\n";
 334                                  }
 335                                  echo "    <option value='0'";
 336                                  if ($form_facility === '0') echo " selected";
 337                                  echo ">-- " . htmlspecialchars(xl('Unspecified'), ENT_QUOTES) . " --\n";
 338                                  echo "   </select>\n";
 339                                 ?>
 340                         </td>
 341                         <td class='label'>
 342                            <?php echo htmlspecialchars(xl('Provider'), ENT_QUOTES) ?>:
 343                         </td>
 344                         <td>
 345                                 <?php
 346
 347                                  // Build a drop-down list of providers.
 348                                  //
 349
 350                                  $query = "SELECT id, lname, fname FROM users WHERE ".
 351                                   "authorized = 1 $provider_facility_filter ORDER BY lname, fname"; //(CHEMED) facility filter
 352
 353                                  $ures = sqlStatement($query);
 354
 355                                  echo "   <select name='form_provider'>\n";
 356                                  echo "    <option value=''>-- " . htmlspecialchars(xl('All'), ENT_QUOTES) . " --\n";
 357
 358                                  while ($urow = sqlFetchArray($ures)) {
 359                                   $provid = htmlspecialchars($urow['id'], ENT_QUOTES);
 360                                   echo "    <option value='$provid'";
 361                                   if ($provid == $_POST['form_provider']) echo " selected";
 362                                   echo ">" . htmlspecialchars($urow['lname'] . ", " . $urow['fname'], ENT_QUOTES) . "\n";
 363                                  }
 364
 365                                  echo "   </select>\n";
 366
 367                                 ?>
 368                         </td>
 369                         <td>&nbsp;
 370                         </td>
 371                 </tr>
 372                 <tr>
 373                         <td class='label'>
 374                            <?php echo htmlspecialchars(xl('Search'), ENT_QUOTES) ?>:
 375                         </td>
 376                         <td>
 377                            <select name="report_by" id="report_by" onChange="checkType();">
 378                                 <option value="ICD9" <?php echo ($report_by == 'ICD9') ? "selected='selected'" : ''; ?>><?php echo htmlspecialchars(xl('ICD9'), ENT_QUOTES) ?></option>
 379                                 <option value="CPT4" <?php echo ($report_by == 'CPT4') ? "selected='selected'" : ''; ?>><?php echo htmlspecialchars(xl('CPT4'), ENT_QUOTES) ?></option>
 380                                 <option value="Drugs" <?php echo ($report_by == 'Drugs') ? "selected='selected'" : ''; ?>><?php echo htmlspecialchars(xl('Drugs'), ENT_QUOTES) ?></option>
 381                                 <option value="Referral" <?php echo ($report_by == 'Referral') ? "selected='selected'" : ''; ?>><?php echo htmlspecialchars(xl('Referral'), ENT_QUOTES) ?></option>
 382                            </select>
 383                            <label for="txtSearch"></label>
 384                            <input type="text" name="txtSearch" id="txtSearch" value="<?php echo $txtSearch?>" class="optional_area">&nbsp;</td>
 385                         <td class='label'>
 386
 387                         </td>
 388                         <td>
 389
 390                         </td>
 391                         <td>
 392
 393                         </td>
 394                 </tr>
 395         </table>
 396
 397         </div>
 398
 399   </td>
 400   <td align='left' valign='middle' height="100%">
 401         <table style='border-left:1px solid; width:100%; height:100%' >
 402                 <tr>
 403                         <td>
 404                                 <div style='margin-left:15px'>
 405                                         <a href='#' class='css_button' onclick='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
 406                                         <span>
 407                                                 <?php echo htmlspecialchars(xl('Submit'), ENT_QUOTES) ?>
 408                                         </span>
 409                                         </a>
 410
 411                                         <?php if ($_POST['form_refresh'] || $_POST['form_orderby'] ) { ?>
 412                                         <a href='#' class='css_button' onclick='window.print()'>
 413                                                 <span>
 414                                                         <?php echo htmlspecialchars(xl('Print'), ENT_QUOTES) ?>
 415                                                 </span>
 416                                         </a>
 417                                         <?php } ?>
 418
 419                                         <?php if ($_POST['form_refresh']) { ?>
 420                                         <a href='#' class='css_button' onclick='$("#form_refresh").attr("value","export"); $("#theform").submit();'>
 421                                                 <span>
 422                                                         <?php echo htmlspecialchars(xl('Export'), ENT_QUOTES) ?>
 423                                                 </span>
 424                                         </a>
 425                                         <?php } ?>
 426                                 </div>
 427                         </td>
 428                 </tr>
 429         </table>
 430   </td>
 431  </tr>
 432 </table>
 433
 434 </div> <!-- end report_parameters -->
 435
 436 <?php
 437
 438 // SQL scripts for the various searches
 439
 440 if ($_POST['form_refresh']) {
 441         $result = sqlStatement($sql, $sqlBindArray);
 442
 443         if(sqlNumRows($result) > 0){
 444 ?>
 445                 <div id="report_results">
 446                         <table>
 447                                 <thead>
 448                                         <?php
 449                                         if($report_by == 'ICD9' || $report_by == 'CPT4'){
 450                                                 ?>
 451                                                 <th><?php echo htmlspecialchars(xl('Code'), ENT_QUOTES) ?></th>
 452                                                 <th><?php echo htmlspecialchars(xl('ICD9/CPT4 Description'), ENT_QUOTES) ?></th>
 453                                                 <?php
 454                                         }
 455                                         else if($report_by == 'Drugs'){
 456                                                 ?>
 457                                                 <th><?php echo htmlspecialchars(xl('Drugs'), ENT_QUOTES) ?></th>
 458                                                 <?php
 459                                         }
 460                                         else if($report_by == 'Referral'){
 461                                                 ?>
 462                                                 <th><?php echo htmlspecialchars(xl('Code'), ENT_QUOTES); ?></th>
 463                                                 <th><?php echo htmlspecialchars(xl('ICD9/CPT4 Description'), ENT_QUOTES) ?></th>
 464                                                 <?php
 465                                         }
 466                                         ?>
 467                                         <th> <?php echo htmlspecialchars(xl('Patient Name'), ENT_QUOTES) ?></th>
 468                                         <th> <?php echo htmlspecialchars(xl('Date of Birth'), ENT_QUOTES) ?></th>
 469                                         <th> <?php echo htmlspecialchars(xl('Patient ID'), ENT_QUOTES) ?></th>
 470
 471                                         <?php
 472                                         if($report_by != 'Referral' && $report_by != 'Drugs' ){
 473                                         ?>
 474                                         <th> <?php echo htmlspecialchars(xl('Encounter ID'), ENT_QUOTES) ?></th>
 475                                         <?php
 476                                         }
 477                                         ?>
 478
 479
 480                                         <th> <?php echo htmlspecialchars(xl('Date of Visit'), ENT_QUOTES) ?></th>
 481                                         <th> <?php echo htmlspecialchars(xl('Provider'), ENT_QUOTES) ?></th>
 482                                 </thead>
 483                                 <tbody>
 484                                 <?php
 485                                         while($row = sqlFetchArray($result)){
 486                                                 if($report_by == 'Referral'){
 487                                                         $current_code = $row['code'];
 488                                                         $code_desc = '';
 489
 490                                                         if(strlen($current_code) > 0){
 491                                                                 $current_code_arr = explode(":", $current_code);
 492                                                                 $result_search = sqlStatement("SELECT * FROM codes WHERE code = '".$current_code_arr[1]."'");
 493
 494                                                                 if(sqlNumRows($result_search) > 0){
 495                                                                         $row_search = sqlFetchArray($result_search);
 496                                                                         $code_desc = $row_search['code_text'];
 497                                                                 }
 498                                                         }
 499                                                 }
 500                                         ?>
 501                                         <tr>
 502                                                 <?php
 503                                                 if($report_by == 'ICD9' || $report_by == 'CPT4'){
 504                                                         ?>
 505                                                         <td> <?php echo htmlspecialchars($row['code'], ENT_QUOTES); ?>&nbsp;</td>
 506                                                         <td> <?php echo htmlspecialchars($row['code_text'], ENT_QUOTES); ?>&nbsp;</td>
 507                                                         <?php
 508                                                 }
 509                                                 else if($report_by == 'Drugs'){
 510                                                         ?>
 511                                                         <td width="200"> <?php echo htmlspecialchars($row['drug'], ENT_QUOTES) ?>&nbsp;</td>
 512                                                         <?php
 513                                                 }
 514                                                 else if($report_by == 'Referral'){
 515                                                         ?>
 516                                                         <td> <?php echo htmlspecialchars($row['code'], ENT_QUOTES); ?>&nbsp;</td>
 517                                                         <td> <?php echo htmlspecialchars($code_desc, ENT_QUOTES); ?>&nbsp;</td>
 518                                                         <?php
 519                                                 }
 520                                                 ?>
 521                                                 <td> <a target="RBot" href="../patient_file/summary/demographics2.php?pid=<?php echo htmlspecialchars($row['pid'], ENT_QUOTES); ?>"><?php echo htmlspecialchars($row['patient_name'], ENT_QUOTES); ?></a>&nbsp;</td>
 522                                                 <td> <?php echo htmlspecialchars($row['DOB'], ENT_QUOTES); ?>&nbsp;</td>
 523                                                 <td> <?php echo htmlspecialchars($row['pid'], ENT_QUOTES); ?>&nbsp;</td>
 524
 525                                                 <?php
 526                                                 if($report_by != 'Referral'  && $report_by != 'Drugs' ){
 527                                                 ?>
 528                                                 <td> <a target="RBot" href="../patient_file/encounter/encounter_top.php?set_encounter=<?php echo htmlspecialchars($row['encounter_id'], ENT_QUOTES); ?>&pid=<?php echo htmlspecialchars($row['pid'], ENT_QUOTES); ?>"><?php echo htmlspecialchars($row['encounter_id'], ENT_QUOTES); ?></a>&nbsp;</td>
 529                                                 <?php
 530                                                 }
 531                                                 ?>
 532
 533
 534                                                 <td> <?php echo htmlspecialchars($row['date_of_visit'], ENT_QUOTES); ?>&nbsp;</td>
 535                                                 <td> <?php echo htmlspecialchars($row['provider'], ENT_QUOTES); ?>&nbsp;</td>
 536                                         </tr>
 537                                 <?php
 538                                         }
 539                                 ?>
 540                                 </tbody>
 541                         </table>
 542                 </div>
 543                 <!-- end of results -->
 544         <?php
 545         }
 546         ?>
 547 <?php
 548 }
 549 else
 550 {
 551         ?><div class='text'> <?php echo htmlspecialchars(xl('Please input search criteria above, and click Submit to view results.'), ENT_QUOTES) ?> </div><?php
 552 }
 553 ?>
 554 </form>
 555 </body>
 556
 557
 558 </html>