5 * (TLH) Added payor,provider,fixed cvs download to included selected fields
6 * (TLH) Added ability to download selected invoices only or all for patient
9 * @link http://www.open-emr.org
10 * @author Rod Roark <rod@sunsetsystems.com>
11 * @author Terry Hill <terry@lillysystems.com>
12 * @author Brady Miller <brady.g.miller@gmail.com>
13 * @copyright Copyright (c) 2006-2016 Rod Roark <rod@sunsetsystems.com>
14 * @copyright Copyright (c) 2015 Terry Hill <terry@lillysystems.com>
15 * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
16 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
20 require_once("../globals.php");
21 require_once("../../library/patient.inc");
22 require_once("../../library/invoice_summary.inc.php");
23 require_once("../../library/sl_eob.inc.php");
24 require_once "$srcdir/options.inc.php";
26 use OpenEMR\Core\Header
;
30 $export_patient_count = 0;
33 $form_date = (isset($_POST['form_date'])) ?
DateToYYYYMMDD($_POST['form_date']) : "";
34 $form_to_date = (isset($_POST['form_to_date'])) ?
DateToYYYYMMDD($_POST['form_to_date']) : "";
35 $is_ins_summary = $_POST['form_category'] == 'Ins Summary';
36 $is_due_ins = ($_POST['form_category'] == 'Due Ins') ||
$is_ins_summary;
37 $is_due_pt = $_POST['form_category'] == 'Due Pt';
38 $is_all = $_POST['form_category'] == 'All';
39 $is_ageby_lad = strpos($_POST['form_ageby'], 'Last') !== false;
40 $form_facility = $_POST['form_facility'];
41 $form_provider = $_POST['form_provider'];
42 $form_payer_id = $_POST['form_payer_id'];
44 if ($_POST['form_refresh'] ||
$_POST['form_export'] ||
$_POST['form_csvexport']) {
45 if ($is_ins_summary) {
48 $form_cb_pubpid = false;
49 $form_cb_adate = false;
50 $form_cb_policy = false;
51 $form_cb_phone = false;
52 $form_cb_city = false;
53 $form_cb_ins1 = false;
54 $form_cb_referrer = false;
55 $form_cb_idays = false;
58 $form_cb_ssn = $_POST['form_cb_ssn'] ?
true : false;
59 $form_cb_dob = $_POST['form_cb_dob'] ?
true : false;
60 $form_cb_pubpid = $_POST['form_cb_pubpid'] ?
true : false;
61 $form_cb_adate = $_POST['form_cb_adate'] ?
true : false;
62 $form_cb_policy = $_POST['form_cb_policy'] ?
true : false;
63 $form_cb_phone = $_POST['form_cb_phone'] ?
true : false;
64 $form_cb_city = $_POST['form_cb_city'] ?
true : false;
65 $form_cb_ins1 = $_POST['form_cb_ins1'] ?
true : false;
66 $form_cb_referrer = $_POST['form_cb_referrer'] ?
true : false;
67 $form_cb_idays = $_POST['form_cb_idays'] ?
true : false;
68 $form_cb_err = $_POST['form_cb_err'] ?
true : false;
73 $form_cb_pubpid = false;
74 $form_cb_adate = false;
75 $form_cb_policy = false;
76 $form_cb_phone = true;
77 $form_cb_city = false;
78 $form_cb_ins1 = false;
79 $form_cb_referrer = false;
80 $form_cb_idays = false;
84 $form_age_cols = (int) $_POST['form_age_cols'];
85 $form_age_inc = (int) $_POST['form_age_inc'];
86 if ($form_age_cols > 0 && $form_age_cols < 50) {
87 if ($form_age_inc <= 0) {
108 if ($form_cb_pubpid) {
112 if ($form_cb_policy) {
116 if ($form_cb_phone) {
128 if ($form_cb_referrer) {
132 if ($form_provider) {
136 if ($form_payer_id) {
140 $final_colspan = $form_cb_adate ?
6 : 5;
141 $form_cb_with_debt = $_POST['form_cb_with_debt'] ?
true : false;
142 $grand_total_charges = 0;
143 $grand_total_adjustments = 0;
144 $grand_total_paid = 0;
145 $grand_total_agedbal = array();
146 for ($c = 0; $c < $form_age_cols;
148 $grand_total_agedbal[$c] = 0;
152 function bucks($amount)
155 return oeFormatMoney($amount); // was printf("%.2f", $amount);
159 function endPatient($ptrow)
161 global $export_patient_count, $export_dollars, $bgcolor;
162 global $grand_total_charges, $grand_total_adjustments, $grand_total_paid;
163 global $grand_total_agedbal, $is_due_ins, $form_age_cols;
164 global $initial_colspan, $final_colspan, $form_cb_idays, $form_cb_err;
166 if (!$ptrow['pid']) {
170 $pt_balance = $ptrow['amount'] - $ptrow['paid'];
172 if ($_POST['form_export']) {
173 // This is a fixed-length format used by Transworld Systems. Your
174 // needs will surely be different, so consider this just an example.
176 echo "1896H"; // client number goes here
177 echo "000"; // filler
178 echo sprintf("%-30s", substr($ptrow['ptname'], 0, 30));
179 echo sprintf("%-30s", " ");
180 echo sprintf("%-30s", substr($ptrow['address1'], 0, 30));
181 echo sprintf("%-15s", substr($ptrow['city'], 0, 15));
182 echo sprintf("%-2s", substr($ptrow['state'], 0, 2));
183 echo sprintf("%-5s", $ptrow['zipcode'] ?
substr($ptrow['zipcode'], 0, 5) : '00000');
184 echo "1"; // service code
185 echo sprintf("%010.0f", $ptrow['pid']); // transmittal number = patient id
187 echo sprintf("%-15s", substr($ptrow['ss'], 0, 15));
188 echo substr($ptrow['dos'], 5, 2) . substr($ptrow['dos'], 8, 2) . substr($ptrow['dos'], 2, 2);
189 echo sprintf("%08.0f", $pt_balance * 100);
190 echo sprintf("%-9s\n", " ");
192 if (!$_POST['form_without']) {
193 sqlStatement("UPDATE patient_data SET " .
194 "billing_note = CONCAT('IN COLLECTIONS " . date("Y-m-d") . "', billing_note) " .
195 "WHERE pid = ? ", array($ptrow['pid']));
198 $export_patient_count +
= 1;
199 $export_dollars +
= $pt_balance;
200 } else if ($_POST['form_csvexport']) {
201 $export_patient_count +
= 1;
202 $export_dollars +
= $pt_balance;
204 if ($ptrow['count'] > 1) {
205 echo " <tr bgcolor='$bgcolor'>\n";
206 /***************************************************************
207 echo " <td class='detail' colspan='$initial_colspan'>";
208 echo " </td>\n";
209 echo " <td class='detotal' colspan='$final_colspan'> Total Patient Balance:</td>\n";
210 ***************************************************************/
211 echo " <td class='detotal' colspan='" . ($initial_colspan +
$final_colspan) .
212 "'> " . xlt('Total Patient Balance') . ":</td>\n";
213 /**************************************************************/
214 if ($form_age_cols) {
215 for ($c = 0; $c < $form_age_cols; ++
$c) {
216 echo " <td class='detotal' align='right'> " .
217 text(oeFormatMoney($ptrow['agedbal'][$c])) . " </td>\n";
220 echo " <td class='detotal' align='right'> " .
221 text(oeFormatMoney($pt_balance)) . " </td>\n";
224 if ($form_cb_idays) {
225 echo " <td class='detail'> </td>\n";
228 echo " <td class='detail' colspan='2'> </td>\n";
230 echo " <td class='detail'> </td>\n";
237 $grand_total_charges +
= $ptrow['charges'];
238 $grand_total_adjustments +
= $ptrow['adjustments'];
239 $grand_total_paid +
= $ptrow['paid'];
240 for ($c = 0; $c < $form_age_cols; ++
$c) {
241 $grand_total_agedbal[$c] +
= $ptrow['agedbal'][$c];
245 function endInsurance($insrow)
247 global $export_patient_count, $export_dollars, $bgcolor;
248 global $grand_total_charges, $grand_total_adjustments, $grand_total_paid;
249 global $grand_total_agedbal, $is_due_ins, $form_age_cols;
250 global $initial_colspan, $form_cb_idays, $form_cb_err;
251 if (!$insrow['pid']) {
255 $ins_balance = $insrow['amount'] - $insrow['paid'];
256 if ($_POST['form_export'] ||
$_POST['form_csvexport']) {
257 // No exporting of insurance summaries.
258 $export_patient_count +
= 1;
259 $export_dollars +
= $ins_balance;
261 echo " <tr bgcolor='$bgcolor'>\n";
262 echo " <td class='detail'>" . text($insrow['insname']) . "</td>\n";
263 echo " <td class='detotal' align='right'> " .
264 text(oeFormatMoney($insrow['charges'])) . " </td>\n";
265 echo " <td class='detotal' align='right'> " .
266 text(oeFormatMoney($insrow['adjustments'])) . " </td>\n";
267 echo " <td class='detotal' align='right'> " .
268 text(oeFormatMoney($insrow['paid'])) . " </td>\n";
269 if ($form_age_cols) {
270 for ($c = 0; $c < $form_age_cols; ++
$c) {
271 echo " <td class='detotal' align='right'> " .
272 text(oeFormatMoney($insrow['agedbal'][$c])) . " </td>\n";
275 echo " <td class='detotal' align='right'> " .
276 text(oeFormatMoney($ins_balance)) . " </td>\n";
282 $grand_total_charges +
= $insrow['charges'];
283 $grand_total_adjustments +
= $insrow['adjustments'];
284 $grand_total_paid +
= $insrow['paid'];
285 for ($c = 0; $c < $form_age_cols; ++
$c) {
286 $grand_total_agedbal[$c] +
= $insrow['agedbal'][$c];
290 function getInsName($payerid)
292 $tmp = sqlQuery("SELECT name FROM insurance_companies WHERE id = ? ", array($payerid));
296 // In the case of CSV export only, a download will be forced.
297 if ($_POST['form_csvexport']) {
298 header("Pragma: public");
299 header("Expires: 0");
300 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
301 header("Content-Type: application/force-download");
302 header("Content-Disposition: attachment; filename=collections_report.csv");
303 header("Content-Description: File Transfer");
309 <title
><?php
echo xlt('Collections Report')?
></title
>
311 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
313 <style type
="text/css">
319 #report_parameters_daterange {
328 /* specifically exclude some from the screen */
330 #report_parameters_daterange {
337 <script language
="JavaScript">
338 $
(document
).ready(function() {
339 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
340 var win
= top
.printLogSetup ? top
: opener
.top
;
341 win
.printLogSetup(document
.getElementById('printbutton'));
343 $
('.datepicker').datetimepicker({
344 <?php
$datetimepicker_timepicker = false; ?
>
345 <?php
$datetimepicker_showseconds = false; ?
>
346 <?php
$datetimepicker_formatInput = true; ?
>
347 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
348 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
352 function checkAll(checked
) {
353 var f
= document
.forms
[0];
354 for (var i
= 0; i
< f
.elements
.length
; ++i
) {
355 var ename
= f
.elements
[i
].name
;
356 if (ename
.indexOf('form_cb[') == 0)
357 f
.elements
[i
].checked
= checked
;
364 <body
class="body_top">
366 <span
class='title'><?php
echo xlt('Report'); ?
> - <?php
echo xlt('Collections'); ?
></span
>
368 <form method
='post' action
='collections_report.php' enctype
='multipart/form-data' id
='theform' onsubmit
='return top.restoreSession()'>
370 <div id
="report_parameters">
372 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
373 <input type
='hidden' name
='form_export' id
='form_export' value
=''/>
374 <input type
='hidden' name
='form_csvexport' id
='form_csvexport' value
=''/>
379 <div style
='float:left'>
383 <td
class='control-label'>
386 <td
><?php
echo xlt('Displayed Columns') ?
>:</td
>
390 <label
><input type
='checkbox' name
='form_cb_ssn'<?php
echo ($form_cb_ssn) ?
' checked' : ''; ?
>>
391 <?php
echo xlt('SSN') ?
> 
;</label
>
394 <label
><input type
='checkbox' name
='form_cb_dob'<?php
echo ($form_cb_dob) ?
' checked' : ''; ?
>>
395 <?php
echo xlt('DOB') ?
> 
;</label
>
398 <label
><input type
='checkbox' name
='form_cb_pubpid'<?php
echo ($form_cb_pubpid) ?
' checked' : ''; ?
>>
399 <?php
echo xlt('ID') ?
> 
;</label
>
402 <label
><input type
='checkbox' name
='form_cb_policy'<?php
echo ($form_cb_policy) ?
' checked' : ''; ?
>>
403 <?php
echo xlt('Policy') ?
> 
;</label
>
406 <label
><input type
='checkbox' name
='form_cb_phone'<?php
echo ($form_cb_phone) ?
' checked' : ''; ?
>>
407 <?php
echo xlt('Phone') ?
> 
;</label
>
410 <label
><input type
='checkbox' name
='form_cb_city'<?php
echo ($form_cb_city) ?
' checked' : ''; ?
>>
411 <?php
echo xlt('City') ?
> 
;</label
>
416 <label
><input type
='checkbox' name
='form_cb_ins1'<?php
echo ($form_cb_ins1) ?
' checked' : ''; ?
>>
417 <?php
echo xlt('Primary Ins') ?
> 
;</label
>
420 <label
><input type
='checkbox' name
='form_cb_referrer'<?php
echo ($form_cb_referrer) ?
' checked' : ''; ?
>>
421 <?php
echo xlt('Referrer') ?
> 
;</label
>
424 <label
><input type
='checkbox' name
='form_cb_adate'<?php
echo ($form_cb_adate) ?
' checked' : ''; ?
>>
425 <?php
echo xlt('Act Date') ?
> 
;</label
>
428 <label
><input type
='checkbox' name
='form_cb_idays'<?php
echo ($form_cb_idays) ?
' checked' : ''; ?
>>
429 <?php
echo xlt('Inactive Days') ?
> 
;</label
>
432 <label
><input type
='checkbox' name
='form_cb_err'<?php
echo ($form_cb_err) ?
' checked' : ''; ?
>>
433 <?php
echo xlt('Errors') ?
></label
>
444 <td
class='control-label'>
445 <?php
echo xlt('Service Date'); ?
>:
448 <input type
='text' class='datepicker form-control' name
='form_date' id
="form_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_date)); ?>'>
450 <td
class='control-label'>
451 <?php
echo xlt('To'); ?
>:
454 <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)); ?>'>
457 <select name
='form_category' class='form-control'>
459 foreach (array('Open' => xl('Open'),'Due Pt' => xl('Due Pt'),'Due Ins' => xl('Due Ins'),'Ins Summary' => xl('Ins Summary'),'Credits' => xl('Credits'),'All' => xl('All')) as $key => $value) {
460 echo " <option value='" . attr($key) . "'";
461 if ($_POST['form_category'] == $key) {
465 echo ">" . text($value) . "</option>\n";
475 <td
class='control-label'>
476 <?php
echo xlt('Facility'); ?
>:
479 <?php
dropdown_facility($form_facility, 'form_facility', false); ?
>
482 <td
class='control-label'>
483 <?php
echo xlt('Payor'); ?
>:
486 <?php
# added dropdown for payors (TLH)
487 $insurancei = getInsuranceProviders();
488 echo " <select name='form_payer_id' class='form-control'>\n";
489 echo " <option value='0'>-- " . xlt('All') . " --</option>\n";
490 foreach ($insurancei as $iid => $iname) {
491 echo "<option value='" . attr($iid) . "'";
492 if ($iid == $_POST['form_payer_id']) {
496 echo ">" . text($iname) . "</option>\n";
497 if ($iid == $_POST['form_payer_id']) {
498 $ins_co_name = $iname;
508 <td
class='control-label'>
509 <?php
echo xlt('Age By') ?
>:
512 <select name
='form_ageby' class='form-control'>
514 foreach (array( 'Service Date'=>xl('Service Date'), 'Last Activity Date'=>xl('Last Activity Date')) as $key => $value) {
515 echo " <option value='" . attr($key) . "'";
516 if ($_POST['form_ageby'] == $value) {
520 echo ">" . text($value) . "</option>\n";
526 <td
class='control-label'>
527 <?php
echo xlt('Provider') ?
>:
530 <?php
# Build a drop-down list of providers.
533 $query = "SELECT id, lname, fname FROM users WHERE ".
534 "authorized = 1 ORDER BY lname, fname"; #(CHEMED) facility filter
536 $ures = sqlStatement($query);
538 echo " <select name='form_provider' class='form-control'>\n";
539 echo " <option value=''>-- " . xlt('All') . " --\n";
541 while ($urow = sqlFetchArray($ures)) {
542 $provid = $urow['id'];
543 echo " <option value='" . attr($provid) . "'";
544 if ($provid == $_POST['form_provider']) {
548 echo ">" . text($urow['lname']) . ", " . text($urow['fname']) . "\n";
549 if ($provid == $_POST['form_provider']) {
550 $provider_name = $urow['lname'] . ", " . $urow['fname'];
559 <td
class='control-label'>
560 <?php
echo xlt('Aging Columns') ?
>:
563 <input type
='text' name
='form_age_cols' class='form-control' size
='2' value
='<?php echo attr($form_age_cols); ?>' />
565 <td
class='control-label'>
566 <?php
echo xlt('Days/Col') ?
>:
569 <input type
='text' name
='form_age_inc' class='form-control' size
='3' value
='<?php echo attr($form_age_inc); ?>' />
572 <div
class="checkbox">
573 <label
><input type
='checkbox' name
='form_cb_with_debt'<?php
echo ($form_cb_with_debt) ?
' checked' : ''; ?
>>
574 <?php
echo xlt('Patients with debt') ?
></label
>
588 <td align
='left' valign
='middle' height
="100%">
589 <table style
='border-left:1px solid; width:100%; height:100%' >
592 <div
class="text-center">
593 <div
class="btn-group" role
="group">
594 <a href
='#' class='btn btn-default btn-save' onclick
='$("#form_refresh").attr("value","true"); $("#form_csvexport").val(""); $("#theform").submit();'>
595 <?php
echo xlt('Submit'); ?
>
597 <?php
if ($_POST['form_refresh']) { ?
>
598 <a href
='#' class='btn btn-default btn-print' onclick
='window.print()'>
599 <?php
echo xlt('Print'); ?
>
614 } // end not form_csvexport
616 if ($_POST['form_refresh'] ||
$_POST['form_export'] ||
$_POST['form_csvexport']) {
620 if ($_POST['form_export'] ||
$_POST['form_csvexport']) {
622 foreach ($_POST['form_cb'] as $key => $value) {
623 list($key_newval['pid'], $key_newval['encounter']) = explode(".", $key);
624 $newkey = $key_newval['pid'];
625 $newencounter = $key_newval['encounter'];
626 # added this condition to handle the downloading of individual invoices (TLH)
627 if ($_POST['form_individual'] ==1) {
628 $where .= " OR f.encounter = ? ";
629 array_push($sqlArray, $newencounter);
631 $where .= " OR f.pid = ? ";
632 array_push($sqlArray, $newkey);
645 $where .= "f.date >= ? AND f.date <= ? ";
646 array_push($sqlArray, $form_date.' 00:00:00', $form_to_date.' 23:59:59');
648 $where .= "f.date >= ? AND f.date <= ? ";
649 array_push($sqlArray, $form_date.' 00:00:00', $form_date.' 23:59:59');
653 if ($form_facility) {
658 $where .= "f.facility_id = ? ";
659 array_push($sqlArray, $form_facility);
662 # added for filtering by provider (TLH)
663 if ($form_provider) {
668 $where .= "f.provider_id = ? ";
669 array_push($sqlArray, $form_provider);
676 # added provider from encounter to the query (TLH)
677 $query = "SELECT f.id, f.date, f.pid, CONCAT(w.lname, ', ', w.fname) AS provider_id, f.encounter, f.last_level_billed, " .
678 "f.last_level_closed, f.last_stmt_date, f.stmt_count, f.invoice_refno, " .
679 "p.fname, p.mname, p.lname, p.street, p.city, p.state, " .
680 "p.postal_code, p.phone_home, p.ss, p.billing_note, " .
681 "p.pubpid, p.DOB, CONCAT(u.lname, ', ', u.fname) AS referrer, " .
682 "( SELECT bill_date FROM billing AS b WHERE " .
683 "b.pid = f.pid AND b.encounter = f.encounter AND " .
684 "b.activity = 1 AND b.code_type != 'COPAY' LIMIT 1) AS billdate, " .
685 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
686 "b.pid = f.pid AND b.encounter = f.encounter AND " .
687 "b.activity = 1 AND b.code_type != 'COPAY' ) AS charges, " .
688 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
689 "b.pid = f.pid AND b.encounter = f.encounter AND " .
690 "b.activity = 1 AND b.code_type = 'COPAY' ) AS copays, " .
691 "( SELECT SUM(s.fee) FROM drug_sales AS s WHERE " .
692 "s.pid = f.pid AND s.encounter = f.encounter ) AS sales, " .
693 "( SELECT SUM(a.pay_amount) FROM ar_activity AS a WHERE " .
694 "a.pid = f.pid AND a.encounter = f.encounter ) AS payments, " .
695 "( SELECT SUM(a.adj_amount) FROM ar_activity AS a WHERE " .
696 "a.pid = f.pid AND a.encounter = f.encounter ) AS adjustments " .
697 "FROM form_encounter AS f " .
698 "JOIN patient_data AS p ON p.pid = f.pid " .
699 "LEFT OUTER JOIN users AS u ON u.id = p.ref_providerID " .
700 "LEFT OUTER JOIN users AS w ON w.id = f.provider_id " .
702 "ORDER BY f.pid, f.encounter";
704 $eres = sqlStatement($query, $sqlArray);
706 while ($erow = sqlFetchArray($eres)) {
707 $patient_id = $erow['pid'];
708 $encounter_id = $erow['encounter'];
709 $pt_balance = $erow['charges'] +
$erow['sales'] +
$erow['copays'] - $erow['payments'] - $erow['adjustments'];
710 $pt_balance = 0 +
sprintf("%.2f", $pt_balance); // yes this seems to be necessary
711 $svcdate = substr($erow['date'], 0, 10);
713 if ($form_cb_with_debt && $pt_balance<=0) {
718 if ($_POST['form_refresh'] && ! $is_all) {
719 if ($pt_balance == 0) {
724 if ($_POST['form_category'] == 'Credits') {
725 if ($pt_balance > 0) {
730 // If we have not yet billed the patient, then compute $duncount as a
731 // negative count of the number of insurance plans for which we have not
732 // yet closed out insurance. Here we also compute $insname as the name of
733 // the insurance plan from which we are awaiting payment, and its sequence
734 // number $insposition (1-3).
735 $last_level_closed = $erow['last_level_closed'];
736 $duncount = $erow['stmt_count'];
741 for ($i = 1; $i <= 3; ++
$i) {
742 $tmp = arGetPayerID($patient_id, $svcdate, $i);
751 $duncount = $last_level_closed - count($payerids);
753 if (!empty($payerids[$last_level_closed])) {
754 $insname = getInsName($payerids[$last_level_closed]);
755 $insposition = $last_level_closed +
1;
760 // Skip invoices not in the desired "Due..." category.
762 if ($is_due_ins && $duncount >= 0) {
766 if ($is_due_pt && $duncount < 0) {
770 // echo "<!-- " . $erow['encounter'] . ': ' . $erow['charges'] . ' + ' . $erow['sales'] . ' + ' . $erow['copays'] . ' - ' . $erow['payments'] . ' - ' . $erow['adjustments'] . " -->\n"; // debugging
772 // An invoice is due from the patient if money is owed and we are
773 // not waiting for insurance to pay.
774 $isduept = ($duncount >= 0) ?
" checked" : "";
778 $row['id'] = $erow['id'];
779 $row['invnumber'] = "$patient_id.$encounter_id";
780 $row['custid'] = $patient_id;
781 $row['name'] = $erow['fname'] . ' ' . $erow['lname'];
782 $row['address1'] = $erow['street'];
783 $row['city'] = $erow['city'];
784 $row['state'] = $erow['state'];
785 $row['zipcode'] = $erow['postal_code'];
786 $row['phone'] = $erow['phone_home'];
787 $row['duncount'] = $duncount;
788 $row['dos'] = $svcdate;
789 $row['ss'] = $erow['ss'];
790 $row['DOB'] = $erow['DOB'];
791 $row['pubpid'] = $erow['pubpid'];
792 $row['billnote'] = $erow['billing_note'];
793 $row['referrer'] = $erow['referrer'];
794 $row['provider'] = $erow['provider_id'];
795 $row['irnumber'] = $erow['invoice_refno'];
796 $row['billdate'] = $erow['billdate']; // use this for ins_due claim age date
798 // Also get the primary insurance company name whenever there is one.
800 if ($insposition == 1) {
801 $row['ins1'] = $insname;
803 if (empty($payerids)) {
804 $tmp = arGetPayerID($patient_id, $svcdate, 1);
810 if (!empty($payerids)) {
811 $row['ins1'] = getInsName($payerids[0]);
815 // This computes the invoice's total original charges and adjustments,
816 // date of last activity, and determines if insurance has responded to
817 // all billing items.
818 $invlines = ar_get_invoice_summary($patient_id, $encounter_id, true);
820 // if ($encounter_id == 185) { // debugging
822 // print_r($invlines);
827 $row['adjustments'] = 0;
829 $ins_seems_done = true;
831 foreach ($invlines as $key => $value) {
832 $row['charges'] +
= $value['chg'] +
$value['adj'];
833 $row['adjustments'] +
= 0 - $value['adj'];
834 $row['paid'] +
= $value['chg'] - $value['bal'];
835 foreach ($value['dtl'] as $dkey => $dvalue) {
836 $dtldate = trim(substr($dkey, 0, 10));
837 if ($dtldate && $dtldate > $ladate) {
842 $lckey = strtolower($key);
843 if ($lckey == 'co-pay' ||
$lckey == 'claim') {
847 if (count($value['dtl']) <= 1) {
848 $ins_seems_done = false;
852 // Simulating ar.amount in SQL-Ledger which is charges with adjustments:
853 $row['amount'] = $row['charges'] +
$row['adjustments'];
855 $row['billing_errmsg'] = '';
856 if ($is_due_ins && $last_level_closed < 1 && $ins_seems_done) {
857 $row['billing_errmsg'] = 'Ins1 seems done';
858 } else if ($last_level_closed >= 1 && !$ins_seems_done) {
859 $row['billing_errmsg'] = 'Ins1 seems not done';
862 // Calculate claim age date for due ins
864 $ladate = $row['billdate'];
867 $row['ladate'] = $ladate;
870 $row['inactive_days'] = "n/a";
872 // Compute number of days since last activity.
877 substr($ladate, 5, 2),
878 substr($ladate, 8, 2),
879 substr($ladate, 0, 4)
881 $row['inactive_days'] = floor((time() - $latime) / (60 * 60 * 24));
884 // Look up insurance policy number if we need it.
885 if ($form_cb_policy) {
886 $instype = ($insposition == 2) ?
'secondary' : (($insposition == 3) ?
'tertiary' : 'primary');
887 $insrow = sqlQuery("SELECT policy_number FROM insurance_data WHERE " .
888 "pid = ? AND type = ? AND date <= ? " .
889 "ORDER BY date DESC LIMIT 1", array($patient_id, $instype, $svcdate));
890 $row['policy'] = $insrow['policy_number'];
893 $ptname = $erow['lname'] . ", " . $erow['fname'];
894 if ($erow['mname']) {
895 $ptname .= " " . substr($erow['mname'], 0, 1);
902 $rows[$insname . '|' . $ptname . '|' . $encounter_id] = $row;
908 if ($_POST['form_export']) {
909 echo "<textarea rows='35' cols='100' readonly>";
910 } else if ($_POST['form_csvexport']) {
911 # CSV headers added conditions if they are checked to display then export them (TLH)
913 echo '"' . xl('Insurance') . '",';
914 echo '"' . xl('Name') . '",';
916 echo '"' . xl('SSN') . '",';
920 echo '"' . xl('DOB') . '",';
923 if ($form_cb_pubid) {
924 echo '"' . xl('Pubid') . '",';
927 if ($form_cb_policy) {
928 echo '"' . xl('Policy') . '",';
931 if ($form_cb_phone) {
932 echo '"' . xl('Phone') . '",';
936 echo '"' . xl('City') . '",';
939 echo '"' . xl('Invoice') . '",';
940 echo '"' . xl('DOS') . '",';
941 echo '"' . xl('Referrer') . '",';
942 echo '"' . xl('Provider') . '",';
943 echo '"' . xl('Charge') . '",';
944 echo '"' . xl('Adjust') . '",';
945 echo '"' . xl('Paid') . '",';
946 echo '"' . xl('Balance') . '",';
947 echo '"' . xl('IDays') . '",';
949 echo '"' . xl('LADate') . '",';
950 echo '"' . xl('Error') . '"' . "\n";
952 echo '"' . xl('LADate') . '"' . "\n";
958 <div id
="report_results">
959 <table id
='mymaintable'>
962 <?php
if ($is_due_ins) { ?
>
963 <th
> 
;<?php
echo xlt('Insurance')?
></th
>
965 <?php
if (!$is_ins_summary) { ?
>
966 <th
> 
;<?php
echo xlt('Name')?
></th
>
968 <?php
if ($form_cb_ssn) { ?
>
969 <th
> 
;<?php
echo xlt('SSN')?
></th
>
971 <?php
if ($form_cb_dob) { ?
>
972 <th
> 
;<?php
echo xlt('DOB')?
></th
>
974 <?php
if ($form_cb_pubpid) { ?
>
975 <th
> 
;<?php
echo xlt('ID')?
></th
>
977 <?php
if ($form_cb_policy) { ?
>
978 <th
> 
;<?php
echo xlt('Policy')?
></th
>
980 <?php
if ($form_cb_phone) { ?
>
981 <th
> 
;<?php
echo xlt('Phone')?
></th
>
983 <?php
if ($form_cb_city) { ?
>
984 <th
> 
;<?php
echo xlt('City')?
></th
>
986 <?php
if ($form_cb_ins1 ||
$form_payer_id) { ?
>
987 <th
> 
;<?php
echo xlt('Primary Ins')?
></th
>
989 <?php
if ($form_provider) { ?
>
990 <th
> 
;<?php
echo xlt('Provider')?
></th
>
992 <?php
if ($form_cb_referrer) { ?
>
993 <th
> 
;<?php
echo xlt('Referrer')?
></th
>
995 <?php
if (!$is_ins_summary) { ?
>
996 <th
> 
;<?php
echo xlt('Invoice') ?
></th
>
997 <th
> 
;<?php
echo xlt('Svc Date') ?
></th
>
998 <?php
if ($form_cb_adate) { ?
>
999 <th
> 
;<?php
echo xlt('Act Date')?
></th
>
1002 <th align
="right"><?php
echo xlt('Charge') ?
> 
;</th
>
1003 <th align
="right"><?php
echo xlt('Adjust') ?
> 
;</th
>
1004 <th align
="right"><?php
echo xlt('Paid') ?
> 
;</th
>
1006 // Generate aging headers if appropriate, else balance header.
1007 if ($form_age_cols) {
1008 for ($c = 0; $c < $form_age_cols;) {
1009 echo " <th class='dehead' align='right'>";
1010 echo $form_age_inc * $c;
1011 if (++
$c < $form_age_cols) {
1012 echo "-" . ($form_age_inc * $c - 1);
1021 <th align
="right"><?php
echo xlt('Balance') ?
> 
;</th
>
1025 <?php
if ($form_cb_idays) { ?
>
1026 <th align
="right"><?php
echo xlt('IDays')?
> 
;</th
>
1028 <?php
if (!$is_ins_summary) { ?
>
1029 <th align
="center"><?php
echo xlt('Prv') ?
></th
>
1030 <th align
="center"><?php
echo xlt('Sel') ?
></th
>
1032 <?php
if ($form_cb_err) { ?
>
1033 <th
> 
;<?php
echo xlt('Error')?
></th
>
1040 $ptrow = array('insname' => '', 'pid' => 0);
1043 foreach ($rows as $key => $row) {
1044 list($insname, $ptname, $trash) = explode('|', $key);
1045 list($pid, $encounter) = explode(".", $row['invnumber']);
1046 if ($form_payer_id) {
1047 if ($ins_co_name <> $row['ins1']) {
1052 if ($is_ins_summary && $insname != $ptrow['insname']) {
1053 endInsurance($ptrow);
1054 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
1055 $ptrow = array('insname' => $insname, 'ptname' => $ptname, 'pid' => $pid, 'count' => 1);
1056 foreach ($row as $key => $value) {
1057 $ptrow[$key] = $value;
1060 $ptrow['agedbal'] = array();
1061 } else if (!$is_ins_summary && ($insname != $ptrow['insname'] ||
$pid != $ptrow['pid'])) {
1062 // For the report, this will write the patient totals. For the
1063 // collections export this writes everything for the patient:
1065 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
1066 $ptrow = array('insname' => $insname, 'ptname' => $ptname, 'pid' => $pid, 'count' => 1);
1067 foreach ($row as $key => $value) {
1068 $ptrow[$key] = $value;
1071 $ptrow['agedbal'] = array();
1073 $ptrow['amount'] +
= $row['amount'];
1074 $ptrow['paid'] +
= $row['paid'];
1075 $ptrow['charges'] +
= $row['charges'];
1076 $ptrow['adjustments'] +
= $row['adjustments'];
1080 // Compute invoice balance and aging column number, and accumulate aging.
1081 $balance = $row['charges'] +
$row['adjustments'] - $row['paid'];
1082 if ($form_age_cols) {
1083 $agedate = $is_ageby_lad ?
$row['ladate'] : $row['dos'];
1088 substr($agedate, 5, 2),
1089 substr($agedate, 8, 2),
1090 substr($agedate, 0, 4)
1092 $days = floor((time() - $agetime) / (60 * 60 * 24));
1093 $agecolno = min($form_age_cols - 1, max(0, floor($days / $form_age_inc)));
1094 $ptrow['agedbal'][$agecolno] +
= $balance;
1097 if (!$is_ins_summary && !$_POST['form_export'] && !$_POST['form_csvexport']) {
1098 $in_collections = stristr($row['billnote'], 'IN COLLECTIONS') !== false;
1100 <tr bgcolor
='<?php echo attr($bgcolor) ?>'>
1102 if ($ptrow['count'] == 1) {
1104 echo " <td class='detail'> " . text($insname) ."</td>\n";
1107 echo " <td class='detail'> " . text($ptname) ."</td>\n";
1109 echo " <td class='detail'> " . text($row['ss']) . "</td>\n";
1113 echo " <td class='detail'> " . text(oeFormatShortDate($row['DOB'])) . "</td>\n";
1116 if ($form_cb_pubpid) {
1117 echo " <td class='detail'> " . text($row['pubpid']) . "</td>\n";
1120 if ($form_cb_policy) {
1121 echo " <td class='detail'> " . text($row['policy']) . "</td>\n";
1124 if ($form_cb_phone) {
1125 echo " <td class='detail'> " . text($row['phone']) . "</td>\n";
1128 if ($form_cb_city) {
1129 echo " <td class='detail'> " . text($row['city']) . "</td>\n";
1132 if ($form_cb_ins1 ||
$form_payer_id) {
1133 echo " <td class='detail'> " . text($row['ins1']) . "</td>\n";
1136 if ($form_provider) {
1137 echo " <td class='detail'> " . text($provider_name) . "</td>\n";
1140 if ($form_cb_referrer) {
1141 echo " <td class='detail'> " . text($row['referrer']) . "</td>\n";
1144 echo " <td class='detail' colspan='" . attr($initial_colspan) . "'>";
1145 echo " </td>\n";
1149  
;<a href
="../billing/sl_eob_invoice.php?id=<?php echo attr($row['id']) ?>"
1150 target
="_blank"><?php
echo empty($row['irnumber']) ?
text($row['invnumber']) : text($row['irnumber']); ?
></a
>
1153  
;<?php
echo text(oeFormatShortDate($row['dos'])); ?
>
1155 <?php
if ($form_cb_adate) { ?
>
1157  
;<?php
echo text(oeFormatShortDate($row['ladate'])); ?
>
1160 <td
class="detail" align
="right">
1161 <?php
echo text(bucks($row['charges'])) ?
> 
;
1163 <td
class="detail" align
="right">
1164 <?php
echo text(bucks($row['adjustments'])) ?
> 
;
1166 <td
class="detail" align
="right">
1167 <?php
echo text(bucks($row['paid'])) ?
> 
;
1170 if ($form_age_cols) {
1171 for ($c = 0; $c < $form_age_cols; ++
$c) {
1172 echo " <td class='detail' align='right'>";
1173 if ($c == $agecolno) {
1174 echo text(bucks($balance));
1177 echo " </td>\n";
1181 <td
class="detail" align
="right"><?php
echo text(bucks($balance)); ?
> 
;</td
>
1186 if ($form_cb_idays) {
1187 echo " <td class='detail' align='right'>";
1188 echo text($row['inactive_days']) . " </td>\n";
1191 <td
class="detail" align
="center">
1192 <?php
echo $row['duncount'] ?
text($row['duncount']) : " " ?
>
1194 <td
class="detail" align
="center">
1196 if ($in_collections) {
1197 echo " <b><font color='red'>IC</font></b>\n";
1199 echo " <input type='checkbox' name='form_cb[" . attr($row['invnumber']) . "]' />\n";
1205 echo " <td class='detail'> ";
1206 echo text($row['billing_errmsg']) . "</td>\n";
1211 } // end not export and not insurance summary
1213 else if ($_POST['form_csvexport']) {
1214 # The CSV detail line is written here added conditions for checked items (TLH).
1215 $balance = $row['charges'] +
$row['adjustments'] - $row['paid'];
1217 // echo '"' . $insname . '",';
1218 echo '"' . $row['ins1'] . '",';
1219 echo '"' . $ptname . '",';
1221 echo '"' . $row['ss'] . '",';
1225 echo '"' . oeFormatShortDate($row['DOB']) . '",';
1228 if ($form_cb_pubid) {
1229 echo '"' . $row['pubpid'] . '",';
1232 if ($form_cb_policy) {
1233 echo '"' . $row['policy'] . '",';
1236 if ($form_cb_phone) {
1237 echo '"' . $row['phone'] . '",';
1240 if ($form_cb_city) {
1241 echo '"' . $row['city'] . '",';
1244 echo '"' . (empty($row['irnumber']) ?
$row['invnumber'] : $row['irnumber']) . '",';
1245 echo '"' . oeFormatShortDate($row['dos']) . '",';
1246 echo '"' . $row['referrer'] . '",';
1247 echo '"' . $row['provider'] . '",';
1248 echo '"' . oeFormatMoney($row['charges']) . '",';
1249 echo '"' . oeFormatMoney($row['adjustments']) . '",';
1250 echo '"' . oeFormatMoney($row['paid']) . '",';
1251 echo '"' . oeFormatMoney($balance) . '",';
1252 echo '"' . $row['inactive_days'] . '",';
1254 echo '"' . oeFormatShortDate($row['ladate']) . '",';
1255 echo '"' . $row['billing_errmsg'] . '"' . "\n";
1257 echo '"' . oeFormatShortDate($row['ladate']) . '"' . "\n";
1260 } // end $form_csvexport
1263 if ($is_ins_summary) {
1264 endInsurance($ptrow);
1269 if ($_POST['form_export']) {
1270 echo "</textarea>\n";
1271 $alertmsg .= "$export_patient_count patients with total of " .
1272 oeFormatMoney($export_dollars) . " have been exported ";
1273 if ($_POST['form_without']) {
1274 $alertmsg .= "but NOT flagged as in collections.";
1276 $alertmsg .= "AND flagged as in collections.";
1278 } else if ($_POST['form_csvexport']) {
1279 // echo "</textarea>\n";
1280 // $alertmsg .= "$export_patient_count patients representing $" .
1281 // sprintf("%.2f", $export_dollars) . " have been exported.";
1283 echo " <tr bgcolor='#ffffff'>\n";
1284 if ($is_ins_summary) {
1285 echo " <td class='dehead'> " . xlt('Report Totals') . ":</td>\n";
1287 echo " <td class='detail' colspan='" . attr($initial_colspan) . "'>\n";
1288 echo " </td>\n";
1289 echo " <td class='dehead' colspan='" . attr($final_colspan - 3) .
1290 "'> " . xlt('Report Totals') . ":</td>\n";
1293 echo " <td class='dehead' align='right'> " .
1294 text(oeFormatMoney($grand_total_charges)) . " </td>\n";
1295 echo " <td class='dehead' align='right'> " .
1296 text(oeFormatMoney($grand_total_adjustments)) . " </td>\n";
1297 echo " <td class='dehead' align='right'> " .
1298 text(oeFormatMoney($grand_total_paid)) . " </td>\n";
1299 if ($form_age_cols) {
1300 for ($c = 0; $c < $form_age_cols; ++
$c) {
1301 echo " <td class='dehead' align='right'>" .
1302 text(oeFormatMoney($grand_total_agedbal[$c])) . " </td>\n";
1305 echo " <td class='dehead' align='right'>" .
1306 text(oeFormatMoney($grand_total_charges +
1307 $grand_total_adjustments - $grand_total_paid)) . " </td>\n";
1310 if ($form_cb_idays) {
1311 echo " <td class='detail'> </td>\n";
1314 if (!$is_ins_summary) {
1315 echo " <td class='detail' colspan='2'> </td>\n";
1319 echo " <td class='detail'> </td>\n";
1326 } // end if form_refresh
1329 if (!$_POST['form_csvexport']) {
1330 if (!$_POST['form_export']) {
1333 <div style
='float;margin-top:5px'>
1334 <div
class="btn-group pull-left" role
="group">
1335 <a href
='javascript:;' class='btn btn-default btn-save' onclick
='checkAll(true)'><?php
echo xlt('Select All'); ?
></a
>
1336 <a href
='javascript:;' class='btn btn-default btn-cancel' onclick
='checkAll(false)'><?php
echo xlt('Clear All'); ?
></a
>
1337 <a href
='javascript:;' class='btn btn-default btn-transmit' onclick
='$("#form_csvexport").attr("value","true"); $("#theform").submit();'>
1338 <?php
echo xlt('Export Selected as CSV'); ?
>
1340 <a href
='javascript:;' class='btn btn-default btn-transmit' onclick
='$("#form_export").attr("value","true"); $("#form_csvexport").val(""); $("#theform").submit();'>
1341 <?php
echo xlt('Export Selected to Collections'); ?
>
1345 <div style
='float:left'>
1346 <label
><input type
='checkbox' name
='form_individual' value
='1' /> <?php
echo xlt('Export Individual Invoices') ?
> 
; 
;</label
>
1349 <div style
='float:left'>
1350 <label
><input type
='checkbox' name
='form_without' value
='1' /> <?php
echo xlt('Without Update') ?
></label
>
1359 <script language
="JavaScript">
1362 echo "alert('" . addslashes($alertmsg) . "');\n";
1369 } // end not form_csvexport