fix possible listing of invoices not due
[openemr.git] / interface / billing / sl_eob_search.php
blob2dc873f36ad5825999b9dd710b06b967b90a0775
1 <?php
2 // Copyright (C) 2005-2006 Rod Roark <rod@sunsetsystems.com>
3 //
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 $alertmsg = '';
23 $where = '';
24 $eraname = '';
25 $eracount = 0;
27 // This is called back by parse_era() if we are processing X12 835's.
29 function era_callback(&$out) {
30 global $where, $eracount, $eraname;
31 // print_r($out); // debugging
32 ++$eracount;
33 // $eraname = $out['isa_control_number'];
34 $eraname = $out['gs_date'] . '_' . ltrim($out['isa_control_number'], '0') .
35 '_' . ltrim($out['payer_id'], '0');
36 list($pid, $encounter, $invnumber) = slInvoiceNumber($out);
38 if ($pid && $encounter) {
39 if ($where) $where .= ' OR ';
40 $where .= "invnumber = '$invnumber'";
44 function bucks($amount) {
45 if ($amount)
46 printf("%.2f", $amount);
49 $today = date("Y-m-d");
51 SLConnect();
53 // Print statements if requested.
55 if ($_POST['form_print'] && $_POST['form_cb']) {
57 $fhprint = fopen($STMT_TEMP_FILE, 'w');
59 $where = "";
60 foreach ($_POST['form_cb'] as $key => $value) $where .= " OR ar.id = $key";
61 $where = substr($where, 4);
63 // Sort by patient so that multiple invoices can be
64 // represented on a single statement.
65 $res = SLQuery("SELECT ar.*, customer.name, " .
66 "customer.address1, customer.address2, " .
67 "customer.city, customer.state, customer.zipcode, " .
68 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS lname, " .
69 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS fname " .
70 "FROM ar, customer WHERE ( $where ) AND " .
71 "customer.id = ar.customer_id " .
72 "ORDER BY lname, fname, ar.customer_id, ar.transdate");
73 if ($sl_err) die($sl_err);
75 $stmt = array();
77 for ($irow = 0; $irow < SLRowCount($res); ++$irow) {
78 $row = SLGetRow($res, $irow);
80 // Determine the date of service. An 8-digit encounter number is
81 // presumed to be a date of service imported during conversion.
82 // Otherwise look it up in the form_encounter table.
84 $svcdate = "";
85 list($pid, $encounter) = explode(".", $row['invnumber']);
86 if (strlen($encounter) == 8) {
87 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
88 "-" . substr($encounter, 6, 2);
89 } else if ($encounter) {
90 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
91 "encounter = $encounter");
92 $svcdate = substr($tmp['date'], 0, 10);
95 // How many times have we dunned them for this invoice?
96 $intnotes = trim($row['intnotes']);
97 $duncount = substr_count(strtolower($intnotes), "statement sent");
99 // If this is a new patient then print the pending statement
100 // and start a new one. This is an associative array:
102 // cid = SQL-Ledger customer ID
103 // pid = OpenEMR patient ID
104 // patient = patient name
105 // amount = total amount due
106 // adjust = adjustments (already applied to amount)
107 // duedate = due date of the oldest included invoice
108 // age = number of days from duedate to today
109 // to = array of addressee name/address lines
110 // lines = array of:
111 // dos = date of service "yyyy-mm-dd"
112 // desc = description
113 // amount = charge less adjustments
114 // paid = amount paid
115 // notice = 1 for first notice, 2 for second, etc.
116 // detail = array of details, see invoice_summary.inc.php
118 if ($stmt['cid'] != $row['customer_id']) {
119 fwrite($fhprint, create_statement($stmt));
120 $stmt['cid'] = $row['customer_id'];
121 $stmt['pid'] = $pid;
122 $stmt['patient'] = $row['name'];
123 $stmt['to'] = array($row['name']);
124 if ($row['address1']) $stmt['to'][] = $row['address1'];
125 if ($row['address2']) $stmt['to'][] = $row['address2'];
126 $stmt['to'][] = $row['city'] . ", " . $row['state'] . " " . $row['zipcode'];
127 $stmt['lines'] = array();
128 $stmt['amount'] = '0.00';
129 $stmt['today'] = $today;
130 $stmt['duedate'] = $row['duedate'];
131 } else {
132 // Report the oldest due date.
133 if ($row['duedate'] < $stmt['duedate']) {
134 $stmt['duedate'] = $row['duedate'];
138 $stmt['age'] = round((strtotime($today) - strtotime($stmt['duedate'])) /
139 (24 * 60 * 60));
141 $invlines = get_invoice_summary($row['id'], true); // true added by Rod 2006-06-09
142 foreach ($invlines as $key => $value) {
143 $line = array();
144 $line['dos'] = $svcdate;
145 $line['desc'] = ($key == 'CO-PAY') ? "Patient Payment" : "Procedure $key";
146 $line['amount'] = sprintf("%.2f", $value['chg']);
147 $line['adjust'] = sprintf("%.2f", $value['adj']);
148 $line['paid'] = sprintf("%.2f", $value['chg'] - $value['bal']);
149 $line['notice'] = $duncount + 1;
150 $line['detail'] = $value['dtl']; // Added by Rod 2006-06-09
151 $stmt['lines'][] = $line;
152 $stmt['amount'] = sprintf("%.2f", $stmt['amount'] + $value['bal']);
155 // Record something in ar.intnotes about this statement run.
156 if ($intnotes) $intnotes .= "\n";
157 $intnotes = addslashes($intnotes . "Statement sent $today");
158 if (! $DEBUG && ! $_POST['form_without']) {
159 SLQuery("UPDATE ar SET intnotes = '$intnotes' WHERE id = " . $row['id']);
160 if ($sl_err) die($sl_err);
164 fwrite($fhprint, create_statement($stmt));
166 if ($DEBUG) {
167 $alertmsg = xl("Printing skipped; see test output in ").$STMT_TEMP_FILE;
168 } else {
169 exec("$STMT_PRINT_CMD $STMT_TEMP_FILE");
170 if ($_POST['form_without']) {
171 $alertmsg = xl("Now printing statements; invoices will not be updated.");
172 } else {
173 $alertmsg = xl("Now printing statements and updating invoices.");
178 <html>
179 <head>
180 <link rel=stylesheet href="<?echo $css_header;?>" type="text/css">
181 <title><?xl('EOB Posting - Search','e')?></title>
183 <script language="JavaScript">
185 function checkAll(checked) {
186 var f = document.forms[0];
187 for (var i = 0; i < f.elements.length; ++i) {
188 var ename = f.elements[i].name;
189 if (ename.indexOf('form_cb[') == 0)
190 f.elements[i].checked = checked;
194 function npopup(pid) {
195 window.open('sl_eob_patient_note.php?patient_id=' + pid, '_blank', 'width=500,height=250,resizable=1');
196 return false;
199 </script>
201 </head>
203 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
204 <center>
206 <form method='post' action='sl_eob_search.php' enctype='multipart/form-data'>
208 <table border='0' cellpadding='5' cellspacing='0'>
210 <tr>
211 <td height="1" colspan="10">
212 </td>
213 </tr>
215 <tr>
216 <td colspan='2'>
217 &nbsp;
218 </td>
219 <td>
220 <?xl('Source:','e')?>
221 </td>
222 <td>
223 <input type='text' name='form_source' size='10' value='<?php echo $_POST['form_source']; ?>'
224 title='<?xl("A check number or claim number to identify the payment","e")?>'>
225 </td>
226 <td>
227 <?xl('Pay Date:','e')?>
228 </td>
229 <td>
230 <input type='text' name='form_paydate' size='10' value='<?php echo $_POST['form_paydate']; ?>'
231 title='<?xl("Date of payment mm/dd/yyyy","e")?>'>
232 </td>
233 <td>
234 <?xl('Amount:','e')?>
235 </td>
236 <td>
237 <input type='text' name='form_amount' size='10' value='<?php echo $_POST['form_amount']; ?>'
238 title='<?xl("Paid amount that you will allocate","e")?>'>
239 </td>
240 <td colspan='2' align='right'>
241 <a href='sl_eob_help.php' target='_blank'><?xl('Help','e')?></a>
242 </td>
243 </tr>
245 <tr>
246 <td height="1" colspan="10">
247 </td>
248 </tr>
250 <tr bgcolor='#ddddff'>
251 <td>
252 <?xl('Name:','e')?>
253 </td>
254 <td>
255 <input type='text' name='form_name' size='10' value='<?php echo $_POST['form_name']; ?>'
256 title='<?xl("Any part of the patient name, or 'last,first', or 'X-Y'","e")?>'>
257 </td>
258 <td>
259 <?xl('Chart ID:','e')?>
260 </td>
261 <td>
262 <input type='text' name='form_pid' size='10' value='<?php echo $_POST['form_pid']; ?>'
263 title='<?xl("Patient chart ID","e")?>'>
264 </td>
265 <td>
266 <?xl('Encounter:','e')?>
267 </td>
268 <td>
269 <input type='text' name='form_encounter' size='10' value='<?php echo $_POST['form_encounter']; ?>'
270 title='<?xl("Encounter number","e")?>'>
271 </td>
272 <td>
273 <?xl('Svc Date:','e')?>
274 </td>
275 <td>
276 <input type='text' name='form_date' size='10' value='<?php echo $_POST['form_date']; ?>'
277 title='<?xl("Date of service mm/dd/yyyy","e")?>'>
278 </td>
279 <td>
280 <?xl('To:','e')?>
281 </td>
282 <td>
283 <input type='text' name='form_to_date' size='10' value='<?php echo $_POST['form_to_date']; ?>'
284 title='<?xl("Ending DOS mm/dd/yyyy if you wish to enter a range","e")?>'>
285 </td>
286 <td>
287 <select name='form_category'>
288 <?php
289 foreach (array(xl('Open'), xl('All'), xl('Due Pt'), xl('Due Ins')) as $value) {
290 echo " <option value='$value'";
291 if ($_POST['form_category'] == $value) echo " selected";
292 echo ">$value</option>\n";
295 </select>
296 </td>
297 <td>
298 <input type='submit' name='form_search' value='<?xl("Search","e")?>'>
299 </td>
300 </tr>
302 <!-- Support for X12 835 upload -->
303 <tr bgcolor='#ddddff'>
304 <td colspan='12'>
305 <?xl('Or upload ERA file:','e')?>
306 <input type="hidden" name="MAX_FILE_SIZE" value="5000000" />
307 <input name="form_erafile" type="file" />
308 </td>
309 </tr>
311 <tr>
312 <td height="1" colspan="10">
313 </td>
314 </tr>
316 </table>
318 <?php
319 if ($_POST['form_search'] || $_POST['form_print']) {
320 $form_name = trim($_POST['form_name']);
321 $form_pid = trim($_POST['form_pid']);
322 $form_encounter = trim($_POST['form_encounter']);
323 $form_date = fixDate($_POST['form_date'], "");
324 $form_to_date = fixDate($_POST['form_to_date'], "");
326 $where = "";
328 // Handle X12 835 file upload.
330 if ($_FILES['form_erafile']['size']) {
331 $tmp_name = $_FILES['form_erafile']['tmp_name'];
333 // Handle .zip extension if present. Probably won't work on Windows.
334 if (strtolower(substr($_FILES['form_erafile']['name'], -4)) == '.zip') {
335 rename($tmp_name, "$tmp_name.zip");
336 exec("unzip -p $tmp_name.zip > $tmp_name");
337 unlink("$tmp_name.zip");
340 echo "<!-- Notes from ERA upload processing:\n";
341 $alertmsg .= parse_era($tmp_name, 'era_callback');
342 echo "-->\n";
343 $erafullname = "$webserver_root/era/$eraname.edi";
345 if (is_file($erafullname)) {
346 $alertmsg .= "Warning: Set $eraname was already uploaded ";
347 if (is_file("$webserver_root/era/$eraname.html"))
348 $alertmsg .= "and processed. ";
349 else
350 $alertmsg .= "but not yet processed. ";
352 // if (!move_uploaded_file($_FILES['form_erafile']['tmp_name'], $erafullname)) {
353 // die("Upload failed! $alertmsg");
354 // }
355 rename($tmp_name, $erafullname);
358 if ($eracount) {
359 if (! $where) $where = '1 = 2';
361 else {
362 if ($form_name) {
363 if ($where) $where .= " AND ";
364 // Allow the last name to be followed by a comma and some part of a first name.
365 if (preg_match('/^(.*\S)\s*,\s*(.*)/', $form_name, $matches)) {
366 $where .= "customer.name ILIKE '" . $matches[2] . '% ' . $matches[1] . "%'";
367 // Allow a filter like "A-C" on the first character of the last name.
368 } else if (preg_match('/^(\S)\s*-\s*(\S)$/', $form_name, $matches)) {
369 $tmp = '1 = 2';
370 while (ord($matches[1]) <= ord($matches[2])) {
371 $tmp .= " OR customer.name ILIKE '% " . $matches[1] . "%'";
372 $matches[1] = chr(ord($matches[1]) + 1);
374 $where .= "( $tmp ) ";
375 } else {
376 $where .= "customer.name ILIKE '%$form_name%'";
379 if ($form_pid && $form_encounter) {
380 if ($where) $where .= " AND ";
381 $where .= "ar.invnumber = '$form_pid.$form_encounter'";
383 else if ($form_pid) {
384 if ($where) $where .= " AND ";
385 $where .= "ar.invnumber LIKE '$form_pid.%'";
387 else if ($form_encounter) {
388 if ($where) $where .= " AND ";
389 $where .= "ar.invnumber like '%.$form_encounter'";
392 if ($form_date) {
393 if ($where) $where .= " AND ";
394 $date1 = substr($form_date, 0, 4) . substr($form_date, 5, 2) .
395 substr($form_date, 8, 2);
396 if ($form_to_date) {
397 $date2 = substr($form_to_date, 0, 4) . substr($form_to_date, 5, 2) .
398 substr($form_to_date, 8, 2);
399 $where .= "((CAST (substring(ar.invnumber from position('.' in ar.invnumber) + 1 for 8) AS integer) " .
400 "BETWEEN '$date1' AND '$date2')";
401 $tmp = "date >= '$form_date' AND date <= '$form_to_date'";
403 else {
404 // This catches old converted invoices where we have no encounters:
405 $where .= "(ar.invnumber LIKE '%.$date1'";
406 $tmp = "date = '$form_date'";
408 // Pick out the encounters from MySQL with the desired DOS:
409 $rez = sqlStatement("SELECT pid, encounter FROM form_encounter WHERE $tmp");
410 while ($row = sqlFetchArray($rez)) {
411 $where .= " OR ar.invnumber = '" . $row['pid'] . "." . $row['encounter'] . "'";
413 $where .= ")";
416 if (! $where) {
417 if ($_POST['form_category'] == 'All') {
418 die("At least one search parameter is required if you select All.");
419 } else {
420 $where = "1 = 1";
425 $query = "SELECT ar.id, ar.invnumber, ar.duedate, ar.amount, ar.paid, " .
426 "ar.intnotes, ar.notes, ar.shipvia, customer.name, " .
427 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS lname, " .
428 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS fname, " .
429 "(SELECT SUM(invoice.fxsellprice) FROM invoice WHERE " .
430 "invoice.trans_id = ar.id AND invoice.fxsellprice > 0) AS charges, " .
431 "(SELECT SUM(invoice.fxsellprice) FROM invoice WHERE " .
432 "invoice.trans_id = ar.id AND invoice.fxsellprice < 0) AS adjustments " .
433 "FROM ar, customer WHERE ( $where ) AND customer.id = ar.customer_id ";
434 if ($_POST['form_category'] != 'All' && !$eracount) {
435 $query .= "AND ar.amount != ar.paid ";
436 // if ($_POST['form_category'] == 'Due') {
437 // $query .= "AND ar.duedate <= CURRENT_DATE ";
438 // }
440 $query .= "ORDER BY lname, fname, ar.invnumber";
442 echo "<!-- $query -->\n"; // debugging
444 $t_res = SLQuery($query);
445 if ($sl_err) die($sl_err);
447 $num_invoices = SLRowCount($t_res);
448 if ($eracount && $num_invoices != $eracount) {
449 $alertmsg .= "Of $eracount remittances, there are $num_invoices " .
450 "matching claims in OpenEMR. ";
454 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
456 <tr bgcolor="#dddddd">
457 <td class="dehead">
458 &nbsp;<?xl('Patient','e')?>
459 </td>
460 <td class="dehead">
461 &nbsp;<?xl('Invoice','e')?>
462 </td>
463 <td class="dehead">
464 &nbsp;<?xl('Svc Date','e')?>
465 </td>
466 <td class="dehead">
467 &nbsp;<?xl('Due Date','e')?>
468 </td>
469 <td class="dehead" align="right">
470 <?xl('Charge','e')?>&nbsp;
471 </td>
472 <td class="dehead" align="right">
473 <?xl('Adjust','e')?>&nbsp;
474 </td>
475 <td class="dehead" align="right">
476 <?xl('Paid','e')?>&nbsp;
477 </td>
478 <td class="dehead" align="right">
479 <?xl('Balance','e')?>&nbsp;
480 </td>
481 <td class="dehead" align="center">
482 <?xl('Prv','e')?>
483 </td>
484 <?php if (!$eracount) { ?>
485 <td class="dehead" align="center">
486 <?xl('Sel','e')?>
487 </td>
488 <?php } ?>
489 </tr>
491 <?php
492 $orow = -1;
493 for ($irow = 0; $irow < $num_invoices; ++$irow) {
494 $row = SLGetRow($t_res, $irow);
496 // $duncount was originally supposed to be the number of times that
497 // the patient was sent a statement for this invoice.
499 $duncount = substr_count(strtolower($row['intnotes']), "statement sent");
501 // But if we have not yet billed the patient, then compute $duncount as a
502 // negative count of the number of insurance plans for which we have not
503 // yet closed out insurance.
505 if (! $duncount) {
506 $insgot = strtolower($row['notes']);
507 $inseobs = strtolower($row['shipvia']);
508 foreach (array('ins1', 'ins2', 'ins3') as $value) {
509 if (strpos($insgot, $value) !== false &&
510 strpos($inseobs, $value) === false)
511 --$duncount;
515 // $isdue = ($row['duedate'] <= $today && $row['amount'] > $row['paid']) ? " checked" : "";
517 $isdueany = sprintf("%.2f",$row['amount']) > sprintf("%.2f",$row['paid']);
519 // An invoice is now due from the patient if money is owed and we are
520 // not waiting for insurance to pay. We no longer look at the due date
521 // for this.
523 $isduept = ($duncount >= 0 && $isdueany) ? " checked" : "";
525 // Skip invoices not in the desired "Due..." category.
527 if (substr($_POST['form_category'], 0, 3) == 'Due' && !$isdueany) continue;
528 if ($_POST['form_category'] == 'Due Ins' && ($duncount >= 0 || !$isdueany)) continue;
529 if ($_POST['form_category'] == 'Due Pt' && ($duncount < 0 || !$isdueany)) continue;
531 $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
533 // Determine the date of service. If this was a search parameter
534 // then we already know it. Or an 8-digit encounter number is
535 // presumed to be a date of service imported during conversion.
536 // Otherwise look it up in the form_encounter table.
538 $svcdate = "";
539 list($pid, $encounter) = explode(".", $row['invnumber']);
540 // if ($form_date) {
541 // $svcdate = $form_date;
542 // } else
543 if (strlen($encounter) == 8) {
544 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
545 "-" . substr($encounter, 6, 2);
547 else if ($encounter) {
548 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
549 "encounter = $encounter");
550 $svcdate = substr($tmp['date'], 0, 10);
553 <tr bgcolor='<?php echo $bgcolor ?>'>
554 <td class="detail">
555 &nbsp;<a href="" onclick="return npopup(<?php echo $pid ?>)"
556 ><?php echo $row['lname'] . ', ' . $row['fname']; ?></a>
557 </td>
558 <td class="detail">
559 &nbsp;<a href="sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
560 target="_blank"><?php echo $row['invnumber'] ?></a>
561 </td>
562 <td class="detail">
563 &nbsp;<?php echo $svcdate ?>
564 </td>
565 <td class="detail">
566 &nbsp;<?php echo $row['duedate'] ?>
567 </td>
568 <td class="detail" align="right">
569 <?php bucks($row['charges']) ?>&nbsp;
570 </td>
571 <td class="detail" align="right">
572 <?php bucks($row['adjustments']) ?>&nbsp;
573 </td>
574 <td class="detail" align="right">
575 <?php bucks($row['paid']) ?>&nbsp;
576 </td>
577 <td class="detail" align="right">
578 <?php bucks($row['charges'] + $row['adjustments'] - $row['paid']) ?>&nbsp;
579 </td>
580 <td class="detail" align="center">
581 <?php echo $duncount ? $duncount : "&nbsp;" ?>
582 </td>
583 <?php if (!$eracount) { ?>
584 <td class="detail" align="center">
585 <input type='checkbox' name='form_cb[<?php echo($row['id']) ?>]'<?php echo $isduept ?> />
586 </td>
587 <?php } ?>
588 </tr>
592 SLClose();
595 </table>
598 <?php if ($eracount) { ?>
599 <input type='button' value='Process ERA File' onclick='processERA()' /> &nbsp;
600 <?php } else { ?>
601 <input type='button' value='Select All' onclick='checkAll(true)' /> &nbsp;
602 <input type='button' value='Clear All' onclick='checkAll(false)' /> &nbsp;
603 <input type='submit' name='form_print' value='Print Selected Statements' /> &nbsp;
604 <?php } ?>
605 <input type='checkbox' name='form_without' value='1' /> <?xl('Without Update','e')?>
606 </p>
608 </form>
609 </center>
610 <script language="JavaScript">
611 function processERA() {
612 var f = document.forms[0];
613 var debug = f.form_without.checked ? '1' : '0';
614 window.open('sl_eob_process.php?eraname=<?php echo $eraname ?>&debug=' + debug, '_blank');
615 return false;
617 <?php
618 if ($alertmsg) {
619 echo "alert('" . htmlentities($alertmsg) . "');\n";
622 </script>
623 </body>
624 </html>