Merge pull request #15094 from mattwire/devcore792_membershipcontributionfail
[civicrm-core.git] / 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 * @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 }