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  * LICENSE: This program is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU General Public License
   9  * as published by the Free Software Foundation; either version 2
  10  * of the License, or (at your option) any later version.
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  14  * GNU General Public License for more details.
  15  * You should have received a copy of the GNU General Public License
  16  * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
  17  *
  18  * @package OpenEMR
  19  * @author  Brady Miller <brady.g.miller@gmail.com>
  20  * @link    http://www.open-emr.org
  21  */
  22
  23 use OpenEMR\Core\Header;
  24
  25  include_once("../globals.php");
  26  include_once("../../library/patient.inc");
  27  include_once("../../library/acl.inc");
  28
  29  // Might want something different here.
  30  //
  31  // if (! acl_check('acct', 'rep')) die("Unauthorized access.");
  32
  33  $from_date = fixDate($_POST['form_from_date']);
  34  $to_date   = fixDate($_POST['form_to_date'], date('Y-m-d'));
  35
  36 if ($_POST['form_csvexport']) {
  37     header("Pragma: public");
  38     header("Expires: 0");
  39     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  40     header("Content-Type: application/force-download");
  41     header("Content-Disposition: attachment; filename=insurance_distribution.csv");
  42     header("Content-Description: File Transfer");
  43   // CSV headers:
  44     if (true) {
  45         echo '"Insurance",';
  46         echo '"Charges",';
  47         echo '"Visits",';
  48         echo '"Patients",';
  49         echo '"Pt Pct"' . "\n";
  50     }
  51 } else {
  52 ?>
  53 <html>
  54 <head>
  55
  56 <title><?php xl('Patient Insurance Distribution', 'e'); ?></title>
  57
  58 <?php Header::setupHeader('datetime-picker'); ?>
  59 <script language="JavaScript">
  60  var mypcc = '<?php echo $GLOBALS['phone_country_code'] ?>';
  61
  62  $(document).ready(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 = false; ?>
  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
  75 </script>
  76
  77 <style type="text/css">
  78
  79 /* specifically include & exclude from printing */
  80 @media print {
  81     #report_parameters {
  82         visibility: hidden;
  83         display: none;
  84     }
  85     #report_parameters_daterange {
  86         visibility: visible;
  87         display: inline;
  88     }
  89     #report_results table {
  90        margin-top: 0px;
  91     }
  92 }
  93
  94 /* specifically exclude some from the screen */
  95 @media screen {
  96     #report_parameters_daterange {
  97         visibility: hidden;
  98         display: none;
  99     }
 100 }
 101
 102 </style>
 103 </head>
 104
 105 <body class="body_top">
 106
 107 <!-- Required for the popup date selectors -->
 108 <div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
 109
 110 <span class='title'><?php xl('Report', 'e'); ?> - <?php xl('Patient Insurance Distribution', 'e'); ?></span>
 111
 112 <div id="report_parameters_daterange">
 113 <?php echo date("d F Y", strtotime($form_from_date)) ." &nbsp; to &nbsp; ". date("d F Y", strtotime($form_to_date)); ?>
 114 </div>
 115
 116 <form name='theform' method='post' action='insurance_allocation_report.php' id='theform'>
 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='control-label'>
 130                 <?php xl('From', 'e'); ?>:
 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 $form_from_date ?>'
 134                 title='yyyy-mm-dd'>
 135             </td>
 136             <td class='control-label'>
 137                 <?php xl('To', 'e'); ?>:
 138             </td>
 139             <td>
 140                <input type='text' class='datepicker form-control' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_date ?>'
 141                 title='yyyy-mm-dd'>
 142             </td>
 143         </tr>
 144     </table>
 145
 146     </div>
 147
 148   </td>
 149   <td align='left' valign='middle' height="100%">
 150     <table style='border-left:1px solid; width:100%; height:100%' >
 151         <tr>
 152             <td>
 153                 <div class="text-center">
 154           <div class="btn-group" role="group">
 155                       <a href='#' class='btn btn-default btn-save' onclick='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
 156                             <?php echo xlt('Submit'); ?>
 157                       </a>
 158                         <?php if ($_POST['form_refresh']) { ?>
 159                         <a href='#' class='btn btn-default btn-print' id='printbutton'>
 160                                 <?php echo xlt('Print'); ?>
 161                         </a>
 162                         <a href='#' class='btn btn-default btn-transmit' onclick='$("#form_csvexport").attr("value","true"); $("#theform").submit();'>
 163                             <?php echo xlt('Export to CSV'); ?>
 164                         </a>
 165                         <?php } ?>
 166           </div>
 167                 </div>
 168             </td>
 169         </tr>
 170     </table>
 171   </td>
 172  </tr>
 173 </table>
 174
 175 </form>
 176 </div> <!-- end parameters -->
 177
 178 <div id="report_results">
 179 <table>
 180
 181  <thead>
 182   <th align='left'> <?php xl('Primary Insurance', 'e'); ?> </th>
 183   <th align='right'> <?php xl('Charges', 'e'); ?> </th>
 184   <th align='right'> <?php xl('Visits', 'e'); ?> </th>
 185   <th align='right'> <?php xl('Patients', 'e'); ?> </th>
 186   <th align='right'> <?php xl('Pt %', 'e'); ?> </th>
 187  </thead>
 188  <tbody>
 189 <?php
 190 } // end not export
 191 if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 192     $from_date = fixDate($_POST['form_from_date']);
 193     $to_date   = fixDate($_POST['form_to_date'], date('Y-m-d'));
 194
 195     $query = "SELECT b.pid, b.encounter, SUM(b.fee) AS charges, " .
 196     "MAX(fe.date) AS date " .
 197     "FROM form_encounter AS fe, billing AS b " .
 198     "WHERE fe.date >= '$from_date' AND fe.date <= '$to_date' " .
 199     "AND b.pid = fe.pid AND b.encounter = fe.encounter " .
 200     "AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 " .
 201     "GROUP BY b.pid, b.encounter ORDER BY b.pid, b.encounter";
 202
 203     $res = sqlStatement($query);
 204     $insarr = array();
 205     $prev_pid = 0;
 206     $patcount = 0;
 207
 208     while ($row = sqlFetchArray($res)) {
 209         $patient_id = $row['pid'];
 210         $encounter_date = $row['date'];
 211         $irow = sqlQuery("SELECT insurance_companies.name " .
 212         "FROM insurance_data, insurance_companies WHERE " .
 213         "insurance_data.pid = $patient_id AND " .
 214         "insurance_data.type = 'primary' AND " .
 215         "insurance_data.date <= '$encounter_date' AND " .
 216         "insurance_companies.id = insurance_data.provider " .
 217         "ORDER BY insurance_data.date DESC LIMIT 1");
 218         $plan = $irow['name'] ? $irow['name'] : '-- No Insurance --';
 219         $insarr[$plan]['visits'] += 1;
 220         $insarr[$plan]['charges'] += sprintf('%0.2f', $row['charges']);
 221         if ($patient_id != $prev_pid) {
 222               ++$patcount;
 223               $insarr[$plan]['patients'] += 1;
 224               $prev_pid = $patient_id;
 225         }
 226     }
 227
 228     ksort($insarr);
 229
 230     while (list($key, $val) = each($insarr)) {
 231         if ($_POST['form_csvexport']) {
 232             echo '"' . $key                                                . '",';
 233             echo '"' . oeFormatMoney($val['charges'])                      . '",';
 234             echo '"' . $val['visits']                                      . '",';
 235             echo '"' . $val['patients']                                    . '",';
 236             echo '"' . sprintf("%.1f", $val['patients'] * 100 / $patcount) . '"' . "\n";
 237         } else {
 238         ?>
 239      <tr>
 240       <td>
 241             <?php echo $key ?>
 242   </td>
 243   <td align='right'>
 244         <?php echo oeFormatMoney($val['charges']) ?>
 245   </td>
 246   <td align='right'>
 247         <?php echo $val['visits'] ?>
 248   </td>
 249   <td align='right'>
 250         <?php echo $val['patients'] ?>
 251   </td>
 252   <td align='right'>
 253         <?php printf("%.1f", $val['patients'] * 100 / $patcount) ?>
 254   </td>
 255  </tr>
 256 <?php
 257         } // end not export
 258     } // end while
 259 } // end if
 260
 261 if (! $_POST['form_csvexport']) {
 262 ?>
 263
 264 </tbody>
 265 </table>
 266 </div> <!-- end of results -->
 267
 268 </body>
 269
 270 </html>
 271 <?php
 272 } // end not export
 273 ?>