Left menu navigations bug fixes:
[openemr.git] / interface / billing / sl_eob_search.php
blob84c09e1e9d04b201fc16566d22e7754127ee0697
1 <?php
2 // Copyright (C) 2005-2010 Rod Roark <rod@sunsetsystems.com>
3 //
4 // Windows compatibility and statement downloading:
5 // 2009 Bill Cernansky and Tony McCormick [mi-squared.com]
6 //
7 // This program is free software; you can redistribute it and/or
8 // modify it under the terms of the GNU General Public License
9 // as published by the Free Software Foundation; either version 2
10 // of the License, or (at your option) any later version.
12 // This is the first of two pages to support posting of EOBs.
13 // The second is sl_eob_invoice.php.
15 require_once("../globals.php");
16 require_once("$srcdir/patient.inc");
17 require_once("$srcdir/sql-ledger.inc");
18 require_once("$srcdir/invoice_summary.inc.php");
19 require_once($GLOBALS['OE_SITE_DIR'] . "/statement.inc.php");
20 require_once("$srcdir/parse_era.inc.php");
21 require_once("$srcdir/sl_eob.inc.php");
22 require_once("$srcdir/formatting.inc.php");
23 require_once("$srcdir/classes/class.ezpdf.php");//for the purpose of pdf creation
25 $DEBUG = 0; // set to 0 for production, 1 to test
27 $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
29 $alertmsg = '';
30 $where = '';
31 $eraname = '';
32 $eracount = 0;
34 // This is called back by parse_era() if we are processing X12 835's.
36 function era_callback(&$out) {
37 global $where, $eracount, $eraname, $INTEGRATED_AR;
38 // print_r($out); // debugging
39 ++$eracount;
40 // $eraname = $out['isa_control_number'];
41 $eraname = $out['gs_date'] . '_' . ltrim($out['isa_control_number'], '0') .
42 '_' . ltrim($out['payer_id'], '0');
43 list($pid, $encounter, $invnumber) = slInvoiceNumber($out);
45 if ($pid && $encounter) {
46 if ($where) $where .= ' OR ';
47 if ($INTEGRATED_AR) {
48 $where .= "( f.pid = '$pid' AND f.encounter = '$encounter' )";
49 } else {
50 $where .= "invnumber = '$invnumber'";
55 function bucks($amount) {
56 if ($amount) echo oeFormatMoney($amount);
59 // Upload a file to the client's browser
61 function upload_file_to_client($file_to_send) {
62 header("Pragma: public");
63 header("Expires: 0");
64 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
65 header("Content-Type: application/force-download");
66 header("Content-Length: " . filesize($file_to_send));
67 header("Content-Disposition: attachment; filename=" . basename($file_to_send));
68 header("Content-Description: File Transfer");
69 readfile($file_to_send);
70 // flush the content to the browser. If you don't do this, the text from the subsequent
71 // output from this script will be in the file instead of sent to the browser.
72 flush();
73 exit(); //added to exit from process properly in order to stop bad html code -ehrlive
74 // sleep one second to ensure there's no follow-on.
75 sleep(1);
77 function upload_file_to_client_pdf($file_to_send) {
78 //Function reads a text file and converts to pdf.
80 global $STMT_TEMP_FILE_PDF;
81 $pdf =& new Cezpdf('LETTER');//pdf creation starts
82 $pdf->ezSetMargins(36,0,36,0);
83 $pdf->selectFont($GLOBALS['fileroot'] . "/library/fonts/Courier.afm");
84 $pdf->ezSetY($pdf->ez['pageHeight'] - $pdf->ez['topMargin']);
85 $countline=1;
86 $file = fopen($file_to_send, "r");//this file contains the text to be converted to pdf.
87 while(!feof($file))
89 $OneLine=fgets($file);//one line is read
90 if(stristr($OneLine, "\014") == true && !feof($file))//form feed means we should start a new page.
92 $pdf->ezNewPage();
93 $pdf->ezSetY($pdf->ez['pageHeight'] - $pdf->ez['topMargin']);
94 str_replace("\014", "", $OneLine);
97 if(stristr($OneLine, 'REMIT TO') == true || stristr($OneLine, 'Visit Date') == true)//lines are made bold when 'REMIT TO' or 'Visit Date' is there.
98 $pdf->ezText('<b>'.$OneLine.'</b>', 12, array('justification' => 'left', 'leading' => 6));
99 else
100 $pdf->ezText($OneLine, 12, array('justification' => 'left', 'leading' => 6));
102 $countline++;
105 $fh = @fopen($STMT_TEMP_FILE_PDF, 'w');//stored to a pdf file
106 if ($fh) {
107 fwrite($fh, $pdf->ezOutput());
108 fclose($fh);
110 header("Pragma: public");//this section outputs the pdf file to browser
111 header("Expires: 0");
112 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
113 header("Content-Type: application/force-download");
114 header("Content-Length: " . filesize($STMT_TEMP_FILE_PDF));
115 header("Content-Disposition: attachment; filename=" . basename($STMT_TEMP_FILE_PDF));
116 header("Content-Description: File Transfer");
117 readfile($STMT_TEMP_FILE_PDF);
118 // flush the content to the browser. If you don't do this, the text from the subsequent
119 // output from this script will be in the file instead of sent to the browser.
120 flush();
121 exit(); //added to exit from process properly in order to stop bad html code -ehrlive
122 // sleep one second to ensure there's no follow-on.
123 sleep(1);
127 $today = date("Y-m-d");
129 if ($INTEGRATED_AR) {
131 // Print or download statements if requested.
133 if (($_POST['form_print'] || $_POST['form_download'] || $_POST['form_pdf']) && $_POST['form_cb']) {
135 $fhprint = fopen($STMT_TEMP_FILE, 'w');
137 $where = "";
138 foreach ($_POST['form_cb'] as $key => $value) $where .= " OR f.id = $key";
139 $where = substr($where, 4);
141 $res = sqlStatement("SELECT " .
142 "f.id, f.date, f.pid, f.encounter, f.stmt_count, f.last_stmt_date, " .
143 "p.fname, p.mname, p.lname, p.street, p.city, p.state, p.postal_code " .
144 "FROM form_encounter AS f, patient_data AS p " .
145 "WHERE ( $where ) AND " .
146 "p.pid = f.pid " .
147 "ORDER BY p.lname, p.fname, f.pid, f.date, f.encounter");
149 $stmt = array();
150 $stmt_count = 0;
152 // This loops once for each invoice/encounter.
154 while ($row = sqlFetchArray($res)) {
155 $svcdate = substr($row['date'], 0, 10);
156 $duedate = $svcdate; // TBD?
157 $duncount = $row['stmt_count'];
159 // If this is a new patient then print the pending statement
160 // and start a new one. This is an associative array:
162 // cid = same as pid
163 // pid = OpenEMR patient ID
164 // patient = patient name
165 // amount = total amount due
166 // adjust = adjustments (already applied to amount)
167 // duedate = due date of the oldest included invoice
168 // age = number of days from duedate to today
169 // to = array of addressee name/address lines
170 // lines = array of:
171 // dos = date of service "yyyy-mm-dd"
172 // desc = description
173 // amount = charge less adjustments
174 // paid = amount paid
175 // notice = 1 for first notice, 2 for second, etc.
176 // detail = array of details, see invoice_summary.inc.php
178 if ($stmt['cid'] != $row['pid']) {
179 if (!empty($stmt)) ++$stmt_count;
180 fwrite($fhprint, create_statement($stmt));
181 $stmt['cid'] = $row['pid'];
182 $stmt['pid'] = $row['pid'];
183 $stmt['patient'] = $row['fname'] . ' ' . $row['lname'];
184 $stmt['to'] = array($row['fname'] . ' ' . $row['lname']);
185 if ($row['street']) $stmt['to'][] = $row['street'];
186 $stmt['to'][] = $row['city'] . ", " . $row['state'] . " " . $row['postal_code'];
187 $stmt['lines'] = array();
188 $stmt['amount'] = '0.00';
189 $stmt['today'] = $today;
190 $stmt['duedate'] = $duedate;
191 } else {
192 // Report the oldest due date.
193 if ($duedate < $stmt['duedate']) {
194 $stmt['duedate'] = $duedate;
198 // Recompute age at each invoice.
199 $stmt['age'] = round((strtotime($today) - strtotime($stmt['duedate'])) /
200 (24 * 60 * 60));
202 $invlines = ar_get_invoice_summary($row['pid'], $row['encounter'], true);
203 foreach ($invlines as $key => $value) {
204 $line = array();
205 $line['dos'] = $svcdate;
206 $line['desc'] = ($key == 'CO-PAY') ? "Patient Payment" : "Procedure $key";
207 $line['amount'] = sprintf("%.2f", $value['chg']);
208 $line['adjust'] = sprintf("%.2f", $value['adj']);
209 $line['paid'] = sprintf("%.2f", $value['chg'] - $value['bal']);
210 $line['notice'] = $duncount + 1;
211 $line['detail'] = $value['dtl'];
212 $stmt['lines'][] = $line;
213 $stmt['amount'] = sprintf("%.2f", $stmt['amount'] + $value['bal']);
216 // Record that this statement was run.
217 if (! $DEBUG && ! $_POST['form_without']) {
218 sqlStatement("UPDATE form_encounter SET " .
219 "last_stmt_date = '$today', stmt_count = stmt_count + 1 " .
220 "WHERE id = " . $row['id']);
222 } // end for
224 if (!empty($stmt)) ++$stmt_count;
225 fwrite($fhprint, create_statement($stmt));
226 fclose($fhprint);
227 sleep(1);
229 // Download or print the file, as selected
230 if ($_POST['form_download']) {
231 upload_file_to_client($STMT_TEMP_FILE);
232 } elseif ($_POST['form_pdf']) {
233 upload_file_to_client_pdf($STMT_TEMP_FILE);
234 } else { // Must be print!
235 if ($DEBUG) {
236 $alertmsg = xl("Printing skipped; see test output in") .' '. $STMT_TEMP_FILE;
237 } else {
238 exec("$STMT_PRINT_CMD $STMT_TEMP_FILE");
239 if ($_POST['form_without']) {
240 $alertmsg = xl('Now printing') .' '. $stmt_count .' '. xl('statements; invoices will not be updated.');
241 } else {
242 $alertmsg = xl('Now printing') .' '. $stmt_count .' '. xl('statements and updating invoices.');
244 } // end not debug
245 } // end not form_download
246 } // end statements requested
247 } // end $INTEGRATED_AR
248 else {
249 SLConnect();
251 // This will be true starting with SQL-Ledger 2.8.x:
252 $got_address_table = SLQueryValue("SELECT count(*) FROM pg_tables WHERE " .
253 "schemaname = 'public' AND tablename = 'address'");
255 // Print or download statements if requested.
257 if (($_POST['form_print'] || $_POST['form_download'] || $_POST['form_pdf']) && $_POST['form_cb']) {
259 $fhprint = fopen($STMT_TEMP_FILE, 'w');
261 $where = "";
262 foreach ($_POST['form_cb'] as $key => $value) $where .= " OR ar.id = $key";
263 $where = substr($where, 4);
265 // Sort by patient so that multiple invoices can be
266 // represented on a single statement.
267 if ($got_address_table) {
268 $res = SLQuery("SELECT ar.*, customer.name, " .
269 "address.address1, address.address2, " .
270 "address.city, address.state, address.zipcode, " .
271 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS fname, " .
272 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS lname " .
273 "FROM ar, customer, address WHERE ( $where ) AND " .
274 "customer.id = ar.customer_id AND " .
275 "address.trans_id = ar.customer_id " .
276 "ORDER BY lname, fname, ar.customer_id, ar.transdate");
278 else {
279 $res = SLQuery("SELECT ar.*, customer.name, " .
280 "customer.address1, customer.address2, " .
281 "customer.city, customer.state, customer.zipcode, " .
282 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS lname, " .
283 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS fname " .
284 "FROM ar, customer WHERE ( $where ) AND " .
285 "customer.id = ar.customer_id " .
286 "ORDER BY lname, fname, ar.customer_id, ar.transdate");
288 if ($sl_err) die($sl_err);
290 $stmt = array();
291 $stmt_count = 0;
293 for ($irow = 0; $irow < SLRowCount($res); ++$irow) {
294 $row = SLGetRow($res, $irow);
296 // Determine the date of service. An 8-digit encounter number is
297 // presumed to be a date of service imported during conversion.
298 // Otherwise look it up in the form_encounter table.
300 $svcdate = "";
301 list($pid, $encounter) = explode(".", $row['invnumber']);
302 if (strlen($encounter) == 8) {
303 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
304 "-" . substr($encounter, 6, 2);
305 } else if ($encounter) {
306 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
307 "encounter = $encounter");
308 $svcdate = substr($tmp['date'], 0, 10);
311 // How many times have we dunned them for this invoice?
312 $intnotes = trim($row['intnotes']);
313 $duncount = substr_count(strtolower($intnotes), "statement sent");
315 // If this is a new patient then print the pending statement
316 // and start a new one. This is an associative array:
318 // cid = SQL-Ledger customer ID
319 // pid = OpenEMR patient ID
320 // patient = patient name
321 // amount = total amount due
322 // adjust = adjustments (already applied to amount)
323 // duedate = due date of the oldest included invoice
324 // age = number of days from duedate to today
325 // to = array of addressee name/address lines
326 // lines = array of:
327 // dos = date of service "yyyy-mm-dd"
328 // desc = description
329 // amount = charge less adjustments
330 // paid = amount paid
331 // notice = 1 for first notice, 2 for second, etc.
332 // detail = array of details, see invoice_summary.inc.php
334 if ($stmt['cid'] != $row['customer_id']) {
335 if (!empty($stmt)) ++$stmt_count;
336 fwrite($fhprint, create_statement($stmt));
337 $stmt['cid'] = $row['customer_id'];
338 $stmt['pid'] = $pid;
340 if ($got_address_table) {
341 $stmt['patient'] = $row['fname'] . ' ' . $row['lname'];
342 $stmt['to'] = array($row['fname'] . ' ' . $row['lname']);
343 } else {
344 $stmt['patient'] = $row['name'];
345 $stmt['to'] = array($row['name']);
348 if ($row['address1']) $stmt['to'][] = $row['address1'];
349 if ($row['address2']) $stmt['to'][] = $row['address2'];
350 $stmt['to'][] = $row['city'] . ", " . $row['state'] . " " . $row['zipcode'];
351 $stmt['lines'] = array();
352 $stmt['amount'] = '0.00';
353 $stmt['today'] = $today;
354 $stmt['duedate'] = $row['duedate'];
355 } else {
356 // Report the oldest due date.
357 if ($row['duedate'] < $stmt['duedate']) {
358 $stmt['duedate'] = $row['duedate'];
362 $stmt['age'] = round((strtotime($today) - strtotime($stmt['duedate'])) /
363 (24 * 60 * 60));
365 $invlines = get_invoice_summary($row['id'], true); // true added by Rod 2006-06-09
366 foreach ($invlines as $key => $value) {
367 $line = array();
368 $line['dos'] = $svcdate;
369 $line['desc'] = ($key == 'CO-PAY') ? "Patient Payment" : "Procedure $key";
370 $line['amount'] = sprintf("%.2f", $value['chg']);
371 $line['adjust'] = sprintf("%.2f", $value['adj']);
372 $line['paid'] = sprintf("%.2f", $value['chg'] - $value['bal']);
373 $line['notice'] = $duncount + 1;
374 $line['detail'] = $value['dtl']; // Added by Rod 2006-06-09
375 $stmt['lines'][] = $line;
376 $stmt['amount'] = sprintf("%.2f", $stmt['amount'] + $value['bal']);
379 // Record something in ar.intnotes about this statement run.
380 if ($intnotes) $intnotes .= "\n";
381 $intnotes = addslashes($intnotes . "Statement sent $today");
382 if (! $DEBUG && ! $_POST['form_without']) {
383 SLQuery("UPDATE ar SET intnotes = '$intnotes' WHERE id = " . $row['id']);
384 if ($sl_err) die($sl_err);
386 } // end for
388 if (!empty($stmt)) ++$stmt_count;
389 fwrite($fhprint, create_statement($stmt));
390 fclose($fhprint);
391 sleep(1);
393 // Download or print the file, as selected
394 if ($_POST['form_download']) {
395 upload_file_to_client($STMT_TEMP_FILE);
396 } elseif ($_POST['form_pdf']) {
397 upload_file_to_client_pdf($STMT_TEMP_FILE);
398 } else { // Must be print!
399 if ($DEBUG) {
400 $alertmsg = xl("Printing skipped; see test output in") .' '. $STMT_TEMP_FILE;
401 } else {
402 exec("$STMT_PRINT_CMD $STMT_TEMP_FILE");
403 if ($_POST['form_without']) {
404 $alertmsg = xl('Now printing') .' '. $stmt_count .' '. xl('statements; invoices will not be updated.');
405 } else {
406 $alertmsg = xl('Now printing') .' '. $stmt_count .' '. xl('statements and updating invoices.');
408 } // end not debug
409 } // end if form_download
410 } // end statements requested
411 } // end not $INTEGRATED_AR
413 <html>
414 <head>
415 <?php html_header_show(); ?>
416 <link rel=stylesheet href="<?echo $css_header;?>" type="text/css">
417 <title><?php xl('EOB Posting - Search','e'); ?></title>
418 <script type="text/javascript" src="../../library/textformat.js"></script>
420 <script language="JavaScript">
422 var mypcc = '1';
424 function checkAll(checked) {
425 var f = document.forms[0];
426 for (var i = 0; i < f.elements.length; ++i) {
427 var ename = f.elements[i].name;
428 if (ename.indexOf('form_cb[') == 0)
429 f.elements[i].checked = checked;
433 function npopup(pid) {
434 window.open('sl_eob_patient_note.php?patient_id=' + pid, '_blank', 'width=500,height=250,resizable=1');
435 return false;
438 </script>
440 </head>
442 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
443 <center>
445 <form method='post' action='sl_eob_search.php' enctype='multipart/form-data'>
447 <table border='0' cellpadding='5' cellspacing='0'>
448 <tr>
450 <?php
451 if ($INTEGRATED_AR) {
452 // Identify the payer to support resumable posting sessions.
453 echo " <td>\n";
454 echo " " . xl('Payer') . ":\n";
455 echo " </td>\n";
456 echo " <td>\n";
457 $insurancei = getInsuranceProviders();
458 echo " <select name='form_payer_id'>\n";
459 echo " <option value='0'>-- " . xl('Patient') . " --</option>\n";
460 foreach ($insurancei as $iid => $iname) {
461 echo "<option value='$iid'";
462 if ($iid == $_POST['form_payer_id']) echo " selected";
463 echo ">" . $iname . "</option>\n";
465 echo " </select>\n";
466 echo " </td>\n";
470 <td>
471 <?php xl('Source:','e'); ?>
472 </td>
473 <td>
474 <input type='text' name='form_source' size='10' value='<?php echo $_POST['form_source']; ?>'
475 title='<?php xl("A check number or claim number to identify the payment","e"); ?>'>
476 </td>
477 <td>
478 <?php xl('Pay Date:','e'); ?>
479 </td>
480 <td>
481 <input type='text' name='form_paydate' size='10' value='<?php echo $_POST['form_paydate']; ?>'
482 onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)'
483 title='<?php xl("Date of payment yyyy-mm-dd","e"); ?>'>
484 </td>
486 <?php if ($INTEGRATED_AR) { // include deposit date ?>
487 <td>
488 <?php xl('Deposit Date:','e'); ?>
489 </td>
490 <td>
491 <input type='text' name='form_deposit_date' size='10' value='<?php echo $_POST['form_deposit_date']; ?>'
492 onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)'
493 title='<?php xl("Date of bank deposit yyyy-mm-dd","e"); ?>'>
494 </td>
495 <?php } ?>
497 <td>
498 <?php xl('Amount:','e'); ?>
499 </td>
500 <td>
501 <input type='text' name='form_amount' size='10' value='<?php echo $_POST['form_amount']; ?>'
502 title='<?php xl("Paid amount that you will allocate","e"); ?>'>
503 </td>
504 <td align='right'>
505 <a href='sl_eob_help.php' target='_blank'><?php xl('Help','e'); ?></a>
506 </td>
508 </tr>
509 </table>
511 <table border='0' cellpadding='5' cellspacing='0'>
513 <tr bgcolor='#ddddff'>
514 <td>
515 <?php xl('Name:','e'); ?>
516 </td>
517 <td>
518 <input type='text' name='form_name' size='10' value='<?php echo $_POST['form_name']; ?>'
519 title='<?php xl("Any part of the patient name, or \"last,first\", or \"X-Y\"","e"); ?>'>
520 </td>
521 <td>
522 <?php xl('Chart ID:','e'); ?>
523 </td>
524 <td>
525 <input type='text' name='form_pid' size='10' value='<?php echo $_POST['form_pid']; ?>'
526 title='<?php xl("Patient chart ID","e"); ?>'>
527 </td>
528 <td>
529 <?php xl('Encounter:','e'); ?>
530 </td>
531 <td>
532 <input type='text' name='form_encounter' size='10' value='<?php echo $_POST['form_encounter']; ?>'
533 title='<?php xl("Encounter number","e"); ?>'>
534 </td>
535 <td>
536 <?php xl('Svc Date:','e'); ?>
537 </td>
538 <td>
539 <input type='text' name='form_date' size='10' value='<?php echo $_POST['form_date']; ?>'
540 title='<?php xl("Date of service mm/dd/yyyy","e"); ?>'>
541 </td>
542 <td>
543 <?php xl('To:','e'); ?>
544 </td>
545 <td>
546 <input type='text' name='form_to_date' size='10' value='<?php echo $_POST['form_to_date']; ?>'
547 title='<?php xl("Ending DOS mm/dd/yyyy if you wish to enter a range","e"); ?>'>
548 </td>
549 <td>
550 <select name='form_category'>
551 <?php
552 foreach (array(xl('Open'), xl('All'), xl('Due Pt'), xl('Due Ins')) as $value) {
553 echo " <option value='$value'";
554 if ($_POST['form_category'] == $value) echo " selected";
555 echo ">$value</option>\n";
558 </select>
559 </td>
560 <td>
561 <input type='submit' name='form_search' value='<?php xl("Search","e"); ?>'>
562 </td>
563 </tr>
565 <!-- Support for X12 835 upload -->
566 <tr bgcolor='#ddddff'>
567 <td colspan='12'>
568 <?php xl('Or upload ERA file:','e'); ?>
569 <input type="hidden" name="MAX_FILE_SIZE" value="5000000" />
570 <input name="form_erafile" type="file" />
571 </td>
572 </tr>
574 <tr>
575 <td height="1" colspan="10">
576 </td>
577 </tr>
579 </table>
581 <?php
582 if ($_POST['form_search'] || $_POST['form_print']) {
583 $form_name = trim($_POST['form_name']);
584 $form_pid = trim($_POST['form_pid']);
585 $form_encounter = trim($_POST['form_encounter']);
586 $form_date = fixDate($_POST['form_date'], "");
587 $form_to_date = fixDate($_POST['form_to_date'], "");
589 $where = "";
591 // Handle X12 835 file upload.
593 if ($_FILES['form_erafile']['size']) {
594 $tmp_name = $_FILES['form_erafile']['tmp_name'];
596 // Handle .zip extension if present. Probably won't work on Windows.
597 if (strtolower(substr($_FILES['form_erafile']['name'], -4)) == '.zip') {
598 rename($tmp_name, "$tmp_name.zip");
599 exec("unzip -p $tmp_name.zip > $tmp_name");
600 unlink("$tmp_name.zip");
603 echo "<!-- Notes from ERA upload processing:\n";
604 $alertmsg .= parse_era($tmp_name, 'era_callback');
605 echo "-->\n";
606 $erafullname = $GLOBALS['OE_SITE_DIR'] . "/era/$eraname.edi";
608 if (is_file($erafullname)) {
609 $alertmsg .= "Warning: Set $eraname was already uploaded ";
610 if (is_file($GLOBALS['OE_SITE_DIR'] . "/era/$eraname.html"))
611 $alertmsg .= "and processed. ";
612 else
613 $alertmsg .= "but not yet processed. ";
615 rename($tmp_name, $erafullname);
616 } // End 835 upload
618 if ($INTEGRATED_AR) {
619 if ($eracount) {
620 // Note that parse_era() modified $eracount and $where.
621 if (! $where) $where = '1 = 2';
623 else {
624 if ($form_name) {
625 if ($where) $where .= " AND ";
626 // Allow the last name to be followed by a comma and some part of a first name.
627 if (preg_match('/^(.*\S)\s*,\s*(.*)/', $form_name, $matches)) {
628 $where .= "p.lname LIKE '" . $matches[1] . "%' AND p.fname LIKE '" . $matches[2] . "%'";
629 // Allow a filter like "A-C" on the first character of the last name.
630 } else if (preg_match('/^(\S)\s*-\s*(\S)$/', $form_name, $matches)) {
631 $tmp = '1 = 2';
632 while (ord($matches[1]) <= ord($matches[2])) {
633 $tmp .= " OR p.lname LIKE '" . $matches[1] . "%'";
634 $matches[1] = chr(ord($matches[1]) + 1);
636 $where .= "( $tmp ) ";
637 } else {
638 $where .= "p.lname LIKE '%$form_name%'";
641 if ($form_pid) {
642 if ($where) $where .= " AND ";
643 $where .= "f.pid = '$form_pid'";
645 if ($form_encounter) {
646 if ($where) $where .= " AND ";
647 $where .= "f.encounter = '$form_encounter'";
649 if ($form_date) {
650 if ($where) $where .= " AND ";
651 if ($form_to_date) {
652 $where .= "f.date >= '$form_date' AND f.date <= '$form_to_date'";
654 else {
655 $where .= "f.date = '$form_date'";
658 if (! $where) {
659 if ($_POST['form_category'] == 'All') {
660 die(xl("At least one search parameter is required if you select All."));
661 } else {
662 $where = "1 = 1";
667 $query = "SELECT f.id, f.pid, f.encounter, f.date, " .
668 "f.last_level_billed, f.last_level_closed, f.last_stmt_date, f.stmt_count, " .
669 "p.fname, p.mname, p.lname, p.pubpid, p.genericname2, p.genericval2, " .
670 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
671 "b.pid = f.pid AND b.encounter = f.encounter AND " .
672 "b.activity = 1 AND b.code_type != 'COPAY' ) AS charges, " .
673 "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
674 "b.pid = f.pid AND b.encounter = f.encounter AND " .
675 "b.activity = 1 AND b.code_type = 'COPAY' ) AS copays, " .
676 "( SELECT SUM(a.pay_amount) FROM ar_activity AS a WHERE " .
677 "a.pid = f.pid AND a.encounter = f.encounter ) AS payments, " .
678 "( SELECT SUM(a.adj_amount) FROM ar_activity AS a WHERE " .
679 "a.pid = f.pid AND a.encounter = f.encounter ) AS adjustments " .
680 "FROM form_encounter AS f " .
681 "JOIN patient_data AS p ON p.pid = f.pid " .
682 "WHERE $where " .
683 "ORDER BY p.lname, p.fname, p.mname, f.pid, f.encounter";
685 // Note that unlike the SQL-Ledger case, this query does not weed
686 // out encounters that are paid up. Also the use of sub-selects
687 // will require MySQL 4.1 or greater.
689 // echo "<!-- $query -->\n"; // debugging
691 $t_res = sqlStatement($query);
693 $num_invoices = mysql_num_rows($t_res);
694 if ($eracount && $num_invoices != $eracount) {
695 $alertmsg .= "Of $eracount remittances, there are $num_invoices " .
696 "matching encounters in OpenEMR. ";
698 } // end $INTEGRATED_AR
699 else {
700 if ($eracount) {
701 // Note that parse_era() modified $eracount and $where.
702 if (! $where) $where = '1 = 2';
704 else {
705 if ($form_name) {
706 if ($where) $where .= " AND ";
707 // Allow the last name to be followed by a comma and some part of a first name.
708 if (preg_match('/^(.*\S)\s*,\s*(.*)/', $form_name, $matches)) {
709 $where .= "customer.name ILIKE '" . $matches[2] . '% ' . $matches[1] . "%'";
710 // Allow a filter like "A-C" on the first character of the last name.
711 } else if (preg_match('/^(\S)\s*-\s*(\S)$/', $form_name, $matches)) {
712 $tmp = '1 = 2';
713 while (ord($matches[1]) <= ord($matches[2])) {
714 // $tmp .= " OR customer.name ILIKE '% " . $matches[1] . "%'";
715 // Fixing the above which was also matching on middle names:
716 $tmp .= " OR customer.name ~* ' " . $matches[1] . "[A-Z]*$'";
717 $matches[1] = chr(ord($matches[1]) + 1);
719 $where .= "( $tmp ) ";
720 } else {
721 $where .= "customer.name ILIKE '%$form_name%'";
724 if ($form_pid && $form_encounter) {
725 if ($where) $where .= " AND ";
726 $where .= "ar.invnumber = '$form_pid.$form_encounter'";
728 else if ($form_pid) {
729 if ($where) $where .= " AND ";
730 $where .= "ar.invnumber LIKE '$form_pid.%'";
732 else if ($form_encounter) {
733 if ($where) $where .= " AND ";
734 $where .= "ar.invnumber like '%.$form_encounter'";
737 if ($form_date) {
738 if ($where) $where .= " AND ";
739 $date1 = substr($form_date, 0, 4) . substr($form_date, 5, 2) .
740 substr($form_date, 8, 2);
741 if ($form_to_date) {
742 $date2 = substr($form_to_date, 0, 4) . substr($form_to_date, 5, 2) .
743 substr($form_to_date, 8, 2);
744 $where .= "((CAST (substring(ar.invnumber from position('.' in ar.invnumber) + 1 for 8) AS integer) " .
745 "BETWEEN '$date1' AND '$date2')";
746 $tmp = "date >= '$form_date' AND date <= '$form_to_date'";
748 else {
749 // This catches old converted invoices where we have no encounters:
750 $where .= "(ar.invnumber LIKE '%.$date1'";
751 $tmp = "date = '$form_date'";
753 // Pick out the encounters from MySQL with the desired DOS:
754 $rez = sqlStatement("SELECT pid, encounter FROM form_encounter WHERE $tmp");
755 while ($row = sqlFetchArray($rez)) {
756 $where .= " OR ar.invnumber = '" . $row['pid'] . "." . $row['encounter'] . "'";
758 $where .= ")";
761 if (! $where) {
762 if ($_POST['form_category'] == 'All') {
763 die(xl("At least one search parameter is required if you select All."));
764 } else {
765 $where = "1 = 1";
770 $query = "SELECT ar.id, ar.invnumber, ar.duedate, ar.amount, ar.paid, " .
771 "ar.intnotes, ar.notes, ar.shipvia, customer.name, customer.id AS custid, ";
772 if ($got_address_table) $query .=
773 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS lname, " .
774 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS fname, ";
775 else $query .=
776 "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS lname, " .
777 "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS fname, ";
778 $query .=
779 "(SELECT SUM(invoice.sellprice * invoice.qty) FROM invoice WHERE " .
780 "invoice.trans_id = ar.id AND invoice.sellprice > 0) AS charges, " .
781 "(SELECT SUM(invoice.sellprice * invoice.qty) FROM invoice WHERE " .
782 "invoice.trans_id = ar.id AND invoice.sellprice < 0) AS adjustments " .
783 "FROM ar, customer WHERE ( $where ) AND customer.id = ar.customer_id ";
784 if ($_POST['form_category'] != 'All' && !$eracount) {
785 $query .= "AND ar.amount != ar.paid ";
786 // if ($_POST['form_category'] == 'Due') {
787 // $query .= "AND ar.duedate <= CURRENT_DATE ";
788 // }
790 $query .= "ORDER BY lname, fname, ar.invnumber";
792 // echo "<!-- $query -->\n"; // debugging
794 $t_res = SLQuery($query);
795 if ($sl_err) die($sl_err);
797 $num_invoices = SLRowCount($t_res);
798 if ($eracount && $num_invoices != $eracount) {
799 $alertmsg .= "Of $eracount remittances, there are $num_invoices " .
800 "matching claims in OpenEMR. ";
803 } // end not $INTEGRATED_AR
806 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
808 <tr bgcolor="#dddddd">
809 <td class="dehead">
810 &nbsp;<?php xl('Patient','e'); ?>
811 </td>
812 <td class="dehead">
813 &nbsp;<?php xl('Invoice','e'); ?>
814 </td>
815 <td class="dehead">
816 &nbsp;<?php xl('Svc Date','e'); ?>
817 </td>
818 <td class="dehead">
819 &nbsp;<?php xl($INTEGRATED_AR ? 'Last Stmt' : 'Due Date','e'); ?>
820 </td>
821 <td class="dehead" align="right">
822 <?php xl('Charge','e'); ?>&nbsp;
823 </td>
824 <td class="dehead" align="right">
825 <?php xl('Adjust','e'); ?>&nbsp;
826 </td>
827 <td class="dehead" align="right">
828 <?php xl('Paid','e'); ?>&nbsp;
829 </td>
830 <td class="dehead" align="right">
831 <?php xl('Balance','e'); ?>&nbsp;
832 </td>
833 <td class="dehead" align="center">
834 <?php xl('Prv','e'); ?>
835 </td>
836 <?php if (!$eracount) { ?>
837 <td class="dehead" align="left">
838 <?php xl('Sel','e'); ?>
839 </td>
840 <?php } ?>
841 </tr>
843 <?php
844 $orow = -1;
846 if ($INTEGRATED_AR) {
847 while ($row = sqlFetchArray($t_res)) {
848 $balance = sprintf("%.2f", $row['charges'] + $row['copays'] - $row['payments'] - $row['adjustments']);
850 if ($_POST['form_category'] != 'All' && $eracount == 0 && $balance == 0) continue;
852 // $duncount was originally supposed to be the number of times that
853 // the patient was sent a statement for this invoice.
855 $duncount = $row['stmt_count'];
857 // But if we have not yet billed the patient, then compute $duncount as a
858 // negative count of the number of insurance plans for which we have not
859 // yet closed out insurance.
861 if (! $duncount) {
862 for ($i = 1; $i <= 3 && arGetPayerID($row['pid'], $row['date'], $i); ++$i) ;
863 $duncount = $row['last_level_closed'] + 1 - $i;
866 $isdueany = ($balance > 0);
868 // An invoice is now due from the patient if money is owed and we are
869 // not waiting for insurance to pay.
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 $svcdate = substr($row['date'], 0, 10);
882 $last_stmt_date = empty($row['last_stmt_date']) ? '' : $row['last_stmt_date'];
884 // Determine if customer is in collections.
886 $billnote = ($row['genericname2'] == 'Billing') ? $row['genericval2'] : '';
887 $in_collections = stristr($billnote, 'IN COLLECTIONS') !== false;
889 <tr bgcolor='<?php echo $bgcolor ?>'>
890 <td class="detail">
891 &nbsp;<a href="" onclick="return npopup(<?php echo $row['pid'] ?>)"
892 ><?php echo $row['lname'] . ', ' . $row['fname']; ?></a>
893 </td>
894 <td class="detail">
895 &nbsp;<a href="sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
896 target="_blank"><?php echo $row['pid'] . '.' . $row['encounter']; ?></a>
897 </td>
898 <td class="detail">
899 &nbsp;<?php echo oeFormatShortDate($svcdate) ?>
900 </td>
901 <td class="detail">
902 &nbsp;<?php echo oeFormatShortDate($last_stmt_date) ?>
903 </td>
904 <td class="detail" align="right">
905 <?php bucks($row['charges']) ?>&nbsp;
906 </td>
907 <td class="detail" align="right">
908 <?php bucks($row['adjustments']) ?>&nbsp;
909 </td>
910 <td class="detail" align="right">
911 <?php bucks($row['payments'] - $row['copays']); ?>&nbsp;
912 </td>
913 <td class="detail" align="right">
914 <?php bucks($balance); ?>&nbsp;
915 </td>
916 <td class="detail" align="center">
917 <?php echo $duncount ? $duncount : "&nbsp;" ?>
918 </td>
919 <?php if (!$eracount) { ?>
920 <td class="detail" align="left">
921 <input type='checkbox' name='form_cb[<?php echo($row['id']) ?>]'<?php echo $isduept ?> />
922 <?php if ($in_collections) echo "<b><font color='red'>IC</font></b>"; ?>
923 </td>
924 <?php } ?>
925 </tr>
926 <?php
927 } // end while
928 } // end $INTEGRATED_AR
930 else { // not $INTEGRATED_AR
931 for ($irow = 0; $irow < $num_invoices; ++$irow) {
932 $row = SLGetRow($t_res, $irow);
934 // $duncount was originally supposed to be the number of times that
935 // the patient was sent a statement for this invoice.
937 $duncount = substr_count(strtolower($row['intnotes']), "statement sent");
939 // But if we have not yet billed the patient, then compute $duncount as a
940 // negative count of the number of insurance plans for which we have not
941 // yet closed out insurance.
943 if (! $duncount) {
944 $insgot = strtolower($row['notes']);
945 $inseobs = strtolower($row['shipvia']);
946 foreach (array('ins1', 'ins2', 'ins3') as $value) {
947 if (strpos($insgot, $value) !== false &&
948 strpos($inseobs, $value) === false)
949 --$duncount;
953 // $isdue = ($row['duedate'] <= $today && $row['amount'] > $row['paid']) ? " checked" : "";
955 $isdueany = sprintf("%.2f",$row['amount']) > sprintf("%.2f",$row['paid']);
957 // An invoice is now due from the patient if money is owed and we are
958 // not waiting for insurance to pay. We no longer look at the due date
959 // for this.
961 $isduept = ($duncount >= 0 && $isdueany) ? " checked" : "";
963 // Skip invoices not in the desired "Due..." category.
965 if (substr($_POST['form_category'], 0, 3) == 'Due' && !$isdueany) continue;
966 if ($_POST['form_category'] == 'Due Ins' && ($duncount >= 0 || !$isdueany)) continue;
967 if ($_POST['form_category'] == 'Due Pt' && ($duncount < 0 || !$isdueany)) continue;
969 $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
971 // Determine the date of service. If this was a search parameter
972 // then we already know it. Or an 8-digit encounter number is
973 // presumed to be a date of service imported during conversion.
974 // Otherwise look it up in the form_encounter table.
976 $svcdate = "";
977 list($pid, $encounter) = explode(".", $row['invnumber']);
978 // if ($form_date) {
979 // $svcdate = $form_date;
980 // } else
981 if (strlen($encounter) == 8) {
982 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
983 "-" . substr($encounter, 6, 2);
985 else if ($encounter) {
986 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
987 "encounter = $encounter");
988 $svcdate = substr($tmp['date'], 0, 10);
991 // Get billing note to determine if customer is in collections.
993 $pdrow = sqlQuery("SELECT pd.genericname2, pd.genericval2 FROM " .
994 "integration_mapping AS im, patient_data AS pd WHERE " .
995 "im.foreign_id = " . $row['custid'] . " AND " .
996 "im.foreign_table = 'customer' AND " .
997 "pd.id = im.local_id");
998 $row['billnote'] = ($pdrow['genericname2'] == 'Billing') ? $pdrow['genericval2'] : '';
999 $in_collections = stristr($row['billnote'], 'IN COLLECTIONS') !== false;
1001 <tr bgcolor='<?php echo $bgcolor ?>'>
1002 <td class="detail">
1003 &nbsp;<a href="" onclick="return npopup(<?php echo $pid ?>)"
1004 ><?php echo $row['lname'] . ', ' . $row['fname']; ?></a>
1005 </td>
1006 <td class="detail">
1007 &nbsp;<a href="sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
1008 target="_blank"><?php echo $row['invnumber'] ?></a>
1009 </td>
1010 <td class="detail">
1011 &nbsp;<?php echo oeFormatShortDate($svcdate) ?>
1012 </td>
1013 <td class="detail">
1014 &nbsp;<?php echo oeFormatShortDate($row['duedate']) ?>
1015 </td>
1016 <td class="detail" align="right">
1017 <?php bucks($row['charges']) ?>&nbsp;
1018 </td>
1019 <td class="detail" align="right">
1020 <?php bucks($row['adjustments']) ?>&nbsp;
1021 </td>
1022 <td class="detail" align="right">
1023 <?php bucks($row['paid']) ?>&nbsp;
1024 </td>
1025 <td class="detail" align="right">
1026 <?php bucks($row['charges'] + $row['adjustments'] - $row['paid']) ?>&nbsp;
1027 </td>
1028 <td class="detail" align="center">
1029 <?php echo $duncount ? $duncount : "&nbsp;" ?>
1030 </td>
1031 <?php if (!$eracount) { ?>
1032 <td class="detail" align="left">
1033 <input type='checkbox' name='form_cb[<?php echo($row['id']) ?>]'<?php echo $isduept ?> />
1034 <?php if ($in_collections) echo "<b><font color='red'>IC</font></b>"; ?>
1035 </td>
1036 <?php } ?>
1037 </tr>
1039 } // end for
1040 } // end not $INTEGRATED_AR
1041 } // end search/print logic
1043 if (!$INTEGRATED_AR) SLClose();
1046 </table>
1049 <?php if ($eracount) { ?>
1050 <input type='button' value='<?php xl('Process ERA File','e')?>' onclick='processERA()' /> &nbsp;
1051 <?php } else { ?>
1052 <input type='button' value='<?php xl('Select All','e')?>' onclick='checkAll(true)' /> &nbsp;
1053 <input type='button' value='<?php xl('Clear All','e')?>' onclick='checkAll(false)' /> &nbsp;
1054 <input type='submit' name='form_print' value='<?php xl('Print Selected Statements','e'); ?>' /> &nbsp;
1055 <input type='submit' name='form_download' value='<?php xl('Download Selected Statements','e'); ?>' /> &nbsp;
1056 <input type='submit' name='form_pdf' value='<?php xl('PDF Download Selected Statements','e'); ?>' /> &nbsp;
1057 <?php } ?>
1058 <input type='checkbox' name='form_without' value='1' /> <?php xl('Without Update','e'); ?>
1059 </p>
1061 </form>
1062 </center>
1063 <script language="JavaScript">
1064 function processERA() {
1065 var f = document.forms[0];
1066 var debug = f.form_without.checked ? '1' : '0';
1067 var paydate = f.form_paydate.value;
1068 window.open('sl_eob_process.php?eraname=<?php echo $eraname ?>&debug=' + debug + '&paydate=' + paydate, '_blank');
1069 return false;
1071 <?php
1072 if ($alertmsg) {
1073 echo "alert('" . htmlentities($alertmsg) . "');\n";
1077 </script>
1078 </body>
1079 </html>