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\Api4TestBase;
  23 use Civi\Api4\Activity;
  24 use Civi\Api4\Contact;
  25 use Civi\Api4\Contribution;
  26 use Civi\Test\TransactionalInterface;
  27
  28 /**
  29  * @group headless
  30  */
  31 class SqlFunctionTest extends Api4TestBase implements TransactionalInterface {
  32
  33   public function testGetFunctions() {
  34     $functions = array_column(\CRM_Api4_Page_Api4Explorer::getSqlFunctions(), NULL, 'name');
  35     $this->assertArrayHasKey('SUM', $functions);
  36     $this->assertArrayNotHasKey('', $functions);
  37     $this->assertArrayNotHasKey('SqlFunction', $functions);
  38     $this->assertEquals(1, $functions['MAX']['params'][0]['min_expr']);
  39     $this->assertEquals(1, $functions['MAX']['params'][0]['max_expr']);
  40   }
  41
  42   public function testGroupAggregates() {
  43     $cid = Contact::create(FALSE)->addValue('first_name', 'bill')->execute()->first()['id'];
  44     Contribution::save(FALSE)
  45       ->setDefaults(['contact_id' => $cid, 'financial_type_id:name' => 'Donation'])
  46       ->setRecords([
  47         ['total_amount' => 100, 'receive_date' => '2020-01-01'],
  48         ['total_amount' => 200, 'receive_date' => '2020-01-01'],
  49         ['total_amount' => 300, 'receive_date' => '2020-01-01', 'financial_type_id:name' => 'Member Dues'],
  50         ['total_amount' => 400, 'receive_date' => '2020-01-01', 'financial_type_id:name' => 'Event Fee'],
  51       ])
  52       ->execute();
  53
  54     // Test AVG, SUM, MAX, MIN, COUNT
  55     $agg = Contribution::get(FALSE)
  56       ->addGroupBy('contact_id')
  57       ->addWhere('contact_id', '=', $cid)
  58       ->addSelect('AVG(total_amount) AS average')
  59       ->addSelect('SUM(total_amount)')
  60       ->addSelect('MAX(total_amount)')
  61       ->addSelect('MIN(total_amount)')
  62       ->addSelect('COUNT(*) AS count')
  63       ->addOrderBy('average')
  64       ->execute()
  65       ->first();
  66     $this->assertTrue(250.0 === $agg['average']);
  67     $this->assertTrue(1000.0 === $agg['SUM:total_amount']);
  68     $this->assertTrue(400.0 === $agg['MAX:total_amount']);
  69     $this->assertTrue(100.0 === $agg['MIN:total_amount']);
  70     $this->assertTrue(4 === $agg['count']);
  71
  72     // Test GROUP_CONCAT
  73     $agg = Contribution::get(FALSE)
  74       ->addGroupBy('contact_id')
  75       ->addWhere('contact_id', '=', $cid)
  76       ->addSelect('GROUP_CONCAT(financial_type_id:name)')
  77       ->addSelect('GROUP_CONCAT(financial_type_id)')
  78       ->addSelect('COUNT(*) AS count')
  79       ->execute()
  80       ->first();
  81
  82     $this->assertTrue(4 === $agg['count']);
  83     $this->assertContains('Donation', $agg['GROUP_CONCAT:financial_type_id:name']);
  84     foreach ($agg['GROUP_CONCAT:financial_type_id'] as $type) {
  85       $this->assertTrue(is_int($type));
  86     }
  87
  88     // Test GROUP_CONCAT with a CONCAT as well
  89     $agg = Contribution::get(FALSE)
  90       ->addGroupBy('contact_id')
  91       ->addWhere('contact_id', '=', $cid)
  92       ->addSelect("GROUP_CONCAT(CONCAT(financial_type_id, ', ', contact_id, ', ', total_amount))")
  93       ->addSelect('COUNT(*) AS count')
  94       ->execute()
  95       ->first();
  96
  97     $this->assertTrue(4 === $agg['count']);
  98     $this->assertContains('1, ' . $cid . ', 100.00', $agg['GROUP_CONCAT:financial_type_id_contact_id_total_amount']);
  99   }
 100
 101   public function testGroupHaving() {
 102     $cid = Contact::create(FALSE)->addValue('first_name', 'donor')->execute()->first()['id'];
 103     Contribution::save(FALSE)
 104       ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1])
 105       ->setRecords([
 106         ['total_amount' => 100, 'receive_date' => '2020-02-02'],
 107         ['total_amount' => 200, 'receive_date' => '2020-02-02'],
 108         ['total_amount' => 300, 'receive_date' => '2020-03-03'],
 109         ['total_amount' => 400, 'receive_date' => '2020-04-04'],
 110       ])
 111       ->execute();
 112     $result = Contribution::get(FALSE)
 113       ->addGroupBy('contact_id')
 114       ->addGroupBy('receive_date')
 115       ->addSelect('contact_id')
 116       ->addSelect('receive_date')
 117       ->addSelect('AVG(total_amount) AS average')
 118       ->addSelect('SUM(total_amount)')
 119       ->addSelect('MAX(total_amount)')
 120       ->addSelect('MIN(total_amount)')
 121       ->addSelect('COUNT(*) AS count')
 122       ->addOrderBy('receive_date')
 123       ->addHaving('contact_id', '=', $cid)
 124       ->addHaving('receive_date', '<', '2020-04-01')
 125       ->execute();
 126     $this->assertCount(2, $result);
 127     $this->assertEquals(150, $result[0]['average']);
 128     $this->assertEquals(300, $result[1]['average']);
 129     $this->assertEquals(300, $result[0]['SUM:total_amount']);
 130     $this->assertEquals(300, $result[1]['SUM:total_amount']);
 131     $this->assertEquals(200, $result[0]['MAX:total_amount']);
 132     $this->assertEquals(100, $result[0]['MIN:total_amount']);
 133     $this->assertEquals(2, $result[0]['count']);
 134     $this->assertEquals(1, $result[1]['count']);
 135
 136     $result = Contribution::get(FALSE)
 137       ->addGroupBy('contact_id')
 138       ->addGroupBy('receive_date')
 139       ->addSelect('contact_id')
 140       ->addSelect('receive_date')
 141       ->addSelect('SUM(total_amount)')
 142       ->addOrderBy('receive_date')
 143       ->addWhere('contact_id', '=', $cid)
 144       ->addHaving('SUM(total_amount)', '>', 300)
 145       ->execute();
 146     $this->assertCount(1, $result);
 147     $this->assertStringStartsWith('2020-04-04', $result[0]['receive_date']);
 148     $this->assertEquals(400, $result[0]['SUM:total_amount']);
 149   }
 150
 151   public function testComparisonFunctions() {
 152     $cid = Contact::create(FALSE)
 153       ->addValue('first_name', 'hello')
 154       ->execute()->first()['id'];
 155     $sampleData = [
 156       ['subject' => 'abc', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 123, 'location' => 'abc'],
 157       ['subject' => 'xyz', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'location' => 'abc', 'is_deleted' => 1],
 158       ['subject' => 'def', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 456, 'location' => 'abc'],
 159     ];
 160     $aids = Activity::save(FALSE)
 161       ->setRecords($sampleData)
 162       ->execute()->column('id');
 163
 164     $result = Activity::get(FALSE)
 165       ->addWhere('id', 'IN', $aids)
 166       ->addSelect('IF(is_deleted, "Trash", "No Trash") AS trashed')
 167       ->addSelect('NULLIF(subject, location) AS subject_is_location')
 168       ->addSelect('NULLIF(duration, 456) AS duration_not_456')
 169       ->addSelect('COALESCE(duration, location) AS duration_or_location')
 170       ->addSelect('GREATEST(duration, 0200) AS duration_or_200')
 171       ->addSelect('LEAST(duration, 300) AS 300_or_duration')
 172       ->addSelect('ISNULL(duration) AS duration_isnull')
 173       ->addOrderBy('id')
 174       ->execute()->indexBy('id');
 175
 176     $this->assertCount(3, $result);
 177     $this->assertEquals('No Trash', $result[$aids[0]]['trashed']);
 178     $this->assertEquals('Trash', $result[$aids[1]]['trashed']);
 179     $this->assertEquals('No Trash', $result[$aids[2]]['trashed']);
 180     $this->assertEquals(NULL, $result[$aids[0]]['subject_is_location']);
 181     $this->assertEquals('xyz', $result[$aids[1]]['subject_is_location']);
 182     $this->assertEquals('def', $result[$aids[2]]['subject_is_location']);
 183     $this->assertEquals(123, $result[$aids[0]]['duration_not_456']);
 184     $this->assertEquals(NULL, $result[$aids[1]]['duration_not_456']);
 185     $this->assertEquals(NULL, $result[$aids[2]]['duration_not_456']);
 186     $this->assertEquals('123', $result[$aids[0]]['duration_or_location']);
 187     $this->assertEquals('abc', $result[$aids[1]]['duration_or_location']);
 188     $this->assertEquals(123, $result[$aids[0]]['300_or_duration']);
 189     $this->assertEquals(300, $result[$aids[2]]['300_or_duration']);
 190     $this->assertEquals(FALSE, $result[$aids[0]]['duration_isnull']);
 191     $this->assertEquals(TRUE, $result[$aids[1]]['duration_isnull']);
 192     $this->assertEquals(FALSE, $result[$aids[2]]['duration_isnull']);
 193   }
 194
 195   public function testStringFunctions() {
 196     $sampleData = [
 197       ['first_name' => 'abc', 'middle_name' => 'Q', 'last_name' => 'tester1', 'source' => '123'],
 198     ];
 199     $cid = Contact::save(FALSE)
 200       ->setRecords($sampleData)
 201       ->execute()->first()['id'];
 202
 203     $result = Contact::get(FALSE)
 204       ->addWhere('id', '=', $cid)
 205       ->addSelect('CONCAT_WS("|", first_name, middle_name, last_name) AS concat_ws')
 206       ->addSelect('REPLACE(first_name, "c", "cdef") AS new_first')
 207       ->addSelect('UPPER(first_name)')
 208       ->addSelect('LOWER(middle_name)')
 209       ->execute()->first();
 210
 211     $this->assertEquals('abc|Q|tester1', $result['concat_ws']);
 212     $this->assertEquals('abcdef', $result['new_first']);
 213     $this->assertEquals('ABC', $result['UPPER:first_name']);
 214     $this->assertEquals('q', $result['LOWER:middle_name']);
 215   }
 216
 217   public function testIncorrectNumberOfArguments() {
 218     try {
 219       Activity::get(FALSE)
 220         ->addSelect('IF(is_deleted) AS whoops')
 221         ->execute();
 222       $this->fail('Api should have thrown exception');
 223     }
 224     catch (\API_Exception $e) {
 225       $this->assertEquals('Missing param 2 for SQL function IF', $e->getMessage());
 226     }
 227
 228     try {
 229       Activity::get(FALSE)
 230         ->addSelect('NULLIF(is_deleted, 1, 2) AS whoops')
 231         ->execute();
 232       $this->fail('Api should have thrown exception');
 233     }
 234     catch (\API_Exception $e) {
 235       $this->assertEquals('Too many arguments given for SQL function NULLIF', $e->getMessage());
 236     }
 237
 238     try {
 239       Activity::get(FALSE)
 240         ->addSelect('CONCAT_WS(",", ) AS whoops')
 241         ->execute();
 242       $this->fail('Api should have thrown exception');
 243     }
 244     catch (\API_Exception $e) {
 245       $this->assertEquals('Too few arguments to param 2 for SQL function CONCAT_WS', $e->getMessage());
 246     }
 247   }
 248
 249   public function testCurrentDate() {
 250     $lastName = uniqid(__FUNCTION__);
 251     $sampleData = [
 252       ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => 'now'],
 253       ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => 'now - 1 year'],
 254       ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => 'now - 10 year'],
 255     ];
 256     Contact::save(FALSE)
 257       ->setRecords($sampleData)
 258       ->execute();
 259
 260     $result = Contact::get(FALSE)
 261       ->addWhere('last_name', '=', $lastName)
 262       ->addWhere('birth_date', '=', 'CURDATE()', TRUE)
 263       ->selectRowCount()
 264       ->execute();
 265     $this->assertCount(1, $result);
 266
 267     $result = Contact::get(FALSE)
 268       ->addWhere('last_name', '=', $lastName)
 269       ->addWhere('birth_date', '<', 'DATE(NOW())', TRUE)
 270       ->selectRowCount()
 271       ->execute();
 272     $this->assertCount(2, $result);
 273   }
 274
 275   public function testRandFunction() {
 276     Contact::save(FALSE)
 277       ->setRecords(array_fill(0, 6, []))
 278       ->execute();
 279
 280     $result = Contact::get(FALSE)
 281       ->addSelect('RAND() AS rand')
 282       ->addOrderBy('RAND()')
 283       ->setDebug(TRUE)
 284       ->setLimit(6)
 285       ->execute();
 286
 287     // Random numbers should have been ordered from least to greatest
 288     $this->assertGreaterThanOrEqual($result[0]['rand'], $result[1]['rand']);
 289     $this->assertGreaterThanOrEqual($result[1]['rand'], $result[2]['rand']);
 290     $this->assertGreaterThanOrEqual($result[2]['rand'], $result[3]['rand']);
 291     $this->assertGreaterThanOrEqual($result[3]['rand'], $result[4]['rand']);
 292     $this->assertGreaterThanOrEqual($result[4]['rand'], $result[5]['rand']);
 293   }
 294
 295   public function testDateInWhereClause() {
 296     $lastName = uniqid(__FUNCTION__);
 297     $sampleData = [
 298       ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => '2009-11-11'],
 299       ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => '2009-01-01'],
 300       ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => '2010-01-01'],
 301     ];
 302     Contact::save(FALSE)
 303       ->setRecords($sampleData)
 304       ->execute();
 305
 306     // Should work with isExpression=FALSE
 307     $result = Contact::get(FALSE)
 308       ->addWhere('last_name', '=', $lastName)
 309       ->addWhere('YEAR(birth_date)', '=', 2009)
 310       ->selectRowCount()
 311       ->execute();
 312     $this->assertCount(2, $result);
 313
 314     // Should work with isExpression=TRUE
 315     $result = Contact::get(FALSE)
 316       ->addWhere('last_name', '=', $lastName)
 317       ->addWhere('YEAR(birth_date)', '=', 2009, TRUE)
 318       ->selectRowCount()
 319       ->execute();
 320     $this->assertCount(2, $result);
 321
 322     // Try an expression in the value
 323     $result = Contact::get(FALSE)
 324       ->addWhere('last_name', '=', $lastName)
 325       ->addWhere('MONTH(birth_date)', '=', 'MONTH("2030-11-12")', TRUE)
 326       ->addSelect('birth_date')
 327       ->execute()->single();
 328     $this->assertEquals('2009-11-11', $result['birth_date']);
 329   }
 330
 331 }