def leaderboard():
"""
Get a table with users sorted by rating
"""
specific_institute = False
atable = db.auth_user
cftable = db.custom_friend
global_leaderboard = False
if request.vars.has_key("global"):
if request.vars["global"] == "True":
global_leaderboard = True
else:
if not auth.is_logged_in():
response.flash = "Login to see Friends Leaderboard"
global_leaderboard = True
else:
if not auth.is_logged_in():
global_leaderboard = True
heading = "Global Leaderboard"
afields = ["first_name", "last_name", "stopstalk_handle",
"institute", "per_day", "rating"]
cfields = afields + ["duplicate_cu"]
aquery = (atable.id > 0)
cquery = (cftable.id > 0)
if global_leaderboard is False:
if auth.is_logged_in():
heading = "Friends Leaderboard"
friends, cusfriends = utilities.get_friends(session.user_id)
custom_friends = [x[0] for x in cusfriends]
# Add logged-in user to leaderboard
friends.append(session.user_id)
aquery &= (atable.id.belongs(friends))
cquery &= (cftable.id.belongs(custom_friends))
else:
aquery &= (1 == 0)
cquery &= (1 == 0)
if request.vars.has_key("q"):
heading = "Institute Leaderboard"
institute = request.vars["q"]
if institute != "":
specific_institute = True
aquery &= (atable.institute == institute)
cquery &= (cftable.institute == institute)
reg_users = db(aquery).select(*afields)
custom_users = db(cquery).select(*cfields)
if specific_institute is False:
reg_users = db(aquery).select(*afields)
custom_users = db(cquery).select(*cfields)
# Find the total solved problems(Lesser than total accepted)
solved_count = {}
sql = """
SELECT stopstalk_handle, COUNT(DISTINCT(problem_name))
FROM submission
WHERE status = 'AC'
GROUP BY user_id, custom_user_id;
"""
tmplist = db.executesql(sql)
for user in tmplist:
solved_count[user[0]] = user[1]
complete_dict = {}
# Prepare a list of stopstalk_handles of the
# users relevant to the requested leaderboard
friends_stopstalk_handles = []
for x in reg_users:
friends_stopstalk_handles.append("'" + x.stopstalk_handle + "'")
complete_dict[x.stopstalk_handle] = []
for custom_user in custom_users:
stopstalk_handle = custom_user.stopstalk_handle
if custom_user.duplicate_cu:
stopstalk_handle = cftable(custom_user.duplicate_cu).stopstalk_handle
friends_stopstalk_handles.append("'" + stopstalk_handle + "'")
complete_dict[stopstalk_handle] = []
if friends_stopstalk_handles == []:
friends_stopstalk_handles = ["-1"]
# Build the complex SQL query
sql_query = """
SELECT stopstalk_handle, DATE(time_stamp), COUNT(*) as cnt
FROM submission
WHERE stopstalk_handle in (%s)
GROUP BY stopstalk_handle, DATE(submission.time_stamp)
ORDER BY time_stamp;
""" % (", ".join(friends_stopstalk_handles))
user_rows = db.executesql(sql_query)
for user in user_rows:
if complete_dict[user[0]] != []:
complete_dict[user[0]].append((user[1], user[2]))
else:
complete_dict[user[0]] = [(user[1], user[2])]
users = []
for user in reg_users:
try:
solved = solved_count[user.stopstalk_handle]
except KeyError:
solved = 0
tup = utilities.compute_row(user, complete_dict, solved)
if tup is not ():
users.append(tup)
for user in custom_users:
try:
if user.duplicate_cu:
orig_user = cftable(user.duplicate_cu)
solved = solved_count[orig_user.stopstalk_handle]
else:
solved = solved_count[user.stopstalk_handle]
except KeyError:
solved = 0
tup = utilities.compute_row(user, complete_dict, solved, True)
if tup is not ():
users.append(tup)
# Sort users according to the rating
users = sorted(users, key=lambda x: x[3], reverse=True)
table = TABLE(_class="centered striped")
table.append(THEAD(TR(TH("Rank"),
TH("Name"),
TH("StopStalk Handle"),
TH("Institute"),
TH("StopStalk Rating"),
TH("Rating Changes"),
TH("Per Day Changes"))))
tbody = TBODY()
rank = 1
for i in users:
if i[5]:
span = SPAN(_class="orange tooltipped",
data={"position": "right",
"delay": "50",
"tooltip": "Custom User"},
_style="cursor: pointer; " + \
"float:right; " + \
"height:10px; " + \
"width:10px; " + \
"border-radius: 50%;")
else:
span = SPAN()
tr = TR()
tr.append(TD(str(rank) + "."))
tr.append(TD(DIV(span, DIV(i[0]))))
tr.append(TD(A(i[1],
_href=URL("user", "profile", args=[i[1]]),
_target="_blank")))
tr.append(TD(A(i[2],
_href=URL("default",
"leaderboard",
vars={"q": i[2],
"global": global_leaderboard}))))
tr.append(TD(i[3]))
if i[6] > 0:
tr.append(TD(B("+%s" % str(i[6])),
_class="green-text text-darken-2"))
elif i[6] < 0:
tr.append(TD(B(i[6]), _class="red-text text-darken-2"))
else:
tr.append(TD(i[6], _class="blue-text text-darken-2"))
diff = "{:1.5f}".format(i[4])
if float(diff) == 0.0:
tr.append(TD("+" + diff, " ",
I(_class="fa fa-minus")))
elif i[4] > 0:
tr.append(TD("+" + str(diff), " ",
I(_class="fa fa-chevron-circle-up",
_style="color: #0f0;")))
elif i[4] < 0:
tr.append(TD(diff, " ",
I(_class="fa fa-chevron-circle-down",
_style="color: #f00;")))
tbody.append(tr)
rank += 1
table.append(tbody)
switch = DIV(LABEL(H6("Friends' Submissions",
INPUT(_type="checkbox", _id="submission-switch"),
SPAN(_class="lever pink accent-3"),
"Global Submissions")),
_class="switch")
div = TAG[""](switch, table)
return dict(div=div,
heading=heading,
global_leaderboard=global_leaderboard)
FROM submission
WHERE user_id IS NOT NULL AND status = 'AC'
GROUP BY user_id;
"""
tmplist = db.executesql(sql)
for user in tmplist:
solved_count[user[0]] = user[1]
users = []
for user in registered_users:
try:
solved = solved_count[user.stopstalk_handle]
except KeyError:
solved = 0
tup = utilities.compute_row(user, solved, update_flag=True)
sql = sql.replace("user_id", "custom_user_id")
tmplist = db.executesql(sql)
for user in tmplist:
solved_count[user[0]] = user[1]
for user in custom_friends:
try:
if user.duplicate_cu:
orig_user = cftable(user.duplicate_cu)
solved = solved_count[orig_user.stopstalk_handle]
else:
solved = solved_count[user.stopstalk_handle]
except KeyError:
if user[0] not in complete_dict:
complete_dict[user[0]] = []
if complete_dict[user[0]] != []:
complete_dict[user[0]].append((user[1], user[2]))
else:
complete_dict[user[0]] = [(user[1], user[2])]
users = []
for user in registered_users:
try:
solved = solved_count[user.stopstalk_handle]
except KeyError:
solved = 0
tup = utilities.compute_row(user,
complete_dict,
solved,
update_flag=update_flag)
sql = sql.replace("user_id", "custom_user_id")
tmplist = db.executesql(sql)
for user in tmplist:
solved_count[user[0]] = user[1]
for user in custom_users:
try:
if user.duplicate_cu:
orig_user = cftable(user.duplicate_cu)
solved = solved_count[orig_user.stopstalk_handle]
else:
solved = solved_count[user.stopstalk_handle]
FROM submission
WHERE user_id IS NOT NULL AND status = 'AC'
GROUP BY user_id;
"""
tmplist = db.executesql(sql)
for user in tmplist:
solved_count[user[0]] = user[1]
users = []
for user in registered_users:
try:
solved = solved_count[user.stopstalk_handle]
except KeyError:
solved = 0
tup = utilities.compute_row(user, solved, update_flag=True)
sql = sql.replace("user_id", "custom_user_id")
tmplist = db.executesql(sql)
for user in tmplist:
solved_count[user[0]] = user[1]
for user in custom_friends:
try:
if user.duplicate_cu:
orig_user = cftable(user.duplicate_cu)
solved = solved_count[orig_user.stopstalk_handle]
else:
solved = solved_count[user.stopstalk_handle]
except KeyError: