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