4 function getBillingById($id, $cols = "*")
6 return sqlQuery("select $cols from billing where id='$id' and activity=1 order by date DESC limit 0,1");
9 function getBillingByPid($pid, $cols = "*")
11 return sqlQuery("select $cols from billing where pid ='$pid' and activity=1 order by date DESC limit 0,1");
14 function getBillingByEncounter($pid, $encounter, $cols = "code_type, code, code_text")
16 $res = sqlStatement("select $cols from billing where encounter = ? and pid=? and activity=1 order by code_type, date ASC", array($encounter,$pid));
19 for ($iter=0; $row=sqlFetchArray($res); $iter++) {
44 $sql = "insert into billing (date, encounter, code_type, code, code_text, " .
45 "pid, authorized, user, groupname, activity, billed, provider_id, " .
46 "modifier, units, fee, ndc_info, justify, notecodes, pricelevel) values (" .
47 "NOW(), ?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
48 return sqlInsert($sql, array($encounter_id,$code_type,$code,$code_text,$pid,$authorized,
49 $_SESSION['authId'],$_SESSION['authProvider'],$billed,$provider,$modifier,$units,$fee,
50 $ndc_info,$justify,$notecodes,$pricelevel));
53 function authorizeBilling($id, $authorized = "1")
55 sqlQuery("update billing set authorized = '$authorized' where id = '$id'");
58 function deleteBilling($id)
60 sqlStatement("update billing set activity = 0 where id = '$id'");
63 function clearBilling($id)
65 sqlStatement("update billing set justify = '' where id = '$id'");
68 // This function supports the Billing page (billing_process.php),
69 // and initiation of secondary processing (sl_eob.inc.php).
70 // It is called in the following situations:
72 // * billing_process.php sets bill_time, bill_process, payer and target on
73 // queueing a claim for processing. Create claims row.
74 // * billing_process.php sets claim status to 2, and payer, on marking a
75 // claim as billed without actually generating any billing. Create a
76 // claims row. In this case bill_process will remain at 0 and process_time
77 // and process_file will not be set.
78 // * billing_process.php sets bill_process, payer, target and x12 partner
79 // before calling gen_x12_837. Create a claims row.
80 // * billing_process.php sets claim status to 2 (billed), bill_process to 2,
81 // process_time and process_file after calling gen_x12_837. Claims row
83 // * billing_process.php sets claim status to 2 (billed) after creating
84 // an electronic batch (hcfa-only with recent changes). Claims
85 // row already exists.
86 // * EOB posting updates claim status to mark a payer as done. Claims row
88 // * EOB posting reopens an encounter for billing a secondary payer. Create
91 // $newversion should be passed to us to indicate if a new claims row
92 // is to be generated, otherwise one must already exist. The payer, if
93 // passed in for the latter case, must match the existing claim.
95 // Currently on the billing page the user can select any of the patient's
96 // payers. That logic will tailor the payer choices to the encounter date.
113 $sql = "SELECT * FROM claims WHERE patient_id = '$patient_id' AND " .
114 "encounter_id = '$encounter_id' AND status > 0 AND status < 4 ";
115 if ($payer_id >= 0) {
116 $sql .= "AND payer_id = '$payer_id' ";
119 $sql .= "ORDER BY version DESC LIMIT 1";
120 $row = sqlQuery($sql);
126 $payer_id = $row['payer_id'];
130 $status = $row['status'];
133 if ($bill_process < 0) {
134 $bill_process = $row['bill_process'];
137 if ($partner_id < 0) {
138 $partner_id = $row['x12_partner_id'];
141 if (!$process_file) {
142 $process_file = $row['process_file'];
146 $target = $row['target'];
152 if (empty($payer_id) || $payer_id < 0) {
156 if ($status==7) {//$status==7 is the claim denial case.
157 $claimset .= ", status = '$status'";
158 } elseif ($status >= 0) {
159 $claimset .= ", status = '$status'";
161 $billset .= ", billed = 1";
163 $billset .= ", bill_date = NOW()";
166 $billset .= ", billed = 0";
170 if ($status==7) {//$status==7 is the claim denial case.
171 $billset .= ", bill_process = '$status'";
172 } elseif ($bill_process >= 0) {
173 $claimset .= ", bill_process = '$bill_process'";
174 $billset .= ", bill_process = '$bill_process'";
177 if ($status==7) {//$status==7 is the claim denial case.
178 $claimset .= ", process_file = '$process_file'";//Denial reason code is stored here
179 } elseif ($process_file) {
180 $claimset .= ", process_file = '$process_file', process_time = NOW()";
181 $billset .= ", process_file = '$process_file', process_date = NOW()";
185 $claimset .= ", target = '$target'";
186 $billset .= ", target = '$target'";
189 if ($payer_id >= 0) {
190 $claimset .= ", payer_id = '$payer_id', payer_type = '$payer_type'";
191 $billset .= ", payer_id = '$payer_id'";
194 if ($partner_id >= 0) {
195 $claimset .= ", x12_partner_id = '$partner_id'";
196 $billset .= ", x12_partner_id = '$partner_id'";
200 $billset = substr($billset, 2);
201 sqlStatement("UPDATE billing SET $billset WHERE " .
202 "encounter = '$encounter_id' AND pid='$patient_id' AND activity = 1");
205 // If a new claim version is requested, insert its row.
209 $payer_id = ($payer_id < 0) ? $row['payer_id'] : $payer_id;
210 $bill_process = ($bill_process < 0) ? $row['bill_process'] : $bill_process;
211 $process_file = ($process_file) ? $row['process_file'] : $process_file;
212 $target = ($target) ? $row['target'] : $target;
213 $partner_id = ($partner_id < 0) ? $row['x12_partner_id'] : $partner_id;
214 $sql = "INSERT INTO claims SET " .
215 "patient_id = '$patient_id', " .
216 "encounter_id = '$encounter_id', " .
217 "bill_time = UNIX_TIMESTAMP(NOW()), " .
218 "payer_id = '$payer_id', " .
219 "status = '$status', " .
220 "payer_type = '" . $row['payer_type'] . "', " .
221 "bill_process = '$bill_process', " .
222 "process_time = '" . $row['process_time'] . "', " .
223 "process_file = '$process_file', " .
224 "target = '$target', " .
225 "x12_partner_id = '$partner_id'";
228 $version = sqlQuery("SELECT IFNULL(MAX(version),0) + 1 AS increment FROM claims WHERE patient_id = ? AND encounter_id = ?", array( $patient_id, $encounter_id ));
230 $sql .= "INSERT INTO claims SET " .
231 "patient_id = $patient_id, " .
232 "encounter_id = $encounter_id, " .
233 "bill_time = NOW() $claimset ," .
234 "version = " . $version['increment'];
235 } else {//Claim automatic forward case.startTra
236 $sql= "INSERT INTO claims SET " .
237 "patient_id = $patient_id, " .
238 "encounter_id = $encounter_id, " .
239 "bill_time = NOW(), status=$status ," .
240 "version = " . $version['increment'];
245 } // Otherwise update the existing claim row.
247 else if ($claimset) {
248 $claimset = substr($claimset, 2);
249 sqlStatement("UPDATE claims SET $claimset WHERE " .
250 "patient_id = '$patient_id' AND encounter_id = '$encounter_id' AND " .
251 // "payer_id = '" . $row['payer_id'] . "' AND " .
252 "version = '" . $row['version'] . "'");
255 // Whenever a claim is marked billed, update A/R accordingly.
258 if ($payer_type > 0) {
259 sqlStatement("UPDATE form_encounter SET " .
260 "last_level_billed = '$payer_type' WHERE " .
261 "pid = '$patient_id' AND encounter = '$encounter_id'");
268 // Determine if the encounter is billed. It is considered billed if it
269 // has at least one chargeable item, and all of them are billed.
271 function isEncounterBilled($pid, $encounter)
273 $billed = -1; // no chargeable services yet
275 $bres = sqlStatement(
277 "billing.billed FROM billing, code_types WHERE " .
278 "billing.pid = ? AND " .
279 "billing.encounter = ? AND " .
280 "billing.activity = 1 AND " .
281 "code_types.ct_key = billing.code_type AND " .
282 "code_types.ct_fee = 1 " .
284 "SELECT billed FROM drug_sales WHERE " .
287 array($pid, $encounter, $pid, $encounter)
290 while ($brow = sqlFetchArray($bres)) {
291 if ($brow['billed'] == 0) {
303 // Get the co-pay amount that is effective on the given date.
304 // Or if no insurance on that date, return -1.
306 function getCopay($patient_id, $encdate)
308 $tmp = sqlQuery("SELECT provider, copay FROM insurance_data " .
309 "WHERE pid = '$patient_id' AND type = 'primary' " .
310 "AND date <= '$encdate' ORDER BY date DESC LIMIT 1");
311 if ($tmp['provider']) {
312 return sprintf('%01.2f', 0 + $tmp['copay']);
318 // Get the total co-pay amount paid by the patient for an encounter
319 function getPatientCopay($patient_id, $encounter)
321 $resMoneyGot = sqlStatement(
322 "SELECT sum(pay_amount) as PatientPay FROM ar_activity where ".
323 "pid = ? and encounter = ? and payer_type=0 and account_code='PCP'",
324 array($patient_id,$encounter)
326 //new fees screen copay gives account_code='PCP'
327 $rowMoneyGot = sqlFetchArray($resMoneyGot);
328 $Copay=$rowMoneyGot['PatientPay'];
332 // Get the "next invoice reference number" from this user's pool of reference numbers.
334 function getInvoiceRefNumber()
338 "FROM users AS u, list_options AS lo " .
339 "WHERE u.username = ? AND " .
340 "lo.list_id = 'irnpool' AND lo.option_id = u.irnpool AND lo.activity = 1 LIMIT 1",
341 array($_SESSION['authUser'])
343 return empty($trow['notes']) ? '' : $trow['notes'];
346 // Increment the "next invoice reference number" of this user's pool.
347 // This identifies the "digits" portion of that number and adds 1 to it.
348 // If it contains more than one string of digits, the last is used.
350 function updateInvoiceRefNumber()
352 $irnumber = getInvoiceRefNumber();
353 // Here "?" specifies a minimal match, to get the most digits possible:
354 if (preg_match('/^(.*?)(\d+)(\D*)$/', $irnumber, $matches)) {
355 $newdigs = sprintf('%0' . strlen($matches[2]) . 'd', $matches[2] + 1);
356 $newnumber = $matches[1] . $newdigs . $matches[3];
358 "UPDATE users AS u, list_options AS lo " .
359 "SET lo.notes = ? WHERE " .
360 "u.username = ? AND " .
361 "lo.list_id = 'irnpool' AND lo.option_id = u.irnpool",
362 array($newnumber, $_SESSION['authUser'])
369 // Common function for voiding a receipt or checkout. When voiding a checkout you can specify
370 // $time as a timestamp (yyyy-mm-dd hh:mm:ss) or 'all'; default is the last checkout.
372 function doVoid($patient_id, $encounter_id, $purge = false, $time = '')
374 $what_voided = $purge ? 'checkout' : 'receipt';
380 // Get last checkout timestamp.
382 "(SELECT bill_date FROM billing WHERE " .
383 "pid = ? AND encounter = ? AND activity = 1 AND bill_date IS NOT NULL) " .
385 "(SELECT bill_date FROM drug_sales WHERE " .
386 "pid = ? AND encounter = ? AND bill_date IS NOT NULL) " .
387 "ORDER BY bill_date DESC LIMIT 1",
388 array($patient_id, $encounter_id, $patient_id, $encounter_id)
390 if (!empty($corow['bill_date'])) {
391 $date_original = $corow['bill_date'];
393 } else if ($time == 'all') {
395 "SELECT SUM(pay_amount) AS payments, " .
396 "SUM(adj_amount) AS adjustments FROM ar_activity WHERE " .
397 "pid = ? AND encounter = ?",
398 array($patient_id, $encounter_id)
400 $adjustments = $row['adjustments'];
401 $payments = $row['payments'];
403 $date_original = $time;
406 // Get its charges and adjustments.
407 if ($date_original) {
409 "SELECT SUM(pay_amount) AS payments, " .
410 "SUM(adj_amount) AS adjustments FROM ar_activity WHERE " .
411 "pid = ? AND encounter = ? AND post_time = ?",
412 array($patient_id, $encounter_id, $date_original)
414 $adjustments = $row['adjustments'];
415 $payments = $row['payments'];
418 // Get old invoice reference number.
420 "SELECT invoice_refno FROM form_encounter WHERE " .
421 "pid = ? AND encounter = ? LIMIT 1",
422 array($patient_id, $encounter_id)
424 $old_invoice_refno = $encrow['invoice_refno'];
426 $usingirnpools = getInvoiceRefNumber();
427 // If not (undoing a checkout or using IRN pools), nothing is done.
428 if ($purge || $usingirnpools) {
429 $query = "INSERT INTO voids SET " .
431 "encounter_id = ?, " .
432 "what_voided = ?, " .
433 "date_voided = NOW(), " .
438 $sqlarr = array($patient_id, $encounter_id, $what_voided, $_SESSION['authUserID'], $row['adjustments'],
439 $row['payments'], $old_invoice_refno);
440 if ($date_original) {
441 $query .= ", date_original = ?";
442 $sqlarr[] = $date_original;
445 sqlStatement($query, $sqlarr);
449 // Purge means delete adjustments and payments from the last checkout
450 // and re-open the visit.
451 if ($date_original) {
453 "DELETE FROM ar_activity WHERE " .
454 "pid = ? AND encounter = ? AND post_time = ?",
455 array($patient_id, $encounter_id, $date_original)
458 "UPDATE billing SET billed = 0, bill_date = NULL WHERE " .
459 "pid = ? AND encounter = ? AND activity = 1 AND " .
460 "bill_date IS NOT NULL AND bill_date = ?",
461 array($patient_id, $encounter_id, $date_original)
464 "update drug_sales SET billed = 0, bill_date = NULL WHERE " .
465 "pid = ? AND encounter = ? AND " .
466 "bill_date IS NOT NULL AND bill_date = ?",
467 array($patient_id, $encounter_id, $date_original)
470 if ($time == 'all') {
472 "DELETE FROM ar_activity WHERE " .
473 "pid = ? AND encounter = ?",
474 array($patient_id, $encounter_id)
479 "UPDATE billing SET billed = 0, bill_date = NULL WHERE " .
480 "pid = ? AND encounter = ? AND activity = 1",
481 array($patient_id, $encounter_id)
484 "update drug_sales SET billed = 0, bill_date = NULL WHERE " .
485 "pid = ? AND encounter = ?",
486 array($patient_id, $encounter_id)
491 "UPDATE form_encounter SET last_level_billed = 0, " .
492 "last_level_closed = 0, stmt_count = 0, last_stmt_date = NULL " .
493 "WHERE pid = ? AND encounter = ?",
494 array($patient_id, $encounter_id)
496 } else if ($usingirnpools) {
497 // Non-purge means just assign a new invoice reference number.
498 $new_invoice_refno = add_escape_custom(updateInvoiceRefNumber());
500 "UPDATE form_encounter " .
501 "SET invoice_refno = ? " .
502 "WHERE pid = ? AND encounter = ?",
503 array($new_invoice_refno, $patient_id, $encounter_id)