3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
29 * Class CRM_Core_PrevNextCache_Sql
31 * Store the previous/next cache in a special-purpose SQL table.
33 class CRM_Core_PrevNextCache_Sql
implements CRM_Core_PrevNextCache_Interface
{
36 * Store the results of a SQL query in the cache.
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']]
46 * @throws CRM_Core_Exception
47 * @see CRM_Core_DAO::composeQuery
49 public function fillWithSql($cacheKey, $sql, $sqlParams = []) {
51 INSERT INTO civicrm_prevnext_cache (cacheKey, entity_id1, data)
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
);
60 public function fillWithArray($cacheKey, $rows) {
65 $insert = CRM_Utils_SQL_Insert
::into('civicrm_prevnext_cache')
72 foreach ($rows as &$row) {
73 $insert->row($row +
['cacheKey' => $cacheKey]);
76 CRM_Core_DAO
::executeQuery($insert->toSQL());
81 * Save checkbox selections.
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.
90 public function markSelection($cacheKey, $action, $ids = NULL) {
96 if ($ids && $cacheKey && $action) {
98 $cIdFilter = "(" . implode(',', $ids) . ")";
101 AND (entity_id1 IN {$cIdFilter} OR entity_id2 IN {$cIdFilter})
107 AND (entity_id1 = %2 OR entity_id2 = %2)
109 $params[2] = array("{$ids}", 'Integer');
111 if ($action == 'select') {
112 $whereClause .= "AND is_selected = 0";
113 $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 1 {$whereClause}";
114 $params[1] = array($cacheKey, 'String');
116 elseif ($action == 'unselect') {
117 $whereClause .= "AND is_selected = 1";
118 $sql = "UPDATE civicrm_prevnext_cache SET is_selected = 0 {$whereClause}";
119 $params[1] = array($cacheKey, 'String');
121 // default action is reseting
123 elseif (!$ids && $cacheKey && $action == 'unselect') {
125 UPDATE civicrm_prevnext_cache
127 WHERE cacheKey = %1 AND is_selected = 1
129 $params[1] = array($cacheKey, 'String');
131 CRM_Core_DAO
::executeQuery($sql, $params);
135 * Get the selections.
137 * @param string $cacheKey
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
146 public function getSelection($cacheKey, $action = 'get') {
152 if ($cacheKey && ($action == 'get' ||
$action == 'getall')) {
153 $actionGet = ($action == "get") ?
" AND is_selected = 1 " : "";
155 SELECT entity_id1 FROM civicrm_prevnext_cache
160 $params[1] = array($cacheKey, 'String');
162 $contactIds = array($cacheKey => array());
163 $cIdDao = CRM_Core_DAO
::executeQuery($sql, $params);
164 while ($cIdDao->fetch()) {
165 $contactIds[$cacheKey][$cIdDao->entity_id1
] = 1;
172 * Get the previous and next keys.
174 * @param string $cacheKey
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",
183 1 => [$id1, 'Integer'],
184 2 => [$cacheKey, 'String'],
188 $pos = ['foundEntry' => 0];
190 $pos['foundEntry'] = 1;
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";
196 1 => [$mergeId, 'Integer'],
197 2 => [$cacheKey, 'String'],
200 $dao = CRM_Core_DAO
::executeQuery($sql . $wherePrev, $p);
202 $pos['prev']['id1'] = $dao->entity_id1
;
203 $pos['prev']['mergeId'] = $dao->id
;
204 $pos['prev']['data'] = $dao->data
;
207 $dao = CRM_Core_DAO
::executeQuery($sql . $whereNext, $p);
209 $pos['next']['id1'] = $dao->entity_id1
;
210 $pos['next']['mergeId'] = $dao->id
;
211 $pos['next']['data'] = $dao->data
;
219 * Delete an item from the prevnext cache table based on the entity.
222 * @param string $cacheKey
224 public function deleteItem($id = NULL, $cacheKey = NULL) {
225 $sql = "DELETE FROM civicrm_prevnext_cache WHERE (1)";
228 if (is_numeric($id)) {
229 $sql .= " AND ( entity_id1 = %2 OR entity_id2 = %2 )";
230 $params[2] = array($id, 'Integer');
233 if (isset($cacheKey)) {
234 $sql .= " AND cacheKey = %3";
235 $params[3] = array($cacheKey, 'String');
237 CRM_Core_DAO
::executeQuery($sql, $params);
241 * Get count of matching rows.
243 * @param string $cacheKey
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);
253 * Fetch a list of contacts from the prev/next cache for displaying a search results page
255 * @param string $cacheKey
257 * @param int $rowCount
259 * List of contact IDs.
261 public function fetch($cacheKey, $offset, $rowCount) {
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')
267 ->limit($rowCount, $offset)
269 while ($dao->fetch()) {