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