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