From 8fc9f99a7201acad4c66ba1c652c2218e1466aa4 Mon Sep 17 00:00:00 2001 From: eileen Date: Tue, 12 Mar 2019 13:39:51 +1300 Subject: [PATCH] Fix relative dates when searching on activity_date_time In testing I found that there is an unreleased regression relating to our date conversion on activity_date_time in activity search. This fixes and adds tests. (regression was it was being ignored). Note that the option value is capitalised which makes the capitalisation slightly odd but I think accepting that is the least bad option --- CRM/Contact/BAO/Query.php | 63 +++++++++++++++++++ .../phpunit/CRM/Activity/Form/SearchTest.php | 59 +++++++++++++++++ 2 files changed, 122 insertions(+) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index abd8711a80..1b98afbb00 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -1784,6 +1784,11 @@ class CRM_Contact_BAO_Query { * @param string $apiEntity */ public function whereClauseSingle(&$values, $apiEntity = NULL) { + if ($this->isARelativeDateField($values[0])) { + $this->buildRelativeDateQuery($values); + return; + } + // do not process custom fields or prefixed contact ids or component params if (CRM_Core_BAO_CustomField::getKeyID($values[0]) || (substr($values[0], 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX) || @@ -6886,4 +6891,62 @@ AND displayRelType.is_active = 1 return TRUE; } + /** + * Is the field a relative date field. + * + * @param string $fieldName + * + * @return bool + */ + protected function isARelativeDateField($fieldName) { + if (substr($fieldName, -9, 9) !== '_relative') { + return FALSE; + } + $realField = substr($fieldName, 0, strlen($fieldName) - 9); + return isset($this->_fields[$realField]); + } + + /** + * @param $values + */ + protected function buildRelativeDateQuery(&$values) { + $value = CRM_Utils_Array::value(2, $values); + if (empty($value)) { + return; + } + $fieldName = substr($values[0], 0, strlen($values[0]) - 9); + $fieldSpec = $this->_fields[$fieldName]; + $tableName = $fieldSpec['table_name']; + $filters = CRM_Core_OptionGroup::values('relative_date_filters'); + $grouping = CRM_Utils_Array::value(3, $values); + $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1; + + $dates = CRM_Utils_Date::getFromTo($value, NULL, NULL); + if (empty($dates[0])) { + // ie. no start date we only have end date + $this->_where[$grouping][] = $fieldSpec['where'] . " <= '{$dates[1]}'"; + + $this->_qill[$grouping][] = ts('%1 is ', [$fieldSpec['title']]) . $filters[$value] . ' (' . ts("to %1", [ + CRM_Utils_Date::customFormat($dates[1]), + ]) . ')'; + } + elseif (empty($dates[1])) { + // ie. no end date we only have start date + $this->_where[$grouping][] = $fieldSpec['where'] . " >= '{$dates[1]}'"; + + $this->_qill[$grouping][] = ts('%1 is ', [$fieldSpec['title']]) . $filters[$value] . ' (' . ts("from %1", [ + CRM_Utils_Date::customFormat($dates[0]), + ]) . ')'; + } + else { + // we have start and end dates. + $this->_where[$grouping][] = $fieldSpec['where'] . " BETWEEN '{$dates[0]}' AND '{$dates[1]}'"; + + $this->_qill[$grouping][] = ts('%1 is ', [$fieldSpec['title']]) . $filters[$value] . ' (' . ts("between %1 and %2", [ + CRM_Utils_Date::customFormat($dates[0]), + CRM_Utils_Date::customFormat($dates[1]), + ]) . ')'; + } + } + } diff --git a/tests/phpunit/CRM/Activity/Form/SearchTest.php b/tests/phpunit/CRM/Activity/Form/SearchTest.php index b3fdb796bf..9061313f1f 100644 --- a/tests/phpunit/CRM/Activity/Form/SearchTest.php +++ b/tests/phpunit/CRM/Activity/Form/SearchTest.php @@ -64,4 +64,63 @@ class CRM_Activity_Form_SearchTest extends CiviUnitTestCase { ], $rows); } + /** + * Test the Qill for activity Date time. + * + * @dataProvider getSearchCriteria + * + * @param array $searchCriteria + * @param array $expectedQill + */ + public function testQill($searchCriteria, $expectedQill) { + $selector = new CRM_Activity_Selector_Search($searchCriteria); + $this->assertEquals($expectedQill, $selector->getQILL()); + } + + /** + * Get criteria for activity testing. + */ + public function getSearchCriteria() { + + // We have to define format because tests crash trying to access the config param from the dataProvider + // perhaps because there is no property on config? + $format = '%B %E%f, %Y %l:%M %P'; + $dates['ending_60.day'] = CRM_Utils_Date::getFromTo('ending_60.day', NULL, NULL); + $dates['earlier.year'] = CRM_Utils_Date::getFromTo('earlier.year', NULL, NULL); + $dates['greater.year'] = CRM_Utils_Date::getFromTo('greater.year', NULL, NULL); + return [ + [ + 'search_criteria' => [ + ['activity_date_time_relative', '=', 'ending_60.day', 0, 0], + ], + 'expected_qill' => [['Activity Date is Last 60 days including today (between ' . CRM_Utils_Date::customFormat($dates['ending_60.day'][0], $format) . ' and ' . CRM_Utils_Date::customFormat($dates['ending_60.day'][1], $format) . ')']], + ], + [ + 'search_criteria' => [ + ['activity_date_time_relative', '=', 'earlier.year', 0, 0], + ], + 'expected_qill' => [['Activity Date is To end of previous calendar year (to ' . CRM_Utils_Date::customFormat($dates['earlier.year'][1], $format) . ')']], + ], + [ + 'search_criteria' => [ + ['activity_date_time_relative', '=', 'greater.year', 0, 0], + ], + 'expected_qill' => [['Activity Date is From start of current calendar year (from ' . CRM_Utils_Date::customFormat($dates['greater.year'][0], $format) . ')']], + ], + [ + 'search_criteria' => [ + ['activity_date_time_low', '=', '2019-03-05', 0, 0], + ['activity_date_time_high', '=', '2019-03-27', 0, 0], + ], + 'expected_qill' => [['Activity Date - greater than or equal to "March 5th, 2019 12:00 AM" AND less than or equal to "March 27th, 2019 11:59 PM"']], + ], + [ + 'search_criteria' => [ + ['activity_status_id', '=', ['IN' => ['1', '2']], 0, 0] + ], + 'expected_qill' => [['Activity Status In Scheduled, Completed']], + ], + ]; + } + } -- 2.25.1