interface/billing/sl_receipts_report.php

   1 <?php
   2
   3 /**
   4  * Report - Cash receipts by Provider
   5  *
   6  * This module was written for one of my clients to report on cash
   7  * receipts by practitioner.  It is not as complete as it should be
   8  * but I wanted to make the code available to the project because
   9  * many other practices have this same need. - rod@sunsetsystems.com
  10  *
  11  * @package   OpenEMR
  12  * @link      http://www.open-emr.org
  13  * @author    Rod Roark <rod@sunsetsystems.com>
  14  * @author    Terry Hill <terry@lillysystems.com>
  15  * @author    Brady Miller <brady.g.miller@gmail.com>
  16  * @copyright Copyright (c) 2006-2020 Rod Roark <rod@sunsetsystems.com>
  17  * @copyright Copyright (c) 2016 Terry Hill <terry@lillysystems.com>
  18  * @copyright Copyright (c) 2017-2019 Brady Miller <brady.g.miller@gmail.com>
  19  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  20  */
  21
  22 // TODO: Replace tables with BS4 grid classes for GSoC
  23
  24
  25 require_once('../globals.php');
  26 require_once($GLOBALS['srcdir'] . '/patient.inc.php');
  27 require_once($GLOBALS['srcdir'] . '/options.inc.php');
  28 require_once($GLOBALS['fileroot'] . '/custom/code_types.inc.php');
  29 // This determines if a particular procedure code corresponds to receipts
  30 // for the "Clinic" column as opposed to receipts for the practitioner.  Each
  31 // practice will have its own policies in this regard, so you'll probably
  32 // have to customize this function.  If you use the "fee sheet" encounter
  33 // form then the code below may work for you.
  34 //
  35 require_once('../forms/fee_sheet/codes.php');
  36
  37 use OpenEMR\Common\Acl\AclMain;
  38 use OpenEMR\Common\Csrf\CsrfUtils;
  39 use OpenEMR\Common\Twig\TwigContainer;
  40 use OpenEMR\Common\Utils\FormatMoney;
  41 use OpenEMR\Core\Header;
  42
  43 if (!AclMain::aclCheckCore('acct', 'rep') && !AclMain::aclCheckCore('acct', 'rep_a')) {
  44     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Cash Receipts by Provider")]);
  45     exit;
  46 }
  47
  48 function is_clinic($code)
  49 {
  50     global $bcodes;
  51     $i = strpos($code, ':');
  52     if ($i) {
  53         $code = substr($code, 0, $i);
  54     }
  55
  56     return (
  57         !empty($bcodes['CPT4'][xl('Lab')][$code])   ||
  58         !empty($bcodes['CPT4'][xl('Immunizations')][$code]) ||
  59         !empty($bcodes['HCPCS'][xl('Therapeutic Injections')][$code])
  60     );
  61 }
  62
  63 $form_use_edate  = $_POST['form_use_edate'] ?? null;
  64
  65 $form_proc_codefull = trim($_POST['form_proc_codefull'] ?? '');
  66 // Parse the code type and the code from <code_type>:<code>
  67 $tmp_code_array = explode(':', $form_proc_codefull);
  68 $form_proc_codetype = $tmp_code_array[0];
  69 $form_proc_code = $tmp_code_array[1] ?? null;
  70
  71 $form_dx_codefull  = trim($_POST['form_dx_codefull'] ?? '');
  72 // Parse the code type and the code from <code_type>:<code>
  73 $tmp_code_array = explode(':', $form_dx_codefull);
  74 $form_dx_codetype = $tmp_code_array[0];
  75 $form_dx_code = $tmp_code_array[1] ?? null;
  76
  77 $form_procedures = empty($_POST['form_procedures']) ? 0 : 1;
  78 $form_from_date = (isset($_POST['form_from_date'])) ? DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-01');
  79 $form_to_date   = (isset($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
  80
  81 $form_facility   = $_POST['form_facility'] ?? null;
  82 ?>
  83 <html>
  84 <head>
  85
  86     <title><?php echo xlt('Cash Receipts by Provider')?></title>
  87
  88     <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
  89
  90     <style>
  91         /* specifically include & exclude from printing */
  92         @media print {
  93             #report_parameters {
  94                 visibility: hidden;
  95                 display: none;
  96             }
  97             #report_parameters_daterange {
  98                 visibility: visible;
  99                 display: inline;
 100             }
 101             #report_results {
 102                margin-top: 30px;
 103             }
 104         }
 105
 106         /* specifically exclude some from the screen */
 107         @media screen {
 108             #report_parameters_daterange {
 109                 visibility: hidden;
 110                 display: none;
 111             }
 112         }
 113     </style>
 114
 115     <script>
 116         $(function () {
 117             oeFixedHeaderSetup(document.getElementById('mymaintable'));
 118             var win = top.printLogSetup ? top : opener.top;
 119             win.printLogSetup(document.getElementById('printbutton'));
 120
 121             $('.datepicker').datetimepicker({
 122                 <?php $datetimepicker_timepicker = false; ?>
 123                 <?php $datetimepicker_showseconds = false; ?>
 124                 <?php $datetimepicker_formatInput = true; ?>
 125                 <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
 126                 <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
 127             });
 128         });
 129
 130         // This is for callback by the find-code popup.
 131         // Erases the current entry
 132         // The target element is set by the find-code popup
 133         //  (this allows use of this in multiple form elements on the same page)
 134         function set_related_target(codetype, code, selector, codedesc, target_element, limit=0) {
 135             var f = document.forms[0];
 136             var s = f[target_element].value;
 137             if (code) {
 138                 s = codetype + ':' + code;
 139             } else {
 140                 s = '';
 141             }
 142             f[target_element].value = s;
 143         }
 144
 145         // This invokes the find-code (procedure/service codes) popup.
 146         function sel_procedure() {
 147             dlgopen('../patient_file/encounter/find_code_popup.php?target_element=form_proc_codefull&codetype=' + <?php echo js_url(collect_codetypes("procedure", "csv")); ?>, '_blank', 500, 400);
 148         }
 149
 150         // This invokes the find-code (diagnosis codes) popup.
 151         function sel_diagnosis() {
 152             dlgopen('../patient_file/encounter/find_code_popup.php?target_element=form_dx_codefull&codetype=' + <?php echo js_url(collect_codetypes("diagnosis", "csv")); ?>, '_blank', 500, 400);
 153         }
 154
 155     </script>
 156 </head>
 157
 158 <body class="body_top">
 159 <div class="container">
 160     <div class="row">
 161         <div class="col-sm-12">
 162             <div class="clearfix">
 163                 <h2 class="title">
 164                     <?php echo xlt('Report'); ?> - <?php echo xlt('Cash Receipts by Provider'); ?>
 165                 </h2>
 166             </div>
 167         </div>
 168     </div><!-- end of header div -->
 169     <div class="row">
 170         <div class="col-12">
 171                <form method='post' action='sl_receipts_report.php' id='theform' onsubmit='return top.restoreSession()'>
 172                 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 173
 174                 <div id="report_parameters">
 175
 176                 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 177
 178                 <table>
 179                 <tr>
 180                 <td style='width: 660px'>
 181                     <div class='float-left'>
 182
 183                     <table class='text'>
 184                         <tr>
 185                             <td class='col-form-label'>
 186                                 <?php echo xlt('Facility'); ?>:
 187                             </td>
 188                             <td>
 189                             <?php dropdown_facility($form_facility, 'form_facility'); ?>
 190                             </td>
 191                             <td class='col-form-label'>
 192                                 <?php echo xlt('Provider'); ?>:
 193                             </td>
 194                             <td>
 195                                 <?php
 196                                 if (AclMain::aclCheckCore('acct', 'rep_a')) {
 197                                     // Build a drop-down list of providers.
 198                                     //
 199                                     $query = "select id, lname, fname from users where " .
 200                                         "authorized = 1 order by lname, fname";
 201                                     $res = sqlStatement($query);
 202                                     echo "<select name='form_doctor' class='form-control'>\n";
 203                                     echo "    <option value=''>-- " . xlt('All Providers') . " --\n";
 204                                     while ($row = sqlFetchArray($res)) {
 205                                         $provid = $row['id'];
 206                                         echo "    <option value='" . attr($provid) . "'";
 207                                         if (!empty($_POST['form_doctor']) && ($provid == $_POST['form_doctor'])) {
 208                                             echo " selected";
 209                                         }
 210
 211                                         echo ">" . text($row['lname']) . ", " . text($row['fname']) . "\n";
 212                                     }
 213
 214                                     echo "   </select>\n";
 215                                 } else {
 216                                     echo "<input type='hidden' name='form_doctor' value='" . attr($_SESSION['authUserID']) . "'>";
 217                                 }
 218                                 ?>
 219                             </td>
 220                             <td>
 221                             <select name='form_use_edate' class='form-control'>
 222                                 <option value='0'><?php echo xlt('Payment Date'); ?></option>
 223                                 <option value='1'<?php echo ($form_use_edate) ? ' selected' : ''; ?>><?php echo xlt('Invoice Date'); ?></option>
 224                             </select>
 225                             </td>
 226                         </tr>
 227                         <tr>
 228                             <td class='col-form-label'>
 229                                 <?php echo xlt('From'); ?>:
 230                             </td>
 231                             <td>
 232                             <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)); ?>'
 233                                 title='<?php echo xla('Date of appointments'); ?>' >
 234                             </td>
 235                             <td class='col-form-label'>
 236                                 <?php echo xlt('To{{Range}}'); ?>:
 237                             </td>
 238                             <td>
 239                             <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)); ?>'
 240                                 title='<?php echo xla('Optional end date'); ?>' >
 241                             </td>
 242                             <td>&nbsp;</td>
 243                         </tr>
 244                         <tr>
 245                             <td class='col-form-label'>
 246                                 <?php
 247                                 if (!$GLOBALS['simplified_demographics']) {
 248                                     echo '&nbsp;' . xlt('Procedure/Service') . ':';
 249                                 } ?>
 250                             </td>
 251                             <td>
 252                             <input type='text' class='form-control' name='form_proc_codefull' size='11' value='<?php echo attr($form_proc_codefull); ?>' onclick='sel_procedure()'
 253                                 title='<?php echo xla('Optional procedure/service code'); ?>'
 254                                 <?php
 255                                 if ($GLOBALS['simplified_demographics']) {
 256                                     echo "style='display:none'";
 257                                 } ?>>
 258                             </td>
 259
 260                             <td class='col-form-label'>
 261                                 <?php
 262                                 if (!$GLOBALS['simplified_demographics']) {
 263                                     echo '&nbsp;' . xlt('Diagnosis') . ':';
 264                                 } ?>
 265                             </td>
 266                             <td>
 267                             <input type='text' class='form-control' name='form_dx_codefull' size='11' value='<?php echo attr($form_dx_codefull); ?>' onclick='sel_diagnosis()'
 268                                 title='<?php echo xla('Enter a diagnosis code to exclude all invoices not containing it'); ?>'
 269                                 <?php
 270                                 if ($GLOBALS['simplified_demographics']) {
 271                                     echo "style='display: none'";
 272                                 } ?>>
 273                             </td>
 274
 275                             <td>
 276                         <div class='checkbox'>
 277                                 <label><input type='checkbox' name='form_details' value='1'<?php echo (!empty($_POST['form_details'])) ? " checked" : ""; ?>><?php echo xlt('Details')?></label>
 278                         </div>
 279                         <div class='checkbox'>
 280                                 <label><input type='checkbox' name='form_procedures' value='1'<?php echo ($form_procedures) ? " checked" : ""; ?>><?php echo xlt('Procedures')?></label>
 281                         </div>
 282                             </td>
 283                         </tr>
 284
 285                     </table>
 286
 287                     </div>
 288
 289                 </td>
 290
 291                 <td class='h-100 align-middle' align='left'>
 292
 293                     <table class='w-100 h-100 border-left'>
 294                         <tr>
 295                             <td>
 296                                 <div class="text-center">
 297                         <div class="btn-group" role="group">
 298                                     <a href='#' class='btn btn-secondary btn-save' onclick='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
 299                                             <?php echo xlt('Submit'); ?>
 300                                     </a>
 301                                         <?php if (!empty($_POST['form_refresh'])) { ?>
 302                                         <a href='#' class='btn btn-secondary btn-print' id='printbutton'>
 303                                                 <?php echo xlt('Print'); ?>
 304                                         </a>
 305                                         <?php } ?>
 306                         </div>
 307                                 </div>
 308                             </td>
 309                         </tr>
 310                     </table>
 311                 </td>
 312                 </tr>
 313                 </table>
 314                 </div>
 315
 316                 <?php
 317                 if (!empty($_POST['form_refresh'])) {
 318                     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
 319                         CsrfUtils::csrfNotVerified();
 320                     }
 321
 322                     ?>
 323                 <div id="report_results">
 324                 <table class='table' cellpadding='1' cellspacing='2' width='98%' id='mymaintable'>
 325                 <thead>
 326                 <th>
 327                     <?php echo xlt('Practitioner') ?>
 328                 </th>
 329                 <th>
 330                     <?php echo xlt('Date') ?>
 331                 </th>
 332                     <?php if ($form_procedures) { ?>
 333                 <th>
 334                         <?php
 335                         if ($GLOBALS['cash_receipts_report_invoice'] == '0') {
 336                             echo xlt('Invoice');
 337                         } else {
 338                             echo xlt('Name');
 339                         }?>
 340                 </th>
 341                     <?php } ?>
 342                     <?php if ($form_proc_codefull) { ?>
 343                 <th align='right'>
 344                         <?php echo xlt('InvAmt') ?>
 345                 </th>
 346                     <?php } ?>
 347                     <?php if ($form_proc_codefull) { ?>
 348                 <th>
 349                         <?php echo xlt('Insurance') ?>
 350                 </th>
 351                     <?php } ?>
 352                     <?php if ($form_procedures) { ?>
 353                 <th>
 354                         <?php echo xlt('Procedure') ?>
 355                 </th>
 356                 <th align="right">
 357                         <?php echo xlt('Prof.') ?>
 358                 </th>
 359                 <th align="right">
 360                         <?php echo xlt('Clinic') ?>
 361                 </th>
 362                     <?php } else { ?>
 363                 <th align="right">
 364                         <?php echo xlt('Received') ?>
 365                 </th>
 366                     <?php } ?>
 367                 </thead>
 368                     <?php
 369                     if ($_POST['form_refresh']) {
 370                         $form_doctor = $_POST['form_doctor'];
 371                         if (!AclMain::aclCheckCore('acct', 'rep_a')) {
 372                             // only allow user to see their encounter information
 373                             $form_doctor = $_SESSION['authUserID'];
 374                         }
 375
 376                         $arows = array();
 377
 378                         $ids_to_skip = array();
 379                         $irow = 0;
 380
 381                         // Get copays.  These will be ignored if a CPT code was specified.
 382                         //
 383                         if (!$form_proc_code || !$form_proc_codetype) {
 384                             /*************************************************************
 385                             $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.modifier, " .
 386                             "fe.date, fe.id AS trans_id, u.id AS docid " .
 387                             "FROM billing AS b " .
 388                             "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 389                             "JOIN forms AS f ON f.pid = b.pid AND f.encounter = b.encounter AND f.formdir = 'newpatient' " .
 390                             "LEFT OUTER JOIN users AS u ON u.username = f.user " .
 391                             "WHERE b.code_type = 'COPAY' AND b.activity = 1 AND " .
 392                             "fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_to_date 23:59:59'";
 393                             // If a facility was specified.
 394                             if ($form_facility) {
 395                             $query .= " AND fe.facility_id = '$form_facility'";
 396                             }
 397                             // If a doctor was specified.
 398                             if ($form_doctor) {
 399                             $query .= " AND u.id = '$form_doctor'";
 400                             }
 401                             *************************************************************/
 402                             $sqlBindArray = array();
 403                             $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.modifier, " .
 404                             "fe.date, fe.id AS trans_id, fe.provider_id AS docid, fe.invoice_refno " .
 405                             "FROM billing AS b " .
 406                             "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 407                             "WHERE b.code_type = 'COPAY' AND b.activity = 1 AND " .
 408                             "fe.date >= ? AND fe.date <= ?";
 409                             array_push($sqlBindArray, $form_from_date . " 00:00:00", $form_to_date . " 23:59:59");
 410                             // If a facility was specified.
 411                             if ($form_facility) {
 412                                 $query .= " AND fe.facility_id = ?";
 413                                 array_push($sqlBindArray, $form_facility);
 414                             }
 415
 416                             // If a doctor was specified.
 417                             if ($form_doctor) {
 418                                 $query .= " AND fe.provider_id = ?";
 419                                 array_push($sqlBindArray, $form_doctor);
 420                             }
 421
 422                             /************************************************************/
 423                             //
 424                             $res = sqlStatement($query, $sqlBindArray);
 425                             while ($row = sqlFetchArray($res)) {
 426                                 $trans_id = $row['trans_id'];
 427                                 $thedate = substr($row['date'], 0, 10);
 428                                 $patient_id = $row['pid'];
 429                                 $encounter_id = $row['encounter'];
 430                             //
 431                                 if (!empty($ids_to_skip[$trans_id])) {
 432                                     continue;
 433                                 }
 434
 435                             //
 436                             // If a diagnosis code was given then skip any invoices without
 437                             // that diagnosis.
 438                                 if ($form_dx_code && $form_dx_codetype) {
 439                                     $tmp = sqlQuery("SELECT count(*) AS count FROM billing WHERE " .
 440                                     "pid = ? AND encounter = ? AND " .
 441                                     "code_type = ? AND code LIKE ? AND " .
 442                                     "activity = 1", array($patient_id,$encounter_id,$form_dx_codetype,$form_dx_code));
 443                                     if (empty($tmp['count'])) {
 444                                         $ids_to_skip[$trans_id] = 1;
 445                                         continue;
 446                                     }
 447                                 }
 448
 449                             //
 450                                 $key = sprintf(
 451                                     "%08u%s%08u%08u%06u",
 452                                     $row['docid'],
 453                                     $thedate,
 454                                     $patient_id,
 455                                     $encounter_id,
 456                                     ++$irow
 457                                 );
 458                                 $arows[$key] = array();
 459                                 $arows[$key]['transdate'] = $thedate;
 460                                 $arows[$key]['amount'] = $row['fee'];
 461                                 $arows[$key]['docid'] = $row['docid'];
 462                                 $arows[$key]['project_id'] = 0;
 463                                 $arows[$key]['memo'] = '';
 464                                 if ($GLOBALS['cash_receipts_report_invoice'] == '0') {
 465                                     $arows[$key]['invnumber'] = "$patient_id.$encounter_id";
 466                                 } else {
 467                                     $arows[$key]['invnumber'] = "$patient_name";
 468                                 }
 469
 470                                 $arows[$key]['irnumber'] = $row['invoice_refno'];
 471                             } // end while
 472                         } // end copays (not $form_proc_code)
 473
 474                         // Get ar_activity (having payments), form_encounter, forms, users, optional ar_session
 475                         /***************************************************************
 476                         $query = "SELECT a.pid, a.encounter, a.post_time, a.code, a.modifier, a.pay_amount, " .
 477                         "fe.date, fe.id AS trans_id, u.id AS docid, s.deposit_date, s.payer_id " .
 478                         "FROM ar_activity AS a " .
 479                         "JOIN form_encounter AS fe ON fe.pid = a.pid AND fe.encounter = a.encounter " .
 480                         "JOIN forms AS f ON f.pid = a.pid AND f.encounter = a.encounter AND f.formdir = 'newpatient' " .
 481                         "LEFT OUTER JOIN users AS u ON u.username = f.user " .
 482                         "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 483                         "WHERE a.pay_amount != 0 AND ( " .
 484                         "a.post_time >= '$form_from_date 00:00:00' AND a.post_time <= '$form_to_date 23:59:59' " .
 485                         "OR fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_to_date 23:59:59' " .
 486                         "OR s.deposit_date >= '$form_from_date' AND s.deposit_date <= '$form_to_date' )";
 487                         // If a procedure code was specified.
 488                         if ($form_proc_code) $query .= " AND a.code = '$form_proc_code'";
 489                         // If a facility was specified.
 490                         if ($form_facility) $query .= " AND fe.facility_id = '$form_facility'";
 491                         // If a doctor was specified.
 492                         if ($form_doctor) $query .= " AND u.id = '$form_doctor'";
 493                         ***************************************************************/
 494                         $sqlBindArray = array();
 495                         $query = "SELECT a.pid, a.encounter, a.post_time, a.code, a.modifier, a.pay_amount, " .
 496                         "fe.date, fe.id AS trans_id, fe.provider_id AS docid, fe.invoice_refno, s.deposit_date, s.payer_id, " .
 497                         "b.provider_id, concat(p.lname, ' ', p.fname) as 'pat_fulname' " .
 498                         "FROM ar_activity AS a " .
 499                         "JOIN form_encounter AS fe ON fe.pid = a.pid AND fe.encounter = a.encounter " .
 500                         "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 501                         "LEFT OUTER JOIN patient_data AS p ON p.pid = a.pid " .
 502                         "LEFT OUTER JOIN billing AS b ON b.pid = a.pid AND b.encounter = a.encounter AND " .
 503                         "b.code = a.code AND b.modifier = a.modifier AND b.activity = 1 AND " .
 504                         "b.code_type != 'COPAY' AND b.code_type != 'TAX' " .
 505                         "WHERE a.deleted IS NULL AND a.pay_amount != 0 AND ( " .
 506                         "a.post_time >= ? AND a.post_time <= ? " .
 507                         "OR fe.date >= ? AND fe.date <= ? " .
 508                         "OR s.deposit_date >= ? AND s.deposit_date <= ? )";
 509                         array_push($sqlBindArray, $form_from_date . " 00:00:00", $form_to_date . " 23:59:59", $form_from_date . " 00:00:00", $form_to_date . " 23:59:59", $form_from_date, $form_to_date);
 510                         // If a procedure code was specified.
 511                         // Support code type if it is in the ar_activity table. Note it is not always included, so
 512                         // also support a blank code type in ar_activity table.
 513                         if ($form_proc_codetype && $form_proc_code) {
 514                             $query .= " AND (a.code_type = ? OR a.code_type = '') AND a.code = ?";
 515                             array_push($sqlBindArray, $form_proc_codetype, $form_proc_code);
 516                         }
 517
 518                         // If a facility was specified.
 519                         if ($form_facility) {
 520                             $query .= " AND fe.facility_id = ?";
 521                             array_push($sqlBindArray, $form_facility);
 522                         }
 523
 524                         // If a doctor was specified.
 525                         if ($form_doctor) {
 526                             $query .= " AND ( b.provider_id = ? OR " .
 527                             "( ( b.provider_id IS NULL OR b.provider_id = 0 ) AND " .
 528                             "fe.provider_id = ? ) )";
 529                             array_push($sqlBindArray, $form_doctor, $form_doctor);
 530                         }
 531
 532                         /**************************************************************/
 533                         //
 534                         $res = sqlStatement($query, $sqlBindArray);
 535                         while ($row = sqlFetchArray($res)) {
 536                             $trans_id = $row['trans_id'];
 537                             $patient_id = $row['pid'];
 538                             $encounter_id = $row['encounter'];
 539                             $patient_name = $row['pat_fulname'];
 540                             //
 541                             if (!empty($ids_to_skip[$trans_id])) {
 542                                 continue;
 543                             }
 544
 545                             //
 546                             if ($form_use_edate) {
 547                                 $thedate = substr($row['date'], 0, 10);
 548                             } else {
 549                                 if (!empty($row['deposit_date'])) {
 550                                     $thedate = $row['deposit_date'];
 551                                 } else {
 552                                     $thedate = substr($row['post_time'], 0, 10);
 553                                 }
 554                             }
 555
 556                             if (strcmp($thedate, $form_from_date) < 0 || strcmp($thedate, $form_to_date) > 0) {
 557                                 continue;
 558                             }
 559
 560                             //
 561                             // If a diagnosis code was given then skip any invoices without
 562                             // that diagnosis.
 563                             if ($form_dx_code && $form_dx_codetype) {
 564                                 $tmp = sqlQuery("SELECT count(*) AS count FROM billing WHERE " .
 565                                 "pid = ? AND encounter = ? AND " .
 566                                 "code_type = ? AND code LIKE ? AND " .
 567                                 "activity = 1", array($patient_id,$encounter_id,$form_dx_codetype,$form_dx_code));
 568                                 if (empty($tmp['count'])) {
 569                                     $ids_to_skip[$trans_id] = 1;
 570                                     continue;
 571                                 }
 572                             }
 573
 574                             //
 575                             $docid = empty($row['encounter_id']) ? $row['docid'] : $row['encounter_id'];
 576                             $key = sprintf(
 577                                 "%08u%s%08u%08u%06u",
 578                                 $docid,
 579                                 $thedate,
 580                                 $patient_id,
 581                                 $encounter_id,
 582                                 ++$irow
 583                             );
 584                             $arows[$key] = array();
 585                             $arows[$key]['transdate'] = $thedate;
 586                             $arows[$key]['amount'] = 0 - $row['pay_amount'];
 587                             $arows[$key]['docid'] = $docid;
 588                             $arows[$key]['project_id'] = empty($row['payer_id']) ? 0 : $row['payer_id'];
 589                             $arows[$key]['memo'] = $row['code'];
 590                             if ($GLOBALS['cash_receipts_report_invoice'] == '0') {
 591                                 $arows[$key]['invnumber'] = "$patient_id.$encounter_id";
 592                             } else {
 593                                 $arows[$key]['invnumber'] = "$patient_name";
 594                             }
 595
 596                             $arows[$key]['irnumber'] = $row['invoice_refno'];
 597                         } // end while
 598
 599                         ksort($arows);
 600                         $docid = 0;
 601
 602                         foreach ($arows as $row) {
 603                         // Get insurance company name
 604                             $insconame = '';
 605                             if ($form_proc_codefull  && $row['project_id']) {
 606                                 $tmp = sqlQuery("SELECT name FROM insurance_companies WHERE " .
 607                                 "id = ?", array($row['project_id']));
 608                                 $insconame = $tmp['name'];
 609                             }
 610
 611                             $amount1 = 0;
 612                             $amount2 = 0;
 613                             if ($form_procedures && is_clinic($row['memo'])) {
 614                                 $amount2 -= $row['amount'];
 615                             } else {
 616                                 $amount1 -= $row['amount'];
 617                             }
 618
 619                         // if ($docid != $row['employee_id']) {
 620                             if ($docid != $row['docid']) {
 621                                 if ($docid) {
 622                                     // Print doc totals.
 623                                     ?>
 624                     <!-- TODO: Replace bgcolor with BS4 !-->
 625                     <tr bgcolor="#ddddff">
 626                         <td class="detail" colspan="<?php echo ($form_proc_codefull ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 627                                     <?php echo xlt('Totals for ') . text($docname) ?>
 628                 </td>
 629                 <td align="right">
 630                                     <?php echo text(FormatMoney::getBucks($doctotal1)) ?>
 631                 </td>
 632                                     <?php if ($form_procedures) { ?>
 633                 <td align="right">
 634                                         <?php echo text(FormatMoney::getBucks($doctotal2)) ?>
 635                 </td>
 636                     <?php } ?>
 637                 </tr>
 638                                     <?php
 639                                 }
 640
 641                                 $doctotal1 = 0;
 642                                 $doctotal2 = 0;
 643
 644                                 $docid = $row['docid'];
 645                                 $tmp = sqlQuery("SELECT lname, fname FROM users WHERE id = ?", array($docid));
 646                                 $docname = empty($tmp) ? xl('Unknown') : $tmp['fname'] . ' ' . $tmp['lname'];
 647
 648                                 $docnameleft = $docname;
 649                             }
 650
 651                             if ($_POST['form_details']) {
 652                                 ?>
 653
 654                 <tr>
 655                     <td class="detail">
 656                                 <?php echo text($docnameleft); $docnameleft = " " ?>
 657                 </td>
 658                 <td class="detail">
 659                                 <?php echo text(oeFormatShortDate($row['transdate'])); ?>
 660                 </td>
 661                                 <?php if ($form_procedures) { ?>
 662                 <td class="detail">
 663                                     <?php echo empty($row['irnumber']) ? text($row['invnumber']) : text($row['irnumber']); ?>
 664                 </td>
 665                     <?php } ?>
 666                                 <?php
 667                                 if ($form_proc_code && $form_proc_codetype) {
 668                                         echo "  <td class='detail' align='right'>";
 669                                         list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 670                                         $tmp = sqlQuery("SELECT SUM(fee) AS sum FROM billing WHERE " .
 671                                             "pid = ? AND encounter = ? AND " .
 672                                             "code_type = ? AND code = ? AND activity = 1", array($patient_id,$encounter_id,$form_proc_codetype,$form_proc_code));
 673                                         echo text(FormatMoney::getBucks($tmp['sum']));
 674                                         echo "  </td>\n";
 675                                 }
 676                                 ?>
 677                                 <?php
 678                                 if ($form_proc_codefull) { ?>
 679                     <td class="detail">
 680                                         <?php echo text($insconame) ?>
 681                     </td>
 682                                     <?php } ?>
 683                                 <?php if ($form_procedures) { ?>
 684                 <td class="detail">
 685                                     <?php echo text($row['memo']) ?>
 686                 </td>
 687                             <?php } ?>
 688                 <td class="detail" align="right">
 689                                 <?php echo text(FormatMoney::getBucks($amount1)) ?>
 690                 </td>
 691                                 <?php if ($form_procedures) { ?>
 692                 <td class="detail" align="right">
 693                                     <?php echo text(FormatMoney::getBucks($amount2)) ?>
 694                 </td>
 695                             <?php } ?>
 696                 </tr>
 697                                 <?php
 698                             } // end details
 699                             $doctotal1   += $amount1;
 700                             $doctotal2   += $amount2;
 701
 702                             $grandtotal1 = $grandtotal1 ?? null;
 703                             $grandtotal1 += $amount1;
 704
 705                             $grandtotal2 = $grandtotal2 ?? null;
 706                             $grandtotal2 += $amount2;
 707                         }
 708                         ?>
 709                 <!-- TODO: Replace bgcolor with BS4 !-->
 710                 <tr bgcolor="#ddddff">
 711                 <td class="detail" colspan="<?php echo ($form_proc_codefull ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 712                         <?php echo xlt('Totals for ') . text($docname ?? '') ?>
 713                 </td>
 714                 <td align="right">
 715                         <?php echo text(FormatMoney::getBucks($doctotal1 ?? '')) ?>
 716                 </td>
 717                         <?php if ($form_procedures) { ?>
 718                 <td align="right">
 719                             <?php echo text(FormatMoney::getBucks($doctotal2)) ?>
 720                 </td>
 721                 <?php } ?>
 722                 </tr>
 723
 724                 <!-- TODO: Replace bgcolor with BS4 !-->
 725                 <tr bgcolor="#ffdddd">
 726                 <td class="detail" colspan="<?php echo ($form_proc_codefull ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 727                         <?php echo xlt('Grand Totals') ?>
 728                 </td>
 729                 <td align="right">
 730                         <?php echo text(FormatMoney::getBucks($grandtotal1 ?? '')) ?>
 731                 </td>
 732                         <?php if ($form_procedures) { ?>
 733                 <td align="right">
 734                             <?php echo text(FormatMoney::getBucks($grandtotal2)) ?>
 735                 </td>
 736                 <?php } ?>
 737                 </tr>
 738                         <?php $report_from_date = oeFormatShortDate($form_from_date)  ;
 739                         $report_to_date = oeFormatShortDate($form_to_date)  ;
 740                         ?>
 741                 <div align='right'><span class='title'><?php echo xlt('Report Date') . ' '; ?><?php echo text($report_from_date);?> - <?php echo text($report_to_date);?></span></div>
 742
 743                         <?php
 744                     }
 745                     ?>
 746
 747                 </table>
 748                 </div>
 749                 <?php } else { ?>
 750                 <div class='text'>
 751                     <?php echo xlt('Please input search criteria above, and click Submit to view results.'); ?>
 752                 </div>
 753                 <?php } ?>
 754
 755                 </form>
 756         </div>
 757     </div>
 758 </div>
 759
 760
 761 </body>
 762
 763 </html>