From 2206409b245dade3543be7fd8108756f6131fe36 Mon Sep 17 00:00:00 2001 From: eileen Date: Mon, 4 Nov 2019 17:17:21 +1300 Subject: [PATCH] Fix Custom field date query builder to work with 'raw' high & low params Once we convert to datePicker we will be passing in parameters like custom_3_high, custom_3_low & custom_3_relative. Currently we pass in custom_3_to & custom_3_from and custom_3_relative. This adds support for the new high & low withoutt touching support for previously working or switching the field across (as yet). This paves the way for the datepicker conversion. After a lot of brain pain I concluded the fundamental problem was the whereTables from the CustomQuery were being merged in but actually then lost when where() is called. --- CRM/Contact/BAO/Query.php | 10 +++--- CRM/Core/BAO/CustomQuery.php | 4 ++- .../phpunit/CRM/Core/BAO/CustomQueryTest.php | 32 +++++++++++++++++++ .../CRMTraits/Custom/CustomDataTrait.php | 2 +- 4 files changed, 41 insertions(+), 7 deletions(-) diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index b7b27375c4..777d09d4c9 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -591,7 +591,6 @@ class CRM_Contact_BAO_Query { $this->_select = array_merge($this->_select, $this->_customQuery->_select); $this->_element = array_merge($this->_element, $this->_customQuery->_element); $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables); - $this->_whereTables = array_merge($this->_whereTables, $this->_customQuery->_whereTables); $this->_options = $this->_customQuery->_options; } $isForcePrimaryOnly = !empty($apiEntity); @@ -2077,12 +2076,12 @@ class CRM_Contact_BAO_Query { } if ($this->_customQuery) { + $this->_whereTables = array_merge($this->_whereTables, $this->_customQuery->_whereTables); // Added following if condition to avoid the wrong value display for 'my account' / any UF info. // Hope it wont affect the other part of civicrm.. if it does please remove it. if (!empty($this->_customQuery->_where)) { $this->_where = CRM_Utils_Array::crmArrayMerge($this->_where, $this->_customQuery->_where); } - $this->_qill = CRM_Utils_Array::crmArrayMerge($this->_qill, $this->_customQuery->_qill); } @@ -2154,7 +2153,8 @@ class CRM_Contact_BAO_Query { $realFieldName = str_replace(['_high', '_low'], '', $name); if (isset($this->_fields[$realFieldName])) { $field = $this->_fields[str_replace(['_high', '_low'], '', $realFieldName)]; - $this->dateQueryBuilder($values, $field['table_name'], $realFieldName, $field['name'], $field['title']); + $columnName = $field['column_name'] ?? $field['name']; + $this->dateQueryBuilder($values, $field['table_name'], $realFieldName, $columnName, $field['title']); } return; } @@ -5303,8 +5303,8 @@ civicrm_relationship.start_date > {$today} // Special handling for contact table as it has a known alias in advanced search. $tableName = 'contact_a'; } - if ($name == "{$fieldName}_low" || - $name == "{$fieldName}_high" + if ($name === "{$fieldName}_low" || + $name === "{$fieldName}_high" ) { if (isset($this->_rangeCache[$fieldName]) || !$value) { return; diff --git a/CRM/Core/BAO/CustomQuery.php b/CRM/Core/BAO/CustomQuery.php index 622200624c..774048bb64 100644 --- a/CRM/Core/BAO/CustomQuery.php +++ b/CRM/Core/BAO/CustomQuery.php @@ -396,7 +396,9 @@ SELECT f.id, f.label, f.data_type, break; case 'Date': - if (substr($name, -9, 9) !== '_relative') { + if (substr($name, -9, 9) !== '_relative' + && substr($name, -4, 4) !== '_low' + && substr($name, -5, 5) !== '_high') { // Relative dates are handled in the buildRelativeDateQuery function. $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, [], CRM_Utils_Type::T_DATE); diff --git a/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php b/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php index f499987ccf..15b93fcb19 100644 --- a/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php +++ b/tests/phpunit/CRM/Core/BAO/CustomQueryTest.php @@ -89,6 +89,38 @@ class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { } + /** + * Test filtering by the renamed custom date fields. + * + * The conversion to date picker will result int these fields + * being renamed _high & _low and needing to return correctly. + * + * @throws \CRM_Core_Exception + */ + public function testSearchCustomDataDateHighLow() { + $this->createCustomGroupWithFieldOfType([], 'date'); + $dateCustomFieldName = $this->getCustomFieldName('date'); + // Assigning the relevant form value to be within a custom key is normally done in + // build field params. It would be better if it were all done in convertFormValues + // but for now we just imitate it. + $formValues = [ + $dateCustomFieldName . '_low' => '2014-06-06', + $dateCustomFieldName . '_high' => '2015-06-06', + ]; + + $params = CRM_Contact_BAO_Query::convertFormValues($formValues); + $queryObject = new CRM_Contact_BAO_Query($params); + $queryObject->query(); + $this->assertEquals( + '( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\' ) AND +( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' <= \'20150606235959\' )', + trim($queryObject->_where[0][0]) + ); + $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM" AND less than or equal to "June 6th, 2015 11:59 PM"', $queryObject->_qill[0][0]); + $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']); + $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables[$this->getCustomGroupTable()])); + } + /** * Test filtering by relative custom data dates. * diff --git a/tests/phpunit/CRMTraits/Custom/CustomDataTrait.php b/tests/phpunit/CRMTraits/Custom/CustomDataTrait.php index f282af1d54..c0217bb2f4 100644 --- a/tests/phpunit/CRMTraits/Custom/CustomDataTrait.php +++ b/tests/phpunit/CRMTraits/Custom/CustomDataTrait.php @@ -310,7 +310,7 @@ trait CRMTraits_Custom_CustomDataTrait { protected function createDateCustomField($params): array { $params = array_merge([ 'name' => 'test_date', - 'label' => 'test_date', + 'label' => 'Test Date', 'html_type' => 'Select Date', 'data_type' => 'Date', 'default_value' => '20090711', -- 2.25.1