From 1fe4682d6819231a2df81ac6bdcbd28eb96dcb5c Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Fri, 17 Sep 2021 15:37:30 -0400
Subject: [PATCH] APIv4 - add descriptions to sql functions

---
 CRM/Api4/Page/Api4Explorer.php                |  1 +
 Civi/Api4/Query/SqlFunction.php               |  5 +++++
 Civi/Api4/Query/SqlFunctionABS.php            |  9 ++++++++-
 Civi/Api4/Query/SqlFunctionAVG.php            |  7 +++++++
 Civi/Api4/Query/SqlFunctionBINARY.php         |  7 +++++++
 Civi/Api4/Query/SqlFunctionCOALESCE.php       |  7 +++++++
 Civi/Api4/Query/SqlFunctionCONCAT.php         |  9 ++++++++-
 Civi/Api4/Query/SqlFunctionCOUNT.php          |  7 +++++++
 Civi/Api4/Query/SqlFunctionCURDATE.php        |  7 +++++++
 Civi/Api4/Query/SqlFunctionDATE.php           |  9 ++++++++-
 Civi/Api4/Query/SqlFunctionGREATEST.php       |  7 +++++++
 Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php   |  7 +++++++
 Civi/Api4/Query/SqlFunctionIF.php             |  9 ++++++++-
 Civi/Api4/Query/SqlFunctionISNULL.php         |  7 +++++++
 Civi/Api4/Query/SqlFunctionLEAST.php          |  7 +++++++
 Civi/Api4/Query/SqlFunctionLOWER.php          |  7 +++++++
 Civi/Api4/Query/SqlFunctionMAX.php            |  7 +++++++
 Civi/Api4/Query/SqlFunctionMIN.php            |  7 +++++++
 Civi/Api4/Query/SqlFunctionNULLIF.php         |  9 ++++++++-
 Civi/Api4/Query/SqlFunctionRAND.php           |  9 ++++++++-
 Civi/Api4/Query/SqlFunctionREPLACE.php        | 11 +++++++++--
 Civi/Api4/Query/SqlFunctionROUND.php          | 15 ++++++++++-----
 Civi/Api4/Query/SqlFunctionSUM.php            |  7 +++++++
 Civi/Api4/Query/SqlFunctionTIME.php           |  9 ++++++++-
 Civi/Api4/Query/SqlFunctionUPPER.php          |  7 +++++++
 Civi/Api4/Query/SqlFunctionYEAR.php           |  7 +++++++
 ang/api4Explorer/Explorer.js                  | 19 +++++++++++--------
 .../crmSearchFunction.component.js            |  2 +-
 28 files changed, 198 insertions(+), 23 deletions(-)

