interface/reports/insurance_allocation_report.php

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