2 // Copyright (C) 2005-2006 Rod Roark <rod@sunsetsystems.com>
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.
9 include_once("sql-ledger.inc");
10 include_once("patient.inc");
11 include_once("billing.inc");
12 include_once("invoice_summary.inc.php");
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;
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'");
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'");
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'");
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'");
41 function slTerminate() {
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.
49 function slInvoiceNumber(&$out) {
50 $invnumber = $out['our_claim_id'];
51 $atmp = preg_split('/[ -]/', $invnumber);
52 $acount = count($atmp);
58 $encounter = $atmp[1];
60 else if ($acount == 3) {
62 $brow = sqlQuery("SELECT encounter FROM billing WHERE " .
63 "pid = '$pid' AND id = '" . $atmp[1] . "' AND activity = 1");
64 $encounter = $brow['encounter'];
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']) . "' " .
71 while ($prow = sqlFetchArray($pres)) {
72 if (strpos($invnumber, $prow['pid']) === 0) {
74 $encounter = substr($invnumber, strlen($pid));
80 if ($pid && $encounter) $invnumber = "$pid.$encounter";
81 return array($pid, $encounter, $invnumber);
84 // Insert a row into the acc_trans table.
86 function slAddTransaction($invid, $chartid, $amount, $date, $source, $memo, $insplan, $debug) {
88 $date = fixDate($date);
89 $query = "INSERT INTO acc_trans ( " .
98 "$invid, " . // trans_id
99 "$chartid, " . // chart_id
100 "$amount, " . // amount
101 "'$date', " . // transdate
102 "'$source', " . // source
103 "$insplan, " . // project_id
107 echo $query . "<br>\n";
110 if ($sl_err) die($sl_err);
114 // Insert a row into the invoice table.
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 ( " .
135 "$invid, " . // trans_id
136 "$services_id, " . // parts_id
137 "'$description', " . // description
140 "$price, " . // sellprice
141 "$price, " . // fxsellprice
144 "$insplan, " . // project_id
145 "'$serialnumber'" . // serialnumber
148 echo $query . "<br>\n";
151 if ($sl_err) die($sl_err);
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.
158 function slUpdateAR($invid, $amount, $paid = 0, $paydate = "", $debug) {
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";
166 echo $query . "<br>\n";
169 if ($sl_err) die($sl_err);
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);
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);
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);
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);
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);
205 // Do whatever is necessary to make this invoice re-billable.
207 function slSetupSecondary($invid, $debug) {
208 global $sl_err, $GLOBALS;
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']);
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.");
230 // Determine the ID of the next insurance company (if any) to be billed.
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'];
245 // Find out if the encounter exists.
246 $ferow = sqlQuery("SELECT pid FROM form_encounter WHERE " .
247 "encounter = $encounter");
248 $encounter_pid = $ferow['pid'];
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);
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.
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";
266 updateClaim(true, $pid, $encounter, $new_payer_id, 1, 5, '', 'hcfa');
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";
274 updateClaim(true, $pid, $encounter, -1, 1, 0, '');
279 echo $query . "<br>\n";
285 $info_msg = xl("Encounter ") . $encounter . xl(" is ready for re-billing.");
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
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."));
298 $employee_id = $arrow['employee_id'];
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 );
309 if (! $date_of_service) die(xl("Invoice has no date!"));
311 // Generate a new encounter number.
312 $conn = $GLOBALS['adodb']['db'];
313 $new_encounter = $conn->GenID("sequences");
315 // Create the "new encounter".
317 $query = "INSERT INTO form_encounter ( " .
318 "date, reason, facility_id, pid, encounter, onset_date " .
320 "'$date_of_service', " .
321 "'" . xl('Imported from Accounting') . "', " .
322 "'" . addslashes($drrow['facility_id']) . "', " .
325 "'$date_of_service' " .
328 echo $query . "<br>\n";
329 echo xl("Call to addForm() goes here.<br>") . "\n";
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. ");
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.
343 $inres = SLQuery("SELECT * FROM invoice WHERE trans_id = $invid " .
345 if ($sl_err) die($sl_err);
347 // When nonzero, this will be the ID of a billing row that needs to
348 // have its justify field set.
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']);
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]);
361 else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['description'], $matches)) {
362 $code = strtoupper($matches[1]);
365 list($code, $modifier) = explode("-", $code);
367 // Set the billing code type and description.
371 foreach ($code_types as $key => $value) {
372 if (preg_match("/$key/", $row['serialnumber'])) {
375 $code_text = xl("Procedure") . " $code";
377 $code_text = xl("Diagnosis") . " $code";
379 $query = "UPDATE billing SET justify = '$code' WHERE id = $proc_ins_id";
381 echo $query . "<br>\n";
393 if (! $code_type) continue;
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.
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 " .
409 "'" . $_SESSION['authId'] . "', " .
410 "'" . $_SESSION['authProvider'] . "', " .
416 ($new_payer_id > 0 ?
"1, " : "0, ") .
417 ($new_payer_id > 0 ?
"5, " : "0, ") .
418 ($new_payer_id > 0 ?
"NOW(), " : "NULL, ") .
423 ($new_payer_id > 0 ?
"'hcfa' " : "NULL ") .
426 echo $query . "<br>\n";
428 $proc_ins_id = idSqlStatement($query);
429 if ($code_type != "CPT4" && $code_type != "HCPCS")
434 // Finally, change this invoice number to contain the new encounter number.
436 $new_invnumber = "$pid.$new_encounter";
437 $query = "UPDATE ar SET invnumber = '$new_invnumber' WHERE id = $invid";
439 echo $query . "<br>\n";
442 if ($sl_err) die($sl_err);
443 $info_msg .= xl("This invoice number has been changed to ") . $new_invnumber;