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