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