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)