3 * This report cross-references appointments with encounters.
4 * For a given date, show a line for each appointment with the
5 * matching encounter, and also for each encounter that has no
6 * matching appointment. This helps to catch these errors:
8 * * Appointments with no encounter
9 * * Encounters with no appointment
10 * * Codes not justified
11 * * Codes not authorized
12 * * Procedure codes without a fee
13 * * Fees assigned to diagnoses (instead of procedures)
14 * * Encounters not billed
16 * For decent performance the following indexes are highly recommended:
17 * openemr_postcalendar_events.pc_eventDate
19 * billing.pid_encounter
22 * @link http://www.open-emr.org
23 * @author Rod Roark <rod@sunsetsystems.com>
24 * @author Brady Miller <brady.g.miller@gmail.com>
25 * @copyright Copyright (c) 2005-2016 Rod Roark <rod@sunsetsystems.com>
26 * @copyright Copyright (c) 2017 Brady Miller <brady.g.miller@gmail.com>
27 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
31 require_once("../globals.php");
32 require_once("$srcdir/patient.inc");
33 require_once("../../custom/code_types.inc.php");
34 require_once("$srcdir/billing.inc");
36 use OpenEMR\Core\Header
;
37 use OpenEMR\Services\FacilityService
;
39 $facilityService = new FacilityService();
42 $alertmsg = ''; // not used yet but maybe later
43 $grand_total_charges = 0;
44 $grand_total_copays = 0;
45 $grand_total_encounters = 0;
47 function postError($msg)
54 $errmsg .= text($msg);
57 function bucks($amount)
60 return oeFormatMoney($amount);
64 function endDoctor(&$docrow)
66 global $grand_total_charges, $grand_total_copays, $grand_total_encounters;
67 if (!$docrow['docname']) {
71 echo " <tr class='report_totals'>\n";
72 echo " <td colspan='5'>\n";
73 echo " " . xlt('Totals for') . ' ' . text($docrow['docname']) . "\n";
75 echo " <td align='right'>\n";
76 echo " " . text($docrow['encounters']) . " \n";
78 echo " <td align='right'>\n";
80 echo text(bucks($docrow['charges']));
83 echo " <td align='right'>\n";
85 echo text(bucks($docrow['copays']));
88 echo " <td colspan='2'>\n";
93 $grand_total_charges +
= $docrow['charges'];
94 $grand_total_copays +
= $docrow['copays'];
95 $grand_total_encounters +
= $docrow['encounters'];
97 $docrow['charges'] = 0;
98 $docrow['copays'] = 0;
99 $docrow['encounters'] = 0;
102 $form_facility = isset($_POST['form_facility']) ?
$_POST['form_facility'] : '';
103 $form_from_date = (isset($_POST['form_from_date'])) ?
DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
104 $form_to_date = (isset($_POST['form_to_date'])) ?
DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
105 if ($_POST['form_refresh']) {
106 // MySQL doesn't grok full outer joins so we do it the hard way.
108 $sqlBindArray = array();
111 "e.pc_eventDate, e.pc_startTime, " .
112 "fe.encounter, fe.date AS encdate, " .
114 "p.fname, p.lname, p.pid, p.pubpid, " .
115 "CONCAT( u.lname, ', ', u.fname ) AS docname " .
116 "FROM openemr_postcalendar_events AS e " .
117 "LEFT OUTER JOIN form_encounter AS fe " .
118 "ON fe.date = e.pc_eventDate AND fe.pid = e.pc_pid " .
119 "LEFT OUTER JOIN forms AS f ON f.pid = fe.pid AND f.encounter = fe.encounter AND f.formdir = 'newpatient' " .
120 "LEFT OUTER JOIN patient_data AS p ON p.pid = e.pc_pid " .
121 // "LEFT OUTER JOIN users AS u ON BINARY u.username = BINARY f.user WHERE ";
122 "LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE ";
124 $query .= "e.pc_eventDate >= ? AND e.pc_eventDate <= ? ";
125 array_push($sqlBindArray, $form_from_date, $form_to_date);
127 $query .= "e.pc_eventDate = ? ";
128 array_push($sqlBindArray, $form_from_date);
131 if ($form_facility !== '') {
132 $query .= "AND e.pc_facility = ? ";
133 array_push($sqlBindArray, $form_facility);
136 // $query .= "AND ( e.pc_catid = 5 OR e.pc_catid = 9 OR e.pc_catid = 10 ) " .
137 $query .= "AND e.pc_pid != '' AND e.pc_apptstatus != ? " .
140 "e.pc_eventDate, e.pc_startTime, " .
141 "fe.encounter, fe.date AS encdate, " .
143 "p.fname, p.lname, p.pid, p.pubpid, " .
144 "CONCAT( u.lname, ', ', u.fname ) AS docname " .
145 "FROM form_encounter AS fe " .
146 "LEFT OUTER JOIN openemr_postcalendar_events AS e " .
147 "ON fe.date = e.pc_eventDate AND fe.pid = e.pc_pid AND " .
148 // "( e.pc_catid = 5 OR e.pc_catid = 9 OR e.pc_catid = 10 ) " .
149 "e.pc_pid != '' AND e.pc_apptstatus != ? " .
150 "LEFT OUTER JOIN forms AS f ON f.pid = fe.pid AND f.encounter = fe.encounter AND f.formdir = 'newpatient' " .
151 "LEFT OUTER JOIN patient_data AS p ON p.pid = fe.pid " .
152 // "LEFT OUTER JOIN users AS u ON BINARY u.username = BINARY f.user WHERE ";
153 "LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE ";
154 array_push($sqlBindArray, '?', '?');
156 // $query .= "LEFT(fe.date, 10) >= '$form_from_date' AND LEFT(fe.date, 10) <= '$form_to_date' ";
157 $query .= "fe.date >= ? AND fe.date <= ? ";
158 array_push($sqlBindArray, $form_from_date.' 00:00:00', $form_to_date.' 23:59:59');
160 // $query .= "LEFT(fe.date, 10) = '$form_from_date' ";
161 $query .= "fe.date >= ? AND fe.date <= ? ";
162 array_push($sqlBindArray, $form_from_date.' 00:00:00', $form_from_date.' 23:59:59');
165 if ($form_facility !== '') {
166 $query .= "AND fe.facility_id = ? ";
167 array_push($sqlBindArray, $form_facility);
170 $query .= ") ORDER BY docname, IFNULL(pc_eventDate, encdate), pc_startTime";
172 $res = sqlStatement($query, $sqlBindArray);
177 <title
><?php
echo xlt('Appointments and Encounters'); ?
></title
>
179 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
181 <style type
="text/css">
182 /* specifically include & exclude from printing */
188 #report_parameters_daterange {
192 #report_results table {
197 /* specifically exclude some from the screen */
199 #report_parameters_daterange {
206 <script LANGUAGE
="JavaScript">
207 $
(document
).ready(function() {
208 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
209 var win
= top
.printLogSetup ? top
: opener
.top
;
210 win
.printLogSetup(document
.getElementById('printbutton'));
212 $
('.datepicker').datetimepicker({
213 <?php
$datetimepicker_timepicker = false; ?
>
214 <?php
$datetimepicker_showseconds = false; ?
>
215 <?php
$datetimepicker_formatInput = true; ?
>
216 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
217 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
223 <body
class="body_top">
225 <span
class='title'><?php
echo xlt('Report'); ?
> - <?php
echo xlt('Appointments and Encounters'); ?
></span
>
227 <div id
="report_parameters_daterange">
228 <?php
echo text(oeFormatShortDate($form_from_date)) ." " . xlt('to') . " ". text(oeFormatShortDate($form_to_date)); ?
>
231 <form method
='post' id
='theform' action
='appt_encounter_report.php' onsubmit
='return top.restoreSession()'>
233 <div id
="report_parameters">
238 <div style
='float:left'>
242 <td
class='control-label'>
243 <?php
echo xlt('Facility'); ?
>:
247 // Build a drop-down list of facilities.
249 $fres = $facilityService->getAll();
250 echo " <select name='form_facility' class='form-control'>\n";
251 echo " <option value=''>-- " . xlt('All Facilities') . " --\n";
252 foreach ($fres as $frow) {
253 $facid = $frow['id'];
254 echo " <option value='" . attr($facid) . "'";
255 if ($facid == $form_facility) {
258 echo ">" . text($frow['name']) . "\n";
261 echo " <option value='0'";
262 if ($form_facility === '0') {
266 echo ">-- " . xlt('Unspecified') . " --\n";
270 <td
class='control-label'>
271 <?php
echo xlt('DOS'); ?
>:
274 <input type
='text' class='datepicker form-control' name
='form_from_date' id
="form_from_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_from_date)); ?>' >
276 <td
class='control-label'>
277 <?php
echo xlt('To'); ?
>:
280 <input type
='text' class='datepicker form-control' name
='form_to_date' id
="form_to_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_to_date)); ?>' >
286 <div
class="checkbox">
287 <label
><input type
='checkbox' name
='form_details'
288 value
='1'<?php
echo ($_POST['form_details']) ?
" checked" : ""; ?
>><?php
echo xlt('Details') ?
></label
>
297 <td align
='left' valign
='middle' height
="100%">
298 <table style
='border-left:1px solid; width:100%; height:100%' >
301 <div
class="text-center">
302 <div
class="btn-group" role
="group">
303 <a href
='#' class='btn btn-default btn-save' onclick
='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
304 <?php
echo xlt('Submit'); ?
>
306 <?php
if ($_POST['form_refresh']) { ?
>
307 <a href
='#' class='btn btn-default btn-print' id
='printbutton'>
308 <?php
echo xlt('Print'); ?
>
320 </div
> <!-- end apptenc_report_parameters
-->
323 if ($_POST['form_refresh']) {
325 <div id
="report_results">
326 <table id
='mymaintable'>
329 <th
>  
;<?php
echo xlt('Practitioner'); ?
> </th
>
330 <th
>  
;<?php
echo xlt('Date/Appt'); ?
> </th
>
331 <th
>  
;<?php
echo xlt('Patient'); ?
> </th
>
332 <th
>  
;<?php
echo xlt('ID'); ?
> </th
>
333 <th align
='right'> <?php
echo xlt('Chart'); ?
> 
; </th
>
334 <th align
='right'> <?php
echo xlt('Encounter'); ?
> 
; </th
>
335 <th align
='right'> <?php
echo xlt('Charges'); ?
> 
; </th
>
336 <th align
='right'> <?php
echo xlt('Copays'); ?
> 
; </th
>
337 <th
> <?php
echo xlt('Billed'); ?
> </th
>
338 <th
>  
;<?php
echo xlt('Error'); ?
> </th
>
343 $docrow = array('docname' => '', 'charges' => 0, 'copays' => 0, 'encounters' => 0);
345 while ($row = sqlFetchArray($res)) {
346 $patient_id = $row['pid'];
347 $encounter = $row['encounter'];
348 $docname = $row['docname'] ?
$row['docname'] : xl('Unknown');
350 if ($docname != $docrow['docname']) {
358 $gcac_related_visit = false;
360 // Scan the billing items for status and fee total.
362 $query = "SELECT code_type, code, modifier, authorized, billed, fee, justify " .
363 "FROM billing WHERE " .
364 "pid = ? AND encounter = ? AND activity = 1";
365 $bres = sqlStatement($query, array($patient_id, $encounter));
367 while ($brow = sqlFetchArray($bres)) {
368 $code_type = $brow['code_type'];
369 if ($code_types[$code_type]['fee'] && !$brow['billed']) {
373 if (!$GLOBALS['simplified_demographics'] && !$brow['authorized']) {
374 postError(xl('Needs Auth'));
377 if ($code_types[$code_type]['just']) {
378 if (! $brow['justify']) {
379 postError(xl('Needs Justify'));
383 if ($code_types[$code_type]['fee']) {
384 $charges +
= $brow['fee'];
385 if ($brow['fee'] == 0 && !$GLOBALS['ippf_specific']) {
386 postError(xl('Missing Fee'));
389 if ($brow['fee'] != 0) {
390 postError(xl('Fee is not allowed'));
394 // Custom logic for IPPF to determine if a GCAC issue applies.
395 if ($GLOBALS['ippf_specific']) {
396 if (!empty($code_types[$code_type]['fee'])) {
397 $sqlBindArray = array();
398 $query = "SELECT related_code FROM codes WHERE code_type = ? AND code = ? AND ";
399 array_push($sqlBindArray, $code_types[$code_type]['id'], $brow['code']);
400 if ($brow['modifier']) {
401 $query .= "modifier = ?";
402 array_push($sqlBindArray, $brow['modifier']);
404 $query .= "(modifier IS NULL OR modifier = '')";
407 $query .= " LIMIT 1";
408 $tmp = sqlQuery($query, $sqlBindArray);
409 $relcodes = explode(';', $tmp['related_code']);
410 foreach ($relcodes as $codestring) {
411 if ($codestring === '') {
415 list($codetype, $code) = explode(':', $codestring);
416 if ($codetype !== 'IPPF') {
420 if (preg_match('/^25222/', $code)) {
421 $gcac_related_visit = true;
428 $copays -= getPatientCopay($patient_id, $encounter);
430 // The following is removed, perhaps temporarily, because gcac reporting
431 // no longer depends on gcac issues. -- Rod 2009-08-11
432 /******************************************************************
433 // More custom code for IPPF. Generates an error message if a
434 // GCAC issue is required but is not linked to this visit.
435 if (!$errmsg && $gcac_related_visit) {
436 $grow = sqlQuery("SELECT l.id, l.title, l.begdate, ie.pid " .
438 "LEFT JOIN issue_encounter AS ie ON ie.pid = l.pid AND " .
439 "ie.encounter = '$encounter' AND ie.list_id = l.id " .
440 "WHERE l.pid = '$patient_id' AND " .
441 "l.activity = 1 AND l.type = 'ippf_gcac' " .
442 "ORDER BY ie.pid DESC, l.begdate DESC LIMIT 1");
443 // Note that reverse-ordering by ie.pid is a trick for sorting
444 // issues linked to the encounter (non-null values) first.
445 if (empty($grow['pid'])) { // if there is no linked GCAC issue
446 if (empty($grow)) { // no GCAC issue exists
447 $errmsg = "GCAC issue does not exist";
449 else { // there is one but none is linked
450 $errmsg = "GCAC issue is not linked";
454 ******************************************************************/
455 if ($gcac_related_visit) {
456 $grow = sqlQuery("SELECT COUNT(*) AS count FROM forms " .
457 "WHERE pid = ? AND encounter = ? AND " .
458 "deleted = 0 AND formdir = 'LBFgcac'", array($patient_id, $encounter));
459 if (empty($grow['count'])) { // if there is no gcac form
460 postError(xl('GCAC visit form is missing'));
463 /*****************************************************************/
466 postError($GLOBALS['simplified_demographics'] ?
467 xl('Not checked out') : xl('Not billed'));
471 postError(xl('No visit'));
478 $docrow['charges'] +
= $charges;
479 $docrow['copays'] +
= $copays;
481 ++
$docrow['encounters'];
484 if ($_POST['form_details']) {
488  
;<?php
echo ($docname == $docrow['docname']) ?
"" : text($docname); ?
>
492 /*****************************************************************
494 echo $row['pc_eventDate'] . '<br>';
495 echo substr($row['pc_startTime'], 0, 5);
497 *****************************************************************/
498 if (empty($row['pc_eventDate'])) {
499 echo text(oeFormatShortDate(substr($row['encdate'], 0, 10)));
501 echo text(oeFormatShortDate($row['pc_eventDate'])) . ' ' . text(substr($row['pc_startTime'], 0, 5));
506  
;<?php
echo text($row['fname']) . " " . text($row['lname']); ?
>
509  
;<?php
echo text($row['pubpid']); ?
>
512 <?php
echo text($row['pid']); ?
> 
;
515 <?php
echo text($encounter); ?
> 
;
518 <?php
echo text(bucks($charges)); ?
> 
;
521 <?php
echo text(bucks($copays)); ?
> 
;
524 <?php
echo text($billed); ?
>
526 <td style
='color:#cc0000'>
527 <?php
echo $errmsg; ?
> 
;
531 } // end of details line
533 $docrow['docname'] = $docname;
538 echo " <tr class='report_totals'>\n";
539 echo " <td colspan='5'>\n";
540 echo " " . xlt('Grand Totals') . "\n";
542 echo " <td align='right'>\n";
543 echo " " . text($grand_total_encounters) . " \n";
545 echo " <td align='right'>\n";
547 echo text(bucks($grand_total_charges));
550 echo " <td align='right'>\n";
552 echo text(bucks($grand_total_copays));
555 echo " <td colspan='2'>\n";
563 </div
> <!-- end the apptenc_report_results
-->
566 <?php
echo xlt('Please input search criteria above, and click Submit to view results.'); ?
>
570 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
574 <?php
if ($alertmsg) {
575 echo " alert('$alertmsg');\n";