3 * Administrative loader for lab compendium data.
5 * Supports loading of lab order codes and related order entry questions from CSV
6 * format into the procedure_order and procedure_questions tables, respectively.
8 * Copyright (C) 2012-2013 Rod Roark <rod@sunsetsystems.com>
10 * LICENSE: This program is free software; you can redistribute it and/or
11 * modify it under the terms of the GNU General Public License
12 * as published by the Free Software Foundation; either version 2
13 * of the License, or (at your option) any later version.
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>.
22 * @author Rod Roark <rod@sunsetsystems.com>
30 require_once("../globals.php");
31 require_once("$srcdir/acl.inc");
33 // This array is an important reference for the supported labs and their NPI
34 // numbers as known to this program. The clinic must define at least one
35 // procedure provider entry for a lab that has a supported NPI number.
38 '1235138868' => 'Diagnostic Pathology Medical Group',
39 '1235186800' => 'Pathgroup Labs LLC',
40 '1598760985' => 'Yosemite Pathology Medical Group',
44 * Get lab's ID from the users table given its NPI. If none return 0.
46 * @param string $npi The lab's NPI number as known to the system
47 * @return integer The numeric value of the lab's address book entry
49 function getLabID($npi)
52 "SELECT ppid FROM procedure_providers WHERE " .
53 "npi = ? ORDER BY ppid LIMIT 1",
56 if (empty($lrow['ppid'])) {
60 return intval($lrow['ppid']);
63 if (!acl_check('admin', 'super')) {
64 die(xlt('Not authorized', '', '', '!'));
67 $form_step = isset($_POST['form_step']) ?
trim($_POST['form_step']) : '0';
68 $form_status = isset($_POST['form_status' ]) ?
trim($_POST['form_status' ]) : '';
70 if (!empty($_POST['form_import'])) {
74 // When true the current form will submit itself after a brief pause.
75 $auto_continue = false;
78 $EXPORT_FILE = $GLOBALS['temporary_files_dir'] . "/openemr_config.sql";
83 <link rel
="stylesheet" href
='<?php echo $css_header ?>' type
='text/css'>
84 <title
><?php
echo xlt('Load Lab Configuration'); ?
></title
>
87 <body
class="body_top">
90 <form method
='post' action
='load_compendium.php' enctype
='multipart/form-data'>
95 if ($form_step == 0) {
97 echo " <td width='1%' nowrap>" . xlt('Vendor') . "</td>\n";
98 echo " <td><select name='vendor'>";
99 foreach ($lab_npi as $key => $value) {
100 echo "<option value='" . attr($key) . "'";
101 if (!getLabID($key)) {
102 // Entries with no matching address book entry will be disabled.
106 echo ">" . text($key) . ": " . text($value) . "</option>";
113 echo " <td nowrap>" . xlt('Action') . "</td>\n";
114 echo " <td><select name='action'>";
115 echo "<option value='1'>" . xlt('Load Order Definitions') . "</option>";
116 echo "<option value='2'>" . xlt('Load Order Entry Questions') . "</option>";
117 echo "<option value='3'>" . xlt('Load OE Question Options') . "</option>";
122 echo " <td nowrap>" . xlt('Container Group Name') . "</td>\n";
123 echo " <td><select name='group'>";
124 $gres = sqlStatement("SELECT procedure_type_id, name FROM procedure_type " .
125 "WHERE procedure_type = 'grp' ORDER BY name, procedure_type_id");
126 while ($grow = sqlFetchArray($gres)) {
127 echo "<option value='" . attr($grow['procedure_type_id']) . "'>" .
128 text($grow['name']) . "</option>";
135 echo " <td nowrap>" . xlt('File to Upload') . "</td>\n";
136 echo "<td><input type='hidden' name='MAX_FILE_SIZE' value='4000000' />";
137 echo "<input type='file' name='userfile' /></td>\n";
141 echo " <td nowrap> </td>\n";
142 echo " <td><input type='submit' value='" . xla('Submit') . "' /></td>\n";
147 echo " <td colspan='2'>\n";
149 if ($form_step == 1) {
150 // Process uploaded config file.
151 if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
152 $form_vendor = $_POST['vendor'];
153 $form_action = intval($_POST['action']);
154 $form_group = intval($_POST['group']);
155 $lab_id = getLabID($form_vendor);
157 $form_status .= xlt('Applying') . "...<br />";
158 echo nl2br(text($form_status));
160 $fhcsv = fopen($_FILES['userfile']['tmp_name'], "r");
163 // Vendor = Pathgroup
165 if ($form_vendor == '1235186800') {
166 if ($form_action == 1) { // load compendium
167 // Mark all "ord" rows having the indicated parent as inactive.
169 "UPDATE procedure_type SET activity = 0 WHERE " .
170 "parent = ? AND procedure_type = 'ord'",
174 // What should be uploaded is the "Compendium" spreadsheet provided by
175 // PathGroup, saved in "Text CSV" format from OpenOffice, using its
176 // default settings. Values for each row are:
177 // 0: Order Code : mapped to procedure_code of order type
178 // 1: Order Name : mapped to name of order type
179 // 2: Result Code : mapped to procedure_code of result type
180 // 3: Result Name : mapped to name of result type
182 while (!feof($fhcsv)) {
183 $acsv = fgetcsv($fhcsv, 4096);
184 if (count($acsv) < 4 ||
$acsv[0] == "Order Code") {
188 $standard_code = empty($acsv[2]) ?
'' : ('CPT4:' . $acsv[2]);
190 // Update or insert the order row, if not already done.
192 "SELECT * FROM procedure_type WHERE " .
193 "parent = ? AND procedure_code = ? AND procedure_type = 'ord' " .
194 "ORDER BY procedure_type_id DESC LIMIT 1",
195 array($form_group, $acsv[0])
197 if (empty($trow['procedure_type_id']) ||
$trow['activity'] == 0) {
198 if (empty($trow['procedure_type_id'])) {
200 "INSERT INTO procedure_type SET " .
201 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?",
202 array($form_group, $acsv[1], $lab_id, $acsv[0], 'ord')
205 $ptid = $trow['procedure_type_id'];
207 "UPDATE procedure_type SET " .
208 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
209 "activity = 1 WHERE procedure_type_id = ?",
210 array($form_group, $acsv[1], $lab_id, $acsv[0], 'ord', $ptid)
215 "UPDATE procedure_type SET activity = 0 WHERE " .
216 "parent = ? AND procedure_type = 'res'",
221 // Update or insert the result row.
222 // Not sure we need this, but what the hell.
224 "SELECT * FROM procedure_type WHERE " .
225 "parent = ? AND procedure_code = ? AND procedure_type = 'res' " .
226 "ORDER BY procedure_type_id DESC LIMIT 1",
227 array($ptid, $acsv[2])
229 // The following should always be true, otherwise duplicate input row.
230 if (empty($trow['procedure_type_id']) ||
$trow['activity'] == 0) {
231 if (empty($trow['procedure_type_id'])) {
233 "INSERT INTO procedure_type SET " .
234 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?",
235 array($ptid, $acsv[3], $lab_id, $acsv[2], 'res')
238 $resid = $trow['procedure_type_id'];
240 "UPDATE procedure_type SET " .
241 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
242 "activity = 1 WHERE procedure_type_id = ?",
243 array($ptid, $acsv[3], $lab_id, $acsv[2], 'res', $resid)
248 } // end load compendium
250 else if ($form_action == 2) { // load questions
251 // Delete the vendor's current questions.
253 "DELETE FROM procedure_questions WHERE lab_id = ?",
257 // What should be uploaded is the "AOE Questions" spreadsheet provided by
258 // PathGroup, saved in "Text CSV" format from OpenOffice, using its
259 // default settings. Values for each row are:
260 // 0: OBRCode (order code)
264 // 4: Required (0 = No, 1 = Yes)
265 // 5: Maxchar (integer length)
266 // 6: FieldType (FT = free text, DD = dropdown, ST = string)
270 while (!feof($fhcsv)) {
271 $acsv = fgetcsv($fhcsv, 4096);
272 if (count($acsv) < 7 ||
$acsv[4] == "Required") {
276 $code = trim($acsv[0]);
281 if ($code != $last_code) {
288 $required = 0 +
$acsv[4];
289 $maxsize = 0 +
$acsv[5];
292 // Figure out field type.
293 if ($acsv[6] == 'DD') {
295 } else if (stristr($acsv[3], 'mm/dd/yy') !== false) {
297 } else if (stristr($acsv[3], 'wks_days') !== false) {
299 } else if ($acsv[6] == 'FT') {
306 "SELECT * FROM procedure_questions WHERE " .
307 "lab_id = ? AND procedure_code = ? AND question_code = ?",
308 array($lab_id, $code, $acsv[1])
311 if (empty($qrow['question_code'])) {
313 "INSERT INTO procedure_questions SET " .
314 "lab_id = ?, procedure_code = ?, question_code = ?, question_text = ?, " .
315 "required = ?, maxsize = ?, fldtype = ?, options = '', tips = ?,
316 activity = 1, seq = ?",
317 array($lab_id, $code, $acsv[1], $acsv[2], $required, $maxsize, $fldtype, $acsv[3], $seq)
321 "UPDATE procedure_questions SET " .
322 "question_text = ?, required = ?, maxsize = ?, fldtype = ?, " .
323 "options = '', tips = ?, activity = 1, seq = ? WHERE " .
324 "lab_id = ? AND procedure_code = ? AND question_code = ?",
325 array($acsv[2], $required, $maxsize, $fldtype, $acsv[3], $seq,
332 } // end load questions
334 else if ($form_action == 3) { // load question options
335 // What should be uploaded is the "AOE Options" spreadsheet provided
336 // by YPMG, saved in "Text CSV" format from OpenOffice, using its
337 // default settings. Values for each row are:
338 // 0: OBXCode (question code)
339 // 1: OBRCode (procedure code)
340 // 2: Option1 (option text)
341 // 3: Optioncode (the row is duplicated for each possible value)
343 while (!feof($fhcsv)) {
344 $acsv = fgetcsv($fhcsv, 4096);
345 if (count($acsv) < 4 ||
($acsv[0] == "OBXCode")) {
349 $pcode = trim($acsv[1]);
350 $qcode = trim($acsv[0]);
351 $options = trim($acsv[2]) . ':' . trim($acsv[3]);
352 if (empty($pcode) ||
empty($qcode)) {
357 "SELECT * FROM procedure_questions WHERE " .
358 "lab_id = ? AND procedure_code = ? AND question_code = ?",
359 array($lab_id, $pcode, $qcode)
361 if (empty($qrow['procedure_code'])) {
362 continue; // should not happen
364 if ($qrow['activity'] == '1' && $qrow['options'] !== '') {
365 $options = $qrow['options'] . ';' . $options;
369 "UPDATE procedure_questions SET " .
370 "options = ? WHERE " .
371 "lab_id = ? AND procedure_code = ? AND question_code = ?",
372 array($options, $lab_id, $pcode, $qcode)
376 } // end load questions
379 // Vendor = YPMG or DPMG
381 if ($form_vendor == '1598760985' ||
$form_vendor == '1235138868') {
382 if ($form_action == 1) { // load compendium
383 // Mark all "ord" rows having the indicated parent as inactive.
385 "UPDATE procedure_type SET activity = 0 WHERE " .
386 "parent = ? AND procedure_type = 'ord'",
389 // What should be uploaded is the Order Compendium spreadsheet provided
390 // by YPMG, saved in "Text CSV" format from OpenOffice, using its
391 // default settings. Values for each row are:
392 // 0: Order code : mapped to procedure_code
393 // 1: Order Name : mapped to name
394 // 2: Result Code : ignored (will cause multiple occurrences of the same order code)
395 // 3: Result Name : ignored
397 while (!feof($fhcsv)) {
398 $acsv = fgetcsv($fhcsv, 4096);
399 $ordercode = trim($acsv[0]);
400 if (count($acsv) < 2 ||
$ordercode == "Order Code") {
405 "SELECT * FROM procedure_type WHERE " .
406 "parent = ? AND procedure_code = ? AND procedure_type = 'ord' " .
407 "ORDER BY procedure_type_id DESC LIMIT 1",
408 array($form_group, $ordercode)
411 if (empty($trow['procedure_type_id'])) {
413 "INSERT INTO procedure_type SET " .
414 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
416 array($form_group, trim($acsv[1]), $lab_id, $ordercode, 'ord')
420 "UPDATE procedure_type SET " .
421 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
423 "WHERE procedure_type_id = ?",
424 array($form_group, trim($acsv[1]), $lab_id, $ordercode, 'ord',
425 $trow['procedure_type_id'])
429 } else if ($form_action == 2) { // load questions
430 // Mark the vendor's current questions inactive.
432 "DELETE FROM procedure_questions WHERE lab_id = ?",
436 // What should be uploaded is the "AOE Questions" spreadsheet provided
437 // by YPMG, saved in "Text CSV" format from OpenOffice, using its
438 // default settings. Values for each row are:
442 // 3: Is Required (always "false")
443 // 4: Field Type ("Free Text", "Pre-Defined Text" or "Drop Down";
444 // "Drop Down" was previously "Multiselect Pre-Defined Text" and
445 // indicates that more than one choice is allowed)
446 // 5: Response (just one; the row is duplicated for each possible value)
450 while (!feof($fhcsv)) {
451 $acsv = fgetcsv($fhcsv, 4096);
452 if (count($acsv) < 5 ||
($acsv[3] !== "false" && $acsv[3] !== "true")) {
456 $pcode = trim($acsv[0]);
457 $qcode = trim($acsv[1]);
458 $required = strtolower(substr($acsv[3], 0, 1)) == 't' ?
1 : 0;
459 $options = trim($acsv[5]);
460 if (empty($pcode) ||
empty($qcode)) {
464 if ($pcode != $last_code) {
471 // Figure out field type.
473 if (strpos($acsv[4], 'Drop') !== false) {
475 } else if (strpos($acsv[4], 'Multiselect') !== false) {
480 "SELECT * FROM procedure_questions WHERE " .
481 "lab_id = ? AND procedure_code = ? AND question_code = ?",
482 array($lab_id, $pcode, $qcode)
485 // If this is the first option value and it's a multi-select list,
486 // then prepend '+;' here to indicate that. YPMG does not use those
487 // but keep this note here for future reference.
489 if (empty($qrow['procedure_code'])) {
491 "INSERT INTO procedure_questions SET " .
492 "lab_id = ?, procedure_code = ?, question_code = ?, question_text = ?, " .
493 "fldtype = ?, required = ?, options = ?, seq = ?, activity = 1",
494 array($lab_id, $pcode, $qcode, trim($acsv[2]), $fldtype, $required, $options, $seq)
497 if ($qrow['activity'] == '1' && $qrow['options'] !== '' && $options !== '') {
498 $options = $qrow['options'] . ';' . $options;
502 "UPDATE procedure_questions SET " .
503 "question_text = ?, fldtype = ?, required = ?, options = ?, activity = 1 WHERE " .
504 "lab_id = ? AND procedure_code = ? AND question_code = ?",
505 array(trim($acsv[2]), $fldtype, $required, $options, $lab_id, $pcode, $qcode)
509 } // end load questions
514 echo xlt('Internal error accessing uploaded file!');
518 echo xlt('Upload failed!');
522 $auto_continue = true;
525 if ($form_step == 2) {
526 $form_status .= xlt('Done') . ".";
527 echo nl2br(text($form_status));
537 <input type
='hidden' name
='form_step' value
='<?php echo attr($form_step); ?>' />
538 <input type
='hidden' name
='form_status' value
='<?php echo attr($form_status); ?>' />
549 <?php
if ($auto_continue) { ?
>
550 <script language
="JavaScript">
551 setTimeout("document.forms[0].submit();", 500);