2 // Copyright (C) 2005 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 // This is the second of two pages to support posting of EOBs.
10 // The first is sl_eob_search.php.
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 include_once("../../library/invoice_summary.inc.php");
17 include_once("../../custom/code_types.inc.php");
19 $debug = 0; // set to 1 for debugging mode
40 // Format money for display.
42 function bucks($amount) {
44 printf("%.2f", $amount);
47 // Insert a row into the acc_trans table.
49 function addTransaction($invid, $chartid, $amount, $date, $source, $memo, $insplan) {
50 global $sl_err, $debug;
51 $date = fixDate($date);
52 $query = "INSERT INTO acc_trans ( " .
61 "$invid, " . // trans_id
62 "$chartid, " . // chart_id
63 "$amount, " . // amount
64 "'$date', " . // transdate
65 "'$source', " . // source
66 "$insplan, " . // project_id
70 echo $query . "<br>\n";
73 if ($sl_err) die($sl_err);
77 // Insert a row into the invoice table.
79 function addLineItem($invid, $serialnumber, $amount, $adjdate, $insplan, $reason) {
80 global $sl_err, $services_id, $debug;
81 $adjdate = fixDate($adjdate);
82 $description = "Adjustment $adjdate $reason";
83 $query = "INSERT INTO invoice ( " .
96 "$invid, " . // trans_id
97 "$services_id, " . // parts_id
98 "'$description', " . // description
101 "$amount, " . // sellprice
102 "$amount, " . // fxsellprice
105 "$insplan, " . // project_id
106 "'$serialnumber'" . // serialnumber
109 echo $query . "<br>\n";
112 if ($sl_err) die($sl_err);
116 // Update totals and payment date in the invoice header. Dollar amounts are
117 // stored as double precision floats so we have to be careful about rounding.
119 function updateAR($invid, $amount, $paid = 0, $paydate = "") {
120 global $sl_err, $debug;
121 $paydate = fixDate($paydate);
122 $query = "UPDATE ar SET amount = round(CAST (amount AS numeric) + $amount, 2), " .
123 "netamount = round(CAST (netamount AS numeric) + $amount, 2)";
124 if ($paid) $query .= ", paid = round(CAST (paid AS numeric) + $paid, 2), datepaid = '$paydate'";
125 $query .= " WHERE id = $invid";
127 echo $query . "<br>\n";
130 if ($sl_err) die($sl_err);
134 // Do whatever is necessary to make this invoice re-billable.
136 function setupSecondary($invid) {
137 global $sl_err, $debug, $info_msg, $GLOBALS;
139 // Get some needed items from the SQL-Ledger invoice.
140 $arres = SLQuery("select invnumber, transdate, customer_id, employee_id " .
141 "from ar where ar.id = $invid");
142 if ($sl_err) die($sl_err);
143 $arrow = SLGetRow($arres, 0);
144 if (! $arrow) die("There is no match for invoice id = $trans_id.");
145 $customer_id = $arrow['customer_id'];
146 list($trash, $encounter) = explode(".", $arrow['invnumber']);
148 // Get the OpenEMR PID corresponding to the customer.
149 $pdrow = sqlQuery("SELECT patient_data.pid " .
150 "FROM integration_mapping, patient_data WHERE " .
151 "integration_mapping.foreign_id = $customer_id AND " .
152 "integration_mapping.foreign_table = 'customer' AND " .
153 "patient_data.id = integration_mapping.local_id");
154 $pid = $pdrow['pid'];
155 if (! $pid) die("Cannot find patient from SQL-Ledger customer id = $customer_id.");
157 // Find out if the encounter exists.
158 $ferow = sqlQuery("SELECT pid FROM form_encounter WHERE " .
159 "encounter = $encounter");
160 $encounter_pid = $ferow['pid'];
162 // If it exists, just update the billing items.
163 if ($encounter_pid) {
164 if ($encounter_pid != $pid)
165 die("Expected form_encounter.pid to be $pid, but was $encounter_pid");
166 $query = "UPDATE billing SET billed = 0, bill_process = 0, payer_id = -1, " .
167 "bill_date = NULL, process_date = NULL, process_file = NULL " .
168 "WHERE encounter = $encounter AND pid = $pid AND activity = 1";
170 echo $query . "<br>\n";
174 $info_msg = "Encounter $encounter is ready for re-billing.";
178 // It does not exist then it better be a date.
179 if (! preg_match("/^20\d\d\d\d\d\d$/", $encounter))
180 die("Internal error: encounter '$encounter' should exist but does not.");
182 $employee_id = $arrow['employee_id'];
184 // Get the OpenEMR provider info corresponding to the SQL-Ledger salesman.
185 $drrow = sqlQuery("SELECT users.id, users.username, users.facility " .
186 "FROM integration_mapping, users WHERE " .
187 "integration_mapping.foreign_id = $employee_id AND " .
188 "integration_mapping.foreign_table = 'salesman' AND " .
189 "users.id = integration_mapping.local_id");
190 $provider_id = $drrow['id'];
191 if (! $provider_id) die("Cannot find provider from SQL-Ledger employee = $employee_id.");
193 $date_of_service = $arrow['transdate'];
194 if (! $date_of_service) die("Invoice has no date!");
196 // Generate a new encounter number.
197 $conn = $GLOBALS['adodb']['db'];
198 $new_encounter = $conn->GenID("sequences");
200 // Create the "new encounter".
202 $query = "INSERT INTO form_encounter ( " .
203 "date, reason, facility, pid, encounter, onset_date " .
205 "'$date_of_service', " .
206 "'Imported from Accounting', " .
207 "'" . addslashes($drrow['facility']) . "', " .
210 "'$date_of_service' " .
213 echo $query . "<br>\n";
214 echo "Call to addForm() goes here.<br>\n";
216 $encounter_id = idSqlStatement($query);
217 if (! $encounter_id) die("Insert failed: $query");
218 addForm($new_encounter, "New Patient Encounter", $encounter_id,
219 "newpatient", $pid, 1, $date_of_service);
220 $info_msg = "Encounter $new_encounter has been created. ";
223 // For each invoice line item with a billing code we will insert
224 // a billing row with payer_id set to -1. Order the line items
225 // chronologically so that each procedure code will be followed by
226 // its associated icd9 code.
228 $inres = SLQuery("SELECT * FROM invoice WHERE trans_id = $invid " .
230 if ($sl_err) die($sl_err);
232 // When nonzero, this will be the ID of a billing row that needs to
233 // have its justify field set.
236 for ($irow = 0; $irow < SLRowCount($inres); ++
$irow) {
237 $row = SLGetRow($inres, $irow);
238 $amount = $row['sellprice'];
240 // Extract the billing code.
242 if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['serialnumber'], $matches)) {
243 $code = strtoupper($matches[1]);
245 else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['description'], $matches)) {
246 $code = strtoupper($matches[1]);
249 list($code, $modifier) = explode("-", $code);
251 // Set the billing code type and description.
256 if (preg_match("/CPT/", $row['serialnumber'])) {
258 $code_text = "Procedure $code";
260 else if (preg_match("/HCPCS/", $row['serialnumber'])) {
261 $code_type = "HCPCS";
262 $code_text = "Procedure $code";
264 else if (preg_match("/ICD/", $row['serialnumber'])) {
266 $code_text = "Diagnosis $code";
268 $query = "UPDATE billing SET justify = '$code' WHERE id = $proc_ins_id";
270 echo $query . "<br>\n";
279 foreach ($code_types as $key => $value) {
280 if (preg_match("/$key/", $row['serialnumber'])) {
283 $code_text = "Procedure $code";
285 $code_text = "Diagnosis $code";
287 $query = "UPDATE billing SET justify = '$code' WHERE id = $proc_ins_id";
289 echo $query . "<br>\n";
301 if (! $code_type) continue;
303 // Insert the billing item. If this for a procedure code then save
304 // the row ID so that we can update the "justify" field with the ICD9
305 // code, which should come next in the loop.
307 $query = "INSERT INTO billing ( " .
308 "date, code_type, code, pid, provider_id, user, groupname, authorized, " .
309 "encounter, code_text, activity, payer_id, billed, bill_process, " .
310 "modifier, units, fee, justify " .
317 "'" . $_SESSION['authId'] . "', " .
318 "'" . $_SESSION['authProvider'] . "', " .
332 echo $query . "<br>\n";
334 $proc_ins_id = idSqlStatement($query);
335 if ($code_type != "CPT4" && $code_type != "HCPCS")
340 // Finally, change this invoice number to contain the new encounter number.
342 $new_invnumber = "$pid.$new_encounter";
343 $query = "UPDATE ar SET invnumber = '$new_invnumber' WHERE id = $invid";
345 echo $query . "<br>\n";
348 if ($sl_err) die($sl_err);
349 $info_msg .= "This invoice number has been changed to $new_invnumber.";
355 <link rel
=stylesheet href
="<?echo $css_header;?>" type
="text/css">
356 <title
>EOB Posting
- Invoice
</title
>
357 <script language
="JavaScript">
359 // An insurance radio button is selected.
360 function setins(istr
) {
361 var f
= document
.forms
[0];
362 for (var i
= 0; i
< f
.elements
.length
; ++i
) {
363 var ename
= f
.elements
[i
].name
;
364 if (ename
.indexOf('[src]') < 0) continue;
365 var evalue
= f
.elements
[i
].value
;
366 var tmp
= evalue
.substring(0, 4).toLowerCase();
367 if (tmp
>= 'ins1' && tmp
<= 'ins3')
368 evalue
= evalue
.substring(4);
369 else if (evalue
.substring(0, 2).toLowerCase() == 'pt')
370 evalue
= evalue
.substring(2);
371 while (evalue
.substring(0, 1) == '/')
372 evalue
= evalue
.substring(1);
373 f
.elements
[i
].value
= istr +
'/' + evalue
;
378 // Compute an adjustment that writes off the balance:
379 function writeoff(code
) {
380 var f
= document
.forms
[0];
382 f
['form_line[' + code +
'][bal]'].value
-
383 f
['form_line[' + code +
'][pay]'].value
;
384 f
['form_line[' + code +
'][adj]'].value
= Number(tmp
).toFixed(2);
388 // Onsubmit handler. A good excuse to write some JavaScript.
389 function validate(f
) {
390 for (var i
= 0; i
< f
.elements
.length
; ++i
) {
391 var ename
= f
.elements
[i
].name
;
392 var pfxlen
= ename
.indexOf('[pay]');
393 if (pfxlen
< 0) continue;
394 var pfx
= ename
.substring(0, pfxlen
);
395 var code
= pfx
.substring(pfx
.indexOf('[')+
1, pfxlen
-1);
396 if (f
[pfx+
'[pay]'].value || f
[pfx+
'[adj]'].value
) {
397 var srcobj
= f
[pfx+
'[src]'];
398 while (srcobj
.value
.length
) {
399 var tmp
= srcobj
.value
.substring(srcobj
.value
.length
- 1);
400 if (tmp
> ' ' && tmp
!= '/') break;
401 srcobj
.value
= srcobj
.value
.substring(0, srcobj
.value
.length
- 1);
403 var svalue
= srcobj
.value
;
405 alert('Source is missing for code ' + code
);
408 var tmp
= svalue
.substring(0, 4).toLowerCase();
409 if (tmp
>= 'ins1' && tmp
<= 'ins3') {
410 svalue
= svalue
.substring(4);
411 } else if (svalue
.substring(0, 2).toLowerCase() == 'pt') {
412 svalue
= svalue
.substring(2);
414 alert('Invalid or missing payer in source for code ' + code
);
418 if (svalue
.substring(0, 1) != '/') {
419 alert('Missing slash after payer in source for code ' + code
);
422 tmp
= svalue
.substring(1, 3).toLowerCase();
423 if (tmp
!= 'nm' && tmp
!= 'ci' && tmp
!= 'cp' && tmp
!= 'ne' &&
424 tmp
!= 'it' && tmp
!= 'pf' && tmp
!= 'pp' && tmp
!= 'ok')
426 alert('Invalid source designation "' + tmp +
'" for code ' + code
);
431 if (! f
[pfx+
'[date]'].value
) {
432 alert('Date is missing for code ' + code
);
436 if (f
[pfx+
'[pay]'].value
&& isNaN(parseFloat(f
[pfx+
'[pay]'].value
))) {
437 alert('Payment value for code ' + code +
' is not a number');
440 if (f
[pfx+
'[adj]'].value
&& isNaN(parseFloat(f
[pfx+
'[adj]'].value
))) {
441 alert('Adjustment value for code ' + code +
' is not a number');
444 // TBD: validate the date format
451 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0'>
453 $trans_id = $_GET['id'];
454 if (! $trans_id) die("You cannot access this page directly.");
458 $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
459 if ($sl_err) die($sl_err);
460 if (! $chart_id_cash) die("There is no COA entry for cash account '$sl_cash_acc'");
462 $chart_id_ar = SLQueryValue("select id from chart where accno = '$sl_ar_acc'");
463 if ($sl_err) die($sl_err);
464 if (! $chart_id_ar) die("There is no COA entry for AR account '$sl_ar_acc'");
466 $chart_id_income = SLQueryValue("select id from chart where accno = '$sl_income_acc'");
467 if ($sl_err) die($sl_err);
468 if (! $chart_id_income) die("There is no COA entry for income account '$sl_income_acc'");
470 $services_id = SLQueryValue("select id from parts where partnumber = '$sl_services_id'");
471 if ($sl_err) die($sl_err);
472 if (! $services_id) die("There is no parts entry for services ID '$sl_services_id'");
474 if ($_POST['form_save'] ||
$_POST['form_cancel']) {
475 if ($_POST['form_save']) {
477 echo "<p><b>This module is in test mode. The database will not be changed.</b><p>\n";
480 foreach ($_POST['form_line'] as $code => $cdata) {
481 $thissrc = trim($cdata['src']);
482 $thisdate = trim($cdata['date']);
483 $thispay = trim($cdata['pay']);
484 $thisadj = trim($cdata['adj']);
485 $thisins = trim($cdata['ins']);
486 $reason = trim($cdata['reason']);
487 if (strpos(strtolower($reason), 'ins') !== false)
488 $reason .= ' ' . $_POST['form_insurance'];
489 if (! $thisins) $thisins = 0;
491 // Post a payment: add to ar, subtract from cash.
492 addTransaction($trans_id, $chart_id_ar, $thispay, $thisdate, $thissrc, $code, $thisins);
493 addTransaction($trans_id, $chart_id_cash, 0 - $thispay, $thisdate, $thissrc, $code, $thisins);
494 updateAR($trans_id, 0, $thispay, $thisdate);
495 $paytotal +
= $thispay;
498 // Post an adjustment: add negative invoice item, add to ar, subtract from income
499 addLineItem($trans_id, $code, 0 - $thisadj, $thisdate, $thisins, $reason);
500 addTransaction($trans_id, $chart_id_ar, $thisadj, $thisdate, "InvAdj $thissrc", $code, $thisins);
501 addTransaction($trans_id, $chart_id_income, 0 - $thisadj, $thisdate, "InvAdj $thissrc", $code, $thisins);
502 updateAR($trans_id, 0 - $thisadj);
505 $form_duedate = fixDate($_POST['form_duedate']);
506 $form_notes = trim($_POST['form_notes']);
508 // Maintain the list of insurances that we mark as finished.
509 // We use the "Ship Via" field of the invoice to hold these.
512 foreach (array('Ins1', 'Ins2', 'Ins3') as $value) {
513 if ($_POST["form_done_$value"]) {
514 if ($form_eobs) $form_eobs .= ","; else $form_eobs = "Done: ";
515 $form_eobs .= $value;
519 $query = "UPDATE ar SET duedate = '$form_duedate', notes = '$form_notes', " .
520 "shipvia = '$form_eobs' WHERE id = $trans_id";
523 echo $query . "<br>\n";
526 if ($sl_err) die($sl_err);
528 if ($_POST['form_secondary']) {
529 setupSecondary($trans_id);
531 echo "<script language='JavaScript'>\n";
532 echo " var tmp = opener.document.forms[0].form_amount.value - $paytotal;\n";
533 echo " opener.document.forms[0].form_amount.value = Number(tmp).toFixed(2);\n";
535 echo "<script language='JavaScript'>\n";
537 if ($info_msg) echo " alert('$info_msg');\n";
538 if (! $debug) echo " window.close();\n";
539 echo "</script></body></html>\n";
544 // Get invoice data into $arrow.
545 $arres = SLQuery("select ar.*, customer.name, employee.name as doctor " .
546 "from ar, customer, employee where ar.id = $trans_id and " .
547 "customer.id = ar.customer_id and employee.id = ar.employee_id");
548 if ($sl_err) die($sl_err);
549 $arrow = SLGetRow($arres, 0);
550 if (! $arrow) die("There is no match for invoice id = $trans_id.");
552 // Determine the date of service. An 8-digit encounter number is
553 // presumed to be a date of service imported during conversion.
554 // Otherwise look it up in the form_encounter table.
557 list($trash, $encounter) = explode(".", $arrow['invnumber']);
558 if (strlen($encounter) == 8) {
559 $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
560 "-" . substr($encounter, 6, 2);
562 else if ($encounter) {
563 $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
564 "encounter = $encounter");
565 $svcdate = substr($tmp['date'], 0, 10);
568 // Get invoice charge details.
569 $codes = get_invoice_summary($trans_id, true);
573 <form method
='post' action
='sl_eob_invoice.php?id=<? echo $trans_id ?>'
574 onsubmit
='return validate(this)'>
576 <table border
='0' cellpadding
='3'>
582 <?
echo $arrow['name'] ?
>
584 <td colspan
="2" rowspan
="3">
585 <textarea name
="form_notes" cols
="50" style
="height:100%"><?
echo $arrow['notes'] ?
></textarea
>
593 <?
echo $arrow['doctor'] ?
>
601 <?
echo $arrow['invnumber'] ?
>
613 <!-- <?
echo $arrow['shipvia'] ?
> -->
616 // Write a checkbox for each insurance. It is to be checked when
617 // we no longer expect any payments from that company for the claim.
618 // The information is stored in the 'shipvia' field of the invoice.
620 $insgot = strtolower($arrow['notes']);
621 $insdone = strtolower($arrow['shipvia']);
622 foreach (array('Ins1', 'Ins2', 'Ins3') as $value) {
623 $lcvalue = strtolower($value);
624 $checked = (strpos($insdone, $lcvalue) === false) ?
"" : " checked";
625 if (strpos($insgot, $lcvalue) !== false) {
626 echo " <input type='checkbox' name='form_done_$value' value='1'$checked />$value \n";
638 <?
echo $arrow['transdate'] ?
>
641 Now posting
for: 
;
642 <input type
='radio' name
='form_insurance' value
='Ins1' onclick
='setins("Ins1")' checked
/>Ins1
 
;
643 <input type
='radio' name
='form_insurance' value
='Ins2' onclick
='setins("Ins2")' />Ins2
 
;
644 <input type
='radio' name
='form_insurance' value
='Ins3' onclick
='setins("Ins3")' />Ins3
 
;
645 <input type
='radio' name
='form_insurance' value
='Pt' onclick
='setins("Pt")' />Patient
646 <input type
='hidden' name
='form_eobs' value
='<?echo addslashes($arrow['shipvia
']) ?>' />
654 <input type
='text' name
='form_duedate' size
='10' value
='<?echo $arrow['duedate
'] ?>'
655 title
='Due date mm/dd/yyyy or yyyy-mm-dd'>
658 <input type
="checkbox" name
="form_secondary" value
="1"> Needs secondary billing
660 <input type
='submit' name
='form_save' value
='Save'>
662 <input type
='button' value
='Cancel' onclick
='window.close()'>
671 <table border
='0' cellpadding
='2' cellspacing
='0' width
='98%'>
673 <tr bgcolor
="#cccccc">
677 <td
class="dehead" align
="right">
680 <td
class="dehead" align
="right">
701 foreach ($codes as $code => $cdata) {
703 $bgcolor = "#" . (($encount & 1) ?
"ddddff" : "ffdddd");
705 // this sorts the details more or less chronologically:
706 ksort($cdata['dtl']);
707 foreach ($cdata['dtl'] as $dkey => $ddata) {
708 $ddate = substr($dkey, 0, 10);
709 if (preg_match('/^(\d\d\d\d)(\d\d)(\d\d)\s*$/', $ddate, $matches)) {
710 $ddate = $matches[1] . '-' . $matches[2] . '-' . $matches[3];
714 if ($ddata['chg'] > 0)
715 $tmpchg = $ddata['chg'];
716 else if ($ddata['chg'] < 0)
717 $tmpadj = 0 - $ddata['chg'];
719 <tr bgcolor
='<? echo $bgcolor ?>'>
721 <?
echo $dispcode; $dispcode = "" ?
>
723 <td
class="detail" align
="right">
726 <td
class="detail" align
="right">
730 <?
echo $ddata['src'] ?
>
736 <?
bucks($ddata['pmt']) ?
>
742 <?
echo $ddata['rsn'] ?
>
746 } // end of prior detail line
748 <tr bgcolor
='<? echo $bgcolor ?>'>
750 <?
echo $dispcode; $dispcode = "" ?
>
752 <td
class="detail" align
="right">
755 <td
class="detail" align
="right">
756 <input type
="hidden" name
="form_line[<? echo $code ?>][bal]" value
="<? bucks($cdata['bal']) ?>">
757 <input type
="hidden" name
="form_line[<? echo $code ?>][ins]" value
="<? echo $cdata['ins'] ?>">
758 <?
printf("%.2f", $cdata['bal']) ?
> 
;
761 <input type
="text" name
="form_line[<? echo $code ?>][src]" size
="10"
762 style
="background-color:<? echo $bgcolor ?>"
763 title
="NM=notmet, CI=coins, CP=copay, NE=notelig, IT=insterm, PF=ptfull, PP=ptpart" />
766 <input type
="text" name
="form_line[<? echo $code ?>][date]" size
="10" style
="background-color:<? echo $bgcolor ?>" />
769 <input type
="text" name
="form_line[<? echo $code ?>][pay]" size
="10" style
="background-color:<? echo $bgcolor ?>" />
772 <input type
="text" name
="form_line[<? echo $code ?>][adj]" size
="10" style
="background-color:<? echo $bgcolor ?>" />
773  
; <a href
="" onclick
="return writeoff('<? echo $code ?>')">W
</a
>
776 <select name
="form_line[<? echo $code ?>][reason]" style
="background-color:<? echo $bgcolor ?>">
778 foreach ($reasons as $value) {
779 echo " <option value=\"$value\">$value</option>\n";
793 <script language
="JavaScript">
794 var f1
= opener
.document
.forms
[0];
795 var f2
= document
.forms
[0];
797 foreach ($codes as $code => $cdata) {
798 echo " f2['form_line[$code][src]'].value = f1.form_source.value;\n";
799 echo " f2['form_line[$code][date]'].value = f1.form_paydate.value;\n";