· 5 years ago · Sep 15, 2020, 03:26 PM
1<?php
2
3class romprmetadata {
4
5 public static function sanitise_data(&$data) {
6
7 foreach (array( 'action',
8 'title',
9 'artist',
10 'trackno',
11 'duration',
12 'albumuri',
13 'image',
14 'album',
15 'uri',
16 'trackai',
17 'albumai',
18 'albumindex',
19 'searched',
20 'lastmodified',
21 'streamname',
22 'streamimage',
23 'streamuri',
24 'type',
25 'ambid',
26 'isaudiobook',
27 'attributes',
28 'imagekey',
29 'which',
30 'genre',
31 'wltrack',
32 'reqid') as $key) {
33 if (!array_key_exists($key, $data)) {
34 $data[$key] = null;
35 }
36 }
37 foreach (array( 'trackno', 'duration', 'isaudiobook') as $key) {
38 if ($data[$key] == null) {
39 $data[$key] = 0;
40 }
41 }
42 $data['albumartist'] = array_key_exists('albumartist', $data) ? $data['albumartist'] : $data['artist'];
43 // One of these following two is redundant, but code needs tidying VASTLY before I can unpick that
44 // i.e. why aren't we using ROMPR_FILE_MODEL for this?
45 $data['date'] = (array_key_exists('date', $data) && $data['date'] != 0) ? getYear($data['date']) : null;
46 $data['year'] = (array_key_exists('date', $data) && $data['date'] != 0) ? getYear($data['date']) : null;
47 $data['urionly'] = array_key_exists('urionly', $data) ? true : false;
48 $data['disc'] = array_key_exists('disc', $data) ? $data['disc'] : 1;
49 $data['domain'] = array_key_exists('domain', $data) ? $data['domain'] : ($data['uri'] === null ? "local" : getDomain($data['uri']));
50 $data['hidden'] = 0;
51 if ($data['genre'] === null) {
52 logger::warn('SANITISER', 'Track has NULL genre');
53 $data['genre'] = 'None';
54 }
55 $data['genreindex'] = check_genre($data['genre']);
56 $data['searchflag'] = 0;
57 if (substr($data['image'],0,4) == "http") {
58 $data['image'] = "getRemoteImage.php?url=".rawurlencode($data['image']);
59 }
60 if ($data['imagekey'] === null) {
61 $albumimage = new baseAlbumImage(array(
62 'artist' => artist_for_image($data['type'], $data['albumartist']),
63 'album' => $data['album']
64 ));
65 $data['imagekey'] = $albumimage->get_image_key();
66 }
67 }
68
69 public static function set($data, $keep_wishlist = false) {
70 global $returninfo;
71 if ($data['artist'] === null ||
72 $data['title'] === null ||
73 $data['attributes'] == null) {
74 logger::error("SET", "Something is not set", $data);
75 header('HTTP/1.1 400 Bad Request');
76 print json_encode(array('error' => 'Artist or Title or Attributes not set'));
77 exit(0);
78 }
79
80 switch ($data['artist']) {
81 case 'geturisfordir':
82 $ttids = romprmetadata::geturisfordir($data);
83 break;
84
85 case 'geturis':
86 $ttids = romprmetadata::geturis($data);
87 break;
88
89 default:
90 $ttids = romprmetadata::find_item($data, forcedUriOnly($data['urionly'], getDomain($data['uri'])));
91 break;
92 }
93
94 $newttids = array();
95 foreach ($ttids as $ttid) {
96 if ($keep_wishlist || !track_is_wishlist($ttid)) {
97 $newttids[] = $ttid;
98 }
99 }
100 $ttids = $newttids;
101
102 if (count($ttids) == 0) {
103 $ttids[0] = create_new_track($data);
104 logger::log("SET", "Created New Track with TTindex ".$ttids[0]);
105 }
106
107 if (count($ttids) > 0) {
108 if (romprmetadata::doTheSetting($ttids, $data['attributes'], $data['uri'])) {
109 } else {
110 header('HTTP/1.1 417 Expectation Failed');
111 $returninfo['error'] = 'Setting attributes failed';
112 }
113 } else {
114 logger::warn("SET", "TTID Not Found");
115 header('HTTP/1.1 417 Expectation Failed');
116 $returninfo['error'] = 'TTindex not found';
117 }
118 }
119
120 public static function add($data, $urionly = true) {
121 // This is used for adding specific tracks so we need urionly to be true
122 // We don't simply call into this using 'set' with urionly set to true
123 // because that might result in the rating being changed
124
125 // The only time we call inot this with $urionly set to false is when we're restoring a metadata
126 // backup. In that case we might be copying data from one setup to another and we might have
127 // the track already in local, so we don't want to add duplicates. Neither way is perfect but
128 // this makes most sense I think.
129
130 global $returninfo;
131 $ttids = romprmetadata::find_item($data, $urionly);
132
133 // As we check by URI we can only have one result.
134 $ttid = null;
135 if (count($ttids) > 0) {
136 $ttid = $ttids[0];
137 if (track_is_hidden($ttid) || track_is_searchresult($ttid)) {
138 logger::mark("ADD", "Track ".$ttid." being added is a search result or a hidden track");
139 // Setting attributes (Rating: 0) will unhide/un-searchify it. Ratings of 0 are got rid of
140 // by remove_cruft at the end, because they're meaningless
141 if ($data['attributes'] == null) {
142 $data['attributes'] = array(array('attribute' => 'Rating', 'value'=> 0));
143 }
144 } else {
145 logger::warn("ADD", "Track being added already exists");
146 }
147 }
148
149 check_for_wishlist_track($data);
150
151 if ($ttid == null) {
152 logger::log("ADD", "Creating Track being added");
153 $ttid = create_new_track($data);
154 }
155
156 romprmetadata::doTheSetting(array($ttid), $data['attributes'], $data['uri']);
157 }
158
159 public static function inc($data) {
160 global $returninfo;
161 // NOTE : 'inc' does not do what you might expect.
162 // This is not an 'increment' function, it still does a SET but it will create a hidden track
163 // if the track can't be found, compare to SET which creates a new unhidden track.
164 if ($data['artist'] === null ||
165 $data['title'] === null ||
166 $data['attributes'] == null) {
167 logger::error("INC", "Something is not set",$data);
168 header('HTTP/1.1 400 Bad Request');
169 print json_encode(array('error' => 'Artist or Title or Attributes not set'));
170 exit(0);
171 }
172 $ttids = romprmetadata::find_item($data, forcedUriOnly(false,getDomain($data['uri'])));
173 if (count($ttids) == 0) {
174 logger::trace("INC", "Doing an INCREMENT action - Found NOTHING so creating hidden track");
175 $data['hidden'] = 1;
176 $ttids[0] = create_new_track($data);
177 }
178
179 romprmetadata::checkLastPlayed($data);
180
181 if (count($ttids) > 0) {
182 foreach ($ttids as $ttid) {
183 logger::trace("INC", "Doing an INCREMENT action - Found TTID ",$ttid);
184 foreach ($data['attributes'] as $pair) {
185 logger::log("INC", "(Increment) Setting",$pair["attribute"],"to",$pair["value"],"on",$ttid);
186 romprmetadata::increment_value($ttid, $pair["attribute"], $pair["value"], $data['lastplayed']);
187 }
188 $returninfo['metadata'] = get_all_data($ttid);
189 }
190 }
191 return $ttids;
192 }
193
194 private static function checkLastPlayed(&$data) {
195 if (array_key_exists('lastplayed', $data)) {
196 if (is_numeric($data['lastplayed'])) {
197 // Convert timestamp from LastFM into MySQL TIMESTAMP format
198 $data['lastplayed'] = date('Y-m-d H:i:s', $data['lastplayed']);
199 }
200 } else {
201 $data['lastplayed'] = date('Y-m-d H:i:s');
202 }
203 }
204
205 public static function syncinc($data) {
206 global $returninfo;
207 if ($data['artist'] === null ||
208 $data['title'] === null ||
209 $data['attributes'] == null) {
210 logger::error("SYNCINC", "Something is not set", $data);
211 header('HTTP/1.1 400 Bad Request');
212 print json_encode(array('error' => 'Artist or Title or Attributes not set'));
213 exit(0);
214 }
215
216 $ttids = romprmetadata::find_item($data, forcedUriOnly(false,getDomain($data['uri'])));
217 if (count($ttids) == 0) {
218 $ttids = romprmetadata::inc($data);
219 romprmetadata::resetSyncCounts($ttids);
220 return true;
221 }
222
223 romprmetadata::checkLastPlayed($data);
224 foreach ($ttids as $ttid) {
225 logger::log("SYNCINC", "Doing a SYNC action on TTID ".$ttid,'LastPlayed is',$data['lastplayed']);
226 $rowcount = generic_sql_query("UPDATE Playcounttable SET SyncCount = SyncCount - 1, LastPlayed = '".$data['lastplayed']."' WHERE TTindex = ".$ttid." AND SyncCount > 0",
227 false, null, null, null, true);
228 if ($rowcount > 0) {
229 logger::trace("SYNCINC", " Decremented sync counter for this track");
230 } else {
231 $rowcount = generic_sql_query("UPDATE Playcounttable SET Playcount = Playcount + 1, LastPlayed = '".$data['lastplayed']."' WHERE TTindex = ".$ttid,
232 false, null, null, null, true);
233 if ($rowcount > 0) {
234 logger::trace("SYNCINC", " Incremented Playcount for this track");
235 // At this point, SyncCount must have been zero but the update will have incremented it again,
236 // because of the trigger. resetSyncCounts takes care of this;
237 } else {
238 logger::log("SYNCINC", " Track not found in Playcounttable");
239 $metadata = get_all_data($ttid);
240 romprmetadata::increment_value($ttid, 'Playcount', $metadata['Playcount'] + 1, $data['lastplayed']);
241 // At this point, SyncCount must have been zero but the update will have incremented it again,
242 // because of the trigger. resetSyncCounts takes care of this;
243 }
244 romprmetadata::resetSyncCounts(array($ttid));
245 }
246 }
247
248 // Let's just see if it's a podcast track and mark it as listened.
249 // This won't always work, as scrobbles are often not what's in the RSS feed, but we can but do our best
250 sql_prepare_query(true, null, null, null,
251 "UPDATE PodcastTracktable SET Listened = ?, New = ? WHERE Title = ? AND Artist = ?",
252 1,
253 0,
254 $data['title'],
255 $data['artist']
256 );
257
258 }
259
260 public static function resetSyncCounts($ttids) {
261 foreach ($ttids as $ttid) {
262 generic_sql_query("UPDATE Playcounttable SET SyncCount = 0 WHERE TTindex = ".$ttid, true);
263 }
264 }
265
266 public static function resetallsyncdata() {
267 generic_sql_query('UPDATE Playcounttable SET SyncCount = 0 WHERE TTindex > 0', true);
268 }
269
270 public static function remove($data) {
271 global $returninfo;
272 if ($data['artist'] === null || $data['title'] === null) {
273 header('HTTP/1.1 400 Bad Request');
274 print json_encode(array('error' => 'Artist or Title not set'));
275 exit(0);
276 }
277 $ttids = romprmetadata::find_item($data, forcedUriOnly($data['urionly'], getDomain($data['uri'])));
278 if (count($ttids) > 0) {
279 foreach ($ttids as $ttid) {
280 $result = true;
281 foreach ($data['attributes'] as $pair) {
282 logger::log("REMOVE", "Removing",$pair);
283 $r = romprmetadata::remove_tag($ttid, $pair["value"]);
284 if ($r == false) {
285 logger::warn("REMOVE", "FAILED Removing",$pair);
286 $result = false;
287 }
288 }
289 if ($result) {
290 $returninfo['metadata'] = get_all_data($ttid);
291 } else {
292 header('HTTP/1.1 417 Expectation Failed');
293 $returninfo['error'] = 'Removing attributes failed';
294 }
295 }
296 } else {
297 logger::warn("USERRATING", "TTID Not Found");
298 header('HTTP/1.1 417 Expectation Failed');
299 $returninfo['error'] = 'TTindex not found';
300 }
301 }
302
303 public static function get($data) {
304 global $returninfo, $nodata;
305 if ($data['artist'] === null || $data['title'] === null) {
306 header('HTTP/1.1 400 Bad Request');
307 print json_encode(array('error' => 'Artist or Title not set'));
308 exit(0);
309 }
310 $ttids = romprmetadata::find_item($data, forcedUriOnly(false, getDomain($data['uri'])));
311 if (count($ttids) > 0) {
312 $ttid = array_shift($ttids);
313 $returninfo = get_all_data($ttid);
314 } else {
315 $returninfo = $nodata;
316 }
317 }
318
319 public static function setalbummbid($data) {
320 global $returninfo, $nodata;
321 $ttids = romprmetadata::find_item($data, forcedUriOnly(false, getDomain($data['uri'])));
322 if (count($ttids) > 0) {
323 foreach ($ttids as $ttid) {
324 logger::trace("BACKEND", "Updating album MBID ".$data['attributes']." from TTindex ".$ttid);
325 $albumindex = simple_query('Albumindex', 'Tracktable', 'TTindex', $ttid, null);
326 logger::debug("BACKEND", " .. album index is ".$albumindex);
327 sql_prepare_query(true, null, null, null, "UPDATE Albumtable SET mbid = ? WHERE Albumindex = ? AND mbid IS NULL",$data['attributes'],$albumindex);
328 }
329 }
330 $returninfo = $nodata;
331 }
332
333 public static function updateAudiobookState($data) {
334 $ttids = romprmetadata::find_item($data, forcedUriOnly(false, getDomain($data['uri'])));
335 if (count($ttids) > 0) {
336 foreach ($ttids as $ttid) {
337 logger::log('SQL', 'Setting Audiobooks state for TTIndex',$ttid,'to',$data['isaudiobook']);
338 sql_prepare_query(true, null, null, null, 'UPDATE Tracktable SET isAudiobook = ? WHERE TTindex = ?', $data['isaudiobook'], $ttid);
339 }
340 }
341 }
342
343 public static function cleanup($data) {
344 logger::info("CLEANUP", "Doing Database Cleanup And Stats Update");
345 remove_cruft();
346 update_track_stats();
347 doCollectionHeader();
348 }
349
350 public static function amendalbum($data) {
351 if ($data['albumindex'] !== null && romprmetadata::amend_album($data['albumindex'], $data['albumartist'], $data['date'])) {
352 } else {
353 header('HTTP/1.1 400 Bad Request');
354 $returninfo['error'] = 'That just did not work';
355 }
356 }
357
358 public static function deletealbum($data) {
359 if ($data['albumindex'] !== null && romprmetadata::delete_album($data['albumindex'])) {
360 } else {
361 header('HTTP/1.1 400 Bad Request');
362 $returninfo['error'] = 'That just did not work';
363 }
364 }
365
366 public static function setasaudiobook($data) {
367 if ($data['albumindex'] !== null && romprmetadata::set_as_audiobook($data['albumindex'], $data['value'])) {
368 } else {
369 header('HTTP/1.1 400 Bad Request');
370 $returninfo['error'] = 'That just did not work';
371 }
372 }
373
374 public static function deletetag($data) {
375 if (romprmetadata::remove_tag_from_db($data['value'])) {
376 } else {
377 header('HTTP/1.1 400 Bad Request');
378 $returninfo['error'] = 'Well, that went well';
379 }
380 }
381
382 public static function delete($data) {
383 $ttids = romprmetadata::find_item($data, true);
384 if (count($ttids) == 0) {
385 header('HTTP/1.1 400 Bad Request');
386 $returninfo['error'] = 'TTindex not found';
387 } else {
388 romprmetadata::delete_track(array_shift($ttids));
389 }
390 }
391
392 public static function deletewl($data) {
393 romprmetadata::delete_track($data['wltrack']);
394 }
395
396 public static function deleteid($data) {
397 romprmetadata::delete_track($data['ttid']);
398 }
399
400 public static function getcharts($data) {
401 global $returninfo;
402 $returninfo['Artists'] = get_artist_charts();
403 $returninfo['Albums'] = get_album_charts();
404 $returninfo['Tracks'] = get_track_charts();
405 }
406
407 public static function clearwishlist() {
408 logger::log("MONKEYS", "Removing Wishlist Tracks");
409 if (clear_wishlist()) {
410 logger::debug("MONKEYS", " ... Success!");
411 } else {
412 logger::warn("MONKEYS", "Failed removing wishlist tracks");
413 }
414 }
415
416 // Private Functions
417
418 static function geturisfordir($data) {
419 global $PLAYER_TYPE;
420 $player = new $PLAYER_TYPE();
421 $uris = $player->get_uris_for_directory($data['uri']);
422 $ttids = array();
423 foreach ($uris as $uri) {
424 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null, "SELECT TTindex FROM Tracktable WHERE Uri = ?", $uri);
425 $ttids = array_merge($ttids, $t);
426 }
427 return $ttids;
428 }
429
430 static function geturis($data) {
431 $uris = getItemsToAdd($data['uri'], "");
432 $ttids = array();
433 foreach ($uris as $uri) {
434 $uri = trim(substr($uri, strpos($uri, ' ')+1, strlen($uri)), '"');
435 $r = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null, "SELECT TTindex FROM Tracktable WHERE Uri = ?", $uri);
436 $ttids = array_merge($ttids, $t);
437 }
438 return $ttids;
439 }
440
441 static function print_debug_ttids($ttids, $s) {
442 $time = time() - $s;
443 if (count($ttids) > 0) {
444 logger::info("TIMINGS", " Found TTindex(es)",$ttids,"in",$time,"seconds");
445 }
446 }
447
448 static function find_item($data,$urionly) {
449
450 // romprmetadata::find_item
451 // Looks for a track in the database based on uri, title, artist, album, and albumartist or
452 // combinations of those
453 // Returns: Array of TTindex
454
455 // romprmetadata::find_item is used by userRatings to find tracks on which to update or display metadata.
456 // It is NOT used when the collection is created
457
458 // When Setting Metadata we do not use a URI because we might have mutliple versions of the
459 // track in the database or someone might be rating a track from Spotify that they already have
460 // in Local. So in this case we check using an increasingly wider check to find the track,
461 // returning as soon as one of these produces matches.
462 // First by Title, TrackNo, AlbumArtist and Album
463 // Third by Track, Album Artist, and Album
464 // Then by Track, Track Artist, and Album
465 // Then by Track, Artist, and Album NULL (meaning wishlist)
466 // We return ALL tracks found, because you might have the same track on multiple backends,
467 // and set metadata on them all.
468 // This means that when getting metadata it doesn't matter which one we match on.
469 // When we Get Metadata we do supply a URI BUT we don't use it if we have one, just because.
470 // $urionly can be set to force looking up only by URI. This is used by when we need to import a
471 // specific version of the track - currently from either the Last.FM importer or when we add a
472 // spotify album to the collection
473
474 // If we don't supply an album to this function that's because we're listening to the radio.
475 // In that case we look for a match where there is something in the album field and then for
476 // where album is NULL
477
478 // FIXME! There is one scenario where the above fails.
479 // If you tag or rate a track, and then add it to the collection again from another backend
480 // later on, the rating doesn't get picked up by the new copy.
481 // Looking everything up by name/album/artist (i.e. ignoring the URI in romprmetadata::find_item)
482 // doesn't fix this because the collection display still doesn't show the rating as that's
483 // looked up by TTindex
484
485 $start_time = time();
486 logger::mark("FIND ITEM", "Looking for item ".$data['title']);
487 $ttids = array();
488 if ($urionly && $data['uri']) {
489 logger::log("FIND ITEM", " Trying by URI ".$data['uri']);
490 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null, "SELECT TTindex FROM Tracktable WHERE Uri = ?", $data['uri']);
491 $ttids = array_merge($ttids, $t);
492 }
493
494 if ($data['artist'] == null || $data['title'] == null || ($urionly && $data['uri'])) {
495 romprmetadata::print_debug_ttids($ttids, $start_time);
496 return $ttids;
497 }
498
499 if (count($ttids) == 0) {
500 if ($data['album']) {
501 if ($data['albumartist'] !== null && $data['trackno'] != 0) {
502 logger::log("FIND ITEM", " Trying by albumartist",$data['albumartist'],"album",$data['album'],"title",$data['title'],"track number",$data['trackno']);
503 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null,
504 "SELECT
505 TTindex
506 FROM
507 Tracktable JOIN Albumtable USING (Albumindex)
508 JOIN Artisttable ON Albumtable.AlbumArtistindex = Artisttable.Artistindex
509 WHERE
510 LOWER(Title) = LOWER(?)
511 AND LOWER(Artistname) = LOWER(?)
512 AND LOWER(Albumname) = LOWER(?)
513 AND TrackNo = ?",
514 $data['title'], $data['albumartist'], $data['album'], $data['trackno']);
515 $ttids = array_merge($ttids, $t);
516 }
517
518 if (count($ttids) == 0 && $data['albumartist'] !== null) {
519 logger::log("FIND ITEM", " Trying by albumartist",$data['albumartist'],"album",$data['album'],"and title",$data['title']);
520 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null,
521 "SELECT
522 TTindex
523 FROM
524 Tracktable JOIN Albumtable USING (Albumindex)
525 JOIN Artisttable ON Albumtable.AlbumArtistindex = Artisttable.Artistindex
526 WHERE
527 LOWER(Title) = LOWER(?)
528 AND LOWER(Artistname) = LOWER(?)
529 AND LOWER(Albumname) = LOWER(?)",
530 $data['title'], $data['albumartist'], $data['album']);
531 $ttids = array_merge($ttids, $t);
532 }
533
534 if (count($ttids) == 0 && ($data['albumartist'] == null || $data['albumartist'] == $data['artist'])) {
535 logger::log("FIND ITEM", " Trying by artist",$data['artist'],",album",$data['album'],"and title",$data['title']);
536 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null,
537 "SELECT
538 TTindex
539 FROM
540 Tracktable JOIN Artisttable USING (Artistindex)
541 JOIN Albumtable USING (Albumindex)
542 WHERE
543 LOWER(Title) = LOWER(?)
544 AND LOWER(Artistname) = LOWER(?)
545 AND LOWER(Albumname) = LOWER(?)", $data['title'], $data['artist'], $data['album']);
546 $ttids = array_merge($ttids, $t);
547 }
548
549 // Finally look for Uri NULL which will be a wishlist item added via a radio station
550 if (count($ttids) == 0) {
551 logger::log("FIND ITEM", " Trying by (wishlist) artist",$data['artist'],"and title",$data['title']);
552 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null,
553 "SELECT
554 TTindex
555 FROM
556 Tracktable JOIN Artisttable USING (Artistindex)
557 WHERE
558 LOWER(Title) = LOWER(?)
559 AND LOWER(Artistname) = LOWER(?)
560 AND Uri IS NULL",
561 $data['title'], $data['artist']);
562 $ttids = array_merge($ttids, $t);
563 }
564 } else {
565 // No album supplied - ie this is from a radio stream. First look for a match where
566 // there is something in the album field
567 logger::log("FIND ITEM", " Trying by artist",$data['artist'],"Uri NOT NULL and title",$data['title']);
568 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null,
569 "SELECT
570 TTindex
571 FROM
572 Tracktable JOIN Artisttable USING (Artistindex)
573 WHERE
574 LOWER(Title) = LOWER(?)
575 AND LOWER(Artistname) = LOWER(?)
576 AND Uri IS NOT NULL", $data['title'], $data['artist']);
577 $ttids = array_merge($ttids, $t);
578
579 if (count($ttids) == 0) {
580 logger::log("FIND ITEM", " Trying by (wishlist) artist",$data['artist'],"and title",$data['title']);
581 $t = sql_prepare_query(false, PDO::FETCH_COLUMN, 'TTindex', null,
582 "SELECT
583 TTindex
584 FROM
585 Tracktable JOIN Artisttable USING (Artistindex)
586 WHERE
587 LOWER(Title) = LOWER(?)
588 AND LOWER(Artistname) = LOWER(?)
589 AND Uri IS NULL", $data['title'], $data['artist']);
590 $ttids = array_merge($ttids, $t);
591 }
592 }
593 }
594 romprmetadata::print_debug_ttids($ttids, $start_time);
595 return $ttids;
596 }
597
598 static function increment_value($ttid, $attribute, $value, $lp) {
599
600 // Increment_value doesn't 'increment' as such - it's used for setting values on tracks without
601 // unhiding them. It's used for Playcount, which was originally an 'increment' type function but
602 // that changed because multiple rompr instances cause multiple increments
603
604 logger::log("INCREMENT", "Setting",$attribute,"to",$value,"for TTID",$ttid);
605 if (sql_prepare_query(true, null, null, null, "REPLACE INTO ".$attribute."table (TTindex, ".$attribute.", LastPlayed) VALUES (?, ?, ?)", $ttid, $value, $lp)) {
606 logger::debug("INCREMENT", " .. success");
607 } else {
608 logger::warn("INCREMENT", "FAILED Setting",$attribute,"to",$value,"for TTID",$ttid);
609 return false;
610 }
611 return true;
612
613 }
614
615 static function set_attribute($ttid, $attribute, $value) {
616
617 // set_attribute
618 // Sets an attribute (Rating, Tag etc) on a TTindex.
619 logger::log("ATTRIBUTE", "Setting",$attribute,"to",$value,"on",$ttid);
620 if (sql_prepare_query(true, null, null, null, "REPLACE INTO ".$attribute."table (TTindex, ".$attribute.") VALUES (?, ?)", $ttid, $value)) {
621 logger::debug("ATTRIBUTE", " .. success");
622 } else {
623 logger::warn("ATTRIBUTE", "FAILED Setting",$attribute,"to",$value,"on",$ttid);
624 return false;
625 }
626 return true;
627 }
628
629 static function doTheSetting($ttids, $attributes, $uri) {
630 global $returninfo;
631 $result = true;
632 logger::debug("USERRATING", "Checking For attributes");
633 if ($attributes !== null) {
634 logger::debug("USERRATING", "Setting attributes");
635 foreach($ttids as $ttid) {
636 logger::debug("USERRATING", "TTid ".$ttid);
637 foreach ($attributes as $pair) {
638 logger::log("USERRATING", "Setting",$pair["attribute"],"to",$pair['value'],"on TTindex",$ttid);
639 switch ($pair['attribute']) {
640 case 'Tags':
641 $result = romprmetadata::addTags($ttid, $pair['value']);
642 break;
643
644 default:
645 $result = romprmetadata::set_attribute($ttid, $pair["attribute"], $pair["value"]);
646 break;
647 }
648 if (!$result) { break; }
649 }
650 romprmetadata::check_audiobook_status($ttid);
651 if ($uri) {
652 $returninfo['metadata'] = get_all_data($ttid);
653 }
654 }
655 }
656 return $result;
657 }
658
659 static function check_audiobook_status($ttid) {
660 global $prefs;
661 $albumindex = generic_sql_query("SELECT Albumindex FROM Tracktable WHERE TTindex = ".$ttid, false, null, 'Albumindex', null);
662 if ($albumindex !== null) {
663 $sorter = choose_sorter_by_key('zalbum'.$albumindex);
664 $lister = new $sorter('zalbum'.$albumindex);
665 if ($lister->album_trackcount($albumindex) > 0) {
666 logger::log('USERRATING', 'Album '.$albumindex.' is an audiobook, updating track audiobook state');
667 generic_sql_query("UPDATE Tracktable SET isAudiobook = 2 WHERE TTindex = ".$ttid);
668 }
669 }
670 }
671
672 static function addTags($ttid, $tags) {
673
674 // addTags
675 // Add a list of tags to a TTindex
676
677 foreach ($tags as $tag) {
678 $t = trim($tag);
679 if ($t == '') continue;
680 logger::log("ADD TAGS", "Adding Tag",$t,"to TTindex",$ttid);
681 $tagindex = sql_prepare_query(false, null, 'Tagindex', null, "SELECT Tagindex FROM Tagtable WHERE Name=?", $t);
682 if ($tagindex == null) $tagindex = romprmetadata::create_new_tag($t);
683 if ($tagindex == null) {
684 logger::warn("ADD TAGS", " Could not create tag",$t);
685 return false;
686 }
687
688 if ($result = generic_sql_query("INSERT INTO TagListtable (TTindex, Tagindex) VALUES ('".$ttid."', '".$tagindex."')", true)) {
689 logger::debug("ADD TAGS", "Success");
690 } else {
691 // Doesn't matter, we have a UNIQUE constraint on both columns to prevent us adding the same tag twice
692 logger::debug("ADD TAGS", " .. Failed but that's OK if it's because of a duplicate entry or UNQIUE constraint");
693 }
694 }
695 return true;
696 }
697
698 static function create_new_tag($tag) {
699
700 // create_new_tags
701 // Creates a new entry in Tagtable
702 // Returns: Tagindex
703
704 global $mysqlc;
705 logger::mark("CREATE TAG", "Creating new tag",$tag);
706 $tagindex = null;
707 if (sql_prepare_query(true, null, null, null, "INSERT INTO Tagtable (Name) VALUES (?)", $tag)) {
708 $tagindex = $mysqlc->lastInsertId();
709 }
710 return $tagindex;
711 }
712
713 static function remove_tag($ttid, $tag) {
714
715 // remove_tags
716 // Removes a tag relation from a TTindex
717
718 logger::log("REMOVE TAG", "Removing Tag",$tag,"from TTindex",$ttid);
719 $retval = false;
720 if ($tagindex = simple_query('Tagindex', 'Tagtable', 'Name', $tag, false)) {
721 $retval = generic_sql_query("DELETE FROM TagListtable WHERE TTindex = '".$ttid."' AND Tagindex = '".$tagindex."'", true);
722 } else {
723 logger::warn("REMOVE TAG", " .. Could not find tag",$tag);
724 }
725 return $retval;
726 }
727
728 static function remove_tag_from_db($tag) {
729 logger::mark("REMOVE TAG", "Removing Tag",$tag,",from database");
730 return sql_prepare_query(true, null, null, null, "DELETE FROM Tagtable WHERE Name=?", $tag);
731 }
732
733 static function delete_track($ttid) {
734 if (remove_ttid($ttid)) {
735 } else {
736 header('HTTP/1.1 400 Bad Request');
737 }
738 }
739
740 static function amend_album($albumindex, $newartist, $date) {
741 logger::mark("AMEND ALBUM", "Updating Album index",$albumindex,"with new artist",$newartist,"and new date",$date);
742 $artistindex = ($newartist == null) ? null : check_artist($newartist);
743 $result = sql_prepare_query(false, PDO::FETCH_OBJ, null, null, "SELECT * FROM Albumtable WHERE Albumindex = ?", $albumindex);
744 $obj = array_shift($result);
745 if ($obj) {
746 $params = array(
747 'album' => $obj->Albumname,
748 'albumai' => ($artistindex == null) ? $obj->AlbumArtistindex : $artistindex,
749 'albumuri' => $obj->AlbumUri,
750 'image' => $obj->Image,
751 'date' => ($date == null) ? $obj->Year : $date,
752 'searched' => $obj->Searched,
753 'imagekey' => $obj->ImgKey,
754 'ambid' => $obj->mbid,
755 'domain' => $obj->Domain);
756 $newalbumindex = check_album($params);
757 if ($albumindex != $newalbumindex) {
758 logger::log("AMEND ALBUM", "Moving all tracks from album",$albumindex,"to album",$newalbumindex);
759 if (sql_prepare_query(true, null, null, null, "UPDATE Tracktable SET Albumindex = ? WHERE Albumindex = ?", $newalbumindex, $albumindex)) {
760 logger::debug("AMEND ALBUM", "...Success");
761 } else {
762 logger::warn("AMEND ALBUM", "Track move Failed!");
763 return false;
764 }
765 }
766 } else {
767 logger::error("AMEND ALBUM", "Failed to find album to update!");
768 return false;
769 }
770 return true;
771 }
772
773 static function delete_album($albumindex) {
774 $result = generic_sql_query('DELETE FROM Tracktable WHERE Albumindex = '.$albumindex);
775 return true;
776 }
777
778 static function set_as_audiobook($albumindex, $value) {
779 $result = sql_prepare_query(true, null, null, null, 'UPDATE Tracktable SET isAudiobook = ?, justAdded = 1 WHERE Albumindex = ?', $value, $albumindex);
780 return $result;
781 }
782
783}
784
785function forcedUriOnly($u,$d) {
786
787 // Some mopidy backends - YouTube and SoundCloud - can return the same artist/album/track info
788 // for multiple different tracks.
789 // This gives us a problem because romprmetadata::find_item will think they're the same.
790 // So for those backends we always force urionly to be true
791 logger::debug("USERRATINGS", "Checking domain : ".$d);
792
793 if ($u || $d == "youtube" || $d == "soundcloud") {
794 return true;
795 } else {
796 return false;
797 }
798
799}
800
801function preparePlaylist() {
802 generic_sql_query('UPDATE Tracktable SET usedInPlaylist = 0 WHERE usedInPlaylist = 1');
803 init_random_albums();
804}
805
806function doPlaylist($playlist, $limit) {
807 global $prefs;
808 logger::mark("SMARTRADIO", "Loading Playlist",$playlist,'limit',$limit);
809 $sqlstring = "";
810 $tags = null;
811 $random = true;
812 switch($playlist) {
813 case "1stars":
814 $sqlstring = "SELECT Uri FROM Tracktable JOIN Ratingtable USING (TTindex) WHERE Rating > 0";
815 break;
816 case "2stars":
817 $sqlstring = "SELECT Uri FROM Tracktable JOIN Ratingtable USING (TTindex) WHERE Rating > 1";
818 break;
819 case "3stars":
820 $sqlstring = "SELECT Uri FROM Tracktable JOIN Ratingtable USING (TTindex) WHERE Rating > 2";
821 break;
822 case "4stars":
823 $sqlstring = "SELECT Uri FROM Tracktable JOIN Ratingtable USING (TTindex) WHERE Rating > 3";
824 break;
825 case "5stars":
826 $sqlstring = "SELECT Uri FROM Tracktable JOIN Ratingtable USING (TTindex) WHERE Rating > 4";
827 break;
828
829 case "recentlyadded_byalbum":
830 $random = false;
831 case "recentlyadded_random":
832 $sqlstring = sql_recent_tracks();
833 break;
834
835 case "favealbums":
836 $avgplays = getAveragePlays();
837 $sqlstring = "SELECT Uri FROM Tracktable JOIN Albumtable USING (Albumindex)
838 WHERE Albumindex IN
839 (SELECT Albumindex FROM Tracktable JOIN Playcounttable USING (TTindex)
840 LEFT JOIN Ratingtable USING (TTindex) WHERE Uri IS NOT NULL
841 AND (Playcount > ".$avgplays." OR Rating IS NOT NULL))";
842 $random = false;
843 break;
844
845 case "mostplayed":
846 // Used to be tracks with above average playcount, now also includes any rated tracks.
847 // Still called mostplayed :)
848 $avgplays = getAveragePlays();
849 $sqlstring = "SELECT Uri FROM Tracktable JOIN Playcounttable USING (TTindex)
850 LEFT JOIN Ratingtable USING (TTindex) WHERE Uri IS NOT NULL
851 AND (Playcount > ".$avgplays." OR Rating IS NOT NULL)";
852 break;
853
854 case "allrandom":
855 $sqlstring = "SELECT Uri FROM Tracktable WHERE Uri IS NOT NULL AND Hidden=0 AND
856 isSearchResult < 2";
857 break;
858
859 case "neverplayed":
860 $sqlstring = "SELECT Tracktable.Uri FROM Tracktable LEFT JOIN Playcounttable ON
861 Tracktable.TTindex = Playcounttable.TTindex WHERE Playcounttable.TTindex IS NULL";
862 break;
863
864 case "recentlyplayed":
865 $sqlstring = recently_played_playlist();
866 break;
867
868 default:
869 if (preg_match('/^(\w+)\+(.+)$/', $playlist, $matches)) {
870 $fn = 'smart_radio_'.$matches[1];
871 list($sqlstring, $tags) = $fn($matches[2]);
872 } else {
873 logger::warn("SMART RADIO", "Unrecognised playlist",$playlist);
874 }
875 break;
876 }
877 $sqlstring .= ' AND (LinkChecked = 0 OR LinkChecked = 2) AND isAudiobook = 0 AND usedInPlaylist = 0 AND isSearchResult < 2 AND Hidden = 0 AND Uri IS NOT NULL';
878 if ($prefs['collection_player'] == 'mopidy' && $prefs['player_backend'] == 'mpd') {
879 $sqlstring .= ' AND Uri LIKE "local:%"';
880 }
881 $uris = getAllURIs($sqlstring, $limit, $tags, $random);
882 $json = array();
883 foreach ($uris as $u) {
884 $json[] = array( 'type' => 'uri', 'name' => $u);
885 }
886 return $json;
887}
888
889function smart_radio_tag($param) {
890 $taglist = explode(',', $param);
891 $sqlstring = 'SELECT DISTINCT Uri FROM Tracktable JOIN TagListtable USING (TTindex) JOIN Tagtable USING (Tagindex) WHERE ';
892 // Concatenate this bracket here otherwise Atom's syntax colouring goes haywire
893 $sqlstring .= '(';
894 $tags = array();
895 foreach ($taglist as $i => $tag) {
896 logger::trace("SMART RADIO", "Getting tag playlist for",$tag);
897 $tags[] = strtolower(trim($tag));
898 if ($i > 0) {
899 $sqlstring .= " OR ";
900 }
901 $sqlstring .= "LOWER(Tagtable.Name) = ?";
902 }
903 $sqlstring .= ")";
904 return array($sqlstring, $tags);
905}
906
907function smart_radio_genre($param) {
908 $genrelist = explode(',', $param);
909 $sqlstring = 'SELECT DISTINCT Uri FROM Tracktable JOIN Genretable USING (Genreindex) WHERE ';
910 // Concatenate this bracket here otherwise Atom's syntax colouring goes haywire
911 $sqlstring .= '(';
912 $tags = array();
913 foreach ($genrelist as $i => $genre) {
914 logger::trace("SMART RADIO", "Getting genre playlist for",$genre);
915 $tags[] = strtolower(trim($genre));
916 if ($i > 0) {
917 $sqlstring .= " OR ";
918 }
919 $sqlstring .= "LOWER(Genre) = ?";
920 }
921 $sqlstring .= ")";
922 return array($sqlstring, $tags);
923}
924
925function smart_radio_artist($param) {
926 $artistlist = explode(',', $param);
927 $sqlstring = 'SELECT DISTINCT Uri FROM Tracktable JOIN Artisttable USING (Artistindex) WHERE ';
928 // Concatenate this bracket here otherwise Atom's syntax colouring goes haywire
929 $sqlstring .= '(';
930 $tags = array();
931 foreach ($artistlist as $i => $artist) {
932 logger::trace("SMART RADIO", "Getting artist playlist for",$artist);
933 $tags[] = strtolower(trim($artist));
934 if ($i > 0) {
935 $sqlstring .= " OR ";
936 }
937 $sqlstring .= "LOWER(Artistname) = ?";
938 }
939 $sqlstring .= ")";
940 return array($sqlstring, $tags);
941}
942
943function smart_radio_custom($param) {
944 $station = json_decode(file_get_contents('prefs/customradio/'.format_for_disc($param).'.json'), true);
945 $tags = array();
946 $sqlstring = "SELECT DISTINCT Uri FROM
947 Tracktable
948 JOIN Artisttable AS ta USING (Artistindex)
949 JOIN Albumtable USING (Albumindex)
950 JOIN Artisttable AS aa ON (Albumtable.AlbumArtistindex = aa.Artistindex)
951 LEFT JOIN Genretable USING (Genreindex)
952 LEFT JOIN Ratingtable USING (TTindex)
953 LEFT JOIN Playcounttable USING (TTindex)
954 LEFT JOIN TagListtable USING (TTindex)
955 LEFT JOIN Tagtable USING (Tagindex)
956 WHERE (";
957 foreach ($station['rules'] as $i => $rule) {
958 if ($i > 0) {
959 $sqlstring .= $station['combine_option'];
960 }
961 $values = explode(',', $rule['value']);
962 $sqlstring .= '(';
963 foreach ($values as $j => $value) {
964 logger::log('CUSTOMRADIO',$rule['db_key'],$value);
965 if ($j > 0) {
966 switch ($rule['option']) {
967 case RADIO_RULE_OPTIONS_STRING_IS:
968 case RADIO_RULE_OPTIONS_STRING_CONTAINS:
969 $sqlstring .= ' OR ';
970 break;
971
972 case RADIO_RULE_OPTIONS_STRING_IS_NOT:
973 case RADIO_RULE_OPTIONS_STRING_NOT_CONTAINS:
974 $sqlstring .= ' AND ';
975 break;
976
977 default:
978 logger::error('CUSTOMRADIO', 'Multiple values in integer option!');
979 break;
980 }
981 }
982
983 if (preg_match('/db_function_(.+)$/', $rule['db_key'], $matches)) {
984 $function = $matches[1];
985 $sqlstring .= $function($rule['option'], trim($value));
986 } else {
987 switch ($rule['option']) {
988 case RADIO_RULE_OPTIONS_STRING_IS:
989 $tags[] = strtolower(trim($value));
990 $sqlstring .= 'LOWER('.$rule['db_key'].') = ?';
991 break;
992
993 case RADIO_RULE_OPTIONS_STRING_IS_NOT:
994 $tags[] = strtolower(trim($value));
995 $sqlstring .= 'LOWER('.$rule['db_key'].') IS NOT ?';
996 break;
997 break;
998
999 case RADIO_RULE_OPTIONS_STRING_CONTAINS:
1000 $tags[] = "%".strtolower(trim($value))."%";
1001 $sqlstring .= 'LOWER('.$rule['db_key'].") LIKE ?";
1002 break;
1003
1004 case RADIO_RULE_OPTIONS_STRING_NOT_CONTAINS:
1005 $tags[] = "%".strtolower(trim($value))."%";
1006 $sqlstring .= 'LOWER('.$rule['db_key'].") NOT LIKE ?";
1007 break;
1008
1009 case RADIO_RULE_OPTIONS_INTEGER_LESSTHAN:
1010 $tags[] = trim($value);
1011 $sqlstring .= $rule['db_key'].' < ?';
1012 break;
1013
1014 case RADIO_RULE_OPTIONS_INTEGER_EQUALS:
1015 $tags[] = trim($value);
1016 $sqlstring .= $rule['db_key'].' = ?';
1017 break;
1018
1019 case RADIO_RULE_OPTIONS_INTEGER_GREATERTHAN:
1020 $tags[] = trim($value);
1021 $sqlstring .= $rule['db_key'].' > ?';
1022 break;
1023
1024 case RADIO_RULE_OPTIONS_STRING_EXISTS:
1025 $sqlstring .= $rule['db_key'].' IS NOT NULL';
1026 break;
1027
1028 default:
1029 logger::error('CUSTOMRADIO', 'Unknown Option Value',$rule['option']);
1030 break;
1031
1032 }
1033 }
1034
1035 }
1036 $sqlstring.= ')';
1037
1038 }
1039 $sqlstring .= ")";
1040
1041 return array($sqlstring, $tags);
1042}
1043
1044function getAllURIs($sqlstring, $limit, $tags, $random = true) {
1045
1046 // Get all track URIs using a supplied SQL string. For playlist generators
1047 $uris = array();
1048 $tries = 0;
1049 $rndstr = $random ? " ORDER BY ".SQL_RANDOM_SORT : " ORDER BY randomSort, Albumindex, Disc, TrackNo";
1050 $sqlstring .= ' '.$rndstr.' LIMIT '.$limit;
1051 logger::log('GETALLURIS', $sqlstring);
1052 foreach ($tags as $t) {
1053 logger::log('GETALLURILS', ' Param :',$t);
1054 }
1055 do {
1056 if ($tries == 1) {
1057 logger::info("SMART PLAYLIST", "No URIs found. Resetting history table");
1058 preparePlaylist();
1059 }
1060 if ($tags) {
1061 $uris = sql_prepare_query(false, PDO::FETCH_COLUMN, 'Uri', null, $sqlstring, $tags);
1062 } else {
1063 $uris = sql_get_column($sqlstring, 'Uri');
1064 }
1065 $tries++;
1066 } while (count($uris) == 0 && $tries < 2);
1067 foreach ($uris as $uri) {
1068 sql_prepare_query(true, null, null, null, 'UPDATE Tracktable SET usedInPlaylist = 1 WHERE Uri = ?', $uri);
1069 }
1070 if (count($uris) == 0) {
1071 $uris = array('NOTRACKS!');
1072 }
1073 return $uris;
1074}
1075
1076function getAveragePlays() {
1077 $avgplays = simple_query('avg(Playcount)', 'Playcounttable', null, null, 0);
1078 return round($avgplays, 0, PHP_ROUND_HALF_DOWN);
1079}
1080
1081?>
1082