tests/phpunit/CRM/Contact/SelectorTest.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *  Include parent class definition
  14  */
  15
  16 /**
  17  *  Test contact custom search functions
  18  *
  19  * @package CiviCRM
  20  * @group headless
  21  */
  22 class CRM_Contact_SelectorTest extends CiviUnitTestCase {
  23
  24   /**
  25    * Test the query from the selector class is consistent with the dataset expectation.
  26    *
  27    * @param array $dataSet
  28    *   The data set to be tested. Note that when adding new datasets often only form_values and expected where
  29    *   clause will need changing.
  30    *
  31    * @dataProvider querySets
  32    * @throws \Exception
  33    */
  34   public function testSelectorQuery($dataSet) {
  35     if (!empty($dataSet['limitedPermissions'])) {
  36       CRM_Core_Config::singleton()->userPermissionClass->permissions = [
  37         'access CiviCRM',
  38         'access deleted contacts',
  39       ];
  40     }
  41     $params = CRM_Contact_BAO_Query::convertFormValues($dataSet['form_values'], 0, FALSE, NULL, []);
  42     $isDeleted = in_array(['deleted_contacts', '=', 1, 0, 0], $params);
  43     foreach ($dataSet['settings'] as $setting) {
  44       $this->callAPISuccess('Setting', 'create', [$setting['name'] => $setting['value']]);
  45     }
  46     $selector = new CRM_Contact_Selector(
  47       $dataSet['class'],
  48       $dataSet['form_values'],
  49       $params,
  50       $dataSet['return_properties'],
  51       $dataSet['action'],
  52       $dataSet['includeContactIds'],
  53       $dataSet['searchDescendentGroups'],
  54       $dataSet['context']
  55     );
  56     $queryObject = $selector->getQueryObject();
  57     // Make sure there is no fail on alphabet query.
  58     $selector->alphabetQuery()->fetchAll();
  59     $sql = $queryObject->query(FALSE, FALSE, FALSE, $isDeleted);
  60     $this->wrangleDefaultClauses($dataSet['expected_query']);
  61     foreach ($dataSet['expected_query'] as $index => $queryString) {
  62       $this->assertLike($this->strWrangle($queryString), $this->strWrangle($sql[$index]));
  63     }
  64     // Ensure that search builder return individual contact as per criteria
  65     if ($dataSet['context'] === 'builder') {
  66       $contactID = $this->individualCreate(['first_name' => 'James', 'last_name' => 'Bond']);
  67       if ('Search builder behaviour for Activity' === $dataSet['description']) {
  68         $this->callAPISuccess('Activity', 'create', [
  69           'activity_type_id' => 'Meeting',
  70           'subject' => 'Test',
  71           'source_contact_id' => $contactID,
  72         ]);
  73         $rows = CRM_Core_DAO::executeQuery(implode(' ', $sql))->fetchAll();
  74         $this->assertCount(1, $rows);
  75         $this->assertEquals($contactID, $rows[0]['source_contact_id']);
  76       }
  77       else {
  78         $this->callAPISuccess('Address', 'create', [
  79           'contact_id' => $contactID,
  80           'location_type_id' => 'Home',
  81           'is_primary' => 1,
  82           'country_id' => 'IN',
  83         ]);
  84         $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 50, '');
  85         $this->assertEquals(1, count($rows));
  86
  87         CRM_Core_DAO::reenableFullGroupByMode();
  88         $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 50, '');
  89
  90         $sortChar = $selector->alphabetQuery()->fetchAll();
  91         // sort name is stored in '<last_name>, <first_name>' format, as per which the first character would be B of Bond
  92         $this->assertEquals('B', $sortChar[0]['sort_name']);
  93         $this->assertEquals($contactID, key($rows));
  94
  95         CRM_Core_DAO::reenableFullGroupByMode();
  96         $selector->getQueryObject()->getCachedContacts([$contactID], FALSE);
  97       }
  98     }
  99     if (!empty($dataSet['limitedPermissions'])) {
 100       $this->cleanUpAfterACLs();
 101     }
 102   }
 103
 104   /**
 105    * Test advanced search results by uf_group_id.
 106    */
 107   public function testSearchByProfile() {
 108     //Create search profile for contacts.
 109     $ufGroup = $this->callAPISuccess('uf_group', 'create', [
 110       'group_type' => 'Contact',
 111       'name' => 'test_search_profile',
 112       'title' => 'Test Search Profile',
 113       'api.uf_field.create' => [
 114         [
 115           'field_name' => 'email',
 116           'visibility' => 'Public Pages and Listings',
 117           'field_type' => 'Contact',
 118           'label' => 'Email',
 119           'in_selector' => 1,
 120         ],
 121       ],
 122     ]);
 123     $contactID = $this->individualCreate(['email' => 'mickey@mouseville.com']);
 124     //Put the email on hold.
 125     $email = $this->callAPISuccess('Email', 'get', [
 126       'sequential' => 1,
 127       'contact_id' => $contactID,
 128     ]);
 129     $this->callAPISuccess('Email', 'create', [
 130       'id' => $email['id'],
 131       'on_hold' => 1,
 132     ]);
 133
 134     $dataSet = [
 135       'description' => 'Normal default behaviour',
 136       'class' => 'CRM_Contact_Selector',
 137       'settings' => [],
 138       'form_values' => ['email' => 'mickey@mouseville.com', 'uf_group_id' => $ufGroup['id']],
 139       'params' => [],
 140       'return_properties' => NULL,
 141       'context' => 'advanced',
 142       'action' => CRM_Core_Action::ADVANCED,
 143       'includeContactIds' => NULL,
 144       'searchDescendentGroups' => FALSE,
 145     ];
 146     $params = CRM_Contact_BAO_Query::convertFormValues($dataSet['form_values'], 0, FALSE, NULL, []);
 147     // create CRM_Contact_Selector instance and set desired query params
 148     $selector = new CRM_Contact_Selector(
 149       $dataSet['class'],
 150       $dataSet['form_values'],
 151       $params,
 152       $dataSet['return_properties'],
 153       $dataSet['action'],
 154       $dataSet['includeContactIds'],
 155       $dataSet['searchDescendentGroups'],
 156       $dataSet['context']
 157     );
 158     $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 50, '');
 159     $this->assertEquals(1, count($rows));
 160     $this->assertEquals($contactID, key($rows));
 161
 162     //Check if email column contains (On Hold) string.
 163     foreach ($rows[$contactID] as $key => $value) {
 164       if (strpos($key, 'email') !== FALSE) {
 165         $this->assertContains("(On Hold)", (string) $value);
 166       }
 167     }
 168   }
 169
 170   /**
 171    * Test the civicrm_prevnext_cache entry if it correctly stores the search query result
 172    */
 173   public function testPrevNextCache() {
 174     $contactID = $this->individualCreate(['email' => 'mickey@mouseville.com']);
 175     $dataSet = [
 176       'description' => 'Normal default behaviour',
 177       'class' => 'CRM_Contact_Selector',
 178       'settings' => [],
 179       'form_values' => ['email' => 'mickey@mouseville.com'],
 180       'params' => [],
 181       'return_properties' => NULL,
 182       'context' => 'advanced',
 183       'action' => CRM_Core_Action::ADVANCED,
 184       'includeContactIds' => NULL,
 185       'searchDescendentGroups' => FALSE,
 186       'expected_query' => [
 187         0 => 'default',
 188         1 => 'default',
 189         2 => "WHERE  ( civicrm_email.email LIKE '%mickey@mouseville.com%' )  AND (contact_a.is_deleted = 0)",
 190       ],
 191     ];
 192     $params = CRM_Contact_BAO_Query::convertFormValues($dataSet['form_values'], 0, FALSE, NULL, []);
 193
 194     // create CRM_Contact_Selector instance and set desired query params
 195     $selector = new CRM_Contact_Selector(
 196       $dataSet['class'],
 197       $dataSet['form_values'],
 198       $params,
 199       $dataSet['return_properties'],
 200       $dataSet['action'],
 201       $dataSet['includeContactIds'],
 202       $dataSet['searchDescendentGroups'],
 203       $dataSet['context']
 204     );
 205     // set cache key
 206     $key = substr(sha1(rand()), 0, 7);
 207     $selector->setKey($key);
 208
 209     // fetch row and check the result
 210     $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 1, NULL);
 211     $this->assertEquals(1, count($rows));
 212     $this->assertEquals($contactID, key($rows));
 213
 214     // build cache key and use to it to fetch prev-next cache record
 215     $cacheKey = 'civicrm search ' . $key;
 216     $contacts = CRM_Utils_SQL_Select::from('civicrm_prevnext_cache')
 217       ->select(['entity_id1', 'cachekey'])
 218       ->where("cachekey = @key")
 219       ->param('key', $cacheKey)
 220       ->execute()
 221       ->fetchAll();
 222     $this->assertEquals(1, count($contacts));
 223     // check the prevNext record matches
 224     $expectedEntry = [
 225       'entity_id1' => $contactID,
 226       'cachekey' => $cacheKey,
 227     ];
 228     $this->checkArrayEquals($contacts[0], $expectedEntry);
 229   }
 230
 231   /**
 232    * Data sets for testing.
 233    */
 234   public function querySets() {
 235     return [
 236       [
 237         [
 238           'description' => 'Empty group test',
 239           'class' => 'CRM_Contact_Selector',
 240           'settings' => [],
 241           'form_values' => [['contact_type', '=', 'Individual', 1, 0], ['group', 'IS NULL', '', 1, 0]],
 242           'params' => [],
 243           'return_properties' => NULL,
 244           'context' => 'builder',
 245           'action' => CRM_Core_Action::NONE,
 246           'includeContactIds' => NULL,
 247           'searchDescendentGroups' => FALSE,
 248           'expected_query' => [],
 249         ],
 250       ],
 251       [
 252         [
 253           'description' => 'Normal default behaviour',
 254           'class' => 'CRM_Contact_Selector',
 255           'settings' => [],
 256           'form_values' => ['email' => 'mickey@mouseville.com'],
 257           'params' => [],
 258           'return_properties' => NULL,
 259           'context' => 'advanced',
 260           'action' => CRM_Core_Action::ADVANCED,
 261           'includeContactIds' => NULL,
 262           'searchDescendentGroups' => FALSE,
 263           'expected_query' => [
 264             0 => 'default',
 265             1 => 'default',
 266             2 => "WHERE  ( civicrm_email.email LIKE '%mickey@mouseville.com%' )  AND ( 1 ) AND (contact_a.is_deleted = 0)",
 267           ],
 268         ],
 269       ],
 270       [
 271         [
 272           'description' => 'Normal default + user added wildcard',
 273           'class' => 'CRM_Contact_Selector',
 274           'settings' => [],
 275           'form_values' => ['email' => '%mickey@mouseville.com', 'sort_name' => 'Mouse'],
 276           'params' => [],
 277           'return_properties' => NULL,
 278           'context' => 'advanced',
 279           'action' => CRM_Core_Action::ADVANCED,
 280           'includeContactIds' => NULL,
 281           'searchDescendentGroups' => FALSE,
 282           'expected_query' => [
 283             0 => 'default',
 284             1 => 'default',
 285             2 => "WHERE  ( civicrm_email.email LIKE '%mickey@mouseville.com%'  AND ( ( ( contact_a.sort_name LIKE '%Mouse%' ) OR ( civicrm_email.email LIKE '%Mouse%' ) ) ) ) AND ( 1 ) AND (contact_a.is_deleted = 0)",
 286           ],
 287         ],
 288       ],
 289       [
 290         [
 291           'description' => 'Site set to not pre-pend wildcard',
 292           'class' => 'CRM_Contact_Selector',
 293           'settings' => [['name' => 'includeWildCardInName', 'value' => FALSE]],
 294           'form_values' => ['email' => 'mickey@mouseville.com', 'sort_name' => 'Mouse'],
 295           'params' => [],
 296           'return_properties' => NULL,
 297           'context' => 'advanced',
 298           'action' => CRM_Core_Action::ADVANCED,
 299           'includeContactIds' => NULL,
 300           'searchDescendentGroups' => FALSE,
 301           'expected_query' => [
 302             0 => 'default',
 303             1 => 'default',
 304             2 => "WHERE  ( civicrm_email.email LIKE 'mickey@mouseville.com%'  AND ( ( ( contact_a.sort_name LIKE 'Mouse%' ) OR ( civicrm_email.email LIKE 'Mouse%' ) ) ) ) AND ( 1 ) AND (contact_a.is_deleted = 0)",
 305           ],
 306         ],
 307       ],
 308       [
 309         [
 310           'description' => 'Site set to not pre-pend wildcard and check that trash value is respected',
 311           'class' => 'CRM_Contact_Selector',
 312           'settings' => [['name' => 'includeWildCardInName', 'value' => FALSE]],
 313           'form_values' => ['email' => 'mickey@mouseville.com', 'sort_name' => 'Mouse', 'deleted_contacts' => 1],
 314           'params' => [],
 315           'return_properties' => NULL,
 316           'context' => 'advanced',
 317           'action' => CRM_Core_Action::ADVANCED,
 318           'includeContactIds' => NULL,
 319           'searchDescendentGroups' => FALSE,
 320           'expected_query' => [
 321             0 => 'default',
 322             1 => 'default',
 323             2 => "WHERE  ( civicrm_email.email LIKE 'mickey@mouseville.com%'  AND ( ( ( contact_a.sort_name LIKE 'Mouse%' ) OR ( civicrm_email.email LIKE 'Mouse%' ) ) ) ) AND ( 1 ) AND (contact_a.is_deleted)",
 324           ],
 325         ],
 326       ],
 327       [
 328         [
 329           'description' => 'Ensure that the Join to the acl contact cache is correct and that if we are searching in deleted contacts appropriate where clause is added',
 330           'class' => 'CRM_Contact_Selector',
 331           'settings' => [['name' => 'includeWildCardInName', 'value' => FALSE]],
 332           'form_values' => ['email' => 'mickey@mouseville.com', 'sort_name' => 'Mouse', 'deleted_contacts' => 1],
 333           'params' => [],
 334           'return_properties' => NULL,
 335           'context' => 'advanced',
 336           'action' => CRM_Core_Action::ADVANCED,
 337           'includeContactIds' => NULL,
 338           'searchDescendentGroups' => FALSE,
 339           'limitedPermissions' => TRUE,
 340           'expected_query' => [
 341             0 => 'default',
 342             1 => 'FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_a.id = aclContactCache.contact_id',
 343             2 => "WHERE  ( civicrm_email.email LIKE 'mickey@mouseville.com%'  AND ( ( ( contact_a.sort_name LIKE 'Mouse%' ) OR ( civicrm_email.email LIKE 'Mouse%' ) ) ) ) AND  aclContactCache.user_id = 0 AND (contact_a.is_deleted)",
 344           ],
 345         ],
 346       ],
 347       [
 348         [
 349           'description' => 'Ensure that the Join to the acl contact cache is correct and that if we are not searching in the trash trashed contacts are not returned',
 350           'class' => 'CRM_Contact_Selector',
 351           'settings' => [['name' => 'includeWildCardInName', 'value' => FALSE]],
 352           'form_values' => ['email' => 'mickey@mouseville.com', 'sort_name' => 'Mouse'],
 353           'params' => [],
 354           'return_properties' => NULL,
 355           'context' => 'advanced',
 356           'action' => CRM_Core_Action::ADVANCED,
 357           'includeContactIds' => NULL,
 358           'searchDescendentGroups' => FALSE,
 359           'limitedPermissions' => TRUE,
 360           'expected_query' => [
 361             0 => 'default',
 362             1 => 'FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id ) LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_a.id = aclContactCache.contact_id',
 363             2 => "WHERE  ( civicrm_email.email LIKE 'mickey@mouseville.com%'  AND ( ( ( contact_a.sort_name LIKE 'Mouse%' ) OR ( civicrm_email.email LIKE 'Mouse%' ) ) ) ) AND  aclContactCache.user_id = 0 AND (contact_a.is_deleted = 0)",
 364           ],
 365         ],
 366       ],
 367       [
 368         [
 369           'description' => 'Use of quotes for exact string',
 370           'use_case_comments' => 'This is something that was in the code but seemingly not working. No UI info on it though!',
 371           'class' => 'CRM_Contact_Selector',
 372           'settings' => [['name' => 'includeWildCardInName', 'value' => FALSE]],
 373           'form_values' => ['email' => '"mickey@mouseville.com"', 'sort_name' => 'Mouse'],
 374           'params' => [],
 375           'return_properties' => NULL,
 376           'context' => 'advanced',
 377           'action' => CRM_Core_Action::ADVANCED,
 378           'includeContactIds' => NULL,
 379           'searchDescendentGroups' => FALSE,
 380           'expected_query' => [
 381             0 => 'default',
 382             1 => 'default',
 383             2 => "WHERE  ( civicrm_email.email = 'mickey@mouseville.com'  AND ( ( ( contact_a.sort_name LIKE 'Mouse%' ) OR ( civicrm_email.email LIKE 'Mouse%' ) ) ) ) AND ( 1 ) AND (contact_a.is_deleted = 0)",
 384           ],
 385         ],
 386       ],
 387       [
 388         [
 389           'description' => 'Normal search builder behaviour',
 390           'class' => 'CRM_Contact_Selector',
 391           'settings' => [],
 392           'form_values' => ['contact_type' => 'Individual', 'country' => ['IS NOT NULL' => 1]],
 393           'params' => [],
 394           'return_properties' => [
 395             'contact_type' => 1,
 396             'contact_sub_type' => 1,
 397             'sort_name' => 1,
 398           ],
 399           'context' => 'builder',
 400           'action' => CRM_Core_Action::NONE,
 401           'includeContactIds' => NULL,
 402           'searchDescendentGroups' => FALSE,
 403           'expected_query' => [
 404             0 => '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, civicrm_address.country_id as country_id',
 405             1 => ' FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )',
 406             2 => 'WHERE ( contact_a.contact_type IN ("Individual") AND civicrm_address.country_id IS NOT NULL ) AND ( 1 ) AND  (contact_a.is_deleted = 0)',
 407           ],
 408         ],
 409       ],
 410       [
 411         [
 412           'description' => 'Search builder behaviour for Activity',
 413           'class' => 'CRM_Contact_Selector',
 414           'settings' => [],
 415           'form_values' => ['source_contact_id' => ['IS NOT NULL' => 1]],
 416           'params' => [],
 417           'return_properties' => [
 418             'source_contact_id' => 1,
 419           ],
 420           'context' => 'builder',
 421           'action' => CRM_Core_Action::NONE,
 422           'includeContactIds' => NULL,
 423           'searchDescendentGroups' => FALSE,
 424           'expected_query' => [
 425             0 => 'SELECT contact_a.id as contact_id, source_contact.id as source_contact_id',
 426             2 => 'WHERE ( source_contact.id IS NOT NULL ) AND ( 1 ) AND (contact_a.is_deleted = 0)',
 427           ],
 428         ],
 429       ],
 430       [
 431         [
 432           'description' => 'Test display relationships',
 433           'class' => 'CRM_Contact_Selector',
 434           'settings' => [],
 435           'form_values' => ['display_relationship_type' => '1_b_a'],
 436           'return_properties' => NULL,
 437           'params' => [],
 438           'context' => 'advanced',
 439           'action' => CRM_Core_Action::NONE,
 440           'includeContactIds' => NULL,
 441           'searchDescendentGroups' => FALSE,
 442           'expected_query' => [
 443             0 => '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`, contact_a.display_name as `display_name`, contact_a.do_not_email as `do_not_email`, contact_a.do_not_phone as `do_not_phone`, contact_a.do_not_mail as `do_not_mail`, contact_a.do_not_sms as `do_not_sms`, contact_a.do_not_trade as `do_not_trade`, contact_a.is_opt_out as `is_opt_out`, contact_a.legal_identifier as `legal_identifier`, contact_a.external_identifier as `external_identifier`, contact_a.nick_name as `nick_name`, contact_a.legal_name as `legal_name`, contact_a.image_URL as `image_URL`, contact_a.preferred_communication_method as `preferred_communication_method`, contact_a.preferred_language as `preferred_language`, contact_a.preferred_mail_format as `preferred_mail_format`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.prefix_id as `prefix_id`, contact_a.suffix_id as `suffix_id`, contact_a.formal_title as `formal_title`, contact_a.communication_style_id as `communication_style_id`, contact_a.job_title as `job_title`, contact_a.gender_id as `gender_id`, contact_a.birth_date as `birth_date`, contact_a.is_deceased as `is_deceased`, contact_a.deceased_date as `deceased_date`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = \'Individual\', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code as `sic_code`, contact_a.is_deleted as `contact_is_deleted`, IF ( contact_a.contact_type = \'Individual\', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.supplemental_address_3 as `supplemental_address_3`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_im.id as im_id, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`',
 444             2 => 'WHERE displayRelType.relationship_type_id = 1
 445 AND   displayRelType.is_active = 1
 446 AND ( 1 ) AND (contact_a.is_deleted = 0)',
 447           ],
 448         ],
 449       ],
 450     ];
 451   }
 452
 453   /**
 454    * Test the contact ID query does not fail on country search.
 455    */
 456   public function testContactIDQuery() {
 457     $params = [
 458       [
 459         0 => 'country-1',
 460         1 => '=',
 461         2 => '1228',
 462         3 => 1,
 463         4 => 0,
 464       ],
 465     ];
 466
 467     $searchOBJ = new CRM_Contact_Selector(NULL);
 468     $searchOBJ->contactIDQuery($params, '1_u');
 469   }
 470
 471   /**
 472    * Test the Search Builder using Non ASCII location type for email filter
 473    */
 474   public function testSelectorQueryOnNonASCIIlocationType() {
 475     $contactID = $this->individualCreate();
 476     $locationTypeID = $this->locationTypeCreate([
 477       'name' => 'Non ASCII Location Type',
 478       'display_name' => 'Дом Location type',
 479       'vcard_name' => 'Non ASCII Location Type',
 480       'is_active' => 1,
 481     ]);
 482     $this->callAPISuccess('Email', 'create', [
 483       'contact_id' => $contactID,
 484       'location_type_id' => $locationTypeID,
 485       'email' => 'test@test.com',
 486     ]);
 487
 488     $selector = new CRM_Contact_Selector(
 489       'CRM_Contact_Selector',
 490       ['email' => ['IS NOT NULL' => 1]],
 491       [
 492         [
 493           0 => 'email-' . $locationTypeID,
 494           1 => 'IS NOT NULL',
 495           2 => NULL,
 496           3 => 1,
 497           4 => 0,
 498         ],
 499       ],
 500       [
 501         'contact_type' => 1,
 502         'contact_sub_type' => 1,
 503         'sort_name' => 1,
 504         'location' => [
 505           'Non ASCII Location Type' => [
 506             'location_type' => $locationTypeID,
 507             'email' => 1,
 508           ],
 509         ],
 510       ],
 511       CRM_Core_Action::NONE,
 512       NULL,
 513       FALSE,
 514       'builder'
 515     );
 516
 517     $sql = $selector->getQueryObject()->query();
 518
 519     $expectedQuery = [
 520       0 => "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`, `Non_ASCII_Location_Type-location_type`.id as `Non_ASCII_Location_Type-location_type_id`, `Non_ASCII_Location_Type-location_type`.name as `Non_ASCII_Location_Type-location_type`, `Non_ASCII_Location_Type-address`.id as `Non_ASCII_Location_Type-address_id`, `Non_ASCII_Location_Type-email`.id as `Non_ASCII_Location_Type-email_id`, `Non_ASCII_Location_Type-email`.email as `Non_ASCII_Location_Type-email`",
 521       // @TODO these FROM clause doesn't matches due to extra spaces or special character
 522       2 => "WHERE  (  ( `Non_ASCII_Location_Type-email`.email IS NOT NULL )  )  AND ( 1 ) AND (contact_a.is_deleted = 0)",
 523     ];
 524     foreach ($expectedQuery as $index => $queryString) {
 525       $this->assertEquals($this->strWrangle($queryString), $this->strWrangle($sql[$index]));
 526     }
 527
 528     $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 1, NULL);
 529     $this->assertEquals(1, count($rows));
 530     $this->assertEquals($contactID, key($rows));
 531     $this->assertEquals('test@test.com', $rows[$contactID]['Non_ASCII_Location_Type-email']);
 532   }
 533
 534   /**
 535    * Test the value use in where clause if it's case sensitive or not against each MySQL operators
 536    */
 537   public function testWhereClauseByOperator() {
 538     $contactID = $this->individualCreate(['first_name' => 'Adam']);
 539
 540     $filters = [
 541       'IS NOT NULL' => 1,
 542       '=' => 'Adam',
 543       'LIKE' => '%Ad%',
 544       'RLIKE' => '^A[a-z]{3}$',
 545       'IN' => ['IN' => ['Adam']],
 546     ];
 547     $filtersByWhereClause = [
 548       // doesn't matter
 549       'IS NOT NULL' => '( contact_a.first_name IS NOT NULL )',
 550       // case sensitive check
 551       '=' => "( contact_a.first_name = 'Adam' )",
 552       // case insensitive check
 553       'LIKE' => "( contact_a.first_name LIKE '%Ad%' )",
 554       // case sensitive check
 555       'RLIKE' => "(  CAST(contact_a.first_name AS BINARY) RLIKE BINARY '^A[a-z]{3}$'  )",
 556       // case sensitive check
 557       'IN' => '( contact_a.first_name IN ("Adam") )',
 558     ];
 559     foreach ($filters as $op => $filter) {
 560       $selector = new CRM_Contact_Selector(
 561         'CRM_Contact_Selector',
 562         ['first_name' => [$op => $filter]],
 563         [
 564           [
 565             0 => 'first_name',
 566             1 => $op,
 567             2 => $filter,
 568             3 => 1,
 569             4 => 0,
 570           ],
 571         ],
 572         [],
 573         CRM_Core_Action::NONE,
 574         NULL,
 575         FALSE,
 576         'builder'
 577       );
 578
 579       $sql = $selector->getQueryObject()->query();
 580       $this->assertEquals(TRUE, strpos($sql[2], $filtersByWhereClause[$op]));
 581
 582       $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 1, NULL);
 583       $this->assertEquals(1, count($rows));
 584       $this->assertEquals($contactID, key($rows));
 585     }
 586   }
 587
 588   /**
 589    * Test if custom table is added in from clause when
 590    * search results are ordered by a custom field.
 591    */
 592   public function testSelectorQueryOrderByCustomField() {
 593     //Search for any params.
 594     $params = [
 595       [
 596         0 => 'country-1',
 597         1 => '=',
 598         2 => '1228',
 599         3 => 1,
 600         4 => 0,
 601       ],
 602     ];
 603
 604     //Create a test custom group and field.
 605     $customGroup = $this->callAPISuccess('CustomGroup', 'create', [
 606       'title' => "test custom group",
 607       'extends' => "Individual",
 608     ]);
 609     $cgTableName = $customGroup['values'][$customGroup['id']]['table_name'];
 610     $customField = $this->callAPISuccess('CustomField', 'create', [
 611       'custom_group_id' => $customGroup['id'],
 612       'label' => "test field",
 613       'html_type' => "Text",
 614     ]);
 615     $customFieldId = $customField['id'];
 616
 617     //Sort by the custom field created above.
 618     $sortParams = [
 619       1 => [
 620         'name' => 'test field',
 621         'sort' => "custom_{$customFieldId}",
 622       ],
 623     ];
 624     $sort = new CRM_Utils_Sort($sortParams, '1_d');
 625
 626     //Form a query to order by a custom field.
 627     $query = new CRM_Contact_BAO_Query($params,
 628       CRM_Contact_BAO_Query::NO_RETURN_PROPERTIES,
 629       NULL, FALSE, FALSE, 1,
 630       FALSE, TRUE, TRUE, NULL,
 631       'AND'
 632     );
 633     $query->searchQuery(0, 0, $sort,
 634       FALSE, FALSE, FALSE,
 635       FALSE, FALSE
 636     );
 637     //Check if custom table is included in $query->_tables.
 638     $this->assertTrue(in_array($cgTableName, array_keys($query->_tables)));
 639     //Assert if from clause joins the custom table.
 640     $this->assertTrue(strpos($query->_fromClause, $cgTableName) !== FALSE);
 641     $this->callAPISuccess('CustomField', 'delete', ['id' => $customField['id']]);
 642     $this->callAPISuccess('CustomGroup', 'delete', ['id' => $customGroup['id']]);
 643   }
 644
 645   /**
 646    * Check where clause of a date custom field when 'IS NOT EMPTY' operator is used
 647    */
 648   public function testCustomDateField() {
 649     $contactID = $this->individualCreate();
 650     //Create a test custom group and field.
 651     $customGroup = $this->callAPISuccess('CustomGroup', 'create', [
 652       'title' => "test custom group",
 653       'extends' => "Individual",
 654     ]);
 655     $customTableName = $this->callAPISuccess('CustomGroup', 'getValue', ['id' => $customGroup['id'], 'return' => 'table_name']);
 656     $customGroupTableName = $customGroup['values'][$customGroup['id']]['table_name'];
 657
 658     $createdField = $this->callAPISuccess('customField', 'create', [
 659       'data_type' => 'Date',
 660       'html_type' => 'Select Date',
 661       'date_format' => 'd M yy',
 662       'time_format' => 1,
 663       'label' => 'test field',
 664       'custom_group_id' => $customGroup['id'],
 665     ]);
 666     $customFieldColumnName = $createdField['values'][$createdField['id']]['column_name'];
 667
 668     $this->callAPISuccess('Contact', 'create', [
 669       'id' => $contactID,
 670       'custom_' . $createdField['id'] => date('YmdHis'),
 671     ]);
 672
 673     $selector = new CRM_Contact_Selector(
 674       'CRM_Contact_Selector',
 675       ['custom_' . $createdField['id'] => ['IS NOT EMPTY' => 1]],
 676       [
 677         [
 678           0 => 'custom_' . $createdField['id'],
 679           1 => 'IS NOT NULL',
 680           2 => 1,
 681           3 => 1,
 682           4 => 0,
 683         ],
 684       ],
 685       [],
 686       CRM_Core_Action::NONE,
 687       NULL,
 688       FALSE,
 689       'builder'
 690     );
 691
 692     $whereClause = $selector->getQueryObject()->query()[2];
 693     $expectedClause = sprintf("( %s.%s IS NOT NULL )", $customGroupTableName, $customFieldColumnName);
 694     // test the presence of expected date clause
 695     $this->assertEquals(TRUE, strpos($whereClause, $expectedClause));
 696
 697     $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 1, NULL);
 698     $this->assertEquals(1, count($rows));
 699   }
 700
 701   /**
 702    * Get the default select string since this is generally consistent.
 703    */
 704   public function getDefaultSelectString() {
 705     return '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`,'
 706       . ' contact_a.display_name  as `display_name`, contact_a.do_not_email  as `do_not_email`, contact_a.do_not_phone as `do_not_phone`, contact_a.do_not_mail  as `do_not_mail`,'
 707       . ' contact_a.do_not_sms  as `do_not_sms`, contact_a.do_not_trade as `do_not_trade`, contact_a.is_opt_out  as `is_opt_out`, contact_a.legal_identifier  as `legal_identifier`,'
 708       . ' contact_a.external_identifier  as `external_identifier`, contact_a.nick_name  as `nick_name`, contact_a.legal_name  as `legal_name`, contact_a.image_URL  as `image_URL`,'
 709       . ' contact_a.preferred_communication_method  as `preferred_communication_method`, contact_a.preferred_language  as `preferred_language`,'
 710       . ' contact_a.preferred_mail_format  as `preferred_mail_format`, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`,'
 711       . ' contact_a.prefix_id  as `prefix_id`, contact_a.suffix_id  as `suffix_id`, contact_a.formal_title  as `formal_title`, contact_a.communication_style_id  as `communication_style_id`,'
 712       . ' contact_a.job_title  as `job_title`, contact_a.gender_id  as `gender_id`, contact_a.birth_date  as `birth_date`, contact_a.is_deceased  as `is_deceased`,'
 713       . ' contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`,'
 714       . ' IF ( contact_a.contact_type = \'Individual\', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code  as `sic_code`, contact_a.is_deleted  as `contact_is_deleted`,'
 715       . ' IF ( contact_a.contact_type = \'Individual\', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id,'
 716       . ' civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, '
 717       . 'civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.supplemental_address_3 as `supplemental_address_3`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, '
 718       . 'civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, '
 719       . 'civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, '
 720       . 'civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_im.id as im_id, '
 721       . 'civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`';
 722   }
 723
 724   /**
 725    * Get the default from string since this is generally consistent.
 726    */
 727   public function getDefaultFromString() {
 728     return ' FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )'
 729       . ' LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id ) '
 730       . ' LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)'
 731       . ' LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)'
 732       . ' LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) '
 733       . 'LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ';
 734   }
 735
 736   /**
 737    * Strangle strings into a more matchable format.
 738    *
 739    * @param string $string
 740    *
 741    * @return string
 742    */
 743   public function strWrangle($string) {
 744     return trim(str_replace('  ', ' ', $string));
 745   }
 746
 747   /**
 748    * Swap out default parts of the query for the actual string.
 749    *
 750    * Note that it seems to make more sense to resolve this earlier & pass it in from a clean code point of
 751    * view, but the output on fail includes long sql statements that are of low relevance then.
 752    *
 753    * @param array $expectedQuery
 754    */
 755   public function wrangleDefaultClauses(&$expectedQuery) {
 756     if (CRM_Utils_Array::value(0, $expectedQuery) == 'default') {
 757       $expectedQuery[0] = $this->getDefaultSelectString();
 758     }
 759     if (CRM_Utils_Array::value(1, $expectedQuery) == 'default') {
 760       $expectedQuery[1] = $this->getDefaultFromString();
 761     }
 762   }
 763
 764 }