Commit | Line | Data |
---|---|---|
f0acec37 CW |
1 | <?php |
2 | ||
3 | /* | |
4 | +--------------------------------------------------------------------+ | |
5 | | Copyright CiviCRM LLC. All rights reserved. | | |
6 | | | | |
7 | | This work is published under the GNU AGPLv3 license with some | | |
8 | | permitted exceptions and without any warranty. For full license | | |
9 | | and copyright information, see https://civicrm.org/licensing | | |
10 | +--------------------------------------------------------------------+ | |
11 | */ | |
12 | ||
13 | /** | |
14 | * | |
15 | * @package CRM | |
16 | * @copyright CiviCRM LLC https://civicrm.org/licensing | |
f0acec37 CW |
17 | */ |
18 | ||
19 | ||
20 | namespace api\v4\Action; | |
21 | ||
22 | use api\v4\UnitTestCase; | |
daf464c1 | 23 | use Civi\Api4\Activity; |
f0acec37 CW |
24 | use Civi\Api4\Contact; |
25 | use Civi\Api4\Contribution; | |
26 | ||
27 | /** | |
28 | * @group headless | |
29 | */ | |
30 | class SqlFunctionTest extends UnitTestCase { | |
31 | ||
32 | public function testGetFunctions() { | |
33 | $functions = array_column(\CRM_Api4_Page_Api4Explorer::getSqlFunctions(), NULL, 'name'); | |
34 | $this->assertArrayHasKey('SUM', $functions); | |
35 | $this->assertArrayNotHasKey('', $functions); | |
36 | $this->assertArrayNotHasKey('SqlFunction', $functions); | |
fa7465e4 CW |
37 | $this->assertEquals(1, $functions['MAX']['params'][0]['min_expr']); |
38 | $this->assertEquals(1, $functions['MAX']['params'][0]['max_expr']); | |
f0acec37 CW |
39 | } |
40 | ||
41 | public function testGroupAggregates() { | |
fe806431 CW |
42 | $cid = Contact::create(FALSE)->addValue('first_name', 'bill')->execute()->first()['id']; |
43 | Contribution::save(FALSE) | |
7ce7b1cd | 44 | ->setDefaults(['contact_id' => $cid, 'financial_type_id:name' => 'Donation']) |
f0acec37 CW |
45 | ->setRecords([ |
46 | ['total_amount' => 100, 'receive_date' => '2020-01-01'], | |
47 | ['total_amount' => 200, 'receive_date' => '2020-01-01'], | |
7ce7b1cd CW |
48 | ['total_amount' => 300, 'receive_date' => '2020-01-01', 'financial_type_id:name' => 'Member Dues'], |
49 | ['total_amount' => 400, 'receive_date' => '2020-01-01', 'financial_type_id:name' => 'Event Fee'], | |
f0acec37 CW |
50 | ]) |
51 | ->execute(); | |
7ce7b1cd CW |
52 | |
53 | // Test AVG, SUM, MAX, MIN, COUNT | |
fe806431 | 54 | $agg = Contribution::get(FALSE) |
f0acec37 CW |
55 | ->addGroupBy('contact_id') |
56 | ->addWhere('contact_id', '=', $cid) | |
57 | ->addSelect('AVG(total_amount) AS average') | |
58 | ->addSelect('SUM(total_amount)') | |
59 | ->addSelect('MAX(total_amount)') | |
60 | ->addSelect('MIN(total_amount)') | |
61 | ->addSelect('COUNT(*) AS count') | |
e62cd61b | 62 | ->addOrderBy('average') |
f0acec37 CW |
63 | ->execute() |
64 | ->first(); | |
7ce7b1cd CW |
65 | $this->assertTrue(250.0 === $agg['average']); |
66 | $this->assertTrue(1000.0 === $agg['SUM:total_amount']); | |
67 | $this->assertTrue(400.0 === $agg['MAX:total_amount']); | |
68 | $this->assertTrue(100.0 === $agg['MIN:total_amount']); | |
69 | $this->assertTrue(4 === $agg['count']); | |
70 | ||
71 | // Test GROUP_CONCAT | |
72 | $agg = Contribution::get(FALSE) | |
73 | ->addGroupBy('contact_id') | |
74 | ->addWhere('contact_id', '=', $cid) | |
75 | ->addSelect('GROUP_CONCAT(financial_type_id:name)') | |
259207d0 | 76 | ->addSelect('GROUP_CONCAT(financial_type_id)') |
7ce7b1cd CW |
77 | ->addSelect('COUNT(*) AS count') |
78 | ->execute() | |
79 | ->first(); | |
80 | ||
81 | $this->assertTrue(4 === $agg['count']); | |
82 | $this->assertContains('Donation', $agg['GROUP_CONCAT:financial_type_id:name']); | |
259207d0 CW |
83 | foreach ($agg['GROUP_CONCAT:financial_type_id'] as $type) { |
84 | $this->assertTrue(is_int($type)); | |
85 | } | |
725a91cb SL |
86 | |
87 | // Test GROUP_CONCAT with a CONCAT as well | |
88 | $agg = Contribution::get(FALSE) | |
89 | ->addGroupBy('contact_id') | |
90 | ->addWhere('contact_id', '=', $cid) | |
91 | ->addSelect("GROUP_CONCAT(CONCAT(financial_type_id, ', ', contact_id, ', ', total_amount))") | |
92 | ->addSelect('COUNT(*) AS count') | |
93 | ->execute() | |
94 | ->first(); | |
95 | ||
96 | $this->assertTrue(4 === $agg['count']); | |
97 | $this->assertContains('1, ' . $cid . ', 100.00', $agg['GROUP_CONCAT:financial_type_id_contact_id_total_amount']); | |
f0acec37 CW |
98 | } |
99 | ||
100 | public function testGroupHaving() { | |
fe806431 CW |
101 | $cid = Contact::create(FALSE)->addValue('first_name', 'donor')->execute()->first()['id']; |
102 | Contribution::save(FALSE) | |
f0acec37 CW |
103 | ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1]) |
104 | ->setRecords([ | |
105 | ['total_amount' => 100, 'receive_date' => '2020-02-02'], | |
106 | ['total_amount' => 200, 'receive_date' => '2020-02-02'], | |
107 | ['total_amount' => 300, 'receive_date' => '2020-03-03'], | |
108 | ['total_amount' => 400, 'receive_date' => '2020-04-04'], | |
109 | ]) | |
110 | ->execute(); | |
fe806431 | 111 | $result = Contribution::get(FALSE) |
f0acec37 CW |
112 | ->addGroupBy('contact_id') |
113 | ->addGroupBy('receive_date') | |
114 | ->addSelect('contact_id') | |
115 | ->addSelect('receive_date') | |
116 | ->addSelect('AVG(total_amount) AS average') | |
117 | ->addSelect('SUM(total_amount)') | |
118 | ->addSelect('MAX(total_amount)') | |
119 | ->addSelect('MIN(total_amount)') | |
120 | ->addSelect('COUNT(*) AS count') | |
121 | ->addOrderBy('receive_date') | |
122 | ->addHaving('contact_id', '=', $cid) | |
123 | ->addHaving('receive_date', '<', '2020-04-01') | |
124 | ->execute(); | |
125 | $this->assertCount(2, $result); | |
126 | $this->assertEquals(150, $result[0]['average']); | |
127 | $this->assertEquals(300, $result[1]['average']); | |
128 | $this->assertEquals(300, $result[0]['SUM:total_amount']); | |
129 | $this->assertEquals(300, $result[1]['SUM:total_amount']); | |
130 | $this->assertEquals(200, $result[0]['MAX:total_amount']); | |
131 | $this->assertEquals(100, $result[0]['MIN:total_amount']); | |
132 | $this->assertEquals(2, $result[0]['count']); | |
133 | $this->assertEquals(1, $result[1]['count']); | |
1f76bc10 PF |
134 | |
135 | $result = Contribution::get(FALSE) | |
136 | ->addGroupBy('contact_id') | |
137 | ->addGroupBy('receive_date') | |
138 | ->addSelect('contact_id') | |
139 | ->addSelect('receive_date') | |
140 | ->addSelect('SUM(total_amount)') | |
141 | ->addOrderBy('receive_date') | |
142 | ->addWhere('contact_id', '=', $cid) | |
143 | ->addHaving('SUM(total_amount)', '>', 300) | |
144 | ->execute(); | |
145 | $this->assertCount(1, $result); | |
146 | $this->assertStringStartsWith('2020-04-04', $result[0]['receive_date']); | |
147 | $this->assertEquals(400, $result[0]['SUM:total_amount']); | |
f0acec37 CW |
148 | } |
149 | ||
daf464c1 CW |
150 | public function testComparisonFunctions() { |
151 | $cid = Contact::create(FALSE) | |
152 | ->addValue('first_name', 'hello') | |
153 | ->execute()->first()['id']; | |
154 | $sampleData = [ | |
155 | ['subject' => 'abc', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 123, 'location' => 'abc'], | |
156 | ['subject' => 'xyz', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'location' => 'abc', 'is_deleted' => 1], | |
157 | ['subject' => 'def', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 456, 'location' => 'abc'], | |
158 | ]; | |
159 | $aids = Activity::save(FALSE) | |
160 | ->setRecords($sampleData) | |
161 | ->execute()->column('id'); | |
162 | ||
163 | $result = Activity::get(FALSE) | |
164 | ->addWhere('id', 'IN', $aids) | |
165 | ->addSelect('IF(is_deleted, "Trash", "No Trash") AS trashed') | |
166 | ->addSelect('NULLIF(subject, location) AS subject_is_location') | |
167 | ->addSelect('NULLIF(duration, 456) AS duration_not_456') | |
168 | ->addSelect('COALESCE(duration, location) AS duration_or_location') | |
169 | ->addSelect('GREATEST(duration, 0200) AS duration_or_200') | |
170 | ->addSelect('LEAST(duration, 300) AS 300_or_duration') | |
171 | ->addSelect('ISNULL(duration) AS duration_isnull') | |
172 | ->addOrderBy('id') | |
173 | ->execute()->indexBy('id'); | |
174 | ||
175 | $this->assertCount(3, $result); | |
176 | $this->assertEquals('No Trash', $result[$aids[0]]['trashed']); | |
177 | $this->assertEquals('Trash', $result[$aids[1]]['trashed']); | |
178 | $this->assertEquals('No Trash', $result[$aids[2]]['trashed']); | |
179 | $this->assertEquals(NULL, $result[$aids[0]]['subject_is_location']); | |
180 | $this->assertEquals('xyz', $result[$aids[1]]['subject_is_location']); | |
181 | $this->assertEquals('def', $result[$aids[2]]['subject_is_location']); | |
182 | $this->assertEquals(123, $result[$aids[0]]['duration_not_456']); | |
183 | $this->assertEquals(NULL, $result[$aids[1]]['duration_not_456']); | |
184 | $this->assertEquals(NULL, $result[$aids[2]]['duration_not_456']); | |
185 | $this->assertEquals('123', $result[$aids[0]]['duration_or_location']); | |
186 | $this->assertEquals('abc', $result[$aids[1]]['duration_or_location']); | |
187 | $this->assertEquals(123, $result[$aids[0]]['300_or_duration']); | |
188 | $this->assertEquals(300, $result[$aids[2]]['300_or_duration']); | |
189 | $this->assertEquals(FALSE, $result[$aids[0]]['duration_isnull']); | |
190 | $this->assertEquals(TRUE, $result[$aids[1]]['duration_isnull']); | |
191 | $this->assertEquals(FALSE, $result[$aids[2]]['duration_isnull']); | |
192 | } | |
193 | ||
2929fd34 CW |
194 | public function testStringFunctions() { |
195 | $sampleData = [ | |
9e9feedf | 196 | ['first_name' => 'abc', 'middle_name' => 'Q', 'last_name' => 'tester1', 'source' => '123'], |
2929fd34 CW |
197 | ]; |
198 | $cid = Contact::save(FALSE) | |
199 | ->setRecords($sampleData) | |
200 | ->execute()->first()['id']; | |
201 | ||
202 | $result = Contact::get(FALSE) | |
203 | ->addWhere('id', '=', $cid) | |
204 | ->addSelect('CONCAT_WS("|", first_name, middle_name, last_name) AS concat_ws') | |
9e9feedf CW |
205 | ->addSelect('REPLACE(first_name, "c", "cdef") AS new_first') |
206 | ->addSelect('UPPER(first_name)') | |
207 | ->addSelect('LOWER(middle_name)') | |
2929fd34 CW |
208 | ->execute()->first(); |
209 | ||
9e9feedf CW |
210 | $this->assertEquals('abc|Q|tester1', $result['concat_ws']); |
211 | $this->assertEquals('abcdef', $result['new_first']); | |
212 | $this->assertEquals('ABC', $result['UPPER:first_name']); | |
213 | $this->assertEquals('q', $result['LOWER:middle_name']); | |
2929fd34 CW |
214 | } |
215 | ||
fa7465e4 CW |
216 | public function testIncorrectNumberOfArguments() { |
217 | try { | |
218 | Activity::get(FALSE) | |
219 | ->addSelect('IF(is_deleted) AS whoops') | |
220 | ->execute(); | |
221 | $this->fail('Api should have thrown exception'); | |
222 | } | |
223 | catch (\API_Exception $e) { | |
9e9feedf | 224 | $this->assertEquals('Missing param 2 for SQL function IF', $e->getMessage()); |
fa7465e4 CW |
225 | } |
226 | ||
227 | try { | |
228 | Activity::get(FALSE) | |
229 | ->addSelect('NULLIF(is_deleted, 1, 2) AS whoops') | |
230 | ->execute(); | |
231 | $this->fail('Api should have thrown exception'); | |
232 | } | |
233 | catch (\API_Exception $e) { | |
9e9feedf CW |
234 | $this->assertEquals('Too many arguments given for SQL function NULLIF', $e->getMessage()); |
235 | } | |
236 | ||
237 | try { | |
238 | Activity::get(FALSE) | |
239 | ->addSelect('CONCAT_WS(",", ) AS whoops') | |
240 | ->execute(); | |
241 | $this->fail('Api should have thrown exception'); | |
242 | } | |
243 | catch (\API_Exception $e) { | |
244 | $this->assertEquals('Too few arguments to param 2 for SQL function CONCAT_WS', $e->getMessage()); | |
fa7465e4 CW |
245 | } |
246 | } | |
247 | ||
9a0f174b CW |
248 | public function testRandFunction() { |
249 | $cid = Contact::create(FALSE) | |
250 | ->addValue('first_name', 'hello') | |
251 | ->execute()->first()['id']; | |
252 | ||
253 | $result = Contact::get(FALSE) | |
254 | ->addSelect('RAND() AS rand') | |
255 | ->addOrderBy('RAND()') | |
256 | ->setDebug(TRUE) | |
257 | ->setLimit(1) | |
258 | ->execute(); | |
259 | ||
260 | $this->assertStringContainsString('ORDER BY RAND()', $result->debug['sql'][0]); | |
261 | $this->assertGreaterThanOrEqual(0, $result[0]['rand']); | |
262 | $this->assertLessThan(1, $result[0]['rand']); | |
263 | } | |
264 | ||
f0acec37 | 265 | } |