def rate_contest(contest):
from judge.models import Rating, Profile
cursor = connection.cursor()
cursor.execute('''
SELECT judge_rating.user_id, judge_rating.rating, judge_rating.volatility, r.times
FROM judge_rating INNER JOIN
judge_contest ON (judge_contest.id = judge_rating.contest_id) INNER JOIN (
SELECT judge_rating.user_id AS id, MAX(judge_contest.end_time) AS last_time,
COUNT(judge_rating.user_id) AS times
FROM judge_contestparticipation INNER JOIN
judge_rating ON (judge_rating.user_id = judge_contestparticipation.user_id) INNER JOIN
judge_contest ON (judge_contest.id = judge_rating.contest_id)
WHERE judge_contestparticipation.contest_id = %s AND judge_contest.end_time < %s AND
judge_contestparticipation.user_id NOT IN (
SELECT profile_id FROM judge_contest_rate_exclude WHERE contest_id = %s
) AND judge_contestparticipation.virtual = 0
GROUP BY judge_rating.user_id
ORDER BY judge_contestparticipation.score DESC, judge_contestparticipation.cumtime ASC,
judge_contestparticipation.tiebreaker ASC
) AS r ON (judge_rating.user_id = r.id AND judge_contest.end_time = r.last_time)
''', (contest.id, contest.end_time, contest.id))
data = {user: (rating, volatility, times) for user, rating, volatility, times in cursor.fetchall()}
cursor.close()
users = contest.users.order_by('is_disqualified', '-score', 'cumtime', 'tiebreaker') \
.annotate(submissions=Count('submission')).exclude(user_id__in=contest.rate_exclude.all()) \
.filter(virtual=0, user__is_unlisted=False).values_list('id', 'user_id', 'score', 'cumtime')
if not contest.rate_all:
users = users.filter(submissions__gt=0)
if contest.rating_floor is not None:
users = users.exclude(user__rating__lt=contest.rating_floor)
if contest.rating_ceiling is not None:
users = users.exclude(user__rating__gt=contest.rating_ceiling)
users = list(tie_ranker(users, key=itemgetter(2, 3)))
participation_ids = [user[1][0] for user in users]
user_ids = [user[1][1] for user in users]
ranking = list(map(itemgetter(0), users))
old_data = [data.get(user, (1200, 535, 0)) for user in user_ids]
old_rating = list(map(itemgetter(0), old_data))
old_volatility = list(map(itemgetter(1), old_data))
times_ranked = list(map(itemgetter(2), old_data))
rating, volatility = recalculate_ratings(old_rating, old_volatility, ranking, times_ranked)
now = timezone.now()
ratings = [Rating(user_id=id, contest=contest, rating=r, volatility=v, last_rated=now, participation_id=p, rank=z)
for id, p, r, v, z in zip(user_ids, participation_ids, rating, volatility, ranking)]
cursor = connection.cursor()
cursor.execute('CREATE TEMPORARY TABLE _profile_rating_update(id integer, rating integer)')
cursor.executemany('INSERT INTO _profile_rating_update VALUES (%s, %s)', list(zip(user_ids, rating)))
with transaction.atomic():
Rating.objects.filter(contest=contest).delete()
Rating.objects.bulk_create(ratings)
cursor.execute('''
UPDATE `%s` p INNER JOIN `_profile_rating_update` tmp ON (p.id = tmp.id)
SET p.rating = tmp.rating
''' % Profile._meta.db_table)
cursor.execute('DROP TABLE _profile_rating_update')
cursor.close()
return old_rating, old_volatility, ranking, times_ranked, rating, volatility
def rate_contest(contest):
from judge.models import Rating, Profile
cursor = connection.cursor()
cursor.execute('''
SELECT judge_rating.user_id, judge_rating.rating, judge_rating.volatility, r.times
FROM judge_rating INNER JOIN
judge_contest ON (judge_contest.id = judge_rating.contest_id) INNER JOIN (
SELECT judge_rating.user_id AS id, MAX(judge_contest.end_time) AS last_time,
COUNT(judge_rating.user_id) AS times
FROM judge_contestparticipation INNER JOIN
judge_rating ON (judge_rating.user_id = judge_contestparticipation.user_id) INNER JOIN
judge_contest ON (judge_contest.id = judge_rating.contest_id)
WHERE judge_contestparticipation.contest_id = %s AND judge_contest.end_time < %s AND
judge_contestparticipation.user_id NOT IN (
SELECT profile_id FROM judge_contest_rate_exclude WHERE contest_id = %s
) AND judge_contestparticipation.virtual = 0
GROUP BY judge_rating.user_id
ORDER BY judge_contestparticipation.score DESC, judge_contestparticipation.cumtime ASC
) AS r ON (judge_rating.user_id = r.id AND judge_contest.end_time = r.last_time)
''', (contest.id, contest.end_time, contest.id))
data = {user: (rating, volatility, times) for user, rating, volatility, times in cursor.fetchall()}
cursor.close()
users = contest.users.order_by('-score', 'cumtime').annotate(submissions=Count('submission')) \
.exclude(user_id__in=contest.rate_exclude.all()).filter(virtual=0, user__is_unlisted=False) \
.values_list('id', 'user_id', 'score', 'cumtime')
if not contest.rate_all:
users = users.filter(submissions__gt=0)
users = list(tie_ranker(users, key=itemgetter(2, 3)))
participation_ids = [user[1][0] for user in users]
user_ids = [user[1][1] for user in users]
ranking = list(map(itemgetter(0), users))
old_data = [data.get(user, (1200, 535, 0)) for user in user_ids]
old_rating = list(map(itemgetter(0), old_data))
old_volatility = list(map(itemgetter(1), old_data))
times_ranked = list(map(itemgetter(2), old_data))
rating, volatility = recalculate_ratings(old_rating, old_volatility, ranking, times_ranked)
now = timezone.now()
ratings = [Rating(user_id=id, contest=contest, rating=r, volatility=v, last_rated=now, participation_id=p, rank=z)
for id, p, r, v, z in zip(user_ids, participation_ids, rating, volatility, ranking)]
cursor = connection.cursor()
cursor.execute('CREATE TEMPORARY TABLE _profile_rating_update(id integer, rating integer)')
cursor.executemany('INSERT INTO _profile_rating_update VALUES (%s, %s)', list(zip(user_ids, rating)))
with transaction.atomic():
Rating.objects.filter(contest=contest).delete()
Rating.objects.bulk_create(ratings)
cursor.execute('''
UPDATE `%s` p INNER JOIN `_profile_rating_update` tmp ON (p.id = tmp.id)
SET p.rating = tmp.rating
''' % Profile._meta.db_table)
cursor.execute('DROP TABLE _profile_rating_update')
cursor.close()
return old_rating, old_volatility, ranking, times_ranked, rating, volatility