interface/reports/appt_encounter_report.php

   1 <?php
   2 /**
   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:
   7  *
   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
  15  *
  16  * For decent performance the following indexes are highly recommended:
  17  *   openemr_postcalendar_events.pc_eventDate
  18  *   forms.encounter
  19  *   billing.pid_encounter
  20  *
  21  * @package   OpenEMR
  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
  28  */
  29
  30
  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");
  35
  36 use OpenEMR\Core\Header;
  37 use OpenEMR\Services\FacilityService;
  38
  39 $facilityService = new FacilityService();
  40
  41 $errmsg  = "";
  42 $alertmsg = ''; // not used yet but maybe later
  43 $grand_total_charges    = 0;
  44 $grand_total_copays     = 0;
  45 $grand_total_encounters = 0;
  46
  47 function postError($msg)
  48 {
  49     global $errmsg;
  50     if ($errmsg) {
  51         $errmsg .= '<br />';
  52     }
  53
  54     $errmsg .= text($msg);
  55 }
  56
  57 function bucks($amount)
  58 {
  59     if ($amount) {
  60         return oeFormatMoney($amount);
  61     }
  62 }
  63
  64 function endDoctor(&$docrow)
  65 {
  66     global $grand_total_charges, $grand_total_copays, $grand_total_encounters;
  67     if (!$docrow['docname']) {
  68         return;
  69     }
  70
  71     echo " <tr class='report_totals'>\n";
  72     echo "  <td colspan='5'>\n";
  73     echo "   &nbsp;" . xlt('Totals for') . ' ' . text($docrow['docname']) . "\n";
  74     echo "  </td>\n";
  75     echo "  <td align='right'>\n";
  76     echo "   &nbsp;" . text($docrow['encounters']) . "&nbsp;\n";
  77     echo "  </td>\n";
  78     echo "  <td align='right'>\n";
  79     echo "   &nbsp;";
  80     echo text(bucks($docrow['charges']));
  81     echo "&nbsp;\n";
  82     echo "  </td>\n";
  83     echo "  <td align='right'>\n";
  84     echo "   &nbsp;";
  85     echo text(bucks($docrow['copays']));
  86     echo "&nbsp;\n";
  87     echo "  </td>\n";
  88     echo "  <td colspan='2'>\n";
  89     echo "   &nbsp;\n";
  90     echo "  </td>\n";
  91     echo " </tr>\n";
  92
  93     $grand_total_charges     += $docrow['charges'];
  94     $grand_total_copays      += $docrow['copays'];
  95     $grand_total_encounters  += $docrow['encounters'];
  96
  97     $docrow['charges']     = 0;
  98     $docrow['copays']      = 0;
  99     $docrow['encounters']  = 0;
 100 }
 101
 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.
 107     //
 108     $sqlBindArray = array();
 109     $query = "( " .
 110     "SELECT " .
 111     "e.pc_eventDate, e.pc_startTime, " .
 112     "fe.encounter, fe.date AS encdate, " .
 113     "f.authorized, " .
 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 ";
 123     if ($form_to_date) {
 124         $query .= "e.pc_eventDate >= ? AND e.pc_eventDate <= ? ";
 125         array_push($sqlBindArray, $form_from_date, $form_to_date);
 126     } else {
 127         $query .= "e.pc_eventDate = ? ";
 128         array_push($sqlBindArray, $form_from_date);
 129     }
 130
 131     if ($form_facility !== '') {
 132         $query .= "AND e.pc_facility = ? ";
 133         array_push($sqlBindArray, $form_facility);
 134     }
 135
 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 != ? " .
 138     ") UNION ( " .
 139     "SELECT " .
 140     "e.pc_eventDate, e.pc_startTime, " .
 141     "fe.encounter, fe.date AS encdate, " .
 142     "f.authorized, " .
 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, '?', '?');
 155     if ($form_to_date) {
 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');
 159     } else {
 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');
 163     }
 164
 165     if ($form_facility !== '') {
 166         $query .= "AND fe.facility_id = ? ";
 167         array_push($sqlBindArray, $form_facility);
 168     }
 169
 170     $query .= ") ORDER BY docname, IFNULL(pc_eventDate, encdate), pc_startTime";
 171
 172     $res = sqlStatement($query, $sqlBindArray);
 173 }
 174 ?>
 175 <html>
 176 <head>
 177     <title><?php echo xlt('Appointments and Encounters'); ?></title>
 178
 179     <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
 180
 181     <style type="text/css">
 182         /* specifically include & exclude from printing */
 183         @media print {
 184             #report_parameters {
 185                 visibility: hidden;
 186                 display: none;
 187             }
 188             #report_parameters_daterange {
 189                 visibility: visible;
 190                 display: inline;
 191             }
 192             #report_results table {
 193                margin-top: 0px;
 194             }
 195         }
 196
 197         /* specifically exclude some from the screen */
 198         @media screen {
 199             #report_parameters_daterange {
 200                 visibility: hidden;
 201                 display: none;
 202             }
 203         }
 204     </style>
 205
 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'));
 211
 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 ?>
 218             });
 219         });
 220     </script>
 221 </head>
 222
 223 <body class="body_top">
 224
 225 <span class='title'><?php echo xlt('Report'); ?> - <?php echo xlt('Appointments and Encounters'); ?></span>
 226
 227 <div id="report_parameters_daterange">
 228     <?php echo text(oeFormatShortDate($form_from_date)) ." &nbsp; " . xlt('to') . " &nbsp; ". text(oeFormatShortDate($form_to_date)); ?>
 229 </div>
 230
 231 <form method='post' id='theform' action='appt_encounter_report.php' onsubmit='return top.restoreSession()'>
 232
 233 <div id="report_parameters">
 234
 235 <table>
 236  <tr>
 237   <td width='630px'>
 238     <div style='float:left'>
 239
 240     <table class='text'>
 241         <tr>
 242             <td class='control-label'>
 243                 <?php echo xlt('Facility'); ?>:
 244             </td>
 245             <td>
 246                 <?php
 247                  // Build a drop-down list of facilities.
 248                  //
 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) {
 256                         echo " selected";
 257                     }
 258                     echo ">" . text($frow['name']) . "\n";
 259                 }
 260
 261                 echo "    <option value='0'";
 262                 if ($form_facility === '0') {
 263                     echo " selected";
 264                 }
 265
 266                  echo ">-- " . xlt('Unspecified') . " --\n";
 267                  echo "   </select>\n";
 268                 ?>
 269             </td>
 270             <td class='control-label'>
 271                 <?php echo xlt('DOS'); ?>:
 272             </td>
 273             <td>
 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)); ?>' >
 275             </td>
 276             <td class='control-label'>
 277                 <?php echo xlt('To'); ?>:
 278             </td>
 279             <td>
 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)); ?>' >
 281             </td>
 282         </tr>
 283         <tr>
 284             <td>&nbsp;</td>
 285             <td>
 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>
 289         </div>
 290             </td>
 291         </tr>
 292     </table>
 293
 294     </div>
 295
 296   </td>
 297   <td align='left' valign='middle' height="100%">
 298     <table style='border-left:1px solid; width:100%; height:100%' >
 299         <tr>
 300             <td>
 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'); ?>
 305                     </a>
 306                     <?php if ($_POST['form_refresh']) { ?>
 307                       <a href='#' class='btn btn-default btn-print' id='printbutton'>
 308                             <?php echo xlt('Print'); ?>
 309                       </a>
 310                     <?php } ?>
 311           </div>
 312                 </div>
 313             </td>
 314         </tr>
 315     </table>
 316   </td>
 317  </tr>
 318 </table>
 319
 320 </div> <!-- end apptenc_report_parameters -->
 321
 322 <?php
 323 if ($_POST['form_refresh']) {
 324 ?>
 325 <div id="report_results">
 326 <table id='mymaintable'>
 327
 328 <thead>
 329 <th> &nbsp;<?php echo xlt('Practitioner'); ?> </th>
 330 <th> &nbsp;<?php echo xlt('Date/Appt'); ?> </th>
 331 <th> &nbsp;<?php echo xlt('Patient'); ?> </th>
 332 <th> &nbsp;<?php echo xlt('ID'); ?> </th>
 333 <th align='right'> <?php echo xlt('Chart'); ?>&nbsp; </th>
 334 <th align='right'> <?php echo xlt('Encounter'); ?>&nbsp; </th>
 335 <th align='right'> <?php echo xlt('Charges'); ?>&nbsp; </th>
 336 <th align='right'> <?php echo xlt('Copays'); ?>&nbsp; </th>
 337 <th> <?php echo xlt('Billed'); ?> </th>
 338 <th> &nbsp;<?php echo xlt('Error'); ?> </th>
 339 </thead>
 340 <tbody>
 341 <?php
 342 if ($res) {
 343     $docrow = array('docname' => '', 'charges' => 0, 'copays' => 0, 'encounters' => 0);
 344
 345     while ($row = sqlFetchArray($res)) {
 346         $patient_id = $row['pid'];
 347         $encounter  = $row['encounter'];
 348         $docname    = $row['docname'] ? $row['docname'] : xl('Unknown');
 349
 350         if ($docname != $docrow['docname']) {
 351             endDoctor($docrow);
 352         }
 353
 354         $errmsg  = "";
 355         $billed  = "Y";
 356         $charges = 0;
 357         $copays  = 0;
 358         $gcac_related_visit = false;
 359
 360         // Scan the billing items for status and fee total.
 361         //
 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));
 366         //
 367         while ($brow = sqlFetchArray($bres)) {
 368             $code_type = $brow['code_type'];
 369             if ($code_types[$code_type]['fee'] && !$brow['billed']) {
 370                 $billed = "";
 371             }
 372
 373             if (!$GLOBALS['simplified_demographics'] && !$brow['authorized']) {
 374                 postError(xl('Needs Auth'));
 375             }
 376
 377             if ($code_types[$code_type]['just']) {
 378                 if (! $brow['justify']) {
 379                     postError(xl('Needs Justify'));
 380                 }
 381             }
 382
 383             if ($code_types[$code_type]['fee']) {
 384                 $charges += $brow['fee'];
 385                 if ($brow['fee'] == 0 && !$GLOBALS['ippf_specific']) {
 386                     postError(xl('Missing Fee'));
 387                 }
 388             } else {
 389                 if ($brow['fee'] != 0) {
 390                     postError(xl('Fee is not allowed'));
 391                 }
 392             }
 393
 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']);
 403                     } else {
 404                         $query .= "(modifier IS NULL OR modifier = '')";
 405                     }
 406
 407                     $query .= " LIMIT 1";
 408                     $tmp = sqlQuery($query, $sqlBindArray);
 409                     $relcodes = explode(';', $tmp['related_code']);
 410                     foreach ($relcodes as $codestring) {
 411                         if ($codestring === '') {
 412                             continue;
 413                         }
 414
 415                         list($codetype, $code) = explode(':', $codestring);
 416                         if ($codetype !== 'IPPF') {
 417                             continue;
 418                         }
 419
 420                         if (preg_match('/^25222/', $code)) {
 421                             $gcac_related_visit = true;
 422                         }
 423                     }
 424                 }
 425             } // End IPPF stuff
 426         } // end while
 427
 428         $copays -= getPatientCopay($patient_id, $encounter);
 429
 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 " .
 437         "FROM lists AS l " .
 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";
 448         }
 449         else { // there is one but none is linked
 450         $errmsg = "GCAC issue is not linked";
 451         }
 452       }
 453      }
 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'));
 461             }
 462         } // end if
 463        /*****************************************************************/
 464
 465         if (!$billed) {
 466             postError($GLOBALS['simplified_demographics'] ?
 467             xl('Not checked out') : xl('Not billed'));
 468         }
 469
 470         if (!$encounter) {
 471             postError(xl('No visit'));
 472         }
 473
 474         if (! $charges) {
 475             $billed = "";
 476         }
 477
 478         $docrow['charges'] += $charges;
 479         $docrow['copays']  += $copays;
 480         if ($encounter) {
 481             ++$docrow['encounters'];
 482         }
 483
 484         if ($_POST['form_details']) {
 485             ?>
 486          <tr>
 487           <td>
 488             &nbsp;<?php echo ($docname == $docrow['docname']) ? "" : text($docname); ?>
 489    </td>
 490    <td>
 491       &nbsp;<?php
 492      /*****************************************************************
 493      if ($form_to_date) {
 494         echo $row['pc_eventDate'] . '<br>';
 495         echo substr($row['pc_startTime'], 0, 5);
 496      }
 497      *****************************************************************/
 498         if (empty($row['pc_eventDate'])) {
 499             echo text(oeFormatShortDate(substr($row['encdate'], 0, 10)));
 500         } else {
 501             echo text(oeFormatShortDate($row['pc_eventDate'])) . ' ' . text(substr($row['pc_startTime'], 0, 5));
 502         }
 503         ?>
 504          </td>
 505          <td>
 506           &nbsp;<?php echo text($row['fname']) . " " . text($row['lname']); ?>
 507          </td>
 508          <td>
 509           &nbsp;<?php echo text($row['pubpid']); ?>
 510          </td>
 511          <td align='right'>
 512             <?php echo text($row['pid']); ?>&nbsp;
 513          </td>
 514          <td align='right'>
 515             <?php echo text($encounter); ?>&nbsp;
 516          </td>
 517          <td align='right'>
 518             <?php echo text(bucks($charges)); ?>&nbsp;
 519          </td>
 520          <td align='right'>
 521             <?php echo text(bucks($copays)); ?>&nbsp;
 522          </td>
 523          <td>
 524             <?php echo text($billed); ?>
 525          </td>
 526          <td style='color:#cc0000'>
 527             <?php echo $errmsg; ?>&nbsp;
 528          </td>
 529         </tr>
 530         <?php
 531         } // end of details line
 532
 533         $docrow['docname'] = $docname;
 534     } // end of row
 535
 536     endDoctor($docrow);
 537
 538     echo " <tr class='report_totals'>\n";
 539     echo "  <td colspan='5'>\n";
 540     echo "   &nbsp;" . xlt('Grand Totals') . "\n";
 541     echo "  </td>\n";
 542     echo "  <td align='right'>\n";
 543     echo "   &nbsp;" . text($grand_total_encounters) . "&nbsp;\n";
 544     echo "  </td>\n";
 545     echo "  <td align='right'>\n";
 546     echo "   &nbsp;";
 547     echo text(bucks($grand_total_charges));
 548     echo "&nbsp;\n";
 549     echo "  </td>\n";
 550     echo "  <td align='right'>\n";
 551     echo "   &nbsp;";
 552     echo text(bucks($grand_total_copays));
 553     echo "&nbsp;\n";
 554     echo "  </td>\n";
 555     echo "  <td colspan='2'>\n";
 556     echo "   &nbsp;\n";
 557     echo "  </td>\n";
 558     echo " </tr>\n";
 559 }
 560 ?>
 561 </tbody>
 562 </table>
 563 </div> <!-- end the apptenc_report_results -->
 564 <?php } else { ?>
 565 <div class='text'>
 566     <?php echo xlt('Please input search criteria above, and click Submit to view results.'); ?>
 567 </div>
 568 <?php } ?>
 569
 570 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 571
 572 </form>
 573 <script>
 574 <?php if ($alertmsg) {
 575     echo " alert('$alertmsg');\n";
 576 } ?>
 577 </script>
 578 </body>
 579
 580 </html>