From daf464c14239ad59141ae24b6fef5423d627450f Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Sat, 17 Jul 2021 21:42:29 -0400 Subject: [PATCH] APIv4 - Fix and add tests for comparison SQL functions --- Civi/Api4/Query/SqlFunctionCOALESCE.php | 13 +++++ Civi/Api4/Query/SqlFunctionCOUNT.php | 2 +- Civi/Api4/Query/SqlFunctionIF.php | 48 +++++++++++++++++++ Civi/Api4/Query/SqlFunctionISNULL.php | 14 ++++++ Civi/Api4/Query/SqlFunctionNULLIF.php | 6 +-- .../phpunit/api/v4/Action/SqlFunctionTest.php | 45 +++++++++++++++++ 6 files changed, 122 insertions(+), 6 deletions(-) create mode 100644 Civi/Api4/Query/SqlFunctionIF.php diff --git a/Civi/Api4/Query/SqlFunctionCOALESCE.php b/Civi/Api4/Query/SqlFunctionCOALESCE.php index 584f4374ec..1173106e9c 100644 --- a/Civi/Api4/Query/SqlFunctionCOALESCE.php +++ b/Civi/Api4/Query/SqlFunctionCOALESCE.php @@ -32,4 +32,17 @@ class SqlFunctionCOALESCE extends SqlFunction { return ts('Coalesce'); } + /** + * Prevent reformatting + * + * @see \Civi\Api4\Utils\FormattingUtil::formatOutputValues + * @param string $value + * @param string $dataType + * @return string|array + */ + public function formatOutputValue($value, &$dataType) { + $dataType = NULL; + return $value; + } + } diff --git a/Civi/Api4/Query/SqlFunctionCOUNT.php b/Civi/Api4/Query/SqlFunctionCOUNT.php index 19ddd4ceba..7a9cda1721 100644 --- a/Civi/Api4/Query/SqlFunctionCOUNT.php +++ b/Civi/Api4/Query/SqlFunctionCOUNT.php @@ -28,7 +28,7 @@ class SqlFunctionCOUNT extends SqlFunction { ]; /** - * Reformat result as array if using default separator + * Reformat result as integer * * @see \Civi\Api4\Utils\FormattingUtil::formatOutputValues * @param string $value diff --git a/Civi/Api4/Query/SqlFunctionIF.php b/Civi/Api4/Query/SqlFunctionIF.php new file mode 100644 index 0000000000..39f688a163 --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionIF.php @@ -0,0 +1,48 @@ + 3, + 'optional' => FALSE, + ], + ]; + + /** + * @return string + */ + public static function getTitle(): string { + return ts('If'); + } + + /** + * Prevent formatting based on first field + * + * @see \Civi\Api4\Utils\FormattingUtil::formatOutputValues + * @param string $value + * @param string $dataType + * @return string|array + */ + public function formatOutputValue($value, &$dataType) { + $dataType = NULL; + return $value; + } + +} diff --git a/Civi/Api4/Query/SqlFunctionISNULL.php b/Civi/Api4/Query/SqlFunctionISNULL.php index 538ad32286..1568c1114a 100644 --- a/Civi/Api4/Query/SqlFunctionISNULL.php +++ b/Civi/Api4/Query/SqlFunctionISNULL.php @@ -32,4 +32,18 @@ class SqlFunctionISNULL extends SqlFunction { return ts('Is null'); } + /** + * Reformat result as boolean + * + * @see \Civi\Api4\Utils\FormattingUtil::formatOutputValues + * @param string $value + * @param string $dataType + * @return string|array + */ + public function formatOutputValue($value, &$dataType) { + // Value is always TRUE or FALSE + $dataType = 'Boolean'; + return $value; + } + } diff --git a/Civi/Api4/Query/SqlFunctionNULLIF.php b/Civi/Api4/Query/SqlFunctionNULLIF.php index 53ec601bcc..0286a46056 100644 --- a/Civi/Api4/Query/SqlFunctionNULLIF.php +++ b/Civi/Api4/Query/SqlFunctionNULLIF.php @@ -22,11 +22,7 @@ class SqlFunctionNULLIF extends SqlFunction { protected static $params = [ [ - 'expr' => 1, - 'optional' => FALSE, - ], - [ - 'expr' => 1, + 'expr' => 2, 'optional' => FALSE, ], ]; diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php index 585a796a1f..72560a1d07 100644 --- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php +++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php @@ -20,6 +20,7 @@ namespace api\v4\Action; use api\v4\UnitTestCase; +use Civi\Api4\Activity; use Civi\Api4\Contact; use Civi\Api4\Contribution; @@ -131,4 +132,48 @@ class SqlFunctionTest extends UnitTestCase { $this->assertEquals(1, $result[1]['count']); } + public function testComparisonFunctions() { + $cid = Contact::create(FALSE) + ->addValue('first_name', 'hello') + ->execute()->first()['id']; + $sampleData = [ + ['subject' => 'abc', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 123, 'location' => 'abc'], + ['subject' => 'xyz', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'location' => 'abc', 'is_deleted' => 1], + ['subject' => 'def', 'activity_type_id:name' => 'Meeting', 'source_contact_id' => $cid, 'duration' => 456, 'location' => 'abc'], + ]; + $aids = Activity::save(FALSE) + ->setRecords($sampleData) + ->execute()->column('id'); + + $result = Activity::get(FALSE) + ->addWhere('id', 'IN', $aids) + ->addSelect('IF(is_deleted, "Trash", "No Trash") AS trashed') + ->addSelect('NULLIF(subject, location) AS subject_is_location') + ->addSelect('NULLIF(duration, 456) AS duration_not_456') + ->addSelect('COALESCE(duration, location) AS duration_or_location') + ->addSelect('GREATEST(duration, 0200) AS duration_or_200') + ->addSelect('LEAST(duration, 300) AS 300_or_duration') + ->addSelect('ISNULL(duration) AS duration_isnull') + ->addOrderBy('id') + ->execute()->indexBy('id'); + + $this->assertCount(3, $result); + $this->assertEquals('No Trash', $result[$aids[0]]['trashed']); + $this->assertEquals('Trash', $result[$aids[1]]['trashed']); + $this->assertEquals('No Trash', $result[$aids[2]]['trashed']); + $this->assertEquals(NULL, $result[$aids[0]]['subject_is_location']); + $this->assertEquals('xyz', $result[$aids[1]]['subject_is_location']); + $this->assertEquals('def', $result[$aids[2]]['subject_is_location']); + $this->assertEquals(123, $result[$aids[0]]['duration_not_456']); + $this->assertEquals(NULL, $result[$aids[1]]['duration_not_456']); + $this->assertEquals(NULL, $result[$aids[2]]['duration_not_456']); + $this->assertEquals('123', $result[$aids[0]]['duration_or_location']); + $this->assertEquals('abc', $result[$aids[1]]['duration_or_location']); + $this->assertEquals(123, $result[$aids[0]]['300_or_duration']); + $this->assertEquals(300, $result[$aids[2]]['300_or_duration']); + $this->assertEquals(FALSE, $result[$aids[0]]['duration_isnull']); + $this->assertEquals(TRUE, $result[$aids[1]]['duration_isnull']); + $this->assertEquals(FALSE, $result[$aids[2]]['duration_isnull']); + } + } -- 2.25.1