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)