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
21 namespace api\v
4\Action
;
23 use api\v
4\UnitTestCase
;
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]['expr']);
40 public function testGroupAggregates() {
41 $cid = Contact
::create()->setCheckPermissions(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
43 ->setCheckPermissions(FALSE)
44 ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
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'],
49 ['total_amount' => 400, 'receive_date' => '2020-01-01'],
52 $agg = Contribution
::get()
53 ->setCheckPermissions(FALSE)
54 ->addGroupBy('contact_id')
55 ->addWhere('contact_id', '=', $cid)
56 ->addSelect('AVG(total_amount) AS average')
57 ->addSelect('SUM(total_amount)')
58 ->addSelect('MAX(total_amount)')
59 ->addSelect('MIN(total_amount)')
60 ->addSelect('COUNT(*) AS count')
63 $this->assertEquals(250, $agg['average']);
64 $this->assertEquals(1000, $agg['SUM:total_amount']);
65 $this->assertEquals(400, $agg['MAX:total_amount']);
66 $this->assertEquals(100, $agg['MIN:total_amount']);
67 $this->assertEquals(4, $agg['count']);
70 public function testGroupHaving() {
71 $cid = Contact
::create()->setCheckPermissions(FALSE)->addValue('first_name', 'donor')->execute()->first()['id'];
73 ->setCheckPermissions(FALSE)
74 ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
76 ['total_amount' => 100, 'receive_date' => '2020-02-02'],
77 ['total_amount' => 200, 'receive_date' => '2020-02-02'],
78 ['total_amount' => 300, 'receive_date' => '2020-03-03'],
79 ['total_amount' => 400, 'receive_date' => '2020-04-04'],
82 $result = Contribution
::get()
83 ->setCheckPermissions(FALSE)
84 ->addGroupBy('contact_id')
85 ->addGroupBy('receive_date')
86 ->addSelect('contact_id')
87 ->addSelect('receive_date')
88 ->addSelect('AVG(total_amount) AS average')
89 ->addSelect('SUM(total_amount)')
90 ->addSelect('MAX(total_amount)')
91 ->addSelect('MIN(total_amount)')
92 ->addSelect('COUNT(*) AS count')
93 ->addOrderBy('receive_date')
94 ->addHaving('contact_id', '=', $cid)
95 ->addHaving('receive_date', '<', '2020-04-01')
97 $this->assertCount(2, $result);
98 $this->assertEquals(150, $result[0]['average']);
99 $this->assertEquals(300, $result[1]['average']);
100 $this->assertEquals(300, $result[0]['SUM:total_amount']);
101 $this->assertEquals(300, $result[1]['SUM:total_amount']);
102 $this->assertEquals(200, $result[0]['MAX:total_amount']);
103 $this->assertEquals(100, $result[0]['MIN:total_amount']);
104 $this->assertEquals(2, $result[0]['count']);
105 $this->assertEquals(1, $result[1]['count']);