From 89d4a22f54e6f35e414882433d93d0058a9aebde Mon Sep 17 00:00:00 2001 From: "deb.monish" Date: Thu, 21 Jun 2018 16:44:28 +0530 Subject: [PATCH] dev/core#41: Search Builder: Not empty with date or integer custom fields gives a sql warning --- CRM/Contact/BAO/Query.php | 6 ++--- CRM/Contact/Form/Search/Builder.php | 18 +++++---------- CRM/Core/BAO/CustomField.php | 23 ++++++++++++++++++++ CRM/Core/BAO/CustomQuery.php | 2 +- templates/CRM/Contact/Form/Search/Builder.js | 9 ++++---- 5 files changed, 38 insertions(+), 20 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 2525c38844..eda7792aa7 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -5662,11 +5662,11 @@ SELECT COUNT( conts.total_amount ) as cancel_count, return $clause; case 'IS EMPTY': - $clause = " (NULLIF($field, '') IS NULL) "; + $clause = ($dataType == 'Date') ? " $field IS NULL " : " (NULLIF($field, '') IS NULL) "; return $clause; case 'IS NOT EMPTY': - $clause = " (NULLIF($field, '') IS NOT NULL) "; + $clause = ($dataType == 'Date') ? " $field IS NOT NULL " : " (NULLIF($field, '') IS NOT NULL) "; return $clause; case 'IN': @@ -5677,7 +5677,7 @@ SELECT COUNT( conts.total_amount ) as cancel_count, } default: - if (empty($dataType)) { + if (empty($dataType) || $dataType == 'Date') { $dataType = 'String'; } if (is_array($value)) { diff --git a/CRM/Contact/Form/Search/Builder.php b/CRM/Contact/Form/Search/Builder.php index 2a5dd79351..4b5f0ddc29 100644 --- a/CRM/Contact/Form/Search/Builder.php +++ b/CRM/Contact/Form/Search/Builder.php @@ -95,18 +95,10 @@ class CRM_Contact_Form_Search_Builder extends CRM_Contact_Form_Search { // This array contain list of available fields and their corresponding data type, // later assigned as json string, to be used to filter list of mysql operators $fieldNameTypes = []; - $dataType = [ - CRM_Utils_Type::T_STRING => 'String', - CRM_Utils_Type::T_TEXT => 'String', - CRM_Utils_Type::T_LONGTEXT => 'String', - CRM_Utils_Type::T_BOOLEAN => 'Boolean', - CRM_Utils_Type::T_DATE => 'Date', - CRM_Utils_Type::T_TIMESTAMP => 'Date', - ]; foreach ($fields as $name => $field) { // Assign date type to respective field name, which will be later used to modify operator list - if (isset($field['type']) && array_key_exists($field['type'], $dataType)) { - $fieldNameTypes[$name] = $dataType[$field['type']]; + if ($type = CRM_Utils_Array::key(CRM_Utils_Array::value('type', $field), CRM_Utils_Type::getValidTypes())) { + $fieldNameTypes[$name] = $type; } // it's necessary to know which of the fields are searchable by label if (isset($field['searchByLabel']) && $field['searchByLabel']) { @@ -477,8 +469,10 @@ class CRM_Contact_Form_Search_Builder extends CRM_Contact_Form_Search { $options[substr($field, 0, -3)] = $entity; } } - elseif (!empty($info['data_type']) && in_array($info['data_type'], array('StateProvince', 'Country'))) { - $options[$field] = $entity; + elseif (!empty($info['data_type'])) { + if (in_array($info['data_type'], array('StateProvince', 'Country'))) { + $options[$field] = $entity; + } } elseif (in_array(substr($field, 0, 3), array( 'is_', diff --git a/CRM/Core/BAO/CustomField.php b/CRM/Core/BAO/CustomField.php index 8dc1cce54f..3154ed24ed 100644 --- a/CRM/Core/BAO/CustomField.php +++ b/CRM/Core/BAO/CustomField.php @@ -83,6 +83,28 @@ class CRM_Core_BAO_CustomField extends CRM_Core_DAO_CustomField { return self::$_dataType; } + /** + * Build the map of custom field's data types and there respective Util type + * + * @return array + * Data data-type => CRM_Utils_Type + */ + public static function dataToType() { + return [ + 'String' => CRM_Utils_Type::T_STRING, + 'Int' => CRM_Utils_Type::T_INT, + 'Float' => CRM_Utils_Type::T_FLOAT, + 'Money' => CRM_Utils_Type::T_FLOAT, + 'Memo' => CRM_Utils_Type::T_TEXT, + 'Date' => CRM_Utils_Type::T_DATE, + 'Boolean' => CRM_Utils_Type::T_BOOLEAN, + 'StateProvince' => CRM_Utils_Type::T_INT, + 'Country' => CRM_Utils_Type::T_INT, + 'Link' => CRM_Utils_Type::T_STRING, + 'ContactReference' => CRM_Utils_Type::T_INT, + ]; + } + /** * Get data to html array. * @@ -691,6 +713,7 @@ class CRM_Core_BAO_CustomField extends CRM_Core_DAO_CustomField { $regexp = preg_replace('/[.,;:!?]/', '', CRM_Utils_Array::value(0, $values)); $importableFields[$key] = array( 'name' => $key, + 'type' => CRM_Utils_Array::value(CRM_Utils_Array::value('data_type', $values), self::dataToType()), 'title' => CRM_Utils_Array::value('label', $values), 'headerPattern' => '/' . preg_quote($regexp, '/') . '/', 'import' => 1, diff --git a/CRM/Core/BAO/CustomQuery.php b/CRM/Core/BAO/CustomQuery.php index bc082255a3..b71acb1081 100644 --- a/CRM/Core/BAO/CustomQuery.php +++ b/CRM/Core/BAO/CustomQuery.php @@ -433,7 +433,7 @@ SELECT f.id, f.label, f.data_type, break; case 'Date': - $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); + $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Date'); list($qillOp, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $field['label'], $value, $op, array(), CRM_Utils_Type::T_DATE); $this->_qill[$grouping][] = "{$field['label']} $qillOp '$qillVal'"; break; diff --git a/templates/CRM/Contact/Form/Search/Builder.js b/templates/CRM/Contact/Form/Search/Builder.js index 019add712d..904e7766c8 100644 --- a/templates/CRM/Contact/Form/Search/Builder.js +++ b/templates/CRM/Contact/Form/Search/Builder.js @@ -19,15 +19,16 @@ var patt = /_1$/; // pattern to check if the change event came from field name if (field !== null && patt.test(this.id)) { // based on data type remove invalid operators e.g. IS EMPTY doesn't work with Boolean type column + var operators = CRM.searchBuilder.generalOperators; if ((field in CRM.searchBuilder.fieldTypes) === true) { - if (CRM.searchBuilder.fieldTypes[field] == 'Boolean') { - CRM.searchBuilder.generalOperators = _.omit(CRM.searchBuilder.generalOperators, ['IS NOT EMPTY', 'IS EMPTY']); + if ($.inArray(CRM.searchBuilder.fieldTypes[field], ['Boolean', 'Int']) > -1) { + operators = _.omit(operators, ['IS NOT EMPTY', 'IS EMPTY']); } else if (CRM.searchBuilder.fieldTypes[field] == 'String') { - CRM.searchBuilder.generalOperators = _.omit(CRM.searchBuilder.generalOperators, ['>', '<', '>=', '<=']); + operators = _.omit(operators, ['>', '<', '>=', '<=']); } } - buildOperator(operator, CRM.searchBuilder.generalOperators); + buildOperator(operator, operators); } // These Ops don't get any input field. -- 2.25.1