Previous names in New/Search doesn't save. (#7438)
[openemr.git] / interface / orders / procedure_stats.php
blob003350cb53144497c7a1baa6d159e8619c838d36
1 <?php
3 /**
4 * This module creates statistical reports related to lab tests and
5 * other procedure orders.
7 * @package OpenEMR
8 * @link http://www.open-emr.org
9 * @author Rod Roark <rod@sunsetsystems.com>
10 * @author Roberto Vasquez <robertogagliotta@gmail.com>
11 * @author Brady Miller <brady.g.miller@gmail.com>
12 * @copyright Copyright (c) 2010-2016 Rod Roark <rod@sunsetsystems.com>
13 * @copyright Copyright (c) 2015 Roberto Vasquez <robertogagliotta@gmail.com>
14 * @copyright Copyright (c) 2017-2019 Brady Miller <brady.g.miller@gmail.com>
15 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
18 require_once("../globals.php");
19 require_once("../../library/patient.inc.php");
20 require_once("../../custom/code_types.inc.php");
21 require_once "$srcdir/options.inc.php";
23 use OpenEMR\Common\Acl\AclMain;
24 use OpenEMR\Common\Csrf\CsrfUtils;
25 use OpenEMR\Common\Twig\TwigContainer;
26 use OpenEMR\Core\Header;
28 if (!AclMain::aclCheckCore('patients', 'lab')) {
29 echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Procedure Statistics Report")]);
30 exit;
33 $from_date = isset($_POST['form_from_date']) ? DateToYYYYMMDD($_POST['form_from_date']) : '0000-00-00';
34 $to_date = isset($_POST['form_to_date']) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
35 $form_by = $_POST['form_by'] ?? null; // this is a scalar
36 $form_show = $_POST['form_show'] ?? null; // this is an array
37 $form_facility = isset($_POST['form_facility']) ? $_POST['form_facility'] : '';
38 $form_sexes = isset($_POST['form_sexes']) ? $_POST['form_sexes'] : '3';
39 $form_output = isset($_POST['form_output']) ? 0 + $_POST['form_output'] : 1;
41 if (empty($form_by)) {
42 $form_by = '4';
45 if (empty($form_show)) {
46 $form_show = array('1');
49 // One of these is chosen as the left column, or Y-axis, of the report.
51 $report_title = xl('Procedure Statistics Report');
52 $arr_by = array(
53 4 => xl('Specific Result'),
54 5 => xl('Followups Indicated'),
57 // This will become the array of reportable values.
58 $areport = array();
60 // This accumulates the bottom line totals.
61 $atotals = array();
63 $arr_show = array(
64 // '.total' => array('title' => 'Total Positives'),
65 '.tneg' => array('title' => 'Total Negatives'),
66 '.age' => array('title' => 'Age Category'),
67 ); // info about selectable columns
69 $arr_titles = array(); // will contain column headers
71 // Query layout_options table to generate the $arr_show table.
72 // Table key is the field ID.
73 $lres = sqlStatement("SELECT field_id, title, data_type, list_id, description " .
74 "FROM layout_options WHERE " .
75 "form_id = 'DEM' AND uor > 0 AND field_id NOT LIKE 'em%' " .
76 "ORDER BY group_id, seq, title");
77 while ($lrow = sqlFetchArray($lres)) {
78 $fid = $lrow['field_id'];
79 if (
80 $fid == 'fname'
81 || $fid == 'mname'
82 || $fid == 'lname'
83 || $fid == 'additional_addresses'
84 ) {
85 continue;
88 $arr_show[$fid] = $lrow;
89 $arr_titles[$fid] = array();
92 // Compute age in years given a DOB and "as of" date.
94 function getAge($dob, $asof = '')
96 if (empty($asof)) {
97 $asof = date('Y-m-d');
100 $a1 = explode('-', substr($dob, 0, 10));
101 $a2 = explode('-', substr($asof, 0, 10));
102 $age = $a2[0] - $a1[0];
103 if ($a2[1] < $a1[1] || ($a2[1] == $a1[1] && $a2[2] < $a1[2])) {
104 --$age;
107 // echo "<!-- $dob $asof $age -->\n"; // debugging
108 return $age;
111 $cellcount = 0;
113 function genStartRow($att)
115 global $cellcount, $form_output;
116 if ($form_output != 3) {
117 echo " <tr $att>\n";
120 $cellcount = 0;
123 function genEndRow()
125 global $form_output;
126 if ($form_output == 3) {
127 echo "\n";
128 } else {
129 echo " </tr>\n";
133 function getListTitle($list, $option)
135 $row = sqlQuery("SELECT title FROM list_options WHERE " .
136 "list_id = ? AND option_id = ? AND activity = 1", array($list, $option));
137 if (empty($row['title'])) {
138 return $option;
141 return $row['title'];
144 // Usually this generates one cell, but allows for two or more.
146 function genAnyCell($data, $right = false, $class = '')
148 global $cellcount, $form_output;
149 if (!is_array($data)) {
150 $data = array(0 => $data);
153 foreach ($data as $datum) {
154 if ($form_output == 3) {
155 if ($cellcount) {
156 echo ',';
159 echo '"' . attr($datum) . '"';
160 } else {
161 echo " <td";
162 if ($class) {
163 echo " class='" . attr($class) . "'";
166 if ($right) {
167 echo " align='right'";
170 echo ">" . text($datum) . "</td>\n";
173 ++$cellcount;
177 function genHeadCell($data, $right = false)
179 genAnyCell($data, $right, 'dehead');
182 // Create an HTML table cell containing a numeric value, and track totals.
184 function genNumCell($num, $cnum)
186 global $atotals, $form_output;
187 $atotals[$cnum] += $num;
188 if (empty($num) && $form_output != 3) {
189 $num = '&nbsp;';
192 genAnyCell($num, true, 'detail');
195 // Helper function called after the reporting key is determined for a row.
197 function loadColumnData($key, $row)
199 global $areport, $arr_titles, $arr_show;
201 // If no result, do nothing.
202 if (empty($row['abnormal'])) {
203 return;
206 // If first instance of this key, initialize its arrays.
207 if (empty($areport[$key])) {
208 $areport[$key] = array();
209 $areport[$key]['.prp'] = 0; // previous pid
210 $areport[$key]['.wom'] = 0; // number of positive results for women
211 $areport[$key]['.men'] = 0; // number of positive results for men
212 $areport[$key]['.neg'] = 0; // number of negative results
213 $areport[$key]['.age'] = array(0,0,0,0,0,0,0,0,0); // age array
214 foreach ($arr_show as $askey => $dummy) {
215 if (substr($askey, 0, 1) == '.') {
216 continue;
219 $areport[$key][$askey] = array();
223 // Flag this patient as having been encountered for this report row.
224 $areport[$key]['.prp'] = $row['pid'];
226 // Collect abnormal results only, except for a column of total negatives.
227 if ($row['abnormal'] == 'no') {
228 ++$areport[$key]['.neg'];
229 return;
232 // Increment the correct sex category.
233 if (strcasecmp($row['sex'], 'Male') == 0) {
234 ++$areport[$key]['.men'];
235 } else {
236 ++$areport[$key]['.wom'];
239 // Increment the correct age category.
240 $age = getAge($row['DOB'], $row['date_ordered']);
241 $i = min(intval(($age - 5) / 5), 8);
242 if ($age < 11) {
243 $i = 0;
246 ++$areport[$key]['.age'][$i];
248 // For each patient attribute to report, this increments the array item
249 // whose key is the attribute's value. This works well for list-based
250 // attributes. A key of "Unspecified" is used where the attribute has
251 // no assigned value.
252 foreach ($arr_show as $askey => $dummy) {
253 if (substr($askey, 0, 1) == '.') {
254 continue;
257 $status = empty($row[$askey]) ? 'Unspecified' : $row[$askey];
258 $areport[$key][$askey][$status] += 1;
259 $arr_titles[$askey][$status] += 1;
263 // This is called for each row returned from the query.
265 function process_result_code($row)
267 global $areport, $arr_titles, $form_by;
269 // Specific Results. One row for each result name.
271 if ($form_by === '4') {
272 $key = $row['order_name'] . ' / ' . $row['result_name'];
273 loadColumnData($key, $row);
274 } elseif ($form_by === '5') { // Recommended followup services.
275 if (!empty($row['related_code'])) {
276 $relcodes = explode(';', $row['related_code']);
277 foreach ($relcodes as $codestring) {
278 if ($codestring === '') {
279 continue;
282 // list($codetype, $code) = explode(':', $codestring);
283 // if ($codetype !== 'IPPF') continue;
284 $key = $codestring;
285 loadColumnData($key, $row);
289 } // end function process_result_code()
291 // If we are doing the CSV export then generate the needed HTTP headers.
292 // Otherwise generate HTML.
294 if ($form_output == 3) {
295 header("Pragma: public");
296 header("Expires: 0");
297 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
298 header("Content-Type: application/force-download");
299 header("Content-Disposition: attachment; filename=service_statistics_report.csv");
300 header("Content-Description: File Transfer");
301 } else {
303 <html>
304 <head>
305 <title><?php echo text($report_title); ?></title>
307 <?php Header::setupHeader(['datetime-picker', 'textformat', 'jquery']); ?>
309 <style>
310 .dehead {
311 font-family: sans-serif;
312 font-weight: bold;
314 </style>
316 <script>
317 $(function () {
318 $('.datepicker').datetimepicker({
319 <?php $datetimepicker_timepicker = false; ?>
320 <?php $datetimepicker_showseconds = false; ?>
321 <?php $datetimepicker_formatInput = true; ?>
322 <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
323 <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
326 </script>
328 </head>
330 <body class='m-0'>
332 <center>
334 <h2><?php echo text($report_title); ?></h2>
336 <form name='theform' method='post' action='procedure_stats.php' onsubmit='return top.restoreSession()'>
337 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
339 <div class="col-8 col-md-8">
340 <div class="row">
341 <div class="col">
342 <?php echo xlt('Facility'); ?>:
343 <?php dropdown_facility($form_facility, 'form_facility', false); ?>
344 </div>
345 <div class="col">
346 <?php echo xlt('From'); ?>
347 <input type='text' class='form-control datepicker' name='form_from_date' id='form_from_date' size='10' value='<?php echo attr(oeFormatShortDate($from_date)); ?>' />
348 </div>
349 <div class="col">
350 <?php echo xlt('To{{Range}}'); ?>
351 <input type='text' class='form-control datepicker' name='form_to_date' id='form_to_date' size='10' value='<?php echo attr(oeFormatShortDate($to_date)); ?>' />
352 </div>
353 </div>
354 </div>
355 <br>
356 <div class="col-8 col-md-8">
357 <div class="row">
358 <div class="col">
359 <?php echo xlt('Rows'); ?>:
360 </div>
361 <div class="col">
362 <select name='form_by' class='form-control' title='Left column of report'>
363 <?php
364 foreach ($arr_by as $key => $value) {
365 echo " <option value='" . attr($key) . "'";
366 if ($key == $form_by) {
367 echo " selected";
370 echo ">" . text($value) . "</option>\n";
373 </select>
374 </div>
375 <div class="col">
376 <?php echo xlt('Sex'); ?>:
377 </div>
378 <div class="col">
379 <select class='form-control' name='form_sexes' title='<?php echo xla('To filter by sex'); ?>'>
380 <?php
381 foreach (array(3 => xl('Men and Women'), 1 => xl('Women Only'), 2 => xl('Men Only')) as $key => $value) {
382 echo " <option value='" . attr($key) . "'";
383 if ($key == $form_sexes) {
384 echo " selected";
387 echo ">" . text($value) . "</option>\n";
390 </select>
391 </div>
392 </div>
393 </div>
394 <br>
395 <div class="col-8 col-md-8">
396 <div class="row">
397 <div class="col">
398 <?php echo xlt('Columns'); ?>:
399 <select class='form-control' name='form_show[]' size='4' multiple title='<?php echo xla('Hold down Ctrl to select multiple items'); ?>'>
400 <?php
401 foreach ($arr_show as $key => $value) {
402 $title = $value['title'];
403 if (empty($title) || $key == 'title') {
404 $title = $value['description'];
407 echo " <option value='" . attr($key) . "'";
408 if (is_array($form_show) && in_array($key, $form_show)) {
409 echo " selected";
412 echo ">" . text($title) . "</option>\n";
415 </select>
416 </div>
417 </div>
418 </div>
419 <br>
420 <div class="col-8 col-md-8">
421 <div class="row">
422 <div class="col">
423 <?php echo xlt('To{{Destination}}'); ?>:
425 <?php
426 foreach (array(1 => xl('Screen'), 2 => xl('Printer'), 3 => xl('Export File')) as $key => $value) {
427 echo " <input type='radio' name='form_output' value='" . attr($key) . "'";
428 if ($key == $form_output) {
429 echo ' checked';
432 echo " />" . text($value) . " &nbsp;";
435 </div>
436 </div>
437 </div>
438 <br>
439 <div class="col-4 col-md-4">
440 <div class="col">
441 <button type='submit' class='btn btn-primary btn-save' name='form_submit' value='form_submit' title='<?php echo xla('Click to generate the report'); ?>'><?php echo xla('Submit') ?></button>
442 </div>
443 </div>
444 <br>
446 <?php
447 } // end not export
449 if (!empty($_POST['form_submit'])) {
450 if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
451 CsrfUtils::csrfNotVerified();
454 $pd_fields = '';
455 foreach ($arr_show as $askey => $asval) {
456 if (substr($askey, 0, 1) == '.') {
457 continue;
460 if (
461 $askey == 'regdate' || $askey == 'sex' || $askey == 'DOB' ||
462 $askey == 'lname' || $askey == 'fname' || $askey == 'mname' ||
463 $askey == 'contrastart' || $askey == 'referral_source'
465 continue;
468 $pd_fields .= ', pd.' . escape_sql_column_name($askey, array('patient_data'));
471 $sexcond = '';
472 if ($form_sexes == '1') {
473 $sexcond = "AND pd.sex NOT LIKE 'Male' ";
474 } elseif ($form_sexes == '2') {
475 $sexcond = "AND pd.sex LIKE 'Male' ";
478 // This gets us all results, with encounter and patient
479 // info attached and grouped by patient and encounter.
481 $sqlBindArray = array();
483 $query = "SELECT " .
484 "po.patient_id, po.encounter_id, po.date_ordered, " .
485 "po.provider_id, pd.regdate, " .
486 "pd.sex, pd.DOB, pd.lname, pd.fname, pd.mname, " .
487 "pd.contrastart, pd.referral_source$pd_fields, " .
488 "ps.abnormal, " .
489 // "pto.name AS order_name, ptr.name AS result_name, ptr.related_code " .
490 "pc.procedure_name AS order_name, ptr.name AS result_name, ptr.related_code " .
491 "FROM procedure_order AS po " .
492 "JOIN form_encounter AS fe ON fe.pid = po.patient_id AND fe.encounter = po.encounter_id " .
493 "JOIN patient_data AS pd ON pd.pid = po.patient_id $sexcond" .
494 "JOIN procedure_order_code AS pc ON pc.procedure_order_id = po.procedure_order_id " .
495 "JOIN procedure_report AS pr ON pr.procedure_order_id = po.procedure_order_id " .
496 "AND pr.procedure_order_seq = pc.procedure_order_seq " .
497 "AND pr.date_report IS NOT NULL " .
498 "JOIN procedure_result AS ps ON ps.procedure_report_id = pr.procedure_report_id " .
499 "AND ps.result_status = 'final' " .
500 // "JOIN procedure_type AS pto ON pto.procedure_type_id = pc.procedure_type_id " .
501 "JOIN procedure_type AS ptr ON ptr.lab_id = po.lab_id AND ptr.procedure_code = ps.result_code " .
502 "AND ptr.procedure_type LIKE 'res%' " .
503 "WHERE po.date_ordered IS NOT NULL AND po.date_ordered >= ? " .
504 "AND po.date_ordered <= ? ";
506 array_push($sqlBindArray, $from_date, $to_date);
508 if ($form_facility) {
509 $query .= "AND fe.facility_id = ? ";
510 array_push($sqlBindArray, $form_facility);
513 $query .= "ORDER BY fe.pid, fe.encounter, ps.result_code"; // needed?
515 $res = sqlStatement($query, $sqlBindArray);
517 while ($row = sqlFetchArray($res)) {
518 process_result_code($row);
521 // Sort everything by key for reporting.
522 ksort($areport);
523 foreach ($arr_titles as $atkey => $dummy) {
524 ksort($arr_titles[$atkey]);
527 if ($form_output != 3) {
528 echo "<table class='table' cellpadding='1' cellspacing='2' width='98%'>\n";
529 } // end not csv export
531 genStartRow("bgcolor='#dddddd'");
533 // genHeadCell($arr_by[$form_by]);
534 // If the key is an MA or IPPF code, then add a column for its description.
535 if ($form_by === '5') {
536 genHeadCell(array($arr_by[$form_by], xl('Description')));
537 } else {
538 genHeadCell($arr_by[$form_by]);
541 // Generate headings for values to be shown.
542 foreach ($form_show as $value) {
543 // if ($value == '.total') { // Total Positives
544 // genHeadCell(xl('Total'));
545 // }
546 if ($value == '.tneg') { // Total Negatives
547 genHeadCell(xl('Negatives'));
548 } elseif ($value == '.age') { // Age
549 genHeadCell(xl('0-10'), true);
550 genHeadCell(xl('11-14'), true);
551 genHeadCell(xl('15-19'), true);
552 genHeadCell(xl('20-24'), true);
553 genHeadCell(xl('25-29'), true);
554 genHeadCell(xl('30-34'), true);
555 genHeadCell(xl('35-39'), true);
556 genHeadCell(xl('40-44'), true);
557 genHeadCell(xl('45+'), true);
558 } elseif (!empty($arr_show[$value]['list_id'])) {
559 foreach ($arr_titles[$value] as $key => $dummy) {
560 genHeadCell(getListTitle($arr_show[$value]['list_id'], $key), true);
562 } elseif (!empty($arr_titles[$value])) {
563 foreach ($arr_titles[$value] as $key => $dummy) {
564 genHeadCell($key, true);
569 if ($form_output != 3) {
570 genHeadCell(xl('Positives'), true);
573 genEndRow();
575 $encount = 0;
577 foreach ($areport as $key => $varr) {
578 $bgcolor = (++$encount & 1) ? "#ddddff" : "#ffdddd";
580 $dispkey = $key;
582 // If the key is an MA or IPPF code, then get its description.
583 if ($form_by === '5') {
584 list($codetype, $code) = explode(':', $key);
585 $type = $code_types[$codetype]['id'];
586 $dispkey = array($key, '');
587 $crow = sqlQuery("SELECT code_text FROM codes WHERE " .
588 "code_type = ? AND code = ? ORDER BY id LIMIT 1", array($type, $code));
589 if (!empty($crow['code_text'])) {
590 $dispkey[1] = $crow['code_text'];
594 genStartRow("bgcolor='" . attr($bgcolor) . "'");
596 genAnyCell($dispkey, false, 'detail');
598 // This is the column index for accumulating column totals.
599 $cnum = 0;
600 $totalsvcs = $areport[$key]['.wom'] + $areport[$key]['.men'];
602 // Generate data for this row.
603 foreach ($form_show as $value) {
604 // if ($value == '.total') { // Total Positives
605 // genNumCell($totalsvcs, $cnum++);
606 // }
607 if ($value == '.tneg') { // Total Negatives
608 genNumCell($areport[$key]['.neg'], $cnum++);
609 } elseif ($value == '.age') { // Age
610 for ($i = 0; $i < 9; ++$i) {
611 genNumCell($areport[$key]['.age'][$i], $cnum++);
613 } elseif (!empty($arr_titles[$value])) {
614 foreach ($arr_titles[$value] as $title => $dummy) {
615 genNumCell($areport[$key][$value][$title], $cnum++);
620 // Write the Total column data.
621 if ($form_output != 3) {
622 $atotals[$cnum] += $totalsvcs;
623 genAnyCell($totalsvcs, true, 'dehead');
626 genEndRow();
627 } // end foreach
629 if ($form_output != 3) {
630 // Generate the line of totals.
631 genStartRow("bgcolor='#dddddd'");
633 // genHeadCell(xl('Totals'));
634 // If the key is an MA or IPPF code, then add a column for its description.
635 if ($form_by === '5') {
636 genHeadCell(array(xl('Totals'), ''));
637 } else {
638 genHeadCell(xl('Totals'));
641 for ($cnum = 0; $cnum < count($atotals); ++$cnum) {
642 genHeadCell($atotals[$cnum], true);
645 genEndRow();
646 // End of table.
647 echo "</table>\n";
649 } // end of if refresh or export
651 if ($form_output != 3) {
653 </form>
654 </center>
656 <script>
657 <?php if ($form_output == 2) { ?>
658 var win = top.printLogPrint ? top : opener.top;
659 win.printLogPrint(window);
660 <?php } ?>
661 </script>
663 </body>
664 </html>
665 <?php
666 } // end not export