async def info(
self,
ctx,
season: Optional[int] = CURRENT_SEASON,
):
"""Get info about your solved and unsolved challenges."""
solved_challenges = (db.select([
CompletedChallenge.challenge_id
]).where(CompletedChallenge.discord_id == ctx.author.id).where(
CompletedChallenge.season == season).cte("solved_challenges"))
solved = (sa.exists().where(
solved_challenges.c.challenge_id == Challenge.id).label("solved"))
score = sa.func.sum(Challenge.points).label("score")
scores_q = (db.select([User.discord_id, score]).select_from(
User.outerjoin(CompletedChallenge).outerjoin(Challenge)).where(
CompletedChallenge.season == season).group_by(
User.discord_id).cte("scores"))
my_score = (db.select([
sa.func.coalesce(scores_q.c.score, 0)
]).select_from(scores_q).where(scores_q.c.discord_id == ctx.author.id))
rank_value = await (db.select([
sa.func.count(sa.distinct(scores_q.c.score)) + 1
]).where(scores_q.c.score > my_score).gino.scalar())
info = await (db.select([Challenge, solved
]).where(sa.not_(Challenge.hidden)).gino.load(
(Challenge, ColumnLoader(solved))).all())
solved, unsolved = split_on(info)
points = sum(c.points for c in solved)
count = len(solved)
solved_msg = ", ".join(c.title for c in solved) or "No solves"
unsolved_msg = ", ".join(c.title for c in unsolved) or "All solved"
msg = textwrap.dedent(f"""
Challenge info for {ctx.author} in season {season}:
Solves: {count}
Points: {points}
Rank: {rank_value}
Solved: `{solved_msg}`
Unsolved: `{unsolved_msg}`
""")
await ctx.send(msg)
async def leaderboard(
self,
ctx,
tag_condition: Optional[Literal["every", "any"]] = "every",
season: Optional[int] = CURRENT_SEASON,
*tags: str,
):
"""View the leaderboard for completed challenges.
tag_condition decides whether to filter by challenges that have EVERY
specified tag, or ANY of the specified tags, defaults to EVERY.
Note that specifying no tags with "every" filters to all challenges,
while no tags with "any" filters to zero challenges!
"""
tag_filter = (Challenge.tags.contains
if tag_condition == "every" else Challenge.tags.overlap)
score = sa.func.sum(Challenge.points).label("score")
count = sa.func.count(Challenge.points).label("count")
rank = sa.func.dense_rank().over(
order_by=sa.desc("score")).label("rank")
q = (db.select([User.discord_id, score, count]).select_from(
User.join(CompletedChallenge).join(Challenge)).where(
tag_filter(tags)).where(
CompletedChallenge.season == season).group_by(
User.discord_id).alias("inner"))
scores = await (db.select([q.c.discord_id, q.c.score, q.c.count,
rank]).select_from(q).order_by(
sa.desc(q.c.score)).limit(10).gino.load(
(
q.c.discord_id,
ColumnLoader(q.c.score),
ColumnLoader(q.c.count),
ColumnLoader(rank),
)).all())
scores_formatted = [[r, self.bot.get_user(uid), s, c]
for (uid, s, c, r) in scores]
table = tabulate(
scores_formatted,
headers=["Rank", "Username", "Score", "Solved Challenges"],
tablefmt="github",
)
await ctx.send(f"Scoreboard for season {season}: ```\n{table}\n```")
async def challenge_by_tag(request: HTTPConnection):
tag = request.path_params["tag"]
solves = sa.func.count(CompletedChallenge.challenge_id).label("solves")
select = [Challenge, solves]
columns = (Challenge, ColumnLoader(solves))
if request.user.is_authenticated:
solved_challenges = (db.select([
CompletedChallenge.challenge_id
]).where(CompletedChallenge.season == CURRENT_SEASON).where(
CompletedChallenge.discord_id == request.user.discord_id).cte(
"solved_challenges"))
solved = (sa.exists().where(
solved_challenges.c.challenge_id == Challenge.id).label("solved"))
select.append(solved)
columns = (*columns, ColumnLoader(solved))
challenges = await (db.select(select).select_from(
Challenge.outerjoin(
CompletedChallenge,
(Challenge.id == CompletedChallenge.challenge_id)
& (CompletedChallenge.season == CURRENT_SEASON),
)).group_by(Challenge.id).where(Challenge.tags.contains(
[tag])).order_by(Challenge.creation_date.desc(),
Challenge.id.desc()).gino.load(columns).all())
rendered = [(
w,
length_constrained_plaintext_markdown(w.content),
solves,
did_solve and did_solve[0],
) for (w, solves, *did_solve) in challenges
if not should_skip_challenge(w, request.user.is_admin)]
return templates.TemplateResponse(
"challenge/index.j2",
{
"request": request,
"challenges": rendered,
},
)
async def challenge_view(request: HTTPConnection):
slug = request.path_params["slug"]
solves = sa.func.count(CompletedChallenge.challenge_id).label("solves")
challenge = await (db.select([Challenge, solves]).select_from(
Challenge.outerjoin(
CompletedChallenge,
(Challenge.id == CompletedChallenge.challenge_id)
& (CompletedChallenge.season == CURRENT_SEASON),
)).group_by(Challenge.id).where(Challenge.slug == slug).gino.load(
(Challenge, ColumnLoader(solves))).first())
if challenge is None:
return abort(404, "Challenge not found")
challenge, solves = challenge
if should_skip_challenge(challenge, request.user.is_admin):
return abort(404, "Challenge not found")
if request.user.is_authenticated:
solved_challenge = await CompletedChallenge.query.where(
(CompletedChallenge.discord_id == request.user.discord_id)
& (CompletedChallenge.challenge_id == challenge.id)
& (CompletedChallenge.season == CURRENT_SEASON)).gino.first()
else:
solved_challenge = False
rendered = highlight_markdown_unsafe(challenge.content)
return templates.TemplateResponse(
"challenge/view.j2",
{
"challenge": challenge,
"request": request,
"rendered": rendered,
"solves": solves,
"submit_form": AnswerForm(),
"solved_challenge": solved_challenge,
},
)
async def stats(
self,
ctx,
tag_condition: Optional[Literal["every", "any"]] = "every",
season: Optional[int] = CURRENT_SEASON,
*tags: str,
):
"""View the most and lest solved challenges.
tag_condition decides whether to filter by challenges that have EVERY
specified tag, or ANY of the specified tags, defaults to EVERY.
Note that specifying no tags with "every" filters to all challenges,
while no tags with "any" filters to zero challenges!
"""
tag_filter = (Challenge.tags.contains
if tag_condition == "every" else Challenge.tags.overlap)
count = sa.func.count(CompletedChallenge.challenge_id).label("count")
rank = sa.func.dense_rank().over(
order_by=sa.desc("count")).label("rank")
q_most = (db.select([
Challenge.title, Challenge.points, count, Challenge.hidden
]).select_from(Challenge.outerjoin(CompletedChallenge)).where(
tag_filter(tags)).where(
CompletedChallenge.season == season).group_by(
Challenge.id).alias("inner"))
hidden_check_most = sa.case([(q_most.c.hidden, "X")],
else_="").label("hidden_check")
most = await (db.select([
rank, q_most.c.title, q_most.c.count, q_most.c.points,
hidden_check_most
]).select_from(q_most).limit(5).order_by(sa.desc(
q_most.c.count)).gino.load(
(rank, q_most.c.title, ColumnLoader(q_most.c.count),
q_most.c.points, hidden_check_most)).all())
q_least = (db.select([
Challenge.title, Challenge.points, count, Challenge.hidden
]).select_from(Challenge.outerjoin(CompletedChallenge)).where(
tag_filter(tags)).where(
CompletedChallenge.season == season).group_by(
Challenge.id).alias("inner"))
hidden_check_least = sa.case([(q_least.c.hidden, "X")],
else_="").label("hidden_check")
least = await (db.select([
rank, q_least.c.title, q_least.c.count, q_least.c.points,
hidden_check_least
]).select_from(q_least).order_by(sa.asc(
q_least.c.count)).limit(5).gino.load(
(rank, q_least.c.title, ColumnLoader(q_least.c.count),
q_least.c.points, hidden_check_least)).all())
most_table = tabulate(
most,
headers=["Rank", "Challenge Title", "Solves", "Points", "Hidden"],
tablefmt="github",
)
least_table = tabulate(
least,
headers=["Rank", "Challenge Title", "Solves", "Points", "Hidden"],
tablefmt="github",
)
msg = textwrap.dedent("""
```md
# Most Solved Challenges in season {season}
{most_table}
# Least Solved Challenges in season {season}
{least_table}
```
""").format(most_table=most_table,
least_table=least_table,
season=season)
await ctx.send(msg)
async def challenge_submit_answer(request: HTTPConnection):
id = request.path_params["id"]
form = await request.form()
form = AnswerForm(form)
is_valid = form.validate()
answer = form.answer.data
solves = sa.func.count(CompletedChallenge.challenge_id).label("solves")
challenge = await (db.select([Challenge, solves]).select_from(
Challenge.outerjoin(
CompletedChallenge,
(Challenge.id == CompletedChallenge.challenge_id)
& (CompletedChallenge.season == CURRENT_SEASON),
)).group_by(Challenge.id).where(Challenge.id == id).gino.load(
(Challenge, ColumnLoader(solves))).first())
if challenge is None:
return abort(404, "Challenge not found")
challenge, solves = challenge
if should_skip_challenge(challenge, request.user.is_admin):
return abort(404, "Challenge not found")
if (challenge.answer or challenge.flag) != answer:
is_valid = False
form.answer.errors.append("Incorrect answer.")
# TODO? change this to a flash message
if challenge.depreciated:
is_valid = False
form.answer.errors.append(
"Correct, but this challenge is depreciated, sorry.")
already_claimed = await CompletedChallenge.query.where(
(CompletedChallenge.discord_id == request.user.discord_id)
& (CompletedChallenge.challenge_id == challenge.id)
& (CompletedChallenge.season == CURRENT_SEASON)).gino.first()
if already_claimed is not None:
# shouldn't see the form anyway
is_valid = False
form.answer.errors.append("You've already solved this challenge.")
if is_valid:
await CompletedChallenge.create(
discord_id=request.user.discord_id,
challenge_id=challenge.id,
season=CURRENT_SEASON,
)
return redirect_response(
url=request.url_for("challenge_view", slug=challenge.slug))
rendered = highlight_markdown_unsafe(challenge.content)
return templates.TemplateResponse(
"challenge/view.j2",
{
"challenge": challenge,
"request": request,
"rendered": rendered,
"solves": solves,
"submit_form": form,
},
)