library/sl_eob.inc.php

   1 <?php
   2   // Copyright (C) 2005-2006 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   include_once("sql-ledger.inc");
  10   include_once("patient.inc");
  11   include_once("invoice_summary.inc.php");
  12
  13   $chart_id_cash   = 0;
  14   $chart_id_ar     = 0;
  15   $chart_id_income = 0;
  16   $services_id     = 0;
  17
  18   function slInitialize() {
  19     global $chart_id_cash, $chart_id_ar, $chart_id_income, $services_id;
  20     global $sl_cash_acc, $sl_ar_acc, $sl_income_acc, $sl_services_id;
  21     SLConnect();
  22
  23     $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
  24     if ($sl_err) die($sl_err);
  25     if (! $chart_id_cash) die(xl("There is no COA entry for cash account ") . "'$sl_cash_acc'");
  26
  27     $chart_id_ar = SLQueryValue("select id from chart where accno = '$sl_ar_acc'");
  28     if ($sl_err) die($sl_err);
  29     if (! $chart_id_ar) die(xl("There is no COA entry for AR account ") . "'$sl_ar_acc'");
  30
  31     $chart_id_income = SLQueryValue("select id from chart where accno = '$sl_income_acc'");
  32     if ($sl_err) die($sl_err);
  33     if (! $chart_id_income) die(xl("There is no COA entry for income account ") . "'$sl_income_acc'");
  34
  35     $services_id = SLQueryValue("select id from parts where partnumber = '$sl_services_id'");
  36     if ($sl_err) die($sl_err);
  37     if (! $services_id) die(xl("There is no parts entry for services ID ") . "'$sl_services_id'");
  38   }
  39
  40   function slTerminate() {
  41     SLClose();
  42   }
  43
  44   // Try to figure out our invoice number (pid.encounter) from the
  45   // claim ID and other stuff in the ERA.  This should be straightforward
  46   // except that some payers mangle the claim ID that we give them.
  47   //
  48   function slInvoiceNumber(&$out) {
  49     $invnumber = $out['our_claim_id'];
  50     $atmp = preg_split('/[ -]/', $invnumber);
  51     $acount = count($atmp);
  52
  53     $pid = 0;
  54     $encounter = 0;
  55     if ($acount == 2) {
  56       $pid = $atmp[0];
  57       $encounter = $atmp[1];
  58     }
  59     else if ($acount == 3) {
  60       $pid = $atmp[0];
  61       $brow = sqlQuery("SELECT encounter FROM billing WHERE " .
  62         "pid = '$pid' AND id = '" . $atmp[1] . "' AND activity = 1");
  63       $encounter = $brow['encounter'];
  64     }
  65     else if ($acount == 1) {
  66       $pres = sqlStatement("SELECT pid FROM patient_data WHERE " .
  67         "lname LIKE '" . addslashes($out['patient_lname']) . "' AND " .
  68         "fname LIKE '" . addslashes($out['patient_fname']) . "' " .
  69         "ORDER BY pid DESC");
  70       while ($prow = sqlFetchArray($pres)) {
  71         if (strpos($invnumber, $prow['pid']) === 0) {
  72           $pid = $prow['pid'];
  73           $encounter = substr($invnumber, strlen($pid));
  74           break;
  75         }
  76       }
  77     }
  78
  79     if ($pid && $encounter) $invnumber = "$pid.$encounter";
  80     return array($pid, $encounter, $invnumber);
  81   }
  82
  83   // Insert a row into the acc_trans table.
  84   //
  85   function slAddTransaction($invid, $chartid, $amount, $date, $source, $memo, $insplan, $debug) {
  86     global $sl_err;
  87     $date = fixDate($date);
  88     $query = "INSERT INTO acc_trans ( " .
  89       "trans_id, "     .
  90       "chart_id, "     .
  91       "amount, "       .
  92       "transdate, "    .
  93       "source, "       .
  94       "project_id, "   .
  95       "memo "          .
  96       ") VALUES ( "    .
  97       "$invid, "       . // trans_id
  98       "$chartid, "     . // chart_id
  99       "$amount, "      . // amount
 100       "'$date', "      . // transdate
 101       "'$source', "    . // source
 102       "$insplan, "     . // project_id
 103       "'$memo' "       . // memo
 104       ")";
 105     if ($debug) {
 106       echo $query . "<br>\n";
 107     } else {
 108       SLQuery($query);
 109       if ($sl_err) die($sl_err);
 110     }
 111   }
 112
 113   // Insert a row into the invoice table.
 114   //
 115   function slAddLineItem($invid, $serialnumber, $amount, $insplan, $description, $debug) {
 116     global $sl_err, $services_id;
 117     $query = "INSERT INTO invoice ( " .
 118       "trans_id, "          .
 119       "parts_id, "          .
 120       "description, "       .
 121       "qty, "               .
 122       "allocated, "         .
 123       "sellprice, "         .
 124       "fxsellprice, "       .
 125       "discount, "          .
 126       "unit, "              .
 127       "project_id, "        .
 128       "serialnumber "       .
 129       ") VALUES ( "         .
 130       "$invid, "            . // trans_id
 131       "$services_id, "      . // parts_id
 132       "'$description', "    . // description
 133       "1, "                 . // qty
 134       "0, "                 . // allocated
 135       "$amount, "           . // sellprice
 136       "$amount, "           . // fxsellprice
 137       "0, "                 . // discount
 138       "'', "                . // unit
 139       "$insplan, "          . // project_id
 140       "'$serialnumber'"     . // serialnumber
 141       ")";
 142     if ($debug) {
 143       echo $query . "<br>\n";
 144     } else {
 145       SLQuery($query);
 146       if ($sl_err) die($sl_err);
 147     }
 148   }
 149
 150   // Update totals and payment date in the invoice header.  Dollar amounts are
 151   // stored as double precision floats so we have to be careful about rounding.
 152   //
 153   function slUpdateAR($invid, $amount, $paid = 0, $paydate = "", $debug) {
 154     global $sl_err;
 155     $paydate = fixDate($paydate);
 156     $query = "UPDATE ar SET amount = round(CAST (amount AS numeric) + $amount, 2), " .
 157       "netamount = round(CAST (netamount AS numeric) + $amount, 2)";
 158     if ($paid) $query .= ", paid = round(CAST (paid AS numeric) + $paid, 2), datepaid = '$paydate'";
 159     $query .= " WHERE id = $invid";
 160     if ($debug) {
 161       echo $query . "<br>\n";
 162     } else {
 163       SLQuery($query);
 164       if ($sl_err) die($sl_err);
 165     }
 166   }
 167
 168   function slPostPayment($trans_id, $thispay, $thisdate, $thissrc, $code, $thisins, $debug) {
 169     global $chart_id_cash, $chart_id_ar;
 170     // Post a payment: add to ar, subtract from cash.
 171     slAddTransaction($trans_id, $chart_id_ar  , $thispay    , $thisdate, $thissrc, $code, $thisins, $debug);
 172     slAddTransaction($trans_id, $chart_id_cash, 0 - $thispay, $thisdate, $thissrc, $code, $thisins, $debug);
 173     slUpdateAR($trans_id, 0, $thispay, $thisdate, $debug);
 174   }
 175
 176   function slPostCharge($trans_id, $thisamt, $thisdate, $code, $thisins, $description, $debug) {
 177     global $chart_id_income, $chart_id_ar;
 178     // Post an adjustment: add negative invoice item, add to ar, subtract from income
 179     slAddLineItem($trans_id, $code, $thisamt, $thisins, $description, $debug);
 180     if ($thisamt) {
 181       slAddTransaction($trans_id, $chart_id_ar    , 0 - $thisamt, $thisdate, $description, $code, $thisins, $debug);
 182       slAddTransaction($trans_id, $chart_id_income, $thisamt    , $thisdate, $description, $code, $thisins, $debug);
 183       slUpdateAR($trans_id, $thisamt, 0, '', $debug);
 184     }
 185   }
 186
 187   function slPostAdjustment($trans_id, $thisadj, $thisdate, $thissrc, $code, $thisins, $reason, $debug) {
 188     global $chart_id_income, $chart_id_ar;
 189     // Post an adjustment: add negative invoice item, add to ar, subtract from income
 190     $adjdate = fixDate($thisdate);
 191     $description = "Adjustment $adjdate $reason";
 192     slAddLineItem($trans_id, $code, 0 - $thisadj, $thisins, $description, $debug);
 193     if ($thisadj) {
 194       slAddTransaction($trans_id, $chart_id_ar, $thisadj, $thisdate, "InvAdj $thissrc", $code, $thisins, $debug);
 195       slAddTransaction($trans_id, $chart_id_income, 0 - $thisadj, $thisdate, "InvAdj $thissrc", $code, $thisins, $debug);
 196       slUpdateAR($trans_id, 0 - $thisadj, 0, '', $debug);
 197     }
 198   }
 199
 200   // Do whatever is necessary to make this invoice re-billable.
 201   //
 202   function slSetupSecondary($invid, $debug) {
 203     global $sl_err, $GLOBALS;
 204     $info_msg = '';
 205
 206     // Get some needed items from the SQL-Ledger invoice.
 207     $arres = SLQuery("select invnumber, transdate, customer_id, employee_id, " .
 208       "shipvia from ar where ar.id = $invid");
 209     if ($sl_err) die($sl_err);
 210     $arrow = SLGetRow($arres, 0);
 211     if (! $arrow) die(xl('There is no match for invoice id') . ' = ' . "$trans_id.");
 212     $customer_id = $arrow['customer_id'];
 213     list($trash, $encounter) = explode(".", $arrow['invnumber']);
 214
 215     // Get the OpenEMR PID corresponding to the customer.
 216     $pdrow = sqlQuery("SELECT patient_data.pid " .
 217       "FROM integration_mapping, patient_data WHERE " .
 218       "integration_mapping.foreign_id = $customer_id AND " .
 219       "integration_mapping.foreign_table = 'customer' AND " .
 220       "patient_data.id = integration_mapping.local_id");
 221     $pid = $pdrow['pid'];
 222     if (! $pid) die(xl("Cannot find patient from SQL-Ledger customer id") . " = $customer_id.");
 223
 224     // Determine the ID of the next insurance company (if any) to be billed.
 225     $new_payer_id = -1;
 226     $insdone = strtolower($arrow['shipvia']);
 227     foreach (array('ins1' => 'primary', 'ins2' => 'secondary', 'ins3' => 'tertiary') as $key => $value) {
 228       if (strpos($insdone, $key) === false) {
 229         $nprow = sqlQuery("SELECT provider FROM insurance_data WHERE " .
 230           "pid = '$pid' AND type = '$value'");
 231         if ($nprow['provider']) {
 232           $new_payer_id = $nprow['provider'];
 233         }
 234         break;
 235       }
 236     }
 237
 238     // Find out if the encounter exists.
 239     $ferow = sqlQuery("SELECT pid FROM form_encounter WHERE " .
 240       "encounter = $encounter");
 241     $encounter_pid = $ferow['pid'];
 242
 243     // If it exists, just update the billing items.
 244     if ($encounter_pid) {
 245       if ($encounter_pid != $pid)
 246         die(xl("Expected form_encounter.pid to be ") . $pid . ', ' . xl(' but was ') . $encounter_pid);
 247
 248       // If there's a payer ID queue it up, otherwise just reopen it.
 249       if ($new_payer_id > 0) {
 250         // TBD: implement a default bill_process and target in config.php,
 251         // it should not really be hard-coded here.
 252         $query = "UPDATE billing SET billed = 0, bill_process = 5, " .
 253           "target = 'hcfa', payer_id = $new_payer_id, " .
 254           "bill_date = NOW(), process_date = NULL, process_file = NULL " .
 255           "WHERE encounter = $encounter AND pid = $pid AND activity = 1";
 256       } else {
 257         $query = "UPDATE billing SET billed = 0, bill_process = 0, payer_id = -1, " .
 258           "bill_date = NULL, process_date = NULL, process_file = NULL " .
 259           "WHERE encounter = $encounter AND pid = $pid AND activity = 1";
 260       }
 261
 262       if ($debug) {
 263         echo $query . "<br>\n";
 264       } else {
 265         sqlQuery($query);
 266       }
 267       $info_msg = xl("Encounter ") . $encounter . xl(" is ready for re-billing.");
 268       return;
 269     }
 270
 271     // If we get here then the encounter does not already exist.  This should
 272     // only happen if A/R was converted from an earlier system.  In this case
 273     // the encounter ID should be the date of service, and we will create the
 274     // encounter.
 275
 276     // If it does not exist then it better be (or start with) a date.
 277     if (! preg_match("/^20\d\d\d\d\d\d/", $encounter))
 278       die(xl("Internal error: encounter '") . $encounter . xl("' should exist but does not."));
 279
 280     $employee_id = $arrow['employee_id'];
 281
 282     // Get the OpenEMR provider info corresponding to the SQL-Ledger salesman.
 283     $drrow = sqlQuery("SELECT users.id, users.username, users.facility " .
 284       "FROM integration_mapping, users WHERE " .
 285       "integration_mapping.foreign_id = $employee_id AND " .
 286       "integration_mapping.foreign_table = 'salesman' AND " .
 287       "users.id = integration_mapping.local_id");
 288     $provider_id = $drrow['id'];
 289     if (! $provider_id) die(xl("Cannot find provider from SQL-Ledger employee = ") . $employee_id );
 290
 291     $date_of_service = $arrow['transdate'];
 292     if (! $date_of_service) die(xl("Invoice has no date!"));
 293
 294     // Generate a new encounter number.
 295     $conn = $GLOBALS['adodb']['db'];
 296     $new_encounter = $conn->GenID("sequences");
 297
 298     // Create the "new encounter".
 299     $encounter_id = 0;
 300     $query = "INSERT INTO form_encounter ( " .
 301       "date, reason, facility, pid, encounter, onset_date " .
 302       ") VALUES ( " .
 303       "'$date_of_service', " .
 304       "'" . xl('Imported from Accounting') . "', " .
 305       "'" . addslashes($drrow['facility']) . "', " .
 306       "$pid, " .
 307       "$new_encounter, " .
 308       "'$date_of_service' " .
 309       ")";
 310     if ($debug) {
 311       echo $query . "<br>\n";
 312       echo xl("Call to addForm() goes here.<br>") . "\n";
 313     } else {
 314       $encounter_id = idSqlStatement($query);
 315       if (! $encounter_id) die(xl("Insert failed: ") . $query);
 316       addForm($new_encounter, xl("New Patient Encounter"), $encounter_id,
 317         "newpatient", $pid, 1, $date_of_service);
 318       $info_msg = xl("Encounter ") . $new_encounter . xl(" has been created. ");
 319     }
 320
 321     // For each invoice line item with a billing code we will insert
 322     // a billing row with payer_id set to -1.  Order the line items
 323     // chronologically so that each procedure code will be followed by
 324     // its associated icd9 code.
 325
 326     $inres = SLQuery("SELECT * FROM invoice WHERE trans_id = $invid " .
 327       "ORDER BY id");
 328     if ($sl_err) die($sl_err);
 329
 330     // When nonzero, this will be the ID of a billing row that needs to
 331     // have its justify field set.
 332     $proc_ins_id = 0;
 333
 334     for ($irow = 0; $irow < SLRowCount($inres); ++$irow) {
 335       $row = SLGetRow($inres, $irow);
 336       $amount   = $row['sellprice'];
 337
 338       // Extract the billing code.
 339       $code = xl("Unknown");
 340       if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['serialnumber'], $matches)) {
 341         $code = strtoupper($matches[1]);
 342       }
 343       else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['description'], $matches)) {
 344         $code = strtoupper($matches[1]);
 345       }
 346
 347       list($code, $modifier) = explode("-", $code);
 348
 349       // Set the billing code type and description.
 350       $code_type = "";
 351       $code_text = "";
 352
 353       foreach ($code_types as $key => $value) {
 354         if (preg_match("/$key/", $row['serialnumber'])) {
 355           $code_type = $key;
 356           if ($value['fee']) {
 357             $code_text = xl("Procedure") . " $code";
 358           } else {
 359             $code_text = xl("Diagnosis") . " $code";
 360             if ($proc_ins_id) {
 361               $query = "UPDATE billing SET justify = '$code' WHERE id = $proc_ins_id";
 362               if ($debug) {
 363                 echo $query . "<br>\n";
 364               } else {
 365                 sqlQuery($query);
 366               }
 367               $proc_ins_id = 0;
 368             }
 369           }
 370           break;
 371         }
 372       }
 373
 374       // Skip adjustments.
 375       if (! $code_type) continue;
 376
 377       // Insert the billing item.  If this for a procedure code then save
 378       // the row ID so that we can update the "justify" field with the ICD9
 379       // code, which should come next in the loop.
 380       //
 381       $query = "INSERT INTO billing ( " .
 382         "date, code_type, code, pid, provider_id, user, groupname, authorized, " .
 383         "encounter, code_text, activity, payer_id, billed, bill_process, " .
 384         "bill_date, modifier, units, fee, justify, target " .
 385         ") VALUES ( " .
 386         "NOW(), " .
 387         "'$code_type', " .
 388         "'$code', " .
 389         "$pid, " .
 390         "$provider_id, " .
 391         "'" . $_SESSION['authId'] . "', " .
 392         "'" . $_SESSION['authProvider'] . "', " .
 393         "1, " .
 394         "$new_encounter, " .
 395         "'$code_text', " .
 396         "1, " .
 397         "$new_payer_id, " .
 398         ($new_payer_id > 0 ? "1, " : "0, ") .
 399         ($new_payer_id > 0 ? "5, " : "0, ") .
 400         ($new_payer_id > 0 ? "NOW(), " : "NULL, ") .
 401         "'$modifier', " .
 402         "0, " .
 403         "$amount, " .
 404         "'', " .
 405         ($new_payer_id > 0 ? "'hcfa' " : "NULL ") .
 406         ")";
 407       if ($debug) {
 408         echo $query . "<br>\n";
 409       } else {
 410         $proc_ins_id = idSqlStatement($query);
 411         if ($code_type != "CPT4" && $code_type != "HCPCS")
 412           $proc_ins_id = 0;
 413       }
 414     }
 415
 416     // Finally, change this invoice number to contain the new encounter number.
 417     //
 418     $new_invnumber = "$pid.$new_encounter";
 419     $query = "UPDATE ar SET invnumber = '$new_invnumber' WHERE id = $invid";
 420     if ($debug) {
 421       echo $query . "<br>\n";
 422     } else {
 423       SLQuery($query);
 424       if ($sl_err) die($sl_err);
 425       $info_msg .= xl("This invoice number has been changed to ") . $new_invnumber;
 426     }
 427
 428     return $info_msg;
 429   }
 430 ?>