From bb05da0cddd622557b81d682bbd3ee714b031da0 Mon Sep 17 00:00:00 2001 From: eileenmcnaugton Date: Mon, 7 Sep 2015 23:38:57 +1200 Subject: [PATCH] CRM-17116 fix advanced search to support custom dates --- CRM/Contact/BAO/Query.php | 185 ++++++++++++++++++++++++--- CRM/Contact/Form/Search/Advanced.php | 11 ++ CRM/Contact/Form/Search/Criteria.php | 16 ++- CRM/Contribute/BAO/Query.php | 2 +- CRM/Core/BAO/CustomQuery.php | 8 ++ CRM/Core/DAO.php | 11 +- templates/CRM/Custom/Form/Search.tpl | 6 +- 7 files changed, 201 insertions(+), 38 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 863ea6fafa..ae986b54bf 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -501,6 +501,16 @@ class CRM_Contact_BAO_Query { } /** + * Function for same purpose as convertFormValues. + * + * Like convert form values this function exists to pre-Process parameters from the form. + * + * It is unclear why they are different functions & likely relates to advances search + * versus search builder. + * + * The direction we are going is having the form convert values to a standardised format & + * moving away from wierd & wonderful where clause switches. + * * Fix and handle contact deletion nicely. * * this code is primarily for search builder use case where different clauses can specify if they want deleted. @@ -905,6 +915,8 @@ class CRM_Contact_BAO_Query { CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'select'); if (!empty($this->_cfIDs)) { + // @todo This function is the select function but instead of running 'select' it + // is running the whole query. $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields); $this->_customQuery->query(); $this->_select = array_merge($this->_select, $this->_customQuery->_select); @@ -1426,7 +1438,26 @@ class CRM_Contact_BAO_Query { } /** - * Convert form values to array for this object. + * Convert values from form-appropriate to query-object appropriate. + * + * The query object is increasingly supporting the sql-filter syntax which is the most flexible syntax. + * So, ideally we would convert all fields to look like + * array( + * 0 => $fieldName + * // Set the operator for legacy reasons, but it is ignored + * 1 => '=' + * // array in sql filter syntax + * 2 => array('BETWEEN' => array(1,60), + * 3 => null + * 4 => null + * ); + * + * There are some examples of the syntax in + * https://github.com/civicrm/civicrm-core/tree/master/api/v3/examples/Relationship + * + * More notes at CRM_Core_DAO::createSQLFilter + * + * and a list of supported operators in CRM_Core_DAO * * @param array $formValues * @param int $wildcard @@ -1458,6 +1489,15 @@ 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::convertCustomDateRelativeFields($formValues, $params, $values, $id); + } elseif (preg_match('/_date_relative$/', $id) || $id == 'event_relative' || $id == 'case_from_relative' || @@ -4342,6 +4382,91 @@ civicrm_relationship.is_permission_a_b = 0 return array($values, $options); } + /** + * Get the actual custom field name by stripping off the appended string. + * + * The string could be _relative, _from, or _to + * + * @todo use metadata rather than convention to do this. + * + * @param string $parameterName + * The name of the parameter submitted to the form. + * e.g + * custom_3_relative + * custom_3_from + * + * @return string + */ + public static function getCustomFieldName($parameterName) { + if (substr($parameterName, -5, 5) == '_from') { + return substr($parameterName, 0, strpos($parameterName, '_from')); + } + if (substr($parameterName, -9, 9) == '_relative') { + return substr($parameterName, 0, strpos($parameterName, '_relative')); + } + if (substr($parameterName, -3, 3) == '_to') { + return substr($parameterName, 0, strpos($parameterName, '_to')); + } + } + + /** + * Convert submitted values for relative custom date 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 convertCustomDateRelativeFields(&$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; + } + + list($from, $to) = CRM_Utils_Date::getFromTo( + NULL, + (empty($formValues[$customFieldName . '_from']) ? NULL : $formValues[$customFieldName . '_from']), + CRM_Utils_Array::value($customFieldName . '_to', $formValues) + ); + } + + if ($from) { + if ($to) { + $relativeFunction = array('BETWEEN' => array($from, $to)); + } + else { + $relativeFunction = array('>=' => $from); + } + } + else { + $relativeFunction = array('<=' => $to); + } + $params[] = array( + $customFieldName, + '=', + $relativeFunction, + 0, + 0, + ); + } + /** * Create and query the db for an contact search. * @@ -5696,34 +5821,43 @@ AND displayRelType.is_active = 1 * * Qill refers to the query detail visible on the UI. * - * @param $daoName - * @param $fieldName - * @param $fieldValue - * @param $op - * @param array $pseduoExtraParam + * @param string $daoName + * @param string $fieldName + * @param mixed $fieldValue + * @param string $op + * @param array $pseudoExtraParam + * @param int $type + * Type of the field per CRM_Utils_Type * * @return array */ - public static function buildQillForFieldValue($daoName = NULL, $fieldName, $fieldValue, $op, $pseduoExtraParam = array()) { + public static function buildQillForFieldValue( + $daoName, + $fieldName, + $fieldValue, + $op, + $pseudoExtraParam = array(), + $type = CRM_Utils_Type::T_STRING + ) { $qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators(); if ($fieldName == 'activity_type_id') { - $pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE); + $pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE); } elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') { - $pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE); + $pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE); } elseif ($fieldName == 'contribution_product_id') { - $pseduoOptions = CRM_Contribute_PseudoConstant::products(); + $pseudoOptions = CRM_Contribute_PseudoConstant::products(); } elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') { - $pseduoOptions = CRM_Core_PseudoConstant::group(); + $pseudoOptions = CRM_Core_PseudoConstant::group(); } elseif ($fieldName == 'country_id') { - $pseduoOptions = CRM_Core_PseudoConstant::country(); + $pseudoOptions = CRM_Core_PseudoConstant::country(); } elseif ($daoName) { - $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array()); + $pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam); } //API usually have fieldValue format as array(operator => array(values)), @@ -5735,18 +5869,33 @@ AND displayRelType.is_active = 1 if (is_array($fieldValue)) { $qillString = array(); - if (!empty($pseduoOptions)) { + if (!empty($pseudoOptions)) { foreach ((array) $fieldValue as $val) { - $qillString[] = CRM_Utils_Array::value($val, $pseduoOptions, $val); + $qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val); } $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($pseduoOptions) && array_key_exists($fieldValue, $pseduoOptions)) { - $fieldValue = $pseduoOptions[$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/Contact/Form/Search/Advanced.php b/CRM/Contact/Form/Search/Advanced.php index 6d400e79c6..21f46f329f 100644 --- a/CRM/Contact/Form/Search/Advanced.php +++ b/CRM/Contact/Form/Search/Advanced.php @@ -401,6 +401,7 @@ class CRM_Contact_Form_Search_Advanced extends CRM_Contact_Form_Search { * Normalize default values for multiselect plugins. * * @param array $defaults + * * @return array */ public function normalizeDefaultValues(&$defaults) { @@ -427,6 +428,16 @@ class CRM_Contact_Form_Search_Advanced extends CRM_Contact_Form_Search { } } } + if (substr($element, 0, 7) == 'custom_' && + (substr($element, -5, 5) == '_from' || substr($element, -3, 3) == '_to') + ) { + // Ensure the _relative field is set if from or to are set to ensure custom date + // fields with 'from' or 'to' values are displayed when the are set in the smart group + // being loaded. (CRM-17116) + if (!isset($defaults[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'])) { + $defaults[CRM_Contact_BAO_Query::getCustomFieldName($element) . '_relative'] = 0; + } + } } } return $defaults; diff --git a/CRM/Contact/Form/Search/Criteria.php b/CRM/Contact/Form/Search/Criteria.php index a0dc548a3d..6fb01c28b0 100644 --- a/CRM/Contact/Form/Search/Criteria.php +++ b/CRM/Contact/Form/Search/Criteria.php @@ -542,12 +542,16 @@ class CRM_Contact_Form_Search_Criteria { foreach ($group['fields'] as $field) { $fieldId = $field['id']; $elementName = 'custom_' . $fieldId; - - CRM_Core_BAO_CustomField::addQuickFormElement($form, - $elementName, - $fieldId, - FALSE, FALSE, TRUE - ); + if ($field['data_type'] == 'Date' && $field['is_search_range']) { + CRM_Core_Form_Date::buildDateRange($form, $elementName, 1, '_from', '_to', ts('From:'), FALSE); + } + else { + CRM_Core_BAO_CustomField::addQuickFormElement($form, + $elementName, + $fieldId, + FALSE, FALSE, TRUE + ); + } } } } diff --git a/CRM/Contribute/BAO/Query.php b/CRM/Contribute/BAO/Query.php index 1693c5d1e1..4ff886c356 100644 --- a/CRM/Contribute/BAO/Query.php +++ b/CRM/Contribute/BAO/Query.php @@ -361,7 +361,7 @@ class CRM_Contribute_BAO_Query { case (strpos($name, '_date') !== FALSE && $name != 'contribution_fulfilled_date'): case 'contribution_campaign_id': $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 08f54b36cf..faceaab739 100644 --- a/CRM/Core/BAO/CustomQuery.php +++ b/CRM/Core/BAO/CustomQuery.php @@ -464,6 +464,14 @@ SELECT label, value break; case 'Date': + if (in_array($op, CRM_Core_DAO::acceptedSQLOperators())) { + $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; + } + $fromValue = CRM_Utils_Array::value('from', $value); $toValue = CRM_Utils_Array::value('to', $value); $value = CRM_Utils_Array::value($op, $value, $value); diff --git a/CRM/Core/DAO.php b/CRM/Core/DAO.php index 821f818854..6991d0d5ae 100644 --- a/CRM/Core/DAO.php +++ b/CRM/Core/DAO.php @@ -2238,6 +2238,8 @@ SELECT contact_id } /** + * Get SQL where clause for SQL filter syntax input parameters. + * * SQL version of api function to assign filters to the DAO based on the syntax * $field => array('IN' => array(4,6,9)) * OR @@ -2258,20 +2260,11 @@ SELECT contact_id * * @throws Exception * - * @todo a better solution would be for the query object to apply these filters based on the - * api supported format (but we don't want to risk breakage in alpha stage & query class is scary - * @todo @time of writing only IN & NOT IN are supported for the array style syntax (as test is - * required to extend further & it may be the comments per above should be implemented. It may be - * preferable to not double-banger the return context next refactor of this - but keeping the attention - * in one place has some advantages as we try to extend this format - * * @return NULL|string|array * a string is returned if $returnSanitisedArray is not set, otherwise and Array or NULL * depending on whether it is supported as yet */ public static function createSQLFilter($fieldName, $filter, $type, $alias = NULL, $returnSanitisedArray = FALSE) { - // http://issues.civicrm.org/jira/browse/CRM-9150 - stick with 'simple' operators for now - // support for other syntaxes is discussed in ticket but being put off for now foreach ($filter as $operator => $criteria) { if (in_array($operator, self::acceptedSQLOperators(), TRUE)) { switch ($operator) { diff --git a/templates/CRM/Custom/Form/Search.tpl b/templates/CRM/Custom/Form/Search.tpl index c29818f434..bda7ef3bbc 100644 --- a/templates/CRM/Custom/Form/Search.tpl +++ b/templates/CRM/Custom/Form/Search.tpl @@ -45,10 +45,8 @@ {$form.$element_name_from.html|crmAddClass:six}   {$form.$element_name_to.label}  {$form.$element_name_to.html|crmAddClass:six} {elseif $element.skip_calendar NEQ true } - {$form.$element_name_from.label} - {include file="CRM/common/jcalendar.tpl" elementName=$element_name_from} -   {$form.$element_name_to.label}   - {include file="CRM/common/jcalendar.tpl" elementName=$element_name_to} + + {include file="CRM/Core/DateRange.tpl" fieldName=$element_name from='_from' to='_to'} {/if} {else} {$form.$element_name.label} -- 2.25.1