" . $time . " " . "AND u.last_seen > " . $last_seen_time; $db_result = mysql_query($sql, $dbh); $connected_users = mysql_fetch_row($db_result); $hours = floor($songs['time']/3600); $size = $songs['size']/1048576; $days = floor($hours/24); $hours = $hours%24; $time_text = "$days "; $time_text .= ($days == 1) ? _("day") : _("days"); $time_text .= ", $hours "; $time_text .= ($hours == 1) ? _("hour") : _("hours"); if ( $size > 1024 ) { $total_size = sprintf("%.2f", ($size/1024)); $size_unit = "GB"; } else { $total_size = sprintf("%.2f", $size); $size_unit = "MB"; } print '

' . _("Catalog Statistics") . '
' . _("Total Users") . '	' . $users[0] .'
' . _("Connected Users") . '	' . $connected_users[0] .'
' . _("Albums") . '	' . $albums[0] .'
' . _("Artists") . '	' . $artists[0] .'
' . _("Songs") . '	' . $songs[0] .'
' . _("Catalog Size") . '	' . $total_size .' ' . $size_unit .'
' . _("Catalog Time") . '	' . $time_text .'

'; } // show_local_catalog_info() /* * show_random_play() * */ function show_random_play() { $web_path = conf('web_path'); print ' '; } // show_random_play() /* * show_artist_pulldown() * * Helper functions for album and artist functions * */ function show_artist_pulldown ($artist) { global $settings; $dbh = dbh(); $query = "SELECT id,name FROM artist ORDER BY name"; $db_result = mysql_query($query, $dbh); echo "\n"; } // show_artist_pulldown() /* * show_album_pulldown() * */ function show_album_pulldown ($album) { global $settings; $dbh = dbh(); $sql = "SELECT id,name FROM album ORDER BY name"; $db_result = mysql_query($sql, $dbh); echo "\n\n"; } // show_album_pulldown() /* * show_flagged_popup($reason); * * Shows a listing of the flagged_types for when people want to mark * a song as being broken in some way. */ function show_flagged_popup($reason,$label='value', $name='flagged_type', $other='') { global $settings; $dbh = dbh(); $access = $_SESSION['userdata']['access']; $query = "SELECT type,value FROM flagged_types"; if ($access !== 'admin') { $query .= " WHERE access = '$access'"; } $db_result = mysql_query($query, $dbh); echo "\n\n"; } // show_flagged_popup() /* * show_genre_pulldown() * * Set complete=1 if you want the entire genre list * */ function show_genre_pulldown ($genre, $complete) { $dbh = dbh(); // find the genres we have in use if ( $complete ) { $sql = "SELECT id FROM genre ORDER BY name"; } else { $sql = "SELECT DISTINCT song.genre FROM genre, song" . " WHERE song.genre = genre.id" . " ORDER BY genre.name"; } $db_result = mysql_query($sql, $dbh); echo "\n\n"; } // show_genre_pulldown() /* * show_catalog_pulldown() * * Set complete=1 if you want the entire catalog list (including disabled) * */ function show_catalog_pulldown ($catalog, $complete) { global $settings; // find the genres we have in use $sql = "SELECT id,name FROM catalog ORDER BY name"; $db_result = mysql_query($sql, dbh()); echo "\n\n"; } // show_catalog_pulldown() /* * update_counter() * * update what song/album/artist has just been played * */ function update_counter ($type, $id, $dbh=0) { global $settings; if (!is_resource($dbh)) { $dbh = dbh(); } // from hopson: these queries will be very useful for generating overall statistics: /* SELECT song.title,SUM(object_count.count) FROM song,object_count WHERE object_count.object_type = 'song' AND object_count.object_id = song.id GROUP BY song.id; SELECT album.name,SUM(object_count.count) FROM album,object_count WHERE object_count.object_type = 'album' AND object_count.object_id = album.id GROUP BY album.id; SELECT artist.name,SUM(object_count.count) FROM artist,object_count WHERE object_count.object_type = 'artist' AND object_count.object_id = artist.id GROUP BY artist.id; SELECT playlist.name,SUM(object_count.count) FROM playlist,object_count WHERE object_count.object_type = 'playlist' AND object_count.object_id = playlist.id GROUP BY playlist.id; */ if ( $type == 'song' ) { $sql = "UPDATE $type SET times_played = times_played + 1 WHERE id = '$id'"; } else { $sql = "UPDATE $type SET times_played = times_played + 1 WHERE id = '$id'"; } $db_result = mysql_query($sql, $dbh); } // update_counter() /* * delete_user_stats() * * just delete stats for specific users or all of them * */ function delete_user_stats ($user) { $dbh = dbh(); if ( $user == 'all' ) { $sql = "DELETE FROM object_count"; } else { $sql = "DELETE FROM object_count WHERE userid = '$user'"; } $db_result = mysql_query($sql, $dbh); } // delete_user_stats() /* * insert_flagged_song() * */ function insert_flagged_song($song, $reason, $comment) { $user = $_SESSION['userdata']['id']; $time = time(); $sql = "INSERT INTO flagged (user,song,type,comment,date)" . " VALUES ('$user','$song', '$reason', '$comment', '$time')"; $db_result = mysql_query($sql, dbh()); } // insert_flagged_song() /* * get_flagged(); * * Get all of the songs from the flagged table. These are songs that * may or may not be broken. * Deprecated by hopson on 7/27 */ function get_flagged() { $dbh = dbh(); $sql = "SELECT flagged.id, user.username, type, song, date, comment" . " FROM flagged, user" . " WHERE flagged.user = user.username" . " ORDER BY date"; $db_result = mysql_query($sql, $dbh); $arr = array(); while ( $flag = mysql_fetch_object($db_result) ) { $arr[] = $flag; } return $arr; } // get_flagged() /* * get_flagged_type($type); * * Return the text associated with this type. */ function get_flagged_type($type) { $dbh = dbh(); $sql = "SELECT value FROM flagged_types WHERE type = '$type'"; echo $sql; $db_result = mysql_query($sql, $dbh); if ($flagged_type = mysql_fetch_object($db_result)) { return $flagged_type->value; } else { return FALSE; } } // get_flagged_type() /* * delete_flagged( $flag ); * */ function delete_flagged($flag) { $dbh = dbh(); $sql = "DELETE FROM flagged WHERE id = '$flag'"; $db_result = mysql_query($sql, $dbh); } // delete_flagged() /*********************************************************/ /* Functions for getting songs given artist, album or id */ /*********************************************************/ // TODO : albums should be always gruoped by // id, like 'greatest hits' album is below, never by name. // Other catalog functions should take care of assigning all // songs with same name album to the same album id. It should // not be done here. // I'm commenting all this out to always sort by ID, to // see how bad it is. -Rubin function get_songs_from_album ($album) { global $settings; $dbh = dbh(); $songs = array(); $query = "SELECT track, id as song FROM song" . " WHERE album = '$album'" . " ORDER BY track, title"; $db_result = mysql_query($query, $dbh); while ( $r = mysql_fetch_array($db_result) ) { $songs[] = $r; } return $songs; } function get_song_ids_from_album ($album) { $dbh = dbh(); $song_ids = array(); $query = "SELECT id FROM song" . " WHERE album = '$album'" . " ORDER BY track, title"; $db_result = mysql_query($query, $dbh); while ( $r = mysql_fetch_object($db_result) ) { $song_ids[] = $r->id; } return $song_ids; } function get_song_ids_from_artist ($artist) { global $settings; $dbh = dbh(); $song_ids = array(); $artist = sql_escape($artist); $query = "SELECT id FROM song" . " WHERE artist = '$artist'" . " ORDER BY album, track"; $db_result = mysql_query($query, $dbh); while ( $r = mysql_fetch_object($db_result) ) { $song_ids[] = $r->id; } return $song_ids; } /* * get_song_ids_from_artist_and_album(); * * Get all of the songs that are from this album and artist * */ function get_song_ids_from_artist_and_album ($artist, $album) { global $settings; $dbh = dbh(); $sql = "SELECT id FROM song" . " WHERE artist = '$artist'" . " AND album = '$album'" . " ORDER BY track, title"; $db_result = mysql_query($sql, $dbh); $song_ids = array(); while ( $r = mysql_fetch_object($db_result) ) { $song_ids[] = $r->id; } return $song_ids; } // Used by playlist functions when you have an array of something of type // and you want to extract the songs from it whether type is artists or albums function get_songs_from_type ($type, $results, $artist_id = 0) { $dbh = dbh(); $count = 0; $song = array(); foreach ($results as $value) { // special case from the album view where we don't want every orphan if ($type == 'album' && ($value == 'orphans' || $artist_id != 0)) { $sql = "SELECT id FROM song WHERE $type = '$value' AND artist = '$artist_id'"; $db_result = mysql_query($sql, $dbh); } else { $sql = "SELECT id FROM song WHERE $type = '$value'"; $db_result = mysql_query($sql, $dbh); } while ( $r = mysql_fetch_row($db_result) ) { $song[$count] = $r[0]; $count++; } } return $song; } /*********************************************************/ /* This is the main song display function. I found tieing it to the playlist functions was really handy in getting added functionality at no cost. /* Lets tie it to album too, so we can show art ;) */ /*********************************************************/ function show_songs ($song_ids, $playlist_id=0, $album=0) { $dbh = dbh(); // Get info about current user $user = new User($_SESSION['userdata']['username']); // Get info about playlist owner if (isset($playlist_id) && $playlist_id != 0) { $sql = "SELECT user FROM playlist WHERE id = '$playlist_id'"; $db_result = mysql_query($sql, $dbh); if ($r = mysql_fetch_array($db_result)) { $pluser = get_user_byid($r[0]); } } $totaltime = 0; $totalsize = 0; require (conf('prefix') . "/templates/show_songs.inc"); return TRUE; }// function show_songs function show_playlist_form () { print << Playlist: ECHO; show_playlist_dropdown(); print << ECHO; } function get_artist_name ($artist, $dbh=0) { global $settings; if (!is_resource($dbh)) { $dbh = dbh(); } $query = "SELECT name FROM artist WHERE id = '$artist'"; $db_result = mysql_query($query, $dbh); if ($r = mysql_fetch_object($db_result)) { return $r->name; } else { return FALSE; } } function get_artist_info ($artist_id) { $dbh = dbh(); $sql = "SELECT * FROM artist WHERE id = '$artist_id'"; $db_result = mysql_query($sql, $dbh); if ($info = mysql_fetch_array($db_result)) { $sql = "SELECT COUNT(song.album) FROM song " . " WHERE song.artist = '$artist_id'" . " GROUP BY song.album"; $db_result = mysql_query($sql, $dbh); $albums = 0; $songs = 0; while(list($song) = mysql_fetch_row($db_result)) { $songs += $song; $albums++; } $info['songs'] = $songs; $info['albums'] = $albums; //FIXME: Lame place to put this //if ($songs < conf('min_artist_songs') || $albums < conf('min_artist_albums')) { // return FALSE; //} return $info; } else { return FALSE; } } function get_artist_from_album ($album_id) { global $settings; $dbh = dbh(); $query = "SELECT DISTINCT artist.id, artist.name FROM artist,song" . " WHERE song.album = '$album_id' AND song.artist = artist.id"; $db_result = mysql_query($query, $dbh); $r = mysql_fetch_object($db_result); return $r; } function get_artist_name_from_song ($song_id) { $dbh = dbh(); $sql = "SELECT artist.name AS name FROM artist, song" . " WHERE artist.id = song.artist" . " AND song.id = '$song_id'"; $db_result = mysql_query($sql, $dbh); if ($r = mysql_fetch_object($db_result)) { return $r->name; } else { return FALSE; } } function get_album_name ($album, $dbh = 0) { $album = new Album($album); return $album->name; } // get_album_name function get_genre_info($genre_id) { global $settings; $dbh = dbh(); $sql = "SELECT name FROM genre WHERE id = '$genre_id'"; $db_result = mysql_query($sql, $dbh); // if its -1 then we're doing all songs if ( $genre_id < 0 ) { $sql = "SELECT count(*) FROM song"; } else { $sql = "SELECT count(*) FROM song WHERE genre = '$genre_id'"; } $genre_result = mysql_query($sql, $dbh); $genre_count = mysql_fetch_row($genre_result); $r = mysql_fetch_row($db_result); // Crude hack for non-standard genre types if ($genre_id == -1) { return array('All', $genre_count[0]); } elseif ($genre_id == 0) { return array('N/A', $genre_count[0]); } else { return array($r[0], $genre_count[0]); } } function get_genre($id) { global $settings; $dbh = dbh(); $query = "SELECT * FROM genre WHERE id = '$id'"; $db_result = mysql_query($query, $dbh); $r = mysql_fetch_object($db_result); return $r; } // Utility function to help move things along function get_song_info ($song, $dbh = 0) { $song = new Song($song); return $song; } // get_song_info /*! @function show_albums @discussion show many albums, uses view class */ function show_albums ($albums,$view=0) { $dbh = libglue_param(libglue_param('dbh_name')); if (!$view) { $view = new View($_SESSION['view_base_sql'], $_SESSION['script'], $total_items,$_SESSION['view_offset_limit']); } if ($albums) { require (conf('prefix') . "/templates/show_albums.inc"); } else { echo "

