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="<?php 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     // Notes that as of release 4.1.1 the copays are stored
 668     // in the ar_activity table marked with a PCP in the account_code column.
 669     $query = "SELECT f.id, f.pid, f.encounter, f.date, " .
 670       "f.last_level_billed, f.last_level_closed, f.last_stmt_date, f.stmt_count, " .
 671       "p.fname, p.mname, p.lname, p.pubpid, p.genericname2, p.genericval2, " .
 672       "( SELECT SUM(b.fee) FROM billing AS b WHERE " .
 673       "b.pid = f.pid AND b.encounter = f.encounter AND " .
 674       "b.activity = 1 AND b.code_type != 'COPAY' ) AS charges, " .
 675       "( SELECT SUM(a.pay_amount) FROM ar_activity AS a WHERE " .
 676       "a.pid = f.pid AND a.encounter = f.encounter AND a.payer_type = 0 AND a.account_code = 'PCP')*-1 AS copays, " .
 677       "( SELECT SUM(a.pay_amount) FROM ar_activity AS a WHERE " .
 678       "a.pid = f.pid AND a.encounter = f.encounter AND a.account_code != 'PCP') AS payments, " .
 679       "( SELECT SUM(a.adj_amount) FROM ar_activity AS a WHERE " .
 680       "a.pid = f.pid AND a.encounter = f.encounter ) AS adjustments " .
 681       "FROM form_encounter AS f " .
 682       "JOIN patient_data AS p ON p.pid = f.pid " .
 683       "WHERE $where " .
 684       "ORDER BY p.lname, p.fname, p.mname, f.pid, f.encounter";
 685
 686     // Note that unlike the SQL-Ledger case, this query does not weed
 687     // out encounters that are paid up.  Also the use of sub-selects
 688     // will require MySQL 4.1 or greater.
 689
 690     // echo "<!-- $query -->\n"; // debugging
 691
 692     $t_res = sqlStatement($query);
 693
 694     $num_invoices = mysql_num_rows($t_res);
 695     if ($eracount && $num_invoices != $eracount) {
 696       $alertmsg .= "Of $eracount remittances, there are $num_invoices " .
 697         "matching encounters in OpenEMR. ";
 698     }
 699   } // end $INTEGRATED_AR
 700   else {
 701     if ($eracount) {
 702       // Note that parse_era() modified $eracount and $where.
 703       if (! $where) $where = '1 = 2';
 704     }
 705     else {
 706       if ($form_name) {
 707         if ($where) $where .= " AND ";
 708         // Allow the last name to be followed by a comma and some part of a first name.
 709         if (preg_match('/^(.*\S)\s*,\s*(.*)/', $form_name, $matches)) {
 710           $where .= "customer.name ILIKE '" . $matches[2] . '% ' . $matches[1] . "%'";
 711         // Allow a filter like "A-C" on the first character of the last name.
 712         } else if (preg_match('/^(\S)\s*-\s*(\S)$/', $form_name, $matches)) {
 713           $tmp = '1 = 2';
 714           while (ord($matches[1]) <= ord($matches[2])) {
 715             // $tmp .= " OR customer.name ILIKE '% " . $matches[1] . "%'";
 716             // Fixing the above which was also matching on middle names:
 717             $tmp .= " OR customer.name ~* ' " . $matches[1] . "[A-Z]*$'";
 718             $matches[1] = chr(ord($matches[1]) + 1);
 719           }
 720           $where .= "( $tmp ) ";
 721         } else {
 722           $where .= "customer.name ILIKE '%$form_name%'";
 723         }
 724       }
 725       if ($form_pid && $form_encounter) {
 726         if ($where) $where .= " AND ";
 727         $where .= "ar.invnumber = '$form_pid.$form_encounter'";
 728       }
 729       else if ($form_pid) {
 730         if ($where) $where .= " AND ";
 731         $where .= "ar.invnumber LIKE '$form_pid.%'";
 732       }
 733       else if ($form_encounter) {
 734         if ($where) $where .= " AND ";
 735         $where .= "ar.invnumber like '%.$form_encounter'";
 736       }
 737
 738       if ($form_date) {
 739         if ($where) $where .= " AND ";
 740         $date1 = substr($form_date, 0, 4) . substr($form_date, 5, 2) .
 741           substr($form_date, 8, 2);
 742         if ($form_to_date) {
 743           $date2 = substr($form_to_date, 0, 4) . substr($form_to_date, 5, 2) .
 744             substr($form_to_date, 8, 2);
 745           $where .= "((CAST (substring(ar.invnumber from position('.' in ar.invnumber) + 1 for 8) AS integer) " .
 746             "BETWEEN '$date1' AND '$date2')";
 747           $tmp = "date >= '$form_date' AND date <= '$form_to_date'";
 748         }
 749         else {
 750           // This catches old converted invoices where we have no encounters:
 751           $where .= "(ar.invnumber LIKE '%.$date1'";
 752           $tmp = "date = '$form_date'";
 753         }
 754         // Pick out the encounters from MySQL with the desired DOS:
 755         $rez = sqlStatement("SELECT pid, encounter FROM form_encounter WHERE $tmp");
 756         while ($row = sqlFetchArray($rez)) {
 757           $where .= " OR ar.invnumber = '" . $row['pid'] . "." . $row['encounter'] . "'";
 758         }
 759         $where .= ")";
 760       }
 761
 762       if (! $where) {
 763         if ($_POST['form_category'] == 'All') {
 764           die(xl("At least one search parameter is required if you select All."));
 765         } else {
 766           $where = "1 = 1";
 767         }
 768       }
 769     }
 770
 771     $query = "SELECT ar.id, ar.invnumber, ar.duedate, ar.amount, ar.paid, " .
 772       "ar.intnotes, ar.notes, ar.shipvia, customer.name, customer.id AS custid, ";
 773     if ($got_address_table) $query .=
 774       "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS lname, " .
 775       "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS fname, ";
 776     else $query .=
 777       "substring(trim(both from customer.name) from '% #\"%#\"' for '#') AS lname, " .
 778       "substring(trim(both from customer.name) from '#\"%#\" %' for '#') AS fname, ";
 779     $query .=
 780       "(SELECT SUM(invoice.sellprice * invoice.qty) FROM invoice WHERE " .
 781       "invoice.trans_id = ar.id AND invoice.sellprice > 0) AS charges, " .
 782       "(SELECT SUM(invoice.sellprice * invoice.qty) FROM invoice WHERE " .
 783       "invoice.trans_id = ar.id AND invoice.sellprice < 0) AS adjustments " .
 784       "FROM ar, customer WHERE ( $where ) AND customer.id = ar.customer_id ";
 785     if ($_POST['form_category'] != 'All' && !$eracount) {
 786       $query .= "AND ar.amount != ar.paid ";
 787       // if ($_POST['form_category'] == 'Due') {
 788       //   $query .= "AND ar.duedate <= CURRENT_DATE ";
 789       // }
 790     }
 791     $query .= "ORDER BY lname, fname, ar.invnumber";
 792
 793     // echo "<!-- $query -->\n"; // debugging
 794
 795     $t_res = SLQuery($query);
 796     if ($sl_err) die($sl_err);
 797
 798     $num_invoices = SLRowCount($t_res);
 799     if ($eracount && $num_invoices != $eracount) {
 800       $alertmsg .= "Of $eracount remittances, there are $num_invoices " .
 801         "matching claims in OpenEMR. ";
 802     }
 803
 804   } // end not $INTEGRATED_AR
 805 ?>
 806
 807 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 808
 809  <tr bgcolor="#dddddd">
 810   <td class="dehead">
 811    &nbsp;<?php xl('Patient','e'); ?>
 812   </td>
 813   <td class="dehead">
 814    &nbsp;<?php xl('Invoice','e'); ?>
 815   </td>
 816   <td class="dehead">
 817    &nbsp;<?php xl('Svc Date','e'); ?>
 818   </td>
 819   <td class="dehead">
 820    &nbsp;<?php xl($INTEGRATED_AR ? 'Last Stmt' : 'Due Date','e'); ?>
 821   </td>
 822   <td class="dehead" align="right">
 823    <?php xl('Charge','e'); ?>&nbsp;
 824   </td>
 825   <td class="dehead" align="right">
 826    <?php xl('Adjust','e'); ?>&nbsp;
 827   </td>
 828   <td class="dehead" align="right">
 829    <?php xl('Paid','e'); ?>&nbsp;
 830   </td>
 831   <td class="dehead" align="right">
 832    <?php xl('Balance','e'); ?>&nbsp;
 833   </td>
 834   <td class="dehead" align="center">
 835    <?php xl('Prv','e'); ?>
 836   </td>
 837 <?php if (!$eracount) { ?>
 838   <td class="dehead" align="left">
 839    <?php xl('Sel','e'); ?>
 840   </td>
 841 <?php } ?>
 842  </tr>
 843
 844 <?php
 845   $orow = -1;
 846
 847   if ($INTEGRATED_AR) {
 848     while ($row = sqlFetchArray($t_res)) {
 849       $balance = sprintf("%.2f", $row['charges'] + $row['copays'] - $row['payments'] - $row['adjustments']);
 850
 851       if ($_POST['form_category'] != 'All' && $eracount == 0 && $balance == 0) continue;
 852
 853       // $duncount was originally supposed to be the number of times that
 854       // the patient was sent a statement for this invoice.
 855       //
 856       $duncount = $row['stmt_count'];
 857
 858       // But if we have not yet billed the patient, then compute $duncount as a
 859       // negative count of the number of insurance plans for which we have not
 860       // yet closed out insurance.
 861       //
 862       if (! $duncount) {
 863         for ($i = 1; $i <= 3 && arGetPayerID($row['pid'], $row['date'], $i); ++$i) ;
 864         $duncount = $row['last_level_closed'] + 1 - $i;
 865       }
 866
 867       $isdueany = ($balance > 0);
 868
 869       // An invoice is now due from the patient if money is owed and we are
 870       // not waiting for insurance to pay.
 871       //
 872       $isduept = ($duncount >= 0 && $isdueany) ? " checked" : "";
 873
 874       // Skip invoices not in the desired "Due..." category.
 875       //
 876       if (substr($_POST['form_category'], 0, 3) == 'Due' && !$isdueany) continue;
 877       if ($_POST['form_category'] == 'Due Ins' && ($duncount >= 0 || !$isdueany)) continue;
 878       if ($_POST['form_category'] == 'Due Pt'  && ($duncount <  0 || !$isdueany)) continue;
 879
 880       $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
 881
 882       $svcdate = substr($row['date'], 0, 10);
 883       $last_stmt_date = empty($row['last_stmt_date']) ? '' : $row['last_stmt_date'];
 884
 885       // Determine if customer is in collections.
 886       //
 887       $billnote = ($row['genericname2'] == 'Billing') ? $row['genericval2'] : '';
 888       $in_collections = stristr($billnote, 'IN COLLECTIONS') !== false;
 889 ?>
 890  <tr bgcolor='<?php echo $bgcolor ?>'>
 891   <td class="detail">
 892    &nbsp;<a href="" onclick="return npopup(<?php echo $row['pid'] ?>)"
 893    ><?php echo $row['lname'] . ', ' . $row['fname']; ?></a>
 894   </td>
 895   <td class="detail">
 896    &nbsp;<a href="sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
 897     target="_blank"><?php echo $row['pid'] . '.' . $row['encounter']; ?></a>
 898   </td>
 899   <td class="detail">
 900    &nbsp;<?php echo oeFormatShortDate($svcdate) ?>
 901   </td>
 902   <td class="detail">
 903    &nbsp;<?php echo oeFormatShortDate($last_stmt_date) ?>
 904   </td>
 905   <td class="detail" align="right">
 906    <?php bucks($row['charges']) ?>&nbsp;
 907   </td>
 908   <td class="detail" align="right">
 909    <?php bucks($row['adjustments']) ?>&nbsp;
 910   </td>
 911   <td class="detail" align="right">
 912    <?php bucks($row['payments'] - $row['copays']); ?>&nbsp;
 913   </td>
 914   <td class="detail" align="right">
 915    <?php bucks($balance); ?>&nbsp;
 916   </td>
 917   <td class="detail" align="center">
 918    <?php echo $duncount ? $duncount : "&nbsp;" ?>
 919   </td>
 920 <?php if (!$eracount) { ?>
 921   <td class="detail" align="left">
 922    <input type='checkbox' name='form_cb[<?php echo($row['id']) ?>]'<?php echo $isduept ?> />
 923    <?php if ($in_collections) echo "<b><font color='red'>IC</font></b>"; ?>
 924   </td>
 925 <?php } ?>
 926  </tr>
 927 <?php
 928     } // end while
 929   } // end $INTEGRATED_AR
 930
 931   else { // not $INTEGRATED_AR
 932     for ($irow = 0; $irow < $num_invoices; ++$irow) {
 933       $row = SLGetRow($t_res, $irow);
 934
 935       // $duncount was originally supposed to be the number of times that
 936       // the patient was sent a statement for this invoice.
 937       //
 938       $duncount = substr_count(strtolower($row['intnotes']), "statement sent");
 939
 940       // But if we have not yet billed the patient, then compute $duncount as a
 941       // negative count of the number of insurance plans for which we have not
 942       // yet closed out insurance.
 943       //
 944       if (! $duncount) {
 945         $insgot = strtolower($row['notes']);
 946         $inseobs = strtolower($row['shipvia']);
 947         foreach (array('ins1', 'ins2', 'ins3') as $value) {
 948           if (strpos($insgot, $value) !== false &&
 949               strpos($inseobs, $value) === false)
 950             --$duncount;
 951         }
 952       }
 953
 954 //    $isdue = ($row['duedate'] <= $today && $row['amount'] > $row['paid']) ? " checked" : "";
 955
 956       $isdueany = sprintf("%.2f",$row['amount']) > sprintf("%.2f",$row['paid']);
 957
 958       // An invoice is now due from the patient if money is owed and we are
 959       // not waiting for insurance to pay.  We no longer look at the due date
 960       // for this.
 961       //
 962       $isduept = ($duncount >= 0 && $isdueany) ? " checked" : "";
 963
 964       // Skip invoices not in the desired "Due..." category.
 965       //
 966       if (substr($_POST['form_category'], 0, 3) == 'Due' && !$isdueany) continue;
 967       if ($_POST['form_category'] == 'Due Ins' && ($duncount >= 0 || !$isdueany)) continue;
 968       if ($_POST['form_category'] == 'Due Pt'  && ($duncount <  0 || !$isdueany)) continue;
 969
 970       $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
 971
 972       // Determine the date of service.  If this was a search parameter
 973       // then we already know it.  Or an 8-digit encounter number is
 974       // presumed to be a date of service imported during conversion.
 975       // Otherwise look it up in the form_encounter table.
 976       //
 977       $svcdate = "";
 978       list($pid, $encounter) = explode(".", $row['invnumber']);
 979       // if ($form_date) {
 980       //   $svcdate = $form_date;
 981       // } else
 982       if (strlen($encounter) == 8) {
 983         $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
 984           "-" . substr($encounter, 6, 2);
 985       }
 986       else if ($encounter) {
 987         $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
 988           "encounter = $encounter");
 989         $svcdate = substr($tmp['date'], 0, 10);
 990       }
 991
 992       // Get billing note to determine if customer is in collections.
 993       //
 994       $pdrow = sqlQuery("SELECT pd.genericname2, pd.genericval2 FROM " .
 995         "integration_mapping AS im, patient_data AS pd WHERE " .
 996         "im.foreign_id = " . $row['custid'] . " AND " .
 997         "im.foreign_table = 'customer' AND " .
 998         "pd.id = im.local_id");
 999       $row['billnote'] = ($pdrow['genericname2'] == 'Billing') ? $pdrow['genericval2'] : '';
1000       $in_collections = stristr($row['billnote'], 'IN COLLECTIONS') !== false;
1001 ?>
1002  <tr bgcolor='<?php echo $bgcolor ?>'>
1003   <td class="detail">
1004    &nbsp;<a href="" onclick="return npopup(<?php echo $pid ?>)"
1005    ><?php echo $row['lname'] . ', ' . $row['fname']; ?></a>
1006   </td>
1007   <td class="detail">
1008    &nbsp;<a href="sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
1009     target="_blank"><?php echo $row['invnumber'] ?></a>
1010   </td>
1011   <td class="detail">
1012    &nbsp;<?php echo oeFormatShortDate($svcdate) ?>
1013   </td>
1014   <td class="detail">
1015    &nbsp;<?php echo oeFormatShortDate($row['duedate']) ?>
1016   </td>
1017   <td class="detail" align="right">
1018    <?php bucks($row['charges']) ?>&nbsp;
1019   </td>
1020   <td class="detail" align="right">
1021    <?php bucks($row['adjustments']) ?>&nbsp;
1022   </td>
1023   <td class="detail" align="right">
1024    <?php bucks($row['paid']) ?>&nbsp;
1025   </td>
1026   <td class="detail" align="right">
1027    <?php bucks($row['charges'] + $row['adjustments'] - $row['paid']) ?>&nbsp;
1028   </td>
1029   <td class="detail" align="center">
1030    <?php echo $duncount ? $duncount : "&nbsp;" ?>
1031   </td>
1032 <?php if (!$eracount) { ?>
1033   <td class="detail" align="left">
1034    <input type='checkbox' name='form_cb[<?php echo($row['id']) ?>]'<?php echo $isduept ?> />
1035    <?php if ($in_collections) echo "<b><font color='red'>IC</font></b>"; ?>
1036   </td>
1037 <?php } ?>
1038  </tr>
1039 <?php
1040
1041     } // end for
1042   } // end not $INTEGRATED_AR
1043 } // end search/print logic
1044
1045 if (!$INTEGRATED_AR) SLClose();
1046 ?>
1047
1048 </table>
1049
1050 <p>
1051 <?php if ($eracount) { ?>
1052 <input type='button' value='<?php xl('Process ERA File','e')?>' onclick='processERA()' /> &nbsp;
1053 <?php } else { ?>
1054 <input type='button' value='<?php xl('Select All','e')?>' onclick='checkAll(true)' /> &nbsp;
1055 <input type='button' value='<?php xl('Clear All','e')?>' onclick='checkAll(false)' /> &nbsp;
1056 <input type='submit' name='form_print' value='<?php xl('Print Selected Statements','e'); ?>' /> &nbsp;
1057 <input type='submit' name='form_download' value='<?php xl('Download Selected Statements','e'); ?>' /> &nbsp;
1058 <input type='submit' name='form_pdf' value='<?php xl('PDF Download Selected Statements','e'); ?>' /> &nbsp;
1059 <?php } ?>
1060 <input type='checkbox' name='form_without' value='1' /> <?php xl('Without Update','e'); ?>
1061 </p>
1062
1063 </form>
1064 </center>
1065 <script language="JavaScript">
1066  function processERA() {
1067   var f = document.forms[0];
1068   var debug = f.form_without.checked ? '1' : '0';
1069   var paydate = f.form_paydate.value;
1070   window.open('sl_eob_process.php?eraname=<?php echo $eraname ?>&debug=' + debug + '&paydate=' + paydate + '&original=original', '_blank');
1071   return false;
1072  }
1073 <?php
1074 if ($alertmsg) {
1075   echo "alert('" . htmlentities($alertmsg) . "');\n";
1076 }
1077
1078 ?>
1079 </script>
1080 </body>
1081 </html>