tests/phpunit/api/v4/Action/SqlFunctionTest.php

   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
  17  *
  18  */
  19
  20
  21 namespace api\v4\Action;
  22
  23 use api\v4\UnitTestCase;
  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);
  37     $this->assertEquals(1, $functions['MAX']['params'][0]['expr']);
  38   }
  39
  40   public function testGroupAggregates() {
  41     $cid = Contact::create(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
  42     Contribution::save(FALSE)
  43       ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
  44       ->setRecords([
  45         ['total_amount' => 100, 'receive_date' => '2020-01-01'],
  46         ['total_amount' => 200, 'receive_date' => '2020-01-01'],
  47         ['total_amount' => 300, 'receive_date' => '2020-01-01'],
  48         ['total_amount' => 400, 'receive_date' => '2020-01-01'],
  49       ])
  50       ->execute();
  51     $agg = Contribution::get(FALSE)
  52       ->addGroupBy('contact_id')
  53       ->addWhere('contact_id', '=', $cid)
  54       ->addSelect('AVG(total_amount) AS average')
  55       ->addSelect('SUM(total_amount)')
  56       ->addSelect('MAX(total_amount)')
  57       ->addSelect('MIN(total_amount)')
  58       ->addSelect('COUNT(*) AS count')
  59       ->execute()
  60       ->first();
  61     $this->assertEquals(250, $agg['average']);
  62     $this->assertEquals(1000, $agg['SUM:total_amount']);
  63     $this->assertEquals(400, $agg['MAX:total_amount']);
  64     $this->assertEquals(100, $agg['MIN:total_amount']);
  65     $this->assertEquals(4, $agg['count']);
  66   }
  67
  68   public function testGroupHaving() {
  69     $cid = Contact::create(FALSE)->addValue('first_name', 'donor')->execute()->first()['id'];
  70     Contribution::save(FALSE)
  71       ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
  72       ->setRecords([
  73         ['total_amount' => 100, 'receive_date' => '2020-02-02'],
  74         ['total_amount' => 200, 'receive_date' => '2020-02-02'],
  75         ['total_amount' => 300, 'receive_date' => '2020-03-03'],
  76         ['total_amount' => 400, 'receive_date' => '2020-04-04'],
  77       ])
  78       ->execute();
  79     $result = Contribution::get(FALSE)
  80       ->addGroupBy('contact_id')
  81       ->addGroupBy('receive_date')
  82       ->addSelect('contact_id')
  83       ->addSelect('receive_date')
  84       ->addSelect('AVG(total_amount) AS average')
  85       ->addSelect('SUM(total_amount)')
  86       ->addSelect('MAX(total_amount)')
  87       ->addSelect('MIN(total_amount)')
  88       ->addSelect('COUNT(*) AS count')
  89       ->addOrderBy('receive_date')
  90       ->addHaving('contact_id', '=', $cid)
  91       ->addHaving('receive_date', '<', '2020-04-01')
  92       ->execute();
  93     $this->assertCount(2, $result);
  94     $this->assertEquals(150, $result[0]['average']);
  95     $this->assertEquals(300, $result[1]['average']);
  96     $this->assertEquals(300, $result[0]['SUM:total_amount']);
  97     $this->assertEquals(300, $result[1]['SUM:total_amount']);
  98     $this->assertEquals(200, $result[0]['MAX:total_amount']);
  99     $this->assertEquals(100, $result[0]['MIN:total_amount']);
 100     $this->assertEquals(2, $result[0]['count']);
 101     $this->assertEquals(1, $result[1]['count']);
 102   }
 103
 104 }