interface/reports/svc_code_financial_report.php

   1 <?php
   2 /**
   3  * This is a report of Financial Summary by Service Code.
   4  *
   5  * This is a summary of service code charge/pay/adjust and balance,
   6  * with the ability to pick "important" codes to either highlight or
   7  * limit to list to. Important codes can be configured in
   8  * Administration->Service section by assigning code with
   9  * 'Service Reporting'.
  10  *
  11  * @package   OpenEMR
  12  * @link      http://www.open-emr.org
  13  * @author    Rod Roark <rod@sunsetsystems.com>
  14  * @author    Visolve
  15  * @author    Brady Miller <brady.g.miller@gmail.com>
  16  * @copyright Copyright (C) 2006-2016 Rod Roark <rod@sunsetsystems.com>
  17  * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
  18  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  19  */
  20
  21
  22 require_once("../globals.php");
  23 require_once("$srcdir/patient.inc");
  24 require_once("$srcdir/acl.inc");
  25 require_once "$srcdir/options.inc.php";
  26 require_once "$srcdir/appointments.inc.php";
  27
  28 use OpenEMR\Core\Header;
  29
  30 $grand_total_units  = 0;
  31 $grand_total_amt_billed  = 0;
  32 $grand_total_amt_paid  = 0;
  33 $grand_total_amt_adjustment  = 0;
  34 $grand_total_amt_balance  = 0;
  35
  36
  37 if (! acl_check('acct', 'rep')) {
  38     die(xlt("Unauthorized access."));
  39 }
  40
  41 $form_from_date = (isset($_POST['form_from_date'])) ? DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
  42 $form_to_date   = (isset($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
  43 $form_facility  = $_POST['form_facility'];
  44 $form_provider  = $_POST['form_provider'];
  45
  46 if ($_POST['form_csvexport']) {
  47     header("Pragma: public");
  48     header("Expires: 0");
  49     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  50     header("Content-Type: application/force-download");
  51     header("Content-Disposition: attachment; filename=svc_financial_report_".attr($form_from_date)."--".attr($form_to_date).".csv");
  52     header("Content-Description: File Transfer");
  53     // CSV headers:
  54 } // end export
  55 else {
  56 ?>
  57 <html>
  58 <head>
  59     <title><?php echo xlt('Financial Summary by Service Code') ?></title>
  60
  61     <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
  62
  63     <style type="text/css">
  64         /* specifically include & exclude from printing */
  65         @media print {
  66             #report_parameters {
  67                 visibility: hidden;
  68                 display: none;
  69             }
  70             #report_parameters_daterange {
  71                 visibility: visible;
  72                 display: inline;
  73             }
  74             #report_results {
  75                 margin-top: 30px;
  76             }
  77         }
  78
  79         /* specifically exclude some from the screen */
  80         @media screen {
  81             #report_parameters_daterange {
  82                 visibility: hidden;
  83                 display: none;
  84             }
  85         }
  86     </style>
  87
  88     <script language="JavaScript">
  89         $(document).ready(function() {
  90             oeFixedHeaderSetup(document.getElementById('mymaintable'));
  91             var win = top.printLogSetup ? top : opener.top;
  92             win.printLogSetup(document.getElementById('printbutton'));
  93
  94             $('.datepicker').datetimepicker({
  95                 <?php $datetimepicker_timepicker = false; ?>
  96                 <?php $datetimepicker_showseconds = false; ?>
  97                 <?php $datetimepicker_formatInput = true; ?>
  98                 <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
  99                 <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
 100             });
 101         });
 102     </script>
 103 </head>
 104
 105 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class="body_top">
 106 <span class='title'><?php echo xlt('Report'); ?> - <?php echo xlt('Financial Summary by Service Code'); ?></span>
 107 <form method='post' action='svc_code_financial_report.php' id='theform' onsubmit='return top.restoreSession()'>
 108 <div id="report_parameters">
 109 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 110 <input type='hidden' name='form_csvexport' id='form_csvexport' value=''/>
 111 <table>
 112 <tr>
 113 <td width='70%'>
 114   <div style='float:left'>
 115   <table class='text'>
 116       <tr>
 117           <td class='control-label'>
 118             <?php echo xlt('Facility'); ?>:
 119           </td>
 120           <td>
 121         <?php dropdown_facility($form_facility, 'form_facility', true); ?>
 122           </td>
 123                     <td class='control-label'><?php echo xlt('Provider'); ?>:</td>
 124             <td><?php
 125                     // Build a drop-down list of providers.
 126                             //
 127                             $query = "SELECT id, lname, fname FROM users WHERE ".
 128                               "authorized = 1 ORDER BY lname, fname"; //(CHEMED) facility filter
 129                             $ures = sqlStatement($query);
 130                             echo "   <select name='form_provider' class='form-control'>\n";
 131                             echo "    <option value=''>-- " . xlt('All') . " --\n";
 132             while ($urow = sqlFetchArray($ures)) {
 133                 $provid = $urow['id'];
 134                 echo "    <option value='" . attr($provid) ."'";
 135                 if ($provid == $_POST['form_provider']) {
 136                     echo " selected";
 137                 }
 138
 139                 echo ">" . text($urow['lname']) . ", " . text($urow['fname']) . "\n";
 140             }
 141
 142                             echo "   </select>\n";
 143                             ?>
 144                 </td>
 145         </tr><tr>
 146                  <td class='control-label'>
 147                             <?php echo xlt('From'); ?>:&nbsp;&nbsp;&nbsp;&nbsp;
 148                           </td>
 149                           <td>
 150                            <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)); ?>'>
 151                         </td>
 152                         <td class='control-label'>
 153                             <?php echo xlt('To'); ?>:
 154                         </td>
 155                         <td>
 156                            <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)); ?>'>
 157                         </td>
 158                         <td>
 159                           <div class="checkbox">
 160                            <label><input type='checkbox' name='form_details'<?php
 161                             if ($_POST['form_details']) {
 162                                     echo ' checked';
 163                             } ?>>
 164                             <?php echo xlt('Important Codes'); ?></label>
 165                           </div>
 166                         </td>
 167         </tr>
 168     </table>
 169     </div>
 170   </td>
 171   <td align='left' valign='middle' height="100%">
 172     <table style='border-left:1px solid; width:100%; height:100%' >
 173         <tr>
 174             <td>
 175                 <div class="text-center">
 176           <div class="btn-group" role="group">
 177                       <a href='#' class='btn btn-default btn-save' onclick='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
 178                             <?php echo xlt('Submit'); ?>
 179                       </a>
 180                         <?php if ($_POST['form_refresh'] || $_POST['form_csvexport']) { ?>
 181                         <a href='#' class='btn btn-default btn-print' id='printbutton'>
 182                                 <?php echo xlt('Print'); ?>
 183                         </a>
 184                         <a href='#' class='btn btn-default btn-transmit' onclick='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
 185                                 <?php echo xlt('CSV Export'); ?>
 186                         </a>
 187                         <?php } ?>
 188           </div>
 189                 </div>
 190             </td>
 191         </tr>
 192     </table>
 193   </td>
 194  </tr>
 195 </table>
 196 </div> <!-- end of parameters -->
 197
 198 <?php
 199 }
 200
 201    // end not export
 202
 203 if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 204     $rows = array();
 205     $from_date = $form_from_date;
 206     $to_date   = $form_to_date;
 207     $sqlBindArray = array();
 208     $query = "select b.code,sum(b.units) as units,sum(b.fee) as billed,sum(ar_act.paid) as PaidAmount, " .
 209     "sum(ar_act.adjust) as AdjustAmount,(sum(b.fee)-(sum(ar_act.paid)+sum(ar_act.adjust))) as Balance, " .
 210     "c.financial_reporting " .
 211     "FROM form_encounter as fe " .
 212     "JOIN billing as b on b.pid=fe.pid and b.encounter=fe.encounter " .
 213     "JOIN (select pid,encounter,code,sum(pay_amount) as paid,sum(adj_amount) as adjust from ar_activity group by pid,encounter,code) as ar_act " .
 214     "ON ar_act.pid=b.pid and ar_act.encounter=b.encounter and ar_act.code=b.code " .
 215     "LEFT OUTER JOIN codes AS c ON c.code = b.code " .
 216     "INNER JOIN code_types AS ct ON ct.ct_key = b.code_type AND ct.ct_fee = '1' " .
 217     "WHERE b.code_type != 'COPAY' AND b.activity = 1 /* AND b.fee != 0 */ AND " .
 218     "fe.date >=  ? AND fe.date <= ?";
 219     array_push($sqlBindArray, "$from_date 00:00:00", "$to_date 23:59:59");
 220     // If a facility was specified.
 221     if ($form_facility) {
 222         $query .= " AND fe.facility_id = ?";
 223         array_push($sqlBindArray, $form_facility);
 224     }
 225
 226     // If a provider was specified.
 227     if ($form_provider) {
 228         $query .= " AND b.provider_id = ?";
 229         array_push($sqlBindArray, $form_provider);
 230     }
 231
 232     // If selected important codes
 233     if ($_POST['form_details']) {
 234         $query .= " AND c.financial_reporting = '1'";
 235     }
 236
 237     $query .= " GROUP BY b.code ORDER BY b.code, fe.date, fe.id ";
 238     $res = sqlStatement($query, $sqlBindArray);
 239     $grand_total_units  = 0;
 240     $grand_total_amt_billed  = 0;
 241     $grand_total_amt_paid  = 0;
 242     $grand_total_amt_adjustment  = 0;
 243     $grand_total_amt_balance  = 0;
 244
 245     while ($erow = sqlFetchArray($res)) {
 246         $row = array();
 247         $row['pid'] = $erow['pid'];
 248         $row['provider_id'] = $erow['provider_id'];
 249         $row['Procedure codes'] = $erow['code'];
 250         $row['Units'] = $erow['units'];
 251         $row['Amt Billed'] = $erow['billed'];
 252         $row['Paid Amt'] = $erow['PaidAmount'];
 253         $row['Adjustment Amt'] = $erow['AdjustAmount'];
 254         $row['Balance Amt'] = $erow['Balance'];
 255         $row['financial_reporting'] = $erow['financial_reporting'];
 256         $rows[$erow['pid'] . '|' . $erow['code'] . '|' . $erow['units']] = $row;
 257     }
 258
 259     if ($_POST['form_csvexport']) {
 260       // CSV headers:
 261         if (true) {
 262             echo '"Procedure codes",';
 263             echo '"Units",';
 264             echo '"Amt Billed",';
 265             echo '"Paid Amt",';
 266             echo '"Adjustment Amt",';
 267             echo '"Balance Amt",' . "\n";
 268         }
 269     } else {
 270 ?> <div id="report_results">
 271 <table id='mymaintable'>
 272 <thead>
 273 <th>
 274 <?php echo xlt('Procedure Codes'); ?>
 275 </th>
 276 <th >
 277 <?php echo xlt('Units'); ?>
 278 </th>
 279 <th>
 280 <?php echo xlt('Amt Billed'); ?>
 281 </th>
 282 <th>
 283 <?php echo xlt('Paid Amt'); ?>
 284 </th>
 285 <th >
 286 <?php echo xlt('Adjustment Amt'); ?>
 287 </th>
 288 <th >
 289 <?php echo xlt('Balance Amt'); ?>
 290 </th>
 291 </thead>
 292 <?php
 293     }
 294
 295             $orow = -1;
 296
 297     foreach ($rows as $key => $row) {
 298         $print = '';
 299         $csv = '';
 300
 301         if ($row['financial_reporting']) {
 302             $bgcolor = "#FFFFDD";
 303         } else {
 304             $bgcolor = "#FFDDDD";
 305         }
 306
 307         $print = "<tr bgcolor='$bgcolor'><td class='detail'>".text($row['Procedure codes'])."</td><td class='detail'>".text($row['Units'])."</td><td class='detail'>".text(oeFormatMoney($row['Amt Billed']))."</td><td class='detail'>".text(oeFormatMoney($row['Paid Amt']))."</td><td class='detail'>".text(oeFormatMoney($row['Adjustment Amt']))."</td><td class='detail'>".text(oeFormatMoney($row['Balance Amt']))."</td>";
 308
 309         $csv = '"' . text($row['Procedure codes']) . '","' . text($row['Units']) . '","' . text(oeFormatMoney($row['Amt Billed'])) . '","' . text(oeFormatMoney($row['Paid Amt'])) . '","' . text(oeFormatMoney($row['Adjustment Amt'])) . '","' . text(oeFormatMoney($row['Balance Amt'])) . '"' . "\n";
 310
 311         $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
 312                        $grand_total_units  += $row['Units'];
 313                                        $grand_total_amt_billed  += $row['Amt Billed'];
 314                                        $grand_total_amt_paid  += $row['Paid Amt'];
 315                                        $grand_total_amt_adjustment  += $row['Adjustment Amt'];
 316                                        $grand_total_amt_balance  += $row['Balance Amt'];
 317
 318         if ($_POST['form_csvexport']) {
 319             echo $csv;
 320         } else {
 321             echo $print;
 322         }
 323     }
 324
 325     if (!$_POST['form_csvexport']) {
 326         echo "<tr bgcolor='#ffffff'>\n";
 327         echo " <td class='detail'>" . xlt("Grand Total") . "</td>\n";
 328         echo " <td class='detail'>" . text($grand_total_units) . "</td>\n";
 329         echo " <td class='detail'>" .
 330         text(oeFormatMoney($grand_total_amt_billed)) . "</td>\n";
 331         echo " <td class='detail'>" .
 332         text(oeFormatMoney($grand_total_amt_paid)) . "</td>\n";
 333         echo " <td class='detail'>" .
 334         text(oeFormatMoney($grand_total_amt_adjustment)) . "</td>\n";
 335         echo " <td class='detail'>" .
 336         text(oeFormatMoney($grand_total_amt_balance)) . "</td>\n";
 337         echo " </tr>\n";
 338         ?>
 339      </table>    </div>
 340                 <?php
 341     }
 342 }
 343
 344 if (! $_POST['form_csvexport']) {
 345     if ($_POST['form_refresh'] && empty($print)) {
 346         echo "<span style='font-size:10pt;'>";
 347            echo xlt('No matches found. Try search again.');
 348            echo "</span>";
 349         echo '<script>document.getElementById("report_results").style.display="none";</script>';
 350         echo '<script>document.getElementById("controls").style.display="none";</script>';
 351     }
 352
 353     if (!$_POST['form_refresh'] && !$_POST['form_csvexport']) { ?>
 354         <div class='text'>
 355         <?php echo xlt('Please input search criteria above, and click Submit to view results.'); ?>
 356         </div><?php
 357     } ?>
 358 </form>
 359 </body>
 360
 361 </html>
 362 <?php
 363 } // End not csv export
 364 ?>