DbController::getDatasetByToken() - Code Metrics - Inspection of "activate --enable-security-analysis" - Rello/analytics - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Branch — master (7b1276)

by Marcel

created 2019-11-20 21:26 UTC

DbController::getDatasetByToken() A

↳ Parent: DbController

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	4
c	0
b	0
f	0
nc	1
nop	1
dl	0
loc	6
rs	10

<?php
/**
 * Data Analytics
 *
 * This file is licensed under the Affero General Public License version 3 or
 * later. See the LICENSE.md file.
 *
 * @author Marcel Scherello <[email protected]>
 * @copyright 2019 Marcel Scherello
 */

namespace OCA\Analytics\Controller;

use OCP\AppFramework\Controller;
use OCP\IDbConnection;
use OCP\IL10N;
use OCP\ILogger;
use OCP\IRequest;

/**
 * Controller class for main page.
 */
class DbController extends Controller
{

    private $userId;
    private $l10n;
    private $db;
    private $logger;

    public function __construct(
        $appName,
        IRequest $request,
        $userId,
        IL10N $l10n,
        IDbConnection $db,
        ILogger $logger
    )
    {
        parent::__construct($appName, $request);
        $this->userId = $userId;
        $this->l10n = $l10n;
        $this->db = $db;
        $this->logger = $logger;
    }


    /**
     * truncates fiels do DB-field size
     *
     * @param $string
     * @param $length
     * @param $dots
     * @return string
     */
    private function truncate($string, $length, $dots = "...")
    {
        return (strlen($string) > $length) ? mb_strcut($string, 0, $length - strlen($dots)) . $dots : $string;
    }

