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_COL_DESCRIPTION_BRIEF','description_brief');
  89 define('EXT_TABLE_NAME','table');
  90 define('EXT_FILTER_CLAUSES','filter_clause');
  91 define('EXT_VERSION_ORDER','filter_version_order');
  92 define('EXT_JOINS','joins');
  93 define('JOIN_TABLE','join');
  94 define('JOIN_FIELDS','fields');
  95 define('DISPLAY_DESCRIPTION',"display_description");
  96
  97 /**
  98  * This is a helper function for defining the metadata that describes the tables
  99  *
 100  * @param type $results             A reference to the global array which stores all the metadata
 101  * @param type $index               The external table ID.  This corresponds to the value in the code_types table in the ct_external column
 102  * @param type $table_name          The name of the table which stores the code informattion (e.g. icd9_dx_code
 103  * @param type $col_code            The name of the column which is the code
 104  * @param type $col_description     The name of the column which is the description
 105  * @param type $col_description_brief The name of the column which is the brief description
 106  * @param type $filter_clauses      An array of clauses to be included in the search "WHERE" clause that limits results
 107  * @param type $version_order       How to choose between different revisions of codes
 108  * @param type $joins               An array which describes additional tables to join as part of a code search.
 109  */
 110 function define_external_table(&$results, $index, $table_name,$col_code, $col_description,$col_description_brief,$filter_clauses=array(),$version_order="",$joins=array(),$display_desc="")
 111 {
 112     $results[$index]=array(EXT_TABLE_NAME=>$table_name,
 113                            EXT_COL_CODE=>$col_code,
 114                            EXT_COL_DESCRIPTION=>$col_description,
 115                            EXT_COL_DESCRIPTION_BRIEF=>$col_description_brief,
 116                            EXT_FILTER_CLAUSES=>$filter_clauses,
 117                            EXT_JOINS=>$joins,
 118                            EXT_VERSION_ORDER=>$version_order,
 119                            DISPLAY_DESCRIPTION=>$display_desc
 120                            );
 121 }
 122 // In order to treat all the code types the same for lookup_code_descriptions, we include metadata for the original codes table
 123 define_external_table($code_external_tables,0,'codes','code','code_text','code_text_short',array(),'id');
 124
 125 // ICD9 External Definitions
 126 define_external_table($code_external_tables,4,'icd9_dx_code','formatted_dx_code','long_desc','short_desc',array("active='1'"),'revision DESC');
 127 define_external_table($code_external_tables,5,'icd9_sg_code','formatted_sg_code','long_desc','short_desc',array("active='1'"),'revision DESC');
 128 //**** End ICD9 External Definitions
 129
 130 // SNOMED Definitions
 131 // For generic SNOMED-CT, there is no need to join with the descriptions table to get a specific description Type
 132
 133 // For generic concepts, use the fully specified description (DescriptionType=3) so we can tell the difference between them.
 134 define_external_table($code_external_tables,7,'sct_descriptions','ConceptId','Term','Term',array("DescriptionStatus=0","DescriptionType=3"),"");
 135
 136
 137 // To determine codes, we need to evaluate data in both the sct_descriptions table, and the sct_concepts table.
 138 // the base join with sct_concepts is the same for all types of SNOMED definitions, so we define the common part here
 139 $SNOMED_joins=array(JOIN_TABLE=>"sct_concepts",JOIN_FIELDS=>array("sct_descriptions.ConceptId=sct_concepts.ConceptId"));
 140
 141 // For disorders, use the preferred term (DescriptionType=1)
 142 define_external_table($code_external_tables,2,'sct_descriptions','ConceptId','Term','Term',array("DescriptionStatus=0","DescriptionType=1"),"",array($SNOMED_joins));
 143 // Add the filter to choose only disorders. This filter happens as part of the join with the sct_concepts table
 144 array_push($code_external_tables[2][EXT_JOINS][0][JOIN_FIELDS],"FullySpecifiedName like '%(disorder)'");
 145
 146 // SNOMED-PR definition
 147 define_external_table($code_external_tables,9,'sct_descriptions','ConceptId','Term','Term',array("DescriptionStatus=0","DescriptionType=1"),"",array($SNOMED_joins));
 148 // Add the filter to choose only procedures. This filter happens as part of the join with the sct_concepts table
 149 array_push($code_external_tables[9][EXT_JOINS][0][JOIN_FIELDS],"FullySpecifiedName like '%(procedure)'");
 150
 151
 152 //**** End SNOMED Definitions
 153
 154 // ICD 10 Definitions
 155 define_external_table($code_external_tables,1,'icd10_dx_order_code','formatted_dx_code','long_desc','short_desc',array("active='1'","valid_for_coding = '1'"),'revision DESC');
 156 define_external_table($code_external_tables,6,'icd10_pcs_order_code','pcs_code','long_desc','short_desc',array("active='1'","valid_for_coding = '1'"),'revision DESC');
 157 //**** End ICD 10 Definitions
 158
 159 /**
 160  * This array stores the external table options. See above for $code_types array
 161  * 'external' attribute  for explanation of the option listings.
 162  * @var array
 163  */
 164 $cd_external_options = array(
 165   '0' => xl('No'),
 166   '4' => xl('ICD9 Diagnosis'),
 167   '5' => xl('ICD9 Procedure/Service'),
 168   '1' => xl('ICD10 Diagnosis'),
 169   '6' => xl('ICD10 Procedure/Service'),
 170   '2' => xl('SNOMED (RF1) Diagnosis'),
 171   '7' => xl('SNOMED (RF1) Clinical Term'),
 172   '9' => xl('SNOMED (RF1) Procedure')
 173 );
 174
 175 /**
 176  * Checks is fee are applicable to any of the code types.
 177  *
 178  * @return boolean
 179  */
 180 function fees_are_used() {
 181  global $code_types;
 182  foreach ($code_types as $value) { if ($value['fee'] && $value['active']) return true; }
 183  return false;
 184 }
 185
 186 /**
 187  * Checks is modifiers are applicable to any of the code types.
 188  * (If a code type is not set to show in the fee sheet, then is ignored)
 189  *
 190  * @param  boolean $fee_sheet Will ignore code types that are not shown in the fee sheet
 191  * @return boolean
 192  */
 193 function modifiers_are_used($fee_sheet=false) {
 194  global $code_types;
 195  foreach ($code_types as $value) {
 196   if ($fee_sheet && !empty($value['nofs'])) continue;
 197   if ($value['mod'] && $value['active']) return true;
 198  }
 199  return false;
 200 }
 201
 202 /**
 203  * Checks if justifiers are applicable to any of the code types.
 204  *
 205  * @return boolean
 206  */
 207 function justifiers_are_used() {
 208  global $code_types;
 209  foreach ($code_types as $value) { if (!empty($value['just']) && $value['active']) return true; }
 210  return false;
 211 }
 212
 213 /**
 214  * Checks is related codes are applicable to any of the code types.
 215  *
 216  * @return boolean
 217  */
 218 function related_codes_are_used() {
 219  global $code_types;
 220  foreach ($code_types as $value) { if ($value['rel'] && $value['active']) return true; }
 221  return false;
 222 }
 223
 224 /**
 225  * Convert a code type id (ct_id) to the key string (ct_key)
 226  *
 227  * @param  integer $id
 228  * @return string
 229  */
 230 function convert_type_id_to_key($id) {
 231  global $code_types;
 232  foreach ($code_types as $key => $value) {
 233   if ($value['id'] == $id) return $key;
 234  }
 235 }
 236
 237 /**
 238  * Return listing of pertinent and active code types.
 239  *
 240  * Function will return listing (ct_key) of pertinent
 241  * active code types, such as diagnosis codes or procedure
 242  * codes in a chosen format. Supported returned formats include
 243  * as 1) an array and as 2) a comma-separated lists that has been
 244  * process by urlencode() in order to place into URL  address safely.
 245  *
 246  * @param  string       $category       category of code types('diagnosis', 'procedure', 'clinical_term' or 'active')
 247  * @param  string       $return_format  format or returned code types ('array' or 'csv')
 248  * @return string/array
 249  */
 250 function collect_codetypes($category,$return_format="array") {
 251  global $code_types;
 252
 253  $return = array();
 254
 255  foreach ($code_types as $ct_key => $ct_arr) {
 256   if (!$ct_arr['active']) continue;
 257
 258   if ($category == "diagnosis") {
 259    if ($ct_arr['diag']) {
 260     array_push($return,$ct_key);
 261    }
 262   }
 263   else if ($category == "procedure") {
 264    if ($ct_arr['proc']) {
 265     array_push($return,$ct_key);
 266    }
 267   }
 268   else if ($category == "clinical_term") {
 269    if ($ct_arr['term']) {
 270     array_push($return,$ct_key);
 271    }
 272   }
 273   else if ($category == "active") {
 274    if ($ct_arr['active']) {
 275     array_push($return,$ct_key);
 276    }
 277   }
 278   else {
 279    //return nothing since no supported category was chosen
 280   }
 281  }
 282
 283  if ($return_format == "csv") {
 284   //return it as a csv string
 285   return csv_like_join($return);
 286  }
 287  else { //$return_format == "array"
 288   //return the array
 289   return $return;
 290  }
 291 }
 292
 293 /**
 294  * Return the code information for a specific code.
 295  *
 296  * Function is able to search a variety of code sets. See the code type items in the comments at top
 297  * of this page for a listing of the code sets supported.
 298  *
 299  * @param  string    $form_code_type  code set key
 300  * @param  string    $code            code
 301  * @param  boolean   $active          if true, then will only return active entries (not pertinent for PROD code sets)
 302  * @return recordset                  will contain only one item (row).
 303  */
 304 function return_code_information($form_code_type,$code,$active=true) {
 305   return code_set_search($form_code_type,$code,false,$active,true);
 306 }
 307
 308 /**
 309 * The main code set searching function.
 310 *
 311 * It will work for searching one or numerous code sets simultaneously.
 312 * Note that when searching numerous code sets, you CAN NOT search the PROD
 313 * codes; the PROD codes can only be searched by itself.
 314 *
 315 * @param string/array  $form_code_type   code set key(s) (can either be one key in a string or multiple/one key(s) in an array
 316 * @param string        $search_term      search term
 317 * @param integer       $limit            Number of results to return (NULL means return all)
 318 * @param string        $category         Category of code sets. This WILL OVERRIDE the $form_code_type setting (category options can be found in the collect_codetypes() function above)
 319 * @param boolean       $active           if true, then will only return active entries
 320 * @param array         $modes            Holds the search modes to process along with the order of processing (if NULL, then default behavior is sequential code then description search)
 321 * @param boolean       $count            if true, then will only return the number of entries
 322 * @param integer       $start            Query start limit (for pagination) (Note this setting will override the above $limit parameter)
 323 * @param integer       $number           Query number returned (for pagination) (Note this setting will override the above $limit parameter)
 324 * @param array         $filter_elements  Array that contains elements to filter
 325 * @return recordset/integer              Will contain either a integer(if counting) or the results (recordset)
 326 */
 327 function main_code_set_search($form_code_type,$search_term,$limit=NULL,$category=NULL,$active=true,$modes=NULL,$count=false,$start=NULL,$number=NULL,$filter_elements=array()) {
 328
 329   // check for a category
 330   if (!empty($category)) {
 331     $form_code_type = collect_codetypes($category,"array");
 332   }
 333
 334   // do the search
 335   if (!empty($form_code_type)) {
 336     if ( is_array($form_code_type) && (count($form_code_type) > 1) ) {
 337       // run the multiple code set search
 338       return multiple_code_set_search($form_code_type,$search_term,$limit,$modes,$count,$active,$start,$number,$filter_elements);
 339     }
 340     if ( is_array($form_code_type) && (count($form_code_type) == 1) ) {
 341       // prepare the variable (ie. convert the one array item to a string) for the non-multiple code set search
 342       $form_code_type = $form_code_type[0];
 343     }
 344     // run the non-multiple code set search
 345     return sequential_code_set_search($form_code_type,$search_term,$limit,$modes,$count,$active,$start,$number,$filter_elements);
 346   }
 347 }
 348
 349 /**
 350  * Main "internal" code set searching function.
 351  *
 352  * Function is able to search a variety of code sets. See the 'external' items in the comments at top
 353  * of this page for a listing of the code sets supported. Also note that Products (using PROD as code type)
 354  * is also supported. (This function is not meant to be called directly)
 355  *
 356  * @param  string    $form_code_type  code set key (special keywords are PROD) (Note --ALL-- has been deprecated and should be run through the multiple_code_set_search() function instead)
 357  * @param  string    $search_term     search term
 358  * @param  boolean   $count           if true, then will only return the number of entries
 359  * @param  boolean   $active          if true, then will only return active entries (not pertinent for PROD code sets)
 360  * @param  boolean   $return_only_one if true, then will only return one perfect matching item
 361  * @param  integer   $start           Query start limit
 362  * @param  integer   $number          Query number returned
 363  * @param  array     $filter_elements Array that contains elements to filter
 364  * @param  integer   $limit           Number of results to return (NULL means return all); note this is ignored if set $start/number
 365  * @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
 366  * @param  array     $return_query    This is a mode that will only return the query (everything except for the LIMIT is included) (returned as an array to include the query string and binding array)
 367  * @return recordset/integer/array
 368  */
 369 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',$return_query=false) {
 370   global $code_types,$code_external_tables;
 371
 372   // Figure out the appropriate limit clause
 373   $limit_query = limit_query_string($limit,$start,$number,$return_only_one);
 374
 375   // build the filter_elements sql code
 376   $query_filter_elements="";
 377   if (!empty($filter_elements)) {
 378    foreach ($filter_elements as $key => $element) {
 379     $query_filter_elements .= " AND codes." . add_escape_custom($key) . "=" . "'" . add_escape_custom($element)  . "' ";
 380    }
 381   }
 382
 383   if ($form_code_type == 'PROD') { // Search for products/drugs
 384    if ($count) {
 385        $query = "SELECT count(dt.drug_id) as count ";
 386    }
 387    else {
 388        $query = "SELECT dt.drug_id, dt.selector, d.name ";
 389    }
 390        $query .= "FROM drug_templates AS dt, drugs AS d WHERE " .
 391                  "( d.name LIKE ? OR " .
 392                  "dt.selector LIKE ? ) " .
 393                  "AND d.drug_id = dt.drug_id " .
 394                  "ORDER BY d.name, dt.selector, dt.drug_id $limit_query";
 395    $res = sqlStatement($query, array("%".$search_term."%", "%".$search_term."%") );
 396   }
 397   else { // Start a codes search
 398       // 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.
 399       $table_id=isset($code_types[$form_code_type]['external']) ? intval(($code_types[$form_code_type]['external'])) : -9999 ;
 400       if($table_id>=0) // We found a definition for the given code search, so start building the query
 401       {
 402             // Place the common columns variable here since all check codes table
 403             $common_columns=" codes.id, codes.code_type, codes.modifier, codes.units, codes.fee, " .
 404                             "codes.superbill, codes.related_code, codes.taxrates, codes.cyp_factor, " .
 405                             "codes.active, codes.reportable, codes.financial_reporting, ";
 406             $columns .= $common_columns . "'" . add_escape_custom($form_code_type) . "' as code_type_name ";
 407
 408             $active_query = '';
 409             if ($active) {
 410                 // Only filter for active codes.  Only the active column in the joined table
 411                 // is affected by this parameter.  Any filtering as a result of "active" status
 412                 // in the external table itself is always applied. I am implementing the behavior
 413                 // just as was done prior to the refactor
 414                 // - Kevin Yeh
 415                 // If there is no entry in codes sql table, then default to active
 416                 //  (this is reason for including NULL below)
 417                 if ($table_id==0) {
 418                     // Search from default codes table
 419                     $active_query=" AND codes.active = 1 ";
 420                 }
 421                 else {
 422                     // Search from external tables
 423                     $active_query=" AND (codes.active = 1 || codes.active IS NULL) ";
 424                 }
 425             }
 426
 427             // Get/set the basic metadata information
 428             $table_info=$code_external_tables[$table_id];
 429             $table=$table_info[EXT_TABLE_NAME];
 430             $table_dot=$table.".";
 431             $code_col=$table_info[EXT_COL_CODE];
 432             $code_text_col=$table_info[EXT_COL_DESCRIPTION];
 433             $code_text_short_col=$table_info[EXT_COL_DESCRIPTION_BRIEF];
 434             if ($table_id==0) {
 435                 $table_info[EXT_FILTER_CLAUSES]=array("code_type=".$code_types[$form_code_type]['id']); // Add a filter for the code type
 436             }
 437             $code_external = $code_types[$form_code_type]['external'];
 438
 439             // If the description is supposed to come from "joined" table instead of the "main",
 440             // the metadata defines a DISPLAY_DESCRIPTION element, and we use that to build up the query
 441             if($table_info[DISPLAY_DESCRIPTION]!="")
 442             {
 443                 $display_description=$table_info[DISPLAY_DESCRIPTION];
 444                 $display_description_brief=$table_info[DISPLAY_DESCRIPTION];
 445             }
 446             else
 447             {
 448                 $display_description=$table_dot.$code_text_col;
 449                 $display_description_brief=$table_dot.$code_text_short_col;
 450             }
 451             // Ensure the external table exists
 452             $check_table = sqlQuery("SHOW TABLES LIKE '".$table."'");
 453             if ( (empty($check_table)) ) {HelpfulDie("Missing table in code set search:".$table);}
 454
 455             $sql_bind_array = array();
 456             if ($count) {
 457                 // only collecting a count
 458                 $query = "SELECT count(".$table_dot.$code_col . ") as count ";
 459             }
 460             else {
 461                 $query = "SELECT '" . $code_external ."' as code_external, " .
 462                          $table_dot.$code_col . " as code, " .
 463                          $display_description . " as code_text, " .
 464                          $display_description_brief . " as code_text_short, " .
 465                          $columns . " ";
 466             }
 467             if ($table_id==0) {
 468                 // Search from default codes table
 469                 $query .= " FROM ".$table." ";
 470             }
 471             else {
 472                 // Search from external tables
 473                 $query .= " FROM ".$table.
 474                           " LEFT OUTER JOIN `codes` " .
 475                           " ON ".$table_dot.$code_col." = codes.code AND codes.code_type = ? ";
 476                 array_push($sql_bind_array,$code_types[$form_code_type]['id']);
 477             }
 478
 479             foreach($table_info[EXT_JOINS] as $join_info)
 480             {
 481               $join_table=$join_info[JOIN_TABLE];
 482               $check_table = sqlQuery("SHOW TABLES LIKE '".$join_table."'");
 483               if ( (empty($check_table)) ) {HelpfulDie("Missing join table in code set search:".$join_table);}
 484               $query.=" INNER JOIN ". $join_table;
 485               $query.=" ON ";
 486               $not_first=false;
 487               foreach($join_info[JOIN_FIELDS] as $field)
 488               {
 489                   if($not_first)
 490                   {
 491                       $query.=" AND ";
 492                   }
 493                   $query.=$field;
 494                   $not_first=true;
 495               }
 496             }
 497
 498       // Setup the where clause based on MODE
 499       $query.= " WHERE ";
 500       if ($return_only_one) {
 501           $query .= $table_dot.$code_col." = ? ";
 502           array_push($sql_bind_array,$search_term);
 503       }
 504       else if($mode=="code") {
 505           $query.= $table_dot.$code_col." like ? ";
 506           array_push($sql_bind_array,$search_term."%");
 507       }
 508       else if($mode=="description"){
 509           $description_keywords=preg_split("/ /",$search_term,-1,PREG_SPLIT_NO_EMPTY);
 510           $query.="(1=1 ";
 511           foreach($description_keywords as $keyword)
 512           {
 513               $query.= " AND ".$table_dot.$code_text_col." LIKE ? ";
 514               array_push($sql_bind_array,"%".$keyword."%");
 515           }
 516           $query.=")";
 517         }
 518       else { // $mode == "default"
 519         $query .= "(".$table_dot.$code_text_col. " LIKE ? OR ".$table_dot.$code_col. " LIKE ?) ";
 520         array_push($sql_bind_array,"%".$search_term."%","%".$search_term."%");
 521       }
 522       // Done setting up the where clause by mode
 523
 524         // Add the metadata related filter clauses
 525         foreach($table_info[EXT_FILTER_CLAUSES] as $filter_clause)
 526         {
 527             $query.=" AND ";
 528             $dot_location=strpos($filter_clause,".");
 529             if($dot_location!==false) {
 530               // The filter clause already includes a table specifier, so don't add one
 531               $query .=$filter_clause;
 532             }
 533             else {
 534                 $query .=$table_dot.$filter_clause;
 535             }
 536         }
 537
 538         $query .=$active_query . $query_filter_elements;
 539
 540         $query .= " ORDER BY ".$table_dot.$code_col."+0,".$table_dot.$code_col;
 541
 542         if ($return_query) {
 543           // Just returning the actual query without the LIMIT information in it. This
 544           // information can then be used to combine queries of different code types
 545           // via the mysql UNION command. Returning an array to contain the query string
 546           // and the binding parameters.
 547           return array('query'=>$query,'binds'=>$sql_bind_array);
 548         }
 549
 550         $query .= $limit_query;
 551
 552         $res = sqlStatement($query,$sql_bind_array);
 553       }
 554     else
 555     {
 556         HelpfulDie("Code type not active or not defined:".$join_info[JOIN_TABLE]);
 557     }
 558   } // End specific code type search
 559
 560   if (isset($res)) {
 561    if ($count) {
 562     // just return the count
 563     $ret = sqlFetchArray($res);
 564     return $ret['count'];
 565    }
 566    else {
 567     // return the data
 568     return $res;
 569    }
 570   }
 571 }
 572
 573 /**
 574  * Lookup Code Descriptions for one or more billing codes.
 575  *
 576  * Function is able to lookup code descriptions from a variety of code sets. See the 'external'
 577  * items in the comments at top of this page for a listing of the code sets supported.
 578  *
 579  * @param  string $codes  Is of the form "type:code;type:code; etc.".
 580  * @param  string $desc_detail Can choose either the normal description('code_text') or the brief description('code_text_short').
 581  * @return string         Is of the form "description;description; etc.".
 582  */
 583 function lookup_code_descriptions($codes,$desc_detail="code_text") {
 584   global $code_types, $code_external_tables;
 585
 586   // ensure $desc_detail is set properly
 587   if ( ($desc_detail != "code_text") && ($desc_detail != "code_text_short") ) {
 588     $desc_detail="code_text";
 589   }
 590
 591   $code_text = '';
 592   if (!empty($codes)) {
 593     $relcodes = explode(';', $codes);
 594     foreach ($relcodes as $codestring) {
 595       if ($codestring === '') continue;
 596       list($codetype, $code) = explode(':', $codestring);
 597       $table_id=$code_types[$codetype]['external'];
 598       if(isset($code_external_tables[$table_id]))
 599       {
 600         $table_info=$code_external_tables[$table_id];
 601         $table_name=$table_info[EXT_TABLE_NAME];
 602         $code_col=$table_info[EXT_COL_CODE];
 603         $desc_col= $table_info[DISPLAY_DESCRIPTION]=="" ? $table_info[EXT_COL_DESCRIPTION] : $table_info[DISPLAY_DESCRIPTION];
 604         $desc_col_short= $table_info[DISPLAY_DESCRIPTION]=="" ? $table_info[EXT_COL_DESCRIPTION_BRIEF] : $table_info[DISPLAY_DESCRIPTION];
 605         $sqlArray = array();
 606         $sql = "SELECT ".$desc_col." as code_text,".$desc_col_short." as code_text_short FROM ".$table_name;
 607
 608         // include the "JOINS" so that we get the preferred term instead of the FullySpecifiedName when appropriate.
 609         foreach($table_info[EXT_JOINS] as $join_info)
 610         {
 611           $join_table=$join_info[JOIN_TABLE];
 612           $check_table = sqlQuery("SHOW TABLES LIKE '".$join_table."'");
 613           if ( (empty($check_table)) ) {HelpfulDie("Missing join table in code set search:".$join_table);}
 614           $sql.=" INNER JOIN ". $join_table;
 615           $sql.=" ON ";
 616           $not_first=false;
 617           foreach($join_info[JOIN_FIELDS] as $field)
 618           {
 619               if($not_first)
 620               {
 621                   $sql.=" AND ";
 622               }
 623               $sql.=$field;
 624               $not_first=true;
 625           }
 626         }
 627
 628         $sql.=" WHERE ";
 629
 630
 631         // Start building up the WHERE clause
 632
 633         // When using the external codes table, we have to filter by the code_type.  (All the other tables only contain one type)
 634         if ($table_id==0) { $sql .= " code_type = '".add_escape_custom($code_types[$codetype]['id'])."' AND ";   }
 635
 636         // Specify the code in the query.
 637         $sql .= $table_name.".".$code_col."=? ";
 638         array_push($sqlArray,$code);
 639
 640         // We need to include the filter clauses
 641         // For SNOMED and SNOMED-CT this ensures that we get the Preferred Term or the Fully Specified Term as appropriate
 642         // It also prevents returning "inactive" results
 643         foreach($table_info[EXT_FILTER_CLAUSES] as $filter_clause)
 644         {
 645             $sql.= " AND ".$filter_clause;
 646         }
 647         // END building the WHERE CLAUSE
 648
 649
 650         if($table_info[EXT_VERSION_ORDER]){$sql .= " ORDER BY ".$table_info[EXT_VERSION_ORDER];}
 651
 652         $sql .= " LIMIT 1";
 653         $crow = sqlQuery($sql,$sqlArray);
 654         if (!empty($crow[$desc_detail])) {
 655           if ($code_text) $code_text .= '; ';
 656           $code_text .= $crow[$desc_detail];
 657         }
 658       }
 659
 660       else {
 661         //using an external code that is not yet supported, so skip.
 662       }
 663     }
 664   }
 665   return $code_text;
 666 }
 667
 668 /**
 669 * Sequential code set "internal" searching function
 670 *
 671 * Function is basically a wrapper of the code_set_search() function to support
 672 * a optimized searching models. The default mode will:
 673 * Searches codes first; then if no hits, it will then search the descriptions
 674 * (which are separated by each word in the code_set_search() function).
 675 * (This function is not meant to be called directly)
 676 *
 677 * @param string $form_code_type code set key (special keyword is PROD) (Note --ALL-- has been deprecated and should be run through the multiple_code_set_search() function instead)
 678 * @param string $search_term search term
 679 * @param integer $limit Number of results to return (NULL means return all)
 680 * @param array $modes Holds the search modes to process along with the order of processing (default behavior is described in above function comment)
 681 * @param boolean $count if true, then will only return the number of entries
 682 * @param boolean $active if true, then will only return active entries
 683 * @param integer $start Query start limit (for pagination)
 684 * @param integer $number Query number returned (for pagination)
 685 * @param array $filter_elements Array that contains elements to filter
 686 * @param string $is_hit_mode This is a mode that simply returns the name of the mode if results were found
 687 * @return recordset/integer/string
 688 */
 689 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(),$is_hit_mode=false) {
 690   // Set the default behavior that is described in above function comments
 691   if (empty($modes)) {
 692     $modes=array('code','description');
 693   }
 694
 695   // Return the Search Results (loop through each mode in order)
 696   foreach ($modes as $mode) {
 697     $res = code_set_search($form_code_type,$search_term,$count,$active,false,$start,$number,$filter_elements,$limit,$mode);
 698     if ( ($count && $res>0) || (!$count && sqlNumRows($res)>0) ) {
 699       if ($is_hit_mode) {
 700         // just return the mode
 701         return $mode;
 702       }
 703       else {
 704         // returns the count number if count is true or returns the data if count is false
 705         return $res;
 706       }
 707     }
 708   }
 709 }
 710
 711 /**
 712 * Code set searching "internal" function for when searching multiple code sets.
 713 *
 714 * It will also work for one code set search, although not meant for this.
 715 * (This function is not meant to be called directly)
 716 *
 717 * @param array $form_code_types code set keys (will default to checking all active code types if blank)
 718 * @param string $search_term search term
 719 * @param integer $limit Number of results to return (NULL means return all)
 720 * @param array $modes Holds the search modes to process along with the order of processing (default behavior is described in above function comment)
 721 * @param boolean $count if true, then will only return the number of entries
 722 * @param boolean $active if true, then will only return active entries
 723 * @param integer $start Query start limit (for pagination)
 724 * @param integer $number Query number returned (for pagination)
 725 * @param array $filter_elements Array that contains elements to filter
 726 * @return recordset/integer
 727 */
 728 function multiple_code_set_search($form_code_types=array(),$search_term,$limit=NULL,$modes=NULL,$count=false,$active=true,$start=NULL,$number=NULL,$filter_elements=array()) {
 729
 730   if (empty($form_code_types)) {
 731     // Collect the active code types
 732     $form_code_types = collect_codetypes("active","array");
 733   }
 734
 735   if ($count) {
 736     //start the counter
 737     $counter = 0;
 738   }
 739   else {
 740     // Figure out the appropriate limit clause
 741     $limit_query = limit_query_string($limit,$start,$number);
 742
 743     // Prepare the sql bind array
 744     $sql_bind_array = array();
 745
 746     // Start the query string
 747     $query = "SELECT * FROM ((";
 748   }
 749
 750   // Loop through each code type
 751   $flag_first = true;
 752   $flag_hit = false; //ensure there is a hit to avoid trying an empty query
 753   foreach ($form_code_types as $form_code_type) {
 754     // see if there is a hit
 755     $mode_hit = NULL;
 756     // only use the count method here, since it's much more efficient than doing the actual query
 757     $mode_hit = sequential_code_set_search($form_code_type,$search_term,NULL,$modes,true,$active,NULL,NULL,$filter_elements,true);
 758     if ($mode_hit) {
 759       if ($count) {
 760         // count the hits
 761         $count_hits = code_set_search($form_code_type,$search_term,$count,$active,false,NULL,NULL,$filter_elements,NULL,$mode_hit);
 762         // increment the counter
 763         $counter += $count_hits;
 764       }
 765       else {
 766         $flag_hit = true;
 767         // build the query
 768         $return_query = code_set_search($form_code_type,$search_term,$count,$active,false,NULL,NULL,$filter_elements,NULL,$mode_hit,true);
 769         if (!empty($sql_bind_array)) {
 770           $sql_bind_array = array_merge($sql_bind_array,$return_query['binds']);
 771         }
 772         else {
 773           $sql_bind_array = $return_query['binds'];
 774         }
 775         if (!$flag_first) {
 776           $query .= ") UNION ALL (";
 777         }
 778         $query .= $return_query['query'];
 779       }
 780       $flag_first = false;
 781     }
 782   }
 783
 784   if ($count) {
 785     //return the count
 786     return $counter;
 787   }
 788   else {
 789     // Finish the query string
 790     $query .= ")) as atari $limit_query";
 791
 792     // Process and return the query (if there was a hit)
 793     if ($flag_hit) {
 794       return sqlStatement($query,$sql_bind_array);
 795     }
 796   }
 797 }
 798
 799 /**
 800 * Returns the limit to be used in the sql query for code set searches.
 801 *
 802 * @param  integer  $limit            Number of results to return (NULL means return all)
 803 * @param  integer  $start            Query start limit (for pagination)
 804 * @param  integer  $number           Query number returned (for pagination)
 805 * @param  boolean  $return_only_one  if true, then will only return one perfect matching item
 806 * @return recordset/integer
 807 */
 808 function limit_query_string($limit=NULL,$start=NULL,$number=NULL,$return_only_one=false) {
 809   if ( !is_null($start) && !is_null($number) ) {
 810     // For pagination of results
 811     $limit_query = " LIMIT $start, $number ";
 812   }
 813   else if (!is_null($limit)) {
 814     $limit_query = " LIMIT $limit ";
 815   }
 816   else {
 817     // No pagination and no limit
 818     $limit_query = '';
 819   }
 820   if ($return_only_one) {
 821      // Only return one result (this is where only matching for exact code match)
 822      // Note this overrides the above limit settings
 823      $limit_query = " LIMIT 1 ";
 824   }
 825   return $limit_query;
 826 }
 827 ?>