interface/reports/misc_report3.php

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