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