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
 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    $query = "SELECT dt.drug_id, dt.selector, d.name " .
 338             "FROM drug_templates AS dt, drugs AS d WHERE " .
 339             "( d.name LIKE ? OR " .
 340             "dt.selector LIKE ? ) " .
 341             "AND d.drug_id = dt.drug_id " .
 342             "ORDER BY d.name, dt.selector, dt.drug_id $limit_query";
 343    $res = sqlStatement($query, array("%".$search_term."%", "%".$search_term."%") );
 344   }
 345   else { // Start a codes search
 346       // 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.
 347       $table_id=isset($code_types[$form_code_type]['external']) ? intval(($code_types[$form_code_type]['external'])) : -9999 ;
 348       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
 349       {
 350         if ( $table_id==0  || ($form_code_type == '--ALL--') ) { // Search from default codes table.  --ALL-- only means all codes in the default tables
 351           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
 352           else {$table_info[EXT_FILTER_CLAUSES]=array();} // define empty filter array for "--ALL--"
 353           $table_dot="c.";              // $table_dot is used to prevent awkward looking concatenations when referring to columns in
 354           $code_col="code";
 355           $code_text_col="code_text";
 356
 357           $active_query = '';
 358              if ($active) {
 359               // Only filter for active codes
 360           $active_query=" AND c.active = 1 ";
 361           }
 362           $sql_bind_array = array();
 363           $query = "SELECT c.id, c.code_text, c.code_text_short, c.code, c.code_type, c.modifier, c.units, c.fee, " .
 364                   "c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, ";
 365           if($table_id==0)
 366           {
 367               // If code type is specified, then include the "constant" as part of the query results for consistency
 368               $query .= "'" . add_escape_custom($form_code_type) . "' as code_type_name " .
 369                         "FROM `codes` as c ";
 370
 371           }
 372           else if($form_code_type=='--ALL--')
 373           {
 374              // For an "--ALL--" search we need to join with the code_types table to get the string representation of each returned code.
 375              $query .=  "ct.ct_key as code_type_name " .
 376                         " FROM `codes` as c " .
 377                         " LEFT OUTER JOIN `code_types` as ct " .
 378                         " ON c.code_type = ct.ct_id ";
 379           }
 380         }
 381         else if ($code_types[$form_code_type]['external'] > 0 ) { // Search from an external table with defined metadata
 382             $common_columns=",c.id, c.code_type, c.modifier, c.units, c.fee, " .
 383                      "c.superbill, c.related_code, c.taxrates, c.cyp_factor, c.active, c.reportable, c.financial_reporting, " .
 384                      "'" . add_escape_custom($form_code_type) . "' as code_type_name " .
 385
 386             $active_query = '';
 387             if ($active) {
 388                 // Only filter for active codes.  Only the active column in the joined table
 389                 // is affected by this parameter.  Any filtering as a result of "active" status
 390                 // in the external table itself is always applied. I am implementing the behavior
 391                 // just as was done prior to the refactor
 392                 // - Kevin Yeh
 393                 // If there is no entry in codes sql table, then default to active
 394                 //  (this is reason for including NULL below)
 395                 $active_query=" AND (c.active = 1 || c.active IS NULL) ";
 396             }
 397
 398             // Get the basic metadata information
 399             $table_info=$code_external_tables[$table_id];
 400             $table=$table_info[EXT_TABLE_NAME];
 401             $table_dot=$table.".";
 402             $code_col=$table_info[EXT_COL_CODE];
 403             $code_text_col=$table_info[EXT_COL_DESCRIPTION];
 404
 405             // If the description is supposed to come from "joined" table instead of the "main",
 406             // the metadata defines a DISPLAY_DESCRIPTION element, and we use that to build up the query
 407             if($table_info[DISPLAY_DESCRIPTION]!="")
 408             {
 409                 $display_description=$table_info[DISPLAY_DESCRIPTION];
 410             }
 411             else
 412             {
 413                 $display_description=$table_dot.$code_text_col;
 414             }
 415             // Ensure the external table exists
 416             $check_table = sqlQuery("SHOW TABLES LIKE '".$table."'");
 417             if ( (empty($check_table)) ) {HelpfulDie("Missing table in code set search:".$table);}
 418
 419             $sql_bind_array = array();
 420             $query = "SELECT ".$table_dot.$code_col . " as code, " .
 421                      $display_description . " as code_text " .
 422                      $common_columns .
 423              " FROM ".$table.
 424              " LEFT OUTER JOIN `codes` as c " .
 425              " ON ".$table_dot.$code_col." = c.code AND c.code_type = ? ";
 426             array_push($sql_bind_array,$code_types[$form_code_type]['id']);
 427
 428             foreach($table_info[EXT_JOINS] as $join_info)
 429             {
 430               $join_table=$join_info[JOIN_TABLE];
 431               $check_table = sqlQuery("SHOW TABLES LIKE '".$join_table."'");
 432               if ( (empty($check_table)) ) {HelpfulDie("Missing join table in code set search:".$join_table);}
 433               $query.=" INNER JOIN ". $join_table;
 434               $query.=" ON ";
 435               $not_first=false;
 436               foreach($join_info[JOIN_FIELDS] as $field)
 437               {
 438                   if($not_first)
 439                   {
 440                       $query.=" AND ";
 441                   }
 442                   $query.=$field;
 443                   $not_first=true;
 444               }
 445             }
 446       } // End of block for handling external_id>0
 447
 448       // Setup the where clause based on MODE
 449       $query.= " WHERE ";
 450       if ($return_only_one) {
 451           $query .= $table_dot.$code_col." = ? ";
 452           array_push($sql_bind_array,$search_term);
 453       }
 454       else if($mode=="code") {
 455           $query.= $table_dot.$code_col." like ? ";
 456           array_push($sql_bind_array,$search_term."%");
 457       }
 458       else if($mode=="description"){
 459           $description_keywords=preg_split("/ /",$search_term,-1,PREG_SPLIT_NO_EMPTY);
 460           $query.="(1=1 ";
 461           foreach($description_keywords as $keyword)
 462           {
 463               $query.= " AND ".$table_dot.$code_text_col." LIKE ? ";
 464               array_push($sql_bind_array,"%".$keyword."%");
 465           }
 466           $query.=")";
 467         }
 468       else { // $mode == "default"
 469         $query .= "(".$table_dot.$code_text_col. " LIKE ? OR ".$table_dot.$code_col. " LIKE ?) ";
 470         array_push($sql_bind_array,"%".$search_term."%","%".$search_term."%");
 471       }
 472       // Done setting up the where clause by mode
 473
 474         // Add the metadata related filter clauses
 475         foreach($table_info[EXT_FILTER_CLAUSES] as $filter_clause)
 476         {
 477             $query .= " AND ".$table_dot.$filter_clause;
 478         }
 479
 480         $query .=$active_query . $query_filter_elements;
 481
 482         $query .= " ORDER BY ".$table_dot.$code_col."+0,".$table_dot.$code_col;
 483         $query .= $limit_query;
 484
 485         $res = sqlStatement($query,$sql_bind_array);
 486       }
 487     else
 488     {
 489         HelpfulDie("Code type not active or not defined:".$join_info[JOIN_TABLE]);
 490     }
 491   } // End specific code type search
 492
 493   if (isset($res)) {
 494    if ($count) {
 495     // just return the count
 496     return sqlNumRows($res);
 497    }
 498    else {
 499     // return the data
 500     return $res;
 501    }
 502   }
 503 }
 504
 505 /**
 506  * Lookup Code Descriptions for one or more billing codes.
 507  *
 508  * Function is able to lookup code descriptions from a variety of code sets. See the 'external'
 509  * items in the comments at top of this page for a listing of the code sets supported.
 510  *
 511  * @param  string $codes  Is of the form "type:code;type:code; etc.".
 512  * @return string         Is of the form "description;description; etc.".
 513  */
 514 function lookup_code_descriptions($codes) {
 515   global $code_types, $code_external_tables;
 516   $code_text = '';
 517   if (!empty($codes)) {
 518     $relcodes = explode(';', $codes);
 519     foreach ($relcodes as $codestring) {
 520       if ($codestring === '') continue;
 521       list($codetype, $code) = explode(':', $codestring);
 522       $table_id=$code_types[$codetype]['external'];
 523       if(isset($code_external_tables[$table_id]))
 524       {
 525         $table_info=$code_external_tables[$table_id];
 526         $table_name=$table_info[EXT_TABLE_NAME];
 527         $code_col=$table_info[EXT_COL_CODE];
 528         $desc_col= $table_info[DISPLAY_DESCRIPTION]=="" ? $table_info[EXT_COL_DESCRIPTION] : $table_info[DISPLAY_DESCRIPTION];
 529         $sqlArray = array();
 530         $sql = "SELECT ".$desc_col." as code_text FROM ".$table_name;
 531
 532         // include the "JOINS" so that we get the preferred term instead of the FullySpecifiedName when appropriate.
 533         foreach($table_info[EXT_JOINS] as $join_info)
 534         {
 535           $join_table=$join_info[JOIN_TABLE];
 536           $check_table = sqlQuery("SHOW TABLES LIKE '".$join_table."'");
 537           if ( (empty($check_table)) ) {HelpfulDie("Missing join table in code set search:".$join_table);}
 538           $sql.=" INNER JOIN ". $join_table;
 539           $sql.=" ON ";
 540           $not_first=false;
 541           foreach($join_info[JOIN_FIELDS] as $field)
 542           {
 543               if($not_first)
 544               {
 545                   $sql.=" AND ";
 546               }
 547               $sql.=$field;
 548               $not_first=true;
 549           }
 550         }
 551
 552         $sql.=" WHERE ";
 553
 554
 555         // Start building up the WHERE clause
 556
 557         // When using the external codes table, we have to filter by the code_type.  (All the other tables only contain one type)
 558         if ($table_id==0) { $sql .= " code_type = '".add_escape_custom($code_types[$codetype]['id'])."' AND ";   }
 559
 560         // Specify the code in the query.
 561         $sql .= $table_name.".".$code_col."=? ";
 562         array_push($sqlArray,$code);
 563
 564         // We need to include the filter clauses
 565         // For SNOMED and SNOMED-CT this ensures that we get the Preferred Term or the Fully Specified Term as appropriate
 566         // It also prevents returning "inactive" results
 567         foreach($table_info[EXT_FILTER_CLAUSES] as $filter_clause)
 568         {
 569             $sql.= " AND ".$filter_clause;
 570         }
 571         // END building the WHERE CLAUSE
 572
 573
 574         if($table_info[EXT_VERSION_ORDER]){$sql .= " ORDER BY ".$table_info[EXT_VERSION_ORDER];}
 575
 576         $sql .= " LIMIT 1";
 577         $crow = sqlQuery($sql,$sqlArray);
 578         if (!empty($crow["code_text"])) {
 579           if ($code_text) $code_text .= '; ';
 580           $code_text .= $crow["code_text"];
 581         }
 582       }
 583
 584       else {
 585         //using an external code that is not yet supported, so skip.
 586       }
 587     }
 588   }
 589   return $code_text;
 590 }
 591
 592 /**
 593 * Sequential code set searching function
 594 *
 595 * Function is basically a wrapper of the code_set_search() function to support
 596 * an optimized searching model. Model searches codes first; then if no hits, it
 597 * will then search the descriptions (which are separated by each word in the
 598 * code_set_search() function).
 599 *
 600 * @param string $form_code_type code set key (special keywords are PROD and --ALL--)
 601 * @param string $search_term search term
 602 * @param array $limit Number of results to return (NULL means return all)
 603 * @param boolean $count if true, then will only return the number of entries
 604 * @param boolean $active if true, then will only return active entries
 605 * @param integer $start Query start limit (for pagination)
 606 * @param integer $number Query number returned (for pagination)
 607 * @param array $filter_elements Array that contains elements to filter
 608 * @return recordset
 609 */
 610 function sequential_code_set_search($form_code_type,$search_term,$limit=NULL,$count=false,$active=true,$start=NULL,$number=NULL,$filter_elements=array()) {
 611
 612   // Return the Search Results
 613   // Search by code first
 614   $res_code = code_set_search($form_code_type,$search_term,false,$active,false,$start,$number,$filter_elements,$limit,'code');
 615   if(sqlNumRows($res_code)>0) {
 616    if ($count) {
 617     // just return the count
 618     return sqlNumRows($res_code);
 619    }
 620    else {
 621     // return the data
 622     return $res_code;
 623    }
 624   }
 625   else {
 626    // no codes found, so search the descriptions;
 627    $res_desc = code_set_search($form_code_type,$search_term,false,$active,false,$start,$number,$filter_elements,$limit,'description');
 628    if(sqlNumRows($res_desc)>0) {
 629     if ($count) {
 630      // just return the count
 631      return sqlNumRows($res_desc);
 632     }
 633     else {
 634      // return the data
 635      return $res_desc;
 636     }
 637    }
 638   }
 639 }
 640 ?>