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