2 // Copyright (C) 2005-2008 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 // This is the first of two pages to support posting of EOBs.
10 // The second is sl_eob_invoice.php.
12 include_once("../globals.php");
13 include_once("../../library/patient.inc");
14 include_once("../../library/sql-ledger.inc");
15 include_once("../../library/invoice_summary.inc.php");
16 include_once("../../custom/statement.inc.php");
17 include_once("../../library/parse_era.inc.php");
18 include_once("../../library/sl_eob.inc.php");
20 $DEBUG = 0; // set to 0 for production, 1 to test
22 $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
29 // This is called back by parse_era() if we are processing X12 835's.
31 function era_callback(&$out) {
32 global $where, $eracount, $eraname, $INTEGRATED_AR;
33 // print_r($out); // debugging
35 // $eraname = $out['isa_control_number'];
36 $eraname = $out['gs_date'] . '_' . ltrim($out['isa_control_number'], '0') .
37 '_' . ltrim($out['payer_id'], '0');
38 list($pid, $encounter, $invnumber) = slInvoiceNumber($out);
40 if ($pid && $encounter) {
41 if ($where) $where .= ' OR ';
43 $where .= "( f.pid = '$pid' AND f.encounter = '$encounter' )";
45 $where .= "invnumber = '$invnumber'";
50 function bucks($amount) {
52 printf("%.2f", $amount);
55 $today = date("Y-m-d");
59 // Print statements if requested.
61 if ($_POST['form_print'] && $_POST['form_cb']) {
63 $fhprint = fopen($STMT_TEMP_FILE, 'w');
66 foreach ($_POST['form_cb'] as $key => $value) $where .= " OR f.id = $key";
67 $where = substr($where, 4);
69 $res = sqlStatement("SELECT " .
70 "f.id, f.date, f.pid, f.encounter, f.stmt_count, f.last_stmt_date, " .
71 "p.fname, p.mname, p.lname, p.street, p.city, p.state, p.postal_code " .
72 "FROM form_encounter AS f, patient_data AS p " .
73 "WHERE ( $where ) AND " .
75 "ORDER BY p.lname, p.fname, f.pid, f.date, f.encounter");
80 // This loops once for each invoice/encounter.
82 while ($row = sqlFetchArray($res)) {
83 $svcdate = substr($row['date'], 0, 10);
84 $duedate = $svcdate; // TBD?
85 $duncount = $row['stmt_count'];
87 // If this is a new patient then print the pending statement
88 // and start a new one. This is an associative array:
91 // pid = OpenEMR patient ID
92 // patient = patient name
93 // amount = total amount due
94 // adjust = adjustments (already applied to amount)
95 // duedate = due date of the oldest included invoice
96 // age = number of days from duedate to today
97 // to = array of addressee name/address lines
99 // dos = date of service "yyyy-mm-dd"
100 // desc = description
101 // amount = charge less adjustments
102 // paid = amount paid
103 // notice = 1 for first notice, 2 for second, etc.
104 // detail = array of details, see invoice_summary.inc.php
106 if ($stmt['cid'] != $row['pid']) {
107 if (!empty($stmt)) ++
$stmt_count;
108 fwrite($fhprint, create_statement($stmt));
109 $stmt['cid'] = $row['pid'];
110 $stmt['pid'] = $row['pid'];
111 $stmt['patient'] = $row['fname'] . ' ' . $row['lname'];
112 $stmt['to'] = array($row['fname'] . ' ' . $row['lname']);
113 if ($row['street']) $stmt['to'][] = $row['street'];
114 $stmt['to'][] = $row['city'] . ", " . $row['state'] . " " . $row['postal_code'];
115 $stmt['lines'] = array();
116 $stmt['amount'] = '0.00';
117 $stmt['today'] = $today;
118 $stmt['duedate'] = $duedate;
120 // Report the oldest due date.
121 if ($duedate < $stmt['duedate']) {
122 $stmt['duedate'] = $duedate;
126 // Recompute age at each invoice.
127 $stmt['age'] = round((strtotime($today) - strtotime($stmt['duedate'])) /
130 $invlines = ar_get_invoice_summary($row['pid'], $row['encounter'], true);
131 foreach ($invlines as $key => $value) {
133 $line['dos'] = $svcdate;
134 $line['desc'] = ($key == 'CO-PAY') ?
"Patient Payment" : "Procedure $key";
135 $line['amount'] = sprintf("%.2f", $value['chg']);
136 $line['adjust'] = sprintf("%.2f", $value['adj']);
137 $line['paid'] = sprintf("%.2f", $value['chg'] - $value['bal']);
138 $line['notice'] = $duncount +
1;
139 $line['detail'] = $value['dtl'];
140 $stmt['lines'][] = $line;
141 $stmt['amount'] = sprintf("%.2f", $stmt['amount'] +
$value['bal']);
144 // Record that this statement was run.
145 if (! $DEBUG && ! $_POST['form_without']) {
146 sqlStatement("UPDATE form_encounter SET " .
147 "last_stmt_date = '$today', stmt_count = stmt_count + 1 " .
148 "WHERE id = " . $row['id']);
152 if (!empty($stmt)) ++
$stmt_count;
153 fwrite($fhprint, create_statement($stmt));
156 $alertmsg = xl("Printing skipped; see test output in ") . $STMT_TEMP_FILE;
158 exec("$STMT_PRINT_CMD $STMT_TEMP_FILE");
159 if ($_POST['form_without']) {
160 $alertmsg = xl("Now printing $stmt_count statements; encounters will not be updated.");
162 $alertmsg = xl("Now printing $stmt_count statements and updating encounters.");
165 } // end statements requested
166 } // end $INTEGRATED_AR
170 // This will be true starting with SQL-Ledger 2.8.x:
171 $got_address_table = SLQueryValue("SELECT count(*) FROM pg_tables WHERE " .
172 "schemaname = 'public' AND tablename = 'address'");
174 // Print statements if requested.
176 if ($_POST['form_print'] && $_POST['form_cb']) {
178 $fhprint = fopen($STMT_TEMP_FILE, 'w');
181 foreach ($_POST['form_cb'] as $key => $value) $where .= " OR ar.id = $key";
182 $where = substr($where, 4);
184 // Sort by patient so that multiple invoices can be
185 // represented on a single statement.
186 if ($got_address_table) {
187 $res = SLQuery("SELECT ar.*, customer.name, " .
188 "address.address1, address.address2, " .
189 "address.city, address.state, address.zipcode, " .
190 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS fname, " .
191 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS lname " .
192 "FROM ar, customer, address WHERE ( $where ) AND " .
193 "customer.id = ar.customer_id AND " .
194 "address.trans_id = ar.customer_id " .
195 "ORDER BY lname, fname, ar.customer_id, ar.transdate");
198 $res = SLQuery("SELECT ar.*, customer.name, " .
199 "customer.address1, customer.address2, " .
200 "customer.city, customer.state, customer.zipcode, " .
201 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS lname, " .
202 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS fname " .
203 "FROM ar, customer WHERE ( $where ) AND " .
204 "customer.id = ar.customer_id " .
205 "ORDER BY lname, fname, ar.customer_id, ar.transdate");
207 if ($sl_err) die($sl_err);
212 for ($irow = 0; $irow < SLRowCount($res); ++
$irow) {
213 $row = SLGetRow($res, $irow);
215 // Determine the date of service. An 8-digit encounter number is
216 // presumed to be a date of service imported during conversion.
217 // Otherwise look it up in the form_encounter table.
220 list($pid, $encounter) = explode(".", $row['invnumber']);
221 if (strlen($encounter) == 8) {
222 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
223 "-" . substr($encounter, 6, 2);
224 } else if ($encounter) {
225 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
226 "encounter = $encounter");
227 $svcdate = substr($tmp['date'], 0, 10);
230 // How many times have we dunned them for this invoice?
231 $intnotes = trim($row['intnotes']);
232 $duncount = substr_count(strtolower($intnotes), "statement sent");
234 // If this is a new patient then print the pending statement
235 // and start a new one. This is an associative array:
237 // cid = SQL-Ledger customer ID
238 // pid = OpenEMR patient ID
239 // patient = patient name
240 // amount = total amount due
241 // adjust = adjustments (already applied to amount)
242 // duedate = due date of the oldest included invoice
243 // age = number of days from duedate to today
244 // to = array of addressee name/address lines
246 // dos = date of service "yyyy-mm-dd"
247 // desc = description
248 // amount = charge less adjustments
249 // paid = amount paid
250 // notice = 1 for first notice, 2 for second, etc.
251 // detail = array of details, see invoice_summary.inc.php
253 if ($stmt['cid'] != $row['customer_id']) {
254 if (!empty($stmt)) ++
$stmt_count;
255 fwrite($fhprint, create_statement($stmt));
256 $stmt['cid'] = $row['customer_id'];
259 if ($got_address_table) {
260 $stmt['patient'] = $row['fname'] . ' ' . $row['lname'];
261 $stmt['to'] = array($row['fname'] . ' ' . $row['lname']);
263 $stmt['patient'] = $row['name'];
264 $stmt['to'] = array($row['name']);
267 if ($row['address1']) $stmt['to'][] = $row['address1'];
268 if ($row['address2']) $stmt['to'][] = $row['address2'];
269 $stmt['to'][] = $row['city'] . ", " . $row['state'] . " " . $row['zipcode'];
270 $stmt['lines'] = array();
271 $stmt['amount'] = '0.00';
272 $stmt['today'] = $today;
273 $stmt['duedate'] = $row['duedate'];
275 // Report the oldest due date.
276 if ($row['duedate'] < $stmt['duedate']) {
277 $stmt['duedate'] = $row['duedate'];
281 $stmt['age'] = round((strtotime($today) - strtotime($stmt['duedate'])) /
284 $invlines = get_invoice_summary($row['id'], true); // true added by Rod 2006-06-09
285 foreach ($invlines as $key => $value) {
287 $line['dos'] = $svcdate;
288 $line['desc'] = ($key == 'CO-PAY') ?
"Patient Payment" : "Procedure $key";
289 $line['amount'] = sprintf("%.2f", $value['chg']);
290 $line['adjust'] = sprintf("%.2f", $value['adj']);
291 $line['paid'] = sprintf("%.2f", $value['chg'] - $value['bal']);
292 $line['notice'] = $duncount +
1;
293 $line['detail'] = $value['dtl']; // Added by Rod 2006-06-09
294 $stmt['lines'][] = $line;
295 $stmt['amount'] = sprintf("%.2f", $stmt['amount'] +
$value['bal']);
298 // Record something in ar.intnotes about this statement run.
299 if ($intnotes) $intnotes .= "\n";
300 $intnotes = addslashes($intnotes . "Statement sent $today");
301 if (! $DEBUG && ! $_POST['form_without']) {
302 SLQuery("UPDATE ar SET intnotes = '$intnotes' WHERE id = " . $row['id']);
303 if ($sl_err) die($sl_err);
307 if (!empty($stmt)) ++
$stmt_count;
308 fwrite($fhprint, create_statement($stmt));
311 $alertmsg = xl("Printing skipped; see test output in ").$STMT_TEMP_FILE;
313 exec("$STMT_PRINT_CMD $STMT_TEMP_FILE");
314 if ($_POST['form_without']) {
315 $alertmsg = xl("Now printing $stmt_count statements; invoices will not be updated.");
317 $alertmsg = xl("Now printing $stmt_count statements and updating invoices.");
320 } // end statements requested
321 } // end not $INTEGRATED_AR
325 <?php
html_header_show(); ?
>
326 <link rel
=stylesheet href
="<?echo $css_header;?>" type
="text/css">
327 <title
><?php
xl('EOB Posting - Search','e'); ?
></title
>
328 <script type
="text/javascript" src
="../../library/textformat.js"></script
>
330 <script language
="JavaScript">
334 function checkAll(checked
) {
335 var f
= document
.forms
[0];
336 for (var i
= 0; i
< f
.elements
.length
; ++i
) {
337 var ename
= f
.elements
[i
].name
;
338 if (ename
.indexOf('form_cb[') == 0)
339 f
.elements
[i
].checked
= checked
;
343 function npopup(pid
) {
344 window
.open('sl_eob_patient_note.php?patient_id=' + pid
, '_blank', 'width=500,height=250,resizable=1');
352 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0'>
355 <form method
='post' action
='sl_eob_search.php' enctype
='multipart/form-data'>
357 <table border
='0' cellpadding
='5' cellspacing
='0'>
361 if ($INTEGRATED_AR) {
362 // Identify the payer to support resumable posting sessions.
364 echo " " . xl('Payer') . ":\n";
367 $insurancei = getInsuranceProviders();
368 echo " <select name='form_payer_id'>\n";
369 echo " <option value='0'>-- " . xl('Patient') . " --</option>\n";
370 foreach ($insurancei as $iid => $iname) {
371 echo "<option value='$iid'";
372 if ($iid == $_POST['form_payer_id']) echo " selected";
373 echo ">" . $iname . "</option>\n";
381 <?php
xl('Source:','e'); ?
>
384 <input type
='text' name
='form_source' size
='10' value
='<?php echo $_POST['form_source
']; ?>'
385 title
='<?php xl("A check number or claim number to identify the payment","e"); ?>'>
388 <?php
xl('Pay Date:','e'); ?
>
391 <input type
='text' name
='form_paydate' size
='10' value
='<?php echo $_POST['form_paydate
']; ?>'
392 onkeyup
='datekeyup(this,mypcc)' onblur
='dateblur(this,mypcc)'
393 title
='<?php xl("Date of payment yyyy-mm-dd","e"); ?>'>
396 <?php
if ($INTEGRATED_AR) { // include deposit date ?>
398 <?php
xl('Deposit Date:','e'); ?
>
401 <input type
='text' name
='form_deposit_date' size
='10' value
='<?php echo $_POST['form_deposit_date
']; ?>'
402 onkeyup
='datekeyup(this,mypcc)' onblur
='dateblur(this,mypcc)'
403 title
='<?php xl("Date of bank deposit yyyy-mm-dd","e"); ?>'>
408 <?php
xl('Amount:','e'); ?
>
411 <input type
='text' name
='form_amount' size
='10' value
='<?php echo $_POST['form_amount
']; ?>'
412 title
='<?php xl("Paid amount that you will allocate","e"); ?>'>
415 <a href
='sl_eob_help.php' target
='_blank'><?php
xl('Help','e'); ?
></a
>
421 <table border
='0' cellpadding
='5' cellspacing
='0'>
423 <tr bgcolor
='#ddddff'>
425 <?php
xl('Name:','e'); ?
>
428 <input type
='text' name
='form_name' size
='10' value
='<?php echo $_POST['form_name
']; ?>'
429 title
='<?php xl("Any part of the patient name, or \"last,first\", or \"X-Y\"","e"); ?>'>
432 <?php
xl('Chart ID:','e'); ?
>
435 <input type
='text' name
='form_pid' size
='10' value
='<?php echo $_POST['form_pid
']; ?>'
436 title
='<?php xl("Patient chart ID","e"); ?>'>
439 <?php
xl('Encounter:','e'); ?
>
442 <input type
='text' name
='form_encounter' size
='10' value
='<?php echo $_POST['form_encounter
']; ?>'
443 title
='<?php xl("Encounter number","e"); ?>'>
446 <?php
xl('Svc Date:','e'); ?
>
449 <input type
='text' name
='form_date' size
='10' value
='<?php echo $_POST['form_date
']; ?>'
450 title
='<?php xl("Date of service mm/dd/yyyy","e"); ?>'>
453 <?php
xl('To:','e'); ?
>
456 <input type
='text' name
='form_to_date' size
='10' value
='<?php echo $_POST['form_to_date
']; ?>'
457 title
='<?php xl("Ending DOS mm/dd/yyyy if you wish to enter a range","e"); ?>'>
460 <select name
='form_category'>
462 foreach (array(xl('Open'), xl('All'), xl('Due Pt'), xl('Due Ins')) as $value) {
463 echo " <option value='$value'";
464 if ($_POST['form_category'] == $value) echo " selected";
465 echo ">$value</option>\n";
471 <input type
='submit' name
='form_search' value
='<?php xl("Search","e"); ?>'>
475 <!-- Support
for X12
835 upload
-->
476 <tr bgcolor
='#ddddff'>
478 <?php
xl('Or upload ERA file:','e'); ?
>
479 <input type
="hidden" name
="MAX_FILE_SIZE" value
="5000000" />
480 <input name
="form_erafile" type
="file" />
485 <td height
="1" colspan
="10">
492 if ($_POST['form_search'] ||
$_POST['form_print']) {
493 $form_name = trim($_POST['form_name']);
494 $form_pid = trim($_POST['form_pid']);
495 $form_encounter = trim($_POST['form_encounter']);
496 $form_date = fixDate($_POST['form_date'], "");
497 $form_to_date = fixDate($_POST['form_to_date'], "");
501 // Handle X12 835 file upload.
503 if ($_FILES['form_erafile']['size']) {
504 $tmp_name = $_FILES['form_erafile']['tmp_name'];
506 // Handle .zip extension if present. Probably won't work on Windows.
507 if (strtolower(substr($_FILES['form_erafile']['name'], -4)) == '.zip') {
508 rename($tmp_name, "$tmp_name.zip");
509 exec("unzip -p $tmp_name.zip > $tmp_name");
510 unlink("$tmp_name.zip");
513 echo "<!-- Notes from ERA upload processing:\n";
514 $alertmsg .= parse_era($tmp_name, 'era_callback');
516 $erafullname = "$webserver_root/era/$eraname.edi";
518 if (is_file($erafullname)) {
519 $alertmsg .= "Warning: Set $eraname was already uploaded ";
520 if (is_file("$webserver_root/era/$eraname.html"))
521 $alertmsg .= "and processed. ";
523 $alertmsg .= "but not yet processed. ";
525 rename($tmp_name, $erafullname);
528 if ($INTEGRATED_AR) {
530 // Note that parse_era() modified $eracount and $where.
531 if (! $where) $where = '1 = 2';
535 if ($where) $where .= " AND ";
536 // Allow the last name to be followed by a comma and some part of a first name.
537 if (preg_match('/^(.*\S)\s*,\s*(.*)/', $form_name, $matches)) {
538 $where .= "p.lname LIKE '" . $matches[1] . "%' AND p.fname LIKE '" . $matches[1] . "%'";
539 // Allow a filter like "A-C" on the first character of the last name.
540 } else if (preg_match('/^(\S)\s*-\s*(\S)$/', $form_name, $matches)) {
542 while (ord($matches[1]) <= ord($matches[2])) {
543 $tmp .= " OR p.lname LIKE '" . $matches[1] . "%'";
544 $matches[1] = chr(ord($matches[1]) +
1);
546 $where .= "( $tmp ) ";
548 $where .= "p.lname LIKE '%$form_name%'";
552 if ($where) $where .= " AND ";
553 $where .= "f.pid = '$form_pid'";
555 if ($form_encounter) {
556 if ($where) $where .= " AND ";
557 $where .= "f.encounter = '$form_encounter'";
560 if ($where) $where .= " AND ";
562 $where .= "f.date >= '$form_date' AND f.date <= '$form_to_date'";
565 $where .= "f.date = '$form_date'";
569 if ($_POST['form_category'] == 'All') {
570 die("At least one search parameter is required if you select All.");
577 $query = "SELECT f.id, f.pid, f.encounter, f.date, " .
578 "f.last_level_billed, f.last_level_closed, f.last_stmt_date, f.stmt_count, " .
579 "p.fname, p.mname, p.lname, p.pubpid, p.genericname2, p.genericval2, " .
580 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
581 "b.pid = f.pid AND b.encounter = f.encounter AND " .
582 "b.activity = 1 AND b.code_type != 'COPAY' ) AS charges, " .
583 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
584 "b.pid = f.pid AND b.encounter = f.encounter AND " .
585 "b.activity = 1 AND b.code_type = 'COPAY' ) AS copays, " .
586 "( SELECT SUM(a.pay_amount) FROM ar_activity AS a WHERE " .
587 "a.pid = f.pid AND a.encounter = f.encounter ) AS payments, " .
588 "( SELECT SUM(a.adj_amount) FROM ar_activity AS a WHERE " .
589 "a.pid = f.pid AND a.encounter = f.encounter ) AS adjustments " .
590 "FROM form_encounter AS f " .
591 "JOIN patient_data AS p ON p.pid = f.pid " .
593 "ORDER BY p.lname, p.fname, p.mname, f.pid, f.encounter";
595 // Note that unlike the SQL-Ledger case, this query does not weed
596 // out encounters that are paid up. Also the use of sub-selects
597 // will require MySQL 4.1 or greater.
599 // echo "<!-- $query -->\n"; // debugging
601 $t_res = sqlStatement($query);
603 $num_invoices = mysql_num_rows($t_res);
604 if ($eracount && $num_invoices != $eracount) {
605 $alertmsg .= "Of $eracount remittances, there are $num_invoices " .
606 "matching encounters in OpenEMR. ";
608 } // end $INTEGRATED_AR
611 // Note that parse_era() modified $eracount and $where.
612 if (! $where) $where = '1 = 2';
616 if ($where) $where .= " AND ";
617 // Allow the last name to be followed by a comma and some part of a first name.
618 if (preg_match('/^(.*\S)\s*,\s*(.*)/', $form_name, $matches)) {
619 $where .= "customer.name ILIKE '" . $matches[2] . '% ' . $matches[1] . "%'";
620 // Allow a filter like "A-C" on the first character of the last name.
621 } else if (preg_match('/^(\S)\s*-\s*(\S)$/', $form_name, $matches)) {
623 while (ord($matches[1]) <= ord($matches[2])) {
624 // $tmp .= " OR customer.name ILIKE '% " . $matches[1] . "%'";
625 // Fixing the above which was also matching on middle names:
626 $tmp .= " OR customer.name ~* ' " . $matches[1] . "[A-Z]*$'";
627 $matches[1] = chr(ord($matches[1]) +
1);
629 $where .= "( $tmp ) ";
631 $where .= "customer.name ILIKE '%$form_name%'";
634 if ($form_pid && $form_encounter) {
635 if ($where) $where .= " AND ";
636 $where .= "ar.invnumber = '$form_pid.$form_encounter'";
638 else if ($form_pid) {
639 if ($where) $where .= " AND ";
640 $where .= "ar.invnumber LIKE '$form_pid.%'";
642 else if ($form_encounter) {
643 if ($where) $where .= " AND ";
644 $where .= "ar.invnumber like '%.$form_encounter'";
648 if ($where) $where .= " AND ";
649 $date1 = substr($form_date, 0, 4) . substr($form_date, 5, 2) .
650 substr($form_date, 8, 2);
652 $date2 = substr($form_to_date, 0, 4) . substr($form_to_date, 5, 2) .
653 substr($form_to_date, 8, 2);
654 $where .= "((CAST (substring(ar.invnumber from position('.' in ar.invnumber) + 1 for 8) AS integer) " .
655 "BETWEEN '$date1' AND '$date2')";
656 $tmp = "date >= '$form_date' AND date <= '$form_to_date'";
659 // This catches old converted invoices where we have no encounters:
660 $where .= "(ar.invnumber LIKE '%.$date1'";
661 $tmp = "date = '$form_date'";
663 // Pick out the encounters from MySQL with the desired DOS:
664 $rez = sqlStatement("SELECT pid, encounter FROM form_encounter WHERE $tmp");
665 while ($row = sqlFetchArray($rez)) {
666 $where .= " OR ar.invnumber = '" . $row['pid'] . "." . $row['encounter'] . "'";
672 if ($_POST['form_category'] == 'All') {
673 die("At least one search parameter is required if you select All.");
680 $query = "SELECT ar.id, ar.invnumber, ar.duedate, ar.amount, ar.paid, " .
681 "ar.intnotes, ar.notes, ar.shipvia, customer.name, customer.id AS custid, ";
682 if ($got_address_table) $query .=
683 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS lname, " .
684 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS fname, ";
686 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS lname, " .
687 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS fname, ";
689 "(SELECT SUM(invoice.sellprice * invoice.qty) FROM invoice WHERE " .
690 "invoice.trans_id = ar.id AND invoice.sellprice > 0) AS charges, " .
691 "(SELECT SUM(invoice.sellprice * invoice.qty) FROM invoice WHERE " .
692 "invoice.trans_id = ar.id AND invoice.sellprice < 0) AS adjustments " .
693 "FROM ar, customer WHERE ( $where ) AND customer.id = ar.customer_id ";
694 if ($_POST['form_category'] != 'All' && !$eracount) {
695 $query .= "AND ar.amount != ar.paid ";
696 // if ($_POST['form_category'] == 'Due') {
697 // $query .= "AND ar.duedate <= CURRENT_DATE ";
700 $query .= "ORDER BY lname, fname, ar.invnumber";
702 // echo "<!-- $query -->\n"; // debugging
704 $t_res = SLQuery($query);
705 if ($sl_err) die($sl_err);
707 $num_invoices = SLRowCount($t_res);
708 if ($eracount && $num_invoices != $eracount) {
709 $alertmsg .= "Of $eracount remittances, there are $num_invoices " .
710 "matching claims in OpenEMR. ";
713 } // end not $INTEGRATED_AR
716 <table border
='0' cellpadding
='1' cellspacing
='2' width
='98%'>
718 <tr bgcolor
="#dddddd">
720  
;<?php
xl('Patient','e'); ?
>
723  
;<?php
xl('Invoice','e'); ?
>
726  
;<?php
xl('Svc Date','e'); ?
>
729  
;<?php
xl($INTEGRATED_AR ?
'Last Stmt' : 'Due Date','e'); ?
>
731 <td
class="dehead" align
="right">
732 <?php
xl('Charge','e'); ?
> 
;
734 <td
class="dehead" align
="right">
735 <?php
xl('Adjust','e'); ?
> 
;
737 <td
class="dehead" align
="right">
738 <?php
xl('Paid','e'); ?
> 
;
740 <td
class="dehead" align
="right">
741 <?php
xl('Balance','e'); ?
> 
;
743 <td
class="dehead" align
="center">
744 <?php
xl('Prv','e'); ?
>
746 <?php
if (!$eracount) { ?
>
747 <td
class="dehead" align
="left">
748 <?php
xl('Sel','e'); ?
>
756 if ($INTEGRATED_AR) {
757 while ($row = sqlFetchArray($t_res)) {
758 $balance = sprintf("%.2f", $row['charges'] +
$row['copays'] - $row['payments'] - $row['adjustments']);
760 if ($_POST['form_category'] != 'All' && $eracount == 0 && $balance == 0) continue;
762 // $duncount was originally supposed to be the number of times that
763 // the patient was sent a statement for this invoice.
765 $duncount = $row['stmt_count'];
767 // But if we have not yet billed the patient, then compute $duncount as a
768 // negative count of the number of insurance plans for which we have not
769 // yet closed out insurance.
772 for ($i = 1; $i <= 3 && arGetPayerID($row['pid'], $row['date'], $i); ++
$i) ;
773 $duncount = $row['last_level_closed'] +
1 - $i;
776 $isdueany = ($balance > 0);
778 // An invoice is now due from the patient if money is owed and we are
779 // not waiting for insurance to pay.
781 $isduept = ($duncount >= 0 && $isdueany) ?
" checked" : "";
783 // Skip invoices not in the desired "Due..." category.
785 if (substr($_POST['form_category'], 0, 3) == 'Due' && !$isdueany) continue;
786 if ($_POST['form_category'] == 'Due Ins' && ($duncount >= 0 ||
!$isdueany)) continue;
787 if ($_POST['form_category'] == 'Due Pt' && ($duncount < 0 ||
!$isdueany)) continue;
789 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
791 $svcdate = substr($row['date'], 0, 10);
792 $last_stmt_date = empty($row['last_stmt_date']) ?
'' : $row['last_stmt_date'];
794 // Determine if customer is in collections.
796 $billnote = ($row['genericname2'] == 'Billing') ?
$row['genericval2'] : '';
797 $in_collections = stristr($billnote, 'IN COLLECTIONS') !== false;
799 <tr bgcolor
='<?php echo $bgcolor ?>'>
801  
;<a href
="" onclick
="return npopup(<?php echo $row['pid'] ?>)"
802 ><?php
echo $row['lname'] . ', ' . $row['fname']; ?
></a
>
805  
;<a href
="sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
806 target
="_blank"><?php
echo $row['pid'] . '.' . $row['encounter']; ?
></a
>
809  
;<?php
echo $svcdate ?
>
812  
;<?php
echo $last_stmt_date ?
>
814 <td
class="detail" align
="right">
815 <?php
bucks($row['charges']) ?
> 
;
817 <td
class="detail" align
="right">
818 <?php
bucks($row['adjustments']) ?
> 
;
820 <td
class="detail" align
="right">
821 <?php
bucks($row['payments'] - $row['copays']); ?
> 
;
823 <td
class="detail" align
="right">
824 <?php
bucks($balance); ?
> 
;
826 <td
class="detail" align
="center">
827 <?php
echo $duncount ?
$duncount : " " ?
>
829 <?php
if (!$eracount) { ?
>
830 <td
class="detail" align
="left">
831 <input type
='checkbox' name
='form_cb[<?php echo($row['id
']) ?>]'<?php
echo $isduept ?
> />
832 <?php
if ($in_collections) echo "<b><font color='red'>IC</font></b>"; ?
>
838 } // end $INTEGRATED_AR
840 else { // not $INTEGRATED_AR
841 for ($irow = 0; $irow < $num_invoices; ++
$irow) {
842 $row = SLGetRow($t_res, $irow);
844 // $duncount was originally supposed to be the number of times that
845 // the patient was sent a statement for this invoice.
847 $duncount = substr_count(strtolower($row['intnotes']), "statement sent");
849 // But if we have not yet billed the patient, then compute $duncount as a
850 // negative count of the number of insurance plans for which we have not
851 // yet closed out insurance.
854 $insgot = strtolower($row['notes']);
855 $inseobs = strtolower($row['shipvia']);
856 foreach (array('ins1', 'ins2', 'ins3') as $value) {
857 if (strpos($insgot, $value) !== false &&
858 strpos($inseobs, $value) === false)
863 // $isdue = ($row['duedate'] <= $today && $row['amount'] > $row['paid']) ? " checked" : "";
865 $isdueany = sprintf("%.2f",$row['amount']) > sprintf("%.2f",$row['paid']);
867 // An invoice is now due from the patient if money is owed and we are
868 // not waiting for insurance to pay. We no longer look at the due date
871 $isduept = ($duncount >= 0 && $isdueany) ?
" checked" : "";
873 // Skip invoices not in the desired "Due..." category.
875 if (substr($_POST['form_category'], 0, 3) == 'Due' && !$isdueany) continue;
876 if ($_POST['form_category'] == 'Due Ins' && ($duncount >= 0 ||
!$isdueany)) continue;
877 if ($_POST['form_category'] == 'Due Pt' && ($duncount < 0 ||
!$isdueany)) continue;
879 $bgcolor = ((++
$orow & 1) ?
"#ffdddd" : "#ddddff");
881 // Determine the date of service. If this was a search parameter
882 // then we already know it. Or an 8-digit encounter number is
883 // presumed to be a date of service imported during conversion.
884 // Otherwise look it up in the form_encounter table.
887 list($pid, $encounter) = explode(".", $row['invnumber']);
889 // $svcdate = $form_date;
891 if (strlen($encounter) == 8) {
892 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
893 "-" . substr($encounter, 6, 2);
895 else if ($encounter) {
896 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
897 "encounter = $encounter");
898 $svcdate = substr($tmp['date'], 0, 10);
901 // Get billing note to determine if customer is in collections.
903 $pdrow = sqlQuery("SELECT pd.genericname2, pd.genericval2 FROM " .
904 "integration_mapping AS im, patient_data AS pd WHERE " .
905 "im.foreign_id = " . $row['custid'] . " AND " .
906 "im.foreign_table = 'customer' AND " .
907 "pd.id = im.local_id");
908 $row['billnote'] = ($pdrow['genericname2'] == 'Billing') ?
$pdrow['genericval2'] : '';
909 $in_collections = stristr($row['billnote'], 'IN COLLECTIONS') !== false;
911 <tr bgcolor
='<?php echo $bgcolor ?>'>
913  
;<a href
="" onclick
="return npopup(<?php echo $pid ?>)"
914 ><?php
echo $row['lname'] . ', ' . $row['fname']; ?
></a
>
917  
;<a href
="sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
918 target
="_blank"><?php
echo $row['invnumber'] ?
></a
>
921  
;<?php
echo $svcdate ?
>
924  
;<?php
echo $row['duedate'] ?
>
926 <td
class="detail" align
="right">
927 <?php
bucks($row['charges']) ?
> 
;
929 <td
class="detail" align
="right">
930 <?php
bucks($row['adjustments']) ?
> 
;
932 <td
class="detail" align
="right">
933 <?php
bucks($row['paid']) ?
> 
;
935 <td
class="detail" align
="right">
936 <?php
bucks($row['charges'] +
$row['adjustments'] - $row['paid']) ?
> 
;
938 <td
class="detail" align
="center">
939 <?php
echo $duncount ?
$duncount : " " ?
>
941 <?php
if (!$eracount) { ?
>
942 <td
class="detail" align
="left">
943 <input type
='checkbox' name
='form_cb[<?php echo($row['id
']) ?>]'<?php
echo $isduept ?
> />
944 <?php
if ($in_collections) echo "<b><font color='red'>IC</font></b>"; ?
>
950 } // end not $INTEGRATED_AR
951 } // end search/print logic
953 if (!$INTEGRATED_AR) SLClose();
959 <?php
if ($eracount) { ?
>
960 <input type
='button' value
='Process ERA File' onclick
='processERA()' />  
;
962 <input type
='button' value
='Select All' onclick
='checkAll(true)' />  
;
963 <input type
='button' value
='Clear All' onclick
='checkAll(false)' />  
;
964 <input type
='submit' name
='form_print' value
='Print Selected Statements' />  
;
966 <input type
='checkbox' name
='form_without' value
='1' /> <?php
xl('Without Update','e'); ?
>
971 <script language
="JavaScript">
972 function processERA() {
973 var f
= document
.forms
[0];
974 var debug
= f
.form_without
.checked ?
'1' : '0';
975 var paydate
= f
.form_paydate
.value
;
976 window
.open('sl_eob_process.php?eraname=<?php echo $eraname ?>&debug=' + debug +
'&paydate=' + paydate
, '_blank');
981 echo "alert('" . htmlentities($alertmsg) . "');\n";