CRM/Contact/BAO/GroupContactCache.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 use Civi\API\Request;
  13 use Civi\Api4\Group;
  14 use Civi\Api4\Query\Api4SelectQuery;
  15 use Civi\Api4\Query\SqlExpression;
  16
  17 /**
  18  *
  19  * @package CRM
  20  * @copyright CiviCRM LLC https://civicrm.org/licensing
  21  */
  22 class CRM_Contact_BAO_GroupContactCache extends CRM_Contact_DAO_GroupContactCache {
  23
  24   /**
  25    * Get a list of caching modes.
  26    *
  27    * @return array
  28    */
  29   public static function getModes(): array {
  30     return [
  31       // Flush expired caches in response to user actions.
  32       'opportunistic' => ts('Opportunistic Flush'),
  33       // Flush expired caches via background cron jobs.
  34       'deterministic' => ts('Cron Flush'),
  35     ];
  36   }
  37
  38   /**
  39    * Check to see if we have cache entries for this group.
  40    *
  41    * If not, regenerate, else return.
  42    *
  43    * @param array $groupIDs
  44    *   Of group that we are checking against.
  45    *
  46    * @return bool
  47    *   TRUE if we did not regenerate, FALSE if we did
  48    */
  49   public static function check($groupIDs): bool {
  50     if (empty($groupIDs)) {
  51       return TRUE;
  52     }
  53
  54     return self::loadAll($groupIDs);
  55   }
  56
  57   /**
  58    * Formulate the query to see which groups needs to be refreshed.
  59    *
  60    * The calculation is based on their cache date and the smartGroupCacheTimeOut
  61    *
  62    * @param string $groupIDClause
  63    *   The clause which limits which groups we need to evaluate.
  64    * @param bool $includeHiddenGroups
  65    *   Hidden groups are excluded by default.
  66    *
  67    * @return string
  68    *   the sql query which lists the groups that need to be refreshed
  69    */
  70   protected static function groupRefreshedClause($groupIDClause = NULL, $includeHiddenGroups = FALSE): string {
  71     $smartGroupCacheTimeoutDateTime = self::getCacheInvalidDateTime();
  72
  73     $query = "
  74 SELECT  g.id
  75 FROM    civicrm_group g
  76 WHERE   ( g.saved_search_id IS NOT NULL OR g.children IS NOT NULL )
  77 AND     g.is_active = 1
  78 AND (
  79   g.cache_date IS NULL
  80   OR cache_date <= $smartGroupCacheTimeoutDateTime
  81 )";
  82
  83     if (!$includeHiddenGroups) {
  84       $query .= "AND (g.is_hidden = 0 OR g.is_hidden IS NULL)";
  85     }
  86
  87     if (!empty($groupIDClause)) {
  88       $query .= " AND ( $groupIDClause ) ";
  89     }
  90
  91     return $query;
  92   }
  93
  94   /**
  95    * Check to see if a group has been refreshed recently.
  96    *
  97    * This is primarily used in a locking scenario when some other process might have refreshed things underneath
  98    * this process
  99    *
 100    * @param int $groupID
 101    *   The group ID.
 102    *
 103    * @return bool
 104    */
 105   public static function shouldGroupBeRefreshed($groupID): bool {
 106     $query = self::groupRefreshedClause('g.id = %1');
 107     $params = [1 => [$groupID, 'Integer']];
 108
 109     // if the query returns the group ID, it means the group is a valid candidate for refreshing
 110     return (bool) CRM_Core_DAO::singleValueQuery($query, $params);
 111   }
 112
 113   /**
 114    * Check to see if we have cache entries for this group.
 115    *
 116    * if not, regenerate, else return
 117    *
 118    * @param array|null $groupIDs groupIDs of group that we are checking against
 119    *                           if empty, all groups are checked
 120    * @param int $limit
 121    *   Limits the number of groups we evaluate.
 122    *
 123    * @return bool
 124    *   TRUE if we did not regenerate, FALSE if we did
 125    */
 126   public static function loadAll($groupIDs = NULL, $limit = 0) {
 127     if ($groupIDs) {
 128       // Passing a single value is deprecated.
 129       $groupIDs = (array) $groupIDs;
 130     }
 131
 132     $processGroupIDs = self::getGroupsNeedingRefreshing($groupIDs, $limit);
 133
 134     if (!empty($processGroupIDs)) {
 135       self::add($processGroupIDs);
 136     }
 137     return TRUE;
 138   }
 139
 140   /**
 141    * Build the smart group cache for given groups.
 142    *
 143    * @param array $groupIDs
 144    */
 145   public static function add($groupIDs) {
 146     $groupIDs = (array) $groupIDs;
 147
 148     foreach ($groupIDs as $groupID) {
 149       // first delete the current cache
 150       $params = [['group', 'IN', [$groupID], 0, 0]];
 151       // the below call updates the cache table as a byproduct of the query
 152       CRM_Contact_BAO_Query::apiQuery($params, ['contact_id'], NULL, NULL, 0, 0, FALSE);
 153     }
 154   }
 155
 156   /**
 157    * Store values into the group contact cache.
 158    *
 159    * @todo review use of INSERT IGNORE. This function appears to be slower that inserting
 160    * with a left join. Also, 200 at once seems too little.
 161    *
 162    * @param array $groupID
 163    * @param array $values
 164    */
 165   protected static function store($groupID, &$values) {
 166     $processed = FALSE;
 167
 168     // sort the values so we put group IDs in front and hence optimize
 169     // mysql storage (or so we think) CRM-9493
 170     sort($values);
 171
 172     // to avoid long strings, lets do BULK_INSERT_COUNT values at a time
 173     while (!empty($values)) {
 174       $processed = TRUE;
 175       $input = array_splice($values, 0, CRM_Core_DAO::BULK_INSERT_COUNT);
 176       $str = implode(',', $input);
 177       $sql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES $str;";
 178       CRM_Core_DAO::executeQuery($sql);
 179     }
 180     self::updateCacheTime($groupID, $processed);
 181   }
 182
 183   /**
 184    * Change the cache_date.
 185    *
 186    * @param array $groupID
 187    * @param bool $processed
 188    *   Whether the cache data was recently modified.
 189    */
 190   protected static function updateCacheTime($groupID, $processed) {
 191     // only update cache entry if we had any values
 192     if ($processed) {
 193       // also update the group with cache date information
 194       $now = date('YmdHis');
 195     }
 196     else {
 197       $now = 'null';
 198     }
 199
 200     $groupIDs = implode(',', $groupID);
 201     $sql = "
 202 UPDATE civicrm_group
 203 SET    cache_date = $now
 204 WHERE  id IN ( $groupIDs )
 205 ";
 206     CRM_Core_DAO::executeQuery($sql);
 207   }
 208
 209   /**
 210    * Function to clear group contact cache and reset the corresponding
 211    *  group's cache and refresh date
 212    *
 213    * @param int $groupID
 214    *
 215    */
 216   protected static function clearGroupContactCache($groupID): void {
 217     $transaction = new CRM_Core_Transaction();
 218     $query = "
 219     DELETE  g
 220       FROM  civicrm_group_contact_cache g
 221       WHERE  g.group_id = %1 ";
 222
 223     $update = "
 224   UPDATE civicrm_group g
 225     SET    cache_date = null
 226     WHERE  id = %1 ";
 227
 228     $params = [
 229       1 => [$groupID, 'Integer'],
 230     ];
 231
 232     CRM_Core_DAO::executeQuery($query, $params);
 233     // also update the cache_date for these groups
 234     CRM_Core_DAO::executeQuery($update, $params);
 235
 236     $transaction->commit();
 237   }
 238
 239   /**
 240    * Refresh the smart group cache tables.
 241    *
 242    * This involves clearing out any aged entries (based on the site timeout setting) and resetting the time outs.
 243    *
 244    * This function should be called via the opportunistic or deterministic cache refresh function to make the intent
 245    * clear.
 246    */
 247   protected static function flushCaches() {
 248     try {
 249       $lock = self::getLockForRefresh();
 250     }
 251     catch (CRM_Core_Exception $e) {
 252       // Someone else is kindly doing the refresh for us right now.
 253       return;
 254     }
 255     $params = [1 => [self::getCacheInvalidDateTime(), 'String']];
 256     $groupsDAO = CRM_Core_DAO::executeQuery("SELECT id FROM civicrm_group WHERE cache_date <= %1", $params);
 257     $expiredGroups = [];
 258     while ($groupsDAO->fetch()) {
 259       $expiredGroups[] = $groupsDAO->id;
 260     }
 261     if (!empty($expiredGroups)) {
 262       $expiredGroups = implode(',', $expiredGroups);
 263       CRM_Core_DAO::executeQuery("DELETE FROM civicrm_group_contact_cache WHERE group_id IN ({$expiredGroups})");
 264
 265       // Clear these out without resetting them because we are not building caches here, only clearing them,
 266       // so the state is 'as if they had never been built'.
 267       CRM_Core_DAO::executeQuery("UPDATE civicrm_group SET cache_date = NULL WHERE id IN ({$expiredGroups})");
 268     }
 269     $lock->release();
 270   }
 271
 272   /**
 273    * Check if the refresh is already initiated.
 274    *
 275    * We have 2 imperfect methods for this:
 276    *   1) a static variable in the function. This works fine within a request
 277    *   2) a mysql lock. This works fine as long as CiviMail is not running, or if mysql is version 5.7+
 278    *
 279    * Where these 2 locks fail we get 2 processes running at the same time, but we have at least minimised that.
 280    *
 281    * @return \Civi\Core\Lock\LockInterface
 282    * @throws \CRM_Core_Exception
 283    */
 284   protected static function getLockForRefresh() {
 285     if (!isset(Civi::$statics[__CLASS__]['is_refresh_init'])) {
 286       Civi::$statics[__CLASS__] = ['is_refresh_init' => FALSE];
 287     }
 288
 289     if (Civi::$statics[__CLASS__]['is_refresh_init']) {
 290       throw new CRM_Core_Exception('A refresh has already run in this process');
 291     }
 292     $lock = Civi::lockManager()->acquire('data.core.group.refresh');
 293     if ($lock->isAcquired()) {
 294       Civi::$statics[__CLASS__]['is_refresh_init'] = TRUE;
 295       return $lock;
 296     }
 297     throw new CRM_Core_Exception('Mysql lock unavailable');
 298   }
 299
 300   /**
 301    * Do an opportunistic cache refresh if the site is configured for these.
 302    *
 303    * Sites that do not run the smart group clearing cron job should refresh the
 304    * caches on demand. The user session will be forced to wait so it is less
 305    * ideal.
 306    */
 307   public static function opportunisticCacheFlush(): void {
 308     if (Civi::settings()->get('smart_group_cache_refresh_mode') === 'opportunistic') {
 309       self::flushCaches();
 310     }
 311   }
 312
 313   /**
 314    * Do a forced cache refresh.
 315    *
 316    * This function is appropriate to be called by system jobs & non-user sessions.
 317    */
 318   public static function deterministicCacheFlush() {
 319     if (self::smartGroupCacheTimeout() == 0) {
 320       CRM_Core_DAO::executeQuery("TRUNCATE civicrm_group_contact_cache");
 321       CRM_Core_DAO::executeQuery("UPDATE civicrm_group SET cache_date = NULL");
 322     }
 323     else {
 324       self::flushCaches();
 325     }
 326   }
 327
 328   /**
 329    * Remove one or more contacts from the smart group cache.
 330    *
 331    * @param int|array $cid
 332    * @param null $groupId
 333    *
 334    * @return bool
 335    *   TRUE if successful.
 336    * @throws \CRM_Core_Exception
 337    */
 338   public static function removeContact($cid, $groupId = NULL) {
 339     $cids = [];
 340     // sanitize input
 341     foreach ((array) $cid as $c) {
 342       $cids[] = CRM_Utils_Type::escape($c, 'Integer');
 343     }
 344     if ($cids) {
 345       $condition = count($cids) == 1 ? "= {$cids[0]}" : "IN (" . implode(',', $cids) . ")";
 346       if ($groupId) {
 347         $condition .= " AND group_id = " . CRM_Utils_Type::escape($groupId, 'Integer');
 348       }
 349       $sql = "DELETE FROM civicrm_group_contact_cache WHERE contact_id $condition";
 350       CRM_Core_DAO::executeQuery($sql);
 351       return TRUE;
 352     }
 353     return FALSE;
 354   }
 355
 356   /**
 357    * Load the smart group cache for a saved search.
 358    *
 359    * @param object $group
 360    *   The smart group that needs to be loaded.
 361    * @param bool $force
 362    *   deprecated parameter = Should we force a search through.
 363    *
 364    * @throws \API_Exception
 365    * @throws \CRM_Core_Exception
 366    * @throws \CiviCRM_API3_Exception
 367    */
 368   public static function load($group, $force = FALSE) {
 369     $groupID = (int) $group->id;
 370     if ($force) {
 371       CRM_Core_Error::deprecatedWarning('use invalidate group contact cache first.');
 372       self::invalidateGroupContactCache($group->id);
 373     }
 374
 375     $locks = self::getLocksForRefreshableGroupsTo([$groupID]);
 376     foreach ($locks as $groupID => $lock) {
 377       $groupContactsTempTable = CRM_Utils_SQL_TempTable::build()
 378         ->setCategory('gccache')
 379         ->setMemory();
 380       self::buildGroupContactTempTable([$groupID], $groupContactsTempTable);
 381       self::updateCacheFromTempTable($groupContactsTempTable, [$groupID]);
 382       $lock->release();
 383     }
 384   }
 385
 386   /**
 387    * Get an array of locks for all the refreshable groups in the array.
 388    *
 389    * The groups are refreshable if both the following conditions are met:
 390    * 1) the cache date in the database is null or stale
 391    * 2) a mysql lock can be aquired for the group.
 392    *
 393    * @param array $groupIDs
 394    *
 395    * @return array
 396    * @throws \CRM_Core_Exception
 397    */
 398   protected static function getLocksForRefreshableGroupsTo(array $groupIDs): array {
 399     $locks = [];
 400     $groupIDs = self::getGroupsNeedingRefreshing($groupIDs);
 401     foreach ($groupIDs as $groupID) {
 402       $lock = Civi::lockManager()->acquire("data.core.group.{$groupID}");
 403       if ($lock->isAcquired()) {
 404         $locks[$groupID] = $lock;
 405       }
 406     }
 407     return $locks;
 408   }
 409
 410   /**
 411    * Retrieve the smart group cache timeout in minutes.
 412    *
 413    * This checks if a timeout has been configured. If one has then smart groups should not
 414    * be refreshed more frequently than the time out. If a group was recently refreshed it should not
 415    * refresh again within that period.
 416    *
 417    * @return int
 418    */
 419   protected static function smartGroupCacheTimeout() {
 420     $config = CRM_Core_Config::singleton();
 421
 422     if (
 423       isset($config->smartGroupCacheTimeout) &&
 424       is_numeric($config->smartGroupCacheTimeout)
 425     ) {
 426       return $config->smartGroupCacheTimeout;
 427     }
 428
 429     // Default to 5 minutes.
 430     return 5;
 431   }
 432
 433   /**
 434    * Get all the smart groups that this contact belongs to.
 435    *
 436    * Note that this could potentially be a super slow function since
 437    * it ensure that all contact groups are loaded in the cache
 438    *
 439    * @param int $contactID
 440    *
 441    * @return array
 442    *   an array of groups that this contact belongs to
 443    */
 444   public static function contactGroup(int $contactID): array {
 445
 446     self::loadAll();
 447
 448     $sql = "
 449 SELECT     gc.group_id, gc.contact_id, g.title, g.children, g.description
 450 FROM       civicrm_group_contact_cache gc
 451 INNER JOIN civicrm_group g ON g.id = gc.group_id
 452 WHERE      gc.contact_id = $contactID
 453             AND (g.is_hidden = 0 OR g.is_hidden IS NULL)
 454 ORDER BY   gc.contact_id, g.children
 455 ";
 456
 457     $dao = CRM_Core_DAO::executeQuery($sql);
 458     $contactGroup = [];
 459     $prevContactID = NULL;
 460     while ($dao->fetch()) {
 461       if (
 462         $prevContactID &&
 463         $prevContactID != $dao->contact_id
 464       ) {
 465         $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 466       }
 467       $prevContactID = $dao->contact_id;
 468       if (!array_key_exists($dao->contact_id, $contactGroup)) {
 469         $contactGroup[$dao->contact_id]
 470           = ['group' => [], 'groupTitle' => []];
 471       }
 472
 473       $contactGroup[$dao->contact_id]['group'][]
 474         = [
 475           'id' => $dao->group_id,
 476           'title' => $dao->title,
 477           'description' => $dao->description,
 478           'children' => $dao->children,
 479         ];
 480       $contactGroup[$dao->contact_id]['groupTitle'][] = $dao->title;
 481     }
 482
 483     if ($prevContactID) {
 484       $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 485     }
 486
 487     if ((!empty($contactGroup[$contactID]))) {
 488       return $contactGroup[$contactID];
 489     }
 490     return $contactGroup;
 491   }
 492
 493   /**
 494    * Get the datetime from which the cache should be considered invalid.
 495    *
 496    * Ie if the smartgroup cache timeout is 5 minutes ago then the cache is invalid if it was
 497    * refreshed 6 minutes ago, but not if it was refreshed 4 minutes ago.
 498    *
 499    * @return string
 500    */
 501   public static function getCacheInvalidDateTime(): string {
 502     return date('YmdHis', strtotime("-" . self::smartGroupCacheTimeout() . " Minutes"));
 503   }
 504
 505   /**
 506    * Invalidates the smart group cache for a particular group
 507    * @param int $groupID - Group to invalidate
 508    */
 509   public static function invalidateGroupContactCache($groupID): void {
 510     CRM_Core_DAO::executeQuery("UPDATE civicrm_group
 511       SET cache_date = NULL
 512       WHERE id = %1 AND (saved_search_id IS NOT NULL OR children IS NOT NULL)", [
 513         1 => [$groupID, 'Positive'],
 514       ]);
 515   }
 516
 517   /**
 518    * @param array $savedSearch
 519    * @param string $addSelect
 520    * @param string $excludeClause
 521    * @return string
 522    * @throws API_Exception
 523    * @throws \Civi\API\Exception\NotImplementedException
 524    * @throws CRM_Core_Exception
 525    */
 526   protected static function getApiSQL(array $savedSearch, string $addSelect, string $excludeClause) {
 527     $apiParams = $savedSearch['api_params'] + ['select' => ['id'], 'checkPermissions' => FALSE];
 528     $idField = SqlExpression::convert($apiParams['select'][0], TRUE)->getAlias();
 529     // Unless there's a HAVING clause, we don't care about other columns
 530     if (empty($apiParams['having'])) {
 531       $apiParams['select'] = array_slice($apiParams['select'], 0, 1);
 532     }
 533     /* @var $api \Civi\Api4\Generic\DAOGetAction */
 534     $api = Request::create($savedSearch['api_entity'], 'get', $apiParams);
 535     $query = new Api4SelectQuery($api);
 536     $query->forceSelectId = FALSE;
 537     $query->getQuery()->having("$idField $excludeClause");
 538     $sql = $query->getSql();
 539     // Place sql in a nested sub-query, otherwise HAVING is impossible on any field other than contact_id
 540     return "SELECT $addSelect, `$idField` AS contact_id FROM ($sql) api_query";
 541   }
 542
 543   /**
 544    * Get sql from a custom search.
 545    *
 546    * We split it up and store custom class
 547    * so temp tables are not destroyed if they are used
 548    *
 549    * @param int $savedSearchID
 550    * @param array $ssParams
 551    * @param string $addSelect
 552    * @param string $excludeClause
 553    *
 554    * @return string
 555    * @throws CRM_Core_Exception
 556    */
 557   protected static function getCustomSearchSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) {
 558     $searchSQL = CRM_Contact_BAO_SearchCustom::customClass($ssParams['customSearchID'], $savedSearchID)->contactIDs();
 559     $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL);
 560     if (strpos($searchSQL, 'WHERE') === FALSE) {
 561       $searchSQL .= " WHERE contact_a.id $excludeClause";
 562     }
 563     else {
 564       $searchSQL .= " AND contact_a.id $excludeClause";
 565     }
 566     return preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $searchSQL);
 567   }
 568
 569   /**
 570    * Get array of sql from a saved query object group.
 571    *
 572    * @param int $savedSearchID
 573    * @param array $ssParams
 574    * @param string $addSelect
 575    * @param string $excludeClause
 576    *
 577    * @return string
 578    * @throws \CRM_Core_Exception
 579    * @throws \CiviCRM_API3_Exception
 580    */
 581   protected static function getQueryObjectSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) {
 582     $returnProperties = NULL;
 583     if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $savedSearchID, 'mapping_id')) {
 584       $fv = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 585       $returnProperties = CRM_Core_BAO_Mapping::returnProperties($fv);
 586     }
 587     $formValues = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 588     // CRM-17075 using the formValues in this way imposes extra logic and complexity.
 589     // we have the where_clause and where tables stored in the saved_search table
 590     // and should use these rather than re-processing the form criteria (which over-works
 591     // the link between the form layer & the query layer too).
 592     // It's hard to think of when you would want to use anything other than return
 593     // properties = array('contact_id' => 1) here as the point would appear to be to
 594     // generate the list of contact ids in the group.
 595     // @todo review this to use values in saved_search table (preferably for 4.8).
 596     $query
 597       = new CRM_Contact_BAO_Query(
 598       $ssParams, $returnProperties, NULL,
 599       FALSE, FALSE, 1,
 600       TRUE, TRUE,
 601       FALSE,
 602       $formValues['display_relationship_type'] ?? NULL,
 603       $formValues['operator'] ?? 'AND'
 604     );
 605     $query->_useDistinct = FALSE;
 606     $query->_useGroupBy = FALSE;
 607     $sqlParts = $query->getSearchSQLParts(
 608       0, 0, NULL,
 609       FALSE, FALSE,
 610       FALSE, TRUE,
 611       "contact_a.id $excludeClause"
 612     );
 613     $select = preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $sqlParts['select']);
 614
 615     return "$select {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['group_by']} {$sqlParts['having']}";
 616   }
 617
 618   /**
 619    * Build a temporary table for the contacts in the specified group.
 620    *
 621    * @param array $groupIDs
 622    *   Currently only one id is build but this has been written
 623    *   to make it easy to switch to multiple.
 624    * @param CRM_Utils_SQL_TempTable $tempTableObject
 625    *
 626    * @throws \API_Exception
 627    * @throws \CRM_Core_Exception
 628    * @throws \CiviCRM_API3_Exception
 629    */
 630   protected static function buildGroupContactTempTable(array $groupIDs, $tempTableObject): void {
 631     $group = Group::get(FALSE)->addWhere('id', 'IN', $groupIDs)
 632       ->setSelect(['saved_search_id', 'children'])->execute()->first();
 633     $groupID = (int) $group['id'];
 634
 635     if ($group['saved_search_id']) {
 636       $ssParams = CRM_Contact_BAO_SavedSearch::getSearchParams($group['saved_search_id']);
 637
 638       $excludeClause = "NOT IN (
 639                         SELECT contact_id FROM civicrm_group_contact
 640                         WHERE civicrm_group_contact.status = 'Removed'
 641                         AND civicrm_group_contact.group_id = $groupID )";
 642       $addSelect = "$groupID AS group_id";
 643
 644       if (!empty($ssParams['api_entity'])) {
 645         $sql = self::getApiSQL($ssParams, $addSelect, $excludeClause);
 646       }
 647       else {
 648         // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance
 649         if (!empty($ssParams)) {
 650           CRM_Contact_BAO_ProximityQuery::fixInputParams($ssParams);
 651         }
 652         if (isset($ssParams['customSearchID'])) {
 653           $sql = self::getCustomSearchSQL($group['saved_search_id'], $ssParams, $addSelect, $excludeClause);
 654         }
 655         else {
 656           $sql = self::getQueryObjectSQL($group['saved_search_id'], $ssParams, $addSelect, $excludeClause);
 657         }
 658       }
 659     }
 660
 661     $tempTableName = $tempTableObject->getName();
 662     $tempTableObject->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)');
 663
 664     if (!empty($sql)) {
 665       $contactQueries[] = $sql;
 666     }
 667     // lets also store the records that are explicitly added to the group
 668     // this allows us to skip the group contact LEFT JOIN
 669     $contactQueries[] =
 670       "SELECT $groupID as group_id, contact_id as contact_id
 671        FROM   civicrm_group_contact
 672        WHERE  civicrm_group_contact.status = 'Added' AND civicrm_group_contact.group_id = $groupID ";
 673
 674     self::clearGroupContactCache($groupID);
 675
 676     foreach ($contactQueries as $contactQuery) {
 677       CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTableName (group_id, contact_id) {$contactQuery}");
 678     }
 679
 680     CRM_Core_DAO::reenableFullGroupByMode();
 681
 682     if ($group['children']) {
 683
 684       // Store a list of contacts who are removed from the parent group
 685       $sqlContactsRemovedFromGroup = "
 686 SELECT contact_id
 687 FROM civicrm_group_contact
 688 WHERE  civicrm_group_contact.status = 'Removed'
 689 AND  civicrm_group_contact.group_id = $groupID ";
 690       $dao = CRM_Core_DAO::executeQuery($sqlContactsRemovedFromGroup);
 691       $removed_contacts = [];
 692       while ($dao->fetch()) {
 693         $removed_contacts[] = $dao->contact_id;
 694       }
 695
 696       $childrenIDs = explode(',', $group['children']);
 697       foreach ($childrenIDs as $childID) {
 698         $contactIDs = CRM_Contact_BAO_Group::getMember($childID, FALSE);
 699         // Unset each contact that is removed from the parent group
 700         foreach ($removed_contacts as $removed_contact) {
 701           unset($contactIDs[$removed_contact]);
 702         }
 703         if (empty($contactIDs)) {
 704           // This child group has no contact IDs so we don't need to add them to
 705           continue;
 706         }
 707         $values = [];
 708         foreach ($contactIDs as $contactID => $dontCare) {
 709           $values[] = "({$groupID},{$contactID})";
 710         }
 711         $str = implode(',', $values);
 712         CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTableName (group_id, contact_id) VALUES $str");
 713       }
 714     }
 715   }
 716
 717   /**
 718    * Populate a temporary table with group ids and contact ids.
 719    *
 720    * Do not call this outside of core tested code - it WILL change.
 721    *
 722    * @param array[int] $groupIDs
 723    * @param string $temporaryTable
 724    *
 725    * @throws \CiviCRM_API3_Exception
 726    */
 727   public static function populateTemporaryTableWithContactsInGroups(array $groupIDs, string $temporaryTable): void {
 728     $groups = civicrm_api3('Group', 'get', [
 729       'is_active' => 1,
 730       'id' => ['IN' => $groupIDs],
 731       'saved_search_id' => ['>' => 0],
 732       'return' => 'id',
 733     ]);
 734     $smartGroups = array_keys($groups['values']);
 735
 736     $query = "
 737        SELECT DISTINCT group_contact.contact_id as contact_id
 738        FROM civicrm_group_contact group_contact
 739        WHERE group_contact.group_id IN (" . implode(', ', $groupIDs) . ")
 740        AND group_contact.status = 'Added' ";
 741
 742     if (!empty($smartGroups)) {
 743       CRM_Contact_BAO_GroupContactCache::check($smartGroups);
 744       $smartGroups = implode(',', $smartGroups);
 745       $query .= "
 746         UNION DISTINCT
 747         SELECT smartgroup_contact.contact_id as contact_id
 748         FROM civicrm_group_contact_cache smartgroup_contact
 749         WHERE smartgroup_contact.group_id IN ({$smartGroups}) ";
 750     }
 751     CRM_Core_DAO::executeQuery('INSERT INTO ' . $temporaryTable . ' ' . $query);
 752   }
 753
 754   /**
 755    * @param array|null $groupIDs
 756    * @param int $limit
 757    *
 758    * @return array
 759    */
 760   protected static function getGroupsNeedingRefreshing(?array $groupIDs, int $limit = 0): array {
 761     $groupIDClause = NULL;
 762     // ensure that all the smart groups are loaded
 763     // this function is expensive and should be sparingly used if groupIDs is empty
 764     if (!empty($groupIDs)) {
 765       // note escapeString is a must here and we can't send the imploded value as second argument to
 766       // the executeQuery(), since that would put single quote around the string and such a string
 767       // of comma separated integers would not work.
 768       $groupIDString = CRM_Core_DAO::escapeString(implode(', ', $groupIDs));
 769       $groupIDClause = "g.id IN ({$groupIDString})";
 770     }
 771
 772     $query = self::groupRefreshedClause($groupIDClause, !empty($groupIDs));
 773
 774     $limitClause = $orderClause = NULL;
 775     if ($limit > 0) {
 776       $limitClause = " LIMIT 0, $limit";
 777       $orderClause = " ORDER BY g.cache_date";
 778     }
 779     // We ignore hidden groups and disabled groups
 780     $query .= "
 781         $orderClause
 782         $limitClause
 783 ";
 784
 785     $dao = CRM_Core_DAO::executeQuery($query);
 786     $processGroupIDs = [];
 787     while ($dao->fetch()) {
 788       $processGroupIDs[] = $dao->id;
 789     }
 790     return $processGroupIDs;
 791   }
 792
 793   /**
 794    * Transfer the contact ids to the group cache table and update the cache time.
 795    *
 796    * @param \CRM_Utils_SQL_TempTable $groupContactsTempTable
 797    * @param array $groupIDs
 798    */
 799   private static function updateCacheFromTempTable(CRM_Utils_SQL_TempTable $groupContactsTempTable, array $groupIDs): void {
 800     $tempTable = $groupContactsTempTable->getName();
 801
 802     // Don't call clearGroupContactCache as we don't want to clear the cache dates
 803     // The will get updated by updateCacheTime() below and not clearing the dates reduces
 804     // the chance that loadAll() will try and rebuild at the same time.
 805     $clearCacheQuery = '
 806     DELETE  g
 807       FROM  civicrm_group_contact_cache g
 808       WHERE  g.group_id IN (%1) ';
 809     $params = [
 810       1 => [implode(',', $groupIDs), 'CommaSeparatedIntegers'],
 811     ];
 812     CRM_Core_DAO::executeQuery($clearCacheQuery, $params);
 813
 814     CRM_Core_DAO::executeQuery(
 815       "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
 816         SELECT DISTINCT contact_id, group_id FROM $tempTable
 817       ");
 818     $groupContactsTempTable->drop();
 819     foreach ($groupIDs as $groupID) {
 820       self::updateCacheTime([$groupID], TRUE);
 821     }
 822   }
 823
 824 }