From 1cb03463ca10df9f79d747c5d0ba5559ff991550 Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Sat, 24 Jul 2021 20:22:06 -0400
Subject: [PATCH] SearchKit - add support for non-aggregate functions

---
 .../ang/crmSearchAdmin/compose/criteria.html  | 13 +++--
 .../crmSearchAdmin.component.js               | 51 ++++++++-----------
 .../crmSearchFunction.component.js            | 46 ++++++++++++++---
 .../ang/crmSearchAdmin/crmSearchFunction.html |  2 +-
 ext/search_kit/css/crmSearchAdmin.css         |  4 ++
 5 files changed, 74 insertions(+), 42 deletions(-)

diff --git a/ext/search_kit/ang/crmSearchAdmin/compose/criteria.html b/ext/search_kit/ang/crmSearchAdmin/compose/criteria.html
index 8aeb118781..64b1694b26 100644
--- a/ext/search_kit/ang/crmSearchAdmin/compose/criteria.html
+++ b/ext/search_kit/ang/crmSearchAdmin/compose/criteria.html
@@ -35,11 +35,14 @@
                crm-ui-select="{placeholder: ts('Group By'), data: fieldsForGroupBy, dropdownCss: {width: '300px'}}"
                on-crm-ui-select="$ctrl.addParam('groupBy', selection)" >
       </div>
-      <fieldset id="crm-search-build-group-aggregate" ng-if="$ctrl.savedSearch.api_params.groupBy.length" class="crm-collapsible collapsed">
-        <legend class="collapsible-title">{{:: ts('Aggregate fields') }}</legend>
-        <div>
-          <fieldset ng-repeat="col in $ctrl.savedSearch.api_params.select" ng-if="$ctrl.canAggregate(col)">
-            <crm-search-function expr="$ctrl.savedSearch.api_params.select[$index]" cat="'aggregate'"></crm-search-function>
+      <fieldset id="crm-search-build-functions">
+        <legend ng-click="controls.showFunctions = !controls.showFunctions">
+          <i class="crm-i fa-caret-{{ !controls.showFunctions ? 'right' : 'down' }}"></i>
+          {{:: ts('Field Transformations') }}
+        </legend>
+        <div ng-if="!!controls.showFunctions">
+          <fieldset ng-repeat="col in $ctrl.savedSearch.api_params.select">
+            <crm-search-function expr="$ctrl.savedSearch.api_params.select[$index]"></crm-search-function>
           </fieldset>
         </div>
       </fieldset>
diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js
index 79ffe0ed4d..2472af053b 100644
--- a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js
+++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js
@@ -296,22 +296,31 @@
       };
 
       $scope.changeGroupBy = function(idx) {
+        // When clearing a selection
         if (!ctrl.savedSearch.api_params.groupBy[idx]) {
           ctrl.clearParam('groupBy', idx);
         }
-        // Remove aggregate functions when no grouping
-        if (!ctrl.savedSearch.api_params.groupBy.length) {
-          _.each(ctrl.savedSearch.api_params.select, function(col, pos) {
-            if (_.contains(col, '(')) {
-              var info = searchMeta.parseExpr(col);
-              if (info.fn.category === 'aggregate') {
-                ctrl.savedSearch.api_params.select[pos] = info.path + info.suffix;
-              }
-            }
-          });
-        }
+        reconcileAggregateColumns();
       };
 
