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()