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 (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 (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 (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 (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' => 'Use of quotes for exact string',
 350           'use_case_comments' => 'This is something that was in the code but seemingly not working. No UI info on it though!',
 351           'class' => 'CRM_Contact_Selector',
 352           'settings' => [['name' => 'includeWildCardInName', 'value' => FALSE]],
 353           'form_values' => ['email' => '"mickey@mouseville.com"', 'sort_name' => 'Mouse'],
 354           'params' => [],
 355           'return_properties' => NULL,
 356           'context' => 'advanced',
 357           'action' => CRM_Core_Action::ADVANCED,
 358           'includeContactIds' => NULL,
 359           'searchDescendentGroups' => FALSE,
 360           'expected_query' => [
 361             0 => 'default',
 362             1 => 'default',
 363             2 => "WHERE  ( civicrm_email.email = 'mickey@mouseville.com'  AND ( ( ( contact_a.sort_name LIKE 'Mouse%' ) OR ( civicrm_email.email LIKE 'Mouse%' ) ) ) ) AND (contact_a.is_deleted = 0)",
 364           ],
 365         ],
 366       ],
 367       [
 368         [
 369           'description' => 'Normal search builder behaviour',
 370           'class' => 'CRM_Contact_Selector',
 371           'settings' => [],
 372           'form_values' => ['contact_type' => 'Individual', 'country' => ['IS NOT NULL' => 1]],
 373           'params' => [],
 374           'return_properties' => [
 375             'contact_type' => 1,
 376             'contact_sub_type' => 1,
 377             'sort_name' => 1,
 378           ],
 379           'context' => 'builder',
 380           'action' => CRM_Core_Action::NONE,
 381           'includeContactIds' => NULL,
 382           'searchDescendentGroups' => FALSE,
 383           'expected_query' => [
 384             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',
 385             1 => ' FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )',
 386             2 => 'WHERE ( contact_a.contact_type IN ("Individual") AND civicrm_address.country_id IS NOT NULL ) AND (contact_a.is_deleted = 0)',
 387           ],
 388         ],
 389       ],
 390       [
 391         [
 392           'description' => 'Search builder behaviour for Activity',
 393           'class' => 'CRM_Contact_Selector',
 394           'settings' => [],
 395           'form_values' => ['source_contact_id' => ['IS NOT NULL' => 1]],
 396           'params' => [],
 397           'return_properties' => [
 398             'source_contact_id' => 1,
 399           ],
 400           'context' => 'builder',
 401           'action' => CRM_Core_Action::NONE,
 402           'includeContactIds' => NULL,
 403           'searchDescendentGroups' => FALSE,
 404           'expected_query' => [
 405             0 => 'SELECT contact_a.id as contact_id, source_contact.id as source_contact_id',
 406             2 => 'WHERE ( source_contact.id IS NOT NULL ) AND (contact_a.is_deleted = 0)',
 407           ],
 408         ],
 409       ],
 410       [
 411         [
 412           'description' => 'Test display relationships',
 413           'class' => 'CRM_Contact_Selector',
 414           'settings' => [],
 415           'form_values' => ['display_relationship_type' => '1_b_a'],
 416           'return_properties' => NULL,
 417           'params' => [],
 418           'context' => 'advanced',
 419           'action' => CRM_Core_Action::NONE,
 420           'includeContactIds' => NULL,
 421           'searchDescendentGroups' => FALSE,
 422           'expected_query' => [
 423             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`',
 424             2 => 'WHERE displayRelType.relationship_type_id = 1
 425 AND   displayRelType.is_active = 1
 426 AND (contact_a.is_deleted = 0)',
 427           ],
 428         ],
 429       ],
 430     ];
 431   }
 432
 433   /**
 434    * Test the contact ID query does not fail on country search.
 435    */
 436   public function testContactIDQuery() {
 437     $params = [
 438       [
 439         0 => 'country-1',
 440         1 => '=',
 441         2 => '1228',
 442         3 => 1,
 443         4 => 0,
 444       ],
 445     ];
 446
 447     $searchOBJ = new CRM_Contact_Selector(NULL);
 448     $searchOBJ->contactIDQuery($params, '1_u');
 449   }
 450
 451   /**
 452    * Test the Search Builder using Non ASCII location type for email filter
 453    */
 454   public function testSelectorQueryOnNonASCIIlocationType() {
 455     $contactID = $this->individualCreate();
 456     $locationType = $this->locationTypeCreate([
 457       'name' => 'Non ASCII Location Type',
 458       'display_name' => 'Дом Location type',
 459       'vcard_name' => 'Non ASCII Location Type',
 460       'is_active' => 1,
 461     ]);
 462     $this->callAPISuccess('Email', 'create', [
 463       'contact_id' => $contactID,
 464       'location_type_id' => $locationType->id,
 465       'email' => 'test@test.com',
 466     ]);
 467
 468     $selector = new CRM_Contact_Selector(
 469       'CRM_Contact_Selector',
 470       ['email' => ['IS NOT NULL' => 1]],
 471       [
 472         [
 473           0 => 'email-' . $locationType->id,
 474           1 => 'IS NOT NULL',
 475           2 => NULL,
 476           3 => 1,
 477           4 => 0,
 478         ],
 479       ],
 480       [
 481         'contact_type' => 1,
 482         'contact_sub_type' => 1,
 483         'sort_name' => 1,
 484         'location' => [
 485           'Non ASCII Location Type' => [
 486             'location_type' => $locationType->id,
 487             'email' => 1,
 488           ],
 489         ],
 490       ],
 491       CRM_Core_Action::NONE,
 492       NULL,
 493       FALSE,
 494       'builder'
 495     );
 496
 497     $sql = $selector->getQueryObject()->query();
 498
 499     $expectedQuery = [
 500       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-email`.id as `Non_ASCII_Location_Type-email_id`, `Non_ASCII_Location_Type-email`.email as `Non_ASCII_Location_Type-email`",
 501       // @TODO these FROM clause doesn't matches due to extra spaces or special character
 502       2 => "WHERE  (  ( `Non_ASCII_Location_Type-email`.email IS NOT NULL )  )  AND (contact_a.is_deleted = 0)",
 503     ];
 504     foreach ($expectedQuery as $index => $queryString) {
 505       $this->assertEquals($this->strWrangle($queryString), $this->strWrangle($sql[$index]));
 506     }
 507
 508     $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 1, NULL);
 509     $this->assertEquals(1, count($rows));
 510     $this->assertEquals($contactID, key($rows));
 511     $this->assertEquals('test@test.com', $rows[$contactID]['Non_ASCII_Location_Type-email']);
 512   }
 513
 514   /**
 515    * Test the value use in where clause if it's case sensitive or not against each MySQL operators
 516    */
 517   public function testWhereClauseByOperator() {
 518     $contactID = $this->individualCreate(['first_name' => 'Adam']);
 519
 520     $filters = [
 521       'IS NOT NULL' => 1,
 522       '=' => 'Adam',
 523       'LIKE' => '%Ad%',
 524       'RLIKE' => '^A[a-z]{3}$',
 525       'IN' => ['IN' => ['Adam']],
 526     ];
 527     $filtersByWhereClause = [
 528       // doesn't matter
 529       'IS NOT NULL' => '( contact_a.first_name IS NOT NULL )',
 530       // case sensitive check
 531       '=' => "( contact_a.first_name = 'Adam' )",
 532       // case insensitive check
 533       'LIKE' => "( contact_a.first_name LIKE '%Ad%' )",
 534       // case sensitive check
 535       'RLIKE' => "(  contact_a.first_name RLIKE BINARY '^A[a-z]{3}$'  )",
 536       // case sensitive check
 537       'IN' => '( contact_a.first_name IN ("Adam") )',
 538     ];
 539     foreach ($filters as $op => $filter) {
 540       $selector = new CRM_Contact_Selector(
 541         'CRM_Contact_Selector',
 542         ['first_name' => [$op => $filter]],
 543         [
 544           [
 545             0 => 'first_name',
 546             1 => $op,
 547             2 => $filter,
 548             3 => 1,
 549             4 => 0,
 550           ],
 551         ],
 552         [],
 553         CRM_Core_Action::NONE,
 554         NULL,
 555         FALSE,
 556         'builder'
 557       );
 558
 559       $sql = $selector->getQueryObject()->query();
 560       $this->assertEquals(TRUE, strpos($sql[2], $filtersByWhereClause[$op]));
 561
 562       $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 1, NULL);
 563       $this->assertEquals(1, count($rows));
 564       $this->assertEquals($contactID, key($rows));
 565     }
 566   }
 567
 568   /**
 569    * Test if custom table is added in from clause when
 570    * search results are ordered by a custom field.
 571    */
 572   public function testSelectorQueryOrderByCustomField() {
 573     //Search for any params.
 574     $params = [
 575       [
 576         0 => 'country-1',
 577         1 => '=',
 578         2 => '1228',
 579         3 => 1,
 580         4 => 0,
 581       ],
 582     ];
 583
 584     //Create a test custom group and field.
 585     $customGroup = $this->callAPISuccess('CustomGroup', 'create', [
 586       'title' => "test custom group",
 587       'extends' => "Individual",
 588     ]);
 589     $cgTableName = $customGroup['values'][$customGroup['id']]['table_name'];
 590     $customField = $this->callAPISuccess('CustomField', 'create', [
 591       'custom_group_id' => $customGroup['id'],
 592       'label' => "test field",
 593       'html_type' => "Text",
 594     ]);
 595     $customFieldId = $customField['id'];
 596
 597     //Sort by the custom field created above.
 598     $sortParams = [
 599       1 => [
 600         'name' => 'test field',
 601         'sort' => "custom_{$customFieldId}",
 602       ],
 603     ];
 604     $sort = new CRM_Utils_Sort($sortParams, '1_d');
 605
 606     //Form a query to order by a custom field.
 607     $query = new CRM_Contact_BAO_Query($params,
 608       CRM_Contact_BAO_Query::NO_RETURN_PROPERTIES,
 609       NULL, FALSE, FALSE, 1,
 610       FALSE, TRUE, TRUE, NULL,
 611       'AND'
 612     );
 613     $query->searchQuery(0, 0, $sort,
 614       FALSE, FALSE, FALSE,
 615       FALSE, FALSE
 616     );
 617     //Check if custom table is included in $query->_tables.
 618     $this->assertTrue(in_array($cgTableName, array_keys($query->_tables)));
 619     //Assert if from clause joins the custom table.
 620     $this->assertTrue(strpos($query->_fromClause, $cgTableName) !== FALSE);
 621     $this->callAPISuccess('CustomField', 'delete', ['id' => $customField['id']]);
 622     $this->callAPISuccess('CustomGroup', 'delete', ['id' => $customGroup['id']]);
 623   }
 624
 625   /**
 626    * Check where clause of a date custom field when 'IS NOT EMPTY' operator is used
 627    */
 628   public function testCustomDateField() {
 629     $contactID = $this->individualCreate();
 630     //Create a test custom group and field.
 631     $customGroup = $this->callAPISuccess('CustomGroup', 'create', [
 632       'title' => "test custom group",
 633       'extends' => "Individual",
 634     ]);
 635     $customTableName = $this->callAPISuccess('CustomGroup', 'getValue', ['id' => $customGroup['id'], 'return' => 'table_name']);
 636     $customGroupTableName = $customGroup['values'][$customGroup['id']]['table_name'];
 637
 638     $createdField = $this->callAPISuccess('customField', 'create', [
 639       'data_type' => 'Date',
 640       'html_type' => 'Select Date',
 641       'date_format' => 'd M yy',
 642       'time_format' => 1,
 643       'label' => 'test field',
 644       'custom_group_id' => $customGroup['id'],
 645     ]);
 646     $customFieldColumnName = $createdField['values'][$createdField['id']]['column_name'];
 647
 648     $this->callAPISuccess('Contact', 'create', [
 649       'id' => $contactID,
 650       'custom_' . $createdField['id'] => date('YmdHis'),
 651     ]);
 652
 653     $selector = new CRM_Contact_Selector(
 654       'CRM_Contact_Selector',
 655       ['custom_' . $createdField['id'] => ['IS NOT EMPTY' => 1]],
 656       [
 657         [
 658           0 => 'custom_' . $createdField['id'],
 659           1 => 'IS NOT NULL',
 660           2 => 1,
 661           3 => 1,
 662           4 => 0,
 663         ],
 664       ],
 665       [],
 666       CRM_Core_Action::NONE,
 667       NULL,
 668       FALSE,
 669       'builder'
 670     );
 671
 672     $whereClause = $selector->getQueryObject()->query()[2];
 673     $expectedClause = sprintf("( %s.%s IS NOT NULL )", $customGroupTableName, $customFieldColumnName);
 674     // test the presence of expected date clause
 675     $this->assertEquals(TRUE, strpos($whereClause, $expectedClause));
 676
 677     $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 1, NULL);
 678     $this->assertEquals(1, count($rows));
 679   }
 680
 681   /**
 682    * Get the default select string since this is generally consistent.
 683    */
 684   public function getDefaultSelectString() {
 685     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`,'
 686       . ' 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`,'
 687       . ' 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`,'
 688       . ' 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`,'
 689       . ' contact_a.preferred_communication_method  as `preferred_communication_method`, contact_a.preferred_language  as `preferred_language`,'
 690       . ' 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`,'
 691       . ' 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`,'
 692       . ' 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`,'
 693       . ' contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`,'
 694       . ' 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`,'
 695       . ' IF ( contact_a.contact_type = \'Individual\', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id,'
 696       . ' civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, '
 697       . '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`, '
 698       . '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`, '
 699       . '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, '
 700       . '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, '
 701       . 'civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`';
 702   }
 703
 704   /**
 705    * Get the default from string since this is generally consistent.
 706    */
 707   public function getDefaultFromString() {
 708     return ' FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )'
 709       . ' LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id ) '
 710       . ' LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)'
 711       . ' LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)'
 712       . ' LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) '
 713       . 'LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ';
 714   }
 715
 716   /**
 717    * Strangle strings into a more matchable format.
 718    *
 719    * @param string $string
 720    *
 721    * @return string
 722    */
 723   public function strWrangle($string) {
 724     return trim(str_replace('  ', ' ', $string));
 725   }
 726
 727   /**
 728    * Swap out default parts of the query for the actual string.
 729    *
 730    * Note that it seems to make more sense to resolve this earlier & pass it in from a clean code point of
 731    * view, but the output on fail includes long sql statements that are of low relevance then.
 732    *
 733    * @param array $expectedQuery
 734    */
 735   public function wrangleDefaultClauses(&$expectedQuery) {
 736     if (CRM_Utils_Array::value(0, $expectedQuery) == 'default') {
 737       $expectedQuery[0] = $this->getDefaultSelectString();
 738     }
 739     if (CRM_Utils_Array::value(1, $expectedQuery) == 'default') {
 740       $expectedQuery[1] = $this->getDefaultFromString();
 741     }
 742   }
 743
 744 }