+      function reconcileAggregateColumns() {
+        _.each(ctrl.savedSearch.api_params.select, function(col, pos) {
+          var info = searchMeta.parseExpr(col),
+            fieldExpr = info.path + info.suffix;
+          if (ctrl.canAggregate(col)) {
+            // Ensure all non-grouped columns are aggregated if using GROUP BY
+            if (!info.fn || info.fn.category !== 'aggregate') {
+              ctrl.savedSearch.api_params.select[pos] = ctrl.DEFAULT_AGGREGATE_FN + '(DISTINCT ' + fieldExpr + ') AS ' + ctrl.DEFAULT_AGGREGATE_FN + '_DISTINCT_' + fieldExpr.replace(/[.:]/g, '_');
+            }
+          } else {
+            // Remove aggregate functions when no grouping
+            if (info.fn && info.fn.category === 'aggregate') {
+              ctrl.savedSearch.api_params.select[pos] = fieldExpr;
+            }
+          }
+        });
+      }
+
       function clauseUsesJoin(clause, alias) {
         if (clause[0].indexOf(alias + '.') === 0) {
           return true;
@@ -407,12 +416,8 @@
       this.addParam = function(name, value) {
         if (value && !_.contains(ctrl.savedSearch.api_params[name], value)) {
           ctrl.savedSearch.api_params[name].push(value);
-          if (name === 'groupBy') {
-            // Expand the aggregate block
-            $timeout(function() {
-              $('#crm-search-build-group-aggregate.collapsed .collapsible-title').click();
-            }, 10);
-          }
+          // This needs to be called when adding a field as well as changing groupBy
+          reconcileAggregateColumns();
         }
       };
 
@@ -431,17 +436,6 @@
         $('.crm-search-results', $element).css('height', '');
       }
 
-      // Ensure all non-grouped columns are aggregated if using GROUP BY
-      function aggregateGroupByColumns() {
-        if (ctrl.savedSearch.api_params.groupBy.length) {
-          _.each(ctrl.savedSearch.api_params.select, function(col, pos) {
-            if (!_.contains(col, '(') && ctrl.canAggregate(col)) {
-              ctrl.savedSearch.api_params.select[pos] = ctrl.DEFAULT_AGGREGATE_FN + '(DISTINCT ' + col + ') AS ' + ctrl.DEFAULT_AGGREGATE_FN + '_DISTINCT_' + col.replace(/[.:]/g, '_');
-            }
-          });
-        }
-      }
-
       // Debounced callback for loadResults
       function _loadResultsCallback() {
         // Multiply limit to read 2 pages at once & save ajax requests
@@ -522,7 +516,6 @@
 
       function loadResults() {
         $scope.loading = true;
-        aggregateGroupByColumns();
         _loadResults();
       }
 
diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js
index 24f1f35f58..2ebd28e86d 100644
--- a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js
+++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js
@@ -3,23 +3,32 @@
 
   angular.module('crmSearchAdmin').component('crmSearchFunction', {
     bindings: {
-      expr: '=',
-      cat: '<'
+      expr: '='
+    },
+    require: {
+      crmSearchAdmin: '^crmSearchAdmin'
     },
     templateUrl: '~/crmSearchAdmin/crmSearchFunction.html',
     controller: function($scope, formatForSelect2, searchMeta) {
       var ts = $scope.ts = CRM.ts('org.civicrm.search_kit'),
         ctrl = this;
 
-      this.$onInit = function() {
-        ctrl.functions = formatForSelect2(_.where(CRM.crmSearchAdmin.functions, {category: ctrl.cat}), 'name', 'title');
-        var fieldInfo = searchMeta.parseExpr(ctrl.expr);
+      var allTypes = {
+        aggregate: ts('Aggregate'),
+        comparison: ts('Comparison'),
+        date: ts('Date'),
+        math: ts('Math'),
+        string: ts('Text')
+      };
+
+      $scope.$watch('$ctrl.expr', function(expr) {
+        var fieldInfo = searchMeta.parseExpr(expr);
         ctrl.path = fieldInfo.path + fieldInfo.suffix;
         ctrl.field = fieldInfo.field;
         ctrl.fn = !fieldInfo.fn ? '' : fieldInfo.fn.name;
         ctrl.modifier = fieldInfo.modifier || null;
         initFunction();
-      };
+      });
 
       function initFunction() {
         ctrl.fnInfo = _.find(CRM.crmSearchAdmin.functions, {name: ctrl.fn});
@@ -33,8 +42,31 @@
         }
       }
 
+      this.getFunctions = function() {
+        var allowedTypes = [], functions = [];
+        if (ctrl.expr && ctrl.field) {
+          if (ctrl.crmSearchAdmin.canAggregate(ctrl.expr)) {
+            allowedTypes.push('aggregate');
+          } else {
+            allowedTypes.push('comparison', 'string');
+            if (_.includes(['Integer', 'Float', 'Date', 'Timestamp'], ctrl.field.data_type)) {
+              allowedTypes.push('math');
+            }
+            if (_.includes(['Date', 'Timestamp'], ctrl.field.data_type)) {
+              allowedTypes.push('date');
+            }
+          }
+          _.each(allowedTypes, function (type) {
+            functions.push({
+              text: allTypes[type],
+              children: formatForSelect2(_.where(CRM.crmSearchAdmin.functions, {category: type}), 'name', 'title')
+            });
+          });
+        }
+        return {results: functions};
+      };
+
       this.selectFunction = function() {
-        initFunction();
         ctrl.writeExpr();
       };
 
diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html
index d8a380e780..2274f262d9 100644
--- a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html
+++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html
@@ -1,6 +1,6 @@
 <div class="form-inline">
   <label>{{ $ctrl.field.label }}:</label>
-  <input class="form-control" style="width: 15em;" ng-model="$ctrl.fn" crm-ui-select="{data: $ctrl.functions, placeholder: ts('Select')}" ng-change="$ctrl.selectFunction()">
+  <input class="form-control" style="width: 15em;" ng-model="$ctrl.fn" crm-ui-select="{data: $ctrl.getFunctions, placeholder: ts('Select')}" ng-change="$ctrl.selectFunction()">
   <label ng-if="$ctrl.modifierName">
     <input type="checkbox" ng-checked="!!$ctrl.modifier" ng-click="$ctrl.toggleModifier()">
     {{ $ctrl.modifierLabel }}
diff --git a/ext/search_kit/css/crmSearchAdmin.css b/ext/search_kit/css/crmSearchAdmin.css
index 6e6749c358..eceab7fcf6 100644
--- a/ext/search_kit/css/crmSearchAdmin.css
+++ b/ext/search_kit/css/crmSearchAdmin.css
@@ -112,6 +112,10 @@
   min-height: 3.5em;
 }
 
+#bootstrap-theme.crm-search legend[ng-click] {
+  cursor: pointer;
+}
+
 #bootstrap-theme.crm-search .api4-input-group {
   display: inline-block;
 }
-- 
2.25.1