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       'serialize' => NULL,
  89     ], $queryObj->getFieldSpec('custom_' . $dateCustomField['id']));
  90
  91   }
  92
  93   /**
  94    * Test filtering by the renamed custom date fields.
  95    *
  96    * The conversion to date picker will result int these fields
  97    * being renamed _high & _low and needing to return correctly.
  98    *
  99    * @throws \API_Exception
 100    * @throws \CRM_Core_Exception
 101    * @throws \Civi\API\Exception\UnauthorizedException
 102    */
 103   public function testSearchCustomDataDateHighLow() {
 104     $this->createCustomGroupWithFieldOfType([], 'date');
 105     $dateCustomFieldName = $this->getCustomFieldName('date');
 106     // Assigning the relevant form value to be within a custom key is normally done in
 107     // build field params. It would be better if it were all done in convertFormValues
 108     // but for now we just imitate it.
 109     $formValues = [
 110       $dateCustomFieldName . '_low' => '2014-06-06',
 111       $dateCustomFieldName . '_high' => '2015-06-06',
 112     ];
 113
 114     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 115     $queryObject = new CRM_Contact_BAO_Query($params);
 116     $queryObject->query();
 117     $this->assertEquals(
 118       '( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\' ) AND
 119 ( civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' <= \'20150606235959\' )',
 120       trim($queryObject->_where[0][0])
 121     );
 122     $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]);
 123     $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']);
 124     $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables[$this->getCustomGroupTable()]));
 125   }
 126
 127   /**
 128    * Test filtering by the renamed custom date fields.
 129    *
 130    * The conversion to date picker will result int these fields
 131    * being renamed _high & _low and needing to return correctly.
 132    *
 133    * @throws \API_Exception
 134    * @throws \CRM_Core_Exception
 135    * @throws \Civi\API\Exception\UnauthorizedException
 136    */
 137   public function testSearchCustomDataDateLowWithPermsInPlay() {
 138     $this->createLoggedInUser();
 139     CRM_Core_Config::singleton()->userPermissionClass->permissions = ['view all contacts', 'access all custom data'];
 140     $this->createCustomGroupWithFieldOfType([], 'date');
 141     $dateCustomFieldName = $this->getCustomFieldName('date');
 142     // Assigning the relevant form value to be within a custom key is normally done in
 143     // build field params. It would be better if it were all done in convertFormValues
 144     // but for now we just imitate it.
 145     $formValues = [
 146       $dateCustomFieldName . '_low' => '2014-06-06',
 147     ];
 148
 149     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 150     $queryObject = new CRM_Contact_BAO_Query($params);
 151     $queryObject->query();
 152     $this->assertEquals(
 153       'civicrm_value_group_with_fi_1.' . $this->getCustomFieldColumnName('date') . ' >= \'20140606000000\'',
 154       trim($queryObject->_where[0][0])
 155     );
 156     $this->assertEquals(
 157       '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' .
 158       ' LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id',
 159       preg_replace('/\s+/', ' ', trim($queryObject->_fromClause))
 160     );
 161     $this->assertEquals('Test Date - greater than or equal to "June 6th, 2014 12:00 AM"', $queryObject->_qill[0][0]);
 162     $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']);
 163     $this->assertEquals('LEFT JOIN ' . $this->getCustomGroupTable() . ' ON ' . $this->getCustomGroupTable() . '.entity_id = `contact_a`.id', trim($queryObject->_whereTables[$this->getCustomGroupTable()]));
 164   }
 165
 166   /**
 167    * Test filtering by relative custom data dates.
 168    *
 169    * @throws \CRM_Core_Exception
 170    */
 171   public function testSearchCustomDataDateFromTo() {
 172     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 173     $dateCustomField = $this->customFieldCreate([
 174       'custom_group_id' => $ids['custom_group_id'],
 175       'label' => 'date field',
 176       'data_type' => 'Date',
 177       'html_type' => 'Select Date',
 178       'default_value' => NULL,
 179     ]);
 180     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 181     // Assigning the relevant form value to be within a custom key is normally done in
 182     // build field params. It would be better if it were all done in convertFormValues
 183     // but for now we just imitate it.
 184     $formValues = [
 185       $dateCustomFieldName . '_from' => '2014-06-06',
 186       $dateCustomFieldName . '_to' => '2015-06-06',
 187     ];
 188
 189     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 190     $queryObj = new CRM_Contact_BAO_Query($params);
 191     $queryObj->query();
 192     $this->assertEquals(
 193       'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"',
 194       $queryObj->_where[0][0]
 195     );
 196     $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'");
 197
 198     //CRM-17236 - Test custom date is correctly displayed without time.
 199     $formattedValue = CRM_Core_BAO_CustomField::displayValue(date('Ymdhms'), $dateCustomField['id']);
 200     $this->assertEquals(date('m/d/Y'), $formattedValue);
 201   }
 202
 203   /**
 204    * Test filtering by relative custom data.
 205    *
 206    * @throws \CRM_Core_Exception
 207    */
 208   public function testSearchCustomDataFromTo() {
 209     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 210     $datas = [
 211       'Int' => 2,
 212       'Float' => 12.123,
 213       'Money' => 91.21,
 214     ];
 215     foreach ($datas as $type => $data) {
 216       $customField = $this->customFieldCreate(
 217         [
 218           'custom_group_id' => $ids['custom_group_id'],
 219           'label' => "$type field",
 220           'data_type' => $type,
 221           'html_type' => 'Text',
 222           'default_value' => NULL,
 223         ]
 224       );
 225       $customFieldName = 'custom_' . $customField['id'];
 226       // Assigning the relevant form value to be within a custom key is normally done in
 227       // build field params. It would be better if it were all done in convertFormValues
 228       // but for now we just imitate it.
 229       $from = $data - 1;
 230       $to = $data;
 231       $formValues = [
 232         $customFieldName . '_from' => $from,
 233         $customFieldName . '_to' => $to,
 234       ];
 235
 236       $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 237       $queryObj = new CRM_Contact_BAO_Query($params);
 238       $queryObj->query();
 239       $this->assertEquals(
 240         'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"",
 241         $queryObj->_where[0][0]
 242       );
 243       $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to");
 244     }
 245   }
 246
 247   /**
 248    * Test filtering by relative custom data.
 249    *
 250    * @throws \CRM_Core_Exception
 251    */
 252   public function testSearchCustomDataFromAndTo() {
 253     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 254     $dataSet = [
 255       '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'"],
 256       // @todo - investigate the impact of using quotes on what should be an integer field.
 257       'Int' => ['value' => 2, 'sql_string' => '"2"'],
 258       'Float' => ['value' => 12.123, 'sql_string' => '"12.123"'],
 259       'Money' => ['value' => 91.21],
 260     ];
 261     foreach ($dataSet as $type => $values) {
 262       $data = $values['value'];
 263       $isDate = ($type === 'Date');
 264       $customField = $this->customFieldCreate(
 265         [
 266           'custom_group_id' => $ids['custom_group_id'],
 267           'label' => "$type field",
 268           'data_type' => $type,
 269           'html_type' => ($isDate) ? 'Select Date' : 'Text',
 270           'default_value' => NULL,
 271         ]
 272       );
 273       $customFieldName = 'custom_' . $customField['id'];
 274
 275       $expectedValue = $values['sql_string'] ?? $data;
 276       $expectedQillValue = $values['qill_string'] ?? $data;
 277       $toQillValue = chr(226) . chr(137) . chr(164) . ' ' . $expectedQillValue;
 278       $fromQillValue = chr(226) . chr(137) . chr(165) . ' ' . ($values['qill_string_greater'] ?? $expectedQillValue);
 279
 280       // Assigning the relevant form value to be within a custom key is normally done in
 281       // build field params. It would be better if it were all done in convertFormValues
 282       // but for now we just imitate it.
 283
 284       //Scenario 2 : TO date filter
 285       $formValues = [
 286         $customFieldName . '_to' => $data,
 287       ];
 288
 289       $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 290       $queryObj = new CRM_Contact_BAO_Query($params);
 291       $queryObj->query();
 292
 293       $this->assertEquals(
 294         'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} <= $expectedValue",
 295         $queryObj->_where[0][0]
 296       );
 297       $this->assertEquals($queryObj->_qill[0][0],
 298         "$type field $toQillValue"
 299       );
 300
 301       //Scenario 2 : FROM date filter
 302       $formValues = [
 303         $customFieldName . '_from' => $values['value'],
 304       ];
 305
 306       $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 307       $queryObj = new CRM_Contact_BAO_Query($params);
 308       $queryObj->query();
 309
 310       $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue;
 311       $this->assertEquals(
 312         'civicrm_value_testsearchcus_1.' . strtolower($type) . "_field_{$customField['id']} >= $expectedValue",
 313         $queryObj->_where[0][0]
 314       );
 315       $this->assertEquals(
 316         "$type field $fromQillValue",
 317         $queryObj->_qill[0][0]
 318       );
 319     }
 320   }
 321
 322   /**
 323    * Test filtering by relative custom data dates.
 324    *
 325    * @throws \CRM_Core_Exception
 326    */
 327   public function testSearchCustomDataDateEquals() {
 328     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 329     $dateCustomField = $this->customFieldCreate([
 330       'custom_group_id' => $ids['custom_group_id'],
 331       'label' => 'date field',
 332       'data_type' => 'Date',
 333       'html_type' => 'Select Date',
 334       'default_value' => NULL,
 335     ]);
 336     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 337     $this->individualCreate([$dateCustomFieldName => '2015-01-01']);
 338     // Assigning the relevant form value to be within a custom key is normally done in
 339     // build field params. It would be better if it were all done in convertFormValues
 340     // but for now we just imitate it.
 341     $formValues = [$dateCustomFieldName => '2015-06-06'];
 342     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
 343     $queryObj = new CRM_Contact_BAO_Query($params);
 344     $queryObj->query();
 345
 346     $this->assertEquals(
 347       "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'",
 348       $queryObj->_where[0][0]
 349     );
 350     $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'");
 351   }
 352
 353   /**
 354    * Test search builder style query including custom address fields.
 355    *
 356    * @throws \API_Exception
 357    * @throws \CRM_Core_Exception
 358    * @throws \Civi\API\Exception\UnauthorizedException
 359    */
 360   public function testAddressCustomFields() {
 361     $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int');
 362     $individualID = $this->individualCreate();
 363     $this->callAPISuccess('Address', 'create', [
 364       'contact_id' => $individualID,
 365       'street_address' => '10 Downing Street',
 366       'location_type_id' => 'Home',
 367       $this->getCustomFieldName('int') => 5,
 368     ]);
 369
 370     $queryObject = new CRM_Contact_BAO_Query(
 371       [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]],
 372       ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]]
 373     );
 374     $queryObject->query();
 375     $tableName = $this->getCustomGroupTable();
 376     $fieldName = $this->getCustomFieldColumnName('int');
 377
 378     $this->assertEquals([], $queryObject->_where[0]);
 379     $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where[1]));
 380     $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']);
 381     $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']));
 382     $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables[$tableName]));
 383     $this->assertEquals([], $queryObject->_qill[0]);
 384     $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill[1]);
 385   }
 386
 387 }