fix to prior commit
[openemr.git] / sl_convert.php
blob69ea6e85d5845218d5e7e44f9a7199c96ba35152
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.
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.
20 // Disable PHP timeout. This will not work in safe mode.
21 ini_set('max_execution_time', '0');
23 $ignoreAuth=true; // no login required
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');
30 // Set this to true to skip all database changes.
31 $dry_run = false;
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!");
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();
50 echo "<p>Be patient, this will take a while...</p>";
51 flush();
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");
59 $invoice_count = 0;
60 $activity_count = 0;
62 $res = SLQuery("SELECT id, invnumber, transdate, shipvia, intnotes " .
63 "FROM ar WHERE invnumber LIKE '%.%' ORDER BY id");
65 for ($irow = 0; $irow < SLRowCount($res); ++$irow) {
66 $row = SLGetRow($res, $irow);
67 list($pid, $encounter) = explode(".", $row['invnumber']);
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;
77 $billing = array();
78 $provider_id = 0;
79 $last_biller = 0;
80 $svcdate = $row['transdate'];
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'");
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;
101 // Get invoice details.
102 $invlines = get_invoice_summary($row['id'], true);
103 // print_r($invlines); // debugging
104 ksort($invlines);
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 = '';
112 $is_product = substr($code, 0, 5) == 'PROD:';
114 $codeonly = $code;
115 $modifier = '';
116 $tmp = explode(":", $code);
117 if (!empty($tmp[1])) {
118 $codeonly = $tmp[0];
119 $modifier = $tmp[1];
122 foreach ($codeinfo['dtl'] as $dtlkey => $dtlinfo) {
123 $dtldate = trim(substr($dtlkey, 0, 10));
125 if (empty($dtldate)) { // if this is a charge
126 $charge = $dtlinfo['chg'];
128 // Zero charges don't matter.
129 if ($charge == 0) continue;
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', '', '' )");
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;
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
160 else if (($bcodeonly === $codeonly || (empty($codeonly) && $charge != 0)) && $brow['fee'] == $charge) {
161 $posskey = $key;
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;
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', '', '' )");
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
190 // End charge item logic. Continue to the next invoice detail item.
191 continue;
193 } // end if charge
195 $payer_id = empty($dtlinfo['ins']) ? 0 : $dtlinfo['ins'];
196 $session_id = 0;
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';
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);
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
223 } // end if payment
224 } // end not insurance
226 $payer_type = 0;
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;
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'");
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;
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");
253 ++$activity_count;
254 } // end detail item
255 } // end code
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);
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;
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;
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'");
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'");
300 if ($brow['code_type'] == 'COPAY') {
301 echo "Patient payment of \$" . sprintf('%01.2f', 0 - $brow['fee']);
303 else {
304 echo "Charge item '" . $brow['code'] . "' with amount \$" .
305 sprintf('%01.2f', $brow['fee']);
307 echo " was not found in SQL-Ledger invoice $pid.$encounter " .
308 "and has been removed from the encounter.<br />\n";
309 flush();
312 ++$invoice_count;
313 } // end invoice
314 SLClose();
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";
333 sqlStatement("UPDATE form_encounter SET last_level_billed = 0 " .
334 "WHERE id = '" . $ferow['id'] . "'");
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";
342 </span>
344 </body>
345 </html>