2 // Copyright (C) 2006-2009 Rod Roark <rod@sunsetsystems.com>
4 // This program is free software; you can redistribute it and/or
5 // modify it under the terms of the GNU General Public License
6 // as published by the Free Software Foundation; either version 2
7 // of the License, or (at your option) any later version.
9 require_once("../globals.php");
10 require_once("../../library/patient.inc");
11 require_once("../../library/sql-ledger.inc");
12 require_once("../../library/invoice_summary.inc.php");
13 require_once("../../library/sl_eob.inc.php");
15 $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
19 $export_patient_count = 0;
22 $today = date("Y-m-d");
24 $form_date = fixDate($_POST['form_date'], "");
25 $form_to_date = fixDate($_POST['form_to_date'], "");
26 $is_ins_summary = $_POST['form_category'] == xl('Ins Summary');
27 $is_due_ins = ($_POST['form_category'] == xl('Due Ins')) ||
$is_ins_summary;
28 $is_due_pt = $_POST['form_category'] == xl('Due Pt');
29 $is_all = $_POST['form_category'] == xl('All');
30 $is_ageby_lad = strpos($_POST['form_ageby'], 'Last') !== false;
31 $form_facility = $_POST['form_facility'];
33 if ($_POST['form_search'] ||
$_POST['form_export'] ||
$_POST['form_csvexport']) {
34 if ($is_ins_summary) {
37 $form_cb_pubpid = false;
38 $form_cb_adate = false;
39 $form_cb_policy = false;
40 $form_cb_phone = false;
41 $form_cb_city = false;
42 $form_cb_ins1 = false;
43 $form_cb_referrer = false;
44 $form_cb_idays = false;
47 $form_cb_ssn = $_POST['form_cb_ssn'] ?
true : false;
48 $form_cb_dob = $_POST['form_cb_dob'] ?
true : false;
49 $form_cb_pubpid = $_POST['form_cb_pubpid'] ?
true : false;
50 $form_cb_adate = $_POST['form_cb_adate'] ?
true : false;
51 $form_cb_policy = $_POST['form_cb_policy'] ?
true : false;
52 $form_cb_phone = $_POST['form_cb_phone'] ?
true : false;
53 $form_cb_city = $_POST['form_cb_city'] ?
true : false;
54 $form_cb_ins1 = $_POST['form_cb_ins1'] ?
true : false;
55 $form_cb_referrer = $_POST['form_cb_referrer'] ?
true : false;
56 $form_cb_idays = $_POST['form_cb_idays'] ?
true : false;
57 $form_cb_err = $_POST['form_cb_err'] ?
true : false;
62 $form_cb_pubpid = false;
63 $form_cb_adate = false;
64 $form_cb_policy = false;
65 $form_cb_phone = true;
66 $form_cb_city = false;
67 $form_cb_ins1 = false;
68 $form_cb_referrer = false;
69 $form_cb_idays = false;
72 $form_age_cols = (int) $_POST['form_age_cols'];
73 $form_age_inc = (int) $_POST['form_age_inc'];
74 if ($form_age_cols > 0 && $form_age_cols < 50) {
75 if ($form_age_inc <= 0) $form_age_inc = 30;
82 if ($is_due_ins ) ++
$initial_colspan;
83 if ($form_cb_ssn ) ++
$initial_colspan;
84 if ($form_cb_dob ) ++
$initial_colspan;
85 if ($form_cb_pubpid ) ++
$initial_colspan;
86 if ($form_cb_policy ) ++
$initial_colspan;
87 if ($form_cb_phone ) ++
$initial_colspan;
88 if ($form_cb_city ) ++
$initial_colspan;
89 if ($form_cb_ins1 ) ++
$initial_colspan;
90 if ($form_cb_referrer) ++
$initial_colspan;
92 $final_colspan = $form_cb_adate ?
6 : 5;
94 $grand_total_charges = 0;
95 $grand_total_adjustments = 0;
96 $grand_total_paid = 0;
97 $grand_total_agedbal = array();
98 for ($c = 0; $c < $form_age_cols; ++
$c) $grand_total_agedbal[$c] = 0;
100 if (!$INTEGRATED_AR) SLConnect();
102 function bucks($amount) {
104 printf("%.2f", $amount);
107 function endPatient($ptrow) {
108 global $export_patient_count, $export_dollars, $bgcolor;
109 global $grand_total_charges, $grand_total_adjustments, $grand_total_paid;
110 global $grand_total_agedbal, $is_due_ins, $form_age_cols;
111 global $initial_colspan, $final_colspan, $form_cb_idays, $form_cb_err;
113 if (!$ptrow['pid']) return;
115 $pt_balance = $ptrow['amount'] - $ptrow['paid'];
117 if ($_POST['form_export']) {
118 // This is a fixed-length format used by Transworld Systems. Your
119 // needs will surely be different, so consider this just an example.
121 echo "1896H"; // client number goes here
122 echo "000"; // filler
123 echo sprintf("%-30s", substr($ptrow['ptname'], 0, 30));
124 echo sprintf("%-30s", " ");
125 echo sprintf("%-30s", substr($ptrow['address1'], 0, 30));
126 echo sprintf("%-15s", substr($ptrow['city'], 0, 15));
127 echo sprintf("%-2s", substr($ptrow['state'], 0, 2));
128 echo sprintf("%-5s", $ptrow['zipcode'] ?
substr($ptrow['zipcode'], 0, 5) : '00000');
129 echo "1"; // service code
130 echo sprintf("%010.0f", $ptrow['pid']); // transmittal number = patient id
132 echo sprintf("%-15s", substr($ptrow['ss'], 0, 15));
133 echo substr($ptrow['dos'], 5, 2) . substr($ptrow['dos'], 8, 2) . substr($ptrow['dos'], 2, 2);
134 echo sprintf("%08.0f", $pt_balance * 100);
135 echo sprintf("%-9s\n", " ");
137 if (!$_POST['form_without']) {
138 sqlStatement("UPDATE patient_data SET " .
139 "genericname2 = 'Billing', " .
140 "genericval2 = CONCAT('IN COLLECTIONS " . date("Y-m-d") . "', genericval2) " .
141 "WHERE pid = '" . $ptrow['pid'] . "'");
143 $export_patient_count +
= 1;
144 $export_dollars +
= $pt_balance;
146 else if ($_POST['form_csvexport']) {
147 $export_patient_count +
= 1;
148 $export_dollars +
= $pt_balance;
151 if ($ptrow['count'] > 1) {
152 echo " <tr bgcolor='$bgcolor'>\n";
153 echo " <td class='detail' colspan='$initial_colspan'>";
154 echo " </td>\n";
155 echo " <td class='detotal' colspan='$final_colspan'> Total Patient Balance:</td>\n";
156 if ($form_age_cols) {
157 for ($c = 0; $c < $form_age_cols; ++
$c) {
158 echo " <td class='detotal' align='right'> " .
159 sprintf("%.2f", $ptrow['agedbal'][$c]) . " </td>\n";
163 echo " <td class='detotal' align='right'> " .
164 sprintf("%.2f", $pt_balance) . " </td>\n";
166 if ($form_cb_idays) echo " <td class='detail'> </td>\n";
167 echo " <td class='detail' colspan='2'> </td>\n";
168 if ($form_cb_err) echo " <td class='detail'> </td>\n";
172 $grand_total_charges +
= $ptrow['charges'];
173 $grand_total_adjustments +
= $ptrow['adjustments'];
174 $grand_total_paid +
= $ptrow['paid'];
175 for ($c = 0; $c < $form_age_cols; ++
$c) {
176 $grand_total_agedbal[$c] +
= $ptrow['agedbal'][$c];
180 function endInsurance($insrow) {
181 global $export_patient_count, $export_dollars, $bgcolor;
182 global $grand_total_charges, $grand_total_adjustments, $grand_total_paid;
183 global $grand_total_agedbal, $is_due_ins, $form_age_cols;
184 global $initial_colspan, $form_cb_idays, $form_cb_err;
185 if (!$insrow['pid']) return;
186 $ins_balance = $insrow['amount'] - $insrow['paid'];
187 if ($_POST['form_export'] ||
$_POST['form_csvexport']) {
188 // No exporting of insurance summaries.
189 $export_patient_count +
= 1;
190 $export_dollars +
= $ins_balance;
193 echo " <tr bgcolor='$bgcolor'>\n";
194 echo " <td class='detail'>" . $insrow['insname'] . "</td>\n";
195 echo " <td class='detotal' align='right'> " .
196 sprintf("%.2f", $insrow['charges']) . " </td>\n";
197 echo " <td class='detotal' align='right'> " .
198 sprintf("%.2f", $insrow['adjustments']) . " </td>\n";
199 echo " <td class='detotal' align='right'> " .
200 sprintf("%.2f", $insrow['paid']) . " </td>\n";
201 if ($form_age_cols) {
202 for ($c = 0; $c < $form_age_cols; ++
$c) {
203 echo " <td class='detotal' align='right'> " .
204 sprintf("%.2f", $insrow['agedbal'][$c]) . " </td>\n";
208 echo " <td class='detotal' align='right'> " .
209 sprintf("%.2f", $ins_balance) . " </td>\n";
213 $grand_total_charges +
= $insrow['charges'];
214 $grand_total_adjustments +
= $insrow['adjustments'];
215 $grand_total_paid +
= $insrow['paid'];
216 for ($c = 0; $c < $form_age_cols; ++
$c) {
217 $grand_total_agedbal[$c] +
= $insrow['agedbal'][$c];
221 function getInsName($payerid) {
222 $tmp = sqlQuery("SELECT name FROM insurance_companies WHERE id = '$payerid'");
226 // In the case of CSV export only, a download will be forced.
227 if ($_POST['form_csvexport']) {
228 header("Pragma: public");
229 header("Expires: 0");
230 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
231 header("Content-Type: application/force-download");
232 header("Content-Disposition: attachment; filename=collections_report.csv");
233 header("Content-Description: File Transfer");
239 <?php
if (function_exists('html_header_show')) html_header_show(); ?
>
240 <link rel
=stylesheet href
="<?php echo $css_header;?>" type
="text/css">
241 <title
><?php
xl('Collections Report','e')?
></title
>
242 <style type
="text/css">
243 body
{ font
-family
:sans
-serif
; font
-size
:10pt
; font
-weight
:normal
}
244 .dehead
{ color
:#000000; font-family:sans-serif; font-size:10pt; font-weight:bold }
245 .detail
{ color
:#000000; font-family:sans-serif; font-size:10pt; font-weight:normal }
246 .detotal
{ color
:#996600; font-family:sans-serif; font-size:10pt; font-weight:normal }
249 <script language
="JavaScript">
251 function checkAll(checked
) {
252 var f
= document
.forms
[0];
253 for (var i
= 0; i
< f
.elements
.length
; ++i
) {
254 var ename
= f
.elements
[i
].name
;
255 if (ename
.indexOf('form_cb[') == 0)
256 f
.elements
[i
].checked
= checked
;
264 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0'>
267 <form method
='post' action
='collections_report.php' enctype
='multipart/form-data'>
269 <table border
='0' cellpadding
='5' cellspacing
='0' width
='98%'>
276 <tr bgcolor
='#ddddff'>
278 <input type
='checkbox' name
='form_cb_ssn'<?php
if ($form_cb_ssn) echo ' checked'; ?
>>
279 <?php
xl('SSN','e') ?
> 
;
280 <input type
='checkbox' name
='form_cb_dob'<?php
if ($form_cb_dob) echo ' checked'; ?
>>
281 <?php
xl('DOB','e') ?
> 
;
282 <input type
='checkbox' name
='form_cb_pubpid'<?php
if ($form_cb_pubpid) echo ' checked'; ?
>>
283 <?php
xl('ID','e') ?
> 
;
284 <input type
='checkbox' name
='form_cb_policy'<?php
if ($form_cb_policy) echo ' checked'; ?
>>
285 <?php
xl('Policy','e') ?
> 
;
286 <input type
='checkbox' name
='form_cb_phone'<?php
if ($form_cb_phone) echo ' checked'; ?
>>
287 <?php
xl('Phone','e') ?
> 
;
288 <input type
='checkbox' name
='form_cb_city'<?php
if ($form_cb_city) echo ' checked'; ?
>>
289 <?php
xl('City','e') ?
> 
;
290 <input type
='checkbox' name
='form_cb_ins1'<?php
if ($form_cb_ins1) echo ' checked'; ?
>>
291 <?php
xl('Primary Ins','e') ?
> 
;
292 <input type
='checkbox' name
='form_cb_referrer'<?php
if ($form_cb_referrer) echo ' checked'; ?
>>
293 <?php
xl('Referrer','e') ?
> 
;
294 <input type
='checkbox' name
='form_cb_adate'<?php
if ($form_cb_adate) echo ' checked'; ?
>>
295 <?php
xl('Act Date','e') ?
> 
;
296 <input type
='checkbox' name
='form_cb_idays'<?php
if ($form_cb_idays) echo ' checked'; ?
>>
297 <?php
xl('Inactive Days','e') ?
> 
;
298 <input type
='checkbox' name
='form_cb_err'<?php
if ($form_cb_err) echo ' checked'; ?
>>
299 <?php
xl('Errors','e') ?
>
303 <tr bgcolor
='#ddddff'>
306 // Build a drop-down list of facilities.
308 $query = "SELECT id, name FROM facility ORDER BY name";
309 $fres = sqlStatement($query);
310 echo " <select name='form_facility'>\n";
311 echo " <option value=''>-- " . xl('All Facilities') . " --\n";
312 while ($frow = sqlFetchArray($fres)) {
313 $facid = $frow['id'];
314 echo " <option value='$facid'";
315 if ($facid == $form_facility) echo " selected";
316 echo ">" . $frow['name'] . "\n";
321 <?php
xl('Age By','e') ?
>:
322 <select name
='form_ageby'>
324 foreach (array('Service Date', 'Last Activity Date') as $value) {
325 echo " <option value='$value'";
326 if ($_POST['form_ageby'] == $value) echo " selected";
327 echo ">" . xl($value) . "</option>\n";
332 <?php
xl('Aging Columns:','e') ?
>
333 <input type
='text' name
='form_age_cols' size
='2' value
='<?php echo $form_age_cols; ?>' />
335 <?php
xl('Days per Column:','e') ?
>
336 <input type
='text' name
='form_age_inc' size
='3' value
='<?php echo $form_age_inc; ?>' />
340 <tr bgcolor
='#ddddff'>
342 <?php
xl('Service Date:','e')?
>
343 <input type
='text' name
='form_date' id
="form_date" size
='10' value
='<?php echo $_POST['form_date
']; ?>'
344 onkeyup
='datekeyup(this,mypcc)' onblur
='dateblur(this,mypcc)'
345 title
='<?php xl("Date of service mm/dd/yyyy","e")?>' />
346 <img src
='../pic/show_calendar.gif' align
='absbottom' width
='24' height
='22'
347 id
='img_from_date' border
='0' alt
='[?]' style
='cursor:pointer'
348 title
='<?php xl('Click here to choose a date
','e
'); ?>' />
350 <?php
xl('To:','e')?
>
351 <input type
='text' name
='form_to_date' id
="form_to_date" size
='10' value
='<?php echo $_POST['form_to_date
']; ?>'
352 onkeyup
='datekeyup(this,mypcc)' onblur
='dateblur(this,mypcc)'
353 title
='<?php xl("Ending DOS mm/dd/yyyy if you wish to enter a range","e")?>' />
354 <img src
='../pic/show_calendar.gif' align
='absbottom' width
='24' height
='22'
355 id
='img_to_date' border
='0' alt
='[?]' style
='cursor:pointer'
356 title
='<?php xl('Click here to choose a date
','e
'); ?>' />
358 <select name
='form_category'>
360 foreach (array(xl('Open'),xl('Due Pt'),xl('Due Ins'),xl('Ins Summary'),xl('Credits'),xl('All')) as $value) {
361 echo " <option value='$value'";
362 if ($_POST['form_category'] == $value) echo " selected";
363 echo ">$value</option>\n";
368 <input type
='submit' name
='form_search' value
='<?php xl("Search","e")?>'>
370 <input type
='button' value
='<?php xl('Print','e
'); ?>' onclick
='window.print()' />
383 } // end not form_csvexport
385 if ($_POST['form_search'] ||
$_POST['form_export'] ||
$_POST['form_csvexport']) {
389 if ($INTEGRATED_AR) {
390 if ($_POST['form_export'] ||
$_POST['form_csvexport']) {
392 foreach ($_POST['form_cb'] as $key => $value) $where .= " OR f.pid = $key";
396 if ($where) $where .= " AND ";
398 $where .= "f.date >= '$form_date 00:00:00' AND f.date <= '$form_to_date 23:59:59'";
401 $where .= "f.date >= '$form_date 00:00:00' AND f.date <= '$form_date 23:59:59'";
404 if ($form_facility) {
405 if ($where) $where .= " AND ";
406 $where .= "f.facility_id = '$form_facility'";
412 $query = "SELECT f.id, f.date, f.pid, f.encounter, f.last_level_billed, " .
413 "f.last_level_closed, f.last_stmt_date, f.stmt_count, " .
414 "p.fname, p.mname, p.lname, p.street, p.city, p.state, " .
415 "p.postal_code, p.phone_home, p.ss, p.genericname2, p.genericval2, " .
416 "p.pubpid, p.DOB, CONCAT(u.lname, ', ', u.fname) AS referrer, " .
417 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
418 "b.pid = f.pid AND b.encounter = f.encounter AND " .
419 "b.activity = 1 AND b.code_type != 'COPAY' ) AS charges, " .
420 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
421 "b.pid = f.pid AND b.encounter = f.encounter AND " .
422 "b.activity = 1 AND b.code_type = 'COPAY' ) AS copays, " .
423 "( SELECT SUM(s.fee) FROM drug_sales AS s WHERE " .
424 "s.pid = f.pid AND s.encounter = f.encounter ) AS sales, " .
425 "( SELECT SUM(a.pay_amount) FROM ar_activity AS a WHERE " .
426 "a.pid = f.pid AND a.encounter = f.encounter ) AS payments, " .
427 "( SELECT SUM(a.adj_amount) FROM ar_activity AS a WHERE " .
428 "a.pid = f.pid AND a.encounter = f.encounter ) AS adjustments " .
429 "FROM form_encounter AS f " .
430 "JOIN patient_data AS p ON p.pid = f.pid " .
431 "LEFT OUTER JOIN users AS u ON u.id = p.providerID " .
433 "ORDER BY f.pid, f.encounter";
434 $eres = sqlStatement($query);
436 while ($erow = sqlFetchArray($eres)) {
437 $patient_id = $erow['pid'];
438 $encounter_id = $erow['encounter'];
439 $pt_balance = $erow['charges'] +
$erow['sales'] +
$erow['copays'] - $erow['payments'] - $erow['adjustments'];
440 $svcdate = substr($erow['date'], 0, 10);
442 // echo "<!-- " . $erow['charges'] . ' + ' . $erow['copays'] . ' - ' . $erow['payments'] . ' - ' . $erow['adjustments'] . " -->\n"; // debugging
444 if ($_POST['form_search'] && ! $is_all) {
445 if ($pt_balance == 0) continue;
447 if ($_POST['form_category'] == 'Credits') {
448 if ($pt_balance > 0) continue;
451 // If we have not yet billed the patient, then compute $duncount as a
452 // negative count of the number of insurance plans for which we have not
453 // yet closed out insurance. Here we also compute $insname as the name of
454 // the insurance plan from which we are awaiting payment, and its sequence
455 // number $insposition (1-3).
456 $last_level_closed = $erow['last_level_closed'];
457 $duncount = $erow['stmt_count'];
462 for ($i = 1; $i <= 3; ++
$i) {
463 $tmp = arGetPayerID($patient_id, $svcdate, $i);
464 if (empty($tmp)) break;
467 $duncount = $last_level_closed - count($payerids);
469 if (!empty($payerids[$last_level_closed])) {
470 $insname = getInsName($payerids[$last_level_closed]);
471 $insposition = $last_level_closed +
1;
476 // Skip invoices not in the desired "Due..." category.
478 if ($is_due_ins && $duncount >= 0) continue;
479 if ($is_due_pt && $duncount < 0) continue;
481 // An invoice is due from the patient if money is owed and we are
482 // not waiting for insurance to pay.
483 $isduept = ($duncount >= 0) ?
" checked" : "";
487 $row['id'] = $erow['id'];
488 $row['invnumber'] = "$patient_id.$encounter_id";
489 $row['custid'] = $patient_id;
490 $row['name'] = $erow['fname'] . ' ' . $erow['lname'];
491 $row['address1'] = $erow['street'];
492 $row['city'] = $erow['city'];
493 $row['state'] = $erow['state'];
494 $row['zipcode'] = $erow['postal_code'];
495 $row['phone'] = $erow['phone_home'];
496 $row['duncount'] = $duncount;
497 $row['dos'] = $svcdate;
498 $row['ss'] = $erow['ss'];
499 $row['DOB'] = $erow['DOB'];
500 $row['pubpid'] = $erow['pubpid'];
501 $row['billnote'] = ($erow['genericname2'] == 'Billing') ?
$erow['genericval2'] : '';
502 $row['referrer'] = $erow['referrer'];
504 // Also get the primary insurance company name whenever there is one.
506 if ($insposition == 1) {
507 $row['ins1'] = $insname;
509 if (empty($payerids)) {
510 $tmp = arGetPayerID($patient_id, $svcdate, 1);
511 if (!empty($tmp)) $payerids[] = $tmp;
513 if (!empty($payerids)) {
514 $row['ins1'] = getInsName($payerids[0]);
518 // This computes the invoice's total original charges and adjustments,
519 // date of last activity, and determines if insurance has responded to
520 // all billing items.
521 $invlines = ar_get_invoice_summary($patient_id, $encounter_id, true);
523 // if ($encounter_id == 185) { // debugging
525 // print_r($invlines);
530 $row['adjustments'] = 0;
532 $ins_seems_done = true;
534 foreach ($invlines as $key => $value) {
535 $row['charges'] +
= $value['chg'] +
$value['adj'];
536 $row['adjustments'] +
= 0 - $value['adj'];
537 $row['paid'] +
= $value['chg'] - $value['bal'];
538 foreach ($value['dtl'] as $dkey => $dvalue) {
539 $dtldate = trim(substr($dkey, 0, 10));
540 if ($dtldate && $dtldate > $ladate) $ladate = $dtldate;
542 $lckey = strtolower($key);
543 if ($lckey == 'co-pay' ||
$lckey == 'claim') continue;
544 if (count($value['dtl']) <= 1) $ins_seems_done = false;
547 // Simulating ar.amount in SQL-Ledger which is charges with adjustments:
548 $row['amount'] = $row['charges'] +
$row['adjustments'];
550 $row['billing_errmsg'] = '';
551 if ($is_due_ins && $last_level_closed < 1 && $ins_seems_done)
552 $row['billing_errmsg'] = 'Ins1 seems done';
553 else if ($last_level_closed >= 1 && !$ins_seems_done)
554 $row['billing_errmsg'] = 'Ins1 seems not done';
556 $row['ladate'] = $ladate;
558 // Compute number of days since last activity.
559 $latime = mktime(0, 0, 0, substr($ladate, 5, 2),
560 substr($ladate, 8, 2), substr($ladate, 0, 4));
561 $row['inactive_days'] = floor((time() - $latime) / (60 * 60 * 24));
563 // Look up insurance policy number if we need it.
564 if ($form_cb_policy) {
565 $instype = ($insposition == 2) ?
'secondary' : (($insposition == 3) ?
'tertiary' : 'primary');
566 $insrow = sqlQuery("SELECT policy_number FROM insurance_data WHERE " .
567 "pid = '$patient_id' AND type = '$instype' AND date <= '$svcdate' " .
568 "ORDER BY date DESC LIMIT 1");
569 $row['policy'] = $insrow['policy_number'];
572 $ptname = $erow['lname'] . ", " . $erow['fname'];
573 if ($erow['mname']) $ptname .= " " . substr($erow['mname'], 0, 1);
575 $rows[$insname . '|' . $ptname . '|' . $encounter_id] = $row;
577 } // end $INTEGRATED_AR
579 if ($_POST['form_export'] ||
$_POST['form_csvexport']) {
581 foreach ($_POST['form_cb'] as $key => $value) $where .= " OR ar.customer_id = $key";
586 if ($where) $where .= " AND ";
587 $date1 = substr($form_date, 0, 4) . substr($form_date, 5, 2) .
588 substr($form_date, 8, 2);
590 $date2 = substr($form_to_date, 0, 4) . substr($form_to_date, 5, 2) .
591 substr($form_to_date, 8, 2);
592 $where .= "((CAST (substring(ar.invnumber from position('.' in ar.invnumber) + 1 for 8) AS integer) " .
593 "BETWEEN '$date1' AND '$date2')";
594 $tmp = "date >= '$form_date' AND date <= '$form_to_date'";
597 // This catches old converted invoices where we have no encounters:
598 $where .= "(ar.invnumber LIKE '%.$date1'";
599 $tmp = "date = '$form_date'";
601 // Pick out the encounters from MySQL with the desired DOS:
602 $rez = sqlStatement("SELECT pid, encounter FROM form_encounter WHERE $tmp");
603 while ($row = sqlFetchArray($rez)) {
604 $where .= " OR ar.invnumber = '" . $row['pid'] . "." . $row['encounter'] . "'";
613 // Instead of the subselects in the following query, we will call
614 // get_invoice_summary() in order to get data at the procedure level and
615 // thus decide if insurance appears to be done with each invoice.
617 $query = "SELECT ar.id, ar.invnumber, ar.duedate, ar.amount, ar.paid, " .
618 "ar.intnotes, ar.notes, ar.shipvia, " .
619 "customer.id AS custid, customer.name, customer.address1, " .
620 "customer.city, customer.state, customer.zipcode, customer.phone " .
621 // ", (SELECT SUM(invoice.fxsellprice) FROM invoice WHERE " .
622 // "invoice.trans_id = ar.id AND invoice.fxsellprice > 0) AS charges, " .
623 // "(SELECT SUM(invoice.fxsellprice) FROM invoice WHERE " .
624 // "invoice.trans_id = ar.id AND invoice.fxsellprice < 0) AS adjustments " .
625 "FROM ar JOIN customer ON customer.id = ar.customer_id " .
627 if ($_POST['form_search'] && ! $is_all) {
628 $query .= "AND ar.amount != ar.paid ";
630 $query .= "ORDER BY ar.invnumber";
632 // echo "<!-- $query -->\n"; // debugging
634 $t_res = SLQuery($query);
635 if ($sl_err) die($sl_err);
636 $num_invoices = SLRowCount($t_res);
638 //////////////////////////////////////////////////////////////////
640 for ($irow = 0; $irow < $num_invoices; ++
$irow) {
641 $row = SLGetRow($t_res, $irow);
643 // If a facility was specified then skip invoices whose encounters
644 // do not indicate that facility.
645 if ($form_facility) {
646 list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
647 $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
648 "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
649 "facility_id = '$form_facility'");
650 if (empty($tmp['count'])) continue;
653 $pt_balance = sprintf("%.2f",$row['amount']) - sprintf("%.2f",$row['paid']);
655 if ($_POST['form_category'] == 'Credits') {
656 if ($pt_balance > 0) continue;
659 // $duncount was originally supposed to be the number of times that
660 // the patient was sent a statement for this invoice.
662 $duncount = substr_count(strtolower($row['intnotes']), "statement sent");
664 // But if we have not yet billed the patient, then compute $duncount as a
665 // negative count of the number of insurance plans for which we have not
666 // yet closed out insurance. Here we also compute $insname as the name of
667 // the insurance plan from which we are awaiting payment, and its sequence
668 // number $insposition (1-3).
672 $inseobs = strtolower($row['shipvia']);
673 $insgot = strtolower($row['notes']);
675 foreach (array('ins1', 'ins2', 'ins3') as $value) {
676 $i = strpos($insgot, $value);
677 if ($i !== false && strpos($inseobs, $value) === false) {
679 if (!$insname && $is_due_ins) {
680 $j = strpos($insgot, "\n", $i);
681 if (!$j) $j = strlen($insgot);
682 $insname = trim(substr($row['notes'], $i +
5, $j - $i - 5));
683 $insposition = substr($value, 3); // 1, 2 or 3
688 $row['insname'] = $insname;
690 // Also get the primary insurance company name whenever there is one.
692 $i = strpos($insgot, 'ins1');
694 $j = strpos($insgot, "\n", $i);
695 if (!$j) $j = strlen($insgot);
696 $row['ins1'] = trim(substr($row['notes'], $i +
5, $j - $i - 5));
699 // An invoice is now due from the patient if money is owed and we are
700 // not waiting for insurance to pay. We no longer look at the due date
703 $isduept = ($duncount >= 0) ?
" checked" : "";
705 // Skip invoices not in the desired "Due..." category.
707 if ($is_due_ins && $duncount >= 0) continue;
708 if ($is_due_pt && $duncount < 0) continue;
710 $row['duncount'] = $duncount;
712 // Determine the date of service. An 8-digit encounter number is
713 // presumed to be a date of service imported during conversion.
714 // Otherwise look it up in the form_encounter table.
717 list($pid, $encounter) = explode(".", $row['invnumber']);
718 if (strlen($encounter) == 8) {
719 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
720 "-" . substr($encounter, 6, 2);
722 else if ($encounter) {
723 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
724 "encounter = $encounter");
725 $svcdate = substr($tmp['date'], 0, 10);
728 $row['dos'] = $svcdate;
730 // This computes the invoice's total original charges and adjustments,
731 // date of last activity, and determines if insurance has responded to
732 // all billing items.
734 $invlines = get_invoice_summary($row['id'], true);
736 $row['adjustments'] = 0;
737 $ins_seems_done = true;
739 foreach ($invlines as $key => $value) {
740 $row['charges'] +
= $value['chg'] +
$value['adj'];
741 $row['adjustments'] +
= 0 - $value['adj'];
742 foreach ($value['dtl'] as $dkey => $dvalue) {
743 $dtldate = trim(substr($dkey, 0, 10));
744 if ($dtldate && $dtldate > $ladate) $ladate = $dtldate;
746 $lckey = strtolower($key);
747 if ($lckey == 'co-pay' ||
$lckey == 'claim') continue;
748 if (count($value['dtl']) <= 1) $ins_seems_done = false;
750 $row['billing_errmsg'] = '';
751 if ($is_due_ins && strpos($inseobs, 'ins1') === false && $ins_seems_done)
752 $row['billing_errmsg'] = 'Ins1 seems done';
753 else if (strpos($inseobs, 'ins1') !== false && !$ins_seems_done)
754 $row['billing_errmsg'] = 'Ins1 seems not done';
756 $row['ladate'] = $ladate;
758 // Compute number of days since last activity.
759 $latime = mktime(0, 0, 0, substr($ladate, 5, 2),
760 substr($ladate, 8, 2), substr($ladate, 0, 4));
761 $row['inactive_days'] = floor((time() - $latime) / (60 * 60 * 24));
763 $pdrow = sqlQuery("SELECT pd.fname, pd.lname, pd.mname, pd.ss, " .
764 "pd.genericname2, pd.genericval2, pd.pid, pd.pubpid, pd.DOB, " .
765 "CONCAT(u.lname, ', ', u.fname) AS referrer FROM " .
766 "integration_mapping AS im, patient_data AS pd " .
767 "LEFT OUTER JOIN users AS u ON u.id = pd.providerID " .
768 "WHERE im.foreign_id = " . $row['custid'] . " AND " .
769 "im.foreign_table = 'customer' AND " .
770 "pd.id = im.local_id");
772 $row['ss'] = $pdrow['ss'];
773 $row['DOB'] = $pdrow['DOB'];
774 $row['pubpid'] = $pdrow['pubpid'];
775 $row['billnote'] = ($pdrow['genericname2'] == 'Billing') ?
$pdrow['genericval2'] : '';
776 $row['referrer'] = $pdrow['referrer'];
778 $ptname = $pdrow['lname'] . ", " . $pdrow['fname'];
779 if ($pdrow['mname']) $ptname .= " " . substr($pdrow['mname'], 0, 1);
781 // Look up insurance policy number if we need it.
782 if ($form_cb_policy) {
783 $patient_id = $pdrow['pid'];
784 $instype = ($insposition == 2) ?
'secondary' : (($insposition == 3) ?
'tertiary' : 'primary');
785 $insrow = sqlQuery("SELECT policy_number FROM insurance_data WHERE " .
786 "pid = '$patient_id' AND type = '$instype' AND date <= '$svcdate' " .
787 "ORDER BY date DESC LIMIT 1");
788 $row['policy'] = $insrow['policy_number'];
791 $rows[$insname . '|' . $ptname . '|' . $encounter] = $row;
793 } // end not $INTEGRATED_AR
797 if ($_POST['form_export']) {
798 echo "<textarea rows='35' cols='100' readonly>";
800 else if ($_POST['form_csvexport']) {
813 echo '"LADate"' . "\n";
819 <table border
='0' cellpadding
='1' cellspacing
='2' width
='98%'>
821 <tr bgcolor
="#dddddd">
822 <?php
if ($is_due_ins) { ?
>
823 <td
class="dehead"> 
;<?php
xl('Insurance','e')?
></td
>
825 <?php
if (!$is_ins_summary) { ?
>
826 <td
class="dehead"> 
;<?php
xl('Name','e')?
></td
>
828 <?php
if ($form_cb_ssn) { ?
>
829 <td
class="dehead"> 
;<?php
xl('SSN','e')?
></td
>
831 <?php
if ($form_cb_dob) { ?
>
832 <td
class="dehead"> 
;<?php
xl('DOB','e')?
></td
>
834 <?php
if ($form_cb_pubpid) { ?
>
835 <td
class="dehead"> 
;<?php
xl('ID','e')?
></td
>
837 <?php
if ($form_cb_policy) { ?
>
838 <td
class="dehead"> 
;<?php
xl('Policy','e')?
></td
>
840 <?php
if ($form_cb_phone) { ?
>
841 <td
class="dehead"> 
;<?php
xl('Phone','e')?
></td
>
843 <?php
if ($form_cb_city) { ?
>
844 <td
class="dehead"> 
;<?php
xl('City','e')?
></td
>
846 <?php
if ($form_cb_ins1) { ?
>
847 <td
class="dehead"> 
;<?php
xl('Primary Ins','e')?
></td
>
849 <?php
if ($form_cb_referrer) { ?
>
850 <td
class="dehead"> 
;<?php
xl('Referrer','e')?
></td
>
852 <?php
if (!$is_ins_summary) { ?
>
853 <td
class="dehead"> 
;<?php
xl('Invoice','e') ?
></td
>
854 <td
class="dehead"> 
;<?php
xl('Svc Date','e') ?
></td
>
855 <?php
if ($form_cb_adate) { ?
>
856 <td
class="dehead"> 
;<?php
xl('Act Date','e')?
></td
>
859 <td
class="dehead" align
="right"><?php
xl('Charge','e') ?
> 
;</td
>
860 <td
class="dehead" align
="right"><?php
xl('Adjust','e') ?
> 
;</td
>
861 <td
class="dehead" align
="right"><?php
xl('Paid','e') ?
> 
;</td
>
863 // Generate aging headers if appropriate, else balance header.
864 if ($form_age_cols) {
865 for ($c = 0; $c < $form_age_cols;) {
866 echo " <td class='dehead' align='right'>";
867 echo $form_age_inc * $c;
868 if (++
$c < $form_age_cols) {
869 echo "-" . ($form_age_inc * $c - 1);
878 <td
class="dehead" align
="right"><?php
xl('Balance','e') ?
> 
;</td
>
882 <?php
if ($form_cb_idays) { ?
>
883 <td
class="dehead" align
="right"><?php
xl('IDays','e')?
> 
;</td
>
885 <?php
if (!$is_ins_summary) { ?
>
886 <td
class="dehead" align
="center"><?php
xl('Prv','e') ?
></td
>
887 <td
class="dehead" align
="center"><?php
xl('Sel','e') ?
></td
>
889 <?php
if ($form_cb_err) { ?
>
890 <td
class="dehead"> 
;<?php
xl('Error','e')?
></td
>
897 $ptrow = array('insname' => '', 'pid' => 0);
900 foreach ($rows as $key => $row) {
901 list($insname, $ptname, $trash) = explode('|', $key);
902 list($pid, $encounter) = explode(".", $row['invnumber']);
904 if ($is_ins_summary && $insname != $ptrow['insname']) {
905 endInsurance($ptrow);
906 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
907 $ptrow = array('insname' => $insname, 'ptname' => $ptname, 'pid' => $pid, 'count' => 1);
908 foreach ($row as $key => $value) $ptrow[$key] = $value;
909 $ptrow['agedbal'] = array();
911 else if (!$is_ins_summary && ($insname != $ptrow['insname'] ||
$pid != $ptrow['pid'])) {
912 // For the report, this will write the patient totals. For the
913 // collections export this writes everything for the patient:
915 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
916 $ptrow = array('insname' => $insname, 'ptname' => $ptname, 'pid' => $pid, 'count' => 1);
917 foreach ($row as $key => $value) $ptrow[$key] = $value;
918 $ptrow['agedbal'] = array();
920 $ptrow['amount'] +
= $row['amount'];
921 $ptrow['paid'] +
= $row['paid'];
922 $ptrow['charges'] +
= $row['charges'];
923 $ptrow['adjustments'] +
= $row['adjustments'];
927 // Compute invoice balance and aging column number, and accumulate aging.
928 $balance = $row['charges'] +
$row['adjustments'] - $row['paid'];
929 if ($form_age_cols) {
930 $agedate = $is_ageby_lad ?
$row['ladate'] : $row['dos'];
931 $agetime = mktime(0, 0, 0, substr($agedate, 5, 2),
932 substr($agedate, 8, 2), substr($agedate, 0, 4));
933 $days = floor((time() - $agetime) / (60 * 60 * 24));
934 $agecolno = min($form_age_cols - 1, max(0, floor($days / $form_age_inc)));
935 $ptrow['agedbal'][$agecolno] +
= $balance;
938 if (!$is_ins_summary && !$_POST['form_export'] && !$_POST['form_csvexport']) {
939 $in_collections = stristr($row['billnote'], 'IN COLLECTIONS') !== false;
941 <tr bgcolor
='<?php echo $bgcolor ?>'>
943 if ($ptrow['count'] == 1) {
945 echo " <td class='detail'> $insname</td>\n";
947 echo " <td class='detail'> $ptname</td>\n";
949 echo " <td class='detail'> " . $row['ss'] . "</td>\n";
952 echo " <td class='detail'> " . $row['DOB'] . "</td>\n";
954 if ($form_cb_pubpid) {
955 echo " <td class='detail'> " . $row['pubpid'] . "</td>\n";
957 if ($form_cb_policy) {
958 echo " <td class='detail'> " . $row['policy'] . "</td>\n";
960 if ($form_cb_phone) {
961 echo " <td class='detail'> " . $row['phone'] . "</td>\n";
964 echo " <td class='detail'> " . $row['city'] . "</td>\n";
967 echo " <td class='detail'> " . $row['ins1'] . "</td>\n";
969 if ($form_cb_referrer) {
970 echo " <td class='detail'> " . $row['referrer'] . "</td>\n";
973 echo " <td class='detail' colspan='$initial_colspan'>";
974 echo " </td>\n";
978  
;<a href
="../billing/sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
979 target
="_blank"><?php
echo $row['invnumber'] ?
></a
>
982  
;<?php
echo $row['dos']; ?
>
984 <?php
if ($form_cb_adate) { ?
>
986  
;<?php
echo $row['ladate']; ?
>
989 <td
class="detail" align
="right">
990 <?php
bucks($row['charges']) ?
> 
;
992 <td
class="detail" align
="right">
993 <?php
bucks($row['adjustments']) ?
> 
;
995 <td
class="detail" align
="right">
996 <?php
bucks($row['paid']) ?
> 
;
999 if ($form_age_cols) {
1000 for ($c = 0; $c < $form_age_cols; ++
$c) {
1001 echo " <td class='detail' align='right'>";
1002 if ($c == $agecolno) {
1005 echo " </td>\n";
1010 <td
class="detail" align
="right"><?php
bucks($balance) ?
> 
;</td
>
1015 if ($form_cb_idays) {
1016 echo " <td class='detail' align='right'>";
1017 echo $row['inactive_days'] . " </td>\n";
1020 <td
class="detail" align
="center">
1021 <?php
echo $row['duncount'] ?
$row['duncount'] : " " ?
>
1023 <td
class="detail" align
="center">
1025 if ($ptrow['count'] == 1) {
1026 if ($in_collections) {
1027 echo " <b><font color='red'>IC</font></b>\n";
1029 echo " <input type='checkbox' name='form_cb[" . $row['custid'] . "]' />\n";
1038 echo " <td class='detail'> ";
1039 echo $row['billing_errmsg'] . "</td>\n";
1044 } // end not export and not insurance summary
1046 else if ($_POST['form_csvexport']) {
1047 // The CSV detail line is written here.
1048 $balance = $row['charges'] +
$row['adjustments'] - $row['paid'];
1049 // echo '"' . $insname . '",';
1050 echo '"' . $row['ins1'] . '",';
1051 echo '"' . $ptname . '",';
1052 echo '"' . $row['invnumber'] . '",';
1053 echo '"' . $row['dos'] . '",';
1054 echo '"' . $row['referrer'] . '",';
1055 echo '"' . sprintf('%.2f', $row['charges']) . '",';
1056 echo '"' . sprintf('%.2f', $row['adjustments']) . '",';
1057 echo '"' . sprintf('%.2f', $row['paid']) . '",';
1058 echo '"' . sprintf('%.2f', $balance) . '",';
1059 echo '"' . $row['inactive_days'] . '",';
1060 echo '"' . $row['ladate'] . '"' . "\n";
1061 } // end $form_csvexport
1065 if ($is_ins_summary)
1066 endInsurance($ptrow);
1070 if ($_POST['form_export']) {
1071 echo "</textarea>\n";
1072 $alertmsg .= "$export_patient_count patients representing $" .
1073 sprintf("%.2f", $export_dollars) . " have been exported ";
1074 if ($_POST['form_without']) {
1075 $alertmsg .= "but NOT flagged as in collections.";
1077 $alertmsg .= "AND flagged as in collections.";
1080 else if ($_POST['form_csvexport']) {
1081 // echo "</textarea>\n";
1082 // $alertmsg .= "$export_patient_count patients representing $" .
1083 // sprintf("%.2f", $export_dollars) . " have been exported.";
1086 echo " <tr bgcolor='#ffffff'>\n";
1087 if ($is_ins_summary) {
1088 echo " <td class='dehead'> " . xl('Report Totals') . ":</td>\n";
1090 echo " <td class='detail' colspan='$initial_colspan'>\n";
1091 echo " </td>\n";
1092 echo " <td class='dehead' colspan='" . ($final_colspan - 3) .
1093 "'> " . xl('Report Totals') . ":</td>\n";
1095 echo " <td class='dehead' align='right'> " .
1096 sprintf("%.2f", $grand_total_charges) . " </td>\n";
1097 echo " <td class='dehead' align='right'> " .
1098 sprintf("%.2f", $grand_total_adjustments) . " </td>\n";
1099 echo " <td class='dehead' align='right'> " .
1100 sprintf("%.2f", $grand_total_paid) . " </td>\n";
1101 if ($form_age_cols) {
1102 for ($c = 0; $c < $form_age_cols; ++
$c) {
1103 echo " <td class='dehead' align='right'>" .
1104 sprintf("%.2f", $grand_total_agedbal[$c]) . " </td>\n";
1108 echo " <td class='dehead' align='right'>" .
1109 sprintf("%.2f", $grand_total_charges +
1110 $grand_total_adjustments - $grand_total_paid) . " </td>\n";
1112 if ($form_cb_idays) echo " <td class='detail'> </td>\n";
1113 if (!$is_ins_summary) echo " <td class='detail' colspan='2'> </td>\n";
1114 if ($form_cb_err) echo " <td class='detail'> </td>\n";
1118 } // end if form_search
1120 if (!$INTEGRATED_AR) SLClose();
1122 if (!$_POST['form_csvexport']) {
1123 if (!$_POST['form_export']) {
1126 <input type
='button' value
=<?php
xl('Select All','e','\'','\''); ?
> onclick
='checkAll(true)' />  
;
1127 <input type
='button' value
=<?php
xl('Clear All','e','\'','\''); ?
> onclick
='checkAll(false)' />  
;
1128 <input type
='submit' name
='form_csvexport' value
=<?php
xl('Export Selected as CSV','e','\'','\''); ?
> />  
;  
;
1129 <input type
='submit' name
='form_export' value
=<?php
xl('Export Selected to Collections','e','\'','\''); ?
> />  
;
1130 <input type
='checkbox' name
='form_without' value
='1' /> <?php
xl('Without Update','e') ?
>
1137 <script language
="JavaScript">
1140 echo "alert('" . htmlentities($alertmsg) . "');\n";
1145 <!-- stuff
for the popup calendar
-->
1146 <style type
="text/css">@import
url(../../library
/dynarch_calendar
.css
);</style
>
1147 <script type
="text/javascript" src
="../../library/dynarch_calendar.js"></script
>
1148 <script type
="text/javascript" src
="../../library/dynarch_calendar_en.js"></script
>
1149 <script type
="text/javascript" src
="../../library/dynarch_calendar_setup.js"></script
>
1150 <script language
="Javascript">
1151 Calendar
.setup({inputField
:"form_date", ifFormat
:"%m/%d/%Y", button
:"img_from_date"});
1152 Calendar
.setup({inputField
:"form_to_date", ifFormat
:"%m/%d/%Y", button
:"img_to_date"});
1156 } // end not form_csvexport