    /**
     * Get file id for single track
     * @param int $dataset
     * @param  $objectDrilldown
     * @param  $dateDrilldown
     * @return array
     */
    public function getData(int $dataset, $objectDrilldown = null, $dateDrilldown = null)
    {
        $SQL = 'SELECT';
        if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
        if ($dateDrilldown === 'true') $SQL .= ' `dimension2`,';
        $SQL .= ' SUM(`dimension3`) AS `dimension3`';
        $SQL .= ' FROM `*PREFIX*analytics_facts`
                WHERE `dataset` = ?
                GROUP BY `dataset`';
        if ($objectDrilldown === 'true') $SQL .= ', `dimension1`';
        if ($dateDrilldown === 'true') $SQL .= ', `dimension2`';
        $SQL .= ' ORDER BY';
        if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
        $SQL .= ' `dimension2` ASC';

        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($dataset));
        return $stmt->fetchAll();
    }

    /**
     * update data
     */
    public function updateData()
    {
    }

    /**
     * create data
     */
    public function deleteData()
    {
    }

    /**
     * create data
     */
    public function createData($datasetId, $dimension1, $dimension2, $dimension3)
    {
        $SQL = 'SELECT `id` FROM `*PREFIX*analytics_facts` WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2));
        $row = $stmt->fetch();
        if ($row) {
            $SQL = 'UPDATE `*PREFIX*analytics_facts` SET `dimension3` = ? WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
            $stmt = $this->db->prepare($SQL);
            $stmt->execute(array($dimension3, $this->userId, $datasetId, $dimension1, $dimension2));
            //$stmt->fetch();
            return 'update';
        } else {
            $SQL = 'INSERT INTO `*PREFIX*analytics_facts` (`user_id`,`dataset`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?)';
            $stmt = $this->db->prepare($SQL);
            $stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2, $dimension3));
            return 'insert';
        }
    }

    /**
     * create dataset
     */
    public function createDataset()
    {
        $SQL = 'INSERT INTO `*PREFIX*analytics_dataset` (`user_id`,`name`,`type`,`parent`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?,?,?)';
        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId, $this->l10n->t('New'), 2, 0, $this->l10n->t('Objekt'), $this->l10n->t('Date'), $this->l10n->t('Value')));
        return true;
    }

    /**
     * update dataset
     * @param $id
     * @param $name
     * @param $parent
     * @param $type
     * @param $link
     * @param $visualization
     * @param $chart
     * @param $dimension1
     * @param $dimension2
     * @param $dimension3
     * @return
     */
    public function updateDataset($id, $name, $parent, $type, $link, $visualization, $chart, $dimension1, $dimension2, $dimension3)
    {
        $SQL = 'UPDATE `*PREFIX*analytics_dataset` SET `name`= ?, `type`= ?, `link`= ?, `visualization`= ?, `chart`= ?, `parent`= ?, `dimension1` = ?, `dimension2` = ?, `dimension3` = ? WHERE `user_id` = ? AND `id` = ?';
        $stmt = $this->db->prepare($SQL);
        $name = $this->truncate($name, 64);
        $stmt->execute(array($name, $type, $link, $visualization, $chart, $parent, $dimension1, $dimension2, $dimension3, $this->userId, $id));
        return true;
    }

    /**
     * delete dataset
     * @param $id
     * @return
     */
    public function deleteDataset($id)
    {
        $SQL = 'DELETE FROM `*PREFIX*analytics_dataset` WHERE `user_id` = ? AND `id` = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId, $id));
        return true;
    }

    /**
     * get datasets
     */
    public function getDatasets()
    {
        $SQL = 'SELECT id, name, type, parent FROM `*PREFIX*analytics_dataset` WHERE  `user_id` = ? ORDER BY `parent` ASC, `name` ASC';
        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($this->userId));
        return $stmt->fetchAll();
    }

    /**
     * get datasets
     * @param $id
     * @return
     */
    public function getOwnDataset($id)
    {
        $SQL = 'SELECT * FROM `*PREFIX*analytics_dataset` WHERE `id` = ? AND `user_id` = ?';
        //$this->logger->error($SQL);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($id, $this->userId));
        return $stmt->fetch();
    }

    /**
     * get datasets
     * @param $id
     * @return
     */
    public function getDatasetOptions($id)
    {
        $SQL = 'SELECT `name`, `visualization`, `chart` FROM `*PREFIX*analytics_dataset` WHERE `id` = ?';
        //$this->logger->error($SQL);

        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($id));
        return $stmt->fetch();
    }

    // ********** SHARE ************
    // ********** SHARE ************
    // ********** SHARE ************

    /**
     * get datasets
     * @param $token
     * @return
     */
    public function getDatasetByToken($token)
    {
        $SQL = 'SELECT DS.*, SH.password AS password FROM `*PREFIX*analytics_dataset` AS DS JOIN `*PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.token = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$token]);
        return $stmt->fetch();
        //$this->logger->error($results['password']);
    }

    /**
     * get shared datasets
     * @return
     */
    public function getSharedDatasets()
    {
        $SQL = 'SELECT DS.id, DS.name, \'99\' as type, 0 as parent FROM `*PREFIX*analytics_dataset` AS DS JOIN `*PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? ORDER BY DS.name ASC';
        //$this->logger->error($this->userId);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId]);
        return $stmt->fetchAll();
    }

    /**
     * get shared datasets
     * @return
     */
    public function getSharedDataset($id)
    {
        $SQL = 'SELECT DS.* FROM `*PREFIX*analytics_dataset` AS DS JOIN `*PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? AND DS.id = ?';
        //$this->logger->error($id);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $id]);
        return $stmt->fetch();
    }

    public function createShare($datasetId, $type, $uid_owner, $token)
    {
        $SQL = 'INSERT INTO `*PREFIX*analytics_share` (`dataset`,`type`,`uid_owner`,`uid_initiator`,`token`) VALUES(?,?,?,?,?)';
        //$this->logger->error($datasetId, $type, $uid_owner, $token);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute(array($datasetId, $type, $uid_owner, $this->userId, $token));
        return true;
    }

    public function getShares($datasetId)
    {
        $SQL = 'SELECT id, type, uid_owner, token, (CASE  WHEN password IS NOT NULL THEN true ELSE false END) AS pass FROM `*PREFIX*analytics_share` WHERE uid_initiator = ? AND dataset = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $datasetId]);
        return $stmt->fetchAll();
    }

    public function getSharedReceiver($datasetId)
    {
        $SQL = 'SELECT uid_owner FROM `*PREFIX*analytics_share` WHERE uid_initiator = ? AND dataset = ? AND type = 0';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $datasetId]);
        return $stmt->fetchAll();
    }

    public function updateShare($shareId, $password)
    {
        $SQL = 'UPDATE `*PREFIX*analytics_share` SET `password`= ? WHERE `uid_initiator` = ? AND `id` = ?';
        //$this->logger->error($shareId. $password);
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$password, $this->userId, $shareId]);
        return true;
    }

    public function deleteShare($shareId)
    {
        $SQL = 'DELETE FROM `*PREFIX*analytics_share` WHERE `uid_initiator` = ? AND `id` = ?';
        $stmt = $this->db->prepare($SQL);
        $stmt->execute([$this->userId, $shareId]);
        return true;
    }

}


