Issues (496)

lib/Db/ReportMapper.php (5 issues)

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
The type OCP\DB\Exception 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. excluded_paths: ["lib/*"], you can move it to the dependency path list as follows:

filter:
    dependency_paths: ["lib/*"]

For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths

Loading history...
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. excluded_paths: ["lib/*"], you can move it to the dependency path list as follows:

filter:
    dependency_paths: ["lib/*"]

For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths

Loading history...
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. excluded_paths: ["lib/*"], you can move it to the dependency path list as follows:

filter:
    dependency_paths: ["lib/*"]

For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths

Loading history...
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. excluded_paths: ["lib/*"], you can move it to the dependency path list as follows:

filter:
    dependency_paths: ["lib/*"]

For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths

Loading history...
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
Bug Best Practice introduced by
The expression return (string)$sql->executeQuery()->fetchOne() returns the type string which is incompatible with the documented return type integer.
Loading history...
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
}