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
   9   /**
  10    * Restore database to empty state.
  11    *
  12    * Note that rollback won't remove custom tables.
  13    *
  14    * @throws \Exception
  15    */
  16   public function tearDown() {
  17     $tablesToTruncate = [
  18       'civicrm_contact',
  19     ];
  20     $this->quickCleanup($tablesToTruncate, TRUE);
  21     parent::tearDown();
  22   }
  23
  24   /**
  25    * Test filtering by relative custom data dates.
  26    */
  27   public function testSearchCustomDataDateRelative() {
  28     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
  29     $dateCustomField = $this->customFieldCreate([
  30       'custom_group_id' => $ids['custom_group_id'],
  31       'label' => 'date field',
  32       'data_type' => 'Date',
  33       'html_type' => 'Select Date',
  34       'default_value' => NULL,
  35     ]);
  36     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
  37     $formValues = [
  38       $dateCustomFieldName . '_relative' => 'this.year',
  39       $dateCustomFieldName . '_from' => '',
  40       $dateCustomFieldName . '_to' => '',
  41     ];
  42     // Assigning the relevant form value to be within a custom key is normally done in
  43     // build field params. It would be better if it were all done in convertFormValues
  44     // but for now we just imitate it.
  45
  46     $params = CRM_Contact_BAO_Query::convertFormValues($formValues);
  47     $queryObj = new CRM_Contact_BAO_Query($params);
  48     $this->assertEquals(
  49       "civicrm_value_testsearchcus_1.date_field_2 BETWEEN '" . date('Y') . "0101000000' AND '" . date('Y') . "1231235959'",
  50       $queryObj->_where[0][0]
  51     );
  52     $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]);
  53     $queryObj = new CRM_Core_BAO_CustomQuery($params);
  54     $this->assertEquals([
  55       'id' => $dateCustomField['id'],
  56       'label' => 'date field',
  57       'extends' => 'Contact',
  58       'data_type' => 'Date',
  59       'html_type' => 'Select Date',
  60       'is_search_range' => '0',
  61       'column_name' => 'date_field_' . $dateCustomField['id'],
  62       'table_name' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'],
  63       'option_group_id' => NULL,
  64       'groupTitle' => 'testSearchCustomDataDateRelative',
  65       'default_value' => NULL,
  66       'text_length' => NULL,
  67       'options_per_line' => NULL,
  68       'custom_group_id' => '1',
  69       'extends_entity_column_value' => NULL,
  70       'extends_entity_column_id' => NULL,
  71       'is_view' => '0',
  72       'is_multiple' => '0',
  73       'date_format' => 'mm/dd/yy',
  74       'time_format' => NULL,
  75       'is_required' => '0',
  76       'extends_table' => 'civicrm_contact',
  77       'search_table' => 'contact_a',
  78       'headerPattern' => '//',
  79       'title' => 'date field',
  80       'custom_field_id' => $dateCustomField['id'],
  81       'name' => 'custom_' . $dateCustomField['id'],
  82       'type' => 4,
  83       'where' => 'civicrm_value_testsearchcus_' . $ids['custom_group_id'] . '.date_field_' . $dateCustomField['id'],
  84     ], $queryObj->getFields()[$dateCustomField['id']]);
  85
  86   }
  87
  88   /**
  89    * Test filtering by relative custom data dates.
  90    */
  91   public function testSearchCustomDataDateFromTo() {
  92     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
  93     $dateCustomField = $this->customFieldCreate([
  94       'custom_group_id' => $ids['custom_group_id'],
  95       'label' => 'date field',
  96       'data_type' => 'Date',
  97       'html_type' => 'Select Date',
  98       'default_value' => NULL,
  99     ]);
 100     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 101     // Assigning the relevant form value to be within a custom key is normally done in
 102     // build field params. It would be better if it were all done in convertFormValues
 103     // but for now we just imitate it.
 104     $formValues = [
 105       $dateCustomFieldName . '_from' => '2014-06-06',
 106       $dateCustomFieldName . '_to' => '2015-06-06',
 107     ];
 108
 109     $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues);
 110     $queryObj = new CRM_Core_BAO_CustomQuery($params);
 111     $queryObj->Query();
 112     $this->assertEquals(
 113       'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"',
 114       $queryObj->_where[0][0]
 115     );
 116     $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'");
 117
 118     //CRM-17236 - Test custom date is correctly displayed without time.
 119     $formattedValue = CRM_Core_BAO_CustomField::displayValue(date('Ymdhms'), $dateCustomField['id']);
 120     $this->assertEquals(date('m/d/Y'), $formattedValue);
 121   }
 122
 123   /**
 124    * Test filtering by relative custom data.
 125    */
 126   public function testSearchCustomDataFromTo() {
 127     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 128     $datas = [
 129       'Int' => 2,
 130       'Float' => 12.123,
 131       'Money' => 91.21,
 132     ];
 133     foreach ($datas as $type => $data) {
 134       $customField = $this->customFieldCreate(
 135         [
 136           'custom_group_id' => $ids['custom_group_id'],
 137           'label' => "$type field",
 138           'data_type' => $type,
 139           'html_type' => 'Text',
 140           'default_value' => NULL,
 141         ]
 142       );
 143       $customFieldName = 'custom_' . $customField['id'];
 144       // Assigning the relevant form value to be within a custom key is normally done in
 145       // build field params. It would be better if it were all done in convertFormValues
 146       // but for now we just imitate it.
 147       $from = $data - 1;
 148       $to = $data;
 149       $formValues = [
 150         $customFieldName . '_from' => $from,
 151         $customFieldName . '_to' => $to,
 152       ];
 153
 154       $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)];
 155       $queryObj = new CRM_Core_BAO_CustomQuery($params);
 156       $queryObj->Query();
 157       $this->assertEquals(
 158         "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"",
 159         $queryObj->_where[0][0]
 160       );
 161       $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to");
 162     }
 163   }
 164
 165   /**
 166    * Test filtering by relative custom data.
 167    */
 168   public function testSearchCustomDataFromAndTo() {
 169     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 170     $datas = [
 171       'Date' => '2015-06-06',
 172       'Int' => 2,
 173       'Float' => 12.123,
 174       'Money' => 91.21,
 175     ];
 176     foreach ($datas as $type => $data) {
 177       $isDate = ($type === 'Date');
 178       $customField = $this->customFieldCreate(
 179         [
 180           'custom_group_id' => $ids['custom_group_id'],
 181           'label' => "$type field",
 182           'data_type' => $type,
 183           'html_type' => ($isDate) ? 'Select Date' : 'Text',
 184           'default_value' => NULL,
 185         ]
 186       );
 187       $customFieldName = 'custom_' . $customField['id'];
 188
 189       $expectedValue = ($isDate) ? '"20150606235959"' : (($type == 'Money') ? $data : "\"$data\"");
 190       $expectedQillValue = ($isDate) ? "'June 6th, 2015 11:59 PM'" : $data;
 191
 192       // Assigning the relevant form value to be within a custom key is normally done in
 193       // build field params. It would be better if it were all done in convertFormValues
 194       // but for now we just imitate it.
 195
 196       //Scenrio 2 : TO date filter
 197       $formValues = [
 198         $customFieldName . '_to' => $data,
 199       ];
 200
 201       $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)];
 202       $queryObj = new CRM_Core_BAO_CustomQuery($params);
 203       $queryObj->Query();
 204       $wierdStringThatMeansGreaterEquals = chr(226) . chr(137) . chr(164);
 205
 206       $this->assertEquals(
 207         "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} <= $expectedValue",
 208         $queryObj->_where[0][0]
 209       );
 210       $this->assertEquals($queryObj->_qill[0][0],
 211         "$type field " . $wierdStringThatMeansGreaterEquals . " $expectedQillValue"
 212       );
 213
 214       //Scenrio 2 : FROM date filter
 215       $formValues = [
 216         $customFieldName . '_from' => $data,
 217       ];
 218
 219       $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)];
 220       $queryObj = new CRM_Core_BAO_CustomQuery($params);
 221       $queryObj->Query();
 222       $wierdStringThatMeansLessThanEquals = chr(226) . chr(137) . chr(165);
 223
 224       $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue;
 225       $expectedQillValue = ($isDate) ? "'June 6th, 2015 12:00 AM'" : $expectedQillValue;
 226       $this->assertEquals(
 227         "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} >= $expectedValue",
 228         $queryObj->_where[0][0]
 229       );
 230       $this->assertEquals($queryObj->_qill[0][0],
 231         "$type field " . $wierdStringThatMeansLessThanEquals . " $expectedQillValue"
 232       );
 233     }
 234   }
 235
 236   /**
 237    * Test filtering by relative custom data dates.
 238    */
 239   public function testSearchCustomDataDateEquals() {
 240     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 241     $dateCustomField = $this->customFieldCreate([
 242       'custom_group_id' => $ids['custom_group_id'],
 243       'label' => 'date field',
 244       'data_type' => 'Date',
 245       'html_type' => 'Select Date',
 246       'default_value' => NULL,
 247     ]);
 248     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 249     $this->individualCreate([$dateCustomFieldName => "2015-01-01"]);
 250     // Assigning the relevant form value to be within a custom key is normally done in
 251     // build field params. It would be better if it were all done in convertFormValues
 252     // but for now we just imitate it.
 253     $formValues = [$dateCustomFieldName => '2015-06-06'];
 254     $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues);
 255     $queryObj = new CRM_Core_BAO_CustomQuery($params);
 256     $queryObj->Query();
 257
 258     $this->assertEquals(
 259       "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'",
 260       $queryObj->_where[0][0]
 261     );
 262     $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'");
 263   }
 264
 265 }