2fe91f9d |
1 | <?php |
2 | require_once 'CiviTest/CiviUnitTestCase.php'; |
3 | /** |
4 | * Include dataProvider for tests |
5 | */ |
6 | class CRM_Core_BAO_CustomQueryTest extends CiviUnitTestCase { |
7 | |
8 | /** |
9 | * Restore database to empty state. |
10 | * |
11 | * Note that rollback won't remove custom tables. |
12 | * |
13 | * @throws \Exception |
14 | */ |
15 | public function tearDown() { |
16 | $tablesToTruncate = array( |
17 | 'civicrm_contact', |
18 | ); |
19 | $this->quickCleanup($tablesToTruncate, TRUE); |
20 | parent::tearDown(); |
21 | } |
22 | |
23 | /** |
24 | * Test filtering by relative custom data dates. |
25 | */ |
26 | public function testSearchCustomDataDateRelative() { |
27 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); |
28 | $dateCustomField = $this->customFieldCreate(array( |
29 | 'custom_group_id' => $ids['custom_group_id'], |
30 | 'label' => 'date field', |
31 | 'data_type' => 'Date', |
32 | 'html_type' => 'Select Date', |
33 | 'default_value' => NULL, |
34 | )); |
35 | $dateCustomFieldName = 'custom_' . $dateCustomField['id']; |
36 | $formValues = array( |
37 | $dateCustomFieldName . '_relative' => 'this.year', |
38 | $dateCustomFieldName . '_from' => '', |
39 | $dateCustomFieldName . '_to' => '', |
40 | ); |
41 | // Assigning the relevant form value to be within a custom key is normally done in |
42 | // build field params. It would be better if it were all done in convertFormValues |
43 | // but for now we just imitate it. |
44 | $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues); |
45 | $queryObj = new CRM_Core_BAO_CustomQuery($params); |
46 | $queryObj->Query(); |
47 | $this->assertEquals( |
48 | 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "' . date('Y') . '0101000000" AND "' . date('Y') . '1231235959"', |
49 | $queryObj->_where[0][0] |
50 | ); |
51 | $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'January 1st, " . date('Y') . " 12:00 AM AND December 31st, " . date('Y') . " 11:59 PM'"); |
52 | } |
53 | |
54 | /** |
55 | * Test filtering by relative custom data dates. |
56 | */ |
57 | public function testSearchCustomDataDateFromTo() { |
58 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); |
59 | $dateCustomField = $this->customFieldCreate(array( |
60 | 'custom_group_id' => $ids['custom_group_id'], |
61 | 'label' => 'date field', |
62 | 'data_type' => 'Date', |
63 | 'html_type' => 'Select Date', |
64 | 'default_value' => NULL, |
65 | )); |
66 | $dateCustomFieldName = 'custom_' . $dateCustomField['id']; |
67 | // Assigning the relevant form value to be within a custom key is normally done in |
68 | // build field params. It would be better if it were all done in convertFormValues |
69 | // but for now we just imitate it. |
70 | $formValues = array( |
71 | $dateCustomFieldName . '_from' => '2014-06-06', |
72 | $dateCustomFieldName . '_to' => '2015-06-06', |
73 | ); |
74 | |
75 | $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues); |
76 | $queryObj = new CRM_Core_BAO_CustomQuery($params); |
77 | $queryObj->Query(); |
78 | $this->assertEquals( |
79 | 'civicrm_value_testsearchcus_1.date_field_2 BETWEEN "20140606000000" AND "20150606235959"', |
80 | $queryObj->_where[0][0] |
81 | ); |
82 | $this->assertEquals($queryObj->_qill[0][0], "date field BETWEEN 'June 6th, 2014 12:00 AM AND June 6th, 2015 11:59 PM'"); |
83 | } |
84 | |
85 | /** |
86 | * Test filtering by relative custom data. |
87 | */ |
88 | public function testSearchCustomDataFromTo() { |
89 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); |
90 | $datas = array( |
91 | 'Int' => 2, |
92 | 'Float' => 12.123, |
93 | 'Money' => 91.21, |
94 | ); |
95 | foreach ($datas as $type => $data) { |
96 | $customField = $this->customFieldCreate( |
97 | array( |
98 | 'custom_group_id' => $ids['custom_group_id'], |
99 | 'label' => "$type field", |
100 | 'data_type' => $type, |
101 | 'html_type' => 'Text', |
102 | 'default_value' => NULL, |
103 | ) |
104 | ); |
105 | $customFieldName = 'custom_' . $customField['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 | $from = $data - 1; |
110 | $to = $data; |
111 | $formValues = array( |
112 | $customFieldName . '_from' => $from, |
113 | $customFieldName . '_to' => $to, |
114 | ); |
115 | |
116 | $params = array($customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)); |
117 | $queryObj = new CRM_Core_BAO_CustomQuery($params); |
118 | $queryObj->Query(); |
119 | $this->assertEquals( |
120 | "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} BETWEEN \"$from\" AND \"$to\"", |
121 | $queryObj->_where[0][0] |
122 | ); |
123 | $this->assertEquals($queryObj->_qill[0][0], "$type field BETWEEN $from, $to"); |
124 | } |
125 | } |
126 | |
127 | /** |
128 | * Test filtering by relative custom data. |
129 | */ |
130 | public function testSearchCustomDataFromAndTo() { |
131 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); |
132 | $datas = array( |
133 | 'Date' => '2015-06-06', |
134 | 'Int' => 2, |
135 | 'Float' => 12.123, |
136 | 'Money' => 91.21, |
137 | ); |
138 | foreach ($datas as $type => $data) { |
139 | $isDate = ($type === 'Date'); |
140 | $customField = $this->customFieldCreate( |
141 | array( |
142 | 'custom_group_id' => $ids['custom_group_id'], |
143 | 'label' => "$type field", |
144 | 'data_type' => $type, |
145 | 'html_type' => ($isDate) ? 'Select Date' : 'Text', |
146 | 'default_value' => NULL, |
147 | ) |
148 | ); |
149 | $customFieldName = 'custom_' . $customField['id']; |
150 | |
151 | $expectedValue = ($isDate) ? '"20150606235959"' : (($type == 'Money') ? $data : "\"$data\""); |
152 | $expectedQillValue = ($isDate) ? "'June 6th, 2015 11:59 PM'" : $data; |
153 | |
154 | // Assigning the relevant form value to be within a custom key is normally done in |
155 | // build field params. It would be better if it were all done in convertFormValues |
156 | // but for now we just imitate it. |
157 | |
158 | //Scenrio 2 : TO date filter |
159 | $formValues = array( |
160 | $customFieldName . '_to' => $data, |
161 | ); |
162 | |
163 | $params = array($customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)); |
164 | $queryObj = new CRM_Core_BAO_CustomQuery($params); |
165 | $queryObj->Query(); |
166 | $wierdStringThatMeansGreaterEquals = chr(226) . chr(137) . chr(164); |
167 | |
168 | $this->assertEquals( |
169 | "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} <= $expectedValue", |
170 | $queryObj->_where[0][0] |
171 | ); |
172 | $this->assertEquals($queryObj->_qill[0][0], |
173 | "$type field " . $wierdStringThatMeansGreaterEquals . " $expectedQillValue" |
174 | ); |
175 | |
176 | //Scenrio 2 : FROM date filter |
177 | $formValues = array( |
178 | $customFieldName . '_from' => $data, |
179 | ); |
180 | |
181 | $params = array($customField['id'] => CRM_Contact_BAO_Query::convertFormValues($formValues)); |
182 | $queryObj = new CRM_Core_BAO_CustomQuery($params); |
183 | $queryObj->Query(); |
184 | $wierdStringThatMeansLessThanEquals = chr(226) . chr(137) . chr(165); |
185 | |
186 | $expectedValue = ($isDate) ? '"20150606000000"' : $expectedValue; |
187 | $expectedQillValue = ($isDate) ? "'June 6th, 2015 12:00 AM'" : $expectedQillValue; |
188 | $this->assertEquals( |
189 | "civicrm_value_testsearchcus_1." . strtolower($type) . "_field_{$customField['id']} >= $expectedValue", |
190 | $queryObj->_where[0][0] |
191 | ); |
192 | $this->assertEquals($queryObj->_qill[0][0], |
193 | "$type field " . $wierdStringThatMeansLessThanEquals . " $expectedQillValue" |
194 | ); |
195 | } |
196 | } |
197 | |
198 | /** |
199 | * Test filtering by relative custom data dates. |
200 | */ |
201 | public function testSearchCustomDataDateEquals() { |
202 | $ids = $this->entityCustomGroupWithSingleFieldCreate(__FUNCTION__, 'ContactTestTest'); |
203 | $dateCustomField = $this->customFieldCreate(array( |
204 | 'custom_group_id' => $ids['custom_group_id'], |
205 | 'label' => 'date field', |
206 | 'data_type' => 'Date', |
207 | 'html_type' => 'Select Date', |
208 | 'default_value' => NULL, |
209 | )); |
210 | $dateCustomFieldName = 'custom_' . $dateCustomField['id']; |
211 | $this->individualCreate(array($dateCustomFieldName => "2015-01-01")); |
212 | // Assigning the relevant form value to be within a custom key is normally done in |
213 | // build field params. It would be better if it were all done in convertFormValues |
214 | // but for now we just imitate it. |
215 | $formValues = array($dateCustomFieldName => '2015-06-06'); |
216 | $params[$dateCustomField['id']] = CRM_Contact_BAO_Query::convertFormValues($formValues); |
217 | $queryObj = new CRM_Core_BAO_CustomQuery($params); |
218 | $queryObj->Query(); |
219 | |
220 | $this->assertEquals( |
221 | "civicrm_value_testsearchcus_1.date_field_2 = '2015-06-06'", |
222 | $queryObj->_where[0][0] |
223 | ); |
224 | $this->assertEquals($queryObj->_qill[0][0], "date field = 'June 6th, 2015'"); |
225 | } |
226 | |
227 | } |