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 namespace api\v4\Action;
  21
  22 use api\v4\UnitTestCase;
  23 use Civi\Api4\Contact;
  24 use Civi\Api4\Contribution;
  25
  26 /**
  27  * @group headless
  28  */
  29 class SqlFunctionTest extends UnitTestCase {
  30
  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']);
  37   }
  38
  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])
  43       ->setRecords([
  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'],
  48       ])
  49       ->execute();
  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')
  58       ->execute()
  59       ->first();
  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']);
  65   }
  66
  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])
  71       ->setRecords([
  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'],
  76       ])
  77       ->execute();
  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')
  91       ->execute();
  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']);
 101   }
 102
 103 }