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_Core_BAO_CustomQuery($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     ], $queryObj->getFields()[$dateCustomField['id']]);
  88
  89   }
  90
  91   /**
  92    * Test filtering by relative custom data dates.
  93    *
  94    * @throws \CRM_Core_Exception
  95    */
  96   public function testSearchCustomDataDateFromTo() {
  97     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
  98     $dateCustomField = $this->customFieldCreate([
  99       'custom_group_id' => $ids['custom_group_id'],
 100       'label' => 'date field',
 101       'data_type' => 'Date',
 102       'html_type' => 'Select Date',
 103       'default_value' => NULL,
 104     ]);
 105     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 106     // Assigning the relevant form value to be within a custom key is normally done in
 107     // build field params. It would be better if it were all done in convertFormValues
 108     // but for now we just imitate it.
 109     $formValues = [
 110       $dateCustomFieldName . '_from' => '2014-06-06',
 111       $dateCustomFieldName . '_to' => '2015-06-06',
 112     ];
 113
 114     $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues);
 115     $queryObj = new CRM_Core_BAO_CustomQuery($params);
 116     $queryObj->query();
 117     $this->assertEquals(
 118       'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"',
 119       $queryObj->_where[0][0]
 120     );
 121     $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'");
 122
 123     //CRM-17236 - Test custom date is correctly displayed without time.
 124     $formattedValue = CRM_Core_BAO_CustomField::displayValue(date('Ymdhms'), $dateCustomField['id']);
 125     $this->assertEquals(date('m/d/Y'), $formattedValue);
 126   }
 127
 128   /**
 129    * Test filtering by relative custom data.
 130    */
 131   public function testSearchCustomDataFromTo() {
 132     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 133     $datas = [
 134       'Int' => 2,
 135       'Float' => 12.123,
 136       'Money' => 91.21,
 137     ];
 138     foreach ($datas as $type => $data) {
 139       $customField = $this->customFieldCreate(
 140         [
 141           'custom_group_id' => $ids['custom_group_id'],
 142           'label' => "$type field",
 143           'data_type' => $type,
 144           'html_type' => 'Text',
 145           'default_value' => NULL,
 146         ]
 147       );
 148       $customFieldName = 'custom_' . $customField['id'];
 149       // Assigning the relevant form value to be within a custom key is normally done in
 150       // build field params. It would be better if it were all done in convertFormValues
 151       // but for now we just imitate it.
 152       $from = $data - 1;
 153       $to = $data;
 154       $formValues = [
 155         $customFieldName . '_from' => $from,
 156         $customFieldName . '_to' => $to,
 157       ];
 158
 159       $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)];
 160       $queryObj = new CRM_Core_BAO_CustomQuery($params);
 161       $queryObj->query();
 162       $this->assertEquals(
 163         "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"",
 164         $queryObj->_where[0][0]
 165       );
 166       $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to");
 167     }
 168   }
 169
 170   /**
 171    * Test filtering by relative custom data.
 172    */
 173   public function testSearchCustomDataFromAndTo() {
 174     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 175     $datas = [
 176       'Date' => '2015-06-06',
 177       'Int' => 2,
 178       'Float' => 12.123,
 179       'Money' => 91.21,
 180     ];
 181     foreach ($datas as $type => $data) {
 182       $isDate = ($type === 'Date');
 183       $customField = $this->customFieldCreate(
 184         [
 185           'custom_group_id' => $ids['custom_group_id'],
 186           'label' => "$type field",
 187           'data_type' => $type,
 188           'html_type' => ($isDate) ? 'Select Date' : 'Text',
 189           'default_value' => NULL,
 190         ]
 191       );
 192       $customFieldName = 'custom_' . $customField['id'];
 193
 194       $expectedValue = ($isDate) ? '"20150606235959"' : (($type == 'Money') ? $data : "\"$data\"");
 195       $expectedQillValue = ($isDate) ? "'June 6th, 2015 11:59 PM'" : $data;
 196
 197       // Assigning the relevant form value to be within a custom key is normally done in
 198       // build field params. It would be better if it were all done in convertFormValues
 199       // but for now we just imitate it.
 200
 201       //Scenrio 2 : TO date filter
 202       $formValues = [
 203         $customFieldName . '_to' => $data,
 204       ];
 205
 206       $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)];
 207       $queryObj = new CRM_Core_BAO_CustomQuery($params);
 208       $queryObj->query();
 209       $wierdStringThatMeansGreaterEquals = chr(226) . chr(137) . chr(164);
 210
 211       $this->assertEquals(
 212         "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} <= $expectedValue",
 213         $queryObj->_where[0][0]
 214       );
 215       $this->assertEquals($queryObj->_qill[0][0],
 216         "$type field " . $wierdStringThatMeansGreaterEquals . " $expectedQillValue"
 217       );
 218
 219       //Scenrio 2 : FROM date filter
 220       $formValues = [
 221         $customFieldName . '_from' => $data,
 222       ];
 223
 224       $params = [$customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)];
 225       $queryObj = new CRM_Core_BAO_CustomQuery($params);
 226       $queryObj->query();
 227       $wierdStringThatMeansLessThanEquals = chr(226) . chr(137) . chr(165);
 228
 229       $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue;
 230       $expectedQillValue = ($isDate) ? "'June 6th, 2015 12:00 AM'" : $expectedQillValue;
 231       $this->assertEquals(
 232         "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} >= $expectedValue",
 233         $queryObj->_where[0][0]
 234       );
 235       $this->assertEquals($queryObj->_qill[0][0],
 236         "$type field " . $wierdStringThatMeansLessThanEquals . " $expectedQillValue"
 237       );
 238     }
 239   }
 240
 241   /**
 242    * Test filtering by relative custom data dates.
 243    *
 244    * @throws \CRM_Core_Exception
 245    */
 246   public function testSearchCustomDataDateEquals() {
 247     $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest');
 248     $dateCustomField = $this->customFieldCreate([
 249       'custom_group_id' => $ids['custom_group_id'],
 250       'label' => 'date field',
 251       'data_type' => 'Date',
 252       'html_type' => 'Select Date',
 253       'default_value' => NULL,
 254     ]);
 255     $dateCustomFieldName = 'custom_' . $dateCustomField['id'];
 256     $this->individualCreate([$dateCustomFieldName => "2015-01-01"]);
 257     // Assigning the relevant form value to be within a custom key is normally done in
 258     // build field params. It would be better if it were all done in convertFormValues
 259     // but for now we just imitate it.
 260     $formValues = [$dateCustomFieldName => '2015-06-06'];
 261     $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues);
 262     $queryObj = new CRM_Core_BAO_CustomQuery($params);
 263     $queryObj->query();
 264
 265     $this->assertEquals(
 266       "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'",
 267       $queryObj->_where[0][0]
 268     );
 269     $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'");
 270   }
 271
 272   /**
 273    * Test search builder style query including custom address fields.
 274    *
 275    * @throws \CRM_Core_Exception
 276    */
 277   public function testAddressCustomFields() {
 278     $this->createCustomGroupWithFieldOfType(['extends' => 'Address'], 'int');
 279     $individualID = $this->individualCreate();
 280     $this->callAPISuccess('Address', 'create', [
 281       'contact_id' => $individualID,
 282       'street_address' => '10 Downing Street',
 283       'location_type_id' => 'Home',
 284       $this->getCustomFieldName('int') => 5,
 285     ]);
 286
 287     $queryObject = new CRM_Contact_BAO_Query(
 288       [[$this->getCustomFieldName('int') . '-1', '=', 5, 1, 0]],
 289       ['contact_type' => 1, 'location' => ['Home' => ['location_type' => 1, $this->getCustomFieldName('int') => 1]]]
 290     );
 291     $queryObject->query();
 292     $tableName = $this->getCustomGroupTable();
 293     $fieldName = $this->getCustomFieldColumnName('int');
 294
 295     $this->assertEquals([], $queryObject->_where[0]);
 296     $this->assertEquals($tableName . '.' . $fieldName . ' = 5', implode(', ', $queryObject->_where[1]));
 297     $this->assertEquals(1, $queryObject->_whereTables['civicrm_contact']);
 298     $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']));
 299     $this->assertEquals("LEFT JOIN {$tableName} ON {$tableName}.entity_id = `Home-address`.id", trim($queryObject->_whereTables[$tableName]));
 300     $this->assertEquals([], $queryObject->_qill[0]);
 301     $this->assertEquals(['Enter integer here = 5'], $queryObject->_qill[1]);
 302   }
 303
 304 }