CRM/Core/BAO/PrevNextCache.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.3                                                |
   5   +--------------------------------------------------------------------+
   6   | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  32  * $Id$
  33  *
  34  */
  35
  36 /**
  37  * BAO object for civicrm_prevnext_cache table
  38  */
  39 class CRM_Core_BAO_PrevNextCache extends CRM_Core_DAO_PrevNextCache {
  40
  41   static function getPositions($cacheKey, $id1, $id2, &$mergeId = NULL, $join = NULL, $where = NULL, $flip = FALSE) {
  42     if ($flip) {
  43       list($id1, $id2) = array($id2, $id1);
  44     }
  45
  46     if ($mergeId == NULL) {
  47       $query = "
  48 SELECT id
  49 FROM   civicrm_prevnext_cache
  50 WHERE  cacheKey     = %3 AND
  51        entity_id1 = %1 AND
  52        entity_id2 = %2 AND
  53        entity_table = 'civicrm_contact'
  54 ";
  55
  56       $params = array(1 => array($id1, 'Integer'),
  57                 2 => array($id2, 'Integer'),
  58                 3 => array($cacheKey, 'String'),
  59       );
  60
  61       $mergeId = CRM_Core_DAO::singleValueQuery($query, $params);
  62     }
  63
  64     $pos = array('foundEntry' => 0);
  65     if ($mergeId) {
  66       $pos['foundEntry'] = 1;
  67
  68       if ($where) {
  69
  70         $where = " AND {$where}";
  71
  72       }
  73       $p = array(1 => array($mergeId, 'Integer'),
  74            2 => array($cacheKey, 'String'),
  75       );
  76       $sql       = "SELECT pn.id, pn.entity_id1, pn.entity_id2, pn.data FROM civicrm_prevnext_cache pn {$join} ";
  77       $wherePrev = " WHERE pn.id < %1 AND pn.cacheKey = %2 {$where} ORDER BY ID DESC LIMIT 1";
  78       $sqlPrev   = $sql . $wherePrev;
  79
  80
  81       $dao = CRM_Core_DAO::executeQuery($sqlPrev, $p);
  82       if ($dao->fetch()) {
  83         $pos['prev']['id1'] = $dao->entity_id1;
  84         $pos['prev']['id2'] = $dao->entity_id2;
  85         $pos['prev']['mergeId'] = $dao->id;
  86         $pos['prev']['data'] = $dao->data;
  87       }
  88
  89       $whereNext = " WHERE pn.id > %1 AND pn.cacheKey = %2 {$where} ORDER BY ID ASC LIMIT 1";
  90       $sqlNext = $sql . $whereNext;
  91
  92       $dao = CRM_Core_DAO::executeQuery($sqlNext, $p);
  93       if ($dao->fetch()) {
  94         $pos['next']['id1'] = $dao->entity_id1;
  95         $pos['next']['id2'] = $dao->entity_id2;
  96         $pos['next']['mergeId'] = $dao->id;
  97         $pos['next']['data'] = $dao->data;
  98       }
  99     }
 100     return $pos;
 101   }
 102
 103   static function deleteItem($id = NULL, $cacheKey = NULL, $entityTable = 'civicrm_contact') {
 104
 105     //clear cache
 106     $sql = "DELETE FROM civicrm_prevnext_cache WHERE  entity_table = %1";
 107     $params = array(1 => array($entityTable, 'String'));
 108
 109     if (is_numeric($id)) {
 110       $sql .= " AND ( entity_id1 = %2 OR entity_id2 = %2 )";
 111       $params[2] = array($id, 'Integer');
 112     }
 113
 114     if (isset($cacheKey)) {
 115       $sql .= " AND cacheKey LIKE %3";
 116       $params[3] = array("{$cacheKey}%", 'String');
 117     }
 118     CRM_Core_DAO::executeQuery($sql, $params);
 119   }
 120
 121   static function deletePair($id1, $id2, $cacheKey = NULL, $isViceVersa = FALSE, $entityTable = 'civicrm_contact') {
 122     $sql = "DELETE FROM civicrm_prevnext_cache WHERE  entity_table = %1";
 123     $params = array(1 => array($entityTable, 'String'));
 124
 125     $pair =
 126       ! $isViceVersa ?
 127       "entity_id1 = %2 AND entity_id2 = %3" :
 128       "(entity_id1 = %2 AND entity_id2 = %3) OR (entity_id1 = %3 AND entity_id2 = %2)";
 129     $sql .= " AND ( {$pair} )";
 130     $params[2] = array($id1, 'Integer');
 131     $params[3] = array($id2, 'Integer');
 132
 133     if (isset($cacheKey)) {
 134       $sql .= " AND cacheKey LIKE %4";
 135       $params[4] = array("{$cacheKey}%", 'String');
 136     }
 137
 138     CRM_Core_DAO::executeQuery($sql, $params);
 139   }
 140
 141   function retrieve($cacheKey, $join = NULL, $where = NULL, $offset = 0, $rowCount = 0) {
 142     $query = "
 143 SELECT data
 144 FROM   civicrm_prevnext_cache pn
 145        {$join}
 146 WHERE  cacheKey = %1
 147 ";
 148     $params = array(1 => array($cacheKey, 'String'));
 149
 150     if ($where) {
 151       $query .= " AND {$where}";
 152     }
 153
 154     if ($rowCount) {
 155       $query .= " LIMIT {$offset}, {$rowCount}";
 156     }
 157
 158     $dao = CRM_Core_DAO::executeQuery($query, $params);
 159
 160     $main = array();
 161     while ($dao->fetch()) {
 162       if (self::is_serialized($dao->data)) {
 163         $main[] = unserialize($dao->data);
 164       }
 165       else {
 166         $main[] = $dao->data;
 167       }
 168     }
 169
 170     return $main;
 171   }
 172
 173   public static function is_serialized($string) {
 174     return (@unserialize($string) !== false);
 175   }
 176
 177   static function setItem($values) {
 178     $insert = "INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data ) VALUES \n";
 179     $query = $insert . implode(",\n ", $values);
 180
 181     //dump the dedupe matches in the prevnext_cache table
 182     CRM_Core_DAO::executeQuery($query);
 183   }
 184
 185   static function getCount($cacheKey, $join = NULL, $where = NULL, $op = "=") {
 186     $query = "
 187 SELECT COUNT(*) FROM civicrm_prevnext_cache pn
 188        {$join}
 189 WHERE cacheKey $op %1
 190 ";
 191     if ($where) {
 192       $query .= " AND {$where}";
 193     }
 194
 195     $params = array(1 => array($cacheKey, 'String'));
 196     return (int) CRM_Core_DAO::singleValueQuery($query, $params, TRUE, FALSE);
 197   }
 198
 199   static function refillCache($rgid = NULL, $gid = NULL, $cacheKeyString = NULL) {
 200     if (!$cacheKeyString && $rgid) {
 201       $contactType = CRM_Core_DAO::getFieldValue('CRM_Dedupe_DAO_RuleGroup', $rgid, 'contact_type');
 202       $cacheKeyString = "merge {$contactType}";
 203       $cacheKeyString .= $rgid ? "_{$rgid}" : '_0';
 204       $cacheKeyString .= $gid ? "_{$gid}" : '_0';
 205     }
 206
 207     if (!$cacheKeyString) {
 208       return FALSE;
 209     }
 210
 211     // 1. Clear cache if any
 212     $sql = "DELETE FROM civicrm_prevnext_cache WHERE  cacheKey LIKE %1";
 213     CRM_Core_DAO::executeQuery($sql, array(1 => array("{$cacheKeyString}%", 'String')));
 214
 215     // FIXME: we need to start using temp tables / queries here instead of arrays.
 216     // And cleanup code in CRM/Contact/Page/DedupeFind.php
 217
 218     // 2. FILL cache
 219     $foundDupes = array();
 220     if ($rgid && $gid) {
 221       $foundDupes = CRM_Dedupe_Finder::dupesInGroup($rgid, $gid);
 222     }
 223     elseif ($rgid) {
 224       $foundDupes = CRM_Dedupe_Finder::dupes($rgid);
 225     }
 226
 227     if (!empty($foundDupes)) {
 228       $cids = $displayNames = $values = array();
 229       foreach ($foundDupes as $dupe) {
 230         $cids[$dupe[0]] = 1;
 231         $cids[$dupe[1]] = 1;
 232       }
 233       $cidString = implode(', ', array_keys($cids));
 234       $sql       = "SELECT id, display_name FROM civicrm_contact WHERE id IN ($cidString) ORDER BY sort_name";
 235       $dao       = new CRM_Core_DAO();
 236       $dao->query($sql);
 237       while ($dao->fetch()) {
 238         $displayNames[$dao->id] = $dao->display_name;
 239       }
 240
 241       $session = CRM_Core_Session::singleton();
 242       $userId = $session->get('userID');
 243
 244       foreach ($foundDupes as $dupes) {
 245         $srcID = $dupes[0];
 246         $dstID = $dupes[1];
 247         if ($dstID == $userId) {
 248           $srcID = $dupes[1];
 249           $dstID = $dupes[0];
 250         }
 251
 252         $row = array(
 253           'srcID' => $srcID,
 254           'srcName' => $displayNames[$srcID],
 255           'dstID' => $dstID,
 256           'dstName' => $displayNames[$dstID],
 257           'weight' => $dupes[2],
 258           'canMerge' => TRUE,
 259         );
 260
 261         $data = CRM_Core_DAO::escapeString(serialize($row));
 262         $values[] = " ( 'civicrm_contact', $srcID, $dstID, '$cacheKeyString', '$data' ) ";
 263       }
 264       self::setItem($values);
 265     }
 266   }
 267
 268   static function cleanupCache() {
 269     // clean up all prev next caches older than $cacheTimeIntervalDays days
 270     $cacheTimeIntervalDays = 2;
 271
 272     // first find all the cacheKeys that match this
 273     $sql = "
 274 DELETE     pn, c
 275 FROM       civicrm_cache c
 276 INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
 277 WHERE      c.group_name = %1
 278 AND        c.created_date < date_sub( NOW( ), INTERVAL %2 day )
 279 ";
 280     $params = array(
 281       1 => array('CiviCRM Search PrevNextCache', 'String'),
 282       2 => array($cacheTimeIntervalDays, 'Integer'),
 283     );
 284     CRM_Core_DAO::executeQuery($sql, $params);
 285   }
 286
 287
 288   /* function for saving the checkbox selections
 289    * $action select   - select a particular contact
 290    *         unselect - unselect a particular contact
 291    */
 292   static function markSelection($cacheKey, $action = 'unselect', $cIds = NULL, $entity_table = 'civicrm_contact') {
 293     if (!$cacheKey) {
 294       return;
 295     }
 296     $params = array();
 297
 298     $entity_whereClause = " AND entity_table = '{$entity_table}'";
 299     if ($cIds && $cacheKey && $action) {
 300       if (is_array($cIds)) {
 301         $cIdFilter = "(" . implode(',', $cIds) . ")";
 302         $whereClause = "
 303 WHERE cacheKey LIKE %1
 304 AND (entity_id1 IN {$cIdFilter} OR entity_id2 IN {$cIdFilter})
 305 ";
 306       }
 307       else {
 308         $whereClause = "
 309 WHERE cacheKey LIKE %1
 310 AND (entity_id1 = %2 OR entity_id2 = %2)
 311 ";
 312         $params[2] = array("{$cIds}", 'Integer');
 313       }
 314       if ($action == 'select') {
 315         $whereClause .= "AND is_selected = 0";
 316         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 1 {$whereClause} {$entity_whereClause}";
 317         $params[1] = array("{$cacheKey}%", 'String');
 318       }
 319       elseif ($action == 'unselect') {
 320         $whereClause .= "AND is_selected = 1";
 321         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 0 {$whereClause} {$entity_whereClause}";
 322         $params[1] = array("%{$cacheKey}%", 'String');
 323       }
 324       // default action is reseting
 325     }
 326     elseif (!$cIds && $cacheKey && $action == 'unselect') {
 327       $sql = "
 328 UPDATE civicrm_prevnext_cache
 329 SET    is_selected = 0
 330 WHERE  cacheKey LIKE %1 AND is_selected = 1
 331        {$entity_whereClause}
 332 ";
 333       $params[1] = array("{$cacheKey}%", 'String');
 334     }
 335     CRM_Core_DAO::executeQuery($sql, $params);
 336   }
 337
 338   /**
 339    * function to get the selections
 340    *
 341    * @param string $cacheKey     cache key
 342    * @param string $action       action
 343    *  $action : get - get only selection records
 344    *            getall - get all the records of the specified cache key
 345    * @param string $entity_table entity table
 346    */
 347   static function getSelection($cacheKey, $action = 'get', $entity_table = 'civicrm_contact') {
 348     if (!$cacheKey) {
 349       return;
 350     }
 351     $params = array();
 352
 353     $entity_whereClause = " AND entity_table = '{$entity_table}'";
 354     if ($cacheKey && ($action == 'get' || $action == 'getall')) {
 355       $actionGet = ($action == "get") ? " AND is_selected = 1 " : "";
 356       $sql = "
 357 SELECT entity_id1, entity_id2 FROM civicrm_prevnext_cache
 358 WHERE cacheKey LIKE %1
 359       $actionGet
 360       $entity_whereClause
 361 ORDER BY id
 362 ";
 363       $params[1] = array("{$cacheKey}%", 'String');
 364
 365       $contactIds = array($cacheKey => array());
 366       $cIdDao = CRM_Core_DAO::executeQuery($sql, $params);
 367       while ($cIdDao->fetch()) {
 368         if ($cIdDao->entity_id1 == $cIdDao->entity_id2) {
 369           $contactIds[$cacheKey][$cIdDao->entity_id1] = 1;
 370         }
 371       }
 372       return $contactIds;
 373     }
 374   }
 375
 376   static function getSelectedContacts() {
 377     $qfKey = CRM_Utils_Request::retrieve('qfKey', 'String');
 378     $cacheKey = "civicrm search {$qfKey}";
 379     $query = "
 380 SELECT *
 381 FROM   civicrm_prevnext_cache
 382 WHERE  cacheKey LIKE %1
 383   AND  is_selected=1
 384   AND  cacheKey NOT LIKE %2
 385 ";
 386     $params1[1] = array("{$cacheKey}%", 'String');
 387     $params1[2] = array("{$cacheKey}_alphabet%", 'String');
 388     $dao = CRM_Core_DAO::executeQuery($query, $params1);
 389
 390     $val = array( );
 391     while ($dao->fetch()) {
 392       $val[] = $dao->data;
 393     }
 394     return $val;
 395   }
 396
 397   static function buildSelectedContactPager( &$obj, &$params) {
 398     $params['status'] = ts('Contacts %%StatusMessage%%');
 399     $params['csvString'] = NULL;
 400     $params['buttonTop'] = 'PagerTopButton';
 401     $params['buttonBottom'] = 'PagerBottomButton';
 402     $params['rowCount'] = $obj->get(CRM_Utils_Pager::PAGE_ROWCOUNT);
 403
 404     if (!$params['rowCount']) {
 405       $params['rowCount'] = CRM_Utils_Pager::ROWCOUNT;
 406     }
 407
 408     $qfKey = CRM_Utils_Request::retrieve('qfKey','String', $this);
 409     $cacheKey = "civicrm search {$qfKey}";
 410
 411     $query = "
 412 SELECT count(id)
 413 FROM   civicrm_prevnext_cache
 414 WHERE  cacheKey LIKE %1
 415   AND  is_selected = 1
 416   AND  cacheKey NOT LIKE %2
 417 ";
 418     $params1[1] = array("{$cacheKey}%", 'String');
 419     $params1[2] = array("{$cacheKey}_alphabet%", 'String');
 420     $paramsTotal     = CRM_Core_DAO::singleValueQuery($query, $params1);
 421     $params['total'] = $paramsTotal;
 422     $obj->_pager    = new CRM_Utils_Pager($params);
 423     $obj->assign_by_ref('pager', $obj->_pager);
 424     list($offset, $rowCount) = $obj->_pager->getOffsetAndRowCount();
 425     $params['offset'] = $offset;
 426     $params['rowCount1'] = $rowCount;
 427     return $params;
 428   }
 429 }
 430