def _get_scores(self):
conn = db_session.connection()
votes = conn.execute("select pi.id AS pli_id, sum(v.direction) AS score from votes v join playlist_items pi on v.playlist_item_id = pi.id join locations l on pi.location_id = l.id where pi.done_playing=0 and pi.bumped=0 and l.id=%s group by pi.id" % self.loc_id)
dic = dict()
for row in votes:
dic[row["pli_id"]] = int(row["score"])
return dic
def leaderboard(self, hrs=0):
conn = db_session.connection()
#TODO: Better sql here. This is pretty weak sauce
#this is a special case to handle the "all time" leaderboard (hrs=0)
if hrs == 0: query = "select u.id as user_id, u.first_name, u.last_name, u.photo_url, u.facebook_id, sum(direction) as score from votes v inner join playlist_items pi on v.playlist_item_id = pi.id inner join users u on pi.user_id = u.id where pi.location_id = %s group by pi.user_id order by score desc limit 10;" % (self.id)
else: query = "select u.id as user_id, u.first_name, u.last_name, u.photo_url, u.facebook_id, sum(direction) as score from votes v inner join playlist_items pi on v.playlist_item_id = pi.id inner join users u on pi.user_id = u.id where pi.location_id = %s and v.timestamp > DATE_SUB(NOW(), INTERVAL %s HOUR) group by pi.user_id order by score desc limit 10;" % (self.id, str(hrs))
leaders = conn.execute(query)
ret = []
for row in leaders:
newrow = dict() #necessary because the SQLALCHEMY row doesn't support value updates
for k in row.keys():
if isinstance(row[k], decimal.Decimal): newrow[k] = int(row[k])
elif k == "last_name" and not User.is_admin(): newrow[k] = row[k][0] #only get first letter of last name if its not an admin
else: newrow[k] = row[k]
ret.append(newrow)
return ret
