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 | |
6c6e6187 TO |
154 | /** |
155 | * CRM-14263 search builder failure with search profile & address in criteria | |
156 | * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has | |
157 | * so few tests detecting any change seems good here :-) | |
158 | */ | |
6ea503d4 | 159 | public function testSearchProfilePrimaryCityCRM14263() { |
6c6e6187 TO |
160 | $contactID = $this->individualCreate(); |
161 | CRM_Core_Config::singleton()->defaultSearchProfileID = 1; | |
92915c55 TO |
162 | $this->callAPISuccess('address', 'create', array( |
163 | 'contact_id' => $contactID, | |
164 | 'city' => 'Cool City', | |
acb1052e | 165 | 'location_type_id' => 1, |
92915c55 | 166 | )); |
6c6e6187 | 167 | $params = array( |
92915c55 TO |
168 | 0 => array( |
169 | 0 => 'city', | |
170 | 1 => '=', | |
171 | 2 => 'Cool City', | |
172 | 3 => 1, | |
173 | 4 => 0, | |
174 | ), | |
175 | ); | |
6c6e6187 | 176 | $returnProperties = array( |
92915c55 TO |
177 | 'contact_type' => 1, |
178 | 'contact_sub_type' => 1, | |
179 | 'sort_name' => 1, | |
180 | ); | |
15093854 | 181 | $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 |
182 | $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties); |
183 | try { | |
184 | $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL, | |
92915c55 TO |
185 | FALSE, FALSE, |
186 | FALSE, FALSE, | |
187 | TRUE)); | |
6c6e6187 TO |
188 | } |
189 | catch (PEAR_Exception $e) { | |
190 | $err = $e->getCause(); | |
191 | $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo); | |
55eb4e22 | 192 | |
55eb4e22 | 193 | } |
6c6e6187 | 194 | } |
96025800 | 195 | |
82ae55f4 | 196 | /** |
197 | * Test set up to test calling the query object per GroupContactCache BAO usage. | |
198 | * | |
199 | * CRM-17254 ensure that if only the contact_id is required other fields should | |
200 | * not be appended. | |
201 | */ | |
202 | public function testGroupContactCacheAddSearch() { | |
203 | $returnProperties = array('contact_id'); | |
2f0c1d42 | 204 | $params = array(array('group', 'IN', array(1), 0, 0)); |
82ae55f4 | 205 | |
206 | $query = new CRM_Contact_BAO_Query( | |
207 | $params, $returnProperties, | |
208 | NULL, TRUE, FALSE, 1, | |
209 | TRUE, | |
210 | TRUE, FALSE | |
211 | ); | |
212 | ||
213 | list($select) = $query->query(FALSE); | |
214 | $this->assertEquals('SELECT contact_a.id as contact_id', $select); | |
215 | } | |
216 | ||
9b1e4469 | 217 | /** |
218 | * Test smart groups with non-numeric don't fail on range queries. | |
219 | * | |
220 | * CRM-14720 | |
221 | */ | |
222 | public function testNumericPostal() { | |
223 | $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main'))); | |
224 | $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main'))); | |
225 | $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main'))); | |
226 | $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main'))); | |
227 | ||
228 | $params = array(array('postal_code_low', '=', 5, 0, 0)); | |
229 | CRM_Contact_BAO_Query::convertFormValues($params); | |
230 | ||
231 | $query = new CRM_Contact_BAO_Query( | |
232 | $params, array('contact_id'), | |
233 | NULL, TRUE, FALSE, 1, | |
234 | TRUE, | |
235 | TRUE, FALSE | |
236 | ); | |
237 | ||
238 | $sql = $query->query(FALSE); | |
239 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); | |
240 | $this->assertEquals(2, $result->N); | |
241 | ||
242 | // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this | |
243 | // results in mysql warnings & hence fatal errors. | |
244 | /// I was unable to get mysql warnings to activate in the context of the unit tests - but | |
245 | // felt this code still provided a useful bit of coverage as it runs the various queries to load | |
246 | // the group & could generate invalid sql if a bug were introduced. | |
247 | $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1); | |
248 | $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams); | |
249 | CRM_Contact_BAO_GroupContactCache::load($group, TRUE); | |
250 | } | |
251 | ||
30415e03 | 252 | /** |
253 | * Test searches are case insensitive. | |
254 | */ | |
255 | public function testCaseInsensitive() { | |
256 | $orgID = $this->organizationCreate(array('organization_name' => 'BOb')); | |
257 | $this->callAPISuccess('Contact', 'create', array('display_name' => 'Minnie Mouse', 'employer_id' => $orgID, 'contact_type' => 'Individual')); | |
258 | $searchParams = array(array('current_employer', '=', 'bob', 0, 1)); | |
259 | $query = new CRM_Contact_BAO_Query($searchParams); | |
260 | $result = $query->apiQuery($searchParams); | |
261 | $this->assertEquals(1, count($result[0])); | |
262 | $contact = reset($result[0]); | |
263 | $this->assertEquals('Minnie Mouse', $contact['display_name']); | |
264 | $this->assertEquals('BOb', $contact['current_employer']); | |
265 | } | |
266 | ||
9a1491bb | 267 | /** |
268 | * Test smart groups with non-numeric don't fail on equal queries. | |
269 | * | |
270 | * CRM-14720 | |
271 | */ | |
272 | public function testNonNumericEqualsPostal() { | |
273 | $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main'))); | |
274 | $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main'))); | |
275 | $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main'))); | |
276 | $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main'))); | |
277 | ||
278 | $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0)); | |
279 | CRM_Contact_BAO_Query::convertFormValues($params); | |
280 | ||
281 | $query = new CRM_Contact_BAO_Query( | |
282 | $params, array('contact_id'), | |
283 | NULL, TRUE, FALSE, 1, | |
284 | TRUE, | |
285 | TRUE, FALSE | |
286 | ); | |
287 | ||
288 | $sql = $query->query(FALSE); | |
289 | $this->assertEquals("WHERE ( civicrm_address.postal_code = 'eh10 4rb-889' ) AND (contact_a.is_deleted = 0)", $sql[2]); | |
290 | $result = CRM_Core_DAO::executeQuery(implode(' ', $sql)); | |
291 | $this->assertEquals(1, $result->N); | |
292 | ||
293 | } | |
294 | ||
c3137c08 | 295 | /** |
296 | * Test the group contact clause does not contain an OR. | |
297 | * | |
298 | * The search should return 3 contacts - 2 households in the smart group of | |
299 | * Contact Type = Household and one Individual hard-added to it. The | |
300 | * Household that meets both criteria should be returned once. | |
301 | */ | |
302 | public function testGroupClause() { | |
303 | $this->householdCreate(); | |
304 | $householdID = $this->householdCreate(); | |
305 | $individualID = $this->individualCreate(); | |
306 | $groupID = $this->smartGroupCreate(); | |
307 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added')); | |
308 | $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added')); | |
309 | ||
485a3a1f | 310 | // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache. |
311 | CRM_Contact_BAO_GroupContactCache::remove($groupID, FALSE); | |
3875e6b6 | 312 | |
313 | $sql = CRM_Contact_BAO_Query::getQuery( | |
c3137c08 | 314 | array(array('group', 'IN', array($groupID), 0, 0)), |
315 | array('contact_id') | |
316 | ); | |
317 | ||
3875e6b6 | 318 | $dao = CRM_Core_DAO::executeQuery($sql); |
c3137c08 | 319 | $this->assertEquals(3, $dao->N); |
3875e6b6 | 320 | $this->assertFalse(strstr($sql, ' OR ')); |
321 | ||
322 | $sql = CRM_Contact_BAO_Query::getQuery( | |
323 | array(array('group', 'IN', array($groupID), 0, 0)), | |
324 | array('contact_id' => 1, 'group' => 1) | |
325 | ); | |
326 | ||
327 | $dao = CRM_Core_DAO::executeQuery($sql); | |
328 | $this->assertEquals(3, $dao->N); | |
329 | $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or'); | |
330 | while ($dao->fetch()) { | |
331 | $this->assertTrue(($dao->groups == $groupID || $dao->groups == ',' . $groupID), $dao->groups . ' includes ' . $groupID); | |
332 | } | |
c3137c08 | 333 | } |
334 | ||
b1128d0b | 335 | /** |
3af96592 | 336 | * CRM-19562 ensure that only ids are used for contact_id searching. |
b1128d0b SL |
337 | */ |
338 | public function testContactIDClause() { | |
339 | $params = array( | |
74714fd2 | 340 | array("mark_x_2", "=", 1, 0, 0), |
b1128d0b SL |
341 | array("mark_x_foo@example.com", "=", 1, 0, 0), |
342 | ); | |
343 | $returnProperties = array( | |
344 | "sort_name" => 1, | |
345 | "email" => 1, | |
346 | "do_not_email" => 1, | |
347 | "is_deceased" => 1, | |
348 | "on_hold" => 1, | |
349 | "display_name" => 1, | |
350 | "preferred_mail_format" => 1, | |
351 | ); | |
5cf7a77e | 352 | $numberOfContacts = 2; |
b1128d0b SL |
353 | $query = new CRM_Contact_BAO_Query($params, $returnProperties); |
354 | try { | |
5cf7a77e | 355 | $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberOfContacts); |
b1128d0b SL |
356 | } |
357 | catch (Exception $e) { | |
5cf7a77e | 358 | $this->assertEquals( |
359 | "A fatal error was triggered: One of parameters (value: foo@example.com) is not of the type Positive", | |
360 | $e->getMessage() | |
361 | ); | |
362 | $this->assertTrue(TRUE); | |
363 | return; | |
b1128d0b | 364 | } |
5cf7a77e | 365 | $this->fail('Test failed for some reason which is not good'); |
b1128d0b SL |
366 | } |
367 | ||
6a488035 | 368 | } |