4 * Include dataProvider for tests
7 class CRM_Core_BAO_CustomQueryTest
extends CiviUnitTestCase
{
8 use CRMTraits_Custom_CustomDataTrait
;
11 * Restore database to empty state.
13 * Note that rollback won't remove custom tables.
17 public function tearDown() {
21 $this->quickCleanup($tablesToTruncate, TRUE);
26 * Test filtering by relative custom data dates.
28 * @throws \CRM_Core_Exception
30 public function testSearchCustomDataDateRelative() {
31 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
32 $dateCustomField = $this->customFieldCreate([
33 'custom_group_id' => $ids['custom_group_id'],
34 'label' => 'date field',
35 'data_type' => 'Date',
36 'html_type' => 'Select Date',
37 'default_value' => NULL,
39 $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
41 $dateCustomFieldName . '_relative' => 'this.year',
42 $dateCustomFieldName . '_from' => '',
43 $dateCustomFieldName . '_to' => '',
45 // Assigning the relevant form value to be within a custom key is normally done in
46 // build field params. It would be better if it were all done in convertFormValues
47 // but for now we just imitate it.
49 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
50 $queryObj = new CRM_Contact_BAO_Query($params);
52 "civicrm_value_testsearchcus_1.date_field_2 BETWEEN '" . date('Y') . "0101000000' AND '" . date('Y') . "1231235959'",
53 $queryObj->_where
[0][0]
55 $this->assertEquals('date field is This calendar year (between January 1st, ' . date('Y') . " 12:00 AM and December 31st, " . date('Y') . " 11:59 PM)", $queryObj->_qill
[0][0]);
56 $queryObj = new CRM_Contact_BAO_Query($params);
58 'id' => $dateCustomField['id'],
59 'label' => 'date field',
60 'extends' => 'Contact',
61 'data_type' => 'Date',
62 'html_type' => 'Select Date',
63 'is_search_range' => '0',
64 'column_name' => 'date_field_' . $dateCustomField['id'],
65 'table_name' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'],
66 'option_group_id' => NULL,
67 'groupTitle' => 'testSearchCustomDataDateRelative',
68 'default_value' => NULL,
69 'text_length' => NULL,
70 'options_per_line' => NULL,
71 'custom_group_id' => '1',
72 'extends_entity_column_value' => NULL,
73 'extends_entity_column_id' => NULL,
76 'date_format' => 'mm/dd/yy',
77 'time_format' => NULL,
79 'extends_table' => 'civicrm_contact',
80 'search_table' => 'contact_a',
81 'headerPattern' => '//',
82 'title' => 'date field',
83 'custom_field_id' => $dateCustomField['id'],
84 'name' => 'custom_' . $dateCustomField['id'],
86 'where' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'] . '.date_field_' . $dateCustomField['id'],
88 ], $queryObj->getFieldSpec('custom_' . $dateCustomField['id']));
93 * Test filtering by the renamed custom date fields.
95 * The conversion to date picker will result int these fields
96 * being renamed _high & _low and needing to return correctly.
98 * @throws \CRM_Core_Exception
100 public function testSearchCustomDataDateHighLow() {
101 $this->createCustomGroupWithFieldOfType([], 'date');
102 $dateCustomFieldName = $this->getCustomFieldName('date');
103 // Assigning the relevant form value to be within a custom key is normally done in
104 // build field params. It would be better if it were all done in convertFormValues
105 // but for now we just imitate it.
107 $dateCustomFieldName . '_low' => '2014-06-06',
108 $dateCustomFieldName . '_high' => '2015-06-06',
111 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
112 $queryObject = new CRM_Contact_BAO_Query($params);
113 $queryObject->query();
115 '( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\' ) AND
116 ( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' <= \'20150606235959\' )',
117 trim($queryObject->_where
[0][0])
119 $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]);
120 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
121 $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables
[$this->getCustomGroupTable()]));
125 * Test filtering by the renamed custom date fields.
127 * The conversion to date picker will result int these fields
128 * being renamed _high & _low and needing to return correctly.
130 * @throws \CRM_Core_Exception
132 public function testSearchCustomDataDateLowWithPermsInPlay() {
133 $this->createLoggedInUser();
134 CRM_Core_Config
::singleton()->userPermissionClass
->permissions
= ['view all contacts', 'access all custom data'];
135 $this->createCustomGroupWithFieldOfType([], 'date');
136 $dateCustomFieldName = $this->getCustomFieldName('date');
137 // Assigning the relevant form value to be within a custom key is normally done in
138 // build field params. It would be better if it were all done in convertFormValues
139 // but for now we just imitate it.
141 $dateCustomFieldName . '_low' => '2014-06-06',
144 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
145 $queryObject = new CRM_Contact_BAO_Query($params);
146 $queryObject->query();
148 'civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\'',
149 trim($queryObject->_where
[0][0])
152 'FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id
153 LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id',
154 trim($queryObject->_fromClause
)
156 $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM"', $queryObject->_qill
[0][0]);
157 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
158 $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables
[$this->getCustomGroupTable()]));
162 * Test filtering by relative custom data dates.
164 * @throws \CRM_Core_Exception
166 public function testSearchCustomDataDateFromTo() {
167 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
168 $dateCustomField = $this->customFieldCreate([
169 'custom_group_id' => $ids['custom_group_id'],
170 'label' => 'date field',
171 'data_type' => 'Date',
172 'html_type' => 'Select Date',
173 'default_value' => NULL,
175 $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
176 // Assigning the relevant form value to be within a custom key is normally done in
177 // build field params. It would be better if it were all done in convertFormValues
178 // but for now we just imitate it.
180 $dateCustomFieldName . '_from' => '2014-06-06',
181 $dateCustomFieldName . '_to' => '2015-06-06',
184 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
185 $queryObj = new CRM_Contact_BAO_Query($params);
188 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"',
189 $queryObj->_where
[0][0]
191 $this->assertEquals($queryObj->_qill
[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'");
193 //CRM-17236 - Test custom date is correctly displayed without time.
194 $formattedValue = CRM_Core_BAO_CustomField
::displayValue(date('Ymdhms'), $dateCustomField['id']);
195 $this->assertEquals(date('m/d/Y'), $formattedValue);
199 * Test filtering by relative custom data.
201 * @throws \CRM_Core_Exception
203 public function testSearchCustomDataFromTo() {
204 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
210 foreach ($datas as $type => $data) {
211 $customField = $this->customFieldCreate(
213 'custom_group_id' => $ids['custom_group_id'],
214 'label' => "$type field",
215 'data_type' => $type,
216 'html_type' => 'Text',
217 'default_value' => NULL,
220 $customFieldName = 'custom_' . $customField['id'];
221 // Assigning the relevant form value to be within a custom key is normally done in
222 // build field params. It would be better if it were all done in convertFormValues
223 // but for now we just imitate it.
227 $customFieldName . '_from' => $from,
228 $customFieldName . '_to' => $to,
231 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
232 $queryObj = new CRM_Contact_BAO_Query($params);
235 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"",
236 $queryObj->_where
[0][0]
238 $this->assertEquals($queryObj->_qill
[0][0], "$type field BETWEEN $from, $to");
243 * Test filtering by relative custom data.
245 * @throws \CRM_Core_Exception
247 public function testSearchCustomDataFromAndTo() {
248 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
250 'Date' => ['value' => '2015-06-06', 'sql_string' => '"20150606235959"', 'qill_string' => "'June 6th, 2015 11:59 PM'", 'qill_string_greater' => "'June 6th, 2015 12:00 AM'"],
251 // @todo - investigate the impact of using quotes on what should be an integer field.
252 'Int' => ['value' => 2, 'sql_string' => '"2"'],
253 'Float' => ['value' => 12.123, 'sql_string' => '"12.123"'],
254 'Money' => ['value' => 91.21],
256 foreach ($dataSet as $type => $values) {
257 $data = $values['value'];
258 $isDate = ($type === 'Date');
259 $customField = $this->customFieldCreate(
261 'custom_group_id' => $ids['custom_group_id'],
262 'label' => "$type field",
263 'data_type' => $type,
264 'html_type' => ($isDate) ?
'Select Date' : 'Text',
265 'default_value' => NULL,
268 $customFieldName = 'custom_' . $customField['id'];
270 $expectedValue = $values['sql_string'] ??
$data;
271 $expectedQillValue = $values['qill_string'] ??
$data;
272 $toQillValue = chr(226) . chr(137) . chr(164) . ' ' . $expectedQillValue;
273 $fromQillValue = chr(226) . chr(137) . chr(165) . ' ' . ($values['qill_string_greater'] ??
$expectedQillValue);
275 // Assigning the relevant form value to be within a custom key is normally done in
276 // build field params. It would be better if it were all done in convertFormValues
277 // but for now we just imitate it.
279 //Scenario 2 : TO date filter
281 $customFieldName . '_to' => $data,
284 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
285 $queryObj = new CRM_Contact_BAO_Query($params);
289 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue",
290 $queryObj->_where
[0][0]
292 $this->assertEquals($queryObj->_qill
[0][0],
293 "$type field $toQillValue"
296 //Scenario 2 : FROM date filter
298 $customFieldName . '_from' => $values['value'],
301 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
302 $queryObj = new CRM_Contact_BAO_Query($params);
305 $expectedValue = ($isDate) ?
'"20150606000000"' : $expectedValue;
307 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue",
308 $queryObj->_where
[0][0]
311 "$type field $fromQillValue",
312 $queryObj->_qill
[0][0]
318 * Test filtering by relative custom data dates.
320 * @throws \CRM_Core_Exception
322 public function testSearchCustomDataDateEquals() {
323 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
324 $dateCustomField = $this->customFieldCreate([
325 'custom_group_id' => $ids['custom_group_id'],
326 'label' => 'date field',
327 'data_type' => 'Date',
328 'html_type' => 'Select Date',
329 'default_value' => NULL,
331 $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
332 $this->individualCreate([$dateCustomFieldName => "2015-01-01"]);
333 // Assigning the relevant form value to be within a custom key is normally done in
334 // build field params. It would be better if it were all done in convertFormValues
335 // but for now we just imitate it.
336 $formValues = [$dateCustomFieldName => '2015-06-06'];
337 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
338 $queryObj = new CRM_Contact_BAO_Query($params);
342 "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'",
343 $queryObj->_where
[0][0]
345 $this->assertEquals($queryObj->_qill
[0][0], "date field = 'June 6th, 2015'");
349 * Test search builder style query including custom address fields.
351 * @throws \CRM_Core_Exception
353 public function testAddressCustomFields() {
354 $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int');
355 $individualID = $this->individualCreate();
356 $this->callAPISuccess('Address', 'create', [
357 'contact_id' => $individualID,
358 'street_address' => '10 Downing Street',
359 'location_type_id' => 'Home',
360 $this->getCustomFieldName('int') => 5,
363 $queryObject = new CRM_Contact_BAO_Query(
364 [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]],
365 ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]]
367 $queryObject->query();
368 $tableName = $this->getCustomGroupTable();
369 $fieldName = $this->getCustomFieldColumnName('int');
371 $this->assertEquals([], $queryObject->_where
[0]);
372 $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where
[1]));
373 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
374 $this->assertEquals('LEFT JOIN civicrm_address `Home-address` ON (`Home-address`.contact_id = contact_a.id AND `Home-address`.location_type_id = 1)', trim($queryObject->_whereTables
['Home-address']));
375 $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables
[$tableName]));
376 $this->assertEquals([], $queryObject->_qill
[0]);
377 $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill
[1]);