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