sl_convert.php

   1 <?php
   2 // Copyright (C) 2008, 2009 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 // Disable PHP timeout.  This will not work in safe mode.
  15 ini_set('max_execution_time', '0');
  16
  17 $ignoreAuth=true; // no login required
  18
  19 require_once('interface/globals.php');
  20 require_once('library/sql-ledger.inc');
  21 require_once('library/invoice_summary.inc.php');
  22 require_once('library/sl_eob.inc.php');
  23
  24 $tmp = sqlQuery("SELECT count(*) AS count FROM ar_activity");
  25 if ($tmp['count']) die("ar_activity and ar_session must be empty to run this script!");
  26 ?>
  27 <html>
  28 <head>
  29 <title>OpenEMR Conversion from SQL-Ledger</title>
  30 <link rel='STYLESHEET' href='interface/themes/style_blue.css'>
  31 </head>
  32 <body>
  33 <span class='title'>OpenEMR Conversion from SQL-Ledger</span>
  34 <br><br>
  35 <span class='text'>
  36 <?php
  37 SLConnect();
  38
  39 echo "<p>Be patient, this will take a while...</p>";
  40 flush();
  41
  42 $invoice_count = 0;
  43 $activity_count = 0;
  44
  45 $res = SLQuery("SELECT id, invnumber, transdate, shipvia, intnotes " .
  46   "FROM ar ORDER BY id");
  47
  48 for ($irow = 0; $irow < SLRowCount($res); ++$irow) {
  49   $row = SLGetRow($res, $irow);
  50   list($pid, $encounter) = explode(".", $row['invnumber']);
  51   $copays = array();
  52   $provider_id = 0;
  53   $last_biller = 0;
  54   $svcdate = $row['transdate'];
  55
  56   // Scan billing table items to get the provider ID and copays.
  57   $bres = sqlStatement("SELECT * FROM billing WHERE " .
  58     "pid = '$pid' AND encounter = '$encounter' AND activity = 1 " .
  59     "AND billed = 1 AND fee != 0 ORDER BY fee DESC");
  60   while ($brow = sqlFetchArray($bres)) {
  61     if (!$provider_id) $provider_id = $brow['provider_id'];
  62     if (!$last_biller && !empty($brow['payer_id'])) $last_biller = $brow['payer_id'];
  63     if ($brow['code_type'] == 'COPAY') $copays[] = 0 - $brow['fee'];
  64   }
  65
  66   // Delete any TAX rows from billing for encounters in SQL-Ledger.
  67   sqlStatement("UPDATE billing SET activity = 0 WHERE " .
  68     "pid = '$pid' AND encounter = '$encounter' AND " .
  69     "code_type = 'TAX'");
  70
  71   $invlines = get_invoice_summary($row['id'], true);
  72
  73   // print_r($invlines); // debugging
  74
  75   ksort($invlines);
  76   foreach ($invlines as $codekey => $codeinfo) {
  77     ksort($codeinfo['dtl']);
  78     $code = strtoupper($codekey);
  79     if ($code == 'CO-PAY' || $code == 'UNKNOWN') $code = '';
  80
  81     foreach ($codeinfo['dtl'] as $dtlkey => $dtlinfo) {
  82       $dtldate = trim(substr($dtlkey, 0, 10));
  83       if (empty($dtldate)) {
  84         // Insert taxes but ignore other charges.
  85         if ($code == 'TAX') {
  86           sqlInsert("INSERT INTO billing ( date, encounter, code_type, code, code_text, " .
  87             "pid, authorized, user, groupname, activity, billed, provider_id, " .
  88             "modifier, units, fee, ndc_info, justify ) values ( " .
  89             "'$svcdate 00:00:00', '$encounter', 'TAX', 'TAX', '" .
  90             addslashes($dtlinfo['dsc']) . "', " .
  91             "'$pid', '1', '$provider_id', 'Default', 1, 1, $provider_id, '', '1', " .
  92             "'" . $dtlinfo['chg'] . "', '', '' )");
  93         }
  94         continue; // otherwise skip charges
  95       }
  96       $payer_id = empty($dtlinfo['ins']) ? 0 : $dtlinfo['ins'];
  97       $session_id = 0;
  98
  99       // Compute a reasonable "source" value.  For payments this will
 100       // commonly be a check number, for adjustments we have none.
 101       $source = empty($dtlinfo['src']) ? '' : $dtlinfo['src'];
 102       $source = preg_replace('!^Ins[123]/!i', '', $source);
 103       $source = preg_replace('!^Pt/!i', '', $source);
 104       if ($source == '' && empty($dtlinfo['pmt'])) {
 105         $source = 'From SQL-Ledger';
 106       }
 107
 108       // For insurance payers look up or create the session table entry.
 109       if ($payer_id) {
 110         $session_id = arGetSession($payer_id, addslashes($source), $dtldate);
 111       }
 112       // For non-insurance payers deal with copay duplication.
 113       else if ($code == '') {
 114         if (!empty($dtlinfo['pmt'])) {
 115           // Skip payments that are already present in the billing table as copays.
 116           foreach ($copays as $key => $value) {
 117             if ($value == $dtlinfo['pmt']) {
 118               unset($copays[$key]);
 119               continue 2; // skip this detail item
 120             }
 121           } // end foreach
 122         } // end if payment
 123       } // end not insurance
 124
 125       $payer_type = 0;
 126
 127       if (!empty($dtlinfo['pmt'])) { // it's a payment
 128         $tmp = strtolower($dtlinfo['src']);
 129         for ($i = 1; $i <= 3; ++$i) {
 130           if (strpos($tmp, "ins$i") !== false) $payer_type = $i;
 131         }
 132         arPostPayment($pid, $encounter, $session_id, $dtlinfo['pmt'], $code,
 133           $payer_type, $source, 0, "$dtldate 00:00:00");
 134         if ($session_id) {
 135           sqlStatement("UPDATE ar_session SET pay_total = pay_total + '" .
 136             $dtlinfo['pmt'] . "' WHERE session_id = '$session_id'");
 137         }
 138       }
 139       else { // it's an adjustment
 140         $tmp = strtolower($dtlinfo['rsn']);
 141         for ($i = 1; $i <= 3; ++$i) {
 142           if (strpos($tmp, "ins$i") !== false) $payer_type = $i;
 143         }
 144         arPostAdjustment($pid, $encounter, $session_id, 0 - $dtlinfo['chg'],
 145           $code, $payer_type, addslashes($dtlinfo['rsn']), 0, "$dtldate 00:00:00");
 146       }
 147
 148       ++$activity_count;
 149     } // end detail item
 150   } // end code
 151
 152
 153   // Compute last insurance level billed.
 154   $last_level_billed = 0;
 155   if ($last_biller) {
 156     $invdate = $row['transdate'];
 157     $tmp = sqlQuery("SELECT type FROM insurance_data WHERE " .
 158       "pid = '$patient_id' AND provider = '$last_biller' AND " .
 159       "date <= '$invdate' ORDER BY date DESC, id ASC LIMIT 1");
 160     $last_level_billed = ($tmp['type'] == 'tertiary') ?
 161       3 : ($tmp['type'] == 'secondary') ? 2 : 1;
 162   }
 163
 164   // Compute last insurance level closed.
 165   $last_level_closed = 0;
 166   $tmp = strtolower($row['shipvia']);
 167   for ($i = 1; $i <= 3; ++$i) {
 168     if (strpos($tmp, "ins$i") !== false) $last_level_closed = $i;
 169   }
 170
 171   // Compute last statement date and number of statements sent.
 172   $last_stmt_date = "NULL";
 173   $stmt_count = 0;
 174   $i = 0;
 175   $tmp = strtolower($row['intnotes']);
 176   while (($i = strpos($tmp, 'statement sent ', $i)) !== false) {
 177     $i += 15;
 178     $last_stmt_date = "'" . substr($tmp, $i, 10) . "'";
 179     ++$stmt_count;
 180   }
 181
 182   sqlStatement("UPDATE form_encounter SET " .
 183     "last_level_billed = '$last_level_billed', " .
 184     "last_level_closed = '$last_level_closed', " .
 185     "last_stmt_date = $last_stmt_date, " .
 186     "stmt_count = '$stmt_count' " .
 187     "WHERE pid = '$pid' AND encounter = '$encounter'");
 188
 189   // Show a warning for any unmatched copays.
 190   foreach ($copays as $copay) {
 191     echo "Co-pay of \$$copay in the encounter was not found in " .
 192       "SQL-Ledger invoice $pid.$encounter.<br />\n";
 193     flush();
 194   }
 195
 196   ++$invoice_count;
 197 } // end invoice
 198 SLClose();
 199 echo "<br />\n";
 200 echo "$invoice_count SQL-Ledger invoices were processed.<br />\n";
 201 echo "$activity_count payments and adjustments were posted.<br />\n";
 202 ?>
 203 </span>
 204
 205 </body>
 206 </html>