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