generalizing column choices
[openemr.git] / sl_convert.php
blobdf4daabf3e6d7979998b802096eab0da033933df
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.
14 // Disable PHP timeout. This will not work in safe mode.
15 ini_set('max_execution_time', '0');
17 $ignoreAuth=true; // no login required
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');
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!");
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();
39 echo "<p>Be patient, this will take a while...</p>";
40 flush();
42 $invoice_count = 0;
43 $activity_count = 0;
45 $res = SLQuery("SELECT id, invnumber, transdate, shipvia, intnotes " .
46 "FROM ar ORDER BY id");
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'];
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'];
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'");
71 $invlines = get_invoice_summary($row['id'], true);
73 // print_r($invlines); // debugging
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 = '';
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'] . "', '', '' )");
94 continue; // otherwise skip charges
96 $payer_id = empty($dtlinfo['ins']) ? 0 : $dtlinfo['ins'];
97 $session_id = 0;
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';
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);
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
121 } // end foreach
122 } // end if payment
123 } // end not insurance
125 $payer_type = 0;
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;
132 arPostPayment($pid, $encounter, $session_id, $dtlinfo['pmt'], $code,
133 $payer_type, addslashes($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'");
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;
144 arPostAdjustment($pid, $encounter, $session_id, 0 - $dtlinfo['chg'],
145 $code, $payer_type, addslashes($dtlinfo['rsn']), 0, "$dtldate 00:00:00");
148 ++$activity_count;
149 } // end detail item
150 } // end code
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;
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;
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;
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'");
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();
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";
203 </span>
205 </body>
206 </html>