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     // @todo this is consistent with previous behaviour but as the first query could take several seconds the second
 318     // could become inaccurate. It seems to make more sense to fetch them first & delete from an array (which would
 319     // also reduce joins). If we do this we should also consider how best to iterate the groups. If we do them one at
 320     // a time we could call a hook, allowing people to manage the frequency on their groups, or possibly custom searches
 321     // might do that too. However, for 2000 groups that's 2000 iterations. If we do all once we potentially create a
 322     // slow query. It's worth noting the speed issue generally relates to the size of the group but if one slow group
 323     // is in a query with 500 fast ones all 500 get locked. One approach might be to calculate group size or the
 324     // number of groups & then process all at once or many query runs depending on what is found. Of course those
 325     // preliminary queries would need speed testing.
 326     CRM_Core_DAO::executeQuery(
 327       "
 328         DELETE gc
 329         FROM civicrm_group_contact_cache gc
 330         INNER JOIN civicrm_group g ON g.id = gc.group_id
 331         WHERE g.cache_date <= %1
 332       ",
 333       $params
 334     );
 335
 336     // Clear these out without resetting them because we are not building caches here, only clearing them,
 337     // so the state is 'as if they had never been built'.
 338     CRM_Core_DAO::executeQuery(
 339       "
 340         UPDATE civicrm_group g
 341         SET    cache_date = NULL,
 342         refresh_date = NULL
 343         WHERE  g.cache_date <= %1
 344       ",
 345       $params
 346     );
 347     $lock->release();
 348   }
 349
 350   /**
 351    * Check if the refresh is already initiated.
 352    *
 353    * We have 2 imperfect methods for this:
 354    *   1) a static variable in the function. This works fine within a request
 355    *   2) a mysql lock. This works fine as long as CiviMail is not running, or if mysql is version 5.7+
 356    *
 357    * Where these 2 locks fail we get 2 processes running at the same time, but we have at least minimised that.
 358    *
 359    * @return \Civi\Core\Lock\LockInterface
 360    * @throws \CRM_Core_Exception
 361    */
 362   protected static function getLockForRefresh() {
 363     if (!isset(Civi::$statics[__CLASS__]['is_refresh_init'])) {
 364       Civi::$statics[__CLASS__] = ['is_refresh_init' => FALSE];
 365     }
 366
 367     if (Civi::$statics[__CLASS__]['is_refresh_init']) {
 368       throw new CRM_Core_Exception('A refresh has already run in this process');
 369     }
 370     $lock = Civi::lockManager()->acquire('data.core.group.refresh');
 371     if ($lock->isAcquired()) {
 372       Civi::$statics[__CLASS__]['is_refresh_init'] = TRUE;
 373       return $lock;
 374     }
 375     throw new CRM_Core_Exception('Mysql lock unavailable');
 376   }
 377
 378   /**
 379    * Do an opportunistic cache refresh if the site is configured for these.
 380    *
 381    * Sites that do not run the smart group clearing cron job should refresh the
 382    * caches on demand. The user session will be forced to wait so it is less
 383    * ideal.
 384    */
 385   public static function opportunisticCacheFlush() {
 386     if (Civi::settings()->get('smart_group_cache_refresh_mode') == 'opportunistic') {
 387       self::flushCaches();
 388     }
 389   }
 390
 391   /**
 392    * Do a forced cache refresh.
 393    *
 394    * This function is appropriate to be called by system jobs & non-user sessions.
 395    */
 396   public static function deterministicCacheFlush() {
 397     if (self::smartGroupCacheTimeout() == 0) {
 398       CRM_Core_DAO::executeQuery("TRUNCATE civicrm_group_contact_cache");
 399       CRM_Core_DAO::executeQuery("
 400         UPDATE civicrm_group g
 401         SET cache_date = null, refresh_date = null");
 402     }
 403     else {
 404       self::flushCaches();
 405     }
 406   }
 407
 408   /**
 409    * Remove one or more contacts from the smart group cache.
 410    *
 411    * @param int|array $cid
 412    * @param int $groupId
 413    *
 414    * @return bool
 415    *   TRUE if successful.
 416    */
 417   public static function removeContact($cid, $groupId = NULL) {
 418     $cids = [];
 419     // sanitize input
 420     foreach ((array) $cid as $c) {
 421       $cids[] = CRM_Utils_Type::escape($c, 'Integer');
 422     }
 423     if ($cids) {
 424       $condition = count($cids) == 1 ? "= {$cids[0]}" : "IN (" . implode(',', $cids) . ")";
 425       if ($groupId) {
 426         $condition .= " AND group_id = " . CRM_Utils_Type::escape($groupId, 'Integer');
 427       }
 428       $sql = "DELETE FROM civicrm_group_contact_cache WHERE contact_id $condition";
 429       CRM_Core_DAO::executeQuery($sql);
 430       return TRUE;
 431     }
 432     return FALSE;
 433   }
 434
 435   /**
 436    * Load the smart group cache for a saved search.
 437    *
 438    * @param object $group
 439    *   The smart group that needs to be loaded.
 440    * @param bool $force
 441    *   Should we force a search through.
 442    *
 443    * @throws \CRM_Core_Exception
 444    */
 445   public static function load(&$group, $force = FALSE) {
 446     $groupID = $group->id;
 447     $savedSearchID = $group->saved_search_id;
 448     if (array_key_exists($groupID, self::$_alreadyLoaded) && !$force) {
 449       return;
 450     }
 451
 452     self::$_alreadyLoaded[$groupID] = 1;
 453
 454     // FIXME: some other process could have actually done the work before we got here,
 455     // Ensure that work needs to be done before continuing
 456     if (!$force && !self::shouldGroupBeRefreshed($groupID, TRUE)) {
 457       return;
 458     }
 459
 460     $sql = NULL;
 461     $customClass = NULL;
 462     if ($savedSearchID) {
 463       $ssParams = CRM_Contact_BAO_SavedSearch::getSearchParams($savedSearchID);
 464
 465       if (!empty($ssParams['api_entity'])) {
 466         $mainCol = 'a';
 467         $sql = self::getApiSQL($savedSearchID, $ssParams);
 468       }
 469       else {
 470         $mainCol = 'contact_a';
 471         // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance
 472         if (!empty($ssParams)) {
 473           CRM_Contact_BAO_ProximityQuery::fixInputParams($ssParams);
 474         }
 475         if (isset($ssParams['customSearchID'])) {
 476           $sql = self::getCustomSearchSQL($savedSearchID, $ssParams);
 477         }
 478         else {
 479           $sql = self::getQueryObjectSQL($savedSearchID, $ssParams);
 480         }
 481       }
 482       $groupID = CRM_Utils_Type::escape($groupID, 'Integer');
 483       $sql['from'] .= " AND $mainCol.id NOT IN (
 484                           SELECT contact_id FROM civicrm_group_contact
 485                           WHERE civicrm_group_contact.status = 'Removed'
 486                           AND   civicrm_group_contact.group_id = $groupID ) ";
 487     }
 488
 489     if (!empty($sql['select'])) {
 490       $sql['select'] = preg_replace("/^\s*SELECT/", "SELECT $groupID as group_id, ", $sql['select']);
 491     }
 492
 493     $groupContactsTempTable = CRM_Utils_SQL_TempTable::build()->setCategory('gccache')->setMemory();
 494     $tempTable = $groupContactsTempTable->getName();
 495     $groupContactsTempTable->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)');
 496
 497     $contactQueries[] = $sql;
 498     // lets also store the records that are explicitly added to the group
 499     // this allows us to skip the group contact LEFT JOIN
 500     $contactQueries[] = [
 501       'select' => "SELECT $groupID as group_id, contact_id as contact_id",
 502       'from' => " FROM   civicrm_group_contact WHERE  civicrm_group_contact.status = 'Added' AND  civicrm_group_contact.group_id = $groupID ",
 503     ];
 504
 505     self::clearGroupContactCache($groupID);
 506
 507     foreach ($contactQueries as $contactQuery) {
 508       if (empty($contactQuery['select']) || empty($contactQuery['from'])) {
 509         continue;
 510       }
 511       if (CRM_Core_DAO::singleValueQuery("SELECT COUNT(*) {$contactQuery['from']}") > 0) {
 512         CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) {$contactQuery['select']} {$contactQuery['from']}");
 513       }
 514     }
 515
 516     if ($group->children) {
 517
 518       // Store a list of contacts who are removed from the parent group
 519       $sqlContactsRemovedFromGroup = "
 520 SELECT contact_id
 521 FROM civicrm_group_contact
 522 WHERE  civicrm_group_contact.status = 'Removed'
 523 AND  civicrm_group_contact.group_id = $groupID ";
 524       $dao = CRM_Core_DAO::executeQuery($sqlContactsRemovedFromGroup);
 525       $removed_contacts = [];
 526       while ($dao->fetch()) {
 527         $removed_contacts[] = $dao->contact_id;
 528       }
 529
 530       $childrenIDs = explode(',', $group->children);
 531       foreach ($childrenIDs as $childID) {
 532         $contactIDs = CRM_Contact_BAO_Group::getMember($childID, FALSE);
 533         // Unset each contact that is removed from the parent group
 534         foreach ($removed_contacts as $removed_contact) {
 535           unset($contactIDs[$removed_contact]);
 536         }
 537         if (empty($contactIDs)) {
 538           // This child group has no contact IDs so we don't need to add them to
 539           continue;
 540         }
 541         $values = [];
 542         foreach ($contactIDs as $contactID => $dontCare) {
 543           $values[] = "({$groupID},{$contactID})";
 544         }
 545         $str = implode(',', $values);
 546         CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) VALUES $str");
 547       }
 548     }
 549
 550     // grab a lock so other processes don't compete and do the same query
 551     $lock = Civi::lockManager()->acquire("data.core.group.{$groupID}");
 552     if (!$lock->isAcquired()) {
 553       // this can cause inconsistent results since we don't know if the other process
 554       // will fill up the cache before our calling routine needs it.
 555       // however this routine does not return the status either, so basically
 556       // its a "lets return and hope for the best"
 557       return;
 558     }
 559
 560     // Don't call clearGroupContactCache as we don't want to clear the cache dates
 561     // The will get updated by updateCacheTime() below and not clearing the dates reduces
 562     // the chance that loadAll() will try and rebuild at the same time.
 563     $clearCacheQuery = "
 564     DELETE  g
 565       FROM  civicrm_group_contact_cache g
 566       WHERE  g.group_id = %1 ";
 567     $params = [
 568       1 => [$groupID, 'Integer'],
 569     ];
 570     CRM_Core_DAO::executeQuery($clearCacheQuery, $params);
 571
 572     CRM_Core_DAO::executeQuery(
 573       "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
 574         SELECT DISTINCT contact_id, group_id FROM $tempTable
 575       ");
 576     $groupContactsTempTable->drop();
 577     self::updateCacheTime([$groupID], TRUE);
 578
 579     $lock->release();
 580   }
 581
 582   /**
 583    * Retrieve the smart group cache timeout in minutes.
 584    *
 585    * This checks if a timeout has been configured. If one has then smart groups should not
 586    * be refreshed more frequently than the time out. If a group was recently refreshed it should not
 587    * refresh again within that period.
 588    *
 589    * @return int
 590    */
 591   public static function smartGroupCacheTimeout() {
 592     $config = CRM_Core_Config::singleton();
 593
 594     if (
 595       isset($config->smartGroupCacheTimeout) &&
 596       is_numeric($config->smartGroupCacheTimeout)
 597     ) {
 598       return $config->smartGroupCacheTimeout;
 599     }
 600
 601     // Default to 5 minutes.
 602     return 5;
 603   }
 604
 605   /**
 606    * Get all the smart groups that this contact belongs to.
 607    *
 608    * Note that this could potentially be a super slow function since
 609    * it ensure that all contact groups are loaded in the cache
 610    *
 611    * @param int $contactID
 612    * @param bool $showHidden
 613    *   Hidden groups are shown only if this flag is set.
 614    *
 615    * @return array
 616    *   an array of groups that this contact belongs to
 617    */
 618   public static function contactGroup($contactID, $showHidden = FALSE) {
 619     if (empty($contactID)) {
 620       return NULL;
 621     }
 622
 623     if (is_array($contactID)) {
 624       $contactIDs = $contactID;
 625     }
 626     else {
 627       $contactIDs = [$contactID];
 628     }
 629
 630     self::loadAll();
 631
 632     $hiddenClause = '';
 633     if (!$showHidden) {
 634       $hiddenClause = ' AND (g.is_hidden = 0 OR g.is_hidden IS NULL) ';
 635     }
 636
 637     $contactIDString = CRM_Core_DAO::escapeString(implode(', ', $contactIDs));
 638     $sql = "
 639 SELECT     gc.group_id, gc.contact_id, g.title, g.children, g.description
 640 FROM       civicrm_group_contact_cache gc
 641 INNER JOIN civicrm_group g ON g.id = gc.group_id
 642 WHERE      gc.contact_id IN ($contactIDString)
 643            $hiddenClause
 644 ORDER BY   gc.contact_id, g.children
 645 ";
 646
 647     $dao = CRM_Core_DAO::executeQuery($sql);
 648     $contactGroup = [];
 649     $prevContactID = NULL;
 650     while ($dao->fetch()) {
 651       if (
 652         $prevContactID &&
 653         $prevContactID != $dao->contact_id
 654       ) {
 655         $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 656       }
 657       $prevContactID = $dao->contact_id;
 658       if (!array_key_exists($dao->contact_id, $contactGroup)) {
 659         $contactGroup[$dao->contact_id]
 660           = ['group' => [], 'groupTitle' => []];
 661       }
 662
 663       $contactGroup[$dao->contact_id]['group'][]
 664         = [
 665           'id' => $dao->group_id,
 666           'title' => $dao->title,
 667           'description' => $dao->description,
 668           'children' => $dao->children,
 669         ];
 670       $contactGroup[$dao->contact_id]['groupTitle'][] = $dao->title;
 671     }
 672
 673     if ($prevContactID) {
 674       $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 675     }
 676
 677     if ((!empty($contactGroup[$contactID]) && is_numeric($contactID))) {
 678       return $contactGroup[$contactID];
 679     }
 680     else {
 681       return $contactGroup;
 682     }
 683   }
 684
 685   /**
 686    * Get the datetime from which the cache should be considered invalid.
 687    *
 688    * Ie if the smartgroup cache timeout is 5 minutes ago then the cache is invalid if it was
 689    * refreshed 6 minutes ago, but not if it was refreshed 4 minutes ago.
 690    *
 691    * @return string
 692    */
 693   public static function getCacheInvalidDateTime() {
 694     return date('YmdHis', strtotime("-" . self::smartGroupCacheTimeout() . " Minutes"));
 695   }
 696
 697   /**
 698    * Get the date when the cache should be refreshed from.
 699    *
 700    * Ie. now + the offset & we will delete anything prior to then.
 701    *
 702    * @return string
 703    */
 704   public static function getRefreshDateTime() {
 705     return date('YmdHis', strtotime("+ " . self::smartGroupCacheTimeout() . " Minutes"));
 706   }
 707
 708   /**
 709    * Invalidates the smart group cache for a particular group
 710    * @param int $groupID - Group to invalidate
 711    */
 712   public static function invalidateGroupContactCache($groupID) {
 713     CRM_Core_DAO::executeQuery("UPDATE civicrm_group
 714       SET cache_date = NULL, refresh_date = NULL
 715       WHERE id = %1", [
 716         1 => [$groupID, 'Positive'],
 717       ]);
 718   }
 719
 720   /**
 721    * @param $savedSearchID
 722    * @param array $savedSearch
 723    * @return array
 724    * @throws API_Exception
 725    * @throws \Civi\API\Exception\NotImplementedException
 726    * @throws CRM_Core_Exception
 727    */
 728   protected static function getApiSQL($savedSearchID, array $savedSearch): array {
 729     $api = \Civi\API\Request::create($savedSearch['api_entity'], 'get', $savedSearch['api_params']);
 730     $query = new \Civi\Api4\Query\Api4SelectQuery($api->getEntityName(), FALSE, $api->entityFields());
 731     $query->select = ['id'];
 732     $query->where = $api->getWhere();
 733     $query->orderBy = $api->getOrderBy();
 734     $query->limit = $api->getLimit();
 735     $query->offset = $api->getOffset();
 736     $sql = $query->getSql();
 737     return [
 738       'select' => substr($sql, 0, strpos($sql, 'FROM')),
 739       'from' => substr($sql, strpos($sql, 'FROM')),
 740     ];
 741   }
 742
 743   /**
 744    * Get sql from a custom search.
 745    *
 746    * @param int $savedSearchID
 747    * @param array $ssParams
 748    *
 749    * @return array
 750    * @throws \Exception
 751    */
 752   protected static function getCustomSearchSQL($savedSearchID, array $ssParams): array {
 753     // if custom search
 754
 755     // we split it up and store custom class
 756     // so temp tables are not destroyed if they are used
 757     // hence customClass is defined above at top of function
 758     $customClass = CRM_Contact_BAO_SearchCustom::customClass($ssParams['customSearchID'], $savedSearchID);
 759     $searchSQL = $customClass->contactIDs();
 760     $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL);
 761     if (strpos($searchSQL, 'WHERE') === FALSE) {
 762       $searchSQL .= " WHERE ( 1 ) ";
 763     }
 764     $sql = [
 765       'select' => substr($searchSQL, 0, strpos($searchSQL, 'FROM')),
 766       'from' => substr($searchSQL, strpos($searchSQL, 'FROM')),
 767     ];
 768     return $sql;
 769   }
 770
 771   /**
 772    * Get array of sql from a saved query object group.
 773    *
 774    * @param int $savedSearchID
 775    * @param array $ssParams
 776    *
 777    * @return array
 778    * @throws \CRM_Core_Exception
 779    * @throws \CiviCRM_API3_Exception
 780    */
 781   protected static function getQueryObjectSQL($savedSearchID, array $ssParams): array {
 782     $returnProperties = NULL;
 783     if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $savedSearchID, 'mapping_id')) {
 784       $fv = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 785       $returnProperties = CRM_Core_BAO_Mapping::returnProperties($fv);
 786     }
 787     $formValues = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 788     // CRM-17075 using the formValues in this way imposes extra logic and complexity.
 789     // we have the where_clause and where tables stored in the saved_search table
 790     // and should use these rather than re-processing the form criteria (which over-works
 791     // the link between the form layer & the query layer too).
 792     // It's hard to think of when you would want to use anything other than return
 793     // properties = array('contact_id' => 1) here as the point would appear to be to
 794     // generate the list of contact ids in the group.
 795     // @todo review this to use values in saved_search table (preferably for 4.8).
 796     $query
 797       = new CRM_Contact_BAO_Query(
 798       $ssParams, $returnProperties, NULL,
 799       FALSE, FALSE, 1,
 800       TRUE, TRUE,
 801       FALSE,
 802       CRM_Utils_Array::value('display_relationship_type', $formValues),
 803       CRM_Utils_Array::value('operator', $formValues, 'AND')
 804     );
 805     $query->_useDistinct = FALSE;
 806     $query->_useGroupBy = FALSE;
 807     $sqlParts = $query->getSearchSQLParts(
 808       0, 0, NULL,
 809       FALSE, FALSE,
 810       FALSE, TRUE
 811     );
 812     $sql = [
 813       'select' => $sqlParts['select'],
 814       'from' => "{$sqlParts['from']} {$sqlParts['where']} {$sqlParts['having']} {$sqlParts['group_by']}",
 815     ];
 816     return $sql;
 817   }
 818
 819 }