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'],
89 ], $queryObj->getFieldSpec('custom_' . $dateCustomField['id']));
94 * Test filtering by the renamed custom date fields.
96 * The conversion to date picker will result int these fields
97 * being renamed _high & _low and needing to return correctly.
99 * @throws \API_Exception
100 * @throws \CRM_Core_Exception
101 * @throws \Civi\API\Exception\UnauthorizedException
103 public function testSearchCustomDataDateHighLow() {
104 $this->createCustomGroupWithFieldOfType([], 'date');
105 $dateCustomFieldName = $this->getCustomFieldName('date');
106 // Assigning the relevant form value to be within a custom key is normally done in
107 // build field params. It would be better if it were all done in convertFormValues
108 // but for now we just imitate it.
110 $dateCustomFieldName . '_low' => '2014-06-06',
111 $dateCustomFieldName . '_high' => '2015-06-06',
114 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
115 $queryObject = new CRM_Contact_BAO_Query($params);
116 $queryObject->query();
118 '( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\' ) AND
119 ( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' <= \'20150606235959\' )',
120 trim($queryObject->_where
[0][0])
122 $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]);
123 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
124 $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables
[$this->getCustomGroupTable()]));
128 * Test filtering by the renamed custom date fields.
130 * The conversion to date picker will result int these fields
131 * being renamed _high & _low and needing to return correctly.
133 * @throws \API_Exception
134 * @throws \CRM_Core_Exception
135 * @throws \Civi\API\Exception\UnauthorizedException
137 public function testSearchCustomDataDateLowWithPermsInPlay() {
138 $this->createLoggedInUser();
139 CRM_Core_Config
::singleton()->userPermissionClass
->permissions
= ['view all contacts', 'access all custom data'];
140 $this->createCustomGroupWithFieldOfType([], 'date');
141 $dateCustomFieldName = $this->getCustomFieldName('date');
142 // Assigning the relevant form value to be within a custom key is normally done in
143 // build field params. It would be better if it were all done in convertFormValues
144 // but for now we just imitate it.
146 $dateCustomFieldName . '_low' => '2014-06-06',
149 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
150 $queryObject = new CRM_Contact_BAO_Query($params);
151 $queryObject->query();
153 'civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\'',
154 trim($queryObject->_where
[0][0])
157 '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' .
158 ' LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id',
159 preg_replace('/\s+/', ' ', trim($queryObject->_fromClause
))
161 $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM"', $queryObject->_qill
[0][0]);
162 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
163 $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables
[$this->getCustomGroupTable()]));
167 * Test filtering by relative custom data dates.
169 * @throws \CRM_Core_Exception
171 public function testSearchCustomDataDateFromTo() {
172 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
173 $dateCustomField = $this->customFieldCreate([
174 'custom_group_id' => $ids['custom_group_id'],
175 'label' => 'date field',
176 'data_type' => 'Date',
177 'html_type' => 'Select Date',
178 'default_value' => NULL,
180 $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
181 // Assigning the relevant form value to be within a custom key is normally done in
182 // build field params. It would be better if it were all done in convertFormValues
183 // but for now we just imitate it.
185 $dateCustomFieldName . '_from' => '2014-06-06',
186 $dateCustomFieldName . '_to' => '2015-06-06',
189 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
190 $queryObj = new CRM_Contact_BAO_Query($params);
193 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"',
194 $queryObj->_where
[0][0]
196 $this->assertEquals($queryObj->_qill
[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'");
198 //CRM-17236 - Test custom date is correctly displayed without time.
199 $formattedValue = CRM_Core_BAO_CustomField
::displayValue(date('Ymdhms'), $dateCustomField['id']);
200 $this->assertEquals(date('m/d/Y'), $formattedValue);
204 * Test filtering by relative custom data.
206 * @throws \CRM_Core_Exception
208 public function testSearchCustomDataFromTo() {
209 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
215 foreach ($datas as $type => $data) {
216 $customField = $this->customFieldCreate(
218 'custom_group_id' => $ids['custom_group_id'],
219 'label' => "$type field",
220 'data_type' => $type,
221 'html_type' => 'Text',
222 'default_value' => NULL,
225 $customFieldName = 'custom_' . $customField['id'];
226 // Assigning the relevant form value to be within a custom key is normally done in
227 // build field params. It would be better if it were all done in convertFormValues
228 // but for now we just imitate it.
232 $customFieldName . '_from' => $from,
233 $customFieldName . '_to' => $to,
236 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
237 $queryObj = new CRM_Contact_BAO_Query($params);
240 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"",
241 $queryObj->_where
[0][0]
243 $this->assertEquals($queryObj->_qill
[0][0], "$type field BETWEEN $from, $to");
248 * Test filtering by relative custom data.
250 * @throws \CRM_Core_Exception
252 public function testSearchCustomDataFromAndTo() {
253 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
255 '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'"],
256 // @todo - investigate the impact of using quotes on what should be an integer field.
257 'Int' => ['value' => 2, 'sql_string' => '"2"'],
258 'Float' => ['value' => 12.123, 'sql_string' => '"12.123"'],
259 'Money' => ['value' => 91.21],
261 foreach ($dataSet as $type => $values) {
262 $data = $values['value'];
263 $isDate = ($type === 'Date');
264 $customField = $this->customFieldCreate(
266 'custom_group_id' => $ids['custom_group_id'],
267 'label' => "$type field",
268 'data_type' => $type,
269 'html_type' => ($isDate) ?
'Select Date' : 'Text',
270 'default_value' => NULL,
273 $customFieldName = 'custom_' . $customField['id'];
275 $expectedValue = $values['sql_string'] ??
$data;
276 $expectedQillValue = $values['qill_string'] ??
$data;
277 $toQillValue = chr(226) . chr(137) . chr(164) . ' ' . $expectedQillValue;
278 $fromQillValue = chr(226) . chr(137) . chr(165) . ' ' . ($values['qill_string_greater'] ??
$expectedQillValue);
280 // Assigning the relevant form value to be within a custom key is normally done in
281 // build field params. It would be better if it were all done in convertFormValues
282 // but for now we just imitate it.
284 //Scenario 2 : TO date filter
286 $customFieldName . '_to' => $data,
289 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
290 $queryObj = new CRM_Contact_BAO_Query($params);
294 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue",
295 $queryObj->_where
[0][0]
297 $this->assertEquals($queryObj->_qill
[0][0],
298 "$type field $toQillValue"
301 //Scenario 2 : FROM date filter
303 $customFieldName . '_from' => $values['value'],
306 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
307 $queryObj = new CRM_Contact_BAO_Query($params);
310 $expectedValue = ($isDate) ?
'"20150606000000"' : $expectedValue;
312 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue",
313 $queryObj->_where
[0][0]
316 "$type field $fromQillValue",
317 $queryObj->_qill
[0][0]
323 * Test filtering by relative custom data dates.
325 * @throws \CRM_Core_Exception
327 public function testSearchCustomDataDateEquals() {
328 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
329 $dateCustomField = $this->customFieldCreate([
330 'custom_group_id' => $ids['custom_group_id'],
331 'label' => 'date field',
332 'data_type' => 'Date',
333 'html_type' => 'Select Date',
334 'default_value' => NULL,
336 $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
337 $this->individualCreate([$dateCustomFieldName => '2015-01-01']);
338 // Assigning the relevant form value to be within a custom key is normally done in
339 // build field params. It would be better if it were all done in convertFormValues
340 // but for now we just imitate it.
341 $formValues = [$dateCustomFieldName => '2015-06-06'];
342 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
343 $queryObj = new CRM_Contact_BAO_Query($params);
347 "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'",
348 $queryObj->_where
[0][0]
350 $this->assertEquals($queryObj->_qill
[0][0], "date field = 'June 6th, 2015'");
354 * Test search builder style query including custom address fields.
356 * @throws \API_Exception
357 * @throws \CRM_Core_Exception
358 * @throws \Civi\API\Exception\UnauthorizedException
360 public function testAddressCustomFields() {
361 $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int');
362 $individualID = $this->individualCreate();
363 $this->callAPISuccess('Address', 'create', [
364 'contact_id' => $individualID,
365 'street_address' => '10 Downing Street',
366 'location_type_id' => 'Home',
367 $this->getCustomFieldName('int') => 5,
370 $queryObject = new CRM_Contact_BAO_Query(
371 [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]],
372 ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]]
374 $queryObject->query();
375 $tableName = $this->getCustomGroupTable();
376 $fieldName = $this->getCustomFieldColumnName('int');
378 $this->assertEquals([], $queryObject->_where
[0]);
379 $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where
[1]));
380 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
381 $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']));
382 $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables
[$tableName]));
383 $this->assertEquals([], $queryObject->_qill
[0]);
384 $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill
[1]);