def topteams(count):
json = {'places': {}}
if utils.get_config('view_scoreboard_if_authed') and not utils.authed():
return redirect(url_for('auth.login', next=request.path))
if utils.hide_scores():
return jsonify(json)
if count > 20 or count < 0:
count = 10
standings = get_standings(count=count)
for i, team in enumerate(standings):
solves = Solves.query.filter_by(teamid=team.teamid)
awards = Awards.query.filter_by(teamid=team.teamid)
freeze = utils.get_config('freeze')
if freeze:
solves = solves.filter(
Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(
Awards.date < utils.unix_time_to_utc(freeze))
solves = solves.all()
awards = awards.all()
json['places'][i + 1] = {
'id': team.teamid,
'name': team.name,
'solves': []
}
for x in solves:
json['places'][i + 1]['solves'].append({
'chal':
x.chalid,
'team':
x.teamid,
'value':
x.chal.value,
'time':
utils.unix_time(x.date)
})
for award in awards:
json['places'][i + 1]['solves'].append({
'chal':
None,
'team':
award.teamid,
'value':
award.value,
'time':
utils.unix_time(award.date)
})
json['places'][i + 1]['solves'] = sorted(json['places'][i +
1]['solves'],
key=lambda k: k['time'])
return jsonify(json)
def solves_public(teamid=None):
solves = None
awards = None
if utils.authed() and session['id'] == teamid:
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
freeze = utils.get_config('freeze')
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
elif utils.hide_scores():
# Use empty values to hide scores
solves = []
awards = []
else:
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
freeze = utils.get_config('freeze')
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
db.session.close()
response = {'solves': []}
for solve in solves:
response['solves'].append({
'chal': solve.chal.name,
'chalid': solve.chalid,
'team': solve.teamid,
'value': solve.chal.value,
'category': solve.chal.category,
'time': utils.unix_time(solve.date)
})
if awards:
for award in awards:
response['solves'].append({
'chal': award.name,
'chalid': None,
'team': award.teamid,
'value': award.value,
'category': award.category or "Award",
'time': utils.unix_time(award.date)
})
response['solves'].sort(key=lambda k: k['time'])
return jsonify(response)
def get_standings(admin=False, count=None, contestid=None):
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
awards = db.session.query(Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.date).label('date')).group_by(
Awards.teamid)
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
if contestid is not None:
scores = scores.filter(Challenges.contestid == contestid)
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(
results.columns.teamid,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.date).label('date')).group_by(
results.columns.teamid).subquery()
if admin:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
if count is None:
standings = standings_query.all()
else:
standings = standings_query.limit(count).all()
db.session.close()
return standings
def topteams_endpoint(count):
json = {'places': {}}
if utils.get_config('view_scoreboard_if_authed') and not utils.authed():
return redirect(url_for('auth.login', next=request.path))
if utils.hide_scores():
return jsonify(json)
if count > 20 or count < 0:
count = 10
standings = get_standings_monkey_patch(count=count)
team_ids = [team.teamid for team in standings]
solves = Solves.query.filter(Solves.teamid.in_(team_ids))
awards = Awards.query.filter(Awards.teamid.in_(team_ids))
freeze = utils.get_config('freeze')
if freeze:
solves = solves.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
solves = solves.all()
awards = awards.all()
time_decay_solves = TimeDecaySolves.query.filter(Solves.teamid.in_(team_ids)).all()
for i, team in enumerate(team_ids):
json['places'][i + 1] = {
'id': standings[i].teamid,
'name': standings[i].name,
'solves': []
}
for solve in solves:
if solve.teamid == team:
score = 0
for t in time_decay_solves:
if t.chalid == solve.chalid and t.teamid == solve.teamid:
score = t.decayed_value
json['places'][i + 1]['solves'].append({
'chal': solve.chalid,
'team': solve.teamid,
'value': score,
'time': utils.unix_time(solve.date)
})
for award in awards:
if award.teamid == team:
json['places'][i + 1]['solves'].append({
'chal': None,
'team': award.teamid,
'value': award.value,
'time': utils.unix_time(award.date)
})
json['places'][i + 1]['solves'] = sorted(json['places'][i + 1]['solves'], key=lambda k: k['time'])
return jsonify(json)
def private_team():
if utils.authed():
teamid = session['id']
freeze = utils.get_config('freeze')
user = Teams.query.filter_by(id=teamid).first_or_404()
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
place = user.place()
score = user.score()
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
return render_template('team.html',
solves=solves,
awards=awards,
team=user,
score=score,
place=place,
score_frozen=utils.is_scoreboard_frozen())
else:
return redirect(url_for('auth.login'))
def get_standings():
standings = scoreboard.get_standings()
# TODO faster lookup here
jstandings = []
for team in standings:
teamid = team[0]
solves = db.session.query(
Solves.chalid.label('chalid')).filter(Solves.teamid == teamid)
freeze = utils.get_config('freeze')
if freeze:
freeze = utils.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.chalid)
jstandings.append({
'teamid': team.teamid,
'score': team.score,
'name': team.name,
'solves': jsolves
})
db.session.close()
return jstandings
def team(teamid):
if utils.get_config('workshop_mode'):
abort(404)
if utils.get_config('view_scoreboard_if_utils.authed') and not utils.authed():
return redirect(url_for('auth.login', next=request.path))
infos = []
errors = []
if utils.ctf_paused():
infos.append('{} is paused'.format(utils.ctf_name()))
if not utils.ctftime():
# It is not CTF time
if utils.view_after_ctf(): # But we are allowed to view after the CTF ends
pass
else: # We are NOT allowed to view after the CTF ends
if utils.get_config('start') and not utils.ctf_started():
errors.append('{} has not started yet'.format(utils.ctf_name()))
if (utils.get_config('end') and utils.ctf_ended()) and not utils.view_after_ctf():
errors.append('{} has ended'.format(utils.ctf_name()))
freeze = utils.get_config('freeze')
user = Teams.query.filter_by(id=teamid).first_or_404()
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
place = user.place()
score = user.score()
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
db.session.close()
if utils.hide_scores() and teamid != session.get('id'):
errors.append('Scores are currently hidden')
else:
# banned is a synonym for hidden :/
if not utils.is_admin() and (user.admin or user.banned):
errors.append('Scores are currently hidden')
if errors:
return render_template('team.html', team=user, infos=infos, errors=errors)
if request.method == 'GET':
return render_template('team.html', solves=solves, awards=awards, team=user, score=score, place=place, score_frozen=utils.is_scoreboard_frozen(), infos=infos)
elif request.method == 'POST':
json = {'solves': []}
for x in solves:
json['solves'].append({'id': x.id, 'chal': x.chalid, 'team': x.teamid})
return jsonify(json)
def solves(teamid=None):
solves = None
awards = None
if teamid is None:
if utils.is_admin():
solves = Solves.query.filter_by(teamid=session['id']).all()
elif utils.user_can_view_challenges():
if utils.authed():
solves = Solves.query.join(Teams,
Solves.teamid == Teams.id).filter(
Solves.teamid == session['id'],
Teams.banned == False).all()
else:
return jsonify({'solves': []})
else:
return redirect(url_for('auth.login', next='solves'))
else:
if utils.hide_scores():
# Use empty values to hide scores
solves = []
awards = []
else:
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
freeze = utils.get_config('freeze')
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
db.session.close()
json = {'solves': []}
for solve in solves:
json['solves'].append({
'chal': solve.chal.name,
'chalid': solve.chalid,
'team': solve.teamid,
'value': solve.chal.value,
'category': solve.chal.category,
'time': utils.unix_time(solve.date)
})
if awards:
for award in awards:
json['solves'].append({
'chal': award.name,
'chalid': None,
'team': award.teamid,
'value': award.value,
'category': award.category or "Award",
'time': utils.unix_time(award.date)
})
json['solves'].sort(key=lambda k: k['time'])
return jsonify(json)
def team(teamid):
if utils.get_config(
'view_scoreboard_if_utils.authed') and not utils.authed():
return redirect(url_for('auth.login', next=request.path))
errors = []
freeze = utils.get_config('freeze')
user = Teams.query.filter_by(id=teamid).first_or_404()
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
points = Gamble.query.filter_by(teamid=teamid).all()
gamble = 0
for point in points:
gamble += point.value
place = user.place()
score = user.score()
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
db.session.close()
if utils.hide_scores() and teamid != session.get('id'):
errors.append('Scores are currently hidden')
if errors:
return render_template('team.html', team=user, errors=errors)
if request.method == 'GET':
return render_template('team.html',
solves=solves,
awards=awards,
gamble=gamble,
team=user,
score=score,
place=place,
score_frozen=utils.is_scoreboard_frozen())
elif request.method == 'POST':
json = {'solves': []}
for x in solves:
json['solves'].append({
'id': x.id,
'chal': x.chalid,
'team': x.teamid
})
return jsonify(json)
def team_solves(compid, teamid):
json = {'solves': []}
if utils.get_config('view_scoreboard_if_authed') and not utils.authed():
return redirect(url_for('auth.login', next=request.path))
if utils.hide_scores():
return jsonify(json)
chalids = [
chal.chalid
for chal in Chalcomp.query.filter(Chalcomp.compid == compid)
]
solves = Solves.query.filter(Solves.teamid == teamid)
awards = Awards.query.filter(Awards.teamid == teamid)
solves = solves.filter(Solves.chalid.in_(chalids))
freeze = utils.get_config('freeze')
if freeze:
solves = solves.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
solves = solves.all()
awards = awards.all()
for solve in solves:
json['solves'].append({
'chal': solve.chalid,
'team': solve.teamid,
'value': solve.chal.value,
'time': utils.unix_time(solve.date)
})
for award in awards:
json['solves'].append({
'chal': None,
'team': award.teamid,
'value': award.value,
'time': utils.unix_time(award.date)
})
json['solves'] = sorted(json['solves'], key=lambda k: k['time'])
return jsonify(json)
def get_standings():
standings = scoreboard.get_standings()
# TODO faster lookup here
jstandings = []
# print(standings)
for team in standings:
mode = utils.get_config("user_mode")
if mode == "teams":
teamid = Users.query.filter_by(id=team[0]).first_or_404().team_id
solves = db.session.query(Solves.challenge_id.label('chalid'), Solves.date.label('date')).filter(
Solves.team_id == teamid)
else:
teamid = team[0]
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 = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
solves = solves.all()
jsolves = []
score = 0
for solve in 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)
if mode == "teams":
jstandings.append({'userid':"", 'teamid':team[0], 'score':score, 'name':team[2],'solves':jsolves})
else:
jstandings.append({'userid':team[0], 'teamid':"", 'score':score, 'name':team[2],'solves':jsolves})
# 重新按分数排序
jstandings.sort(key=lambda x: x["score"], reverse=True)
db.session.close()
return jstandings
def solves(teamid=None):
solves = None
awards = None
if teamid is None:
if utils.is_admin():
solves = Solves.query.filter_by(teamid=session['id']).all()
elif utils.user_can_view_challenges():
if utils.authed():
solves = Solves.query.join(Teams, Solves.teamid == Teams.id).filter(Solves.teamid == session['id'], Teams.banned == False).all()
else:
return jsonify({'solves': []})
else:
return redirect(url_for('auth.login', next='solves'))
else:
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
freeze = utils.get_config('freeze')
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
db.session.close()
json = {'solves': []}
for solve in solves:
json['solves'].append({
'chal': solve.chal.name,
'chalid': solve.chalid,
'team': solve.teamid,
'value': solve.chal.value,
'category': solve.chal.category,
'time': utils.unix_time(solve.date)
})
if awards:
for award in awards:
json['solves'].append({
'chal': award.name,
'chalid': None,
'team': award.teamid,
'value': award.value,
'category': award.category or "Award",
'time': utils.unix_time(award.date)
})
json['solves'].sort(key=lambda k: k['time'])
return jsonify(json)
def team_endpoint(teamid):
if utils.get_config('workshop_mode'):
abort(404)
if utils.get_config('view_scoreboard_if_utils.authed') and not utils.authed():
return redirect(url_for('auth.login', next=request.path))
errors = []
freeze = utils.get_config('freeze')
user = Teams.query.filter_by(id=teamid).first_or_404()
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
place = user.place()
score = user.score()
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
time_decay_solves = TimeDecaySolves.query.filter_by(teamid=teamid).all()
db.session.close()
if utils.hide_scores() and teamid != session.get('id'):
errors.append('Scores are currently hidden')
if errors:
return render_template('team.html', team=user, errors=errors)
# pass decayed_value
for s in solves:
for t in time_decay_solves:
if t.chalid == s.chalid:
s.decayed_value = t.decayed_value
if request.method == 'GET':
return render_template('team.html', solves=solves, awards=awards, team=user, score=score, place=place, score_frozen=utils.is_scoreboard_frozen())
elif request.method == 'POST':
json = {'solves': []}
for x in solves:
json['solves'].append({'id': x.id, 'chal': x.chalid, 'team': x.teamid, 'decayed_value': TimeDecayChallenge.value_for_team(x.chalid, x.teamid)})
return jsonify(json)
def team(teamid):
if utils.get_config('view_scoreboard_if_utils.authed') and not utils.authed():
return redirect(url_for('auth.login', next=request.path))
errors = []
freeze = utils.get_config('freeze')
user = Teams.query.filter_by(id=teamid).first_or_404()
solves = Solves.query.filter_by(teamid=teamid)
awards = Awards.query.filter_by(teamid=teamid)
place = user.place()
score = user.score()
if freeze:
freeze = utils.unix_time_to_utc(freeze)
if teamid != session.get('id'):
solves = solves.filter(Solves.date < freeze)
awards = awards.filter(Awards.date < freeze)
solves = solves.all()
awards = awards.all()
db.session.close()
if utils.hide_scores() and teamid != session.get('id'):
errors.append('Scores are currently hidden')
if errors:
return render_template('team.html', team=user, errors=errors)
if request.method == 'GET':
return render_template('team.html', solves=solves, awards=awards, team=user, score=score, place=place, score_frozen=utils.is_scoreboard_frozen())
elif request.method == 'POST':
json = {'solves': []}
for x in solves:
json['solves'].append({'id': x.id, 'chal': x.chalid, 'team': x.teamid})
return jsonify(json)
def get_standings(admin=False, count=None, classification=None):
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
awards = db.session.query(
Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.date).label('date')).group_by(Awards.teamid)
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(
Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(
Awards.date < utils.unix_time_to_utc(freeze))
results = union_all(scores, awards).alias('results')
sumscores = db.session.query(
results.columns.teamid,
db.func.sum(results.columns.score).label('score'),
db.func.max(results.columns.date).label('date')).group_by(
results.columns.teamid).subquery()
if admin:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score,
Classification.classification
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.join(Classification, Teams.id == Classification.id) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score,
Classification.classification
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.join(Classification, Teams.id == Classification.id) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.date)
if classification and count:
# -=- For TAMUctf, but can be left in without any problems -=-
try:
tamu_test()
c = Classification
if (classification == "tamu"):
standings = standings_query.filter(
or_(c.classification == "U0", c.classification == "U1",
c.classification == "U2", c.classification == "U3",
c.classification == "U4", c.classification == "U5",
c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).limit(count).all()
elif (classification == "tamug"):
standings = standings_query.filter(
or_(c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).limit(count).all()
elif (classification == "tamuu"):
standings = standings_query.filter(
or_(c.classification == "U0", c.classification == "U1",
c.classification == "U2", c.classification == "U3",
c.classification == "U4",
c.classification == "U5")).limit(count).all()
elif (classification == "U4"):
standings = standings_query.filter(
or_(c.classification == "U4",
c.classification == "U5")).limit(count).all()
elif (classification == "tamum"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 5, c.other == 7,
c.other == 8, c.other == 12, c.other == 10,
c.other == 15)).limit(count).all()
elif (classification == "tamumc"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 8, c.other == 10,
c.other == 15)).limit(count).all()
elif (classification == "tamumr"):
standings = standings_query.filter(
or_(c.other == 5, c.other == 8, c.other == 12,
c.other == 15)).limit(count).all()
elif (classification == "tamumd"):
standings = standings_query.filter(
or_(c.other == 7, c.other == 12, c.other == 10,
c.other == 15)).limit(count).all()
else:
standings = standings_query.filter(
Classification.classification == classification).limit(
count).all()
except:
standings = standings_query.filter(
Classification.classification == classification).limit(
count).all()
#-=-
elif classification:
# -=- For TAMUctf, but can be left in without any problems -=-
try:
tamu_test()
c = Classification
if (classification == "tamu"):
standings = standings_query.filter(
or_(c.classification == "U0", c.classification == "U1",
c.classification == "U2", c.classification == "U3",
c.classification == "U4", c.classification == "U5",
c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).all()
elif (classification == "tamug"):
standings = standings_query.filter(
or_(c.classification == "G5", c.classification == "G6",
c.classification == "G7", c.classification == "G8",
c.classification == "G9")).all()
elif (classification == "tamuu"):
standings = standings_query.filter(
or_(c.classification == "U01",
c.classification == "U1", c.classification == "U2",
c.classification == "U3", c.classification == "U4",
c.classification == "U5")).all()
elif (classification == "tamuu"):
standings = standings_query.filter(
or_(c.classification == "U4",
c.classification == "U5")).all()
elif (classification == "tamum"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 5, c.other == 7,
c.other == 8, c.other == 12, c.other == 10,
c.other == 15)).all()
elif (classification == "tamumc"):
standings = standings_query.filter(
or_(c.other == 3, c.other == 8, c.other == 10,
c.other == 15)).all()
elif (classification == "tamumr"):
standings = standings_query.filter(
or_(c.other == 5, c.other == 8, c.other == 12,
c.other == 15)).all()
elif (classification == "tamumd"):
standings = standings_query.filter(
or_(c.other == 7, c.other == 12, c.other == 10,
c.other == 15)).all()
else:
standings = standings_query.filter(
Classification.classification == classification).all()
except:
standings = standings_query.filter(
Classification.classification == classification).all()
#-=-
elif count:
standings = standings_query.limit(count).all()
else:
standings = standings_query.all()
return standings
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 = utils.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_range(comp, admin=False, count=None, teamid=None):
Comp = Competitions.query.filter(Competitions.id == comp).first()
if not Comp:
return []
scores = db.session.query(Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date')).join(
Challenges).join(Chalcomp).group_by(
Solves.teamid)
awards = db.session.query(Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date')).group_by(
Awards.teamid)
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = utils.get_config('freeze')
chals = [x.chalid for x in Comp.chals]
awardTitles = [u"Hint for {}".format(x.chal.name) for x in Comp.chals]
print awardTitles
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.unix_time_to_utc(freeze))
"""
Combine awards and solves with a union. They should have the same amount of columns
"""
scores = scores.filter(Chalcomp.compid == Comp.id)
scores = scores.filter(Solves.date < Comp.endTime)
scores = scores.filter(Solves.date > Comp.startTime)
awards = awards.filter(Awards.date < Comp.endTime)
awards = awards.filter(Awards.date > Comp.startTime)
awards = awards.filter(Awards.name.in_(awardTitles))
#awards=scores.filter(Solves.chalid in chals)
results = union_all(scores, awards).alias('results')
if (teamid is not None):
scores = scores.filter(Solves.teamid == teamid)
awards = awards.filter(Solves.teamid == teamid)
"""
Sum each of the results by the team id to get their score.
"""
sumscores = db.session.query(
results.columns.teamid,
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.teamid).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(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == False) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
#print standings_query
"""
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(admin=False, count=None):
scores = db.session.query(
Solves.teamid.label('teamid'),
db.func.sum(Challenges.value).label('score'),
db.func.max(Solves.id).label('id'),
db.func.max(Solves.date).label('date')).join(Challenges).group_by(
Solves.teamid)
awards = db.session.query(Awards.teamid.label('teamid'),
db.func.sum(Awards.value).label('score'),
db.func.max(Awards.id).label('id'),
db.func.max(Awards.date).label('date')).group_by(
Awards.teamid)
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.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.teamid,
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.teamid).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(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == 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()
db.session.close()
return standings
def get_standings(admin=False, count=None):
"""
Get team standings as a list of tuples containing team_id, team_name, and score e.g. [(team_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.
"""
scores = db.session.query(
Solves.teamid.label('teamid'),
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.teamid)
awards = db.session.query(
Awards.teamid.label('teamid'),
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.teamid)
"""
Filter out solves and awards that are before a specific time point.
"""
freeze = utils.get_config('freeze')
if not admin and freeze:
scores = scores.filter(Solves.date < utils.unix_time_to_utc(freeze))
awards = awards.filter(Awards.date < utils.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.teamid,
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.teamid)\
.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(
Teams.id.label('teamid'),
Teams.name.label('name'),
Teams.banned, sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.order_by(sumscores.columns.score.desc(), sumscores.columns.id)
else:
standings_query = db.session.query(
Teams.id.label('teamid'),
Teams.name.label('name'),
sumscores.columns.score
)\
.join(sumscores, Teams.id == sumscores.columns.teamid) \
.filter(Teams.banned == 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()
db.session.close()
return standings
