From 3acb37cc94521fb0bb19e985eaf89a9477a064e3 Mon Sep 17 00:00:00 2001 From: monishdeb Date: Fri, 22 Jan 2016 01:26:57 +0530 Subject: [PATCH] backporting changes of CRM-17836 in 4.6 ---------------------------------------- * CRM-17836: custom fields attached to relationships are unsearchable https://issues.civicrm.org/jira/browse/CRM-17836 --- CRM/Contact/BAO/Query.php | 109 ++++++++++++++++++++++++++++++- CRM/Contribute/BAO/Query.php | 2 +- CRM/Core/BAO/CustomQuery.php | 121 +++-------------------------------- 3 files changed, 116 insertions(+), 116 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index a927a480d6..881874dd48 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -1489,6 +1489,18 @@ class CRM_Contact_BAO_Query { $params[] = array('on_hold', '=', $formValues['email_on_hold']['on_hold'], 0, 0); } } + elseif (substr($id, 0, 7) == 'custom_' + && ( + substr($id, -9, 9) == '_relative' + || substr($id, -5, 5) == '_from' + || substr($id, -3, 3) == '_to' + ) + ) { + self::convertCustomRelativeFields($formValues, $params, $values, $id); + } + elseif (!empty($values) && !is_array($values) && self::isCustomDateField($id)) { + $params[] = array($id, '=', CRM_Utils_Date::processDate($values), 0, $wildcard); + } elseif (preg_match('/_date_relative$/', $id) || $id == 'event_relative' || $id == 'case_from_relative' || @@ -4385,6 +4397,82 @@ civicrm_relationship.is_permission_a_b = 0 } } + /** + * Convert submitted values for relative custom fields to query object format. + * + * The query will support the sqlOperator format so convert to that format. + * + * @param array $formValues + * Submitted values. + * @param array $params + * Converted parameters for the query object. + * @param string $values + * Submitted value. + * @param string $fieldName + * Submitted field name. (Matches form field not DB field.) + */ + protected static function convertCustomRelativeFields(&$formValues, &$params, $values, $fieldName) { + if (empty($values)) { + // e.g we might have relative set & from & to empty. The form flow is a bit funky & + // this function gets called again after they fields have been converted which can get ugly. + return; + } + $customFieldName = self::getCustomFieldName($fieldName); + + if (substr($fieldName, -9, 9) == '_relative') { + list($from, $to) = CRM_Utils_Date::getFromTo($values, NULL, NULL); + } + else { + if ($fieldName == $customFieldName . '_to' && CRM_Utils_Array::value($customFieldName . '_from', $formValues)) { + // Both to & from are set. We only need to acton one, choosing from. + return; + } + + $from = CRM_Utils_Array::value($customFieldName . '_from', $formValues, NULL); + $to = CRM_Utils_Array::value($customFieldName . '_to', $formValues, NULL); + + if (self::isCustomDateField($customFieldName)) { + list($from, $to) = CRM_Utils_Date::getFromTo(NULL, $from, $to); + } + } + + if ($from) { + if ($to) { + $relativeFunction = array('BETWEEN' => array($from, $to)); + } + else { + $relativeFunction = array('>=' => $from); + } + } + else { + $relativeFunction = array('<=' => $to); + } + $params[] = array( + $customFieldName, + '=', + $relativeFunction, + 0, + 0, + ); + } + + /** + * Are we dealing with custom field of type date. + * + * @param $fieldName + * + * @return bool + */ + public static function isCustomDateField($fieldName) { + if (($customFieldID = CRM_Core_BAO_CustomField::getKeyID($fieldName)) == FALSE) { + return FALSE; + } + if ('Date' == civicrm_api3('CustomField', 'getvalue', array('id' => $customFieldID, 'return' => 'data_type'))) { + return TRUE; + } + return FALSE; + } + /** * Create and query the db for an contact search. * @@ -5170,7 +5258,9 @@ SELECT COUNT( conts.total_amount ) as cancel_count, // We could get away with keeping this in 4.6 if we make it such that it throws an enotice in 4.7 so // people have to de-slopify it. if (!empty($value[0])) { - $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReport; + if ($op != 'BETWEEN') { + $dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReport; + } if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => $value), $dataType)) != FALSE) { return $queryString; } @@ -5683,12 +5773,27 @@ AND displayRelType.is_active = 1 $fieldValue = implode(', ', $qillString); } else { - $fieldValue = implode(', ', $fieldValue); + if ($type == CRM_Utils_Type::T_DATE) { + foreach ($fieldValue as $index => $value) { + $fieldValue[$index] = CRM_Utils_Date::customFormat($value); + } + } + $separator = ', '; + // @todo - this is a bit specific (one operator). + // However it is covered by a unit test so can be altered later with + // some confidence. + if ($op == 'BETWEEN') { + $separator = ' AND '; + } + $fieldValue = implode($separator, $fieldValue); } } elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) { $fieldValue = $pseudoOptions[$fieldValue]; } + elseif ($type === CRM_Utils_Type::T_DATE) { + $fieldValue = CRM_Utils_Date::customFormat($fieldValue); + } return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue); } diff --git a/CRM/Contribute/BAO/Query.php b/CRM/Contribute/BAO/Query.php index fe3f4fbf2b..022dbaa644 100644 --- a/CRM/Contribute/BAO/Query.php +++ b/CRM/Contribute/BAO/Query.php @@ -346,7 +346,7 @@ class CRM_Contribute_BAO_Query { case (strpos($name, '_amount') !== FALSE): case (strpos($name, '_date') !== FALSE && $name != 'contribution_fulfilled_date'): $qillName = $name; - $pseudoExtraParam = NULL; + $pseudoExtraParam = array(); // @todo including names using a switch statement & then using an 'if' to filter them out is ... odd! if ((strpos($name, '_amount') !== FALSE) || (strpos($name, '_date') !== FALSE) || in_array($name, array( diff --git a/CRM/Core/BAO/CustomQuery.php b/CRM/Core/BAO/CustomQuery.php index 32042dbfa4..561bea973d 100644 --- a/CRM/Core/BAO/CustomQuery.php +++ b/CRM/Core/BAO/CustomQuery.php @@ -360,13 +360,7 @@ SELECT label, value case 'Country': if ($field['is_search_range'] && is_array($value)) { - $this->searchRange($field['id'], - $field['label'], - $field['data_type'], - $fieldName, - $value, - $grouping - ); + //didn't found any field under any of these three data-types as searchable by range } else { // fix $value here to escape sql injection attacks @@ -413,13 +407,8 @@ SELECT label, value break; case 'Int': - if ($field['is_search_range'] && is_array($value)) { - $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); - } - else { - $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); - $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));; - } + $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Integer'); + $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue));; break; case 'Boolean': @@ -455,69 +444,14 @@ SELECT label, value } case 'Float': - if ($field['is_search_range']) { - $this->searchRange($field['id'], $field['label'], $field['data_type'], $fieldName, $value, $grouping); - } - else { - $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); - $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); - } + $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'Float'); + $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['label'], 2 => $qillOp, 3 => $qillValue)); break; case 'Date': - $fromValue = CRM_Utils_Array::value('from', $value); - $toValue = CRM_Utils_Array::value('to', $value); - $value = CRM_Utils_Array::value($op, $value, $value); - - if (!$fromValue && !$toValue) { - if (!is_array($value) && !CRM_Utils_Date::processDate($value) && !in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) { - continue; - } - - // hack to handle yy format during search - if (is_numeric($value) && strlen($value) == 4) { - $value = "01-01-{$value}"; - } - - if (is_array($value)) { - $date = $qillValue = array(); - foreach ($value as $key => $val) { - $date[$key] = CRM_Utils_Date::processDate($val); - $qillValue[$key] = CRM_Utils_Date::customFormat($date[$key]); - } - } - else { - $date = CRM_Utils_Date::processDate($value); - $qillValue = CRM_Utils_Date::customFormat($date); - } - - $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $date, 'String'); - $this->_qill[$grouping][] = $field['label'] . " {$qillOp} " . implode(', ', (array) $qillValue); - } - else { - if (is_numeric($fromValue) && strlen($fromValue) == 4) { - $fromValue = "01-01-{$fromValue}"; - } - - if (is_numeric($toValue) && strlen($toValue) == 4) { - $toValue = "01-01-{$toValue}"; - } - - // TO DO: add / remove time based on date parts - $fromDate = CRM_Utils_Date::processDate($fromValue); - $toDate = CRM_Utils_Date::processDate($toValue); - if (!$fromDate && !$toDate) { - continue; - } - if ($fromDate) { - $this->_where[$grouping][] = "$fieldName >= $fromDate"; - $this->_qill[$grouping][] = $field['label'] . ' >= ' . CRM_Utils_Date::customFormat($fromDate); - } - if ($toDate) { - $this->_where[$grouping][] = "$fieldName <= $toDate"; - $this->_qill[$grouping][] = $field['label'] . ' <= ' . CRM_Utils_Date::customFormat($toDate); - } - } + $this->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause($fieldName, $op, $value, 'String'); + 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; case 'File': @@ -572,43 +506,4 @@ SELECT label, value ); } - /** - * @param int $id - * @param $label - * @param $type - * @param string $fieldName - * @param $value - * @param $grouping - */ - public function searchRange(&$id, &$label, $type, $fieldName, &$value, &$grouping) { - $qill = array(); - - if (isset($value['from'])) { - $val = CRM_Utils_Type::escape($value['from'], $type); - - if ($type == 'String') { - $this->_where[$grouping][] = "$fieldName >= '$val'"; - } - else { - $this->_where[$grouping][] = "$fieldName >= $val"; - } - $qill[] = ts('greater than or equal to \'%1\'', array(1 => $value['from'])); - } - - if (isset($value['to'])) { - $val = CRM_Utils_Type::escape($value['to'], $type); - if ($type == 'String') { - $this->_where[$grouping][] = "$fieldName <= '$val'"; - } - else { - $this->_where[$grouping][] = "$fieldName <= $val"; - } - $qill[] = ts('less than or equal to \'%1\'', array(1 => $value['to'])); - } - - if (!empty($qill)) { - $this->_qill[$grouping][] = $label . ' - ' . implode(' ' . ts('and') . ' ', $qill); - } - } - } -- 2.25.1