Integrated access control upgrading into the sql_upgrade.php script - will simplify...
[openemr.git] / interface / orders / procedure_stats.php
blob5bddebc913a907d39f8518ba7b188a768ab199fa
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.
9 // This module creates statistical reports related to lab tests and
10 // other procedure orders.
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";
19 // Might want something different here.
21 if (! acl_check('acct', 'rep')) die("Unauthorized access.");
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;
31 if (empty($form_by)) $form_by = '4';
32 if (empty($form_show)) $form_show = array('1');
34 // One of these is chosen as the left column, or Y-axis, of the report.
36 $report_title = xl('Procedure Statistics Report');
37 $arr_by = array(
38 4 => xl('Specific Result'),
39 5 => xl('Followups Indicated'),
42 // This will become the array of reportable values.
43 $areport = array();
45 // This accumulates the bottom line totals.
46 $atotals = array();
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
54 $arr_titles = array(); // will contain column headers
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();
69 // Compute age in years given a DOB and "as of" date.
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;
81 $cellcount = 0;
83 function genStartRow($att) {
84 global $cellcount, $form_output;
85 if ($form_output != 3) echo " <tr $att>\n";
86 $cellcount = 0;
89 function genEndRow() {
90 global $form_output;
91 if ($form_output == 3) {
92 echo "\n";
94 else {
95 echo " </tr>\n";
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'];
106 // Usually this generates one cell, but allows for two or more.
108 function genAnyCell($data, $right=false, $class='') {
109 global $cellcount, $form_output;
110 if (!is_array($data)) {
111 $data = array(0 => $data);
113 foreach ($data as $datum) {
114 if ($form_output == 3) {
115 if ($cellcount) echo ',';
116 echo '"' . $datum . '"';
118 else {
119 echo " <td";
120 if ($class) echo " class='$class'";
121 if ($right) echo " align='right'";
122 echo ">$datum</td>\n";
124 ++$cellcount;
128 function genHeadCell($data, $right=false) {
129 genAnyCell($data, $right, 'dehead');
132 // Create an HTML table cell containing a numeric value, and track totals.
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');
141 // Helper function called after the reporting key is determined for a row.
143 function loadColumnData($key, $row) {
144 global $areport, $arr_titles, $from_date, $to_date, $arr_show;
146 // If no result, do nothing.
147 if (empty($row['abnormal'])) return;
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();
163 // Flag this patient as having been encountered for this report row.
164 $areport[$key]['.prp'] = $row['pid'];
166 // Collect abnormal results only, except for a column of total negatives.
167 if ($row['abnormal'] == 'no') {
168 ++$areport[$key]['.neg'];
169 return;
172 // Increment the correct sex category.
173 if (strcasecmp($row['sex'], 'Male') == 0)
174 ++$areport[$key]['.men'];
175 else
176 ++$areport[$key]['.wom'];
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];
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;
196 // This is called for each row returned from the query.
198 function process_result_code($row) {
199 global $areport, $arr_titles, $form_by;
201 // Specific Results. One row for each result name.
203 if ($form_by === '4') {
204 $key = $row['order_name'] . ' / ' . $row['result_name'];
205 loadColumnData($key, $row);
208 // Recommended followup services.
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);
223 } // end function process_result_code()
225 // If we are doing the CSV export then generate the needed HTTP headers.
226 // Otherwise generate HTML.
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");
236 else {
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'] ?>';
255 </script>
256 </head>
258 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
260 <center>
262 <h2><?php echo $report_title; ?></h2>
264 <form name='theform' method='post' action='procedure_stats.php'>
266 <table border='0' cellspacing='5' cellpadding='1'>
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";
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";
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";
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;";
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
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;
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' ";
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' ";
415 $query .= "ORDER BY fe.pid, fe.encounter, ps.procedure_type_id"; // needed?
417 $res = sqlStatement($query);
419 while ($row = sqlFetchArray($res)) {
420 process_result_code($row);
423 // Sort everything by key for reporting.
424 ksort($areport);
425 foreach ($arr_titles as $atkey => $dummy) ksort($arr_titles[$atkey]);
427 if ($form_output != 3) {
428 echo "<table border='0' cellpadding='1' cellspacing='2' width='98%'>\n";
429 } // end not csv export
431 genStartRow("bgcolor='#dddddd'");
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')
437 genHeadCell(array($arr_by[$form_by], xl('Description')));
438 } else {
439 genHeadCell($arr_by[$form_by]);
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'));
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);
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);
466 else if (!empty($arr_titles[$value])) {
467 foreach ($arr_titles[$value] as $key => $dummy) {
468 genHeadCell($key, true);
473 if ($form_output != 3) {
474 genHeadCell(xl('Positives'), true);
477 genEndRow();
479 $encount = 0;
481 foreach ($areport as $key => $varr) {
482 $bgcolor = (++$encount & 1) ? "#ddddff" : "#ffdddd";
484 $dispkey = $key;
486 // If the key is an MA or IPPF code, then get its description.
487 if ($form_by === '5')
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'];
497 genStartRow("bgcolor='$bgcolor'");
499 genAnyCell($dispkey, false, 'detail');
501 // This is the column index for accumulating column totals.
502 $cnum = 0;
503 $totalsvcs = $areport[$key]['.wom'] + $areport[$key]['.men'];
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++);
513 else if ($value == '.age') { // Age
514 for ($i = 0; $i < 9; ++$i) {
515 genNumCell($areport[$key]['.age'][$i], $cnum++);
518 else if (!empty($arr_titles[$value])) {
519 foreach ($arr_titles[$value] as $title => $dummy) {
520 genNumCell($areport[$key][$value][$title], $cnum++);
525 // Write the Total column data.
526 if ($form_output != 3) {
527 $atotals[$cnum] += $totalsvcs;
528 genAnyCell($totalsvcs, true, 'dehead');
531 genEndRow();
532 } // end foreach
534 if ($form_output != 3) {
535 // Generate the line of totals.
536 genStartRow("bgcolor='#dddddd'");
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')
542 genHeadCell(array(xl('Totals'), ''));
543 } else {
544 genHeadCell(xl('Totals'));
547 for ($cnum = 0; $cnum < count($atotals); ++$cnum) {
548 genHeadCell($atotals[$cnum], true);
550 genEndRow();
551 // End of table.
552 echo "</table>\n";
555 } // end of if refresh or export
557 if ($form_output != 3) {
559 </form>
560 </center>
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>
570 </body>
571 </html>
572 <?php
573 } // end not export