No Albums Found

"; } } // show_albums // Had to tweak this so it would show both public and private playlists // Defaults to showing both although you could pass type=private|adminprivate|public // to see only those function show_playlists ($type = 'all') { $dbh = dbh(); $user = $GLOBALS['user']; $web_path = conf('web_path'); // mapping of types to pretty names $typemap = array( "public" => _("Public"), "private" => _("Your Private"), "adminprivate" => _("Other Private") ); if ($type == 'all') { show_playlists('private'); if ( $user->access === 'admin' ) { show_playlists('adminprivate'); } show_playlists('public'); return true; } elseif ($type == 'public') { $sql = "SELECT id,name,user,date ". " FROM playlist ". " WHERE type='public'". " ORDER BY name"; } elseif ($type == 'private') { $sql = "SELECT id,name,user,date ". " FROM playlist ". " WHERE type='private'" . " AND user = '$user->username'" . " AND name <> 'Temporary'". " ORDER BY name"; } elseif ($type == 'adminprivate') { if ( $user->access === 'admin' ) { $sql = "SELECT id,name,user,date ". " FROM playlist ". " WHERE type='private'" . " AND username != '$user->username'" . " AND name <> 'Temporary'". " ORDER BY name"; } else { // No admin access $sql = 'SELECT 1+1'; } } else { echo "** Error ** Call to show_playlists with unknown type $type ". "in file ".$_SERVER['PHP_SELF']." **
\n"; $sql = 'SELECT 1+1'; } $db_result = mysql_query($sql, $dbh); print <<$typemap[$type] Playlists ECHO; flip_class(array('even','odd')); if ( mysql_num_rows($db_result) ) { while ( $r = mysql_fetch_array($db_result) ) { $plname = $r['name']; $plid = $r['id']; $pluser = new User($r['user']); $plfullname = $pluser->fullname; $plowner = $pluser->username; // find out how many songs in this playlist $count_query = "SELECT count(*) ". " FROM playlist_data ". " WHERE playlist = '$plid'"; $count_result = mysql_query($count_query, $dbh); list($count) = mysql_fetch_row($count_result); $class = flip_class(); echo " \n"; echo " \n"; echo " \n"; echo " \n"; echo " \n"; if ($user->username == $pluser->username || $user->has_access(100)) { echo " \n"; echo " \n"; } else { echo " \n"; echo " \n"; } if ( $count[0] ) { echo " \n"; } else { echo " \n"; } if( batch_ok() ) { echo" \n"; } else { echo" \n"; } echo " \n"; } echo "\n"; } //if rows in result else { echo " \n"; echo " \n"; echo " \n"; } echo "

