CRM/Core/PrevNextCache/Sql.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   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  * Class CRM_Core_PrevNextCache_Sql
  30  *
  31  * Store the previous/next cache in a special-purpose SQL table.
  32  */
  33 class CRM_Core_PrevNextCache_Sql implements CRM_Core_PrevNextCache_Interface {
  34
  35   /**
  36    * Store the results of a SQL query in the cache.
  37    *
  38    * @param string $sql
  39    *   A SQL query. The query *MUST* be a SELECT statement which yields
  40    *   the following columns (in order): cacheKey, entity_id1, data
  41    * @return bool
  42    * @throws CRM_Core_Exception
  43    */
  44   public function fillWithSql($cacheKey, $sql) {
  45     $insertSQL = "
  46 INSERT INTO civicrm_prevnext_cache (cacheKey, entity_id1, data)
  47 ";
  48     $result = CRM_Core_DAO::executeQuery($insertSQL . $sql, [], FALSE, NULL, FALSE, TRUE, TRUE);
  49     if (is_a($result, 'DB_Error')) {
  50       throw new CRM_Core_Exception($result->message);
  51     }
  52     return TRUE;
  53   }
  54
  55   public function fillWithArray($cacheKey, $rows) {
  56     if (empty($rows)) {
  57       return;
  58     }
  59
  60     $insert = CRM_Utils_SQL_Insert::into('civicrm_prevnext_cache')
  61       ->columns([
  62         'entity_id1',
  63         'cacheKey',
  64         'data'
  65       ]);
  66
  67     foreach ($rows as &$row) {
  68       $insert->row($row + ['cacheKey' => $cacheKey]);
  69     }
  70
  71     CRM_Core_DAO::executeQuery($insert->toSQL());
  72     return TRUE;
  73   }
  74
  75   /**
  76    * Save checkbox selections.
  77    *
  78    * @param string $cacheKey
  79    * @param string $action
  80    *   Ex: 'select', 'unselect'.
  81    * @param array|int|NULL $ids
  82    *   A list of contact IDs to (un)select.
  83    *   To unselect all contact IDs, use NULL.
  84    */
  85   public function markSelection($cacheKey, $action, $ids = NULL) {
  86     if (!$cacheKey) {
  87       return;
  88     }
  89     $params = array();
  90
  91     if ($ids && $cacheKey && $action) {
  92       if (is_array($ids)) {
  93         $cIdFilter = "(" . implode(',', $ids) . ")";
  94         $whereClause = "
  95 WHERE cacheKey = %1
  96 AND (entity_id1 IN {$cIdFilter} OR entity_id2 IN {$cIdFilter})
  97 ";
  98       }
  99       else {
 100         $whereClause = "
 101 WHERE cacheKey = %1
 102 AND (entity_id1 = %2 OR entity_id2 = %2)
 103 ";
 104         $params[2] = array("{$ids}", 'Integer');
 105       }
 106       if ($action == 'select') {
 107         $whereClause .= "AND is_selected = 0";
 108         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 1 {$whereClause}";
 109         $params[1] = array($cacheKey, 'String');
 110       }
 111       elseif ($action == 'unselect') {
 112         $whereClause .= "AND is_selected = 1";
 113         $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 0 {$whereClause}";
 114         $params[1] = array($cacheKey, 'String');
 115       }
 116       // default action is reseting
 117     }
 118     elseif (!$ids && $cacheKey && $action == 'unselect') {
 119       $sql = "
 120 UPDATE civicrm_prevnext_cache
 121 SET    is_selected = 0
 122 WHERE  cacheKey = %1 AND is_selected = 1
 123 ";
 124       $params[1] = array($cacheKey, 'String');
 125     }
 126     CRM_Core_DAO::executeQuery($sql, $params);
 127   }
 128
 129   /**
 130    * Get the selections.
 131    *
 132    * @param string $cacheKey
 133    *   Cache key.
 134    * @param string $action
 135    *   One of the following:
 136    *   - 'get' - get only selection records
 137    *   - 'getall' - get all the records of the specified cache key
 138    *
 139    * @return array|NULL
 140    */
 141   public function getSelection($cacheKey, $action = 'get') {
 142     if (!$cacheKey) {
 143       return NULL;
 144     }
 145     $params = array();
 146
 147     if ($cacheKey && ($action == 'get' || $action == 'getall')) {
 148       $actionGet = ($action == "get") ? " AND is_selected = 1 " : "";
 149       $sql = "
 150 SELECT entity_id1 FROM civicrm_prevnext_cache
 151 WHERE cacheKey = %1
 152       $actionGet
 153 ORDER BY id
 154 ";
 155       $params[1] = array($cacheKey, 'String');
 156
 157       $contactIds = array($cacheKey => array());
 158       $cIdDao = CRM_Core_DAO::executeQuery($sql, $params);
 159       while ($cIdDao->fetch()) {
 160         $contactIds[$cacheKey][$cIdDao->entity_id1] = 1;
 161       }
 162       return $contactIds;
 163     }
 164   }
 165
 166   /**
 167    * Get the previous and next keys.
 168    *
 169    * @param string $cacheKey
 170    * @param int $id1
 171    *
 172    * @return array
 173    */
 174   public function getPositions($cacheKey, $id1) {
 175     $mergeId = CRM_Core_DAO::singleValueQuery(
 176       "SELECT id FROM civicrm_prevnext_cache WHERE cacheKey = %2 AND entity_id1 = %1",
 177       [
 178         1 => [$id1, 'Integer'],
 179         2 => [$cacheKey, 'String'],
 180       ]
 181     );
 182
 183     $pos = ['foundEntry' => 0];
 184     if ($mergeId) {
 185       $pos['foundEntry'] = 1;
 186
 187       $sql = "SELECT pn.id, pn.entity_id1, pn.entity_id2, pn.data FROM civicrm_prevnext_cache pn ";
 188       $wherePrev = " WHERE pn.id < %1 AND pn.cacheKey = %2 ORDER BY ID DESC LIMIT 1";
 189       $whereNext = " WHERE pn.id > %1 AND pn.cacheKey = %2 ORDER BY ID ASC LIMIT 1";
 190       $p = [
 191         1 => [$mergeId, 'Integer'],
 192         2 => [$cacheKey, 'String'],
 193       ];
 194
 195       $dao = CRM_Core_DAO::executeQuery($sql . $wherePrev, $p);
 196       if ($dao->fetch()) {
 197         $pos['prev']['id1'] = $dao->entity_id1;
 198         $pos['prev']['mergeId'] = $dao->id;
 199         $pos['prev']['data'] = $dao->data;
 200       }
 201
 202       $dao = CRM_Core_DAO::executeQuery($sql . $whereNext, $p);
 203       if ($dao->fetch()) {
 204         $pos['next']['id1'] = $dao->entity_id1;
 205         $pos['next']['mergeId'] = $dao->id;
 206         $pos['next']['data'] = $dao->data;
 207       }
 208     }
 209     return $pos;
 210
 211   }
 212
 213   /**
 214    * Delete an item from the prevnext cache table based on the entity.
 215    *
 216    * @param int $id
 217    * @param string $cacheKey
 218    */
 219   public function deleteItem($id = NULL, $cacheKey = NULL) {
 220     $sql = "DELETE FROM civicrm_prevnext_cache WHERE (1)";
 221     $params = array();
 222
 223     if (is_numeric($id)) {
 224       $sql .= " AND ( entity_id1 = %2 OR entity_id2 = %2 )";
 225       $params[2] = array($id, 'Integer');
 226     }
 227
 228     if (isset($cacheKey)) {
 229       $sql .= " AND cacheKey = %3";
 230       $params[3] = array($cacheKey, 'String');
 231     }
 232     CRM_Core_DAO::executeQuery($sql, $params);
 233   }
 234
 235   /**
 236    * Get count of matching rows.
 237    *
 238    * @param string $cacheKey
 239    * @return int
 240    */
 241   public function getCount($cacheKey) {
 242     $query = "SELECT COUNT(*) FROM civicrm_prevnext_cache pn WHERE pn.cacheKey = %1";
 243     $params = [1 => [$cacheKey, 'String']];
 244     return (int) CRM_Core_DAO::singleValueQuery($query, $params, TRUE, FALSE);
 245   }
 246
 247   /**
 248    * Fetch a list of contacts from the prev/next cache for displaying a search results page
 249    *
 250    * @param string $cacheKey
 251    * @param int $offset
 252    * @param int $rowCount
 253    * @return array
 254    *   List of contact IDs.
 255    */
 256   public function fetch($cacheKey, $offset, $rowCount) {
 257     $cids = array();
 258     $dao = CRM_Utils_SQL_Select::from('civicrm_prevnext_cache pnc')
 259       ->where('pnc.cacheKey = @cacheKey', ['cacheKey' => $cacheKey])
 260       ->select('pnc.entity_id1 as cid')
 261       ->orderBy('pnc.id')
 262       ->limit($rowCount, $offset)
 263       ->execute();
 264     while ($dao->fetch()) {
 265       $cids[] = $dao->cid;
 266     }
 267     return $cids;
 268   }
 269
 270 }