interface/billing/sl_eob_invoice.php

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