Merge branch 'master' of git://github.com/openemr/openemr
[openemr.git] / interface / reports / appt_encounter_report.php
blobed06fd24200d543cfa5aa467ded1cd6f0d2d4fa4
1 <?php
2 // Copyright (C) 2005-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 report cross-references appointments with encounters.
10 // For a given date, show a line for each appointment with the
11 // matching encounter, and also for each encounter that has no
12 // matching appointment. This helps to catch these errors:
14 // * Appointments with no encounter
15 // * Encounters with no appointment
16 // * Codes not justified
17 // * Codes not authorized
18 // * Procedure codes without a fee
19 // * Fees assigned to diagnoses (instead of procedures)
20 // * Encounters not billed
22 // For decent performance the following indexes are highly recommended:
23 // openemr_postcalendar_events.pc_eventDate
24 // forms.encounter
25 // billing.pid_encounter
27 require_once("../globals.php");
28 require_once("$srcdir/patient.inc");
29 require_once("$srcdir/formatting.inc.php");
30 require_once("../../custom/code_types.inc.php");
32 $errmsg = "";
33 $alertmsg = ''; // not used yet but maybe later
34 $grand_total_charges = 0;
35 $grand_total_copays = 0;
36 $grand_total_encounters = 0;
38 function postError($msg) {
39 global $errmsg;
40 if ($errmsg) $errmsg .= '<br />';
41 $errmsg .= $msg;
44 function bucks($amount) {
45 if ($amount) echo oeFormatMoney($amount);
48 function endDoctor(&$docrow) {
49 global $grand_total_charges, $grand_total_copays, $grand_total_encounters;
50 if (!$docrow['docname']) return;
52 echo " <tr class='report_totals'>\n";
53 echo " <td colspan='5'>\n";
54 echo " &nbsp;" . xl('Totals for','','',' ') . $docrow['docname'] . "\n";
55 echo " </td>\n";
56 echo " <td align='right'>\n";
57 echo " &nbsp;" . $docrow['encounters'] . "&nbsp;\n";
58 echo " </td>\n";
59 echo " <td align='right'>\n";
60 echo " &nbsp;"; bucks($docrow['charges']); echo "&nbsp;\n";
61 echo " </td>\n";
62 echo " <td align='right'>\n";
63 echo " &nbsp;"; bucks($docrow['copays']); echo "&nbsp;\n";
64 echo " </td>\n";
65 echo " <td colspan='2'>\n";
66 echo " &nbsp;\n";
67 echo " </td>\n";
68 echo " </tr>\n";
70 $grand_total_charges += $docrow['charges'];
71 $grand_total_copays += $docrow['copays'];
72 $grand_total_encounters += $docrow['encounters'];
74 $docrow['charges'] = 0;
75 $docrow['copays'] = 0;
76 $docrow['encounters'] = 0;
79 $form_facility = isset($_POST['form_facility']) ? $_POST['form_facility'] : '';
80 $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
81 $form_to_date = fixDate($_POST['form_to_date'], date('Y-m-d'));
82 if ($_POST['form_refresh']) {
83 $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
84 $form_to_date = fixDate($_POST['form_to_date'], "");
86 // MySQL doesn't grok full outer joins so we do it the hard way.
88 $query = "( " .
89 "SELECT " .
90 "e.pc_eventDate, e.pc_startTime, " .
91 "fe.encounter, fe.date AS encdate, " .
92 "f.authorized, " .
93 "p.fname, p.lname, p.pid, p.pubpid, " .
94 "CONCAT( u.lname, ', ', u.fname ) AS docname " .
95 "FROM openemr_postcalendar_events AS e " .
96 "LEFT OUTER JOIN form_encounter AS fe " .
97 "ON LEFT(fe.date, 10) = e.pc_eventDate AND fe.pid = e.pc_pid " .
98 "LEFT OUTER JOIN forms AS f ON f.encounter = fe.encounter AND f.formdir = 'newpatient' " .
99 "LEFT OUTER JOIN patient_data AS p ON p.pid = e.pc_pid " .
100 // "LEFT OUTER JOIN users AS u ON BINARY u.username = BINARY f.user WHERE ";
101 "LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE ";
102 if ($form_to_date) {
103 $query .= "e.pc_eventDate >= '$form_from_date' AND e.pc_eventDate <= '$form_to_date' ";
104 } else {
105 $query .= "e.pc_eventDate = '$form_from_date' ";
107 if ($form_facility !== '') {
108 $query .= "AND e.pc_facility = '$form_facility' ";
110 // $query .= "AND ( e.pc_catid = 5 OR e.pc_catid = 9 OR e.pc_catid = 10 ) " .
111 $query .= "AND e.pc_pid != '' AND e.pc_apptstatus != '?' " .
112 ") UNION ( " .
113 "SELECT " .
114 "e.pc_eventDate, e.pc_startTime, " .
115 "fe.encounter, fe.date AS encdate, " .
116 "f.authorized, " .
117 "p.fname, p.lname, p.pid, p.pubpid, " .
118 "CONCAT( u.lname, ', ', u.fname ) AS docname " .
119 "FROM form_encounter AS fe " .
120 "LEFT OUTER JOIN openemr_postcalendar_events AS e " .
121 "ON LEFT(fe.date, 10) = e.pc_eventDate AND fe.pid = e.pc_pid AND " .
122 // "( e.pc_catid = 5 OR e.pc_catid = 9 OR e.pc_catid = 10 ) " .
123 "e.pc_pid != '' AND e.pc_apptstatus != '?' " .
124 "LEFT OUTER JOIN forms AS f ON f.encounter = fe.encounter AND f.formdir = 'newpatient' " .
125 "LEFT OUTER JOIN patient_data AS p ON p.pid = fe.pid " .
126 // "LEFT OUTER JOIN users AS u ON BINARY u.username = BINARY f.user WHERE ";
127 "LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE ";
128 if ($form_to_date) {
129 // $query .= "LEFT(fe.date, 10) >= '$form_from_date' AND LEFT(fe.date, 10) <= '$form_to_date' ";
130 $query .= "fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_to_date 23:59:59' ";
131 } else {
132 // $query .= "LEFT(fe.date, 10) = '$form_from_date' ";
133 $query .= "fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_from_date 23:59:59' ";
135 if ($form_facility !== '') {
136 $query .= "AND fe.facility_id = '$form_facility' ";
138 $query .= ") ORDER BY docname, pc_eventDate, pc_startTime";
140 $res = sqlStatement($query);
143 <html>
144 <head>
145 <?php html_header_show();?>
146 <link rel="stylesheet" href="<?php echo $css_header;?>" type="text/css">
147 <style type="text/css">
149 /* specifically include & exclude from printing */
150 @media print {
151 #report_parameters {
152 visibility: hidden;
153 display: none;
155 #report_parameters_daterange {
156 visibility: visible;
157 display: inline;
159 #report_results table {
160 margin-top: 0px;
164 /* specifically exclude some from the screen */
165 @media screen {
166 #report_parameters_daterange {
167 visibility: hidden;
168 display: none;
172 </style>
173 <title><?php xl('Appointments and Encounters','e'); ?></title>
174 </head>
176 <body class="body_top">
178 <span class='title'><?php xl('Report','e'); ?> - <?php xl('Appointments and Encounters','e'); ?></span>
180 <div id="report_parameters_daterange">
181 <?php echo date("d F Y", strtotime($form_from_date)) ." &nbsp; to &nbsp; ". date("d F Y", strtotime($form_to_date)); ?>
182 </div>
184 <form method='post' id='theform' action='appt_encounter_report.php'>
186 <div id="report_parameters">
188 <table>
189 <tr>
190 <td width='630px'>
191 <div style='float:left'>
193 <table class='text'>
194 <tr>
195 <td class='label'>
196 <?php xl('Facility','e'); ?>:
197 </td>
198 <td>
199 <?php
200 // Build a drop-down list of facilities.
202 $query = "SELECT id, name FROM facility ORDER BY name";
203 $fres = sqlStatement($query);
204 echo " <select name='form_facility'>\n";
205 echo " <option value=''>-- " . xl('All Facilities', 'e') . " --\n";
206 while ($frow = sqlFetchArray($fres)) {
207 $facid = $frow['id'];
208 echo " <option value='$facid'";
209 if ($facid == $form_facility) echo " selected";
210 echo ">" . htmlspecialchars($frow['name']) . "\n";
212 echo " <option value='0'";
213 if ($form_facility === '0') echo " selected";
214 echo ">-- " . xl('Unspecified') . " --\n";
215 echo " </select>\n";
217 </td>
218 <td class='label'>
219 <?php xl('DOS','e'); ?>:
220 </td>
221 <td>
222 <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $form_from_date; ?>'
223 title='Date of appointments mm/dd/yyyy' >
224 <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
225 id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
226 title='<?php xl('Click here to choose a date','e'); ?>'>
227 </td>
228 <td class='label'>
229 <?php xl('To','e'); ?>:
230 </td>
231 <td>
232 <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_date; ?>'
233 title='Optional end date mm/dd/yyyy' >
234 <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
235 id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
236 title='<?php xl('Click here to choose a date','e'); ?>'>
237 </td>
238 </tr>
239 <tr>
240 <td>&nbsp;</td>
241 <td>
242 <input type='checkbox' name='form_details'
243 value='1'<?php if ($_POST['form_details']) echo " checked"; ?>><?php xl('Details','e') ?>
244 </td>
245 </tr>
246 </table>
248 </div>
250 </td>
251 <td align='left' valign='middle' height="100%">
252 <table style='border-left:1px solid; width:100%; height:100%' >
253 <tr>
254 <td>
255 <div style='margin-left:15px'>
256 <a href='#' class='css_button' onclick='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
257 <span>
258 <?php xl('Submit','e'); ?>
259 </span>
260 </a>
262 <?php if ($_POST['form_refresh']) { ?>
263 <a href='#' class='css_button' onclick='window.print()'>
264 <span>
265 <?php xl('Print','e'); ?>
266 </span>
267 </a>
268 <?php } ?>
269 </div>
270 </td>
271 </tr>
272 </table>
273 </td>
274 </tr>
275 </table>
277 </div> <!-- end apptenc_report_parameters -->
279 <?php
280 if ($_POST['form_refresh'] ) {
282 <div id="report_results">
283 <table>
285 <thead>
286 <th> &nbsp;<?php xl('Practitioner','e'); ?> </th>
287 <th> &nbsp;<?php xl('Date/Appt','e'); ?> </th>
288 <th> &nbsp;<?php xl('Patient','e'); ?> </th>
289 <th> &nbsp;<?php xl('ID','e'); ?> </th>
290 <th align='right'> <?php xl('Chart','e'); ?>&nbsp; </th>
291 <th align='right'> <?php xl('Encounter','e'); ?>&nbsp; </th>
292 <th align='right'> <?php xl('Charges','e'); ?>&nbsp; </th>
293 <th align='right'> <?php xl('Copays','e'); ?>&nbsp; </th>
294 <th> <?php xl('Billed','e'); ?> </th>
295 <th> &nbsp;<?php xl('Error','e'); ?> </th>
296 </thead>
297 <tbody>
298 <?php
299 if ($res) {
300 $docrow = array('docname' => '', 'charges' => 0, 'copays' => 0, 'encounters' => 0);
302 while ($row = sqlFetchArray($res)) {
303 $patient_id = $row['pid'];
304 $encounter = $row['encounter'];
305 $docname = $row['docname'] ? $row['docname'] : xl('Unknown');
307 if ($docname != $docrow['docname']) {
308 endDoctor($docrow);
311 $errmsg = "";
312 $billed = "Y";
313 $charges = 0;
314 $copays = 0;
315 $gcac_related_visit = false;
317 // Scan the billing items for status and fee total.
319 $query = "SELECT code_type, code, modifier, authorized, billed, fee, justify " .
320 "FROM billing WHERE " .
321 "pid = '$patient_id' AND encounter = '$encounter' AND activity = 1";
322 $bres = sqlStatement($query);
324 while ($brow = sqlFetchArray($bres)) {
325 $code_type = $brow['code_type'];
326 if ($code_types[$code_type]['fee'] && !$brow['billed'])
327 $billed = "";
328 if (!$GLOBALS['simplified_demographics'] && !$brow['authorized'])
329 postError(xl('Needs Auth'));
330 if ($code_types[$code_type]['just']) {
331 if (! $brow['justify']) postError(xl('Needs Justify'));
333 if ($code_type == 'COPAY') {
334 $copays -= $brow['fee'];
335 if ($brow['fee'] >= 0) postError(xl('Copay not positive'));
336 } else if ($code_types[$code_type]['fee']) {
337 $charges += $brow['fee'];
338 if ($brow['fee'] == 0 && !$GLOBALS['ippf_specific']) postError(xl('Missing Fee'));
339 } else {
340 if ($brow['fee'] != 0) postError(xl('Fee is not allowed'));
343 // Custom logic for IPPF to determine if a GCAC issue applies.
344 if ($GLOBALS['ippf_specific']) {
345 if (!empty($code_types[$code_type]['fee'])) {
346 $query = "SELECT related_code FROM codes WHERE code_type = '" .
347 $code_types[$code_type]['id'] . "' AND " .
348 "code = '" . $brow['code'] . "' AND ";
349 if ($brow['modifier']) {
350 $query .= "modifier = '" . $brow['modifier'] . "'";
351 } else {
352 $query .= "(modifier IS NULL OR modifier = '')";
354 $query .= " LIMIT 1";
355 $tmp = sqlQuery($query);
356 $relcodes = explode(';', $tmp['related_code']);
357 foreach ($relcodes as $codestring) {
358 if ($codestring === '') continue;
359 list($codetype, $code) = explode(':', $codestring);
360 if ($codetype !== 'IPPF') continue;
361 if (preg_match('/^25222/', $code)) $gcac_related_visit = true;
364 } // End IPPF stuff
366 } // end while
368 // The following is removed, perhaps temporarily, because gcac reporting
369 // no longer depends on gcac issues. -- Rod 2009-08-11
370 /******************************************************************
371 // More custom code for IPPF. Generates an error message if a
372 // GCAC issue is required but is not linked to this visit.
373 if (!$errmsg && $gcac_related_visit) {
374 $grow = sqlQuery("SELECT l.id, l.title, l.begdate, ie.pid " .
375 "FROM lists AS l " .
376 "LEFT JOIN issue_encounter AS ie ON ie.pid = l.pid AND " .
377 "ie.encounter = '$encounter' AND ie.list_id = l.id " .
378 "WHERE l.pid = '$patient_id' AND " .
379 "l.activity = 1 AND l.type = 'ippf_gcac' " .
380 "ORDER BY ie.pid DESC, l.begdate DESC LIMIT 1");
381 // Note that reverse-ordering by ie.pid is a trick for sorting
382 // issues linked to the encounter (non-null values) first.
383 if (empty($grow['pid'])) { // if there is no linked GCAC issue
384 if (empty($grow)) { // no GCAC issue exists
385 $errmsg = "GCAC issue does not exist";
387 else { // there is one but none is linked
388 $errmsg = "GCAC issue is not linked";
392 ******************************************************************/
393 if ($gcac_related_visit) {
394 $grow = sqlQuery("SELECT COUNT(*) AS count FROM forms " .
395 "WHERE pid = '$patient_id' AND encounter = '$encounter' AND " .
396 "deleted = 0 AND formdir = 'LBFgcac'");
397 if (empty($grow['count'])) { // if there is no gcac form
398 postError(xl('GCAC visit form is missing'));
400 } // end if
401 /*****************************************************************/
403 if (!$billed) postError($GLOBALS['simplified_demographics'] ?
404 xl('Not checked out') : xl('Not billed'));
405 if (!$encounter) postError(xl('No visit'));
407 if (! $charges) $billed = "";
409 $docrow['charges'] += $charges;
410 $docrow['copays'] += $copays;
411 if ($encounter) ++$docrow['encounters'];
413 if ($_POST['form_details']) {
415 <tr>
416 <td>
417 &nbsp;<?php echo ($docname == $docrow['docname']) ? "" : $docname ?>
418 </td>
419 <td>
420 &nbsp;<?php
421 /*****************************************************************
422 if ($form_to_date) {
423 echo $row['pc_eventDate'] . '<br>';
424 echo substr($row['pc_startTime'], 0, 5);
426 *****************************************************************/
427 if (empty($row['pc_eventDate'])) {
428 echo oeFormatShortDate(substr($row['encdate'], 0, 10));
430 else {
431 echo oeFormatShortDate($row['pc_eventDate']) . ' ' . substr($row['pc_startTime'], 0, 5);
434 </td>
435 <td>
436 &nbsp;<?php echo $row['fname'] . " " . $row['lname'] ?>
437 </td>
438 <td>
439 &nbsp;<?php echo $row['pubpid'] ?>
440 </td>
441 <td align='right'>
442 <?php echo $row['pid'] ?>&nbsp;
443 </td>
444 <td align='right'>
445 <?php echo $encounter ?>&nbsp;
446 </td>
447 <td align='right'>
448 <?php bucks($charges) ?>&nbsp;
449 </td>
450 <td align='right'>
451 <?php bucks($copays) ?>&nbsp;
452 </td>
453 <td>
454 <?php echo $billed ?>
455 </td>
456 <td style='color:#cc0000'>
457 <?php echo $errmsg; ?>&nbsp;
458 </td>
459 </tr>
460 <?php
461 } // end of details line
463 $docrow['docname'] = $docname;
464 } // end of row
466 endDoctor($docrow);
468 echo " <tr class='report_totals'>\n";
469 echo " <td colspan='5'>\n";
470 echo " &nbsp;" . xl('Grand Totals') . "\n";
471 echo " </td>\n";
472 echo " <td align='right'>\n";
473 echo " &nbsp;" . $grand_total_encounters . "&nbsp;\n";
474 echo " </td>\n";
475 echo " <td align='right'>\n";
476 echo " &nbsp;"; bucks($grand_total_charges); echo "&nbsp;\n";
477 echo " </td>\n";
478 echo " <td align='right'>\n";
479 echo " &nbsp;"; bucks($grand_total_copays); echo "&nbsp;\n";
480 echo " </td>\n";
481 echo " <td colspan='2'>\n";
482 echo " &nbsp;\n";
483 echo " </td>\n";
484 echo " </tr>\n";
488 </tbody>
489 </table>
490 </div> <!-- end the apptenc_report_results -->
491 <?php } else { ?>
492 <div class='text'>
493 <?php echo xl('Please input search criteria above, and click Submit to view results.', 'e' ); ?>
494 </div>
495 <?php } ?>
497 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
499 </form>
500 <script>
501 <?php if ($alertmsg) { echo " alert('$alertmsg');\n"; } ?>
502 </script>
503 </body>
505 <!-- stuff for the popup calendar -->
506 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
507 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
508 <?php include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?>
509 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
510 <script type="text/javascript" src="../../library/js/jquery.1.3.2.js"></script>
512 <script language="Javascript">
513 Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
514 Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
515 </script>
517 </html>