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