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