From 4efbeace125fe71f49170b820cdd22e43da4a8cc Mon Sep 17 00:00:00 2001 From: mdsupport Date: Mon, 2 Jan 2017 20:08:06 -0800 Subject: [PATCH] Wildcard matching in CDR (#417) --- library/clinical_rules.php | 57 ++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 48 insertions(+), 9 deletions(-) diff --git a/library/clinical_rules.php b/library/clinical_rules.php index 09349c8d7..3a6aef6e1 100644 --- a/library/clinical_rules.php +++ b/library/clinical_rules.php @@ -1859,7 +1859,7 @@ function lists_check($patient_id,$filter,$dateTarget) { * @param string $table selected mysql table * @param string $column selected mysql column * @param string $data_comp data comparison (eq,ne,gt,ge,lt,le) - * @param string $data selected data in the mysql database (2) + * @param string $data selected data in the mysql database (1)(2) * @param string $num_items_comp number items comparison (eq,ne,gt,ge,lt,le) * @param integer $num_items_thres number of items threshold * @param string $intervalType type of interval (ie. year) @@ -1867,6 +1867,7 @@ function lists_check($patient_id,$filter,$dateTarget) { * @param string $dateTarget target date(format Y-m-d H:i:s). * @return boolean true if check passed, otherwise false * + * (1) If data ends with **, operators ne/eq are replaced by (NOT)LIKE operators * (2) If $data contains '#CURDATE#', then it will be converted to the current date. * */ @@ -1917,6 +1918,13 @@ function exist_database_item($patient_id,$table,$column='',$data_comp,$data='',$ "WHERE " . add_escape_custom($patient_id_label) . "=? " . $customSQL, array($patient_id) ); } else { + // mdsupport : Allow trailing '**' in the strings to perform LIKE searches + if ( (substr($data,-2)=='**') && (($compSql == "=") || ($compSql == "!=")) ) { + $compSql = ($compSql == "!=" ? " NOT": "")." LIKE CONCAT('%',?,'%') "; + $data = substr_replace($data, '', -2); + } else { + $compSql = $compSql . "? "; + } if ($whereTables=="" && strpos($table, 'form_')!== false){ //To handle standard forms starting with form_ //In this case, we are assuming the date field is "date" @@ -1926,7 +1934,7 @@ function exist_database_item($patient_id,$table,$column='',$data_comp,$data='',$ "LEFT JOIN `" . add_escape_custom($table) . "` " . " b ". "ON (a.form_id=b.id AND a.formdir LIKE '".add_escape_custom(substr($table, 5))."') ". "WHERE a.deleted != '1' ". - "AND b.`" .add_escape_custom($column) ."`" . $compSql . "? " . + "AND b.`" .add_escape_custom($column) ."`" . $compSql . "AND b." . add_escape_custom($patient_id_label) . "=? " . $customSQL . str_replace("`date`", "b.`date`", $dateSql) ,array($data, $patient_id)); @@ -1941,7 +1949,7 @@ function exist_database_item($patient_id,$table,$column='',$data_comp,$data='',$ $sql = sqlStatementCdrEngine("SELECT `" . add_escape_custom($column) . "` " . "FROM `" . add_escape_custom($table) . "` " . " " . $whereTables . " " . - "WHERE `" . add_escape_custom($column) . "`" . $compSql . "? " . + "WHERE `" . add_escape_custom($column) . "`" . $compSql . "AND " . add_escape_custom($patient_id_label) . "=? " . $customSQL . $dateSql, array($data, $patient_id)); } @@ -1958,13 +1966,16 @@ function exist_database_item($patient_id,$table,$column='',$data_comp,$data='',$ * @param string $proc_title procedure title * @param string $proc_code procedure identifier code (array of :||:|| etc.) * @param string $results_comp results comparison (eq,ne,gt,ge,lt,le) - * @param string $result_data results data + * @param string $result_data results data (1) * @param string $num_items_comp number items comparison (eq,ne,gt,ge,lt,le) * @param integer $num_items_thres number of items threshold * @param string $intervalType type of interval (ie. year) * @param integer $intervalValue searched for within this many times of the interval type * @param string $dateTarget target date(format Y-m-d H:i:s). * @return boolean true if check passed, otherwise false + * + * (1) If result_data ends with **, operators ne/eq are replaced by (NOT)LIKE operators + * */ function exist_procedure_item($patient_id,$proc_title,$proc_code,$result_comp,$result_data='',$num_items_comp,$num_items_thres,$intervalType='',$intervalValue='',$dateTarget='') { @@ -2024,8 +2035,15 @@ function exist_procedure_item($patient_id,$proc_title,$proc_code,$result_comp,$r "OR (procedure_type.procedure_code = ? AND procedure_type.procedure_code != '') ) OR "; array_push($sqlBindArray,$tem,$tem); } + // mdsupport : Allow trailing '**' in the strings to perform LIKE searches + if ( (substr($result_data,-2)=='**') && (($compSql == "=") || ($compSql == "!=")) ) { + $compSql = ($compSql == "!=" ? " NOT": "")." LIKE CONCAT('%',?,'%') "; + $result_data = substr_replace($result_data, '', -2); + } else { + $compSql = $compSql . "? "; + } $sql_query .= "(procedure_type.name = ? AND procedure_type.name != '') ) " . - "AND procedure_result.result " . $compSql . " ? " . + "AND procedure_result.result " . $compSql . "AND " . add_escape_custom($patient_id_label) . " = ? " . $dateSql; array_push($sqlBindArray,$proc_title,$result_data,$patient_id); @@ -2112,9 +2130,12 @@ function exist_lifestyle_item($patient_id,$lifestyle,$status,$dateTarget) { * * @param string $patient_id pid of selected patient. * @param string $type type (medical_problem, allergy, medication, etc) - * @param string $value value searching for + * @param string $value value searching for (1) * @param string $dateTarget target date(format Y-m-d H:i:s). * @return boolean true if check passed, otherwise false + * + * (1) If value ends with **, operators ne/eq are replaced by (NOT)LIKE operators + * */ function exist_lists_item($patient_id,$type,$value,$dateTarget) { @@ -2129,12 +2150,21 @@ function exist_lists_item($patient_id,$type,$value,$dateTarget) { $code_type = $value_array[0]; $code = $value_array[1]; + // Modify $code for both 'CUSTOM' and diagnosis searches + // Note: Diagnosis is always 'LIKE' and should not have '**' + if (substr($code,-2)=='**') { + $sqloper = " LIKE CONCAT('%',?,'%') "; + $code = substr_replace($code, '', -2); + } else { + $sqloper = "=?"; + } + if ($code_type=='CUSTOM') { // Deal with custom code type first (title column in lists table) $response = sqlQueryCdrEngine("SELECT * FROM `lists` " . "WHERE `type`=? " . "AND `pid`=? " . - "AND `title`=? " . + "AND `title` $sqloper " . "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " . "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,$code,$dateTarget,$dateTarget,$dateTarget) ); if (!empty($response)) return true; @@ -2153,16 +2183,25 @@ function exist_lists_item($patient_id,$type,$value,$dateTarget) { else { // count($value_array) == 1 // Search the title column in lists table // Yes, this is essentially the same as the code type listed as CUSTOM above. This provides flexibility and will ensure compatibility. + + // Check for '**' + if (substr($value,-2)=='**') { + $sqloper = " LIKE CONCAT('%',?,'%') "; + $value = substr_replace($value, '', -2); + } else { + $sqloper = "=?"; + } + $response = sqlQueryCdrEngine("SELECT * FROM `lists` " . "WHERE `type`=? " . "AND `pid`=? " . - "AND `title`=? ". + "AND `title` $sqloper ". "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " . "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,$value,$dateTarget,$dateTarget,$dateTarget) ); if (!empty($response)) return true; if($type == 'medication'){ // Special case needed for medication as it need to be looked into current medications (prescriptions table) from ccda import - $response = sqlQueryCdrEngine("SELECT * FROM `prescriptions` where `patient_id` = ? and `drug` = ? and `date_added` <= ?", array($patient_id,$value,$dateTarget)); + $response = sqlQueryCdrEngine("SELECT * FROM `prescriptions` where `patient_id` = ? and `drug` $sqloper and `date_added` <= ?", array($patient_id,$value,$dateTarget)); if(!empty($response)) return true; } } -- 2.11.4.GIT