tests/phpunit/CRM/Contact/BAO/QueryTest.php

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