Playlist Name	# Songs	Owner	Actions
$plname	$count	$plfullname	" . _("View") . "	" . _("Edit") . "	" . _("Delete") . "			" . _("Play") . " \| " . "" . _("Random") . "		" . _("Download") . "
" . _("There are no playlists of this type") . "

\n"; echo "
\n"; } function get_playlist_track_from_song ( $playlist_id, $song_id ) { $dbh = dbh(); $sql = "SELECT track FROM playlist_data" . " WHERE playlist = '$playlist_id'" . " AND song = '$song_id'"; $db_result = mysql_query($sql, $dbh); if ($r = mysql_fetch_array($db_result)) { return $r[0]; } else { return FALSE; } } //FIXME: Pull this and put it in a template function show_playlist_create () { $web_path = conf('web_path'); print <<

ECHO; print _("Create a new playlist"); print <<
Name:
Type:

ECHO; } function show_playlist_edit ( $playlist ) { $username = $_SESSION['userdata']['username']; if (check_playlist_access($playlist->id,$username) == false) { show_playlist_access_error($playlist, $username); return; } $plname = $playlist->name; $self = $_SERVER['PHP_SELF']; print <<

Editing Playlist
Name:
Type:

ECHO; } // See if this user has access to work on this list function check_playlist_access ($playlist_id, $username) { $dbh = dbh(); $sql = "SELECT playlist.id FROM playlist, user" . " WHERE playlist.id = '$playlist_id'" . " AND playlist.user = user.username" . " AND user.username = '$username'"; $db_result = mysql_query($sql, $dbh); if ( mysql_num_rows($db_result) == 1) { return TRUE; } else { if (!conf('use_auth')) { return TRUE; } // check to see if this user is an admin if ($user = get_user($username)) { if ( $user->access == 'admin' ) { return TRUE; } } } // If we get here, access is denied return FALSE; } function show_playlist_dropdown ($playlist_id=0) { global $settings; $dbh = dbh(); $userid = scrub_in($_SESSION['userdata']['username']); $sql = "SELECT * FROM playlist" . " WHERE user = '$userid'" . " AND name <> 'Temporary'" . " ORDER BY name"; $db_result = @mysql_query($sql, $dbh); print << ECHO; while ( $r = @mysql_fetch_object($db_result) ) { if ( $playlist_id == $r->id ) { echo "\n"; } else { echo "\n"; } } echo "\n"; } // Used to show when we have an access error for a playlist function show_playlist_access_error ($playlist, $username) { $plname = $playlist->name; $pluser = new User($playlist->user); $plowner = $pluser->username; print << Playlist Access Error

