3 * This module creates statistical reports related to lab tests and
4 * other procedure orders.
6 * Copyright (C) 2010-2013 Rod Roark <rod@sunsetsystems.com>
7 * Copyright (C) 2015 Roberto Vasquez <robertogagliotta@gmail.com>
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>;.
21 * @author Rod Roark <rod@sunsetsystems.com>
22 * @author Roberto Vasquez <robertogagliotta@gmail.com>
23 * @link http://www.open-emr.org
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";
33 // Might want something different here.
35 if (! acl_check('acct', 'rep')) die("Unauthorized access.");
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;
45 if (empty($form_by)) $form_by = '4';
46 if (empty($form_show)) $form_show = array('1');
48 // One of these is chosen as the left column, or Y-axis, of the report.
50 $report_title = xl('Procedure Statistics Report');
52 4 => xl('Specific Result'),
53 5 => xl('Followups Indicated'),
56 // This will become the array of reportable values.
59 // This accumulates the bottom line totals.
63 // '.total' => array('title' => 'Total Positives'),
64 '.tneg' => array('title' => 'Total Negatives'),
65 '.age' => array('title' => 'Age Category'),
66 ); // info about selectable columns
68 $arr_titles = array(); // will contain column headers
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();
83 // Compute age in years given a DOB and "as of" date.
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
97 function genStartRow($att) {
98 global $cellcount, $form_output;
99 if ($form_output != 3) echo " <tr $att>\n";
103 function genEndRow() {
105 if ($form_output == 3) {
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'];
120 // Usually this generates one cell, but allows for two or more.
122 function genAnyCell($data, $right=false, $class='') {
123 global $cellcount, $form_output;
124 if (!is_array($data)) {
125 $data = array(0 => $data);
127 foreach ($data as $datum) {
128 if ($form_output == 3) {
129 if ($cellcount) echo ',';
130 echo '"' . $datum . '"';
134 if ($class) echo " class='$class'";
135 if ($right) echo " align='right'";
136 echo ">$datum</td>\n";
142 function genHeadCell($data, $right=false) {
143 genAnyCell($data, $right, 'dehead');
146 // Create an HTML table cell containing a numeric value, and track totals.
148 function genNumCell($num, $cnum) {
149 global $atotals, $form_output;
150 $atotals[$cnum] +
= $num;
151 if (empty($num) && $form_output != 3) $num = ' ';
152 genAnyCell($num, true, 'detail');
155 // Helper function called after the reporting key is determined for a row.
157 function loadColumnData($key, $row) {
158 global $areport, $arr_titles, $from_date, $to_date, $arr_show;
160 // If no result, do nothing.
161 if (empty($row['abnormal'])) return;
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();
177 // Flag this patient as having been encountered for this report row.
178 $areport[$key]['.prp'] = $row['pid'];
180 // Collect abnormal results only, except for a column of total negatives.
181 if ($row['abnormal'] == 'no') {
182 ++
$areport[$key]['.neg'];
186 // Increment the correct sex category.
187 if (strcasecmp($row['sex'], 'Male') == 0)
188 ++
$areport[$key]['.men'];
190 ++
$areport[$key]['.wom'];
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];
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;
210 // This is called for each row returned from the query.
212 function process_result_code($row) {
213 global $areport, $arr_titles, $form_by;
215 // Specific Results. One row for each result name.
217 if ($form_by === '4') {
218 $key = $row['order_name'] . ' / ' . $row['result_name'];
219 loadColumnData($key, $row);
222 // Recommended followup services.
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;
232 loadColumnData($key, $row);
237 } // end function process_result_code()
239 // If we are doing the CSV export then generate the needed HTTP headers.
240 // Otherwise generate HTML.
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");
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 }
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
'] ?>';
272 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0'>
276 <h2
><?php
echo $report_title; ?
></h2
>
278 <form name
='theform' method
='post' action
='procedure_stats.php'>
280 <table border
='0' cellspacing
='5' cellpadding
='1'>
283 <td valign
='top' class='dehead' nowrap
>
284 <?php
xl('Rows','e'); ?
>:
286 <td valign
='top' class='detail'>
287 <select name
='form_by' title
='Left column of report'>
289 foreach ($arr_by as $key => $value) {
290 echo " <option value='$key'";
291 if ($key == $form_by) echo " selected";
292 echo ">" . $value . "</option>\n";
297 <td valign
='top' class='dehead' nowrap
>
298 <?php
xl('Filters','e'); ?
>:
300 <td rowspan
='2' colspan
='2' class='detail'
301 style
='border-style:solid;border-width:1px;border-color:#cccccc'>
304 <td valign
='top' class='detail' nowrap
>
305 <?php
xl('Sex','e'); ?
>:
307 <td
class='detail' valign
='top'>
308 <select name
='form_sexes' title
='<?php xl('To filter by sex
','e
'); ?>'>
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";
320 <td valign
='top' class='detail' nowrap
>
321 <?php
xl('Facility','e'); ?
>:
323 <td valign
='top' class='detail'>
324 <?php
dropdown_facility(strip_escape_custom($form_facility), 'form_facility', false); ?
>
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
'); ?>'>
347 <td valign
='top' class='dehead' nowrap
>
348 <?php
xl('Columns','e'); ?
>:
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
'); ?>'>
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";
366 <td valign
='top' class='dehead' nowrap
>
367 <?php
xl('To','e'); ?
>:
369 <td colspan
='3' valign
='top' class='detail' nowrap
>
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 ";
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
'); ?>' />
384 <td colspan
='5' height
="1">
391 if ($_POST['form_submit']) {
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;
402 if ($form_sexes == '1') $sexcond = "AND pd.sex NOT LIKE 'Male' ";
403 else if ($form_sexes == '2') $sexcond = "AND pd.sex LIKE 'Male' ";
405 // This gets us all results, with encounter and patient
406 // info attached and grouped by patient and encounter.
408 $sqlBindArray = array();
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, " .
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 <= ? ";
433 array_push($sqlBindArray, $from_date, $to_date);
435 if ($form_facility) {
436 $query .= "AND fe.facility_id = ? ";
437 array_push($sqlBindArray, $form_facility);
439 $query .= "ORDER BY fe.pid, fe.encounter, ps.result_code"; // needed?
441 $res = sqlStatement($query, $sqlBindArray);
443 while ($row = sqlFetchArray($res)) {
444 process_result_code($row);
447 // Sort everything by key for reporting.
449 foreach ($arr_titles as $atkey => $dummy) ksort($arr_titles[$atkey]);
451 if ($form_output != 3) {
452 echo "<table border='0' cellpadding='1' cellspacing='2' width='98%'>\n";
453 } // end not csv export
455 genStartRow("bgcolor='#dddddd'");
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')
461 genHeadCell(array($arr_by[$form_by], xl('Description')));
463 genHeadCell($arr_by[$form_by]);
466 // Generate headings for values to be shown.
467 foreach ($form_show as $value) {
468 // if ($value == '.total') { // Total Positives
469 // genHeadCell(xl('Total'));
471 if ($value == '.tneg') { // Total Negatives
472 genHeadCell(xl('Negatives'));
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);
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);
490 else if (!empty($arr_titles[$value])) {
491 foreach ($arr_titles[$value] as $key => $dummy) {
492 genHeadCell($key, true);
497 if ($form_output != 3) {
498 genHeadCell(xl('Positives'), true);
505 foreach ($areport as $key => $varr) {
506 $bgcolor = (++
$encount & 1) ?
"#ddddff" : "#ffdddd";
510 // If the key is an MA or IPPF code, then get its description.
511 if ($form_by === '5')
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'];
521 genStartRow("bgcolor='$bgcolor'");
523 genAnyCell($dispkey, false, 'detail');
525 // This is the column index for accumulating column totals.
527 $totalsvcs = $areport[$key]['.wom'] +
$areport[$key]['.men'];
529 // Generate data for this row.
530 foreach ($form_show as $value) {
531 // if ($value == '.total') { // Total Positives
532 // genNumCell($totalsvcs, $cnum++);
534 if ($value == '.tneg') { // Total Negatives
535 genNumCell($areport[$key]['.neg'], $cnum++
);
537 else if ($value == '.age') { // Age
538 for ($i = 0; $i < 9; ++
$i) {
539 genNumCell($areport[$key]['.age'][$i], $cnum++
);
542 else if (!empty($arr_titles[$value])) {
543 foreach ($arr_titles[$value] as $title => $dummy) {
544 genNumCell($areport[$key][$value][$title], $cnum++
);
549 // Write the Total column data.
550 if ($form_output != 3) {
551 $atotals[$cnum] +
= $totalsvcs;
552 genAnyCell($totalsvcs, true, 'dehead');
558 if ($form_output != 3) {
559 // Generate the line of totals.
560 genStartRow("bgcolor='#dddddd'");
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')
566 genHeadCell(array(xl('Totals'), ''));
568 genHeadCell(xl('Totals'));
571 for ($cnum = 0; $cnum < count($atotals); ++
$cnum) {
572 genHeadCell($atotals[$cnum], true);
579 } // end of if refresh or export
581 if ($form_output != 3) {
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
);