interface/orders/procedure_stats.php

   1 <?php
   2 /**
   3  * This module creates statistical reports related to lab tests and
   4  * other procedure orders.
   5  *
   6  *  Copyright (C) 2010-2013 Rod Roark <rod@sunsetsystems.com>
   7  *  Copyright (C) 2015 Roberto Vasquez <robertogagliotta@gmail.com>
   8  *
   9  * LICENSE: This program is free software; you can redistribute it and/or
  10  * modify it under the terms of the GNU General Public License
  11  * as published by the Free Software Foundation; either version 2
  12  * of the License, or (at your option) any later version.
  13  * This program is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  16  * GNU General Public License for more details.
  17  * You should have received a copy of the GNU General Public License
  18  * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
  19  *
  20  * @package OpenEMR
  21  * @author  Rod Roark <rod@sunsetsystems.com>
  22  * @author Roberto Vasquez <robertogagliotta@gmail.com>
  23  * @link    http://www.open-emr.org
  24  */
  25
  26 require_once("../globals.php");
  27 require_once("../../library/patient.inc");
  28 require_once("../../library/acl.inc");
  29 require_once("../../custom/code_types.inc.php");
  30 require_once "$srcdir/options.inc.php";
  31 require_once "$srcdir/formdata.inc.php";
  32
  33 // Might want something different here.
  34 //
  35 if (! acl_check('acct', 'rep')) die("Unauthorized access.");
  36
  37 $from_date     = fixDate($_POST['form_from_date']);
  38 $to_date       = fixDate($_POST['form_to_date'], date('Y-m-d'));
  39 $form_by       = $_POST['form_by'];     // this is a scalar
  40 $form_show     = $_POST['form_show'];   // this is an array
  41 $form_facility = isset($_POST['form_facility']) ? $_POST['form_facility'] : '';
  42 $form_sexes    = isset($_POST['form_sexes']) ? $_POST['form_sexes'] : '3';
  43 $form_output   = isset($_POST['form_output']) ? 0 + $_POST['form_output'] : 1;
  44
  45 if (empty($form_by))    $form_by = '4';
  46 if (empty($form_show))  $form_show = array('1');
  47
  48 // One of these is chosen as the left column, or Y-axis, of the report.
  49 //
  50 $report_title = xl('Procedure Statistics Report');
  51 $arr_by = array(
  52   4  => xl('Specific Result'),
  53   5  => xl('Followups Indicated'),
  54 );
  55
  56 // This will become the array of reportable values.
  57 $areport = array();
  58
  59 // This accumulates the bottom line totals.
  60 $atotals = array();
  61
  62 $arr_show   = array(
  63   // '.total' => array('title' => 'Total Positives'),
  64   '.tneg'  => array('title' => 'Total Negatives'),
  65   '.age'   => array('title' => 'Age Category'),
  66 ); // info about selectable columns
  67
  68 $arr_titles = array(); // will contain column headers
  69
  70 // Query layout_options table to generate the $arr_show table.
  71 // Table key is the field ID.
  72 $lres = sqlStatement("SELECT field_id, title, data_type, list_id, description " .
  73   "FROM layout_options WHERE " .
  74   "form_id = 'DEM' AND uor > 0 AND field_id NOT LIKE 'em%' " .
  75   "ORDER BY group_name, seq, title");
  76 while ($lrow = sqlFetchArray($lres)) {
  77   $fid = $lrow['field_id'];
  78   if ($fid == 'fname' || $fid == 'mname' || $fid == 'lname') continue;
  79   $arr_show[$fid] = $lrow;
  80   $arr_titles[$fid] = array();
  81 }
  82
  83 // Compute age in years given a DOB and "as of" date.
  84 //
  85 function getAge($dob, $asof='') {
  86   if (empty($asof)) $asof = date('Y-m-d');
  87   $a1 = explode('-', substr($dob , 0, 10));
  88   $a2 = explode('-', substr($asof, 0, 10));
  89   $age = $a2[0] - $a1[0];
  90   if ($a2[1] < $a1[1] || ($a2[1] == $a1[1] && $a2[2] < $a1[2])) --$age;
  91   // echo "<!-- $dob $asof $age -->\n"; // debugging
  92   return $age;
  93 }
  94
  95 $cellcount = 0;
  96
  97 function genStartRow($att) {
  98   global $cellcount, $form_output;
  99   if ($form_output != 3) echo " <tr $att>\n";
 100   $cellcount = 0;
 101 }
 102
 103 function genEndRow() {
 104   global $form_output;
 105   if ($form_output == 3) {
 106     echo "\n";
 107   }
 108   else {
 109     echo " </tr>\n";
 110   }
 111 }
 112
 113 function getListTitle($list, $option) {
 114   $row = sqlQuery("SELECT title FROM list_options WHERE " .
 115     "list_id = '$list' AND option_id = '$option'");
 116   if (empty($row['title'])) return $option;
 117   return $row['title'];
 118 }
 119
 120 // Usually this generates one cell, but allows for two or more.
 121 //
 122 function genAnyCell($data, $right=false, $class='') {
 123   global $cellcount, $form_output;
 124   if (!is_array($data)) {
 125     $data = array(0 => $data);
 126   }
 127   foreach ($data as $datum) {
 128     if ($form_output == 3) {
 129       if ($cellcount) echo ',';
 130       echo '"' . $datum . '"';
 131     }
 132     else {
 133       echo "  <td";
 134       if ($class) echo " class='$class'";
 135       if ($right) echo " align='right'";
 136       echo ">$datum</td>\n";
 137     }
 138     ++$cellcount;
 139   }
 140 }
 141
 142 function genHeadCell($data, $right=false) {
 143   genAnyCell($data, $right, 'dehead');
 144 }
 145
 146 // Create an HTML table cell containing a numeric value, and track totals.
 147 //
 148 function genNumCell($num, $cnum) {
 149   global $atotals, $form_output;
 150   $atotals[$cnum] += $num;
 151   if (empty($num) && $form_output != 3) $num = '&nbsp;';
 152   genAnyCell($num, true, 'detail');
 153 }
 154
 155 // Helper function called after the reporting key is determined for a row.
 156 //
 157 function loadColumnData($key, $row) {
 158   global $areport, $arr_titles, $from_date, $to_date, $arr_show;
 159
 160   // If no result, do nothing.
 161   if (empty($row['abnormal'])) return;
 162
 163   // If first instance of this key, initialize its arrays.
 164   if (empty($areport[$key])) {
 165     $areport[$key] = array();
 166     $areport[$key]['.prp'] = 0;       // previous pid
 167     $areport[$key]['.wom'] = 0;       // number of positive results for women
 168     $areport[$key]['.men'] = 0;       // number of positive results for men
 169     $areport[$key]['.neg'] = 0;       // number of negative results
 170     $areport[$key]['.age'] = array(0,0,0,0,0,0,0,0,0); // age array
 171     foreach ($arr_show as $askey => $dummy) {
 172       if (substr($askey, 0, 1) == '.') continue;
 173       $areport[$key][$askey] = array();
 174     }
 175   }
 176
 177   // Flag this patient as having been encountered for this report row.
 178   $areport[$key]['.prp'] = $row['pid'];
 179
 180   // Collect abnormal results only, except for a column of total negatives.
 181   if ($row['abnormal'] == 'no') {
 182     ++$areport[$key]['.neg'];
 183     return;
 184   }
 185
 186   // Increment the correct sex category.
 187   if (strcasecmp($row['sex'], 'Male') == 0)
 188     ++$areport[$key]['.men'];
 189   else
 190     ++$areport[$key]['.wom'];
 191
 192   // Increment the correct age category.
 193   $age = getAge(fixDate($row['DOB']), $row['date_ordered']);
 194   $i = min(intval(($age - 5) / 5), 8);
 195   if ($age < 11) $i = 0;
 196   ++$areport[$key]['.age'][$i];
 197
 198   // For each patient attribute to report, this increments the array item
 199   // whose key is the attribute's value.  This works well for list-based
 200   // attributes.  A key of "Unspecified" is used where the attribute has
 201   // no assigned value.
 202   foreach ($arr_show as $askey => $dummy) {
 203     if (substr($askey, 0, 1) == '.') continue;
 204     $status = empty($row[$askey]) ? 'Unspecified' : $row[$askey];
 205     $areport[$key][$askey][$status] += 1;
 206     $arr_titles[$askey][$status] += 1;
 207   }
 208 }
 209
 210 // This is called for each row returned from the query.
 211 //
 212 function process_result_code($row) {
 213   global $areport, $arr_titles, $form_by;
 214
 215   // Specific Results.  One row for each result name.
 216   //
 217   if ($form_by === '4') {
 218     $key = $row['order_name'] . ' / ' . $row['result_name'];
 219     loadColumnData($key, $row);
 220   }
 221
 222   // Recommended followup services.
 223   //
 224   else if ($form_by === '5') {
 225     if (!empty($row['related_code'])) {
 226       $relcodes = explode(';', $row['related_code']);
 227       foreach ($relcodes as $codestring) {
 228         if ($codestring === '') continue;
 229         // list($codetype, $code) = explode(':', $codestring);
 230         // if ($codetype !== 'IPPF') continue;
 231         $key = $codestring;
 232         loadColumnData($key, $row);
 233       }
 234     }
 235   }
 236
 237 } // end function process_result_code()
 238
 239   // If we are doing the CSV export then generate the needed HTTP headers.
 240   // Otherwise generate HTML.
 241   //
 242   if ($form_output == 3) {
 243     header("Pragma: public");
 244     header("Expires: 0");
 245     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 246     header("Content-Type: application/force-download");
 247     header("Content-Disposition: attachment; filename=service_statistics_report.csv");
 248     header("Content-Description: File Transfer");
 249   }
 250   else {
 251 ?>
 252 <html>
 253 <head>
 254 <?php html_header_show(); ?>
 255 <title><?php echo $report_title; ?></title>
 256 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 257 <style type="text/css">
 258  body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
 259  .dehead    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:bold }
 260  .detail    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:normal }
 261 </style>
 262 <script type="text/javascript" src="../../library/textformat.js"></script>
 263 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 264 <?php include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?>
 265 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 266 <script language="JavaScript">
 267  var mypcc = '<?php echo $GLOBALS['phone_country_code'] ?>';
 268
 269 </script>
 270 </head>
 271
 272 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
 273
 274 <center>
 275
 276 <h2><?php echo $report_title; ?></h2>
 277
 278 <form name='theform' method='post' action='procedure_stats.php'>
 279
 280 <table border='0' cellspacing='5' cellpadding='1'>
 281
 282  <tr>
 283   <td valign='top' class='dehead' nowrap>
 284    <?php xl('Rows','e'); ?>:
 285   </td>
 286   <td valign='top' class='detail'>
 287    <select name='form_by' title='Left column of report'>
 288 <?php
 289   foreach ($arr_by as $key => $value) {
 290     echo "    <option value='$key'";
 291     if ($key == $form_by) echo " selected";
 292     echo ">" . $value . "</option>\n";
 293   }
 294 ?>
 295    </select>
 296   </td>
 297   <td valign='top' class='dehead' nowrap>
 298    <?php xl('Filters','e'); ?>:
 299   </td>
 300   <td rowspan='2' colspan='2' class='detail'
 301    style='border-style:solid;border-width:1px;border-color:#cccccc'>
 302    <table>
 303     <tr>
 304      <td valign='top' class='detail' nowrap>
 305       <?php xl('Sex','e'); ?>:
 306      </td>
 307      <td class='detail' valign='top'>
 308       <select name='form_sexes' title='<?php xl('To filter by sex','e'); ?>'>
 309 <?php
 310   foreach (array(3 => xl('Men and Women'), 1 => xl('Women Only'), 2 => xl('Men Only')) as $key => $value) {
 311     echo "       <option value='$key'";
 312     if ($key == $form_sexes) echo " selected";
 313     echo ">$value</option>\n";
 314   }
 315 ?>
 316       </select>
 317      </td>
 318     </tr>
 319     <tr>
 320      <td valign='top' class='detail' nowrap>
 321       <?php xl('Facility','e'); ?>:
 322      </td>
 323      <td valign='top' class='detail'>
 324       <?php dropdown_facility(strip_escape_custom($form_facility), 'form_facility', false); ?>
 325      </td>
 326     </tr>
 327     <tr>
 328      <td colspan='2' class='detail' nowrap>
 329       <?php xl('From','e'); ?>
 330       <input type='text' name='form_from_date' id='form_from_date' size='10' value='<?php echo $from_date ?>'
 331        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='Start date yyyy-mm-dd'>
 332       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 333        id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 334        title='<?php xl('Click here to choose a date','e'); ?>'>
 335       <?php xl('To','e'); ?>
 336       <input type='text' name='form_to_date' id='form_to_date' size='10' value='<?php echo $to_date ?>'
 337        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='End date yyyy-mm-dd'>
 338       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 339        id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 340        title='<?php xl('Click here to choose a date','e'); ?>'>
 341      </td>
 342     </tr>
 343    </table>
 344   </td>
 345  </tr>
 346  <tr>
 347   <td valign='top' class='dehead' nowrap>
 348    <?php xl('Columns','e'); ?>:
 349   </td>
 350   <td valign='top' class='detail'>
 351    <select name='form_show[]' size='4' multiple
 352     title='<?php xl('Hold down Ctrl to select multiple items','e'); ?>'>
 353 <?php
 354   foreach ($arr_show as $key => $value) {
 355     $title = $value['title'];
 356     if (empty($title) || $key == 'title') $title = $value['description'];
 357     echo "    <option value='$key'";
 358     if (is_array($form_show) && in_array($key, $form_show)) echo " selected";
 359     echo ">$title</option>\n";
 360   }
 361 ?>
 362    </select>
 363   </td>
 364  </tr>
 365  <tr>
 366   <td valign='top' class='dehead' nowrap>
 367    <?php xl('To','e'); ?>:
 368   </td>
 369   <td colspan='3' valign='top' class='detail' nowrap>
 370 <?php
 371 foreach (array(1 => 'Screen', 2 => 'Printer', 3 => 'Export File') as $key => $value) {
 372   echo "   <input type='radio' name='form_output' value='$key'";
 373   if ($key == $form_output) echo ' checked';
 374   echo " />$value &nbsp;";
 375 }
 376 ?>
 377   </td>
 378   <td align='right' valign='top' class='detail' nowrap>
 379    <input type='submit' name='form_submit' value='<?php xl('Submit','e'); ?>'
 380     title='<?php xl('Click to generate the report','e'); ?>' />
 381   </td>
 382  </tr>
 383  <tr>
 384   <td colspan='5' height="1">
 385   </td>
 386  </tr>
 387 </table>
 388 <?php
 389   } // end not export
 390
 391   if ($_POST['form_submit']) {
 392     $pd_fields = '';
 393     foreach ($arr_show as $askey => $asval) {
 394       if (substr($askey, 0, 1) == '.') continue;
 395       if ($askey == 'regdate' || $askey == 'sex' || $askey == 'DOB' ||
 396         $askey == 'lname' || $askey == 'fname' || $askey == 'mname' ||
 397         $askey == 'contrastart' || $askey == 'referral_source') continue;
 398       $pd_fields .= ', pd.' . $askey;
 399     }
 400
 401     $sexcond = '';
 402     if ($form_sexes == '1') $sexcond = "AND pd.sex NOT LIKE 'Male' ";
 403     else if ($form_sexes == '2') $sexcond = "AND pd.sex LIKE 'Male' ";
 404
 405     // This gets us all results, with encounter and patient
 406     // info attached and grouped by patient and encounter.
 407
 408     $sqlBindArray = array();
 409
 410     $query = "SELECT " .
 411       "po.patient_id, po.encounter_id, po.date_ordered, " .
 412       "po.provider_id, pd.regdate, " .
 413       "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
 414       "pd.contrastart, pd.referral_source$pd_fields, " .
 415       "ps.abnormal, " .
 416       // "pto.name AS order_name, ptr.name AS result_name, ptr.related_code " .
 417       "pc.procedure_name AS order_name, ptr.name AS result_name, ptr.related_code " .
 418       "FROM procedure_order AS po " .
 419       "JOIN form_encounter AS fe ON fe.pid = po.patient_id AND fe.encounter = po.encounter_id " .
 420       "JOIN patient_data AS pd ON pd.pid = po.patient_id $sexcond" .
 421       "JOIN procedure_order_code AS pc ON pc.procedure_order_id = po.procedure_order_id " .
 422       "JOIN procedure_report AS pr ON pr.procedure_order_id = po.procedure_order_id " .
 423       "AND pr.procedure_order_seq = pc.procedure_order_seq " .
 424       "AND pr.date_report IS NOT NULL " .
 425       "JOIN procedure_result AS ps ON ps.procedure_report_id = pr.procedure_report_id " .
 426       "AND ps.result_status = 'final' " .
 427       // "JOIN procedure_type AS pto ON pto.procedure_type_id = pc.procedure_type_id " .
 428       "JOIN procedure_type AS ptr ON ptr.lab_id = po.lab_id AND ptr.procedure_code = ps.result_code " .
 429       "AND ptr.procedure_type LIKE 'res%' " .
 430       "WHERE po.date_ordered IS NOT NULL AND po.date_ordered >= ? " .
 431       "AND po.date_ordered <= ? ";
 432
 433     array_push($sqlBindArray, $from_date, $to_date);
 434
 435     if ($form_facility) {
 436       $query .= "AND fe.facility_id = ? ";
 437       array_push($sqlBindArray, $form_facility);
 438     }
 439     $query .= "ORDER BY fe.pid, fe.encounter, ps.result_code"; // needed?
 440
 441     $res = sqlStatement($query, $sqlBindArray);
 442
 443     while ($row = sqlFetchArray($res)) {
 444       process_result_code($row);
 445     }
 446
 447     // Sort everything by key for reporting.
 448     ksort($areport);
 449     foreach ($arr_titles as $atkey => $dummy) ksort($arr_titles[$atkey]);
 450
 451     if ($form_output != 3) {
 452       echo "<table border='0' cellpadding='1' cellspacing='2' width='98%'>\n";
 453     } // end not csv export
 454
 455     genStartRow("bgcolor='#dddddd'");
 456
 457     // genHeadCell($arr_by[$form_by]);
 458     // If the key is an MA or IPPF code, then add a column for its description.
 459     if ($form_by === '5')
 460     {
 461       genHeadCell(array($arr_by[$form_by], xl('Description')));
 462     } else {
 463       genHeadCell($arr_by[$form_by]);
 464     }
 465
 466     // Generate headings for values to be shown.
 467     foreach ($form_show as $value) {
 468       // if ($value == '.total') { // Total Positives
 469       //   genHeadCell(xl('Total'));
 470       // }
 471       if ($value == '.tneg') { // Total Negatives
 472         genHeadCell(xl('Negatives'));
 473       }
 474       else if ($value == '.age') { // Age
 475         genHeadCell(xl('0-10' ), true);
 476         genHeadCell(xl('11-14'), true);
 477         genHeadCell(xl('15-19'), true);
 478         genHeadCell(xl('20-24'), true);
 479         genHeadCell(xl('25-29'), true);
 480         genHeadCell(xl('30-34'), true);
 481         genHeadCell(xl('35-39'), true);
 482         genHeadCell(xl('40-44'), true);
 483         genHeadCell(xl('45+'  ), true);
 484       }
 485       else if ($arr_show[$value]['list_id']) {
 486         foreach ($arr_titles[$value] as $key => $dummy) {
 487           genHeadCell(getListTitle($arr_show[$value]['list_id'],$key), true);
 488         }
 489       }
 490       else if (!empty($arr_titles[$value])) {
 491         foreach ($arr_titles[$value] as $key => $dummy) {
 492           genHeadCell($key, true);
 493         }
 494       }
 495     }
 496
 497     if ($form_output != 3) {
 498       genHeadCell(xl('Positives'), true);
 499     }
 500
 501     genEndRow();
 502
 503     $encount = 0;
 504
 505     foreach ($areport as $key => $varr) {
 506       $bgcolor = (++$encount & 1) ? "#ddddff" : "#ffdddd";
 507
 508       $dispkey = $key;
 509
 510       // If the key is an MA or IPPF code, then get its description.
 511       if ($form_by === '5')
 512       {
 513         list($codetype, $code) = explode(':', $key);
 514         $type = $code_types[$codetype]['id'];
 515         $dispkey = array($key, '');
 516         $crow = sqlQuery("SELECT code_text FROM codes WHERE " .
 517           "code_type = '$type' AND code = '$code' ORDER BY id LIMIT 1");
 518         if (!empty($crow['code_text'])) $dispkey[1] = $crow['code_text'];
 519       }
 520
 521       genStartRow("bgcolor='$bgcolor'");
 522
 523       genAnyCell($dispkey, false, 'detail');
 524
 525       // This is the column index for accumulating column totals.
 526       $cnum = 0;
 527       $totalsvcs = $areport[$key]['.wom'] + $areport[$key]['.men'];
 528
 529       // Generate data for this row.
 530       foreach ($form_show as $value) {
 531         // if ($value == '.total') { // Total Positives
 532         //   genNumCell($totalsvcs, $cnum++);
 533         // }
 534         if ($value == '.tneg') { // Total Negatives
 535           genNumCell($areport[$key]['.neg'], $cnum++);
 536         }
 537         else if ($value == '.age') { // Age
 538           for ($i = 0; $i < 9; ++$i) {
 539             genNumCell($areport[$key]['.age'][$i], $cnum++);
 540           }
 541         }
 542         else if (!empty($arr_titles[$value])) {
 543           foreach ($arr_titles[$value] as $title => $dummy) {
 544             genNumCell($areport[$key][$value][$title], $cnum++);
 545           }
 546         }
 547       }
 548
 549       // Write the Total column data.
 550       if ($form_output != 3) {
 551         $atotals[$cnum] += $totalsvcs;
 552         genAnyCell($totalsvcs, true, 'dehead');
 553       }
 554
 555       genEndRow();
 556     } // end foreach
 557
 558     if ($form_output != 3) {
 559       // Generate the line of totals.
 560       genStartRow("bgcolor='#dddddd'");
 561
 562       // genHeadCell(xl('Totals'));
 563       // If the key is an MA or IPPF code, then add a column for its description.
 564       if ($form_by === '5')
 565       {
 566         genHeadCell(array(xl('Totals'), ''));
 567       } else {
 568         genHeadCell(xl('Totals'));
 569       }
 570
 571       for ($cnum = 0; $cnum < count($atotals); ++$cnum) {
 572         genHeadCell($atotals[$cnum], true);
 573       }
 574       genEndRow();
 575       // End of table.
 576       echo "</table>\n";
 577     }
 578
 579   } // end of if refresh or export
 580
 581   if ($form_output != 3) {
 582 ?>
 583 </form>
 584 </center>
 585
 586 <script language='JavaScript'>
 587  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 588  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 589 <?php if ($form_output == 2) { ?>
 590  var win = top.printLogPrint ? top : opener.top;
 591  win.printLogPrint(window);
 592 <?php } ?>
 593 </script>
 594
 595 </body>
 596 </html>
 597 <?php
 598   } // end not export
 599 ?>