From ecd96898cef4c0bf964f40aff40a109660cfc747 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Thu, 12 May 2022 22:55:18 -0400 Subject: [PATCH] SearchKit - Add "Totals" footer row for sums, averages, and other aggregate statisics Fixes dev/core#3186 --- .../Civi/Api4/Action/SearchDisplay/Run.php | 30 +++++++++++++++++++ .../searchAdminDisplayTable.component.js | 27 ++++++++++++++++- .../displays/searchAdminDisplayTable.html | 18 +++++++++++ .../traits/searchDisplayBaseTrait.service.js | 4 +++ .../ang/crmSearchDisplayTable.ang.php | 3 ++ .../crmSearchDisplayTable.component.js | 19 ++++++++++++ .../crmSearchDisplayTable.html | 3 +- .../crmSearchDisplayTableBody.html | 2 +- .../crmSearchDisplayTableLoading.html | 2 +- .../crmSearchDisplayTally.html | 13 ++++++++ ext/search_kit/css/crmSearchDisplayTable.css | 14 +++++++++ ext/search_kit/css/crmSearchTasks.css | 9 ------ 12 files changed, 131 insertions(+), 13 deletions(-) create mode 100644 ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTally.html create mode 100644 ext/search_kit/css/crmSearchDisplayTable.css diff --git a/ext/search_kit/Civi/Api4/Action/SearchDisplay/Run.php b/ext/search_kit/Civi/Api4/Action/SearchDisplay/Run.php index fad6bc95a5..3d631321e6 100644 --- a/ext/search_kit/Civi/Api4/Action/SearchDisplay/Run.php +++ b/ext/search_kit/Civi/Api4/Action/SearchDisplay/Run.php @@ -2,6 +2,8 @@ namespace Civi\Api4\Action\SearchDisplay; +use Civi\API\Request; +use Civi\Api4\Query\Api4SelectQuery; use Civi\Api4\Utils\CoreUtil; /** @@ -53,6 +55,34 @@ class Run extends AbstractRunAction { unset($apiParams['orderBy'], $apiParams['limit']); break; + case 'tally': + $this->applyFilters(); + unset($apiParams['orderBy'], $apiParams['limit']); + $api = Request::create($entityName, 'get', $apiParams); + $query = new Api4SelectQuery($api); + $query->forceSelectId = FALSE; + $sql = $query->getSql(); + $select = []; + foreach ($settings['columns'] as $col) { + if (!empty($col['tally']['fn']) && !empty($col['key'])) { + $fn = \CRM_Core_DAO::escapeString($col['tally']['fn']); + $key = \CRM_Core_DAO::escapeString($col['key']); + $select[] = $fn . '(`' . $key . '`) `' . $key . '`'; + } + } + $query = 'SELECT ' . implode(', ', $select) . ' FROM (' . $sql . ') `api_query`'; + $dao = \CRM_Core_DAO::executeQuery($query); + $dao->fetch(); + $tally = []; + foreach ($settings['columns'] as $col) { + if (!empty($col['tally']['fn']) && !empty($col['key'])) { + $alias = str_replace('.', '_', $col['key']); + $tally[$col['key']] = $dao->$alias ?? NULL; + } + } + $result[] = $tally; + return; + default: if (($settings['pager'] ?? FALSE) !== FALSE && preg_match('/^page:\d+$/', $key)) { $page = explode(':', $key)[1]; diff --git a/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.component.js b/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.component.js index 11dfb6522f..d2e09fcd49 100644 --- a/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.component.js @@ -11,7 +11,7 @@ parent: '^crmSearchAdminDisplay' }, templateUrl: '~/crmSearchAdmin/displays/searchAdminDisplayTable.html', - controller: function($scope, searchMeta) { + controller: function($scope, searchMeta, formatForSelect2) { var ts = $scope.ts = CRM.ts('org.civicrm.search_kit'), ctrl = this; @@ -56,6 +56,31 @@ ctrl.parent.initColumns({label: true, sortable: true}); }; + this.toggleTally = function() { + if (ctrl.display.settings.tally) { + delete ctrl.display.settings.tally; + _.each(ctrl.display.settings.columns, function(col) { + delete col.tally; + }); + } else { + ctrl.display.settings.tally = {label: ts('Total')}; + _.each(ctrl.display.settings.columns, function(col) { + if (col.type === 'field') { + col.tally = { + fn: searchMeta.getDefaultAggregateFn(searchMeta.parseExpr(col.key)).fnName + }; + } + }); + } + }; + + this.getTallyFunctions = function() { + var allowedFunctions = _.filter(CRM.crmSearchAdmin.functions, function(fn) { + return fn.category === 'aggregate' && fn.params.length; + }); + return {results: formatForSelect2(allowedFunctions, 'name', 'title', ['description'])}; + }; + } }); diff --git a/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.html b/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.html index 3a8a214ff8..99971ebbb0 100644 --- a/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.html +++ b/ext/search_kit/ang/crmSearchAdmin/displays/searchAdminDisplayTable.html @@ -32,6 +32,18 @@ +
+
+ +
+
+ + +
+
@@ -63,6 +75,12 @@
+
+ + + + +
diff --git a/ext/search_kit/ang/crmSearchDisplay/traits/searchDisplayBaseTrait.service.js b/ext/search_kit/ang/crmSearchDisplay/traits/searchDisplayBaseTrait.service.js index bffde300df..21be429058 100644 --- a/ext/search_kit/ang/crmSearchDisplay/traits/searchDisplayBaseTrait.service.js +++ b/ext/search_kit/ang/crmSearchDisplay/traits/searchDisplayBaseTrait.service.js @@ -82,6 +82,10 @@ $scope.$watch('$ctrl.filters', onChangeFilters, true); }, + hasExtraFirstColumn: function() { + return this.settings.actions || this.settings.draggable || (this.settings.tally && this.settings.tally.label); + }, + getAfformFilters: function() { return _.pick(this.afFieldset ? this.afFieldset.getFieldData() : {}, function(val) { return val !== null && (_.includes(['boolean', 'number'], typeof val) || val.length); diff --git a/ext/search_kit/ang/crmSearchDisplayTable.ang.php b/ext/search_kit/ang/crmSearchDisplayTable.ang.php index c52b7f3e17..dbbcca4595 100644 --- a/ext/search_kit/ang/crmSearchDisplayTable.ang.php +++ b/ext/search_kit/ang/crmSearchDisplayTable.ang.php @@ -8,6 +8,9 @@ return [ 'partials' => [ 'ang/crmSearchDisplayTable', ], + 'css' => [ + 'css/crmSearchDisplayTable.css', + ], 'basePages' => ['civicrm/search', 'civicrm/admin/search'], 'requires' => ['crmSearchDisplay', 'crmUi', 'crmSearchTasks', 'ui.bootstrap', 'ui.sortable'], 'bundles' => ['bootstrap3'], diff --git a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.component.js b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.component.js index 76e500910e..b356bfffa8 100644 --- a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.component.js +++ b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.component.js @@ -20,6 +20,25 @@ ctrl = angular.extend(this, searchDisplayBaseTrait, searchDisplayTasksTrait, searchDisplaySortableTrait); this.$onInit = function() { + var tallyParams; + + if (ctrl.settings.tally) { + ctrl.onPreRun.push(function (apiParams) { + ctrl.tally = null; + tallyParams = _.cloneDeep(apiParams); + }); + + ctrl.onPostRun.push(function (results, status) { + ctrl.tally = null; + if (status === 'success' && tallyParams) { + tallyParams.return = 'tally'; + crmApi4('SearchDisplay', 'run', tallyParams).then(function (result) { + ctrl.tally = result[0]; + }); + } + }); + } + this.initializeDisplay($scope, $element); if (ctrl.settings.draggable) { diff --git a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.html b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.html index 0293a9294f..82e9e03cc4 100644 --- a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.html +++ b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTable.html @@ -6,7 +6,7 @@ - +
+ @@ -17,6 +17,7 @@
diff --git a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableBody.html b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableBody.html index 36aba9cd4e..617dc299be 100644 --- a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableBody.html +++ b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableBody.html @@ -1,5 +1,5 @@ - + diff --git a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableLoading.html b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableLoading.html index 35738b7edd..8054df17d4 100644 --- a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableLoading.html +++ b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTableLoading.html @@ -1,6 +1,6 @@ - + diff --git a/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTally.html b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTally.html new file mode 100644 index 0000000000..bd6809f191 --- /dev/null +++ b/ext/search_kit/ang/crmSearchDisplayTable/crmSearchDisplayTally.html @@ -0,0 +1,13 @@ + + + + {{:: $ctrl.settings.tally.label }} + + +
+
+ {{:: col.tally.label }} + {{ $ctrl.tally[col.key] }} +
+ + diff --git a/ext/search_kit/css/crmSearchDisplayTable.css b/ext/search_kit/css/crmSearchDisplayTable.css new file mode 100644 index 0000000000..d4541374e5 --- /dev/null +++ b/ext/search_kit/css/crmSearchDisplayTable.css @@ -0,0 +1,14 @@ +/* search kit table display styling */ + +#bootstrap-theme .crm-search-display-table > table.table > thead > tr > th.crm-search-result-select { + padding-left: 0; + padding-right: 0; + text-transform: none; + color: initial; + /* Don't allow button to be split on 2 lines */ + min-width: 86px; +} + +#bootstrap-theme .crm-search-display.crm-search-display-table tfoot > tr > td { + font-weight: bold; +} diff --git a/ext/search_kit/css/crmSearchTasks.css b/ext/search_kit/css/crmSearchTasks.css index 8a96d12a5f..bdb38fbc2d 100644 --- a/ext/search_kit/css/crmSearchTasks.css +++ b/ext/search_kit/css/crmSearchTasks.css @@ -4,15 +4,6 @@ border: 1px solid lightgrey; } -#bootstrap-theme .crm-search-display-table > table.table > thead > tr > th.crm-search-result-select { - padding-left: 0; - padding-right: 0; - text-transform: none; - color: initial; - /* Don't allow button to be split on 2 lines */ - min-width: 86px; -} - .crm-search-display.crm-search-display-table td > crm-search-display-editable, .crm-search-display.crm-search-display-table td > .crm-editable-enabled { display: block !important; -- 2.25.1