1			<?php
2			/**
3			* Data Analytics
4			*
5			* This file is licensed under the Affero General Public License version 3 or
6			* later. See the LICENSE.md file.
7			*
8			* @author Marcel Scherello <[email protected]>
9			* @copyright 2019 Marcel Scherello
10			*/
11
12			namespace OCA\Analytics\Controller;
13
14			use OCP\AppFramework\Controller;
15			use OCP\IDbConnection;
16			use OCP\IL10N;
17			use OCP\ILogger;
18			use OCP\IRequest;
19
20			/**
21			* Controller class for main page.
22			*/
23			class DbController extends Controller
24			{
25
26			private $userId;
27			private $l10n;
28			private $db;
29			private $logger;
30
31			public function __construct(
32			$appName,
33			IRequest $request,
34			$userId,
35			IL10N $l10n,
36			IDbConnection $db,
37			ILogger $logger
38			)
39			{
40			parent::__construct($appName, $request);
41			$this->userId = $userId;
42			$this->l10n = $l10n;
43			$this->db = $db;
44			$this->logger = $logger;
45			}
46
47
48			/**
49			* truncates fiels do DB-field size
50			*
51			* @param $string
52			* @param $length
53			* @param $dots
54			* @return string
55			*/
56			private function truncate($string, $length, $dots = "...")
57			{
58			return (strlen($string) > $length) ? mb_strcut($string, 0, $length - strlen($dots)) . $dots : $string;
59			}
60
61			/**
62			* Get file id for single track
63			* @param int $dataset
64			* @param $objectDrilldown
65			* @param $dateDrilldown
66			* @return array
67			*/
68			public function getData(int $dataset, $objectDrilldown = null, $dateDrilldown = null)
69			{
70			$SQL = 'SELECT';
71			if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
72			if ($dateDrilldown === 'true') $SQL .= ' `dimension2`,';
73			$SQL .= ' SUM(`dimension3`) AS `dimension3`';
74			$SQL .= ' FROM `PREFIXanalytics_facts`
75			WHERE `dataset` = ?
76			GROUP BY `dataset`';
77			if ($objectDrilldown === 'true') $SQL .= ', `dimension1`';
78			if ($dateDrilldown === 'true') $SQL .= ', `dimension2`';
79			$SQL .= ' ORDER BY';
80			if ($objectDrilldown === 'true') $SQL .= ' `dimension1`,';
81			$SQL .= ' `dimension2` ASC';
82
83			//$this->logger->error($SQL);
84
85			$stmt = $this->db->prepare($SQL);
86			$stmt->execute(array($dataset));
87			return $stmt->fetchAll();
88			}
89
90			/**
91			* update data
92			*/
93			public function updateData()
94			{
95			}
96
97			/**
98			* create data
99			*/
100			public function deleteData()
101			{
102			}
103
104			/**
105			* create data
106			*/
107			public function createData($datasetId, $dimension1, $dimension2, $dimension3)
108			{
109			$SQL = 'SELECT `id` FROM `PREFIXanalytics_facts` WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
110			$stmt = $this->db->prepare($SQL);
111			$stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2));
112			$row = $stmt->fetch();
113			if ($row) {
114			$SQL = 'UPDATE `PREFIXanalytics_facts` SET `dimension3` = ? WHERE `user_id` = ? AND `dataset` = ? AND `dimension1` = ? AND `dimension2` = ?';
115			$stmt = $this->db->prepare($SQL);
116			$stmt->execute(array($dimension3, $this->userId, $datasetId, $dimension1, $dimension2));
117			//$stmt->fetch();
118			return 'update';
119			} else {
120			$SQL = 'INSERT INTO `PREFIXanalytics_facts` (`user_id`,`dataset`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?)';
121			$stmt = $this->db->prepare($SQL);
122			$stmt->execute(array($this->userId, $datasetId, $dimension1, $dimension2, $dimension3));
123			return 'insert';
124			}
125			}
126
127			/**
128			* create dataset
129			*/
130			public function createDataset()
131			{
132			$SQL = 'INSERT INTO `PREFIXanalytics_dataset` (`user_id`,`name`,`type`,`parent`,`dimension1`,`dimension2`,`dimension3`) VALUES(?,?,?,?,?,?,?)';
133			//$this->logger->error($SQL);
134
135			$stmt = $this->db->prepare($SQL);
136			$stmt->execute(array($this->userId, $this->l10n->t('New'), 2, 0, $this->l10n->t('Objekt'), $this->l10n->t('Date'), $this->l10n->t('Value')));
137			return true;
138			}
139
140			/**
141			* update dataset
142			* @param $id
143			* @param $name
144			* @param $parent
145			* @param $type
146			* @param $link
147			* @param $visualization
148			* @param $chart
149			* @param $dimension1
150			* @param $dimension2
151			* @param $dimension3
152			* @return
153			*/
154			public function updateDataset($id, $name, $parent, $type, $link, $visualization, $chart, $dimension1, $dimension2, $dimension3)
155			{
156			$SQL = 'UPDATE `PREFIXanalytics_dataset` SET `name`= ?, `type`= ?, `link`= ?, `visualization`= ?, `chart`= ?, `parent`= ?, `dimension1` = ?, `dimension2` = ?, `dimension3` = ? WHERE `user_id` = ? AND `id` = ?';
157			$stmt = $this->db->prepare($SQL);
158			$name = $this->truncate($name, 64);
159			$stmt->execute(array($name, $type, $link, $visualization, $chart, $parent, $dimension1, $dimension2, $dimension3, $this->userId, $id));
160			return true;
161			}
162
163			/**
164			* delete dataset
165			* @param $id
166			* @return
167			*/
168			public function deleteDataset($id)
169			{
170			$SQL = 'DELETE FROM `PREFIXanalytics_dataset` WHERE `user_id` = ? AND `id` = ?';
171			$stmt = $this->db->prepare($SQL);
172			$stmt->execute(array($this->userId, $id));
173			return true;
174			}
175
176			/**
177			* get datasets
178			*/
179			public function getDatasets()
180			{
181			$SQL = 'SELECT id, name, type, parent FROM `PREFIXanalytics_dataset` WHERE `user_id` = ? ORDER BY `parent` ASC, `name` ASC';
182			//$this->logger->error($SQL);
183
184			$stmt = $this->db->prepare($SQL);
185			$stmt->execute(array($this->userId));
186			return $stmt->fetchAll();
187			}
188
189			/**
190			* get datasets
191			* @param $id
192			* @return
193			*/
194			public function getOwnDataset($id)
195			{
196			$SQL = 'SELECT * FROM `PREFIXanalytics_dataset` WHERE `id` = ? AND `user_id` = ?';
197			//$this->logger->error($SQL);
198			$stmt = $this->db->prepare($SQL);
199			$stmt->execute(array($id, $this->userId));
200			return $stmt->fetch();
201			}
202
203			/**
204			* get datasets
205			* @param $id
206			* @return
207			*/
208			public function getDatasetOptions($id)
209			{
210			$SQL = 'SELECT `name`, `visualization`, `chart` FROM `PREFIXanalytics_dataset` WHERE `id` = ?';
211			//$this->logger->error($SQL);
212
213			$stmt = $this->db->prepare($SQL);
214			$stmt->execute(array($id));
215			return $stmt->fetch();
216			}
217
218			// ******** SHARE **********
219			// ******** SHARE **********
220			// ******** SHARE **********
221
222			/**
223			* get datasets
224			* @param $token
225			* @return
226			*/
227			public function getDatasetByToken($token)
228			{
229			$SQL = 'SELECT DS., SH.password AS password FROM `PREFIXanalytics_dataset` AS DS JOIN `PREFIX*analytics_share` AS SH ON DS.id = SH.dataset WHERE SH.token = ?';
230			$stmt = $this->db->prepare($SQL);
231			$stmt->execute([$token]);
232			return $stmt->fetch();
233			//$this->logger->error($results['password']);
234			}
235
236			/**
237			* get shared datasets
238			* @return
239			*/
240			public function getSharedDatasets()
241			{
242			$SQL = 'SELECT DS.id, DS.name, \'99\' as type, 0 as parent FROM `PREFIXanalytics_dataset` AS DS JOIN `PREFIXanalytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? ORDER BY DS.name ASC';
243			//$this->logger->error($this->userId);
244			$stmt = $this->db->prepare($SQL);
245			$stmt->execute([$this->userId]);
246			return $stmt->fetchAll();
247			}
248
249			/**
250			* get shared datasets
251			* @return
252			*/
253			public function getSharedDataset($id)
254			{
255			$SQL = 'SELECT DS.* FROM `PREFIXanalytics_dataset` AS DS JOIN `PREFIXanalytics_share` AS SH ON DS.id = SH.dataset WHERE SH.uid_owner = ? AND DS.id = ?';
256			//$this->logger->error($id);
257			$stmt = $this->db->prepare($SQL);
258			$stmt->execute([$this->userId, $id]);
259			return $stmt->fetch();
260			}
261
262			public function createShare($datasetId, $type, $uid_owner, $token)
263			{
264			$SQL = 'INSERT INTO `PREFIXanalytics_share` (`dataset`,`type`,`uid_owner`,`uid_initiator`,`token`) VALUES(?,?,?,?,?)';
265			//$this->logger->error($datasetId, $type, $uid_owner, $token);
266			$stmt = $this->db->prepare($SQL);
267			$stmt->execute(array($datasetId, $type, $uid_owner, $this->userId, $token));
268			return true;
269			}
270
271			public function getShares($datasetId)
272			{
273			$SQL = 'SELECT id, type, uid_owner, token, (CASE WHEN password IS NOT NULL THEN true ELSE false END) AS pass FROM `PREFIXanalytics_share` WHERE uid_initiator = ? AND dataset = ?';
274			$stmt = $this->db->prepare($SQL);
275			$stmt->execute([$this->userId, $datasetId]);
276			return $stmt->fetchAll();
277			}
278
279			public function getSharedReceiver($datasetId)
280			{
281			$SQL = 'SELECT uid_owner FROM `PREFIXanalytics_share` WHERE uid_initiator = ? AND dataset = ? AND type = 0';
282			$stmt = $this->db->prepare($SQL);
283			$stmt->execute([$this->userId, $datasetId]);
284			return $stmt->fetchAll();
285			}
286
287			public function updateShare($shareId, $password)
288			{
289			$SQL = 'UPDATE `PREFIXanalytics_share` SET `password`= ? WHERE `uid_initiator` = ? AND `id` = ?';
290			//$this->logger->error($shareId. $password);
291			$stmt = $this->db->prepare($SQL);
292			$stmt->execute([$password, $this->userId, $shareId]);
293			return true;
294			}
295
296			public function deleteShare($shareId)
297			{
298			$SQL = 'DELETE FROM `PREFIXanalytics_share` WHERE `uid_initiator` = ? AND `id` = ?';
299			$stmt = $this->db->prepare($SQL);
300			$stmt->execute([$this->userId, $shareId]);
301			return true;
302			}
303
304			}
305

Rello / analytics

Branch — master (7b1276)

DbController::getDatasetByToken() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like