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