3 * This is a report of Financial Summary by Service Code.
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
11 * Copyright (C) 2006-2010 Rod Roark <rod@sunsetsystems.com>
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>;.
26 * @link http://www.open-emr.org
29 $sanitize_all_escapes=true;
30 $fake_register_globals=false;
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";
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;
48 if (! acl_check('acct', 'rep')) die(xlt("Unauthorized access."));
50 $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
52 if (!$INTEGRATED_AR) SLConnect();
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'];
59 if ($_POST['form_csvexport']) {
60 header("Pragma: public");
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");
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 */
86 #report_parameters_daterange {
95 /* specifically exclude some from the screen */
97 #report_parameters_daterange {
104 <title
><?php
echo xlt('Financial Summary by Service Code') ?
></title
>
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
=''/>
115 <div style
='float:left'>
119 <?php
echo xlt('Facility'); ?
>:
122 <?php
dropdown_facility($form_facility, 'form_facility', true); ?
>
124 <td
><?php
echo xlt('Provider'); ?
>:</td
>
126 // Build a drop-down list of providers.
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";
144 <?php
echo xlt('From'); ?
>: 
; 
; 
; 
;
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"); ?>'>
152 <?php
echo xlt('To'); ?
>:
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"); ?>'>
162 <input type
='checkbox' name
='form_details'<?php
if ($_POST['form_details']) echo ' checked'; ?
>>
163 <?php
echo xlt('Important Codes'); ?
>
169 <td align
='left' valign
='middle' height
="100%">
170 <table style
='border-left:1px solid; width:100%; height:100%' >
173 <div style
='margin-left:15px'>
174 <a href
='#' class='css_button' onclick
='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
176 <?php
echo xlt('Submit'); ?
>
180 <?php
if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) { ?
>
182 <a href
='#' class='css_button' onclick
='window.print()'>
184 <?php
echo xlt('Print'); ?
>
187 <a href
='#' class='css_button' onclick
='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
189 <?php
echo xlt('CSV Export'); ?
>
201 </div
> <!-- end of parameters
-->
207 if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) {
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);
230 // If a provider was specified.
231 if ($form_provider) {
232 $query .= " AND b.provider_id = ?";
233 array_push($sqlBindArray,$form_provider);
235 // If selected important codes
236 if($_POST['form_details']) {
237 $query .= " AND c.financial_reporting = '1'";
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;
247 while ($erow = sqlFetchArray($res)) {
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;
260 if ($_POST['form_csvexport']) {
263 echo '"Procedure codes",';
265 echo '"Amt Billed",';
267 echo '"Adjustment Amt",';
268 echo '"Balance Amt",' . "\n";
271 ?
> <div id
="report_results">
275 <?php
echo xlt('Procedure Codes'); ?
>
278 <?php
echo xlt('Units'); ?
>
281 <?php
echo xlt('Amt Billed'); ?
>
284 <?php
echo xlt('Paid Amt'); ?
>
287 <?php
echo xlt('Adjustment Amt'); ?
>
290 <?php
echo xlt('Balance Amt'); ?
>
297 foreach ($rows as $key => $row) {
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>";
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";
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'];
313 if ($_POST['form_csvexport']) { echo $csv; }
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";
337 if (! $_POST['form_csvexport']) {
338 if ( $_POST['form_refresh'] && count($print) != 1)
340 echo "<span style='font-size:10pt;'>";
341 echo xlt('No matches found. Try search again.');
343 echo '<script>document.getElementById("report_results").style.display="none";</script>';
344 echo '<script>document.getElementById("controls").style.display="none";</script>';
347 if (!$_POST['form_refresh'] && !$_POST['form_csvexport']) { ?
>
349 <?php
echo xlt('Please input search criteria above, and click Submit to view results.' ); ?
>
355 <!-- stuff
for the popup calendar
-->
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();
369 } // End not csv export