4 * Library and data structure to manage Code Types and code type lookups.
6 * The data structure is the $code_types array.
7 * The $code_types array is built from the code_types sql table and provides
8 * abstraction of diagnosis/billing code types. This is desirable
9 * because different countries or fields of practice use different methods for
10 * coding diagnoses, procedures and supplies. Fees will not be relevant where
11 * medical care is socialized.
12 * <pre>Attributes of the $code_types array are:
13 * active - 1 if this code type is activated
14 * id - the numeric identifier of this code type in the codes table
15 * claim - 1 if this code type is used in claims
16 * fee - 1 if fees are used, else 0
17 * mod - the maximum length of a modifier, 0 if modifiers are not used
18 * just - the code type used for justification, empty if none
19 * rel - 1 if other billing codes may be "related" to this code type
20 * nofs - 1 if this code type should NOT appear in the Fee Sheet
21 * diag - 1 if this code type is for diagnosis
22 * proc - 1 if this code type is a procedure/service
23 * label - label used for code type
24 * external - 0 for storing codes in the code table
25 * 1 for storing codes in external ICD10 Diagnosis tables
26 * 2 for storing codes in external SNOMED (RF1) Diagnosis tables
27 * 3 for storing codes in external SNOMED (RF2) Diagnosis tables
28 * 4 for storing codes in external ICD9 Diagnosis tables
29 * 5 for storing codes in external ICD9 Procedure/Service tables
30 * 6 for storing codes in external ICD10 Procedure/Service tables
31 * 7 for storing codes in external SNOMED Clinical Term tables
32 * 8 for storing codes in external SNOMED (RF2) Clinical Term tables (for future)
33 * 9 for storing codes in external SNOMED (RF1) Procedure Term tables
34 * 10 for storing codes in external SNOMED (RF2) Procedure Term tables (for future)
35 * term - 1 if this code type is used as a clinical term
36 * problem - 1 if this code type is used as a medical problem
37 * drug - 1 if this code type is used as a medication
43 * @link https://www.open-emr.org
44 * @author Rod Roark <rod@sunsetsystems.com>
45 * @author Brady Miller <brady.g.miller@gmail.com>
46 * @author Kevin Yeh <kevin.y@integralemr.com>
47 * @author Jerry Padgett <sjpadgett@gmail.com>
48 * @copyright Copyright (c) 2006-2010 Rod Roark <rod@sunsetsystems.com>
49 * @copyright Copyright (c) 2019 Brady Miller <brady.g.miller@gmail.com>
50 * @copyright Copyright (c) 2021-2022 Robert Down <robertdown@live.com>
51 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
54 use OpenEMR\Events\Codes\ExternalCodesCreatedEvent
;
55 use Symfony\Component\EventDispatcher\EventDispatcher
;
57 require_once(__DIR__
. "/../library/csv_like_join.php");
59 $code_types = array();
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 'problem' => $ctrow['ct_problem'],
79 'drug' => $ctrow['ct_drug']
81 if (!array_key_exists($GLOBALS['default_search_code_type'], $code_types)) {
83 $GLOBALS['default_search_code_type'] = key($code_types);
87 /** This array contains metadata describing the arrangement of the external data
88 * tables for storing codes.
90 $code_external_tables = array();
91 global $code_external_tables;
92 define('EXT_COL_CODE', 'code');
93 define('EXT_COL_DESCRIPTION', 'description');
94 define('EXT_COL_DESCRIPTION_BRIEF', 'description_brief');
95 define('EXT_TABLE_NAME', 'table');
96 define('EXT_FILTER_CLAUSES', 'filter_clause');
97 define('EXT_VERSION_ORDER', 'filter_version_order');
98 define('EXT_JOINS', 'joins');
99 define('JOIN_TABLE', 'join');
100 define('JOIN_FIELDS', 'fields');
101 define('DISPLAY_DESCRIPTION', "display_description");
104 * This is a helper function for defining the metadata that describes the tables
106 * @param type $results A reference to the global array which stores all the metadata
107 * @param type $index The external table ID. This corresponds to the value in the code_types table in the ct_external column
108 * @param type $table_name The name of the table which stores the code informattion (e.g. icd9_dx_code
109 * @param type $col_code The name of the column which is the code
110 * @param type $col_description The name of the column which is the description
111 * @param type $col_description_brief The name of the column which is the brief description
112 * @param type $filter_clauses An array of clauses to be included in the search "WHERE" clause that limits results
113 * @param type $version_order How to choose between different revisions of codes
114 * @param type $joins An array which describes additional tables to join as part of a code search.
116 function define_external_table(&$results, $index, $table_name, $col_code, $col_description, $col_description_brief, $filter_clauses = array(), $version_order = "", $joins = array(), $display_desc = "")
118 $results[$index] = array(EXT_TABLE_NAME
=> $table_name,
119 EXT_COL_CODE
=> $col_code,
120 EXT_COL_DESCRIPTION
=> $col_description,
121 EXT_COL_DESCRIPTION_BRIEF
=> $col_description_brief,
122 EXT_FILTER_CLAUSES
=> $filter_clauses,
124 EXT_VERSION_ORDER
=> $version_order,
125 DISPLAY_DESCRIPTION
=> $display_desc
128 // In order to treat all the code types the same for lookup_code_descriptions, we include metadata for the original codes table
129 define_external_table($code_external_tables, 0, 'codes', 'code', 'code_text', 'code_text_short', array(), 'id');
131 // ICD9 External Definitions
132 define_external_table($code_external_tables, 4, 'icd9_dx_code', 'formatted_dx_code', 'long_desc', 'short_desc', array("active='1'"), 'revision DESC');
133 define_external_table($code_external_tables, 5, 'icd9_sg_code', 'formatted_sg_code', 'long_desc', 'short_desc', array("active='1'"), 'revision DESC');
134 //**** End ICD9 External Definitions
136 // SNOMED Definitions
137 // For generic SNOMED-CT, there is no need to join with the descriptions table to get a specific description Type
139 // For generic concepts, use the fully specified description (DescriptionType=3) so we can tell the difference between them.
140 define_external_table($code_external_tables, 7, 'sct_descriptions', 'ConceptId', 'Term', 'Term', array("DescriptionStatus=0","DescriptionType=3"), "");
142 // To determine codes, we need to evaluate data in both the sct_descriptions table, and the sct_concepts table.
143 // the base join with sct_concepts is the same for all types of SNOMED definitions, so we define the common part here
144 $SNOMED_joins = array(JOIN_TABLE
=> "sct_concepts",JOIN_FIELDS
=> array("sct_descriptions.ConceptId=sct_concepts.ConceptId"));
146 // For disorders, use the preferred term (DescriptionType=1)
147 define_external_table($code_external_tables, 2, 'sct_descriptions', 'ConceptId', 'Term', 'Term', array("DescriptionStatus=0","DescriptionType=1"), "", array($SNOMED_joins));
148 // Add the filter to choose only disorders. This filter happens as part of the join with the sct_concepts table
149 array_push($code_external_tables[2][EXT_JOINS
][0][JOIN_FIELDS
], "FullySpecifiedName like '%(disorder)'");
151 // SNOMED-PR definition
152 define_external_table($code_external_tables, 9, 'sct_descriptions', 'ConceptId', 'Term', 'Term', array("DescriptionStatus=0","DescriptionType=1"), "", array($SNOMED_joins));
153 // Add the filter to choose only procedures. This filter happens as part of the join with the sct_concepts table
154 array_push($code_external_tables[9][EXT_JOINS
][0][JOIN_FIELDS
], "FullySpecifiedName like '%(procedure)'");
156 // SNOMED RF2 definitions
157 define_external_table($code_external_tables, 11, 'sct2_description', 'conceptId', 'term', 'term', array("active=1"), "");
158 if (isSnomedSpanish()) {
159 define_external_table($code_external_tables, 10, 'sct2_description', 'conceptId', 'term', 'term', array("active=1", "term LIKE '%(trastorno)'"), "");
160 define_external_table($code_external_tables, 12, 'sct2_description', 'conceptId', 'term', 'term', array("active=1", "term LIKE '%(procedimiento)'"), "");
162 define_external_table($code_external_tables, 10, 'sct2_description', 'conceptId', 'term', 'term', array("active=1", "term LIKE '%(disorder)'"), "");
163 define_external_table($code_external_tables, 12, 'sct2_description', 'conceptId', 'term', 'term', array("active=1", "term LIKE '%(procedure)'"), "");
166 //**** End SNOMED Definitions
168 // ICD 10 Definitions
169 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');
170 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');
171 //**** End ICD 10 Definitions
173 define_external_table($code_external_tables, 13, 'valueset', 'code', 'description', 'description', array(), '');
176 * This array stores the external table options. See above for $code_types array
177 * 'external' attribute for explanation of the option listings.
180 global $ct_external_options;
181 $ct_external_options = array(
183 '4' => xl('ICD9 Diagnosis'),
184 '5' => xl('ICD9 Procedure/Service'),
185 '1' => xl('ICD10 Diagnosis'),
186 '6' => xl('ICD10 Procedure/Service'),
187 '2' => xl('SNOMED (RF1) Diagnosis'),
188 '7' => xl('SNOMED (RF1) Clinical Term'),
189 '9' => xl('SNOMED (RF1) Procedure'),
190 '10' => xl('SNOMED (RF2) Diagnosis'),
191 '11' => xl('SNOMED (RF2) Clinical Term'),
192 '12' => xl('SNOMED (RF2) Procedure'),
193 '13' => xl('CQM (Mixed Types) Value Set')
197 * @var EventDispatcher
199 $eventDispatcher = $GLOBALS['kernel']->getEventDispatcher();
200 $externalCodesEvent = new ExternalCodesCreatedEvent($ct_external_options);
201 $eventDispatcher->dispatch($externalCodesEvent, ExternalCodesCreatedEvent
::EVENT_HANDLE
);
202 $ct_external_options = $externalCodesEvent->getExternalCodeData();
205 * Checks to see if using spanish snomed
207 function isSnomedSpanish()
209 // See if most recent SNOMED entry is International:Spanish
210 $sql = sqlQuery("SELECT `revision_version` FROM `standardized_tables_track` WHERE `name` = 'SNOMED' ORDER BY `id` DESC");
211 if ((!empty($sql)) && ($sql['revision_version'] == "International:Spanish")) {
218 * Checks is fee are applicable to any of the code types.
222 function fees_are_used()
225 foreach ($code_types as $value) {
226 if ($value['fee'] && $value['active']) {
235 * Checks if modifiers are applicable to any of the code types.
236 * (If a code type is not set to show in the fee sheet, then is ignored)
238 * @param boolean $fee_sheet Will ignore code types that are not shown in the fee sheet
241 function modifiers_are_used($fee_sheet = false)
244 foreach ($code_types as $value) {
245 if ($fee_sheet && !empty($value['nofs'])) {
249 if ($value['mod'] && $value['active']) {
258 * Checks if justifiers are applicable to any of the code types.
262 function justifiers_are_used()
265 foreach ($code_types as $value) {
266 if (!empty($value['just']) && $value['active']) {
275 * Checks is related codes are applicable to any of the code types.
279 function related_codes_are_used()
282 foreach ($code_types as $value) {
283 if ($value['rel'] && $value['active']) {
292 * Convert a code type id (ct_id) to the key string (ct_key)
297 function convert_type_id_to_key($id)
300 foreach ($code_types as $key => $value) {
301 if ($value['id'] == $id) {
308 * Checks to see if code allows justification (ct_just)
313 function check_is_code_type_justify($key)
317 if (!empty($code_types[$key]['just'])) {
325 * Checks if a key string (ct_key) is selected for an element/filter(s)
328 * @param array $filter (array of elements that can include 'active','fee','rel','nofs','diag','claim','proc','term','problem')
331 function check_code_set_filters($key, $filters = array())
335 if (empty($filters)) {
339 foreach ($filters as $filter) {
340 if (array_key_exists($key, $code_types)) {
341 if ($code_types[$key][$filter] != 1) {
352 * Return listing of pertinent and active code types.
354 * Function will return listing (ct_key) of pertinent
355 * active code types, such as diagnosis codes or procedure
356 * codes in a chosen format. Supported returned formats include
357 * as 1) an array and as 2) a comma-separated lists that has been
358 * process by urlencode() in order to place into URL address safely.
360 * @param string $category category of code types('diagnosis', 'procedure', 'clinical_term', 'active' or 'medical_problem')
361 * @param string $return_format format or returned code types ('array' or 'csv')
362 * @return string/array
364 function collect_codetypes($category, $return_format = "array")
370 foreach ($code_types as $ct_key => $ct_arr) {
371 if (!$ct_arr['active']) {
375 if ($category == "diagnosis") {
376 if ($ct_arr['diag']) {
379 } elseif ($category == "procedure") {
380 if ($ct_arr['proc']) {
383 } elseif ($category == "clinical_term") {
384 if ($ct_arr['term']) {
387 } elseif ($category == "active") {
388 if ($ct_arr['active']) {
391 } elseif ($category == "medical_problem") {
392 if ($ct_arr['problem']) {
395 } elseif ($category == "drug") {
396 if ($ct_arr['drug']) {
400 //return nothing since no supported category was chosen
404 if ($return_format == "csv") {
405 //return it as a csv string
406 return csv_like_join($return);
409 //$return_format == "array"
415 * Return the code information for a specific code.
417 * Function is able to search a variety of code sets. See the code type items in the comments at top
418 * of this page for a listing of the code sets supported.
420 * @param string $form_code_type code set key
421 * @param string $code code
422 * @param boolean $active if true, then will only return active entries (not pertinent for PROD code sets)
423 * @return mixed recordset - will contain only one item (row).
425 function return_code_information($form_code_type, $code, $active = true)
427 return code_set_search($form_code_type, $code, false, $active, true);
431 * The main code set searching function.
433 * It will work for searching one or numerous code sets simultaneously.
434 * Note that when searching numerous code sets, you CAN NOT search the PROD
435 * codes; the PROD codes can only be searched by itself.
437 * @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
438 * @param string $search_term search term
439 * @param integer $limit Number of results to return (NULL means return all)
440 * @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)
441 * @param boolean $active if true, then will only return active entries
442 * @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)
443 * @param boolean $count if true, then will only return the number of entries
444 * @param integer $start Query start limit (for pagination) (Note this setting will override the above $limit parameter)
445 * @param integer $number Query number returned (for pagination) (Note this setting will override the above $limit parameter)
446 * @param array $filter_elements Array that contains elements to filter
447 * @return mixed recordset/integer - Will contain either a integer(if counting) or the results (recordset)
449 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())
452 // check for a category
453 if (!empty($category)) {
454 $form_code_type = collect_codetypes($category, "array");
458 if (!empty($form_code_type)) {
459 if (is_array($form_code_type) && (count($form_code_type) > 1)) {
460 // run the multiple code set search
461 return multiple_code_set_search($form_code_type, $search_term, $limit, $modes, $count, $active, $start, $number, $filter_elements);
464 if (is_array($form_code_type) && (count($form_code_type) == 1)) {
465 // prepare the variable (ie. convert the one array item to a string) for the non-multiple code set search
466 $form_code_type = $form_code_type[0];
469 // run the non-multiple code set search
470 return sequential_code_set_search($form_code_type, $search_term, $limit, $modes, $count, $active, $start, $number, $filter_elements);
475 * Main "internal" code set searching function.
477 * Function is able to search a variety of code sets. See the 'external' items in the comments at top
478 * of this page for a listing of the code sets supported. Also note that Products (using PROD as code type)
479 * is also supported. (This function is not meant to be called directly)
481 * @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)
482 * @param string $search_term search term
483 * @param boolean $count if true, then will only return the number of entries
484 * @param boolean $active if true, then will only return active entries (not pertinent for PROD code sets)
485 * @param boolean $return_only_one if true, then will only return one perfect matching item
486 * @param integer $start Query start limit
487 * @param integer $number Query number returned
488 * @param array $filter_elements Array that contains elements to filter
489 * @param integer $limit Number of results to return (NULL means return all); note this is ignored if set $start/number
490 * @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
491 * @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)
492 * @return mixed recordset/integer/array
494 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)
496 global $code_types, $code_external_tables;
498 // Figure out the appropriate limit clause
499 $limit_query = limit_query_string($limit, $start, $number, $return_only_one);
501 // build the filter_elements sql code
502 $query_filter_elements = "";
503 if (!empty($filter_elements)) {
504 foreach ($filter_elements as $key => $element) {
505 $query_filter_elements .= " AND codes." . add_escape_custom($key) . "=" . "'" . add_escape_custom($element) . "' ";
509 if ($form_code_type == 'PROD') { // Search for products/drugs
511 $query = "SELECT count(dt.drug_id) as count ";
513 $query = "SELECT dt.drug_id, dt.selector, d.name ";
516 $query .= "FROM drug_templates AS dt, drugs AS d WHERE " .
517 "( d.name LIKE ? OR " .
518 "dt.selector LIKE ? ) " .
519 "AND d.drug_id = dt.drug_id " .
520 "ORDER BY d.name, dt.selector, dt.drug_id $limit_query";
521 $res = sqlStatement($query, array("%" . $search_term . "%", "%" . $search_term . "%"));
522 } else { // Start a codes search
523 // 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.
524 $table_id = isset($code_types[$form_code_type]['external']) ?
intval(($code_types[$form_code_type]['external'])) : -9999 ;
525 if ($table_id >= 0) { // We found a definition for the given code search, so start building the query
526 // Place the common columns variable here since all check codes table
527 $common_columns = " codes.id, codes.code_type, codes.modifier, codes.units, codes.fee, " .
528 "codes.superbill, codes.related_code, codes.taxrates, codes.cyp_factor, " .
529 "codes.active, codes.reportable, codes.financial_reporting, codes.revenue_code, ";
530 $columns = $common_columns . "'" . add_escape_custom($form_code_type) . "' as code_type_name ";
534 // Only filter for active codes. Only the active column in the joined table
535 // is affected by this parameter. Any filtering as a result of "active" status
536 // in the external table itself is always applied. I am implementing the behavior
537 // just as was done prior to the refactor
539 // If there is no entry in codes sql table, then default to active
540 // (this is reason for including NULL below)
541 if ($table_id == 0) {
542 // Search from default codes table
543 $active_query = " AND codes.active = 1 ";
545 // Search from external tables
546 $active_query = " AND (codes.active = 1 || codes.active IS NULL) ";
550 // Get/set the basic metadata information
551 $table_info = $code_external_tables[$table_id];
552 $table = $table_info[EXT_TABLE_NAME
];
553 $table_dot = $table . ".";
554 $code_col = $table_info[EXT_COL_CODE
];
555 $code_text_col = $table_info[EXT_COL_DESCRIPTION
];
556 $code_text_short_col = $table_info[EXT_COL_DESCRIPTION_BRIEF
];
557 if ($table_id == 0) {
558 $table_info[EXT_FILTER_CLAUSES
] = array("code_type=" . $code_types[$form_code_type]['id']); // Add a filter for the code type
561 $code_external = $code_types[$form_code_type]['external'];
563 // If the description is supposed to come from "joined" table instead of the "main",
564 // the metadata defines a DISPLAY_DESCRIPTION element, and we use that to build up the query
565 if ($table_info[DISPLAY_DESCRIPTION
] != "") {
566 $display_description = $table_info[DISPLAY_DESCRIPTION
];
567 $display_description_brief = $table_info[DISPLAY_DESCRIPTION
];
569 $display_description = $table_dot . $code_text_col;
570 $display_description_brief = $table_dot . $code_text_short_col;
573 // Ensure the external table exists
574 $check_table = sqlQuery("SHOW TABLES LIKE '" . $table . "'");
575 if ((empty($check_table))) {
576 HelpfulDie("Missing table in code set search:" . $table);
579 $sql_bind_array = array();
581 // only collecting a count
582 $query = "SELECT count(" . $table_dot . $code_col . ") as count ";
585 if ($table_dot === 'valueset.') {
586 $substitute = 'valueset.code_type as valueset_code_type, ';
588 $query = "SELECT '" . $code_external . "' as code_external, " .
589 $table_dot . $code_col . " as code, " .
590 $display_description . " as code_text, " .
591 $display_description_brief . " as code_text_short, " .
592 $substitute . $columns . " ";
595 if ($table_id == 0) {
596 // Search from default codes table
597 $query .= " FROM " . $table . " ";
599 // Search from external tables
600 $query .= " FROM " . $table .
601 " LEFT OUTER JOIN `codes` " .
602 " ON " . $table_dot . $code_col . " = codes.code AND codes.code_type = ? ";
603 $sql_bind_array[] = $code_types[$form_code_type]['id'];
606 foreach ($table_info[EXT_JOINS
] as $join_info) {
607 $join_table = $join_info[JOIN_TABLE
];
608 $check_table = sqlQuery("SHOW TABLES LIKE '" . $join_table . "'");
609 if ((empty($check_table))) {
610 HelpfulDie("Missing join table in code set search:" . $join_table);
613 $query .= " INNER JOIN " . $join_table;
616 foreach ($join_info[JOIN_FIELDS
] as $field) {
626 // Setup the where clause based on MODE
628 if ($return_only_one) {
629 $query .= $table_dot . $code_col . " = ? ";
630 $sql_bind_array[] = $search_term;
631 } elseif ($mode == "code") {
632 $query .= $table_dot . $code_col . " like ? ";
633 $sql_bind_array[] = $search_term . "%";
634 } elseif ($mode == "description") {
635 $description_keywords = preg_split("/ /", $search_term, -1, PREG_SPLIT_NO_EMPTY
);
637 foreach ($description_keywords as $keyword) {
638 $query .= " AND " . $table_dot . $code_text_col . " LIKE ? ";
639 $sql_bind_array[] = "%" . $keyword . "%";
643 } else { // $mode == "default"
644 $query .= "(" . $table_dot . $code_text_col . " LIKE ? OR " . $table_dot . $code_col . " LIKE ?) ";
645 array_push($sql_bind_array, "%" . $search_term . "%", "%" . $search_term . "%");
648 // Done setting up the where clause by mode
650 // Add the metadata related filter clauses
651 foreach ($table_info[EXT_FILTER_CLAUSES
] as $filter_clause) {
653 $dot_location = strpos($filter_clause, ".");
654 if ($dot_location !== false) {
655 // The filter clause already includes a table specifier, so don't add one
656 $query .= $filter_clause;
658 $query .= $table_dot . $filter_clause;
662 $query .= $active_query . $query_filter_elements;
664 $query .= " ORDER BY " . $table_dot . $code_col . "+0," . $table_dot . $code_col;
667 // Just returning the actual query without the LIMIT information in it. This
668 // information can then be used to combine queries of different code types
669 // via the mysql UNION command. Returning an array to contain the query string
670 // and the binding parameters.
671 return array('query' => $query,'binds' => $sql_bind_array);
674 $query .= $limit_query;
676 $res = sqlStatement($query, $sql_bind_array);
678 HelpfulDie("Code type not active or not defined:" . $join_info[JOIN_TABLE
]);
680 } // End specific code type search
684 // just return the count
685 $ret = sqlFetchArray($res);
686 return $ret['count'];
694 * Lookup Code Descriptions for one or more billing codes.
696 * Function is able to lookup code descriptions from a variety of code sets. See the 'external'
697 * items in the comments at top of this page for a listing of the code sets supported.
699 * @param string $codes Is of the form "type:code;type:code; etc.".
700 * @param string $desc_detail Can choose either the normal description('code_text') or the brief description('code_text_short').
701 * @return string Is of the form "description;description; etc.".
703 function lookup_code_descriptions($codes, $desc_detail = "code_text")
705 global $code_types, $code_external_tables;
707 // ensure $desc_detail is set properly
708 if (($desc_detail != "code_text") && ($desc_detail != "code_text_short")) {
709 $desc_detail = "code_text";
713 if (!empty($codes)) {
714 $relcodes = explode(';', $codes);
715 foreach ($relcodes as $codestring) {
716 if ($codestring === '') {
720 // added $modifier for HCPCS and other internal codesets so can grab exact entry in codes table
721 $code_parts = explode(':', $codestring);
722 $codetype = $code_parts[0] ??
null;
723 $code = $code_parts[1] ??
null;
724 $modifier = $code_parts[2] ??
null;
725 // if we don't have the code types we can't do much here
726 if (!isset($code_types[$codetype])) {
727 // we can't do much so we will just continue here...
731 $table_id = $code_types[$codetype]['external'] ??
'';
732 if (!isset($code_external_tables[$table_id])) {
733 //using an external code that is not yet supported, so skip.
737 $table_info = $code_external_tables[$table_id];
738 $table_name = $table_info[EXT_TABLE_NAME
];
739 $code_col = $table_info[EXT_COL_CODE
];
740 $desc_col = $table_info[DISPLAY_DESCRIPTION
] == "" ?
$table_info[EXT_COL_DESCRIPTION
] : $table_info[DISPLAY_DESCRIPTION
];
741 $desc_col_short = $table_info[DISPLAY_DESCRIPTION
] == "" ?
$table_info[EXT_COL_DESCRIPTION_BRIEF
] : $table_info[DISPLAY_DESCRIPTION
];
743 $sql = "SELECT " . $desc_col . " as code_text," . $desc_col_short . " as code_text_short FROM " . $table_name;
745 // include the "JOINS" so that we get the preferred term instead of the FullySpecifiedName when appropriate.
746 foreach ($table_info[EXT_JOINS
] as $join_info) {
747 $join_table = $join_info[JOIN_TABLE
];
748 $check_table = sqlQuery("SHOW TABLES LIKE '" . $join_table . "'");
749 if ((empty($check_table))) {
750 HelpfulDie("Missing join table in code set search:" . $join_table);
753 $sql .= " INNER JOIN " . $join_table;
756 foreach ($join_info[JOIN_FIELDS
] as $field) {
769 // Start building up the WHERE clause
771 // When using the external codes table, we have to filter by the code_type. (All the other tables only contain one type)
772 if ($table_id == 0) {
773 $sql .= " code_type = '" . add_escape_custom($code_types[$codetype]['id']) . "' AND ";
776 // Specify the code in the query.
777 $sql .= $table_name . "." . $code_col . "=? ";
780 // Add the modifier if necessary for CPT and HCPCS which differentiates code
782 $sql .= " AND modifier = ? ";
783 $sqlArray[] = $modifier;
786 // We need to include the filter clauses
787 // For SNOMED and SNOMED-CT this ensures that we get the Preferred Term or the Fully Specified Term as appropriate
788 // It also prevents returning "inactive" results
789 foreach ($table_info[EXT_FILTER_CLAUSES
] as $filter_clause) {
790 $sql .= " AND " . $filter_clause;
793 // END building the WHERE CLAUSE
796 if ($table_info[EXT_VERSION_ORDER
]) {
797 $sql .= " ORDER BY " . $table_info[EXT_VERSION_ORDER
];
801 $crow = sqlQuery($sql, $sqlArray);
802 if (!empty($crow[$desc_detail])) {
807 $code_text .= $crow[$desc_detail];
816 * Sequential code set "internal" searching function
818 * Function is basically a wrapper of the code_set_search() function to support
819 * a optimized searching models. The default mode will:
820 * Searches codes first; then if no hits, it will then search the descriptions
821 * (which are separated by each word in the code_set_search() function).
822 * (This function is not meant to be called directly)
824 * @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)
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 * @param string $is_hit_mode This is a mode that simply returns the name of the mode if results were found
834 * @return mixed recordset/integer/string
836 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)
838 // Set the default behavior that is described in above function comments
840 $modes = array('code','description');
843 // Return the Search Results (loop through each mode in order)
844 foreach ($modes as $mode) {
845 $res = code_set_search($form_code_type, $search_term, $count, $active, false, $start, $number, $filter_elements, $limit, $mode);
846 if (($count && $res > 0) ||
(!$count && sqlNumRows($res) > 0)) {
848 // just return the mode
851 // returns the count number if count is true or returns the data if count is false
859 * Code set searching "internal" function for when searching multiple code sets.
861 * It will also work for one code set search, although not meant for this.
862 * (This function is not meant to be called directly)
864 * @param array $form_code_types code set keys (will default to checking all active code types if blank)
865 * @param string $search_term search term
866 * @param integer $limit Number of results to return (NULL means return all)
867 * @param array $modes Holds the search modes to process along with the order of processing (default behavior is described in above function comment)
868 * @param boolean $count if true, then will only return the number of entries
869 * @param boolean $active if true, then will only return active entries
870 * @param integer $start Query start limit (for pagination)
871 * @param integer $number Query number returned (for pagination)
872 * @param array $filter_elements Array that contains elements to filter
873 * @return mixed recordset/integer
875 function multiple_code_set_search(array $form_code_types = null, $search_term, $limit = null, $modes = null, $count = false, $active = true, $start = null, $number = null, $filter_elements = array())
878 if (empty($form_code_types)) {
879 // Collect the active code types
880 $form_code_types = collect_codetypes("active", "array");
887 // Figure out the appropriate limit clause
888 $limit_query = limit_query_string($limit, $start, $number);
890 // Prepare the sql bind array
891 $sql_bind_array = array();
893 // Start the query string
894 $query = "SELECT * FROM ((";
897 // Loop through each code type
899 $flag_hit = false; //ensure there is a hit to avoid trying an empty query
900 foreach ($form_code_types as $form_code_type) {
901 // see if there is a hit
903 // only use the count method here, since it's much more efficient than doing the actual query
904 $mode_hit = sequential_code_set_search($form_code_type, $search_term, null, $modes, true, $active, null, null, $filter_elements, true);
908 $count_hits = code_set_search($form_code_type, $search_term, $count, $active, false, null, null, $filter_elements, null, $mode_hit);
909 // increment the counter
910 $counter +
= $count_hits;
914 $return_query = code_set_search($form_code_type, $search_term, $count, $active, false, null, null, $filter_elements, null, $mode_hit, true);
915 if (!empty($sql_bind_array)) {
916 $sql_bind_array = array_merge($sql_bind_array, $return_query['binds']);
918 $sql_bind_array = $return_query['binds'];
922 $query .= ") UNION ALL (";
925 $query .= $return_query['query'];
936 // Finish the query string
937 $query .= ")) as atari $limit_query";
939 // Process and return the query (if there was a hit)
941 return sqlStatement($query, $sql_bind_array);
947 * Returns the limit to be used in the sql query for code set searches.
949 * @param integer $limit Number of results to return (NULL means return all)
950 * @param integer $start Query start limit (for pagination)
951 * @param integer $number Query number returned (for pagination)
952 * @param boolean $return_only_one if true, then will only return one perfect matching item
953 * @return mixed recordset/integer
955 function limit_query_string($limit = null, $start = null, $number = null, $return_only_one = false)
957 if (!is_null($start) && !is_null($number)) {
958 // For pagination of results
959 $limit_query = " LIMIT " . escape_limit($start) . ", " . escape_limit($number) . " ";
960 } elseif (!is_null($limit)) {
961 $limit_query = " LIMIT " . escape_limit($limit) . " ";
963 // No pagination and no limit
967 if ($return_only_one) {
968 // Only return one result (this is where only matching for exact code match)
969 // Note this overrides the above limit settings
970 $limit_query = " LIMIT 1 ";
976 // Recursive function to look up the IPPF2 (or other type) code, if any,
977 // for a given related code field.
979 function recursive_related_code($related_code, $typewanted = 'IPPF2', $depth = 0)
982 // echo "<!-- related_code = '$related_code' depth = '$depth' -->\n"; // debugging
983 if (++
$depth > 4 ||
empty($related_code)) {
984 return false; // protects against relation loops
986 $relcodes = explode(';', $related_code);
987 foreach ($relcodes as $codestring) {
988 if ($codestring === '') {
991 list($codetype, $code) = explode(':', $codestring);
992 if ($codetype === $typewanted) {
993 // echo "<!-- returning '$code' -->\n"; // debugging
997 "SELECT related_code FROM codes WHERE " .
998 "code_type = ? AND code = ? AND active = 1 " .
999 "ORDER BY id LIMIT 1",
1000 array($code_types[$codetype]['id'], $code)
1002 $tmp = recursive_related_code($row['related_code'], $typewanted, $depth);
1003 if ($tmp !== false) {