interface/billing/sl_eob_search.php

   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.
  11
  12 // This is the first of two pages to support posting of EOBs.
  13 // The second is sl_eob_invoice.php.
  14
  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
  24
  25 $DEBUG = 0; // set to 0 for production, 1 to test
  26
  27 $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
  28
  29 $alertmsg = '';
  30 $where = '';
  31 $eraname = '';
  32 $eracount = 0;
  33
  34 // This is called back by parse_era() if we are processing X12 835's.
  35 //
  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);
  44
  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'";
  51     }
  52   }
  53 }
  54
  55 function bucks($amount) {
  56   if ($amount) echo oeFormatMoney($amount);
  57 }
  58
  59 // Upload a file to the client's browser
  60 //
  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);
  76 }
  77 function upload_file_to_client_pdf($file_to_send) {
  78 //Function reads a text file and converts to pdf.
  79
  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))
  88    {
  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.
  91           {
  92             $pdf->ezNewPage();
  93             $pdf->ezSetY($pdf->ez['pageHeight'] - $pdf->ez['topMargin']);
  94                 str_replace("\014", "", $OneLine);
  95           }
  96
  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));
 101
 102         $countline++;
 103    }
 104
 105         $fh = @fopen($STMT_TEMP_FILE_PDF, 'w');//stored to a pdf file
 106     if ($fh) {
 107       fwrite($fh, $pdf->ezOutput());
 108       fclose($fh);
 109     }
 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);
 124 }
 125
 126
 127 $today = date("Y-m-d");
 128
 129 if ($INTEGRATED_AR) {
 130
 131   // Print or download statements if requested.
 132   //
 133   if (($_POST['form_print'] || $_POST['form_download'] || $_POST['form_pdf']) && $_POST['form_cb']) {
 134
 135     $fhprint = fopen($STMT_TEMP_FILE, 'w');
 136
 137     $where = "";
 138     foreach ($_POST['form_cb'] as $key => $value) $where .= " OR f.id = $key";
 139     $where = substr($where, 4);
 140
 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");
 148
 149     $stmt = array();
 150     $stmt_count = 0;
 151
 152     // This loops once for each invoice/encounter.
 153     //
 154     while ($row = sqlFetchArray($res)) {
 155       $svcdate = substr($row['date'], 0, 10);
 156       $duedate = $svcdate; // TBD?
 157       $duncount = $row['stmt_count'];
 158
 159       // If this is a new patient then print the pending statement
 160       // and start a new one.  This is an associative array:
 161       //
 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
 177       //
 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;
 195         }
 196       }
 197
 198       // Recompute age at each invoice.
 199       $stmt['age'] = round((strtotime($today) - strtotime($stmt['duedate'])) /
 200         (24 * 60 * 60));
 201
 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']);
 214       }
 215
 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']);
 221       }
 222     } // end for
 223
 224     if (!empty($stmt)) ++$stmt_count;
 225     fwrite($fhprint, create_statement($stmt));
 226     fclose($fhprint);
 227     sleep(1);
 228
 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.');
 243         }
 244       } // end not debug
 245     } // end not form_download
 246   } // end statements requested
 247 } // end $INTEGRATED_AR
 248 else {
 249   SLConnect();
 250
 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'");
 254
 255   // Print or download statements if requested.
 256   //
 257   if (($_POST['form_print'] || $_POST['form_download'] || $_POST['form_pdf']) && $_POST['form_cb']) {
 258
 259     $fhprint = fopen($STMT_TEMP_FILE, 'w');
 260
 261     $where = "";
 262     foreach ($_POST['form_cb'] as $key => $value) $where .= " OR ar.id = $key";
 263     $where = substr($where, 4);
 264
 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");
 277     }
 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");
 287     }
 288     if ($sl_err) die($sl_err);
 289
 290     $stmt = array();
 291     $stmt_count = 0;
 292
 293     for ($irow = 0; $irow < SLRowCount($res); ++$irow) {
 294       $row = SLGetRow($res, $irow);
 295
 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.
 299       //
 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);
 309       }
 310
 311       // How many times have we dunned them for this invoice?
 312       $intnotes = trim($row['intnotes']);
 313       $duncount = substr_count(strtolower($intnotes), "statement sent");
 314
 315       // If this is a new patient then print the pending statement
 316       // and start a new one.  This is an associative array:
 317       //
 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
 333       //
 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;
 339
 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']);
 346         }
 347
 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'];
 359         }
 360       }
 361
 362       $stmt['age'] = round((strtotime($today) - strtotime($stmt['duedate'])) /
 363         (24 * 60 * 60));
 364
 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']);
 377       }
 378
 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);
 385       }
 386     } // end for
 387
 388     if (!empty($stmt)) ++$stmt_count;
 389     fwrite($fhprint, create_statement($stmt));
 390     fclose($fhprint);
 391     sleep(1);
 392
 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.');
 407         }
 408       } // end not debug
 409     } // end if form_download
 410   } // end statements requested
 411 } // end not $INTEGRATED_AR
 412 ?>
 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>
 419
 420 <script language="JavaScript">
 421
 422 var mypcc = '1';
 423
 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;
 430  }
 431 }
 432
 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;
 436 }
 437
 438 </script>
 439
 440 </head>
 441
 442 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
 443 <center>
 444
 445 <form method='post' action='sl_eob_search.php' enctype='multipart/form-data'>
 446
 447 <table border='0' cellpadding='5' cellspacing='0'>
 448  <tr>
 449
 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";
 464   }
 465   echo "   </select>\n";
 466   echo "  </td>\n";
 467 }
 468 ?>
 469
 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>
 485
 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 } ?>
 496
 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>
 507
 508  </tr>
 509 </table>
 510
 511 <table border='0' cellpadding='5' cellspacing='0'>
 512
 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";
 556  }
 557 ?>
 558    </select>
 559   </td>
 560   <td>
 561    <input type='submit' name='form_search' value='<?php xl("Search","e"); ?>'>
 562   </td>
 563  </tr>
 564
 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>
 573
 574  <tr>
 575   <td height="1" colspan="10">
 576   </td>
 577  </tr>
 578
 579 </table>
 580
 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'], "");
 588
 589   $where = "";
 590
 591   // Handle X12 835 file upload.
 592   //
 593   if ($_FILES['form_erafile']['size']) {
 594     $tmp_name = $_FILES['form_erafile']['tmp_name'];
 595
 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");
 601     }
 602
 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";
 607
 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. ";
 614     }
 615     rename($tmp_name, $erafullname);
 616   } // End 835 upload
 617
 618   if ($INTEGRATED_AR) {
 619     if ($eracount) {
 620       // Note that parse_era() modified $eracount and $where.
 621       if (! $where) $where = '1 = 2';
 622     }
 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);
 635           }
 636           $where .= "( $tmp ) ";
 637         } else {
 638           $where .= "p.lname LIKE '%$form_name%'";
 639         }
 640       }
 641       if ($form_pid) {
 642         if ($where) $where .= " AND ";
 643         $where .= "f.pid = '$form_pid'";
 644       }
 645       if ($form_encounter) {
 646         if ($where) $where .= " AND ";
 647         $where .= "f.encounter = '$form_encounter'";
 648       }
 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'";
 653         }
 654         else {
 655           $where .= "f.date = '$form_date'";
 656         }
 657       }
 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";
 663         }
 664       }
 665     }
 666
 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";
 684
 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.
 688
 689     // echo "<!-- $query -->\n"; // debugging
 690
 691     $t_res = sqlStatement($query);
 692
 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. ";
 697     }
 698   } // end $INTEGRATED_AR
 699   else {
 700     if ($eracount) {
 701       // Note that parse_era() modified $eracount and $where.
 702       if (! $where) $where = '1 = 2';
 703     }
 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);
 718           }
 719           $where .= "( $tmp ) ";
 720         } else {
 721           $where .= "customer.name ILIKE '%$form_name%'";
 722         }
 723       }
 724       if ($form_pid && $form_encounter) {
 725         if ($where) $where .= " AND ";
 726         $where .= "ar.invnumber = '$form_pid.$form_encounter'";
 727       }
 728       else if ($form_pid) {
 729         if ($where) $where .= " AND ";
 730         $where .= "ar.invnumber LIKE '$form_pid.%'";
 731       }
 732       else if ($form_encounter) {
 733         if ($where) $where .= " AND ";
 734         $where .= "ar.invnumber like '%.$form_encounter'";
 735       }
 736
 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'";
 747         }
 748         else {
 749           // This catches old converted invoices where we have no encounters:
 750           $where .= "(ar.invnumber LIKE '%.$date1'";
 751           $tmp = "date = '$form_date'";
 752         }
 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'] . "'";
 757         }
 758         $where .= ")";
 759       }
 760
 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";
 766         }
 767       }
 768     }
 769
 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       // }
 789     }
 790     $query .= "ORDER BY lname, fname, ar.invnumber";
 791
 792     // echo "<!-- $query -->\n"; // debugging
 793
 794     $t_res = SLQuery($query);
 795     if ($sl_err) die($sl_err);
 796
 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. ";
 801     }
 802
 803   } // end not $INTEGRATED_AR
 804 ?>
 805
 806 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 807
 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>
 842
 843 <?php
 844   $orow = -1;
 845
 846   if ($INTEGRATED_AR) {
 847     while ($row = sqlFetchArray($t_res)) {
 848       $balance = sprintf("%.2f", $row['charges'] + $row['copays'] - $row['payments'] - $row['adjustments']);
 849
 850       if ($_POST['form_category'] != 'All' && $eracount == 0 && $balance == 0) continue;
 851
 852       // $duncount was originally supposed to be the number of times that
 853       // the patient was sent a statement for this invoice.
 854       //
 855       $duncount = $row['stmt_count'];
 856
 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.
 860       //
 861       if (! $duncount) {
 862         for ($i = 1; $i <= 3 && arGetPayerID($row['pid'], $row['date'], $i); ++$i) ;
 863         $duncount = $row['last_level_closed'] + 1 - $i;
 864       }
 865
 866       $isdueany = ($balance > 0);
 867
 868       // An invoice is now due from the patient if money is owed and we are
 869       // not waiting for insurance to pay.
 870       //
 871       $isduept = ($duncount >= 0 && $isdueany) ? " checked" : "";
 872
 873       // Skip invoices not in the desired "Due..." category.
 874       //
 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;
 878
 879       $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
 880
 881       $svcdate = substr($row['date'], 0, 10);
 882       $last_stmt_date = empty($row['last_stmt_date']) ? '' : $row['last_stmt_date'];
 883
 884       // Determine if customer is in collections.
 885       //
 886       $billnote = ($row['genericname2'] == 'Billing') ? $row['genericval2'] : '';
 887       $in_collections = stristr($billnote, 'IN COLLECTIONS') !== false;
 888 ?>
 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
 929
 930   else { // not $INTEGRATED_AR
 931     for ($irow = 0; $irow < $num_invoices; ++$irow) {
 932       $row = SLGetRow($t_res, $irow);
 933
 934       // $duncount was originally supposed to be the number of times that
 935       // the patient was sent a statement for this invoice.
 936       //
 937       $duncount = substr_count(strtolower($row['intnotes']), "statement sent");
 938
 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.
 942       //
 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;
 950         }
 951       }
 952
 953 //    $isdue = ($row['duedate'] <= $today && $row['amount'] > $row['paid']) ? " checked" : "";
 954
 955       $isdueany = sprintf("%.2f",$row['amount']) > sprintf("%.2f",$row['paid']);
 956
 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.
 960       //
 961       $isduept = ($duncount >= 0 && $isdueany) ? " checked" : "";
 962
 963       // Skip invoices not in the desired "Due..." category.
 964       //
 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;
 968
 969       $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
 970
 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.
 975       //
 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);
 984       }
 985       else if ($encounter) {
 986         $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
 987           "encounter = $encounter");
 988         $svcdate = substr($tmp['date'], 0, 10);
 989       }
 990
 991       // Get billing note to determine if customer is in collections.
 992       //
 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;
1000 ?>
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>
1038 <?
1039     } // end for
1040   } // end not $INTEGRATED_AR
1041 } // end search/print logic
1042
1043 if (!$INTEGRATED_AR) SLClose();
1044 ?>
1045
1046 </table>
1047
1048 <p>
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>
1060
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;
1070  }
1071 <?php
1072 if ($alertmsg) {
1073   echo "alert('" . htmlentities($alertmsg) . "');\n";
1074 }
1075
1076 ?>
1077 </script>
1078 </body>
1079 </html>