def base_contest_ranking_list(contest, problems, queryset, for_user=None):
cursor = connection.cursor()
cursor.execute('''
SELECT part.id, cp.id, prob.code, MAX(cs.points) AS best, MAX(sub.date) AS `last`
FROM judge_contestproblem cp CROSS JOIN judge_contestparticipation part INNER JOIN
judge_problem prob ON (cp.problem_id = prob.id) LEFT OUTER JOIN
judge_contestsubmission cs ON (cs.problem_id = cp.id AND cs.participation_id = part.id) LEFT OUTER JOIN
judge_submission sub ON (sub.id = cs.submission_id)
WHERE cp.contest_id = %s AND part.contest_id = %s {extra}
GROUP BY cp.id, part.id
'''.format(extra=('AND part.user_id = %s' if for_user is not None else
'AND part.virtual = 0')),
(contest.id, contest.id) + ((for_user,) if for_user is not None else ()))
data = {(part, prob): (code, best, last and from_database_time(last)) for part, prob, code, best, last in cursor}
cursor.close()
problems = map(attrgetter('id', 'points', 'is_pretested'), problems)
def make_ranking_profile(participation):
part = participation.id
return make_contest_ranking_profile(participation, [
BestSolutionData(code=data[part, prob][0], points=data[part, prob][1],
time=data[part, prob][2] - participation.start,
state=best_solution_state(data[part, prob][1], points),
is_pretested=is_pretested)
if (part, prob) in data and data[part, prob][1] is not None else None
for prob, points, is_pretested in problems])
return map(make_ranking_profile, queryset.select_related('user__user', 'rating')
.defer('user__about', 'user__organizations__about'))
def get_participation_ranking_profile(contest, participation, problems):
cursor = connection.cursor()
cursor.execute('''
SELECT cp.id, (
SELECT MAX(ccs.points)
FROM judge_contestsubmission ccs
WHERE ccs.problem_id = cp.id AND ccs.participation_id = %s
GROUP BY ccs.points, ccs.bonus
HAVING ccs.points+ccs.bonus = MAX(cs.points+cs.bonus)
) AS best, MAX(cs.points+cs.bonus) AS total_best, MAX(sub.date) AS `last`
FROM judge_contestproblem cp INNER JOIN
judge_contestsubmission cs ON (cs.problem_id = cp.id AND cs.participation_id = %s) LEFT OUTER JOIN
judge_submission sub ON (sub.id = cs.submission_id)
WHERE cp.contest_id = %s
GROUP BY cp.id
''', (participation.id, participation.id, contest.id))
scoring = {prob: (best, total_best, last and from_database_time(last)) for prob, best, total_best, last in cursor}
cursor.close()
return make_contest_ranking_profile(participation, [
BestSolutionData(code=problem.problem.code, points=scoring[problem.id][0], bonus=scoring[problem.id][1] - scoring[problem.id][0],
time=scoring[problem.id][2] - participation.start,
state=best_solution_state(scoring[problem.id][0], problem.points),
is_pretested=problem.is_pretested)
if problem.id in scoring else None for problem in problems
])
def update_participation(self, participation):
cumtime = 0
points = 0
format_data = {}
with connection.cursor() as cursor:
cursor.execute('''
SELECT MAX(cs.points) as `score`, (
SELECT MIN(csub.date)
FROM judge_contestsubmission ccs LEFT OUTER JOIN
judge_submission csub ON (csub.id = ccs.submission_id)
WHERE ccs.problem_id = cp.id AND ccs.participation_id = %s AND ccs.points = MAX(cs.points)
) AS `time`, cp.id AS `prob`
FROM judge_contestproblem cp INNER JOIN
judge_contestsubmission cs ON (cs.problem_id = cp.id AND cs.participation_id = %s) LEFT OUTER JOIN
judge_submission sub ON (sub.id = cs.submission_id)
GROUP BY cp.id
''', (participation.id, participation.id))
for score, time, prob in cursor.fetchall():
if self.config['cumtime']:
dt = (from_database_time(time) - participation.start).total_seconds()
if score:
cumtime += dt
else:
dt = 0
format_data[str(prob)] = {'time': dt, 'points': score}
points += score
participation.cumtime = max(cumtime, 0)
participation.score = points
participation.format_data = format_data
participation.save()
def update_participation(self, participation):
cumtime = 0
points = 0
format_data = {}
with connection.cursor() as cursor:
cursor.execute('''
SELECT MAX(cs.points) as `score`, (
SELECT MIN(csub.date)
FROM judge_contestsubmission ccs LEFT OUTER JOIN
judge_submission csub ON (csub.id = ccs.submission_id)
WHERE ccs.problem_id = cp.id AND ccs.participation_id = %s AND ccs.points = MAX(cs.points)
) AS `time`, cp.id AS `prob`
FROM judge_contestproblem cp INNER JOIN
judge_contestsubmission cs ON (cs.problem_id = cp.id AND cs.participation_id = %s) LEFT OUTER JOIN
judge_submission sub ON (sub.id = cs.submission_id)
GROUP BY cp.id
''', (participation.id, participation.id))
for score, time, prob in cursor.fetchall():
if self.config['cumtime']:
dt = (from_database_time(time) - participation.start).total_seconds()
if score:
cumtime += dt
else:
dt = 0
format_data[str(prob)] = {'time': dt, 'points': score}
points += score
participation.cumtime = max(cumtime, 0)
participation.score = points
participation.format_data = format_data
participation.save()
def update_participation(self, participation):
cumtime = 0
last = 0
penalty = 0
score = 0
format_data = {}
with connection.cursor() as cursor:
cursor.execute(
'''
SELECT MAX(cs.points) as `points`, (
SELECT MIN(csub.date)
FROM judge_contestsubmission ccs LEFT OUTER JOIN
judge_submission csub ON (csub.id = ccs.submission_id)
WHERE ccs.problem_id = cp.id AND ccs.participation_id = %s AND ccs.points = MAX(cs.points)
) AS `time`, cp.id AS `prob`
FROM judge_contestproblem cp INNER JOIN
judge_contestsubmission cs ON (cs.problem_id = cp.id AND cs.participation_id = %s) LEFT OUTER JOIN
judge_submission sub ON (sub.id = cs.submission_id)
GROUP BY cp.id
''', (participation.id, participation.id))
for points, time, prob in cursor.fetchall():
time = from_database_time(time)
dt = (time - participation.start).total_seconds()
# Compute penalty
if self.config['penalty']:
# An IE can have a submission result of `None`
subs = participation.submissions.exclude(submission__result__isnull=True) \
.exclude(submission__result__in=['IE', 'CE']) \
.filter(problem_id=prob)
if points:
prev = subs.filter(
submission__date__lte=time).count() - 1
penalty += prev * self.config['penalty'] * 60
else:
# We should always display the penalty, even if the user has a score of 0
prev = subs.count()
else:
prev = 0
if points:
cumtime += dt
last = max(last, dt)
format_data[str(prob)] = {
'time': dt,
'points': points,
'penalty': prev
}
score += points
participation.cumtime = cumtime + penalty
participation.score = score
participation.tiebreaker = last # field is sorted from least to greatest
participation.format_data = format_data
participation.save()
def update_participation(self, participation):
cumtime = 0
points = 0
format_data = {}
with connection.cursor() as cursor:
cursor.execute(
'''
SELECT (
SELECT MAX(ccs.points)
FROM judge_contestsubmission ccs LEFT OUTER JOIN
judge_submission csub ON (csub.id = ccs.submission_id)
WHERE ccs.problem_id = cp.id AND ccs.participation_id = %s AND csub.date = MAX(sub.date)
) AS `score`, MAX(sub.date) AS `time`, cp.id AS `prob`, (
SELECT COUNT(ccs.id)
FROM judge_contestsubmission ccs LEFT OUTER JOIN
judge_submission csub ON (csub.id = ccs.submission_id)
WHERE ccs.problem_id = cp.id AND ccs.participation_id = %s AND csub.result NOT IN ('IE', 'CE')
) AS `subs`, cp.points AS `max_score`
FROM judge_contestproblem cp INNER JOIN
judge_contestsubmission cs ON (cs.problem_id = cp.id AND cs.participation_id = %s) LEFT OUTER JOIN
judge_submission sub ON (sub.id = cs.submission_id)
GROUP BY cp.id
''', (participation.id, participation.id, participation.id))
for score, time, prob, subs, max_score in cursor.fetchall():
time = from_database_time(time)
dt = (time - participation.start).total_seconds()
if self.config['cumtime']:
cumtime += dt
bonus = 0
if score > 0:
# First AC bonus
if subs == 1 and score == max_score:
bonus += self.config['first_ac_bonus']
# Time bonus
if self.config['time_bonus']:
bonus += (participation.end_time - time).total_seconds(
) // 60 // self.config['time_bonus']
points += bonus
format_data[str(prob)] = {
'time': dt,
'points': score,
'bonus': bonus
}
points += score
participation.cumtime = cumtime
participation.score = points
participation.tiebreaker = 0
participation.format_data = format_data
participation.save()
def get_pp_breakdown(user, start=0, end=100):
with connection.cursor() as cursor:
cursor.execute(
'''
SELECT max_points_table.problem_code,
max_points_table.problem_name,
max_points_table.max_points,
judge_submission.id,
judge_submission.date,
judge_submission.case_points,
judge_submission.case_total,
judge_submission.result,
judge_language.short_name,
judge_language.key
FROM judge_submission
JOIN (SELECT judge_problem.id problem_id,
judge_problem.name problem_name,
judge_problem.code problem_code,
MAX(judge_submission.points) AS max_points
FROM judge_problem
INNER JOIN judge_submission ON (judge_problem.id = judge_submission.problem_id)
WHERE (judge_problem.is_public = True AND
judge_problem.is_organization_private = False AND
judge_submission.points IS NOT NULL AND
judge_submission.user_id = %s)
GROUP BY judge_problem.id
HAVING MAX(judge_submission.points) > 0.0) AS max_points_table
ON (judge_submission.problem_id = max_points_table.problem_id AND
judge_submission.points = max_points_table.max_points AND
judge_submission.user_id = %s)
JOIN judge_language
ON judge_submission.language_id = judge_language.id
GROUP BY max_points_table.problem_id
ORDER BY max_points DESC, judge_submission.date DESC
LIMIT %s OFFSET %s
''', (user.id, user.id, end - start + 1, start))
data = cursor.fetchall()
breakdown = []
for weight, contrib in zip(PP_WEIGHT_TABLE[start:end], data):
code, name, points, id, date, case_points, case_total, result, lang_short_name, lang_key = contrib
# Replicates a lot of the logic usually done on Submission objects
lang_short_display_name = lang_short_name or lang_key
result_class = Submission.result_class_from_code(
result, case_points, case_total)
long_status = Submission.USER_DISPLAY_CODES.get(result, '')
breakdown.append(
PPBreakdown(
points=points,
weight=weight * 100,
scaled_points=points * weight,
problem_name=name,
problem_code=code,
sub_id=id,
sub_date=from_database_time(date),
sub_points=case_points,
sub_total=case_total,
sub_short_status=result,
sub_long_status=long_status,
sub_result_class=result_class,
sub_lang=lang_short_display_name,
))
has_more = end < min(len(PP_WEIGHT_TABLE), start + len(data))
return breakdown, has_more
def get_pp_breakdown(user, start=0, end=PP_ENTRIES):
with connection.cursor() as cursor:
cursor.execute('''
SELECT max_points_table.problem_code,
max_points_table.problem_name,
max_points_table.max_points,
judge_submission.id,
judge_submission.date,
judge_submission.case_points,
judge_submission.case_total,
judge_submission.result,
judge_language.short_name,
judge_language.key
FROM judge_submission
JOIN (SELECT judge_problem.id problem_id,
judge_problem.name problem_name,
judge_problem.code problem_code,
MAX(judge_submission.points) AS max_points
FROM judge_problem
INNER JOIN judge_submission ON (judge_problem.id = judge_submission.problem_id)
WHERE (judge_problem.is_public = True AND
judge_problem.is_organization_private = False AND
judge_submission.points IS NOT NULL AND
judge_submission.user_id = %s)
GROUP BY judge_problem.id
HAVING MAX(judge_submission.points) > 0.0) AS max_points_table
ON (judge_submission.problem_id = max_points_table.problem_id AND
judge_submission.points = max_points_table.max_points AND
judge_submission.user_id = %s)
JOIN judge_language
ON judge_submission.language_id = judge_language.id
GROUP BY max_points_table.problem_id
ORDER BY max_points DESC, judge_submission.date DESC
LIMIT %s OFFSET %s
''', (user.id, user.id, end - start + 1, start))
data = cursor.fetchall()
breakdown = []
for weight, contrib in zip(PP_WEIGHT_TABLE[start:end], data):
code, name, points, id, date, case_points, case_total, result, lang_short_name, lang_key = contrib
# Replicates a lot of the logic usually done on Submission objects
lang_short_display_name = lang_short_name or lang_key
result_class = Submission.result_class_from_code(result, case_points, case_total)
long_status = Submission.USER_DISPLAY_CODES.get(result, '')
breakdown.append(PPBreakdown(
points=points,
weight=weight * 100,
scaled_points=points * weight,
problem_name=name,
problem_code=code,
sub_id=id,
sub_date=from_database_time(date),
sub_points=case_points,
sub_total=case_total,
sub_short_status=result,
sub_long_status=long_status,
sub_result_class=result_class,
sub_lang=lang_short_display_name,
))
has_more = end < min(len(PP_WEIGHT_TABLE), start + len(data))
return breakdown, has_more
def update_participation(self, participation):
cumtime = 0
score = 0
format_data = {}
with connection.cursor() as cursor:
cursor.execute(
"""
SELECT q.prob,
MIN(q.date) as `date`,
q.batch_points
FROM (
SELECT cp.id as `prob`,
sub.id as `subid`,
sub.date as `date`,
tc.points as `points`,
tc.batch as `batch`,
MIN(tc.points) as `batch_points`
FROM judge_contestproblem cp
INNER JOIN
judge_contestsubmission cs
ON (cs.problem_id = cp.id AND cs.participation_id = %s)
LEFT OUTER JOIN
judge_submission sub
ON (sub.id = cs.submission_id AND sub.status = 'D')
INNER JOIN judge_submissiontestcase tc
ON sub.id = tc.submission_id
GROUP BY cp.id, tc.batch, sub.id
) q
INNER JOIN (
SELECT prob, batch, MAX(r.batch_points) as max_batch_points
FROM (
SELECT cp.id as `prob`,
tc.batch as `batch`,
MIN(tc.points) as `batch_points`
FROM judge_contestproblem cp
INNER JOIN
judge_contestsubmission cs
ON (cs.problem_id = cp.id AND cs.participation_id = %s)
LEFT OUTER JOIN
judge_submission sub
ON (sub.id = cs.submission_id AND sub.status = 'D')
INNER JOIN judge_submissiontestcase tc
ON sub.id = tc.submission_id
GROUP BY cp.id, tc.batch, sub.id
) r
GROUP BY prob, batch
) p
ON p.prob = q.prob AND (p.batch = q.batch OR p.batch is NULL AND q.batch is NULL)
WHERE p.max_batch_points = q.batch_points
GROUP BY q.prob, q.batch
""", (participation.id, participation.id))
for problem_id, time, subtask_points in cursor.fetchall():
problem_id = str(problem_id)
time = from_database_time(time)
if self.config['cumtime']:
dt = (time - participation.start).total_seconds()
else:
dt = 0
if format_data.get(problem_id) is None:
format_data[problem_id] = {'points': 0, 'time': 0}
format_data[problem_id]['points'] += subtask_points
format_data[problem_id]['time'] = max(
dt, format_data[problem_id]['time'])
for problem_data in format_data.values():
penalty = problem_data['time']
points = problem_data['points']
if self.config['cumtime'] and points:
cumtime += penalty
score += points
participation.cumtime = max(cumtime, 0)
participation.score = round(score, self.contest.points_precision)
participation.tiebreaker = 0
participation.format_data = format_data
participation.save()
