interface/billing/sl_eob_search.php

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