From 13b73d76f5ca7381d1450990dcfad1c39a6e2509 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Tue, 29 Mar 2022 21:55:35 -0400 Subject: [PATCH] SearchKit - Add basic SearchSegment functionality for numeric range groups --- .../SearchSegmentExtraFieldProvider.php | 97 ++++++++++++++ .../Provider/SearchSegmentSpecProvider.php | 33 +++++ .../crmSearchAdmin.component.js | 2 +- .../crmSearchAdminDisplay.component.js | 2 +- ext/search_kit/search_kit.php | 11 ++ .../v4/SearchSegment/SearchSegmentTest.php | 122 ++++++++++++++++++ 6 files changed, 265 insertions(+), 2 deletions(-) create mode 100644 ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php create mode 100644 ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentSpecProvider.php create mode 100644 ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php diff --git a/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php b/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php new file mode 100644 index 0000000000..e2e18fdabb --- /dev/null +++ b/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php @@ -0,0 +1,97 @@ +getEntity()) as $fullName => $set) { + $field = new FieldSpec($fullName, $spec->getEntity()); + $field->setLabel($set['label']); + $field->setColumnName($set['field_name']); + $field->setOptions(array_column($set['items'], 'label')); + $field->setSuffixes(['label']); + $field->setSqlRenderer([__CLASS__, 'renderSql']); + $spec->addFieldSpec($field); + } + } + + /** + * @inheritDoc + */ + public function applies($entity, $action) { + return $entity !== 'SearchSegment' && $action === 'get'; + } + + /** + * @param string $entity + * @return array[] + */ + private static function getSets($entity) { + if (!isset(\Civi::$statics['all_search_segments'])) { + \Civi::$statics['all_search_segments'] = []; + try { + $searchSegments = SearchSegment::get(FALSE)->addOrderBy('label')->execute(); + } + // Suppress SearchSegment BAO/table not found error e.g. during upgrade mode + catch (\Exception $e) { + return []; + } + foreach ($searchSegments as $set) { + \Civi::$statics['all_search_segments'][$set['entity_name']]['segment_' . $set['name']] = $set; + } + } + return \Civi::$statics['all_search_segments'][$entity] ?? []; + } + + /** + * Generates the sql case statement with a clause for each item. + * + * @param array $field + * @return string + */ + public static function renderSql(array $field): string { + $set = self::getSets($field['entity'])[$field['name']]; + $sqlName = $field['sql_name']; + $cases = []; + foreach ($set['items'] as $index => $item) { + $conditions = []; + if (isset($item['min'])) { + $conditions[] = $sqlName . ' >= ' . (float) $item['min']; + } + if (isset($item['max'])) { + $conditions[] = $sqlName . ' < ' . (float) $item['max']; + } + // If no conditions, this is the ELSE clause + if (!$conditions) { + $elseClause = 'ELSE ' . (int) $index; + } + else { + $cases[] = 'WHEN ' . implode(' AND ', $conditions) . ' THEN ' . (int) $index; + } + } + // Place ELSE clause at the end + if (isset($elseClause)) { + $cases[] = $elseClause; + } + return 'CASE ' . implode("\n ", $cases) . "\nEND"; + } + +} diff --git a/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentSpecProvider.php b/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentSpecProvider.php new file mode 100644 index 0000000000..10fe1bc537 --- /dev/null +++ b/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentSpecProvider.php @@ -0,0 +1,33 @@ +getFieldByName('name')->setRequired(FALSE); + } + + /** + * @inheritDoc + */ + public function applies($entity, $action) { + return $entity === 'SearchSegment'; + } + +} diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js index bf6311ebbb..646f494a39 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js @@ -443,7 +443,7 @@ }; $scope.fieldsForGroupBy = function() { - return {results: ctrl.getAllFields('', ['Field', 'Custom'], function(key) { + return {results: ctrl.getAllFields('', ['Field', 'Custom', 'Extra'], function(key) { return _.contains(ctrl.savedSearch.api_params.groupBy, key); }) }; diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdminDisplay.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdminDisplay.component.js index 39f15f6325..5956e2b3b6 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdminDisplay.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdminDisplay.component.js @@ -312,7 +312,7 @@ text: ts('Columns'), children: ctrl.crmSearchAdmin.getSelectFields(disabledIf) } - ].concat(ctrl.crmSearchAdmin.getAllFields('', ['Field', 'Custom'], disabledIf)) + ].concat(ctrl.crmSearchAdmin.getAllFields('', ['Field', 'Custom', 'Extra'], disabledIf)) }; }; diff --git a/ext/search_kit/search_kit.php b/ext/search_kit/search_kit.php index b3163c327a..bf742583e3 100644 --- a/ext/search_kit/search_kit.php +++ b/ext/search_kit/search_kit.php @@ -92,3 +92,14 @@ function search_kit_civicrm_pre($op, $entity, $id, &$params) { ->execute(); } } + +/** + * Implements hook_civicrm_post(). + */ +function search_kit_civicrm_post($op, $entity, $id, $object) { + // Flush fieldSpec cache when saving a SearchSegment + if ($entity === 'SearchSegment') { + \Civi::$statics['all_search_segments'] = NULL; + \Civi::cache('metadata')->clear(); + } +} diff --git a/ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php b/ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php new file mode 100644 index 0000000000..dfc45233b6 --- /dev/null +++ b/ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php @@ -0,0 +1,122 @@ +installMe(__DIR__) + ->apply(); + } + + /** + * Test running a searchDisplay with a numeric range segment. + */ + public function testRangeSearchSegment() { + $cid = Contact::create(FALSE)->execute()->single()['id']; + + $sampleData = [ + ['total_amount' => 1.5], + ['total_amount' => 10], + ['total_amount' => 20], + ['total_amount' => 25], + ['total_amount' => 32], + ['total_amount' => 33], + ['total_amount' => 56], + ]; + Contribution::save(FALSE) + ->addDefault('contact_id', $cid) + ->addDefault('financial_type_id:name', 'Donation') + ->addDefault('receive_date', 'now') + ->setRecords($sampleData)->execute(); + + SearchSegment::create(FALSE) + ->addValue('label', 'Giving Tier') + ->addValue('entity_name', 'Contribution') + ->addValue('field_name', 'total_amount') + ->addValue('description', 'Tiers by donation amount') + ->addValue('items', [ + // Only a max means no minimum + [ + 'label' => 'Low ball', + 'max' => 10, + ], + [ + 'label' => 'Minor league', + 'min' => 10, + 'max' => 25, + ], + [ + 'label' => 'Major league', + 'min' => 25, + 'max' => 40, + ], + // No conditions makes this the ELSE clause + [ + 'label' => 'Heavy hitter', + ], + ]) + ->execute(); + + $getField = Contribution::getFields(FALSE) + ->addWhere('name', '=', 'segment_Giving_Tier') + ->setLoadOptions(TRUE) + ->execute()->single(); + + $this->assertEquals('Giving Tier', $getField['label']); + $this->assertEquals(['Low ball', 'Minor league', 'Major league', 'Heavy hitter'], $getField['options']); + + $params = [ + 'checkPermissions' => FALSE, + 'return' => 'page:1', + 'savedSearch' => [ + 'api_entity' => 'Contribution', + 'api_params' => [ + 'version' => 4, + 'select' => [ + 'segment_Giving_Tier:label', + 'AVG(total_amount) AS AVG_total_amount', + 'COUNT(total_amount) AS COUNT_total_amount', + ], + 'where' => [['contact_id', '=', $cid]], + 'groupBy' => [ + 'segment_Giving_Tier', + ], + 'join' => [], + 'having' => [], + ], + ], + ]; + + $result = civicrm_api4('SearchDisplay', 'run', $params); + $this->assertCount(4, $result); + + $this->assertEquals('Low ball', $result[0]['columns'][0]['val']); + $this->assertEquals(1.5, $result[0]['data']['AVG_total_amount']); + $this->assertEquals(1, $result[0]['data']['COUNT_total_amount']); + + $this->assertEquals('Minor league', $result[1]['columns'][0]['val']); + $this->assertEquals(15.0, $result[1]['data']['AVG_total_amount']); + $this->assertEquals(2, $result[1]['data']['COUNT_total_amount']); + + $this->assertEquals('Major league', $result[2]['columns'][0]['val']); + $this->assertEquals(30.0, $result[2]['data']['AVG_total_amount']); + $this->assertEquals(3, $result[2]['data']['COUNT_total_amount']); + + $this->assertEquals('Heavy hitter', $result[3]['columns'][0]['val']); + $this->assertEquals(56.0, $result[3]['data']['AVG_total_amount']); + $this->assertEquals(1, $result[3]['data']['COUNT_total_amount']); + } + +} -- 2.25.1