Merge pull request #17920 from eileenmcnaughton/dupe

[civicrm-core.git] / tests / phpunit / api / v4 / Action / SqlFunctionTest.php

diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 9b33c594c3951f0c72bb9a8d734fb4c865fd9812..af91acffaff4d63c8d839258ed17369374eaa74c 100644 (file)
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -14,7 +14,6 @@
  *
  * @package CRM
  * @copyright CiviCRM LLC https://civicrm.org/licensing
- *
  */
 
 
@@ -29,10 +28,17 @@ use Civi\Api4\Contribution;
  */
 class SqlFunctionTest extends UnitTestCase {
 
+  public function testGetFunctions() {
+    $functions = array_column(\CRM_Api4_Page_Api4Explorer::getSqlFunctions(), NULL, 'name');
+    $this->assertArrayHasKey('SUM', $functions);
+    $this->assertArrayNotHasKey('', $functions);
+    $this->assertArrayNotHasKey('SqlFunction', $functions);
+    $this->assertEquals(1, $functions['MAX']['params'][0]['expr']);
+  }
+
   public function testGroupAggregates() {
-    $cid = Contact::create()->setCheckPermissions(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
-    Contribution::save()
-      ->setCheckPermissions(FALSE)
+    $cid = Contact::create(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
+    Contribution::save(FALSE)
       ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
       ->setRecords([
         ['total_amount' => 100, 'receive_date' => '2020-01-01'],
@@ -41,8 +47,7 @@ class SqlFunctionTest extends UnitTestCase {
         ['total_amount' => 400, 'receive_date' => '2020-01-01'],
       ])
       ->execute();
-    $agg = Contribution::get()
-      ->setCheckPermissions(FALSE)
+    $agg = Contribution::get(FALSE)
       ->addGroupBy('contact_id')
       ->addWhere('contact_id', '=', $cid)
       ->addSelect('AVG(total_amount) AS average')
@@ -59,4 +64,40 @@ class SqlFunctionTest extends UnitTestCase {
     $this->assertEquals(4, $agg['count']);
   }
 
+  public function testGroupHaving() {
+    $cid = Contact::create(FALSE)->addValue('first_name', 'donor')->execute()->first()['id'];
+    Contribution::save(FALSE)
+      ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
+      ->setRecords([
+        ['total_amount' => 100, 'receive_date' => '2020-02-02'],
+        ['total_amount' => 200, 'receive_date' => '2020-02-02'],
+        ['total_amount' => 300, 'receive_date' => '2020-03-03'],
+        ['total_amount' => 400, 'receive_date' => '2020-04-04'],
+      ])
+      ->execute();
+    $result = Contribution::get(FALSE)
+      ->addGroupBy('contact_id')
+      ->addGroupBy('receive_date')
+      ->addSelect('contact_id')
+      ->addSelect('receive_date')
+      ->addSelect('AVG(total_amount) AS average')
+      ->addSelect('SUM(total_amount)')
+      ->addSelect('MAX(total_amount)')
+      ->addSelect('MIN(total_amount)')
+      ->addSelect('COUNT(*) AS count')
+      ->addOrderBy('receive_date')
+      ->addHaving('contact_id', '=', $cid)
+      ->addHaving('receive_date', '<', '2020-04-01')
+      ->execute();
+    $this->assertCount(2, $result);
+    $this->assertEquals(150, $result[0]['average']);
+    $this->assertEquals(300, $result[1]['average']);
+    $this->assertEquals(300, $result[0]['SUM:total_amount']);
+    $this->assertEquals(300, $result[1]['SUM:total_amount']);
+    $this->assertEquals(200, $result[0]['MAX:total_amount']);
+    $this->assertEquals(100, $result[0]['MIN:total_amount']);
+    $this->assertEquals(2, $result[0]['count']);
+    $this->assertEquals(1, $result[1]['count']);
+  }
+
 }