interface/reports/svc_code_financial_report.php

   1 <?php
   2
   3 /**
   4  * This is a report of Financial Summary by Service Code.
   5  *
   6  * This is a summary of service code charge/pay/adjust and balance,
   7  * with the ability to pick "important" codes to either highlight or
   8  * limit to list to. Important codes can be configured in
   9  * Administration->Service section by assigning code with
  10  * 'Service Reporting'.
  11  *
  12  * @package   OpenEMR
  13  * @link      https://www.open-emr.org
  14  * @author    Rod Roark <rod@sunsetsystems.com>
  15  * @author    Visolve
  16  * @author    Brady Miller <brady.g.miller@gmail.com>
  17  * @copyright Copyright (C) 2006-2020 Rod Roark <rod@sunsetsystems.com>
  18  * @copyright Copyright (c) 2017-2018 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 require_once("../globals.php");
  23 require_once("$srcdir/patient.inc.php");
  24 require_once "$srcdir/options.inc.php";
  25 require_once "$srcdir/appointments.inc.php";
  26
  27 use OpenEMR\Common\Acl\AclMain;
  28 use OpenEMR\Common\Csrf\CsrfUtils;
  29 use OpenEMR\Common\Twig\TwigContainer;
  30 use OpenEMR\Core\Header;
  31
  32 if (!AclMain::aclCheckCore('acct', 'rep_a')) {
  33     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Financial Summary by Service Code")]);
  34     exit;
  35 }
  36
  37 if (!empty($_POST)) {
  38     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  39         CsrfUtils::csrfNotVerified();
  40     }
  41 }
  42
  43 $grand_total_units  = 0;
  44 $grand_total_amt_billed  = 0;
  45 $grand_total_amt_paid  = 0;
  46 $grand_total_amt_adjustment  = 0;
  47 $grand_total_amt_balance  = 0;
  48
  49 $form_from_date = (isset($_POST['form_from_date'])) ? DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
  50 $form_to_date   = (isset($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
  51 $form_facility  = $_POST['form_facility'] ?? null;
  52 $form_provider  = $_POST['form_provider'] ?? null;
  53
  54 if (!empty($_POST['form_csvexport'])) {
  55     header("Pragma: public");
  56     header("Expires: 0");
  57     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  58     header("Content-Type: application/force-download");
  59     header("Content-Disposition: attachment; filename=svc_financial_report_" . attr($form_from_date) . "--" . attr($form_to_date) . ".csv");
  60     header("Content-Description: File Transfer");
  61     // CSV headers:
  62 } else { // end export
  63     ?>
  64 <html>
  65 <head>
  66     <title><?php echo xlt('Financial Summary by Service Code') ?></title>
  67
  68     <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
  69
  70     <style>
  71         /* specifically include & exclude from printing */
  72         @media print {
  73             #report_parameters {
  74                 visibility: hidden;
  75                 display: none;
  76             }
  77             #report_parameters_daterange {
  78                 visibility: visible;
  79                 display: inline;
  80             }
  81             #report_results {
  82                 margin-top: 30px;
  83             }
  84         }
  85
  86         /* specifically exclude some from the screen */
  87         @media screen {
  88             #report_parameters_daterange {
  89                 visibility: hidden;
  90                 display: none;
  91             }
  92         }
  93     </style>
  94
  95     <script>
  96         $(function () {
  97             oeFixedHeaderSetup(document.getElementById('mymaintable'));
  98             var win = top.printLogSetup ? top : opener.top;
  99             win.printLogSetup(document.getElementById('printbutton'));
 100
 101             $('.datepicker').datetimepicker({
 102                 <?php $datetimepicker_timepicker = false; ?>
 103                 <?php $datetimepicker_showseconds = false; ?>
 104                 <?php $datetimepicker_formatInput = true; ?>
 105                 <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
 106                 <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
 107             });
 108         });
 109     </script>
 110 </head>
 111
 112 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class="body_top">
 113 <span class='title'><?php echo xlt('Report'); ?> - <?php echo xlt('Financial Summary by Service Code'); ?></span>
 114 <form method='post' action='svc_code_financial_report.php' id='theform' onsubmit='return top.restoreSession()'>
 115 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 116 <div id="report_parameters">
 117 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 118 <input type='hidden' name='form_csvexport' id='form_csvexport' value=''/>
 119 <table>
 120 <tr>
 121 <td width='70%'>
 122   <div style='float:left'>
 123   <table class='text'>
 124       <tr>
 125           <td class='col-form-label'>
 126             <?php echo xlt('Facility'); ?>:
 127           </td>
 128           <td>
 129         <?php dropdown_facility($form_facility, 'form_facility', true); ?>
 130           </td>
 131                     <td class='col-form-label'><?php echo xlt('Provider'); ?>:</td>
 132             <td><?php
 133                     // Build a drop-down list of providers.
 134                             //
 135                             $query = "SELECT id, lname, fname FROM users WHERE " .
 136                               "authorized = 1 ORDER BY lname, fname"; //(CHEMED) facility filter
 137                             $ures = sqlStatement($query);
 138                             echo "   <select name='form_provider' class='form-control'>\n";
 139                             echo "    <option value=''>-- " . xlt('All') . " --\n";
 140             while ($urow = sqlFetchArray($ures)) {
 141                 $provid = $urow['id'];
 142                 echo "    <option value='" . attr($provid) . "'";
 143                 if (!empty($_POST['form_provider']) && ($provid == $_POST['form_provider'])) {
 144                     echo " selected";
 145                 }
 146
 147                 echo ">" . text($urow['lname']) . ", " . text($urow['fname']) . "\n";
 148             }
 149
 150                             echo "   </select>\n";
 151             ?>
 152                 </td>
 153         </tr><tr>
 154                  <td class='col-form-label'>
 155                             <?php echo xlt('From'); ?>:&nbsp;&nbsp;&nbsp;&nbsp;
 156                           </td>
 157                           <td>
 158                            <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)); ?>'>
 159                         </td>
 160                         <td class='col-form-label'>
 161                             <?php echo xlt('To{{Range}}'); ?>:
 162                         </td>
 163                         <td>
 164                            <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)); ?>'>
 165                         </td>
 166                         <td>
 167                           <div class="checkbox">
 168                            <label><input type='checkbox' name='form_details'<?php
 169                             if (!empty($_POST['form_details'])) {
 170                                     echo ' checked';
 171                             } ?>>
 172                             <?php echo xlt('Important Codes'); ?></label>
 173                           </div>
 174                         </td>
 175         </tr>
 176     </table>
 177     </div>
 178   </td>
 179   <td class='h-100' align='left' valign='middle'>
 180     <table class='w-100 h-100' style='border-left:1px solid;'>
 181         <tr>
 182             <td>
 183                 <div class="text-center">
 184           <div class="btn-group" role="group">
 185                       <a href='#' class='btn btn-secondary btn-save' onclick='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
 186                             <?php echo xlt('Submit'); ?>
 187                       </a>
 188                         <?php if (!empty($_POST['form_refresh']) || !empty($_POST['form_csvexport'])) { ?>
 189                         <a href='#' class='btn btn-secondary btn-print' id='printbutton'>
 190                                 <?php echo xlt('Print'); ?>
 191                         </a>
 192                         <a href='#' class='btn btn-secondary btn-transmit' onclick='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
 193                                 <?php echo xlt('CSV Export'); ?>
 194                         </a>
 195                         <?php } ?>
 196           </div>
 197                 </div>
 198             </td>
 199         </tr>
 200     </table>
 201   </td>
 202  </tr>
 203 </table>
 204 </div> <!-- end of parameters -->
 205
 206     <?php
 207 }
 208
 209    // end not export
 210
 211 if (!empty($_POST['form_refresh']) || !empty($_POST['form_csvexport'])) {
 212     $rows = array();
 213     $from_date = $form_from_date;
 214     $to_date   = $form_to_date;
 215     $sqlBindArray = array();
 216     $query = "select b.code,sum(b.units) as units,sum(b.fee) as billed,sum(ar_act.paid) as PaidAmount, " .
 217     "sum(ar_act.adjust) as AdjustAmount,(sum(b.fee)-(sum(ar_act.paid)+sum(ar_act.adjust))) as Balance, " .
 218     "c.financial_reporting " .
 219     "FROM form_encounter as fe " .
 220     "JOIN billing as b on b.pid=fe.pid and b.encounter=fe.encounter " .
 221     "JOIN (select pid, encounter, code, sum(pay_amount) as paid, sum(adj_amount) as adjust " .
 222     "from ar_activity WHERE deleted IS NULL group by pid, encounter, code) as ar_act " .
 223     "ON ar_act.pid=b.pid and ar_act.encounter=b.encounter and ar_act.code=b.code " .
 224     "LEFT OUTER JOIN codes AS c ON c.code = b.code " .
 225     "INNER JOIN code_types AS ct ON ct.ct_key = b.code_type AND ct.ct_fee = '1' " .
 226     "WHERE b.code_type != 'COPAY' AND b.activity = 1 /* AND b.fee != 0 */ AND " .
 227     "fe.date >=  ? AND fe.date <= ?";
 228     array_push($sqlBindArray, "$from_date 00:00:00", "$to_date 23:59:59");
 229     // If a facility was specified.
 230     if ($form_facility) {
 231         $query .= " AND fe.facility_id = ?";
 232         array_push($sqlBindArray, $form_facility);
 233     }
 234
 235     // If a provider was specified.
 236     if ($form_provider) {
 237         $query .= " AND b.provider_id = ?";
 238         array_push($sqlBindArray, $form_provider);
 239     }
 240
 241     // If selected important codes
 242     if (!empty($_POST['form_details'])) {
 243         $query .= " AND c.financial_reporting = '1'";
 244     }
 245
 246     $query .= " GROUP BY b.code ORDER BY b.code, fe.date, fe.id ";
 247     $res = sqlStatement($query, $sqlBindArray);
 248     $grand_total_units  = 0;
 249     $grand_total_amt_billed  = 0;
 250     $grand_total_amt_paid  = 0;
 251     $grand_total_amt_adjustment  = 0;
 252     $grand_total_amt_balance  = 0;
 253
 254     while ($erow = sqlFetchArray($res)) {
 255         $row = array();
 256         $row['pid'] = $erow['pid'] ?? null;
 257         $row['provider_id'] = $erow['provider_id'] ?? null;
 258         $row['Procedure codes'] = $erow['code'];
 259         $row['Units'] = $erow['units'];
 260         $row['Amt Billed'] = $erow['billed'];
 261         $row['Paid Amt'] = $erow['PaidAmount'];
 262         $row['Adjustment Amt'] = $erow['AdjustAmount'];
 263         $row['Balance Amt'] = $erow['Balance'];
 264         $row['financial_reporting'] = $erow['financial_reporting'];
 265         $rows[($erow['pid'] ?? null) . '|' . $erow['code'] . '|' . $erow['units']] = $row;
 266     }
 267
 268     if ($_POST['form_csvexport']) {
 269       // CSV headers:
 270         if (true) {
 271             echo csvEscape("Procedure codes") . ',';
 272             echo csvEscape("Units") . ',';
 273             echo csvEscape("Amt Billed") . ',';
 274             echo csvEscape("Paid Amt") . ',';
 275             echo csvEscape("Adjustment Amt") . ',';
 276             echo csvEscape("Balance Amt") . "\n";
 277         }
 278     } else {
 279         ?>
 280 <div id="report_results">
 281 <table class='table' id='mymaintable'>
 282 <thead class='thead-light'>
 283 <th>
 284         <?php echo xlt('Procedure Codes'); ?>
 285 </th>
 286 <th >
 287         <?php echo xlt('Units'); ?>
 288 </th>
 289 <th>
 290         <?php echo xlt('Amt Billed'); ?>
 291 </th>
 292 <th>
 293         <?php echo xlt('Paid Amt'); ?>
 294 </th>
 295 <th >
 296         <?php echo xlt('Adjustment Amt'); ?>
 297 </th>
 298 <th >
 299         <?php echo xlt('Balance Amt'); ?>
 300 </th>
 301 </thead>
 302         <?php
 303     }
 304
 305             $orow = -1;
 306
 307     foreach ($rows as $key => $row) {
 308         $print = '';
 309         $csv = '';
 310
 311         if ($row['financial_reporting']) {
 312             $bgcolor = "#FFFFDD";
 313         } else {
 314             $bgcolor = "#FFDDDD";
 315         }
 316
 317         $print = "<tr bgcolor='" . attr($bgcolor) . "'><td class='detail'>" . text($row['Procedure codes']) . "</td><td class='detail'>" . text($row['Units']) . "</td><td class='detail'>" . text(oeFormatMoney($row['Amt Billed'])) . "</td><td class='detail'>" . text(oeFormatMoney($row['Paid Amt'])) . "</td><td class='detail'>" . text(oeFormatMoney($row['Adjustment Amt'])) . "</td><td class='detail'>" . text(oeFormatMoney($row['Balance Amt'])) . "</td>";
 318
 319         $csv = csvEscape($row['Procedure codes']) . ',' . csvEscape($row['Units']) . ',' . csvEscape(oeFormatMoney($row['Amt Billed'])) . ',' . csvEscape(oeFormatMoney($row['Paid Amt'])) . ',' . csvEscape(oeFormatMoney($row['Adjustment Amt'])) . ',' . csvEscape(oeFormatMoney($row['Balance Amt'])) . "\n";
 320
 321         $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
 322                        $grand_total_units  += $row['Units'];
 323                                        $grand_total_amt_billed  += $row['Amt Billed'];
 324                                        $grand_total_amt_paid  += $row['Paid Amt'];
 325                                        $grand_total_amt_adjustment  += $row['Adjustment Amt'];
 326                                        $grand_total_amt_balance  += $row['Balance Amt'];
 327
 328         if ($_POST['form_csvexport']) {
 329             echo $csv;
 330         } else {
 331             echo $print;
 332         }
 333     }
 334
 335     if (!$_POST['form_csvexport']) {
 336         echo "<tr class='bg-white'>\n";
 337         echo " <td class='detail'>" . xlt("Grand Total") . "</td>\n";
 338         echo " <td class='detail'>" . text($grand_total_units) . "</td>\n";
 339         echo " <td class='detail'>" .
 340         text(oeFormatMoney($grand_total_amt_billed)) . "</td>\n";
 341         echo " <td class='detail'>" .
 342         text(oeFormatMoney($grand_total_amt_paid)) . "</td>\n";
 343         echo " <td class='detail'>" .
 344         text(oeFormatMoney($grand_total_amt_adjustment)) . "</td>\n";
 345         echo " <td class='detail'>" .
 346         text(oeFormatMoney($grand_total_amt_balance)) . "</td>\n";
 347         echo " </tr>\n";
 348         ?>
 349      </table>    </div>
 350                 <?php
 351     }
 352 }
 353
 354 if (empty($_POST['form_csvexport'])) {
 355     if (!empty($_POST['form_refresh']) && empty($print)) {
 356         echo "<span style='font-size:10pt;'>";
 357            echo xlt('No matches found. Try search again.');
 358            echo "</span>";
 359         echo '<script>document.getElementById("report_results").style.display="none";</script>';
 360     }
 361
 362     if (empty($_POST['form_refresh']) && empty($_POST['form_csvexport'])) { ?>
 363         <div class='text'>
 364         <?php echo xlt('Please input search criteria above, and click Submit to view results.'); ?>
 365         </div><?php
 366     } ?>
 367 </form>
 368 </body>
 369
 370 </html>
 371     <?php
 372 } // End not csv export
 373 ?>