PageRenderTime 804ms CodeModel.GetById 12ms app.highlight 36ms RepoModel.GetById 1ms app.codeStats 743ms

/interface/billing/sl_eob_search.php

https://bitbucket.org/DenizYldrm/openemr
PHP | 1081 lines | 824 code | 112 blank | 145 comment | 219 complexity | 6138b30abfd0cc7c2720710d946a1b1d MD5 | raw file
   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
  15require_once("../globals.php");
  16require_once("$srcdir/patient.inc");
  17require_once("$srcdir/sql-ledger.inc");
  18require_once("$srcdir/invoice_summary.inc.php");
  19require_once($GLOBALS['OE_SITE_DIR'] . "/statement.inc.php");
  20require_once("$srcdir/parse_era.inc.php");
  21require_once("$srcdir/sl_eob.inc.php");
  22require_once("$srcdir/formatting.inc.php");
  23require_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//
  36function 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
  55function bucks($amount) {
  56  if ($amount) echo oeFormatMoney($amount);
  57}
  58
  59// Upload a file to the client's browser
  60//
  61function 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}
  77function 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
 129if ($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
 248else {
 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
 422var mypcc = '1';
 423
 424function 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
 433function 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
 451if ($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
 582if ($_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
1045if (!$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
1074if ($alertmsg) {
1075  echo "alert('" . htmlentities($alertmsg) . "');\n";
1076}
1077
1078?>
1079</script>
1080</body>
1081</html>