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