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