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]