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\Contact
;
24 use Civi\Api4\Contribution
;
29 class SqlFunctionTest
extends UnitTestCase
{
31 public function testGetFunctions() {
32 $functions = array_column(\CRM_Api4_Page_Api4Explorer
::getSqlFunctions(), NULL, 'name');
33 $this->assertArrayHasKey('SUM', $functions);
34 $this->assertArrayNotHasKey('', $functions);
35 $this->assertArrayNotHasKey('SqlFunction', $functions);
36 $this->assertEquals(1, $functions['MAX']['params'][0]['expr']);
39 public function testGroupAggregates() {
40 $cid = Contact
::create(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
41 Contribution
::save(FALSE)
42 ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
44 ['total_amount' => 100, 'receive_date' => '2020-01-01'],
45 ['total_amount' => 200, 'receive_date' => '2020-01-01'],
46 ['total_amount' => 300, 'receive_date' => '2020-01-01'],
47 ['total_amount' => 400, 'receive_date' => '2020-01-01'],
50 $agg = Contribution
::get(FALSE)
51 ->addGroupBy('contact_id')
52 ->addWhere('contact_id', '=', $cid)
53 ->addSelect('AVG(total_amount) AS average')
54 ->addSelect('SUM(total_amount)')
55 ->addSelect('MAX(total_amount)')
56 ->addSelect('MIN(total_amount)')
57 ->addSelect('COUNT(*) AS count')
60 $this->assertEquals(250, $agg['average']);
61 $this->assertEquals(1000, $agg['SUM:total_amount']);
62 $this->assertEquals(400, $agg['MAX:total_amount']);
63 $this->assertEquals(100, $agg['MIN:total_amount']);
64 $this->assertEquals(4, $agg['count']);
67 public function testGroupHaving() {
68 $cid = Contact
::create(FALSE)->addValue('first_name', 'donor')->execute()->first()['id'];
69 Contribution
::save(FALSE)
70 ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
72 ['total_amount' => 100, 'receive_date' => '2020-02-02'],
73 ['total_amount' => 200, 'receive_date' => '2020-02-02'],
74 ['total_amount' => 300, 'receive_date' => '2020-03-03'],
75 ['total_amount' => 400, 'receive_date' => '2020-04-04'],
78 $result = Contribution
::get(FALSE)
79 ->addGroupBy('contact_id')
80 ->addGroupBy('receive_date')
81 ->addSelect('contact_id')
82 ->addSelect('receive_date')
83 ->addSelect('AVG(total_amount) AS average')
84 ->addSelect('SUM(total_amount)')
85 ->addSelect('MAX(total_amount)')
86 ->addSelect('MIN(total_amount)')
87 ->addSelect('COUNT(*) AS count')
88 ->addOrderBy('receive_date')
89 ->addHaving('contact_id', '=', $cid)
90 ->addHaving('receive_date', '<', '2020-04-01')
92 $this->assertCount(2, $result);
93 $this->assertEquals(150, $result[0]['average']);
94 $this->assertEquals(300, $result[1]['average']);
95 $this->assertEquals(300, $result[0]['SUM:total_amount']);
96 $this->assertEquals(300, $result[1]['SUM:total_amount']);
97 $this->assertEquals(200, $result[0]['MAX:total_amount']);
98 $this->assertEquals(100, $result[0]['MIN:total_amount']);
99 $this->assertEquals(2, $result[0]['count']);
100 $this->assertEquals(1, $result[1]['count']);