def post(self): user = db.c.fetch_row( "SELECT user_id, username AS name, user_avatar AS avatar, user_avatar_type AS avatar_type, user_colour AS colour, rank_title AS rank, " "radio_totalvotes AS total_votes, radio_totalratings AS total_ratings, radio_totalmindchange AS mind_changes, " "radio_totalrequests AS total_requests, radio_winningvotes AS winning_votes, radio_losingvotes AS losing_votes, " "radio_winningrequests AS winning_requests, radio_losingrequests AS losing_requests " "FROM phpbb_users LEFT JOIN phpbb_ranks ON (user_rank = rank_id) WHERE user_id = %s", (self.get_argument("id"),)) user['avatar'] = UserLib.solve_avatar(user['avatar_type'], user['avatar']) user.pop("avatar_type") # user['top_albums'] = db.c.fetch_all( # "SELECT album_name, album_rating_user, album_rating " # "FROM r4_album_ratings " # "JOIN r4_album_sid USING (album_id) " # "JOIN r4_albums USING (album_id) " # "WHERE user_id = %s AND r4_album_ratings.sid = %s AND album_exists = TRUE " # "ORDER BY album_rating DESC " # "LIMIT 20", # (self.get_argument("id"), self.sid)) user['votes_by_station'] = db.c.fetch_all("SELECT sid, COUNT(vote_id) AS votes " "FROM r4_vote_history " "WHERE user_id = %s " "GROUP BY sid", (self.get_argument("id"),)) user['votes_by_source_station'] = db.c.fetch_all("SELECT song_origin_sid AS sid, COUNT(vote_id) AS votes " "FROM r4_vote_history JOIN r4_songs USING (song_id) " "WHERE user_id = %s " "GROUP BY song_origin_sid", (self.get_argument("id"),)) user['requests_by_station'] = db.c.fetch_all("SELECT sid, COUNT(request_id) AS requests " "FROM r4_request_history " "WHERE user_id = %s AND sid IS NOT NULL " "GROUP BY sid", (self.get_argument("id"),)) user['requests_by_source_station'] = db.c.fetch_all("SELECT song_origin_sid AS sid, COUNT(request_id) AS requests " "FROM r4_request_history JOIN r4_songs USING (song_id) " "WHERE user_id = %s AND song_verified = TRUE " "GROUP BY song_origin_sid", (self.get_argument("id"),)) user['ratings_by_station'] = db.c.fetch_all("SELECT song_origin_sid AS sid, TO_CHAR(AVG(song_rating_user), 'FM9.99') AS average_rating, COUNT(song_rating_user) AS ratings " "FROM r4_song_ratings JOIN r4_songs USING (song_id) " "WHERE user_id = %s AND song_verified = TRUE AND song_origin_sid > 0 " "GROUP BY song_origin_sid", (self.get_argument("id"),)) user['rating_completion'] = {} for row in user['ratings_by_station']: user['rating_completion'][row['sid']] = math.floor(float(row['ratings']) / float(playlist.num_songs[row['sid']]) * 100) user['rating_spread'] = db.c.fetch_all("SELECT COUNT(song_id) AS ratings, song_rating_user AS rating FROM r4_song_ratings WHERE user_id = %s AND song_rating_user IS NOT NULL GROUP BY song_rating_user ORDER BY song_rating_user", (self.get_argument("id"), )) self.append("listener", user)
def post(self): user = db.c.fetch_row( "SELECT user_id, username AS name, user_avatar AS avatar, user_avatar_type AS avatar_type, user_colour AS colour, rank_title AS rank, " "radio_totalvotes AS total_votes, radio_totalratings AS total_ratings, radio_totalmindchange AS mind_changes, " "radio_totalrequests AS total_requests, radio_winningvotes AS winning_votes, radio_losingvotes AS losing_votes, " "radio_winningrequests AS winning_requests, radio_losingrequests AS losing_requests " "FROM phpbb_users LEFT JOIN phpbb_ranks ON (user_rank = rank_id) WHERE user_id = %s", (self.get_argument("id"), )) user['avatar'] = UserLib.solve_avatar(user['avatar_type'], user['avatar']) user.pop("avatar_type") # user['top_albums'] = db.c.fetch_all( # "SELECT album_name, album_rating_user, album_rating " # "FROM r4_album_ratings " # "JOIN r4_album_sid USING (album_id) " # "JOIN r4_albums USING (album_id) " # "WHERE user_id = %s AND r4_album_ratings.sid = %s AND album_exists = TRUE " # "ORDER BY album_rating DESC " # "LIMIT 20", # (self.get_argument("id"), self.sid)) user['votes_by_station'] = db.c.fetch_all( "SELECT sid, COUNT(vote_id) AS votes " "FROM r4_vote_history " "WHERE user_id = %s " "GROUP BY sid", (self.get_argument("id"), )) user['votes_by_source_station'] = db.c.fetch_all( "SELECT song_origin_sid AS sid, COUNT(vote_id) AS votes " "FROM r4_vote_history JOIN r4_songs USING (song_id) " "WHERE user_id = %s " "GROUP BY song_origin_sid", (self.get_argument("id"), )) user['requests_by_station'] = db.c.fetch_all( "SELECT sid, COUNT(request_id) AS requests " "FROM r4_request_history " "WHERE user_id = %s AND sid IS NOT NULL " "GROUP BY sid", (self.get_argument("id"), )) user['requests_by_source_station'] = db.c.fetch_all( "SELECT song_origin_sid AS sid, COUNT(request_id) AS requests " "FROM r4_request_history JOIN r4_songs USING (song_id) " "WHERE user_id = %s " "GROUP BY song_origin_sid", (self.get_argument("id"), )) user['ratings_by_station'] = db.c.fetch_all( "SELECT song_origin_sid AS sid, TO_CHAR(AVG(song_rating_user), 'FM9.99') AS average_rating, COUNT(song_rating_user) AS ratings " "FROM r4_song_ratings JOIN r4_songs USING (song_id) " "WHERE user_id = %s " "GROUP BY song_origin_sid", (self.get_argument("id"), )) user['rating_completion'] = {} for row in user['ratings_by_station']: user['rating_completion'][row['sid']] = math.ceil( float(row['ratings']) / float(playlist.num_songs[row['sid']]) * 100) user['rating_spread'] = db.c.fetch_all( "SELECT COUNT(song_id) AS ratings, song_rating_user AS rating FROM r4_song_ratings WHERE user_id = %s AND song_rating_user IS NOT NULL GROUP BY song_rating_user ORDER BY song_rating_user", (self.get_argument("id"), )) self.append("listener", user)
def post(self): user = db.c.fetch_row( "SELECT user_id, COALESCE(radio_username, username) AS name, user_avatar AS avatar, user_avatar_type AS avatar_type, user_colour AS colour, rank_title AS rank, " "0 AS total_votes, 0 AS total_ratings, 0 AS mind_changes, " "0 AS total_requests, 0 AS winning_votes, 0 AS losing_votes, " "0 AS winning_requests, 0 AS losing_requests, user_regdate AS regdate " "FROM phpbb_users LEFT JOIN phpbb_ranks ON (user_rank = rank_id) WHERE user_id = %s", (self.get_argument("id"), ), ) user["avatar"] = UserLib.solve_avatar(user["avatar_type"], user["avatar"]) user.pop("avatar_type") user["top_albums"] = db.c.fetch_all( "SELECT album_id AS id, album_name AS name, CAST(ROUND(CAST(album_rating_user AS NUMERIC), 1) AS REAL) AS rating_listener, CAST(ROUND(CAST(album_rating AS NUMERIC), 1) AS REAL) AS rating " "FROM r4_album_ratings " "JOIN r4_album_sid USING (album_id, sid) " "JOIN r4_albums USING (album_id) " "WHERE user_id = %s AND r4_album_ratings.sid = %s AND album_exists = TRUE AND r4_album_sid.album_song_count >= 4 AND r4_album_ratings.album_rating_user > 0 " "ORDER BY album_rating_user DESC NULLS LAST, r4_album_sid.album_song_count DESC " "LIMIT 10", (self.get_argument("id"), self.sid), ) if self.sid == 5: user["top_request_albums"] = db.c.fetch_all( "SELECT COUNT(request_id) AS request_count_listener, id, name FROM (" "SELECT r4_songs.album_id AS id, album_name AS name, request_id " "FROM r4_request_history " "JOIN r4_songs USING (song_id) " "JOIN r4_albums USING (album_id) " "WHERE r4_request_history.user_id = %s " "ORDER BY request_id DESC " "LIMIT 1000" ") AS reqs " "GROUP BY id, name " "ORDER BY request_count_listener DESC " "LIMIT 10", (self.get_argument("id"), ), ) else: user["top_request_albums"] = db.c.fetch_all( "SELECT COUNT(request_id) AS request_count_listener, id, name FROM (" "SELECT r4_songs.album_id AS id, album_name AS name, request_id " "FROM r4_request_history " "JOIN r4_songs USING (song_id) " "JOIN r4_album_sid ON (r4_album_sid.sid = %s AND r4_album_sid.album_exists = TRUE AND r4_songs.album_id = r4_album_sid.album_id) " "JOIN r4_albums ON (r4_album_sid.album_id = r4_albums.album_id) " "WHERE r4_request_history.user_id = %s AND r4_request_history.sid = %s " "ORDER BY request_id DESC " "LIMIT 1000" ") AS reqs " "GROUP BY id, name " "ORDER BY request_count_listener DESC " "LIMIT 10", (self.sid, self.get_argument("id"), self.sid), ) user["votes_by_station"] = db.c.fetch_all( "SELECT sid, COUNT(vote_id) AS votes " "FROM r4_vote_history " "WHERE user_id = %s " "GROUP BY sid", (self.get_argument("id"), ), ) user["requests_by_station"] = db.c.fetch_all( "SELECT sid, COUNT(request_id) AS requests " "FROM r4_request_history " "WHERE user_id = %s AND sid IS NOT NULL " "GROUP BY sid", (self.get_argument("id"), ), ) user["requests_by_source_station"] = db.c.fetch_all( "SELECT song_origin_sid AS sid, COUNT(request_id) AS requests " "FROM r4_request_history JOIN r4_songs USING (song_id) " "WHERE user_id = %s AND song_verified = TRUE " "GROUP BY song_origin_sid", (self.get_argument("id"), ), ) user["ratings_by_station"] = db.c.fetch_all( "SELECT song_origin_sid AS sid, TO_CHAR(AVG(song_rating_user), 'FM9.99') AS average_rating, COUNT(song_rating_user) AS ratings " "FROM r4_song_ratings JOIN r4_songs USING (song_id) " "WHERE user_id = %s AND song_verified = TRUE AND song_origin_sid > 0 AND song_rating_user IS NOT NULL " "GROUP BY song_origin_sid", (self.get_argument("id"), ), ) user["rating_completion"] = {} for row in user["ratings_by_station"]: user["rating_completion"][row["sid"]] = math.floor( float(row["ratings"]) / float(playlist.num_origin_songs[row["sid"]]) * 100) user["rating_spread"] = db.c.fetch_all( "SELECT COUNT(song_id) AS ratings, song_rating_user AS rating FROM r4_song_ratings JOIN r4_songs USING (song_id) WHERE user_id = %s AND song_rating_user IS NOT NULL AND song_verified IS TRUE GROUP BY song_rating_user ORDER BY song_rating_user", (self.get_argument("id"), ), ) self.append("listener", user)