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
12 * @link http://www.open-emr.org
13 * @author Rod Roark <rod@sunsetsystems.com>
15 * @author Brady Miller <brady.g.miller@gmail.com>
16 * @copyright Copyright (C) 2006-2016 Rod Roark <rod@sunsetsystems.com>
17 * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
18 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
22 require_once("../globals.php");
23 require_once("$srcdir/patient.inc");
24 require_once("$srcdir/acl.inc");
25 require_once "$srcdir/options.inc.php";
26 require_once "$srcdir/appointments.inc.php";
28 use OpenEMR\Core\Header
;
30 $grand_total_units = 0;
31 $grand_total_amt_billed = 0;
32 $grand_total_amt_paid = 0;
33 $grand_total_amt_adjustment = 0;
34 $grand_total_amt_balance = 0;
37 if (! acl_check('acct', 'rep')) {
38 die(xlt("Unauthorized access."));
41 $form_from_date = (isset($_POST['form_from_date'])) ?
DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
42 $form_to_date = (isset($_POST['form_to_date'])) ?
DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
43 $form_facility = $_POST['form_facility'];
44 $form_provider = $_POST['form_provider'];
46 if ($_POST['form_csvexport']) {
47 header("Pragma: public");
49 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
50 header("Content-Type: application/force-download");
51 header("Content-Disposition: attachment; filename=svc_financial_report_".attr($form_from_date)."--".attr($form_to_date).".csv");
52 header("Content-Description: File Transfer");
59 <title
><?php
echo xlt('Financial Summary by Service Code') ?
></title
>
61 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
63 <style type
="text/css">
64 /* specifically include & exclude from printing */
70 #report_parameters_daterange {
79 /* specifically exclude some from the screen */
81 #report_parameters_daterange {
88 <script language
="JavaScript">
89 $
(document
).ready(function() {
90 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
91 var win
= top
.printLogSetup ? top
: opener
.top
;
92 win
.printLogSetup(document
.getElementById('printbutton'));
94 $
('.datepicker').datetimepicker({
95 <?php
$datetimepicker_timepicker = false; ?
>
96 <?php
$datetimepicker_showseconds = false; ?
>
97 <?php
$datetimepicker_formatInput = true; ?
>
98 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
99 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
105 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class="body_top">
106 <span
class='title'><?php
echo xlt('Report'); ?
> - <?php
echo xlt('Financial Summary by Service Code'); ?
></span
>
107 <form method
='post' action
='svc_code_financial_report.php' id
='theform' onsubmit
='return top.restoreSession()'>
108 <div id
="report_parameters">
109 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
110 <input type
='hidden' name
='form_csvexport' id
='form_csvexport' value
=''/>
114 <div style
='float:left'>
117 <td
class='control-label'>
118 <?php
echo xlt('Facility'); ?
>:
121 <?php
dropdown_facility($form_facility, 'form_facility', true); ?
>
123 <td
class='control-label'><?php
echo xlt('Provider'); ?
>:</td
>
125 // Build a drop-down list of providers.
127 $query = "SELECT id, lname, fname FROM users WHERE ".
128 "authorized = 1 ORDER BY lname, fname"; //(CHEMED) facility filter
129 $ures = sqlStatement($query);
130 echo " <select name='form_provider' class='form-control'>\n";
131 echo " <option value=''>-- " . xlt('All') . " --\n";
132 while ($urow = sqlFetchArray($ures)) {
133 $provid = $urow['id'];
134 echo " <option value='" . attr($provid) ."'";
135 if ($provid == $_POST['form_provider']) {
139 echo ">" . text($urow['lname']) . ", " . text($urow['fname']) . "\n";
146 <td
class='control-label'>
147 <?php
echo xlt('From'); ?
>: 
; 
; 
; 
;
150 <input type
='text' class='datepicker form-control' name
='form_from_date' id
="form_from_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_from_date)); ?>'>
152 <td
class='control-label'>
153 <?php
echo xlt('To'); ?
>:
156 <input type
='text' class='datepicker form-control' name
='form_to_date' id
="form_to_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_to_date)); ?>'>
159 <div
class="checkbox">
160 <label
><input type
='checkbox' name
='form_details'<?php
161 if ($_POST['form_details']) {
164 <?php
echo xlt('Important Codes'); ?
></label
>
171 <td align
='left' valign
='middle' height
="100%">
172 <table style
='border-left:1px solid; width:100%; height:100%' >
175 <div
class="text-center">
176 <div
class="btn-group" role
="group">
177 <a href
='#' class='btn btn-default btn-save' onclick
='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
178 <?php
echo xlt('Submit'); ?
>
180 <?php
if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) { ?
>
181 <a href
='#' class='btn btn-default btn-print' id
='printbutton'>
182 <?php
echo xlt('Print'); ?
>
184 <a href
='#' class='btn btn-default btn-transmit' onclick
='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
185 <?php
echo xlt('CSV Export'); ?
>
196 </div
> <!-- end of parameters
-->
203 if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) {
205 $from_date = $form_from_date;
206 $to_date = $form_to_date;
207 $sqlBindArray = array();
208 $query = "select b.code,sum(b.units) as units,sum(b.fee) as billed,sum(ar_act.paid) as PaidAmount, " .
209 "sum(ar_act.adjust) as AdjustAmount,(sum(b.fee)-(sum(ar_act.paid)+sum(ar_act.adjust))) as Balance, " .
210 "c.financial_reporting " .
211 "FROM form_encounter as fe " .
212 "JOIN billing as b on b.pid=fe.pid and b.encounter=fe.encounter " .
213 "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 " .
214 "ON ar_act.pid=b.pid and ar_act.encounter=b.encounter and ar_act.code=b.code " .
215 "LEFT OUTER JOIN codes AS c ON c.code = b.code " .
216 "INNER JOIN code_types AS ct ON ct.ct_key = b.code_type AND ct.ct_fee = '1' " .
217 "WHERE b.code_type != 'COPAY' AND b.activity = 1 /* AND b.fee != 0 */ AND " .
218 "fe.date >= ? AND fe.date <= ?";
219 array_push($sqlBindArray, "$from_date 00:00:00", "$to_date 23:59:59");
220 // If a facility was specified.
221 if ($form_facility) {
222 $query .= " AND fe.facility_id = ?";
223 array_push($sqlBindArray, $form_facility);
226 // If a provider was specified.
227 if ($form_provider) {
228 $query .= " AND b.provider_id = ?";
229 array_push($sqlBindArray, $form_provider);
232 // If selected important codes
233 if ($_POST['form_details']) {
234 $query .= " AND c.financial_reporting = '1'";
237 $query .= " GROUP BY b.code ORDER BY b.code, fe.date, fe.id ";
238 $res = sqlStatement($query, $sqlBindArray);
239 $grand_total_units = 0;
240 $grand_total_amt_billed = 0;
241 $grand_total_amt_paid = 0;
242 $grand_total_amt_adjustment = 0;
243 $grand_total_amt_balance = 0;
245 while ($erow = sqlFetchArray($res)) {
247 $row['pid'] = $erow['pid'];
248 $row['provider_id'] = $erow['provider_id'];
249 $row['Procedure codes'] = $erow['code'];
250 $row['Units'] = $erow['units'];
251 $row['Amt Billed'] = $erow['billed'];
252 $row['Paid Amt'] = $erow['PaidAmount'];
253 $row['Adjustment Amt'] = $erow['AdjustAmount'];
254 $row['Balance Amt'] = $erow['Balance'];
255 $row['financial_reporting'] = $erow['financial_reporting'];
256 $rows[$erow['pid'] . '|' . $erow['code'] . '|' . $erow['units']] = $row;
259 if ($_POST['form_csvexport']) {
262 echo '"Procedure codes",';
264 echo '"Amt Billed",';
266 echo '"Adjustment Amt",';
267 echo '"Balance Amt",' . "\n";
270 ?
> <div id
="report_results">
271 <table id
='mymaintable'>
274 <?php
echo xlt('Procedure Codes'); ?
>
277 <?php
echo xlt('Units'); ?
>
280 <?php
echo xlt('Amt Billed'); ?
>
283 <?php
echo xlt('Paid Amt'); ?
>
286 <?php
echo xlt('Adjustment Amt'); ?
>
289 <?php
echo xlt('Balance Amt'); ?
>
297 foreach ($rows as $key => $row) {
301 if ($row['financial_reporting']) {
302 $bgcolor = "#FFFFDD";
304 $bgcolor = "#FFDDDD";
307 $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>";
309 $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";
311 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
312 $grand_total_units +
= $row['Units'];
313 $grand_total_amt_billed +
= $row['Amt Billed'];
314 $grand_total_amt_paid +
= $row['Paid Amt'];
315 $grand_total_amt_adjustment +
= $row['Adjustment Amt'];
316 $grand_total_amt_balance +
= $row['Balance Amt'];
318 if ($_POST['form_csvexport']) {
325 if (!$_POST['form_csvexport']) {
326 echo "<tr bgcolor='#ffffff'>\n";
327 echo " <td class='detail'>" . xlt("Grand Total") . "</td>\n";
328 echo " <td class='detail'>" . text($grand_total_units) . "</td>\n";
329 echo " <td class='detail'>" .
330 text(oeFormatMoney($grand_total_amt_billed)) . "</td>\n";
331 echo " <td class='detail'>" .
332 text(oeFormatMoney($grand_total_amt_paid)) . "</td>\n";
333 echo " <td class='detail'>" .
334 text(oeFormatMoney($grand_total_amt_adjustment)) . "</td>\n";
335 echo " <td class='detail'>" .
336 text(oeFormatMoney($grand_total_amt_balance)) . "</td>\n";
344 if (! $_POST['form_csvexport']) {
345 if ($_POST['form_refresh'] && empty($print)) {
346 echo "<span style='font-size:10pt;'>";
347 echo xlt('No matches found. Try search again.');
349 echo '<script>document.getElementById("report_results").style.display="none";</script>';
350 echo '<script>document.getElementById("controls").style.display="none";</script>';
353 if (!$_POST['form_refresh'] && !$_POST['form_csvexport']) { ?
>
355 <?php
echo xlt('Please input search criteria above, and click Submit to view results.'); ?
>
363 } // End not csv export