interface/orders/procedure_stats.php

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