Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
0eea664b | 2 | |
6a488035 TO |
3 | /** |
4 | * Include dataProvider for tests | |
fb1ee468 | 5 | * |
acb109b7 | 6 | * @group headless |
6a488035 TO |
7 | */ |
8 | class CRM_Contact_BAO_QueryTest extends CiviUnitTestCase { | |
fb1ee468 | 9 | |
dbaa9d7d | 10 | use CRMTraits_Financial_FinancialACLTrait; |
faba82fc | 11 | use CRMTraits_Financial_PriceSetTrait; |
6a488035 | 12 | |
e9479dcf EM |
13 | /** |
14 | * @return CRM_Contact_BAO_QueryTestDataProvider | |
15 | */ | |
6a488035 | 16 | public function dataProvider() { |
acb1052e | 17 | return new CRM_Contact_BAO_QueryTestDataProvider(); |
6a488035 TO |
18 | } |
19 | ||
00be9182 | 20 | public function setUp() { |
6a488035 TO |
21 | parent::setUp(); |
22 | } | |
23 | ||
c69ecb12 | 24 | /** |
25 | * Clean up after test. | |
26 | * | |
27 | * @throws \Exception | |
28 | */ | |
00be9182 | 29 | public function tearDown() { |
faba82fc | 30 | $this->quickCleanUpFinancialEntities(); |
c69ecb12 | 31 | $tablesToTruncate = [ |
6a488035 TO |
32 | 'civicrm_group_contact', |
33 | 'civicrm_group', | |
34 | 'civicrm_saved_search', | |
35 | 'civicrm_entity_tag', | |
36 | 'civicrm_tag', | |
37 | 'civicrm_contact', | |
9b1e4469 | 38 | 'civicrm_address', |
c69ecb12 | 39 | ]; |
6a488035 | 40 | $this->quickCleanup($tablesToTruncate); |
673fae6c | 41 | parent::tearDown(); |
6a488035 TO |
42 | } |
43 | ||
44 | /** | |
3af96592 | 45 | * Test CRM_Contact_BAO_Query::searchQuery(). |
46 | * | |
6c6e6187 | 47 | * @dataProvider dataProvider |
3af96592 | 48 | * |
1e1fdcf6 EM |
49 | * @param $fv |
50 | * @param $count | |
51 | * @param $ids | |
52 | * @param $full | |
6a488035 | 53 | */ |
00be9182 | 54 | public function testSearch($fv, $count, $ids, $full) { |
c69ecb12 | 55 | $this->callAPISuccess('SavedSearch', 'create', ['form_values' => 'a:9:{s:5:"qfKey";s:32:"0123456789abcdef0123456789abcdef";s:13:"includeGroups";a:1:{i:0;s:1:"3";}s:13:"excludeGroups";a:0:{}s:11:"includeTags";a:0:{}s:11:"excludeTags";a:0:{}s:4:"task";s:2:"14";s:8:"radio_ts";s:6:"ts_all";s:14:"customSearchID";s:1:"4";s:17:"customSearchClass";s:36:"CRM_Contact_Form_Search_Custom_Group";}']); |
56 | $this->callAPISuccess('SavedSearch', 'create', ['form_values' => 'a:9:{s:5:"qfKey";s:32:"0123456789abcdef0123456789abcdef";s:13:"includeGroups";a:1:{i:0;s:1:"3";}s:13:"excludeGroups";a:0:{}s:11:"includeTags";a:0:{}s:11:"excludeTags";a:0:{}s:4:"task";s:2:"14";s:8:"radio_ts";s:6:"ts_all";s:14:"customSearchID";s:1:"4";s:17:"customSearchClass";s:36:"CRM_Contact_Form_Search_Custom_Group";}']); | |
57 | ||
58 | $tag7 = $this->ids['Tag'][7] = $this->tagCreate(['name' => 'Test Tag 7', 'description' => 'Test Tag 7'])['id']; | |
59 | $tag9 = $this->ids['Tag'][9] = $this->tagCreate(['name' => 'Test Tag 9', 'description' => 'Test Tag 9'])['id']; | |
60 | $this->tagCreate(['name' => 'Test Tag 10']); | |
61 | $groups = [ | |
62 | 3 => ['name' => 'Test Group 3'], | |
63 | 4 => ['name' => 'Test Smart Group 4', 'saved_search_id' => 1], | |
64 | 5 => ['name' => 'Test Group 5'], | |
65 | 6 => ['name' => 'Test Smart Group 6', 'saved_search_id' => 2], | |
66 | ]; | |
67 | ||
68 | foreach ($groups as $id => $group) { | |
69 | $this->ids['Group'][$id] = $this->groupCreate(array_merge($group, ['title' => $group['name']])); | |
70 | } | |
71 | $individuals = [ | |
72 | ['first_name' => 'Test', 'last_name' => 'Test Contact 9', 'gender_id' => 1, 'prefix_id' => 1, 'suffix_id' => 1], | |
73 | ['first_name' => 'Test', 'last_name' => 'Test Contact 10', 'gender_id' => 2, 'prefix_id' => 2, 'suffix_id' => 2, 'api.entity_tag.create' => ['tag_id' => $tag9]], | |
74 | ['first_name' => 'Test', 'last_name' => 'Test Contact 11', 'gender_id' => 3, 'prefix_id' => 3, 'suffix_id' => 3, 'api.entity_tag.create' => ['tag_id' => $tag7]], | |
fb1ee468 | 75 | [ |
76 | 'first_name' => 'Test', | |
77 | 'last_name' => 'Test Contact 12', | |
78 | 'gender_id' => 3, | |
79 | 'prefix_id' => 4, | |
80 | 'suffix_id' => 4, | |
81 | 'api.entity_tag.create' => ['tag_id' => $tag9], | |
82 | 'api.entity_tag.create.2' => ['tag_id' => $tag7], | |
83 | ], | |
c69ecb12 | 84 | ['first_name' => 'Test', 'last_name' => 'Test Contact 13', 'gender_id' => 2, 'prefix_id' => 2, 'suffix_id' => 2], |
85 | ['first_name' => 'Test', 'last_name' => 'Test Contact 14', 'gender_id' => 3, 'prefix_id' => 4, 'suffix_id' => 4, 'api.entity_tag.create' => ['tag_id' => $tag9]], | |
86 | ['first_name' => 'Test', 'last_name' => 'Test Contact 15', 'gender_id' => 3, 'prefix_id' => 4, 'suffix_id' => 5, 'api.entity_tag.create' => ['tag_id' => $tag7]], | |
fb1ee468 | 87 | [ |
88 | 'first_name' => 'Test', | |
89 | 'last_name' => 'Test Contact 16', | |
90 | 'gender_id' => 3, | |
91 | 'prefix_id' => 4, | |
92 | 'suffix_id' => 6, | |
93 | 'api.entity_tag.create' => ['tag_id' => $tag9], | |
94 | 'api.entity_tag.create.2' => ['tag_id' => $tag7], | |
95 | ], | |
c69ecb12 | 96 | ['first_name' => 'Test', 'last_name' => 'Test Contact 17', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 7], |
97 | ['first_name' => 'Test', 'last_name' => 'Test Contact 18', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 4, 'api.entity_tag.create' => ['tag_id' => $tag9]], | |
98 | ['first_name' => 'Test', 'last_name' => 'Test Contact 19', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 6, 'api.entity_tag.create.2' => ['tag_id' => $tag7]], | |
fb1ee468 | 99 | [ |
100 | 'first_name' => 'Test', | |
101 | 'last_name' => 'Test Contact 20', | |
102 | 'gender_id' => 1, | |
103 | 'prefix_id' => 4, | |
104 | 'suffix_id' => 6, | |
105 | 'api.entity_tag.create' => ['tag_id' => $tag9], | |
106 | 'api.entity_tag.create.2' => ['tag_id' => $tag7], | |
107 | ], | |
c69ecb12 | 108 | ['first_name' => 'Test', 'last_name' => 'Test Contact 21', 'gender_id' => 3, 'prefix_id' => 1, 'suffix_id' => 6], |
109 | ['first_name' => 'Test', 'last_name' => 'Test Contact 22', 'gender_id' => 1, 'prefix_id' => 1, 'suffix_id' => 1, 'api.entity_tag.create' => ['tag_id' => $tag9]], | |
110 | ['first_name' => 'Test', 'last_name' => 'Test Contact 23', 'gender_id' => 3, 'prefix_id' => 1, 'suffix_id' => 1, 'api.entity_tag.create' => ['tag_id' => $tag7]], | |
fb1ee468 | 111 | [ |
112 | 'first_name' => 'Test', | |
113 | 'last_name' => 'Test Contact 24', | |
114 | 'gender_id' => 3, | |
115 | 'prefix_id' => 3, | |
116 | 'suffix_id' => 2, | |
117 | 'api.entity_tag.create' => ['tag_id' => $tag9], | |
118 | 'api.entity_tag.create.2' => ['tag_id' => $tag7], | |
119 | ], | |
c69ecb12 | 120 | ]; |
121 | foreach ($individuals as $individual) { | |
122 | $this->ids['Contact'][$individual['last_name']] = $this->individualCreate($individual); | |
123 | } | |
124 | $groupContacts = [ | |
125 | [5 => 13], | |
126 | [5 => 14], | |
127 | [5 => 15], | |
128 | [5 => 16], | |
129 | [5 => 21], | |
130 | [5 => 22], | |
131 | [5 => 23], | |
132 | [5 => 24], | |
133 | [3 => 17], | |
134 | [3 => 18], | |
135 | [3 => 19], | |
136 | [3 => 20], | |
137 | [3 => 21], | |
138 | [3 => 22], | |
139 | [3 => 23], | |
140 | [3 => 24], | |
141 | ]; | |
142 | foreach ($groupContacts as $group) { | |
143 | $groupID = $this->ids['Group'][key($group)]; | |
144 | $contactID = $this->ids['Contact']['Test Contact ' . reset($group)]; | |
145 | $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID, 'status' => 'Added']); | |
146 | } | |
147 | ||
148 | // We have migrated from a hard-coded dataset to a dynamic one but are still working with the same | |
149 | // dataprovider at this stage -> wrangle. | |
150 | foreach ($fv as $key => $value) { | |
151 | $entity = ucfirst($key); | |
152 | if (!array_key_exists($entity, $this->ids)) { | |
153 | continue; | |
154 | } | |
155 | if (is_numeric($value)) { | |
156 | $fv[$key] = $this->ids[$entity][$value]; | |
157 | } | |
158 | elseif (!empty($value[0])) { | |
159 | foreach ($value as $index => $oldGroup) { | |
160 | $fv[$key][$index] = $this->ids[$entity][$oldGroup]; | |
161 | } | |
162 | } | |
163 | else { | |
164 | foreach (array_keys($value) as $index) { | |
165 | unset($fv[$key][$index]); | |
166 | $fv[$key][$this->ids[$entity][$index]] = 1; | |
167 | } | |
168 | } | |
169 | } | |
6a488035 TO |
170 | |
171 | $params = CRM_Contact_BAO_Query::convertFormValues($fv); | |
92915c55 | 172 | $obj = new CRM_Contact_BAO_Query($params); |
b81f44dd | 173 | |
174 | // let's set useGroupBy=true since we are listing contacts here who might belong to | |
175 | // more than one group / tag / notes etc. | |
176 | $obj->_useGroupBy = TRUE; | |
177 | ||
92915c55 | 178 | $dao = $obj->searchQuery(); |
6a488035 | 179 | |
c69ecb12 | 180 | $contacts = []; |
6a488035 TO |
181 | while ($dao->fetch()) { |
182 | $contacts[] = $dao->contact_id; | |
183 | } | |
184 | ||
185 | sort($contacts, SORT_NUMERIC); | |
186 | ||
c69ecb12 | 187 | $expectedIDs = []; |
188 | foreach ($ids as $id) { | |
189 | $expectedIDs[] = $this->ids['Contact']['Test Contact ' . $id]; | |
190 | } | |
191 | $this->assertEquals($expectedIDs, $contacts); | |
6a488035 | 192 | } |
e5fccefb EM |
193 | |
194 | /** | |
eceb18cc | 195 | * Check that we get a successful result querying for home address. |
e5fccefb EM |
196 | * CRM-14263 search builder failure with search profile & address in criteria |
197 | */ | |
00be9182 | 198 | public function testSearchProfileHomeCityCRM14263() { |
e5fccefb EM |
199 | $contactID = $this->individualCreate(); |
200 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
fb1ee468 | 201 | $this->callAPISuccess('address', 'create', [ |
39b959db SL |
202 | 'contact_id' => $contactID, |
203 | 'city' => 'Cool City', | |
204 | 'location_type_id' => 1, | |
fb1ee468 | 205 | ]); |
206 | $params = [ | |
207 | 0 => [ | |
e5fccefb EM |
208 | 0 => 'city-1', |
209 | 1 => '=', | |
210 | 2 => 'Cool City', | |
211 | 3 => 1, | |
212 | 4 => 0, | |
fb1ee468 | 213 | ], |
214 | ]; | |
215 | $returnProperties = [ | |
e5fccefb EM |
216 | 'contact_type' => 1, |
217 | 'contact_sub_type' => 1, | |
218 | 'sort_name' => 1, | |
fb1ee468 | 219 | ]; |
e5fccefb EM |
220 | |
221 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
222 | try { | |
63ae2e7b | 223 | $resultDAO = $queryObj->searchQuery(); |
55eb4e22 | 224 | $this->assertTrue($resultDAO->fetch()); |
e5fccefb | 225 | } |
55eb4e22 EM |
226 | catch (PEAR_Exception $e) { |
227 | $err = $e->getCause(); | |
228 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
e5fccefb | 229 | |
55eb4e22 | 230 | } |
e5fccefb EM |
231 | } |
232 | ||
55eb4e22 | 233 | /** |
eceb18cc | 234 | * Check that we get a successful result querying for home address. |
55eb4e22 EM |
235 | * CRM-14263 search builder failure with search profile & address in criteria |
236 | */ | |
00be9182 | 237 | public function testSearchProfileHomeCityNoResultsCRM14263() { |
55eb4e22 EM |
238 | $contactID = $this->individualCreate(); |
239 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
fb1ee468 | 240 | $this->callAPISuccess('address', 'create', [ |
39b959db SL |
241 | 'contact_id' => $contactID, |
242 | 'city' => 'Cool City', | |
243 | 'location_type_id' => 1, | |
fb1ee468 | 244 | ]); |
245 | $params = [ | |
246 | 0 => [ | |
55eb4e22 EM |
247 | 0 => 'city-1', |
248 | 1 => '=', | |
249 | 2 => 'Dumb City', | |
250 | 3 => 1, | |
251 | 4 => 0, | |
fb1ee468 | 252 | ], |
253 | ]; | |
254 | $returnProperties = [ | |
55eb4e22 EM |
255 | 'contact_type' => 1, |
256 | 'contact_sub_type' => 1, | |
257 | 'sort_name' => 1, | |
fb1ee468 | 258 | ]; |
55eb4e22 EM |
259 | |
260 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
261 | try { | |
63ae2e7b | 262 | $resultDAO = $queryObj->searchQuery(); |
55eb4e22 EM |
263 | $this->assertFalse($resultDAO->fetch()); |
264 | } | |
265 | catch (PEAR_Exception $e) { | |
266 | $err = $e->getCause(); | |
267 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
268 | ||
269 | } | |
270 | } | |
92915c55 | 271 | |
b3e1c09d | 272 | /** |
a3fccfc7 | 273 | * Test searchPrimaryDetailsOnly setting. |
b3e1c09d | 274 | */ |
9349bcf7 | 275 | public function testSearchPrimaryLocTypes() { |
b3e1c09d | 276 | $contactID = $this->individualCreate(); |
fb1ee468 | 277 | $params = [ |
b3e1c09d | 278 | 'contact_id' => $contactID, |
279 | 'email' => 'primary@example.com', | |
280 | 'is_primary' => 1, | |
fb1ee468 | 281 | ]; |
b3e1c09d | 282 | $this->callAPISuccess('email', 'create', $params); |
283 | ||
284 | unset($params['is_primary']); | |
285 | $params['email'] = 'secondary@team.com'; | |
286 | $this->callAPISuccess('email', 'create', $params); | |
287 | ||
fb1ee468 | 288 | foreach ([0, 1] as $searchPrimary) { |
a3fccfc7 | 289 | Civi::settings()->set('searchPrimaryDetailsOnly', $searchPrimary); |
b3e1c09d | 290 | |
fb1ee468 | 291 | $params = [ |
292 | 0 => [ | |
b3e1c09d | 293 | 0 => 'email', |
294 | 1 => 'LIKE', | |
c245fd99 | 295 | 2 => 'sEcondary@example.com', |
b3e1c09d | 296 | 3 => 0, |
297 | 4 => 1, | |
fb1ee468 | 298 | ], |
299 | ]; | |
300 | $returnProperties = [ | |
b3e1c09d | 301 | 'contact_type' => 1, |
302 | 'contact_sub_type' => 1, | |
303 | 'sort_name' => 1, | |
fb1ee468 | 304 | ]; |
b3e1c09d | 305 | |
306 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
63ae2e7b | 307 | $resultDAO = $queryObj->searchQuery(); |
b3e1c09d | 308 | |
309 | if ($searchPrimary) { | |
310 | $this->assertEquals($resultDAO->N, 0); | |
311 | } | |
312 | else { | |
313 | //Assert secondary email gets included in search results. | |
314 | while ($resultDAO->fetch()) { | |
315 | $this->assertEquals('secondary@example.com', $resultDAO->email); | |
316 | } | |
317 | } | |
318 | ||
319 | // API should always return primary email. | |
fb1ee468 | 320 | $result = $this->callAPISuccess('Contact', 'get', ['contact_id' => $contactID]); |
b3e1c09d | 321 | $this->assertEquals('primary@example.com', $result['values'][$contactID]['email']); |
322 | } | |
323 | } | |
324 | ||
db1a73f5 KE |
325 | /** |
326 | * Test created to prove failure of search on state when location | |
327 | * display name is different form location name (issue 607) | |
328 | */ | |
329 | public function testSearchOtherLocationUpperLower() { | |
330 | ||
331 | $params = [ | |
332 | 0 => [ | |
333 | 0 => 'state_province-4', | |
334 | 1 => 'IS NOT EMPTY', | |
335 | 2 => '', | |
336 | 3 => 1, | |
337 | 4 => 0, | |
338 | ], | |
339 | ]; | |
340 | $returnProperties = [ | |
341 | 'contact_type' => 1, | |
342 | 'contact_sub_type' => 1, | |
343 | 'sort_name' => 1, | |
344 | 'location' => [ | |
345 | 'other' => [ | |
346 | 'location_type' => 4, | |
347 | 'state_province' => 1, | |
348 | ], | |
349 | ], | |
350 | ]; | |
351 | ||
6f1ae9ee KE |
352 | // update with the api does not work because it updates both the name and the |
353 | // the display_name. Plain SQL however does the job | |
db1a73f5 KE |
354 | CRM_Core_DAO::executeQuery('update civicrm_location_type set name=%2 where id=%1', |
355 | [ | |
356 | 1 => [4, 'Integer'], | |
357 | 2 => ['other', 'String'], | |
358 | ]); | |
359 | ||
360 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
361 | ||
63ae2e7b | 362 | $resultDAO = $queryObj->searchQuery(); |
db1a73f5 KE |
363 | $resultDAO->fetch(); |
364 | } | |
365 | ||
6c6e6187 | 366 | /** |
fea8ae41 | 367 | * CRM-14263 search builder failure with search profile & address in criteria. |
368 | * | |
6c6e6187 TO |
369 | * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has |
370 | * so few tests detecting any change seems good here :-) | |
fea8ae41 | 371 | * |
372 | * @dataProvider getSearchProfileData | |
373 | * | |
374 | * @param array $params | |
39b959db SL |
375 | * @param string $selectClause |
376 | * @param string $whereClause | |
fb1ee468 | 377 | * |
378 | * @throws \CRM_Core_Exception | |
6c6e6187 | 379 | */ |
fea8ae41 | 380 | public function testSearchProfilePrimaryCityCRM14263($params, $selectClause, $whereClause) { |
6c6e6187 TO |
381 | $contactID = $this->individualCreate(); |
382 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
fb1ee468 | 383 | $this->callAPISuccess('address', 'create', [ |
39b959db SL |
384 | 'contact_id' => $contactID, |
385 | 'city' => 'Cool CITY', | |
386 | 'street_address' => 'Long STREET', | |
387 | 'location_type_id' => 1, | |
fb1ee468 | 388 | ]); |
389 | $returnProperties = [ | |
92915c55 TO |
390 | 'contact_type' => 1, |
391 | 'contact_sub_type' => 1, | |
392 | 'sort_name' => 1, | |
fb1ee468 | 393 | ]; |
fea8ae41 | 394 | $expectedSQL = "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.sort_name as `sort_name`, civicrm_address.id as address_id, " . $selectClause . " FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE ( ( " . $whereClause . " ) ) AND (contact_a.is_deleted = 0) ORDER BY `contact_a`.`sort_name` ASC, `contact_a`.`id` "; |
6c6e6187 TO |
395 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); |
396 | try { | |
299c1530 | 397 | $this->assertLike($expectedSQL, $queryObj->getSearchSQL()); |
fea8ae41 | 398 | list($select, $from, $where, $having) = $queryObj->query(); |
399 | $dao = CRM_Core_DAO::executeQuery("$select $from $where $having"); | |
400 | $dao->fetch(); | |
401 | $this->assertEquals('Anderson, Anthony', $dao->sort_name); | |
6c6e6187 TO |
402 | } |
403 | catch (PEAR_Exception $e) { | |
404 | $err = $e->getCause(); | |
405 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
55eb4e22 | 406 | |
55eb4e22 | 407 | } |
6c6e6187 | 408 | } |
96025800 | 409 | |
fea8ae41 | 410 | /** |
411 | * Get data sets to test for search. | |
412 | */ | |
413 | public function getSearchProfileData() { | |
414 | return [ | |
415 | [ | |
fb1ee468 | 416 | [['city', '=', 'Cool City', 1, 0]], |
417 | "civicrm_address.city as `city`", | |
418 | "civicrm_address.city = 'Cool City'", | |
fea8ae41 | 419 | ], |
420 | [ | |
421 | // Note that in the query 'long street' is lower cased. We eventually want to change that & not mess with the vars - it turns out | |
422 | // it doesn't work on some charsets. However, the the lcasing affects more vars & we are looking to stagger removal of lcasing 'in case' | |
423 | // (although we have been removing without blowback since 2017) | |
fb1ee468 | 424 | [['street_address', '=', 'Long Street', 1, 0]], |
425 | "civicrm_address.street_address as `street_address`", | |
426 | "civicrm_address.street_address LIKE '%Long Street%'", | |
fea8ae41 | 427 | ], |
428 | ]; | |
429 | } | |
430 | ||
82ae55f4 | 431 | /** |
432 | * Test set up to test calling the query object per GroupContactCache BAO usage. | |
433 | * | |
434 | * CRM-17254 ensure that if only the contact_id is required other fields should | |
435 | * not be appended. | |
436 | */ | |
437 | public function testGroupContactCacheAddSearch() { | |
fb1ee468 | 438 | $returnProperties = ['contact_id']; |
439 | $params = [['group', 'IN', [1], 0, 0]]; | |
82ae55f4 | 440 | |
441 | $query = new CRM_Contact_BAO_Query( | |
442 | $params, $returnProperties, | |
443 | NULL, TRUE, FALSE, 1, | |
444 | TRUE, | |
445 | TRUE, FALSE | |
446 | ); | |
447 | ||
448 | list($select) = $query->query(FALSE); | |
449 | $this->assertEquals('SELECT contact_a.id as contact_id', $select); | |
450 | } | |
451 | ||
9b1e4469 | 452 | /** |
453 | * Test smart groups with non-numeric don't fail on range queries. | |
454 | * | |
455 | * CRM-14720 | |
456 | */ | |
457 | public function testNumericPostal() { | |
91c164ed | 458 | // Precaution as hitting some inconsistent set up running in isolation vs in the suite. |
459 | CRM_Core_DAO::executeQuery('UPDATE civicrm_address SET postal_code = NULL'); | |
460 | ||
fb1ee468 | 461 | $this->individualCreate(['api.address.create' => ['postal_code' => 5, 'location_type_id' => 'Main']]); |
462 | $this->individualCreate(['api.address.create' => ['postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main']]); | |
463 | $this->individualCreate(['api.address.create' => ['postal_code' => '4', 'location_type_id' => 'Main']]); | |
464 | $this->individualCreate(['api.address.create' => ['postal_code' => '6', 'location_type_id' => 'Main']]); | |
465 | $this->individualCreate(['api.address.create' => ['street_address' => 'just a street', 'location_type_id' => 'Main']]); | |
466 | $this->individualCreate(['api.address.create' => ['postal_code' => '12345678444455555555555555555555555555555555551314151617181920', 'location_type_id' => 'Main']]); | |
9b1e4469 | 467 | |
fb1ee468 | 468 | $params = [['postal_code_low', '=', 5, 0, 0]]; |
9b1e4469 | 469 | CRM_Contact_BAO_Query::convertFormValues($params); |
470 | ||
471 | $query = new CRM_Contact_BAO_Query( | |
fb1ee468 | 472 | $params, ['contact_id'], |
9b1e4469 | 473 | NULL, TRUE, FALSE, 1, |
474 | TRUE, | |
475 | TRUE, FALSE | |
476 | ); | |
477 | ||
478 | $sql = $query->query(FALSE); | |
479 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); | |
480 | $this->assertEquals(2, $result->N); | |
481 | ||
482 | // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this | |
483 | // results in mysql warnings & hence fatal errors. | |
484 | /// I was unable to get mysql warnings to activate in the context of the unit tests - but | |
485 | // felt this code still provided a useful bit of coverage as it runs the various queries to load | |
486 | // the group & could generate invalid sql if a bug were introduced. | |
fb1ee468 | 487 | $groupParams = ['title' => 'postal codes', 'formValues' => $params, 'is_active' => 1]; |
9b1e4469 | 488 | $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams); |
489 | CRM_Contact_BAO_GroupContactCache::load($group, TRUE); | |
490 | } | |
491 | ||
30415e03 | 492 | /** |
493 | * Test searches are case insensitive. | |
494 | */ | |
495 | public function testCaseInsensitive() { | |
fb1ee468 | 496 | $orgID = $this->organizationCreate(['organization_name' => 'BOb']); |
497 | $params = [ | |
1809f3cf | 498 | 'display_name' => 'Minnie Mouse', |
499 | 'first_name' => 'Minnie', | |
500 | 'last_name' => 'Mouse', | |
501 | 'employer_id' => $orgID, | |
502 | 'contact_type' => 'Individual', | |
503 | 'nick_name' => 'Mins', | |
fb1ee468 | 504 | ]; |
1809f3cf | 505 | $this->callAPISuccess('Contact', 'create', $params); |
506 | unset($params['contact_type']); | |
507 | foreach ($params as $key => $value) { | |
508 | if ($key == 'employer_id') { | |
fb1ee468 | 509 | $searchParams = [['current_employer', '=', 'bob', 0, 1]]; |
1809f3cf | 510 | } |
511 | else { | |
fb1ee468 | 512 | $searchParams = [[$key, '=', strtolower($value), 0, 1]]; |
1809f3cf | 513 | } |
514 | $query = new CRM_Contact_BAO_Query($searchParams); | |
515 | $result = $query->apiQuery($searchParams); | |
516 | $this->assertEquals(1, count($result[0]), 'search for ' . $key); | |
517 | $contact = reset($result[0]); | |
518 | $this->assertEquals('Minnie Mouse', $contact['display_name']); | |
519 | $this->assertEquals('BOb', $contact['current_employer']); | |
520 | } | |
30415e03 | 521 | } |
522 | ||
9a1491bb | 523 | /** |
524 | * Test smart groups with non-numeric don't fail on equal queries. | |
525 | * | |
526 | * CRM-14720 | |
527 | */ | |
528 | public function testNonNumericEqualsPostal() { | |
fb1ee468 | 529 | $this->individualCreate(['api.address.create' => ['postal_code' => 5, 'location_type_id' => 'Main']]); |
530 | $this->individualCreate(['api.address.create' => ['postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main']]); | |
531 | $this->individualCreate(['api.address.create' => ['postal_code' => '4', 'location_type_id' => 'Main']]); | |
532 | $this->individualCreate(['api.address.create' => ['postal_code' => '6', 'location_type_id' => 'Main']]); | |
9a1491bb | 533 | |
fb1ee468 | 534 | $params = [['postal_code', '=', 'EH10 4RB-889', 0, 0]]; |
9a1491bb | 535 | CRM_Contact_BAO_Query::convertFormValues($params); |
536 | ||
537 | $query = new CRM_Contact_BAO_Query( | |
fb1ee468 | 538 | $params, ['contact_id'], |
9a1491bb | 539 | NULL, TRUE, FALSE, 1, |
540 | TRUE, | |
541 | TRUE, FALSE | |
542 | ); | |
543 | ||
544 | $sql = $query->query(FALSE); | |
2fc64082 | 545 | $this->assertEquals("WHERE ( civicrm_address.postal_code = 'EH10 4RB-889' ) AND (contact_a.is_deleted = 0)", $sql[2]); |
9a1491bb | 546 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); |
547 | $this->assertEquals(1, $result->N); | |
548 | ||
549 | } | |
550 | ||
72a2eeab AS |
551 | public function testNonReciprocalRelationshipTargetGroupIsCorrectResults() { |
552 | $contactID_a = $this->individualCreate(); | |
553 | $contactID_b = $this->individualCreate(); | |
fb1ee468 | 554 | $this->callAPISuccess('Relationship', 'create', [ |
72a2eeab AS |
555 | 'contact_id_a' => $contactID_a, |
556 | 'contact_id_b' => $contactID_b, | |
557 | 'relationship_type_id' => 1, | |
558 | 'is_active' => 1, | |
fb1ee468 | 559 | ]); |
72a2eeab AS |
560 | // Create a group and add contact A to it. |
561 | $groupID = $this->groupCreate(); | |
fb1ee468 | 562 | $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added']); |
72a2eeab AS |
563 | |
564 | // Add another (sans-relationship) contact to the group, | |
565 | $contactID_c = $this->individualCreate(); | |
fb1ee468 | 566 | $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added']); |
72a2eeab | 567 | |
fb1ee468 | 568 | $params = [ |
569 | [ | |
72a2eeab AS |
570 | 0 => 'relation_type_id', |
571 | 1 => 'IN', | |
572 | 2 => | |
fb1ee468 | 573 | [ |
574 | 0 => '1_b_a', | |
575 | ], | |
72a2eeab AS |
576 | 3 => 0, |
577 | 4 => 0, | |
fb1ee468 | 578 | ], |
579 | [ | |
72a2eeab AS |
580 | 0 => 'relation_target_group', |
581 | 1 => 'IN', | |
582 | 2 => | |
fb1ee468 | 583 | [ |
584 | 0 => $groupID, | |
585 | ], | |
72a2eeab AS |
586 | 3 => 0, |
587 | 4 => 0, | |
fb1ee468 | 588 | ], |
589 | ]; | |
72a2eeab AS |
590 | |
591 | $query = new CRM_Contact_BAO_Query($params); | |
592 | $dao = $query->searchQuery(); | |
593 | $this->assertEquals('1', $dao->N, "Search query returns exactly 1 result?"); | |
594 | $this->assertTrue($dao->fetch(), "Search query returns success?"); | |
595 | $this->assertEquals($contactID_b, $dao->contact_id, "Search query returns parent of contact A?"); | |
596 | } | |
597 | ||
fb1ee468 | 598 | /** |
599 | * @throws \CRM_Core_Exception | |
600 | */ | |
72a2eeab AS |
601 | public function testReciprocalRelationshipTargetGroupIsCorrectResults() { |
602 | $contactID_a = $this->individualCreate(); | |
603 | $contactID_b = $this->individualCreate(); | |
fb1ee468 | 604 | $this->callAPISuccess('Relationship', 'create', [ |
72a2eeab AS |
605 | 'contact_id_a' => $contactID_a, |
606 | 'contact_id_b' => $contactID_b, | |
607 | 'relationship_type_id' => 2, | |
608 | 'is_active' => 1, | |
fb1ee468 | 609 | ]); |
72a2eeab AS |
610 | // Create a group and add contact A to it. |
611 | $groupID = $this->groupCreate(); | |
fb1ee468 | 612 | $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added']); |
72a2eeab AS |
613 | |
614 | // Add another (sans-relationship) contact to the group, | |
615 | $contactID_c = $this->individualCreate(); | |
fb1ee468 | 616 | $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added']); |
72a2eeab | 617 | |
fb1ee468 | 618 | $params = [ |
619 | [ | |
72a2eeab AS |
620 | 0 => 'relation_type_id', |
621 | 1 => 'IN', | |
622 | 2 => | |
fb1ee468 | 623 | [ |
624 | 0 => '2_a_b', | |
625 | ], | |
72a2eeab AS |
626 | 3 => 0, |
627 | 4 => 0, | |
fb1ee468 | 628 | ], |
629 | [ | |
72a2eeab AS |
630 | 0 => 'relation_target_group', |
631 | 1 => 'IN', | |
632 | 2 => | |
fb1ee468 | 633 | [ |
634 | 0 => $groupID, | |
635 | ], | |
72a2eeab AS |
636 | 3 => 0, |
637 | 4 => 0, | |
fb1ee468 | 638 | ], |
639 | ]; | |
72a2eeab AS |
640 | |
641 | $query = new CRM_Contact_BAO_Query($params); | |
642 | $dao = $query->searchQuery(); | |
643 | $this->assertEquals('1', $dao->N, "Search query returns exactly 1 result?"); | |
644 | $this->assertTrue($dao->fetch(), "Search query returns success?"); | |
645 | $this->assertEquals($contactID_b, $dao->contact_id, "Search query returns spouse of contact A?"); | |
646 | } | |
647 | ||
648 | public function testReciprocalRelationshipTargetGroupUsesTempTable() { | |
649 | $groupID = $this->groupCreate(); | |
fb1ee468 | 650 | $params = [ |
651 | [ | |
72a2eeab AS |
652 | 0 => 'relation_type_id', |
653 | 1 => 'IN', | |
654 | 2 => | |
fb1ee468 | 655 | [ |
656 | 0 => '2_a_b', | |
657 | ], | |
72a2eeab AS |
658 | 3 => 0, |
659 | 4 => 0, | |
fb1ee468 | 660 | ], |
661 | [ | |
72a2eeab AS |
662 | 0 => 'relation_target_group', |
663 | 1 => 'IN', | |
664 | 2 => | |
fb1ee468 | 665 | [ |
666 | 0 => $groupID, | |
667 | ], | |
72a2eeab AS |
668 | 3 => 0, |
669 | 4 => 0, | |
fb1ee468 | 670 | ], |
671 | ]; | |
72a2eeab AS |
672 | $sql = CRM_Contact_BAO_Query::getQuery($params); |
673 | $this->assertContains('INNER JOIN civicrm_rel_temp_', $sql, "Query appears to use temporary table of compiled relationships?", TRUE); | |
674 | } | |
675 | ||
9c9908e9 | 676 | public function testRelationshipPermissionClause() { |
677 | $params = [['relation_type_id', 'IN', ['1_b_a'], 0, 0], ['relation_permission', 'IN', [2], 0, 0]]; | |
678 | $sql = CRM_Contact_BAO_Query::getQuery($params); | |
679 | $this->assertContains('(civicrm_relationship.is_permission_a_b IN (2))', $sql); | |
680 | } | |
681 | ||
5ad36be5 SL |
682 | /** |
683 | * Test Relationship Clause | |
684 | */ | |
685 | public function testRelationshipClause() { | |
686 | $today = date('Ymd'); | |
5de9b484 JP |
687 | $from1 = " FROM civicrm_contact contact_a LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id ) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )"; |
688 | $from2 = " FROM civicrm_contact contact_a LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id ) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id )"; | |
5ad36be5 SL |
689 | $where1 = "WHERE ( ( |
690 | civicrm_relationship.is_active = 1 AND | |
691 | ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND | |
692 | ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} ) | |
693 | ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8) ) AND (contact_a.is_deleted = 0)"; | |
694 | $where2 = "WHERE ( ( | |
695 | civicrm_relationship.is_active = 1 AND | |
696 | ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND | |
697 | ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} ) | |
698 | ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8,10) ) AND (contact_a.is_deleted = 0)"; | |
699 | // Test Traditional single select format | |
fb1ee468 | 700 | $params1 = [['relation_type_id', '=', '8_a_b', 0, 0]]; |
5ad36be5 | 701 | $query1 = new CRM_Contact_BAO_Query( |
fb1ee468 | 702 | $params1, ['contact_id'], |
5ad36be5 SL |
703 | NULL, TRUE, FALSE, 1, |
704 | TRUE, | |
705 | TRUE, FALSE | |
706 | ); | |
707 | $sql1 = $query1->query(FALSE); | |
299c1530 | 708 | $this->assertLike($from1, $sql1[1]); |
709 | $this->assertLike($where1, $sql1[2]); | |
5ad36be5 | 710 | // Test single relationship type selected in multiple select. |
fb1ee468 | 711 | $params2 = [['relation_type_id', 'IN', ['8_a_b'], 0, 0]]; |
5ad36be5 | 712 | $query2 = new CRM_Contact_BAO_Query( |
fb1ee468 | 713 | $params2, ['contact_id'], |
5ad36be5 SL |
714 | NULL, TRUE, FALSE, 1, |
715 | TRUE, | |
716 | TRUE, FALSE | |
717 | ); | |
718 | $sql2 = $query2->query(FALSE); | |
299c1530 | 719 | $this->assertLike($from1, $sql2[1]); |
720 | $this->assertLike($where1, $sql2[2]); | |
5ad36be5 | 721 | // Test multiple relationship types selected. |
fb1ee468 | 722 | $params3 = [['relation_type_id', 'IN', ['8_a_b', '10_a_b'], 0, 0]]; |
5ad36be5 | 723 | $query3 = new CRM_Contact_BAO_Query( |
fb1ee468 | 724 | $params3, ['contact_id'], |
5ad36be5 SL |
725 | NULL, TRUE, FALSE, 1, |
726 | TRUE, | |
727 | TRUE, FALSE | |
728 | ); | |
729 | $sql3 = $query3->query(FALSE); | |
299c1530 | 730 | $this->assertLike($from1, $sql3[1]); |
731 | $this->assertLike($where2, $sql3[2]); | |
5ad36be5 | 732 | // Test Multiple Relationship type selected where one doesn't actually exist. |
fb1ee468 | 733 | $params4 = [['relation_type_id', 'IN', ['8_a_b', '10_a_b', '14_a_b'], 0, 0]]; |
5ad36be5 | 734 | $query4 = new CRM_Contact_BAO_Query( |
fb1ee468 | 735 | $params4, ['contact_id'], |
5ad36be5 SL |
736 | NULL, TRUE, FALSE, 1, |
737 | TRUE, | |
738 | TRUE, FALSE | |
739 | ); | |
740 | $sql4 = $query4->query(FALSE); | |
299c1530 | 741 | $this->assertLike($from1, $sql4[1]); |
742 | $this->assertLike($where2, $sql4[2]); | |
5de9b484 JP |
743 | |
744 | // Test Multiple b to a Relationship type . | |
fb1ee468 | 745 | $params5 = [['relation_type_id', 'IN', ['8_b_a', '10_b_a', '14_b_a'], 0, 0]]; |
5de9b484 | 746 | $query5 = new CRM_Contact_BAO_Query( |
fb1ee468 | 747 | $params5, ['contact_id'], |
5de9b484 JP |
748 | NULL, TRUE, FALSE, 1, |
749 | TRUE, | |
750 | TRUE, FALSE | |
751 | ); | |
752 | $sql5 = $query5->query(FALSE); | |
299c1530 | 753 | $this->assertLike($from2, $sql5[1]); |
754 | $this->assertLike($where2, $sql5[2]); | |
5ad36be5 | 755 | } |
8d710ea5 | 756 | |
757 | /** | |
758 | * Test we can narrow a group get by status. | |
fb1ee468 | 759 | * |
760 | * @throws \CRM_Core_Exception | |
8d710ea5 | 761 | */ |
762 | public function testGetByGroupWithStatus() { | |
763 | $groupID = $this->groupCreate(); | |
d7570a7f | 764 | $this->groupContactCreate($groupID, 3); |
8d710ea5 | 765 | $groupContactID = $this->callAPISuccessGetSingle('GroupContact', ['group_id' => $groupID, 'options' => ['limit' => 1]])['id']; |
766 | $this->callAPISuccess('GroupContact', 'create', ['id' => $groupContactID, 'status' => 'Removed']); | |
767 | $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]); | |
768 | $resultDAO = $queryObj->searchQuery(); | |
769 | $this->assertEquals(1, $resultDAO->N); | |
770 | ||
771 | $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Added' => 1], 0, 0]]); | |
772 | $resultDAO = $queryObj->searchQuery(); | |
773 | $this->assertEquals(2, $resultDAO->N); | |
774 | ||
775 | $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0]]); | |
776 | $resultDAO = $queryObj->searchQuery(); | |
777 | $this->assertEquals(2, $resultDAO->N); | |
778 | } | |
5ad36be5 | 779 | |
0d38cb6d | 780 | /** |
781 | * Test we can narrow a group get by status. | |
782 | * | |
783 | * @throws \Exception | |
784 | */ | |
785 | public function testGetByGroupWithStatusSmartGroup() { | |
786 | $groupID = $this->smartGroupCreate(); | |
787 | // This means they are actually all hard-added, which is fine for this purpose. | |
788 | $this->groupContactCreate($groupID, 3); | |
789 | $groupContactID = $this->callAPISuccessGetSingle('GroupContact', ['group_id' => $groupID, 'options' => ['limit' => 1]])['id']; | |
790 | $this->callAPISuccess('GroupContact', 'create', ['id' => $groupContactID, 'status' => 'Removed']); | |
791 | ||
792 | $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]); | |
793 | $resultDAO = $queryObj->searchQuery(); | |
794 | $this->assertEquals(1, $resultDAO->N); | |
795 | } | |
796 | ||
c3137c08 | 797 | /** |
798 | * Test the group contact clause does not contain an OR. | |
799 | * | |
800 | * The search should return 3 contacts - 2 households in the smart group of | |
801 | * Contact Type = Household and one Individual hard-added to it. The | |
802 | * Household that meets both criteria should be returned once. | |
0d38cb6d | 803 | * |
804 | * @throws \Exception | |
c3137c08 | 805 | */ |
806 | public function testGroupClause() { | |
807 | $this->householdCreate(); | |
808 | $householdID = $this->householdCreate(); | |
809 | $individualID = $this->individualCreate(); | |
810 | $groupID = $this->smartGroupCreate(); | |
fb1ee468 | 811 | $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added']); |
812 | $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added']); | |
c3137c08 | 813 | |
485a3a1f | 814 | // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache. |
0626851e | 815 | CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID); |
3875e6b6 | 816 | |
817 | $sql = CRM_Contact_BAO_Query::getQuery( | |
fb1ee468 | 818 | [['group', 'IN', [$groupID], 0, 0]], |
819 | ['contact_id'] | |
c3137c08 | 820 | ); |
821 | ||
3875e6b6 | 822 | $dao = CRM_Core_DAO::executeQuery($sql); |
c3137c08 | 823 | $this->assertEquals(3, $dao->N); |
3875e6b6 | 824 | $this->assertFalse(strstr($sql, ' OR ')); |
825 | ||
826 | $sql = CRM_Contact_BAO_Query::getQuery( | |
fb1ee468 | 827 | [['group', 'IN', [$groupID], 0, 0]], |
828 | ['contact_id' => 1, 'group' => 1] | |
3875e6b6 | 829 | ); |
830 | ||
831 | $dao = CRM_Core_DAO::executeQuery($sql); | |
832 | $this->assertEquals(3, $dao->N); | |
833 | $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or'); | |
834 | while ($dao->fetch()) { | |
835 | $this->assertTrue(($dao->groups == $groupID || $dao->groups == ',' . $groupID), $dao->groups . ' includes ' . $groupID); | |
836 | } | |
c3137c08 | 837 | } |
838 | ||
b1128d0b | 839 | /** |
3af96592 | 840 | * CRM-19562 ensure that only ids are used for contact_id searching. |
b1128d0b SL |
841 | */ |
842 | public function testContactIDClause() { | |
fb1ee468 | 843 | $params = [ |
844 | ["mark_x_2", "=", 1, 0, 0], | |
845 | ["mark_x_foo@example.com", "=", 1, 0, 0], | |
846 | ]; | |
847 | $returnProperties = [ | |
b1128d0b SL |
848 | "sort_name" => 1, |
849 | "email" => 1, | |
850 | "do_not_email" => 1, | |
851 | "is_deceased" => 1, | |
852 | "on_hold" => 1, | |
853 | "display_name" => 1, | |
854 | "preferred_mail_format" => 1, | |
fb1ee468 | 855 | ]; |
5cf7a77e | 856 | $numberOfContacts = 2; |
b1128d0b SL |
857 | $query = new CRM_Contact_BAO_Query($params, $returnProperties); |
858 | try { | |
5cf7a77e | 859 | $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberOfContacts); |
b1128d0b SL |
860 | } |
861 | catch (Exception $e) { | |
5cf7a77e | 862 | $this->assertEquals( |
863 | "A fatal error was triggered: One of parameters (value: foo@example.com) is not of the type Positive", | |
864 | $e->getMessage() | |
865 | ); | |
866 | $this->assertTrue(TRUE); | |
867 | return; | |
b1128d0b | 868 | } |
5cf7a77e | 869 | $this->fail('Test failed for some reason which is not good'); |
b1128d0b SL |
870 | } |
871 | ||
7f594311 | 872 | /** |
873 | * Test the sorting on the contact ID query works. | |
874 | * | |
875 | * Checking for lack of fatal. | |
876 | * | |
877 | * @param string $sortOrder | |
878 | * Param reflecting how sort is passed in. | |
879 | * - 1_d is column 1 descending. | |
880 | * | |
881 | * @dataProvider getSortOptions | |
882 | */ | |
883 | public function testContactIDQuery($sortOrder) { | |
884 | $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all'], NULL, ['sort_name' => 1]); | |
885 | $selector->contactIDQuery([], $sortOrder); | |
886 | } | |
887 | ||
708ce91f | 888 | /** |
889 | * Test the sorting on the contact ID query works with a profile search. | |
890 | * | |
891 | * Checking for lack of fatal. | |
892 | */ | |
893 | public function testContactIDQueryProfileSearchResults() { | |
894 | $profile = $this->callAPISuccess('UFGroup', 'create', ['group_type' => 'Contact', 'name' => 'search', 'title' => 'search']); | |
895 | $this->callAPISuccess('UFField', 'create', [ | |
896 | 'uf_group_id' => $profile['id'], | |
897 | 'field_name' => 'postal_code', | |
898 | 'field_type' => 'Contact', | |
899 | 'in_selector' => TRUE, | |
900 | 'is_searchable' => TRUE, | |
901 | 'label' => 'postal code', | |
902 | 'visibility' => 'Public Pages and Listings', | |
903 | ]); | |
904 | $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all', 'uf_group_id' => $profile['id']], NULL, ['sort_name' => 1]); | |
905 | $selector->contactIDQuery([], '2_d'); | |
906 | } | |
907 | ||
908 | /** | |
909 | * Get search options to reflect how a UI search would look. | |
910 | * | |
911 | * @return array | |
912 | */ | |
7f594311 | 913 | public function getSortOptions() { |
914 | return [ | |
915 | ['1_d'], | |
916 | ['2_d'], | |
917 | ['3_d'], | |
918 | ['4_d'], | |
919 | ['5_d'], | |
920 | ['6_d'], | |
921 | ]; | |
922 | } | |
dbaa9d7d | 923 | |
924 | /** | |
925 | * Test the summary query does not add an acl clause when acls not enabled.. | |
926 | */ | |
5384a978 | 927 | public function testGetSummaryQueryWithFinancialACLDisabled() { |
faba82fc | 928 | $this->createContributionsForSummaryQueryTests(); |
929 | ||
930 | // Test the function directly | |
dbaa9d7d | 931 | $where = $from = NULL; |
932 | $queryObject = new CRM_Contact_BAO_Query(); | |
faba82fc | 933 | $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where, |
dbaa9d7d | 934 | $from); |
faba82fc | 935 | $this->assertEquals(NULL, $where); |
936 | $this->assertEquals(NULL, $from); | |
937 | ||
938 | // Test the function in action | |
939 | $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]); | |
940 | $summary = $queryObject->summaryContribution(); | |
941 | $this->assertEquals([ | |
942 | 'total' => [ | |
943 | 'avg' => '$ 233.33', | |
944 | 'amount' => '$ 1,400.00', | |
945 | 'count' => 6, | |
39b959db | 946 | ], |
faba82fc | 947 | 'cancel' => [ |
948 | 'count' => 2, | |
949 | 'amount' => '$ 100.00', | |
950 | 'avg' => '$ 50.00', | |
951 | ], | |
952 | ], $summary); | |
5384a978 VR |
953 | } |
954 | ||
dbaa9d7d | 955 | /** |
956 | * Test the summary query accurately adds financial acl filters. | |
957 | */ | |
5384a978 | 958 | public function testGetSummaryQueryWithFinancialACLEnabled() { |
dbaa9d7d | 959 | $where = $from = NULL; |
faba82fc | 960 | $this->createContributionsForSummaryQueryTests(); |
dbaa9d7d | 961 | $this->enableFinancialACLs(); |
962 | $this->createLoggedInUserWithFinancialACL(); | |
faba82fc | 963 | |
964 | // Test the function directly | |
dbaa9d7d | 965 | $queryObject = new CRM_Contact_BAO_Query(); |
faba82fc | 966 | $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where, |
dbaa9d7d | 967 | $from); |
968 | $donationTypeID = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'); | |
5384a978 VR |
969 | $this->assertEquals( |
970 | " LEFT JOIN civicrm_line_item li | |
971 | ON civicrm_contribution.id = li.contribution_id AND | |
dbaa9d7d | 972 | li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN ({$donationTypeID}) ", $from); |
faba82fc | 973 | |
974 | // Test the function in action | |
975 | $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]); | |
976 | $summary = $queryObject->summaryContribution(); | |
977 | $this->assertEquals([ | |
978 | 'total' => [ | |
979 | 'avg' => '$ 200.00', | |
980 | 'amount' => '$ 400.00', | |
981 | 'count' => 2, | |
faba82fc | 982 | ], |
983 | 'cancel' => [ | |
984 | 'count' => 1, | |
985 | 'amount' => '$ 50.00', | |
986 | 'avg' => '$ 50.00', | |
987 | ], | |
988 | ], $summary); | |
dbaa9d7d | 989 | $this->disableFinancialACLs(); |
5384a978 VR |
990 | } |
991 | ||
7c757812 AS |
992 | /** |
993 | * When we have a relative date in search criteria, check that convertFormValues() sets _low & _high date fields and returns other criteria. | |
994 | * CRM-21816 fix relative dates in search bug | |
995 | */ | |
996 | public function testConvertFormValuesCRM21816() { | |
fb1ee468 | 997 | $fv = [ |
998 | // next 60 days | |
39b959db | 999 | "member_end_date_relative" => "starting_2.month", |
7c757812 AS |
1000 | "member_end_date_low" => "20180101000000", |
1001 | "member_end_date_high" => "20180331235959", | |
1002 | "membership_is_current_member" => "1", | |
1003 | "member_is_primary" => "1", | |
fb1ee468 | 1004 | ]; |
39b959db SL |
1005 | // $fv is modified by convertFormValues() |
1006 | $fv_orig = $fv; | |
7c757812 AS |
1007 | $params = CRM_Contact_BAO_Query::convertFormValues($fv); |
1008 | ||
1009 | // restructure for easier testing | |
fb1ee468 | 1010 | $modparams = []; |
7c757812 AS |
1011 | foreach ($params as $p) { |
1012 | $modparams[$p[0]] = $p; | |
1013 | } | |
1014 | ||
1015 | // Check member_end_date_low is in params | |
1016 | $this->assertTrue(is_array($modparams['member_end_date_low'])); | |
1017 | // ... fv and params should match | |
1018 | $this->assertEquals($modparams['member_end_date_low'][2], $fv['member_end_date_low']); | |
1019 | // ... fv & fv_orig should be different | |
1020 | $this->assertNotEquals($fv['member_end_date_low'], $fv_orig['member_end_date_low']); | |
1021 | ||
1022 | // same for member_end_date_high | |
1023 | $this->assertTrue(is_array($modparams['member_end_date_high'])); | |
1024 | $this->assertEquals($modparams['member_end_date_high'][2], $fv['member_end_date_high']); | |
1025 | $this->assertNotEquals($fv['member_end_date_high'], $fv_orig['member_end_date_high']); | |
1026 | ||
1027 | // Check other fv values are in params | |
1028 | $this->assertEquals($modparams['membership_is_current_member'][2], $fv_orig['membership_is_current_member']); | |
1029 | $this->assertEquals($modparams['member_is_primary'][2], $fv_orig['member_is_primary']); | |
1030 | } | |
1031 | ||
faba82fc | 1032 | /** |
1033 | * Create contributions to test summary calculations. | |
1034 | * | |
1035 | * financial type | cancel_date |total_amount| source | line_item_financial_types |number_line_items| line_amounts | |
1036 | * Donation |NULL | 100.00 |SSF | Donation | 1 | 100.00 | |
1037 | * Member Dues |NULL | 100.00 |SSF | Member Dues | 1 | 100.00 | |
1038 | * Donation |NULL | 300.00 |SSF | Event Fee,Event Fee | 2 | 200.00,100.00 | |
1039 | * Donation |NULL | 300.00 |SSF | Event Fee,Donation | 2 | 200.00,100.00 | |
1040 | * Donation |NULL | 300.00 |SSF | Donation,Donation | 2 | 200.00,100.00 | |
1041 | * Donation |2019-02-13 00:00:00 | 50.00 |SSF | Donation | 1 | 50.00 | |
1042 | * Member Dues |2019-02-13 00:00:00 | 50.00 |SSF | Member Dues | 1 | 50.00 | |
1043 | */ | |
1044 | protected function createContributionsForSummaryQueryTests() { | |
1045 | $contactID = $this->individualCreate(); | |
1046 | $this->contributionCreate(['contact_id' => $contactID]); | |
1047 | $this->contributionCreate([ | |
1048 | 'contact_id' => $contactID, | |
1049 | 'total_amount' => 100, | |
1050 | 'financial_type_id' => 'Member Dues', | |
1051 | ]); | |
1052 | $eventFeeType = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Event Fee'); | |
1053 | $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF']); | |
1054 | $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [ | |
1055 | CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'), | |
1056 | $eventFeeType, | |
1057 | ]); | |
1058 | $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [ | |
1059 | CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'), | |
1060 | CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'), | |
1061 | ]); | |
1062 | $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF', 'financial_type_id' => $eventFeeType], [ | |
1063 | CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'), | |
1064 | CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'), | |
1065 | ]); | |
1066 | $this->contributionCreate([ | |
1067 | 'contact_id' => $contactID, | |
1068 | 'total_amount' => 50, | |
1069 | 'contribution_status_id' => 'Cancelled', | |
1070 | 'cancel_date' => 'yesterday', | |
1071 | ]); | |
1072 | $this->contributionCreate([ | |
1073 | 'contact_id' => $contactID, | |
1074 | 'total_amount' => 50, | |
1075 | 'contribution_status_id' => 'Cancelled', | |
1076 | 'cancel_date' => 'yesterday', | |
1077 | 'financial_type_id' => 'Member Dues', | |
1078 | ]); | |
1079 | } | |
1080 | ||
6a488035 | 1081 | } |