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