interface/billing/sl_eob_invoice.php

   1 <?
   2   // This is the second of two pages to support posting of EOBs.
   3   // The first is sl_eob_search.php.
   4
   5   include_once("../globals.php");
   6   include_once("../../library/patient.inc");
   7   include_once("../../library/forms.inc");
   8   include_once("../../library/sql-ledger.inc");
   9
  10   $debug = 0; // set to 1 for debugging mode
  11
  12   $info_msg = "";
  13
  14   // Format money for display.
  15   //
  16   function bucks($amount) {
  17     if ($amount)
  18       printf("%.2f", $amount);
  19   }
  20
  21   // Insert a row into the acc_trans table.
  22   //
  23   function addTransaction($invid, $chartid, $amount, $date, $source, $memo, $insplan) {
  24     global $sl_err, $debug;
  25     $date = fixDate($date);
  26     $query = "INSERT INTO acc_trans ( " .
  27       "trans_id, "     .
  28       "chart_id, "     .
  29       "amount, "       .
  30       "transdate, "    .
  31       "source, "       .
  32       "project_id, "   .
  33       "memo "          .
  34       ") VALUES ( "    .
  35       "$invid, "       . // trans_id
  36       "$chartid, "     . // chart_id
  37       "$amount, "      . // amount
  38       "'$date', "      . // transdate
  39       "'$source', "    . // source
  40       "$insplan, "     . // project_id
  41       "'$memo' "       . // memo
  42       ")";
  43     if ($debug) {
  44       echo $query . "<br>\n";
  45     } else {
  46       SLQuery($query);
  47       if ($sl_err) die($sl_err);
  48     }
  49   }
  50
  51   // Insert a row into the invoice table.
  52   //
  53   function addLineItem($invid, $serialnumber, $amount, $adjdate, $insplan) {
  54     global $sl_err, $services_id, $debug;
  55     $adjdate = fixDate($adjdate);
  56     $description = "Adjustment $adjdate";
  57     $query = "INSERT INTO invoice ( " .
  58       "trans_id, "          .
  59       "parts_id, "          .
  60       "description, "       .
  61       "qty, "               .
  62       "allocated, "         .
  63       "sellprice, "         .
  64       "fxsellprice, "       .
  65       "discount, "          .
  66       "unit, "              .
  67       "project_id, "        .
  68       "serialnumber "       .
  69       ") VALUES ( "         .
  70       "$invid, "            . // trans_id
  71       "$services_id, "      . // parts_id
  72       "'$description', "    . // description
  73       "1, "                 . // qty
  74       "0, "                 . // allocated
  75       "$amount, "           . // sellprice
  76       "$amount, "           . // fxsellprice
  77       "0, "                 . // discount
  78       "'', "                . // unit
  79       "$insplan, "          . // project_id
  80       "'$serialnumber'"     . // serialnumber
  81       ")";
  82     if ($debug) {
  83       echo $query . "<br>\n";
  84     } else {
  85       SLQuery($query);
  86       if ($sl_err) die($sl_err);
  87     }
  88   }
  89
  90   // Update totals and payment date in the invoice header.  Dollar amounts are
  91   // stored as double precision floats so we have to be careful about rounding.
  92   //
  93   function updateAR($invid, $amount, $paid = 0, $paydate = "") {
  94     global $sl_err, $debug;
  95     $paydate = fixDate($paydate);
  96     $query = "UPDATE ar SET amount = round(CAST (amount AS numeric) + $amount, 2), " .
  97       "netamount = round(CAST (netamount AS numeric) + $amount, 2)";
  98     if ($paid) $query .= ", paid = round(CAST (paid AS numeric) + $paid, 2), datepaid = '$paydate'";
  99     $query .= " WHERE id = $invid";
 100     if ($debug) {
 101       echo $query . "<br>\n";
 102     } else {
 103       SLQuery($query);
 104       if ($sl_err) die($sl_err);
 105     }
 106   }
 107
 108   // Do whatever is necessary to make this invoice re-billable.
 109   //
 110   function setupSecondary($invid) {
 111     global $sl_err, $debug, $info_msg, $GLOBALS;
 112
 113     // Get some needed items from the SQL-Ledger invoice.
 114     $arres = SLQuery("select invnumber, transdate, customer_id, employee_id " .
 115       "from ar where ar.id = $invid");
 116     if ($sl_err) die($sl_err);
 117     $arrow = SLGetRow($arres, 0);
 118     if (! $arrow) die("There is no match for invoice id = $trans_id.");
 119     $customer_id = $arrow['customer_id'];
 120     list($trash, $encounter) = explode(".", $arrow['invnumber']);
 121
 122     // Get the OpenEMR PID corresponding to the customer.
 123     $pdrow = sqlQuery("SELECT patient_data.pid " .
 124       "FROM integration_mapping, patient_data WHERE " .
 125       "integration_mapping.foreign_id = $customer_id AND " .
 126       "integration_mapping.foreign_table = 'customer' AND " .
 127       "patient_data.id = integration_mapping.local_id");
 128     $pid = $pdrow['pid'];
 129     if (! $pid) die("Cannot find patient from SQL-Ledger customer id = $customer_id.");
 130
 131     // Find out if the encounter exists.
 132     $ferow = sqlQuery("SELECT pid FROM form_encounter WHERE " .
 133       "encounter = $encounter");
 134     $encounter_pid = $ferow['pid'];
 135
 136     // If it exists, just update the billing items.
 137     if ($encounter_pid) {
 138       if ($encounter_pid != $pid)
 139         die("Expected form_encounter.pid to be $pid, but was $encounter_pid");
 140       $query = "UPDATE billing SET billed = 0, bill_process = 0, payer_id = -1, " .
 141         "bill_date = NULL, process_date = NULL, process_file = NULL " .
 142         "WHERE encounter = $encounter AND pid = $pid AND activity = 1";
 143       if ($debug) {
 144         echo $query . "<br>\n";
 145       } else {
 146         sqlQuery($query);
 147       }
 148       $info_msg = "Encounter $encounter is ready for secondary billing.";
 149       return;
 150     }
 151
 152     // It does not exist then it better be a date.
 153     if (! preg_match("/^20\d\d\d\d\d\d$/", $encounter))
 154       die("Internal error: encounter '$encounter' should exist but does not.");
 155
 156     $employee_id = $arrow['employee_id'];
 157
 158     // Get the OpenEMR provider info corresponding to the SQL-Ledger salesman.
 159     $drrow = sqlQuery("SELECT users.id, users.username, users.facility " .
 160       "FROM integration_mapping, users WHERE " .
 161       "integration_mapping.foreign_id = $employee_id AND " .
 162       "integration_mapping.foreign_table = 'salesman' AND " .
 163       "users.id = integration_mapping.local_id");
 164     $provider_id = $drrow['id'];
 165     if (! $provider_id) die("Cannot find provider from SQL-Ledger employee = $employee_id.");
 166
 167     $date_of_service = $arrow['transdate'];
 168     if (! $date_of_service) die("Invoice has no date!");
 169
 170     // Generate a new encounter number.
 171     $conn = $GLOBALS['adodb']['db'];
 172     $new_encounter = $conn->GenID("sequences");
 173
 174     // Create the "new encounter".
 175     $encounter_id = 0;
 176     $query = "INSERT INTO form_encounter ( " .
 177       "date, reason, facility, pid, encounter, onset_date " .
 178       ") VALUES ( " .
 179       "'$date_of_service', " .
 180       "'Imported from Accounting', " .
 181       "'" . addslashes($drrow['facility']) . "', " .
 182       "$pid, " .
 183       "$new_encounter, " .
 184       "'$date_of_service' " .
 185       ")";
 186     if ($debug) {
 187       echo $query . "<br>\n";
 188       echo "Call to addForm() goes here.<br>\n";
 189     } else {
 190       $encounter_id = idSqlStatement($query);
 191       if (! $encounter_id) die("Insert failed: $query");
 192       addForm($new_encounter, "New Patient Encounter", $encounter_id,
 193         "newpatient", $pid, 1, $date_of_service);
 194       $info_msg = "Encounter $new_encounter has been created. ";
 195     }
 196
 197     // For each invoice line item with a billing code we will insert
 198     // a billing row with payer_id set to -1.  Order the line items
 199     // chronologically so that each procedure code will be followed by
 200     // its associated icd9 code.
 201
 202     $inres = SLQuery("SELECT * FROM invoice WHERE trans_id = $invid " .
 203       "ORDER BY id");
 204     if ($sl_err) die($sl_err);
 205
 206     // When nonzero, this will be the ID of a billing row that needs to
 207     // have its justify field set.
 208     $proc_ins_id = 0;
 209
 210     for ($irow = 0; $irow < SLRowCount($inres); ++$irow) {
 211       $row = SLGetRow($inres, $irow);
 212       $amount   = $row['sellprice'];
 213       $ins_id   = $row['project_id'];
 214
 215       // Extract the billing code.
 216       $code = "Unknown";
 217       if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['serialnumber'], $matches)) {
 218         $code = strtoupper($matches[1]);
 219       }
 220       else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['description'], $matches)) {
 221         $code = strtoupper($matches[1]);
 222       }
 223
 224       list($code, $modifier) = explode("-", $code);
 225
 226       // Set the billing code type and description.
 227       $code_type = "";
 228       $code_text = "";
 229       if (preg_match("/CPT/", $row['serialnumber'])) {
 230         $code_type = "CPT4";
 231         $code_text = "Procedure $code";
 232       }
 233       else if (preg_match("/HCPCS/", $row['serialnumber'])) {
 234         $code_type = "HCPCS";
 235         $code_text = "Procedure $code";
 236       }
 237       else if (preg_match("/ICD/", $row['serialnumber'])) {
 238         $code_type = "ICD9";
 239         $code_text = "Diagnosis $code";
 240         if ($proc_ins_id) {
 241           $query = "UPDATE billing SET justify = '$code' WHERE id = $proc_ins_id";
 242           if ($debug) {
 243             echo $query . "<br>\n";
 244           } else {
 245             sqlQuery($query);
 246           }
 247           $proc_ins_id = 0;
 248         }
 249       }
 250
 251       // Skip adjustments.
 252       if (! $code_type) continue;
 253
 254       // Insert the billing item.  If this for a procedure code then save
 255       // the row ID so that we can update the "justify" field with the ICD9
 256       // code, which should come next in the loop.
 257       //
 258       $query = "INSERT INTO billing ( " .
 259         "date, code_type, code, pid, provider_id, user, groupname, authorized, " .
 260         "encounter, code_text, activity, payer_id, billed, bill_process, " .
 261         "modifier, units, fee, justify " .
 262         ") VALUES ( " .
 263         "NOW(), " .
 264         "'$code_type', " .
 265         "'$code', " .
 266         "$pid, " .
 267         "$provider_id, " .
 268         "'" . $_SESSION['authId'] . "', " .
 269         "'" . $_SESSION['authProvider'] . "', " .
 270         "1, " .
 271         "$new_encounter, " .
 272         "'$code_text', " .
 273         "1, " .
 274         "-1, " .
 275         "0, " .
 276         "0, " .
 277         "'$modifier', " .
 278         "0, " .
 279         "$amount, " .
 280         "'' " .
 281         ")";
 282       if ($debug) {
 283         echo $query . "<br>\n";
 284       } else {
 285         $proc_ins_id = idSqlStatement($query);
 286         if ($code_type != "CPT4" && $code_type != "HCPCS")
 287           $proc_ins_id = 0;
 288       }
 289     }
 290
 291     // Finally, change this invoice number to contain the new encounter number.
 292     //
 293     $new_invnumber = "$pid.$new_encounter";
 294     $query = "UPDATE ar SET invnumber = '$new_invnumber' WHERE id = $invid";
 295     if ($debug) {
 296       echo $query . "<br>\n";
 297     } else {
 298       SLQuery($query);
 299       if ($sl_err) die($sl_err);
 300       $info_msg .= "This invoice number has been changed to $new_invnumber.";
 301     }
 302   }
 303 ?>
 304 <html>
 305 <head>
 306 <link rel=stylesheet href="<?echo $css_header;?>" type="text/css">
 307 <title>EOB Posting - Invoice</title>
 308 <script language="JavaScript">
 309
 310 // Compute an adjustment that writes off the balance:
 311 function writeoff(code) {
 312  var f = document.forms[0];
 313  var tmp =
 314   f['form_line[' + code + '][bal]'].value -
 315   f['form_line[' + code + '][pay]'].value;
 316  f['form_line[' + code + '][adj]'].value = Number(tmp).toFixed(2);
 317  return false;
 318 }
 319
 320 // Onsubmit handler.  A good excuse to write some JavaScript.
 321 function validate(f) {
 322  for (var i = 0; i < f.elements.length; ++i) {
 323   var ename = f.elements[i].name;
 324   var pfxlen = ename.indexOf('[pay]');
 325   if (pfxlen < 0) continue;
 326   var pfx = ename.substring(0, pfxlen);
 327   var code = pfx.substring(pfx.indexOf('[')+1, pfxlen-1);
 328   if (f[pfx+'[pay]'].value || f[pfx+'[adj]'].value) {
 329    if (! f[pfx+'[src]'].value) {
 330     alert('Source is missing for code ' + code + '; this should be a check or EOB number');
 331     return false;
 332    }
 333    if (! f[pfx+'[date]'].value) {
 334     alert('Date is missing for code ' + code);
 335     return false;
 336    }
 337   }
 338   if (f[pfx+'[pay]'].value && isNaN(parseFloat(f[pfx+'[pay]'].value))) {
 339    alert('Payment value for code ' + code + ' is not a number');
 340    return false;
 341   }
 342   if (f[pfx+'[adj]'].value && isNaN(parseFloat(f[pfx+'[adj]'].value))) {
 343    alert('Adjustment value for code ' + code + ' is not a number');
 344    return false;
 345   }
 346   // TBD: validate the date format
 347  }
 348  return true;
 349 }
 350
 351 </script>
 352 </head>
 353 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
 354 <?
 355   $trans_id = $_GET['id'];
 356   if (! $trans_id) die("You cannot access this page directly.");
 357
 358   SLConnect();
 359
 360   $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
 361   if ($sl_err) die($sl_err);
 362   if (! $chart_id_cash) die("There is no COA entry for cash account '$sl_cash_acc'");
 363
 364   $chart_id_ar = SLQueryValue("select id from chart where accno = '$sl_ar_acc'");
 365   if ($sl_err) die($sl_err);
 366   if (! $chart_id_ar) die("There is no COA entry for AR account '$sl_ar_acc'");
 367
 368   $chart_id_income = SLQueryValue("select id from chart where accno = '$sl_income_acc'");
 369   if ($sl_err) die($sl_err);
 370   if (! $chart_id_income) die("There is no COA entry for income account '$sl_income_acc'");
 371
 372   $services_id = SLQueryValue("select id from parts where partnumber = '$sl_services_id'");
 373   if ($sl_err) die($sl_err);
 374   if (! $services_id) die("There is no parts entry for services ID '$sl_services_id'");
 375
 376   if ($_POST['form_save'] || $_POST['form_cancel']) {
 377     if ($_POST['form_save']) {
 378       if ($debug) {
 379         echo "<p><b>This module is in test mode. The database will not be changed.</b><p>\n";
 380       }
 381       $paytotal = 0;
 382       foreach ($_POST['form_line'] as $code => $cdata) {
 383         $thissrc  = trim($cdata['src']);
 384         $thisdate = trim($cdata['date']);
 385         $thispay  = trim($cdata['pay']);
 386         $thisadj  = trim($cdata['adj']);
 387         $thisins  = trim($cdata['ins']);
 388         if (! $thisins) $thisins = 0;
 389         if ($thispay) {
 390           // Post a payment: add to ar, subtract from cash.
 391           addTransaction($trans_id, $chart_id_ar, $thispay, $thisdate, $thissrc, $code, $thisins);
 392           addTransaction($trans_id, $chart_id_cash, 0 - $thispay, $thisdate, $thissrc, $code, $thisins);
 393           updateAR($trans_id, 0, $thispay, $thisdate);
 394           $paytotal += $thispay;
 395         }
 396         if ($thisadj) {
 397           // Post an adjustment: add negative invoice item, add to ar, subtract from income
 398           addLineItem($trans_id, $code, 0 - $thisadj, $thisdate, $thisins);
 399           addTransaction($trans_id, $chart_id_ar, $thisadj, $thisdate, "InvAdj $thissrc", $code, $thisins);
 400           addTransaction($trans_id, $chart_id_income, 0 - $thisadj, $thisdate, "InvAdj $thissrc", $code, $thisins);
 401           updateAR($trans_id, 0 - $thisadj);
 402         }
 403       }
 404       $form_duedate = fixDate($_POST['form_duedate']);
 405       $form_notes = trim($_POST['form_notes']);
 406       $query = "UPDATE ar SET duedate = '$form_duedate', notes = '$form_notes' " .
 407         "WHERE id = $trans_id";
 408       if ($debug) {
 409         echo $query . "<br>\n";
 410       } else {
 411         SLQuery($query);
 412         if ($sl_err) die($sl_err);
 413       }
 414       if ($_POST['form_secondary']) {
 415         setupSecondary($trans_id);
 416       }
 417       echo "<script language='JavaScript'>\n";
 418       echo " var tmp = opener.document.forms[0].form_amount.value - $paytotal;\n";
 419       echo " opener.document.forms[0].form_amount.value = Number(tmp).toFixed(2);\n";
 420     } else {
 421       echo "<script language='JavaScript'>\n";
 422     }
 423     if ($info_msg) echo " alert('$info_msg');\n";
 424     if (! $debug) echo " window.close();\n";
 425     echo "</script></body></html>\n";
 426     SLClose();
 427     exit();
 428   }
 429
 430   // Get invoice data into $arrow.
 431   $arres = SLQuery("select ar.*, customer.name, employee.name as doctor " .
 432     "from ar, customer, employee where ar.id = $trans_id and " .
 433     "customer.id = ar.customer_id and employee.id = ar.employee_id");
 434   if ($sl_err) die($sl_err);
 435   $arrow = SLGetRow($arres, 0);
 436   if (! $arrow) die("There is no match for invoice id = $trans_id.");
 437
 438   // Request all cash entries belonging to the invoice.
 439   $atres = SLQuery("select * from acc_trans where trans_id = $trans_id and chart_id = $chart_id_cash");
 440   if ($sl_err) die($sl_err);
 441
 442   // Deduct payments for each procedure code from the respective balance owed.
 443   $codes = array();
 444   for ($irow = 0; $irow < SLRowCount($atres); ++$irow) {
 445     $row = SLGetRow($atres, $irow);
 446     $code     = strtoupper($row['memo']);
 447     $ins_id   = $row['project_id'];
 448     if (! $code) $code = "Unknown";
 449     $amount   = $row['amount'];
 450     $codes[$code]['bal'] += $amount; // amount is negative for a payment
 451     if ($ins_id)
 452       $codes[$code]['ins'] = $ins_id;
 453     // echo "<!-- $code $chart_id $amount -->\n"; // debugging
 454   }
 455
 456   // Request all line items with money belonging to the invoice.
 457   $inres = SLQuery("select * from invoice where trans_id = $trans_id and sellprice != 0");
 458   if ($sl_err) die($sl_err);
 459
 460   // Add charges and adjustments for each procedure code into its total and balance.
 461   for ($irow = 0; $irow < SLRowCount($inres); ++$irow) {
 462     $row = SLGetRow($inres, $irow);
 463     $amount   = $row['sellprice'];
 464     $ins_id   = $row['project_id'];
 465
 466     $code = "Unknown";
 467     if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['serialnumber'], $matches)) {
 468       $code = strtoupper($matches[1]);
 469     }
 470     else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['description'], $matches)) {
 471       $code = strtoupper($matches[1]);
 472     }
 473
 474     $codes[$code]['chg'] += $amount;
 475     $codes[$code]['bal'] += $amount;
 476
 477     if ($ins_id)
 478       $codes[$code]['ins'] = $ins_id;
 479   }
 480 ?>
 481 <center>
 482
 483 <form method='post' action='sl_eob_invoice.php?id=<? echo $trans_id ?>'
 484  onsubmit='return validate(this)'>
 485
 486 <table border='0' cellpadding='3'>
 487  <tr>
 488   <td>
 489    Patient:
 490   </td>
 491   <td>
 492    <?echo $arrow['name'] ?>
 493   </td>
 494   <td colspan="2" rowspan="4">
 495    <textarea name="form_notes" cols="50" style="height:100%"><?echo $arrow['notes'] ?></textarea>
 496   </td>
 497  </tr>
 498  <tr>
 499   <td>
 500    Provider:
 501   </td>
 502   <td>
 503    <?echo $arrow['doctor'] ?>
 504   </td>
 505  </tr>
 506  <tr>
 507   <td>
 508    Invoice:
 509   </td>
 510   <td>
 511    <?echo $arrow['invnumber'] ?>
 512   </td>
 513  </tr>
 514  <tr>
 515   <td>
 516    Bill Date:
 517   </td>
 518   <td>
 519    <?echo $arrow['transdate'] ?>
 520   </td>
 521  </tr>
 522  <tr>
 523   <td>
 524    Due Date:
 525   </td>
 526   <td>
 527    <input type='text' name='form_duedate' size='10' value='<?echo $arrow['duedate'] ?>'
 528     title='Due date mm/dd/yyyy or yyyy-mm-dd'>
 529   </td>
 530   <td colspan="2">
 531    <input type="checkbox" name="form_secondary" value="1"> Needs secondary billing
 532    &nbsp;&nbsp;
 533    <input type='submit' name='form_save' value='Save'>
 534    &nbsp;
 535    <input type='button' value='Cancel' onclick='window.close()'>
 536   </td>
 537  </tr>
 538  <tr>
 539   <td height="1">
 540   </td>
 541  </tr>
 542 </table>
 543
 544 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 545
 546  <tr bgcolor="#dddddd">
 547   <td class="dehead">
 548    Code
 549   </td>
 550   <td class="dehead" align="right">
 551    Charge
 552   </td>
 553   <td class="dehead" align="right">
 554    Balance
 555   </td>
 556   <td class="dehead">
 557    Source
 558   </td>
 559   <td class="dehead">
 560    Date
 561   </td>
 562   <td class="dehead">
 563    Pay
 564   </td>
 565   <td class="dehead">
 566    Adjust
 567   </td>
 568  </tr>
 569 <?
 570   foreach ($codes as $code => $cdata) {
 571 ?>
 572  <tr>
 573   <td class="detail">
 574    <? echo $code ?>
 575   </td>
 576   <td class="detail" align="right">
 577    <? bucks($cdata['chg']) ?>
 578   </td>
 579   <td class="detail" align="right">
 580    <input type="hidden" name="form_line[<? echo $code ?>][bal]" value="<? bucks($cdata['bal']) ?>">
 581    <input type="hidden" name="form_line[<? echo $code ?>][ins]" value="<? echo $cdata['ins'] ?>">
 582    <? bucks($cdata['bal']) ?>
 583   </td>
 584   <td class="detail">
 585    <input type="text" name="form_line[<? echo $code ?>][src]" size="10">
 586   </td>
 587   <td class="detail">
 588    <input type="text" name="form_line[<? echo $code ?>][date]" size="10">
 589   </td>
 590   <td class="detail">
 591    <input type="text" name="form_line[<? echo $code ?>][pay]" size="10">
 592   </td>
 593   <td class="detail">
 594    <input type="text" name="form_line[<? echo $code ?>][adj]" size="10">
 595    &nbsp; <a href="" onclick="return writeoff('<? echo $code ?>')">W</a>
 596   </td>
 597  </tr>
 598 <?
 599   }
 600   SLClose();
 601 ?>
 602
 603 </table>
 604 </form>
 605 </center>
 606 <script language="JavaScript">
 607  var f1 = opener.document.forms[0];
 608  var f2 = document.forms[0];
 609 <?
 610   foreach ($codes as $code => $cdata) {
 611     echo " f2['form_line[$code][src]'].value  = f1.form_source.value;\n";
 612     echo " f2['form_line[$code][date]'].value = f1.form_paydate.value;\n";
 613   }
 614 ?>
 615 </script>
 616 </body>
 617 </html>