From 22837cc5f4e6d7913bf6037525674131b4d92ea3 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Fri, 24 Sep 2021 12:01:07 -0400 Subject: [PATCH] SearchKit - Add UI for arithmetic equations --- ext/search_kit/Civi/Search/Admin.php | 41 ++++++++++++++++++- ext/search_kit/ang/crmSearchAdmin.module.js | 9 ++-- .../crmSearchFunction.component.js | 33 ++++++--------- .../ang/crmSearchAdmin/crmSearchFunction.html | 11 +++-- 4 files changed, 65 insertions(+), 29 deletions(-) diff --git a/ext/search_kit/Civi/Search/Admin.php b/ext/search_kit/Civi/Search/Admin.php index ab7104d79a..1e05fa1a57 100644 --- a/ext/search_kit/Civi/Search/Admin.php +++ b/ext/search_kit/Civi/Search/Admin.php @@ -12,6 +12,8 @@ namespace Civi\Search; use Civi\Api4\Action\SearchDisplay\AbstractRunAction; +use Civi\Api4\Query\SqlEquation; +use Civi\Api4\Query\SqlFunction; use Civi\Api4\Tag; use CRM_Search_ExtensionUtil as E; @@ -32,7 +34,7 @@ class Admin { 'joins' => self::getJoins($schema), 'pseudoFields' => AbstractRunAction::getPseudoFields(), 'operators' => \CRM_Utils_Array::makeNonAssociative(self::getOperators()), - 'functions' => \CRM_Api4_Page_Api4Explorer::getSqlFunctions(), + 'functions' => self::getSqlFunctions(), 'displayTypes' => Display::getDisplayTypes(['id', 'name', 'label', 'description', 'icon']), 'styles' => \CRM_Utils_Array::makeNonAssociative(self::getStyles()), 'defaultPagerSize' => \Civi::settings()->get('default_pager_size'), @@ -377,4 +379,41 @@ class Admin { return $conditions; } + private static function getSqlFunctions() { + $functions = \CRM_Api4_Page_Api4Explorer::getSqlFunctions(); + // Add faux function "e" for SqlEquations + $functions[] = [ + 'name' => 'e', + 'title' => ts('Arithmetic'), + 'description' => ts('Add, subtract, multiply, divide'), + 'category' => SqlFunction::CATEGORY_MATH, + 'dataType' => 'Number', + 'params' => [ + [ + 'label' => ts('Value'), + 'min_expr' => 1, + 'max_expr' => 1, + 'must_be' => ['SqlField', 'SqlNumber'], + ], + [ + 'label' => ts('Value'), + 'min_expr' => 1, + 'max_expr' => 99, + 'flag_before' => array_combine(SqlEquation::$arithmeticOperators, SqlEquation::$arithmeticOperators), + 'must_be' => ['SqlField', 'SqlNumber'], + ], + ], + ]; + // Filter out empty param properties (simplifies the javascript which treats empty arrays/objects as != null) + foreach ($functions as &$function) { + foreach ($function['params'] as $i => $param) { + $function['params'][$i] = array_filter($param); + } + } + usort($functions, function($a, $b) { + return $a['title'] <=> $b['title']; + }); + return $functions; + } + } diff --git a/ext/search_kit/ang/crmSearchAdmin.module.js b/ext/search_kit/ang/crmSearchAdmin.module.js index 10ebb5ac43..248e3f5dfc 100644 --- a/ext/search_kit/ang/crmSearchAdmin.module.js +++ b/ext/search_kit/ang/crmSearchAdmin.module.js @@ -149,7 +149,7 @@ function parseFnArgs(info, expr) { var fnName = expr.split('(')[0], argString = expr.substr(fnName.length + 1, expr.length - fnName.length - 2); - info.fn = _.find(CRM.crmSearchAdmin.functions, {name: fnName}); + info.fn = _.find(CRM.crmSearchAdmin.functions, {name: fnName || 'e'}); function getKeyword(whitelist) { var keyword; @@ -188,9 +188,9 @@ if (!argString.length || (param.name && !getKeyword(param.name))) { return false; } - flagBefore = getKeyword(_.keys(param.flag_before || {})); if (param.max_expr) { while (++exprCount <= param.max_expr && argString.length) { + flagBefore = getKeyword(_.keys(param.flag_before || {})); expr = getExpr(); if (expr) { expr.param = param.name || index; @@ -198,9 +198,10 @@ info.args.push(expr); } // Only continue if an expression was found and followed by a comma - if (!expr || !getKeyword([','])) { + if (!expr) { break; } + getKeyword([',']); } if (expr && !_.isEmpty(expr.flag_after)) { _.last(info.args).flag_after = getKeyword(_.keys(param.flag_after)); @@ -245,7 +246,7 @@ var splitAs = expr.split(' AS '), info = {fn: null, args: [], alias: _.last(splitAs)}, bracketPos = expr.indexOf('('); - if (bracketPos > 0) { + if (bracketPos >= 0) { parseFnArgs(info, splitAs[0]); } else { var arg = parseArg(splitAs[0]); diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js index 090ae17359..3fda35afe0 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js @@ -37,8 +37,10 @@ }; this.addArg = function(exprType) { + var param = ctrl.getParam(ctrl.args.length); ctrl.args.push({ type: ctrl.exprTypes[exprType].type, + flag_before: _.keys(param.flag_before)[0], value: exprType === 'SqlNumber' ? 0 : '' }); }; @@ -47,14 +49,6 @@ if (!ctrl.fn) { return; } - if (ctrl.fn && ctrl.fn.params[0] && !_.isEmpty(ctrl.fn.params[0].flag_before)) { - ctrl.modifierName = _.keys(ctrl.fn.params[0].flag_before)[0]; - ctrl.modifierLabel = ctrl.fn.params[0].flag_before[ctrl.modifierName]; - } - else { - ctrl.modifierName = null; - ctrl.modifier = null; - } // Push args to reach the minimum _.each(ctrl.fn.params, function(param, index) { while ( @@ -93,7 +87,7 @@ allowedTypes.push('aggregate'); } else { allowedTypes.push('comparison', 'string'); - if (_.includes(['Integer', 'Float', 'Date', 'Timestamp'], ctrl.fieldArg.field.data_type)) { + if (_.includes(['Integer', 'Float', 'Date', 'Timestamp', 'Money'], ctrl.fieldArg.field.data_type)) { allowedTypes.push('math'); } if (_.includes(['Date', 'Timestamp'], ctrl.fieldArg.field.data_type)) { @@ -102,10 +96,7 @@ } _.each(allowedTypes, function(type) { var allowedFunctions = _.filter(CRM.crmSearchAdmin.functions, function(fn) { - return fn.category === type && - fn.params.length && - fn.params[0].min_expr > 0 && - _.includes(fn.params[0].must_be, 'SqlField'); + return fn.category === type && fn.params.length; }); functions.push({ text: allTypes[type], @@ -124,6 +115,7 @@ this.selectFunction = function() { ctrl.fn = _.find(CRM.crmSearchAdmin.functions, {name: ctrl.fnName}); + delete ctrl.fieldArg.flag_before; ctrl.args = [ctrl.fieldArg]; if (ctrl.fn) { var exprType, pos = 0, @@ -142,11 +134,6 @@ ctrl.writeExpr(); }; - this.toggleModifier = function() { - ctrl.modifier = ctrl.modifier ? null : ctrl.modifierName; - ctrl.writeExpr(); - }; - this.changeArg = function(index) { var val = ctrl.args[index].value; // Delete empty value @@ -164,12 +151,16 @@ this.writeExpr = function() { if (ctrl.fnName) { - var args = _.transform(ctrl.args, function(args, arg) { + var args = _.transform(ctrl.args, function(args, arg, index) { if (arg.value) { - args.push(arg.type === 'string' ? JSON.stringify(arg.value) : arg.value); + var prefix = arg.flag_before ? (index ? ' ' : '') + arg.flag_before + ' ' : (index ? ', ' : ''); + args.push(prefix + (arg.type === 'string' ? JSON.stringify(arg.value) : arg.value)); } }); - ctrl.expr = ctrl.fnName + '(' + (ctrl.modifier ? ctrl.modifier + ' ' : '') + args.join(', ') + ') AS ' + makeAlias(); + // Replace fake function "e" + ctrl.expr = (ctrl.fnName === 'e' ? '' : ctrl.fnName) + '('; + ctrl.expr += args.join(''); + ctrl.expr += ') AS ' + makeAlias(); } else { ctrl.expr = ctrl.args[0].value; } diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html index 89bba89a0a..e540ebd658 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html @@ -1,11 +1,16 @@
-