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