def get_team_standings(count=None, admin=False, fields=None):
if fields is None:
fields = []
scores = (db.session.query(
Solves.team_id.label("team_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.team_id))
awards = (db.session.query(
Awards.team_id.label("team_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.team_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.team_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.team_id).subquery())
if admin:
standings_query = (db.session.query(
Teams.id.label("team_id"),
Teams.oauth_id.label("oauth_id"),
Teams.name.label("name"),
Teams.hidden,
Teams.banned,
sumscores.columns.score,
*fields,
).join(sumscores, Teams.id == sumscores.columns.team_id).order_by(
sumscores.columns.score.desc(), sumscores.columns.id))
else:
standings_query = (db.session.query(
Teams.id.label("team_id"),
Teams.oauth_id.label("oauth_id"),
Teams.name.label("name"),
sumscores.columns.score,
*fields,
).join(sumscores, Teams.id == sumscores.columns.team_id).filter(
Teams.banned == False).filter(Teams.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, 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 _build_solves_query(extra_filters=(), admin_view=False):
# This can return None (unauth) if visibility is set to public
user = get_current_user()
# We only set a condition for matching user solves if there is a user and
# they have an account ID (user mode or in a team in teams mode)
if user is not None and user.account_id is not None:
user_solved_cond = Solves.account_id == user.account_id
else:
user_solved_cond = false()
# We have to filter solves to exclude any made after the current freeze
# time unless we're in an admin view as determined by the caller.
freeze = get_config("freeze")
if freeze and not admin_view:
freeze_cond = Solves.date < unix_time_to_utc(freeze)
else:
freeze_cond = true()
# Finally, we never count solves made by hidden or banned users/teams, even
# if we are an admin. This is to match the challenge detail API.
AccountModel = get_model()
exclude_solves_cond = and_(
AccountModel.banned == false(),
AccountModel.hidden == false(),
)
# This query counts the number of solves per challenge, as well as the sum
# of correct solves made by the current user per the condition above (which
# should probably only be 0 or 1!)
solves_q = (db.session.query(
Solves.challenge_id,
sa_func.count(Solves.challenge_id),
sa_func.sum(cast(user_solved_cond, sa_types.Integer)),
).join(AccountModel).filter(*extra_filters, freeze_cond,
exclude_solves_cond).group_by(
Solves.challenge_id))
return solves_q
def get_standings():
standings = scoreboard.get_standings()
# TODO faster lookup here
jstandings = []
for team in standings:
teamid = team[0]
solves = db.session.query(
Solves.challenge_id.label('challenge_id')).filter(
Solves.team_id == teamid)
freeze = utils.get_config('freeze')
if freeze:
freeze = unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
solves = solves.all()
jsolves = []
for solve in solves:
jsolves.append(solve.challenge_id)
jstandings.append({
'teamid': team[0],
'score': team[3],
'name': team[2],
'solves': jsolves
})
db.session.close()
return jstandings
def _build_solves_query(extra_filters=(), admin_view=False):
"""Returns queries and data that that are used for showing an account's solves.
It returns a tuple of
- SQLAlchemy query with (challenge_id, solve_count_for_challenge_id)
- Current user's solved challenge IDs
"""
# This can return None (unauth) if visibility is set to public
user = get_current_user()
# We only set a condition for matching user solves if there is a user and
# they have an account ID (user mode or in a team in teams mode)
AccountModel = get_model()
if user is not None and user.account_id is not None:
user_solved_cond = Solves.account_id == user.account_id
else:
user_solved_cond = false()
# We have to filter solves to exclude any made after the current freeze
# time unless we're in an admin view as determined by the caller.
freeze = get_config("freeze")
if freeze and not admin_view:
freeze_cond = Solves.date < unix_time_to_utc(freeze)
else:
freeze_cond = true()
# Finally, we never count solves made by hidden or banned users/teams, even
# if we are an admin. This is to match the challenge detail API.
exclude_solves_cond = and_(
AccountModel.banned == false(), AccountModel.hidden == false(),
)
# This query counts the number of solves per challenge, as well as the sum
# of correct solves made by the current user per the condition above (which
# should probably only be 0 or 1!)
solves_q = (
db.session.query(Solves.challenge_id, sa_func.count(Solves.challenge_id),)
.join(AccountModel)
.filter(*extra_filters, freeze_cond, exclude_solves_cond)
.group_by(Solves.challenge_id)
)
# Also gather the user's solve items which can be different from above query
# For example, even if we are a hidden user, we should see that we have solved a challenge
# however as a hidden user we are not included in the count of the above query
if admin_view:
# If we're an admin we should show all challenges as solved to break through any requirements
challenges = Challenges.query.all()
solve_ids = {challenge.id for challenge in challenges}
else:
# If not an admin we calculate solves as normal
solve_ids = (
Solves.query.with_entities(Solves.challenge_id)
.filter(user_solved_cond)
.all()
)
solve_ids = {value for value, in solve_ids}
return solves_q, solve_ids
def get_scores(admin=False):
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())
return sumscores
def format_variables(content):
ctf_name = get_config("ctf_name")
ctf_description = get_config("ctf_description")
ctf_start = get_config("start")
if ctf_start:
ctf_start = isoformat(unix_time_to_utc(int(ctf_start)))
ctf_end = get_config("end")
if ctf_end:
ctf_end = isoformat(unix_time_to_utc(int(ctf_end)))
ctf_freeze = get_config("freeze")
if ctf_freeze:
ctf_freeze = isoformat(unix_time_to_utc(int(ctf_freeze)))
content = safe_format(
content,
ctf_name=ctf_name,
ctf_description=ctf_description,
ctf_start=ctf_start,
ctf_end=ctf_end,
ctf_freeze=ctf_freeze,
)
return content
def scoreslist(count=10):
json = {"success": True, "data": {}}
if scores_visible() and not authed():
return redirect(url_for('auth.login', next=request.path))
if not scores_visible():
return jsonify(json)
standings = get_standings()
for i, x in enumerate(standings[:10]):
solves = (db.session.query(
Solves.challenge_id, Challenges.value, Solves.date).join(
Challenges, Solves.challenge_id == Challenges.id).filter(
Challenges.category.in_(
x['cat'])).filter(Solves.team_id == x['teamid']))
freeze = utils.get_config('freeze')
if freeze:
freeze = unix_time_to_utc(freeze)
if x['teamid'] != session.get('id'):
solves = solves.filter(Solves.date < freeze)
solves = solves.all()
#print(x['teamid'],'Stat Solve',solves)
sol = []
for s in solves:
sol.append({
'account_id': x['teamid'],
'challenge_id': s[0],
'date': s[2],
'team_id': x['teamid'],
'user_id': x['teamid'],
'value': s[1]
})
sol = sorted(sol, key=lambda i: i["date"])
json['data'].update({
str(i + 1): {
'id': x['teamid'],
'name': escape(x['name']),
'solves': sol
}
})
return jsonify(json)
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.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.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
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(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))
hints_name_list = db.session.query(
db.func.concat("Hint ", Hints.id).label("hints_name")).count()
if hints_name_list > 0:
hints_name = db.func.concat("Hint ", Hints.id).label("hints_name")
awards = db.session.query(
Awards.account_id.label('account_id'),
Awards.team_id.label('team_id'),
Awards.user_id.label('user_id'),
Awards.value.label('value'),
Awards.date.label('date'),
) \
.join(Hints, Awards.name == hints_name) \
.join(Solves, (Awards.account_id == Solves.account_id) & (Hints.challenge_id == Solves.challenge_id)) \
.filter(Awards.value != 0) \
.filter(Awards.account_id.in_(team_ids))
awards_by_admin = db.session.query(
Awards.account_id.label('account_id'),
Awards.team_id.label('team_id'),
Awards.user_id.label('user_id'),
Awards.value.label('value'),
Awards.date.label('date'),
) \
.filter(Awards.account_id.in_(team_ids)) \
.filter(Awards.value > 0)
awards = awards.union(awards_by_admin)
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(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(self, count):
mode = get_config("user_mode")
team_ids = session.get('teams_watching')
if team_ids == None:
team_ids = []
response = {
"places_matched": {},
"places_unmatched": {},
"places_custom": {}
}
if is_admin():
matched_standings = get_matched_standings(admin=True, count=count)
unmatched_standings = get_unmatched_standings(admin=True,
count=count)
custom_standings = get_custom_standings(team_ids=team_ids,
admin=True,
count=count)
else:
matched_standings = get_matched_standings(count=count)
unmatched_standings = get_unmatched_standings(count=count)
custom_standings = get_custom_standings(team_ids=team_ids,
count=count)
matched_team_ids = [team.account_id for team in matched_standings]
unmatched_team_ids = [team.account_id for team in unmatched_standings]
custom_team_ids = [team.account_id for team in custom_standings]
matched_solves = Solves.query.filter(
Solves.account_id.in_(matched_team_ids))
matched_awards = Awards.query.filter(
Awards.account_id.in_(matched_team_ids))
unmatched_solves = Solves.query.filter(
Solves.account_id.in_(unmatched_team_ids))
unmatched_awards = Awards.query.filter(
Awards.account_id.in_(unmatched_team_ids))
custom_solves = Solves.query.filter(
Solves.account_id.in_(custom_team_ids))
custom_awards = Awards.query.filter(
Awards.account_id.in_(custom_team_ids))
freeze = get_config('freeze')
if freeze:
matched_solves = matched_solves.filter(
Solves.date < unix_time_to_utc(freeze))
matched_awards = matched_awards.filter(
Awards.date < unix_time_to_utc(freeze))
unmatched_solves = unmatched_solves.filter(
Solves.date < unix_time_to_utc(freeze))
unmatched_awards = unmatched_awards.filter(
Awards.date < unix_time_to_utc(freeze))
custom_solves = custom_solves.filter(
Solves.date < unix_time_to_utc(freeze))
custom_awards = custom_awards.filter(
Awards.date < unix_time_to_utc(freeze))
matched_solves = matched_solves.all()
matched_awards = matched_awards.all()
unmatched_solves = unmatched_solves.all()
unmatched_awards = unmatched_awards.all()
custom_solves = custom_solves.all()
custom_awards = custom_awards.all()
for i, team in enumerate(matched_team_ids):
response['places_matched'][i + 1] = {
'id': matched_standings[i].account_id,
'name': matched_standings[i].name,
'solves': []
}
for solve in matched_solves:
if solve.account_id == team:
response['places_matched'][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 matched_awards:
if award.account_id == team:
response['places_matched'][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['places_matched'][i + 1]['solves'] = sorted(
response['places_matched'][i + 1]['solves'],
key=lambda k: k['date'])
for i, team in enumerate(unmatched_team_ids):
response['places_unmatched'][i + 1] = {
'id': unmatched_standings[i].account_id,
'name': unmatched_standings[i].name,
'solves': []
}
for solve in unmatched_solves:
if solve.account_id == team:
response['places_unmatched'][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 unmatched_awards:
if award.account_id == team:
response['places_unmatched'][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['places_unmatched'][i + 1]['solves'] = sorted(
response['places_unmatched'][i + 1]['solves'],
key=lambda k: k['date'])
for i, team in enumerate(custom_team_ids):
response['places_custom'][i + 1] = {
'id': custom_standings[i].account_id,
'name': custom_standings[i].name,
'solves': []
}
for solve in custom_solves:
if solve.account_id == team:
response['places_custom'][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 custom_awards:
if award.account_id == team:
response['places_custom'][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['places_custom'][i + 1]['solves'] = sorted(
response['places_custom'][i + 1]['solves'],
key=lambda k: k['date'])
return {'success': True, 'data': response}
def get_standings():
standings = glowworm_get_standings()
# TODO faster lookup here
jstandings = []
print(standings)
for team in standings:
mode = utils.get_config("user_mode")
if mode == "teams":
teamid = team[0]
basic_solves = db.session.query(
Solves.challenge_id.label('chalid'),
Solves.date.label('date')).filter(Solves.team_id == teamid)
else:
teamid = team[0]
basic_solves = db.session.query(
Solves.challenge_id.label('chalid'),
Solves.date.label('date')).filter(Solves.user_id == teamid)
freeze = utils.get_config('freeze')
if freeze:
freeze = unix_time_to_utc(freeze)
if teamid != session.get('id'):
basic_solves = basic_solves.filter(Solves.date < freeze)
basic_solves = basic_solves.all()
jsolves = []
score = 0 + team[3]
# basic challenge
# 1 first blood
# 2 second blood
# 3 third blood
for solve in basic_solves:
cvalue = Challenges.query.filter_by(
id=solve.chalid).first().value
top = Solves.query.filter_by(challenge_id=solve.chalid,
type='correct').order_by(
Solves.date.asc()).all()
if (solve.date == top[0].date):
solve = str(solve.chalid) + "-1"
score = score + int(cvalue * 1.3)
elif (solve.date == top[1].date):
solve = str(solve.chalid) + "-2"
score = score + int(cvalue * 1.2)
elif (solve.date == top[2].date):
solve = str(solve.chalid) + "-3"
score = score + int(cvalue * 1.1)
else:
solve = str(solve.chalid) + "-0"
score = score + int(cvalue * 1)
jsolves.append(solve)
# ada challenge
# 4 safe
# 5 hacked
try:
from CTFd.plugins.ctfd_glowworm.models import GlowwormAttacks, ADAChallenge
from CTFd.plugins.ctfd_glowworm.extensions import get_round
all_challenges = ADAChallenge.query.all()
for challenge in all_challenges:
envname = challenge.dirname.split('/')[1]
log = GlowwormAttacks.query.filter_by(
round=get_round(), victim_id=teamid,
envname=envname).first()
if log == None:
solve = str(challenge.id) + "-4"
pass
elif envname == log.envname:
solve = str(challenge.id) + "-5"
pass
jsolves.append(solve)
except Exception as e:
print(e)
if mode == "teams":
jstandings.append({
'userid': "",
'teamid': team[0],
'score': int(score),
'name': team[2],
'solves': jsolves
})
else:
jstandings.append({
'userid': team[0],
'teamid': "",
'score': int(score),
'name': team[2],
'solves': jsolves
})
# 重新按分数排序
jstandings.sort(key=lambda x: x["score"], reverse=True)
db.session.close()
return jstandings
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()
# Build a mapping of accounts to their solves and awards
solves_mapper = defaultdict(list)
for solve in solves:
solves_mapper[solve.account_id].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:
solves_mapper[award.account_id].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),
})
# Sort all solves by date
for team_id in solves_mapper:
solves_mapper[team_id] = sorted(solves_mapper[team_id],
key=lambda k: k["date"])
for i, team in enumerate(team_ids):
response[i + 1] = {
"id": standings[i].account_id,
"name": standings[i].name,
"solves": solves_mapper.get(standings[i].account_id, []),
}
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'),
db.func.concat("0", "").cast(db.Integer).label('unlock_count'),
db.func.count(Solves.id).label('solve'),
).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'),
db.func.concat("0", "").cast(db.Integer).label('unlock_count'),
db.func.concat("0", "").cast(db.Integer).label('solve')
) \
.filter(Awards.value != 0) \
.group_by(Awards.account_id)
hints_name_list = db.session.query(
db.func.concat("Hint ", Hints.id).label("hints_name")).count()
if hints_name_list > 0:
hints_name = db.func.concat("Hint ", Hints.id).label("hints_name")
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'),
db.func.count(Awards.value < 0).label('unlock_count'),
db.func.concat("0", "").cast(db.Integer).label('solve')
) \
.join(Hints, Awards.name == hints_name) \
.join(Solves, (Awards.account_id == Solves.account_id) & (Hints.challenge_id == Solves.challenge_id)) \
.filter(Awards.value != 0) \
.group_by(Awards.account_id)
awards_by_admin = 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'),
db.func.concat("0", "").cast(db.Integer).label('unlock_count'),
db.func.concat("0", "").cast(db.Integer).label('solve')
) \
.filter(Awards.value > 0) \
.group_by(Awards.account_id)
awards = awards.union(awards_by_admin)
"""
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'),
db.func.max(results.columns.unlock_count).label('unlock_count'),
db.func.sum(results.columns.solve).label('solve'),
).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.unlock_count.asc(), sumscores.columns.date.asc(), 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,
sumscores.columns.solve,
) \
.join(sumscores, Model.id == sumscores.columns.account_id) \
.filter(Model.banned == False, Model.hidden == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.unlock_count.asc(), sumscores.columns.date.asc(), 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()
db.session.close()
return standings
def get_standings():
standings = scoreboard.get_standings()
# TODO faster lookup here
jstandings = []
for team in standings:
teamid = team[0]
solves = (db.session.query(
Solves.challenge_id, Challenges.category,
db.func.sum(Challenges.value), db.func.max(Solves.date)).join(
Challenges, Solves.challenge_id == Challenges.id).group_by(
Challenges.category).filter(
Solves.team_id == teamid).filter(
Challenges.value != 1).filter(
Challenges.value != 0))
challenge = (db.session.query(Challenges.category,
db.func.sum(
Challenges.value)).group_by(
Challenges.category)).all()
#print(team[2])
chal_details = {}
for i in challenge:
chal_details.update({i[0]: i[1]})
freeze = utils.get_config('freeze')
if freeze:
freeze = unix_time_to_utc(freeze)
if teamid != session.get('id'):
#print(session.get('id'),teamid,freeze)
solves = solves.filter(Solves.date < freeze)
solves = solves.all()
score = []
cat = get_challenges()["cat"]
for i in solves:
score.append({
"id": i[0],
"score": i[2],
"cat": i[1],
"date": i[3]
})
for i in cat:
if i not in [j["cat"] for j in score]:
#score.append({"score":0,"cat":i,"date":datetime.datetime.utcfromtimestamp(111111111111)})
score.append({
"score": 0,
"cat": i,
"date": None,
"id": -1
})
score = sorted(score, key=lambda i: i["cat"])
maxscore = 0
temp = []
catfil = []
count = 0
for c, i in enumerate(score):
if chal_details[i['cat']] == i['score']:
temp.append(i)
catfil.append(i['cat'])
maxscore += i['score']
count += 1
if maxscore == 0:
maxscore = {i["date"]: i["score"] for i in score}
date = max(maxscore, key=maxscore.get)
maxscore = maxscore[date]
cat = {i["cat"]: i["score"] for i in score}
cat = max(cat, key=cat.get)
catfil.append(cat)
else:
date = sorted(temp, key=lambda i: i['date'],
reverse=True)[0]['date']
if date == None:
continue
# Check for the cat with the least date if there are multiple max values
jstandings.append({
'teamid': team[0],
'cat': catfil,
'solves': score,
'name': escape(team[2]),
'date': date,
'state': count,
'score': maxscore
})
jstandings = sorted(jstandings, key=lambda i: i["date"])
#for i in jstandings:
# print(teamid,i['date'],i['score'])
jstandings = sorted(jstandings,
key=lambda i: i["score"],
reverse=True)
jstandings = sorted(jstandings,
key=lambda i: i["state"],
reverse=True)
#print('next sort')
#for i in jstandings:
# print(i['date'],i['score'])
db.session.close()
return jstandings
