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