interface/reports/insurance_allocation_report.php

   1 <?php
   2  // This module shows relative insurance usage by unique patients
   3  // that are seen within a given time period.  Each patient that had
   4  // a visit is counted only once, regardless of how many visits.
   5
   6  include_once("../globals.php");
   7  include_once("../../library/patient.inc");
   8  include_once("../../library/acl.inc");
   9
  10  // Might want something different here.
  11  //
  12  // if (! acl_check('acct', 'rep')) die("Unauthorized access.");
  13
  14  $from_date = fixDate($_POST['form_from_date']);
  15  $to_date   = fixDate($_POST['form_to_date'], date('Y-m-d'));
  16
  17 if ($_POST['form_csvexport']) {
  18   header("Pragma: public");
  19   header("Expires: 0");
  20   header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  21   header("Content-Type: application/force-download");
  22   header("Content-Disposition: attachment; filename=insurance_distribution.csv");
  23   header("Content-Description: File Transfer");
  24   // CSV headers:
  25   if (true) {
  26     echo '"Insurance",';
  27     echo '"Charges",';
  28     echo '"Visits",';
  29     echo '"Patients",';
  30     echo '"Pt Pct"' . "\n";
  31   }
  32 }
  33 else {
  34 ?>
  35 <html>
  36 <head>
  37 <?php html_header_show();?>
  38 <title><?php xl('Patient Insurance Distribution','e'); ?></title>
  39 <script type="text/javascript" src="../../library/overlib_mini.js"></script>
  40 <script type="text/javascript" src="../../library/calendar.js"></script>
  41 <script type="text/javascript" src="../../library/textformat.js"></script>
  42 <script language="JavaScript">
  43  var mypcc = '<?php echo $GLOBALS['phone_country_code'] ?>';
  44 </script>
  45
  46 <link rel=stylesheet href="<?php echo $css_header;?>" type="text/css">
  47 <style type="text/css">
  48
  49 /* specifically include & exclude from printing */
  50 @media print {
  51     #patinsreport_parameters {
  52         visibility: hidden;
  53         display: none;
  54     }
  55     #patinsreport_parameters_daterange {
  56         visibility: visible;
  57         display: inline;
  58     }
  59 }
  60
  61 /* specifically exclude some from the screen */
  62 @media screen {
  63     #patinsreport_parameters_daterange {
  64         visibility: hidden;
  65         display: none;
  66     }
  67 }
  68
  69 #patinsreport_parameters {
  70     width: 100%;
  71     background-color: #ddf;
  72 }
  73 #patinsreport_parameters table {
  74     border: none;
  75     border-collapse: collapse;
  76 }
  77 #patinsreport_parameters table td {
  78     padding: 3px;
  79 }
  80
  81 #patinsreport_results {
  82     width: 100%;
  83     margin-top: 10px;
  84 }
  85 #patinsreport_results table {
  86    border: 1px solid black;
  87    width: 98%;
  88    border-collapse: collapse;
  89 }
  90 #patinsreport_results table thead {
  91     display: table-header-group;
  92     background-color: #ddd;
  93 }
  94 #patinsreport_results table th {
  95     border-bottom: 1px solid black;
  96 }
  97 #patinsreport_results table td {
  98     padding: 1px;
  99     margin: 2px;
 100     border-bottom: 1px solid #eee;
 101 }
 102 </style>
 103 </head>
 104
 105 <body class="body_top">
 106 <center>
 107 <!-- Required for the popup date selectors -->
 108 <div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
 109
 110 <h2><?php xl('Patient Insurance Distribution','e'); ?></h2>
 111 <div id="patinsreport_parameters_daterange">
 112 <?php echo date("d F Y", strtotime($form_from_date)) ." &nbsp; to &nbsp; ". date("d F Y", strtotime($form_to_date)); ?>
 113 </div>
 114
 115 <div id="patinsreport_parameters">
 116 <form name='theform' method='post' action='insurance_allocation_report.php'>
 117 <table>
 118  <tr>
 119   <td>
 120    <?php xl('From','e'); ?>:
 121    <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $from_date ?>'
 122     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 123    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 124     id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 125     title='<?php xl('Click here to choose a date','e'); ?>'>
 126    &nbsp;<?php xl('To','e'); ?>:
 127    <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $to_date ?>'
 128     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 129    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 130     id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 131     title='<?php xl('Click here to choose a date','e'); ?>'>
 132    &nbsp;
 133    <input type='submit' name='form_refresh' value='<?php xl('Refresh','e'); ?>'>
 134    &nbsp;
 135    <input type='submit' name='form_csvexport' value='<?php xl('Export as CSV','e'); ?>' />
 136   </td>
 137  </tr>
 138 </table>
 139 </form>
 140 </div> <!-- end parameters -->
 141
 142 <div id="patinsreport_results">
 143 <table>
 144
 145  <thead>
 146   <th> <?php xl('Primary Insurance','e'); ?> </th>
 147   <th> <?php xl('Charges','e'); ?> </th>
 148   <th> <?php xl('Visits','e'); ?> </th>
 149   <th> <?php xl('Patients','e'); ?> </th>
 150   <th> <?php xl('Pt %','e'); ?> </th>
 151  </thead>
 152  <tbody>
 153 <?php
 154 } // end not export
 155 if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 156
 157   $from_date = fixDate($_POST['form_from_date']);
 158   $to_date   = fixDate($_POST['form_to_date'], date('Y-m-d'));
 159
 160   $query = "SELECT b.pid, b.encounter, SUM(b.fee) AS charges, " .
 161     "MAX(fe.date) AS date " .
 162     "FROM form_encounter AS fe, billing AS b " .
 163     "WHERE fe.date >= '$from_date' AND fe.date <= '$to_date' " .
 164     "AND b.pid = fe.pid AND b.encounter = fe.encounter " .
 165     "AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 " .
 166     "GROUP BY b.pid, b.encounter ORDER BY b.pid, b.encounter";
 167
 168   $res = sqlStatement($query);
 169   $insarr = array();
 170   $prev_pid = 0;
 171   $patcount = 0;
 172
 173   while ($row = sqlFetchArray($res)) {
 174     $patient_id = $row['pid'];
 175     $encounter_date = $row['date'];
 176     $irow = sqlQuery("SELECT insurance_companies.name " .
 177       "FROM insurance_data, insurance_companies WHERE " .
 178       "insurance_data.pid = $patient_id AND " .
 179       "insurance_data.type = 'primary' AND " .
 180       "insurance_data.date <= '$encounter_date' AND " .
 181       "insurance_companies.id = insurance_data.provider " .
 182       "ORDER BY insurance_data.date DESC LIMIT 1");
 183     $plan = $irow['name'] ? $irow['name'] : '-- No Insurance --';
 184     $insarr[$plan]['visits'] += 1;
 185     $insarr[$plan]['charges'] += sprintf('%0.2f', $row['charges']);
 186     if ($patient_id != $prev_pid) {
 187       ++$patcount;
 188       $insarr[$plan]['patients'] += 1;
 189       $prev_pid = $patient_id;
 190     }
 191   }
 192
 193   ksort($insarr);
 194
 195   while (list($key, $val) = each($insarr)) {
 196     if ($_POST['form_csvexport']) {
 197         echo '"' . $key                                                . '",';
 198         echo '"' . sprintf('%0.2f', $val['charges'])                   . '",';
 199         echo '"' . $val['visits']                                      . '",';
 200         echo '"' . $val['patients']                                    . '",';
 201         echo '"' . sprintf("%.1f", $val['patients'] * 100 / $patcount) . '"' . "\n";
 202     }
 203     else {
 204 ?>
 205  <tr>
 206   <td>
 207    <?php echo $key ?>
 208   </td>
 209   <td align='right'>
 210    <?php echo sprintf('%0.2f', $val['charges']) ?>
 211   </td>
 212   <td align='right'>
 213    <?php echo $val['visits'] ?>
 214   </td>
 215   <td align='right'>
 216    <?php echo $val['patients'] ?>
 217   </td>
 218   <td align='right'>
 219    <?php printf("%.1f", $val['patients'] * 100 / $patcount) ?>
 220   </td>
 221  </tr>
 222 <?php
 223     } // end not export
 224   } // end while
 225 } // end if
 226
 227 if (! $_POST['form_csvexport']) {
 228 ?>
 229
 230 </tbody>
 231 </table>
 232 </div> <!-- end of results -->
 233
 234 </center>
 235
 236 </body>
 237
 238 <!-- stuff for the popup calendar -->
 239 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 240 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 241 <script type="text/javascript" src="../../library/dynarch_calendar_en.js"></script>
 242 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 243 <script language="Javascript">
 244  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 245  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 246 </script>
 247 </html>
 248 <?php
 249 } // end not export
 250 ?>