def get_events(request):
if request.user.is_authenticated:
coder = request.user.coder
else:
coder = None
referer = request.META.get('HTTP_REFERER')
if referer:
parsed = urlparse(referer)
as_coder = parse_qs(parsed.query).get('as_coder')
if as_coder and request.user.has_perm('as_coder'):
coder = Coder.objects.get(user__username=as_coder[0])
tzname = get_timezone(request)
offset = get_timezone_offset(tzname)
query = Q()
categories = request.POST.getlist('categories')
ignore_filters = request.POST.getlist('ignore_filters')
if coder:
query = coder.get_contest_filter(categories, ignore_filters)
if not coder or coder.settings.get('calendar_filter_long', True):
if categories == ['calendar'] and '0' not in ignore_filters:
query &= Q(duration_in_secs__lt=timedelta(days=1).total_seconds())
start_time = arrow.get(request.POST.get('start', timezone.now())).datetime
end_time = arrow.get(request.POST.get('end', timezone.now() + timedelta(days=31))).datetime
query = query & Q(end_time__gte=start_time) & Q(start_time__lte=end_time)
search_query = request.POST.get('search_query', None)
if search_query:
search_query_re = verify_regex(search_query)
query &= Q(host__iregex=search_query_re) | Q(title__iregex=search_query_re)
party_slug = request.POST.get('party')
if party_slug:
party = get_object_or_404(Party.objects.for_user(request.user), slug=party_slug)
query = Q(rating__party=party) & query
contests = Contest.objects if party_slug else Contest.visible
try:
result = []
for contest in contests.filter(query):
c = {
'id': contest.pk,
'title': contest.title,
'host': contest.host,
'url': contest.url,
'start': (contest.start_time + timedelta(minutes=offset)).strftime("%Y-%m-%dT%H:%M:%S"),
'end': (contest.end_time + timedelta(minutes=offset)).strftime("%Y-%m-%dT%H:%M:%S"),
'countdown': contest.next_time,
'hr_duration': contest.hr_duration,
'color': contest.resource.color,
}
result.append(c)
except Exception as e:
return JsonResponse({'message': f'query = `{search_query}`, error = {e}'}, safe=False, status=400)
return JsonResponse(result, safe=False)
def search(request, **kwargs):
query = request.GET.get('query', None)
if not query or not isinstance(query, str):
return HttpResponseBadRequest('invalid query')
count = int(request.GET.get('count', django_settings.DEFAULT_COUNT_QUERY_))
count = min(count, django_settings.DEFAULT_COUNT_LIMIT_)
page = int(request.GET.get('page', 1))
if query == 'themes':
ret = {}
for t in django_settings.THEMES_:
ret[t] = t.title()
return JsonResponse(ret)
elif query == 'timezones':
ret = {}
for tz in get_timezones():
ret[tz["name"]] = f'{tz["name"]} {tz["repr"]}'
return JsonResponse(ret)
elif query == 'resources':
qs = Resource.objects.all()
if 'regex' in request.GET:
qs = qs.filter(get_iregex_filter(request.GET['regex'], 'host'))
qs = qs.order_by('-n_accounts', 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{'id': r.id, 'text': r.host, 'icon': r.icon} for r in qs]
elif query == 'resources-for-add-account' and request.user.is_authenticated:
coder = request.user.coder
coder_accounts = coder.account_set.filter(resource=OuterRef('pk'))
qs = Resource.objects \
.annotate(has_coder_account=Exists(coder_accounts)) \
.annotate(has_multi=F('module__multi_account_allowed')) \
.annotate(disabled=Case(
When(module__isnull=True, then=Value(True)),
When(has_coder_account=True, has_multi=False, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
if 'regex' in request.GET:
qs = qs.filter(get_iregex_filter(request.GET['regex'], 'host'))
qs = qs.order_by('disabled', 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{
'id': r.id,
'text': r.host,
'disabled': r.disabled,
} for r in qs]
elif query == 'accounts-for-add-account' and request.user.is_authenticated:
coder = request.user.coder
qs = Account.objects.all()
resource = request.GET.get('resource')
if resource:
qs = qs.filter(resource__id=int(resource))
else:
qs = qs.select_related('resource')
order = ['disabled']
if 'user' in request.GET:
re_search = request.GET.get('user')
exact_qs = qs.filter(key__iexact=re_search)
if exact_qs.exists():
qs = exact_qs
else:
qs = qs.filter(get_iregex_filter(re_search, 'key', 'name'))
search_striped = re_search.rstrip('$').lstrip('^')
qs = qs.annotate(match=Case(
When(Q(key__iexact=search_striped)
| Q(name__iexact=search_striped),
then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
order.append('-match')
qs = qs.annotate(
has_multi=F('resource__module__multi_account_allowed'))
qs = qs.annotate(disabled=Case(
When(coders=coder, then=Value(True)),
When(coders__isnull=False, has_multi=False, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
qs = qs.order_by(*order, 'pk')
qs = qs[(page - 1) * count:page * count]
ret = []
for r in qs:
fields = {
'id':
r.key,
'text':
f'{r.key} - {r.name}'
if r.name and r.key.find(r.name) == -1 else r.key,
'disabled':
r.disabled,
}
if not resource:
fields['text'] += f', {r.resource.host}'
fields['resource'] = {
'id': r.resource.pk,
'text': r.resource.host
}
ret.append(fields)
elif query == 'organization':
qs = Organization.objects.all()
name = request.GET.get('name')
if name:
qs = qs.filter(
Q(name__icontains=name) | Q(name_ru__icontains=name)
| Q(abbreviation__icontains=name))
qs = qs[(page - 1) * count:page * count]
ret = [{'id': o.name, 'text': o.name} for o in qs]
elif query == 'team':
qs = Team.objects.all()
name = request.GET.get('name')
if name:
qs = qs.filter(name__icontains=name)
event = kwargs.get('event')
if event:
qs = qs.filter(event=event)
qs = qs.annotate(
disabled=Case(When(status=TeamStatus.NEW, then=Value(False)),
default=Value(True),
output_field=BooleanField())).order_by(
'disabled', '-modified', 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{
'id': r.id,
'text': r.name,
'disabled': r.disabled
} for r in qs]
elif query == 'country':
qs = list(countries)
name = request.GET.get('name')
if name:
name = name.lower()
qs = [(c, n) for c, n in countries if name in n.lower()]
qs = qs[(page - 1) * count:page * count]
ret = [{'id': c, 'text': n} for c, n in qs]
elif query == 'notpast':
title = request.GET.get('title')
qs = Contest.objects.filter(title__iregex=verify_regex(title),
end_time__gte=timezone.now())
qs = qs[(page - 1) * count:page * count]
ret = [{'id': c.id, 'text': c.title} for c in qs]
elif query == 'field-to-select':
contest = get_object_or_404(Contest, pk=request.GET.get('cid'))
text = request.GET.get('text')
field = request.GET.get('field')
assert '__' not in field
if field == 'languages':
qs = contest.info.get('languages', [])
qs = ['any'
] + [q for q in qs if not text or text.lower() in q.lower()]
elif field == 'rating':
qs = ['rated', 'unrated']
else:
field = f'addition__{field}'
qs = contest.statistics_set
if text:
qs = qs.filter(**{f'{field}__icontains': text})
qs = qs.distinct(field).values_list(field, flat=True)
qs = qs[(page - 1) * count:page * count]
ret = [{'id': f, 'text': f} for f in qs]
elif query == 'coders':
qs = Coder.objects.all()
if 'regex' in request.GET:
qs = qs.filter(get_iregex_filter(request.GET['regex'], 'username'))
order = ['-n_accounts', 'pk']
if request.user.is_authenticated:
qs = qs.annotate(
iam=Case(When(pk=request.user.coder.pk, then=Value(0)),
default=Value(1),
output_field=IntegerField()))
order.insert(0, 'iam')
qs = qs.order_by(*order, 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{'id': r.id, 'text': r.username} for r in qs]
elif query == 'accounts':
qs = Account.objects.all()
if request.GET.get('resource'):
qs = qs.filter(resource_id=int(request.GET.get('resource')))
order = ['-n_contests', 'pk']
if 'regex' in request.GET:
re_search = request.GET['regex']
exact_qs = qs.filter(key__iexact=re_search)
if exact_qs.exists():
qs = exact_qs
else:
qs = qs.filter(get_iregex_filter(re_search, 'key', 'name'))
search_striped = re_search.rstrip('$').lstrip('^')
qs = qs.annotate(match=Case(
When(Q(key__iexact=search_striped)
| Q(name__iexact=search_striped),
then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
order.insert(0, '-match')
qs = qs.select_related('resource')
qs = qs.order_by(*order, 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{
'id':
r.id,
'text':
f'{r.key}, {r.name}, {r.resource.host}'
if r.name else f'{r.key}, {r.resource.host}'
} for r in qs]
else:
return HttpResponseBadRequest('invalid query')
result = {
'items': ret,
'more': len(ret) and len(ret) == count,
}
return HttpResponse(json.dumps(result, ensure_ascii=False),
content_type="application/json")
def search(request, **kwargs):
query = request.GET.get('query', None)
if not query or not isinstance(query, str):
return HttpResponseBadRequest('invalid query')
count = int(request.GET.get('count', 10))
page = int(request.GET.get('page', 1))
if query == 'timezones':
ret = {}
for tz in get_timezones():
ret[tz["name"]] = f'{tz["name"]} {tz["repr"]}'
return JsonResponse(ret)
elif query == 'resources-for-add-account':
coder = request.user.coder
coder_accounts = coder.account_set.filter(resource=OuterRef('pk'))
qs = Resource.objects \
.annotate(has_coder_account=Exists(coder_accounts)) \
.annotate(has_multi=F('module__multi_account_allowed')) \
.annotate(disabled=Case(
When(module__isnull=True, then=Value(True)),
When(has_coder_account=True, has_multi=False, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
if 'regex' in request.GET:
qs = qs.filter(get_iregex_filter(request.GET['regex'], 'host'))
qs = qs.order_by('disabled', 'pk')
total = qs.count()
qs = qs[(page - 1) * count:page * count]
ret = [{
'id': r.id,
'text': r.host,
'disabled': r.disabled,
} for r in qs]
elif query == 'accounts-for-add-account':
coder = request.user.coder
qs = Account.objects.all()
resource = request.GET.get('resource')
if resource:
qs = qs.filter(resource__id=int(resource))
else:
qs = qs.select_related('resource')
if 'user' in request.GET:
qs = qs.filter(
get_iregex_filter(request.GET.get('user'), 'key', 'name'))
qs = qs.annotate(has_multi=F('resource__module__multi_account_allowed')) \
qs = qs.annotate(disabled=Case(
When(coders=coder, then=Value(True)),
When(coders__isnull=False, has_multi=False, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
qs = qs.order_by('disabled')
total = qs.count()
qs = qs[(page - 1) * count:page * count]
ret = []
for r in qs:
fields = {
'id':
r.key,
'text':
f'{r.key} - {r.name}'
if r.name and r.key.find(r.name) == -1 else r.key,
'disabled':
r.disabled,
}
if not resource:
fields['text'] += f', {r.resource.host}'
fields['resource'] = {
'id': r.resource.pk,
'text': r.resource.host
}
ret.append(fields)
elif query == 'organization':
qs = Organization.objects.all()
name = request.GET.get('name')
if name:
qs = qs.filter(
Q(name__icontains=name) | Q(name_ru__icontains=name)
| Q(abbreviation__icontains=name))
total = qs.count()
qs = qs[(page - 1) * count:page * count]
ret = [{'id': o.name, 'text': o.name} for o in qs]
elif query == 'team':
qs = Team.objects.all()
name = request.GET.get('name')
if name:
qs = qs.filter(name__icontains=name)
event = kwargs.get('event')
if event:
qs = qs.filter(event=event)
qs = qs.annotate(
disabled=Case(When(status=TeamStatus.NEW, then=Value(False)),
default=Value(True),
output_field=BooleanField())).order_by(
'disabled', '-modified')
total = qs.count()
qs = qs[(page - 1) * count:page * count]
ret = [{
'id': r.id,
'text': r.name,
'disabled': r.disabled
} for r in qs]
elif query == 'country':
qs = list(countries)
name = request.GET.get('name')
if name:
name = name.lower()
qs = [(c, n) for c, n in countries if name in n.lower()]
total = len(qs)
qs = qs[(page - 1) * count:page * count]
ret = [{'id': c, 'text': n} for c, n in qs]
elif query == 'notpast':
title = request.GET.get('title')
qs = Contest.objects.filter(title__iregex=verify_regex(title),
end_time__gte=timezone.now())
total = qs.count()
qs = qs[(page - 1) * count:page * count]
ret = [{'id': c.id, 'text': c.title} for c in qs]
elif query == 'field-to-select':
contest = get_object_or_404(Contest, pk=request.GET.get('cid'))
text = request.GET.get('text')
field = request.GET.get('field')
assert '__' not in field
if field == 'languages':
qs = contest.info.get('languages', [])
qs = [[q] for q in qs if not text or text.lower() in q.lower()]
total = len(qs)
else:
field = f'addition__{field}'
qs = contest.statistics_set
if text:
qs = qs.filter(**{f'{field}__icontains': text})
qs = qs.distinct(field).values_list(field)
total = qs.count()
qs = qs[(page - 1) * count:page * count]
ret = [{'id': f[0], 'text': f[0]} for f in qs]
else:
return HttpResponseBadRequest('invalid query')
result = {
'items': ret,
'more': page * count <= total,
}
return HttpResponse(json.dumps(result, ensure_ascii=False),
content_type="application/json")
def get_profile_context(request, statistics):
history_resources = statistics \
.filter(contest__resource__has_rating_history=True) \
.filter(contest__stage__isnull=True) \
.annotate(new_rating=Cast(KeyTextTransform('new_rating', 'addition'), IntegerField())) \
.filter(new_rating__isnull=False) \
.annotate(host=F('contest__resource__host')) \
.values('host') \
.annotate(num_contests=Count('contest')) \
.order_by('-num_contests')
stats = statistics \
.select_related('contest') \
.filter(addition__medal__isnull=False) \
.order_by('-contest__end_time')
resource_medals = {}
account_medals = {}
for stat in stats:
resource_medals.setdefault(stat.contest.resource_id, []).append(stat)
account_medals.setdefault(stat.account.id, []).append(stat)
statistics = statistics \
.select_related('contest', 'contest__resource', 'account') \
.order_by('-contest__end_time')
search = request.GET.get('search')
filters = {}
if search:
for search in search.split(' && '):
if search.startswith('problem:'):
field, search = search.split(':', 1)
search_re = verify_regex(search)
statistics = statistics.filter(
addition__problems__iregex=f'"[^"]*{search_re}[^"]*"')
elif search.startswith('contest:'):
field, search = search.split(':', 1)
statistics = statistics.filter(contest__id=search)
elif search.startswith('account:'):
field, search = search.split(':', 1)
statistics = statistics.filter(account__key=search)
elif search.startswith('resource:'):
field, search = search.split(':', 1)
statistics = statistics.filter(contest__resource__host=search)
history_resources = history_resources.filter(
contest__resource__host=search)
else:
field = 'regex'
search_re = verify_regex(search)
query = Q(contest__resource__host__iregex=search_re) | Q(
contest__title__iregex=search_re)
statistics = statistics.filter(query)
filters.setdefault(field, []).append(search)
search_resource = filters.pop('resource', [])
search_resource = search_resource[0] if len(search_resource) == 1 else None
context = {
'statistics': statistics,
'history_resources': history_resources,
'show_history_ratings': not filters,
'resource_medals': resource_medals,
'account_medals': account_medals,
'search_resource': search_resource,
}
return context
def standings(request,
title_slug,
contest_id,
template='standings.html',
extra_context=None):
groupby = request.GET.get('groupby')
search = request.GET.get('search')
if search == '':
url = request.get_full_path()
url = re.sub('search=&?', '', url)
url = re.sub(r'\?$', '', url)
return redirect(url)
orderby = request.GET.getlist('orderby')
if orderby:
if '--' in orderby:
updated_orderby = []
else:
orderby_set = set()
unique_orderby = reversed([
f for k, f in [(f.lstrip('-'), f) for f in reversed(orderby)]
if k not in orderby_set and not orderby_set.add(k)
])
updated_orderby = [
f for f in unique_orderby if not f.startswith('--')
]
if updated_orderby != orderby:
query = request.GET.copy()
query.setlist('orderby', updated_orderby)
return redirect(f'{request.path}?{query.urlencode()}')
contest = get_object_or_404(Contest.objects.select_related('resource'),
pk=contest_id)
if slug(contest.title) != title_slug:
return HttpResponseNotFound(f'Not found {slug(contest.title)} slug')
contest_fields = contest.info.get('fields', [])
statistics = Statistics.objects.filter(contest=contest)
order = None
resource_standings = contest.resource.info.get('standings', {})
order = copy.copy(resource_standings.get('order'))
if order:
for f in order:
if f.startswith('addition__') and f.split(
'__', 1)[1] not in contest_fields:
order = None
break
if order is None:
order = ['place_as_int', '-solving']
options = contest.info.get('standings', {})
# fixed fields
fixed_fields = (
('penalty', 'Penalty'), ('total_time', 'Time'),
('advanced', 'Advanced')) + tuple(options.get('fixed_fields', []))
statistics = statistics \
.select_related('account') \
.select_related('account__resource') \
.prefetch_related('account__coders')
has_country = 'country' in contest_fields or statistics.filter(
account__country__isnull=False).exists()
division = request.GET.get('division')
if division == 'any':
if 'place_as_int' in order:
order.remove('place_as_int')
order.append('place_as_int')
fixed_fields += (('division', 'Division'), )
if 'team_id' in contest_fields and not groupby:
order.append('addition__name')
statistics = statistics.distinct(*[f.lstrip('-') for f in order])
order.append('pk')
statistics = statistics.order_by(*order)
fields = OrderedDict()
for k, v in fixed_fields:
if k in contest_fields:
fields[k] = v
# field to select
fields_to_select = {}
for f in contest_fields:
f = f.strip('_')
if f.lower() in [
'institution', 'room', 'affiliation', 'city', 'languages'
]:
fields_to_select[f] = request.GET.getlist(f)
with_detail = request.GET.get('detail') in ['true', 'on']
if request.user.is_authenticated:
coder = request.user.coder
if 'detail' in request.GET:
coder.settings['standings_with_detail'] = with_detail
coder.save()
else:
with_detail = coder.settings.get('standings_with_detail', False)
else:
coder = None
if with_detail:
for k in contest_fields:
if (k not in fields and k not in [
'problems', 'name', 'team_id', 'solved', 'hack',
'challenges', 'url', 'participant_type', 'division'
] and 'country' not in k and not k.startswith('_')):
field = ' '.join(k.split('_'))
if field and not field[0].isupper():
field = field.title()
fields[k] = field
per_page = options.get('per_page', 200)
if per_page is None:
per_page = 100500
data_1st_u = options.get('1st_u')
participants_info = {}
if data_1st_u:
seen = {}
last_hl = None
for s in statistics:
match = re.search(data_1st_u['regex'], s.account.key)
k = match.group('key')
solving = s.solving
penalty = s.addition.get('penalty')
info = participants_info.setdefault(s.id, {})
info['search'] = rf'^{k}'
if k in seen or last_hl:
p_info = participants_info.get(seen.get(k))
if (not p_info or last_hl and
(-last_hl['solving'], last_hl['penalty']) <
(-p_info['solving'], p_info['penalty'])):
p_info = last_hl
info.update({
't_solving':
p_info['solving'] - solving,
't_penalty':
p_info['penalty'] -
penalty if penalty is not None else None,
})
if k not in seen:
seen[k] = s.id
info.update({
'n': len(seen),
'solving': solving,
'penalty': penalty
})
if len(seen) == options.get('n_highlight'):
last_hl = info
elif 'n_highlight' in options:
for idx, s in enumerate(statistics[:options['n_highlight']], 1):
participants_info[s.id] = {'n': idx}
medals = options.get('medals')
if medals:
names = [m['name'] for m in medals]
counts = [m['count'] for m in medals]
medals = list(zip(names, accumulate(counts)))
mod_penalty = {}
first = statistics.first()
if first and all('time' not in k for k in contest_fields):
penalty = first.addition.get('penalty')
if penalty and isinstance(penalty,
int) and 'solved' not in first.addition:
mod_penalty.update({'solving': first.solving, 'penalty': penalty})
params = {}
problems = contest.info.get('problems', {})
if 'division' in problems:
divisions_order = list(
problems.get('divisions_order',
sorted(contest.info['problems']['division'].keys())))
divisions_order.append('any')
if division not in divisions_order:
division = divisions_order[0]
params['division'] = division
if division == 'any':
_problems = OrderedDict()
for div in reversed(divisions_order):
for p in problems['division'].get(div, []):
k = get_problem_key(p)
if k not in _problems:
_problems[k] = p
else:
for f in 'n_accepted', 'n_teams':
if f in p:
_problems[k][f] = _problems[k].get(f, 0) + p[f]
problems = list(_problems.values())
else:
statistics = statistics.filter(addition__division=division)
problems = problems['division'][division]
else:
divisions_order = []
for p in problems:
if 'full_score' in p and isinstance(
p['full_score'],
(int, float)) and abs(p['full_score'] - 1) > 1e-9:
mod_penalty = {}
break
last = None
merge_problems = False
for p in problems:
if last and 'name' in last and last.get('name') == p.get(
'name') and last.get('full_score'):
merge_problems = True
last['colspan'] = last.get('colspan', 1) + 1
p['skip'] = True
else:
last = p
# own_stat = statistics.filter(account__coders=coder).first() if coder else None
# filter by search
search = request.GET.get('search')
if search:
if search.startswith('party:'):
_, party_slug = search.split(':')
party = get_object_or_404(Party.objects.for_user(request.user),
slug=party_slug)
statistics = statistics.filter(
Q(account__coders__in=party.coders.all())
| Q(account__coders__in=party.admins.all())
| Q(account__coders=party.author))
else:
search_re = verify_regex(search)
statistics = statistics.filter(
Q(account__key__iregex=search_re)
| Q(addition__name__iregex=search_re))
# filter by country
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
cond = Q(account__country__in=countries)
if 'None' in countries:
cond |= Q(account__country__isnull=True)
statistics = statistics.filter(cond)
params['countries'] = countries
# filter by field to select
for field, values in fields_to_select.items():
if not values:
continue
filt = Q()
if field == 'languages':
for lang in values:
filt |= Q(**{f'addition___languages__contains': [lang]})
else:
for q in values:
if q == 'None':
filt |= Q(**{f'addition__{field}__isnull': True})
else:
filt |= Q(**{f'addition__{field}': q})
statistics = statistics.filter(filt)
# groupby
if groupby == 'country' or groupby in fields_to_select:
fields = OrderedDict()
fields['groupby'] = groupby
fields['n_accounts'] = 'num'
fields['avg_score'] = 'avg'
if 'medal' in contest_fields:
for medal in settings.ORDERED_MEDALS_:
fields[f'n_{medal}'] = medal[0]
if 'advanced' in contest_fields:
fields['n_advanced'] = 'adv'
orderby = [f for f in orderby if f.lstrip('-') in fields
] or ['-n_accounts', '-avg_score']
if groupby == 'languages':
_, before_params = statistics.query.sql_with_params()
querysets = []
for problem in problems:
key = get_problem_key(problem)
field = f'addition__problems__{key}__language'
score = f'addition__problems__{key}__result'
qs = statistics \
.filter(**{f'{field}__isnull': False}) \
.annotate(language=Cast(JSONF(field), models.TextField())) \
.annotate(score=Case(
When(**{f'{score}__startswith': '+'}, then=1),
When(**{f'{score}__startswith': '-'}, then=0),
When(**{f'{score}__startswith': '?'}, then=0),
default=Cast(JSONF(score), models.FloatField()),
output_field=models.FloatField(),
)) \
.annotate(sid=F('pk'))
querysets.append(qs)
merge_statistics = querysets[0].union(*querysets[1:], all=True)
language_query, language_params = merge_statistics.query.sql_with_params(
)
field = 'solving'
statistics = statistics.annotate(groupby=F(field))
elif groupby == 'country':
field = 'account__country'
statistics = statistics.annotate(groupby=F(field))
else:
field = f'addition__{groupby}'
statistics = statistics.annotate(
groupby=Cast(JSONF(field), models.TextField()))
statistics = statistics.order_by('groupby')
statistics = statistics.values('groupby')
statistics = statistics.annotate(n_accounts=Count('id'))
statistics = statistics.annotate(avg_score=Avg('solving'))
if 'medal' in contest_fields:
for medal in settings.ORDERED_MEDALS_:
n_medal = f'n_{medal}'
statistics = statistics.annotate(
**{
f'{n_medal}':
Count(Case(When(addition__medal__iexact=medal,
then=1)))
})
if 'advanced' in contest_fields:
statistics = statistics.annotate(
n_advanced=Count(Case(When(addition__advanced=True, then=1))))
statistics = statistics.order_by(*orderby)
if groupby == 'languages':
query, params = statistics.query.sql_with_params()
query = query.replace(
f'"ranking_statistics"."{field}" AS "groupby"',
'"language" AS "groupby"')
query = query.replace(f'GROUP BY "ranking_statistics"."{field}"',
'GROUP BY "language"')
query = query.replace(f'"ranking_statistics".', '')
query = query.replace(f'AVG("solving") AS "avg_score"',
'AVG("score") AS "avg_score"')
query = query.replace(f'COUNT("id") AS "n_accounts"',
'COUNT("sid") AS "n_accounts"')
query = re.sub('FROM "ranking_statistics".*GROUP BY',
f'FROM ({language_query}) t1 GROUP BY', query)
params = params[:-len(before_params)] + language_params
with connection.cursor() as cursor:
cursor.execute(query, params)
columns = [col[0] for col in cursor.description]
statistics = [
dict(zip(columns, row)) for row in cursor.fetchall()
]
statistics = ListAsQueryset(statistics)
problems = []
labels_groupby = {
'n_accounts': 'Number of participants',
'avg_score': 'Average score',
'n_gold': 'Number of gold',
'n_silver': 'Number of silver',
'n_bronze': 'Number of bronze',
'n_advanced': 'Number of advanced',
}
else:
groupby = None
labels_groupby = None
context = {
'data_1st_u': data_1st_u,
'participants_info': participants_info,
'standings_options': options,
'medals': medals,
'mod_penalty': mod_penalty,
'contest': contest,
'statistics': statistics,
'problems': problems,
'params': params,
'fields': fields,
'divisions_order': divisions_order,
'has_country': has_country,
'per_page': per_page,
'with_row_num': bool(search or countries),
'merge_problems': merge_problems,
'fields_to_select': fields_to_select,
'truncatechars_name_problem': 10 * (2 if merge_problems else 1),
'with_detail': with_detail,
'groupby': groupby,
'pie_limit_rows_groupby': 50,
'labels_groupby': labels_groupby,
'num_rows': statistics.count(),
'advance': contest.info.get('advance'),
}
if extra_context is not None:
context.update(extra_context)
return render(request, template, context)
def profile(request, username, template='profile.html', extra_context=None):
coder = get_object_or_404(Coder, user__username=username)
statistics = Statistics.objects \
.filter(account__coders=coder) \
.select_related('contest', 'contest__resource', 'account') \
.order_by('-contest__end_time')
search = request.GET.get('search')
if search:
for search in search.split(' && '):
if search.startswith('problem:'):
_, search = search.split(':', 1)
search_re = verify_regex(search)
statistics = statistics.filter(
addition__problems__iregex=f'"[^"]*{search_re}[^"]*"')
elif search.startswith('contest:'):
_, search = search.split(':', 1)
statistics = statistics.filter(contest__id=search)
elif search.startswith('account:'):
_, search = search.split(':', 1)
statistics = statistics.filter(account__key=search)
elif search.startswith('resource:'):
_, search = search.split(':', 1)
statistics = statistics.filter(contest__resource__host=search)
else:
search_re = verify_regex(search)
query = Q(contest__resource__host__iregex=search_re) | Q(
contest__title__iregex=search_re)
statistics = statistics.filter(query)
history_resources = Statistics.objects \
.filter(account__coders=coder) \
.filter(contest__resource__has_rating_history=True) \
.filter(contest__stage__isnull=True) \
.annotate(new_rating=Cast(KeyTextTransform('new_rating', 'addition'), IntegerField())) \
.filter(new_rating__isnull=False) \
.annotate(host=F('contest__resource__host')) \
.values('host') \
.annotate(num_contests=Count('contest')) \
.order_by('-num_contests')
resources = Resource.objects \
.prefetch_related(Prefetch(
'account_set',
queryset=(
Account.objects
.filter(coders=coder)
.annotate(num_stats=Count('statistics'))
.order_by('-num_stats')
),
to_attr='coder_accounts',
)) \
.annotate(num_contests=Count(
'contest',
filter=Q(contest__in=Statistics.objects.filter(account__coders=coder).values('contest'))
)) \
.filter(num_contests__gt=0).order_by('-num_contests')
stats = Statistics.objects \
.select_related('contest') \
.filter(account__coders=coder, addition__medal__isnull=False) \
.order_by('-contest__end_time')
medals = {}
for stat in stats:
medals.setdefault(stat.contest.resource_id, []).append(stat)
context = {
'coder': coder,
'resources': resources,
'statistics': statistics,
'history_resources': history_resources,
'medals': medals,
}
if extra_context is not None:
context.update(extra_context)
return render(request, template, context)
def get_events(request):
if request.user.is_authenticated:
coder = request.user.coder
else:
coder = None
categories = request.POST.getlist('categories')
ignore_filters = request.POST.getlist('ignore_filters')
has_filter = False
referer = request.META.get('HTTP_REFERER')
if referer:
parsed = urlparse(referer)
query_dict = parse_qs(parsed.query)
as_coder = query_dict.get('as_coder')
if as_coder and request.user.has_perm('as_coder'):
coder = Coder.objects.get(user__username=as_coder[0])
has_filter = 'filter' in query_dict
categories = query_dict.get('filter', categories)
tzname = get_timezone(request)
offset = get_timezone_offset(tzname)
query = Q()
if coder:
query = coder.get_contest_filter(categories, ignore_filters)
elif has_filter:
query = Coder.get_contest_filter(None, categories, ignore_filters)
if not coder or coder.settings.get('calendar_filter_long', True):
if categories == ['calendar'] and '0' not in ignore_filters:
query &= Q(duration_in_secs__lt=timedelta(days=1).total_seconds())
past_action = settings.PAST_CALENDAR_DEFAULT_ACTION_
if coder:
past_action = coder.settings.get('past_action_in_calendar',
past_action)
start_time = arrow.get(request.POST.get('start', timezone.now())).datetime
end_time = arrow.get(
request.POST.get('end',
timezone.now() + timedelta(days=31))).datetime
query = query & Q(end_time__gte=start_time) & Q(start_time__lte=end_time)
search_query = request.POST.get('search_query', None)
if search_query:
search_query_re = verify_regex(search_query)
query &= Q(host__iregex=search_query_re) | Q(
title__iregex=search_query_re)
party_slug = request.POST.get('party')
if party_slug:
party = get_object_or_404(Party.objects.for_user(request.user),
slug=party_slug)
query = Q(rating__party=party) & query
contests = Contest.objects if party_slug else Contest.visible
contests = contests.select_related('resource')
contests = contests.annotate(has_statistics=Exists('statistics'))
contests = contests.order_by('start_time', 'title')
if past_action == 'hide':
contests = contests.filter(end_time__gte=timezone.now())
now = timezone.now()
try:
result = []
for contest in contests.filter(query):
color = contest.resource.color
if past_action not in ['show', 'hide'] and contest.end_time < now:
color = contest.resource.info.get('get_events', {}).get(
'colors', {}).get(past_action, color)
c = {
'id':
contest.pk,
'title':
contest.title,
'host':
contest.host,
'url':
(reverse('ranking:standings',
args=(slug(contest.title),
contest.pk)) if contest.has_statistics else
(contest.standings_url if contest.standings_url
and contest.end_time < now else contest.url)),
'start':
(contest.start_time +
timedelta(minutes=offset)).strftime("%Y-%m-%dT%H:%M:%S"),
'end':
(contest.end_time +
timedelta(minutes=offset)).strftime("%Y-%m-%dT%H:%M:%S"),
'countdown':
contest.next_time_to(now),
'hr_duration':
contest.hr_duration,
'color':
color,
'icon':
contest.resource.icon,
}
result.append(c)
except Exception as e:
return JsonResponse(
{'message': f'query = `{search_query}`, error = {e}'},
safe=False,
status=400)
return JsonResponse(result, safe=False)