Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
0eea664b | 2 | |
6a488035 TO |
3 | /** |
4 | * Include dataProvider for tests | |
acb109b7 | 5 | * @group headless |
6a488035 TO |
6 | */ |
7 | class CRM_Contact_BAO_QueryTest extends CiviUnitTestCase { | |
dbaa9d7d | 8 | use CRMTraits_Financial_FinancialACLTrait; |
6a488035 | 9 | |
e9479dcf EM |
10 | /** |
11 | * @return CRM_Contact_BAO_QueryTestDataProvider | |
12 | */ | |
6a488035 | 13 | public function dataProvider() { |
acb1052e | 14 | return new CRM_Contact_BAO_QueryTestDataProvider(); |
6a488035 TO |
15 | } |
16 | ||
00be9182 | 17 | public function setUp() { |
6a488035 TO |
18 | parent::setUp(); |
19 | } | |
20 | ||
00be9182 | 21 | public function tearDown() { |
6a488035 TO |
22 | $tablesToTruncate = array( |
23 | 'civicrm_group_contact', | |
24 | 'civicrm_group', | |
25 | 'civicrm_saved_search', | |
26 | 'civicrm_entity_tag', | |
27 | 'civicrm_tag', | |
28 | 'civicrm_contact', | |
9b1e4469 | 29 | 'civicrm_address', |
6a488035 TO |
30 | ); |
31 | $this->quickCleanup($tablesToTruncate); | |
32 | } | |
33 | ||
34 | /** | |
3af96592 | 35 | * Test CRM_Contact_BAO_Query::searchQuery(). |
36 | * | |
6c6e6187 | 37 | * @dataProvider dataProvider |
3af96592 | 38 | * |
1e1fdcf6 EM |
39 | * @param $fv |
40 | * @param $count | |
41 | * @param $ids | |
42 | * @param $full | |
6a488035 | 43 | */ |
00be9182 | 44 | public function testSearch($fv, $count, $ids, $full) { |
6a488035 TO |
45 | $op = new PHPUnit_Extensions_Database_Operation_Insert(); |
46 | $op->execute($this->_dbconn, | |
bbfd46a5 | 47 | $this->createFlatXMLDataSet( |
6a488035 TO |
48 | dirname(__FILE__) . '/queryDataset.xml' |
49 | ) | |
50 | ); | |
51 | ||
52 | $params = CRM_Contact_BAO_Query::convertFormValues($fv); | |
92915c55 | 53 | $obj = new CRM_Contact_BAO_Query($params); |
b81f44dd | 54 | |
55 | // let's set useGroupBy=true since we are listing contacts here who might belong to | |
56 | // more than one group / tag / notes etc. | |
57 | $obj->_useGroupBy = TRUE; | |
58 | ||
92915c55 | 59 | $dao = $obj->searchQuery(); |
6a488035 TO |
60 | |
61 | $contacts = array(); | |
62 | while ($dao->fetch()) { | |
63 | $contacts[] = $dao->contact_id; | |
64 | } | |
65 | ||
66 | sort($contacts, SORT_NUMERIC); | |
67 | ||
a15773db | 68 | $this->assertEquals($ids, $contacts); |
6a488035 | 69 | } |
e5fccefb EM |
70 | |
71 | /** | |
eceb18cc | 72 | * Check that we get a successful result querying for home address. |
e5fccefb EM |
73 | * CRM-14263 search builder failure with search profile & address in criteria |
74 | */ | |
00be9182 | 75 | public function testSearchProfileHomeCityCRM14263() { |
e5fccefb EM |
76 | $contactID = $this->individualCreate(); |
77 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
92915c55 TO |
78 | $this->callAPISuccess('address', 'create', array( |
79 | 'contact_id' => $contactID, | |
80 | 'city' => 'Cool City', | |
acb1052e | 81 | 'location_type_id' => 1, |
92915c55 | 82 | )); |
e5fccefb EM |
83 | $params = array( |
84 | 0 => array( | |
85 | 0 => 'city-1', | |
86 | 1 => '=', | |
87 | 2 => 'Cool City', | |
88 | 3 => 1, | |
89 | 4 => 0, | |
21dfd5f5 | 90 | ), |
e5fccefb EM |
91 | ); |
92 | $returnProperties = array( | |
93 | 'contact_type' => 1, | |
94 | 'contact_sub_type' => 1, | |
95 | 'sort_name' => 1, | |
96 | ); | |
97 | ||
98 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
99 | try { | |
55eb4e22 | 100 | $resultDAO = $queryObj->searchQuery(0, 0, NULL, |
e5fccefb EM |
101 | FALSE, FALSE, |
102 | FALSE, FALSE, | |
103 | FALSE); | |
55eb4e22 | 104 | $this->assertTrue($resultDAO->fetch()); |
e5fccefb | 105 | } |
55eb4e22 EM |
106 | catch (PEAR_Exception $e) { |
107 | $err = $e->getCause(); | |
108 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
e5fccefb | 109 | |
55eb4e22 | 110 | } |
e5fccefb EM |
111 | } |
112 | ||
55eb4e22 | 113 | /** |
eceb18cc | 114 | * Check that we get a successful result querying for home address. |
55eb4e22 EM |
115 | * CRM-14263 search builder failure with search profile & address in criteria |
116 | */ | |
00be9182 | 117 | public function testSearchProfileHomeCityNoResultsCRM14263() { |
55eb4e22 EM |
118 | $contactID = $this->individualCreate(); |
119 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
92915c55 TO |
120 | $this->callAPISuccess('address', 'create', array( |
121 | 'contact_id' => $contactID, | |
122 | 'city' => 'Cool City', | |
acb1052e | 123 | 'location_type_id' => 1, |
92915c55 | 124 | )); |
55eb4e22 EM |
125 | $params = array( |
126 | 0 => array( | |
127 | 0 => 'city-1', | |
128 | 1 => '=', | |
129 | 2 => 'Dumb City', | |
130 | 3 => 1, | |
131 | 4 => 0, | |
21dfd5f5 | 132 | ), |
55eb4e22 EM |
133 | ); |
134 | $returnProperties = array( | |
135 | 'contact_type' => 1, | |
136 | 'contact_sub_type' => 1, | |
137 | 'sort_name' => 1, | |
138 | ); | |
139 | ||
140 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
141 | try { | |
142 | $resultDAO = $queryObj->searchQuery(0, 0, NULL, | |
143 | FALSE, FALSE, | |
144 | FALSE, FALSE, | |
145 | FALSE); | |
146 | $this->assertFalse($resultDAO->fetch()); | |
147 | } | |
148 | catch (PEAR_Exception $e) { | |
149 | $err = $e->getCause(); | |
150 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
151 | ||
152 | } | |
153 | } | |
92915c55 | 154 | |
b3e1c09d | 155 | /** |
a3fccfc7 | 156 | * Test searchPrimaryDetailsOnly setting. |
b3e1c09d | 157 | */ |
9349bcf7 | 158 | public function testSearchPrimaryLocTypes() { |
b3e1c09d | 159 | $contactID = $this->individualCreate(); |
160 | $params = array( | |
161 | 'contact_id' => $contactID, | |
162 | 'email' => 'primary@example.com', | |
163 | 'is_primary' => 1, | |
164 | ); | |
165 | $this->callAPISuccess('email', 'create', $params); | |
166 | ||
167 | unset($params['is_primary']); | |
168 | $params['email'] = 'secondary@team.com'; | |
169 | $this->callAPISuccess('email', 'create', $params); | |
170 | ||
171 | foreach (array(0, 1) as $searchPrimary) { | |
a3fccfc7 | 172 | Civi::settings()->set('searchPrimaryDetailsOnly', $searchPrimary); |
b3e1c09d | 173 | |
174 | $params = array( | |
175 | 0 => array( | |
176 | 0 => 'email', | |
177 | 1 => 'LIKE', | |
c245fd99 | 178 | 2 => 'sEcondary@example.com', |
b3e1c09d | 179 | 3 => 0, |
180 | 4 => 1, | |
181 | ), | |
182 | ); | |
183 | $returnProperties = array( | |
184 | 'contact_type' => 1, | |
185 | 'contact_sub_type' => 1, | |
186 | 'sort_name' => 1, | |
187 | ); | |
188 | ||
189 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
190 | $resultDAO = $queryObj->searchQuery(0, 0, NULL, | |
191 | FALSE, FALSE, | |
192 | FALSE, FALSE, | |
193 | FALSE); | |
194 | ||
195 | if ($searchPrimary) { | |
196 | $this->assertEquals($resultDAO->N, 0); | |
197 | } | |
198 | else { | |
199 | //Assert secondary email gets included in search results. | |
200 | while ($resultDAO->fetch()) { | |
201 | $this->assertEquals('secondary@example.com', $resultDAO->email); | |
202 | } | |
203 | } | |
204 | ||
205 | // API should always return primary email. | |
206 | $result = $this->callAPISuccess('Contact', 'get', array('contact_id' => $contactID)); | |
207 | $this->assertEquals('primary@example.com', $result['values'][$contactID]['email']); | |
208 | } | |
209 | } | |
210 | ||
db1a73f5 KE |
211 | /** |
212 | * Test created to prove failure of search on state when location | |
213 | * display name is different form location name (issue 607) | |
214 | */ | |
215 | public function testSearchOtherLocationUpperLower() { | |
216 | ||
217 | $params = [ | |
218 | 0 => [ | |
219 | 0 => 'state_province-4', | |
220 | 1 => 'IS NOT EMPTY', | |
221 | 2 => '', | |
222 | 3 => 1, | |
223 | 4 => 0, | |
224 | ], | |
225 | ]; | |
226 | $returnProperties = [ | |
227 | 'contact_type' => 1, | |
228 | 'contact_sub_type' => 1, | |
229 | 'sort_name' => 1, | |
230 | 'location' => [ | |
231 | 'other' => [ | |
232 | 'location_type' => 4, | |
233 | 'state_province' => 1, | |
234 | ], | |
235 | ], | |
236 | ]; | |
237 | ||
6f1ae9ee KE |
238 | // update with the api does not work because it updates both the name and the |
239 | // the display_name. Plain SQL however does the job | |
db1a73f5 KE |
240 | CRM_Core_DAO::executeQuery('update civicrm_location_type set name=%2 where id=%1', |
241 | [ | |
242 | 1 => [4, 'Integer'], | |
243 | 2 => ['other', 'String'], | |
244 | ]); | |
245 | ||
246 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
247 | ||
248 | $resultDAO = $queryObj->searchQuery(0, 0, NULL, | |
249 | FALSE, FALSE, | |
250 | FALSE, FALSE, | |
251 | FALSE); | |
252 | $resultDAO->fetch(); | |
253 | } | |
254 | ||
255 | ||
6c6e6187 | 256 | /** |
fea8ae41 | 257 | * CRM-14263 search builder failure with search profile & address in criteria. |
258 | * | |
6c6e6187 TO |
259 | * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has |
260 | * so few tests detecting any change seems good here :-) | |
fea8ae41 | 261 | * |
262 | * @dataProvider getSearchProfileData | |
263 | * | |
264 | * @param array $params | |
6c6e6187 | 265 | */ |
fea8ae41 | 266 | public function testSearchProfilePrimaryCityCRM14263($params, $selectClause, $whereClause) { |
6c6e6187 TO |
267 | $contactID = $this->individualCreate(); |
268 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
92915c55 TO |
269 | $this->callAPISuccess('address', 'create', array( |
270 | 'contact_id' => $contactID, | |
54e02ce8 | 271 | 'city' => 'Cool CITY', |
272 | 'street_address' => 'Long STREET', | |
acb1052e | 273 | 'location_type_id' => 1, |
92915c55 | 274 | )); |
6c6e6187 | 275 | $returnProperties = array( |
92915c55 TO |
276 | 'contact_type' => 1, |
277 | 'contact_sub_type' => 1, | |
278 | 'sort_name' => 1, | |
279 | ); | |
fea8ae41 | 280 | $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 |
281 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); |
282 | try { | |
283 | $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL, | |
92915c55 TO |
284 | FALSE, FALSE, |
285 | FALSE, FALSE, | |
286 | TRUE)); | |
fea8ae41 | 287 | list($select, $from, $where, $having) = $queryObj->query(); |
288 | $dao = CRM_Core_DAO::executeQuery("$select $from $where $having"); | |
289 | $dao->fetch(); | |
290 | $this->assertEquals('Anderson, Anthony', $dao->sort_name); | |
6c6e6187 TO |
291 | } |
292 | catch (PEAR_Exception $e) { | |
293 | $err = $e->getCause(); | |
294 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
55eb4e22 | 295 | |
55eb4e22 | 296 | } |
6c6e6187 | 297 | } |
96025800 | 298 | |
fea8ae41 | 299 | /** |
300 | * Get data sets to test for search. | |
301 | */ | |
302 | public function getSearchProfileData() { | |
303 | return [ | |
304 | [ | |
2fc64082 | 305 | [['city', '=', 'Cool City', 1, 0]], "civicrm_address.city as `city`", "civicrm_address.city = 'Cool City'", |
fea8ae41 | 306 | ], |
307 | [ | |
308 | // Note that in the query 'long street' is lower cased. We eventually want to change that & not mess with the vars - it turns out | |
309 | // it doesn't work on some charsets. However, the the lcasing affects more vars & we are looking to stagger removal of lcasing 'in case' | |
310 | // (although we have been removing without blowback since 2017) | |
2fc64082 | 311 | [['street_address', '=', 'Long Street', 1, 0]], "civicrm_address.street_address as `street_address`", "civicrm_address.street_address LIKE '%Long Street%'", |
fea8ae41 | 312 | ], |
313 | ]; | |
314 | } | |
315 | ||
82ae55f4 | 316 | /** |
317 | * Test set up to test calling the query object per GroupContactCache BAO usage. | |
318 | * | |
319 | * CRM-17254 ensure that if only the contact_id is required other fields should | |
320 | * not be appended. | |
321 | */ | |
322 | public function testGroupContactCacheAddSearch() { | |
323 | $returnProperties = array('contact_id'); | |
2f0c1d42 | 324 | $params = array(array('group', 'IN', array(1), 0, 0)); |
82ae55f4 | 325 | |
326 | $query = new CRM_Contact_BAO_Query( | |
327 | $params, $returnProperties, | |
328 | NULL, TRUE, FALSE, 1, | |
329 | TRUE, | |
330 | TRUE, FALSE | |
331 | ); | |
332 | ||
333 | list($select) = $query->query(FALSE); | |
334 | $this->assertEquals('SELECT contact_a.id as contact_id', $select); | |
335 | } | |
336 | ||
9b1e4469 | 337 | /** |
338 | * Test smart groups with non-numeric don't fail on range queries. | |
339 | * | |
340 | * CRM-14720 | |
341 | */ | |
342 | public function testNumericPostal() { | |
91c164ed | 343 | // Precaution as hitting some inconsistent set up running in isolation vs in the suite. |
344 | CRM_Core_DAO::executeQuery('UPDATE civicrm_address SET postal_code = NULL'); | |
345 | ||
9b1e4469 | 346 | $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main'))); |
347 | $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main'))); | |
348 | $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main'))); | |
349 | $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main'))); | |
91c164ed | 350 | $this->individualCreate(array('api.address.create' => array('street_address' => 'just a street', 'location_type_id' => 'Main'))); |
351 | $this->individualCreate(array('api.address.create' => array('postal_code' => '12345678444455555555555555555555555555555555551314151617181920', 'location_type_id' => 'Main'))); | |
9b1e4469 | 352 | |
353 | $params = array(array('postal_code_low', '=', 5, 0, 0)); | |
354 | CRM_Contact_BAO_Query::convertFormValues($params); | |
355 | ||
356 | $query = new CRM_Contact_BAO_Query( | |
357 | $params, array('contact_id'), | |
358 | NULL, TRUE, FALSE, 1, | |
359 | TRUE, | |
360 | TRUE, FALSE | |
361 | ); | |
362 | ||
363 | $sql = $query->query(FALSE); | |
364 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); | |
365 | $this->assertEquals(2, $result->N); | |
366 | ||
367 | // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this | |
368 | // results in mysql warnings & hence fatal errors. | |
369 | /// I was unable to get mysql warnings to activate in the context of the unit tests - but | |
370 | // felt this code still provided a useful bit of coverage as it runs the various queries to load | |
371 | // the group & could generate invalid sql if a bug were introduced. | |
372 | $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1); | |
373 | $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams); | |
374 | CRM_Contact_BAO_GroupContactCache::load($group, TRUE); | |
375 | } | |
376 | ||
30415e03 | 377 | /** |
378 | * Test searches are case insensitive. | |
379 | */ | |
380 | public function testCaseInsensitive() { | |
381 | $orgID = $this->organizationCreate(array('organization_name' => 'BOb')); | |
1809f3cf | 382 | $params = array( |
383 | 'display_name' => 'Minnie Mouse', | |
384 | 'first_name' => 'Minnie', | |
385 | 'last_name' => 'Mouse', | |
386 | 'employer_id' => $orgID, | |
387 | 'contact_type' => 'Individual', | |
388 | 'nick_name' => 'Mins', | |
389 | ); | |
390 | $this->callAPISuccess('Contact', 'create', $params); | |
391 | unset($params['contact_type']); | |
392 | foreach ($params as $key => $value) { | |
393 | if ($key == 'employer_id') { | |
394 | $searchParams = array(array('current_employer', '=', 'bob', 0, 1)); | |
395 | } | |
396 | else { | |
397 | $searchParams = array(array($key, '=', strtolower($value), 0, 1)); | |
398 | } | |
399 | $query = new CRM_Contact_BAO_Query($searchParams); | |
400 | $result = $query->apiQuery($searchParams); | |
401 | $this->assertEquals(1, count($result[0]), 'search for ' . $key); | |
402 | $contact = reset($result[0]); | |
403 | $this->assertEquals('Minnie Mouse', $contact['display_name']); | |
404 | $this->assertEquals('BOb', $contact['current_employer']); | |
405 | } | |
30415e03 | 406 | } |
407 | ||
9a1491bb | 408 | /** |
409 | * Test smart groups with non-numeric don't fail on equal queries. | |
410 | * | |
411 | * CRM-14720 | |
412 | */ | |
413 | public function testNonNumericEqualsPostal() { | |
414 | $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main'))); | |
415 | $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main'))); | |
416 | $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main'))); | |
417 | $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main'))); | |
418 | ||
419 | $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0)); | |
420 | CRM_Contact_BAO_Query::convertFormValues($params); | |
421 | ||
422 | $query = new CRM_Contact_BAO_Query( | |
423 | $params, array('contact_id'), | |
424 | NULL, TRUE, FALSE, 1, | |
425 | TRUE, | |
426 | TRUE, FALSE | |
427 | ); | |
428 | ||
429 | $sql = $query->query(FALSE); | |
2fc64082 | 430 | $this->assertEquals("WHERE ( civicrm_address.postal_code = 'EH10 4RB-889' ) AND (contact_a.is_deleted = 0)", $sql[2]); |
9a1491bb | 431 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); |
432 | $this->assertEquals(1, $result->N); | |
433 | ||
434 | } | |
435 | ||
72a2eeab AS |
436 | public function testNonReciprocalRelationshipTargetGroupIsCorrectResults() { |
437 | $contactID_a = $this->individualCreate(); | |
438 | $contactID_b = $this->individualCreate(); | |
439 | $this->callAPISuccess('Relationship', 'create', array( | |
440 | 'contact_id_a' => $contactID_a, | |
441 | 'contact_id_b' => $contactID_b, | |
442 | 'relationship_type_id' => 1, | |
443 | 'is_active' => 1, | |
444 | )); | |
445 | // Create a group and add contact A to it. | |
446 | $groupID = $this->groupCreate(); | |
447 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added')); | |
448 | ||
449 | // Add another (sans-relationship) contact to the group, | |
450 | $contactID_c = $this->individualCreate(); | |
451 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added')); | |
452 | ||
453 | $params = array( | |
454 | array( | |
455 | 0 => 'relation_type_id', | |
456 | 1 => 'IN', | |
457 | 2 => | |
458 | array( | |
459 | 0 => '1_b_a', | |
460 | ), | |
461 | 3 => 0, | |
462 | 4 => 0, | |
463 | ), | |
464 | array( | |
465 | 0 => 'relation_target_group', | |
466 | 1 => 'IN', | |
467 | 2 => | |
468 | array( | |
469 | 0 => $groupID, | |
470 | ), | |
471 | 3 => 0, | |
472 | 4 => 0, | |
473 | ), | |
474 | ); | |
475 | ||
476 | $query = new CRM_Contact_BAO_Query($params); | |
477 | $dao = $query->searchQuery(); | |
478 | $this->assertEquals('1', $dao->N, "Search query returns exactly 1 result?"); | |
479 | $this->assertTrue($dao->fetch(), "Search query returns success?"); | |
480 | $this->assertEquals($contactID_b, $dao->contact_id, "Search query returns parent of contact A?"); | |
481 | } | |
482 | ||
483 | public function testReciprocalRelationshipTargetGroupIsCorrectResults() { | |
484 | $contactID_a = $this->individualCreate(); | |
485 | $contactID_b = $this->individualCreate(); | |
486 | $this->callAPISuccess('Relationship', 'create', array( | |
487 | 'contact_id_a' => $contactID_a, | |
488 | 'contact_id_b' => $contactID_b, | |
489 | 'relationship_type_id' => 2, | |
490 | 'is_active' => 1, | |
491 | )); | |
492 | // Create a group and add contact A to it. | |
493 | $groupID = $this->groupCreate(); | |
494 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added')); | |
495 | ||
496 | // Add another (sans-relationship) contact to the group, | |
497 | $contactID_c = $this->individualCreate(); | |
498 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added')); | |
499 | ||
500 | $params = array( | |
501 | array( | |
502 | 0 => 'relation_type_id', | |
503 | 1 => 'IN', | |
504 | 2 => | |
505 | array( | |
506 | 0 => '2_a_b', | |
507 | ), | |
508 | 3 => 0, | |
509 | 4 => 0, | |
510 | ), | |
511 | array( | |
512 | 0 => 'relation_target_group', | |
513 | 1 => 'IN', | |
514 | 2 => | |
515 | array( | |
516 | 0 => $groupID, | |
517 | ), | |
518 | 3 => 0, | |
519 | 4 => 0, | |
520 | ), | |
521 | ); | |
522 | ||
523 | $query = new CRM_Contact_BAO_Query($params); | |
524 | $dao = $query->searchQuery(); | |
525 | $this->assertEquals('1', $dao->N, "Search query returns exactly 1 result?"); | |
526 | $this->assertTrue($dao->fetch(), "Search query returns success?"); | |
527 | $this->assertEquals($contactID_b, $dao->contact_id, "Search query returns spouse of contact A?"); | |
528 | } | |
529 | ||
530 | public function testReciprocalRelationshipTargetGroupUsesTempTable() { | |
531 | $groupID = $this->groupCreate(); | |
532 | $params = array( | |
533 | array( | |
534 | 0 => 'relation_type_id', | |
535 | 1 => 'IN', | |
536 | 2 => | |
537 | array( | |
538 | 0 => '2_a_b', | |
539 | ), | |
540 | 3 => 0, | |
541 | 4 => 0, | |
542 | ), | |
543 | array( | |
544 | 0 => 'relation_target_group', | |
545 | 1 => 'IN', | |
546 | 2 => | |
547 | array( | |
548 | 0 => $groupID, | |
549 | ), | |
550 | 3 => 0, | |
551 | 4 => 0, | |
552 | ), | |
553 | ); | |
554 | $sql = CRM_Contact_BAO_Query::getQuery($params); | |
555 | $this->assertContains('INNER JOIN civicrm_rel_temp_', $sql, "Query appears to use temporary table of compiled relationships?", TRUE); | |
556 | } | |
557 | ||
9c9908e9 | 558 | public function testRelationshipPermissionClause() { |
559 | $params = [['relation_type_id', 'IN', ['1_b_a'], 0, 0], ['relation_permission', 'IN', [2], 0, 0]]; | |
560 | $sql = CRM_Contact_BAO_Query::getQuery($params); | |
561 | $this->assertContains('(civicrm_relationship.is_permission_a_b IN (2))', $sql); | |
562 | } | |
563 | ||
5ad36be5 SL |
564 | /** |
565 | * Test Relationship Clause | |
566 | */ | |
567 | public function testRelationshipClause() { | |
568 | $today = date('Ymd'); | |
5de9b484 JP |
569 | $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 )"; |
570 | $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 |
571 | $where1 = "WHERE ( ( |
572 | civicrm_relationship.is_active = 1 AND | |
573 | ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND | |
574 | ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} ) | |
575 | ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8) ) AND (contact_a.is_deleted = 0)"; | |
576 | $where2 = "WHERE ( ( | |
577 | civicrm_relationship.is_active = 1 AND | |
578 | ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND | |
579 | ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} ) | |
580 | ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8,10) ) AND (contact_a.is_deleted = 0)"; | |
581 | // Test Traditional single select format | |
582 | $params1 = array(array('relation_type_id', '=', '8_a_b', 0, 0)); | |
583 | $query1 = new CRM_Contact_BAO_Query( | |
584 | $params1, array('contact_id'), | |
585 | NULL, TRUE, FALSE, 1, | |
586 | TRUE, | |
587 | TRUE, FALSE | |
588 | ); | |
589 | $sql1 = $query1->query(FALSE); | |
5de9b484 | 590 | $this->assertEquals($from1, $sql1[1]); |
5ad36be5 SL |
591 | $this->assertEquals($where1, $sql1[2]); |
592 | // Test single relationship type selected in multiple select. | |
593 | $params2 = array(array('relation_type_id', 'IN', array('8_a_b'), 0, 0)); | |
594 | $query2 = new CRM_Contact_BAO_Query( | |
595 | $params2, array('contact_id'), | |
596 | NULL, TRUE, FALSE, 1, | |
597 | TRUE, | |
598 | TRUE, FALSE | |
599 | ); | |
600 | $sql2 = $query2->query(FALSE); | |
5de9b484 | 601 | $this->assertEquals($from1, $sql2[1]); |
5ad36be5 SL |
602 | $this->assertEquals($where1, $sql2[2]); |
603 | // Test multiple relationship types selected. | |
604 | $params3 = array(array('relation_type_id', 'IN', array('8_a_b', '10_a_b'), 0, 0)); | |
605 | $query3 = new CRM_Contact_BAO_Query( | |
606 | $params3, array('contact_id'), | |
607 | NULL, TRUE, FALSE, 1, | |
608 | TRUE, | |
609 | TRUE, FALSE | |
610 | ); | |
611 | $sql3 = $query3->query(FALSE); | |
5de9b484 | 612 | $this->assertEquals($from1, $sql3[1]); |
5ad36be5 SL |
613 | $this->assertEquals($where2, $sql3[2]); |
614 | // Test Multiple Relationship type selected where one doesn't actually exist. | |
615 | $params4 = array(array('relation_type_id', 'IN', array('8_a_b', '10_a_b', '14_a_b'), 0, 0)); | |
616 | $query4 = new CRM_Contact_BAO_Query( | |
617 | $params4, array('contact_id'), | |
618 | NULL, TRUE, FALSE, 1, | |
619 | TRUE, | |
620 | TRUE, FALSE | |
621 | ); | |
622 | $sql4 = $query4->query(FALSE); | |
5de9b484 | 623 | $this->assertEquals($from1, $sql4[1]); |
5ad36be5 | 624 | $this->assertEquals($where2, $sql4[2]); |
5de9b484 JP |
625 | |
626 | // Test Multiple b to a Relationship type . | |
627 | $params5 = array(array('relation_type_id', 'IN', array('8_b_a', '10_b_a', '14_b_a'), 0, 0)); | |
628 | $query5 = new CRM_Contact_BAO_Query( | |
629 | $params5, array('contact_id'), | |
630 | NULL, TRUE, FALSE, 1, | |
631 | TRUE, | |
632 | TRUE, FALSE | |
633 | ); | |
634 | $sql5 = $query5->query(FALSE); | |
635 | $this->assertEquals($from2, $sql5[1]); | |
636 | $this->assertEquals($where2, $sql5[2]); | |
5ad36be5 SL |
637 | } |
638 | ||
c3137c08 | 639 | /** |
640 | * Test the group contact clause does not contain an OR. | |
641 | * | |
642 | * The search should return 3 contacts - 2 households in the smart group of | |
643 | * Contact Type = Household and one Individual hard-added to it. The | |
644 | * Household that meets both criteria should be returned once. | |
645 | */ | |
646 | public function testGroupClause() { | |
647 | $this->householdCreate(); | |
648 | $householdID = $this->householdCreate(); | |
649 | $individualID = $this->individualCreate(); | |
650 | $groupID = $this->smartGroupCreate(); | |
651 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added')); | |
652 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added')); | |
653 | ||
485a3a1f | 654 | // 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 | 655 | CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID); |
3875e6b6 | 656 | |
657 | $sql = CRM_Contact_BAO_Query::getQuery( | |
c3137c08 | 658 | array(array('group', 'IN', array($groupID), 0, 0)), |
659 | array('contact_id') | |
660 | ); | |
661 | ||
3875e6b6 | 662 | $dao = CRM_Core_DAO::executeQuery($sql); |
c3137c08 | 663 | $this->assertEquals(3, $dao->N); |
3875e6b6 | 664 | $this->assertFalse(strstr($sql, ' OR ')); |
665 | ||
666 | $sql = CRM_Contact_BAO_Query::getQuery( | |
667 | array(array('group', 'IN', array($groupID), 0, 0)), | |
668 | array('contact_id' => 1, 'group' => 1) | |
669 | ); | |
670 | ||
671 | $dao = CRM_Core_DAO::executeQuery($sql); | |
672 | $this->assertEquals(3, $dao->N); | |
673 | $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or'); | |
674 | while ($dao->fetch()) { | |
675 | $this->assertTrue(($dao->groups == $groupID || $dao->groups == ',' . $groupID), $dao->groups . ' includes ' . $groupID); | |
676 | } | |
c3137c08 | 677 | } |
678 | ||
b1128d0b | 679 | /** |
3af96592 | 680 | * CRM-19562 ensure that only ids are used for contact_id searching. |
b1128d0b SL |
681 | */ |
682 | public function testContactIDClause() { | |
683 | $params = array( | |
74714fd2 | 684 | array("mark_x_2", "=", 1, 0, 0), |
b1128d0b SL |
685 | array("mark_x_foo@example.com", "=", 1, 0, 0), |
686 | ); | |
687 | $returnProperties = array( | |
688 | "sort_name" => 1, | |
689 | "email" => 1, | |
690 | "do_not_email" => 1, | |
691 | "is_deceased" => 1, | |
692 | "on_hold" => 1, | |
693 | "display_name" => 1, | |
694 | "preferred_mail_format" => 1, | |
695 | ); | |
5cf7a77e | 696 | $numberOfContacts = 2; |
b1128d0b SL |
697 | $query = new CRM_Contact_BAO_Query($params, $returnProperties); |
698 | try { | |
5cf7a77e | 699 | $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberOfContacts); |
b1128d0b SL |
700 | } |
701 | catch (Exception $e) { | |
5cf7a77e | 702 | $this->assertEquals( |
703 | "A fatal error was triggered: One of parameters (value: foo@example.com) is not of the type Positive", | |
704 | $e->getMessage() | |
705 | ); | |
706 | $this->assertTrue(TRUE); | |
707 | return; | |
b1128d0b | 708 | } |
5cf7a77e | 709 | $this->fail('Test failed for some reason which is not good'); |
b1128d0b SL |
710 | } |
711 | ||
dbaa9d7d | 712 | |
713 | /** | |
714 | * Test the summary query does not add an acl clause when acls not enabled.. | |
715 | */ | |
5384a978 | 716 | public function testGetSummaryQueryWithFinancialACLDisabled() { |
dbaa9d7d | 717 | $where = $from = NULL; |
718 | $queryObject = new CRM_Contact_BAO_Query(); | |
719 | $query = $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where, | |
720 | $from); | |
721 | $this->assertEquals($where, $query[0]); | |
722 | $this->assertEquals($from, $query[1]); | |
5384a978 VR |
723 | } |
724 | ||
dbaa9d7d | 725 | /** |
726 | * Test the summary query accurately adds financial acl filters. | |
727 | */ | |
5384a978 | 728 | public function testGetSummaryQueryWithFinancialACLEnabled() { |
dbaa9d7d | 729 | $where = $from = NULL; |
730 | $this->enableFinancialACLs(); | |
731 | $this->createLoggedInUserWithFinancialACL(); | |
732 | $queryObject = new CRM_Contact_BAO_Query(); | |
733 | $query = $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where, | |
734 | $from); | |
735 | $donationTypeID = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'); | |
5384a978 VR |
736 | $this->assertEquals( |
737 | " LEFT JOIN civicrm_line_item li | |
738 | ON civicrm_contribution.id = li.contribution_id AND | |
dbaa9d7d | 739 | li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN ({$donationTypeID}) ", $from); |
740 | $this->disableFinancialACLs(); | |
5384a978 VR |
741 | } |
742 | ||
7c757812 AS |
743 | /** |
744 | * When we have a relative date in search criteria, check that convertFormValues() sets _low & _high date fields and returns other criteria. | |
745 | * CRM-21816 fix relative dates in search bug | |
746 | */ | |
747 | public function testConvertFormValuesCRM21816() { | |
748 | $fv = array( | |
749 | "member_end_date_relative" => "starting_2.month", // next 60 days | |
750 | "member_end_date_low" => "20180101000000", | |
751 | "member_end_date_high" => "20180331235959", | |
752 | "membership_is_current_member" => "1", | |
753 | "member_is_primary" => "1", | |
754 | ); | |
755 | $fv_orig = $fv; // $fv is modified by convertFormValues() | |
756 | $params = CRM_Contact_BAO_Query::convertFormValues($fv); | |
757 | ||
758 | // restructure for easier testing | |
759 | $modparams = array(); | |
760 | foreach ($params as $p) { | |
761 | $modparams[$p[0]] = $p; | |
762 | } | |
763 | ||
764 | // Check member_end_date_low is in params | |
765 | $this->assertTrue(is_array($modparams['member_end_date_low'])); | |
766 | // ... fv and params should match | |
767 | $this->assertEquals($modparams['member_end_date_low'][2], $fv['member_end_date_low']); | |
768 | // ... fv & fv_orig should be different | |
769 | $this->assertNotEquals($fv['member_end_date_low'], $fv_orig['member_end_date_low']); | |
770 | ||
771 | // same for member_end_date_high | |
772 | $this->assertTrue(is_array($modparams['member_end_date_high'])); | |
773 | $this->assertEquals($modparams['member_end_date_high'][2], $fv['member_end_date_high']); | |
774 | $this->assertNotEquals($fv['member_end_date_high'], $fv_orig['member_end_date_high']); | |
775 | ||
776 | // Check other fv values are in params | |
777 | $this->assertEquals($modparams['membership_is_current_member'][2], $fv_orig['membership_is_current_member']); | |
778 | $this->assertEquals($modparams['member_is_primary'][2], $fv_orig['member_is_primary']); | |
779 | } | |
780 | ||
6a488035 | 781 | } |