ext/search_kit/Civi/Search/Admin.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 namespace Civi\Search;
  13
  14 use Civi\Api4\Action\SearchDisplay\AbstractRunAction;
  15 use Civi\Api4\Tag;
  16 use CRM_Search_ExtensionUtil as E;
  17
  18 /**
  19  * Class Admin
  20  * @package Civi\Search
  21  */
  22 class Admin {
  23
  24   /**
  25    * @return array
  26    */
  27   public static function getAdminSettings():array {
  28     $schema = self::getSchema();
  29     $extensions = \CRM_Extension_System::singleton()->getMapper();
  30     return [
  31       'schema' => self::addImplicitFKFields($schema),
  32       'joins' => self::getJoins($schema),
  33       'pseudoFields' => AbstractRunAction::getPseudoFields(),
  34       'operators' => \CRM_Utils_Array::makeNonAssociative(self::getOperators()),
  35       'functions' => \CRM_Api4_Page_Api4Explorer::getSqlFunctions(),
  36       'displayTypes' => Display::getDisplayTypes(['id', 'name', 'label', 'description', 'icon']),
  37       'styles' => \CRM_Utils_Array::makeNonAssociative(self::getStyles()),
  38       'defaultPagerSize' => \Civi::settings()->get('default_pager_size'),
  39       'afformEnabled' => $extensions->isActiveModule('afform'),
  40       'afformAdminEnabled' => $extensions->isActiveModule('afform_admin'),
  41       'tags' => Tag::get()
  42         ->addSelect('id', 'name', 'color', 'is_selectable', 'description')
  43         ->addWhere('used_for', 'CONTAINS', 'civicrm_saved_search')
  44         ->execute(),
  45     ];
  46   }
  47
  48   /**
  49    * @return string[]
  50    */
  51   public static function getOperators():array {
  52     return [
  53       '=' => '=',
  54       '!=' => '≠',
  55       '>' => '>',
  56       '<' => '<',
  57       '>=' => '≥',
  58       '<=' => '≤',
  59       'CONTAINS' => E::ts('Contains'),
  60       'IN' => E::ts('Is One Of'),
  61       'NOT IN' => E::ts('Not One Of'),
  62       'LIKE' => E::ts('Is Like'),
  63       'REGEXP' => E::ts('Matches Regexp'),
  64       'NOT LIKE' => E::ts('Not Like'),
  65       'NOT REGEXP' => E::ts('Not Regexp'),
  66       'BETWEEN' => E::ts('Is Between'),
  67       'NOT BETWEEN' => E::ts('Not Between'),
  68       'IS EMPTY' => E::ts('Is Empty'),
  69       'IS NOT EMPTY' => E::ts('Not Empty'),
  70     ];
  71   }
  72
  73   /**
  74    * @return string[]
  75    */
  76   public static function getStyles():array {
  77     return [
  78       'default' => E::ts('Default'),
  79       'primary' => E::ts('Primary'),
  80       'secondary' => E::ts('Secondary'),
  81       'success' => E::ts('Success'),
  82       'info' => E::ts('Info'),
  83       'warning' => E::ts('Warning'),
  84       'danger' => E::ts('Danger'),
  85     ];
  86   }
  87
  88   /**
  89    * Fetch all entities the current user has permission to `get`
  90    * @return array
  91    */
  92   public static function getSchema() {
  93     $schema = [];
  94     $entities = \Civi\Api4\Entity::get()
  95       ->addSelect('name', 'title', 'title_plural', 'bridge_title', 'type', 'primary_key', 'description', 'label_field', 'icon', 'paths', 'dao', 'bridge', 'ui_join_filters', 'searchable')
  96       ->addWhere('searchable', '!=', 'none')
  97       ->addOrderBy('title_plural')
  98       ->setChain([
  99         'get' => ['$name', 'getActions', ['where' => [['name', '=', 'get']]], ['params']],
 100       ])->execute();
 101     foreach ($entities as $entity) {
 102       // Skip if entity doesn't have a 'get' action or the user doesn't have permission to use get
 103       if ($entity['get']) {
 104         // Add paths (but only RUD actions) with translated titles
 105         foreach ($entity['paths'] as $action => $path) {
 106           unset($entity['paths'][$action]);
 107           if (in_array($action, ['view', 'update', 'delete'], TRUE)) {
 108             $entity['paths'][] = [
 109               'path' => $path,
 110               'action' => $action,
 111             ];
 112           }
 113         }
 114         $getFields = civicrm_api4($entity['name'], 'getFields', [
 115           'select' => ['name', 'title', 'label', 'description', 'type', 'options', 'input_type', 'input_attrs', 'data_type', 'serialize', 'entity', 'fk_entity', 'readonly', 'operators'],
 116           'where' => [['name', 'NOT IN', ['api_key', 'hash']]],
 117           'orderBy' => ['label'],
 118         ]);
 119         foreach ($getFields as $field) {
 120           $field['fieldName'] = $field['name'];
 121           // Hack for RelationshipCache to make Relationship fields editable
 122           if ($entity['name'] === 'RelationshipCache') {
 123             $entity['primary_key'] = ['relationship_id'];
 124             if (in_array($field['name'], ['is_active', 'start_date', 'end_date'])) {
 125               $field['readonly'] = FALSE;
 126             }
 127           }
 128           $entity['fields'][] = $field;
 129         }
 130         $params = $entity['get'][0];
 131         // Entity must support at least these params or it is too weird for search kit
 132         if (!array_diff(['select', 'where', 'orderBy', 'limit', 'offset'], array_keys($params))) {
 133           \CRM_Utils_Array::remove($params, 'checkPermissions', 'debug', 'chain', 'language', 'select', 'where', 'orderBy', 'limit', 'offset');
 134           unset($entity['get']);
 135           $schema[$entity['name']] = ['params' => array_keys($params)] + array_filter($entity);
 136         }
 137       }
 138     }
 139     return $schema;
 140   }
 141
 142   /**
 143    * Add in FK fields for implicit joins
 144    * For example, add a `campaign_id.title` field to the Contribution entity
 145    * @param $schema
 146    * @return array
 147    */
 148   private static function addImplicitFKFields($schema) {
 149     foreach ($schema as &$entity) {
 150       if ($entity['searchable'] !== 'bridge') {
 151         foreach (array_reverse($entity['fields'], TRUE) as $index => $field) {
 152           if (!empty($field['fk_entity']) && !$field['options'] && !empty($schema[$field['fk_entity']]['label_field'])) {
 153             $isCustom = strpos($field['name'], '.');
 154             // Custom fields: append "Contact ID" to original field label
 155             if ($isCustom) {
 156               $entity['fields'][$index]['label'] .= ' ' . E::ts('Contact ID');
 157             }
 158             // DAO fields: use title instead of label since it represents the id (title usually ends in ID but label does not)
 159             else {
 160               $entity['fields'][$index]['label'] = $field['title'];
 161             }
 162             // Add the label field from the other entity to this entity's list of fields
 163             $newField = \CRM_Utils_Array::findAll($schema[$field['fk_entity']]['fields'], ['name' => $schema[$field['fk_entity']]['label_field']])[0];
 164             $newField['name'] = $field['name'] . '.' . $schema[$field['fk_entity']]['label_field'];
 165             $newField['label'] = $field['label'] . ' ' . $newField['label'];
 166             array_splice($entity['fields'], $index, 0, [$newField]);
 167           }
 168         }
 169       }
 170     }
 171     return array_values($schema);
 172   }
 173
 174   /**
 175    * @param array $allowedEntities
 176    * @return array
 177    */
 178   public static function getJoins(array $allowedEntities) {
 179     $joins = [];
 180     foreach ($allowedEntities as $entity) {
 181       // Multi-record custom field groups (to-date only the contact entity supports these)
 182       if (in_array('CustomValue', $entity['type'])) {
 183         $targetEntity = $allowedEntities['Contact'];
 184         // Join from Custom group to Contact (n-1)
 185         $alias = $entity['name'] . '_Contact_entity_id';
 186         $joins[$entity['name']][] = [
 187           'label' => $entity['title'] . ' ' . $targetEntity['title'],
 188           'description' => '',
 189           'entity' => 'Contact',
 190           'conditions' => self::getJoinConditions('entity_id', $alias . '.id'),
 191           'defaults' => self::getJoinDefaults($alias, $targetEntity),
 192           'alias' => $alias,
 193           'multi' => FALSE,
 194         ];
 195         // Join from Contact to Custom group (n-n)
 196         $alias = 'Contact_' . $entity['name'] . '_entity_id';
 197         $joins['Contact'][] = [
 198           'label' => $entity['title_plural'],
 199           'description' => '',
 200           'entity' => $entity['name'],
 201           'conditions' => self::getJoinConditions('id', $alias . '.entity_id'),
 202           'defaults' => self::getJoinDefaults($alias, $entity),
 203           'alias' => $alias,
 204           'multi' => TRUE,
 205         ];
 206       }
 207       // Non-custom DAO entities
 208       elseif (!empty($entity['dao'])) {
 209         /* @var \CRM_Core_DAO $daoClass */
 210         $daoClass = $entity['dao'];
 211         $references = $daoClass::getReferenceColumns();
 212         // Only the first bridge reference gets processed, so if it's dynamic we want to be sure it's first in the list
 213         usort($references, function($first, $second) {
 214           foreach ([-1 => $first, 1 => $second] as $weight => $reference) {
 215             if (is_a($reference, 'CRM_Core_Reference_Dynamic')) {
 216               return $weight;
 217             }
 218           }
 219           return 0;
 220         });
 221         $fields = array_column($entity['fields'], NULL, 'name');
 222         $bridge = in_array('EntityBridge', $entity['type']) ? $entity['name'] : NULL;
 223         $bridgeFields = array_keys($entity['bridge'] ?? []);
 224         foreach ($references as $reference) {
 225           $keyField = $fields[$reference->getReferenceKey()] ?? NULL;
 226           if (
 227             // Sanity check - keyField must exist
 228             !$keyField ||
 229             // Exclude any joins that are better represented by pseudoconstants
 230             is_a($reference, 'CRM_Core_Reference_OptionValue') || (!$bridge && !empty($keyField['options'])) ||
 231             // Limit bridge joins to just the first
 232             ($bridge && array_search($keyField['name'], $bridgeFields) !== 0) ||
 233             // Sanity check - table should match
 234             $daoClass::getTableName() !== $reference->getReferenceTable()
 235           ) {
 236             continue;
 237           }
 238           // Dynamic references use a column like "entity_table" (for normal joins this value will be null)
 239           $dynamicCol = $reference->getTypeColumn();
 240
 241           // For dynamic references getTargetEntities will return multiple targets; for normal joins this loop will only run once
 242           foreach ($reference->getTargetEntities() as $targetTable => $targetEntityName) {
 243             if (!isset($allowedEntities[$targetEntityName]) || $targetEntityName === $entity['name']) {
 244               continue;
 245             }
 246             $targetEntity = $allowedEntities[$targetEntityName];
 247             // Non-bridge joins directly between 2 entities
 248             if (!$bridge) {
 249               // Add the straight 1-1 join
 250               $alias = $entity['name'] . '_' . $targetEntityName . '_' . $keyField['name'];
 251               $joins[$entity['name']][] = [
 252                 'label' => $entity['title'] . ' ' . ($dynamicCol ? $targetEntity['title'] : $keyField['label']),
 253                 'description' => '',
 254                 'entity' => $targetEntityName,
 255                 'conditions' => self::getJoinConditions($keyField['name'], $alias . '.' . $reference->getTargetKey(), $targetTable, $dynamicCol),
 256                 'defaults' => self::getJoinDefaults($alias, $targetEntity),
 257                 'alias' => $alias,
 258                 'multi' => FALSE,
 259               ];
 260               // Flip the conditions & add the reverse (1-n) join
 261               $alias = $targetEntityName . '_' . $entity['name'] . '_' . $keyField['name'];
 262               $joins[$targetEntityName][] = [
 263                 'label' => $targetEntity['title'] . ' ' . $entity['title_plural'],
 264                 'description' => $dynamicCol ? '' : $keyField['label'],
 265                 'entity' => $entity['name'],
 266                 'conditions' => self::getJoinConditions($reference->getTargetKey(), $alias . '.' . $keyField['name'], $targetTable, $dynamicCol ? $alias . '.' . $dynamicCol : NULL),
 267                 'defaults' => self::getJoinDefaults($alias, $entity),
 268                 'alias' => $alias,
 269                 'multi' => TRUE,
 270               ];
 271             }
 272             // Bridge joins (sanity check - bridge must specify exactly 2 FK fields)
 273             elseif (count($entity['bridge']) === 2) {
 274               // Get the other entity being linked through this bridge
 275               $baseKey = array_search($reference->getReferenceKey(), $bridgeFields) ? $bridgeFields[0] : $bridgeFields[1];
 276               $baseEntity = $allowedEntities[$fields[$baseKey]['fk_entity']] ?? NULL;
 277               if (!$baseEntity) {
 278                 continue;
 279               }
 280               // Add joins for the two entities that connect through this bridge (n-n)
 281               $symmetric = $baseEntity['name'] === $targetEntityName;
 282               $targetsTitle = $symmetric ? $allowedEntities[$bridge]['title_plural'] : $targetEntity['title_plural'];
 283               $alias = $baseEntity['name'] . "_{$bridge}_" . $targetEntityName;
 284               $joins[$baseEntity['name']][] = [
 285                 'label' => $baseEntity['title'] . ' ' . $targetsTitle,
 286                 'description' => $entity['bridge'][$baseKey]['description'] ?? E::ts('Multiple %1 per %2', [1 => $targetsTitle, 2 => $baseEntity['title']]),
 287                 'entity' => $targetEntityName,
 288                 'conditions' => array_merge(
 289                   [$bridge],
 290                   self::getJoinConditions('id', $alias . '.' . $baseKey, NULL, NULL)
 291                 ),
 292                 'defaults' => self::getJoinDefaults($alias, $targetEntity, $entity),
 293                 'bridge' => $bridge,
 294                 'alias' => $alias,
 295                 'multi' => TRUE,
 296               ];
 297               if (!$symmetric) {
 298                 $alias = $targetEntityName . "_{$bridge}_" . $baseEntity['name'];
 299                 $joins[$targetEntityName][] = [
 300                   'label' => $targetEntity['title'] . ' ' . $baseEntity['title_plural'],
 301                   'description' => $entity['bridge'][$reference->getReferenceKey()]['description'] ?? E::ts('Multiple %1 per %2', [1 => $baseEntity['title_plural'], 2 => $targetEntity['title']]),
 302                   'entity' => $baseEntity['name'],
 303                   'conditions' => array_merge(
 304                     [$bridge],
 305                     self::getJoinConditions($reference->getTargetKey(), $alias . '.' . $keyField['name'], $targetTable, $dynamicCol ? $alias . '.' . $dynamicCol : NULL)
 306                   ),
 307                   'defaults' => self::getJoinDefaults($alias, $baseEntity, $entity),
 308                   'bridge' => $bridge,
 309                   'alias' => $alias,
 310                   'multi' => TRUE,
 311                 ];
 312               }
 313             }
 314           }
 315         }
 316       }
 317     }
 318     return $joins;
 319   }
 320
 321   /**
 322    * Boilerplate join clause
 323    *
 324    * @param string $nearCol
 325    * @param string $farCol
 326    * @param string $targetTable
 327    * @param string|null $dynamicCol
 328    * @return array[]
 329    */
 330   private static function getJoinConditions($nearCol, $farCol, $targetTable = NULL, $dynamicCol = NULL) {
 331     $conditions = [
 332       [
 333         $nearCol,
 334         '=',
 335         $farCol,
 336       ],
 337     ];
 338     if ($dynamicCol) {
 339       $conditions[] = [
 340         $dynamicCol,
 341         '=',
 342         "'$targetTable'",
 343       ];
 344     }
 345     return $conditions;
 346   }
 347
 348   /**
 349    * @param $alias
 350    * @param array ...$entities
 351    * @return array
 352    */
 353   private static function getJoinDefaults($alias, ...$entities):array {
 354     $conditions = [];
 355     foreach ($entities as $entity) {
 356       foreach ($entity['ui_join_filters'] ?? [] as $fieldName) {
 357         $field = civicrm_api4($entity['name'], 'getFields', [
 358           'select' => ['options'],
 359           'where' => [['name', '=', $fieldName]],
 360           'loadOptions' => ['name'],
 361         ])->first();
 362         $value = isset($field['options'][0]) ? json_encode($field['options'][0]['name']) : '';
 363         $conditions[] = [
 364           $alias . '.' . $fieldName . ($value ? ':name' : ''),
 365           '=',
 366           $value,
 367         ];
 368       }
 369     }
 370     return $conditions;
 371   }
 372
 373 }