tests/phpunit/CRM/Contact/SelectorTest.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *  Include parent class definition
  30  */
  31
  32 /**
  33  *  Test contact custom search functions
  34  *
  35  * @package CiviCRM
  36  * @group headless
  37  */
  38 class CRM_Contact_Form_SelectorTest extends CiviUnitTestCase {
  39
  40   public function tearDown() {
  41
  42   }
  43   /**
  44    * Test the query from the selector class is consistent with the dataset expectation.
  45    *
  46    * @param array $dataSet
  47    *   The data set to be tested. Note that when adding new datasets often only form_values and expected where
  48    *   clause will need changing.
  49    *
  50    * @dataProvider querySets
  51    */
  52   public function testSelectorQuery($dataSet) {
  53     $params = CRM_Contact_BAO_Query::convertFormValues($dataSet['form_values'], 0, FALSE, NULL, array());
  54     foreach ($dataSet['settings'] as $setting) {
  55       $this->callAPISuccess('Setting', 'create', array($setting['name'] => $setting['value']));
  56     }
  57     $selector = new CRM_Contact_Selector(
  58       $dataSet['class'],
  59       $dataSet['form_values'],
  60       $params,
  61       $dataSet['return_properties'],
  62       $dataSet['action'],
  63       $dataSet['includeContactIds'],
  64       $dataSet['searchDescendentGroups'],
  65       $dataSet['context']
  66     );
  67     $queryObject = $selector->getQueryObject();
  68     $sql = $queryObject->query();
  69     $this->wrangleDefaultClauses($dataSet['expected_query']);
  70     foreach ($dataSet['expected_query'] as $index => $queryString) {
  71       $this->assertEquals($this->strWrangle($queryString), $this->strWrangle($sql[$index]));
  72     }
  73     // Ensure that search builder return individual contact as per criteria
  74     if (!empty($dataSet['context'] == 'builder')) {
  75       $contactID = $this->individualCreate(['first_name' => 'James', 'last_name' => 'Bond']);
  76       if ('Search builder behaviour for Activity' == $dataSet['description']) {
  77         $this->callAPISuccess('Activity', 'create', [
  78           'activity_type_id' => 'Meeting',
  79           'subject' => "Test",
  80           'source_contact_id' => $contactID,
  81         ]);
  82         $rows = CRM_Core_DAO::executeQuery(implode(' ', $sql))->fetchAll();
  83         $this->assertEquals(1, count($rows));
  84         $this->assertEquals($contactID, $rows[0]['source_contact_id']);
  85       }
  86       else {
  87         $this->callAPISuccess('Address', 'create', [
  88           'contact_id' => $contactID,
  89           'location_type_id' => "Home",
  90           'is_primary' => 1,
  91           'country_id' => "IN",
  92         ]);
  93         $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, 50, '');
  94         $this->assertEquals(1, count($rows));
  95         $sortChar = $selector->alphabetQuery()->fetchAll();
  96         // sort name is stored in '<last_name>, <first_name>' format, as per which the first character would be B of Bond
  97         $this->assertEquals('B', $sortChar[0]['sort_name']);
  98         $this->assertEquals($contactID, key($rows));
  99       }
 100     }
 101   }
 102
 103   /**
 104    * Test the civicrm_prevnext_cache entry if it correctly stores the search query result
 105    */
 106   public function testPrevNextCache() {
 107     $contactID = $this->individualCreate(['email' => 'mickey@mouseville.com']);
 108     $dataSet = array(
 109       'description' => 'Normal default behaviour',
 110       'class' => 'CRM_Contact_Selector',
 111       'settings' => array(),
 112       'form_values' => array('email' => 'mickey@mouseville.com'),
 113       'params' => array(),
 114       'return_properties' => NULL,
 115       'context' => 'advanced',
 116       'action' => CRM_Core_Action::ADVANCED,
 117       'includeContactIds' => NULL,
 118       'searchDescendentGroups' => FALSE,
 119       'expected_query' => array(
 120         0 => 'default',
 121         1 => 'default',
 122         2 => "WHERE  ( civicrm_email.email LIKE '%mickey@mouseville.com%' )  AND (contact_a.is_deleted = 0)",
 123       ),
 124     );
 125     $params = CRM_Contact_BAO_Query::convertFormValues($dataSet['form_values'], 0, FALSE, NULL, array());
 126
 127     // create CRM_Contact_Selector instance and set desired query params
 128     $selector = new CRM_Contact_Selector(
 129       $dataSet['class'],
 130       $dataSet['form_values'],
 131       $params,
 132       $dataSet['return_properties'],
 133       $dataSet['action'],
 134       $dataSet['includeContactIds'],
 135       $dataSet['searchDescendentGroups'],
 136       $dataSet['context']
 137     );
 138     // set cache key
 139     $key = substr(sha1(rand()), 0, 7);
 140     $selector->setKey($key);
 141
 142     // fetch row and check the result
 143     $rows = $selector->getRows(CRM_Core_Action::VIEW, 0, TRUE, NULL);
 144     $this->assertEquals(1, count($rows));
 145     $this->assertEquals($contactID, key($rows));
 146
 147     // build cache key and use to it to fetch prev-next cache record
 148     $cacheKey = 'civicrm search ' . $key;
 149     $contacts = CRM_Utils_SQL_Select::from('civicrm_prevnext_cache')
 150                   ->select(['entity_table', 'entity_id1', 'cacheKey'])
 151                   ->where("cacheKey = '!key'")
 152                   ->param('!key', $cacheKey)
 153                   ->execute()
 154                   ->fetchAll();
 155     $this->assertEquals(1, count($contacts));
 156     // check the prevNext record matches
 157     $expectedEntry = [
 158       'entity_table' => 'civicrm_contact',
 159       'entity_id1' => $contactID,
 160       'cacheKey' => $cacheKey,
 161     ];
 162     $this->checkArrayEquals($contacts[0], $expectedEntry);
 163   }
 164
 165   /**
 166    * Data sets for testing.
 167    */
 168   public function querySets() {
 169     return array(
 170       array(
 171         array(
 172           'description' => 'Normal default behaviour',
 173           'class' => 'CRM_Contact_Selector',
 174           'settings' => array(),
 175           'form_values' => array('email' => 'mickey@mouseville.com'),
 176           'params' => array(),
 177           'return_properties' => NULL,
 178           'context' => 'advanced',
 179           'action' => CRM_Core_Action::ADVANCED,
 180           'includeContactIds' => NULL,
 181           'searchDescendentGroups' => FALSE,
 182           'expected_query' => array(
 183             0 => 'default',
 184             1 => 'default',
 185             2 => "WHERE  ( civicrm_email.email LIKE '%mickey@mouseville.com%' )  AND (contact_a.is_deleted = 0)",
 186           ),
 187         ),
 188       ),
 189       array(
 190         array(
 191           'description' => 'Normal default + user added wildcard',
 192           'class' => 'CRM_Contact_Selector',
 193           'settings' => array(),
 194           'form_values' => array('email' => '%mickey@mouseville.com', 'sort_name' => 'Mouse'),
 195           'params' => array(),
 196           'return_properties' => NULL,
 197           'context' => 'advanced',
 198           'action' => CRM_Core_Action::ADVANCED,
 199           'includeContactIds' => NULL,
 200           'searchDescendentGroups' => FALSE,
 201           'expected_query' => array(
 202             0 => 'default',
 203             1 => 'default',
 204             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)",
 205           ),
 206         ),
 207       ),
 208       array(
 209         array(
 210           'description' => 'Site set to not pre-pend wildcard',
 211           'class' => 'CRM_Contact_Selector',
 212           'settings' => array(array('name' => 'includeWildCardInName', 'value' => FALSE)),
 213           'form_values' => array('email' => 'mickey@mouseville.com', 'sort_name' => 'Mouse'),
 214           'params' => array(),
 215           'return_properties' => NULL,
 216           'context' => 'advanced',
 217           'action' => CRM_Core_Action::ADVANCED,
 218           'includeContactIds' => NULL,
 219           'searchDescendentGroups' => FALSE,
 220           'expected_query' => array(
 221             0 => 'default',
 222             1 => 'default',
 223             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)",
 224           ),
 225         ),
 226       ),
 227       array(
 228         array(
 229           'description' => 'Use of quotes for exact string',
 230           'use_case_comments' => 'This is something that was in the code but seemingly not working. No UI info on it though!',
 231           'class' => 'CRM_Contact_Selector',
 232           'settings' => array(array('name' => 'includeWildCardInName', 'value' => FALSE)),
 233           'form_values' => array('email' => '"mickey@mouseville.com"', 'sort_name' => 'Mouse'),
 234           'params' => array(),
 235           'return_properties' => NULL,
 236           'context' => 'advanced',
 237           'action' => CRM_Core_Action::ADVANCED,
 238           'includeContactIds' => NULL,
 239           'searchDescendentGroups' => FALSE,
 240           'expected_query' => array(
 241             0 => 'default',
 242             1 => 'default',
 243             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)",
 244           ),
 245         ),
 246       ),
 247       array(
 248         array(
 249           'description' => 'Normal search builder behaviour',
 250           'class' => 'CRM_Contact_Selector',
 251           'settings' => array(),
 252           'form_values' => array('contact_type' => 'Individual', 'country' => array('IS NOT NULL' => 1)),
 253           'params' => array(),
 254           'return_properties' => array(
 255             'contact_type' => 1,
 256             'contact_sub_type' => 1,
 257             'sort_name' => 1,
 258           ),
 259           'context' => 'builder',
 260           'action' => CRM_Core_Action::NONE,
 261           'includeContactIds' => NULL,
 262           'searchDescendentGroups' => FALSE,
 263           'expected_query' => array(
 264             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',
 265             1 => ' FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )',
 266             2 => 'WHERE ( contact_a.contact_type IN ("Individual") AND civicrm_address.country_id IS NOT NULL ) AND (contact_a.is_deleted = 0)',
 267           ),
 268         ),
 269       ),
 270       array(
 271         array(
 272           'description' => 'Search builder behaviour for Activity',
 273           'class' => 'CRM_Contact_Selector',
 274           'settings' => array(),
 275           'form_values' => array('source_contact_id' => array('IS NOT NULL' => 1)),
 276           'params' => array(),
 277           'return_properties' => array(
 278             'source_contact_id' => 1,
 279           ),
 280           'context' => 'builder',
 281           'action' => CRM_Core_Action::NONE,
 282           'includeContactIds' => NULL,
 283           'searchDescendentGroups' => FALSE,
 284           'expected_query' => array(
 285             0 => 'SELECT contact_a.id as contact_id, source_contact.id as source_contact_id',
 286             2 => 'WHERE ( source_contact.id IS NOT NULL ) AND (contact_a.is_deleted = 0)',
 287           ),
 288         ),
 289       ),
 290     );
 291   }
 292
 293   /**
 294    * Test the contact ID query does not fail on country search.
 295    */
 296   public function testContactIDQuery() {
 297     $params = [[
 298       0 => 'country-1',
 299       1 => '=',
 300       2 => '1228',
 301       3 => 1,
 302       4 => 0,
 303     ]];
 304
 305     $searchOBJ = new CRM_Contact_Selector(NULL);
 306     $searchOBJ->contactIDQuery($params, '1_u');
 307   }
 308
 309   /**
 310    * Test if custom table is added in from clause when
 311    * search results are ordered by a custom field.
 312    */
 313   public function testSelectorQueryOrderByCustomField() {
 314     //Search for any params.
 315     $params = [[
 316       0 => 'country-1',
 317       1 => '=',
 318       2 => '1228',
 319       3 => 1,
 320       4 => 0,
 321     ]];
 322
 323     //Create a test custom group and field.
 324     $customGroup = $this->callAPISuccess('CustomGroup', 'create', array(
 325       'title' => "test custom group",
 326       'extends' => "Individual",
 327     ));
 328     $cgTableName = $customGroup['values'][$customGroup['id']]['table_name'];
 329     $customField = $this->callAPISuccess('CustomField', 'create', array(
 330       'custom_group_id' => $customGroup['id'],
 331       'label' => "test field",
 332       'html_type' => "Text",
 333     ));
 334     $customFieldId = $customField['id'];
 335
 336     //Sort by the custom field created above.
 337     $sortParams = array(
 338       1 => array(
 339         'name' => 'test field',
 340         'sort' => "custom_{$customFieldId}",
 341       ),
 342     );
 343     $sort = new CRM_Utils_Sort($sortParams, '1_d');
 344
 345     //Form a query to order by a custom field.
 346     $query = new CRM_Contact_BAO_Query($params,
 347       CRM_Contact_BAO_Query::NO_RETURN_PROPERTIES,
 348       NULL, FALSE, FALSE, 1,
 349       FALSE, TRUE, TRUE, NULL,
 350       'AND'
 351     );
 352     $query->searchQuery(0, 0, $sort,
 353       FALSE, FALSE, FALSE,
 354       FALSE, FALSE
 355     );
 356     //Check if custom table is included in $query->_tables.
 357     $this->assertTrue(in_array($cgTableName, array_keys($query->_tables)));
 358     //Assert if from clause joins the custom table.
 359     $this->assertTrue(strpos($query->_fromClause, $cgTableName) !== FALSE);
 360   }
 361
 362   /**
 363    * Get the default select string since this is generally consistent.
 364    */
 365   public function getDefaultSelectString() {
 366     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`,'
 367     . ' 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`,'
 368     . ' 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`,'
 369     . ' 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`,'
 370     . ' contact_a.preferred_communication_method  as `preferred_communication_method`, contact_a.preferred_language  as `preferred_language`,'
 371     . ' 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`,'
 372     . ' 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`,'
 373     . ' 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`,'
 374     . ' contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`,'
 375     . ' 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`,'
 376     . ' IF ( contact_a.contact_type = \'Individual\', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id,'
 377     . ' civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, '
 378     . '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`, '
 379     . '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`, '
 380     . '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, '
 381     . '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, '
 382     . 'civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`';
 383   }
 384
 385   /**
 386    * Get the default from string since this is generally consistent.
 387    */
 388   public function getDefaultFromString() {
 389     return ' FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )'
 390     . ' LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)'
 391     . ' LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)'
 392     . ' LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1) '
 393     . 'LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id ';
 394   }
 395
 396   /**
 397    * Strangle strings into a more matchable format.
 398    *
 399    * @param string $string
 400    * @return string
 401    */
 402   public function strWrangle($string) {
 403     return str_replace('  ', ' ', $string);
 404   }
 405
 406   /**
 407    * Swap out default parts of the query for the actual string.
 408    *
 409    * Note that it seems to make more sense to resolve this earlier & pass it in from a clean code point of
 410    * view, but the output on fail includes long sql statements that are of low relevance then.
 411    *
 412    * @param array $expectedQuery
 413    */
 414   public function wrangleDefaultClauses(&$expectedQuery) {
 415     if (CRM_Utils_Array::value(0, $expectedQuery) == 'default') {
 416       $expectedQuery[0] = $this->getDefaultSelectString();
 417     }
 418     if (CRM_Utils_Array::value(1, $expectedQuery) == 'default') {
 419       $expectedQuery[1] = $this->getDefaultFromString();
 420     }
 421   }
 422
 423 }