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 { | |
6a488035 | 8 | |
e9479dcf EM |
9 | /** |
10 | * @return CRM_Contact_BAO_QueryTestDataProvider | |
11 | */ | |
6a488035 | 12 | public function dataProvider() { |
acb1052e | 13 | return new CRM_Contact_BAO_QueryTestDataProvider(); |
6a488035 TO |
14 | } |
15 | ||
00be9182 | 16 | public function setUp() { |
6a488035 TO |
17 | parent::setUp(); |
18 | } | |
19 | ||
00be9182 | 20 | public function tearDown() { |
6a488035 TO |
21 | $tablesToTruncate = array( |
22 | 'civicrm_group_contact', | |
23 | 'civicrm_group', | |
24 | 'civicrm_saved_search', | |
25 | 'civicrm_entity_tag', | |
26 | 'civicrm_tag', | |
27 | 'civicrm_contact', | |
9b1e4469 | 28 | 'civicrm_address', |
6a488035 TO |
29 | ); |
30 | $this->quickCleanup($tablesToTruncate); | |
31 | } | |
32 | ||
33 | /** | |
3af96592 | 34 | * Test CRM_Contact_BAO_Query::searchQuery(). |
35 | * | |
6c6e6187 | 36 | * @dataProvider dataProvider |
3af96592 | 37 | * |
1e1fdcf6 EM |
38 | * @param $fv |
39 | * @param $count | |
40 | * @param $ids | |
41 | * @param $full | |
6a488035 | 42 | */ |
00be9182 | 43 | public function testSearch($fv, $count, $ids, $full) { |
6a488035 TO |
44 | $op = new PHPUnit_Extensions_Database_Operation_Insert(); |
45 | $op->execute($this->_dbconn, | |
bbfd46a5 | 46 | $this->createFlatXMLDataSet( |
6a488035 TO |
47 | dirname(__FILE__) . '/queryDataset.xml' |
48 | ) | |
49 | ); | |
50 | ||
51 | $params = CRM_Contact_BAO_Query::convertFormValues($fv); | |
92915c55 | 52 | $obj = new CRM_Contact_BAO_Query($params); |
b81f44dd | 53 | |
54 | // let's set useGroupBy=true since we are listing contacts here who might belong to | |
55 | // more than one group / tag / notes etc. | |
56 | $obj->_useGroupBy = TRUE; | |
57 | ||
92915c55 | 58 | $dao = $obj->searchQuery(); |
6a488035 TO |
59 | |
60 | $contacts = array(); | |
61 | while ($dao->fetch()) { | |
62 | $contacts[] = $dao->contact_id; | |
63 | } | |
64 | ||
65 | sort($contacts, SORT_NUMERIC); | |
66 | ||
a15773db | 67 | $this->assertEquals($ids, $contacts); |
6a488035 | 68 | } |
e5fccefb EM |
69 | |
70 | /** | |
eceb18cc | 71 | * Check that we get a successful result querying for home address. |
e5fccefb EM |
72 | * CRM-14263 search builder failure with search profile & address in criteria |
73 | */ | |
00be9182 | 74 | public function testSearchProfileHomeCityCRM14263() { |
e5fccefb EM |
75 | $contactID = $this->individualCreate(); |
76 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
92915c55 TO |
77 | $this->callAPISuccess('address', 'create', array( |
78 | 'contact_id' => $contactID, | |
79 | 'city' => 'Cool City', | |
acb1052e | 80 | 'location_type_id' => 1, |
92915c55 | 81 | )); |
e5fccefb EM |
82 | $params = array( |
83 | 0 => array( | |
84 | 0 => 'city-1', | |
85 | 1 => '=', | |
86 | 2 => 'Cool City', | |
87 | 3 => 1, | |
88 | 4 => 0, | |
21dfd5f5 | 89 | ), |
e5fccefb EM |
90 | ); |
91 | $returnProperties = array( | |
92 | 'contact_type' => 1, | |
93 | 'contact_sub_type' => 1, | |
94 | 'sort_name' => 1, | |
95 | ); | |
96 | ||
97 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
98 | try { | |
55eb4e22 | 99 | $resultDAO = $queryObj->searchQuery(0, 0, NULL, |
e5fccefb EM |
100 | FALSE, FALSE, |
101 | FALSE, FALSE, | |
102 | FALSE); | |
55eb4e22 | 103 | $this->assertTrue($resultDAO->fetch()); |
e5fccefb | 104 | } |
55eb4e22 EM |
105 | catch (PEAR_Exception $e) { |
106 | $err = $e->getCause(); | |
107 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
e5fccefb | 108 | |
55eb4e22 | 109 | } |
e5fccefb EM |
110 | } |
111 | ||
55eb4e22 | 112 | /** |
eceb18cc | 113 | * Check that we get a successful result querying for home address. |
55eb4e22 EM |
114 | * CRM-14263 search builder failure with search profile & address in criteria |
115 | */ | |
00be9182 | 116 | public function testSearchProfileHomeCityNoResultsCRM14263() { |
55eb4e22 EM |
117 | $contactID = $this->individualCreate(); |
118 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
92915c55 TO |
119 | $this->callAPISuccess('address', 'create', array( |
120 | 'contact_id' => $contactID, | |
121 | 'city' => 'Cool City', | |
acb1052e | 122 | 'location_type_id' => 1, |
92915c55 | 123 | )); |
55eb4e22 EM |
124 | $params = array( |
125 | 0 => array( | |
126 | 0 => 'city-1', | |
127 | 1 => '=', | |
128 | 2 => 'Dumb City', | |
129 | 3 => 1, | |
130 | 4 => 0, | |
21dfd5f5 | 131 | ), |
55eb4e22 EM |
132 | ); |
133 | $returnProperties = array( | |
134 | 'contact_type' => 1, | |
135 | 'contact_sub_type' => 1, | |
136 | 'sort_name' => 1, | |
137 | ); | |
138 | ||
139 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
140 | try { | |
141 | $resultDAO = $queryObj->searchQuery(0, 0, NULL, | |
142 | FALSE, FALSE, | |
143 | FALSE, FALSE, | |
144 | FALSE); | |
145 | $this->assertFalse($resultDAO->fetch()); | |
146 | } | |
147 | catch (PEAR_Exception $e) { | |
148 | $err = $e->getCause(); | |
149 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
150 | ||
151 | } | |
152 | } | |
92915c55 | 153 | |
b3e1c09d | 154 | /** |
155 | * Test searchByPrimaryEmailOnly setting. | |
156 | */ | |
157 | public function testSearchByPrimaryEmailOnly() { | |
158 | $contactID = $this->individualCreate(); | |
159 | $params = array( | |
160 | 'contact_id' => $contactID, | |
161 | 'email' => 'primary@example.com', | |
162 | 'is_primary' => 1, | |
163 | ); | |
164 | $this->callAPISuccess('email', 'create', $params); | |
165 | ||
166 | unset($params['is_primary']); | |
167 | $params['email'] = 'secondary@team.com'; | |
168 | $this->callAPISuccess('email', 'create', $params); | |
169 | ||
170 | foreach (array(0, 1) as $searchPrimary) { | |
171 | Civi::settings()->set('searchPrimaryEmailOnly', $searchPrimary); | |
172 | ||
173 | $params = array( | |
174 | 0 => array( | |
175 | 0 => 'email', | |
176 | 1 => 'LIKE', | |
177 | 2 => 'secondary@example.com', | |
178 | 3 => 0, | |
179 | 4 => 1, | |
180 | ), | |
181 | ); | |
182 | $returnProperties = array( | |
183 | 'contact_type' => 1, | |
184 | 'contact_sub_type' => 1, | |
185 | 'sort_name' => 1, | |
186 | ); | |
187 | ||
188 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); | |
189 | $resultDAO = $queryObj->searchQuery(0, 0, NULL, | |
190 | FALSE, FALSE, | |
191 | FALSE, FALSE, | |
192 | FALSE); | |
193 | ||
194 | if ($searchPrimary) { | |
195 | $this->assertEquals($resultDAO->N, 0); | |
196 | } | |
197 | else { | |
198 | //Assert secondary email gets included in search results. | |
199 | while ($resultDAO->fetch()) { | |
200 | $this->assertEquals('secondary@example.com', $resultDAO->email); | |
201 | } | |
202 | } | |
203 | ||
204 | // API should always return primary email. | |
205 | $result = $this->callAPISuccess('Contact', 'get', array('contact_id' => $contactID)); | |
206 | $this->assertEquals('primary@example.com', $result['values'][$contactID]['email']); | |
207 | } | |
208 | } | |
209 | ||
6c6e6187 TO |
210 | /** |
211 | * CRM-14263 search builder failure with search profile & address in criteria | |
212 | * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has | |
213 | * so few tests detecting any change seems good here :-) | |
214 | */ | |
6ea503d4 | 215 | public function testSearchProfilePrimaryCityCRM14263() { |
6c6e6187 TO |
216 | $contactID = $this->individualCreate(); |
217 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
92915c55 TO |
218 | $this->callAPISuccess('address', 'create', array( |
219 | 'contact_id' => $contactID, | |
220 | 'city' => 'Cool City', | |
acb1052e | 221 | 'location_type_id' => 1, |
92915c55 | 222 | )); |
6c6e6187 | 223 | $params = array( |
92915c55 TO |
224 | 0 => array( |
225 | 0 => 'city', | |
226 | 1 => '=', | |
227 | 2 => 'Cool City', | |
228 | 3 => 1, | |
229 | 4 => 0, | |
230 | ), | |
231 | ); | |
6c6e6187 | 232 | $returnProperties = array( |
92915c55 TO |
233 | 'contact_type' => 1, |
234 | 'contact_sub_type' => 1, | |
235 | 'sort_name' => 1, | |
236 | ); | |
15093854 | 237 | $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, civicrm_address.city as `city` FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE ( ( LOWER(civicrm_address.city) = 'cool city' ) ) AND (contact_a.is_deleted = 0) ORDER BY `contact_a`.`sort_name` asc, `contact_a`.`id` "; |
6c6e6187 TO |
238 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); |
239 | try { | |
240 | $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL, | |
92915c55 TO |
241 | FALSE, FALSE, |
242 | FALSE, FALSE, | |
243 | TRUE)); | |
6c6e6187 TO |
244 | } |
245 | catch (PEAR_Exception $e) { | |
246 | $err = $e->getCause(); | |
247 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
55eb4e22 | 248 | |
55eb4e22 | 249 | } |
6c6e6187 | 250 | } |
96025800 | 251 | |
82ae55f4 | 252 | /** |
253 | * Test set up to test calling the query object per GroupContactCache BAO usage. | |
254 | * | |
255 | * CRM-17254 ensure that if only the contact_id is required other fields should | |
256 | * not be appended. | |
257 | */ | |
258 | public function testGroupContactCacheAddSearch() { | |
259 | $returnProperties = array('contact_id'); | |
2f0c1d42 | 260 | $params = array(array('group', 'IN', array(1), 0, 0)); |
82ae55f4 | 261 | |
262 | $query = new CRM_Contact_BAO_Query( | |
263 | $params, $returnProperties, | |
264 | NULL, TRUE, FALSE, 1, | |
265 | TRUE, | |
266 | TRUE, FALSE | |
267 | ); | |
268 | ||
269 | list($select) = $query->query(FALSE); | |
270 | $this->assertEquals('SELECT contact_a.id as contact_id', $select); | |
271 | } | |
272 | ||
9b1e4469 | 273 | /** |
274 | * Test smart groups with non-numeric don't fail on range queries. | |
275 | * | |
276 | * CRM-14720 | |
277 | */ | |
278 | public function testNumericPostal() { | |
279 | $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main'))); | |
280 | $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main'))); | |
281 | $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main'))); | |
282 | $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main'))); | |
283 | ||
284 | $params = array(array('postal_code_low', '=', 5, 0, 0)); | |
285 | CRM_Contact_BAO_Query::convertFormValues($params); | |
286 | ||
287 | $query = new CRM_Contact_BAO_Query( | |
288 | $params, array('contact_id'), | |
289 | NULL, TRUE, FALSE, 1, | |
290 | TRUE, | |
291 | TRUE, FALSE | |
292 | ); | |
293 | ||
294 | $sql = $query->query(FALSE); | |
295 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); | |
296 | $this->assertEquals(2, $result->N); | |
297 | ||
298 | // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this | |
299 | // results in mysql warnings & hence fatal errors. | |
300 | /// I was unable to get mysql warnings to activate in the context of the unit tests - but | |
301 | // felt this code still provided a useful bit of coverage as it runs the various queries to load | |
302 | // the group & could generate invalid sql if a bug were introduced. | |
303 | $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1); | |
304 | $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams); | |
305 | CRM_Contact_BAO_GroupContactCache::load($group, TRUE); | |
306 | } | |
307 | ||
30415e03 | 308 | /** |
309 | * Test searches are case insensitive. | |
310 | */ | |
311 | public function testCaseInsensitive() { | |
312 | $orgID = $this->organizationCreate(array('organization_name' => 'BOb')); | |
313 | $this->callAPISuccess('Contact', 'create', array('display_name' => 'Minnie Mouse', 'employer_id' => $orgID, 'contact_type' => 'Individual')); | |
314 | $searchParams = array(array('current_employer', '=', 'bob', 0, 1)); | |
315 | $query = new CRM_Contact_BAO_Query($searchParams); | |
316 | $result = $query->apiQuery($searchParams); | |
317 | $this->assertEquals(1, count($result[0])); | |
318 | $contact = reset($result[0]); | |
319 | $this->assertEquals('Minnie Mouse', $contact['display_name']); | |
320 | $this->assertEquals('BOb', $contact['current_employer']); | |
321 | } | |
322 | ||
9a1491bb | 323 | /** |
324 | * Test smart groups with non-numeric don't fail on equal queries. | |
325 | * | |
326 | * CRM-14720 | |
327 | */ | |
328 | public function testNonNumericEqualsPostal() { | |
329 | $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main'))); | |
330 | $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main'))); | |
331 | $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main'))); | |
332 | $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main'))); | |
333 | ||
334 | $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0)); | |
335 | CRM_Contact_BAO_Query::convertFormValues($params); | |
336 | ||
337 | $query = new CRM_Contact_BAO_Query( | |
338 | $params, array('contact_id'), | |
339 | NULL, TRUE, FALSE, 1, | |
340 | TRUE, | |
341 | TRUE, FALSE | |
342 | ); | |
343 | ||
344 | $sql = $query->query(FALSE); | |
345 | $this->assertEquals("WHERE ( civicrm_address.postal_code = 'eh10 4rb-889' ) AND (contact_a.is_deleted = 0)", $sql[2]); | |
346 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); | |
347 | $this->assertEquals(1, $result->N); | |
348 | ||
349 | } | |
350 | ||
c3137c08 | 351 | /** |
352 | * Test the group contact clause does not contain an OR. | |
353 | * | |
354 | * The search should return 3 contacts - 2 households in the smart group of | |
355 | * Contact Type = Household and one Individual hard-added to it. The | |
356 | * Household that meets both criteria should be returned once. | |
357 | */ | |
358 | public function testGroupClause() { | |
359 | $this->householdCreate(); | |
360 | $householdID = $this->householdCreate(); | |
361 | $individualID = $this->individualCreate(); | |
362 | $groupID = $this->smartGroupCreate(); | |
363 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added')); | |
364 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added')); | |
365 | ||
485a3a1f | 366 | // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache. |
367 | CRM_Contact_BAO_GroupContactCache::remove($groupID, FALSE); | |
3875e6b6 | 368 | |
369 | $sql = CRM_Contact_BAO_Query::getQuery( | |
c3137c08 | 370 | array(array('group', 'IN', array($groupID), 0, 0)), |
371 | array('contact_id') | |
372 | ); | |
373 | ||
3875e6b6 | 374 | $dao = CRM_Core_DAO::executeQuery($sql); |
c3137c08 | 375 | $this->assertEquals(3, $dao->N); |
3875e6b6 | 376 | $this->assertFalse(strstr($sql, ' OR ')); |
377 | ||
378 | $sql = CRM_Contact_BAO_Query::getQuery( | |
379 | array(array('group', 'IN', array($groupID), 0, 0)), | |
380 | array('contact_id' => 1, 'group' => 1) | |
381 | ); | |
382 | ||
383 | $dao = CRM_Core_DAO::executeQuery($sql); | |
384 | $this->assertEquals(3, $dao->N); | |
385 | $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or'); | |
386 | while ($dao->fetch()) { | |
387 | $this->assertTrue(($dao->groups == $groupID || $dao->groups == ',' . $groupID), $dao->groups . ' includes ' . $groupID); | |
388 | } | |
c3137c08 | 389 | } |
390 | ||
b1128d0b | 391 | /** |
3af96592 | 392 | * CRM-19562 ensure that only ids are used for contact_id searching. |
b1128d0b SL |
393 | */ |
394 | public function testContactIDClause() { | |
395 | $params = array( | |
74714fd2 | 396 | array("mark_x_2", "=", 1, 0, 0), |
b1128d0b SL |
397 | array("mark_x_foo@example.com", "=", 1, 0, 0), |
398 | ); | |
399 | $returnProperties = array( | |
400 | "sort_name" => 1, | |
401 | "email" => 1, | |
402 | "do_not_email" => 1, | |
403 | "is_deceased" => 1, | |
404 | "on_hold" => 1, | |
405 | "display_name" => 1, | |
406 | "preferred_mail_format" => 1, | |
407 | ); | |
408 | $numberofContacts = 2; | |
409 | $query = new CRM_Contact_BAO_Query($params, $returnProperties); | |
410 | try { | |
411 | $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberofContacts); | |
412 | } | |
413 | catch (Exception $e) { | |
414 | $this->assertEquals("A fatal error was triggered: One of parameters (value: foo@example.com) is not of the type Positive", | |
415 | $e->getMessage()); | |
74714fd2 | 416 | return $this->assertTrue(TRUE); |
b1128d0b | 417 | } |
74714fd2 | 418 | return $this->fail('Test failed for some reason which is not good'); |
b1128d0b SL |
419 | } |
420 | ||
6a488035 | 421 | } |