sl_convert.php

   1 <?php
   2 // Copyright (C) 2008-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 may be run after an upgraded OpenEMR has been installed.
  10 // Its purpose is to extract A/R information from SQL-Ledger and
  11 // convert it to the OpenEMR tables that maintain A/R internally,
  12 // thus eliminating SQL-Ledger.
  13
  14 // Significant changes were made around November 2009: SQL-Ledger
  15 // data is now considered authoritative, and the billing table is
  16 // modified to reflect that.  This is so that financial reports in
  17 // the new system will (hopefully) match up with the old system.
  18 // Discrepancies are logged to the display during conversion.
  19
  20 // Disable PHP timeout.  This will not work in safe mode.
  21 ini_set('max_execution_time', '0');
  22
  23 $ignoreAuth=true; // no login required
  24
  25 require_once('interface/globals.php');
  26 require_once('library/sql-ledger.inc');
  27 require_once('library/invoice_summary.inc.php');
  28 require_once('library/sl_eob.inc.php');
  29
  30 // Set this to true to skip all database changes.
  31 $dry_run = false;
  32
  33 if (!$dry_run) {
  34   $tmp = sqlQuery("SELECT count(*) AS count FROM ar_activity");
  35   if ($tmp['count']) die("ar_activity and ar_session must be empty to run this script!");
  36 }
  37 ?>
  38 <html>
  39 <head>
  40 <title>OpenEMR Conversion from SQL-Ledger</title>
  41 <link rel='STYLESHEET' href='interface/themes/style_blue.css'>
  42 </head>
  43 <body>
  44 <span class='title'>OpenEMR Conversion from SQL-Ledger</span>
  45 <br><br>
  46 <span class='text'>
  47 <?php
  48 SLConnect();
  49
  50 echo "<p>Be patient, this will take a while...</p>";
  51 flush();
  52
  53 // This marker will eventually tell us which encounters have no
  54 // matching invoice.
  55 if (!$dry_run) {
  56   sqlStatement("UPDATE form_encounter SET last_level_billed = -1");
  57 }
  58
  59 $invoice_count = 0;
  60 $activity_count = 0;
  61
  62 $res = SLQuery("SELECT id, invnumber, transdate, shipvia, intnotes " .
  63   "FROM ar WHERE invnumber LIKE '%.%' ORDER BY id");
  64
  65 for ($irow = 0; $irow < SLRowCount($res); ++$irow) {
  66   $row = SLGetRow($res, $irow);
  67   list($pid, $encounter) = explode(".", $row['invnumber']);
  68
  69   $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
  70     "pid = '$pid' AND encounter = '$encounter'");
  71   if ($tmp['count'] == 0) {
  72     echo "SQL-Ledger invoice $pid.$encounter has no matching encounter " .
  73       "and is ignored. This will affect financial reports!<br />\n";
  74     continue;
  75   }
  76
  77   $billing = array();
  78   $provider_id = 0;
  79   $last_biller = 0;
  80   $svcdate = $row['transdate'];
  81
  82   if (!$dry_run) {
  83     // Delete any TAX rows from billing for encounters in SQL-Ledger.
  84     sqlStatement("UPDATE billing SET activity = 0 WHERE " .
  85       "pid = '$pid' AND encounter = '$encounter' AND " .
  86       "code_type = 'TAX'");
  87   }
  88
  89   // Get all billing table items with money for this encounter, and
  90   // compute provider ID and billing status.
  91   $bres = sqlStatement("SELECT * FROM billing WHERE " .
  92     "pid = '$pid' AND encounter = '$encounter' AND activity = 1 " .
  93     "AND code_type != 'TAX' AND fee != 0 ORDER BY fee DESC");
  94   while ($brow = sqlFetchArray($bres)) {
  95     if (!$provider_id) $provider_id = $brow['provider_id'];
  96     if (!$last_biller && $brow['billed'] && !empty($brow['payer_id']))
  97       $last_biller = $brow['payer_id'];
  98     $billing[$brow['id']] = $brow;
  99   }
 100
 101   // Get invoice details.
 102   $invlines = get_invoice_summary($row['id'], true);
 103   // print_r($invlines); // debugging
 104   ksort($invlines);
 105
 106   // For each line item or payment from the invoice...
 107   foreach ($invlines as $codekey => $codeinfo) {
 108     ksort($codeinfo['dtl']);
 109     $code = strtoupper($codekey);
 110     if ($code == 'CO-PAY' || $code == 'UNKNOWN') $code = '';
 111
 112     $is_product = substr($code, 0, 5) == 'PROD:';
 113
 114     $codeonly = $code;
 115     $modifier = '';
 116     $tmp = explode(":", $code);
 117     if (!empty($tmp[1])) {
 118       $codeonly = $tmp[0];
 119       $modifier = $tmp[1];
 120     }
 121
 122     foreach ($codeinfo['dtl'] as $dtlkey => $dtlinfo) {
 123       $dtldate = trim(substr($dtlkey, 0, 10));
 124
 125       if (empty($dtldate)) { // if this is a charge
 126         $charge = $dtlinfo['chg'];
 127
 128         // Zero charges don't matter.
 129         if ($charge == 0) continue;
 130
 131         // Insert taxes but ignore other charges.
 132         if ($code == 'TAX') {
 133           if (!$dry_run) {
 134             sqlInsert("INSERT INTO billing ( date, encounter, code_type, code, code_text, " .
 135               "pid, authorized, user, groupname, activity, billed, provider_id, " .
 136               "modifier, units, fee, ndc_info, justify ) values ( " .
 137               "'$svcdate 00:00:00', '$encounter', 'TAX', 'TAX', '" .
 138               addslashes($dtlinfo['dsc']) . "', " .
 139               "'$pid', '1', '$provider_id', 'Default', 1, 1, 0, '', '1', " .
 140               "'$charge', '', '' )");
 141           }
 142         }
 143         else {
 144           // Non-tax charges for products are in the drug_sales table.
 145           // We won't bother trying to make sure they match the invoice.
 146           if ($is_product) continue;
 147
 148           // Look up this charge in the $billing array.
 149           // If found, remove it from the array and skip to the next detail item.
 150           // Otherwise add it to the billing table and log the discrepancy.
 151           $posskey = 0;
 152           foreach ($billing as $key => $brow) {
 153             $bcode = strtoupper($brow['code']);
 154             $bcodeonly = $bcode;
 155             if ($brow['modifier']) $bcode .= ':' . strtoupper($brow['modifier']);
 156             if ($bcode === $code && $brow['fee'] == $charge) {
 157               unset($billing[$key]);
 158               continue 2; // done with this detail item
 159             }
 160             else if (($bcodeonly === $codeonly || (empty($codeonly) && $charge != 0)) && $brow['fee'] == $charge) {
 161               $posskey = $key;
 162             }
 163           }
 164           if ($posskey) {
 165             // There was no exact match, but there was a match if the modifiers
 166             // are ignored or if the SL code is empty.  Good enough.
 167             unset($billing[$posskey]);
 168             continue;
 169           }
 170           // This charge is not in the billing table!
 171           $codetype = preg_match('/^[A-V]/', $code) ? 'HCPCS' : 'CPT4';
 172           // Note that get_invoice_summary() loses the code type.  The above
 173           // statement works for normal U.S. clinics, but sites that have
 174           // charges other than CPT4 and HCPCS will need to have their code
 175           // types for these generated entries, if any, fixed.
 176           if (!$dry_run) {
 177             sqlInsert("INSERT INTO billing ( date, encounter, code_type, code, code_text, " .
 178               "pid, authorized, user, groupname, activity, billed, provider_id, " .
 179               "modifier, units, fee, ndc_info, justify ) values ( " .
 180               "'$svcdate 00:00:00', '$encounter', '$codetype', '$codeonly',
 181               'Copied from SQL-Ledger by sl_convert.php', " .
 182               "'$pid', '1', '$provider_id', 'Default', 1, 1, 0, '$modifier', '1', " .
 183               "'$charge', '', '' )");
 184           }
 185           echo "Billing code '$code' with charge \$$charge was copied from " .
 186             "SQL-Ledger invoice $pid.$encounter.<br />\n";
 187           flush();
 188         } // end non-tax charge
 189
 190         // End charge item logic.  Continue to the next invoice detail item.
 191         continue;
 192
 193       } // end if charge
 194
 195       $payer_id = empty($dtlinfo['ins']) ? 0 : $dtlinfo['ins'];
 196       $session_id = 0;
 197
 198       // Compute a reasonable "source" value.  For payments this will
 199       // commonly be a check number, for adjustments we have none.
 200       $source = empty($dtlinfo['src']) ? '' : $dtlinfo['src'];
 201       $source = preg_replace('!^Ins[123]/!i', '', $source);
 202       $source = preg_replace('!^Pt/!i', '', $source);
 203       if ($source == '' && empty($dtlinfo['pmt'])) {
 204         $source = 'From SQL-Ledger';
 205       }
 206
 207       // For insurance payers look up or create the session table entry.
 208       if ($payer_id) {
 209         if (!$dry_run) {
 210           $session_id = arGetSession($payer_id, addslashes($source), $dtldate);
 211         }
 212       }
 213       // For non-insurance payers deal with copay duplication.
 214       else if ($code == '') {
 215         if (!empty($dtlinfo['pmt'])) {
 216           // Skip payments that are already present in the billing table as copays.
 217           foreach ($billing as $key => $brow) {
 218             if ($brow['code_type'] == 'COPAY' && (0 - $brow['fee']) == $dtlinfo['pmt']) {
 219               unset($billing[$key]);
 220               continue 2; // done with this detail item
 221             }
 222           }
 223         } // end if payment
 224       } // end not insurance
 225
 226       $payer_type = 0;
 227
 228       if (!empty($dtlinfo['pmt'])) { // it's a payment
 229         $tmp = strtolower($dtlinfo['src']);
 230         for ($i = 1; $i <= 3; ++$i) {
 231           if (strpos($tmp, "ins$i") !== false) $payer_type = $i;
 232         }
 233         if (!$dry_run) {
 234           arPostPayment($pid, $encounter, $session_id, $dtlinfo['pmt'], $code,
 235             $payer_type, addslashes($source), 0, "$dtldate 00:00:00");
 236           if ($session_id) {
 237             sqlStatement("UPDATE ar_session SET pay_total = pay_total + '" .
 238               $dtlinfo['pmt'] . "' WHERE session_id = '$session_id'");
 239           }
 240         }
 241       }
 242       else { // it's an adjustment
 243         $tmp = strtolower($dtlinfo['rsn']);
 244         for ($i = 1; $i <= 3; ++$i) {
 245           if (strpos($tmp, "ins$i") !== false) $payer_type = $i;
 246         }
 247         if (!$dry_run) {
 248           arPostAdjustment($pid, $encounter, $session_id, 0 - $dtlinfo['chg'],
 249             $code, $payer_type, addslashes($dtlinfo['rsn']), 0, "$dtldate 00:00:00");
 250         }
 251       }
 252
 253       ++$activity_count;
 254     } // end detail item
 255   } // end code
 256
 257   // Compute last insurance level billed.
 258   $last_level_billed = 0;
 259   if ($last_biller) {
 260     $invdate = $row['transdate'];
 261     $tmp = sqlQuery("SELECT type FROM insurance_data WHERE " .
 262       "pid = '$pid' AND provider = '$last_biller' AND " .
 263       "date <= '$invdate' ORDER BY date DESC, id ASC LIMIT 1");
 264     $last_level_billed = ($tmp['type'] == 'tertiary') ?
 265       3 : (($tmp['type'] == 'secondary') ? 2 : 1);
 266   }
 267
 268   // Compute last insurance level closed.
 269   $last_level_closed = 0;
 270   $tmp = strtolower($row['shipvia']);
 271   for ($i = 1; $i <= 3; ++$i) {
 272     if (strpos($tmp, "ins$i") !== false) $last_level_closed = $i;
 273   }
 274
 275   // Compute last statement date and number of statements sent.
 276   $last_stmt_date = "NULL";
 277   $stmt_count = 0;
 278   $i = 0;
 279   $tmp = strtolower($row['intnotes']);
 280   while (($i = strpos($tmp, 'statement sent ', $i)) !== false) {
 281     $i += 15;
 282     $last_stmt_date = "'" . substr($tmp, $i, 10) . "'";
 283     ++$stmt_count;
 284   }
 285
 286   if (!$dry_run) {
 287     sqlStatement("UPDATE form_encounter SET " .
 288       "last_level_billed = '$last_level_billed', " .
 289       "last_level_closed = '$last_level_closed', " .
 290       "last_stmt_date = $last_stmt_date, " .
 291       "stmt_count = '$stmt_count' " .
 292       "WHERE pid = '$pid' AND encounter = '$encounter'");
 293   }
 294
 295   // Delete and show a warning for any unmatched copays or charges.
 296   foreach ($billing as $key => $brow) {
 297     if (!$dry_run) {
 298       sqlStatement("UPDATE billing SET activity = 0 WHERE id = '$key'");
 299     }
 300     if ($brow['code_type'] == 'COPAY') {
 301       echo "Patient payment of \$" . sprintf('%01.2f', 0 - $brow['fee']);
 302     }
 303     else {
 304       echo "Charge item '" . $brow['code'] . "' with amount \$" .
 305         sprintf('%01.2f', $brow['fee']);
 306     }
 307     echo " was not found in SQL-Ledger invoice $pid.$encounter " .
 308       "and has been removed from the encounter.<br />\n";
 309     flush();
 310   }
 311
 312   ++$invoice_count;
 313 } // end invoice
 314 SLClose();
 315
 316 if (!$dry_run) {
 317   $feres = sqlStatement("SELECT * FROM form_encounter WHERE " .
 318     "last_level_billed = -1 ORDER BY pid, encounter");
 319   while ($ferow = sqlFetchArray($feres)) {
 320     $pid = 0 + $ferow['pid'];
 321     $encounter = 0 + $ferow['encounter'];
 322     $tmp = sqlQuery("SELECT sum(fee) AS sum FROM billing WHERE " .
 323       "pid = '$pid' AND encounter = '$encounter' AND " .
 324       "activity = 1 AND billed = 1");
 325     if ($tmp['sum'] != 0) {
 326       $sum = sprintf('%0.2f', 0 + $tmp['sum']);
 327       arPostAdjustment($pid, $encounter, 0, $sum,
 328         '', 0, 'Missing SL invoice', 0, date('Y-m-d') . ' 00:00:00');
 329       echo "Adjustment amount $sum was applied to write off billed " .
 330         "items in encounter $pid.$encounter because it has no matching " .
 331         "invoice.<br />\n";
 332     }
 333     sqlStatement("UPDATE form_encounter SET last_level_billed = 0 " .
 334       "WHERE id = '" . $ferow['id'] . "'");
 335   }
 336 }
 337
 338 echo "<br />\n";
 339 echo "$invoice_count SQL-Ledger invoices were processed.<br />\n";
 340 echo "$activity_count payments and adjustments were posted.<br />\n";
 341 ?>
 342 </span>
 343
 344 </body>
 345 </html>