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