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

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