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, |
88 | ], $queryObj->getFieldSpec('custom_' . $dateCustomField['id'])); | |
acd6c6ab | 89 | |
2fe91f9d | 90 | } |
91 | ||
92 | /** | |
93 | * Test filtering by relative custom data dates. | |
0e1544e7 | 94 | * |
95 | * @throws \CRM_Core_Exception | |
2fe91f9d | 96 | */ |
97 | public function testSearchCustomDataDateFromTo() { | |
98 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
9099cab3 | 99 | $dateCustomField = $this->customFieldCreate([ |
2fe91f9d | 100 | 'custom_group_id' => $ids['custom_group_id'], |
101 | 'label' => 'date field', | |
102 | 'data_type' => 'Date', | |
103 | 'html_type' => 'Select Date', | |
104 | 'default_value' => NULL, | |
9099cab3 | 105 | ]); |
2fe91f9d | 106 | $dateCustomFieldName = 'custom_' . $dateCustomField['id']; |
107 | // Assigning the relevant form value to be within a custom key is normally done in | |
108 | // build field params. It would be better if it were all done in convertFormValues | |
109 | // but for now we just imitate it. | |
9099cab3 | 110 | $formValues = [ |
2fe91f9d | 111 | $dateCustomFieldName . '_from' => '2014-06-06', |
112 | $dateCustomFieldName . '_to' => '2015-06-06', | |
9099cab3 | 113 | ]; |
2fe91f9d | 114 | |
cc3f334a | 115 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
116 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 117 | $queryObj->query(); |
2fe91f9d | 118 | $this->assertEquals( |
119 | 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"', | |
120 | $queryObj->_where[0][0] | |
121 | ); | |
122 | $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'"); | |
c65ec456 JP |
123 | |
124 | //CRM-17236 - Test custom date is correctly displayed without time. | |
125 | $formattedValue = CRM_Core_BAO_CustomField::displayValue(date('Ymdhms'), $dateCustomField['id']); | |
126 | $this->assertEquals(date('m/d/Y'), $formattedValue); | |
2fe91f9d | 127 | } |
128 | ||
129 | /** | |
130 | * Test filtering by relative custom data. | |
cc3f334a | 131 | * |
132 | * @throws \CRM_Core_Exception | |
2fe91f9d | 133 | */ |
134 | public function testSearchCustomDataFromTo() { | |
135 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
9099cab3 | 136 | $datas = [ |
2fe91f9d | 137 | 'Int' => 2, |
138 | 'Float' => 12.123, | |
139 | 'Money' => 91.21, | |
9099cab3 | 140 | ]; |
2fe91f9d | 141 | foreach ($datas as $type => $data) { |
142 | $customField = $this->customFieldCreate( | |
9099cab3 | 143 | [ |
2fe91f9d | 144 | 'custom_group_id' => $ids['custom_group_id'], |
145 | 'label' => "$type field", | |
146 | 'data_type' => $type, | |
147 | 'html_type' => 'Text', | |
148 | 'default_value' => NULL, | |
9099cab3 | 149 | ] |
2fe91f9d | 150 | ); |
151 | $customFieldName = 'custom_' . $customField['id']; | |
152 | // Assigning the relevant form value to be within a custom key is normally done in | |
153 | // build field params. It would be better if it were all done in convertFormValues | |
154 | // but for now we just imitate it. | |
155 | $from = $data - 1; | |
156 | $to = $data; | |
9099cab3 | 157 | $formValues = [ |
2fe91f9d | 158 | $customFieldName . '_from' => $from, |
159 | $customFieldName . '_to' => $to, | |
9099cab3 | 160 | ]; |
2fe91f9d | 161 | |
cc3f334a | 162 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
163 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 164 | $queryObj->query(); |
2fe91f9d | 165 | $this->assertEquals( |
cc3f334a | 166 | 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"", |
2fe91f9d | 167 | $queryObj->_where[0][0] |
168 | ); | |
169 | $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to"); | |
170 | } | |
171 | } | |
172 | ||
173 | /** | |
174 | * Test filtering by relative custom data. | |
cc3f334a | 175 | * |
176 | * @throws \CRM_Core_Exception | |
2fe91f9d | 177 | */ |
178 | public function testSearchCustomDataFromAndTo() { | |
179 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
b23b546c | 180 | $dataSet = [ |
181 | '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'"], | |
182 | // @todo - investigate the impact of using quotes on what should be an integer field. | |
183 | 'Int' => ['value' => 2, 'sql_string' => '"2"'], | |
184 | 'Float' => ['value' => 12.123, 'sql_string' => '"12.123"'], | |
185 | 'Money' => ['value' => 91.21], | |
9099cab3 | 186 | ]; |
b23b546c | 187 | foreach ($dataSet as $type => $values) { |
188 | $data = $values['value']; | |
2fe91f9d | 189 | $isDate = ($type === 'Date'); |
190 | $customField = $this->customFieldCreate( | |
9099cab3 | 191 | [ |
2fe91f9d | 192 | 'custom_group_id' => $ids['custom_group_id'], |
193 | 'label' => "$type field", | |
194 | 'data_type' => $type, | |
195 | 'html_type' => ($isDate) ? 'Select Date' : 'Text', | |
196 | 'default_value' => NULL, | |
9099cab3 | 197 | ] |
2fe91f9d | 198 | ); |
199 | $customFieldName = 'custom_' . $customField['id']; | |
200 | ||
b23b546c | 201 | $expectedValue = $values['sql_string'] ?? $data; |
202 | $expectedQillValue = $values['qill_string'] ?? $data; | |
203 | $toQillValue = chr(226) . chr(137) . chr(164) . ' ' . $expectedQillValue; | |
204 | $fromQillValue = chr(226) . chr(137) . chr(165) . ' ' . ($values['qill_string_greater'] ?? $expectedQillValue); | |
2fe91f9d | 205 | |
206 | // Assigning the relevant form value to be within a custom key is normally done in | |
207 | // build field params. It would be better if it were all done in convertFormValues | |
208 | // but for now we just imitate it. | |
209 | ||
b23b546c | 210 | //Scenario 2 : TO date filter |
9099cab3 | 211 | $formValues = [ |
2fe91f9d | 212 | $customFieldName . '_to' => $data, |
9099cab3 | 213 | ]; |
2fe91f9d | 214 | |
cc3f334a | 215 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
216 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 217 | $queryObj->query(); |
2fe91f9d | 218 | |
219 | $this->assertEquals( | |
b23b546c | 220 | 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue", |
2fe91f9d | 221 | $queryObj->_where[0][0] |
222 | ); | |
223 | $this->assertEquals($queryObj->_qill[0][0], | |
b23b546c | 224 | "$type field $toQillValue" |
2fe91f9d | 225 | ); |
226 | ||
b23b546c | 227 | //Scenario 2 : FROM date filter |
9099cab3 | 228 | $formValues = [ |
b23b546c | 229 | $customFieldName . '_from' => $values['value'], |
9099cab3 | 230 | ]; |
2fe91f9d | 231 | |
cc3f334a | 232 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
233 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 234 | $queryObj->query(); |
2fe91f9d | 235 | |
236 | $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue; | |
2fe91f9d | 237 | $this->assertEquals( |
b23b546c | 238 | 'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue", |
2fe91f9d | 239 | $queryObj->_where[0][0] |
240 | ); | |
b23b546c | 241 | $this->assertEquals( |
242 | "$type field $fromQillValue", | |
243 | $queryObj->_qill[0][0] | |
2fe91f9d | 244 | ); |
245 | } | |
246 | } | |
247 | ||
248 | /** | |
249 | * Test filtering by relative custom data dates. | |
0e1544e7 | 250 | * |
251 | * @throws \CRM_Core_Exception | |
2fe91f9d | 252 | */ |
253 | public function testSearchCustomDataDateEquals() { | |
254 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); | |
9099cab3 | 255 | $dateCustomField = $this->customFieldCreate([ |
2fe91f9d | 256 | 'custom_group_id' => $ids['custom_group_id'], |
257 | 'label' => 'date field', | |
258 | 'data_type' => 'Date', | |
259 | 'html_type' => 'Select Date', | |
260 | 'default_value' => NULL, | |
9099cab3 | 261 | ]); |
2fe91f9d | 262 | $dateCustomFieldName = 'custom_' . $dateCustomField['id']; |
9099cab3 | 263 | $this->individualCreate([$dateCustomFieldName => "2015-01-01"]); |
2fe91f9d | 264 | // Assigning the relevant form value to be within a custom key is normally done in |
265 | // build field params. It would be better if it were all done in convertFormValues | |
266 | // but for now we just imitate it. | |
9099cab3 | 267 | $formValues = [$dateCustomFieldName => '2015-06-06']; |
cc3f334a | 268 | $params = CRM_Contact_BAO_Query::convertFormValues($formValues); |
269 | $queryObj = new CRM_Contact_BAO_Query($params); | |
0e1544e7 | 270 | $queryObj->query(); |
2fe91f9d | 271 | |
272 | $this->assertEquals( | |
273 | "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'", | |
274 | $queryObj->_where[0][0] | |
275 | ); | |
276 | $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'"); | |
277 | } | |
278 | ||
0e1544e7 | 279 | /** |
280 | * Test search builder style query including custom address fields. | |
281 | * | |
282 | * @throws \CRM_Core_Exception | |
283 | */ | |
284 | public function testAddressCustomFields() { | |
285 | $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int'); | |
286 | $individualID = $this->individualCreate(); | |
287 | $this->callAPISuccess('Address', 'create', [ | |
288 | 'contact_id' => $individualID, | |
289 | 'street_address' => '10 Downing Street', | |
290 | 'location_type_id' => 'Home', | |
291 | $this->getCustomFieldName('int') => 5, | |
292 | ]); | |
293 | ||
294 | $queryObject = new CRM_Contact_BAO_Query( | |
295 | [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]], | |
296 | ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]] | |
297 | ); | |
298 | $queryObject->query(); | |
299 | $tableName = $this->getCustomGroupTable(); | |
300 | $fieldName = $this->getCustomFieldColumnName('int'); | |
301 | ||
302 | $this->assertEquals([], $queryObject->_where[0]); | |
303 | $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where[1])); | |
304 | $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']); | |
305 | $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'])); | |
306 | $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables[$tableName])); | |
307 | $this->assertEquals([], $queryObject->_qill[0]); | |
308 | $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill[1]); | |
309 | } | |
310 | ||
2fe91f9d | 311 | } |