$username doesn't have access to update the '$plname' playlist, it is owned by $plowner.

ECHO; } // Used to show a form with confirm action button on it (for deleting playlists, users, etc) /*! @function show_confirm_action @discussion shows a confirmation of an action, gives a YES/NO choice */ function show_confirm_action ($text, $script, $arg) { $web_path = conf('web_path'); require (conf('prefix') . "/templates/show_confirm_action.inc.php"); } // show_confirm_action // search functions function search_by_type ($type, $search) { $dbh = dbh(); // supported types are album, artist and song if ( $type == 'Album' ) { $query = "SELECT id FROM album WHERE name LIKE '%$search%'"; } elseif ( $type == 'Artist' ) { $query = "SELECT id FROM artist WHERE name LIKE '%$search%'"; } elseif ( $type == 'Song title' ) { $query = "SELECT id FROM song WHERE title LIKE '%$search%'"; } elseif ( $type == 'Genre' ) { $query = "SELECT song.id as id FROM song, genre" . " WHERE song.genre = genre.id" . " AND genre.name LIKE '%$search%'"; } $db_result = mysql_query($query, $dbh); $search_result = array(); while ( $r = mysql_fetch_array($db_result) ) { $search_result[] = $r; } return ($search_result); } function scrub_out($str) { return stripslashes($str); } function unhtmlentities ($string) { $trans_tbl = get_html_translation_table (HTML_ENTITIES); $trans_tbl = array_flip ($trans_tbl); $ret = strtr ($string, $trans_tbl); return preg_replace('/&#(\d+);/me', "chr('\\1')",$ret); } function insert_album($album) { global $settings; $dbh = dbh(); preg_match("/^(A |An |The ){0,1}(.*?)$/i",$album, $matches); $album = sql_escape($matches[2]); switch($matches[1]) { case 'The ': case 'the ': $prefix = 'The'; break; case 'A ': case 'a ': $prefix = 'A'; break; case 'An ': case 'an ': $prefix = 'An'; break; default: $prefix = ''; } $sql = "INSERT INTO album (name, prefix)" . " VALUES ( '$album', '$prefix' )"; $db_result = mysql_query($sql, $dbh); return (mysql_insert_id($dbh)); } // insert_album /* * insert_artist() * */ function insert_artist($artist) { global $settings; $dbh = dbh(); $matches = array(); $var = preg_match('/^(A |An |The ){0,1}(.*?)$/i',$artist, $matches); $artist = sql_escape($matches[2]); switch($matches[1]) { case 'The ': case 'the ': $prefix = 'The'; break; case 'A ': case 'a ': $prefix = 'A'; break; case 'An ': case 'an ': $prefix = 'An'; break; default: $prefix = ''; } $sql = "INSERT INTO artist (name, prefix)" . " VALUES ( '$artist', '$prefix' )"; $db_result = mysql_query($sql, $dbh); return (mysql_insert_id($dbh)); } // insert_artist ?>