4 +--------------------------------------------------------------------+
5 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
16 * @copyright CiviCRM LLC https://civicrm.org/licensing
20 namespace api\v
4\Action
;
22 use api\v
4\UnitTestCase
;
23 use Civi\Api4\Activity
;
24 use Civi\Api4\Contact
;
25 use Civi\Api4\Contribution
;
30 class SqlFunctionTest
extends UnitTestCase
{
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);
37 $this->assertEquals(1, $functions['MAX']['params'][0]['min_expr']);
38 $this->assertEquals(1, $functions['MAX']['params'][0]['max_expr']);
41 public function testGroupAggregates() {
42 $cid = Contact
::create(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
43 Contribution
::save(FALSE)
44 ->setDefaults(['contact_id' => $cid, 'financial_type_id:name' => 'Donation'])
46 ['total_amount' => 100, 'receive_date' => '2020-01-01'],
47 ['total_amount' => 200, 'receive_date' => '2020-01-01'],
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'],
53 // Test AVG, SUM, MAX, MIN, COUNT
54 $agg = Contribution
::get(FALSE)
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')
62 ->addOrderBy('average')
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']);
72 $agg = Contribution
::get(FALSE)
73 ->addGroupBy('contact_id')
74 ->addWhere('contact_id', '=', $cid)
75 ->addSelect('GROUP_CONCAT(financial_type_id:name)')
76 ->addSelect('GROUP_CONCAT(financial_type_id)')
77 ->addSelect('COUNT(*) AS count')
81 $this->assertTrue(4 === $agg['count']);
82 $this->assertContains('Donation', $agg['GROUP_CONCAT:financial_type_id:name']);
83 foreach ($agg['GROUP_CONCAT:financial_type_id'] as $type) {
84 $this->assertTrue(is_int($type));
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')
96 $this->assertTrue(4 === $agg['count']);
97 $this->assertContains('1, ' . $cid . ', 100.00', $agg['GROUP_CONCAT:financial_type_id_contact_id_total_amount']);
100 public function testGroupHaving() {
101 $cid = Contact
::create(FALSE)->addValue('first_name', 'donor')->execute()->first()['id'];
102 Contribution
::save(FALSE)
103 ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
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'],
111 $result = Contribution
::get(FALSE)
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')
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']);
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)
145 $this->assertCount(1, $result);
146 $this->assertStringStartsWith('2020-04-04', $result[0]['receive_date']);
147 $this->assertEquals(400, $result[0]['SUM:total_amount']);
150 public function testComparisonFunctions() {
151 $cid = Contact
::create(FALSE)
152 ->addValue('first_name', 'hello')
153 ->execute()->first()['id'];
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'],
159 $aids = Activity
::save(FALSE)
160 ->setRecords($sampleData)
161 ->execute()->column('id');
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')
173 ->execute()->indexBy('id');
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']);
194 public function testStringFunctions() {
196 ['first_name' => 'abc', 'middle_name' => 'Q', 'last_name' => 'tester1', 'source' => '123'],
198 $cid = Contact
::save(FALSE)
199 ->setRecords($sampleData)
200 ->execute()->first()['id'];
202 $result = Contact
::get(FALSE)
203 ->addWhere('id', '=', $cid)
204 ->addSelect('CONCAT_WS("|", first_name, middle_name, last_name) AS concat_ws')
205 ->addSelect('REPLACE(first_name, "c", "cdef") AS new_first')
206 ->addSelect('UPPER(first_name)')
207 ->addSelect('LOWER(middle_name)')
208 ->execute()->first();
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']);
216 public function testIncorrectNumberOfArguments() {
219 ->addSelect('IF(is_deleted) AS whoops')
221 $this->fail('Api should have thrown exception');
223 catch (\API_Exception
$e) {
224 $this->assertEquals('Missing param 2 for SQL function IF', $e->getMessage());
229 ->addSelect('NULLIF(is_deleted, 1, 2) AS whoops')
231 $this->fail('Api should have thrown exception');
233 catch (\API_Exception
$e) {
234 $this->assertEquals('Too many arguments given for SQL function NULLIF', $e->getMessage());
239 ->addSelect('CONCAT_WS(",", ) AS whoops')
241 $this->fail('Api should have thrown exception');
243 catch (\API_Exception
$e) {
244 $this->assertEquals('Too few arguments to param 2 for SQL function CONCAT_WS', $e->getMessage());
248 public function testRandFunction() {
249 $cid = Contact
::create(FALSE)
250 ->addValue('first_name', 'hello')
251 ->execute()->first()['id'];
253 $result = Contact
::get(FALSE)
254 ->addSelect('RAND() AS rand')
255 ->addOrderBy('RAND()')
260 $this->assertStringContainsString('ORDER BY RAND()', $result->debug
['sql'][0]);
261 $this->assertGreaterThanOrEqual(0, $result[0]['rand']);
262 $this->assertLessThan(1, $result[0]['rand']);