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

   1 <?php
   2
   3 /**
   4  *  Include dataProvider for tests
   5  *
   6  * @group headless
   7  */
   8 class CRM_Contact_BAO_QueryTest extends CiviUnitTestCase {
   9
  10   use CRMTraits_Financial_FinancialACLTrait;
  11   use CRMTraits_Financial_PriceSetTrait;
  12
  13   /**
  14    * @return CRM_Contact_BAO_QueryTestDataProvider
  15    */
  16   public function dataProvider() {
  17     return new CRM_Contact_BAO_QueryTestDataProvider();
  18   }
  19
  20   public function setUp() {
  21     parent::setUp();
  22   }
  23
  24   /**
  25    * Clean up after test.
  26    *
  27    * @throws \Exception
  28    */
  29   public function tearDown() {
  30     $this->quickCleanUpFinancialEntities();
  31     $tablesToTruncate = [
  32       'civicrm_group_contact',
  33       'civicrm_group',
  34       'civicrm_saved_search',
  35       'civicrm_entity_tag',
  36       'civicrm_tag',
  37       'civicrm_contact',
  38       'civicrm_address',
  39     ];
  40     $this->quickCleanup($tablesToTruncate);
  41     parent::tearDown();
  42   }
  43
  44   /**
  45    *  Test CRM_Contact_BAO_Query::searchQuery().
  46    *
  47    * @dataProvider dataProvider
  48    *
  49    * @param $fv
  50    * @param $count
  51    * @param $ids
  52    *
  53    * @throws \CRM_Core_Exception
  54    */
  55   public function testSearch($fv, $count, $ids) {
  56     $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";}']);
  57     $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";}']);
  58
  59     $tag7 = $this->ids['Tag'][7] = $this->tagCreate(['name' => 'Test Tag 7', 'description' => 'Test Tag 7'])['id'];
  60     $tag9 = $this->ids['Tag'][9] = $this->tagCreate(['name' => 'Test Tag 9', 'description' => 'Test Tag 9'])['id'];
  61     $this->tagCreate(['name' => 'Test Tag 10']);
  62     $groups = [
  63       3 => ['name' => 'Test Group 3'],
  64       4 => ['name' => 'Test Smart Group 4', 'saved_search_id' => 1],
  65       5 => ['name' => 'Test Group 5'],
  66       6 => ['name' => 'Test Smart Group 6', 'saved_search_id' => 2],
  67     ];
  68
  69     foreach ($groups as $id => $group) {
  70       $this->ids['Group'][$id] = $this->groupCreate(array_merge($group, ['title' => $group['name']]));
  71     }
  72     $individuals = [
  73       ['first_name' => 'Test', 'last_name' => 'Test Contact 9', 'gender_id' => 1, 'prefix_id' => 1, 'suffix_id' => 1],
  74       ['first_name' => 'Test', 'last_name' => 'Test Contact 10', 'gender_id' => 2, 'prefix_id' => 2, 'suffix_id' => 2, 'api.entity_tag.create' => ['tag_id' => $tag9]],
  75       ['first_name' => 'Test', 'last_name' => 'Test Contact 11', 'gender_id' => 3, 'prefix_id' => 3, 'suffix_id' => 3, 'api.entity_tag.create' => ['tag_id' => $tag7]],
  76       [
  77         'first_name' => 'Test',
  78         'last_name' => 'Test Contact 12',
  79         'gender_id' => 3,
  80         'prefix_id' => 4,
  81         'suffix_id' => 4,
  82         'api.entity_tag.create' => ['tag_id' => $tag9],
  83         'api.entity_tag.create.2' => ['tag_id' => $tag7],
  84       ],
  85       ['first_name' => 'Test', 'last_name' => 'Test Contact 13', 'gender_id' => 2, 'prefix_id' => 2, 'suffix_id' => 2],
  86       ['first_name' => 'Test', 'last_name' => 'Test Contact 14', 'gender_id' => 3, 'prefix_id' => 4, 'suffix_id' => 4, 'api.entity_tag.create' => ['tag_id' => $tag9]],
  87       ['first_name' => 'Test', 'last_name' => 'Test Contact 15', 'gender_id' => 3, 'prefix_id' => 4, 'suffix_id' => 5, 'api.entity_tag.create' => ['tag_id' => $tag7]],
  88       [
  89         'first_name' => 'Test',
  90         'last_name' => 'Test Contact 16',
  91         'gender_id' => 3,
  92         'prefix_id' => 4,
  93         'suffix_id' => 6,
  94         'api.entity_tag.create' => ['tag_id' => $tag9],
  95         'api.entity_tag.create.2' => ['tag_id' => $tag7],
  96       ],
  97       ['first_name' => 'Test', 'last_name' => 'Test Contact 17', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 7],
  98       ['first_name' => 'Test', 'last_name' => 'Test Contact 18', 'gender_id' => 2, 'prefix_id' => 4, 'suffix_id' => 4, 'api.entity_tag.create' => ['tag_id' => $tag9]],
  99       ['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]],
 100       [
 101         'first_name' => 'Test',
 102         'last_name' => 'Test Contact 20',
 103         'gender_id' => 1,
 104         'prefix_id' => 4,
 105         'suffix_id' => 6,
 106         'api.entity_tag.create' => ['tag_id' => $tag9],
 107         'api.entity_tag.create.2' => ['tag_id' => $tag7],
 108       ],
 109       ['first_name' => 'Test', 'last_name' => 'Test Contact 21', 'gender_id' => 3, 'prefix_id' => 1, 'suffix_id' => 6],
 110       ['first_name' => 'Test', 'last_name' => 'Test Contact 22', 'gender_id' => 1, 'prefix_id' => 1, 'suffix_id' => 1, 'api.entity_tag.create' => ['tag_id' => $tag9]],
 111       ['first_name' => 'Test', 'last_name' => 'Test Contact 23', 'gender_id' => 3, 'prefix_id' => 1, 'suffix_id' => 1, 'api.entity_tag.create' => ['tag_id' => $tag7]],
 112       [
 113         'first_name' => 'Test',
 114         'last_name' => 'Test Contact 24',
 115         'gender_id' => 3,
 116         'prefix_id' => 3,
 117         'suffix_id' => 2,
 118         'api.entity_tag.create' => ['tag_id' => $tag9],
 119         'api.entity_tag.create.2' => ['tag_id' => $tag7],
 120       ],
 121     ];
 122     foreach ($individuals as $individual) {
 123       $this->ids['Contact'][$individual['last_name']] = $this->individualCreate($individual);
 124     }
 125     $groupContacts = [
 126       [5 => 13],
 127       [5 => 14],
 128       [5 => 15],
 129       [5 => 16],
 130       [5 => 21],
 131       [5 => 22],
 132       [5 => 23],
 133       [5 => 24],
 134       [3 => 17],
 135       [3 => 18],
 136       [3 => 19],
 137       [3 => 20],
 138       [3 => 21],
 139       [3 => 22],
 140       [3 => 23],
 141       [3 => 24],
 142     ];
 143     foreach ($groupContacts as $group) {
 144       $groupID = $this->ids['Group'][key($group)];
 145       $contactID = $this->ids['Contact']['Test Contact ' . reset($group)];
 146       $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID, 'status' => 'Added']);
 147     }
 148
 149     // We have migrated from a hard-coded dataset to a dynamic one but are still working with the same
 150     // dataprovider at this stage -> wrangle.
 151     foreach ($fv as $key => $value) {
 152       $entity = ucfirst($key);
 153       if (!array_key_exists($entity, $this->ids)) {
 154         continue;
 155       }
 156       if (is_numeric($value)) {
 157         $fv[$key] = $this->ids[$entity][$value];
 158       }
 159       elseif (!empty($value[0])) {
 160         foreach ($value as $index => $oldGroup) {
 161           $fv[$key][$index] = $this->ids[$entity][$oldGroup];
 162         }
 163       }
 164       else {
 165         foreach (array_keys($value) as $index) {
 166           unset($fv[$key][$index]);
 167           $fv[$key][$this->ids[$entity][$index]] = 1;
 168         }
 169       }
 170     }
 171
 172     $params = CRM_Contact_BAO_Query::convertFormValues($fv);
 173     $obj = new CRM_Contact_BAO_Query($params);
 174
 175     // let's set useGroupBy=true since we are listing contacts here who might belong to
 176     // more than one group / tag / notes etc.
 177     $obj->_useGroupBy = TRUE;
 178
 179     $dao = $obj->searchQuery();
 180
 181     $contacts = [];
 182     while ($dao->fetch()) {
 183       $contacts[] = $dao->contact_id;
 184     }
 185
 186     sort($contacts, SORT_NUMERIC);
 187
 188     $expectedIDs = [];
 189     foreach ($ids as $id) {
 190       $expectedIDs[] = $this->ids['Contact']['Test Contact ' . $id];
 191     }
 192     $this->assertEquals($expectedIDs, $contacts);
 193   }
 194
 195   /**
 196    * Check that we get a successful result querying for home address.
 197    * CRM-14263 search builder failure with search profile & address in criteria
 198    *
 199    * @throws \CRM_Core_Exception
 200    */
 201   public function testSearchProfileHomeCityCRM14263() {
 202     $contactID = $this->individualCreate();
 203     Civi::settings()->set('defaultSearchProfileID', 1);
 204     $this->callAPISuccess('address', 'create', [
 205       'contact_id' => $contactID,
 206       'city' => 'Cool City',
 207       'location_type_id' => 1,
 208     ]);
 209     $params = [
 210       0 => [
 211         0 => 'city-1',
 212         1 => '=',
 213         2 => 'Cool City',
 214         3 => 1,
 215         4 => 0,
 216       ],
 217     ];
 218     $returnProperties = [
 219       'contact_type' => 1,
 220       'contact_sub_type' => 1,
 221       'sort_name' => 1,
 222     ];
 223
 224     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 225     try {
 226       $resultDAO = $queryObj->searchQuery();
 227       $this->assertTrue($resultDAO->fetch());
 228     }
 229     catch (PEAR_Exception $e) {
 230       $err = $e->getCause();
 231       $this->fail('invalid SQL created' . $e->getMessage() . ' ' . $err->userinfo);
 232
 233     }
 234   }
 235
 236   /**
 237    * Check that we get a successful result querying for home address.
 238    * CRM-14263 search builder failure with search profile & address in criteria
 239    *
 240    * @throws \CRM_Core_Exception
 241    */
 242   public function testSearchProfileHomeCityNoResultsCRM14263() {
 243     $contactID = $this->individualCreate();
 244     Civi::settings()->set('defaultSearchProfileID', 1);
 245     $this->callAPISuccess('address', 'create', [
 246       'contact_id' => $contactID,
 247       'city' => 'Cool City',
 248       'location_type_id' => 1,
 249     ]);
 250     $params = [
 251       0 => [
 252         0 => 'city-1',
 253         1 => '=',
 254         2 => 'Dumb City',
 255         3 => 1,
 256         4 => 0,
 257       ],
 258     ];
 259     $returnProperties = [
 260       'contact_type' => 1,
 261       'contact_sub_type' => 1,
 262       'sort_name' => 1,
 263     ];
 264
 265     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 266     try {
 267       $resultDAO = $queryObj->searchQuery();
 268       $this->assertFalse($resultDAO->fetch());
 269     }
 270     catch (PEAR_Exception $e) {
 271       $err = $e->getCause();
 272       $this->fail('invalid SQL created' . $e->getMessage() . ' ' . $err->userinfo);
 273
 274     }
 275   }
 276
 277   /**
 278    * Test searchPrimaryDetailsOnly setting.
 279    *
 280    * @throws \CRM_Core_Exception
 281    */
 282   public function testSearchPrimaryLocTypes() {
 283     $contactID = $this->individualCreate();
 284     $params = [
 285       'contact_id' => $contactID,
 286       'email' => 'primary@example.com',
 287       'is_primary' => 1,
 288     ];
 289     $this->callAPISuccess('email', 'create', $params);
 290
 291     unset($params['is_primary']);
 292     $params['email'] = 'secondary@team.com';
 293     $this->callAPISuccess('email', 'create', $params);
 294
 295     foreach ([0, 1] as $searchPrimary) {
 296       Civi::settings()->set('searchPrimaryDetailsOnly', $searchPrimary);
 297
 298       $params = [
 299         0 => [
 300           0 => 'email',
 301           1 => 'LIKE',
 302           2 => 'sEcondary@example.com',
 303           3 => 0,
 304           4 => 1,
 305         ],
 306       ];
 307       $returnProperties = [
 308         'contact_type' => 1,
 309         'contact_sub_type' => 1,
 310         'sort_name' => 1,
 311       ];
 312
 313       $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 314       $resultDAO = $queryObj->searchQuery();
 315
 316       if ($searchPrimary) {
 317         $this->assertEquals($resultDAO->N, 0);
 318       }
 319       else {
 320         //Assert secondary email gets included in search results.
 321         while ($resultDAO->fetch()) {
 322           $this->assertEquals('secondary@example.com', $resultDAO->email);
 323         }
 324       }
 325
 326       // API should always return primary email.
 327       $result = $this->callAPISuccess('Contact', 'get', ['contact_id' => $contactID]);
 328       $this->assertEquals('primary@example.com', $result['values'][$contactID]['email']);
 329     }
 330   }
 331
 332   /**
 333    *  Test created to prove failure of search on state when location
 334    *  display name is different form location name (issue 607)
 335    *
 336    * @throws \CRM_Core_Exception
 337    */
 338   public function testSearchOtherLocationUpperLower() {
 339
 340     $params = [
 341       0 => [
 342         0 => 'state_province-4',
 343         1 => 'IS NOT EMPTY',
 344         2 => '',
 345         3 => 1,
 346         4 => 0,
 347       ],
 348     ];
 349     $returnProperties = [
 350       'contact_type' => 1,
 351       'contact_sub_type' => 1,
 352       'sort_name' => 1,
 353       'location' => [
 354         'other' => [
 355           'location_type' => 4,
 356           'state_province' => 1,
 357         ],
 358       ],
 359     ];
 360
 361     // update with the api does not work because it updates both the name and the
 362     // the display_name. Plain SQL however does the job
 363     CRM_Core_DAO::executeQuery('update civicrm_location_type set name=%2 where id=%1',
 364       [
 365         1 => [4, 'Integer'],
 366         2 => ['other', 'String'],
 367       ]);
 368
 369     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 370
 371     $resultDAO = $queryObj->searchQuery();
 372     $resultDAO->fetch();
 373   }
 374
 375   /**
 376    * CRM-14263 search builder failure with search profile & address in criteria.
 377    *
 378    * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
 379    * so few tests detecting any change seems good here :-)
 380    *
 381    * @dataProvider getSearchProfileData
 382    *
 383    * @param array $params
 384    * @param string $selectClause
 385    * @param string $whereClause
 386    *
 387    * @throws \CRM_Core_Exception
 388    */
 389   public function testSearchProfilePrimaryCityCRM14263($params, $selectClause, $whereClause) {
 390     $contactID = $this->individualCreate();
 391     Civi::settings()->set('defaultSearchProfileID', 1);
 392     $this->callAPISuccess('address', 'create', [
 393       'contact_id' => $contactID,
 394       'city' => 'Cool CITY',
 395       'street_address' => 'Long STREET',
 396       'location_type_id' => 1,
 397     ]);
 398     $returnProperties = [
 399       'contact_type' => 1,
 400       'contact_sub_type' => 1,
 401       'sort_name' => 1,
 402     ];
 403     $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` ";
 404     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 405     try {
 406       $this->assertLike($expectedSQL, $queryObj->getSearchSQL());
 407       list($select, $from, $where, $having) = $queryObj->query();
 408       $dao = CRM_Core_DAO::executeQuery("$select $from $where $having");
 409       $dao->fetch();
 410       $this->assertEquals('Anderson, Anthony', $dao->sort_name);
 411     }
 412     catch (PEAR_Exception $e) {
 413       $err = $e->getCause();
 414       $this->fail('invalid SQL created' . $e->getMessage() . ' ' . $err->userinfo);
 415
 416     }
 417   }
 418
 419   /**
 420    * Get data sets to test for search.
 421    */
 422   public function getSearchProfileData() {
 423     return [
 424       [
 425         [['city', '=', 'Cool City', 1, 0]],
 426         'civicrm_address.city as `city`',
 427         "civicrm_address.city = 'Cool City'",
 428       ],
 429       [
 430         // Note that in the query 'long street' is lower cased. We eventually want to change that & not mess with the vars - it turns out
 431         // it doesn't work on some charsets. However, the the lcasing affects more vars & we are looking to stagger removal of lcasing 'in case'
 432         // (although we have been removing without blowback since 2017)
 433         [['street_address', '=', 'Long Street', 1, 0]],
 434         'civicrm_address.street_address as `street_address`',
 435         "civicrm_address.street_address LIKE '%Long Street%'",
 436       ],
 437     ];
 438   }
 439
 440   /**
 441    * Test similarly handled activity fields qill and where clauses.
 442    *
 443    * @throws \CRM_Core_Exception
 444    */
 445   public function testSearchBuilderActivityType() {
 446     $queryObj = new CRM_Contact_BAO_Query([['activity_type', '=', '3', 1, 0]]);
 447     $this->assertContains('WHERE  (  ( civicrm_activity.activity_type_id = 3 )', $queryObj->getSearchSQL());
 448     $this->assertEquals('Activity Type = Email', $queryObj->_qill[1][0]);
 449
 450     $queryObj = new CRM_Contact_BAO_Query([['activity_type_id', '=', '3', 1, 0]]);
 451     $this->assertContains('WHERE  (  ( civicrm_activity.activity_type_id = 3 )', $queryObj->getSearchSQL());
 452     $this->assertEquals('Activity Type ID = Email', $queryObj->_qill[1][0]);
 453
 454     $queryObj = new CRM_Contact_BAO_Query([['activity_status', '=', '3', 1, 0]]);
 455     $this->assertContains('WHERE  (  ( civicrm_activity.status_id = 3 )', $queryObj->getSearchSQL());
 456     $this->assertEquals('Activity Status = Cancelled', $queryObj->_qill[1][0]);
 457
 458     $queryObj = new CRM_Contact_BAO_Query([['activity_status_id', '=', '3', 1, 0]]);
 459     $this->assertContains('WHERE  (  ( civicrm_activity.status_id = 3 )', $queryObj->getSearchSQL());
 460     $this->assertEquals('Activity Status = Cancelled', $queryObj->_qill[1][0]);
 461
 462     $queryObj = new CRM_Contact_BAO_Query([['activity_engagement_level', '=', '3', 1, 0]]);
 463     $this->assertContains('WHERE  (  ( civicrm_activity.engagement_level = 3 )', $queryObj->getSearchSQL());
 464     $this->assertEquals('Engagement Index = 3', $queryObj->_qill[1][0]);
 465
 466     $queryObj = new CRM_Contact_BAO_Query([['activity_id', '=', '3', 1, 0]]);
 467     $this->assertContains('WHERE  (  ( civicrm_activity.id = 3 )', $queryObj->getSearchSQL());
 468     $this->assertEquals('Activity ID = 3', $queryObj->_qill[1][0]);
 469
 470     $queryObj = new CRM_Contact_BAO_Query([['activity_campaign_id', '=', '3', 1, 0]]);
 471     $this->assertContains('WHERE  (  ( civicrm_activity.campaign_id = 3 )', $queryObj->getSearchSQL());
 472     $this->assertEquals('Campaign = 3', $queryObj->_qill[1][0]);
 473
 474     $queryObj = new CRM_Contact_BAO_Query([['activity_priority_id', '=', '3', 1, 0]]);
 475     $this->assertContains('WHERE  (  ( civicrm_activity.priority_id = 3 )', $queryObj->getSearchSQL());
 476     $this->assertEquals('Priority = Low', $queryObj->_qill[1][0]);
 477
 478     $queryObj = new CRM_Contact_BAO_Query([['activity_subject', '=', '3', 1, 0]]);
 479     $this->assertContains("WHERE  (  ( civicrm_activity.subject = '3' )", $queryObj->getSearchSQL());
 480     $this->assertEquals("Subject = '3'", $queryObj->_qill[1][0]);
 481   }
 482
 483   /**
 484    * Test set up to test calling the query object per GroupContactCache BAO usage.
 485    *
 486    * CRM-17254 ensure that if only the contact_id is required other fields should
 487    * not be appended.
 488    *
 489    * @throws \CRM_Core_Exception
 490    */
 491   public function testGroupContactCacheAddSearch() {
 492     $returnProperties = ['contact_id'];
 493     $params = [['group', 'IN', [1], 0, 0]];
 494
 495     $query = new CRM_Contact_BAO_Query(
 496       $params, $returnProperties,
 497       NULL, TRUE, FALSE, 1,
 498       TRUE,
 499       TRUE, FALSE
 500     );
 501
 502     list($select) = $query->query();
 503     $this->assertEquals('SELECT contact_a.id as contact_id', $select);
 504   }
 505
 506   /**
 507    * Test smart groups with non-numeric don't fail on range queries.
 508    *
 509    * CRM-14720
 510    *
 511    * @throws \CRM_Core_Exception
 512    */
 513   public function testNumericPostal() {
 514     // Precaution as hitting some inconsistent set up running in isolation vs in the suite.
 515     CRM_Core_DAO::executeQuery('UPDATE civicrm_address SET postal_code = NULL');
 516
 517     $this->individualCreate(['api.address.create' => ['postal_code' => 5, 'location_type_id' => 'Main']]);
 518     $this->individualCreate(['api.address.create' => ['postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main']]);
 519     $this->individualCreate(['api.address.create' => ['postal_code' => '4', 'location_type_id' => 'Main']]);
 520     $this->individualCreate(['api.address.create' => ['postal_code' => '6', 'location_type_id' => 'Main']]);
 521     $this->individualCreate(['api.address.create' => ['street_address' => 'just a street', 'location_type_id' => 'Main']]);
 522     $this->individualCreate(['api.address.create' => ['postal_code' => '12345678444455555555555555555555555555555555551314151617181920', 'location_type_id' => 'Main']]);
 523
 524     $params = [['postal_code_low', '=', 5, 0, 0]];
 525     CRM_Contact_BAO_Query::convertFormValues($params);
 526
 527     $query = new CRM_Contact_BAO_Query(
 528       $params, ['contact_id'],
 529       NULL, TRUE, FALSE, 1,
 530       TRUE,
 531       TRUE, FALSE
 532     );
 533
 534     $sql = $query->query();
 535     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 536     $this->assertEquals(2, $result->N);
 537
 538     // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
 539     // results in mysql warnings & hence fatal errors.
 540     /// I was unable to get mysql warnings to activate in the context of the unit tests - but
 541     // felt this code still provided a useful bit of coverage as it runs the various queries to load
 542     // the group & could generate invalid sql if a bug were introduced.
 543     $groupParams = ['title' => 'postal codes', 'formValues' => $params, 'is_active' => 1];
 544     $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams);
 545     CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
 546   }
 547
 548   /**
 549    * Test searches are case insensitive.
 550    *
 551    * @throws \CRM_Core_Exception
 552    */
 553   public function testCaseInsensitive() {
 554     $orgID = $this->organizationCreate(['organization_name' => 'BOb']);
 555     $params = [
 556       'display_name' => 'Minnie Mouse',
 557       'first_name' => 'Minnie',
 558       'last_name' => 'Mouse',
 559       'employer_id' => $orgID,
 560       'contact_type' => 'Individual',
 561       'nick_name' => 'Mins',
 562     ];
 563     $this->callAPISuccess('Contact', 'create', $params);
 564     unset($params['contact_type']);
 565     foreach ($params as $key => $value) {
 566       if ($key === 'employer_id') {
 567         $searchParams = [['current_employer', '=', 'bob', 0, 1]];
 568       }
 569       else {
 570         $searchParams = [[$key, '=', strtolower($value), 0, 1]];
 571       }
 572
 573       $result = CRM_Contact_BAO_Query::apiQuery($searchParams);
 574       $this->assertCount(1, $result[0], 'search for ' . $key);
 575       $contact = reset($result[0]);
 576       $this->assertEquals('Minnie Mouse', $contact['display_name']);
 577       $this->assertEquals('BOb', $contact['current_employer']);
 578     }
 579   }
 580
 581   /**
 582    * Test smart groups with non-numeric don't fail on equal queries.
 583    *
 584    * CRM-14720
 585    *
 586    * @throws \CRM_Core_Exception
 587    */
 588   public function testNonNumericEqualsPostal() {
 589     $this->individualCreate(['api.address.create' => ['postal_code' => 5, 'location_type_id' => 'Main']]);
 590     $this->individualCreate(['api.address.create' => ['postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main']]);
 591     $this->individualCreate(['api.address.create' => ['postal_code' => '4', 'location_type_id' => 'Main']]);
 592     $this->individualCreate(['api.address.create' => ['postal_code' => '6', 'location_type_id' => 'Main']]);
 593
 594     $params = [['postal_code', '=', 'EH10 4RB-889', 0, 0]];
 595     CRM_Contact_BAO_Query::convertFormValues($params);
 596
 597     $query = new CRM_Contact_BAO_Query(
 598       $params, ['contact_id'],
 599       NULL, TRUE, FALSE, 1,
 600       TRUE,
 601       TRUE, FALSE
 602     );
 603
 604     $sql = $query->query(FALSE);
 605     $this->assertEquals("WHERE  ( civicrm_address.postal_code = 'EH10 4RB-889' )  AND (contact_a.is_deleted = 0)", $sql[2]);
 606     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 607     $this->assertEquals(1, $result->N);
 608
 609   }
 610
 611   /**
 612    * Test relationship description.
 613    *
 614    * @throws \CRM_Core_Exception
 615    */
 616   public function testRelationshipDescription() {
 617     $relType = $this->callAPISuccess('RelationshipType', 'create', [
 618       'name_a_b' => 'blah',
 619       'name_b_a' => 'other blah',
 620     ]);
 621     $contactID_a = $this->individualCreate([], 1);
 622     $contactID_b = $this->individualCreate([], 2);
 623     $contactID_c = $this->individualCreate([], 3);
 624     $contactID_d = $this->individualCreate([], 4);
 625     $desc = uniqid('rel', TRUE);
 626     $this->callAPISuccess('Relationship', 'create', [
 627       'contact_id_a' => $contactID_a,
 628       'contact_id_b' => $contactID_b,
 629       'relationship_type_id' => $relType['id'],
 630       'is_active' => 1,
 631       'description' => $desc,
 632     ]);
 633     $this->callAPISuccess('Relationship', 'create', [
 634       'contact_id_a' => $contactID_c,
 635       'contact_id_b' => $contactID_d,
 636       'relationship_type_id' => $relType['id'],
 637       'is_active' => 1,
 638       'description' => 'nothing of interest',
 639     ]);
 640     $params = [
 641       ['relation_description', '=', substr($desc, 3, 18), 0, 0],
 642     ];
 643
 644     $query = new CRM_Contact_BAO_Query($params);
 645     $dao = $query->searchQuery();
 646     // This is a little weird but seems consistent with the behavior of the search form in general.
 647     // Technically there are 2 contacts who share a relationship with the description searched for,
 648     // so one might expect the search form to return both of them instead of just Contact A... but it doesn't.
 649     $this->assertEquals('1', $dao->N, 'Search query returns exactly 1 result?');
 650     $this->assertTrue($dao->fetch(), 'Search query returns success?');
 651     $this->assertEquals($contactID_a, $dao->contact_id, 'Search query returns contact A?');
 652   }
 653
 654   /**
 655    * Test non-reciprocal relationship.
 656    *
 657    * @throws \CRM_Core_Exception
 658    */
 659   public function testNonReciprocalRelationshipTargetGroupIsCorrectResults() {
 660     $contactID_a = $this->individualCreate();
 661     $contactID_b = $this->individualCreate();
 662     $this->callAPISuccess('Relationship', 'create', [
 663       'contact_id_a' => $contactID_a,
 664       'contact_id_b' => $contactID_b,
 665       'relationship_type_id' => 1,
 666       'is_active' => 1,
 667     ]);
 668     // Create a group and add contact A to it.
 669     $groupID = $this->groupCreate();
 670     $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added']);
 671
 672     // Add another (sans-relationship) contact to the group,
 673     $contactID_c = $this->individualCreate();
 674     $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added']);
 675
 676     $params = [
 677       [
 678         0 => 'relation_type_id',
 679         1 => 'IN',
 680         2 =>
 681           [
 682             0 => '1_b_a',
 683           ],
 684         3 => 0,
 685         4 => 0,
 686       ],
 687       [
 688         0 => 'relation_target_group',
 689         1 => 'IN',
 690         2 =>
 691           [
 692             0 => $groupID,
 693           ],
 694         3 => 0,
 695         4 => 0,
 696       ],
 697     ];
 698
 699     $query = new CRM_Contact_BAO_Query($params);
 700     $dao = $query->searchQuery();
 701     $this->assertEquals('1', $dao->N, 'Search query returns exactly 1 result?');
 702     $this->assertTrue($dao->fetch(), 'Search query returns success?');
 703     $this->assertEquals($contactID_b, $dao->contact_id, 'Search query returns parent of contact A?');
 704   }
 705
 706   /**
 707    * Relationship search with custom fields.
 708    *
 709    * @throws \CRM_Core_Exception
 710    */
 711   public function testReciprocalRelationshipWithCustomFields() {
 712     $params = [
 713       'extends' => 'Relationship',
 714     ];
 715     $customGroup = $this->customGroupCreate($params);
 716     $customFieldId = $this->customFieldCreate(['custom_group_id' => $customGroup['id']])['id'];
 717     $contactID_a = $this->individualCreate();
 718     $contactID_b = $this->individualCreate();
 719     $relationship = $this->callAPISuccess('Relationship', 'create', [
 720       'contact_id_a' => $contactID_a,
 721       'contact_id_b' => $contactID_b,
 722       'relationship_type_id' => 2,
 723       'is_active' => 1,
 724       "custom_{$customFieldId}" => 'testvalue',
 725     ]);
 726     $params = [
 727       [
 728         0 => 'relation_type_id',
 729         1 => 'IN',
 730         2 =>
 731           [
 732             0 => '2_a_b',
 733           ],
 734         3 => 0,
 735         4 => 0,
 736       ],
 737       [
 738         0 => "custom_{$customFieldId}",
 739         1 => '=',
 740         2 => 'testvalue',
 741         3 => 0,
 742         4 => 0,
 743       ],
 744     ];
 745
 746     $query = new CRM_Contact_BAO_Query($params);
 747     $dao = $query->searchQuery();
 748     $this->assertEquals('2', $dao->N);
 749     $this->callAPISuccess('Relationship', 'delete', ['id' => $relationship['id']]);
 750     $this->callAPISuccess('Contact', 'delete', ['id' => $contactID_a, 'skip_undelete' => 1]);
 751     $this->callAPISuccess('Contact', 'delete', ['id' => $contactID_b, 'skip_undelete' => 1]);
 752     $this->callAPISuccess('CustomField', 'delete', ['id' => $customFieldId, 'skip_undelete' => 1]);
 753     $this->callAPISuccess('CustomGroup', 'delete', ['id' => $customGroup]);
 754   }
 755
 756   /**
 757    * @throws \CRM_Core_Exception
 758    */
 759   public function testReciprocalRelationshipTargetGroupIsCorrectResults() {
 760     $contactID_a = $this->individualCreate();
 761     $contactID_b = $this->individualCreate();
 762     $this->callAPISuccess('Relationship', 'create', [
 763       'contact_id_a' => $contactID_a,
 764       'contact_id_b' => $contactID_b,
 765       'relationship_type_id' => 2,
 766       'is_active' => 1,
 767     ]);
 768     // Create a group and add contact A to it.
 769     $groupID = $this->groupCreate();
 770     $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_a, 'status' => 'Added']);
 771
 772     // Add another (sans-relationship) contact to the group,
 773     $contactID_c = $this->individualCreate();
 774     $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $contactID_c, 'status' => 'Added']);
 775
 776     $params = [
 777       [
 778         0 => 'relation_type_id',
 779         1 => 'IN',
 780         2 =>
 781           [
 782             0 => '2_a_b',
 783           ],
 784         3 => 0,
 785         4 => 0,
 786       ],
 787       [
 788         0 => 'relation_target_group',
 789         1 => 'IN',
 790         2 =>
 791           [
 792             0 => $groupID,
 793           ],
 794         3 => 0,
 795         4 => 0,
 796       ],
 797     ];
 798
 799     $query = new CRM_Contact_BAO_Query($params);
 800     $dao = $query->searchQuery();
 801     $this->assertEquals('1', $dao->N, 'Search query returns exactly 1 result?');
 802     $this->assertTrue($dao->fetch(), 'Search query returns success?');
 803     $this->assertEquals($contactID_b, $dao->contact_id, 'Search query returns spouse of contact A?');
 804   }
 805
 806   /**
 807    * Test correct temporary table in reciprocal relationship search.
 808    *
 809    * @throws \CRM_Core_Exception
 810    */
 811   public function testReciprocalRelationshipTargetGroupUsesTempTable() {
 812     $groupID = $this->groupCreate();
 813     $params = [
 814       [
 815         0 => 'relation_type_id',
 816         1 => 'IN',
 817         2 =>
 818           [
 819             0 => '2_a_b',
 820           ],
 821         3 => 0,
 822         4 => 0,
 823       ],
 824       [
 825         0 => 'relation_target_group',
 826         1 => 'IN',
 827         2 =>
 828           [
 829             0 => $groupID,
 830           ],
 831         3 => 0,
 832         4 => 0,
 833       ],
 834     ];
 835     $sql = CRM_Contact_BAO_Query::getQuery($params);
 836     $this->assertContains('INNER JOIN civicrm_tmp_e', $sql, 'Query appears to use temporary table of compiled relationships?', TRUE);
 837   }
 838
 839   /**
 840    * Test relationship permission clause.
 841    *
 842    * @throws \CRM_Core_Exception
 843    */
 844   public function testRelationshipPermissionClause() {
 845     $params = [['relation_type_id', 'IN', ['1_b_a'], 0, 0], ['relation_permission', 'IN', [2], 0, 0]];
 846     $sql = CRM_Contact_BAO_Query::getQuery($params);
 847     $this->assertContains('(civicrm_relationship.is_permission_a_b IN (2))', $sql);
 848   }
 849
 850   /**
 851    * Test Relationship Clause
 852    *
 853    * @throws \CRM_Core_Exception
 854    */
 855   public function testRelationshipClause() {
 856     $today = date('Ymd');
 857     $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 )';
 858     $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 )';
 859     $where1 = "WHERE  ( (
 860 civicrm_relationship.is_active = 1 AND
 861 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
 862 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
 863 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8) )  AND (contact_a.is_deleted = 0)";
 864     $where2 = "WHERE  ( (
 865 civicrm_relationship.is_active = 1 AND
 866 ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND
 867 ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )
 868 ) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id IN (8,10) )  AND (contact_a.is_deleted = 0)";
 869     // Test Traditional single select format
 870     $params1 = [['relation_type_id', '=', '8_a_b', 0, 0]];
 871     $query1 = new CRM_Contact_BAO_Query(
 872       $params1, ['contact_id'],
 873       NULL, TRUE, FALSE, 1,
 874       TRUE,
 875       TRUE, FALSE
 876     );
 877     $sql1 = $query1->query();
 878     $this->assertLike($from1, $sql1[1]);
 879     $this->assertLike($where1, $sql1[2]);
 880     // Test single relationship type selected in multiple select.
 881     $params2 = [['relation_type_id', 'IN', ['8_a_b'], 0, 0]];
 882     $query2 = new CRM_Contact_BAO_Query(
 883       $params2, ['contact_id'],
 884       NULL, TRUE, FALSE, 1,
 885       TRUE,
 886       TRUE, FALSE
 887     );
 888     $sql2 = $query2->query(FALSE);
 889     $this->assertLike($from1, $sql2[1]);
 890     $this->assertLike($where1, $sql2[2]);
 891     // Test multiple relationship types selected.
 892     $params3 = [['relation_type_id', 'IN', ['8_a_b', '10_a_b'], 0, 0]];
 893     $query3 = new CRM_Contact_BAO_Query(
 894       $params3, ['contact_id'],
 895       NULL, TRUE, FALSE, 1,
 896       TRUE,
 897       TRUE, FALSE
 898     );
 899     $sql3 = $query3->query(FALSE);
 900     $this->assertLike($from1, $sql3[1]);
 901     $this->assertLike($where2, $sql3[2]);
 902     // Test Multiple Relationship type selected where one doesn't actually exist.
 903     $params4 = [['relation_type_id', 'IN', ['8_a_b', '10_a_b', '14_a_b'], 0, 0]];
 904     $query4 = new CRM_Contact_BAO_Query(
 905       $params4, ['contact_id'],
 906       NULL, TRUE, FALSE, 1,
 907       TRUE,
 908       TRUE, FALSE
 909     );
 910     $sql4 = $query4->query();
 911     $this->assertLike($from1, $sql4[1]);
 912     $this->assertLike($where2, $sql4[2]);
 913
 914     // Test Multiple b to a Relationship type  .
 915     $params5 = [['relation_type_id', 'IN', ['8_b_a', '10_b_a', '14_b_a'], 0, 0]];
 916     $query5 = new CRM_Contact_BAO_Query(
 917       $params5, ['contact_id'],
 918       NULL, TRUE, FALSE, 1,
 919       TRUE,
 920       TRUE, FALSE
 921     );
 922     $sql5 = $query5->query(FALSE);
 923     $this->assertLike($from2, $sql5[1]);
 924     $this->assertLike($where2, $sql5[2]);
 925   }
 926
 927   /**
 928    * Test we can narrow a group get by status.
 929    *
 930    * @throws \CRM_Core_Exception
 931    */
 932   public function testGetByGroupWithStatus() {
 933     $groupID = $this->groupCreate();
 934     $this->groupContactCreate($groupID, 3);
 935     $groupContactID = $this->callAPISuccessGetSingle('GroupContact', ['group_id' => $groupID, 'options' => ['limit' => 1]])['id'];
 936     $this->callAPISuccess('GroupContact', 'create', ['id' => $groupContactID, 'status' => 'Removed']);
 937     $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]);
 938     $resultDAO = $queryObj->searchQuery();
 939     $this->assertEquals(1, $resultDAO->N);
 940
 941     $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Added' => 1], 0, 0]]);
 942     $resultDAO = $queryObj->searchQuery();
 943     $this->assertEquals(2, $resultDAO->N);
 944
 945     $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0]]);
 946     $resultDAO = $queryObj->searchQuery();
 947     $this->assertEquals(2, $resultDAO->N);
 948   }
 949
 950   /**
 951    * Test we can narrow a group get by status.
 952    *
 953    * @throws \Exception
 954    */
 955   public function testGetByGroupWithStatusSmartGroup() {
 956     $groupID = $this->smartGroupCreate();
 957     // This means they are actually all hard-added, which is fine for this purpose.
 958     $this->groupContactCreate($groupID, 3);
 959     $groupContactID = $this->callAPISuccessGetSingle('GroupContact', ['group_id' => $groupID, 'options' => ['limit' => 1]])['id'];
 960     $this->callAPISuccess('GroupContact', 'create', ['id' => $groupContactID, 'status' => 'Removed']);
 961
 962     $queryObj = new CRM_Contact_BAO_Query([['group', '=', $groupID, 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]);
 963     $resultDAO = $queryObj->searchQuery();
 964     $this->assertEquals(1, $resultDAO->N);
 965
 966     $queryObj = new CRM_Contact_BAO_Query([['group', 'IS NOT EMPTY', '', 0, 0], ['group_contact_status', 'IN', ['Removed' => 1], 0, 0]]);
 967     $resultDAO = $queryObj->searchQuery();
 968     $this->assertEquals(1, $resultDAO->N);
 969   }
 970
 971   /**
 972    * Test the group contact clause does not contain an OR.
 973    *
 974    * The search should return 3 contacts - 2 households in the smart group of
 975    * Contact Type = Household and one Individual hard-added to it. The
 976    * Household that meets both criteria should be returned once.
 977    *
 978    * @throws \Exception
 979    */
 980   public function testGroupClause() {
 981     $this->householdCreate();
 982     $householdID = $this->householdCreate();
 983     $individualID = $this->individualCreate();
 984     $groupID = $this->smartGroupCreate();
 985     $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added']);
 986     $this->callAPISuccess('GroupContact', 'create', ['group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added']);
 987
 988     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 989     CRM_Contact_BAO_GroupContactCache::clearGroupContactCache($groupID);
 990
 991     $sql = CRM_Contact_BAO_Query::getQuery(
 992       [['group', 'IN', [$groupID], 0, 0]],
 993       ['contact_id']
 994     );
 995
 996     $dao = CRM_Core_DAO::executeQuery($sql);
 997     $this->assertEquals(3, $dao->N);
 998     $this->assertFalse(strstr($sql, ' OR '));
 999
1000     $sql = CRM_Contact_BAO_Query::getQuery(
1001       [['group', 'IN', [$groupID], 0, 0]],
1002       ['contact_id' => 1, 'group' => 1]
1003     );
1004
1005     $dao = CRM_Core_DAO::executeQuery($sql);
1006     $this->assertEquals(3, $dao->N);
1007     $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or');
1008     while ($dao->fetch()) {
1009       $this->assertTrue(($dao->groups == $groupID || $dao->groups == ',' . $groupID), $dao->groups . ' includes ' . $groupID);
1010     }
1011   }
1012
1013   /**
1014    * CRM-19562 ensure that only ids are used for contact_id searching.
1015    */
1016   public function testContactIDClause() {
1017     $params = [
1018       ['mark_x_2', '=', 1, 0, 0],
1019       ['mark_x_foo@example.com', '=', 1, 0, 0],
1020     ];
1021     $returnProperties = [
1022       'sort_name' => 1,
1023       'email' => 1,
1024       'do_not_email' => 1,
1025       'is_deceased' => 1,
1026       'on_hold' => 1,
1027       'display_name' => 1,
1028       'preferred_mail_format' => 1,
1029     ];
1030     $numberOfContacts = 2;
1031
1032     try {
1033       CRM_Contact_BAO_Query::apiQuery($params, $returnProperties, NULL, NULL, 0, $numberOfContacts);
1034     }
1035     catch (Exception $e) {
1036       $this->assertEquals(
1037         'One of parameters  (value: foo@example.com) is not of the type Positive',
1038         $e->getMessage()
1039       );
1040       $this->assertTrue(TRUE);
1041       return;
1042     }
1043     $this->fail('Test failed for some reason which is not good');
1044   }
1045
1046   /**
1047    * Test the sorting on the contact ID query works.
1048    *
1049    * Checking for lack of fatal.
1050    *
1051    * @param string $sortOrder
1052    *   Param reflecting how sort is passed in.
1053    *   - 1_d is column 1 descending.
1054    *
1055    * @dataProvider getSortOptions
1056    */
1057   public function testContactIDQuery($sortOrder) {
1058     $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all'], NULL, ['sort_name' => 1]);
1059     $selector->contactIDQuery([], $sortOrder);
1060   }
1061
1062   /**
1063    * Test the sorting on the contact ID query works with a profile search.
1064    *
1065    * Checking for lack of fatal.
1066    *
1067    * @throws \CRM_Core_Exception
1068    */
1069   public function testContactIDQueryProfileSearchResults() {
1070     $profile = $this->callAPISuccess('UFGroup', 'create', ['group_type' => 'Contact', 'name' => 'search', 'title' => 'search']);
1071     $this->callAPISuccess('UFField', 'create', [
1072       'uf_group_id' => $profile['id'],
1073       'field_name' => 'postal_code',
1074       'field_type' => 'Contact',
1075       'in_selector' => TRUE,
1076       'is_searchable' => TRUE,
1077       'label' => 'postal code',
1078       'visibility' => 'Public Pages and Listings',
1079     ]);
1080     $selector = new CRM_Contact_Selector(NULL, ['radio_ts' => 'ts_all', 'uf_group_id' => $profile['id']], NULL, ['sort_name' => 1]);
1081     $selector->contactIDQuery([], '2_d');
1082   }
1083
1084   /**
1085    * Get search options to reflect how a UI search would look.
1086    *
1087    * @return array
1088    */
1089   public function getSortOptions() {
1090     return [
1091       ['1_d'],
1092       ['2_d'],
1093       ['3_d'],
1094       ['4_d'],
1095       ['5_d'],
1096       ['6_d'],
1097     ];
1098   }
1099
1100   /**
1101    * Test the summary query does not add an acl clause when acls not enabled..
1102    *
1103    * @throws \CRM_Core_Exception
1104    */
1105   public function testGetSummaryQueryWithFinancialACLDisabled() {
1106     $this->createContributionsForSummaryQueryTests();
1107
1108     // Test the function directly
1109     $where = $from = NULL;
1110     $queryObject = new CRM_Contact_BAO_Query();
1111     $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
1112       $from);
1113     $this->assertEquals(NULL, $where);
1114     $this->assertEquals(NULL, $from);
1115
1116     // Test the function in action
1117     $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
1118     $summary = $queryObject->summaryContribution();
1119     $this->assertEquals([
1120       'total' => [
1121         'avg' => '$ 233.33',
1122         'amount' => '$ 1,400.00',
1123         'count' => 6,
1124       ],
1125       'cancel' => [
1126         'count' => 2,
1127         'amount' => '$ 100.00',
1128         'avg' => '$ 50.00',
1129       ],
1130     ], $summary);
1131   }
1132
1133   /**
1134    * Test the summary query accurately adds financial acl filters.
1135    *
1136    * @throws \CRM_Core_Exception
1137    */
1138   public function testGetSummaryQueryWithFinancialACLEnabled() {
1139     $where = $from = NULL;
1140     $this->createContributionsForSummaryQueryTests();
1141     $this->enableFinancialACLs();
1142     $this->createLoggedInUserWithFinancialACL();
1143
1144     // Test the function directly
1145     $queryObject = new CRM_Contact_BAO_Query();
1146     $queryObject->appendFinancialTypeWhereAndFromToQueryStrings($where,
1147       $from);
1148     $donationTypeID = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation');
1149     $this->assertEquals(
1150       " LEFT JOIN civicrm_line_item li
1151                   ON civicrm_contribution.id = li.contribution_id AND
1152                      li.entity_table = 'civicrm_contribution' AND li.financial_type_id NOT IN ({$donationTypeID}) ", $from);
1153
1154     // Test the function in action
1155     $queryObject = new CRM_Contact_BAO_Query([['contribution_source', '=', 'SSF', '', '']]);
1156     $summary = $queryObject->summaryContribution();
1157     $this->assertEquals([
1158       'total' => [
1159         'avg' => '$ 200.00',
1160         'amount' => '$ 400.00',
1161         'count' => 2,
1162       ],
1163       'cancel' => [
1164         'count' => 1,
1165         'amount' => '$ 50.00',
1166         'avg' => '$ 50.00',
1167       ],
1168     ], $summary);
1169     $this->disableFinancialACLs();
1170   }
1171
1172   /**
1173    * Test relative date filters to ensure they generate correct SQL.
1174    *
1175    * @dataProvider relativeDateFilters
1176    *
1177    * @param string $filter
1178    * @param string $expectedWhere
1179    *
1180    * @throws \CRM_Core_Exception
1181    */
1182   public function testRelativeDateFilters($filter, $expectedWhere) {
1183     $params = [['created_date_relative', '=', $filter, 0, 0]];
1184
1185     $dates = CRM_Utils_Date::getFromTo($filter, NULL, NULL);
1186     $expectedWhere = str_replace(['date0', 'date1'], [$dates[0], $dates[1]], $expectedWhere);
1187
1188     $query = new CRM_Contact_BAO_Query(
1189       $params, [],
1190       NULL, TRUE, FALSE, 1,
1191       TRUE,
1192       TRUE, FALSE
1193     );
1194
1195     list($select, $from, $where, $having) = $query->query();
1196     $this->assertEquals($expectedWhere, $where);
1197   }
1198
1199   /**
1200    * Data provider to relative date filter configurations.
1201    *
1202    * @return array
1203    */
1204   public function relativeDateFilters() {
1205     $dataProvider[] = ['this.year', "WHERE  ( contact_a.created_date BETWEEN 'date0' AND 'date1' )  AND (contact_a.is_deleted = 0)"];
1206     $dataProvider[] = ['greater.day', "WHERE  ( contact_a.created_date >= 'date0' )  AND (contact_a.is_deleted = 0)"];
1207     $dataProvider[] = ['earlier.week', "WHERE  ( contact_a.created_date <= 'date1' )  AND (contact_a.is_deleted = 0)"];
1208     return $dataProvider;
1209   }
1210
1211   /**
1212    * Create contributions to test summary calculations.
1213    *
1214    * financial type     | cancel_date        |total_amount| source    | line_item_financial_types  |number_line_items| line_amounts
1215    * Donation           |NULL                | 100.00     |SSF         | Donation                  | 1                | 100.00
1216    * Member Dues        |NULL                | 100.00     |SSF         | Member Dues               | 1                | 100.00
1217    * Donation           |NULL                | 300.00     |SSF         | Event Fee,Event Fee       | 2                | 200.00,100.00
1218    * Donation           |NULL                | 300.00     |SSF         | Event Fee,Donation        | 2                | 200.00,100.00
1219    * Donation           |NULL                | 300.00     |SSF         | Donation,Donation         | 2                | 200.00,100.00
1220    * Donation           |2019-02-13 00:00:00 | 50.00      |SSF         | Donation                  | 1                | 50.00
1221    * Member Dues        |2019-02-13 00:00:00 | 50.00      |SSF         | Member Dues               | 1                | 50.00
1222    *
1223    * @throws \CRM_Core_Exception
1224    */
1225   protected function createContributionsForSummaryQueryTests() {
1226     $contactID = $this->individualCreate();
1227     $this->contributionCreate(['contact_id' => $contactID]);
1228     $this->contributionCreate([
1229       'contact_id' => $contactID,
1230       'total_amount' => 100,
1231       'financial_type_id' => 'Member Dues',
1232     ]);
1233     $eventFeeType = CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Event Fee');
1234     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF']);
1235     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
1236       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1237       $eventFeeType,
1238     ]);
1239     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF'], [
1240       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1241       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1242     ]);
1243     $this->createContributionWithTwoLineItemsAgainstPriceSet(['contact_id' => $contactID, 'source' => 'SSF', 'financial_type_id' => $eventFeeType], [
1244       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1245       CRM_Core_PseudoConstant::getKey('CRM_Contribute_BAO_Contribution', 'financial_type_id', 'Donation'),
1246     ]);
1247     $this->contributionCreate([
1248       'contact_id' => $contactID,
1249       'total_amount' => 50,
1250       'contribution_status_id' => 'Cancelled',
1251       'cancel_date' => 'yesterday',
1252     ]);
1253     $this->contributionCreate([
1254       'contact_id' => $contactID,
1255       'total_amount' => 50,
1256       'contribution_status_id' => 'Cancelled',
1257       'cancel_date' => 'yesterday',
1258       'financial_type_id' => 'Member Dues',
1259     ]);
1260   }
1261
1262   /**
1263    * Test the options are handled for the qill.
1264    */
1265   public function testQillOptions() {
1266     $qill = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_BAO_Activity', 'activity_type_id', 2, '=');
1267     $this->assertEquals(['=', 'Phone Call'], $qill);
1268
1269     $qill = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Activity_BAO_Activity', 'priority_id', 2, '=');
1270     $this->assertEquals(['=', 'Normal'], $qill);
1271   }
1272
1273   /**
1274    * Test tests that a value on 'any entity' with the right metadata will be handled.
1275    *
1276    * @throws \CRM_Core_Exception
1277    */
1278   public function testGenericWhereHandling() {
1279     $query = new CRM_Contact_BAO_Query([['suffix_id', '=', 2, 0]]);
1280     $this->assertEquals('contact_a.suffix_id = 2', $query->_where[0][0]);
1281     $this->assertEquals('Individual Suffix = Sr.', $query->_qill[0][0]);
1282     $this->assertNotTrue(isset($query->_tables['civicrm_activity']));
1283
1284     $query = new CRM_Contact_BAO_Query([['prefix_id', '=', 2, 0]]);
1285     $this->assertEquals('contact_a.prefix_id = 2', $query->_where[0][0]);
1286     $this->assertEquals('Individual Prefix = Ms.', $query->_qill[0][0]);
1287     $this->assertNotTrue(isset($query->_tables['civicrm_activity']));
1288
1289     $query = new CRM_Contact_BAO_Query([['gender_id', '=', 2, 0]]);
1290     $this->assertEquals('contact_a.gender_id = 2', $query->_where[0][0]);
1291     $this->assertEquals('Gender = Male', $query->_qill[0][0]);
1292     $this->assertNotTrue(isset($query->_tables['civicrm_activity']));
1293
1294     $query = new CRM_Contact_BAO_Query([['communication_style_id', '=', 2, 0]]);
1295     $this->assertEquals('contact_a.communication_style_id = 2', $query->_where[0][0]);
1296     $this->assertEquals('Communication Style = Familiar', $query->_qill[0][0]);
1297
1298     $query = new CRM_Contact_BAO_Query([['communication_style_id', '=', 2, 0]]);
1299     $this->assertEquals('contact_a.communication_style_id = 2', $query->_where[0][0]);
1300     $this->assertEquals('Communication Style = Familiar', $query->_qill[0][0]);
1301
1302     $query = new CRM_Contact_BAO_Query([['contact_type', '=', 'Household', 0]]);
1303     $this->assertEquals("contact_a.contact_type = 'Household'", $query->_where[0][0]);
1304     $this->assertEquals('Contact Type = Household', $query->_qill[0][0]);
1305
1306     $query = new CRM_Contact_BAO_Query([['on_hold', '=', 0, 0]]);
1307     $this->assertEquals('civicrm_email.on_hold = 0', $query->_where[0][0]);
1308     $this->assertEquals('On Hold = 0', $query->_qill[0][0]);
1309
1310     $query = new CRM_Contact_BAO_Query([['on_hold', '=', 1, 0]]);
1311     $this->assertEquals('civicrm_email.on_hold = 1', $query->_where[0][0]);
1312     $this->assertEquals('On Hold = 1', $query->_qill[0][0]);
1313
1314     $query = new CRM_Contact_BAO_Query([['world_region', '=', 3, 0]]);
1315     $this->assertEquals('civicrm_worldregion.id = 3', $query->_where[0][0]);
1316     $this->assertEquals('World Region = Middle East and North Africa', $query->_qill[0][0]);
1317   }
1318
1319 }