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

   1 <?php
   2
   3 /**
   4  *  Include dataProvider for tests
   5  * @group headless
   6  */
   7 class CRM_Contact_BAO_QueryTest extends CiviUnitTestCase {
   8
   9   /**
  10    * @return CRM_Contact_BAO_QueryTestDataProvider
  11    */
  12   public function dataProvider() {
  13     return new CRM_Contact_BAO_QueryTestDataProvider();
  14   }
  15
  16   public function setUp() {
  17     parent::setUp();
  18   }
  19
  20   public function tearDown() {
  21     $tablesToTruncate = array(
  22       'civicrm_group_contact',
  23       'civicrm_group',
  24       'civicrm_saved_search',
  25       'civicrm_entity_tag',
  26       'civicrm_tag',
  27       'civicrm_contact',
  28       'civicrm_address',
  29     );
  30     $this->quickCleanup($tablesToTruncate);
  31   }
  32
  33   /**
  34    *  Test CRM_Contact_BAO_Query::searchQuery()
  35    * @dataProvider dataProvider
  36    * @param $fv
  37    * @param $count
  38    * @param $ids
  39    * @param $full
  40    */
  41   public function testSearch($fv, $count, $ids, $full) {
  42     $op = new PHPUnit_Extensions_Database_Operation_Insert();
  43     $op->execute($this->_dbconn,
  44       $this->createFlatXMLDataSet(
  45         dirname(__FILE__) . '/queryDataset.xml'
  46       )
  47     );
  48
  49     $params = CRM_Contact_BAO_Query::convertFormValues($fv);
  50     $obj = new CRM_Contact_BAO_Query($params);
  51
  52     // let's set useGroupBy=true since we are listing contacts here who might belong to
  53     // more than one group / tag / notes etc.
  54     $obj->_useGroupBy = TRUE;
  55
  56     $dao = $obj->searchQuery();
  57
  58     $contacts = array();
  59     while ($dao->fetch()) {
  60       $contacts[] = $dao->contact_id;
  61     }
  62
  63     sort($contacts, SORT_NUMERIC);
  64
  65     $this->assertEquals($ids, $contacts);
  66   }
  67
  68   /**
  69    * Check that we get a successful result querying for home address.
  70    * CRM-14263 search builder failure with search profile & address in criteria
  71    */
  72   public function testSearchProfileHomeCityCRM14263() {
  73     $contactID = $this->individualCreate();
  74     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
  75     $this->callAPISuccess('address', 'create', array(
  76         'contact_id' => $contactID,
  77         'city' => 'Cool City',
  78         'location_type_id' => 1,
  79       ));
  80     $params = array(
  81       0 => array(
  82         0 => 'city-1',
  83         1 => '=',
  84         2 => 'Cool City',
  85         3 => 1,
  86         4 => 0,
  87       ),
  88     );
  89     $returnProperties = array(
  90       'contact_type' => 1,
  91       'contact_sub_type' => 1,
  92       'sort_name' => 1,
  93     );
  94
  95     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
  96     try {
  97       $resultDAO = $queryObj->searchQuery(0, 0, NULL,
  98         FALSE, FALSE,
  99         FALSE, FALSE,
 100         FALSE);
 101       $this->assertTrue($resultDAO->fetch());
 102     }
 103     catch (PEAR_Exception $e) {
 104       $err = $e->getCause();
 105       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 106
 107     }
 108   }
 109
 110   /**
 111    * Check that we get a successful result querying for home address.
 112    * CRM-14263 search builder failure with search profile & address in criteria
 113    */
 114   public function testSearchProfileHomeCityNoResultsCRM14263() {
 115     $contactID = $this->individualCreate();
 116     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
 117     $this->callAPISuccess('address', 'create', array(
 118         'contact_id' => $contactID,
 119         'city' => 'Cool City',
 120         'location_type_id' => 1,
 121       ));
 122     $params = array(
 123       0 => array(
 124         0 => 'city-1',
 125         1 => '=',
 126         2 => 'Dumb City',
 127         3 => 1,
 128         4 => 0,
 129       ),
 130     );
 131     $returnProperties = array(
 132       'contact_type' => 1,
 133       'contact_sub_type' => 1,
 134       'sort_name' => 1,
 135     );
 136
 137     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 138     try {
 139       $resultDAO = $queryObj->searchQuery(0, 0, NULL,
 140         FALSE, FALSE,
 141         FALSE, FALSE,
 142         FALSE);
 143       $this->assertFalse($resultDAO->fetch());
 144     }
 145     catch (PEAR_Exception $e) {
 146       $err = $e->getCause();
 147       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 148
 149     }
 150   }
 151
 152   /**
 153    * CRM-14263 search builder failure with search profile & address in criteria
 154    * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
 155    * so few tests detecting any change seems good here :-)
 156    */
 157   public function testSearchProfilePrimaryCityCRM14263() {
 158     $contactID = $this->individualCreate();
 159     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
 160     $this->callAPISuccess('address', 'create', array(
 161         'contact_id' => $contactID,
 162         'city' => 'Cool City',
 163         'location_type_id' => 1,
 164       ));
 165     $params = array(
 166       0 => array(
 167         0 => 'city',
 168         1 => '=',
 169         2 => 'Cool City',
 170         3 => 1,
 171         4 => 0,
 172       ),
 173     );
 174     $returnProperties = array(
 175       'contact_type' => 1,
 176       'contact_sub_type' => 1,
 177       'sort_name' => 1,
 178     );
 179     $expectedSQL = "SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, civicrm_address.id as address_id, civicrm_address.city as `city`  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE  (  ( LOWER(civicrm_address.city) = 'cool city' )  )  AND (contact_a.is_deleted = 0)    ORDER BY `contact_a`.`sort_name` asc, `contact_a`.`id` ";
 180     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 181     try {
 182       $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL,
 183         FALSE, FALSE,
 184         FALSE, FALSE,
 185         TRUE));
 186     }
 187     catch (PEAR_Exception $e) {
 188       $err = $e->getCause();
 189       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 190
 191     }
 192   }
 193
 194   /**
 195    * Test set up to test calling the query object per GroupContactCache BAO usage.
 196    *
 197    * CRM-17254 ensure that if only the contact_id is required other fields should
 198    * not be appended.
 199    */
 200   public function testGroupContactCacheAddSearch() {
 201     $returnProperties = array('contact_id');
 202     $params = array(array('group', 'IN', array(1), 0, 0));
 203
 204     $query = new CRM_Contact_BAO_Query(
 205       $params, $returnProperties,
 206       NULL, TRUE, FALSE, 1,
 207       TRUE,
 208       TRUE, FALSE
 209     );
 210
 211     list($select) = $query->query(FALSE);
 212     $this->assertEquals('SELECT contact_a.id as contact_id', $select);
 213   }
 214
 215   /**
 216    * Test smart groups with non-numeric don't fail on range queries.
 217    *
 218    * CRM-14720
 219    */
 220   public function testNumericPostal() {
 221     $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
 222     $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
 223     $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
 224     $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
 225
 226     $params = array(array('postal_code_low', '=', 5, 0, 0));
 227     CRM_Contact_BAO_Query::convertFormValues($params);
 228
 229     $query = new CRM_Contact_BAO_Query(
 230       $params, array('contact_id'),
 231       NULL, TRUE, FALSE, 1,
 232       TRUE,
 233       TRUE, FALSE
 234     );
 235
 236     $sql = $query->query(FALSE);
 237     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 238     $this->assertEquals(2, $result->N);
 239
 240     // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
 241     // results in mysql warnings & hence fatal errors.
 242     /// I was unable to get mysql warnings to activate in the context of the unit tests - but
 243     // felt this code still provided a useful bit of coverage as it runs the various queries to load
 244     // the group & could generate invalid sql if a bug were introduced.
 245     $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
 246     $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams);
 247     CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
 248   }
 249
 250   /**
 251    * Test searches are case insensitive.
 252    */
 253   public function testCaseInsensitive() {
 254     $orgID = $this->organizationCreate(array('organization_name' => 'BOb'));
 255     $this->callAPISuccess('Contact', 'create', array('display_name' => 'Minnie Mouse', 'employer_id' => $orgID, 'contact_type' => 'Individual'));
 256     $searchParams = array(array('current_employer', '=', 'bob', 0, 1));
 257     $query = new CRM_Contact_BAO_Query($searchParams);
 258     $result = $query->apiQuery($searchParams);
 259     $this->assertEquals(1, count($result[0]));
 260     $contact = reset($result[0]);
 261     $this->assertEquals('Minnie Mouse', $contact['display_name']);
 262     $this->assertEquals('BOb', $contact['current_employer']);
 263   }
 264
 265   /**
 266    * Test smart groups with non-numeric don't fail on equal queries.
 267    *
 268    * CRM-14720
 269    */
 270   public function testNonNumericEqualsPostal() {
 271     $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
 272     $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
 273     $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
 274     $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
 275
 276     $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0));
 277     CRM_Contact_BAO_Query::convertFormValues($params);
 278
 279     $query = new CRM_Contact_BAO_Query(
 280       $params, array('contact_id'),
 281       NULL, TRUE, FALSE, 1,
 282       TRUE,
 283       TRUE, FALSE
 284     );
 285
 286     $sql = $query->query(FALSE);
 287     $this->assertEquals("WHERE  ( civicrm_address.postal_code = 'eh10 4rb-889' )  AND (contact_a.is_deleted = 0)", $sql[2]);
 288     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 289     $this->assertEquals(1, $result->N);
 290
 291   }
 292
 293   /**
 294    * Test the group contact clause does not contain an OR.
 295    *
 296    * The search should return 3 contacts - 2 households in the smart group of
 297    * Contact Type = Household and one Individual hard-added to it. The
 298    * Household that meets both criteria should be returned once.
 299    */
 300   public function testGroupClause() {
 301     $this->householdCreate();
 302     $householdID = $this->householdCreate();
 303     $individualID = $this->individualCreate();
 304     $groupID = $this->smartGroupCreate();
 305     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $individualID, 'status' => 'Added'));
 306     $this->callAPISuccess('GroupContact', 'create', array('group_id' => $groupID, 'contact_id' => $householdID, 'status' => 'Added'));
 307
 308     // Refresh the cache for test purposes. It would be better to alter to alter the GroupContact add function to add contacts to the cache.
 309     CRM_Contact_BAO_GroupContactCache::remove($groupID, FALSE);
 310
 311     $sql = CRM_Contact_BAO_Query::getQuery(
 312       array(array('group', 'IN', array($groupID), 0, 0)),
 313       array('contact_id')
 314     );
 315
 316     $dao = CRM_Core_DAO::executeQuery($sql);
 317     $this->assertEquals(3, $dao->N);
 318     $this->assertFalse(strstr($sql, ' OR '));
 319
 320     $sql = CRM_Contact_BAO_Query::getQuery(
 321       array(array('group', 'IN', array($groupID), 0, 0)),
 322       array('contact_id' => 1, 'group' => 1)
 323     );
 324
 325     $dao = CRM_Core_DAO::executeQuery($sql);
 326     $this->assertEquals(3, $dao->N);
 327     $this->assertFalse(strstr($sql, ' OR '), 'Query does not include or');
 328     while ($dao->fetch()) {
 329       $this->assertTrue(($dao->groups == $groupID || $dao->groups == ',' . $groupID), $dao->groups . ' includes ' . $groupID);
 330     }
 331   }
 332
 333   /**
 334    * CRM-19562 ensure that only ids are used for contactid searching.
 335    */
 336   public function testContactIDClause() {
 337     $params = array(
 338       array("mark_x_2", "=", 1, 0, 0),
 339       array("mark_x_foo@example.com", "=", 1, 0, 0),
 340     );
 341     $returnProperties = array(
 342       "sort_name" => 1,
 343       "email" => 1,
 344       "do_not_email" => 1,
 345       "is_deceased" => 1,
 346       "on_hold" => 1,
 347       "display_name" => 1,
 348       "preferred_mail_format" => 1,
 349     );
 350     $numberofContacts = 2;
 351     $query = new CRM_Contact_BAO_Query($params, $returnProperties);
 352     try {
 353       $query->apiQuery($params, $returnProperties, NULL, NULL, 0, $numberofContacts);
 354     }
 355     catch (Exception $e) {
 356       $this->assertEquals("A fatal error was triggered: One of parameters  (value: foo@example.com) is not of the type Positive",
 357         $e->getMessage());
 358       return $this->assertTrue(TRUE);
 359     }
 360     return $this->fail('Test failed for some reason which is not good');
 361   }
 362
 363 }