CRM/Core/BAO/PrevNextCache.php

   1 <?php
   2 /*
   3   +--------------------------------------------------------------------+
   4   | CiviCRM version 4.4                                                |
   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       $offset = CRM_Utils_Type::escape($offset, 'Int');
 156       $rowCount = CRM_Utils_Type::escape($rowCount, 'Int');
 157
 158       $query .= " LIMIT {$offset}, {$rowCount}";
 159     }
 160
 161     $dao = CRM_Core_DAO::executeQuery($query, $params);
 162
 163     $main = array();
 164     while ($dao->fetch()) {
 165       if (self::is_serialized($dao->data)) {
 166         $main[] = unserialize($dao->data);
 167       }
 168       else {
 169         $main[] = $dao->data;
 170       }
 171     }
 172
 173     return $main;
 174   }
 175
 176   public static function is_serialized($string) {
 177     return (@unserialize($string) !== false);
 178   }
 179
 180   static function setItem($values) {
 181     $insert = "INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data ) VALUES \n";
 182     $query = $insert . implode(",\n ", $values);
 183
 184     //dump the dedupe matches in the prevnext_cache table
 185     CRM_Core_DAO::executeQuery($query);
 186   }
 187
 188   static function getCount($cacheKey, $join = NULL, $where = NULL, $op = "=") {
 189     $query = "
 190 SELECT COUNT(*) FROM civicrm_prevnext_cache pn
 191        {$join}
 192 WHERE cacheKey $op %1
 193 ";
 194     if ($where) {
 195       $query .= " AND {$where}";
 196     }
 197
 198     $params = array(1 => array($cacheKey, 'String'));
 199     return (int) CRM_Core_DAO::singleValueQuery($query, $params, TRUE, FALSE);
 200   }
 201
 202   static function refillCache($rgid = NULL, $gid = NULL, $cacheKeyString = NULL) {
 203     if (!$cacheKeyString && $rgid) {
 204       $contactType = CRM_Core_DAO::getFieldValue('CRM_Dedupe_DAO_RuleGroup', $rgid, 'contact_type');
 205       $cacheKeyString = "merge {$contactType}";
 206       $cacheKeyString .= $rgid ? "_{$rgid}" : '_0';
 207       $cacheKeyString .= $gid ? "_{$gid}" : '_0';
 208     }
 209
 210     if (!$cacheKeyString) {
 211       return FALSE;
 212     }
 213
 214     // 1. Clear cache if any
 215     $sql = "DELETE FROM civicrm_prevnext_cache WHERE  cacheKey LIKE %1";
 216     CRM_Core_DAO::executeQuery($sql, array(1 => array("{$cacheKeyString}%", 'String')));
 217
 218     // FIXME: we need to start using temp tables / queries here instead of arrays.
 219     // And cleanup code in CRM/Contact/Page/DedupeFind.php
 220
 221     // 2. FILL cache
 222     $foundDupes = array();
 223     if ($rgid && $gid) {
 224       $foundDupes = CRM_Dedupe_Finder::dupesInGroup($rgid, $gid);
 225     }
 226     elseif ($rgid) {
 227       $foundDupes = CRM_Dedupe_Finder::dupes($rgid);
 228     }
 229
 230     if (!empty($foundDupes)) {
 231       $cids = $displayNames = $values = array();
 232       foreach ($foundDupes as $dupe) {
 233         $cids[$dupe[0]] = 1;
 234         $cids[$dupe[1]] = 1;
 235       }
 236       $cidString = implode(', ', array_keys($cids));
 237       $sql       = "SELECT id, display_name FROM civicrm_contact WHERE id IN ($cidString) ORDER BY sort_name";
 238       $dao       = new CRM_Core_DAO();
 239       $dao->query($sql);
 240       while ($dao->fetch()) {
 241         $displayNames[$dao->id] = $dao->display_name;
 242       }
 243
 244       $session = CRM_Core_Session::singleton();
 245       $userId = $session->get('userID');
 246
 247       foreach ($foundDupes as $dupes) {
 248         $srcID = $dupes[0];
 249         $dstID = $dupes[1];
 250         if ($dstID == $userId) {
 251           $srcID = $dupes[1];
 252           $dstID = $dupes[0];
 253         }
 254
 255         $row = array(
 256           'srcID' => $srcID,
 257           'srcName' => $displayNames[$srcID],
 258           'dstID' => $dstID,
 259           'dstName' => $displayNames[$dstID],
 260           'weight' => $dupes[2],
 261           'canMerge' => TRUE,
 262         );
 263
 264         $data = CRM_Core_DAO::escapeString(serialize($row));
 265         $values[] = " ( 'civicrm_contact', $srcID, $dstID, '$cacheKeyString', '$data' ) ";
 266       }
 267       self::setItem($values);
 268     }
 269   }
 270
 271   static function cleanupCache() {
 272     // clean up all prev next caches older than $cacheTimeIntervalDays days
 273     $cacheTimeIntervalDays = 2;
 274
 275     // first find all the cacheKeys that match this
 276     $sql = "
 277 DELETE     pn, c
 278 FROM       civicrm_cache c
 279 INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
 280 WHERE      c.group_name = %1
 281 AND        c.created_date < date_sub( NOW( ), INTERVAL %2 day )
 282 ";
 283     $params = array(
 284       1 => array('CiviCRM Search PrevNextCache', 'String'),
 285       2 => array($cacheTimeIntervalDays, 'Integer'),
 286     );
 287     CRM_Core_DAO::executeQuery($sql, $params);
 288   }
 289
 290
 291   /* function for saving the checkbox selections
 292    * $action select   - select a particular contact
 293    *         unselect - unselect a particular contact
 294    */
 295   static function markSelection($cacheKey, $action = 'unselect', $cIds = NULL, $entity_table = 'civicrm_contact') {
 296     if (!$cacheKey) {
 297       return;
 298     }
 299     $params = array();
 300
 301     $entity_whereClause = " AND entity_table = '{$entity_table}'";
 302     if ($cIds && $cacheKey && $action) {
 303       if (is_array($cIds)) {
 304         $cIdFilter = "(" . implode(',', $cIds) . ")";
 305         $whereClause = "
 306 WHERE cacheKey LIKE %1
 307 AND (entity_id1 IN {$cIdFilter} OR entity_id2 IN {$cIdFilter})
 308 ";
 309       }
 310       else {
 311         $whereClause = "
 312 WHERE cacheKey LIKE %1
 313 AND (entity_id1 = %2 OR entity_id2 = %2)
 314 ";
 315         $params[2] = array("{$cIds}", 'Integer');
 316       }
 317       if ($action == 'select') {
 318         $whereClause .= "AND is_selected = 0";
 319         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 1 {$whereClause} {$entity_whereClause}";
 320         $params[1] = array("{$cacheKey}%", 'String');
 321       }
 322       elseif ($action == 'unselect') {
 323         $whereClause .= "AND is_selected = 1";
 324         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 0 {$whereClause} {$entity_whereClause}";
 325         $params[1] = array("%{$cacheKey}%", 'String');
 326       }
 327       // default action is reseting
 328     }
 329     elseif (!$cIds && $cacheKey && $action == 'unselect') {
 330       $sql = "
 331 UPDATE civicrm_prevnext_cache
 332 SET    is_selected = 0
 333 WHERE  cacheKey LIKE %1 AND is_selected = 1
 334        {$entity_whereClause}
 335 ";
 336       $params[1] = array("{$cacheKey}%", 'String');
 337     }
 338     CRM_Core_DAO::executeQuery($sql, $params);
 339   }
 340
 341   /**
 342    * function to get the selections
 343    *
 344    * @param string $cacheKey     cache key
 345    * @param string $action       action
 346    *  $action : get - get only selection records
 347    *            getall - get all the records of the specified cache key
 348    * @param string $entity_table entity table
 349    */
 350   static function getSelection($cacheKey, $action = 'get', $entity_table = 'civicrm_contact') {
 351     if (!$cacheKey) {
 352       return;
 353     }
 354     $params = array();
 355
 356     $entity_whereClause = " AND entity_table = '{$entity_table}'";
 357     if ($cacheKey && ($action == 'get' || $action == 'getall')) {
 358       $actionGet = ($action == "get") ? " AND is_selected = 1 " : "";
 359       $sql = "
 360 SELECT entity_id1, entity_id2 FROM civicrm_prevnext_cache
 361 WHERE cacheKey LIKE %1
 362       $actionGet
 363       $entity_whereClause
 364 ORDER BY id
 365 ";
 366       $params[1] = array("{$cacheKey}%", 'String');
 367
 368       $contactIds = array($cacheKey => array());
 369       $cIdDao = CRM_Core_DAO::executeQuery($sql, $params);
 370       while ($cIdDao->fetch()) {
 371         if ($cIdDao->entity_id1 == $cIdDao->entity_id2) {
 372           $contactIds[$cacheKey][$cIdDao->entity_id1] = 1;
 373         }
 374       }
 375       return $contactIds;
 376     }
 377   }
 378
 379   static function getSelectedContacts() {
 380     $qfKey = CRM_Utils_Request::retrieve('qfKey', 'String');
 381     $cacheKey = "civicrm search {$qfKey}";
 382     $query = "
 383 SELECT *
 384 FROM   civicrm_prevnext_cache
 385 WHERE  cacheKey LIKE %1
 386   AND  is_selected=1
 387   AND  cacheKey NOT LIKE %2
 388 ";
 389     $params1[1] = array("{$cacheKey}%", 'String');
 390     $params1[2] = array("{$cacheKey}_alphabet%", 'String');
 391     $dao = CRM_Core_DAO::executeQuery($query, $params1);
 392
 393     $val = array( );
 394     while ($dao->fetch()) {
 395       $val[] = $dao->data;
 396     }
 397     return $val;
 398   }
 399
 400   static function buildSelectedContactPager( &$obj, &$params) {
 401     $params['status'] = ts('Contacts %%StatusMessage%%');
 402     $params['csvString'] = NULL;
 403     $params['buttonTop'] = 'PagerTopButton';
 404     $params['buttonBottom'] = 'PagerBottomButton';
 405     $params['rowCount'] = $obj->get(CRM_Utils_Pager::PAGE_ROWCOUNT);
 406
 407     if (!$params['rowCount']) {
 408       $params['rowCount'] = CRM_Utils_Pager::ROWCOUNT;
 409     }
 410
 411     $qfKey = CRM_Utils_Request::retrieve('qfKey','String', $this);
 412     $cacheKey = "civicrm search {$qfKey}";
 413
 414     $query = "
 415 SELECT count(id)
 416 FROM   civicrm_prevnext_cache
 417 WHERE  cacheKey LIKE %1
 418   AND  is_selected = 1
 419   AND  cacheKey NOT LIKE %2
 420 ";
 421     $params1[1] = array("{$cacheKey}%", 'String');
 422     $params1[2] = array("{$cacheKey}_alphabet%", 'String');
 423     $paramsTotal     = CRM_Core_DAO::singleValueQuery($query, $params1);
 424     $params['total'] = $paramsTotal;
 425     $obj->_pager    = new CRM_Utils_Pager($params);
 426     $obj->assign_by_ref('pager', $obj->_pager);
 427     list($offset, $rowCount) = $obj->_pager->getOffsetAndRowCount();
 428     $params['offset'] = $offset;
 429     $params['rowCount1'] = $rowCount;
 430     return $params;
 431   }
 432 }
 433