tests/phpunit/CRM/Core/BAO/CustomQueryTest.php

   1 <?php
   2
   3 /**
   4  *  Include dataProvider for tests
   5  * @group headless
   6  */
   7 class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase {
   8   use CRMTraits_Custom_CustomDataTrait;
   9
  10   /**
  11    * Restore database to empty state.
  12    *
  13    * Note that rollback won't remove custom tables.
  14    *
  15    * @throws \Exception
  16    */
  17   public function tearDown() {
  18     $tablesToTruncate = [
  19       'civicrm_contact',
  20     ];
  21     $this->quickCleanup($tablesToTruncate, TRUE);
  22     parent::tearDown();
  23   }
  24
  25   /**
  26    * Test filtering by relative custom data dates.
  27    *
  28    * @throws \CRM_Core_Exception
  29    */
  30   public function testSearchCustomDataDateRelative() {
  31     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
  32     $dateCustomField = $this->customFieldCreate([
  33       'custom_group_id' => $ids['custom_group_id'],
  34       'label' => 'date field',
  35       'data_type' => 'Date',
  36       'html_type' => 'Select Date',
  37       'default_value' => NULL,
  38     ]);
  39     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
  40     $formValues = [
  41       $dateCustomFieldName . '_relative' => 'this.year',
  42       $dateCustomFieldName . '_from' => '',
  43       $dateCustomFieldName . '_to' => '',
  44     ];
  45     // Assigning the relevant form value to be within a custom key is normally done in
  46     // build field params. It would be better if it were all done in convertFormValues
  47     // but for now we just imitate it.
  48
  49     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
  50     $queryObj = new CRM_Contact_BAO_Query($params);
  51     $this->assertEquals(
  52       "civicrm_value_testsearchcus_1.date_field_2 BETWEEN '" . date('Y') . "0101000000' AND '" . date('Y') . "1231235959'",
  53       $queryObj->_where[0][0]
  54     );
  55     $this->assertEquals('date field is This calendar year (between January 1st, ' . date('Y') . " 12:00 AM and December 31st, " . date('Y') . " 11:59 PM)", $queryObj->_qill[0][0]);
  56     $queryObj = new CRM_Contact_BAO_Query($params);
  57     $this->assertEquals([
  58       'id' => $dateCustomField['id'],
  59       'label' => 'date field',
  60       'extends' => 'Contact',
  61       'data_type' => 'Date',
  62       'html_type' => 'Select Date',
  63       'is_search_range' => '0',
  64       'column_name' => 'date_field_' . $dateCustomField['id'],
  65       'table_name' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'],
  66       'option_group_id' => NULL,
  67       'groupTitle' => 'testSearchCustomDataDateRelative',
  68       'default_value' => NULL,
  69       'text_length' => NULL,
  70       'options_per_line' => NULL,
  71       'custom_group_id' => '1',
  72       'extends_entity_column_value' => NULL,
  73       'extends_entity_column_id' => NULL,
  74       'is_view' => '0',
  75       'is_multiple' => '0',
  76       'date_format' => 'mm/dd/yy',
  77       'time_format' => NULL,
  78       'is_required' => '0',
  79       'extends_table' => 'civicrm_contact',
  80       'search_table' => 'contact_a',
  81       'headerPattern' => '//',
  82       'title' => 'date field',
  83       'custom_field_id' => $dateCustomField['id'],
  84       'name' => 'custom_' . $dateCustomField['id'],
  85       'type' => 4,
  86       'where' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'] . '.date_field_' . $dateCustomField['id'],
  87       'import' => 1,
  88     ], $queryObj->getFieldSpec('custom_' . $dateCustomField['id']));
  89
  90   }
  91
  92   /**
  93    * Test filtering by the renamed custom date fields.
  94    *
  95    * The conversion to date picker will result int these fields
  96    * being renamed _high & _low and needing to return correctly.
  97    *
  98    * @throws \API_Exception
  99    * @throws \CRM_Core_Exception
 100    * @throws \Civi\API\Exception\UnauthorizedException
 101    */
 102   public function testSearchCustomDataDateHighLow() {
 103     $this->createCustomGroupWithFieldOfType([], 'date');
 104     $dateCustomFieldName = $this->getCustomFieldName('date');
 105     // Assigning the relevant form value to be within a custom key is normally done in
 106     // build field params. It would be better if it were all done in convertFormValues
 107     // but for now we just imitate it.
 108     $formValues = [
 109       $dateCustomFieldName . '_low' => '2014-06-06',
 110       $dateCustomFieldName . '_high' => '2015-06-06',
 111     ];
 112
 113     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 114     $queryObject = new CRM_Contact_BAO_Query($params);
 115     $queryObject->query();
 116     $this->assertEquals(
 117       '( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\' ) AND
 118 ( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' <= \'20150606235959\' )',
 119       trim($queryObject->_where[0][0])
 120     );
 121     $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM" AND less than or equal to "June 6th, 2015 11:59 PM"', $queryObject->_qill[0][0]);
 122     $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']);
 123     $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables[$this->getCustomGroupTable()]));
 124   }
 125
 126   /**
 127    * Test filtering by the renamed custom date fields.
 128    *
 129    * The conversion to date picker will result int these fields
 130    * being renamed _high & _low and needing to return correctly.
 131    *
 132    * @throws \API_Exception
 133    * @throws \CRM_Core_Exception
 134    * @throws \Civi\API\Exception\UnauthorizedException
 135    */
 136   public function testSearchCustomDataDateLowWithPermsInPlay() {
 137     $this->createLoggedInUser();
 138     CRM_Core_Config::singleton()->userPermissionClass->permissions = ['view all contacts', 'access all custom data'];
 139     $this->createCustomGroupWithFieldOfType([], 'date');
 140     $dateCustomFieldName = $this->getCustomFieldName('date');
 141     // Assigning the relevant form value to be within a custom key is normally done in
 142     // build field params. It would be better if it were all done in convertFormValues
 143     // but for now we just imitate it.
 144     $formValues = [
 145       $dateCustomFieldName . '_low' => '2014-06-06',
 146     ];
 147
 148     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 149     $queryObject = new CRM_Contact_BAO_Query($params);
 150     $queryObject->query();
 151     $this->assertEquals(
 152       'civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\'',
 153       trim($queryObject->_where[0][0])
 154     );
 155     $this->assertEquals(
 156       '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
 157 LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id',
 158       trim($queryObject->_fromClause)
 159     );
 160     $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM"', $queryObject->_qill[0][0]);
 161     $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']);
 162     $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables[$this->getCustomGroupTable()]));
 163   }
 164
 165   /**
 166    * Test filtering by relative custom data dates.
 167    *
 168    * @throws \CRM_Core_Exception
 169    */
 170   public function testSearchCustomDataDateFromTo() {
 171     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 172     $dateCustomField = $this->customFieldCreate([
 173       'custom_group_id' => $ids['custom_group_id'],
 174       'label' => 'date field',
 175       'data_type' => 'Date',
 176       'html_type' => 'Select Date',
 177       'default_value' => NULL,
 178     ]);
 179     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 180     // Assigning the relevant form value to be within a custom key is normally done in
 181     // build field params. It would be better if it were all done in convertFormValues
 182     // but for now we just imitate it.
 183     $formValues = [
 184       $dateCustomFieldName . '_from' => '2014-06-06',
 185       $dateCustomFieldName . '_to' => '2015-06-06',
 186     ];
 187
 188     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 189     $queryObj = new CRM_Contact_BAO_Query($params);
 190     $queryObj->query();
 191     $this->assertEquals(
 192       'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"',
 193       $queryObj->_where[0][0]
 194     );
 195     $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'");
 196
 197     //CRM-17236 - Test custom date is correctly displayed without time.
 198     $formattedValue = CRM_Core_BAO_CustomField::displayValue(date('Ymdhms'), $dateCustomField['id']);
 199     $this->assertEquals(date('m/d/Y'), $formattedValue);
 200   }
 201
 202   /**
 203    * Test filtering by relative custom data.
 204    *
 205    * @throws \CRM_Core_Exception
 206    */
 207   public function testSearchCustomDataFromTo() {
 208     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 209     $datas = [
 210       'Int' => 2,
 211       'Float' => 12.123,
 212       'Money' => 91.21,
 213     ];
 214     foreach ($datas as $type => $data) {
 215       $customField = $this->customFieldCreate(
 216         [
 217           'custom_group_id' => $ids['custom_group_id'],
 218           'label' => "$type field",
 219           'data_type' => $type,
 220           'html_type' => 'Text',
 221           'default_value' => NULL,
 222         ]
 223       );
 224       $customFieldName = 'custom_' . $customField['id'];
 225       // Assigning the relevant form value to be within a custom key is normally done in
 226       // build field params. It would be better if it were all done in convertFormValues
 227       // but for now we just imitate it.
 228       $from = $data - 1;
 229       $to = $data;
 230       $formValues = [
 231         $customFieldName . '_from' => $from,
 232         $customFieldName . '_to' => $to,
 233       ];
 234
 235       $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 236       $queryObj = new CRM_Contact_BAO_Query($params);
 237       $queryObj->query();
 238       $this->assertEquals(
 239         'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"",
 240         $queryObj->_where[0][0]
 241       );
 242       $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to");
 243     }
 244   }
 245
 246   /**
 247    * Test filtering by relative custom data.
 248    *
 249    * @throws \CRM_Core_Exception
 250    */
 251   public function testSearchCustomDataFromAndTo() {
 252     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 253     $dataSet = [
 254       'Date' => ['value' => '2015-06-06', 'sql_string' => '"20150606235959"', 'qill_string' => "'June 6th, 2015 11:59 PM'", 'qill_string_greater' => "'June 6th, 2015 12:00 AM'"],
 255       // @todo - investigate the impact of using quotes on what should be an integer field.
 256       'Int' => ['value' => 2, 'sql_string' => '"2"'],
 257       'Float' => ['value' => 12.123, 'sql_string' => '"12.123"'],
 258       'Money' => ['value' => 91.21],
 259     ];
 260     foreach ($dataSet as $type => $values) {
 261       $data = $values['value'];
 262       $isDate = ($type === 'Date');
 263       $customField = $this->customFieldCreate(
 264         [
 265           'custom_group_id' => $ids['custom_group_id'],
 266           'label' => "$type field",
 267           'data_type' => $type,
 268           'html_type' => ($isDate) ? 'Select Date' : 'Text',
 269           'default_value' => NULL,
 270         ]
 271       );
 272       $customFieldName = 'custom_' . $customField['id'];
 273
 274       $expectedValue = $values['sql_string'] ?? $data;
 275       $expectedQillValue = $values['qill_string'] ?? $data;
 276       $toQillValue = chr(226) . chr(137) . chr(164) . ' ' . $expectedQillValue;
 277       $fromQillValue = chr(226) . chr(137) . chr(165) . ' ' . ($values['qill_string_greater'] ?? $expectedQillValue);
 278
 279       // Assigning the relevant form value to be within a custom key is normally done in
 280       // build field params. It would be better if it were all done in convertFormValues
 281       // but for now we just imitate it.
 282
 283       //Scenario 2 : TO date filter
 284       $formValues = [
 285         $customFieldName . '_to' => $data,
 286       ];
 287
 288       $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 289       $queryObj = new CRM_Contact_BAO_Query($params);
 290       $queryObj->query();
 291
 292       $this->assertEquals(
 293         'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue",
 294         $queryObj->_where[0][0]
 295       );
 296       $this->assertEquals($queryObj->_qill[0][0],
 297         "$type field $toQillValue"
 298       );
 299
 300       //Scenario 2 : FROM date filter
 301       $formValues = [
 302         $customFieldName . '_from' => $values['value'],
 303       ];
 304
 305       $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 306       $queryObj = new CRM_Contact_BAO_Query($params);
 307       $queryObj->query();
 308
 309       $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue;
 310       $this->assertEquals(
 311         'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue",
 312         $queryObj->_where[0][0]
 313       );
 314       $this->assertEquals(
 315         "$type field $fromQillValue",
 316         $queryObj->_qill[0][0]
 317       );
 318     }
 319   }
 320
 321   /**
 322    * Test filtering by relative custom data dates.
 323    *
 324    * @throws \CRM_Core_Exception
 325    */
 326   public function testSearchCustomDataDateEquals() {
 327     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 328     $dateCustomField = $this->customFieldCreate([
 329       'custom_group_id' => $ids['custom_group_id'],
 330       'label' => 'date field',
 331       'data_type' => 'Date',
 332       'html_type' => 'Select Date',
 333       'default_value' => NULL,
 334     ]);
 335     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 336     $this->individualCreate([$dateCustomFieldName => '2015-01-01']);
 337     // Assigning the relevant form value to be within a custom key is normally done in
 338     // build field params. It would be better if it were all done in convertFormValues
 339     // but for now we just imitate it.
 340     $formValues = [$dateCustomFieldName => '2015-06-06'];
 341     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 342     $queryObj = new CRM_Contact_BAO_Query($params);
 343     $queryObj->query();
 344
 345     $this->assertEquals(
 346       "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'",
 347       $queryObj->_where[0][0]
 348     );
 349     $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'");
 350   }
 351
 352   /**
 353    * Test search builder style query including custom address fields.
 354    *
 355    * @throws \API_Exception
 356    * @throws \CRM_Core_Exception
 357    * @throws \Civi\API\Exception\UnauthorizedException
 358    */
 359   public function testAddressCustomFields() {
 360     $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int');
 361     $individualID = $this->individualCreate();
 362     $this->callAPISuccess('Address', 'create', [
 363       'contact_id' => $individualID,
 364       'street_address' => '10 Downing Street',
 365       'location_type_id' => 'Home',
 366       $this->getCustomFieldName('int') => 5,
 367     ]);
 368
 369     $queryObject = new CRM_Contact_BAO_Query(
 370       [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]],
 371       ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]]
 372     );
 373     $queryObject->query();
 374     $tableName = $this->getCustomGroupTable();
 375     $fieldName = $this->getCustomFieldColumnName('int');
 376
 377     $this->assertEquals([], $queryObject->_where[0]);
 378     $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where[1]));
 379     $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']);
 380     $this->assertEquals('LEFT JOIN civicrm_address `Home-address` ON (`Home-address`.contact_id = contact_a.id AND `Home-address`.location_type_id = 1)', trim($queryObject->_whereTables['Home-address']));
 381     $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables[$tableName]));
 382     $this->assertEquals([], $queryObject->_qill[0]);
 383     $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill[1]);
 384   }
 385
 386 }