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