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
  34 function get_invoice_summary($trans_id, $with_detail = false) {
  35   global $sl_err, $sl_cash_acc;
  36
  37   $codes = array();
  38
  39   $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
  40   if ($sl_err) die($sl_err);
  41   if (! $chart_id_cash) die("There is no COA entry for cash account '$sl_cash_acc'");
  42
  43   // Request all cash entries belonging to the invoice.
  44   $atres = SLQuery("select * from acc_trans where trans_id = $trans_id and chart_id = $chart_id_cash");
  45   if ($sl_err) die($sl_err);
  46
  47   // Deduct payments for each procedure code from the respective balance owed.
  48   $keysuffix = 5000;
  49   for ($irow = 0; $irow < SLRowCount($atres); ++$irow) {
  50     $row = SLGetRow($atres, $irow);
  51     $code = strtoupper($row['memo']);
  52     $ins_id = $row['project_id'];
  53     if (! $code) $code = "Unknown";
  54     $amount = $row['amount'];
  55     $codes[$code]['bal'] += $amount; // amount is negative for a payment
  56     if ($ins_id)
  57       $codes[$code]['ins'] = $ins_id;
  58
  59     // Add the details if they want 'em.
  60     if ($with_detail) {
  61       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
  62       $tmpkey = $row['transdate'] . $keysuffix++;
  63       $tmp = array();
  64       $tmp['pmt'] = 0 - $amount;
  65       $tmp['src'] = $row['source'];
  66       if ($ins_id) $tmp['ins'] = $ins_id;
  67       $codes[$code]['dtl'][$tmpkey] = $tmp;
  68     }
  69   }
  70
  71   // Request all line items with money or adjustment reasons belonging
  72   // to the invoice.
  73   $inres = SLQuery("SELECT * FROM invoice WHERE trans_id = $trans_id AND " .
  74     "( sellprice != 0 OR description LIKE 'Adjustment%' OR serialnumber = 'Claim' )");
  75   if ($sl_err) die($sl_err);
  76
  77   // Add charges and adjustments for each procedure code into its total and balance.
  78   $keysuffix = 1000;
  79   for ($irow = 0; $irow < SLRowCount($inres); ++$irow) {
  80     $row = SLGetRow($inres, $irow);
  81     // $amount = $row['sellprice'];
  82     $amount = sprintf('%01.2f', $row['sellprice'] * $row['qty']);
  83     $ins_id = $row['project_id'];
  84
  85     $code = "Unknown";
  86     if ($row['serialnumber'] == 'Claim') {
  87       $code = 'Claim';
  88     }
  89     else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['serialnumber'], $matches)) {
  90       $code = strtoupper($matches[1]);
  91     }
  92     else if (preg_match("/([A-Za-z0-9]\d\d\S*)/", $row['description'], $matches)) {
  93       $code = strtoupper($matches[1]);
  94     }
  95     else if (preg_match("/^TAX:/", $row['description'])) {
  96       $code = 'TAX';
  97     }
  98
  99     $codes[$code]['chg'] += $amount;
 100     $codes[$code]['bal'] += $amount;
 101     if ($amount < 0) $codes[$code]['adj'] -= $amount;
 102
 103     if ($ins_id)
 104       $codes[$code]['ins'] = $ins_id;
 105
 106     // Add the details if they want 'em.
 107     if ($with_detail) {
 108       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 109       if (preg_match("/^Adjustment\s*(\S*)\s*(.*)/", $row['description'], $matches)) {
 110         $tmpkey = str_pad($matches[1], 10) . $keysuffix++;
 111         $tmp = array();
 112         $tmp['chg'] = $amount;
 113         $tmp['rsn'] = $matches[2];
 114         if ($ins_id) $tmp['ins'] = $ins_id;
 115         $codes[$code]['dtl'][$tmpkey] = $tmp;
 116       }
 117       else {
 118         $tmpkey = "          " . $keysuffix++;
 119         $tmp = array();
 120         $tmp['chg'] = $amount;
 121         if ($code == 'TAX') $tmp['dsc'] = substr($row['description'], 4);
 122         $codes[$code]['dtl'][$tmpkey] = $tmp;
 123       }
 124     }
 125   }
 126
 127   return $codes;
 128 }
 129
 130 // Like the above, but for Integrated A/R.
 131 //
 132 function ar_get_invoice_summary($patient_id, $encounter_id, $with_detail = false) {
 133   $codes = array();
 134   $keysuff1 = 1000;
 135   $keysuff2 = 5000;
 136
 137   //Get CO-PAYs from ar_activity
 138   $resPCP = sqlStatement("
 139       SELECT post_time as date, co.code_type, co.code, co.modifier, co.code_text, pay_amount AS fee , units, account_code
 140       FROM ar_activity AS ar, codes AS co
 141       WHERE ar.code=co.code AND ar.modifier=co.modifier
 142       AND account_code = 'PCP'
 143       AND pid=? AND encounter=? ORDER BY  fee DESC,CODE,modifier
 144     ",array($patient_id,$encounter_id));
 145
 146   while ($rowPCP = sqlFetchArray($resPCP)) {
 147     $amount = sprintf('%01.2f', $rowPCP['fee']);
 148
 149     $code = 'CO-PAY';
 150     $codes[$code]['bal'] += $amount;
 151     if ($rowPCP['modifier']) $code .= ':' . strtoupper($rowPCP['modifier']);
 152     $codes[$code]['chg'] += $amount;
 153
 154     // Add the details if they want 'em.
 155     if ($with_detail) {
 156       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 157       $tmp = array();
 158         $tmp['pmt'] = 0 - $amount;
 159         $tmp['src'] = 'Pt Paid';
 160         $tmp['plv'] = 0;
 161         $tmp['tab'] = 'ar_activity';
 162         $tmp['id'] = $rowPCP['id'];
 163
 164         $tmpkey = substr($rowPCP['date'], 0, 10) . $keysuff2++;
 165       $tmp['code_text'] = $rowPCP['code_text'];//Z&H
 166       $tmp['code_value'] = $rowPCP['code'];//Z&H
 167           $tmp['modifier'] = $rowPCP['modifier'];
 168           $tmp['justify'] = $rowPCP['justify'];
 169       $codes[$code]['dtl'][$tmpkey] = $tmp;
 170     }
 171   }
 172
 173   // Get charges from services.
 174   $res = sqlStatement("SELECT " .
 175     "date, code_type, code, modifier, code_text, fee " .
 176     "FROM billing WHERE " .
 177     "pid = ? AND encounter = ? AND " .
 178     "activity = 1 AND fee != 0.00 ORDER BY id", array($patient_id,$encounter_id) );
 179
 180   while ($row = sqlFetchArray($res)) {
 181     $amount = sprintf('%01.2f', $row['fee']);
 182
 183     if ($row['code_type'] == 'COPAY') {
 184       $code = 'CO-PAY';
 185       $codes[$code]['bal'] += $amount;
 186     }
 187     else {
 188       $code = strtoupper($row['code']);
 189       if (! $code) $code = "Unknown";
 190       if ($row['modifier']) $code .= ':' . strtoupper($row['modifier']);
 191       $codes[$code]['chg'] += $amount;
 192       $codes[$code]['bal'] += $amount;
 193     }
 194     // Add the details if they want 'em.
 195     if ($with_detail) {
 196       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 197       $tmp = array();
 198       if ($row['code_type'] == 'COPAY') {
 199         $tmp['pmt'] = 0 - $amount;
 200         $tmp['src'] = 'Pt Paid';
 201         $tmp['plv'] = 0;
 202         $tmpkey = substr($row['date'], 0, 10) . $keysuff2++;
 203       }
 204       else {
 205         $tmp['chg'] = $amount;
 206         $tmpkey = "          " . $keysuff1++;
 207       }
 208       $codes[$code]['dtl'][$tmpkey] = $tmp;
 209     }
 210   }
 211
 212   // Get charges from product sales.
 213   $query = "SELECT s.drug_id, s.sale_date, s.fee, s.quantity " .
 214     "FROM drug_sales AS s " .
 215     "WHERE " .
 216     "s.pid = ? AND s.encounter = ? AND s.fee != 0 " .
 217     "ORDER BY s.sale_id";
 218   $res = sqlStatement($query, array($patient_id,$encounter_id) );
 219   while ($row = sqlFetchArray($res)) {
 220     $amount = sprintf('%01.2f', $row['fee']);
 221     $code = 'PROD:' . $row['drug_id'];
 222     $codes[$code]['chg'] += $amount;
 223     $codes[$code]['bal'] += $amount;
 224     // Add the details if they want 'em.
 225     if ($with_detail) {
 226       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 227       $tmp = array();
 228       $tmp['chg'] = $amount;
 229       $tmpkey = "          " . $keysuff1++;
 230       $codes[$code]['dtl'][$tmpkey] = $tmp;
 231     }
 232   }
 233
 234   // Get payments and adjustments.
 235   $res = sqlStatement("SELECT " .
 236     "a.code, a.modifier, a.memo, a.payer_type, a.adj_amount, a.pay_amount, a.reason_code, " .
 237     "a.post_time, a.session_id, a.sequence_no, " .
 238     "s.payer_id, s.reference, s.check_date, s.deposit_date " .
 239     ",i.name " .
 240     "FROM ar_activity AS a " .
 241     "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 242     "LEFT OUTER JOIN insurance_companies AS i ON i.id = s.payer_id " .
 243     "WHERE a.pid = ? AND a.encounter = ? " .
 244     "ORDER BY s.check_date, a.sequence_no", array($patient_id,$encounter_id) );
 245   while ($row = sqlFetchArray($res)) {
 246     $code = strtoupper($row['code']);
 247     if (! $code) $code = "Unknown";
 248     if ($row['modifier']) $code .= ':' . strtoupper($row['modifier']);
 249     $ins_id = 0 + $row['payer_id'];
 250     $codes[$code]['bal'] -= $row['pay_amount'];
 251     $codes[$code]['bal'] -= $row['adj_amount'];
 252     $codes[$code]['chg'] -= $row['adj_amount'];
 253     $codes[$code]['adj'] += $row['adj_amount'];
 254     if ($ins_id) $codes[$code]['ins'] = $ins_id;
 255     // Add the details if they want 'em.
 256     if ($with_detail) {
 257       if (! $codes[$code]['dtl']) $codes[$code]['dtl'] = array();
 258       $tmp = array();
 259       $paydate = empty($row['deposit_date']) ? substr($row['post_time'], 0, 10) : $row['deposit_date'];
 260       if ($row['pay_amount'] != 0) $tmp['pmt'] = $row['pay_amount'];
 261       if ( isset($row['reason_code'] ) ) {
 262         $tmp['msp'] = $row['reason_code'];
 263       }
 264       if ($row['adj_amount'] != 0 || $row['pay_amount'] == 0) {
 265         $tmp['chg'] = 0 - $row['adj_amount'];
 266         // $tmp['rsn'] = (empty($row['memo']) || empty($row['session_id'])) ? 'Unknown adjustment' : $row['memo'];
 267         $tmp['rsn'] = empty($row['memo']) ? 'Unknown adjustment' : $row['memo'];
 268         $tmpkey = $paydate . $keysuff1++;
 269       }
 270       else {
 271         $tmpkey = $paydate . $keysuff2++;
 272       }
 273       $tmp['src'] = empty($row['session_id']) ? $row['memo'] : $row['reference'];
 274       $tmp['insurance_company'] = substr($row['name'], 0, 10);
 275       if ($ins_id) $tmp['ins'] = $ins_id;
 276       $tmp['plv'] = $row['payer_type'];
 277       $tmp['arseq'] = $row['sequence_no'];
 278       $codes[$code]['dtl'][$tmpkey] = $tmp;
 279     }
 280   }
 281   return $codes;
 282 }
 283
 284 // This determines the party from whom payment is currently expected.
 285 // Returns: -1=Nobody, 0=Patient, 1=Ins1, 2=Ins2, 3=Ins3.
 286 //
 287 function responsible_party($trans_id) {
 288   global $sl_err;
 289   $arres = SLQuery("select * from ar where id = $trans_id");
 290   if ($sl_err) die($sl_err);
 291   $arrow = SLGetRow($arres, 0);
 292   if (! $arrow) die(xl("There is no match for invoice id = ") . $trans_id);
 293   if ($arrow['paid'] >= $arrow['netamount']) return -1;
 294   $insgot  = strtolower($arrow['notes']);
 295   $insdone = strtolower($arrow['shipvia']);
 296   for ($i = 1; $i <= 3; ++$i) {
 297     $lcvalue = "ins$i";
 298     if (strpos($insgot, $lcvalue) !== false && strpos($insdone, $lcvalue) === false)
 299       return $i;
 300   }
 301   return 0;
 302 }
 303
 304 // As above but for Integrated A/R.
 305 //
 306 function ar_responsible_party($patient_id, $encounter_id) {
 307   $row = sqlQuery("SELECT date, last_level_billed, last_level_closed " .
 308     "FROM form_encounter WHERE " .
 309     "pid = ? AND encounter = ? " .
 310     "ORDER BY id DESC LIMIT 1", array($patient_id,$encounter_id) );
 311   if (empty($row)) return -1;
 312   $next_level = $row['last_level_closed'] + 1;
 313   if ($next_level <= $row['last_level_billed'])
 314     return $next_level;
 315   if (arGetPayerID($patient_id, substr($row['date'], 0, 10), $next_level))
 316     return $next_level;
 317   // There is no unclosed insurance, so see if there is an unpaid balance.
 318   // Currently hoping that form_encounter.balance_due can be discarded.
 319   $balance = 0;
 320   $codes = ar_get_invoice_summary($patient_id, $encounter_id);
 321   foreach ($codes as $cdata) $balance += $cdata['bal'];
 322   if ($balance > 0) return 0;
 323   return -1;
 324 }
 325 ?>