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-2015 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 <script language
="JavaScript">
108 $
(document
).ready(function() {
109 var win
= top
.printLogSetup ? top
: opener
.top
;
110 win
.printLogSetup(document
.getElementById('printbutton'));
116 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class="body_top">
117 <span
class='title'><?php
echo xlt('Report'); ?
> - <?php
echo xlt('Financial Summary by Service Code'); ?
></span
>
118 <form method
='post' action
='svc_code_financial_report.php' id
='theform'>
119 <div id
="report_parameters">
120 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
121 <input type
='hidden' name
='form_csvexport' id
='form_csvexport' value
=''/>
125 <div style
='float:left'>
129 <?php
echo xlt('Facility'); ?
>:
132 <?php
dropdown_facility($form_facility, 'form_facility', true); ?
>
134 <td
><?php
echo xlt('Provider'); ?
>:</td
>
136 // Build a drop-down list of providers.
138 $query = "SELECT id, lname, fname FROM users WHERE ".
139 "authorized = 1 ORDER BY lname, fname"; //(CHEMED) facility filter
140 $ures = sqlStatement($query);
141 echo " <select name='form_provider'>\n";
142 echo " <option value=''>-- " . xlt('All') . " --\n";
143 while ($urow = sqlFetchArray($ures)) {
144 $provid = $urow['id'];
145 echo " <option value='" . attr($provid) ."'";
146 if ($provid == $_POST['form_provider']) echo " selected";
147 echo ">" . text($urow['lname']) . ", " . text($urow['fname']) . "\n";
154 <?php
echo xlt('From'); ?
>: 
; 
; 
; 
;
155 <input type
='text' name
='form_from_date' id
="form_from_date" size
='10' value
='<?php echo attr($form_from_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_from_date' border
='0' alt
='[?]' style
='cursor:pointer'
159 title
='<?php echo xla("Click here to choose a date"); ?>'>
162 <?php
echo xlt('To'); ?
>:
165 <input type
='text' name
='form_to_date' id
="form_to_date" size
='10' value
='<?php echo attr($form_to_date) ?>'
166 onkeyup
='datekeyup(this,mypcc)' onblur
='dateblur(this,mypcc)' title
='yyyy-mm-dd'>
167 <img src
='../pic/show_calendar.gif' align
='absbottom' width
='24' height
='22'
168 id
='img_to_date' border
='0' alt
='[?]' style
='cursor:pointer'
169 title
='<?php echo xla("Click here to choose a date"); ?>'>
172 <input type
='checkbox' name
='form_details'<?php
if ($_POST['form_details']) echo ' checked'; ?
>>
173 <?php
echo xlt('Important Codes'); ?
>
179 <td align
='left' valign
='middle' height
="100%">
180 <table style
='border-left:1px solid; width:100%; height:100%' >
183 <div style
='margin-left:15px'>
184 <a href
='#' class='css_button' onclick
='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
186 <?php
echo xlt('Submit'); ?
>
190 <?php
if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) { ?
>
192 <a href
='#' class='css_button' id
='printbutton'>
194 <?php
echo xlt('Print'); ?
>
197 <a href
='#' class='css_button' onclick
='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
199 <?php
echo xlt('CSV Export'); ?
>
211 </div
> <!-- end of parameters
-->
217 if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) {
219 $from_date = $form_from_date;
220 $to_date = $form_to_date;
221 $sqlBindArray = array();
222 if ($INTEGRATED_AR) {
223 $query = "select b.code,sum(b.units) as units,sum(b.fee) as billed,sum(ar_act.paid) as PaidAmount, " .
224 "sum(ar_act.adjust) as AdjustAmount,(sum(b.fee)-(sum(ar_act.paid)+sum(ar_act.adjust))) as Balance, " .
225 "c.financial_reporting " .
226 "FROM form_encounter as fe " .
227 "JOIN billing as b on b.pid=fe.pid and b.encounter=fe.encounter " .
228 "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 " .
229 "ON ar_act.pid=b.pid and ar_act.encounter=b.encounter and ar_act.code=b.code " .
230 "LEFT OUTER JOIN codes AS c ON c.code = b.code " .
231 "INNER JOIN code_types AS ct ON ct.ct_key = b.code_type AND ct.ct_fee = '1' " .
232 "WHERE b.code_type != 'COPAY' AND b.activity = 1 /* AND b.fee != 0 */ AND " .
233 "fe.date >= ? AND fe.date <= ?";
234 array_push($sqlBindArray,"$from_date 00:00:00","$to_date 23:59:59");
235 // If a facility was specified.
236 if ($form_facility) {
237 $query .= " AND fe.facility_id = ?";
238 array_push($sqlBindArray,$form_facility);
240 // If a provider was specified.
241 if ($form_provider) {
242 $query .= " AND b.provider_id = ?";
243 array_push($sqlBindArray,$form_provider);
245 // If selected important codes
246 if($_POST['form_details']) {
247 $query .= " AND c.financial_reporting = '1'";
249 $query .= " GROUP BY b.code ORDER BY b.code, fe.date, fe.id ";
250 $res = sqlStatement($query,$sqlBindArray);
251 $grand_total_units = 0;
252 $grand_total_amt_billed = 0;
253 $grand_total_amt_paid = 0;
254 $grand_total_amt_adjustment = 0;
255 $grand_total_amt_balance = 0;
257 while ($erow = sqlFetchArray($res)) {
259 $row['pid'] = $erow['pid'];
260 $row['provider_id'] = $erow['provider_id'];
261 $row['Procedure codes'] = $erow['code'];
262 $row['Units'] = $erow['units'];
263 $row['Amt Billed'] = $erow['billed'];
264 $row['Paid Amt'] = $erow['PaidAmount'];
265 $row['Adjustment Amt'] = $erow['AdjustAmount'];
266 $row['Balance Amt'] = $erow['Balance'];
267 $row['financial_reporting'] = $erow['financial_reporting'];
268 $rows[$erow['pid'] . '|' . $erow['code'] . '|' . $erow['units']] = $row;
270 if ($_POST['form_csvexport']) {
273 echo '"Procedure codes",';
275 echo '"Amt Billed",';
277 echo '"Adjustment Amt",';
278 echo '"Balance Amt",' . "\n";
281 ?
> <div id
="report_results">
285 <?php
echo xlt('Procedure Codes'); ?
>
288 <?php
echo xlt('Units'); ?
>
291 <?php
echo xlt('Amt Billed'); ?
>
294 <?php
echo xlt('Paid Amt'); ?
>
297 <?php
echo xlt('Adjustment Amt'); ?
>
300 <?php
echo xlt('Balance Amt'); ?
>
307 foreach ($rows as $key => $row) {
311 if($row['financial_reporting']){ $bgcolor = "#FFFFDD"; }else { $bgcolor = "#FFDDDD"; }
312 $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 $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 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
317 $grand_total_units +
= $row['Units'];
318 $grand_total_amt_billed +
= $row['Amt Billed'];
319 $grand_total_amt_paid +
= $row['Paid Amt'];
320 $grand_total_amt_adjustment +
= $row['Adjustment Amt'];
321 $grand_total_amt_balance +
= $row['Balance Amt'];
323 if ($_POST['form_csvexport']) { echo $csv; }
327 if (!$_POST['form_csvexport']) {
328 echo "<tr bgcolor='#ffffff'>\n";
329 echo " <td class='detail'>" . xlt("Grand Total") . "</td>\n";
330 echo " <td class='detail'>" . text($grand_total_units) . "</td>\n";
331 echo " <td class='detail'>" .
332 text(oeFormatMoney($grand_total_amt_billed)) . "</td>\n";
333 echo " <td class='detail'>" .
334 text(oeFormatMoney($grand_total_amt_paid)) . "</td>\n";
335 echo " <td class='detail'>" .
336 text(oeFormatMoney($grand_total_amt_adjustment)) . "</td>\n";
337 echo " <td class='detail'>" .
338 text(oeFormatMoney($grand_total_amt_balance)) . "</td>\n";
347 if (! $_POST['form_csvexport']) {
348 if ( $_POST['form_refresh'] && count($print) != 1)
350 echo "<span style='font-size:10pt;'>";
351 echo xlt('No matches found. Try search again.');
353 echo '<script>document.getElementById("report_results").style.display="none";</script>';
354 echo '<script>document.getElementById("controls").style.display="none";</script>';
357 if (!$_POST['form_refresh'] && !$_POST['form_csvexport']) { ?
>
359 <?php
echo xlt('Please input search criteria above, and click Submit to view results.' ); ?
>
365 <!-- stuff
for the popup calendar
-->
367 <link rel
='stylesheet' href
='<?php echo $css_header ?>' type
='text/css'>
368 <style type
="text/css">@import
url(../../library
/dynarch_calendar
.css
);</style
>
369 <script type
="text/javascript" src
="../../library/dynarch_calendar.js"></script
>
370 <?php
include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?
>
371 <script type
="text/javascript" src
="../../library/dynarch_calendar_setup.js"></script
>
372 <script language
="Javascript">
373 Calendar
.setup({inputField
:"form_from_date", ifFormat
:"%Y-%m-%d", button
:"img_from_date"});
374 Calendar
.setup({inputField
:"form_to_date", ifFormat
:"%Y-%m-%d", button
:"img_to_date"});
375 top
.restoreSession();
379 } // End not csv export