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

   1 <?php
   2
   3 require_once 'CiviTest/Contact.php';
   4
   5 /**
   6  *  Include dataProvider for tests
   7  * @group headless
   8  */
   9 class CRM_Contact_BAO_QueryTest extends CiviUnitTestCase {
  10
  11   /**
  12    * @return CRM_Contact_BAO_QueryTestDataProvider
  13    */
  14   public function dataProvider() {
  15     return new CRM_Contact_BAO_QueryTestDataProvider();
  16   }
  17
  18   public function setUp() {
  19     parent::setUp();
  20   }
  21
  22   public function tearDown() {
  23     $tablesToTruncate = array(
  24       'civicrm_group_contact',
  25       'civicrm_group',
  26       'civicrm_saved_search',
  27       'civicrm_entity_tag',
  28       'civicrm_tag',
  29       'civicrm_contact',
  30       'civicrm_address',
  31     );
  32     $this->quickCleanup($tablesToTruncate);
  33   }
  34
  35   /**
  36    *  Test CRM_Contact_BAO_Query::searchQuery()
  37    * @dataProvider dataProvider
  38    * @param $fv
  39    * @param $count
  40    * @param $ids
  41    * @param $full
  42    */
  43   public function testSearch($fv, $count, $ids, $full) {
  44     $op = new PHPUnit_Extensions_Database_Operation_Insert();
  45     $op->execute($this->_dbconn,
  46       $this->createFlatXMLDataSet(
  47         dirname(__FILE__) . '/queryDataset.xml'
  48       )
  49     );
  50
  51     $params = CRM_Contact_BAO_Query::convertFormValues($fv);
  52     $obj = new CRM_Contact_BAO_Query($params);
  53
  54     // let's set useGroupBy=true since we are listing contacts here who might belong to
  55     // more than one group / tag / notes etc.
  56     $obj->_useGroupBy = TRUE;
  57
  58     $dao = $obj->searchQuery();
  59
  60     $contacts = array();
  61     while ($dao->fetch()) {
  62       $contacts[] = $dao->contact_id;
  63     }
  64
  65     sort($contacts, SORT_NUMERIC);
  66
  67     $this->assertEquals($ids, $contacts);
  68   }
  69
  70   /**
  71    * Check that we get a successful result querying for home address.
  72    * CRM-14263 search builder failure with search profile & address in criteria
  73    */
  74   public function testSearchProfileHomeCityCRM14263() {
  75     $contactID = $this->individualCreate();
  76     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
  77     $this->callAPISuccess('address', 'create', array(
  78         'contact_id' => $contactID,
  79         'city' => 'Cool City',
  80         'location_type_id' => 1,
  81       ));
  82     $params = array(
  83       0 => array(
  84         0 => 'city-1',
  85         1 => '=',
  86         2 => 'Cool City',
  87         3 => 1,
  88         4 => 0,
  89       ),
  90     );
  91     $returnProperties = array(
  92       'contact_type' => 1,
  93       'contact_sub_type' => 1,
  94       'sort_name' => 1,
  95     );
  96
  97     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
  98     try {
  99       $resultDAO = $queryObj->searchQuery(0, 0, NULL,
 100         FALSE, FALSE,
 101         FALSE, FALSE,
 102         FALSE);
 103       $this->assertTrue($resultDAO->fetch());
 104     }
 105     catch (PEAR_Exception $e) {
 106       $err = $e->getCause();
 107       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 108
 109     }
 110   }
 111
 112   /**
 113    * Check that we get a successful result querying for home address.
 114    * CRM-14263 search builder failure with search profile & address in criteria
 115    */
 116   public function testSearchProfileHomeCityNoResultsCRM14263() {
 117     $contactID = $this->individualCreate();
 118     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
 119     $this->callAPISuccess('address', 'create', array(
 120         'contact_id' => $contactID,
 121         'city' => 'Cool City',
 122         'location_type_id' => 1,
 123       ));
 124     $params = array(
 125       0 => array(
 126         0 => 'city-1',
 127         1 => '=',
 128         2 => 'Dumb City',
 129         3 => 1,
 130         4 => 0,
 131       ),
 132     );
 133     $returnProperties = array(
 134       'contact_type' => 1,
 135       'contact_sub_type' => 1,
 136       'sort_name' => 1,
 137     );
 138
 139     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 140     try {
 141       $resultDAO = $queryObj->searchQuery(0, 0, NULL,
 142         FALSE, FALSE,
 143         FALSE, FALSE,
 144         FALSE);
 145       $this->assertFalse($resultDAO->fetch());
 146     }
 147     catch (PEAR_Exception $e) {
 148       $err = $e->getCause();
 149       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 150
 151     }
 152   }
 153
 154   /**
 155    * CRM-14263 search builder failure with search profile & address in criteria
 156    * We are retrieving primary here - checking the actual sql seems super prescriptive - but since the massive query object has
 157    * so few tests detecting any change seems good here :-)
 158    */
 159   public function testSearchProfilePrimaryCityCRM14263() {
 160     $contactID = $this->individualCreate();
 161     CRM_Core_Config::singleton()->defaultSearchProfileID = 1;
 162     $this->callAPISuccess('address', 'create', array(
 163         'contact_id' => $contactID,
 164         'city' => 'Cool City',
 165         'location_type_id' => 1,
 166       ));
 167     $params = array(
 168       0 => array(
 169         0 => 'city',
 170         1 => '=',
 171         2 => 'Cool City',
 172         3 => 1,
 173         4 => 0,
 174       ),
 175     );
 176     $returnProperties = array(
 177       'contact_type' => 1,
 178       'contact_sub_type' => 1,
 179       'sort_name' => 1,
 180     );
 181     $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` ";
 182     $queryObj = new CRM_Contact_BAO_Query($params, $returnProperties);
 183     try {
 184       $this->assertEquals($expectedSQL, $queryObj->searchQuery(0, 0, NULL,
 185         FALSE, FALSE,
 186         FALSE, FALSE,
 187         TRUE));
 188     }
 189     catch (PEAR_Exception $e) {
 190       $err = $e->getCause();
 191       $this->fail('invalid SQL created' . $e->getMessage() . " " . $err->userinfo);
 192
 193     }
 194   }
 195
 196   /**
 197    * Test set up to test calling the query object per GroupContactCache BAO usage.
 198    *
 199    * CRM-17254 ensure that if only the contact_id is required other fields should
 200    * not be appended.
 201    */
 202   public function testGroupContactCacheAddSearch() {
 203     $returnProperties = array('contact_id');
 204     $params = array(array('group', 'IN', array(1), 0, 0));
 205
 206     $query = new CRM_Contact_BAO_Query(
 207       $params, $returnProperties,
 208       NULL, TRUE, FALSE, 1,
 209       TRUE,
 210       TRUE, FALSE
 211     );
 212
 213     list($select) = $query->query(FALSE);
 214     $this->assertEquals('SELECT contact_a.id as contact_id', $select);
 215   }
 216
 217   /**
 218    * Test smart groups with non-numeric don't fail on range queries.
 219    *
 220    * CRM-14720
 221    */
 222   public function testNumericPostal() {
 223     $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
 224     $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
 225     $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
 226     $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
 227
 228     $params = array(array('postal_code_low', '=', 5, 0, 0));
 229     CRM_Contact_BAO_Query::convertFormValues($params);
 230
 231     $query = new CRM_Contact_BAO_Query(
 232       $params, array('contact_id'),
 233       NULL, TRUE, FALSE, 1,
 234       TRUE,
 235       TRUE, FALSE
 236     );
 237
 238     $sql = $query->query(FALSE);
 239     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 240     $this->assertEquals(2, $result->N);
 241
 242     // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
 243     // results in mysql warnings & hence fatal errors.
 244     /// I was unable to get mysql warnings to activate in the context of the unit tests - but
 245     // felt this code still provided a useful bit of coverage as it runs the various queries to load
 246     // the group & could generate invalid sql if a bug were introduced.
 247     $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
 248     $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams);
 249     CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
 250   }
 251
 252   /**
 253    * Test smart groups with non-numeric don't fail on equal queries.
 254    *
 255    * CRM-14720
 256    */
 257   public function testNonNumericEqualsPostal() {
 258     $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
 259     $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
 260     $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
 261     $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
 262
 263     $params = array(array('postal_code', '=', 'EH10 4RB-889', 0, 0));
 264     CRM_Contact_BAO_Query::convertFormValues($params);
 265
 266     $query = new CRM_Contact_BAO_Query(
 267       $params, array('contact_id'),
 268       NULL, TRUE, FALSE, 1,
 269       TRUE,
 270       TRUE, FALSE
 271     );
 272
 273     $sql = $query->query(FALSE);
 274     $this->assertEquals("WHERE  ( civicrm_address.postal_code = 'eh10 4rb-889' )  AND (contact_a.is_deleted = 0)", $sql[2]);
 275     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
 276     $this->assertEquals(1, $result->N);
 277
 278   }
 279
 280 }