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