4 * Include dataProvider for tests
7 class CRM_Contact_BAO_QueryTest
extends CiviUnitTestCase
{
8 use CRMTraits_Financial_FinancialACLTrait
;
9 use CRMTraits_Financial_PriceSetTrait
;
12 * @return CRM_Contact_BAO_QueryTestDataProvider
14 public function dataProvider() {
15 return new CRM_Contact_BAO_QueryTestDataProvider();
18 public function setUp() {
22 public function tearDown() {
23 $this->quickCleanUpFinancialEntities();
24 $tablesToTruncate = array(
25 'civicrm_group_contact',
27 'civicrm_saved_search',
33 $this->quickCleanup($tablesToTruncate);
37 * Test CRM_Contact_BAO_Query::searchQuery().
39 * @dataProvider dataProvider
46 public function testSearch($fv, $count, $ids, $full) {
47 $op = new PHPUnit_Extensions_Database_Operation_Insert();
48 $op->execute($this->_dbconn
,
49 $this->createFlatXMLDataSet(
50 dirname(__FILE__
) . '/queryDataset.xml'
54 $params = CRM_Contact_BAO_Query
::convertFormValues($fv);
55 $obj = new CRM_Contact_BAO_Query($params);
57 // let's set useGroupBy=true since we are listing contacts here who might belong to
58 // more than one group / tag / notes etc.
59 $obj->_useGroupBy
= TRUE;
61 $dao = $obj->searchQuery();
64 while ($dao->fetch()) {
65 $contacts[] = $dao->contact_id
;
68 sort($contacts, SORT_NUMERIC
);
70 $this->assertEquals($ids, $contacts);
74 * Check that we get a successful result querying for home address.
75 * CRM-14263 search builder failure with search profile & address in criteria
77 public function testSearchProfileHomeCityCRM14263() {
78 $contactID = $this->individualCreate();
79 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
80 $this->callAPISuccess('address', 'create', array(
81 'contact_id' => $contactID,
82 'city' => 'Cool City',
83 'location_type_id' => 1,
94 $returnProperties = array(
96 'contact_sub_type' => 1,
100 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
102 $resultDAO = $queryObj->searchQuery();
103 $this->assertTrue($resultDAO->fetch());
105 catch (PEAR_Exception
$e) {
106 $err = $e->getCause();
107 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
113 * Check that we get a successful result querying for home address.
114 * CRM-14263 search builder failure with search profile & address in criteria
116 public function testSearchProfileHomeCityNoResultsCRM14263() {
117 $contactID = $this->individualCreate();
118 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
119 $this->callAPISuccess('address', 'create', array(
120 'contact_id' => $contactID,
121 'city' => 'Cool City',
122 'location_type_id' => 1,
133 $returnProperties = array(
135 'contact_sub_type' => 1,
139 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
141 $resultDAO = $queryObj->searchQuery();
142 $this->assertFalse($resultDAO->fetch());
144 catch (PEAR_Exception
$e) {
145 $err = $e->getCause();
146 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
152 * Test searchPrimaryDetailsOnly setting.
154 public function testSearchPrimaryLocTypes() {
155 $contactID = $this->individualCreate();
157 'contact_id' => $contactID,
158 'email' => 'primary@example.com',
161 $this->callAPISuccess('email', 'create', $params);
163 unset($params['is_primary']);
164 $params['email'] = 'secondary@team.com';
165 $this->callAPISuccess('email', 'create', $params);
167 foreach (array(0, 1) as $searchPrimary) {
168 Civi
::settings()->set('searchPrimaryDetailsOnly', $searchPrimary);
174 2 => 'sEcondary@example.com',
179 $returnProperties = array(
181 'contact_sub_type' => 1,
185 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
186 $resultDAO = $queryObj->searchQuery();
188 if ($searchPrimary) {
189 $this->assertEquals($resultDAO->N
, 0);
192 //Assert secondary email gets included in search results.
193 while ($resultDAO->fetch()) {
194 $this->assertEquals('secondary@example.com', $resultDAO->email
);
198 // API should always return primary email.
199 $result = $this->callAPISuccess('Contact', 'get', array('contact_id' => $contactID));
200 $this->assertEquals('primary@example.com', $result['values'][$contactID]['email']);
205 * Test created to prove failure of search on state when location
206 * display name is different form location name (issue 607)
208 public function testSearchOtherLocationUpperLower() {
212 0 => 'state_province-4',
219 $returnProperties = [
221 'contact_sub_type' => 1,
225 'location_type' => 4,
226 'state_province' => 1,
231 // update with the api does not work because it updates both the name and the
232 // the display_name. Plain SQL however does the job
233 CRM_Core_DAO
::executeQuery('update civicrm_location_type set name=%2 where id=%1',
236 2 => ['other', 'String'],
239 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
241 $resultDAO = $queryObj->searchQuery();
247 * CRM-14263 search builder failure with search profile & address in criteria.
249 * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
250 * so few tests detecting any change seems good here :-)
252 * @dataProvider getSearchProfileData
254 * @param array $params
256 public function testSearchProfilePrimaryCityCRM14263($params, $selectClause, $whereClause) {
257 $contactID = $this->individualCreate();
258 CRM_Core_Config
::singleton()->defaultSearchProfileID
= 1;
259 $this->callAPISuccess('address', 'create', array(
260 'contact_id' => $contactID,
261 'city' => 'Cool CITY',
262 'street_address' => 'Long STREET',
263 'location_type_id' => 1,
265 $returnProperties = array(
267 'contact_sub_type' => 1,
270 $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` ";
271 $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
273 $this->assertEquals($expectedSQL, $queryObj->getSearchSQL());
274 list($select, $from, $where, $having) = $queryObj->query();
275 $dao = CRM_Core_DAO
::executeQuery("$select $from $where $having");
277 $this->assertEquals('Anderson, Anthony', $dao->sort_name
);
279 catch (PEAR_Exception
$e) {
280 $err = $e->getCause();
281 $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo
);
287 * Get data sets to test for search.
289 public function getSearchProfileData() {
292 [['city', '=', 'Cool City', 1, 0]], "civicrm_address.city as `city`", "civicrm_address.city = 'Cool City'",
295 // Note that in the query 'long street' is lower cased. We eventually want to change that & not mess with the vars - it turns out
296 // it doesn't work on some charsets. However, the the lcasing affects more vars & we are looking to stagger removal of lcasing 'in case'
297 // (although we have been removing without blowback since 2017)
298 [['street_address', '=', 'Long Street', 1, 0]], "civicrm_address.street_address as `street_address`", "civicrm_address.street_address LIKE '%Long Street%'",
304 * Test set up to test calling the query object per GroupContactCache BAO usage.
306 * CRM-17254 ensure that if only the contact_id is required other fields should
309 public function testGroupContactCacheAddSearch() {
310 $returnProperties = array('contact_id');
311 $params = array(array('group', 'IN', array(1), 0, 0));
313 $query = new CRM_Contact_BAO_Query(
314 $params, $returnProperties,
315 NULL, TRUE, FALSE, 1,
320 list($select) = $query->query(FALSE);
321 $this->assertEquals('SELECT contact_a.id as contact_id', $select);
325 * Test smart groups with non-numeric don't fail on range queries.
329 public function testNumericPostal() {
330 // Precaution as hitting some inconsistent set up running in isolation vs in the suite.
331 CRM_Core_DAO
::executeQuery('UPDATE civicrm_address SET postal_code = NULL');
333 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
334 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
335 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
336 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
337 $this->individualCreate(array('api.address.create' => array('street_address' => 'just a street', 'location_type_id' => 'Main')));
338 $this->individualCreate(array('api.address.create' => array('postal_code' => '12345678444455555555555555555555555555555555551314151617181920', 'location_type_id' => 'Main')));
340 $params = array(array('postal_code_low', '=', 5, 0, 0));
341 CRM_Contact_BAO_Query
::convertFormValues($params);
343 $query = new CRM_Contact_BAO_Query(
344 $params, array('contact_id'),
345 NULL, TRUE, FALSE, 1,
350 $sql = $query->query(FALSE);
351 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
352 $this->assertEquals(2, $result->N
);
354 // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
355 // results in mysql warnings & hence fatal errors.
356 /// I was unable to get mysql warnings to activate in the context of the unit tests - but
357 // felt this code still provided a useful bit of coverage as it runs the various queries to load
358 // the group & could generate invalid sql if a bug were introduced.
359 $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
360 $group = CRM_Contact_BAO_Group
::createSmartGroup($groupParams);
361 CRM_Contact_BAO_GroupContactCache
::load($group, TRUE);
365 * Test searches are case insensitive.
367 public function testCaseInsensitive() {
368 $orgID = $this->organizationCreate(array('organization_name' => 'BOb'));
370 'display_name' => 'Minnie Mouse',
371 'first_name' => 'Minnie',
372 'last_name' => 'Mouse',
373 'employer_id' => $orgID,
374 'contact_type' => 'Individual',
375 'nick_name' => 'Mins',
377 $this->callAPISuccess('Contact', 'create', $params);
378 unset($params['contact_type']);
379 foreach ($params as $key => $value) {
380 if ($key == 'employer_id') {
381 $searchParams = array(array('current_employer', '=', 'bob', 0, 1));
384 $searchParams = array(array($key, '=', strtolower($value), 0, 1));
386 $query = new CRM_Contact_BAO_Query($searchParams);
387 $result = $query->apiQuery($searchParams);
388 $this->assertEquals(1, count($result[0]), 'search for ' . $key);
389 $contact = reset($result[0]);
390 $this->assertEquals('Minnie Mouse', $contact['display_name']);
391 $this->assertEquals('BOb', $contact['current_employer']);
396 * Test smart groups with non-numeric don't fail on equal queries.
400 public function testNonNumericEqualsPostal() {
401 $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
402 $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
403 $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
404 $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
406 $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0));
407 CRM_Contact_BAO_Query
::convertFormValues($params);
409 $query = new CRM_Contact_BAO_Query(
410 $params, array('contact_id'),
411 NULL, TRUE, FALSE, 1,
416 $sql = $query->query(FALSE);
417 $this->assertEquals("WHERE ( civicrm_address.postal_code = 'EH10 4RB-889' ) AND (contact_a.is_deleted = 0)", $sql[2]);
418 $result = CRM_Core_DAO
::executeQuery(implode(' ', $sql));
419 $this->assertEquals(1, $result->N
);
423 public function testNonReciprocalRelationshipTargetGroupIsCorrectResults() {
424 $contactID_a = $this->individualCreate();
425 $contactID_b = $this->individualCreate();
426 $this->callAPISuccess('Relationship', 'create', array(
427 'contact_id_a' => $contactID_a,
428 'contact_id_b' => $contactID_b,
429 'relationship_type_id' => 1,
432 // Create a group and add contact A to it.
433 $groupID = $this->groupCreate();
434 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added'));
436 // Add another (sans-relationship) contact to the group,
437 $contactID_c = $this->individualCreate();
438 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added'));
442 0 => 'relation_type_id',
452 0 => 'relation_target_group',
463 $query = new CRM_Contact_BAO_Query($params);
464 $dao = $query->searchQuery();
465 $this->assertEquals('1', $dao->N
, "Search query returns exactly 1 result?");
466 $this->assertTrue($dao->fetch(), "Search query returns success?");
467 $this->assertEquals($contactID_b, $dao->contact_id
, "Search query returns parent of contact A?");
470 public function testReciprocalRelationshipTargetGroupIsCorrectResults() {
471 $contactID_a = $this->individualCreate();
472 $contactID_b = $this->individualCreate();
473 $this->callAPISuccess('Relationship', 'create', array(
474 'contact_id_a' => $contactID_a,
475 'contact_id_b' => $contactID_b,
476 'relationship_type_id' => 2,
479 // Create a group and add contact A to it.
480 $groupID = $this->groupCreate();
481 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added'));
483 // Add another (sans-relationship) contact to the group,
484 $contactID_c = $this->individualCreate();
485 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added'));
489 0 => 'relation_type_id',
499 0 => 'relation_target_group',
510 $query = new CRM_Contact_BAO_Query($params);
511 $dao = $query->searchQuery();
512 $this->assertEquals('1', $dao->N
, "Search query returns exactly 1 result?");
513 $this->assertTrue($dao->fetch(), "Search query returns success?");
514 $this->assertEquals($contactID_b, $dao->contact_id
, "Search query returns spouse of contact A?");
517 public function testReciprocalRelationshipTargetGroupUsesTempTable() {
518 $groupID = $this->groupCreate();
521 0 => 'relation_type_id',
531 0 => 'relation_target_group',
541 $sql = CRM_Contact_BAO_Query
::getQuery($params);
542 $this->assertContains('INNER JOIN civicrm_rel_temp_', $sql, "Query appears to use temporary table of compiled relationships?", TRUE);
545 public function testRelationshipPermissionClause() {
546 $params = [['relation_type_id', 'IN', ['1_b_a'], 0, 0], ['relation_permission', 'IN', [2], 0, 0]];
547 $sql = CRM_Contact_BAO_Query
::getQuery($params);
548 $this->assertContains('(civicrm_relationship.is_permission_a_b IN (2))', $sql);
552 * Test Relationship Clause
554 public function testRelationshipClause() {
555 $today = date('Ymd');
556 $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 )";
557 $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 )";
559 civicrm_relationship.is_active = 1 AND
560 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
561 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
562 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8) ) AND (contact_a.is_deleted = 0)";
564 civicrm_relationship.is_active = 1 AND
565 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
566 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
567 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8,10) ) AND (contact_a.is_deleted = 0)";
568 // Test Traditional single select format
569 $params1 = array(array('relation_type_id', '=', '8_a_b', 0, 0));
570 $query1 = new CRM_Contact_BAO_Query(
571 $params1, array('contact_id'),
572 NULL, TRUE, FALSE, 1,
576 $sql1 = $query1->query(FALSE);
577 $this->assertEquals($from1, $sql1[1]);
578 $this->assertEquals($where1, $sql1[2]);
579 // Test single relationship type selected in multiple select.
580 $params2 = array(array('relation_type_id', 'IN', array('8_a_b'), 0, 0));
581 $query2 = new CRM_Contact_BAO_Query(
582 $params2, array('contact_id'),
583 NULL, TRUE, FALSE, 1,
587 $sql2 = $query2->query(FALSE);
588 $this->assertEquals($from1, $sql2[1]);
589 $this->assertEquals($where1, $sql2[2]);
590 // Test multiple relationship types selected.
591 $params3 = array(array('relation_type_id', 'IN', array('8_a_b', '10_a_b'), 0, 0));
592 $query3 = new CRM_Contact_BAO_Query(
593 $params3, array('contact_id'),
594 NULL, TRUE, FALSE, 1,
598 $sql3 = $query3->query(FALSE);
599 $this->assertEquals($from1, $sql3[1]);
600 $this->assertEquals($where2, $sql3[2]);
601 // Test Multiple Relationship type selected where one doesn't actually exist.
602 $params4 = array(array('relation_type_id', 'IN', array('8_a_b', '10_a_b', '14_a_b'), 0, 0));
603 $query4 = new CRM_Contact_BAO_Query(
604 $params4, array('contact_id'),
605 NULL, TRUE, FALSE, 1,
609 $sql4 = $query4->query(FALSE);
610 $this->assertEquals($from1, $sql4[1]);
611 $this->assertEquals($where2, $sql4[2]);
613 // Test Multiple b to a Relationship type .
614 $params5 = array(array('relation_type_id', 'IN', array('8_b_a', '10_b_a', '14_b_a'), 0, 0));
615 $query5 = new CRM_Contact_BAO_Query(
616 $params5, array('contact_id'),
617 NULL, TRUE, FALSE, 1,
621 $sql5 = $query5->query(FALSE);
622 $this->assertEquals($from2, $sql5[1]);
623 $this->assertEquals($where2, $sql5[2]);
627 * Test the group contact clause does not contain an OR.
629 * The search should return 3 contacts - 2 households in the smart group of
630 * Contact Type = Household and one Individual hard-added to it. The
631 * Household that meets both criteria should be returned once.
633 public function testGroupClause() {
634 $this->householdCreate();
635 $householdID = $this->householdCreate();
636 $individualID = $this->individualCreate();
637 $groupID = $this->smartGroupCreate();
638 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added'));
639 $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added'));
641 // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
642 CRM_Contact_BAO_GroupContactCache
::clearGroupContactCache($groupID);
644 $sql = CRM_Contact_BAO_Query
::getQuery(
645 array(array('group', 'IN', array($groupID), 0, 0)),
649 $dao = CRM_Core_DAO
::executeQuery($sql);
650 $this->assertEquals(3, $dao->N
);
651 $this->assertFalse(strstr($sql, ' OR '));
653 $sql = CRM_Contact_BAO_Query
::getQuery(
654 array(array('group', 'IN', array($groupID), 0, 0)),
655 array('contact_id' => 1, 'group' => 1)
658 $dao = CRM_Core_DAO
::executeQuery($sql);
659 $this->assertEquals(3, $dao->N
);
660 $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or');
661 while ($dao->fetch()) {
662 $this->assertTrue(($dao->groups
== $groupID ||
$dao->groups
== ',' . $groupID), $dao->groups
. ' includes ' . $groupID);
667 * CRM-19562 ensure that only ids are used for contact_id searching.
669 public function testContactIDClause() {
671 array("mark_x_2", "=", 1, 0, 0),
672 array("mark_x_foo@example.com", "=", 1, 0, 0),
674 $returnProperties = array(
681 "preferred_mail_format" => 1,
683 $numberOfContacts = 2;
684 $query = new CRM_Contact_BAO_Query($params, $returnProperties);
686 $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberOfContacts);
688 catch (Exception
$e) {
690 "A fatal error was triggered: One of parameters (value: foo@example.com) is not of the type Positive",
693 $this->assertTrue(TRUE);
696 $this->fail('Test failed for some reason which is not good');
700 * Test the sorting on the contact ID query works.
702 * Checking for lack of fatal.
704 * @param string $sortOrder
705 * Param reflecting how sort is passed in.
706 * - 1_d is column 1 descending.
708 * @dataProvider getSortOptions
710 public function testContactIDQuery($sortOrder) {
711 $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all'], NULL, ['sort_name' => 1]);
712 $selector->contactIDQuery([], $sortOrder);
716 * Test the sorting on the contact ID query works with a profile search.
718 * Checking for lack of fatal.
720 public function testContactIDQueryProfileSearchResults() {
721 $profile = $this->callAPISuccess('UFGroup', 'create', ['group_type' => 'Contact', 'name' => 'search', 'title' => 'search']);
722 $this->callAPISuccess('UFField', 'create', [
723 'uf_group_id' => $profile['id'],
724 'field_name' => 'postal_code',
725 'field_type' => 'Contact',
726 'in_selector' => TRUE,
727 'is_searchable' => TRUE,
728 'label' => 'postal code',
729 'visibility' => 'Public Pages and Listings',
731 $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all', 'uf_group_id' => $profile['id']], NULL, ['sort_name' => 1]);
732 $selector->contactIDQuery([], '2_d');
736 * Get search options to reflect how a UI search would look.
740 public function getSortOptions() {
752 * Test the summary query does not add an acl clause when acls not enabled..
754 public function testGetSummaryQueryWithFinancialACLDisabled() {
755 $this->createContributionsForSummaryQueryTests();
757 // Test the function directly
758 $where = $from = NULL;
759 $queryObject = new CRM_Contact_BAO_Query();
760 $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
762 $this->assertEquals(NULL, $where);
763 $this->assertEquals(NULL, $from);
765 // Test the function in action
766 $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
767 $summary = $queryObject->summaryContribution();
768 $this->assertEquals([
771 'amount' => '$ 1,400.00',
776 'amount' => '$ 100.00',
783 * Test the summary query accurately adds financial acl filters.
785 public function testGetSummaryQueryWithFinancialACLEnabled() {
786 $where = $from = NULL;
787 $this->createContributionsForSummaryQueryTests();
788 $this->enableFinancialACLs();
789 $this->createLoggedInUserWithFinancialACL();
791 // Test the function directly
792 $queryObject = new CRM_Contact_BAO_Query();
793 $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
795 $donationTypeID = CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation');
797 " LEFT JOIN civicrm_line_item li
798 ON civicrm_contribution.id = li.contribution_id AND
799 li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN ({$donationTypeID}) ", $from);
801 // Test the function in action
802 $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
803 $summary = $queryObject->summaryContribution();
804 $this->assertEquals([
807 'amount' => '$ 400.00',
812 'amount' => '$ 50.00',
816 $this->disableFinancialACLs();
820 * When we have a relative date in search criteria, check that convertFormValues() sets _low & _high date fields and returns other criteria.
821 * CRM-21816 fix relative dates in search bug
823 public function testConvertFormValuesCRM21816() {
825 "member_end_date_relative" => "starting_2.month", // next 60 days
826 "member_end_date_low" => "20180101000000",
827 "member_end_date_high" => "20180331235959",
828 "membership_is_current_member" => "1",
829 "member_is_primary" => "1",
831 $fv_orig = $fv; // $fv is modified by convertFormValues()
832 $params = CRM_Contact_BAO_Query
::convertFormValues($fv);
834 // restructure for easier testing
835 $modparams = array();
836 foreach ($params as $p) {
837 $modparams[$p[0]] = $p;
840 // Check member_end_date_low is in params
841 $this->assertTrue(is_array($modparams['member_end_date_low']));
842 // ... fv and params should match
843 $this->assertEquals($modparams['member_end_date_low'][2], $fv['member_end_date_low']);
844 // ... fv & fv_orig should be different
845 $this->assertNotEquals($fv['member_end_date_low'], $fv_orig['member_end_date_low']);
847 // same for member_end_date_high
848 $this->assertTrue(is_array($modparams['member_end_date_high']));
849 $this->assertEquals($modparams['member_end_date_high'][2], $fv['member_end_date_high']);
850 $this->assertNotEquals($fv['member_end_date_high'], $fv_orig['member_end_date_high']);
852 // Check other fv values are in params
853 $this->assertEquals($modparams['membership_is_current_member'][2], $fv_orig['membership_is_current_member']);
854 $this->assertEquals($modparams['member_is_primary'][2], $fv_orig['member_is_primary']);
858 * Create contributions to test summary calculations.
860 * financial type | cancel_date |total_amount| source | line_item_financial_types |number_line_items| line_amounts
861 * Donation |NULL | 100.00 |SSF | Donation | 1 | 100.00
862 * Member Dues |NULL | 100.00 |SSF | Member Dues | 1 | 100.00
863 * Donation |NULL | 300.00 |SSF | Event Fee,Event Fee | 2 | 200.00,100.00
864 * Donation |NULL | 300.00 |SSF | Event Fee,Donation | 2 | 200.00,100.00
865 * Donation |NULL | 300.00 |SSF | Donation,Donation | 2 | 200.00,100.00
866 * Donation |2019-02-13 00:00:00 | 50.00 |SSF | Donation | 1 | 50.00
867 * Member Dues |2019-02-13 00:00:00 | 50.00 |SSF | Member Dues | 1 | 50.00
869 protected function createContributionsForSummaryQueryTests() {
870 $contactID = $this->individualCreate();
871 $this->contributionCreate(['contact_id' => $contactID]);
872 $this->contributionCreate([
873 'contact_id' => $contactID,
874 'total_amount' => 100,
875 'financial_type_id' => 'Member Dues',
877 $eventFeeType = CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Event Fee');
878 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF']);
879 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
880 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
883 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
884 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
885 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
887 $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF', 'financial_type_id' => $eventFeeType], [
888 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
889 CRM_Core_PseudoConstant
::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
891 $this->contributionCreate([
892 'contact_id' => $contactID,
893 'total_amount' => 50,
894 'contribution_status_id' => 'Cancelled',
895 'cancel_date' => 'yesterday',
897 $this->contributionCreate([
898 'contact_id' => $contactID,
899 'total_amount' => 50,
900 'contribution_status_id' => 'Cancelled',
901 'cancel_date' => 'yesterday',
902 'financial_type_id' => 'Member Dues',