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 <title><? xl('Patient Insurance Distribution','e'); ?></title>
  38 <script type="text/javascript" src="../../library/overlib_mini.js"></script>
  39 <script type="text/javascript" src="../../library/calendar.js"></script>
  40 <script type="text/javascript" src="../../library/textformat.js"></script>
  41 <script language="JavaScript">
  42  var mypcc = '<? echo $GLOBALS['phone_country_code'] ?>';
  43 </script>
  44 </head>
  45
  46 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
  47
  48 <!-- Required for the popup date selectors -->
  49 <div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
  50
  51 <center>
  52
  53 <h2><? xl('Patient Insurance Distribution','e'); ?></h2>
  54
  55 <form name='theform' method='post' action='insurance_allocation_report.php'>
  56
  57 <table border='0' cellpadding='3'>
  58
  59  <tr>
  60   <td>
  61    <?php xl('From','e'); ?>:
  62    <input type='text' name='form_from_date' size='10' value='<?php echo $from_date ?>'
  63     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
  64    <a href="javascript:show_calendar('theform.form_from_date')"
  65     title=".xl('Click here to choose a date')"
  66     ><img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22' border='0'></a>
  67    &nbsp;<? xl('To','e'); ?>:
  68    <input type='text' name='form_to_date' size='10' value='<? echo $to_date ?>'
  69     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
  70    <a href="javascript:show_calendar('theform.form_to_date')"
  71     title=".xl('Click here to choose a date')"
  72     ><img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22' border='0'></a>
  73    &nbsp;
  74    <input type='submit' name='form_refresh' value='<?php xl('Refresh','e'); ?>'>
  75    &nbsp;
  76    <input type='submit' name='form_csvexport' value='<?php xl('Export as CSV','e'); ?>' />
  77   </td>
  78  </tr>
  79
  80  <tr>
  81   <td height="1">
  82   </td>
  83  </tr>
  84
  85 </table>
  86
  87 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
  88
  89  <tr bgcolor="#dddddd">
  90   <td class="dehead">
  91    <?php xl('Primary Insurance','e'); ?>
  92   </td>
  93   <td class='dehead' align='right'>
  94    <?php xl('Charges','e'); ?>
  95   </td>
  96   <td class='dehead' align='right'>
  97    <?php xl('Visits','e'); ?>
  98   </td>
  99   <td class='dehead' align='right'>
 100    <?php xl('Patients','e'); ?>
 101   </td>
 102   <td class='dehead' align='right'>
 103    <?php xl('Pt %','e'); ?>
 104   </td>
 105  </tr>
 106 <?php
 107 } // end not export
 108 if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 109
 110   $from_date = fixDate($_POST['form_from_date']);
 111   $to_date   = fixDate($_POST['form_to_date'], date('Y-m-d'));
 112
 113   $query = "SELECT b.pid, b.encounter, SUM(b.fee) AS charges, " .
 114     "MAX(fe.date) AS date " .
 115     "FROM form_encounter AS fe, billing AS b " .
 116     "WHERE fe.date >= '$from_date' AND fe.date <= '$to_date' " .
 117     "AND b.pid = fe.pid AND b.encounter = fe.encounter " .
 118     "AND b.code_type != 'COPAY' AND b.activity > 0 AND b.fee != 0 " .
 119     "GROUP BY b.pid, b.encounter ORDER BY b.pid, b.encounter";
 120
 121   $res = sqlStatement($query);
 122   $insarr = array();
 123   $prev_pid = 0;
 124   $patcount = 0;
 125
 126   while ($row = sqlFetchArray($res)) {
 127     $patient_id = $row['pid'];
 128     $encounter_date = $row['date'];
 129     $irow = sqlQuery("SELECT insurance_companies.name " .
 130       "FROM insurance_data, insurance_companies WHERE " .
 131       "insurance_data.pid = $patient_id AND " .
 132       "insurance_data.type = 'primary' AND " .
 133       "insurance_data.date <= '$encounter_date' AND " .
 134       "insurance_companies.id = insurance_data.provider " .
 135       "ORDER BY insurance_data.date DESC LIMIT 1");
 136     $plan = $irow['name'] ? $irow['name'] : '-- No Insurance --';
 137     $insarr[$plan]['visits'] += 1;
 138     $insarr[$plan]['charges'] += sprintf('%0.2f', $row['charges']);
 139     if ($patient_id != $prev_pid) {
 140       ++$patcount;
 141       $insarr[$plan]['patients'] += 1;
 142       $prev_pid = $patient_id;
 143     }
 144   }
 145
 146   ksort($insarr);
 147
 148   while (list($key, $val) = each($insarr)) {
 149     if ($_POST['form_csvexport']) {
 150         echo '"' . $key                                                . '",';
 151         echo '"' . sprintf('%0.2f', $val['charges'])                   . '",';
 152         echo '"' . $val['visits']                                      . '",';
 153         echo '"' . $val['patients']                                    . '",';
 154         echo '"' . sprintf("%.1f", $val['patients'] * 100 / $patcount) . '"' . "\n";
 155     }
 156     else {
 157 ?>
 158  <tr>
 159   <td class='detail'>
 160    <?php echo $key ?>
 161   </td>
 162   <td class='detail' align='right'>
 163    <?php echo sprintf('%0.2f', $val['charges']) ?>
 164   </td>
 165   <td class='detail' align='right'>
 166    <?php echo $val['visits'] ?>
 167   </td>
 168   <td class='detail' align='right'>
 169    <?php echo $val['patients'] ?>
 170   </td>
 171   <td class='detail' align='right'>
 172    <?php printf("%.1f", $val['patients'] * 100 / $patcount) ?>
 173   </td>
 174  </tr>
 175 <?php
 176     } // end not export
 177   } // end while
 178 } // end if
 179
 180 if (! $_POST['form_csvexport']) {
 181 ?>
 182
 183 </table>
 184 </form>
 185 </center>
 186 </body>
 187 </html>
 188 <?php
 189 } // end not export
 190 ?>