def get(self, count):
response = {}
standings = get_standings(count=count)
team_ids = [team.account_id for team in standings]
solves = Solves.query.filter(Solves.account_id.in_(team_ids))
awards = Awards.query.filter(Awards.account_id.in_(team_ids))
freeze = get_config("freeze")
if freeze:
solves = solves.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
solves = solves.all()
awards = awards.all()
for i, team in enumerate(team_ids):
response[i + 1] = {
"id": standings[i].account_id,
"name": standings[i].name,
"solves": [],
}
for solve in solves:
if solve.account_id == team:
response[i + 1]["solves"].append(
{
"challenge_id": solve.challenge_id,
"account_id": solve.account_id,
"team_id": solve.team_id,
"user_id": solve.user_id,
"value": solve.challenge.value,
"date": isoformat(solve.date),
}
)
for award in awards:
if award.account_id == team:
response[i + 1]["solves"].append(
{
"challenge_id": None,
"account_id": award.account_id,
"team_id": award.team_id,
"user_id": award.user_id,
"value": award.value,
"date": isoformat(award.date),
}
)
response[i + 1]["solves"] = sorted(
response[i + 1]["solves"], key=lambda k: k["date"]
)
return {"success": True, "data": response}
def get_user_standings(count=None, admin=False):
scores = (db.session.query(
Solves.user_id.label("user_id"),
db.func.sum(Challenges.value).label("score"),
db.func.max(Solves.id).label("id"),
db.func.max(Solves.date).label("date"),
).join(Challenges).filter(Challenges.value != 0).group_by(Solves.user_id))
awards = (db.session.query(
Awards.user_id.label("user_id"),
db.func.sum(Awards.value).label("score"),
db.func.max(Awards.id).label("id"),
db.func.max(Awards.date).label("date"),
).filter(Awards.value != 0).group_by(Awards.user_id))
freeze = get_config("freeze")
if not admin and freeze:
scores = scores.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
results = union_all(scores, awards).alias("results")
sumscores = (db.session.query(
results.columns.user_id,
db.func.sum(results.columns.score).label("score"),
db.func.max(results.columns.id).label("id"),
db.func.max(results.columns.date).label("date"),
).group_by(results.columns.user_id).subquery())
if admin:
standings_query = (db.session.query(Users.id.label("user_id")).join(
sumscores, Users.id == sumscores.columns.user_id).order_by(
sumscores.columns.score.desc(), sumscores.columns.id))
else:
standings_query = (db.session.query(Users.id.label("user_id")).join(
sumscores, Users.id == sumscores.columns.user_id).filter(
Users.banned == False,
Users.hidden == False).order_by(sumscores.columns.score.desc(),
sumscores.columns.id))
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
return standings
def get(self, challenge_id):
if is_admin():
chal = Challenges.query.filter(
Challenges.id == challenge_id).first_or_404()
else:
chal = Challenges.query.filter(
Challenges.id == challenge_id,
and_(Challenges.state != "hidden",
Challenges.state != "locked"),
).first_or_404()
chal_class = get_chal_class(chal.type)
if chal.requirements:
requirements = chal.requirements.get("prerequisites", [])
anonymize = chal.requirements.get("anonymize")
if challenges_visible():
user = get_current_user()
if user:
solve_ids = (Solves.query.with_entities(
Solves.challenge_id).filter_by(
account_id=user.account_id).order_by(
Solves.challenge_id.asc()).all())
else:
# We need to handle the case where a user is viewing challenges anonymously
solve_ids = []
solve_ids = set([value for value, in solve_ids])
prereqs = set(requirements)
if solve_ids >= prereqs or is_admin():
pass
else:
if anonymize:
return {
"success": True,
"data": {
"id": chal.id,
"type": "hidden",
"name": "???",
"value": 0,
"category": "???",
"tags": [],
"template": "",
"script": "",
},
}
abort(403)
else:
abort(403)
tags = [
tag["value"]
for tag in TagSchema("user", many=True).dump(chal.tags).data
]
unlocked_hints = set()
hints = []
if authed():
user = get_current_user()
team = get_current_team()
# TODO: Convert this into a re-useable decorator
if is_admin():
pass
else:
if config.is_teams_mode() and team is None:
abort(403)
unlocked_hints = set([
u.target for u in HintUnlocks.query.filter_by(
type="hints", account_id=user.account_id)
])
files = []
for f in chal.files:
token = {
"user_id": user.id,
"team_id": team.id if team else None,
"file_id": f.id,
}
files.append(
url_for("views.files",
path=f.location,
token=serialize(token)))
else:
files = [
url_for("views.files", path=f.location) for f in chal.files
]
for hint in Hints.query.filter_by(challenge_id=chal.id).all():
if hint.id in unlocked_hints or ctf_ended():
hints.append({
"id": hint.id,
"cost": hint.cost,
"content": hint.content
})
else:
hints.append({"id": hint.id, "cost": hint.cost})
response = chal_class.read(challenge=chal)
Model = get_model()
if scores_visible() is True and accounts_visible() is True:
solves = Solves.query.join(Model,
Solves.account_id == Model.id).filter(
Solves.challenge_id == chal.id,
Model.banned == False,
Model.hidden == False,
)
# Only show solves that happened before freeze time if configured
freeze = get_config("freeze")
if not is_admin() and freeze:
solves = solves.filter(Solves.date < unix_time_to_utc(freeze))
solves = solves.count()
response["solves"] = solves
else:
response["solves"] = None
response["files"] = files
response["tags"] = tags
response["hints"] = hints
db.session.close()
return {"success": True, "data": response}
def get_standings(count=None, admin=False):
"""
Get standings as a list of tuples containing account_id, name, and score e.g. [(account_id, team_name, score)].
Ties are broken by who reached a given score first based on the solve ID. Two users can have the same score but one
user will have a solve ID that is before the others. That user will be considered the tie-winner.
Challenges & Awards with a value of zero are filtered out of the calculations to avoid incorrect tie breaks.
"""
Model = get_model()
scores = (db.session.query(
Solves.account_id.label("account_id"),
db.func.sum(Challenges.value).label("score"),
db.func.max(Solves.id).label("id"),
db.func.max(Solves.date).label("date"),
).join(Challenges).filter(Challenges.value != 0).group_by(
Solves.account_id))
awards = (db.session.query(
Awards.account_id.label("account_id"),
db.func.sum(Awards.value).label("score"),
db.func.max(Awards.id).label("id"),
db.func.max(Awards.date).label("date"),
).filter(Awards.value != 0).group_by(Awards.account_id))
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = get_config("freeze")
if not admin and freeze:
scores = scores.filter(Solves.date < unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
results = union_all(scores, awards).alias("results")
"""
Sum each of the results by the team id to get their score.
"""
sumscores = (db.session.query(
results.columns.account_id,
db.func.sum(results.columns.score).label("score"),
db.func.max(results.columns.id).label("id"),
db.func.max(results.columns.date).label("date"),
).group_by(results.columns.account_id).subquery())
"""
Admins can see scores for all users but the public cannot see banned users.
Filters out banned users.
Properly resolves value ties by ID.
Different databases treat time precision differently so resolve by the row ID instead.
"""
if admin:
standings_query = (db.session.query(
Model.id.label("account_id"),
Model.oauth_id.label("oauth_id"),
Model.name.label("name"),
Model.hidden,
Model.banned,
sumscores.columns.score,
).join(sumscores, Model.id == sumscores.columns.account_id).order_by(
sumscores.columns.score.desc(), sumscores.columns.id))
else:
standings_query = (db.session.query(
Model.id.label("account_id"),
Model.oauth_id.label("oauth_id"),
Model.name.label("name"),
sumscores.columns.score,
).join(sumscores, Model.id == sumscores.columns.account_id).filter(
Model.banned == False,
Model.hidden == False).order_by(sumscores.columns.score.desc(),
sumscores.columns.id))
"""
Only select a certain amount of users if asked.
"""
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
return standings