1 | <?php |
||
2 | /** |
||
3 | * Audio Player |
||
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 | * @author Sebastian Doell <[email protected]> |
||
10 | * @copyright 2016-2019 Marcel Scherello |
||
11 | * @copyright 2015 Sebastian Doell |
||
12 | */ |
||
13 | |||
14 | namespace OCA\audioplayer\Controller; |
||
15 | |||
16 | use OCP\AppFramework\Controller; |
||
17 | use OCP\AppFramework\Http\JSONResponse as JSONResponseAlias; |
||
18 | use OCP\IRequest; |
||
19 | use OCP\IL10N; |
||
20 | use OCP\IDbConnection; |
||
21 | use OCP\Share\IManager; |
||
22 | use OCP\ILogger; |
||
23 | use OCP\ITagManager; |
||
24 | |||
25 | /** |
||
26 | * Controller class for main page. |
||
27 | */ |
||
28 | class DbController extends Controller |
||
29 | { |
||
30 | |||
31 | private $userId; |
||
32 | private $l10n; |
||
33 | private $db; |
||
34 | private $occ_job; |
||
35 | private $shareManager; |
||
36 | private $tagManager; |
||
37 | private $logger; |
||
38 | |||
39 | public function __construct( |
||
40 | $appName, |
||
41 | IRequest $request, |
||
42 | $userId, |
||
43 | IL10N $l10n, |
||
44 | IDbConnection $db, |
||
45 | ITagManager $tagManager, |
||
46 | IManager $shareManager, |
||
47 | ILogger $logger |
||
48 | ) |
||
49 | { |
||
50 | parent::__construct($appName, $request); |
||
51 | $this->userId = $userId; |
||
52 | $this->l10n = $l10n; |
||
53 | $this->db = $db; |
||
54 | $this->shareManager = $shareManager; |
||
55 | $this->tagManager = $tagManager; |
||
56 | $this->logger = $logger; |
||
57 | } |
||
58 | |||
59 | public function loadArtistsToAlbum($iAlbumId, $ARtistID) |
||
60 | { |
||
61 | # load albumartist if available |
||
62 | # if no albumartist, we will load all artists from the tracks |
||
63 | # if all the same - display it as album artist |
||
64 | # if different track-artists, display "various" |
||
65 | if ((int)$ARtistID !== 0) { |
||
66 | $stmt = $this->db->prepare('SELECT `name` FROM `*PREFIX*audioplayer_artists` WHERE `id` = ?'); |
||
67 | $stmt->execute(array($ARtistID)); |
||
68 | $row = $stmt->fetch(); |
||
69 | return $row['name']; |
||
70 | } else { |
||
71 | $stmt = $this->db->prepare('SELECT DISTINCT(`artist_id`) FROM `*PREFIX*audioplayer_tracks` WHERE `album_id` = ?'); |
||
72 | $stmt->execute(array($iAlbumId)); |
||
73 | $TArtist = $stmt->fetch(); |
||
74 | $rowCount = $stmt->rowCount(); |
||
75 | |||
76 | if ($rowCount === 1) { |
||
77 | $stmt = $this->db->prepare('SELECT `name` FROM `*PREFIX*audioplayer_artists` WHERE `id` = ?'); |
||
78 | $stmt->execute(array($TArtist['artist_id'])); |
||
79 | $row = $stmt->fetch(); |
||
80 | return $row['name']; |
||
81 | } else { |
||
82 | return (string)$this->l10n->t('Various Artists'); |
||
83 | } |
||
84 | } |
||
85 | } |
||
86 | |||
87 | /** |
||
88 | * @NoAdminRequired |
||
89 | * @param $searchquery |
||
90 | * @return array |
||
91 | */ |
||
92 | public function search($searchquery) |
||
93 | { |
||
94 | $searchresult = array(); |
||
95 | $SQL = "SELECT `id`,`name` FROM `*PREFIX*audioplayer_albums` WHERE (LOWER(`name`) LIKE LOWER(?)) AND `user_id` = ?"; |
||
96 | $stmt = $this->db->prepare($SQL); |
||
97 | $stmt->execute(array('%' . addslashes($searchquery) . '%', $this->userId)); |
||
98 | $results = $stmt->fetchAll(); |
||
99 | if (!is_null($results)) { |
||
100 | foreach ($results as $row) { |
||
101 | $searchresult[] = [ |
||
102 | 'id' => 'Album-' . $row['id'], |
||
103 | 'name' => $this->l10n->t('Album') . ': ' . $row['name'], |
||
104 | ]; |
||
105 | } |
||
106 | } |
||
107 | |||
108 | $SQL = "SELECT `AA`.`id`, `AA`.`name` |
||
109 | FROM `*PREFIX*audioplayer_artists` `AA` |
||
110 | JOIN `*PREFIX*audioplayer_tracks` `AT` |
||
111 | ON `AA`.`id` = `AT`.`artist_id` |
||
112 | WHERE (LOWER(`AA`.`name`) LIKE LOWER(?)) AND `AA`.`user_id` = ?"; |
||
113 | $stmt = $this->db->prepare($SQL); |
||
114 | $stmt->execute(array('%' . addslashes($searchquery) . '%', $this->userId)); |
||
115 | $results = $stmt->fetchAll(); |
||
116 | if (!is_null($results)) { |
||
117 | foreach ($results as $row) { |
||
118 | $searchresult[] = [ |
||
119 | 'id' => 'Artist-' . $row['id'], |
||
120 | 'name' => $this->l10n->t('Artist') . ': ' . $row['name'], |
||
121 | ]; |
||
122 | } |
||
123 | } |
||
124 | |||
125 | $SQL = "SELECT `album_id`, `title` FROM `*PREFIX*audioplayer_tracks` WHERE (LOWER(`title`) LIKE LOWER(?)) AND `user_id` = ?"; |
||
126 | $stmt = $this->db->prepare($SQL); |
||
127 | $stmt->execute(array('%' . addslashes($searchquery) . '%', $this->userId)); |
||
128 | $results = $stmt->fetchAll(); |
||
129 | if (!is_null($results)) { |
||
130 | foreach ($results as $row) { |
||
131 | $searchresult[] = [ |
||
132 | 'id' => 'Album-' . $row['album_id'], |
||
133 | 'name' => $this->l10n->t('Title') . ': ' . $row['title'], |
||
134 | ]; |
||
135 | } |
||
136 | } |
||
137 | return $searchresult; |
||
138 | } |
||
139 | |||
140 | /** |
||
141 | * @NoAdminRequired |
||
142 | * @param string $userId |
||
143 | * @param $output |
||
144 | * @param $hook |
||
145 | * |
||
146 | * @return bool|JSONResponseAlias |
||
147 | */ |
||
148 | public function resetMediaLibrary($userId = null, $output = null, $hook = null) |
||
149 | { |
||
150 | |||
151 | if ($userId !== null) { |
||
152 | $this->occ_job = true; |
||
153 | $this->userId = $userId; |
||
154 | } else { |
||
155 | $this->occ_job = false; |
||
156 | } |
||
157 | |||
158 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_tracks` WHERE `user_id` = ?'); |
||
159 | $stmt->execute(array($this->userId)); |
||
160 | |||
161 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_artists` WHERE `user_id` = ?'); |
||
162 | $stmt->execute(array($this->userId)); |
||
163 | |||
164 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_genre` WHERE `user_id` = ?'); |
||
165 | $stmt->execute(array($this->userId)); |
||
166 | |||
167 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_albums` WHERE `user_id` = ?'); |
||
168 | $stmt->execute(array($this->userId)); |
||
169 | |||
170 | $stmt = $this->db->prepare('SELECT `id` FROM `*PREFIX*audioplayer_playlists` WHERE `user_id` = ?'); |
||
171 | $stmt->execute(array($this->userId)); |
||
172 | $results = $stmt->fetchAll(); |
||
173 | if (!is_null($results)) { |
||
174 | foreach ($results as $row) { |
||
175 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_playlist_tracks` WHERE `playlist_id` = ?'); |
||
176 | $stmt->execute(array($row['id'])); |
||
177 | } |
||
178 | } |
||
179 | |||
180 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_playlists` WHERE `user_id` = ?'); |
||
181 | $stmt->execute(array($this->userId)); |
||
182 | |||
183 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_stats` WHERE `user_id` = ?'); |
||
184 | $stmt->execute(array($this->userId)); |
||
185 | |||
186 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_streams` WHERE `user_id` = ?'); |
||
187 | $stmt->execute(array($this->userId)); |
||
188 | |||
189 | $result = [ |
||
190 | 'status' => 'success', |
||
191 | 'msg' => 'all good' |
||
192 | ]; |
||
193 | |||
194 | // applies if scanner is not started via occ |
||
195 | if (!$this->occ_job) { |
||
196 | $response = new JSONResponseAlias(); |
||
197 | $response->setData($result); |
||
198 | return $response; |
||
199 | } elseif ($hook === null) { |
||
200 | $output->writeln("Reset finished"); |
||
201 | } else { |
||
202 | $this->logger->info('Library of "' . $userId . '" reset due to user deletion', array('app' => 'audioplayer')); |
||
203 | } |
||
204 | return true; |
||
205 | } |
||
206 | |||
207 | /** |
||
208 | * Delete single title from audio player tables |
||
209 | * @NoAdminRequired |
||
210 | * @param int $file_id |
||
211 | * @param int $userId |
||
212 | * @return bool |
||
213 | */ |
||
214 | public function deleteFromDB($file_id, $userId = null) |
||
215 | { |
||
216 | // check if scanner is started from web or occ |
||
217 | if ($userId !== null) { |
||
218 | $this->userId = $userId; |
||
219 | } |
||
220 | $this->logger->debug('deleteFromDB: ' . $file_id, array('app' => 'audioplayer')); |
||
221 | |||
222 | $stmt = $this->db->prepare('SELECT `album_id`, `id` FROM `*PREFIX*audioplayer_tracks` WHERE `file_id` = ? AND `user_id` = ?'); |
||
223 | $stmt->execute(array($file_id, $this->userId)); |
||
224 | $row = $stmt->fetch(); |
||
225 | $AlbumId = $row['album_id']; |
||
226 | $TrackId = $row['id']; |
||
227 | |||
228 | $stmt = $this->db->prepare('SELECT COUNT(`album_id`) AS `ALBUMCOUNT` FROM `*PREFIX*audioplayer_tracks` WHERE `album_id` = ? '); |
||
229 | $stmt->execute(array($AlbumId)); |
||
230 | $row = $stmt->fetch(); |
||
231 | if ((int)$row['ALBUMCOUNT'] === 1) { |
||
232 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_albums` WHERE `id` = ? AND `user_id` = ?'); |
||
233 | $stmt->execute(array($AlbumId, $this->userId)); |
||
234 | } |
||
235 | |||
236 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_tracks` WHERE `file_id` = ? AND `user_id` = ?'); |
||
237 | $stmt->execute(array($file_id, $this->userId)); |
||
238 | |||
239 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_streams` WHERE `file_id` = ? AND `user_id` = ?'); |
||
240 | $stmt->execute(array($file_id, $this->userId)); |
||
241 | |||
242 | $stmt = $this->db->prepare('SELECT `playlist_id` FROM `*PREFIX*audioplayer_playlist_tracks` WHERE `track_id` = ?'); |
||
243 | $stmt->execute(array($TrackId)); |
||
244 | $row = $stmt->fetch(); |
||
245 | $PlaylistId = $row['playlist_id']; |
||
246 | |||
247 | $stmt = $this->db->prepare('SELECT COUNT(`playlist_id`) AS `PLAYLISTCOUNT` FROM `*PREFIX*audioplayer_playlist_tracks` WHERE `playlist_id` = ? '); |
||
248 | $stmt->execute(array($PlaylistId)); |
||
249 | $row = $stmt->fetch(); |
||
250 | if ((int)$row['PLAYLISTCOUNT'] === 1) { |
||
251 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_playlists` WHERE `id` = ? AND `user_id` = ?'); |
||
252 | $stmt->execute(array($PlaylistId, $this->userId)); |
||
253 | } |
||
254 | |||
255 | $stmt = $this->db->prepare('DELETE FROM `*PREFIX*audioplayer_playlist_tracks` WHERE `track_id` = ?'); |
||
256 | $stmt->execute(array($TrackId)); |
||
257 | return true; |
||
258 | } |
||
259 | |||
260 | /** |
||
261 | * write cover image data to album |
||
262 | * @param int $userId |
||
263 | * @param integer $iAlbumId |
||
264 | * @param string $sImage |
||
265 | * @return true |
||
266 | */ |
||
267 | public function writeCoverToAlbum($userId, $iAlbumId, $sImage) |
||
268 | { |
||
269 | $stmt = $this->db->prepare('UPDATE `*PREFIX*audioplayer_albums` SET `cover`= ?, `bgcolor`= ? WHERE `id` = ? AND `user_id` = ?'); |
||
270 | $stmt->execute(array($sImage, '', $iAlbumId, $userId)); |
||
271 | return true; |
||
272 | } |
||
273 | |||
274 | /** |
||
275 | * Add album to db if not exist |
||
276 | * @param int $userId |
||
277 | * @param string $sAlbum |
||
278 | * @param string $sYear |
||
279 | * @param int $iArtistId |
||
280 | * @param int $parentId |
||
281 | * @return array |
||
282 | * @throws \Doctrine\DBAL\DBALException |
||
283 | */ |
||
284 | public function writeAlbumToDB($userId, $sAlbum, $sYear, $iArtistId, $parentId) |
||
285 | { |
||
286 | $sAlbum = $this->truncate($sAlbum, '256'); |
||
287 | $sYear = $this->normalizeInteger($sYear); |
||
288 | $AlbumCount = 0; |
||
289 | |||
290 | $stmt = $this->db->prepare('SELECT `id`, `artist_id` FROM `*PREFIX*audioplayer_albums` WHERE `user_id` = ? AND `name` = ? AND `folder_id` = ?'); |
||
291 | $stmt->execute(array($userId, $sAlbum, $parentId)); |
||
292 | $row = $stmt->fetch(); |
||
293 | if ($row) { |
||
294 | if ((int)$row['artist_id'] !== (int)$iArtistId) { |
||
295 | $various_id = $this->writeArtistToDB($userId, $this->l10n->t('Various Artists')); |
||
296 | $stmt = $this->db->prepare('UPDATE `*PREFIX*audioplayer_albums` SET `artist_id`= ? WHERE `id` = ? AND `user_id` = ?'); |
||
297 | $stmt->execute(array($various_id, $row['id'], $userId)); |
||
298 | } |
||
299 | $insertid = $row['id']; |
||
300 | } else { |
||
301 | $stmt = $this->db->prepare('INSERT INTO `*PREFIX*audioplayer_albums` (`user_id`,`name`,`folder_id`) VALUES(?,?,?)'); |
||
302 | $stmt->execute(array($userId, $sAlbum, $parentId)); |
||
303 | $insertid = $this->db->lastInsertId('*PREFIX*audioplayer_albums'); |
||
304 | if ($iArtistId) { |
||
305 | $stmt = $this->db->prepare('UPDATE `*PREFIX*audioplayer_albums` SET `year`= ?, `artist_id`= ? WHERE `id` = ? AND `user_id` = ?'); |
||
306 | $stmt->execute(array((int)$sYear, $iArtistId, $insertid, $userId)); |
||
307 | } else { |
||
308 | $stmt = $this->db->prepare('UPDATE `*PREFIX*audioplayer_albums` SET `year`= ? WHERE `id` = ? AND `user_id` = ?'); |
||
309 | $stmt->execute(array((int)$sYear, $insertid, $userId)); |
||
310 | } |
||
311 | $AlbumCount = 1; |
||
312 | } |
||
313 | |||
314 | $return = [ |
||
315 | 'id' => $insertid, |
||
316 | 'state' => true, |
||
317 | 'albumcount' => $AlbumCount, |
||
318 | ]; |
||
319 | return $return; |
||
320 | } |
||
321 | |||
322 | /** |
||
323 | * truncates fiels do DB-field size |
||
324 | * |
||
325 | * @param $string |
||
326 | * @param $length |
||
327 | * @param $dots |
||
328 | * @return string |
||
329 | */ |
||
330 | private function truncate($string, $length, $dots = "...") |
||
331 | { |
||
332 | return (strlen($string) > $length) ? mb_strcut($string, 0, $length - strlen($dots)) . $dots : $string; |
||
333 | } |
||
334 | |||
335 | /** |
||
336 | * validate unsigned int values |
||
337 | * |
||
338 | * @param string $value |
||
339 | * @return int value |
||
340 | */ |
||
341 | private function normalizeInteger($value) |
||
342 | { |
||
343 | // convert format '1/10' to '1' and '-1' to null |
||
344 | $tmp = explode('/', $value); |
||
345 | $tmp = explode('-', $tmp[0]); |
||
346 | $value = $tmp[0]; |
||
347 | if (is_numeric($value) && ((int)$value) > 0) { |
||
348 | $value = (int)$value; |
||
349 | } else { |
||
350 | $value = 0; |
||
351 | } |
||
352 | return $value; |
||
353 | } |
||
354 | |||
355 | /** |
||
356 | * Add artist to db if not exist |
||
357 | * @param int $userId |
||
358 | * @param string $sArtist |
||
359 | * @return int |
||
360 | */ |
||
361 | public function writeArtistToDB($userId, $sArtist) |
||
362 | { |
||
363 | $sArtist = $this->truncate($sArtist, '256'); |
||
364 | |||
365 | $stmt = $this->db->prepare('SELECT `id` FROM `*PREFIX*audioplayer_artists` WHERE `user_id` = ? AND `name` = ?'); |
||
366 | $stmt->execute(array($userId, $sArtist)); |
||
367 | $row = $stmt->fetch(); |
||
368 | if ($row) { |
||
369 | return $row['id']; |
||
370 | } else { |
||
371 | $stmt = $this->db->prepare('INSERT INTO `*PREFIX*audioplayer_artists` (`user_id`,`name`) VALUES(?,?)'); |
||
372 | $stmt->execute(array($userId, $sArtist)); |
||
373 | $insertid = $this->db->lastInsertId('*PREFIX*audioplayer_artists'); |
||
374 | return $insertid; |
||
375 | } |
||
376 | } |
||
377 | |||
378 | /** |
||
379 | * Add genre to db if not exist |
||
380 | * @param int $userId |
||
381 | * @param string $sGenre |
||
382 | * @return int |
||
383 | */ |
||
384 | public function writeGenreToDB($userId, $sGenre) |
||
385 | { |
||
386 | $sGenre = $this->truncate($sGenre, '256'); |
||
387 | |||
388 | $stmt = $this->db->prepare('SELECT `id` FROM `*PREFIX*audioplayer_genre` WHERE `user_id` = ? AND `name` = ?'); |
||
389 | $stmt->execute(array($userId, $sGenre)); |
||
390 | $row = $stmt->fetch(); |
||
391 | if ($row) { |
||
392 | return $row['id']; |
||
393 | } else { |
||
394 | $stmt = $this->db->prepare('INSERT INTO `*PREFIX*audioplayer_genre` (`user_id`,`name`) VALUES(?,?)'); |
||
395 | $stmt->execute(array($userId, $sGenre)); |
||
396 | $insertid = $this->db->lastInsertId('*PREFIX*audioplayer_genre'); |
||
397 | return $insertid; |
||
398 | } |
||
399 | } |
||
400 | |||
401 | /** |
||
402 | * Add track to db if not exist |
||
403 | * @param int $userId |
||
404 | * @param array $aTrack |
||
405 | * @return array |
||
406 | */ |
||
407 | public function writeTrackToDB($userId, $aTrack) |
||
408 | { |
||
409 | $dublicate = 0; |
||
410 | $insertid = 0; |
||
411 | $SQL = 'SELECT `id` FROM `*PREFIX*audioplayer_tracks` WHERE `user_id`= ? AND `title`= ? AND `number`= ? |
||
412 | AND `artist_id`= ? AND `album_id`= ? AND `length`= ? AND `bitrate`= ? |
||
413 | AND `mimetype`= ? AND `genre_id`= ? AND `year`= ? |
||
414 | AND `disc`= ? AND `composer`= ? AND `subtitle`= ?'; |
||
415 | $stmt = $this->db->prepare($SQL); |
||
416 | $stmt->execute(array($userId, |
||
417 | $aTrack['title'], |
||
418 | $aTrack['number'], |
||
419 | $aTrack['artist_id'], |
||
420 | $aTrack['album_id'], |
||
421 | $aTrack['length'], |
||
422 | $aTrack['bitrate'], |
||
423 | $aTrack['mimetype'], |
||
424 | $aTrack['genre'], |
||
425 | $aTrack['year'], |
||
426 | $aTrack['disc'], |
||
427 | $aTrack['composer'], |
||
428 | $aTrack['subtitle'], |
||
429 | )); |
||
430 | $row = $stmt->fetch(); |
||
431 | if (isset($row['id'])) { |
||
432 | $dublicate = 1; |
||
433 | } else { |
||
434 | $stmt = $this->db->prepare('INSERT INTO `*PREFIX*audioplayer_tracks` (`user_id`,`title`,`number`,`artist_id`,`album_id`,`length`,`file_id`,`bitrate`,`mimetype`,`genre_id`,`year`,`folder_id`,`disc`,`composer`,`subtitle`,`isrc`,`copyright`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'); |
||
435 | $stmt->execute(array($userId, |
||
436 | $aTrack['title'], |
||
437 | $aTrack['number'], |
||
438 | $aTrack['artist_id'], |
||
439 | $aTrack['album_id'], |
||
440 | $aTrack['length'], |
||
441 | $aTrack['file_id'], |
||
442 | $aTrack['bitrate'], |
||
443 | $aTrack['mimetype'], |
||
444 | $aTrack['genre'], |
||
445 | $aTrack['year'], |
||
446 | $aTrack['folder_id'], |
||
447 | $aTrack['disc'], |
||
448 | $aTrack['composer'], |
||
449 | $aTrack['subtitle'], |
||
450 | $aTrack['isrc'], |
||
451 | $aTrack['copyright'], |
||
452 | )); |
||
453 | $insertid = $this->db->lastInsertId('*PREFIX*audioplayer_tracks'); |
||
454 | } |
||
455 | $return = [ |
||
456 | 'id' => $insertid, |
||
457 | 'state' => true, |
||
458 | 'dublicate' => $dublicate, |
||
459 | ]; |
||
460 | return $return; |
||
461 | } |
||
462 | |||
463 | /** |
||
464 | * Get audio info for single track |
||
465 | * @param int $trackId |
||
466 | * @param int $fileId |
||
467 | * @return array |
||
468 | */ |
||
469 | public function getTrackInfo($trackId = null, $fileId = null) |
||
470 | { |
||
471 | |||
472 | $SQL = "SELECT `AT`.`title` AS `Title`, |
||
473 | `AT`.`subtitle` AS `Subtitle`, |
||
474 | `AA`.`name` AS `Artist`, |
||
475 | `AB`.`artist_id` AS `Album Artist`, |
||
476 | `AT`.`composer` AS `Composer`, |
||
477 | `AB`.`name` AS `Album`, |
||
478 | `AG`.`name` AS `Genre`, |
||
479 | `AT`.`year` AS `Year`, |
||
480 | `AT`.`disc` AS `Disc`, |
||
481 | `AT`.`number` AS `Track`, |
||
482 | `AT`.`length` AS `Length`, |
||
483 | ROUND((`AT`.`bitrate` / 1000 ),0) AS `Bitrate`, |
||
484 | `AT`.`mimetype` AS `MIME type`, |
||
485 | `AT`.`isrc` AS `ISRC`, |
||
486 | `AT`.`copyright` AS `Copyright`, |
||
487 | `AT`.`file_id`, |
||
488 | `AB`.`id` AS `album_id`, |
||
489 | `AT`.`id` |
||
490 | FROM `*PREFIX*audioplayer_tracks` `AT` |
||
491 | LEFT JOIN `*PREFIX*audioplayer_artists` `AA` ON `AT`.`artist_id` = `AA`.`id` |
||
492 | LEFT JOIN `*PREFIX*audioplayer_genre` `AG` ON `AT`.`genre_id` = `AG`.`id` |
||
493 | LEFT JOIN `*PREFIX*audioplayer_albums` `AB` ON `AT`.`album_id` = `AB`.`id`"; |
||
494 | |||
495 | if ($trackId !== null) { |
||
496 | $SQL .= " WHERE `AT`.`user_id` = ? AND `AT`.`id` = ? |
||
497 | ORDER BY `AT`.`album_id` ASC,`AT`.`number` ASC "; |
||
498 | $selectId = $trackId; |
||
499 | } else { |
||
500 | $SQL .= " WHERE `AT`.`user_id` = ? AND `AT`.`file_id` = ? |
||
501 | ORDER BY `AT`.`album_id` ASC,`AT`.`number` ASC "; |
||
502 | $selectId = $fileId; |
||
503 | } |
||
504 | |||
505 | $stmt = $this->db->prepare($SQL); |
||
506 | $stmt->execute(array($this->userId, $selectId)); |
||
507 | $row = $stmt->fetch(); |
||
508 | |||
509 | $favorites = $this->tagManager->load('files')->getFavorites(); |
||
510 | if (in_array($row['file_id'], $favorites)) { |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
511 | $row['fav'] = "t"; |
||
512 | } else { |
||
513 | $row['fav'] = "f"; |
||
514 | } |
||
515 | |||
516 | return $row; |
||
517 | } |
||
518 | |||
519 | /** |
||
520 | * Get file id for single track |
||
521 | * @param int $trackId |
||
522 | * @return int |
||
523 | */ |
||
524 | public function getFileId($trackId) |
||
525 | { |
||
526 | $SQL = "SELECT `file_id` FROM `*PREFIX*audioplayer_tracks` WHERE `user_id` = ? AND `id` = ?"; |
||
527 | $stmt = $this->db->prepare($SQL); |
||
528 | $stmt->execute(array($this->userId, $trackId)); |
||
529 | $row = $stmt->fetch(); |
||
530 | return $row['file_id']; |
||
531 | } |
||
532 | |||
533 | /** |
||
534 | * Add track to db if not exist |
||
535 | * @param int $userId |
||
536 | * @param int $track_id |
||
537 | * @param string $editKey |
||
538 | * @param string $editValue |
||
539 | * @return bool |
||
540 | */ |
||
541 | public function updateTrack($userId, $track_id, $editKey, $editValue) |
||
542 | { |
||
543 | $SQL = 'UPDATE `*PREFIX*audioplayer_tracks` SET `' . $editKey . '` = ? WHERE `user_id` = ? AND `id` = ?'; |
||
544 | $stmt = $this->db->prepare($SQL); |
||
545 | $stmt->execute(array($editValue, |
||
546 | $userId, |
||
547 | $track_id |
||
548 | )); |
||
549 | return true; |
||
550 | } |
||
551 | |||
552 | /** |
||
553 | * Add stream to db if not exist |
||
554 | * @param int $userId |
||
555 | * @param array $aStream |
||
556 | * @return array |
||
557 | */ |
||
558 | public function writeStreamToDB($userId, $aStream) |
||
559 | { |
||
560 | $stmt = $this->db->prepare('SELECT `id` FROM `*PREFIX*audioplayer_streams` WHERE `user_id` = ? AND `file_id` = ? '); |
||
561 | $stmt->execute(array($userId, $aStream['file_id'])); |
||
562 | $row = $stmt->fetch(); |
||
563 | $dublicate = 0; |
||
564 | $insertid = 0; |
||
565 | if (isset($row['id'])) { |
||
566 | $dublicate = 1; |
||
567 | } else { |
||
568 | $stmt = $this->db->prepare('INSERT INTO `*PREFIX*audioplayer_streams` (`user_id`,`title`,`file_id`,`mimetype`) VALUES(?,?,?,?)'); |
||
569 | $stmt->execute(array($userId, |
||
570 | $aStream['title'], |
||
571 | $aStream['file_id'], |
||
572 | $aStream['mimetype'], |
||
573 | )); |
||
574 | $insertid = $this->db->lastInsertId('*PREFIX*audioplayer_streams'); |
||
575 | } |
||
576 | $return = [ |
||
577 | 'id' => $insertid, |
||
578 | 'state' => true, |
||
579 | 'dublicate' => $dublicate, |
||
580 | ]; |
||
581 | return $return; |
||
582 | } |
||
583 | |||
584 | /** |
||
585 | * Get all playlists were track is added |
||
586 | * @param int $userId |
||
587 | * @param int $trackId |
||
588 | * @return array |
||
589 | */ |
||
590 | public function getPlaylistsForTrack($userId, $trackId) |
||
591 | { |
||
592 | $playlists = array(); |
||
593 | $SQL = "SELECT `AP`.`playlist_id` , `AN`.`name`, LOWER(`AN`.`name`) AS `lower` |
||
594 | FROM `*PREFIX*audioplayer_playlist_tracks` `AP` |
||
595 | LEFT JOIN `*PREFIX*audioplayer_playlists` `AN` |
||
596 | ON `AP`.`playlist_id` = `AN`.`id` |
||
597 | WHERE `AN`.`user_id` = ? |
||
598 | AND `AP`.`track_id` = ? |
||
599 | ORDER BY LOWER(`AN`.`name`) ASC |
||
600 | "; |
||
601 | $stmt = $this->db->prepare($SQL); |
||
602 | $stmt->execute(array($userId, $trackId)); |
||
603 | $results = $stmt->fetchAll(); |
||
604 | foreach ($results as $row) { |
||
605 | array_splice($row, 2, 1); |
||
606 | $playlists[] = $row; |
||
607 | } |
||
608 | |||
609 | return $playlists; |
||
610 | } |
||
611 | |||
612 | /** |
||
613 | * @NoAdminRequired |
||
614 | * @param $type |
||
615 | * @param $value |
||
616 | * @param $userId |
||
617 | * @return string |
||
618 | */ |
||
619 | public function setSessionValue($type, $value, $userId) |
||
620 | { |
||
621 | if ($userId) $this->userId = $userId; |
||
622 | //$this->session->set($type, $value); |
||
623 | $SQL = 'SELECT `configvalue` FROM `*PREFIX*preferences` WHERE `userid`= ? AND `appid`= ? AND `configkey`= ?'; |
||
624 | $stmt = $this->db->prepare($SQL); |
||
625 | $stmt->execute(array($this->userId, 'audioplayer', $type)); |
||
626 | $row = $stmt->fetch(); |
||
627 | if (isset($row['configvalue'])) { |
||
628 | $stmt = $this->db->prepare('UPDATE `*PREFIX*preferences` SET `configvalue`= ? WHERE `userid`= ? AND `appid`= ? AND `configkey`= ?'); |
||
629 | $stmt->execute(array($value, $this->userId, 'audioplayer', $type)); |
||
630 | return 'update'; |
||
631 | } else { |
||
632 | $stmt = $this->db->prepare('INSERT INTO `*PREFIX*preferences` (`userid`,`appid`,`configkey`,`configvalue`) VALUES(?,?,?,?)'); |
||
633 | $stmt->execute(array($this->userId, 'audioplayer', $type, $value)); |
||
634 | return 'insert'; |
||
635 | } |
||
636 | } |
||
637 | |||
638 | /** |
||
639 | * @NoAdminRequired |
||
640 | * @param $type |
||
641 | * @return string |
||
642 | */ |
||
643 | public function getSessionValue($type) |
||
644 | { |
||
645 | //return $this->session->get($type); |
||
646 | $SQL = 'SELECT `configvalue` FROM `*PREFIX*preferences` WHERE `userid`= ? AND `appid`= ? AND `configkey`= ?'; |
||
647 | $stmt = $this->db->prepare($SQL); |
||
648 | $stmt->execute(array($this->userId, 'audioplayer', $type)); |
||
649 | $row = $stmt->fetch(); |
||
650 | return $row['configvalue']; |
||
651 | } |
||
652 | |||
653 | } |
||
654 |