4 * This report cross-references appointments with encounters.
5 * For a given date, show a line for each appointment with the
6 * matching encounter, and also for each encounter that has no
7 * matching appointment. This helps to catch these errors:
9 * * Appointments with no encounter
10 * * Encounters with no appointment
11 * * Codes not justified
12 * * Codes not authorized
13 * * Procedure codes without a fee
14 * * Fees assigned to diagnoses (instead of procedures)
15 * * Encounters not billed
17 * For decent performance the following indexes are highly recommended:
18 * openemr_postcalendar_events.pc_eventDate
20 * billing.pid_encounter
23 * @link http://www.open-emr.org
24 * @author Rod Roark <rod@sunsetsystems.com>
25 * @author Brady Miller <brady.g.miller@gmail.com>
26 * @copyright Copyright (c) 2005-2016 Rod Roark <rod@sunsetsystems.com>
27 * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
28 * @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");
35 use OpenEMR\Billing\BillingUtilities
;
36 use OpenEMR\Common\Csrf\CsrfUtils
;
37 use OpenEMR\Core\Header
;
38 use OpenEMR\Services\FacilityService
;
39 use OpenEMR\Common\Acl\AclMain
;
42 if (!CsrfUtils
::verifyCsrfToken($_POST["csrf_token_form"])) {
43 CsrfUtils
::csrfNotVerified();
47 if (!AclMain
::aclCheckCore('patients', 'appt') ||
!AclMain
::aclCheckCore('acct', 'rep_a')) {
48 die(xlt("Unauthorized access."));
51 $facilityService = new FacilityService();
54 $alertmsg = ''; // not used yet but maybe later
55 $grand_total_charges = 0;
56 $grand_total_copays = 0;
57 $grand_total_encounters = 0;
59 function postError($msg)
66 $errmsg .= text($msg);
69 function bucks($amount)
72 return oeFormatMoney($amount);
76 function endDoctor(&$docrow)
78 global $grand_total_charges, $grand_total_copays, $grand_total_encounters;
79 if (!$docrow['docname']) {
83 echo " <tr class='report_totals'>\n";
84 echo " <td colspan='5'>\n";
85 echo " " . xlt('Totals for') . ' ' . text($docrow['docname']) . "\n";
87 echo " <td align='right'>\n";
88 echo " " . text($docrow['encounters']) . " \n";
90 echo " <td align='right'>\n";
92 echo text(bucks($docrow['charges']));
95 echo " <td align='right'>\n";
97 echo text(bucks($docrow['copays']));
100 echo " <td colspan='2'>\n";
105 $grand_total_charges +
= $docrow['charges'];
106 $grand_total_copays +
= $docrow['copays'];
107 $grand_total_encounters +
= $docrow['encounters'];
109 $docrow['charges'] = 0;
110 $docrow['copays'] = 0;
111 $docrow['encounters'] = 0;
114 $form_facility = isset($_POST['form_facility']) ?
$_POST['form_facility'] : '';
115 $form_from_date = (isset($_POST['form_from_date'])) ?
DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
116 $form_to_date = (isset($_POST['form_to_date'])) ?
DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
117 if (!empty($_POST['form_refresh'])) {
118 // MySQL doesn't grok full outer joins so we do it the hard way.
120 $sqlBindArray = array();
123 "e.pc_eventDate, e.pc_startTime, " .
124 "fe.encounter, fe.date AS encdate, " .
126 "p.fname, p.lname, p.pid, p.pubpid, " .
127 "CONCAT( u.lname, ', ', u.fname ) AS docname " .
128 "FROM openemr_postcalendar_events AS e " .
129 "LEFT OUTER JOIN form_encounter AS fe " .
130 "ON fe.date = e.pc_eventDate AND fe.pid = e.pc_pid " .
131 "LEFT OUTER JOIN forms AS f ON f.pid = fe.pid AND f.encounter = fe.encounter AND f.formdir = 'newpatient' " .
132 "LEFT OUTER JOIN patient_data AS p ON p.pid = e.pc_pid " .
133 // "LEFT OUTER JOIN users AS u ON BINARY u.username = BINARY f.user WHERE ";
134 "LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE ";
136 $query .= "e.pc_eventDate >= ? AND e.pc_eventDate <= ? ";
137 array_push($sqlBindArray, $form_from_date, $form_to_date);
139 $query .= "e.pc_eventDate = ? ";
140 array_push($sqlBindArray, $form_from_date);
143 if ($form_facility !== '') {
144 $query .= "AND e.pc_facility = ? ";
145 array_push($sqlBindArray, $form_facility);
148 // $query .= "AND ( e.pc_catid = 5 OR e.pc_catid = 9 OR e.pc_catid = 10 ) " .
149 $query .= "AND e.pc_pid != '' AND e.pc_apptstatus != ? " .
152 "e.pc_eventDate, e.pc_startTime, " .
153 "fe.encounter, fe.date AS encdate, " .
155 "p.fname, p.lname, p.pid, p.pubpid, " .
156 "CONCAT( u.lname, ', ', u.fname ) AS docname " .
157 "FROM form_encounter AS fe " .
158 "LEFT OUTER JOIN openemr_postcalendar_events AS e " .
159 "ON fe.date = e.pc_eventDate AND fe.pid = e.pc_pid AND " .
160 // "( e.pc_catid = 5 OR e.pc_catid = 9 OR e.pc_catid = 10 ) " .
161 "e.pc_pid != '' AND e.pc_apptstatus != ? " .
162 "LEFT OUTER JOIN forms AS f ON f.pid = fe.pid AND f.encounter = fe.encounter AND f.formdir = 'newpatient' " .
163 "LEFT OUTER JOIN patient_data AS p ON p.pid = fe.pid " .
164 // "LEFT OUTER JOIN users AS u ON BINARY u.username = BINARY f.user WHERE ";
165 "LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE ";
166 array_push($sqlBindArray, '?', '?');
168 // $query .= "LEFT(fe.date, 10) >= '$form_from_date' AND LEFT(fe.date, 10) <= '$form_to_date' ";
169 $query .= "fe.date >= ? AND fe.date <= ? ";
170 array_push($sqlBindArray, $form_from_date . ' 00:00:00', $form_to_date . ' 23:59:59');
172 // $query .= "LEFT(fe.date, 10) = '$form_from_date' ";
173 $query .= "fe.date >= ? AND fe.date <= ? ";
174 array_push($sqlBindArray, $form_from_date . ' 00:00:00', $form_from_date . ' 23:59:59');
177 if ($form_facility !== '') {
178 $query .= "AND fe.facility_id = ? ";
179 array_push($sqlBindArray, $form_facility);
182 $query .= ") ORDER BY docname, IFNULL(pc_eventDate, encdate), pc_startTime";
184 $res = sqlStatement($query, $sqlBindArray);
189 <title
><?php
echo xlt('Appointments and Encounters'); ?
></title
>
191 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
194 /* specifically include & exclude from printing */
200 #report_parameters_daterange {
204 #report_results table {
209 /* specifically exclude some from the screen */
211 #report_parameters_daterange {
220 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
221 var win
= top
.printLogSetup ? top
: opener
.top
;
222 win
.printLogSetup(document
.getElementById('printbutton'));
224 $
('.datepicker').datetimepicker({
225 <?php
$datetimepicker_timepicker = false; ?
>
226 <?php
$datetimepicker_showseconds = false; ?
>
227 <?php
$datetimepicker_formatInput = true; ?
>
228 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
229 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
235 <body
class="body_top">
237 <span
class='title'><?php
echo xlt('Report'); ?
> - <?php
echo xlt('Appointments and Encounters'); ?
></span
>
239 <div id
="report_parameters_daterange">
240 <?php
echo text(oeFormatShortDate($form_from_date)) . " " . xlt('to{{Range}}') . " " . text(oeFormatShortDate($form_to_date)); ?
>
243 <form method
='post' id
='theform' action
='appt_encounter_report.php' onsubmit
='return top.restoreSession()'>
244 <input type
="hidden" name
="csrf_token_form" value
="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
246 <div id
="report_parameters">
251 <div style
='float:left'>
255 <td
class='col-form-label'>
256 <?php
echo xlt('Facility'); ?
>:
260 // Build a drop-down list of facilities.
262 $fres = $facilityService->getAllFacility();
263 echo " <select name='form_facility' class='form-control'>\n";
264 echo " <option value=''>-- " . xlt('All Facilities') . " --\n";
265 foreach ($fres as $frow) {
266 $facid = $frow['id'];
267 echo " <option value='" . attr($facid) . "'";
268 if ($facid == $form_facility) {
271 echo ">" . text($frow['name']) . "\n";
274 echo " <option value='0'";
275 if ($form_facility === '0') {
279 echo ">-- " . xlt('Unspecified') . " --\n";
283 <td
class='col-form-label'>
284 <?php
echo xlt('DOS'); ?
>:
287 <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)); ?>' >
289 <td
class='col-form-label'>
290 <?php
echo xlt('To{{Range}}'); ?
>:
293 <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)); ?>' >
299 <div
class="checkbox">
300 <label
><input type
='checkbox' name
='form_details'
301 value
='1'<?php
echo (!empty($_POST['form_details'])) ?
" checked" : ""; ?
>><?php
echo xlt('Details') ?
></label
>
310 <td
class='h-100' align
='left' valign
='middle'>
311 <table
class='w-100 h-100' style
='border-left:1px solid;'>
314 <div
class="text-center">
315 <div
class="btn-group" role
="group">
316 <a href
='#' class='btn btn-secondary btn-save' onclick
='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
317 <?php
echo xlt('Submit'); ?
>
319 <?php
if (!empty($_POST['form_refresh'])) { ?
>
320 <a href
='#' class='btn btn-secondary btn-print' id
='printbutton'>
321 <?php
echo xlt('Print'); ?
>
333 </div
> <!-- end apptenc_report_parameters
-->
336 if (!empty($_POST['form_refresh'])) {
338 <div id
="report_results">
339 <table
class='table' id
='mymaintable'>
341 <thead
class='thead-light'>
342 <th
>  
;<?php
echo xlt('Practitioner'); ?
> </th
>
343 <th
>  
;<?php
echo xlt('Date/Appt'); ?
> </th
>
344 <th
>  
;<?php
echo xlt('Patient'); ?
> </th
>
345 <th
>  
;<?php
echo xlt('ID'); ?
> </th
>
346 <th align
='right'> <?php
echo xlt('Chart'); ?
> 
; </th
>
347 <th align
='right'> <?php
echo xlt('Encounter'); ?
> 
; </th
>
348 <th align
='right'> <?php
echo xlt('Charges'); ?
> 
; </th
>
349 <th align
='right'> <?php
echo xlt('Copays'); ?
> 
; </th
>
350 <th
> <?php
echo xlt('Billed'); ?
> </th
>
351 <th
>  
;<?php
echo xlt('Error'); ?
> </th
>
356 $docrow = array('docname' => '', 'charges' => 0, 'copays' => 0, 'encounters' => 0);
358 while ($row = sqlFetchArray($res)) {
359 $patient_id = $row['pid'];
360 $encounter = $row['encounter'];
361 $docname = $row['docname'] ?
$row['docname'] : xl('Unknown');
363 if ($docname != $docrow['docname']) {
371 $gcac_related_visit = false;
373 // Scan the billing items for status and fee total.
375 $query = "SELECT code_type, code, modifier, authorized, billed, fee, justify " .
376 "FROM billing WHERE " .
377 "pid = ? AND encounter = ? AND activity = 1";
378 $bres = sqlStatement($query, array($patient_id, $encounter));
380 while ($brow = sqlFetchArray($bres)) {
381 $code_type = $brow['code_type'];
382 if ($code_types[$code_type]['fee'] && !$brow['billed']) {
386 if (!$GLOBALS['simplified_demographics'] && !$brow['authorized']) {
387 postError(xl('Needs Auth'));
390 if ($code_types[$code_type]['just']) {
391 if (! $brow['justify']) {
392 postError(xl('Needs Justify'));
396 if ($code_types[$code_type]['fee']) {
397 $charges +
= $brow['fee'];
398 if ($brow['fee'] == 0 && !$GLOBALS['ippf_specific']) {
399 postError(xl('Missing Fee'));
402 if ($brow['fee'] != 0) {
403 postError(xl('Fee is not allowed'));
407 // Custom logic for IPPF to determine if a GCAC issue applies.
408 if ($GLOBALS['ippf_specific']) {
409 if (!empty($code_types[$code_type]['fee'])) {
410 $sqlBindArray = array();
411 $query = "SELECT related_code FROM codes WHERE code_type = ? AND code = ? AND ";
412 array_push($sqlBindArray, $code_types[$code_type]['id'], $brow['code']);
413 if ($brow['modifier']) {
414 $query .= "modifier = ?";
415 array_push($sqlBindArray, $brow['modifier']);
417 $query .= "(modifier IS NULL OR modifier = '')";
420 $query .= " LIMIT 1";
421 $tmp = sqlQuery($query, $sqlBindArray);
422 $relcodes = explode(';', $tmp['related_code']);
423 foreach ($relcodes as $codestring) {
424 if ($codestring === '') {
428 list($codetype, $code) = explode(':', $codestring);
429 if ($codetype !== 'IPPF') {
433 if (preg_match('/^25222/', $code)) {
434 $gcac_related_visit = true;
441 $copays -= BillingUtilities
::getPatientCopay($patient_id, $encounter);
443 // The following is removed, perhaps temporarily, because gcac reporting
444 // no longer depends on gcac issues. -- Rod 2009-08-11
445 /******************************************************************
446 // More custom code for IPPF. Generates an error message if a
447 // GCAC issue is required but is not linked to this visit.
448 if (!$errmsg && $gcac_related_visit) {
449 $grow = sqlQuery("SELECT l.id, l.title, l.begdate, ie.pid " .
451 "LEFT JOIN issue_encounter AS ie ON ie.pid = l.pid AND " .
452 "ie.encounter = '$encounter' AND ie.list_id = l.id " .
453 "WHERE l.pid = '$patient_id' AND " .
454 "l.activity = 1 AND l.type = 'ippf_gcac' " .
455 "ORDER BY ie.pid DESC, l.begdate DESC LIMIT 1");
456 // Note that reverse-ordering by ie.pid is a trick for sorting
457 // issues linked to the encounter (non-null values) first.
458 if (empty($grow['pid'])) { // if there is no linked GCAC issue
459 if (empty($grow)) { // no GCAC issue exists
460 $errmsg = "GCAC issue does not exist";
462 else { // there is one but none is linked
463 $errmsg = "GCAC issue is not linked";
467 ******************************************************************/
468 if ($gcac_related_visit) {
469 $grow = sqlQuery("SELECT COUNT(*) AS count FROM forms " .
470 "WHERE pid = ? AND encounter = ? AND " .
471 "deleted = 0 AND formdir = 'LBFgcac'", array($patient_id, $encounter));
472 if (empty($grow['count'])) { // if there is no gcac form
473 postError(xl('GCAC visit form is missing'));
476 /*****************************************************************/
479 postError($GLOBALS['simplified_demographics'] ?
480 xl('Not checked out') : xl('Not billed'));
484 postError(xl('No visit'));
491 $docrow['charges'] +
= $charges;
492 $docrow['copays'] +
= $copays;
494 ++
$docrow['encounters'];
497 if (!empty($_POST['form_details'])) {
501  
;<?php
echo ($docname == $docrow['docname']) ?
"" : text($docname); ?
>
505 /*****************************************************************
507 echo $row['pc_eventDate'] . '<br />';
508 echo substr($row['pc_startTime'], 0, 5);
510 *****************************************************************/
511 if (empty($row['pc_eventDate'])) {
512 echo text(oeFormatShortDate(substr($row['encdate'], 0, 10)));
514 echo text(oeFormatShortDate($row['pc_eventDate'])) . ' ' . text(substr($row['pc_startTime'], 0, 5));
519  
;<?php
echo text($row['fname']) . " " . text($row['lname']); ?
>
522  
;<?php
echo text($row['pubpid']); ?
>
525 <?php
echo text($row['pid']); ?
> 
;
528 <?php
echo text($encounter); ?
> 
;
531 <?php
echo text(bucks($charges)); ?
> 
;
534 <?php
echo text(bucks($copays)); ?
> 
;
537 <?php
echo text($billed); ?
>
539 <td style
='color:#cc0000'>
540 <?php
echo $errmsg; ?
> 
;
544 } // end of details line
546 $docrow['docname'] = $docname;
551 echo " <tr class='report_totals'>\n";
552 echo " <td colspan='5'>\n";
553 echo " " . xlt('Grand Totals') . "\n";
555 echo " <td align='right'>\n";
556 echo " " . text($grand_total_encounters) . " \n";
558 echo " <td align='right'>\n";
560 echo text(bucks($grand_total_charges));
563 echo " <td align='right'>\n";
565 echo text(bucks($grand_total_copays));
568 echo " <td colspan='2'>\n";
576 </div
> <!-- end the apptenc_report_results
-->
579 <?php
echo xlt('Please input search criteria above, and click Submit to view results.'); ?
>
583 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
587 <?php
if ($alertmsg) {
588 echo " alert(" . js_escape($alertmsg) . ");\n";