1 | <?php |
||
2 | /** |
||
3 | * Analytics |
||
4 | * |
||
5 | * SPDX-FileCopyrightText: 2019-2022 Marcel Scherello |
||
6 | * SPDX-License-Identifier: AGPL-3.0-or-later |
||
7 | */ |
||
8 | |||
9 | namespace OCA\Analytics\Db; |
||
10 | |||
11 | use OCP\DB\Exception; |
||
0 ignored issues
–
show
|
|||
12 | use OCP\IDBConnection; |
||
0 ignored issues
–
show
The type
OCP\IDBConnection was not found. Maybe you did not declare it correctly or list all dependencies?
The issue could also be caused by a filter entry in the build configuration.
If the path has been excluded in your configuration, e.g. filter:
dependency_paths: ["lib/*"]
For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths ![]() |
|||
13 | use OCP\IL10N; |
||
0 ignored issues
–
show
The type
OCP\IL10N was not found. Maybe you did not declare it correctly or list all dependencies?
The issue could also be caused by a filter entry in the build configuration.
If the path has been excluded in your configuration, e.g. filter:
dependency_paths: ["lib/*"]
For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths ![]() |
|||
14 | use Psr\Log\LoggerInterface; |
||
0 ignored issues
–
show
The type
Psr\Log\LoggerInterface was not found. Maybe you did not declare it correctly or list all dependencies?
The issue could also be caused by a filter entry in the build configuration.
If the path has been excluded in your configuration, e.g. filter:
dependency_paths: ["lib/*"]
For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths ![]() |
|||
15 | |||
16 | class ReportMapper |
||
17 | { |
||
18 | private $userId; |
||
19 | private $l10n; |
||
20 | private $db; |
||
21 | private $logger; |
||
22 | const TABLE_NAME = 'analytics_report'; |
||
23 | |||
24 | public function __construct( |
||
25 | $userId, |
||
26 | IL10N $l10n, |
||
27 | IDBConnection $db, |
||
28 | LoggerInterface $logger |
||
29 | ) |
||
30 | { |
||
31 | $this->userId = $userId; |
||
32 | $this->l10n = $l10n; |
||
33 | $this->db = $db; |
||
34 | $this->logger = $logger; |
||
35 | } |
||
36 | |||
37 | /** |
||
38 | * get reports |
||
39 | * @return array |
||
40 | * @throws Exception |
||
41 | */ |
||
42 | public function index() |
||
43 | { |
||
44 | $sql = $this->db->getQueryBuilder(); |
||
45 | $sql->from(self::TABLE_NAME) |
||
46 | ->select('id') |
||
47 | ->addSelect('name') |
||
48 | ->addSelect('type') |
||
49 | ->addSelect('parent') |
||
50 | ->addSelect('dataset') |
||
51 | ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId))) |
||
52 | ->orderBy('parent', 'ASC') |
||
53 | ->addOrderBy('name', 'ASC'); |
||
54 | $statement = $sql->executeQuery(); |
||
55 | $result = $statement->fetchAll(); |
||
56 | $statement->closeCursor(); |
||
57 | |||
58 | if ($result !== false && is_array($result)) { |
||
59 | foreach ($result as &$row) { |
||
60 | $row['dataset'] = (int) $row['dataset']; |
||
61 | $row['type'] = (int) $row['type']; |
||
62 | $row['parent'] = (int) $row['parent']; |
||
63 | } |
||
64 | unset($row); // Unset reference |
||
65 | } |
||
66 | |||
67 | return $result; |
||
68 | } |
||
69 | |||
70 | /** |
||
71 | * get reports for user |
||
72 | * @param $userId |
||
73 | * @return array |
||
74 | * @throws Exception |
||
75 | */ |
||
76 | public function indexByUser($userId) |
||
77 | { |
||
78 | $sql = $this->db->getQueryBuilder(); |
||
79 | $sql->from(self::TABLE_NAME) |
||
80 | ->select('id') |
||
81 | ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($userId))); |
||
82 | $statement = $sql->executeQuery(); |
||
83 | $result = $statement->fetchAll(); |
||
84 | $statement->closeCursor(); |
||
85 | return $result; |
||
86 | } |
||
87 | |||
88 | /** |
||
89 | * create report |
||
90 | * @param $name |
||
91 | * @param $subheader |
||
92 | * @param $parent |
||
93 | * @param $type |
||
94 | * @param $dataset |
||
95 | * @param $link |
||
96 | * @param $visualization |
||
97 | * @param $chart |
||
98 | * @param $dimension1 |
||
99 | * @param $dimension2 |
||
100 | * @param $value |
||
101 | * @return int |
||
102 | * @throws Exception |
||
103 | */ |
||
104 | public function create($name, $subheader, $parent, $type, $dataset, $link, $visualization, $chart, $dimension1, $dimension2, $value) |
||
105 | { |
||
106 | $sql = $this->db->getQueryBuilder(); |
||
107 | $sql->insert(self::TABLE_NAME) |
||
108 | ->values([ |
||
109 | 'user_id' => $sql->createNamedParameter($this->userId), |
||
110 | 'dataset' => $sql->createNamedParameter($dataset), |
||
111 | 'name' => $sql->createNamedParameter($name), |
||
112 | 'subheader' => $sql->createNamedParameter($subheader), |
||
113 | 'link' => $sql->createNamedParameter($link), |
||
114 | 'type' => $sql->createNamedParameter($type), |
||
115 | 'parent' => $sql->createNamedParameter($parent), |
||
116 | 'dimension1' => $sql->createNamedParameter($dimension1), |
||
117 | 'dimension2' => $sql->createNamedParameter($dimension2), |
||
118 | //'dimension3' => $sql->createNamedParameter($this->l10n->t('Value')), |
||
119 | //'dimension4' => $sql->createNamedParameter($this->l10n->t('Value')), |
||
120 | //'timestamp' => $sql->createNamedParameter($this->l10n->t('Date')), |
||
121 | //'unit' => $sql->createNamedParameter($this->l10n->t('Value')), |
||
122 | 'value' => $sql->createNamedParameter($value), |
||
123 | 'chart' => $sql->createNamedParameter($chart), |
||
124 | 'visualization' => $sql->createNamedParameter($visualization), |
||
125 | ]); |
||
126 | $sql->executeStatement(); |
||
127 | return (int)$sql->getLastInsertId(); |
||
128 | } |
||
129 | |||
130 | /** |
||
131 | * get single report for user |
||
132 | * @param int $id |
||
133 | * @return array |
||
134 | * @throws Exception |
||
135 | */ |
||
136 | public function readOwn(int $id) |
||
137 | { |
||
138 | $sql = $this->db->getQueryBuilder(); |
||
139 | $sql->from(self::TABLE_NAME) |
||
140 | ->select('*') |
||
141 | ->where($sql->expr()->eq('id', $sql->createNamedParameter($id))) |
||
142 | ->andWhere($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId))) |
||
143 | ->orderBy('parent', 'ASC') |
||
144 | ->addOrderBy('name', 'ASC'); |
||
145 | $statement = $sql->executeQuery(); |
||
146 | $result = $statement->fetch(); |
||
147 | $statement->closeCursor(); |
||
148 | |||
149 | if ($result !== false) { |
||
150 | $result['dataset'] = (int) $result['dataset']; |
||
151 | $result['type'] = (int) $result['type']; |
||
152 | $result['parent'] = (int) $result['parent']; |
||
153 | $result['refresh'] = (int) $result['refresh']; |
||
154 | } |
||
155 | |||
156 | return $result; |
||
157 | } |
||
158 | |||
159 | /** |
||
160 | * get single report |
||
161 | * @param int $id |
||
162 | * @return array |
||
163 | * @throws Exception |
||
164 | */ |
||
165 | public function read(int $id) |
||
166 | { |
||
167 | $sql = $this->db->getQueryBuilder(); |
||
168 | $sql->from(self::TABLE_NAME) |
||
169 | ->select('*') |
||
170 | ->where($sql->expr()->eq('id', $sql->createNamedParameter($id))); |
||
171 | $statement = $sql->executeQuery(); |
||
172 | $result = $statement->fetch(); |
||
173 | $statement->closeCursor(); |
||
174 | |||
175 | if ($result !== false) { |
||
176 | $result['dataset'] = (int) $result['dataset']; |
||
177 | $result['type'] = (int) $result['type']; |
||
178 | $result['parent'] = (int) $result['parent']; |
||
179 | $result['refresh'] = (int) $result['refresh']; |
||
180 | } |
||
181 | |||
182 | return $result; |
||
183 | } |
||
184 | |||
185 | /** |
||
186 | * update report |
||
187 | * @param $id |
||
188 | * @param $name |
||
189 | * @param $subheader |
||
190 | * @param $parent |
||
191 | * @param $link |
||
192 | * @param $visualization |
||
193 | * @param $chart |
||
194 | * @param $chartoptions |
||
195 | * @param $dataoptions |
||
196 | * @param $dimension1 |
||
197 | * @param $dimension2 |
||
198 | * @param $value |
||
199 | * @param $filteroptions |
||
200 | * @return bool |
||
201 | * @throws Exception |
||
202 | */ |
||
203 | public function update($id, $name, $subheader, $parent, $link, $visualization, $chart, $chartoptions, $dataoptions, $dimension1, $dimension2, $value, $filteroptions = null) |
||
204 | { |
||
205 | $name = $this->truncate($name, 64); |
||
206 | $sql = $this->db->getQueryBuilder(); |
||
207 | $sql->update(self::TABLE_NAME) |
||
208 | ->set('name', $sql->createNamedParameter($name)) |
||
209 | ->set('subheader', $sql->createNamedParameter($subheader)) |
||
210 | ->set('link', $sql->createNamedParameter($link)) |
||
211 | ->set('visualization', $sql->createNamedParameter($visualization)) |
||
212 | ->set('chart', $sql->createNamedParameter($chart)) |
||
213 | ->set('chartoptions', $sql->createNamedParameter($chartoptions)) |
||
214 | ->set('dataoptions', $sql->createNamedParameter($dataoptions)) |
||
215 | ->set('parent', $sql->createNamedParameter($parent)) |
||
216 | ->set('dimension1', $sql->createNamedParameter($dimension1)) |
||
217 | ->set('dimension2', $sql->createNamedParameter($dimension2)) |
||
218 | ->set('value', $sql->createNamedParameter($value)) |
||
219 | ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId))) |
||
220 | ->andWhere($sql->expr()->eq('id', $sql->createNamedParameter($id))); |
||
221 | if ($filteroptions !== null) $sql->set('filteroptions', $sql->createNamedParameter($filteroptions)); |
||
222 | $sql->executeStatement(); |
||
223 | return true; |
||
224 | } |
||
225 | |||
226 | /** |
||
227 | * update report options |
||
228 | * @param $id |
||
229 | * @param $chartoptions |
||
230 | * @param $dataoptions |
||
231 | * @param $filteroptions |
||
232 | * @param $tableoptions |
||
233 | * @return bool |
||
234 | * @throws Exception |
||
235 | */ |
||
236 | public function updateOptions($id, $chartoptions, $dataoptions, $filteroptions, $tableoptions) |
||
237 | { |
||
238 | $sql = $this->db->getQueryBuilder(); |
||
239 | $sql->update(self::TABLE_NAME) |
||
240 | ->set('chartoptions', $sql->createNamedParameter($chartoptions)) |
||
241 | ->set('dataoptions', $sql->createNamedParameter($dataoptions)) |
||
242 | ->set('filteroptions', $sql->createNamedParameter($filteroptions)) |
||
243 | ->set('tableoptions', $sql->createNamedParameter($tableoptions)) |
||
244 | ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId))) |
||
245 | ->andWhere($sql->expr()->eq('id', $sql->createNamedParameter($id))); |
||
246 | $sql->executeStatement(); |
||
247 | return true; |
||
248 | } |
||
249 | |||
250 | /** |
||
251 | * update report refresh interval |
||
252 | * @param $id |
||
253 | * @param $refresh |
||
254 | * @return bool |
||
255 | * @throws Exception |
||
256 | */ |
||
257 | public function updateRefresh($id, $refresh) |
||
258 | { |
||
259 | $sql = $this->db->getQueryBuilder(); |
||
260 | $sql->update(self::TABLE_NAME) |
||
261 | ->set('refresh', $sql->createNamedParameter($refresh)) |
||
262 | ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId))) |
||
263 | ->andWhere($sql->expr()->eq('id', $sql->createNamedParameter($id))); |
||
264 | $sql->executeStatement(); |
||
265 | return true; |
||
266 | } |
||
267 | |||
268 | /** |
||
269 | * update report group assignment (from drag & drop) |
||
270 | * @param $id |
||
271 | * @param $groupId |
||
272 | * @return bool |
||
273 | * @throws Exception |
||
274 | */ |
||
275 | public function updateGroup($id, $groupId) |
||
276 | { |
||
277 | $sql = $this->db->getQueryBuilder(); |
||
278 | $sql->update(self::TABLE_NAME) |
||
279 | ->set('parent', $sql->createNamedParameter($groupId)) |
||
280 | ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId))) |
||
281 | ->andWhere($sql->expr()->eq('id', $sql->createNamedParameter($id))); |
||
282 | $sql->executeStatement(); |
||
283 | return true; |
||
284 | } |
||
285 | |||
286 | /** |
||
287 | * read report options |
||
288 | * @param $id |
||
289 | * @return array |
||
290 | * @throws Exception |
||
291 | */ |
||
292 | public function readOptions($id) |
||
293 | { |
||
294 | $sql = $this->db->getQueryBuilder(); |
||
295 | $sql->from(self::TABLE_NAME) |
||
296 | ->select('name') |
||
297 | ->addSelect('visualization') |
||
298 | ->addSelect('chart') |
||
299 | ->addSelect('user_id') |
||
300 | ->where($sql->expr()->eq('id', $sql->createNamedParameter($id))); |
||
301 | $statement = $sql->executeQuery(); |
||
302 | $result = $statement->fetch(); |
||
303 | $statement->closeCursor(); |
||
304 | return $result; |
||
305 | } |
||
306 | |||
307 | /** |
||
308 | * delete report |
||
309 | * @param $id |
||
310 | * @return bool |
||
311 | * @throws Exception |
||
312 | */ |
||
313 | public function delete($id) |
||
314 | { |
||
315 | $sql = $this->db->getQueryBuilder(); |
||
316 | $sql->delete(self::TABLE_NAME) |
||
317 | ->where($sql->expr()->eq('id', $sql->createNamedParameter($id))); |
||
318 | $sql->executeStatement(); |
||
319 | return true; |
||
320 | } |
||
321 | |||
322 | /** |
||
323 | * search reports by search string |
||
324 | * @param $searchString |
||
325 | * @return array |
||
326 | * @throws Exception |
||
327 | */ |
||
328 | public function search($searchString) |
||
329 | { |
||
330 | $sql = $this->db->getQueryBuilder(); |
||
331 | $sql->from(self::TABLE_NAME) |
||
332 | ->select('id') |
||
333 | ->addSelect('name') |
||
334 | ->addSelect('type') |
||
335 | ->where($sql->expr()->eq('user_id', $sql->createNamedParameter($this->userId))) |
||
336 | ->andWhere($sql->expr()->iLike('name', $sql->createNamedParameter('%' . $this->db->escapeLikeParameter($searchString) . '%'))) |
||
337 | ->orderBy('name', 'ASC'); |
||
338 | $statement = $sql->executeQuery(); |
||
339 | $result = $statement->fetchAll(); |
||
340 | $statement->closeCursor(); |
||
341 | |||
342 | if ($result !== false && is_array($result)) { |
||
343 | foreach ($result as &$row) { |
||
344 | $row['type'] = (int) $row['type']; |
||
345 | } |
||
346 | unset($row); // Unset reference |
||
347 | } |
||
348 | |||
349 | return $result; |
||
350 | } |
||
351 | |||
352 | /** |
||
353 | * get the report owner |
||
354 | * @param $reportId |
||
355 | * @return int |
||
356 | * @throws Exception |
||
357 | */ |
||
358 | public function getOwner($reportId) |
||
359 | { |
||
360 | $sql = $this->db->getQueryBuilder(); |
||
361 | $sql->from(self::TABLE_NAME) |
||
362 | ->select('user_id') |
||
363 | ->where($sql->expr()->eq('id', $sql->createNamedParameter($reportId))); |
||
364 | return (string)$sql->executeQuery()->fetchOne(); |
||
0 ignored issues
–
show
|
|||
365 | } |
||
366 | |||
367 | /** |
||
368 | * get reports by group |
||
369 | * @param $groupId |
||
370 | * @return array |
||
371 | * @throws Exception |
||
372 | */ |
||
373 | public function getReportsByGroup($groupId) |
||
374 | { |
||
375 | $sql = $this->db->getQueryBuilder(); |
||
376 | $sql->from(self::TABLE_NAME) |
||
377 | ->select('*') |
||
378 | ->where($sql->expr()->eq('parent', $sql->createNamedParameter($groupId))); |
||
379 | $statement = $sql->executeQuery(); |
||
380 | $result = $statement->fetchAll(); |
||
381 | $statement->closeCursor(); |
||
382 | |||
383 | if ($result !== false && is_array($result)) { |
||
384 | foreach ($result as &$row) { |
||
385 | $row['dataset'] = (int) $row['dataset']; |
||
386 | $row['type'] = (int) $row['type']; |
||
387 | $row['parent'] = (int) $row['parent']; |
||
388 | } |
||
389 | unset($row); // Unset reference |
||
390 | } |
||
391 | |||
392 | return $result; |
||
393 | } |
||
394 | |||
395 | /** |
||
396 | * reports for a dataset |
||
397 | * @param $datasetId |
||
398 | * @return array |
||
399 | * @throws Exception |
||
400 | */ |
||
401 | public function reportsForDataset($datasetId) |
||
402 | { |
||
403 | $sql = $this->db->getQueryBuilder(); |
||
404 | $sql->from(self::TABLE_NAME) |
||
405 | ->select('id') |
||
406 | ->addSelect('name') |
||
407 | ->addSelect('user_id') |
||
408 | ->where($sql->expr()->eq('dataset', $sql->createNamedParameter($datasetId))); |
||
409 | $statement = $sql->executeQuery(); |
||
410 | $result = $statement->fetchAll(); |
||
411 | $statement->closeCursor(); |
||
412 | return $result; |
||
413 | } |
||
414 | |||
415 | /** |
||
416 | * truncates fiels do DB-field size |
||
417 | * |
||
418 | * @param $string |
||
419 | * @param $length |
||
420 | * @param $dots |
||
421 | * @return string |
||
422 | */ |
||
423 | private function truncate($string, $length, $dots = "...") |
||
424 | { |
||
425 | return (strlen($string) > $length) ? mb_strcut($string, 0, $length - strlen($dots)) . $dots : $string; |
||
426 | } |
||
427 | } |
The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g.
excluded_paths: ["lib/*"]
, you can move it to the dependency path list as follows:For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths