library/invoice_summary.inc.php

   1 <?php
   2 // Copyright (C) 2005-2010 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 // This returns an associative array keyed on procedure code, representing
  10 // all charge items for one invoice.  This array's values are themselves
  11 // associative arrays having the following keys:
  12 //
  13 //  chg - the sum of line items, including adjustments, for the code
  14 //  bal - the unpaid balance
  15 //  adj - the (positive) sum of inverted adjustments
  16 //  ins - the id of the insurance company that was billed (obsolete)
  17 //  dtl - associative array of details, if requested
  18 //
  19 // Where details are requested, each dtl array is keyed on a string
  20 // beginning with a date in yyyy-mm-dd format, or blanks in the case
  21 // of the original charge items.  The value array is:
  22 //
  23 //  pmt - payment amount as a positive number, only for payments
  24 //  src - check number or other source, only for payments
  25 //  chg - invoice line item amount amount, only for charges or
  26 //        adjustments (adjustments may be zero)
  27 //  rsn - adjustment reason, only for adjustments
  28 //  plv - provided for "integrated A/R" only: 0=pt, 1=Ins1, etc.
  29 //  dsc - for tax charges, a description of the tax
  30 //  arseq - ar_activity.sequence_no when it applies.
  31
  32 require_once("sl_eob.inc.php");
  33 require_once(dirname(__FILE__) . "/../custom/code_types.inc.php");
  34
  35 function get_invoice_summary($trans_id, $with_detail = false) {
  36   global $sl_err, $sl_cash_acc;
  37
  38   $codes = array();
  39
  40   $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
  41   if ($sl_err) die($sl_err);
  42   if (! $chart_id_cash) die("There is no COA entry for cash account '$sl_cash_acc'");
  43
  44   // Request all cash entries belonging to the invoice.
  45   $atres = SLQuery("select * from acc_trans where trans_id = $trans_id and chart_id = $chart_id_cash");
  46   if ($sl_err) die($sl_err);
  47
  48   // Deduct payments for each procedure code from the respective balance owed.
  49   $keysuffix = 5000;
  50   for ($irow = 0; $irow < SLRowCount($atres); ++$irow) {
  51     $row = SLGetRow($atres, $irow);
  52     $code = strtoupper($row['memo']);
  53     $ins_id = $row['project_id'];
  54     if (! $code) $code = "Unknown";
  55     $amount = $row['amount'];
  56     $codes[$code]['bal'] += $amount; // amount is negative for a payment
  57     if ($ins_id)
  58       $codes[$code]['ins'] = $ins_id;
  59
  60     // Add the details if they want 'em.
  61     if ($with_detail) {
  62       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
  63       $tmpkey = $row['transdate'] . $keysuffix++;
  64       $tmp = array();
  65       $tmp['pmt'] = 0 - $amount;
  66       $tmp['src'] = $row['source'];
  67       if ($ins_id) $tmp['ins'] = $ins_id;
  68       $codes[$code]['dtl'][$tmpkey] = $tmp;
  69     }
  70   }
  71
  72   // Request all line items with money or adjustment reasons belonging
  73   // to the invoice.
  74   $inres = SLQuery("SELECT * FROM invoice WHERE trans_id = $trans_id AND " .
  75     "( sellprice != 0 OR description LIKE 'Adjustment%' OR serialnumber = 'Claim' )");
  76   if ($sl_err) die($sl_err);
  77
  78   // Add charges and adjustments for each procedure code into its total and balance.
  79   $keysuffix = 1000;
  80   for ($irow = 0; $irow < SLRowCount($inres); ++$irow) {
  81     $row = SLGetRow($inres, $irow);
  82     // $amount = $row['sellprice'];
  83     $amount = sprintf('%01.2f', $row['sellprice'] * $row['qty']);
  84     $ins_id = $row['project_id'];
  85
  86     $code = "Unknown";
  87     if ($row['serialnumber'] == 'Claim') {
  88       $code = 'Claim';
  89     }
  90     else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['serialnumber'], $matches)) {
  91       $code = strtoupper($matches[1]);
  92     }
  93     else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['description'], $matches)) {
  94       $code = strtoupper($matches[1]);
  95     }
  96     else if (preg_match("/^TAX:/", $row['description'])) {
  97       $code = 'TAX';
  98     }
  99
 100     $codes[$code]['chg'] += $amount;
 101     $codes[$code]['bal'] += $amount;
 102     if ($amount < 0) $codes[$code]['adj'] -= $amount;
 103
 104     if ($ins_id)
 105       $codes[$code]['ins'] = $ins_id;
 106
 107     // Add the details if they want 'em.
 108     if ($with_detail) {
 109       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 110       if (preg_match("/^Adjustment\s*(\S*)\s*(.*)/", $row['description'], $matches)) {
 111         $tmpkey = str_pad($matches[1], 10) . $keysuffix++;
 112         $tmp = array();
 113         $tmp['chg'] = $amount;
 114         $tmp['rsn'] = $matches[2];
 115         if ($ins_id) $tmp['ins'] = $ins_id;
 116         $codes[$code]['dtl'][$tmpkey] = $tmp;
 117       }
 118       else {
 119         $tmpkey = "          " . $keysuffix++;
 120         $tmp = array();
 121         $tmp['chg'] = $amount;
 122         if ($code == 'TAX') $tmp['dsc'] = substr($row['description'], 4);
 123         $codes[$code]['dtl'][$tmpkey] = $tmp;
 124       }
 125     }
 126   }
 127
 128   return $codes;
 129 }
 130
 131 // Like the above, but for Integrated A/R.
 132 //
 133 function ar_get_invoice_summary($patient_id, $encounter_id, $with_detail = false) {
 134   $codes = array();
 135   $keysuff1 = 1000;
 136   $keysuff2 = 5000;
 137
 138   // Get charges from services.
 139   $res = sqlStatement("SELECT " .
 140     "date, code_type, code, modifier, code_text, fee " .
 141     "FROM billing WHERE " .
 142     "pid = ? AND encounter = ? AND " .
 143     "activity = 1 AND fee != 0.00 ORDER BY id", array($patient_id,$encounter_id) );
 144
 145   while ($row = sqlFetchArray($res)) {
 146     $amount = sprintf('%01.2f', $row['fee']);
 147
 148       $code = $row['code'];
 149       if (! $code) $code = "Unknown";
 150       if ($row['modifier']) $code .= ':' . $row['modifier'];
 151       $codes[$code]['chg'] += $amount;
 152       $codes[$code]['bal'] += $amount;
 153
 154     // Pass the code type, code and code_text fields
 155     // Although not all used yet, useful information
 156     // to improve the statement reporting etc.
 157     $codes[$code]['code_type'] = $row['code_type'];
 158     $codes[$code]['code_value'] = $row['code'];
 159     $codes[$code]['modifier'] = $row['modifier'];
 160     $codes[$code]['code_text'] = $row['code_text'];
 161
 162     // Add the details if they want 'em.
 163     if ($with_detail) {
 164       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 165       $tmp = array();
 166       $tmp['chg'] = $amount;
 167       $tmpkey = "          " . $keysuff1++;
 168       $codes[$code]['dtl'][$tmpkey] = $tmp;
 169     }
 170   }
 171
 172   // Get charges from product sales.
 173   $query = "SELECT s.drug_id, s.sale_date, s.fee, s.quantity " .
 174     "FROM drug_sales AS s " .
 175     "WHERE " .
 176     "s.pid = ? AND s.encounter = ? AND s.fee != 0 " .
 177     "ORDER BY s.sale_id";
 178   $res = sqlStatement($query, array($patient_id,$encounter_id) );
 179   while ($row = sqlFetchArray($res)) {
 180     $amount = sprintf('%01.2f', $row['fee']);
 181     $code = 'PROD:' . $row['drug_id'];
 182     $codes[$code]['chg'] += $amount;
 183     $codes[$code]['bal'] += $amount;
 184     // Add the details if they want 'em.
 185     if ($with_detail) {
 186       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 187       $tmp = array();
 188       $tmp['chg'] = $amount;
 189       $tmpkey = "          " . $keysuff1++;
 190       $codes[$code]['dtl'][$tmpkey] = $tmp;
 191     }
 192   }
 193
 194   // Get payments and adjustments. (includes copays)
 195   $res = sqlStatement("SELECT " .
 196     "a.code_type, a.code, a.modifier, a.memo, a.payer_type, a.adj_amount, a.pay_amount, a.reason_code, " .
 197     "a.post_time, a.session_id, a.sequence_no, a.account_code, " .
 198     "s.payer_id, s.reference, s.check_date, s.deposit_date " .
 199     ",i.name " .
 200     "FROM ar_activity AS a " .
 201     "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 202     "LEFT OUTER JOIN insurance_companies AS i ON i.id = s.payer_id " .
 203     "WHERE a.pid = ? AND a.encounter = ? " .
 204     "ORDER BY s.check_date, a.sequence_no", array($patient_id,$encounter_id) );
 205   while ($row = sqlFetchArray($res)) {
 206     $code = $row['code'];
 207     if (! $code) $code = "Unknown";
 208     if ($row['modifier']) $code .= ':' . $row['modifier'];
 209     $ins_id = 0 + $row['payer_id'];
 210     $codes[$code]['bal'] -= $row['pay_amount'];
 211     $codes[$code]['bal'] -= $row['adj_amount'];
 212     $codes[$code]['chg'] -= $row['adj_amount'];
 213     $codes[$code]['adj'] += $row['adj_amount'];
 214     if ($ins_id) $codes[$code]['ins'] = $ins_id;
 215     // Add the details if they want 'em.
 216     if ($with_detail) {
 217       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 218       $tmp = array();
 219       $paydate = empty($row['deposit_date']) ? substr($row['post_time'], 0, 10) : $row['deposit_date'];
 220       if ($row['pay_amount'] != 0) $tmp['pmt'] = $row['pay_amount'];
 221       if ( isset($row['reason_code'] ) ) {
 222         $tmp['msp'] = $row['reason_code'];
 223       }
 224       if ($row['adj_amount'] != 0 || $row['pay_amount'] == 0) {
 225         $tmp['chg'] = 0 - $row['adj_amount'];
 226         // $tmp['rsn'] = (empty($row['memo']) || empty($row['session_id'])) ? 'Unknown adjustment' : $row['memo'];
 227         $tmp['rsn'] = empty($row['memo']) ? 'Unknown adjustment' : $row['memo'];
 228         $tmpkey = $paydate . $keysuff1++;
 229       }
 230       else {
 231         $tmpkey = $paydate . $keysuff2++;
 232       }
 233       if ($row['account_code'] == "PCP") {
 234         //copay
 235         $tmp['src'] = 'Pt Paid';
 236       }
 237       else {
 238         $tmp['src'] = empty($row['session_id']) ? $row['memo'] : $row['reference'];
 239       }
 240       $tmp['insurance_company'] = substr($row['name'], 0, 10);
 241       if ($ins_id) $tmp['ins'] = $ins_id;
 242       $tmp['plv'] = $row['payer_type'];
 243       $tmp['arseq'] = $row['sequence_no'];
 244       $codes[$code]['dtl'][$tmpkey] = $tmp;
 245     }
 246   }
 247   return $codes;
 248 }
 249
 250 // This determines the party from whom payment is currently expected.
 251 // Returns: -1=Nobody, 0=Patient, 1=Ins1, 2=Ins2, 3=Ins3.
 252 //
 253 function responsible_party($trans_id) {
 254   global $sl_err;
 255   $arres = SLQuery("select * from ar where id = $trans_id");
 256   if ($sl_err) die($sl_err);
 257   $arrow = SLGetRow($arres, 0);
 258   if (! $arrow) die(xl("There is no match for invoice id = ") . $trans_id);
 259   if ($arrow['paid'] >= $arrow['netamount']) return -1;
 260   $insgot  = strtolower($arrow['notes']);
 261   $insdone = strtolower($arrow['shipvia']);
 262   for ($i = 1; $i <= 3; ++$i) {
 263     $lcvalue = "ins$i";
 264     if (strpos($insgot, $lcvalue) !== false && strpos($insdone, $lcvalue) === false)
 265       return $i;
 266   }
 267   return 0;
 268 }
 269
 270 // As above but for Integrated A/R.
 271 //
 272 function ar_responsible_party($patient_id, $encounter_id) {
 273   $row = sqlQuery("SELECT date, last_level_billed, last_level_closed " .
 274     "FROM form_encounter WHERE " .
 275     "pid = ? AND encounter = ? " .
 276     "ORDER BY id DESC LIMIT 1", array($patient_id,$encounter_id) );
 277   if (empty($row)) return -1;
 278   $next_level = $row['last_level_closed'] + 1;
 279   if ($next_level <= $row['last_level_billed'])
 280     return $next_level;
 281   if (arGetPayerID($patient_id, substr($row['date'], 0, 10), $next_level))
 282     return $next_level;
 283   // There is no unclosed insurance, so see if there is an unpaid balance.
 284   // Currently hoping that form_encounter.balance_due can be discarded.
 285   $balance = 0;
 286   $codes = ar_get_invoice_summary($patient_id, $encounter_id);
 287   foreach ($codes as $cdata) $balance += $cdata['bal'];
 288   if ($balance > 0) return 0;
 289   return -1;
 290 }
 291 ?>