interface/orders/load_compendium.php

   1 <?php
   2 /**
   3 * Administrative loader for lab compendium data.
   4 *
   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.
   7 *
   8 * Copyright (C) 2012 Rod Roark <rod@sunsetsystems.com>
   9 *
  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>.
  20 *
  21 * @package   OpenEMR
  22 * @author    Rod Roark <rod@sunsetsystems.com>
  23 */
  24
  25 set_time_limit(0);
  26
  27 $sanitize_all_escapes  = true;
  28 $fake_register_globals = false;
  29
  30 require_once("../globals.php");
  31 require_once("$srcdir/acl.inc");
  32
  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 // address book entry for a lab that has a supported NPI number.
  36 //
  37 $lab_npi = array(
  38   '1235186800' => 'Pathgroup Labs LLC',
  39   '1598760985' => 'Yosemite Pathology Medical Group',
  40 );
  41
  42 /**
  43  * Get lab's ID from the users table given its NPI.  If none return 0.
  44  *
  45  * @param  string  $npi           The lab's NPI number as known to the system
  46  * @return integer                The numeric value of the lab's address book entry
  47  */
  48 function getLabID($npi) {
  49   $lrow = sqlQuery("SELECT ppid FROM procedure_providers WHERE " .
  50     "npi = ? ORDER BY ppid LIMIT 1",
  51     array($npi));
  52   if (empty($lrow['ppid'])) return 0;
  53   return intval($lrow['ppid']);
  54 }
  55
  56 if (!acl_check('admin', 'super')) die(xlt('Not authorized','','','!'));
  57
  58 $form_step   = isset($_POST['form_step']) ? trim($_POST['form_step']) : '0';
  59 $form_status = isset($_POST['form_status' ]) ? trim($_POST['form_status' ]) : '';
  60
  61 if (!empty($_POST['form_import'])) $form_step = 1;
  62
  63 // When true the current form will submit itself after a brief pause.
  64 $auto_continue = false;
  65
  66 // Set up main paths.
  67 $EXPORT_FILE = $GLOBALS['temporary_files_dir'] . "/openemr_config.sql";
  68 ?>
  69 <html>
  70
  71 <head>
  72 <link rel="stylesheet" href='<?php echo $css_header ?>' type='text/css'>
  73 <title><?php echo xlt('Load Lab Configuration'); ?></title>
  74 </head>
  75
  76 <body class="body_top">
  77 <center>
  78 &nbsp;<br />
  79 <form method='post' action='load_compendium.php' enctype='multipart/form-data'>
  80
  81 <table>
  82
  83 <?php
  84 if ($form_step == 0) {
  85   echo " <tr>\n";
  86   echo "  <td width='1%' nowrap>" . xlt('Vendor') . "</td>\n";
  87   echo "  <td><select name='vendor'>";
  88   foreach ($lab_npi as $key => $value) {
  89     echo "<option value='" . attr($key) . "'";
  90     if (!getLabID($key)) {
  91       // Entries with no matching address book entry will be disabled.
  92       echo " disabled";
  93     }
  94     echo ">" . text($key) . ": " . text($value) . "</option>";
  95   }
  96   echo "</td>\n";
  97   echo " </tr>\n";
  98
  99   echo " <tr>\n";
 100   echo "  <td nowrap>" . xlt('Action') . "</td>\n";
 101   echo "  <td><select name='action'>";
 102   echo "<option value='1'>" . xlt('Load Order Definitions'    ) . "</option>";
 103   echo "<option value='2'>" . xlt('Load Order Entry Questions') . "</option>";
 104   echo "<option value='3'>" . xlt('Load OE Question Options'  ) . "</option>";
 105   echo "</td>\n";
 106   echo " </tr>\n";
 107
 108   echo " <tr>\n";
 109   echo "  <td nowrap>" . xlt('Container Group Name') . "</td>\n";
 110   echo "  <td><select name='group'>";
 111   $gres = sqlStatement("SELECT procedure_type_id, name FROM procedure_type " .
 112     "WHERE procedure_type = 'grp' ORDER BY name, procedure_type_id");
 113   while ($grow = sqlFetchArray($gres)) {
 114     echo "<option value='" . attr($grow['procedure_type_id']) . "'>" .
 115       text($grow['name']) . "</option>";
 116   }
 117   echo "</td>\n";
 118   echo " </tr>\n";
 119
 120   echo " <tr>\n";
 121   echo "  <td nowrap>" . xlt('File to Upload') . "</td>\n";
 122   echo "<td><input type='hidden' name='MAX_FILE_SIZE' value='4000000' />";
 123   echo "<input type='file' name='userfile' /></td>\n";
 124   echo " </tr>\n";
 125
 126   echo " <tr>\n";
 127   echo "  <td nowrap>&nbsp;</td>\n";
 128   echo "  <td><input type='submit' value='" . xla('Submit') . "' /></td>\n";
 129   echo " </tr>\n";
 130 }
 131
 132 echo " <tr>\n";
 133 echo "  <td colspan='2'>\n";
 134
 135 if ($form_step == 1) {
 136   // Process uploaded config file.
 137   if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
 138     $form_vendor = $_POST['vendor'];
 139     $form_action = intval($_POST['action']);
 140     $form_group  = intval($_POST['group']);
 141     $lab_id = getLabID($form_vendor);
 142
 143     $form_status .= xlt('Applying') . "...<br />";
 144     echo nl2br($form_status);
 145
 146     $fhcsv = fopen($_FILES['userfile']['tmp_name'], "r");
 147
 148     if ($fhcsv) {
 149
 150       // Vendor = Pathgroup
 151       //
 152       if ($form_vendor == '1235186800') {
 153
 154         if ($form_action == 1) { // load compendium
 155           // Mark all "ord" rows having the indicated parent as inactive.
 156           sqlStatement("UPDATE procedure_type SET activity = 0 WHERE " .
 157             "parent = ? AND procedure_type = 'ord'",
 158             array($form_group));
 159
 160           // What should be uploaded is the "Compendium" spreadsheet provided by
 161           // PathGroup, saved in "Text CSV" format from OpenOffice, using its
 162           // default settings.  Values for each row are:
 163           //   0: Order Code  : mapped to procedure_code of order type
 164           //   1: Order Name  : mapped to name of order type
 165           //   2: Result Code : mapped to procedure_code of result type
 166           //   3: Result Name : mapped to name of result type
 167           //
 168           while (!feof($fhcsv)) {
 169             $acsv = fgetcsv($fhcsv, 4096);
 170             if (count($acsv) < 4 || $acsv[0] == "Order Code") continue;
 171             $standard_code = empty($acsv[2]) ? '' : ('CPT4:' . $acsv[2]);
 172
 173             // Update or insert the order row, if not already done.
 174             $trow = sqlQuery("SELECT * FROM procedure_type WHERE " .
 175               "parent = ? AND procedure_code = ? AND procedure_type = 'ord' " .
 176               "ORDER BY procedure_type_id DESC LIMIT 1",
 177               array($form_group, $acsv[0]));
 178             if (empty($trow['procedure_type_id']) || $trow['activity'] == 0) {
 179               if (empty($trow['procedure_type_id'])) {
 180                 $ptid = sqlInsert("INSERT INTO procedure_type SET " .
 181                   "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?",
 182                   array($form_group, $acsv[1], $lab_id, $acsv[0], 'ord'));
 183               }
 184               else {
 185                 $ptid = $trow['procedure_type_id'];
 186                 sqlStatement("UPDATE procedure_type SET " .
 187                   "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
 188                   "activity = 1 WHERE procedure_type_id = ?",
 189                   array($form_group, $acsv[1], $lab_id, $acsv[0], 'ord', $ptid));
 190               }
 191               sqlStatement("UPDATE procedure_type SET activity = 0 WHERE " .
 192                 "parent = ? AND procedure_type = 'res'",
 193                 array($ptid));
 194             }
 195
 196             // Update or insert the result row.
 197             // Not sure we need this, but what the hell.
 198             $trow = sqlQuery("SELECT * FROM procedure_type WHERE " .
 199               "parent = ? AND procedure_code = ? AND procedure_type = 'res' " .
 200               "ORDER BY procedure_type_id DESC LIMIT 1",
 201               array($ptid, $acsv[2]));
 202             // The following should always be true, otherwise duplicate input row.
 203             if (empty($trow['procedure_type_id']) || $trow['activity'] == 0) {
 204               if (empty($trow['procedure_type_id'])) {
 205                 sqlInsert("INSERT INTO procedure_type SET " .
 206                   "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?",
 207                   array($ptid, $acsv[3], $lab_id, $acsv[2], 'res'));
 208               }
 209               else {
 210                 $resid = $trow['procedure_type_id'];
 211                 sqlStatement("UPDATE procedure_type SET " .
 212                   "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
 213                   "activity = 1 WHERE procedure_type_id = ?",
 214                   array($ptid, $acsv[3], $lab_id, $acsv[2], 'res', $resid));
 215               }
 216             } // end if
 217           } // end while
 218         } // end load compendium
 219
 220         else if ($form_action == 2) { // load questions
 221           // Mark the vendor's current questions inactive.
 222           sqlStatement("UPDATE procedure_questions SET activity = 0 WHERE lab_id = ?",
 223             array($lab_id));
 224
 225           // What should be uploaded is the "AOE Questions" spreadsheet provided by
 226           // PathGroup, saved in "Text CSV" format from OpenOffice, using its
 227           // default settings.  Values for each row are:
 228           //   0: OBRCode (order code)
 229           //   1: Question Code
 230           //   2: Question
 231           //   3: "Tips"
 232           //   4: Required (0 = No, 1 = Yes)
 233           //   5: Maxchar (integer length)
 234           //   6: FieldType (FT = free text, DD = dropdown, ST = string)
 235           //
 236           $seq = 0;
 237           $last_code = '';
 238           while (!feof($fhcsv)) {
 239             $acsv = fgetcsv($fhcsv, 4096);
 240             if (count($acsv) < 7 || $acsv[4] == "Required") continue;
 241             $code = trim($acsv[0]);
 242             if (empty($code)) continue;
 243
 244             if ($code != $last_code) {
 245               $seq = 0;
 246               $last_code = $code;
 247             }
 248             ++$seq;
 249
 250             $required = 0 + $acsv[4];
 251             $maxsize = 0 + $acsv[5];
 252             $fldtype = 'T';
 253
 254             // Figure out field type.
 255             if ($acsv[6] == 'DD') $fldtype = 'S';
 256             else if (stristr($acsv[3], 'mm/dd/yy') !== FALSE) $fldtype = 'D';
 257             else if (stristr($acsv[3], 'wks_days') !== FALSE) $fldtype = 'G';
 258             else if ($acsv[6] == 'FT') $fldtype = 'T';
 259             else $fldtype = 'N';
 260
 261             $qrow = sqlQuery("SELECT * FROM procedure_questions WHERE " .
 262               "lab_id = ? AND procedure_code = ? AND question_code = ?",
 263               array($lab_id, $code, $acsv[1]));
 264
 265             if (empty($qrow['question_code'])) {
 266               sqlStatement("INSERT INTO procedure_questions SET " .
 267                 "lab_id = ?, procedure_code = ?, question_code = ?, question_text = ?, " .
 268                 "required = ?, maxsize = ?, fldtype = ?, options = '', tips = ?,
 269                 activity = 1, seq = ?",
 270                 array($lab_id, $code, $acsv[1], $acsv[2], $required, $maxsize, $fldtype, $acsv[3], $seq));
 271             }
 272             else {
 273               sqlStatement("UPDATE procedure_questions SET " .
 274                 "question_text = ?, required = ?, maxsize = ?, fldtype = ?, " .
 275                 "options = '', tips = ?, activity = 1, seq = ? WHERE " .
 276                 "lab_id = ? AND procedure_code = ? AND question_code = ?",
 277                 array($acsv[2], $required, $maxsize, $fldtype, $acsv[3], $seq,
 278                   $lab_id, $code, $acsv[1]));
 279             }
 280
 281           } // end while
 282         } // end load questions
 283
 284         else if ($form_action == 3) { // load question options
 285           // What should be uploaded is the "AOE Options" spreadsheet provided
 286           // by YPMG, saved in "Text CSV" format from OpenOffice, using its
 287           // default settings.  Values for each row are:
 288           //   0: OBXCode (question code)
 289           //   1: OBRCode (procedure code)
 290           //   2: Option1 (option text)
 291           //   3: Optioncode (the row is duplicated for each possible value)
 292           //
 293           while (!feof($fhcsv)) {
 294             $acsv = fgetcsv($fhcsv, 4096);
 295             if (count($acsv) < 4 || ($acsv[0] == "OBXCode")) continue;
 296             $pcode   = trim($acsv[1]);
 297             $qcode   = trim($acsv[0]);
 298             $options = trim($acsv[2]) . ':' . trim($acsv[3]);
 299             if (empty($pcode) || empty($qcode)) continue;
 300             $qrow = sqlQuery("SELECT * FROM procedure_questions WHERE " .
 301               "lab_id = ? AND procedure_code = ? AND question_code = ?",
 302               array($lab_id, $pcode, $qcode));
 303             if (empty($qrow['procedure_code'])) {
 304               continue; // should not happen
 305             }
 306             else {
 307               if ($qrow['activity'] == '1' && $qrow['options'] !== '') {
 308                 $options = $qrow['options'] . ';' . $options;
 309               }
 310               sqlStatement("UPDATE procedure_questions SET " .
 311                 "options = ? WHERE " .
 312                 "lab_id = ? AND procedure_code = ? AND question_code = ?",
 313                 array($options, $lab_id, $pcode, $qcode));
 314             }
 315           } // end while
 316         } // end load questions
 317       } // End Pathgroup
 318
 319       // Vendor = Yosemite Pathology Medical Group
 320       //
 321       if ($form_vendor == '1598760985') {
 322         if ($form_action == 1) { // load compendium
 323           // Mark all "ord" rows having the indicated parent as inactive.
 324           sqlStatement("UPDATE procedure_type SET activity = 0 WHERE " .
 325             "parent = ? AND procedure_type = 'ord'",
 326             array($form_group));
 327           // What should be uploaded is the Order Compendium spreadsheet provided
 328           // by YPMG, saved in "Text CSV" format from OpenOffice, using its
 329           // default settings.  Values for each row are:
 330           //   0: Order code    : mapped to procedure_code
 331           //   1: Order Name    : mapped to name
 332           //   2: Result Code   : ignored (will cause multiple occurrences of the same order code)
 333           //   3: Result Name   : ignored
 334           //
 335           while (!feof($fhcsv)) {
 336             $acsv = fgetcsv($fhcsv, 4096);
 337             $ordercode = trim($acsv[0]);
 338             if (count($acsv) < 2 || $ordercode == "Order Code") continue;
 339             $trow = sqlQuery("SELECT * FROM procedure_type WHERE " .
 340               "parent = ? AND procedure_code = ? AND procedure_type = 'ord' " .
 341               "ORDER BY procedure_type_id DESC LIMIT 1",
 342               array($form_group, $ordercode));
 343
 344             if (empty($trow['procedure_type_id'])) {
 345               sqlStatement("INSERT INTO procedure_type SET " .
 346                 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
 347                 "activity = 1",
 348                 array($form_group, trim($acsv[1]), $lab_id, $ordercode, 'ord'));
 349             }
 350             else {
 351               sqlStatement("UPDATE procedure_type SET " .
 352                 "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
 353                 "activity = 1 " .
 354                 "WHERE procedure_type_id = ?",
 355                 array($form_group, trim($acsv[1]), $lab_id, $ordercode, 'ord',
 356                   $trow['procedure_type_id']));
 357             }
 358           }
 359         }
 360         else if ($form_action == 2) { // load questions
 361           // Mark the vendor's current questions inactive.
 362           sqlStatement("UPDATE procedure_questions SET activity = 0 WHERE lab_id = ?",
 363             array($lab_id));
 364
 365           // What should be uploaded is the "AOE Questions" spreadsheet provided
 366           // by YPMG, saved in "Text CSV" format from OpenOffice, using its
 367           // default settings.  Values for each row are:
 368           //   0: Order Code
 369           //   1: Question Code
 370           //   2: Question
 371           //   3: Is Required (always "false")
 372           //   4: Field Type ("Free Text", "Pre-Defined Text" or "Multiselect Pre-Defined Text")
 373           //   5: Response (just one; the row is duplicated for each possible value)
 374           //
 375           while (!feof($fhcsv)) {
 376             $acsv = fgetcsv($fhcsv, 4096);
 377             if (count($acsv) < 5 || ($acsv[3] !== "false" && $acsv[3] !== "true")) continue;
 378
 379             $pcode   = trim($acsv[0]);
 380             $qcode   = trim($acsv[1]);
 381             $options = trim($acsv[5]);
 382             if (empty($pcode) || empty($qcode)) continue;
 383
 384             $qrow = sqlQuery("SELECT * FROM procedure_questions WHERE " .
 385               "lab_id = ? AND procedure_code = ? AND question_code = ?",
 386               array($lab_id, $pcode, $qcode));
 387
 388             // If this is the first option value and it's Multiselect, then
 389             // prepend '+;' to indicate start of a multi-select list.
 390             if (!empty($options) &&
 391                 (empty($qrow['options']) || empty($qrow['activity'])) &&
 392                 strpos($acsv[4], 'Multiselect') !== FALSE)
 393             {
 394               $options = '+;' . $options;
 395             }
 396
 397             if (empty($qrow['procedure_code'])) {
 398               sqlStatement("INSERT INTO procedure_questions SET " .
 399                 "lab_id = ?, procedure_code = ?, question_code = ?, question_text = ?, " .
 400                 "options = ?, activity = 1",
 401                 array($lab_id, $pcode, $qcode, trim($acsv[2]), $options));
 402             }
 403             else {
 404               if ($qrow['activity'] == '1' && $qrow['options'] !== '' && $options !== '') {
 405                 $options = $qrow['options'] . ';' . $options;
 406               }
 407               sqlStatement("UPDATE procedure_questions SET " .
 408                 "question_text = ?, options = ?, activity = 1 WHERE " .
 409                 "lab_id = ? AND procedure_code = ? AND question_code = ?",
 410                 array(trim($acsv[2]), $options, $lab_id, $pcode, $qcode));
 411             }
 412           } // end while
 413         } // end load questions
 414       } // End YPMG
 415
 416       fclose($fhcsv);
 417     }
 418     else {
 419       echo xlt('Internal error accessing uploaded file!');
 420       $form_step = -1;
 421     }
 422   }
 423   else {
 424     echo xlt('Upload failed!');
 425     $form_step = -1;
 426   }
 427   $auto_continue = true;
 428 }
 429
 430 if ($form_step == 2) {
 431   $form_status .= xlt('Done') . ".";
 432   echo nl2br($form_status);
 433 }
 434
 435 ++$form_step;
 436 ?>
 437
 438   </td>
 439  </tr>
 440 </table>
 441
 442 <input type='hidden' name='form_step' value='<?php echo attr($form_step); ?>' />
 443 <input type='hidden' name='form_status' value='<?php echo $form_status; ?>' />
 444
 445 </form>
 446
 447 <?php
 448 ob_flush();
 449 flush();
 450 ?>
 451
 452 </center>
 453
 454 <?php if ($auto_continue) { ?>
 455 <script language="JavaScript">
 456  setTimeout("document.forms[0].submit();", 500);
 457 </script>
 458 <?php } ?>
 459
 460 </body>
 461 </html>
 462