Commit | Line | Data |
---|---|---|
2fe91f9d | 1 | <?php |
0eea664b | 2 | |
2fe91f9d | 3 | /** |
4 | * Include dataProvider for tests | |
acb109b7 | 5 | * @group headless |
2fe91f9d | 6 | */ |
7 | class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { | |
0e1544e7 | 8 | use CRMTraits_Custom_CustomDataTrait; |
2fe91f9d | 9 | |
10 | /** | |
11 | * Restore database to empty state. | |
12 | * | |
13 | * Note that rollback won't remove custom tables. | |
14 | * | |
15 | * @throws \Exception | |
16 | */ | |
17 | public function tearDown() { | |
9099cab3 | 18 | $tablesToTruncate = [ |
2fe91f9d | 19 | 'civicrm_contact', |
9099cab3 | 20 | ]; |
2fe91f9d | 21 | $this->quickCleanup($tablesToTruncate, TRUE); |
22 | parent::tearDown(); | |
23 | } | |
24 | ||
25 | /** | |
26 | * Test filtering by relative custom data dates. | |
0e1544e7 | 27 | * |
28 | * @throws \CRM_Core_Exception | |
2fe91f9d | 29 | */ |
30 | public function testSearchCustomDataDateRelative() { | |
31 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
9099cab3 | 32 | $dateCustomField = $this->customFieldCreate([ |
2fe91f9d | 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, | |
9099cab3 | 38 | ]); |
2fe91f9d | 39 | $dateCustomFieldName = 'custom_' . $dateCustomField['id']; |
9099cab3 | 40 | $formValues = [ |
2fe91f9d | 41 | $dateCustomFieldName . '_relative' => 'this.year', |
42 | $dateCustomFieldName . '_from' => '', | |
43 | $dateCustomFieldName . '_to' => '', | |
9099cab3 | 44 | ]; |
2fe91f9d | 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. | |
7f175707 | 48 | |
49 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); | |
50 | $queryObj = new CRM_Contact_BAO_Query($params); | |
2fe91f9d | 51 | $this->assertEquals( |
7f175707 | 52 | "civicrm_value_testsearchcus_1.date_field_2 BETWEEN '" . date('Y') . "0101000000' AND '" . date('Y') . "1231235959'", |
2fe91f9d | 53 | $queryObj->_where[0][0] |
54 | ); | |
cc3f334a | 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); | |
acd6c6ab | 57 | $this->assertEquals([ |
58 | 'id' => $dateCustomField['id'], | |
59 | 'label' => 'date field', | |
1f61a7b1 | 60 | 'extends' => 'Contact', |
acd6c6ab | 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, | |
1f61a7b1 | 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, | |
74 | 'is_view' => '0', | |
75 | 'is_multiple' => '0', | |
76 | 'date_format' => 'mm/dd/yy', | |
77 | 'time_format' => NULL, | |
78 | 'is_required' => '0', | |
79 | 'extends_table' => 'civicrm_contact', | |
80 | 'search_table' => 'contact_a', | |
cef2e96c | 81 | 'headerPattern' => '//', |
82 | 'title' => 'date field', | |
83 | 'custom_field_id' => $dateCustomField['id'], | |
84 | 'name' => 'custom_' . $dateCustomField['id'], | |
85 | 'type' => 4, | |
86 | 'where' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'] . '.date_field_' . $dateCustomField['id'], | |
cc3f334a | 87 | 'import' => 1, |
1dd8e8ce | 88 | 'serialize' => NULL, |
cc3f334a | 89 | ], $queryObj->getFieldSpec('custom_' . $dateCustomField['id'])); |
acd6c6ab | 90 | |
2fe91f9d | 91 | } |
92 | ||
2206409b | 93 | /** |
94 | * Test filtering by the renamed custom date fields. | |
95 | * | |
96 | * The conversion to date picker will result int these fields | |
97 | * being renamed _high & _low and needing to return correctly. | |
98 | * | |
ca64c337 | 99 | * @throws \API_Exception |
2206409b | 100 | * @throws \CRM_Core_Exception |
ca64c337 | 101 | * @throws \Civi\API\Exception\UnauthorizedException |
2206409b | 102 | */ |
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. | |
109 | $formValues = [ | |
110 | $dateCustomFieldName . '_low' => '2014-06-06', | |
111 | $dateCustomFieldName . '_high' => '2015-06-06', | |
112 | ]; | |
113 | ||
114 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); | |
115 | $queryObject = new CRM_Contact_BAO_Query($params); | |
116 | $queryObject->query(); | |
117 | $this->assertEquals( | |
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]) | |
121 | ); | |
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()])); | |
125 | } | |
126 | ||
56d1630d | 127 | /** |
128 | * Test filtering by the renamed custom date fields. | |
129 | * | |
130 | * The conversion to date picker will result int these fields | |
131 | * being renamed _high & _low and needing to return correctly. | |
132 | * | |
ca64c337 | 133 | * @throws \API_Exception |
56d1630d | 134 | * @throws \CRM_Core_Exception |
ca64c337 | 135 | * @throws \Civi\API\Exception\UnauthorizedException |
56d1630d | 136 | */ |
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. | |
145 | $formValues = [ | |
146 | $dateCustomFieldName . '_low' => '2014-06-06', | |
147 | ]; | |
148 | ||
149 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); | |
150 | $queryObject = new CRM_Contact_BAO_Query($params); | |
151 | $queryObject->query(); | |
152 | $this->assertEquals( | |
153 | 'civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\'', | |
154 | trim($queryObject->_where[0][0]) | |
155 | ); | |
156 | $this->assertEquals( | |
1dd8e8ce CW |
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)) | |
56d1630d | 160 | ); |
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()])); | |
164 | } | |
165 | ||
2fe91f9d | 166 | /** |
167 | * Test filtering by relative custom data dates. | |
0e1544e7 | 168 | * |
169 | * @throws \CRM_Core_Exception | |
2fe91f9d | 170 | */ |
171 | public function testSearchCustomDataDateFromTo() { | |
172 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
9099cab3 | 173 | $dateCustomField = $this->customFieldCreate([ |
2fe91f9d | 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, | |
9099cab3 | 179 | ]); |
2fe91f9d | 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. | |
9099cab3 | 184 | $formValues = [ |
2fe91f9d | 185 | $dateCustomFieldName . '_from' => '2014-06-06', |
186 | $dateCustomFieldName . '_to' => '2015-06-06', | |
9099cab3 | 187 | ]; |
2fe91f9d | 188 | |
cc3f334a | 189 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
190 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 191 | $queryObj->query(); |
2fe91f9d | 192 | $this->assertEquals( |
193 | 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"', | |
194 | $queryObj->_where[0][0] | |
195 | ); | |
196 | $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'"); | |
c65ec456 JP |
197 | |
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); | |
2fe91f9d | 201 | } |
202 | ||
203 | /** | |
204 | * Test filtering by relative custom data. | |
cc3f334a | 205 | * |
206 | * @throws \CRM_Core_Exception | |
2fe91f9d | 207 | */ |
208 | public function testSearchCustomDataFromTo() { | |
209 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
9099cab3 | 210 | $datas = [ |
2fe91f9d | 211 | 'Int' => 2, |
212 | 'Float' => 12.123, | |
213 | 'Money' => 91.21, | |
9099cab3 | 214 | ]; |
2fe91f9d | 215 | foreach ($datas as $type => $data) { |
216 | $customField = $this->customFieldCreate( | |
9099cab3 | 217 | [ |
2fe91f9d | 218 | 'custom_group_id' => $ids['custom_group_id'], |
219 | 'label' => "$type field", | |
220 | 'data_type' => $type, | |
221 | 'html_type' => 'Text', | |
222 | 'default_value' => NULL, | |
9099cab3 | 223 | ] |
2fe91f9d | 224 | ); |
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. | |
229 | $from = $data - 1; | |
230 | $to = $data; | |
9099cab3 | 231 | $formValues = [ |
2fe91f9d | 232 | $customFieldName . '_from' => $from, |
233 | $customFieldName . '_to' => $to, | |
9099cab3 | 234 | ]; |
2fe91f9d | 235 | |
cc3f334a | 236 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
237 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 238 | $queryObj->query(); |
2fe91f9d | 239 | $this->assertEquals( |
cc3f334a | 240 | 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"", |
2fe91f9d | 241 | $queryObj->_where[0][0] |
242 | ); | |
243 | $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to"); | |
244 | } | |
245 | } | |
246 | ||
247 | /** | |
248 | * Test filtering by relative custom data. | |
cc3f334a | 249 | * |
250 | * @throws \CRM_Core_Exception | |
2fe91f9d | 251 | */ |
252 | public function testSearchCustomDataFromAndTo() { | |
253 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
b23b546c | 254 | $dataSet = [ |
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], | |
9099cab3 | 260 | ]; |
b23b546c | 261 | foreach ($dataSet as $type => $values) { |
262 | $data = $values['value']; | |
2fe91f9d | 263 | $isDate = ($type === 'Date'); |
264 | $customField = $this->customFieldCreate( | |
9099cab3 | 265 | [ |
2fe91f9d | 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, | |
9099cab3 | 271 | ] |
2fe91f9d | 272 | ); |
273 | $customFieldName = 'custom_' . $customField['id']; | |
274 | ||
b23b546c | 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); | |
2fe91f9d | 279 | |
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. | |
283 | ||
b23b546c | 284 | //Scenario 2 : TO date filter |
9099cab3 | 285 | $formValues = [ |
2fe91f9d | 286 | $customFieldName . '_to' => $data, |
9099cab3 | 287 | ]; |
2fe91f9d | 288 | |
cc3f334a | 289 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
290 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 291 | $queryObj->query(); |
2fe91f9d | 292 | |
293 | $this->assertEquals( | |
b23b546c | 294 | 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue", |
2fe91f9d | 295 | $queryObj->_where[0][0] |
296 | ); | |
297 | $this->assertEquals($queryObj->_qill[0][0], | |
b23b546c | 298 | "$type field $toQillValue" |
2fe91f9d | 299 | ); |
300 | ||
b23b546c | 301 | //Scenario 2 : FROM date filter |
9099cab3 | 302 | $formValues = [ |
b23b546c | 303 | $customFieldName . '_from' => $values['value'], |
9099cab3 | 304 | ]; |
2fe91f9d | 305 | |
cc3f334a | 306 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
307 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 308 | $queryObj->query(); |
2fe91f9d | 309 | |
310 | $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue; | |
2fe91f9d | 311 | $this->assertEquals( |
b23b546c | 312 | 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue", |
2fe91f9d | 313 | $queryObj->_where[0][0] |
314 | ); | |
b23b546c | 315 | $this->assertEquals( |
316 | "$type field $fromQillValue", | |
317 | $queryObj->_qill[0][0] | |
2fe91f9d | 318 | ); |
319 | } | |
320 | } | |
321 | ||
322 | /** | |
323 | * Test filtering by relative custom data dates. | |
0e1544e7 | 324 | * |
325 | * @throws \CRM_Core_Exception | |
2fe91f9d | 326 | */ |
327 | public function testSearchCustomDataDateEquals() { | |
328 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
9099cab3 | 329 | $dateCustomField = $this->customFieldCreate([ |
2fe91f9d | 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, | |
9099cab3 | 335 | ]); |
2fe91f9d | 336 | $dateCustomFieldName = 'custom_' . $dateCustomField['id']; |
ca64c337 | 337 | $this->individualCreate([$dateCustomFieldName => '2015-01-01']); |
2fe91f9d | 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. | |
9099cab3 | 341 | $formValues = [$dateCustomFieldName => '2015-06-06']; |
cc3f334a | 342 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
343 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 344 | $queryObj->query(); |
2fe91f9d | 345 | |
346 | $this->assertEquals( | |
347 | "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'", | |
348 | $queryObj->_where[0][0] | |
349 | ); | |
350 | $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'"); | |
351 | } | |
352 | ||
0e1544e7 | 353 | /** |
354 | * Test search builder style query including custom address fields. | |
355 | * | |
ca64c337 | 356 | * @throws \API_Exception |
0e1544e7 | 357 | * @throws \CRM_Core_Exception |
ca64c337 | 358 | * @throws \Civi\API\Exception\UnauthorizedException |
0e1544e7 | 359 | */ |
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, | |
368 | ]); | |
369 | ||
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]]] | |
373 | ); | |
374 | $queryObject->query(); | |
375 | $tableName = $this->getCustomGroupTable(); | |
376 | $fieldName = $this->getCustomFieldColumnName('int'); | |
377 | ||
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]); | |
385 | } | |
386 | ||
2fe91f9d | 387 | } |