Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
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 | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 | 11 | |
7876aee8 | 12 | use Civi\API\Request; |
43a96367 | 13 | use Civi\Api4\Group; |
7876aee8 | 14 | use Civi\Api4\Query\Api4SelectQuery; |
23e56526 | 15 | use Civi\Api4\Query\SqlExpression; |
d96f7984 | 16 | use Civi\Api4\SavedSearch; |
23e56526 | 17 | |
6a488035 TO |
18 | /** |
19 | * | |
20 | * @package CRM | |
ca5cec67 | 21 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
22 | */ |
23 | class CRM_Contact_BAO_GroupContactCache extends CRM_Contact_DAO_GroupContactCache { | |
24 | ||
a8eb1fe6 TO |
25 | /** |
26 | * Get a list of caching modes. | |
27 | * | |
28 | * @return array | |
29 | */ | |
7876aee8 | 30 | public static function getModes(): array { |
be2fb01f | 31 | return [ |
a8eb1fe6 TO |
32 | // Flush expired caches in response to user actions. |
33 | 'opportunistic' => ts('Opportunistic Flush'), | |
a8eb1fe6 TO |
34 | // Flush expired caches via background cron jobs. |
35 | 'deterministic' => ts('Cron Flush'), | |
be2fb01f | 36 | ]; |
a8eb1fe6 TO |
37 | } |
38 | ||
6a488035 | 39 | /** |
67d19299 | 40 | * Check to see if we have cache entries for this group. |
41 | * | |
42 | * If not, regenerate, else return. | |
6a488035 | 43 | * |
adf28ffd | 44 | * @param array $groupIDs |
77c5b619 | 45 | * Of group that we are checking against. |
6a488035 | 46 | * |
5396af74 | 47 | * @return bool |
a6c01b45 | 48 | * TRUE if we did not regenerate, FALSE if we did |
6a488035 | 49 | */ |
7876aee8 | 50 | public static function check($groupIDs): bool { |
6a488035 TO |
51 | if (empty($groupIDs)) { |
52 | return TRUE; | |
53 | } | |
54 | ||
55 | return self::loadAll($groupIDs); | |
56 | } | |
57 | ||
abdb2607 | 58 | /** |
adf28ffd | 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 | |
abdb2607 | 62 | * |
77c5b619 TO |
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. | |
abdb2607 | 67 | * |
a6c01b45 CW |
68 | * @return string |
69 | * the sql query which lists the groups that need to be refreshed | |
abdb2607 | 70 | */ |
be7eed4c | 71 | protected static function groupRefreshedClause($groupIDClause = NULL, $includeHiddenGroups = FALSE): string { |
4052239b | 72 | $smartGroupCacheTimeoutDateTime = self::getCacheInvalidDateTime(); |
abdb2607 DL |
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 | |
4052239b | 79 | AND ( |
80 | g.cache_date IS NULL | |
81 | OR cache_date <= $smartGroupCacheTimeoutDateTime | |
4052239b | 82 | )"; |
abdb2607 DL |
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 | /** | |
adf28ffd | 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 | |
abdb2607 DL |
99 | * this process |
100 | * | |
77c5b619 TO |
101 | * @param int $groupID |
102 | * The group ID. | |
abdb2607 | 103 | * |
cb09fd1c | 104 | * @return bool |
abdb2607 | 105 | */ |
cb09fd1c EM |
106 | public static function shouldGroupBeRefreshed($groupID): bool { |
107 | $query = self::groupRefreshedClause('g.id = %1'); | |
be2fb01f | 108 | $params = [1 => [$groupID, 'Integer']]; |
abdb2607 | 109 | |
4f53db5a | 110 | // if the query returns the group ID, it means the group is a valid candidate for refreshing |
cb09fd1c | 111 | return (bool) CRM_Core_DAO::singleValueQuery($query, $params); |
abdb2607 DL |
112 | } |
113 | ||
6a488035 | 114 | /** |
adf28ffd | 115 | * Check to see if we have cache entries for this group. |
116 | * | |
6a488035 TO |
117 | * if not, regenerate, else return |
118 | * | |
b52cbe71 | 119 | * @param array|null $groupIDs groupIDs of group that we are checking against |
6a488035 | 120 | * if empty, all groups are checked |
77c5b619 TO |
121 | * @param int $limit |
122 | * Limits the number of groups we evaluate. | |
6a488035 | 123 | * |
5396af74 | 124 | * @return bool |
a6c01b45 | 125 | * TRUE if we did not regenerate, FALSE if we did |
6a488035 | 126 | */ |
e60f24eb | 127 | public static function loadAll($groupIDs = NULL, $limit = 0) { |
b52cbe71 | 128 | if ($groupIDs) { |
129 | // Passing a single value is deprecated. | |
130 | $groupIDs = (array) $groupIDs; | |
6a488035 | 131 | } |
6a488035 | 132 | |
06cc9899 JG |
133 | // Treat the default help text in Scheduled Jobs as equivalent to no limit. |
134 | $limit = (int) $limit; | |
b52cbe71 | 135 | $processGroupIDs = self::getGroupsNeedingRefreshing($groupIDs, $limit); |
6a488035 | 136 | |
b52cbe71 | 137 | if (!empty($processGroupIDs)) { |
6a488035 | 138 | self::add($processGroupIDs); |
6a488035 | 139 | } |
b52cbe71 | 140 | return TRUE; |
6a488035 TO |
141 | } |
142 | ||
86538308 | 143 | /** |
6b05374e | 144 | * Build the smart group cache for given groups. |
adf28ffd | 145 | * |
6b05374e | 146 | * @param array $groupIDs |
86538308 | 147 | */ |
6b05374e | 148 | public static function add($groupIDs) { |
149 | $groupIDs = (array) $groupIDs; | |
6a488035 | 150 | |
6b05374e | 151 | foreach ($groupIDs as $groupID) { |
152 | // first delete the current cache | |
be2fb01f | 153 | $params = [['group', 'IN', [$groupID], 0, 0]]; |
abdb2607 | 154 | // the below call updates the cache table as a byproduct of the query |
be2fb01f | 155 | CRM_Contact_BAO_Query::apiQuery($params, ['contact_id'], NULL, NULL, 0, 0, FALSE); |
6a488035 TO |
156 | } |
157 | } | |
158 | ||
86538308 | 159 | /** |
adf28ffd | 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 | * | |
6b05374e | 165 | * @param array $groupID |
adf28ffd | 166 | * @param array $values |
86538308 | 167 | */ |
be7eed4c | 168 | protected static function store($groupID, &$values) { |
6a488035 TO |
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); | |
eb917190 | 174 | |
6a488035 TO |
175 | // to avoid long strings, lets do BULK_INSERT_COUNT values at a time |
176 | while (!empty($values)) { | |
177 | $processed = TRUE; | |
353ffa53 TO |
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;"; | |
6a488035 TO |
181 | CRM_Core_DAO::executeQuery($sql); |
182 | } | |
183 | self::updateCacheTime($groupID, $processed); | |
184 | } | |
185 | ||
186 | /** | |
fe482240 | 187 | * Change the cache_date. |
6a488035 | 188 | * |
5a4f6742 CW |
189 | * @param array $groupID |
190 | * @param bool $processed | |
191 | * Whether the cache data was recently modified. | |
6a488035 | 192 | */ |
4e465c12 | 193 | protected static function updateCacheTime($groupID, $processed) { |
6a488035 TO |
194 | // only update cache entry if we had any values |
195 | if ($processed) { | |
196 | // also update the group with cache date information | |
a986056d | 197 | $now = date('YmdHis'); |
6a488035 TO |
198 | } |
199 | else { | |
353ffa53 | 200 | $now = 'null'; |
6a488035 TO |
201 | } |
202 | ||
203 | $groupIDs = implode(',', $groupID); | |
204 | $sql = " | |
205 | UPDATE civicrm_group | |
826096b0 | 206 | SET cache_date = $now |
6a488035 TO |
207 | WHERE id IN ( $groupIDs ) |
208 | "; | |
209 | CRM_Core_DAO::executeQuery($sql); | |
210 | } | |
211 | ||
4ebc8d27 | 212 | /** |
712855a3 | 213 | * Function to clear group contact cache |
4ebc8d27 | 214 | * |
712855a3 | 215 | * @param array $groupIDs |
4ebc8d27 | 216 | * |
217 | */ | |
712855a3 MW |
218 | protected static function clearGroupContactCache($groupIDs): void { |
219 | $clearCacheQuery = ' | |
4ebc8d27 | 220 | DELETE g |
221 | FROM civicrm_group_contact_cache g | |
712855a3 | 222 | WHERE g.group_id IN (%1) '; |
be2fb01f | 223 | $params = [ |
712855a3 | 224 | 1 => [implode(',', $groupIDs), 'CommaSeparatedIntegers'], |
be2fb01f | 225 | ]; |
712855a3 | 226 | CRM_Core_DAO::executeQuery($clearCacheQuery, $params); |
6a488035 TO |
227 | } |
228 | ||
801bafd7 | 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 | */ | |
2b68a50c | 237 | protected static function flushCaches() { |
1937cd69 MW |
238 | if (!CRM_Core_Config::isPermitCacheFlushMode()) { |
239 | return; | |
240 | } | |
241 | ||
cc7b1cd9 | 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. | |
801bafd7 | 247 | return; |
248 | } | |
1104f8db MW |
249 | |
250 | // Get the list of expired smart groups that may need flushing | |
be2fb01f | 251 | $params = [1 => [self::getCacheInvalidDateTime(), 'String']]; |
1104f8db MW |
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); | |
73d446b1 MW |
254 | $expiredGroups = []; |
255 | while ($groupsDAO->fetch()) { | |
256 | $expiredGroups[] = $groupsDAO->id; | |
257 | } | |
1104f8db MW |
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']]); | |
73d446b1 MW |
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'. | |
1104f8db | 274 | CRM_Core_DAO::executeQuery("UPDATE civicrm_group SET cache_date = NULL WHERE id IN (%1)", [1 => [$expiredGroupsCSV, 'CommaSeparatedIntegers']]); |
73d446b1 | 275 | } |
cc7b1cd9 | 276 | $lock->release(); |
801bafd7 | 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. | |
cc7b1cd9 | 287 | * |
288 | * @return \Civi\Core\Lock\LockInterface | |
289 | * @throws \CRM_Core_Exception | |
801bafd7 | 290 | */ |
cc7b1cd9 | 291 | protected static function getLockForRefresh() { |
0626851e | 292 | if (!isset(Civi::$statics[__CLASS__]['is_refresh_init'])) { |
be2fb01f | 293 | Civi::$statics[__CLASS__] = ['is_refresh_init' => FALSE]; |
cc7b1cd9 | 294 | } |
295 | ||
296 | if (Civi::$statics[__CLASS__]['is_refresh_init']) { | |
297 | throw new CRM_Core_Exception('A refresh has already run in this process'); | |
801bafd7 | 298 | } |
299 | $lock = Civi::lockManager()->acquire('data.core.group.refresh'); | |
cc7b1cd9 | 300 | if ($lock->isAcquired()) { |
301 | Civi::$statics[__CLASS__]['is_refresh_init'] = TRUE; | |
302 | return $lock; | |
801bafd7 | 303 | } |
cc7b1cd9 | 304 | throw new CRM_Core_Exception('Mysql lock unavailable'); |
801bafd7 | 305 | } |
306 | ||
307 | /** | |
308 | * Do an opportunistic cache refresh if the site is configured for these. | |
309 | * | |
e047612e CB |
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. | |
801bafd7 | 313 | */ |
7876aee8 EM |
314 | public static function opportunisticCacheFlush(): void { |
315 | if (Civi::settings()->get('smart_group_cache_refresh_mode') === 'opportunistic') { | |
2b68a50c | 316 | self::flushCaches(); |
801bafd7 | 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. | |
801bafd7 | 324 | */ |
2b68a50c | 325 | public static function deterministicCacheFlush() { |
801bafd7 | 326 | if (self::smartGroupCacheTimeout() == 0) { |
327 | CRM_Core_DAO::executeQuery("TRUNCATE civicrm_group_contact_cache"); | |
826096b0 | 328 | CRM_Core_DAO::executeQuery("UPDATE civicrm_group SET cache_date = NULL"); |
801bafd7 | 329 | } |
330 | else { | |
2b68a50c | 331 | self::flushCaches(); |
801bafd7 | 332 | } |
333 | } | |
334 | ||
2c6bbd06 | 335 | /** |
adf28ffd | 336 | * Remove one or more contacts from the smart group cache. |
337 | * | |
2c6bbd06 | 338 | * @param int|array $cid |
7876aee8 | 339 | * @param null $groupId |
adf28ffd | 340 | * |
4eeb9a5b TO |
341 | * @return bool |
342 | * TRUE if successful. | |
7876aee8 | 343 | * @throws \CRM_Core_Exception |
2c6bbd06 | 344 | */ |
00be9182 | 345 | public static function removeContact($cid, $groupId = NULL) { |
be2fb01f | 346 | $cids = []; |
2c6bbd06 CW |
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 | ||
6a488035 | 363 | /** |
fe482240 | 364 | * Load the smart group cache for a saved search. |
abdb2607 | 365 | * |
77c5b619 TO |
366 | * @param object $group |
367 | * The smart group that needs to be loaded. | |
368 | * @param bool $force | |
4f39b1e6 | 369 | * deprecated parameter = Should we force a search through. |
409e43e5 | 370 | * |
43a96367 | 371 | * @throws \API_Exception |
409e43e5 | 372 | * @throws \CRM_Core_Exception |
43a96367 | 373 | * @throws \CiviCRM_API3_Exception |
6a488035 | 374 | */ |
43a96367 | 375 | public static function load($group, $force = FALSE) { |
376 | $groupID = (int) $group->id; | |
4f39b1e6 EM |
377 | if ($force) { |
378 | CRM_Core_Error::deprecatedWarning('use invalidate group contact cache first.'); | |
379 | self::invalidateGroupContactCache($group->id); | |
6a488035 | 380 | } |
cc13551d | 381 | |
0a0b59bd EM |
382 | $lockedGroups = self::getLocksForRefreshableGroupsTo([$groupID]); |
383 | foreach ($lockedGroups as $groupID) { | |
40d64921 EM |
384 | $groupContactsTempTable = CRM_Utils_SQL_TempTable::build() |
385 | ->setCategory('gccache') | |
386 | ->setMemory(); | |
387 | self::buildGroupContactTempTable([$groupID], $groupContactsTempTable); | |
712855a3 | 388 | self::clearGroupContactCache([$groupID]); |
73b8877b | 389 | self::updateCacheFromTempTable($groupContactsTempTable, [$groupID]); |
0a0b59bd | 390 | self::releaseGroupLocks([$groupID]); |
40d64921 EM |
391 | } |
392 | } | |
34e7abb6 | 393 | |
40d64921 EM |
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) { | |
0a0b59bd EM |
410 | if (self::getGroupLock($groupID)) { |
411 | $locks[] = $groupID; | |
40d64921 EM |
412 | } |
413 | } | |
414 | return $locks; | |
6a488035 TO |
415 | } |
416 | ||
86538308 | 417 | /** |
adf28ffd | 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 | * | |
86538308 EM |
424 | * @return int |
425 | */ | |
be7eed4c | 426 | protected static function smartGroupCacheTimeout() { |
6a488035 TO |
427 | $config = CRM_Core_Config::singleton(); |
428 | ||
429 | if ( | |
430 | isset($config->smartGroupCacheTimeout) && | |
9bdcddd7 | 431 | is_numeric($config->smartGroupCacheTimeout) |
353ffa53 | 432 | ) { |
6a488035 TO |
433 | return $config->smartGroupCacheTimeout; |
434 | } | |
435 | ||
adf28ffd | 436 | // Default to 5 minutes. |
6a488035 TO |
437 | return 5; |
438 | } | |
439 | ||
9be31c7a | 440 | /** |
fe482240 | 441 | * Get all the smart groups that this contact belongs to. |
adf28ffd | 442 | * |
9be31c7a DL |
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 | * | |
77c5b619 | 446 | * @param int $contactID |
9be31c7a | 447 | * |
a6c01b45 CW |
448 | * @return array |
449 | * an array of groups that this contact belongs to | |
9be31c7a | 450 | */ |
2b549d90 | 451 | public static function contactGroup(int $contactID): array { |
6a488035 TO |
452 | |
453 | self::loadAll(); | |
454 | ||
6a488035 TO |
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 | |
2b549d90 | 459 | WHERE gc.contact_id = $contactID |
460 | AND (g.is_hidden = 0 OR g.is_hidden IS NULL) | |
6a488035 TO |
461 | ORDER BY gc.contact_id, g.children |
462 | "; | |
463 | ||
464 | $dao = CRM_Core_DAO::executeQuery($sql); | |
be2fb01f | 465 | $contactGroup = []; |
e60f24eb | 466 | $prevContactID = NULL; |
6a488035 TO |
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)) { | |
5396af74 | 476 | $contactGroup[$dao->contact_id] |
be2fb01f | 477 | = ['group' => [], 'groupTitle' => []]; |
6a488035 TO |
478 | } |
479 | ||
5396af74 | 480 | $contactGroup[$dao->contact_id]['group'][] |
be2fb01f | 481 | = [ |
6a488035 TO |
482 | 'id' => $dao->group_id, |
483 | 'title' => $dao->title, | |
484 | 'description' => $dao->description, | |
21dfd5f5 | 485 | 'children' => $dao->children, |
be2fb01f | 486 | ]; |
6a488035 TO |
487 | $contactGroup[$dao->contact_id]['groupTitle'][] = $dao->title; |
488 | } | |
489 | ||
490 | if ($prevContactID) { | |
491 | $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']); | |
492 | } | |
493 | ||
2b549d90 | 494 | if ((!empty($contactGroup[$contactID]))) { |
6a488035 TO |
495 | return $contactGroup[$contactID]; |
496 | } | |
2b549d90 | 497 | return $contactGroup; |
6a488035 TO |
498 | } |
499 | ||
4052239b | 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 | */ | |
7876aee8 | 508 | public static function getCacheInvalidDateTime(): string { |
8b6074a7 | 509 | return date('YmdHis', strtotime("-" . self::smartGroupCacheTimeout() . " Minutes")); |
4052239b | 510 | } |
511 | ||
dadeae7d SL |
512 | /** |
513 | * Invalidates the smart group cache for a particular group | |
514 | * @param int $groupID - Group to invalidate | |
515 | */ | |
7876aee8 | 516 | public static function invalidateGroupContactCache($groupID): void { |
1b29c899 | 517 | CRM_Core_DAO::executeQuery('UPDATE civicrm_group |
826096b0 | 518 | SET cache_date = NULL |
1b29c899 | 519 | WHERE id = %1 AND (saved_search_id IS NOT NULL OR children IS NOT NULL)', [ |
dadeae7d SL |
520 | 1 => [$groupID, 'Positive'], |
521 | ]); | |
522 | } | |
523 | ||
4e97c268 | 524 | /** |
4e97c268 | 525 | * @param array $savedSearch |
1b29c899 EM |
526 | * @param int $groupID |
527 | * | |
f29d74c4 | 528 | * @return string |
4e97c268 CW |
529 | * @throws API_Exception |
530 | * @throws \Civi\API\Exception\NotImplementedException | |
531 | * @throws CRM_Core_Exception | |
532 | */ | |
1b29c899 EM |
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 | ||
48102254 | 540 | $apiParams = $savedSearch['api_params'] + ['select' => ['id'], 'checkPermissions' => FALSE]; |
23e56526 CW |
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 | } | |
3b8b7581 CW |
546 | // Order is irrelevant unless using limit or offset |
547 | if (empty($apiParams['limit']) && empty($apiParams['offset'])) { | |
548 | unset($apiParams['orderBy']); | |
549 | } | |
7876aee8 EM |
550 | /* @var $api \Civi\Api4\Generic\DAOGetAction */ |
551 | $api = Request::create($savedSearch['api_entity'], 'get', $apiParams); | |
552 | $query = new Api4SelectQuery($api); | |
48102254 | 553 | $query->forceSelectId = FALSE; |
19fde02c | 554 | $query->getQuery()->having("$idField $excludeClause"); |
23e56526 CW |
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"; | |
4e97c268 CW |
558 | } |
559 | ||
409e43e5 | 560 | /** |
561 | * Get sql from a custom search. | |
562 | * | |
48102254 CW |
563 | * We split it up and store custom class |
564 | * so temp tables are not destroyed if they are used | |
565 | * | |
c3f7cb2f | 566 | * @param array $savedSearch |
1b29c899 | 567 | * @param int $groupID |
409e43e5 | 568 | * |
f29d74c4 | 569 | * @return string |
1b29c899 EM |
570 | * @throws \CRM_Core_Exception |
571 | * @throws \CiviCRM_API3_Exception | |
409e43e5 | 572 | */ |
1b29c899 EM |
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"; | |
c3f7cb2f EM |
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 | } | |
f29d74c4 | 585 | $searchSQL = CRM_Contact_BAO_SearchCustom::customClass($ssParams['customSearchID'], $savedSearchID)->contactIDs(); |
409e43e5 | 586 | $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL); |
587 | if (strpos($searchSQL, 'WHERE') === FALSE) { | |
f29d74c4 | 588 | $searchSQL .= " WHERE contact_a.id $excludeClause"; |
409e43e5 | 589 | } |
f29d74c4 CW |
590 | else { |
591 | $searchSQL .= " AND contact_a.id $excludeClause"; | |
592 | } | |
593 | return preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $searchSQL); | |
409e43e5 | 594 | } |
595 | ||
596 | /** | |
597 | * Get array of sql from a saved query object group. | |
598 | * | |
c3f7cb2f | 599 | * @param array $savedSearch |
1b29c899 | 600 | * @param int $groupID |
409e43e5 | 601 | * |
f29d74c4 | 602 | * @return string |
409e43e5 | 603 | * @throws \CRM_Core_Exception |
604 | * @throws \CiviCRM_API3_Exception | |
605 | */ | |
1b29c899 EM |
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"; | |
c3f7cb2f EM |
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 | ||
409e43e5 | 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, | |
f29d74c4 CW |
646 | $formValues['display_relationship_type'] ?? NULL, |
647 | $formValues['operator'] ?? 'AND' | |
409e43e5 | 648 | ); |
649 | $query->_useDistinct = FALSE; | |
650 | $query->_useGroupBy = FALSE; | |
651 | $sqlParts = $query->getSearchSQLParts( | |
652 | 0, 0, NULL, | |
653 | FALSE, FALSE, | |
f29d74c4 CW |
654 | FALSE, TRUE, |
655 | "contact_a.id $excludeClause" | |
409e43e5 | 656 | ); |
f29d74c4 CW |
657 | $select = preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $sqlParts['select']); |
658 | ||
659 | return "$select {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['group_by']} {$sqlParts['having']}"; | |
409e43e5 | 660 | } |
661 | ||
43a96367 | 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 { | |
5949be80 EM |
675 | $groups = Group::get(FALSE)->addWhere('id', 'IN', $groupIDs) |
676 | ->setSelect(['saved_search_id', 'children', 'id'])->execute(); | |
43a96367 | 677 | $tempTableName = $tempTableObject->getName(); |
678 | $tempTableObject->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)'); | |
5949be80 EM |
679 | foreach ($groups as $group) { |
680 | self::insertGroupContactsIntoTempTable($tempTableName, $group['id'], $group['saved_search_id'], $group['children']); | |
43a96367 | 681 | } |
682 | } | |
683 | ||
7afacd0f | 684 | /** |
97399fa1 | 685 | * [Internal core function] Populate a temporary table with group ids and contact ids. |
7afacd0f EM |
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 | * | |
97399fa1 EM |
692 | * @throws \API_Exception |
693 | * @throws \CRM_Core_Exception | |
7afacd0f EM |
694 | * @throws \CiviCRM_API3_Exception |
695 | */ | |
696 | public static function populateTemporaryTableWithContactsInGroups(array $groupIDs, string $temporaryTable): void { | |
c6ffa626 | 697 | $childAndParentGroupIDs = array_merge($groupIDs, CRM_Contact_BAO_GroupNesting::getDescendentGroupIds($groupIDs)); |
7afacd0f EM |
698 | $groups = civicrm_api3('Group', 'get', [ |
699 | 'is_active' => 1, | |
c6ffa626 | 700 | 'id' => ['IN' => $childAndParentGroupIDs], |
7afacd0f EM |
701 | 'saved_search_id' => ['>' => 0], |
702 | 'return' => 'id', | |
703 | ]); | |
704 | $smartGroups = array_keys($groups['values']); | |
705 | ||
97399fa1 | 706 | $query = ' |
7afacd0f EM |
707 | SELECT DISTINCT group_contact.contact_id as contact_id |
708 | FROM civicrm_group_contact group_contact | |
97399fa1 | 709 | WHERE group_contact.group_id IN (' . implode(', ', $childAndParentGroupIDs) . ") |
7afacd0f EM |
710 | AND group_contact.status = 'Added' "; |
711 | ||
712 | if (!empty($smartGroups)) { | |
97399fa1 EM |
713 | $groupContactsTempTable = CRM_Utils_SQL_TempTable::build() |
714 | ->setCategory('gccache') | |
715 | ->setMemory(); | |
0a0b59bd EM |
716 | $lockedGroups = self::getLocksForRefreshableGroupsTo($smartGroups); |
717 | if (!empty($lockedGroups)) { | |
718 | self::buildGroupContactTempTable($lockedGroups, $groupContactsTempTable); | |
97399fa1 EM |
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. | |
712855a3 | 732 | self::clearGroupContactCache($lockedGroups); |
0a0b59bd EM |
733 | self::updateCacheFromTempTable($groupContactsTempTable, $lockedGroups); |
734 | self::releaseGroupLocks($lockedGroups); | |
97399fa1 EM |
735 | } |
736 | ||
7afacd0f EM |
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 | ||
b52cbe71 | 747 | /** |
748 | * @param array|null $groupIDs | |
749 | * @param int $limit | |
750 | * | |
751 | * @return array | |
752 | */ | |
40d64921 | 753 | protected static function getGroupsNeedingRefreshing(?array $groupIDs, int $limit = 0): array { |
b52cbe71 | 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 | ||
40d64921 | 765 | $query = self::groupRefreshedClause($groupIDClause, !empty($groupIDs)); |
b52cbe71 | 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 | ||
73b8877b EM |
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 | ||
73b8877b EM |
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 | ||
5949be80 EM |
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) { | |
d96f7984 EM |
823 | $savedSearch = SavedSearch::get(FALSE) |
824 | ->addWhere('id', '=', $savedSearchID) | |
c3f7cb2f | 825 | ->addSelect('*') |
d96f7984 EM |
826 | ->execute() |
827 | ->first(); | |
5949be80 | 828 | |
c3f7cb2f | 829 | if ($savedSearch['api_entity']) { |
1b29c899 EM |
830 | $sql = self::getApiSQL($savedSearch, $groupID); |
831 | } | |
832 | elseif (!empty($savedSearch['form_values']['customSearchID'])) { | |
833 | $sql = self::getCustomSearchSQL($savedSearch, $groupID); | |
5949be80 EM |
834 | } |
835 | else { | |
1b29c899 | 836 | $sql = self::getQueryObjectSQL($savedSearch, $groupID); |
5949be80 EM |
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 | ||
5949be80 EM |
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 | ||
0a0b59bd EM |
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 | ||
6a488035 | 929 | } |