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");
  17
  18 use OpenEMR\Common\Csrf\CsrfUtils;
  19 use OpenEMR\Core\Header;
  20
  21 if (!empty($_POST)) {
  22     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  23         CsrfUtils::csrfNotVerified();
  24     }
  25 }
  26
  27 $form_from_date = (!empty($_POST['form_from_date'])) ?  DateToYYYYMMDD($_POST['form_from_date']) : '';
  28 $form_to_date   = (!empty($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
  29
  30 if (!empty($_POST['form_csvexport'])) {
  31     header("Pragma: public");
  32     header("Expires: 0");
  33     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  34     header("Content-Type: application/force-download");
  35     header("Content-Disposition: attachment; filename=insurance_distribution.csv");
  36     header("Content-Description: File Transfer");
  37     // CSV headers:
  38     if (true) {
  39         echo csvEscape("Insurance") . ',';
  40         echo csvEscape("Charges") . ',';
  41         echo csvEscape("Visits") . ',';
  42         echo csvEscape("Patients") . ',';
  43         echo csvEscape("Pt Pct") . "\n";
  44     }
  45 } else {
  46     ?>
  47 <html>
  48 <head>
  49
  50 <title><?php echo xlt('Patient Insurance Distribution'); ?></title>
  51
  52     <?php Header::setupHeader('datetime-picker'); ?>
  53
  54 <script>
  55     $(function () {
  56     var win = top.printLogSetup ? top : opener.top;
  57     win.printLogSetup(document.getElementById('printbutton'));
  58
  59     $('.datepicker').datetimepicker({
  60         <?php $datetimepicker_timepicker = false; ?>
  61         <?php $datetimepicker_showseconds = false; ?>
  62         <?php $datetimepicker_formatInput = true; ?>
  63         <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
  64         <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
  65         });
  66     });
  67 </script>
  68
  69 <style>
  70
  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 table {
  82        margin-top: 0px;
  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
  94 </style>
  95 </head>
  96
  97 <body class="body_top">
  98
  99 <!-- Required for the popup date selectors -->
 100 <div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
 101
 102 <span class='title'><?php echo xlt('Report'); ?> - <?php echo xlt('Patient Insurance Distribution'); ?></span>
 103
 104 <div id="report_parameters_daterange">
 105     <?php echo text(oeFormatShortDate($form_from_date)) . " &nbsp; " . xlt("to{{Range}}") . " &nbsp; " . text(oeFormatShortDate($form_to_date)); ?>
 106 </div>
 107
 108 <form name='theform' method='post' action='insurance_allocation_report.php' id='theform' onsubmit='return top.restoreSession()'>
 109 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 110
 111 <div id="report_parameters">
 112 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 113 <input type='hidden' name='form_csvexport' id='form_csvexport' value=''/>
 114
 115 <table>
 116  <tr>
 117   <td width='410px'>
 118     <div style='float:left'>
 119
 120     <table class='text'>
 121         <tr>
 122             <td class='col-form-label'>
 123                 <?php echo xlt('From'); ?>:
 124             </td>
 125             <td>
 126                <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)); ?>'>
 127             </td>
 128             <td class='col-form-label'>
 129                 <?php echo xlt('To{{Range}}'); ?>:
 130             </td>
 131             <td>
 132                <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)); ?>'>
 133             </td>
 134         </tr>
 135     </table>
 136
 137     </div>
 138
 139   </td>
 140   <td class='h-100' align='left' valign='middle'>
 141     <table class='w-100 h-100' style='border-left:1px solid;'>
 142         <tr>
 143             <td>
 144                 <div class="text-center">
 145           <div class="btn-group" role="group">
 146                       <a href='#' class='btn btn-secondary btn-save' onclick='$("#form_refresh").attr("value","true"); $("#form_csvexport").val(""); $("#theform").submit();'>
 147                             <?php echo xlt('Submit'); ?>
 148                       </a>
 149                         <?php if (!empty($_POST['form_refresh'])) { ?>
 150                         <a href='#' class='btn btn-secondary btn-print' id='printbutton'>
 151                                 <?php echo xlt('Print'); ?>
 152                         </a>
 153                         <a href='#' class='btn btn-secondary btn-transmit' onclick='$("#form_csvexport").attr("value","true"); $("#theform").submit();'>
 154                             <?php echo xlt('Export to CSV'); ?>
 155                         </a>
 156                         <?php } ?>
 157           </div>
 158                 </div>
 159             </td>
 160         </tr>
 161     </table>
 162   </td>
 163  </tr>
 164 </table>
 165
 166 </form>
 167 </div> <!-- end parameters -->
 168
 169 <div id="report_results">
 170 <table class='table'>
 171
 172  <thead class='thead-light'>
 173   <th align='left'> <?php echo xlt('Primary Insurance'); ?> </th>
 174   <th align='right'> <?php echo xlt('Charges'); ?> </th>
 175   <th align='right'> <?php echo xlt('Visits'); ?> </th>
 176   <th align='right'> <?php echo xlt('Patients'); ?> </th>
 177   <th align='right'> <?php echo xlt('Pt %'); ?> </th>
 178  </thead>
 179  <tbody>
 180     <?php
 181 } // end not export
 182 if (!empty($_POST['form_refresh']) || !empty($_POST['form_csvexport'])) {
 183     $query = "SELECT b.pid, b.encounter, SUM(b.fee) AS charges, " .
 184     "MAX(fe.date) AS date " .
 185     "FROM form_encounter AS fe, billing AS b " .
 186     "WHERE fe.date >= ? AND fe.date <= ? " .
 187     "AND b.pid = fe.pid AND b.encounter = fe.encounter " .
 188     "AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 " .
 189     "GROUP BY b.pid, b.encounter ORDER BY b.pid, b.encounter";
 190
 191     $res = sqlStatement($query, array((!empty($form_from_date)) ? $form_from_date : '0000-00-00', $form_to_date));
 192     $insarr = array();
 193     $prev_pid = 0;
 194     $patcount = 0;
 195
 196     while ($row = sqlFetchArray($res)) {
 197         $patient_id = $row['pid'];
 198         $encounter_date = $row['date'];
 199         $irow = sqlQuery("SELECT insurance_companies.name " .
 200         "FROM insurance_data, insurance_companies WHERE " .
 201         "insurance_data.pid = ? AND " .
 202         "insurance_data.type = 'primary' AND " .
 203         "(insurance_data.date <= ? OR insurance_data.date IS NULL) AND " .
 204         "insurance_companies.id = insurance_data.provider " .
 205         "ORDER BY insurance_data.date DESC LIMIT 1", array($patient_id, $encounter_date));
 206         $plan = (!empty($irow['name'])) ? $irow['name'] : '-- No Insurance --';
 207         $insarr[$plan]['visits'] = $insarr[$plan]['visits'] ?? null;
 208         $insarr[$plan]['visits'] += 1;
 209         $insarr[$plan]['charges'] = $insarr[$plan]['charges'] ?? null;
 210         $insarr[$plan]['charges'] += sprintf('%0.2f', $row['charges']);
 211         if ($patient_id != $prev_pid) {
 212             ++$patcount;
 213             $insarr[$plan]['patients'] =  $insarr[$plan]['patients'] ?? null;
 214             $insarr[$plan]['patients'] += 1;
 215             $prev_pid = $patient_id;
 216         }
 217     }
 218
 219     ksort($insarr);
 220
 221     foreach ($insarr as $key => $val) {
 222         if ($_POST['form_csvexport']) {
 223             echo csvEscape($key)                                                . ',';
 224             echo csvEscape(oeFormatMoney($val['charges']))                      . ',';
 225             echo csvEscape($val['visits'])                                      . ',';
 226             echo csvEscape($val['patients'])                                    . ',';
 227             echo csvEscape(sprintf("%.1f", $val['patients'] * 100 / $patcount)) . "\n";
 228         } else {
 229             ?>
 230      <tr>
 231       <td>
 232             <?php echo text($key); ?>
 233   </td>
 234   <td align='right'>
 235             <?php echo text(oeFormatMoney($val['charges'])); ?>
 236   </td>
 237   <td align='right'>
 238             <?php echo text($val['visits']); ?>
 239   </td>
 240   <td align='right'>
 241             <?php echo text($val['patients']); ?>
 242   </td>
 243   <td align='right'>
 244             <?php printf("%.1f", $val['patients'] * 100 / $patcount) ?>
 245   </td>
 246  </tr>
 247             <?php
 248         } // end not export
 249     } // end while
 250 } // end if
 251
 252 if (empty($_POST['form_csvexport'])) {
 253     ?>
 254
 255 </tbody>
 256 </table>
 257 </div> <!-- end of results -->
 258
 259 </body>
 260
 261 </html>
 262     <?php
 263 } // end not export
 264 ?>