custom/code_types.inc.php

   1 <?php
   2 /**
   3  * Library and data structure to manage Code Types and code type lookups.
   4  *
   5  * The data structure is the $code_types array.
   6  * The $code_types array is built from the code_types sql table and provides
   7  * abstraction of diagnosis/billing code types.  This is desirable
   8  * because different countries or fields of practice use different methods for
   9  * coding diagnoses, procedures and supplies.  Fees will not be relevant where
  10  * medical care is socialized.
  11  * <pre>Attributes of the $code_types array are:
  12  *  active   - 1 if this code type is activated
  13  *  id       - the numeric identifier of this code type in the codes table
  14  *  claim    - 1 if this code type is used in claims
  15  *  fee      - 1 if fees are used, else 0
  16  *  mod      - the maximum length of a modifier, 0 if modifiers are not used
  17  *  just     - the code type used for justification, empty if none
  18  *  rel      - 1 if other billing codes may be "related" to this code type
  19  *  nofs     - 1 if this code type should NOT appear in the Fee Sheet
  20  *  diag     - 1 if this code type is for diagnosis
  21  *  proc     - 1 if this code type is a procedure/service
  22  *  label    - label used for code type
  23  *  external - 0 for storing codes in the code table
  24  *             1 for storing codes in external ICD10 Diagnosis tables
  25  *             2 for storing codes in external SNOMED (RF1) Diagnosis tables
  26  *             3 for storing codes in external SNOMED (RF2) Diagnosis tables
  27  *             4 for storing codes in external ICD9 Diagnosis tables
  28  *             5 for storing codes in external ICD9 Procedure/Service tables
  29  *             6 for storing codes in external ICD10 Procedure/Service tables
  30  *             7 for storing codes in external SNOMED Clinical Term tables
  31  *             8 for storing codes in external SNOMED (RF2) Clinical Term tables (for future)
  32  *             9 for storing codes in external SNOMED (RF1) Procedure Term tables
  33  *             10 for storing codes in external SNOMED (RF2) Procedure Term tables (for future)
  34  *  term     - 1 if this code type is used as a clinical term
  35  *  </pre>
  36  *
  37  * Copyright (C) 2006-2010 Rod Roark <rod@sunsetsystems.com>
  38  *
  39  * LICENSE: This program is free software; you can redistribute it and/or
  40  * modify it under the terms of the GNU General Public License
  41  * as published by the Free Software Foundation; either version 2
  42  * of the License, or (at your option) any later version.
  43  * This program is distributed in the hope that it will be useful,
  44  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  45  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  46  * GNU General Public License for more details.
  47  * You should have received a copy of the GNU General Public License
  48  * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
  49  *
  50  * @package OpenEMR
  51  * @author  Rod Roark <rod@sunsetsystems.com>
  52  * @author  Brady Miller <brady@sparmy.com>
  53  * @author  Kevin Yeh <kevin.y@integralemr.com>
  54  * @link    http://www.open-emr.org
  55  */
  56
  57 require_once(dirname(__FILE__)."/../library/csv_like_join.php");
  58
  59 $code_types = array();
  60 $default_search_type = '';
  61 $ctres = sqlStatement("SELECT * FROM code_types WHERE ct_active=1 ORDER BY ct_seq, ct_key");
  62 while ($ctrow = sqlFetchArray($ctres)) {
  63   $code_types[$ctrow['ct_key']] = array(
  64     'active' => $ctrow['ct_active'  ],
  65     'id'   => $ctrow['ct_id'  ],
  66     'fee'  => $ctrow['ct_fee' ],
  67     'mod'  => $ctrow['ct_mod' ],
  68     'just' => $ctrow['ct_just'],
  69     'rel'  => $ctrow['ct_rel' ],
  70     'nofs' => $ctrow['ct_nofs'],
  71     'diag' => $ctrow['ct_diag'],
  72     'mask' => $ctrow['ct_mask'],
  73     'label'=> ( (empty($ctrow['ct_label'])) ? $ctrow['ct_key'] : $ctrow['ct_label'] ),
  74     'external'=> $ctrow['ct_external'],
  75     'claim' => $ctrow['ct_claim'],
  76     'proc' => $ctrow['ct_proc'],
  77     'term' => $ctrow['ct_term']
  78   );
  79   if ($default_search_type === '') $default_search_type = $ctrow['ct_key'];
  80 }
  81
  82 /** This array contains metadata describing the arrangement of the external data
  83  *  tables for storing codes.
  84  */
  85 $code_external_tables=array();
  86 define('EXT_COL_CODE','code');
  87 define('EXT_COL_DESCRIPTION','description');
  88 define('EXT_TABLE_NAME','table');
  89 define('EXT_FILTER_CLAUSES','filter_clause');
  90 define('EXT_VERSION_ORDER','filter_version_order');
  91 define('EXT_JOINS','joins');
  92 define('JOIN_TABLE','join');
  93 define('JOIN_FIELDS','fields');
  94 define('DISPLAY_DESCRIPTION',"display_description");
  95
  96 /**
  97  * This is a helper function for defining the metadata that describes the tables
  98  *
  99  * @param type $results             A reference to the global array which stores all the metadata
 100  * @param type $index               The external table ID.  This corresponds to the value in the code_types table in the ct_external column
 101  * @param type $table_name          The name of the table which stores the code informattion (e.g. icd9_dx_code
 102  * @param type $col_code            The name of the column which is the code
 103  * @param type $col_description     The name of the column which is the description
 104  * @param type $filter_clauses      An array of clauses to be included in the search "WHERE" clause that limits results
 105  * @param type $version_order       How to choose between different revisions of codes
 106  * @param type $joins               An array which describes additional tables to join as part of a code search.
 107  */
 108 function define_external_table(&$results, $index, $table_name,$col_code, $col_description,$filter_clauses=array(),$version_order="",$joins=array(),$display_desc="")
 109 {
 110     $results[$index]=array(EXT_TABLE_NAME=>$table_name,
 111                            EXT_COL_CODE=>$col_code,
 112                            EXT_COL_DESCRIPTION=>$col_description,
 113                            EXT_FILTER_CLAUSES=>$filter_clauses,
 114                            EXT_JOINS=>$joins,
 115                            EXT_VERSION_ORDER=>$version_order,
 116                            DISPLAY_DESCRIPTION=>$display_desc
 117                            );
 118 }
 119 // In order to treat all the code types the same for lookup_code_descriptions, we include metadata for the original codes table
 120 define_external_table($code_external_tables,0,'codes','code','code_text',array(),'id');
 121
 122 // ICD9 External Definitions
 123 define_external_table($code_external_tables,4,'icd9_dx_code','formatted_dx_code','long_desc',array("active='1'"),'revision DESC');
 124 define_external_table($code_external_tables,5,'icd9_sg_code','formatted_sg_code','long_desc',array("active='1'"),'revision DESC');
 125 //**** End ICD9 External Definitions
 126
 127 // SNOMED Definitions
 128 // For generic SNOMED-CT, there is no need to join with the descriptions table to get a specific description Type
 129
 130 // For generic concepts, use the fully specified description (DescriptionType=3) so we can tell the difference between them.
 131 define_external_table($code_external_tables,7,'sct_descriptions','ConceptId','Term',array("DescriptionStatus=0","DescriptionType=3"),"");
 132
 133
 134 // To determine codes, we need to evaluate data in both the sct_descriptions table, and the sct_concepts table.
 135 // the base join with sct_concepts is the same for all types of SNOMED definitions, so we define the common part here
 136 $SNOMED_joins=array(JOIN_TABLE=>"sct_concepts",JOIN_FIELDS=>array("sct_descriptions.ConceptId=sct_concepts.ConceptId"));
 137
 138 // For disorders, use the preferred term (DescriptionType=1)
 139 define_external_table($code_external_tables,2,'sct_descriptions','ConceptId','Term',array("DescriptionStatus=0","DescriptionType=1"),"",array($SNOMED_joins));
 140 // Add the filter to choose only disorders. This filter happens as part of the join with the sct_concepts table
 141 array_push($code_external_tables[2][EXT_JOINS][0][JOIN_FIELDS],"FullySpecifiedName like '%(disorder)'");
 142
 143 // SNOMED-PR definition
 144 define_external_table($code_external_tables,9,'sct_descriptions','ConceptId','Term',array("DescriptionStatus=0","DescriptionType=1"),"",array($SNOMED_joins));
 145 // Add the filter to choose only procedures. This filter happens as part of the join with the sct_concepts table
 146 array_push($code_external_tables[9][EXT_JOINS][0][JOIN_FIELDS],"FullySpecifiedName like '%(procedure)'");
 147
 148
 149 //**** End SNOMED Definitions
 150
 151 // ICD 10 Definitions
 152 define_external_table($code_external_tables,1,'icd10_dx_order_code','formatted_dx_code','long_desc',array("active='1'","valid_for_coding = '1'"),'revision DESC');
 153 define_external_table($code_external_tables,6,'icd10_pcs_order_code','pcs_code','long_desc',array("active='1'","valid_for_coding = '1'"),'revision DESC');
 154 //**** End ICD 10 Definitions
 155
 156 /**
 157  * This array stores the external table options. See above for $code_types array
 158  * 'external' attribute  for explanation of the option listings.
 159  * @var array
 160  */
 161 $cd_external_options = array(
 162   '0' => xl('No'),
 163   '4' => xl('ICD9 Diagnosis'),
 164   '5' => xl('ICD9 Procedure/Service'),
 165   '1' => xl('ICD10 Diagnosis'),
 166   '6' => xl('ICD10 Procedure/Service'),
 167   '2' => xl('SNOMED (RF1) Diagnosis'),
 168   '3' => xl('SNOMED (RF2) Diagnosis'),
 169   '7' => xl('SNOMED (RF1) Clinical Term'),
 170   '9' => xl('SNOMED (RF1) Procedure')
 171 );
 172
 173 /**
 174  * Checks is fee are applicable to any of the code types.
 175  *
 176  * @return boolean
 177  */
 178 function fees_are_used() {
 179  global $code_types;
 180  foreach ($code_types as $value) { if ($value['fee'] && $value['active']) return true; }
 181  return false;
 182 }
 183
 184 /**
 185  * Checks is modifiers are applicable to any of the code types.
 186  * (If a code type is not set to show in the fee sheet, then is ignored)
 187  *
 188  * @param  boolean $fee_sheet Will ignore code types that are not shown in the fee sheet
 189  * @return boolean
 190  */
 191 function modifiers_are_used($fee_sheet=false) {
 192  global $code_types;
 193  foreach ($code_types as $value) {
 194   if ($fee_sheet && !empty($value['nofs'])) continue;
 195   if ($value['mod'] && $value['active']) return true;
 196  }
 197  return false;
 198 }
 199
 200 /**
 201  * Checks if justifiers are applicable to any of the code types.
 202  *
 203  * @return boolean
 204  */
 205 function justifiers_are_used() {
 206  global $code_types;
 207  foreach ($code_types as $value) { if (!empty($value['just']) && $value['active']) return true; }
 208  return false;
 209 }
 210
 211 /**
 212  * Checks is related codes are applicable to any of the code types.
 213  *
 214  * @return boolean
 215  */
 216 function related_codes_are_used() {
 217  global $code_types;
 218  foreach ($code_types as $value) { if ($value['rel'] && $value['active']) return true; }
 219  return false;
 220 }
 221
 222 /**
 223  * Convert a code type id (ct_id) to the key string (ct_key)
 224  *
 225  * @param  integer $id
 226  * @return string
 227  */
 228 function convert_type_id_to_key($id) {
 229  global $code_types;
 230  foreach ($code_types as $key => $value) {
 231   if ($value['id'] == $id) return $key;
 232  }
 233 }
 234
 235 /**
 236  * Return listing of pertinent and active code types.
 237  *
 238  * Function will return listing (ct_key) of pertinent
 239  * active code types, such as diagnosis codes or procedure
 240  * codes in a chosen format. Supported returned formats include
 241  * as 1) an array and as 2) a comma-separated lists that has been
 242  * process by urlencode() in order to place into URL  address safely.
 243  *
 244  * @param  string       $category       category of code types('diagnosis', 'procedure' or 'clinical_term')
 245  * @param  string       $return_format  format or returned code types ('array' or 'csv')
 246  * @return string/array
 247  */
 248 function collect_codetypes($category,$return_format="array") {
 249  global $code_types;
 250
 251  $return = array();
 252
 253  foreach ($code_types as $ct_key => $ct_arr) {
 254   if (!$ct_arr['active']) continue;
 255
 256   if ($category == "diagnosis") {
 257    if ($ct_arr['diag']) {
 258     array_push($return,$ct_key);
 259    }
 260   }
 261   else if ($category == "procedure") {
 262    if ($ct_arr['proc']) {
 263     array_push($return,$ct_key);
 264    }
 265   }
 266   else if ($category == "clinical_term") {
 267    if ($ct_arr['term']) {
 268     array_push($return,$ct_key);
 269    }
 270   }
 271   else {
 272    //return nothing since no supported category was chosen
 273   }
 274  }
 275
 276  if ($return_format == "csv") {
 277   //return it as a csv string
 278   return csv_like_join($return);
 279  }
 280  else { //$return_format == "array"
 281   //return the array
 282   return $return;
 283  }
 284 }
 285
 286 /**
 287  * Main code set searching function.
 288  *
 289  * Function is able to search a variety of code sets. See the 'external' items in the comments at top
 290  * of this page for a listing of the code sets supported. Also note that Products (using PROD as code type)
 291  * is also supported.
 292  *
 293  * @param  string    $form_code_type  code set key (special keywords are PROD and --ALL--)
 294  * @param  string    $search_term     search term
 295  * @param  boolean   $count           if true, then will only return the number of entries
 296  * @param  boolean   $active          if true, then will only return active entries (not pertinent for PROD code sets)
 297  * @param  boolean   $return_only_one if true, then will only return one perfect matching item
 298  * @param  integer   $start           Query start limit
 299  * @param  integer   $number          Query number returned
 300  * @param  array     $filter_elements Array that contains elements to filter
 301  * @param array      $limit           Number of results to return (NULL means return all); note this is ignored if set $start/number
 302  * @param array      $mode            'default' mode searches code and description, 'code' mode only searches code, 'description' mode searches description (and separates words); note this is ignored if set $return_only_one to TRUE
 303  * @return recordset/integer
 304  */
 305 function code_set_search($form_code_type,$search_term="",$count=false,$active=true,$return_only_one=false,$start=NULL,$number=NULL,$filter_elements=array(),$limit=NULL,$mode='default') {
 306   global $code_types,$code_external_tables;
 307
 308   // Figure out the appropriate limit clause
 309   if ( !is_null($start) && !is_null($number) ) {
 310     // For pagination of results
 311     $limit_query = " LIMIT $start, $number ";
 312   }
 313   else if (!is_null($limit)) {
 314     $limit_query = " LIMIT $limit ";
 315   }
 316   else {
 317     // No pagination and no limit
 318     $limit_query = '';
 319   }
 320
 321   if ($return_only_one) {
 322      // Only return one result (this is where only matching for exact code match)
 323      // Note this overrides the above limit settings
 324      $limit_query = " LIMIT 1 ";
 325   }
 326   // End determining limit clause
 327
 328   // build the filter_elements sql code
 329   $query_filter_elements="";
 330   if (!empty($filter_elements)) {
 331    foreach ($filter_elements as $key => $element) {
 332     $query_filter_elements .= " AND c." . add_escape_custom($key) . "=" . "'" . add_escape_custom($element)  . "' ";
 333    }
 334   }
 335
 336   if ($form_code_type == 'PROD') { // Search for products/drugs
 337    if ($count) {
 338        $query = "SELECT count(dt.drug_id) as count ";
 339    }
 340    else {
 341        $query = "SELECT dt.drug_id, dt.selector, d.name ";
 342    }
 343        $query .= "FROM drug_templates AS dt, drugs AS d WHERE " .
 344                  "( d.name LIKE ? OR " .
 345                  "dt.selector LIKE ? ) " .
 346                  "AND d.drug_id = dt.drug_id " .
 347                  "ORDER BY d.name, dt.selector, dt.drug_id $limit_query";
 348    $res = sqlStatement($query, array("%".$search_term."%", "%".$search_term."%") );
 349   }
 350   else { // Start a codes search
 351       // We are looking up the external table id here.  An "unset" value gets treated as 0(zero) without this test.  This way we can differentiate between "unset" and explicitly zero.
 352       $table_id=isset($code_types[$form_code_type]['external']) ? intval(($code_types[$form_code_type]['external'])) : -9999 ;
 353       if(($table_id>=0) || ($form_code_type == '--ALL--')) // Either we found a definition for the given code search or we are doing an "--ALL--" search, so start building the query
 354       {
 355
 356         // Place the common columns variable here since all check codes table
 357         $common_columns=" c.id, c.code_type, c.modifier, c.units, c.fee, " .
 358                         "c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ";
 359
 360         if ( $table_id==0  || ($form_code_type == '--ALL--') ) { // Search from default codes table.  --ALL-- only means all codes in the default tables
 361           if($table_id==0){ $table_info[EXT_FILTER_CLAUSES]=array("code_type=".$code_types[$form_code_type]['id']); } // Add a filter for the code type
 362           else {$table_info[EXT_FILTER_CLAUSES]=array("ct.ct_external=0");} // define filter array for "--ALL--"
 363           $table_dot="c.";              // $table_dot is used to prevent awkward looking concatenations when referring to columns in
 364           $code_col="code";
 365           $code_text_col="code_text";
 366
 367           $active_query = '';
 368              if ($active) {
 369               // Only filter for active codes
 370           $active_query=" AND c.active = 1 ";
 371           }
 372           $sql_bind_array = array();
 373           $columns = " c.code_text, c.code_text_short, c.code, ".$common_columns;
 374           if($table_id==0)
 375           {
 376               // If code type is specified, then include the "constant" as part of the query results for consistency
 377               $columns .= " '" . add_escape_custom($form_code_type) . "' as code_type_name ";
 378               if ($count) {
 379                   $query = "SELECT count(c.code) as count ";
 380               }
 381               else {
 382                   $query = "SELECT ".$columns;
 383               }
 384               $query .= " FROM `codes` as c ";
 385
 386           }
 387           else if($form_code_type=='--ALL--')
 388           {
 389              // For an "--ALL--" search we need to join with the code_types table to get the string representation of each returned code.
 390              $columns .= " ct.ct_key as code_type_name ";
 391               if ($count) {
 392                   $query = "SELECT count(c.code) as count ";
 393               }
 394               else {
 395                   $query = "SELECT ".$columns;
 396               }
 397              $query .=  " FROM `codes` as c " .
 398                         " LEFT OUTER JOIN `code_types` as ct " .
 399                         " ON c.code_type = ct.ct_id ";
 400           }
 401         }
 402         else if ($code_types[$form_code_type]['external'] > 0 ) { // Search from an external table with defined metadata
 403             $columns .= $common_columns . "'" . add_escape_custom($form_code_type) . "' as code_type_name ";
 404
 405             $active_query = '';
 406             if ($active) {
 407                 // Only filter for active codes.  Only the active column in the joined table
 408                 // is affected by this parameter.  Any filtering as a result of "active" status
 409                 // in the external table itself is always applied. I am implementing the behavior
 410                 // just as was done prior to the refactor
 411                 // - Kevin Yeh
 412                 // If there is no entry in codes sql table, then default to active
 413                 //  (this is reason for including NULL below)
 414                 $active_query=" AND (c.active = 1 || c.active IS NULL) ";
 415             }
 416
 417             // Get the basic metadata information
 418             $table_info=$code_external_tables[$table_id];
 419             $table=$table_info[EXT_TABLE_NAME];
 420             $table_dot=$table.".";
 421             $code_col=$table_info[EXT_COL_CODE];
 422             $code_text_col=$table_info[EXT_COL_DESCRIPTION];
 423
 424             // If the description is supposed to come from "joined" table instead of the "main",
 425             // the metadata defines a DISPLAY_DESCRIPTION element, and we use that to build up the query
 426             if($table_info[DISPLAY_DESCRIPTION]!="")
 427             {
 428                 $display_description=$table_info[DISPLAY_DESCRIPTION];
 429             }
 430             else
 431             {
 432                 $display_description=$table_dot.$code_text_col;
 433             }
 434             // Ensure the external table exists
 435             $check_table = sqlQuery("SHOW TABLES LIKE '".$table."'");
 436             if ( (empty($check_table)) ) {HelpfulDie("Missing table in code set search:".$table);}
 437
 438             $sql_bind_array = array();
 439             if ($count) {
 440                 // only collecting a count
 441                 $query = "SELECT count(".$table_dot.$code_col . ") as count ";
 442             }
 443             else {
 444                 $query = "SELECT ".$table_dot.$code_col . " as code, " .
 445                          $display_description . " as code_text, " .
 446                          $columns . " ";
 447             }
 448             $query .= " FROM ".$table.
 449                       " LEFT OUTER JOIN `codes` as c " .
 450                       " ON ".$table_dot.$code_col." = c.code AND c.code_type = ? ";
 451             array_push($sql_bind_array,$code_types[$form_code_type]['id']);
 452
 453             foreach($table_info[EXT_JOINS] as $join_info)
 454             {
 455               $join_table=$join_info[JOIN_TABLE];
 456               $check_table = sqlQuery("SHOW TABLES LIKE '".$join_table."'");
 457               if ( (empty($check_table)) ) {HelpfulDie("Missing join table in code set search:".$join_table);}
 458               $query.=" INNER JOIN ". $join_table;
 459               $query.=" ON ";
 460               $not_first=false;
 461               foreach($join_info[JOIN_FIELDS] as $field)
 462               {
 463                   if($not_first)
 464                   {
 465                       $query.=" AND ";
 466                   }
 467                   $query.=$field;
 468                   $not_first=true;
 469               }
 470             }
 471       } // End of block for handling external_id>0
 472
 473       // Setup the where clause based on MODE
 474       $query.= " WHERE ";
 475       if ($return_only_one) {
 476           $query .= $table_dot.$code_col." = ? ";
 477           array_push($sql_bind_array,$search_term);
 478       }
 479       else if($mode=="code") {
 480           $query.= $table_dot.$code_col." like ? ";
 481           array_push($sql_bind_array,$search_term."%");
 482       }
 483       else if($mode=="description"){
 484           $description_keywords=preg_split("/ /",$search_term,-1,PREG_SPLIT_NO_EMPTY);
 485           $query.="(1=1 ";
 486           foreach($description_keywords as $keyword)
 487           {
 488               $query.= " AND ".$table_dot.$code_text_col." LIKE ? ";
 489               array_push($sql_bind_array,"%".$keyword."%");
 490           }
 491           $query.=")";
 492         }
 493       else { // $mode == "default"
 494         $query .= "(".$table_dot.$code_text_col. " LIKE ? OR ".$table_dot.$code_col. " LIKE ?) ";
 495         array_push($sql_bind_array,"%".$search_term."%","%".$search_term."%");
 496       }
 497       // Done setting up the where clause by mode
 498
 499         // Add the metadata related filter clauses
 500         foreach($table_info[EXT_FILTER_CLAUSES] as $filter_clause)
 501         {
 502             $query.=" AND ";
 503             $dot_location=strpos($filter_clause,".");
 504             if($dot_location!==false) {
 505               // The filter clause already includes a table specifier, so don't add one
 506               $query .=$filter_clause;
 507             }
 508             else {
 509                 $query .=$table_dot.$filter_clause;
 510             }
 511         }
 512
 513         $query .=$active_query . $query_filter_elements;
 514
 515         $query .= " ORDER BY ".$table_dot.$code_col."+0,".$table_dot.$code_col;
 516         $query .= $limit_query;
 517
 518         $res = sqlStatement($query,$sql_bind_array);
 519       }
 520     else
 521     {
 522         HelpfulDie("Code type not active or not defined:".$join_info[JOIN_TABLE]);
 523     }
 524   } // End specific code type search
 525
 526   if (isset($res)) {
 527    if ($count) {
 528     // just return the count
 529     $ret = sqlFetchArray($res);
 530     return $ret['count'];
 531    }
 532    else {
 533     // return the data
 534     return $res;
 535    }
 536   }
 537 }
 538
 539 /**
 540  * Lookup Code Descriptions for one or more billing codes.
 541  *
 542  * Function is able to lookup code descriptions from a variety of code sets. See the 'external'
 543  * items in the comments at top of this page for a listing of the code sets supported.
 544  *
 545  * @param  string $codes  Is of the form "type:code;type:code; etc.".
 546  * @return string         Is of the form "description;description; etc.".
 547  */
 548 function lookup_code_descriptions($codes) {
 549   global $code_types, $code_external_tables;
 550   $code_text = '';
 551   if (!empty($codes)) {
 552     $relcodes = explode(';', $codes);
 553     foreach ($relcodes as $codestring) {
 554       if ($codestring === '') continue;
 555       list($codetype, $code) = explode(':', $codestring);
 556       $table_id=$code_types[$codetype]['external'];
 557       if(isset($code_external_tables[$table_id]))
 558       {
 559         $table_info=$code_external_tables[$table_id];
 560         $table_name=$table_info[EXT_TABLE_NAME];
 561         $code_col=$table_info[EXT_COL_CODE];
 562         $desc_col= $table_info[DISPLAY_DESCRIPTION]=="" ? $table_info[EXT_COL_DESCRIPTION] : $table_info[DISPLAY_DESCRIPTION];
 563         $sqlArray = array();
 564         $sql = "SELECT ".$desc_col." as code_text FROM ".$table_name;
 565
 566         // include the "JOINS" so that we get the preferred term instead of the FullySpecifiedName when appropriate.
 567         foreach($table_info[EXT_JOINS] as $join_info)
 568         {
 569           $join_table=$join_info[JOIN_TABLE];
 570           $check_table = sqlQuery("SHOW TABLES LIKE '".$join_table."'");
 571           if ( (empty($check_table)) ) {HelpfulDie("Missing join table in code set search:".$join_table);}
 572           $sql.=" INNER JOIN ". $join_table;
 573           $sql.=" ON ";
 574           $not_first=false;
 575           foreach($join_info[JOIN_FIELDS] as $field)
 576           {
 577               if($not_first)
 578               {
 579                   $sql.=" AND ";
 580               }
 581               $sql.=$field;
 582               $not_first=true;
 583           }
 584         }
 585
 586         $sql.=" WHERE ";
 587
 588
 589         // Start building up the WHERE clause
 590
 591         // When using the external codes table, we have to filter by the code_type.  (All the other tables only contain one type)
 592         if ($table_id==0) { $sql .= " code_type = '".add_escape_custom($code_types[$codetype]['id'])."' AND ";   }
 593
 594         // Specify the code in the query.
 595         $sql .= $table_name.".".$code_col."=? ";
 596         array_push($sqlArray,$code);
 597
 598         // We need to include the filter clauses
 599         // For SNOMED and SNOMED-CT this ensures that we get the Preferred Term or the Fully Specified Term as appropriate
 600         // It also prevents returning "inactive" results
 601         foreach($table_info[EXT_FILTER_CLAUSES] as $filter_clause)
 602         {
 603             $sql.= " AND ".$filter_clause;
 604         }
 605         // END building the WHERE CLAUSE
 606
 607
 608         if($table_info[EXT_VERSION_ORDER]){$sql .= " ORDER BY ".$table_info[EXT_VERSION_ORDER];}
 609
 610         $sql .= " LIMIT 1";
 611         $crow = sqlQuery($sql,$sqlArray);
 612         if (!empty($crow["code_text"])) {
 613           if ($code_text) $code_text .= '; ';
 614           $code_text .= $crow["code_text"];
 615         }
 616       }
 617
 618       else {
 619         //using an external code that is not yet supported, so skip.
 620       }
 621     }
 622   }
 623   return $code_text;
 624 }
 625
 626 /**
 627 * Sequential code set searching function
 628 *
 629 * Function is basically a wrapper of the code_set_search() function to support
 630 * a optimized searching models. The default mode will:
 631 * Searches codes first; then if no hits, it will then search the descriptions
 632 * (which are separated by each word in the code_set_search() function).
 633 *
 634 * @param string $form_code_type code set key (special keywords are PROD and --ALL--)
 635 * @param string $search_term search term
 636 * @param array $limit Number of results to return (NULL means return all)
 637 * @param array $modes Holds the search modes to process along with the order of processing (default behavior is described in above function comment)
 638 * @param boolean $count if true, then will only return the number of entries
 639 * @param boolean $active if true, then will only return active entries
 640 * @param integer $start Query start limit (for pagination)
 641 * @param integer $number Query number returned (for pagination)
 642 * @param array $filter_elements Array that contains elements to filter
 643 * @return recordset/integer
 644 */
 645 function sequential_code_set_search($form_code_type,$search_term,$limit=NULL,$modes=NULL,$count=false,$active=true,$start=NULL,$number=NULL,$filter_elements=array()) {
 646   // Set the default behavior that is described in above function comments
 647   if (empty($modes)) {
 648     $modes=array('code','description');
 649   }
 650
 651   // Return the Search Results (loop through each mode in order)
 652   foreach ($modes as $mode) {
 653     $res = code_set_search($form_code_type,$search_term,$count,$active,false,$start,$number,$filter_elements,$limit,$mode);
 654     if ( ($count && $res>0) || (!$count && sqlNumRows($res)>0) ) {
 655       // returns the count number if count is true or returns the data if count is false
 656       return $res;
 657     }
 658   }
 659 }
 660 ?>