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