diff --git a/CRM/Api4/Page/Api4Explorer.php b/CRM/Api4/Page/Api4Explorer.php
index 9cd4508fc5..a31e5feb62 100644
--- a/CRM/Api4/Page/Api4Explorer.php
+++ b/CRM/Api4/Page/Api4Explorer.php
@@ -59,6 +59,7 @@ class CRM_Api4_Page_Api4Explorer extends CRM_Core_Page {
           $fns[] = [
             'name' => $className::getName(),
             'title' => $className::getTitle(),
+            'description' => $className::getDescription(),
             'params' => $className::getParams(),
             'category' => $className::getCategory(),
             'dataType' => $className::getDataType(),
diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php
index 915962aa82..521a67b3bc 100644
--- a/Civi/Api4/Query/SqlFunction.php
+++ b/Civi/Api4/Query/SqlFunction.php
@@ -283,4 +283,9 @@ abstract class SqlFunction extends SqlExpression {
    */
   abstract public static function getTitle(): string;
 
+  /**
+   * @return string
+   */
+  abstract public static function getDescription(): string;
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionABS.php b/Civi/Api4/Query/SqlFunctionABS.php
index 015906c4de..7e8b6f1aab 100644
--- a/Civi/Api4/Query/SqlFunctionABS.php
+++ b/Civi/Api4/Query/SqlFunctionABS.php
@@ -31,7 +31,14 @@ class SqlFunctionABS extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Absolute');
+    return ts('Absolute value');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The positive value of a number.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionAVG.php b/Civi/Api4/Query/SqlFunctionAVG.php
index 59e6548a73..44ff3b0720 100644
--- a/Civi/Api4/Query/SqlFunctionAVG.php
+++ b/Civi/Api4/Query/SqlFunctionAVG.php
@@ -34,4 +34,11 @@ class SqlFunctionAVG extends SqlFunction {
     return ts('Average');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The mean of all values in the grouping.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionBINARY.php b/Civi/Api4/Query/SqlFunctionBINARY.php
index e2500d83db..cce4b8ea50 100644
--- a/Civi/Api4/Query/SqlFunctionBINARY.php
+++ b/Civi/Api4/Query/SqlFunctionBINARY.php
@@ -34,4 +34,11 @@ class SqlFunctionBINARY extends SqlFunction {
     return ts('Binary');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Case-sensitive string treatment.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionCOALESCE.php b/Civi/Api4/Query/SqlFunctionCOALESCE.php
index 62622be45f..fc5765f6df 100644
--- a/Civi/Api4/Query/SqlFunctionCOALESCE.php
+++ b/Civi/Api4/Query/SqlFunctionCOALESCE.php
@@ -36,4 +36,11 @@ class SqlFunctionCOALESCE extends SqlFunction {
     return ts('Coalesce');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The first value that is not null.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionCONCAT.php b/Civi/Api4/Query/SqlFunctionCONCAT.php
index a1c201fec2..4d6437eb4a 100644
--- a/Civi/Api4/Query/SqlFunctionCONCAT.php
+++ b/Civi/Api4/Query/SqlFunctionCONCAT.php
@@ -34,7 +34,14 @@ class SqlFunctionCONCAT extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Combine');
+    return ts('Combine text');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Multiple values concatenated into a single string.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionCOUNT.php b/Civi/Api4/Query/SqlFunctionCOUNT.php
index c51e024856..e45c9ff67d 100644
--- a/Civi/Api4/Query/SqlFunctionCOUNT.php
+++ b/Civi/Api4/Query/SqlFunctionCOUNT.php
@@ -38,4 +38,11 @@ class SqlFunctionCOUNT extends SqlFunction {
     return ts('Count');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The number of items in the grouping.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionCURDATE.php b/Civi/Api4/Query/SqlFunctionCURDATE.php
index f81993b536..951e4498c5 100644
--- a/Civi/Api4/Query/SqlFunctionCURDATE.php
+++ b/Civi/Api4/Query/SqlFunctionCURDATE.php
@@ -29,4 +29,11 @@ class SqlFunctionCURDATE extends SqlFunction {
     return ts('Now');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The current date.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionDATE.php b/Civi/Api4/Query/SqlFunctionDATE.php
index 05362a5103..0fe0d136b6 100644
--- a/Civi/Api4/Query/SqlFunctionDATE.php
+++ b/Civi/Api4/Query/SqlFunctionDATE.php
@@ -33,7 +33,14 @@ class SqlFunctionDATE extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Date Only');
+    return ts('Date only');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Only the date portion of a date/time.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionGREATEST.php b/Civi/Api4/Query/SqlFunctionGREATEST.php
index 549e59a3de..3874ce4aec 100644
--- a/Civi/Api4/Query/SqlFunctionGREATEST.php
+++ b/Civi/Api4/Query/SqlFunctionGREATEST.php
@@ -36,4 +36,11 @@ class SqlFunctionGREATEST extends SqlFunction {
     return ts('Greatest');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The largest of all provided values.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php b/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php
index 4745cbabea..1b67dfb908 100644
--- a/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php
+++ b/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php
@@ -80,4 +80,11 @@ class SqlFunctionGROUP_CONCAT extends SqlFunction {
     return ts('List');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('All values in the grouping.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionIF.php b/Civi/Api4/Query/SqlFunctionIF.php
index 1b120d4fed..66916a46f7 100644
--- a/Civi/Api4/Query/SqlFunctionIF.php
+++ b/Civi/Api4/Query/SqlFunctionIF.php
@@ -34,7 +34,14 @@ class SqlFunctionIF extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('If');
+    return ts('If/Else');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('If the field is empty, the first value, otherwise the second.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionISNULL.php b/Civi/Api4/Query/SqlFunctionISNULL.php
index beeaebc77d..17d729d430 100644
--- a/Civi/Api4/Query/SqlFunctionISNULL.php
+++ b/Civi/Api4/Query/SqlFunctionISNULL.php
@@ -35,4 +35,11 @@ class SqlFunctionISNULL extends SqlFunction {
     return ts('Is null');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('TRUE if the value is NULL, otherwise FALSE.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionLEAST.php b/Civi/Api4/Query/SqlFunctionLEAST.php
index 6317647241..bbf903819e 100644
--- a/Civi/Api4/Query/SqlFunctionLEAST.php
+++ b/Civi/Api4/Query/SqlFunctionLEAST.php
@@ -36,4 +36,11 @@ class SqlFunctionLEAST extends SqlFunction {
     return ts('Least');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The smallest of all provided values.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionLOWER.php b/Civi/Api4/Query/SqlFunctionLOWER.php
index d1a77b6b5f..23d535e352 100644
--- a/Civi/Api4/Query/SqlFunctionLOWER.php
+++ b/Civi/Api4/Query/SqlFunctionLOWER.php
@@ -36,4 +36,11 @@ class SqlFunctionLOWER extends SqlFunction {
     return ts('Lowercase');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Lowercase version of text.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionMAX.php b/Civi/Api4/Query/SqlFunctionMAX.php
index 6e3a880bf4..24ad68af4a 100644
--- a/Civi/Api4/Query/SqlFunctionMAX.php
+++ b/Civi/Api4/Query/SqlFunctionMAX.php
@@ -36,4 +36,11 @@ class SqlFunctionMAX extends SqlFunction {
     return ts('Max');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The largest value in the grouping.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionMIN.php b/Civi/Api4/Query/SqlFunctionMIN.php
index 3c6a79227c..0883ee4c8f 100644
--- a/Civi/Api4/Query/SqlFunctionMIN.php
+++ b/Civi/Api4/Query/SqlFunctionMIN.php
@@ -36,4 +36,11 @@ class SqlFunctionMIN extends SqlFunction {
     return ts('Min');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The smallest value in the grouping.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionNULLIF.php b/Civi/Api4/Query/SqlFunctionNULLIF.php
index 79e117a7fd..6cb6b2fb78 100644
--- a/Civi/Api4/Query/SqlFunctionNULLIF.php
+++ b/Civi/Api4/Query/SqlFunctionNULLIF.php
@@ -34,7 +34,14 @@ class SqlFunctionNULLIF extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Null if');
+    return ts('Unequal');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The first value, only if it is not equal to the second.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionRAND.php b/Civi/Api4/Query/SqlFunctionRAND.php
index 1cb0cfeeb9..7e834ea55e 100644
--- a/Civi/Api4/Query/SqlFunctionRAND.php
+++ b/Civi/Api4/Query/SqlFunctionRAND.php
@@ -26,7 +26,14 @@ class SqlFunctionRAND extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Random Number');
+    return ts('Random number');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Generates a random number between 0 and 1.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionREPLACE.php b/Civi/Api4/Query/SqlFunctionREPLACE.php
index 27c7c1b51c..916678db0f 100644
--- a/Civi/Api4/Query/SqlFunctionREPLACE.php
+++ b/Civi/Api4/Query/SqlFunctionREPLACE.php
@@ -24,7 +24,7 @@ class SqlFunctionREPLACE extends SqlFunction {
         'min_expr' => 3,
         'max_expr' => 3,
         'optional' => FALSE,
-        'must_be' => ['SqlField', 'SqlString'],
+        'must_be' => ['SqlString', 'SqlField'],
       ],
     ];
   }
@@ -33,7 +33,14 @@ class SqlFunctionREPLACE extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Replace');
+    return ts('Replace text');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Substitutes one value for another in the text.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionROUND.php b/Civi/Api4/Query/SqlFunctionROUND.php
index 5a57c584b1..285f43c621 100644
--- a/Civi/Api4/Query/SqlFunctionROUND.php
+++ b/Civi/Api4/Query/SqlFunctionROUND.php
@@ -22,11 +22,9 @@ class SqlFunctionROUND extends SqlFunction {
     return [
       [
         'optional' => FALSE,
-        'must_be' => ['SqlField', 'SqlNumber'],
-      ],
-      [
-        'optional' => TRUE,
-        'must_be' => ['SqlNumber'],
+        'min_expr' => 1,
+        'max_expr' => 2,
+        'must_be' => ['SqlNumber', 'SqlField'],
       ],
     ];
   }
@@ -38,4 +36,11 @@ class SqlFunctionROUND extends SqlFunction {
     return ts('Round');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Number rounded to specified number of decimal places.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionSUM.php b/Civi/Api4/Query/SqlFunctionSUM.php
index 28522692aa..71c56611b8 100644
--- a/Civi/Api4/Query/SqlFunctionSUM.php
+++ b/Civi/Api4/Query/SqlFunctionSUM.php
@@ -34,4 +34,11 @@ class SqlFunctionSUM extends SqlFunction {
     return ts('Sum');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('The sum of all values in the grouping.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionTIME.php b/Civi/Api4/Query/SqlFunctionTIME.php
index e55b48b867..0354877325 100644
--- a/Civi/Api4/Query/SqlFunctionTIME.php
+++ b/Civi/Api4/Query/SqlFunctionTIME.php
@@ -33,7 +33,14 @@ class SqlFunctionTIME extends SqlFunction {
    * @return string
    */
   public static function getTitle(): string {
-    return ts('Time Only');
+    return ts('Time only');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Only the time portaion of a date/time.');
   }
 
 }
diff --git a/Civi/Api4/Query/SqlFunctionUPPER.php b/Civi/Api4/Query/SqlFunctionUPPER.php
index 57e3ae3243..51c08af908 100644
--- a/Civi/Api4/Query/SqlFunctionUPPER.php
+++ b/Civi/Api4/Query/SqlFunctionUPPER.php
@@ -36,4 +36,11 @@ class SqlFunctionUPPER extends SqlFunction {
     return ts('Uppercase');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Uppercase version of text.');
+  }
+
 }
diff --git a/Civi/Api4/Query/SqlFunctionYEAR.php b/Civi/Api4/Query/SqlFunctionYEAR.php
index 81b0ae52a5..f655000c43 100644
--- a/Civi/Api4/Query/SqlFunctionYEAR.php
+++ b/Civi/Api4/Query/SqlFunctionYEAR.php
@@ -36,4 +36,11 @@ class SqlFunctionYEAR extends SqlFunction {
     return ts('Year Only');
   }
 
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('Only the year of a date.');
+  }
+
 }
diff --git a/ang/api4Explorer/Explorer.js b/ang/api4Explorer/Explorer.js
index 2bdaa72b11..0d5404a8f4 100644
--- a/ang/api4Explorer/Explorer.js
+++ b/ang/api4Explorer/Explorer.js
@@ -456,8 +456,8 @@
             children: _.transform(CRM.vars.api4.functions, function(result, fn) {
               result.push({
                 id: fn.name + '() AS ' + fn.name.toLowerCase(),
-                text: fn.name + '()',
-                description: fn.name + '(' + describeSqlFn(fn.params) + ')'
+                description: fn.description,
+                text: fn.name + '(' + describeSqlFn(fn.params) + ')'
               });
             })
           };
@@ -607,16 +607,19 @@
       var desc = ' ';
       _.each(params, function(param) {
         desc += ' ';
-        if (param.prefix) {
-          desc += _.filter(param.prefix).join('|') + ' ';
+        if (param.name) {
+          desc += param.name + ' ';
         }
-        if (param.expr === 1) {
+        if (!_.isEmpty(param.flag_before)) {
+          desc += '[' + _.filter(param.name ? [param.name] : _.keys(param.flag_before)).join('|') + '] ';
+        }
+        if (param.max_expr === 1) {
           desc += 'expr ';
-        } else if (param.expr > 1) {
+        } else if (param.max_expr > 1) {
           desc += 'expr, ... ';
         }
-        if (param.suffix) {
-          desc += ' ' + _.filter(param.suffix).join('|') + ' ';
+        if (!_.isEmpty(param.flag_after)) {
+          desc += ' [' + _.filter(param.flag_after).join('|') + '] ';
         }
       });
       return desc.replace(/[ ]+/g, ' ');
diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js
index b1d31d5675..0aee848858 100644
--- a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js
+++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js
@@ -69,7 +69,7 @@
             });
             functions.push({
               text: allTypes[type],
-              children: formatForSelect2(allowedFunctions, 'name', 'title')
+              children: formatForSelect2(allowedFunctions, 'name', 'title', ['description'])
             });
           });
         }
-- 
2.25.1