CRM/Contact/BAO/GroupContactCache.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_BAO_GroupContactCache extends CRM_Contact_DAO_GroupContactCache {
  36
  37   static $_alreadyLoaded = array();
  38
  39   /**
  40    * Check to see if we have cache entries for this group
  41    * if not, regenerate, else return
  42    *
  43    * @param $groupIDs
  44    *
  45    * @internal param int $groupID groupID of group that we are checking against
  46    *
  47    * @return boolean true if we did not regenerate, false if we did
  48    */
  49   static function check($groupIDs) {
  50     if (empty($groupIDs)) {
  51       return TRUE;
  52     }
  53
  54     return self::loadAll($groupIDs);
  55   }
  56
  57   /**
  58    * Common function that formulates the query to see which groups needs to be refreshed
  59    * based on their cache date and the smartGroupCacheTimeOut
  60    *
  61    * @param string $groupIDClause the clause which limits which groups we need to evaluate
  62    * @param boolean $includeHiddenGroups hidden groups are excluded by default
  63    *
  64    * @return string the sql query which lists the groups that need to be refreshed
  65    * @static
  66    * @public
  67    */
  68   static function groupRefreshedClause($groupIDClause = null, $includeHiddenGroups = FALSE) {
  69     $smartGroupCacheTimeout = self::smartGroupCacheTimeout();
  70     $now = CRM_Utils_Date::getUTCTime();
  71
  72     $query = "
  73 SELECT  g.id
  74 FROM    civicrm_group g
  75 WHERE   ( g.saved_search_id IS NOT NULL OR g.children IS NOT NULL )
  76 AND     g.is_active = 1
  77 AND     ( g.cache_date IS NULL OR
  78           ( TIMESTAMPDIFF(MINUTE, g.cache_date, $now) >= $smartGroupCacheTimeout ) OR
  79           ( $now >= g.refresh_date )
  80         )
  81 ";
  82
  83     if (!$includeHiddenGroups) {
  84       $query .= "AND (g.is_hidden = 0 OR g.is_hidden IS NULL)";
  85     }
  86
  87     if (!empty($groupIDClause)) {
  88       $query .= " AND ( $groupIDClause ) ";
  89     }
  90
  91     return $query;
  92   }
  93
  94   /**
  95    * Checks to see if a group has been refreshed recently. This is primarily used
  96    * in a locking scenario when some other process might have refreshed things underneath
  97    * this process
  98    *
  99    * @param int $groupID the group ID
 100    * @param boolean $includeHiddenGroups hidden groups are excluded by default
 101    *
 102    * @return string the sql query which lists the groups that need to be refreshed
 103    * @static
 104    * @public
 105    */
 106   static function shouldGroupBeRefreshed($groupID, $includeHiddenGroups = FALSE) {
 107     $query = self::groupRefreshedClause("g.id = %1", $includeHiddenGroups);
 108     $params = array(1 => array($groupID, 'Integer'));
 109
 110     // if the query returns the group ID, it means the group is a valid candidate for refreshing
 111     return CRM_Core_DAO::singleValueQuery($query, $params);
 112   }
 113
 114   /**
 115    * Check to see if we have cache entries for this group
 116    * if not, regenerate, else return
 117    *
 118    * @param int/array $groupID groupID of group that we are checking against
 119    *                           if empty, all groups are checked
 120    * @param int       $limit   limits the number of groups we evaluate
 121    *
 122    * @return boolean true if we did not regenerate, false if we did
 123    */
 124   static function loadAll($groupIDs = null, $limit = 0) {
 125     // ensure that all the smart groups are loaded
 126     // this function is expensive and should be sparingly used if groupIDs is empty
 127     if (empty($groupIDs)) {
 128       $groupIDClause = null;
 129       $groupIDs = array( );
 130     }
 131     else {
 132       if (!is_array($groupIDs)) {
 133         $groupIDs = array($groupIDs);
 134       }
 135
 136       // note escapeString is a must here and we can't send the imploded value as second arguement to
 137       // the executeQuery(), since that would put single quote around the string and such a string
 138       // of comma separated integers would not work.
 139       $groupIDString = CRM_Core_DAO::escapeString(implode(', ', $groupIDs));
 140
 141       $groupIDClause = "g.id IN ({$groupIDString})";
 142     }
 143
 144     $query = self::groupRefreshedClause($groupIDClause);
 145
 146     $limitClause = $orderClause = NULL;
 147     if ($limit > 0) {
 148       $limitClause = " LIMIT 0, $limit";
 149       $orderClause = " ORDER BY g.cache_date, g.refresh_date";
 150     }
 151     // We ignore hidden groups and disabled groups
 152     $query .= "
 153         $orderClause
 154         $limitClause
 155 ";
 156
 157     $dao = CRM_Core_DAO::executeQuery($query);
 158     $processGroupIDs = array();
 159     $refreshGroupIDs = $groupIDs;
 160     while ($dao->fetch()) {
 161       $processGroupIDs[] = $dao->id;
 162
 163       // remove this id from refreshGroupIDs
 164       foreach ($refreshGroupIDs as $idx => $gid) {
 165         if ($gid == $dao->id) {
 166           unset($refreshGroupIDs[$idx]);
 167           break;
 168         }
 169       }
 170     }
 171
 172     if (!empty($refreshGroupIDs)) {
 173       $refreshGroupIDString = CRM_Core_DAO::escapeString(implode(', ', $refreshGroupIDs));
 174       $time  = CRM_Utils_Date::getUTCTime(self::smartGroupCacheTimeout() * 60);
 175       $query = "
 176 UPDATE civicrm_group g
 177 SET    g.refresh_date = $time
 178 WHERE  g.id IN ( {$refreshGroupIDString} )
 179 AND    g.refresh_date IS NULL
 180 ";
 181       CRM_Core_DAO::executeQuery($query);
 182     }
 183
 184     if (empty($processGroupIDs)) {
 185       return TRUE;
 186     }
 187     else {
 188       self::add($processGroupIDs);
 189       return FALSE;
 190     }
 191   }
 192
 193   /**
 194    * FIXME: This function should not be needed, because the cache table should not be getting truncated
 195    */
 196   static function fillIfEmpty() {
 197     if (!CRM_Core_DAO::singleValueQuery("SELECT COUNT(id) FROM civicrm_group_contact_cache")) {
 198       self::loadAll();
 199     }
 200   }
 201
 202   /**
 203    * @param $groupID
 204    */
 205   static function add($groupID) {
 206     // first delete the current cache
 207     self::remove($groupID);
 208     if (!is_array($groupID)) {
 209       $groupID = array($groupID);
 210     }
 211
 212     $returnProperties = array('contact_id');
 213     foreach ($groupID as $gid) {
 214       $params = array(array('group', 'IN', array($gid => 1), 0, 0));
 215       // the below call updates the cache table as a byproduct of the query
 216       CRM_Contact_BAO_Query::apiQuery($params, $returnProperties, NULL, NULL, 0, 0, FALSE);
 217     }
 218   }
 219
 220   /**
 221    * @param $groupID
 222    * @param $values
 223    */
 224   static function store(&$groupID, &$values) {
 225     $processed = FALSE;
 226
 227     // sort the values so we put group IDs in front and hence optimize
 228     // mysql storage (or so we think) CRM-9493
 229     sort($values);
 230
 231     // to avoid long strings, lets do BULK_INSERT_COUNT values at a time
 232     while (!empty($values)) {
 233       $processed = TRUE;
 234       $input     = array_splice($values, 0, CRM_Core_DAO::BULK_INSERT_COUNT);
 235       $str       = implode(',', $input);
 236       $sql       = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES $str;";
 237       CRM_Core_DAO::executeQuery($sql);
 238     }
 239     self::updateCacheTime($groupID, $processed);
 240   }
 241
 242   /**
 243    * Change the cache_date
 244    *
 245    * @param $groupID array(int)
 246    * @param $processed bool, whether the cache data was recently modified
 247    */
 248   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       //make sure to give original timezone settings again.
 253       $now     = CRM_Utils_Date::getUTCTime();
 254       $refresh = 'null';
 255     }
 256     else {
 257       $now     = 'null';
 258       $refresh = 'null';
 259     }
 260
 261     $groupIDs = implode(',', $groupID);
 262     $sql = "
 263 UPDATE civicrm_group
 264 SET    cache_date = $now, refresh_date = $refresh
 265 WHERE  id IN ( $groupIDs )
 266 ";
 267     CRM_Core_DAO::executeQuery($sql);
 268   }
 269
 270   /**
 271    * Removes all the cache entries pertaining to a specific group
 272    * If no groupID is passed in, removes cache entries for all groups
 273    * Has an optimization to bypass repeated invocations of this function.
 274    * Note that this function is an advisory, i.e. the removal respects the
 275    * cache date, i.e. the removal is not done if the group was recently
 276    * loaded into the cache.
 277    *
 278    * @param $groupID  int the groupID to delete cache entries, NULL for all groups
 279    * @param $onceOnly boolean run the function exactly once for all groups.
 280    *
 281    * @public
 282    * @return void
 283    * @static
 284    */
 285   static function remove($groupID = NULL, $onceOnly = TRUE) {
 286     static $invoked = FALSE;
 287
 288     // typically this needs to happy only once per instance
 289     // this is especially true in import, where we dont need
 290     // to do this all the time
 291     // this optimization is done only when no groupID is passed
 292     // i.e. cache is reset for all groups
 293     if (
 294       $onceOnly &&
 295       $invoked &&
 296       $groupID == NULL
 297     ) {
 298       return;
 299     }
 300
 301     if ($groupID == NULL) {
 302       $invoked = TRUE;
 303     } else if (is_array($groupID)) {
 304       foreach ($groupID as $gid) {
 305         unset(self::$_alreadyLoaded[$gid]);
 306       }
 307     } else if ($groupID && array_key_exists($groupID, self::$_alreadyLoaded)) {
 308       unset(self::$_alreadyLoaded[$groupID]);
 309     }
 310
 311     $refresh = null;
 312     $params  = array();
 313     $smartGroupCacheTimeout = self::smartGroupCacheTimeout();
 314
 315     $now         = CRM_Utils_Date::getUTCTime();
 316     $refreshTime = CRM_Utils_Date::getUTCTime($smartGroupCacheTimeout * 60);
 317
 318     if (!isset($groupID)) {
 319       if ($smartGroupCacheTimeout == 0) {
 320         $query = "
 321 TRUNCATE civicrm_group_contact_cache
 322 ";
 323         $update = "
 324 UPDATE civicrm_group g
 325 SET    cache_date = null,
 326        refresh_date = null
 327 ";
 328       }
 329       else {
 330         $query = "
 331 DELETE     gc
 332 FROM       civicrm_group_contact_cache gc
 333 INNER JOIN civicrm_group g ON g.id = gc.group_id
 334 WHERE      TIMESTAMPDIFF(MINUTE, g.cache_date, $now) >= $smartGroupCacheTimeout
 335 ";
 336         $update = "
 337 UPDATE civicrm_group g
 338 SET    cache_date = null,
 339        refresh_date = null
 340 WHERE  TIMESTAMPDIFF(MINUTE, cache_date, $now) >= $smartGroupCacheTimeout
 341 ";
 342         $refresh = "
 343 UPDATE civicrm_group g
 344 SET    refresh_date = $refreshTime
 345 WHERE  TIMESTAMPDIFF(MINUTE, cache_date, $now) < $smartGroupCacheTimeout
 346 AND    refresh_date IS NULL
 347 ";
 348       }
 349     }
 350     elseif (is_array($groupID)) {
 351       $groupIDs = implode(', ', $groupID);
 352       $query = "
 353 DELETE     g
 354 FROM       civicrm_group_contact_cache g
 355 WHERE      g.group_id IN ( $groupIDs )
 356 ";
 357       $update = "
 358 UPDATE civicrm_group g
 359 SET    cache_date = null,
 360        refresh_date = null
 361 WHERE  id IN ( $groupIDs )
 362 ";
 363     }
 364     else {
 365       $query = "
 366 DELETE     g
 367 FROM       civicrm_group_contact_cache g
 368 WHERE      g.group_id = %1
 369 ";
 370       $update = "
 371 UPDATE civicrm_group g
 372 SET    cache_date = null,
 373        refresh_date = null
 374 WHERE  id = %1
 375 ";
 376       $params = array(1 => array($groupID, 'Integer'));
 377     }
 378
 379     CRM_Core_DAO::executeQuery($query, $params);
 380
 381     if ($refresh) {
 382       CRM_Core_DAO::executeQuery($refresh, $params);
 383     }
 384
 385     // also update the cache_date for these groups
 386     CRM_Core_DAO::executeQuery($update, $params);
 387   }
 388
 389   /**
 390    * load the smart group cache for a saved search
 391    *
 392    * @param object  $group - the smart group that needs to be loaded
 393    * @param boolean $force - should we force a search through
 394    *
 395    */
 396   static function load(&$group, $force = FALSE) {
 397     $groupID = $group->id;
 398     $savedSearchID = $group->saved_search_id;
 399     if (array_key_exists($groupID, self::$_alreadyLoaded) && !$force) {
 400       return;
 401     }
 402
 403     // grab a lock so other processes dont compete and do the same query
 404     $lockName = "civicrm.group.{$groupID}";
 405     $lock = new CRM_Core_Lock($lockName);
 406     if (!$lock->isAcquired()) {
 407       // this can cause inconsistent results since we dont know if the other process
 408       // will fill up the cache before our calling routine needs it.
 409       // however this routine does not return the status either, so basically
 410       // its a "lets return and hope for the best"
 411       return;
 412     }
 413
 414     self::$_alreadyLoaded[$groupID] = 1;
 415
 416     // we now have the lock, but some other proces could have actually done the work
 417     // before we got here, so before we do any work, lets ensure that work needs to be
 418     // done
 419     // we allow hidden groups here since we dont know if the caller wants to evaluate an
 420     // hidden group
 421     if (!$force && !self::shouldGroupBeRefreshed($groupID, TRUE)) {
 422       $lock->release();
 423       return;
 424     }
 425
 426     $sql         = NULL;
 427     $idName      = 'id';
 428     $customClass = NULL;
 429     if ($savedSearchID) {
 430       $ssParams = CRM_Contact_BAO_SavedSearch::getSearchParams($savedSearchID);
 431
 432       // rectify params to what proximity search expects if there is a value for prox_distance
 433       // CRM-7021
 434       if (!empty($ssParams)) {
 435         CRM_Contact_BAO_ProximityQuery::fixInputParams($ssParams);
 436       }
 437
 438
 439       $returnProperties = array();
 440       if (CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_SavedSearch', $savedSearchID, 'mapping_id')) {
 441         $fv = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 442         $returnProperties = CRM_Core_BAO_Mapping::returnProperties($fv);
 443       }
 444
 445       if (isset($ssParams['customSearchID'])) {
 446         // if custom search
 447
 448         // we split it up and store custom class
 449         // so temp tables are not destroyed if they are used
 450         // hence customClass is defined above at top of function
 451         $customClass =
 452           CRM_Contact_BAO_SearchCustom::customClass($ssParams['customSearchID'], $savedSearchID);
 453         $searchSQL = $customClass->contactIDs();
 454         $searchSQL = str_replace('ORDER BY contact_a.id ASC', '', $searchSQL);
 455         $idName = 'contact_id';
 456       }
 457       else {
 458         $formValues = CRM_Contact_BAO_SavedSearch::getFormValues($savedSearchID);
 459
 460         $query =
 461           new CRM_Contact_BAO_Query(
 462             $ssParams, $returnProperties, NULL,
 463             FALSE, FALSE, 1,
 464             TRUE, TRUE,
 465             FALSE,
 466             CRM_Utils_Array::value('display_relationship_type', $formValues),
 467             CRM_Utils_Array::value('operator', $formValues, 'AND')
 468         );
 469         $query->_useDistinct = FALSE;
 470         $query->_useGroupBy  = FALSE;
 471         $searchSQL           =
 472           $query->searchQuery(
 473             0, 0, NULL,
 474           FALSE, FALSE,
 475           FALSE, TRUE,
 476           TRUE,
 477           NULL, NULL, NULL,
 478           TRUE
 479         );
 480       }
 481       $groupID = CRM_Utils_Type::escape($groupID, 'Integer');
 482       $sql = $searchSQL . " AND contact_a.id NOT IN (
 483                               SELECT contact_id FROM civicrm_group_contact
 484                               WHERE civicrm_group_contact.status = 'Removed'
 485                               AND   civicrm_group_contact.group_id = $groupID ) ";
 486     }
 487
 488     if ($sql) {
 489       $sql = preg_replace("/^\s*SELECT/", "SELECT $groupID as group_id, ", $sql);
 490     }
 491
 492     // lets also store the records that are explicitly added to the group
 493     // this allows us to skip the group contact LEFT JOIN
 494     $sqlB = "
 495 SELECT $groupID as group_id, contact_id as $idName
 496 FROM   civicrm_group_contact
 497 WHERE  civicrm_group_contact.status = 'Added'
 498   AND  civicrm_group_contact.group_id = $groupID ";
 499
 500     $groupIDs = array($groupID);
 501     self::remove($groupIDs);
 502     $processed = FALSE;
 503     $tempTable = 'civicrm_temp_group_contact_cache' . rand(0,2000);
 504     foreach (array($sql, $sqlB) as $selectSql) {
 505       if (!$selectSql) {
 506         continue;
 507       }
 508       $insertSql = "CREATE TEMPORARY TABLE $tempTable ($selectSql);";
 509       $processed = TRUE;
 510       $result = CRM_Core_DAO::executeQuery($insertSql);
 511       CRM_Core_DAO::executeQuery(
 512         "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
 513         SELECT DISTINCT $idName, group_id FROM $tempTable
 514       ");
 515       CRM_Core_DAO::executeQuery(" DROP TABLE $tempTable");
 516     }
 517
 518     self::updateCacheTime($groupIDs, $processed);
 519
 520     if ($group->children) {
 521
 522       //Store a list of contacts who are removed from the parent group
 523       $sql = "
 524 SELECT contact_id
 525 FROM civicrm_group_contact
 526 WHERE  civicrm_group_contact.status = 'Removed'
 527 AND  civicrm_group_contact.group_id = $groupID ";
 528       $dao = CRM_Core_DAO::executeQuery($sql);
 529       $removed_contacts = array();
 530       while ($dao->fetch()) {
 531         $removed_contacts[] = $dao->contact_id;
 532       }
 533
 534       $childrenIDs = explode(',', $group->children);
 535       foreach ($childrenIDs as $childID) {
 536         $contactIDs = CRM_Contact_BAO_Group::getMember($childID, FALSE);
 537         //Unset each contact that is removed from the parent group
 538         foreach ($removed_contacts as $removed_contact) {
 539           unset($contactIDs[$removed_contact]);
 540         }
 541         $values = array();
 542         foreach ($contactIDs as $contactID => $dontCare) {
 543           $values[] = "({$groupID},{$contactID})";
 544         }
 545
 546         self::store($groupIDs, $values);
 547       }
 548     }
 549
 550     $lock->release();
 551   }
 552
 553   /**
 554    * @return int
 555    */
 556   static function smartGroupCacheTimeout() {
 557     $config = CRM_Core_Config::singleton();
 558
 559     if (
 560       isset($config->smartGroupCacheTimeout) &&
 561       is_numeric($config->smartGroupCacheTimeout) &&
 562       $config->smartGroupCacheTimeout > 0) {
 563       return $config->smartGroupCacheTimeout;
 564     }
 565
 566     // lets have a min cache time of 5 mins if not set
 567     return 5;
 568   }
 569
 570   /**
 571    * Get all the smart groups that this contact belongs to
 572    * Note that this could potentially be a super slow function since
 573    * it ensure that all contact groups are loaded in the cache
 574    *
 575    * @param int     $contactID
 576    * @param boolean $showHidden - hidden groups are shown only if this flag is set
 577    *
 578    * @return array an array of groups that this contact belongs to
 579    */
 580   static function contactGroup($contactID, $showHidden = FALSE) {
 581     if (empty($contactID)) {
 582       return;
 583     }
 584
 585     if (is_array($contactID)) {
 586       $contactIDs = $contactID;
 587     }
 588     else {
 589       $contactIDs = array($contactID);
 590     }
 591
 592     self::loadAll();
 593
 594     $hiddenClause = '';
 595     if (!$showHidden) {
 596       $hiddenClause = ' AND (g.is_hidden = 0 OR g.is_hidden IS NULL) ';
 597     }
 598
 599     $contactIDString = CRM_Core_DAO::escapeString(implode(', ', $contactIDs));
 600     $sql = "
 601 SELECT     gc.group_id, gc.contact_id, g.title, g.children, g.description
 602 FROM       civicrm_group_contact_cache gc
 603 INNER JOIN civicrm_group g ON g.id = gc.group_id
 604 WHERE      gc.contact_id IN ($contactIDString)
 605            $hiddenClause
 606 ORDER BY   gc.contact_id, g.children
 607 ";
 608
 609     $dao = CRM_Core_DAO::executeQuery($sql);
 610     $contactGroup = array();
 611     $prevContactID = null;
 612     while ($dao->fetch()) {
 613       if (
 614         $prevContactID &&
 615         $prevContactID != $dao->contact_id
 616       ) {
 617         $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 618       }
 619       $prevContactID = $dao->contact_id;
 620       if (!array_key_exists($dao->contact_id, $contactGroup)) {
 621         $contactGroup[$dao->contact_id] =
 622           array( 'group' => array(), 'groupTitle' => array());
 623       }
 624
 625       $contactGroup[$dao->contact_id]['group'][] =
 626         array(
 627           'id' => $dao->group_id,
 628           'title' => $dao->title,
 629           'description' => $dao->description,
 630           'children' => $dao->children
 631         );
 632       $contactGroup[$dao->contact_id]['groupTitle'][] = $dao->title;
 633     }
 634
 635     if ($prevContactID) {
 636       $contactGroup[$prevContactID]['groupTitle'] = implode(', ', $contactGroup[$prevContactID]['groupTitle']);
 637     }
 638
 639     if ((!empty($contactGroup[$contactID]) && is_numeric($contactID))) {
 640       return $contactGroup[$contactID];
 641     }
 642     else {
 643       return $contactGroup;
 644     }
 645   }
 646
 647 }
 648