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 \API_Exception
99 * @throws \CRM_Core_Exception
100 * @throws \Civi\API\Exception\UnauthorizedException
102 public function testSearchCustomDataDateHighLow() {
103 $this->createCustomGroupWithFieldOfType([], 'date');
104 $dateCustomFieldName = $this->getCustomFieldName('date');
105 // Assigning the relevant form value to be within a custom key is normally done in
106 // build field params. It would be better if it were all done in convertFormValues
107 // but for now we just imitate it.
109 $dateCustomFieldName . '_low' => '2014-06-06',
110 $dateCustomFieldName . '_high' => '2015-06-06',
113 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
114 $queryObject = new CRM_Contact_BAO_Query($params);
115 $queryObject->query();
117 '( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\' ) AND
118 ( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' <= \'20150606235959\' )',
119 trim($queryObject->_where
[0][0])
121 $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]);
122 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
123 $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables
[$this->getCustomGroupTable()]));
127 * Test filtering by the renamed custom date fields.
129 * The conversion to date picker will result int these fields
130 * being renamed _high & _low and needing to return correctly.
132 * @throws \API_Exception
133 * @throws \CRM_Core_Exception
134 * @throws \Civi\API\Exception\UnauthorizedException
136 public function testSearchCustomDataDateLowWithPermsInPlay() {
137 $this->createLoggedInUser();
138 CRM_Core_Config
::singleton()->userPermissionClass
->permissions
= ['view all contacts', 'access all custom data'];
139 $this->createCustomGroupWithFieldOfType([], 'date');
140 $dateCustomFieldName = $this->getCustomFieldName('date');
141 // Assigning the relevant form value to be within a custom key is normally done in
142 // build field params. It would be better if it were all done in convertFormValues
143 // but for now we just imitate it.
145 $dateCustomFieldName . '_low' => '2014-06-06',
148 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
149 $queryObject = new CRM_Contact_BAO_Query($params);
150 $queryObject->query();
152 'civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\'',
153 trim($queryObject->_where
[0][0])
156 '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
157 LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id',
158 trim($queryObject->_fromClause
)
160 $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM"', $queryObject->_qill
[0][0]);
161 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
162 $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables
[$this->getCustomGroupTable()]));
166 * Test filtering by relative custom data dates.
168 * @throws \CRM_Core_Exception
170 public function testSearchCustomDataDateFromTo() {
171 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
172 $dateCustomField = $this->customFieldCreate([
173 'custom_group_id' => $ids['custom_group_id'],
174 'label' => 'date field',
175 'data_type' => 'Date',
176 'html_type' => 'Select Date',
177 'default_value' => NULL,
179 $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
180 // Assigning the relevant form value to be within a custom key is normally done in
181 // build field params. It would be better if it were all done in convertFormValues
182 // but for now we just imitate it.
184 $dateCustomFieldName . '_from' => '2014-06-06',
185 $dateCustomFieldName . '_to' => '2015-06-06',
188 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
189 $queryObj = new CRM_Contact_BAO_Query($params);
192 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"',
193 $queryObj->_where
[0][0]
195 $this->assertEquals($queryObj->_qill
[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'");
197 //CRM-17236 - Test custom date is correctly displayed without time.
198 $formattedValue = CRM_Core_BAO_CustomField
::displayValue(date('Ymdhms'), $dateCustomField['id']);
199 $this->assertEquals(date('m/d/Y'), $formattedValue);
203 * Test filtering by relative custom data.
205 * @throws \CRM_Core_Exception
207 public function testSearchCustomDataFromTo() {
208 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
214 foreach ($datas as $type => $data) {
215 $customField = $this->customFieldCreate(
217 'custom_group_id' => $ids['custom_group_id'],
218 'label' => "$type field",
219 'data_type' => $type,
220 'html_type' => 'Text',
221 'default_value' => NULL,
224 $customFieldName = 'custom_' . $customField['id'];
225 // Assigning the relevant form value to be within a custom key is normally done in
226 // build field params. It would be better if it were all done in convertFormValues
227 // but for now we just imitate it.
231 $customFieldName . '_from' => $from,
232 $customFieldName . '_to' => $to,
235 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
236 $queryObj = new CRM_Contact_BAO_Query($params);
239 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"",
240 $queryObj->_where
[0][0]
242 $this->assertEquals($queryObj->_qill
[0][0], "$type field BETWEEN $from, $to");
247 * Test filtering by relative custom data.
249 * @throws \CRM_Core_Exception
251 public function testSearchCustomDataFromAndTo() {
252 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
254 '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'"],
255 // @todo - investigate the impact of using quotes on what should be an integer field.
256 'Int' => ['value' => 2, 'sql_string' => '"2"'],
257 'Float' => ['value' => 12.123, 'sql_string' => '"12.123"'],
258 'Money' => ['value' => 91.21],
260 foreach ($dataSet as $type => $values) {
261 $data = $values['value'];
262 $isDate = ($type === 'Date');
263 $customField = $this->customFieldCreate(
265 'custom_group_id' => $ids['custom_group_id'],
266 'label' => "$type field",
267 'data_type' => $type,
268 'html_type' => ($isDate) ?
'Select Date' : 'Text',
269 'default_value' => NULL,
272 $customFieldName = 'custom_' . $customField['id'];
274 $expectedValue = $values['sql_string'] ??
$data;
275 $expectedQillValue = $values['qill_string'] ??
$data;
276 $toQillValue = chr(226) . chr(137) . chr(164) . ' ' . $expectedQillValue;
277 $fromQillValue = chr(226) . chr(137) . chr(165) . ' ' . ($values['qill_string_greater'] ??
$expectedQillValue);
279 // Assigning the relevant form value to be within a custom key is normally done in
280 // build field params. It would be better if it were all done in convertFormValues
281 // but for now we just imitate it.
283 //Scenario 2 : TO date filter
285 $customFieldName . '_to' => $data,
288 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
289 $queryObj = new CRM_Contact_BAO_Query($params);
293 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue",
294 $queryObj->_where
[0][0]
296 $this->assertEquals($queryObj->_qill
[0][0],
297 "$type field $toQillValue"
300 //Scenario 2 : FROM date filter
302 $customFieldName . '_from' => $values['value'],
305 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
306 $queryObj = new CRM_Contact_BAO_Query($params);
309 $expectedValue = ($isDate) ?
'"20150606000000"' : $expectedValue;
311 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue",
312 $queryObj->_where
[0][0]
315 "$type field $fromQillValue",
316 $queryObj->_qill
[0][0]
322 * Test filtering by relative custom data dates.
324 * @throws \CRM_Core_Exception
326 public function testSearchCustomDataDateEquals() {
327 $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__
, 'ContactTestTest');
328 $dateCustomField = $this->customFieldCreate([
329 'custom_group_id' => $ids['custom_group_id'],
330 'label' => 'date field',
331 'data_type' => 'Date',
332 'html_type' => 'Select Date',
333 'default_value' => NULL,
335 $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
336 $this->individualCreate([$dateCustomFieldName => '2015-01-01']);
337 // Assigning the relevant form value to be within a custom key is normally done in
338 // build field params. It would be better if it were all done in convertFormValues
339 // but for now we just imitate it.
340 $formValues = [$dateCustomFieldName => '2015-06-06'];
341 $params = CRM_Contact_BAO_Query
::convertFormValues($formValues);
342 $queryObj = new CRM_Contact_BAO_Query($params);
346 "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'",
347 $queryObj->_where
[0][0]
349 $this->assertEquals($queryObj->_qill
[0][0], "date field = 'June 6th, 2015'");
353 * Test search builder style query including custom address fields.
355 * @throws \API_Exception
356 * @throws \CRM_Core_Exception
357 * @throws \Civi\API\Exception\UnauthorizedException
359 public function testAddressCustomFields() {
360 $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int');
361 $individualID = $this->individualCreate();
362 $this->callAPISuccess('Address', 'create', [
363 'contact_id' => $individualID,
364 'street_address' => '10 Downing Street',
365 'location_type_id' => 'Home',
366 $this->getCustomFieldName('int') => 5,
369 $queryObject = new CRM_Contact_BAO_Query(
370 [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]],
371 ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]]
373 $queryObject->query();
374 $tableName = $this->getCustomGroupTable();
375 $fieldName = $this->getCustomFieldColumnName('int');
377 $this->assertEquals([], $queryObject->_where
[0]);
378 $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where
[1]));
379 $this->assertEquals(1, $queryObject->_whereTables
['civicrm_contact']);
380 $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']));
381 $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables
[$tableName]));
382 $this->assertEquals([], $queryObject->_qill
[0]);
383 $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill
[1]);