From e7f6def66d71b46da12ea41927d17450b00e5aa6 Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Mon, 20 Apr 2020 09:18:04 -0400
Subject: [PATCH] APIv4 - Add more SQL functions

---
 CRM/Api4/Page/Api4Explorer.php              |  1 +
 Civi/Api4/Query/SqlFunction.php             | 20 ++++++++++
 Civi/Api4/Query/SqlFunctionABS.php          | 29 ++++++++++++++
 Civi/Api4/Query/SqlFunctionAVG.php          |  2 +
 Civi/Api4/Query/SqlFunctionCOALESCE.php     | 28 ++++++++++++++
 Civi/Api4/Query/SqlFunctionCONCAT.php       | 29 ++++++++++++++
 Civi/Api4/Query/SqlFunctionCOUNT.php        |  2 +
 Civi/Api4/Query/SqlFunctionCURDATE.php      | 21 ++++++++++
 Civi/Api4/Query/SqlFunctionGREATEST.php     | 28 ++++++++++++++
 Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php | 43 +++++++++++++++++++++
 Civi/Api4/Query/SqlFunctionISNULL.php       | 28 ++++++++++++++
 Civi/Api4/Query/SqlFunctionLEAST.php        | 28 ++++++++++++++
 Civi/Api4/Query/SqlFunctionLOWER.php        | 29 ++++++++++++++
 Civi/Api4/Query/SqlFunctionMAX.php          |  2 +
 Civi/Api4/Query/SqlFunctionMIN.php          |  2 +
 Civi/Api4/Query/SqlFunctionNULLIF.php       | 32 +++++++++++++++
 Civi/Api4/Query/SqlFunctionREPLACE.php      | 39 +++++++++++++++++++
 Civi/Api4/Query/SqlFunctionROUND.php        | 34 ++++++++++++++++
 Civi/Api4/Query/SqlFunctionSUM.php          |  2 +
 Civi/Api4/Query/SqlFunctionUPPER.php        | 29 ++++++++++++++
 20 files changed, 428 insertions(+)
 create mode 100644 Civi/Api4/Query/SqlFunctionABS.php
 create mode 100644 Civi/Api4/Query/SqlFunctionCOALESCE.php
 create mode 100644 Civi/Api4/Query/SqlFunctionCONCAT.php
 create mode 100644 Civi/Api4/Query/SqlFunctionCURDATE.php
 create mode 100644 Civi/Api4/Query/SqlFunctionGREATEST.php
 create mode 100644 Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php
 create mode 100644 Civi/Api4/Query/SqlFunctionISNULL.php
 create mode 100644 Civi/Api4/Query/SqlFunctionLEAST.php
 create mode 100644 Civi/Api4/Query/SqlFunctionLOWER.php
 create mode 100644 Civi/Api4/Query/SqlFunctionNULLIF.php
 create mode 100644 Civi/Api4/Query/SqlFunctionREPLACE.php
 create mode 100644 Civi/Api4/Query/SqlFunctionROUND.php
 create mode 100644 Civi/Api4/Query/SqlFunctionUPPER.php

diff --git a/CRM/Api4/Page/Api4Explorer.php b/CRM/Api4/Page/Api4Explorer.php
index c5280bb257..35897d051d 100644
--- a/CRM/Api4/Page/Api4Explorer.php
+++ b/CRM/Api4/Page/Api4Explorer.php
@@ -71,6 +71,7 @@ class CRM_Api4_Page_Api4Explorer extends CRM_Core_Page {
           $fns[] = [
             'name' => $className::getName(),
             'params' => $className::getParams(),
+            'category' => $className::getCategory(),
           ];
         }
       }
diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php
index 19753d91ac..dacf04c533 100644
--- a/Civi/Api4/Query/SqlFunction.php
+++ b/Civi/Api4/Query/SqlFunction.php
@@ -22,6 +22,19 @@ abstract class SqlFunction extends SqlExpression {
 
   protected $args = [];
 
+  /**
+   * Used for categorizing functions in the UI
+   *
+   * @var string
+   */
+  protected static $category;
+
+  const CATEGORY_AGGREGATE = 'aggregate',
+    CATEGORY_COMPARISON = 'comparison',
+    CATEGORY_DATE = 'date',
+    CATEGORY_MATH = 'math',
+    CATEGORY_STRING = 'string';
+
   /**
    * Parse the argument string into an array of function arguments
    */
@@ -186,4 +199,11 @@ abstract class SqlFunction extends SqlExpression {
     return $params;
   }
 
+  /**
+   * @return string
+   */
+  public static function getCategory(): string {
+    return static::$category;
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionABS.php b/Civi/Api4/Query/SqlFunctionABS.php
new file mode 100644
index 0000000000..f626e41f27
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionABS.php
@@ -0,0 +1,29 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionABS extends SqlFunction {
+
+  protected static $category = self::CATEGORY_MATH;
+
+  protected static $params = [
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlNumber'],
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionAVG.php b/Civi/Api4/Query/SqlFunctionAVG.php
index 9a064135e9..9d9fcdd539 100644
--- a/Civi/Api4/Query/SqlFunctionAVG.php
+++ b/Civi/Api4/Query/SqlFunctionAVG.php
@@ -16,6 +16,8 @@ namespace Civi\Api4\Query;
  */
 class SqlFunctionAVG extends SqlFunction {
 
+  protected static $category = self::CATEGORY_AGGREGATE;
+
   protected static $params = [
     [
       'prefix' => ['', 'DISTINCT', 'ALL'],
diff --git a/Civi/Api4/Query/SqlFunctionCOALESCE.php b/Civi/Api4/Query/SqlFunctionCOALESCE.php
new file mode 100644
index 0000000000..938eb02f03
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionCOALESCE.php
@@ -0,0 +1,28 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionCOALESCE extends SqlFunction {
+
+  protected static $category = self::CATEGORY_COMPARISON;
+
+  protected static $params = [
+    [
+      'expr' => 99,
+      'optional' => FALSE,
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionCONCAT.php b/Civi/Api4/Query/SqlFunctionCONCAT.php
new file mode 100644
index 0000000000..ff6b7b993b
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionCONCAT.php
@@ -0,0 +1,29 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionCONCAT extends SqlFunction {
+
+  protected static $category = self::CATEGORY_STRING;
+
+  protected static $params = [
+    [
+      'expr' => 99,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlString'],
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionCOUNT.php b/Civi/Api4/Query/SqlFunctionCOUNT.php
index d444675350..84bb2105fb 100644
--- a/Civi/Api4/Query/SqlFunctionCOUNT.php
+++ b/Civi/Api4/Query/SqlFunctionCOUNT.php
@@ -16,6 +16,8 @@ namespace Civi\Api4\Query;
  */
 class SqlFunctionCOUNT extends SqlFunction {
 
+  protected static $category = self::CATEGORY_AGGREGATE;
+
   protected static $params = [
     [
       'prefix' => ['', 'DISTINCT', 'ALL'],
diff --git a/Civi/Api4/Query/SqlFunctionCURDATE.php b/Civi/Api4/Query/SqlFunctionCURDATE.php
new file mode 100644
index 0000000000..db3cb84415
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionCURDATE.php
@@ -0,0 +1,21 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionCURDATE extends SqlFunction {
+
+  protected static $category = self::CATEGORY_DATE;
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionGREATEST.php b/Civi/Api4/Query/SqlFunctionGREATEST.php
new file mode 100644
index 0000000000..8b2dc40122
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionGREATEST.php
@@ -0,0 +1,28 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionGREATEST extends SqlFunction {
+
+  protected static $category = self::CATEGORY_COMPARISON;
+
+  protected static $params = [
+    [
+      'expr' => 99,
+      'optional' => FALSE,
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php b/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php
new file mode 100644
index 0000000000..43d548a679
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php
@@ -0,0 +1,43 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionGROUP_CONCAT extends SqlFunction {
+
+  protected static $category = self::CATEGORY_AGGREGATE;
+
+  protected static $params = [
+    [
+      'prefix' => ['', 'DISTINCT', 'ALL'],
+      'expr' => 1,
+      'must_be' => ['SqlField'],
+      'optional' => FALSE,
+    ],
+    [
+      'prefix' => ['ORDER BY'],
+      'expr' => 1,
+      'suffix' => ['', 'ASC', 'DESC'],
+      'must_be' => ['SqlField'],
+      'optional' => TRUE,
+    ],
+    [
+      'prefix' => ['SEPARATOR'],
+      'expr' => 1,
+      'must_be' => ['SqlString'],
+      'optional' => TRUE,
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionISNULL.php b/Civi/Api4/Query/SqlFunctionISNULL.php
new file mode 100644
index 0000000000..30ab3ddae8
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionISNULL.php
@@ -0,0 +1,28 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionISNULL extends SqlFunction {
+
+  protected static $category = self::CATEGORY_COMPARISON;
+
+  protected static $params = [
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionLEAST.php b/Civi/Api4/Query/SqlFunctionLEAST.php
new file mode 100644
index 0000000000..4c22415c12
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionLEAST.php
@@ -0,0 +1,28 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionLEAST extends SqlFunction {
+
+  protected static $category = self::CATEGORY_COMPARISON;
+
+  protected static $params = [
+    [
+      'expr' => 99,
+      'optional' => FALSE,
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionLOWER.php b/Civi/Api4/Query/SqlFunctionLOWER.php
new file mode 100644
index 0000000000..eefd0f943b
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionLOWER.php
@@ -0,0 +1,29 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionLOWER extends SqlFunction {
+
+  protected static $category = self::CATEGORY_STRING;
+
+  protected static $params = [
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlString'],
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionMAX.php b/Civi/Api4/Query/SqlFunctionMAX.php
index f80ebeea15..c40d5876aa 100644
--- a/Civi/Api4/Query/SqlFunctionMAX.php
+++ b/Civi/Api4/Query/SqlFunctionMAX.php
@@ -16,6 +16,8 @@ namespace Civi\Api4\Query;
  */
 class SqlFunctionMAX extends SqlFunction {
 
+  protected static $category = self::CATEGORY_AGGREGATE;
+
   protected static $params = [
     [
       'prefix' => ['', 'DISTINCT', 'ALL'],
diff --git a/Civi/Api4/Query/SqlFunctionMIN.php b/Civi/Api4/Query/SqlFunctionMIN.php
index 993a5b18eb..29c313452f 100644
--- a/Civi/Api4/Query/SqlFunctionMIN.php
+++ b/Civi/Api4/Query/SqlFunctionMIN.php
@@ -16,6 +16,8 @@ namespace Civi\Api4\Query;
  */
 class SqlFunctionMIN extends SqlFunction {
 
+  protected static $category = self::CATEGORY_AGGREGATE;
+
   protected static $params = [
     [
       'prefix' => ['', 'DISTINCT', 'ALL'],
diff --git a/Civi/Api4/Query/SqlFunctionNULLIF.php b/Civi/Api4/Query/SqlFunctionNULLIF.php
new file mode 100644
index 0000000000..53e74999ca
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionNULLIF.php
@@ -0,0 +1,32 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionNULLIF extends SqlFunction {
+
+  protected static $category = self::CATEGORY_COMPARISON;
+
+  protected static $params = [
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+    ],
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionREPLACE.php b/Civi/Api4/Query/SqlFunctionREPLACE.php
new file mode 100644
index 0000000000..36d5bcdfbc
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionREPLACE.php
@@ -0,0 +1,39 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionREPLACE extends SqlFunction {
+
+  protected static $category = self::CATEGORY_STRING;
+
+  protected static $params = [
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlString'],
+    ],
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlString'],
+    ],
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlString'],
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionROUND.php b/Civi/Api4/Query/SqlFunctionROUND.php
new file mode 100644
index 0000000000..29eb06c77d
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionROUND.php
@@ -0,0 +1,34 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionROUND extends SqlFunction {
+
+  protected static $category = self::CATEGORY_MATH;
+
+  protected static $params = [
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlNumber'],
+    ],
+    [
+      'expr' => 1,
+      'optional' => TRUE,
+      'must_be' => ['SqlNumber'],
+    ],
+  ];
+
+}
diff --git a/Civi/Api4/Query/SqlFunctionSUM.php b/Civi/Api4/Query/SqlFunctionSUM.php
index 36f4ebb3cc..44520c3f32 100644
--- a/Civi/Api4/Query/SqlFunctionSUM.php
+++ b/Civi/Api4/Query/SqlFunctionSUM.php
@@ -16,6 +16,8 @@ namespace Civi\Api4\Query;
  */
 class SqlFunctionSUM extends SqlFunction {
 
+  protected static $category = self::CATEGORY_AGGREGATE;
+
   protected static $params = [
     [
       'prefix' => ['', 'DISTINCT', 'ALL'],
diff --git a/Civi/Api4/Query/SqlFunctionUPPER.php b/Civi/Api4/Query/SqlFunctionUPPER.php
new file mode 100644
index 0000000000..c23430365c
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionUPPER.php
@@ -0,0 +1,29 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function
+ */
+class SqlFunctionUPPER extends SqlFunction {
+
+  protected static $category = self::CATEGORY_STRING;
+
+  protected static $params = [
+    [
+      'expr' => 1,
+      'optional' => FALSE,
+      'must_be' => ['SqlField', 'SqlString'],
+    ],
+  ];
+
+}
-- 
2.25.1