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
  28
  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 {
  51     $lrow = sqlQuery(
  52         "SELECT ppid FROM procedure_providers WHERE " .
  53         "npi = ? ORDER BY ppid LIMIT 1",
  54         array($npi)
  55     );
  56     if (empty($lrow['ppid'])) {
  57         return 0;
  58     }
  59
  60     return intval($lrow['ppid']);
  61 }
  62
  63 if (!acl_check('admin', 'super')) {
  64     die(xlt('Not authorized', '', '', '!'));
  65 }
  66
  67 $form_step   = isset($_POST['form_step']) ? trim($_POST['form_step']) : '0';
  68 $form_status = isset($_POST['form_status' ]) ? trim($_POST['form_status' ]) : '';
  69
  70 if (!empty($_POST['form_import'])) {
  71     $form_step = 1;
  72 }
  73
  74 // When true the current form will submit itself after a brief pause.
  75 $auto_continue = false;
  76
  77 // Set up main paths.
  78 $EXPORT_FILE = $GLOBALS['temporary_files_dir'] . "/openemr_config.sql";
  79 ?>
  80 <html>
  81
  82 <head>
  83 <link rel="stylesheet" href='<?php echo $css_header ?>' type='text/css'>
  84 <title><?php echo xlt('Load Lab Configuration'); ?></title>
  85 </head>
  86
  87 <body class="body_top">
  88 <center>
  89 &nbsp;<br />
  90 <form method='post' action='load_compendium.php' enctype='multipart/form-data'>
  91
  92 <table>
  93
  94 <?php
  95 if ($form_step == 0) {
  96     echo " <tr>\n";
  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.
 103             echo " disabled";
 104         }
 105
 106         echo ">" . text($key) . ": " . text($value) . "</option>";
 107     }
 108
 109     echo "</td>\n";
 110     echo " </tr>\n";
 111
 112     echo " <tr>\n";
 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>";
 118     echo "</td>\n";
 119     echo " </tr>\n";
 120
 121     echo " <tr>\n";
 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>";
 129     }
 130
 131     echo "</td>\n";
 132     echo " </tr>\n";
 133
 134     echo " <tr>\n";
 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";
 138     echo " </tr>\n";
 139
 140     echo " <tr>\n";
 141     echo "  <td nowrap>&nbsp;</td>\n";
 142     echo "  <td><input type='submit' value='" . xla('Submit') . "' /></td>\n";
 143     echo " </tr>\n";
 144 }
 145
 146 echo " <tr>\n";
 147 echo "  <td colspan='2'>\n";
 148
 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);
 156
 157         $form_status .= xlt('Applying') . "...<br />";
 158         echo nl2br(text($form_status));
 159
 160         $fhcsv = fopen($_FILES['userfile']['tmp_name'], "r");
 161
 162         if ($fhcsv) {
 163             // Vendor = Pathgroup
 164             //
 165             if ($form_vendor == '1235186800') {
 166                 if ($form_action == 1) { // load compendium
 167                     // Mark all "ord" rows having the indicated parent as inactive.
 168                     sqlStatement(
 169                         "UPDATE procedure_type SET activity = 0 WHERE " .
 170                         "parent = ? AND procedure_type = 'ord'",
 171                         array($form_group)
 172                     );
 173
 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
 181                     //
 182                     while (!feof($fhcsv)) {
 183                               $acsv = fgetcsv($fhcsv, 4096);
 184                         if (count($acsv) < 4 || $acsv[0] == "Order Code") {
 185                             continue;
 186                         }
 187
 188                               $standard_code = empty($acsv[2]) ? '' : ('CPT4:' . $acsv[2]);
 189
 190                               // Update or insert the order row, if not already done.
 191                               $trow = sqlQuery(
 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])
 196                               );
 197                         if (empty($trow['procedure_type_id']) || $trow['activity'] == 0) {
 198                             if (empty($trow['procedure_type_id'])) {
 199                                 $ptid = sqlInsert(
 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')
 203                                 );
 204                             } else {
 205                                   $ptid = $trow['procedure_type_id'];
 206                                   sqlStatement(
 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)
 211                                   );
 212                             }
 213
 214                                                 sqlStatement(
 215                                                     "UPDATE procedure_type SET activity = 0 WHERE " .
 216                                                     "parent = ? AND procedure_type = 'res'",
 217                                                     array($ptid)
 218                                                 );
 219                         }
 220
 221                               // Update or insert the result row.
 222                               // Not sure we need this, but what the hell.
 223                               $trow = sqlQuery(
 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])
 228                               );
 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'])) {
 232                                 sqlInsert(
 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')
 236                                 );
 237                             } else {
 238                                   $resid = $trow['procedure_type_id'];
 239                                   sqlStatement(
 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)
 244                                   );
 245                             }
 246                         } // end if
 247                     } // end while
 248                 } // end load compendium
 249
 250                 else if ($form_action == 2) { // load questions
 251                     // Delete the vendor's current questions.
 252                     sqlStatement(
 253                         "DELETE FROM procedure_questions WHERE lab_id = ?",
 254                         array($lab_id)
 255                     );
 256
 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)
 261                     //   1: Question Code
 262                     //   2: Question
 263                     //   3: "Tips"
 264                     //   4: Required (0 = No, 1 = Yes)
 265                     //   5: Maxchar (integer length)
 266                     //   6: FieldType (FT = free text, DD = dropdown, ST = string)
 267                     //
 268                     $seq = 0;
 269                     $last_code = '';
 270                     while (!feof($fhcsv)) {
 271                         $acsv = fgetcsv($fhcsv, 4096);
 272                         if (count($acsv) < 7 || $acsv[4] == "Required") {
 273                             continue;
 274                         }
 275
 276                         $code = trim($acsv[0]);
 277                         if (empty($code)) {
 278                             continue;
 279                         }
 280
 281                         if ($code != $last_code) {
 282                             $seq = 0;
 283                             $last_code = $code;
 284                         }
 285
 286                         ++$seq;
 287
 288                         $required = 0 + $acsv[4];
 289                         $maxsize = 0 + $acsv[5];
 290                         $fldtype = 'T';
 291
 292                         // Figure out field type.
 293                         if ($acsv[6] == 'DD') {
 294                             $fldtype = 'S';
 295                         } else if (stristr($acsv[3], 'mm/dd/yy') !== false) {
 296                             $fldtype = 'D';
 297                         } else if (stristr($acsv[3], 'wks_days') !== false) {
 298                             $fldtype = 'G';
 299                         } else if ($acsv[6] == 'FT') {
 300                             $fldtype = 'T';
 301                         } else {
 302                             $fldtype = 'N';
 303                         }
 304
 305                         $qrow = sqlQuery(
 306                             "SELECT * FROM procedure_questions WHERE " .
 307                             "lab_id = ? AND procedure_code = ? AND question_code = ?",
 308                             array($lab_id, $code, $acsv[1])
 309                         );
 310
 311                         if (empty($qrow['question_code'])) {
 312                                     sqlStatement(
 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)
 318                                     );
 319                         } else {
 320                                       sqlStatement(
 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,
 326                                           $lab_id,
 327                                           $code,
 328                                           $acsv[1])
 329                                       );
 330                         }
 331                     } // end while
 332                 } // end load questions
 333
 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)
 342                     //
 343                     while (!feof($fhcsv)) {
 344                         $acsv = fgetcsv($fhcsv, 4096);
 345                         if (count($acsv) < 4 || ($acsv[0] == "OBXCode")) {
 346                             continue;
 347                         }
 348
 349                         $pcode   = trim($acsv[1]);
 350                         $qcode   = trim($acsv[0]);
 351                         $options = trim($acsv[2]) . ':' . trim($acsv[3]);
 352                         if (empty($pcode) || empty($qcode)) {
 353                             continue;
 354                         }
 355
 356                         $qrow = sqlQuery(
 357                             "SELECT * FROM procedure_questions WHERE " .
 358                             "lab_id = ? AND procedure_code = ? AND question_code = ?",
 359                             array($lab_id, $pcode, $qcode)
 360                         );
 361                         if (empty($qrow['procedure_code'])) {
 362                                     continue; // should not happen
 363                         } else {
 364                             if ($qrow['activity'] == '1' && $qrow['options'] !== '') {
 365                                 $options = $qrow['options'] . ';' . $options;
 366                             }
 367
 368                                       sqlStatement(
 369                                           "UPDATE procedure_questions SET " .
 370                                           "options = ? WHERE " .
 371                                           "lab_id = ? AND procedure_code = ? AND question_code = ?",
 372                                           array($options, $lab_id, $pcode, $qcode)
 373                                       );
 374                         }
 375                     } // end while
 376                 } // end load questions
 377             } // End Pathgroup
 378
 379             // Vendor = YPMG or DPMG
 380             //
 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.
 384                     sqlStatement(
 385                         "UPDATE procedure_type SET activity = 0 WHERE " .
 386                         "parent = ? AND procedure_type = 'ord'",
 387                         array($form_group)
 388                     );
 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
 396                     //
 397                     while (!feof($fhcsv)) {
 398                               $acsv = fgetcsv($fhcsv, 4096);
 399                               $ordercode = trim($acsv[0]);
 400                         if (count($acsv) < 2 || $ordercode == "Order Code") {
 401                             continue;
 402                         }
 403
 404                               $trow = sqlQuery(
 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)
 409                               );
 410
 411                         if (empty($trow['procedure_type_id'])) {
 412                                               sqlStatement(
 413                                                   "INSERT INTO procedure_type SET " .
 414                                                   "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
 415                                                   "activity = 1",
 416                                                   array($form_group, trim($acsv[1]), $lab_id, $ordercode, 'ord')
 417                                               );
 418                         } else {
 419                                                 sqlStatement(
 420                                                     "UPDATE procedure_type SET " .
 421                                                     "parent = ?, name = ?, lab_id = ?, procedure_code = ?, procedure_type = ?, " .
 422                                                     "activity = 1 " .
 423                                                     "WHERE procedure_type_id = ?",
 424                                                     array($form_group, trim($acsv[1]), $lab_id, $ordercode, 'ord',
 425                                                     $trow['procedure_type_id'])
 426                                                 );
 427                         }
 428                     }
 429                 } else if ($form_action == 2) { // load questions
 430                     // Mark the vendor's current questions inactive.
 431                     sqlStatement(
 432                         "DELETE FROM procedure_questions WHERE lab_id = ?",
 433                         array($lab_id)
 434                     );
 435
 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:
 439                     //   0: Order Code
 440                     //   1: Question Code
 441                     //   2: Question
 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)
 447                     //
 448                     $seq = 0;
 449                     $last_code = '';
 450                     while (!feof($fhcsv)) {
 451                         $acsv = fgetcsv($fhcsv, 4096);
 452                         if (count($acsv) < 5 || ($acsv[3] !== "false" && $acsv[3] !== "true")) {
 453                             continue;
 454                         }
 455
 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)) {
 461                             continue;
 462                         }
 463
 464                         if ($pcode != $last_code) {
 465                             $seq = 0;
 466                             $last_code = $pcode;
 467                         }
 468
 469                         ++$seq;
 470
 471                         // Figure out field type.
 472                         $fldtype = 'T';
 473                         if (strpos($acsv[4], 'Drop') !== false) {
 474                             $fldtype = 'S';
 475                         } else if (strpos($acsv[4], 'Multiselect') !== false) {
 476                             $fldtype = 'S';
 477                         }
 478
 479                         $qrow = sqlQuery(
 480                             "SELECT * FROM procedure_questions WHERE " .
 481                             "lab_id = ? AND procedure_code = ? AND question_code = ?",
 482                             array($lab_id, $pcode, $qcode)
 483                         );
 484
 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.
 488
 489                         if (empty($qrow['procedure_code'])) {
 490                                     sqlStatement(
 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)
 495                                     );
 496                         } else {
 497                             if ($qrow['activity'] == '1' && $qrow['options'] !== '' && $options !== '') {
 498                                 $options = $qrow['options'] . ';' . $options;
 499                             }
 500
 501                                       sqlStatement(
 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)
 506                                       );
 507                         }
 508                     } // end while
 509                 } // end load questions
 510             } // End YPMG
 511
 512             fclose($fhcsv);
 513         } else {
 514             echo xlt('Internal error accessing uploaded file!');
 515             $form_step = -1;
 516         }
 517     } else {
 518         echo xlt('Upload failed!');
 519         $form_step = -1;
 520     }
 521
 522     $auto_continue = true;
 523 }
 524
 525 if ($form_step == 2) {
 526     $form_status .= xlt('Done') . ".";
 527     echo nl2br(text($form_status));
 528 }
 529
 530 ++$form_step;
 531 ?>
 532
 533   </td>
 534  </tr>
 535 </table>
 536
 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); ?>' />
 539
 540 </form>
 541
 542 <?php
 543 ob_flush();
 544 flush();
 545 ?>
 546
 547 </center>
 548
 549 <?php if ($auto_continue) { ?>
 550 <script language="JavaScript">
 551  setTimeout("document.forms[0].submit();", 500);
 552 </script>
 553 <?php } ?>
 554
 555 </body>
 556 </html>
 557