4 * This is a report of Financial Summary by Service Code.
6 * This is a summary of service code charge/pay/adjust and balance,
7 * with the ability to pick "important" codes to either highlight or
8 * limit to list to. Important codes can be configured in
9 * Administration->Service section by assigning code with
10 * 'Service Reporting'.
13 * @link https://www.open-emr.org
14 * @author Rod Roark <rod@sunsetsystems.com>
16 * @author Brady Miller <brady.g.miller@gmail.com>
17 * @copyright Copyright (C) 2006-2020 Rod Roark <rod@sunsetsystems.com>
18 * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
19 * @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.php");
24 require_once "$srcdir/options.inc.php";
25 require_once "$srcdir/appointments.inc.php";
27 use OpenEMR\Common\Acl\AclMain
;
28 use OpenEMR\Common\Csrf\CsrfUtils
;
29 use OpenEMR\Common\Twig\TwigContainer
;
30 use OpenEMR\Core\Header
;
32 if (!AclMain
::aclCheckCore('acct', 'rep_a')) {
33 echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Financial Summary by Service Code")]);
38 if (!CsrfUtils
::verifyCsrfToken($_POST["csrf_token_form"])) {
39 CsrfUtils
::csrfNotVerified();
43 $grand_total_units = 0;
44 $grand_total_amt_billed = 0;
45 $grand_total_amt_paid = 0;
46 $grand_total_amt_adjustment = 0;
47 $grand_total_amt_balance = 0;
49 $form_from_date = (isset($_POST['form_from_date'])) ?
DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
50 $form_to_date = (isset($_POST['form_to_date'])) ?
DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
51 $form_facility = $_POST['form_facility'] ??
null;
52 $form_provider = $_POST['form_provider'] ??
null;
54 if (!empty($_POST['form_csvexport'])) {
55 header("Pragma: public");
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");
62 } else { // end export
66 <title
><?php
echo xlt('Financial Summary by Service Code') ?
></title
>
68 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
71 /* specifically include & exclude from printing */
77 #report_parameters_daterange {
86 /* specifically exclude some from the screen */
88 #report_parameters_daterange {
97 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
98 var win
= top
.printLogSetup ? top
: opener
.top
;
99 win
.printLogSetup(document
.getElementById('printbutton'));
101 $
('.datepicker').datetimepicker({
102 <?php
$datetimepicker_timepicker = false; ?
>
103 <?php
$datetimepicker_showseconds = false; ?
>
104 <?php
$datetimepicker_formatInput = true; ?
>
105 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
106 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
112 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class="body_top">
113 <span
class='title'><?php
echo xlt('Report'); ?
> - <?php
echo xlt('Financial Summary by Service Code'); ?
></span
>
114 <form method
='post' action
='svc_code_financial_report.php' id
='theform' onsubmit
='return top.restoreSession()'>
115 <input type
="hidden" name
="csrf_token_form" value
="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
116 <div id
="report_parameters">
117 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
118 <input type
='hidden' name
='form_csvexport' id
='form_csvexport' value
=''/>
122 <div style
='float:left'>
125 <td
class='col-form-label'>
126 <?php
echo xlt('Facility'); ?
>:
129 <?php
dropdown_facility($form_facility, 'form_facility', true); ?
>
131 <td
class='col-form-label'><?php
echo xlt('Provider'); ?
>:</td
>
133 // Build a drop-down list of providers.
135 $query = "SELECT id, lname, fname FROM users WHERE " .
136 "authorized = 1 ORDER BY lname, fname"; //(CHEMED) facility filter
137 $ures = sqlStatement($query);
138 echo " <select name='form_provider' class='form-control'>\n";
139 echo " <option value=''>-- " . xlt('All') . " --\n";
140 while ($urow = sqlFetchArray($ures)) {
141 $provid = $urow['id'];
142 echo " <option value='" . attr($provid) . "'";
143 if (!empty($_POST['form_provider']) && ($provid == $_POST['form_provider'])) {
147 echo ">" . text($urow['lname']) . ", " . text($urow['fname']) . "\n";
154 <td
class='col-form-label'>
155 <?php
echo xlt('From'); ?
>: 
; 
; 
; 
;
158 <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)); ?>'>
160 <td
class='col-form-label'>
161 <?php
echo xlt('To{{Range}}'); ?
>:
164 <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)); ?>'>
167 <div
class="checkbox">
168 <label
><input type
='checkbox' name
='form_details'<?php
169 if (!empty($_POST['form_details'])) {
172 <?php
echo xlt('Important Codes'); ?
></label
>
179 <td
class='h-100' align
='left' valign
='middle'>
180 <table
class='w-100 h-100' style
='border-left:1px solid;'>
183 <div
class="text-center">
184 <div
class="btn-group" role
="group">
185 <a href
='#' class='btn btn-secondary btn-save' onclick
='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
186 <?php
echo xlt('Submit'); ?
>
188 <?php
if (!empty($_POST['form_refresh']) ||
!empty($_POST['form_csvexport'])) { ?
>
189 <a href
='#' class='btn btn-secondary btn-print' id
='printbutton'>
190 <?php
echo xlt('Print'); ?
>
192 <a href
='#' class='btn btn-secondary btn-transmit' onclick
='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
193 <?php
echo xlt('CSV Export'); ?
>
204 </div
> <!-- end of parameters
-->
211 if (!empty($_POST['form_refresh']) ||
!empty($_POST['form_csvexport'])) {
213 $from_date = $form_from_date;
214 $to_date = $form_to_date;
215 $sqlBindArray = array();
216 $query = "select b.code,sum(b.units) as units,sum(b.fee) as billed,sum(ar_act.paid) as PaidAmount, " .
217 "sum(ar_act.adjust) as AdjustAmount,(sum(b.fee)-(sum(ar_act.paid)+sum(ar_act.adjust))) as Balance, " .
218 "c.financial_reporting " .
219 "FROM form_encounter as fe " .
220 "JOIN billing as b on b.pid=fe.pid and b.encounter=fe.encounter " .
221 "JOIN (select pid, encounter, code, sum(pay_amount) as paid, sum(adj_amount) as adjust " .
222 "from ar_activity WHERE deleted IS NULL group by pid, encounter, code) as ar_act " .
223 "ON ar_act.pid=b.pid and ar_act.encounter=b.encounter and ar_act.code=b.code " .
224 "LEFT OUTER JOIN codes AS c ON c.code = b.code " .
225 "INNER JOIN code_types AS ct ON ct.ct_key = b.code_type AND ct.ct_fee = '1' " .
226 "WHERE b.code_type != 'COPAY' AND b.activity = 1 /* AND b.fee != 0 */ AND " .
227 "fe.date >= ? AND fe.date <= ?";
228 array_push($sqlBindArray, "$from_date 00:00:00", "$to_date 23:59:59");
229 // If a facility was specified.
230 if ($form_facility) {
231 $query .= " AND fe.facility_id = ?";
232 array_push($sqlBindArray, $form_facility);
235 // If a provider was specified.
236 if ($form_provider) {
237 $query .= " AND b.provider_id = ?";
238 array_push($sqlBindArray, $form_provider);
241 // If selected important codes
242 if (!empty($_POST['form_details'])) {
243 $query .= " AND c.financial_reporting = '1'";
246 $query .= " GROUP BY b.code ORDER BY b.code, fe.date, fe.id ";
247 $res = sqlStatement($query, $sqlBindArray);
248 $grand_total_units = 0;
249 $grand_total_amt_billed = 0;
250 $grand_total_amt_paid = 0;
251 $grand_total_amt_adjustment = 0;
252 $grand_total_amt_balance = 0;
254 while ($erow = sqlFetchArray($res)) {
256 $row['pid'] = $erow['pid'] ??
null;
257 $row['provider_id'] = $erow['provider_id'] ??
null;
258 $row['Procedure codes'] = $erow['code'];
259 $row['Units'] = $erow['units'];
260 $row['Amt Billed'] = $erow['billed'];
261 $row['Paid Amt'] = $erow['PaidAmount'];
262 $row['Adjustment Amt'] = $erow['AdjustAmount'];
263 $row['Balance Amt'] = $erow['Balance'];
264 $row['financial_reporting'] = $erow['financial_reporting'];
265 $rows[($erow['pid'] ??
null) . '|' . $erow['code'] . '|' . $erow['units']] = $row;
268 if ($_POST['form_csvexport']) {
271 echo csvEscape("Procedure codes") . ',';
272 echo csvEscape("Units") . ',';
273 echo csvEscape("Amt Billed") . ',';
274 echo csvEscape("Paid Amt") . ',';
275 echo csvEscape("Adjustment Amt") . ',';
276 echo csvEscape("Balance Amt") . "\n";
280 <div id
="report_results">
281 <table
class='table' id
='mymaintable'>
282 <thead
class='thead-light'>
284 <?php
echo xlt('Procedure Codes'); ?
>
287 <?php
echo xlt('Units'); ?
>
290 <?php
echo xlt('Amt Billed'); ?
>
293 <?php
echo xlt('Paid Amt'); ?
>
296 <?php
echo xlt('Adjustment Amt'); ?
>
299 <?php
echo xlt('Balance Amt'); ?
>
307 foreach ($rows as $key => $row) {
311 if ($row['financial_reporting']) {
312 $bgcolor = "#FFFFDD";
314 $bgcolor = "#FFDDDD";
317 $print = "<tr bgcolor='" . attr($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>";
319 $csv = csvEscape($row['Procedure codes']) . ',' . csvEscape($row['Units']) . ',' . csvEscape(oeFormatMoney($row['Amt Billed'])) . ',' . csvEscape(oeFormatMoney($row['Paid Amt'])) . ',' . csvEscape(oeFormatMoney($row['Adjustment Amt'])) . ',' . csvEscape(oeFormatMoney($row['Balance Amt'])) . "\n";
321 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
322 $grand_total_units +
= $row['Units'];
323 $grand_total_amt_billed +
= $row['Amt Billed'];
324 $grand_total_amt_paid +
= $row['Paid Amt'];
325 $grand_total_amt_adjustment +
= $row['Adjustment Amt'];
326 $grand_total_amt_balance +
= $row['Balance Amt'];
328 if ($_POST['form_csvexport']) {
335 if (!$_POST['form_csvexport']) {
336 echo "<tr class='bg-white'>\n";
337 echo " <td class='detail'>" . xlt("Grand Total") . "</td>\n";
338 echo " <td class='detail'>" . text($grand_total_units) . "</td>\n";
339 echo " <td class='detail'>" .
340 text(oeFormatMoney($grand_total_amt_billed)) . "</td>\n";
341 echo " <td class='detail'>" .
342 text(oeFormatMoney($grand_total_amt_paid)) . "</td>\n";
343 echo " <td class='detail'>" .
344 text(oeFormatMoney($grand_total_amt_adjustment)) . "</td>\n";
345 echo " <td class='detail'>" .
346 text(oeFormatMoney($grand_total_amt_balance)) . "</td>\n";
354 if (empty($_POST['form_csvexport'])) {
355 if (!empty($_POST['form_refresh']) && empty($print)) {
356 echo "<span style='font-size:10pt;'>";
357 echo xlt('No matches found. Try search again.');
359 echo '<script>document.getElementById("report_results").style.display="none";</script>';
362 if (empty($_POST['form_refresh']) && empty($_POST['form_csvexport'])) { ?
>
364 <?php
echo xlt('Please input search criteria above, and click Submit to view results.'); ?
>
372 } // End not csv export