interface/reports/insurance_allocation_report.php

   1 <?php
   2
   3 /**
   4  * This module shows relative insurance usage by unique patients
   5  * that are seen within a given time period.  Each patient that had
   6  * a visit is counted only once, regardless of how many visits.
   7  *
   8  * @package   OpenEMR
   9  * @link      http://www.open-emr.org
  10  * @author    Brady Miller <brady.g.miller@gmail.com>
  11  * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
  12  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  13  */
  14
  15 require_once("../globals.php");
  16 require_once("../../library/patient.inc.php");
  17
  18 use OpenEMR\Common\Acl\AclMain;
  19 use OpenEMR\Common\Csrf\CsrfUtils;
  20 use OpenEMR\Common\Twig\TwigContainer;
  21 use OpenEMR\Core\Header;
  22
  23 if (!AclMain::aclCheckCore('acct', 'rep_a')) {
  24     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Patient Insurance Distribution")]);
  25     exit;
  26 }
  27
  28 if (!empty($_POST)) {
  29     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  30         CsrfUtils::csrfNotVerified();
  31     }
  32 }
  33
  34 $form_from_date = (!empty($_POST['form_from_date'])) ?  DateToYYYYMMDD($_POST['form_from_date']) : '';
  35 $form_to_date   = (!empty($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
  36
  37 if (!empty($_POST['form_csvexport'])) {
  38     header("Pragma: public");
  39     header("Expires: 0");
  40     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  41     header("Content-Type: application/force-download");
  42     header("Content-Disposition: attachment; filename=insurance_distribution.csv");
  43     header("Content-Description: File Transfer");
  44     // CSV headers:
  45     if (true) {
  46         echo csvEscape("Insurance") . ',';
  47         echo csvEscape("Charges") . ',';
  48         echo csvEscape("Visits") . ',';
  49         echo csvEscape("Patients") . ',';
  50         echo csvEscape("Pt Pct") . "\n";
  51     }
  52 } else {
  53     ?>
  54 <html>
  55 <head>
  56
  57 <title><?php echo xlt('Patient Insurance Distribution'); ?></title>
  58
  59     <?php Header::setupHeader('datetime-picker'); ?>
  60
  61 <script>
  62     $(function () {
  63     var win = top.printLogSetup ? top : opener.top;
  64     win.printLogSetup(document.getElementById('printbutton'));
  65
  66     $('.datepicker').datetimepicker({
  67         <?php $datetimepicker_timepicker = false; ?>
  68         <?php $datetimepicker_showseconds = false; ?>
  69         <?php $datetimepicker_formatInput = true; ?>
  70         <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
  71         <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
  72         });
  73     });
  74 </script>
  75
  76 <style>
  77
  78 /* specifically include & exclude from printing */
  79 @media print {
  80     #report_parameters {
  81         visibility: hidden;
  82         display: none;
  83     }
  84     #report_parameters_daterange {
  85         visibility: visible;
  86         display: inline;
  87     }
  88     #report_results table {
  89        margin-top: 0px;
  90     }
  91 }
  92
  93 /* specifically exclude some from the screen */
  94 @media screen {
  95     #report_parameters_daterange {
  96         visibility: hidden;
  97         display: none;
  98     }
  99 }
 100
 101 </style>
 102 </head>
 103
 104 <body class="body_top">
 105
 106 <!-- Required for the popup date selectors -->
 107 <div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
 108
 109 <span class='title'><?php echo xlt('Report'); ?> - <?php echo xlt('Patient Insurance Distribution'); ?></span>
 110
 111 <div id="report_parameters_daterange">
 112     <?php echo text(oeFormatShortDate($form_from_date)) . " &nbsp; " . xlt("to{{Range}}") . " &nbsp; " . text(oeFormatShortDate($form_to_date)); ?>
 113 </div>
 114
 115 <form name='theform' method='post' action='insurance_allocation_report.php' id='theform' onsubmit='return top.restoreSession()'>
 116 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 117
 118 <div id="report_parameters">
 119 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 120 <input type='hidden' name='form_csvexport' id='form_csvexport' value=''/>
 121
 122 <table>
 123  <tr>
 124   <td width='410px'>
 125     <div style='float:left'>
 126
 127     <table class='text'>
 128         <tr>
 129             <td class='col-form-label'>
 130                 <?php echo xlt('From'); ?>:
 131             </td>
 132             <td>
 133                <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)); ?>'>
 134             </td>
 135             <td class='col-form-label'>
 136                 <?php echo xlt('To{{Range}}'); ?>:
 137             </td>
 138             <td>
 139                <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)); ?>'>
 140             </td>
 141         </tr>
 142     </table>
 143
 144     </div>
 145
 146   </td>
 147   <td class='h-100' valign='middle'>
 148     <table class='w-100 h-100' style='border-left:1px solid;'>
 149         <tr>
 150             <td>
 151                 <div class="text-center">
 152           <div class="btn-group" role="group">
 153                       <a href='#' class='btn btn-secondary btn-save' onclick='$("#form_refresh").attr("value","true"); $("#form_csvexport").val(""); $("#theform").submit();'>
 154                             <?php echo xlt('Submit'); ?>
 155                       </a>
 156                         <?php if (!empty($_POST['form_refresh'])) { ?>
 157                         <a href='#' class='btn btn-secondary btn-print' id='printbutton'>
 158                                 <?php echo xlt('Print'); ?>
 159                         </a>
 160                         <a href='#' class='btn btn-secondary btn-transmit' onclick='$("#form_csvexport").attr("value","true"); $("#theform").submit();'>
 161                             <?php echo xlt('Export to CSV'); ?>
 162                         </a>
 163                         <?php } ?>
 164           </div>
 165                 </div>
 166             </td>
 167         </tr>
 168     </table>
 169   </td>
 170  </tr>
 171 </table>
 172
 173 </form>
 174 </div> <!-- end parameters -->
 175
 176 <div id="report_results">
 177 <table class='table'>
 178
 179  <thead class='thead-light'>
 180  <th> <?php echo xlt('Primary Insurance'); ?> </th>
 181  <th> <?php echo xlt('Charges'); ?> </th>
 182  <th> <?php echo xlt('Visits'); ?> </th>
 183  <th> <?php echo xlt('Patients'); ?> </th>
 184  <th> <?php echo xlt('Pt %'); ?> </th>
 185  </thead>
 186  <tbody>
 187     <?php
 188 } // end not export
 189 if (!empty($_POST['form_refresh']) || !empty($_POST['form_csvexport'])) {
 190     $query = "SELECT b.pid, b.encounter, SUM(b.fee) AS charges, " .
 191     "MAX(fe.date) AS date " .
 192     "FROM form_encounter AS fe, billing AS b " .
 193     "WHERE fe.date >= ? AND fe.date <= ? " .
 194     "AND b.pid = fe.pid AND b.encounter = fe.encounter " .
 195     "AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 " .
 196     "GROUP BY b.pid, b.encounter ORDER BY b.pid, b.encounter";
 197
 198     $res = sqlStatement($query, array((!empty($form_from_date)) ? $form_from_date : '0000-00-00', $form_to_date));
 199     $insarr = array();
 200     $prev_pid = 0;
 201     $patcount = 0;
 202
 203     while ($row = sqlFetchArray($res)) {
 204         $patient_id = $row['pid'];
 205         $encounter_date = $row['date'];
 206         $irow = sqlQuery("SELECT insurance_companies.name " .
 207         "FROM insurance_data, insurance_companies WHERE " .
 208         "insurance_data.pid = ? AND " .
 209         "insurance_data.type = 'primary' AND " .
 210         "(insurance_data.date <= ? OR insurance_data.date IS NULL) AND " .
 211         "insurance_companies.id = insurance_data.provider " .
 212         "ORDER BY insurance_data.date DESC LIMIT 1", array($patient_id, $encounter_date));
 213         $plan = (!empty($irow['name'])) ? $irow['name'] : '-- No Insurance --';
 214         $insarr[$plan]['visits'] = $insarr[$plan]['visits'] ?? null;
 215         $insarr[$plan]['visits'] += 1;
 216         $insarr[$plan]['charges'] = $insarr[$plan]['charges'] ?? null;
 217         $insarr[$plan]['charges'] += sprintf('%0.2f', $row['charges']);
 218         if ($patient_id != $prev_pid) {
 219             ++$patcount;
 220             $insarr[$plan]['patients'] =  $insarr[$plan]['patients'] ?? null;
 221             $insarr[$plan]['patients'] += 1;
 222             $prev_pid = $patient_id;
 223         }
 224     }
 225
 226     ksort($insarr);
 227
 228     foreach ($insarr as $key => $val) {
 229         if ($_POST['form_csvexport']) {
 230             echo csvEscape($key)                                                . ',';
 231             echo csvEscape(oeFormatMoney($val['charges']))                      . ',';
 232             echo csvEscape($val['visits'])                                      . ',';
 233             echo csvEscape($val['patients'])                                    . ',';
 234             echo csvEscape(sprintf("%.1f", $val['patients'] * 100 / $patcount)) . "\n";
 235         } else {
 236             ?>
 237      <tr>
 238       <td>
 239             <?php echo text($key); ?>
 240   </td>
 241   <td>
 242             <?php echo text(oeFormatMoney($val['charges'])); ?>
 243   </td>
 244   <td>
 245             <?php echo text($val['visits']); ?>
 246   </td>
 247   <td>
 248             <?php echo text($val['patients']); ?>
 249   </td>
 250   <td>
 251             <?php printf("%.1f", $val['patients'] * 100 / $patcount) ?>
 252   </td>
 253  </tr>
 254             <?php
 255         } // end not export
 256     } // end while
 257 } // end if
 258
 259 if (empty($_POST['form_csvexport'])) {
 260     ?>
 261
 262 </tbody>
 263 </table>
 264 </div> <!-- end of results -->
 265
 266 </body>
 267
 268 </html>
 269     <?php
 270 } // end not export
 271 ?>