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