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