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