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\Activity;
  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]['min_expr']);
  38     $this->assertEquals(1, $functions['MAX']['params'][0]['max_expr']);
  39   }
  40
  41   public function testGroupAggregates() {
  42     $cid = Contact::create(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
  43     Contribution::save(FALSE)
  44       ->setDefaults(['contact_id' => $cid, 'financial_type_id:name' => 'Donation'])
  45       ->setRecords([
  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', 'financial_type_id:name' => 'Member Dues'],
  49         ['total_amount' => 400, 'receive_date' => '2020-01-01', 'financial_type_id:name' => 'Event Fee'],
  50       ])
  51       ->execute();
  52
  53     // Test AVG, SUM, MAX, MIN, COUNT
  54     $agg = Contribution::get(FALSE)
  55       ->addGroupBy('contact_id')
  56       ->addWhere('contact_id', '=', $cid)
  57       ->addSelect('AVG(total_amount) AS average')
  58       ->addSelect('SUM(total_amount)')
  59       ->addSelect('MAX(total_amount)')
  60       ->addSelect('MIN(total_amount)')
  61       ->addSelect('COUNT(*) AS count')
  62       ->addOrderBy('average')
  63       ->execute()
  64       ->first();
  65     $this->assertTrue(250.0 === $agg['average']);
  66     $this->assertTrue(1000.0 === $agg['SUM:total_amount']);
  67     $this->assertTrue(400.0 === $agg['MAX:total_amount']);
  68     $this->assertTrue(100.0 === $agg['MIN:total_amount']);
  69     $this->assertTrue(4 === $agg['count']);
  70
  71     // Test GROUP_CONCAT
  72     $agg = Contribution::get(FALSE)
  73       ->addGroupBy('contact_id')
  74       ->addWhere('contact_id', '=', $cid)
  75       ->addSelect('GROUP_CONCAT(financial_type_id:name)')
  76       ->addSelect('GROUP_CONCAT(financial_type_id)')
  77       ->addSelect('COUNT(*) AS count')
  78       ->execute()
  79       ->first();
  80
  81     $this->assertTrue(4 === $agg['count']);
  82     $this->assertContains('Donation', $agg['GROUP_CONCAT:financial_type_id:name']);
  83     foreach ($agg['GROUP_CONCAT:financial_type_id'] as $type) {
  84       $this->assertTrue(is_int($type));
  85     }
  86
  87     // Test GROUP_CONCAT with a CONCAT as well
  88     $agg = Contribution::get(FALSE)
  89       ->addGroupBy('contact_id')
  90       ->addWhere('contact_id', '=', $cid)
  91       ->addSelect("GROUP_CONCAT(CONCAT(financial_type_id, ', ', contact_id, ', ', total_amount))")
  92       ->addSelect('COUNT(*) AS count')
  93       ->execute()
  94       ->first();
  95
  96     $this->assertTrue(4 === $agg['count']);
  97     $this->assertContains('1, ' . $cid . ', 100.00', $agg['GROUP_CONCAT:financial_type_id_contact_id_total_amount']);
  98   }
  99
 100   public function testGroupHaving() {
 101     $cid = Contact::create(FALSE)->addValue('first_name', 'donor')->execute()->first()['id'];
 102     Contribution::save(FALSE)
 103       ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
 104       ->setRecords([
 105         ['total_amount' => 100, 'receive_date' => '2020-02-02'],
 106         ['total_amount' => 200, 'receive_date' => '2020-02-02'],
 107         ['total_amount' => 300, 'receive_date' => '2020-03-03'],
 108         ['total_amount' => 400, 'receive_date' => '2020-04-04'],
 109       ])
 110       ->execute();
 111     $result = Contribution::get(FALSE)
 112       ->addGroupBy('contact_id')
 113       ->addGroupBy('receive_date')
 114       ->addSelect('contact_id')
 115       ->addSelect('receive_date')
 116       ->addSelect('AVG(total_amount) AS average')
 117       ->addSelect('SUM(total_amount)')
 118       ->addSelect('MAX(total_amount)')
 119       ->addSelect('MIN(total_amount)')
 120       ->addSelect('COUNT(*) AS count')
 121       ->addOrderBy('receive_date')
 122       ->addHaving('contact_id', '=', $cid)
 123       ->addHaving('receive_date', '<', '2020-04-01')
 124       ->execute();
 125     $this->assertCount(2, $result);
 126     $this->assertEquals(150, $result[0]['average']);
 127     $this->assertEquals(300, $result[1]['average']);
 128     $this->assertEquals(300, $result[0]['SUM:total_amount']);
 129     $this->assertEquals(300, $result[1]['SUM:total_amount']);
 130     $this->assertEquals(200, $result[0]['MAX:total_amount']);
 131     $this->assertEquals(100, $result[0]['MIN:total_amount']);
 132     $this->assertEquals(2, $result[0]['count']);
 133     $this->assertEquals(1, $result[1]['count']);
 134
 135     $result = Contribution::get(FALSE)
 136       ->addGroupBy('contact_id')
 137       ->addGroupBy('receive_date')
 138       ->addSelect('contact_id')
 139       ->addSelect('receive_date')
 140       ->addSelect('SUM(total_amount)')
 141       ->addOrderBy('receive_date')
 142       ->addWhere('contact_id', '=', $cid)
 143       ->addHaving('SUM(total_amount)', '>', 300)
 144       ->execute();
 145     $this->assertCount(1, $result);
 146     $this->assertStringStartsWith('2020-04-04', $result[0]['receive_date']);
 147     $this->assertEquals(400, $result[0]['SUM:total_amount']);
 148   }
 149
 150   public function testComparisonFunctions() {
 151     $cid = Contact::create(FALSE)
 152       ->addValue('first_name', 'hello')
 153       ->execute()->first()['id'];
 154     $sampleData = [
 155       ['subject' => 'abc', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 123, 'location' => 'abc'],
 156       ['subject' => 'xyz', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'location' => 'abc', 'is_deleted' => 1],
 157       ['subject' => 'def', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 456, 'location' => 'abc'],
 158     ];
 159     $aids = Activity::save(FALSE)
 160       ->setRecords($sampleData)
 161       ->execute()->column('id');
 162
 163     $result = Activity::get(FALSE)
 164       ->addWhere('id', 'IN', $aids)
 165       ->addSelect('IF(is_deleted, "Trash", "No Trash") AS trashed')
 166       ->addSelect('NULLIF(subject, location) AS subject_is_location')
 167       ->addSelect('NULLIF(duration, 456) AS duration_not_456')
 168       ->addSelect('COALESCE(duration, location) AS duration_or_location')
 169       ->addSelect('GREATEST(duration, 0200) AS duration_or_200')
 170       ->addSelect('LEAST(duration, 300) AS 300_or_duration')
 171       ->addSelect('ISNULL(duration) AS duration_isnull')
 172       ->addOrderBy('id')
 173       ->execute()->indexBy('id');
 174
 175     $this->assertCount(3, $result);
 176     $this->assertEquals('No Trash', $result[$aids[0]]['trashed']);
 177     $this->assertEquals('Trash', $result[$aids[1]]['trashed']);
 178     $this->assertEquals('No Trash', $result[$aids[2]]['trashed']);
 179     $this->assertEquals(NULL, $result[$aids[0]]['subject_is_location']);
 180     $this->assertEquals('xyz', $result[$aids[1]]['subject_is_location']);
 181     $this->assertEquals('def', $result[$aids[2]]['subject_is_location']);
 182     $this->assertEquals(123, $result[$aids[0]]['duration_not_456']);
 183     $this->assertEquals(NULL, $result[$aids[1]]['duration_not_456']);
 184     $this->assertEquals(NULL, $result[$aids[2]]['duration_not_456']);
 185     $this->assertEquals('123', $result[$aids[0]]['duration_or_location']);
 186     $this->assertEquals('abc', $result[$aids[1]]['duration_or_location']);
 187     $this->assertEquals(123, $result[$aids[0]]['300_or_duration']);
 188     $this->assertEquals(300, $result[$aids[2]]['300_or_duration']);
 189     $this->assertEquals(FALSE, $result[$aids[0]]['duration_isnull']);
 190     $this->assertEquals(TRUE, $result[$aids[1]]['duration_isnull']);
 191     $this->assertEquals(FALSE, $result[$aids[2]]['duration_isnull']);
 192   }
 193
 194   public function testIncorrectNumberOfArguments() {
 195     try {
 196       Activity::get(FALSE)
 197         ->addSelect('IF(is_deleted) AS whoops')
 198         ->execute();
 199       $this->fail('Api should have thrown exception');
 200     }
 201     catch (\API_Exception $e) {
 202       $this->assertEquals('Incorrect number of arguments for SQL function IF', $e->getMessage());
 203     }
 204
 205     try {
 206       Activity::get(FALSE)
 207         ->addSelect('NULLIF(is_deleted, 1, 2) AS whoops')
 208         ->execute();
 209       $this->fail('Api should have thrown exception');
 210     }
 211     catch (\API_Exception $e) {
 212       $this->assertEquals('Incorrect number of arguments for SQL function NULLIF', $e->getMessage());
 213     }
 214   }
 215
 216   public function testRandFunction() {
 217     $cid = Contact::create(FALSE)
 218       ->addValue('first_name', 'hello')
 219       ->execute()->first()['id'];
 220
 221     $result = Contact::get(FALSE)
 222       ->addSelect('RAND() AS rand')
 223       ->addOrderBy('RAND()')
 224       ->setDebug(TRUE)
 225       ->setLimit(1)
 226       ->execute();
 227
 228     $this->assertStringContainsString('ORDER BY RAND()', $result->debug['sql'][0]);
 229     $this->assertGreaterThanOrEqual(0, $result[0]['rand']);
 230     $this->assertLessThan(1, $result[0]['rand']);
 231   }
 232
 233 }