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