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