| 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 | $customClass = NULL; |
| 461 | if ($savedSearchID) { |
| 462 | $ssParams = CRM_Contact_BAO_SavedSearch::getSearchParams($savedSearchID); |
| 463 | $groupID = CRM_Utils_Type::escape($groupID, 'Integer'); |
| 464 | |
| 465 | $excludeClause = "NOT IN ( |
| 466 | SELECT contact_id FROM civicrm_group_contact |
| 467 | WHERE civicrm_group_contact.status = 'Removed' |
| 468 | AND civicrm_group_contact.group_id = $groupID )"; |
| 469 | $addSelect = "$groupID AS group_id"; |
| 470 | |
| 471 | if (!empty($ssParams['api_entity'])) { |
| 472 | $sql = self::getApiSQL($ssParams, $addSelect, $excludeClause); |
| 473 | } |
| 474 | else { |
| 475 | // CRM-7021 rectify params to what proximity search expects if there is a value for prox_distance |
| 476 | if (!empty($ssParams)) { |
| 477 | CRM_Contact_BAO_ProximityQuery::fixInputParams($ssParams); |
| 478 | } |
| 479 | if (isset($ssParams['customSearchID'])) { |
| 480 | $sql = self::getCustomSearchSQL($savedSearchID, $ssParams, $addSelect, $excludeClause); |
| 481 | } |
| 482 | else { |
| 483 | $sql = self::getQueryObjectSQL($savedSearchID, $ssParams, $addSelect, $excludeClause); |
| 484 | } |
| 485 | } |
| 486 | } |
| 487 | |
| 488 | $groupContactsTempTable = CRM_Utils_SQL_TempTable::build()->setCategory('gccache')->setMemory(); |
| 489 | $tempTable = $groupContactsTempTable->getName(); |
| 490 | $groupContactsTempTable->createWithColumns('contact_id int, group_id int, UNIQUE UI_contact_group (contact_id,group_id)'); |
| 491 | |
| 492 | $contactQueries[] = $sql; |
| 493 | // lets also store the records that are explicitly added to the group |
| 494 | // this allows us to skip the group contact LEFT JOIN |
| 495 | $contactQueries[] = |
| 496 | "SELECT $groupID as group_id, contact_id as contact_id |
| 497 | FROM civicrm_group_contact |
| 498 | WHERE civicrm_group_contact.status = 'Added' AND civicrm_group_contact.group_id = $groupID "; |
| 499 | |
| 500 | self::clearGroupContactCache($groupID); |
| 501 | |
| 502 | foreach ($contactQueries as $contactQuery) { |
| 503 | CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) {$contactQuery}"); |
| 504 | } |
| 505 | |
| 506 | if ($group->children) { |
| 507 | |
| 508 | // Store a list of contacts who are removed from the parent group |
| 509 | $sqlContactsRemovedFromGroup = " |
| 510 | SELECT contact_id |
| 511 | FROM civicrm_group_contact |
| 512 | WHERE civicrm_group_contact.status = 'Removed' |
| 513 | AND civicrm_group_contact.group_id = $groupID "; |
| 514 | $dao = CRM_Core_DAO::executeQuery($sqlContactsRemovedFromGroup); |
| 515 | $removed_contacts = []; |
| 516 | while ($dao->fetch()) { |
| 517 | $removed_contacts[] = $dao->contact_id; |
| 518 | } |
| 519 | |
| 520 | $childrenIDs = explode(',', $group->children); |
| 521 | foreach ($childrenIDs as $childID) { |
| 522 | $contactIDs = CRM_Contact_BAO_Group::getMember($childID, FALSE); |
| 523 | // Unset each contact that is removed from the parent group |
| 524 | foreach ($removed_contacts as $removed_contact) { |
| 525 | unset($contactIDs[$removed_contact]); |
| 526 | } |
| 527 | if (empty($contactIDs)) { |
| 528 | // This child group has no contact IDs so we don't need to add them to |
| 529 | continue; |
| 530 | } |
| 531 | $values = []; |
| 532 | foreach ($contactIDs as $contactID => $dontCare) { |
| 533 | $values[] = "({$groupID},{$contactID})"; |
| 534 | } |
| 535 | $str = implode(',', $values); |
| 536 | CRM_Core_DAO::executeQuery("INSERT IGNORE INTO $tempTable (group_id, contact_id) VALUES $str"); |
| 537 | } |
| 538 | } |
| 539 | |
| 540 | // grab a lock so other processes don't compete and do the same query |
| 541 | $lock = Civi::lockManager()->acquire("data.core.group.{$groupID}"); |
| 542 | if (!$lock->isAcquired()) { |
| 543 | // this can cause inconsistent results since we don't know if the other process |
| 544 | // will fill up the cache before our calling routine needs it. |
| 545 | // however this routine does not return the status either, so basically |
| 546 | // its a "lets return and hope for the best" |
| 547 | return; |
| 548 | } |
| 549 | |
| 550 | // Don't call clearGroupContactCache as we don't want to clear the cache dates |
| 551 | // The will get updated by updateCacheTime() below and not clearing the dates reduces |
| 552 | // the chance that loadAll() will try and rebuild at the same time. |
| 553 | $clearCacheQuery = " |
| 554 | DELETE g |
| 555 | FROM civicrm_group_contact_cache g |
| 556 | WHERE g.group_id = %1 "; |
| 557 | $params = [ |
| 558 | 1 => [$groupID, 'Integer'], |
| 559 | ]; |
| 560 | CRM_Core_DAO::executeQuery($clearCacheQuery, $params); |
| 561 | |
| 562 | CRM_Core_DAO::executeQuery( |
| 563 | "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) |
| 564 | SELECT DISTINCT contact_id, group_id FROM $tempTable |
| 565 | "); |
| 566 | $groupContactsTempTable->drop(); |
| 567 | self::updateCacheTime([$groupID], TRUE); |
| 568 | |
| 569 | $lock->release(); |
| 570 | } |
| 571 | |
| 572 | /** |
| 573 | * Retrieve the smart group cache timeout in minutes. |
| 574 | * |
| 575 | * This checks if a timeout has been configured. If one has then smart groups should not |
| 576 | * be refreshed more frequently than the time out. If a group was recently refreshed it should not |
| 577 | * refresh again within that period. |
| 578 | * |
| 579 | * @return int |
| 580 | */ |
| 581 | public static function smartGroupCacheTimeout() { |
| 582 | $config = CRM_Core_Config::singleton(); |
| 583 | |
| 584 | if ( |
| 585 | isset($config->smartGroupCacheTimeout) && |
| 586 | is_numeric($config->smartGroupCacheTimeout) |
| 587 | ) { |
| 588 | return $config->smartGroupCacheTimeout; |
| 589 | } |
| 590 | |
| 591 | // Default to 5 minutes. |
| 592 | return 5; |
| 593 | } |
| 594 | |
| 595 | /** |
| 596 | * Get all the smart groups that this contact belongs to. |
| 597 | * |
| 598 | * Note that this could potentially be a super slow function since |
| 599 | * it ensure that all contact groups are loaded in the cache |
| 600 | * |
| 601 | * @param int $contactID |
| 602 | * @param bool $showHidden |
| 603 | * Hidden groups are shown only if this flag is set. |
| 604 | * |
| 605 | * @return array |
| 606 | * an array of groups that this contact belongs to |
| 607 | */ |
| 608 | public static function contactGroup($contactID, $showHidden = FALSE) { |
| 609 | if (empty($contactID)) { |
| 610 | return NULL; |
| 611 | } |
| 612 | |
| 613 | if (is_array($contactID)) { |
| 614 | $contactIDs = $contactID; |
| 615 | } |
| 616 | else { |
| 617 | $contactIDs = [$contactID]; |
| 618 | } |
| 619 | |
| 620 | self::loadAll(); |
| 621 | |
| 622 | $hiddenClause = ''; |
| 623 | if (!$showHidden) { |
| 624 | $hiddenClause = ' AND (g.is_hidden = 0 OR g.is_hidden IS NULL) '; |
| 625 | } |
| 626 | |
| 627 | $contactIDString = CRM_Core_DAO::escapeString(implode(', ', $contactIDs)); |
| 628 | $sql = " |
| 629 | SELECT gc.group_id, gc.contact_id, g.title, g.children, g.description |
| 630 | FROM civicrm_group_contact_cache gc |
| 631 | INNER JOIN civicrm_group g ON g.id = gc.group_id |
| 632 | WHERE gc.contact_id IN ($contactIDString) |
| 633 | $hiddenClause |
| 634 | ORDER BY gc.contact_id, g.children |
| 635 | "; |
| 636 | |
| 637 | $dao = CRM_Core_DAO::executeQuery($sql); |
| 638 | $contactGroup = []; |
| 639 | $prevContactID = NULL; |
| 640 | while ($dao->fetch()) { |
| 641 | if ( |
| 642 | $prevContactID && |
| 643 | $prevContactID != $dao->contact_id |
| 644 | ) { |
| 645 | $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']); |
| 646 | } |
| 647 | $prevContactID = $dao->contact_id; |
| 648 | if (!array_key_exists($dao->contact_id, $contactGroup)) { |
| 649 | $contactGroup[$dao->contact_id] |
| 650 | = ['group' => [], 'groupTitle' => []]; |
| 651 | } |
| 652 | |
| 653 | $contactGroup[$dao->contact_id]['group'][] |
| 654 | = [ |
| 655 | 'id' => $dao->group_id, |
| 656 | 'title' => $dao->title, |
| 657 | 'description' => $dao->description, |
| 658 | 'children' => $dao->children, |
| 659 | ]; |
| 660 | $contactGroup[$dao->contact_id]['groupTitle'][] = $dao->title; |
| 661 | } |
| 662 | |
| 663 | if ($prevContactID) { |
| 664 | $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']); |
| 665 | } |
| 666 | |
| 667 | if ((!empty($contactGroup[$contactID]) && is_numeric($contactID))) { |
| 668 | return $contactGroup[$contactID]; |
| 669 | } |
| 670 | else { |
| 671 | return $contactGroup; |
| 672 | } |
| 673 | } |
| 674 | |
| 675 | /** |
| 676 | * Get the datetime from which the cache should be considered invalid. |
| 677 | * |
| 678 | * Ie if the smartgroup cache timeout is 5 minutes ago then the cache is invalid if it was |
| 679 | * refreshed 6 minutes ago, but not if it was refreshed 4 minutes ago. |
| 680 | * |
| 681 | * @return string |
| 682 | */ |
| 683 | public static function getCacheInvalidDateTime() { |
| 684 | return date('YmdHis', strtotime("-" . self::smartGroupCacheTimeout() . " Minutes")); |
| 685 | } |
| 686 | |
| 687 | /** |
| 688 | * Get the date when the cache should be refreshed from. |
| 689 | * |
| 690 | * Ie. now + the offset & we will delete anything prior to then. |
| 691 | * |
| 692 | * @return string |
| 693 | */ |
| 694 | public static function getRefreshDateTime() { |
| 695 | return date('YmdHis', strtotime("+ " . self::smartGroupCacheTimeout() . " Minutes")); |
| 696 | } |
| 697 | |
| 698 | /** |
| 699 | * Invalidates the smart group cache for a particular group |
| 700 | * @param int $groupID - Group to invalidate |
| 701 | */ |
| 702 | public static function invalidateGroupContactCache($groupID) { |
| 703 | CRM_Core_DAO::executeQuery("UPDATE civicrm_group |
| 704 | SET cache_date = NULL, refresh_date = NULL |
| 705 | WHERE id = %1", [ |
| 706 | 1 => [$groupID, 'Positive'], |
| 707 | ]); |
| 708 | } |
| 709 | |
| 710 | /** |
| 711 | * @param array $savedSearch |
| 712 | * @param string $addSelect |
| 713 | * @param string $excludeClause |
| 714 | * @return string |
| 715 | * @throws API_Exception |
| 716 | * @throws \Civi\API\Exception\NotImplementedException |
| 717 | * @throws CRM_Core_Exception |
| 718 | */ |
| 719 | protected static function getApiSQL(array $savedSearch, string $addSelect, string $excludeClause) { |
| 720 | $apiParams = $savedSearch['api_params'] + ['select' => ['id'], 'checkPermissions' => FALSE]; |
| 721 | list($idField) = explode(' AS ', $apiParams['select'][0]); |
| 722 | $apiParams['select'] = [ |
| 723 | $addSelect, |
| 724 | $idField . ' AS smart_group_contact_id', |
| 725 | ]; |
| 726 | $api = \Civi\API\Request::create($savedSearch['api_entity'], 'get', $apiParams); |
| 727 | $query = new \Civi\Api4\Query\Api4SelectQuery($api); |
| 728 | $query->forceSelectId = FALSE; |
| 729 | $query->getQuery()->having('smart_group_contact_id ' . $excludeClause); |
| 730 | return $query->getSql(); |
| 731 | } |
| 732 | |
| 733 | /** |
| 734 | * Get sql from a custom search. |
| 735 | * |
| 736 | * We split it up and store custom class |
| 737 | * so temp tables are not destroyed if they are used |
| 738 | * |
| 739 | * @param int $savedSearchID |
| 740 | * @param array $ssParams |
| 741 | * @param string $addSelect |
| 742 | * @param string $excludeClause |
| 743 | * |
| 744 | * @return string |
| 745 | * @throws \Exception |
| 746 | */ |
| 747 | protected static function getCustomSearchSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) { |
| 748 | $searchSQL = CRM_Contact_BAO_SearchCustom::customClass($ssParams['customSearchID'], $savedSearchID)->contactIDs(); |
| 749 | $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL); |
| 750 | if (strpos($searchSQL, 'WHERE') === FALSE) { |
| 751 | $searchSQL .= " WHERE contact_a.id $excludeClause"; |
| 752 | } |
| 753 | else { |
| 754 | $searchSQL .= " AND contact_a.id $excludeClause"; |
| 755 | } |
| 756 | return preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $searchSQL); |
| 757 | } |
| 758 | |
| 759 | /** |
| 760 | * Get array of sql from a saved query object group. |
| 761 | * |
| 762 | * @param int $savedSearchID |
| 763 | * @param array $ssParams |
| 764 | * @param string $addSelect |
| 765 | * @param string $excludeClause |
| 766 | * |
| 767 | * @return string |
| 768 | * @throws \CRM_Core_Exception |
| 769 | * @throws \CiviCRM_API3_Exception |
| 770 | */ |
| 771 | protected static function getQueryObjectSQL($savedSearchID, array $ssParams, string $addSelect, string $excludeClause) { |
| 772 | $returnProperties = NULL; |
| 773 | if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $savedSearchID, 'mapping_id')) { |
| 774 | $fv = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID); |
| 775 | $returnProperties = CRM_Core_BAO_Mapping::returnProperties($fv); |
| 776 | } |
| 777 | $formValues = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID); |
| 778 | // CRM-17075 using the formValues in this way imposes extra logic and complexity. |
| 779 | // we have the where_clause and where tables stored in the saved_search table |
| 780 | // and should use these rather than re-processing the form criteria (which over-works |
| 781 | // the link between the form layer & the query layer too). |
| 782 | // It's hard to think of when you would want to use anything other than return |
| 783 | // properties = array('contact_id' => 1) here as the point would appear to be to |
| 784 | // generate the list of contact ids in the group. |
| 785 | // @todo review this to use values in saved_search table (preferably for 4.8). |
| 786 | $query |
| 787 | = new CRM_Contact_BAO_Query( |
| 788 | $ssParams, $returnProperties, NULL, |
| 789 | FALSE, FALSE, 1, |
| 790 | TRUE, TRUE, |
| 791 | FALSE, |
| 792 | $formValues['display_relationship_type'] ?? NULL, |
| 793 | $formValues['operator'] ?? 'AND' |
| 794 | ); |
| 795 | $query->_useDistinct = FALSE; |
| 796 | $query->_useGroupBy = FALSE; |
| 797 | $sqlParts = $query->getSearchSQLParts( |
| 798 | 0, 0, NULL, |
| 799 | FALSE, FALSE, |
| 800 | FALSE, TRUE, |
| 801 | "contact_a.id $excludeClause" |
| 802 | ); |
| 803 | $select = preg_replace("/^\s*SELECT /", "SELECT $addSelect, ", $sqlParts['select']); |
| 804 | |
| 805 | return "$select {$sqlParts['from']} {$sqlParts['where']} {$sqlParts['group_by']} {$sqlParts['having']}"; |
| 806 | } |
| 807 | |
| 808 | } |