interface/reports/receipts_by_method_report.php

   1 <?php
   2
   3 /**
   4  * This is a report of receipts by payer or payment method.
   5  *
   6  * The payer option means an insurance company name or "Personal pay".
   7  *
   8  * The payment method option is most useful for sites using
   9  * pos_checkout.php (e.g. weight loss clinics) because this plugs
  10  * a payment method like Cash, Check, VISA, etc. into the "source"
  11  * column of the SQL-Ledger acc_trans table or ar_session table.
  12  *
  13  * @package   OpenEMR
  14  * @link      http://www.open-emr.org
  15  * @author    Rod Roark <rod@sunsetsystems.com>
  16  * @author    Brady Miller <brady.g.miller@gmail.com>
  17  * @author    Stephen Waite <stephen.waite@cmsvt.com>
  18  * @copyright Copyright (c) 2006-2020 Rod Roark <rod@sunsetsystems.com>
  19  * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
  20  * @copyright Copyright (c) 2019-2022 Stephen Waite <stephen.waite@cmsvt.com>
  21  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  22  */
  23
  24 require_once("../globals.php");
  25 require_once("$srcdir/patient.inc.php");
  26 require_once("$srcdir/options.inc.php");
  27 require_once("../../custom/code_types.inc.php");
  28
  29 use OpenEMR\Common\Acl\AclMain;
  30 use OpenEMR\Common\Csrf\CsrfUtils;
  31 use OpenEMR\Common\Twig\TwigContainer;
  32 use OpenEMR\Common\Utils\FormatMoney;
  33 use OpenEMR\Core\Header;
  34 use OpenEMR\Services\InsuranceCompanyService;
  35 use OpenEMR\Services\InsuranceService;
  36
  37 if (!AclMain::aclCheckCore('acct', 'rep_a')) {
  38     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Receipts Summary")]);
  39     exit;
  40 }
  41
  42 if (!empty($_POST)) {
  43     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  44         CsrfUtils::csrfNotVerified();
  45     }
  46 }
  47
  48 // This controls whether we show pt name, policy number and DOS.
  49 $showing_ppd = true;
  50
  51 $insarray = array();
  52
  53 function thisLineItem(
  54     $patient_id,
  55     $encounter_id,
  56     $memo,
  57     $transdate,
  58     $rowmethod,
  59     $rowpayamount,
  60     $rowadjamount,
  61     $payer_type = 0,
  62     $irnumber = ''
  63 ) {
  64
  65     global $form_report_by, $insarray, $grandpaytotal, $grandadjtotal;
  66
  67     if ($form_report_by != '1') { // reporting by method or check number
  68         showLineItem(
  69             $patient_id,
  70             $encounter_id,
  71             $memo,
  72             $transdate,
  73             $rowmethod,
  74             $rowpayamount,
  75             $rowadjamount,
  76             $payer_type,
  77             $irnumber
  78         );
  79         return;
  80     }
  81
  82   // Reporting by payer.
  83   //
  84     if (!empty($_POST['form_details'])) { // details are wanted
  85         // Save everything for later sorting.
  86         $insarray[] = array(
  87             $patient_id,
  88             $encounter_id,
  89             $memo,
  90             $transdate,
  91             $rowmethod,
  92             $rowpayamount,
  93             $rowadjamount,
  94             $payer_type,
  95             $irnumber
  96         );
  97     } else { // details not wanted
  98         if (empty($insarray[$rowmethod])) {
  99             $insarray[$rowmethod] = array(0, 0);
 100         }
 101
 102         $insarray[$rowmethod][0] += $rowpayamount;
 103         $insarray[$rowmethod][1] += $rowadjamount;
 104         $grandpaytotal  += $rowpayamount;
 105         $grandadjtotal  += $rowadjamount;
 106     }
 107 }
 108
 109 function showLineItem(
 110     $patient_id,
 111     $encounter_id,
 112     $memo,
 113     $transdate,
 114     $rowmethod,
 115     $rowpayamount,
 116     $rowadjamount,
 117     $payer_type = 0,
 118     $irnumber = ''
 119 ) {
 120
 121     global $paymethod, $paymethodleft, $methodpaytotal, $methodadjtotal,
 122     $grandpaytotal, $grandadjtotal, $showing_ppd;
 123
 124     if (!$rowmethod) {
 125         $rowmethod = 'Unknown';
 126     }
 127
 128     if ($paymethod != $rowmethod) {
 129         if ($paymethod) {
 130             // Print method total.
 131             ?>
 132
 133             <tr class="table-secondary">
 134                 <td colspan="<?php echo $showing_ppd ? 8 : 4; ?>">
 135                     <?php echo xlt('Total for ') . text($paymethod); ?>
 136                 </td>
 137                 <td class="text-right">
 138                     <?php echo text(FormatMoney::getBucks($methodadjtotal)); ?>
 139                 </td>
 140                 <td class="text-right">
 141                     <?php echo text(FormatMoney::getBucks($methodpaytotal)); ?>
 142                 </td>
 143             </tr>
 144             <?php
 145         }
 146
 147         $methodpaytotal = 0;
 148         $methodadjtotal  = 0;
 149         $paymethod = $rowmethod;
 150         $paymethodleft = $paymethod;
 151     }
 152
 153     if ($_POST['form_details']) {
 154         ?>
 155
 156     <tr>
 157         <td>
 158             <?php echo text($paymethodleft); $paymethodleft = " " ?>
 159         </td>
 160         <td>
 161             <?php echo text($memo); $memo = " " ?>
 162         </td>
 163         <td>
 164             <?php echo text(oeFormatShortDate($transdate)); ?>
 165         </td>
 166         <td>
 167             <?php
 168                 $pferow = sqlQuery("SELECT p.fname, p.mname, p.lname, fe.date, fe.id " .
 169                 "FROM patient_data AS p, form_encounter AS fe WHERE " .
 170                 "p.pid = ? AND fe.pid = p.pid AND " .
 171                 "fe.encounter = ? LIMIT 1", array($patient_id, $encounter_id));
 172             if (!empty($irnumber)) {
 173                 echo text($invnumber);
 174             } else {
 175                 echo "<input type='button' class='btn btn-sm btn-secondary' value='" .
 176                       attr($patient_id) . "-" . attr($encounter_id) .
 177                       "' onclick='editInvoice(event, " . attr_js($pferow['id']) . ")' />";
 178             }
 179             ?>
 180         </td>
 181         <?php
 182         if ($showing_ppd) {
 183             $dos = substr($pferow['date'], 0, 10);
 184
 185             echo "  <td class='font-weight-bold'>\n";
 186             echo "   " . text($pferow['lname']) . ", " . text($pferow['fname']) . " " . text($pferow['mname']);
 187             echo "  </td>\n";
 188
 189             echo "  <td class='font-weight-bold'>\n";
 190             if ($payer_type) {
 191                 $ptarr = array(1 => 'primary', 2 => 'secondary', 3 => 'tertiary');
 192                 $insrow = getInsuranceDataByDate(
 193                     $patient_id,
 194                     $dos,
 195                     $ptarr[$payer_type],
 196                     "policy_number"
 197                 );
 198                 echo "   " . text($insrow['policy_number']);
 199             }
 200
 201             echo "  </td>\n";
 202
 203             echo "  <td class='font-weight-bold'>\n";
 204             echo "   " . text(oeFormatShortDate($dos)) . "\n";
 205             echo "  </td>\n";
 206         }
 207         ?>
 208
 209   <td>
 210         <?php echo text($memo); ?>
 211   </td>
 212   <td align="right">
 213         <?php echo text(FormatMoney::getBucks($rowadjamount)); ?>
 214   </td>
 215   <td align="right">
 216         <?php echo text(FormatMoney::getBucks($rowpayamount)); ?>
 217   </td>
 218  </tr>
 219         <?php
 220     }
 221
 222     $methodpaytotal += $rowpayamount;
 223     $grandpaytotal  += $rowpayamount;
 224     $methodadjtotal += $rowadjamount;
 225     $grandadjtotal  += $rowadjamount;
 226 }
 227
 228 // This is called by usort() when reporting by payer with details.
 229 // Sorts by payer/date/patient/encounter/memo.
 230 function payerCmp($a, $b)
 231 {
 232     foreach (array(4,3,0,1,2,7) as $i) {
 233         if ($a[$i] < $b[$i]) {
 234             return -1;
 235         }
 236
 237         if ($a[$i] > $b[$i]) {
 238             return  1;
 239         }
 240     }
 241
 242     return 0;
 243 }
 244
 245 $form_from_date = (isset($_POST['form_from_date'])) ? DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
 246 $form_to_date   = (isset($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
 247 $form_use_edate = $_POST['form_use_edate'] ?? null;
 248 $form_facility  = $_POST['form_facility'] ?? null;
 249 $form_report_by = $_POST['form_report_by'] ?? null;
 250 $form_proc_codefull = trim($_POST['form_proc_codefull'] ?? '');
 251 // Parse the code type and the code from <code_type>:<code>
 252 $tmp_code_array = explode(':', $form_proc_codefull);
 253 $form_proc_codetype = $tmp_code_array[0];
 254 $form_proc_code = $tmp_code_array[1] ?? null;
 255
 256 ?>
 257 <!DOCTYPE html>
 258 <html>
 259 <head>
 260     <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
 261
 262     <title><?php echo xlt('Receipts Summary')?></title>
 263
 264     <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
 265
 266     <style>
 267         /* specifically include & exclude from printing */
 268         @media print {
 269             #report_parameters {
 270                 visibility: hidden;
 271                 display: none;
 272             }
 273             #report_parameters_daterange {
 274                 visibility: visible;
 275                 display: inline;
 276             }
 277             #report_results {
 278                margin-top: 30px;
 279             }
 280         }
 281
 282         /* specifically exclude some from the screen */
 283         @media screen {
 284             #report_parameters_daterange {
 285                 visibility: hidden;
 286                 display: none;
 287             }
 288         }
 289
 290     </style>
 291
 292     <script>
 293         $(function () {
 294             oeFixedHeaderSetup(document.getElementById('mymaintable'));
 295             var win = top.printLogSetup ? top : opener.top;
 296             win.printLogSetup(document.getElementById('printbutton'));
 297
 298             $('.datepicker').datetimepicker({
 299                 <?php $datetimepicker_timepicker = false; ?>
 300                 <?php $datetimepicker_showseconds = false; ?>
 301                 <?php $datetimepicker_formatInput = true; ?>
 302                 <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
 303                 <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
 304             });
 305         });
 306
 307         // This is for callback by the find-code popup.
 308         // Erases the current entry
 309         function set_related(codetype, code, selector, codedesc) {
 310             var f = document.forms[0];
 311             var s = f.form_proc_codefull.value;
 312             if (code) {
 313                 s = codetype + ':' + code;
 314             } else {
 315                 s = '';
 316             }
 317             f.form_proc_codefull.value = s;
 318         }
 319
 320         // This invokes the find-code popup.
 321         function sel_procedure() {
 322             dlgopen('../patient_file/encounter/find_code_popup.php?codetype=<?php echo attr_url(collect_codetypes("procedure", "csv")) ?>', '_blank', 500, 400);
 323         }
 324
 325         function editInvoice(e, id) {
 326             e.preventDefault();
 327             let url = '../billing/sl_eob_invoice.php?id=' + encodeURIComponent(id);
 328             <?php if (isset($_FILES['form_erafile']['size']) && !$_FILES['form_erafile']['size']) { ?>
 329                 dlgopen(url,'','modal-full',700,false,'', {
 330                 sizeHeight: 'full',
 331                 onClosed: 'reSubmit'
 332             }); <?php } else { // keep era page up so can check on other remits ?>
 333                 dlgopen(url,'','modal-full',700,false,'', {
 334                 sizeHeight: 'full',
 335                 onClosed: ''
 336             }); <?php } ?>
 337         }
 338     </script>
 339 </head>
 340
 341 <body class="body_top">
 342
 343 <span class='title'><?php echo xlt('Report'); ?> - <?php echo xlt('Receipts Summary'); ?></span>
 344
 345 <form method='post' action='receipts_by_method_report.php' id='theform' onsubmit='return top.restoreSession()'>
 346 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 347 <div id="report_parameters">
 348     <div class="form-row col-md-6">
 349         <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 350         <div class="form-group auto">
 351             <label for='form_report_by'><?php echo xlt('Report by'); ?></label>
 352             <?php echo " <select name='form_report_by' id='form_report_by' class='form-control'>\n";
 353             foreach (
 354                     array(
 355                         1 => 'Payer',
 356                         2 => 'Payment Method',
 357                         3 => 'Check Number'
 358                     ) as $key => $value
 359             ) {
 360                 echo "    <option value='" . attr($key) . "'";
 361                 if ($key == $form_report_by) {
 362                     echo ' selected';
 363                 }
 364
 365                 echo ">" . xlt($value) . "</option>\n";
 366             }
 367
 368                 echo "   </select>&nbsp;\n";
 369             ?>
 370         </div>
 371         <div class="form-group col-auto">
 372             <label for='form_facility'><?php echo xlt('Facility'); ?></label>
 373             <?php dropdown_facility($form_facility, 'form_facility', false); ?>
 374         </div>
 375     </div>
 376     <div class="form-row col-md-6">
 377         <div class="form-group col-auto">
 378             <label for='form_provider'><?php echo xlt('Provider'); ?></label>
 379             <?php echo xlt('Provider'); ?>:
 380                 <td>
 381                     <?php
 382                     if (AclMain::aclCheckCore('acct', 'rep_a')) {
 383                         // Build a drop-down list of providers.
 384                         //
 385                         $query = "select id, lname, fname from users where " .
 386                             "authorized = 1 order by lname, fname";
 387                         $res = sqlStatement($query);
 388                         echo "<select name='form_provider' class='form-control'>\n";
 389                         echo "    <option value=''>-- " . xlt('All Providers') . " --\n";
 390                         while ($row = sqlFetchArray($res)) {
 391                             $provid = $row['id'];
 392                             echo "    <option value='" . attr($provid) . "'";
 393                             if (!empty($_POST['form_provider']) && ($provid == $_POST['form_provider'])) {
 394                                 echo " selected";
 395                             }
 396
 397                             echo ">" . text($row['lname']) . ", " . text($row['fname']) . "\n";
 398                         }
 399
 400                         echo "   </select>\n";
 401                     } else {
 402                         echo "<input type='hidden' name='form_provider' value='" . attr($_SESSION['authUserID']) . "'>";
 403                     }
 404                     ?>
 405                 </td>
 406         </div>
 407         <div class="form-group col-auto">
 408             <label for="form_proc_codefull">
 409             <?php
 410             if (!$GLOBALS['simplified_demographics']) {
 411                 echo xlt('Procedure/Service');
 412             }
 413             ?>
 414             </label>
 415             <input type='text' name='form_proc_codefull' id='form_proc_codefull' class='form-control' size='12' value='<?php echo attr($form_proc_codefull); ?>' onclick='sel_procedure()'
 416                 title='<?php echo xla('Click to select optional procedure code'); ?>'
 417             <?php
 418             if ($GLOBALS['simplified_demographics']) {
 419                 echo "style='display:none'";
 420             } ?> />
 421         </div>
 422     </div>
 423     <div class="form-row col-md-6">
 424         <div class="form-group col-auto">
 425             <label for='form_use_edate'>
 426                 <select name='form_use_edate' class='form-control'>
 427                     <option value='0'><?php echo xlt('Payment Date'); ?></option>
 428                     <option value='1'<?php echo ($form_use_edate) ? ' selected' : ''; ?>><?php echo xlt('Invoice Date'); ?></option>
 429                 </select>
 430             </label>
 431         </div>
 432         <div class="form-group col-auto">
 433             <div class="form-check">
 434                 <input class="form-check-input" type='checkbox' name='form_details' value='1'<?php echo (!empty($_POST['form_details'])) ? " checked" : ""; ?> />
 435                 <label class="form-check-label">
 436                     <?php echo xlt('Details')?>
 437                 </label>
 438             </div>
 439         </div>
 440         <div class="form-group col-auto">
 441             <label for="form_from_date">
 442                 <?php echo xlt('From'); ?>
 443             </label>
 444             <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)); ?>'>
 445         </div>
 446         <div class="form-group col-auto">
 447             <label for="form_to_date">
 448                 <?php echo xlt('To{{Range}}'); ?>
 449             </label>
 450             <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)); ?>'>
 451         </div>
 452     </div>
 453     <div class="form-row col-md-2 mb-2">
 454         <div class="btn-group col-auto" role="group">
 455             <a href='#' class='btn btn-secondary btn-save' onclick='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
 456                 <?php echo xlt('Submit'); ?>
 457             </a>
 458             <?php if (!empty($_POST['form_refresh'])) { ?>
 459             <a href='#' class='btn btn-secondary btn-print' id='printbutton'>
 460                     <?php echo xlt('Print'); ?>
 461             </a>
 462             <?php } ?>
 463         </div>
 464     </div>
 465 </div> <!-- end of parameters -->
 466
 467 <?php
 468 if (!empty($_POST['form_refresh'])) {
 469     ?>
 470 <div id="report_results">
 471
 472 <table id='mymaintable' class='table table-hover'>
 473
 474 <thead class="thead-light">
 475     <tr>
 476         <th scope="col">
 477             <?php echo xlt('Method') ?>
 478         </th>
 479         <th scope ="col"><?php echo xlt('Reference') ?>
 480         </th>
 481         <th scope="col">
 482             <?php echo xlt('Date') ?>
 483         </th>
 484         <th scope="col">
 485             <?php echo xlt('Invoice') ?>
 486         </th>
 487             <?php if ($showing_ppd) { ?>
 488         <th scope="col">
 489                 <?php echo xlt('Patient')?>
 490         </th>
 491         <th scope="col">
 492                 <?php echo xlt('Policy')?>
 493         </th>
 494         <th scope="col">
 495                 <?php echo xlt('DOS')?>
 496         </th>
 497             <?php } ?>
 498         <th scope="col">
 499             <?php echo xlt('Procedure')?>
 500         </th>
 501         <th class="text-right" scope="col">
 502             <?php echo xlt('Adjustments')?>
 503         </th>
 504         <th class="text-right" scope="col"">
 505             <?php echo xlt('Payments')?>
 506         </th>
 507     </tr>
 508 </thead>
 509 <tbody>
 510     <?php
 511
 512     if ($_POST['form_refresh']) {
 513         $paymethod   = "";
 514         $paymethodleft = "";
 515         $methodpaytotal = 0;
 516         $grandpaytotal  = 0;
 517         $methodadjtotal  = 0;
 518         $grandadjtotal  = 0;
 519
 520         $form_provider = $_POST['form_provider'];
 521         if (!AclMain::aclCheckCore('acct', 'rep_a')) {
 522             // only allow user to see their encounter information
 523             $form_provider = $_SESSION['authUserID'];
 524         }
 525
 526
 527         // Get co-pays using the encounter date as the pay date.  These will
 528         // always be considered patient payments.  Ignored if selecting by
 529         // billing code.
 530         //
 531         if (!$form_proc_code || !$form_proc_codetype) {
 532             $sqlBindArray = array();
 533             $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, " .
 534             "fe.date, fe.facility_id, fe.invoice_refno, fe.provider_id " .
 535             "FROM billing AS b " .
 536             "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 537             "WHERE b.code_type = 'COPAY' AND b.activity = 1 AND b.fee != 0 AND " .
 538             "fe.date >= ? AND fe.date <= ?";
 539             array_push($sqlBindArray, $form_from_date . ' 00:00:00', $form_to_date . ' 23:59:59');
 540             // If a facility was specified.
 541             if ($form_facility) {
 542                 $query .= " AND fe.facility_id = ?";
 543                 array_push($sqlBindArray, $form_facility);
 544             }
 545
 546             // If a provider was specified.
 547             if ($form_provider) {
 548                 $query .= " AND fe.provider_id = ?";
 549                 array_push($sqlBindArray, $form_provider);
 550             }
 551
 552             $query .= " ORDER BY fe.date, b.pid, b.encounter, fe.id";
 553
 554             $res = sqlStatement($query, $sqlBindArray);
 555
 556             while ($row = sqlFetchArray($res)) {
 557                 $rowmethod = $form_report_by == 1 ? 'Patient' : 'Co-Pay';
 558                 thisLineItem(
 559                     $row['pid'],
 560                     $row['encounter'],
 561                     $row['code_text'],
 562                     substr($row['date'], 0, 10),
 563                     $rowmethod,
 564                     0 - $row['fee'],
 565                     0,
 566                     0,
 567                     $row['invoice_refno']
 568                 );
 569             }
 570         } // end if not form_proc_code
 571
 572         // Get all other payments and adjustments and their dates, corresponding
 573         // payers and check reference data, and the encounter dates separately.
 574         //
 575         $sqlBindArray = array();
 576         $query = "SELECT a.pid, a.encounter, a.post_time, a.pay_amount, " .
 577           "a.adj_amount, a.memo, a.session_id, a.code, a.payer_type, fe.id, fe.date, fe.provider_id, fe.id, " .
 578           "fe.invoice_refno, s.deposit_date, s.payer_id, s.reference, s.payment_method, i.name " .
 579           "FROM ar_activity AS a " .
 580           "JOIN form_encounter AS fe ON fe.pid = a.pid AND fe.encounter = a.encounter " .
 581           "JOIN forms AS f ON f.pid = a.pid AND f.encounter = a.encounter AND f.formdir = 'newpatient' " .
 582           "LEFT JOIN ar_session AS s ON s.session_id = a.session_id " .
 583           "LEFT JOIN insurance_companies AS i ON i.id = s.payer_id " .
 584           "LEFT OUTER JOIN billing AS b ON b.pid = a.pid AND b.encounter = a.encounter AND " .
 585           "b.code = a.code AND b.modifier = a.modifier AND b.activity = 1 AND " .
 586           "b.code_type != 'COPAY' AND b.code_type != 'TAX' " .
 587           "WHERE a.deleted IS NULL AND (a.pay_amount != 0 OR a.adj_amount != 0)";
 588         //
 589         if ($form_use_edate) {
 590             $query .= " AND fe.date >= ? AND fe.date <= ?";
 591             array_push($sqlBindArray, $form_from_date . ' 00:00:00', $form_to_date . ' 23:59:59');
 592         } else {
 593             $query .= " AND ( ( s.deposit_date IS NOT NULL AND " .
 594             "s.deposit_date >= ? AND s.deposit_date <= ? ) OR " .
 595             "( s.deposit_date IS NULL AND a.post_time >= ? AND " .
 596             "a.post_time <= ? ) )";
 597             array_push($sqlBindArray, $form_from_date, $form_to_date, $form_from_date . ' 00:00:00', $form_to_date . ' 23:59:59');
 598         }
 599
 600         // If a procedure code was specified.
 601         if ($form_proc_code && $form_proc_codetype) {
 602           // if a code_type is entered into the ar_activity table, then use it. If it is not entered in, then do not use it.
 603             $query .= " AND ( a.code_type = ? OR a.code_type = '' ) AND a.code LIKE ?";
 604             array_push($sqlBindArray, $form_proc_codetype, $form_proc_code . '%');
 605         }
 606
 607         // If a facility was specified.
 608         if ($form_facility) {
 609             $query .= " AND fe.facility_id = ?";
 610             array_push($sqlBindArray, $form_facility);
 611         }
 612
 613         // If a provider was specified.
 614         if ($form_provider) {
 615             $query .= " AND ( b.provider_id = ? OR " .
 616             "( ( b.provider_id IS NULL OR b.provider_id = 0 ) AND " .
 617             "fe.provider_id = ? ) )";
 618             array_push($sqlBindArray, $form_provider, $form_provider);
 619         }
 620
 621         //
 622         if ($form_use_edate) {
 623             $query .= " ORDER BY s.reference, fe.date, a.pid, a.encounter, fe.id";
 624         } else {
 625             $query .= " ORDER BY s.payment_method, s.deposit_date, a.post_time, a.pid, a.encounter, fe.id";
 626         }
 627
 628         //
 629         $res = sqlStatement($query, $sqlBindArray);
 630         while ($row = sqlFetchArray($res)) {
 631             if ($form_use_edate) {
 632                 $thedate = substr($row['date'], 0, 10);
 633             } elseif (!empty($row['deposit_date'])) {
 634                 $thedate = $row['deposit_date'];
 635             } else {
 636                 $thedate = substr($row['post_time'], 0, 10);
 637             }
 638
 639           // Compute reporting key: insurance company name or payment method.
 640             if ($form_report_by == '1') {
 641                 if (empty($row['payer_id'])) {
 642                     // 'ar_session' is not capturing payer_id when entering payments through invoice or era posting
 643                     if ($row['payer_type'] == '1') {
 644                         $insurance_id = (new InsuranceService())->getOneByPid($row['pid'], "primary");
 645                     } elseif ($row['payer_type'] == '2') {
 646                         $insurance_id = (new InsuranceService())->getOneByPid($row['pid'], "secondary");
 647                     } elseif ($row['payer_type'] == '3') {
 648                         $insurance_id = (new InsuranceService())->getOneByPid($row['pid'], "tertiary");
 649                     } elseif ($row['payer_type'] == '0') {
 650                         $rowmethod = xl('Personal pay');
 651                         $rowreference = trim($row['reference']);
 652                     } else {
 653                         $rowmethod = xl('Unnamed insurance company');
 654                     }
 655                     if (!empty($insurance_id['provider'])) {
 656                         $insurance_company = (new InsuranceCompanyService())->getOneById($insurance_id['provider']) ?? '';
 657                         $rowmethod = xl($insurance_company['name']);
 658                     } elseif (!($row['payer_type'] == '0')) {
 659                         $rowmethod = xl('Unnamed insurance company');
 660                     }
 661                 } else {
 662                     $rowmethod = $row['name'];
 663                 }
 664             } else {
 665                 if (empty($row['session_id'])) {
 666                     $rowmethod = trim($row['memo']);
 667                 } else {
 668                     $rowmethod = trim(getListItemTitle('payment_method', $row['payment_method']));
 669                     $rowreference = trim($row['reference']);
 670                 }
 671             }
 672
 673             thisLineItem(
 674                 $row['pid'],
 675                 $row['encounter'],
 676                 ($rowreference ?? ''),
 677                 $thedate,
 678                 $rowmethod,
 679                 $row['pay_amount'],
 680                 $row['adj_amount'],
 681                 $row['payer_type'],
 682                 $row['invoice_refno']
 683             );
 684         }
 685
 686       // Not payer summary.
 687         if ($form_report_by != '1' || !empty($_POST['form_details'])) {
 688             if ($form_report_by == '1') { // by payer with details
 689                 // Sort and dump saved info, and consolidate items with all key
 690                 // fields being the same.
 691                 usort($insarray, 'payerCmp');
 692                 $b = array();
 693                 foreach ($insarray as $a) {
 694                     if (empty($a[4])) {
 695                         $a[4] = xl('Patient');
 696                     }
 697
 698                     if (empty($b)) {
 699                         $b = $a;
 700                     } else {
 701                         $match = true;
 702                         foreach (array(4,3,0,1,2,7) as $i) {
 703                             if ($a[$i] != $b[$i]) {
 704                                 $match = false;
 705                             }
 706                         }
 707
 708                         if ($match) {
 709                             $b[5] += $a[5];
 710                             $b[6] += $a[6];
 711                         } else {
 712                             showLineItem($b[0], $b[1], $b[2], $b[3], $b[4], $b[5], $b[6], $b[7], $b[8]);
 713                             $b = $a;
 714                         }
 715                     }
 716                 }
 717
 718                 if (!empty($b)) {
 719                     showLineItem($b[0], $b[1], $b[2], $b[3], $b[4], $b[5], $b[6], $b[7], $b[8]);
 720                 }
 721             } // end by payer with details
 722
 723             // Print last method total.
 724             ?>
 725             <tr class="table-secondary" scope="row">
 726                 <td colspan="<?php echo $showing_ppd ? 8 : 4; ?>">
 727                     <?php echo xlt('Total for ') . text($paymethod); ?>
 728                 </td>
 729                 <td class="text-right">
 730                     <?php echo text(FormatMoney::getBucks($methodadjtotal)); ?>
 731                 </td>
 732                 <td class="text-right">
 733                     <?php echo text(FormatMoney::getBucks($methodpaytotal)); ?>
 734                 </td>
 735             </tr>
 736             <?php
 737         } else { // Payer summary: need to sort and then print it all.
 738             ksort($insarray);
 739             foreach ($insarray as $key => $value) {
 740                 if (empty($key)) {
 741                     $key = xl('Patient');
 742                 }
 743                 ?>
 744                 <tr>
 745                     <td colspan="<?php echo $showing_ppd ? 8 : 4; ?>">
 746                         <?php echo text($key); ?>
 747                     </td>
 748                     <td class="text-right">
 749                         <?php echo text(FormatMoney::getBucks($value[1])); ?>
 750                     </td>
 751                     <td class="text-right">
 752                         <?php echo text(FormatMoney::getBucks($value[0])); ?>
 753                     </td>
 754                 </tr>
 755                 <?php
 756             } // end foreach
 757         } // end payer summary
 758         ?>
 759         <tr class="table-info">
 760             <td colspan="<?php echo $showing_ppd ? 8 : 4; ?>">
 761                 <?php echo xlt('Grand Total') ?>
 762             </td>
 763             <td class="text-right">
 764                 <?php echo text(FormatMoney::getBucks($grandadjtotal)); ?>
 765             </td>
 766             <td class="text-right">
 767                 <?php echo text(FormatMoney::getBucks($grandpaytotal)); ?>
 768             </td>
 769         </tr>
 770
 771         <?php
 772     } // end form refresh
 773     ?>
 774
 775 </tbody>
 776 </table>
 777 </div>
 778 <?php } else { ?>
 779 <div class='text'>
 780     <?php echo xlt('Please input search criteria above, and click Submit to view results.'); ?>
 781 </div>
 782 <?php } ?>
 783
 784 </form>
 785 </body>
 786
 787 </html>