def get_district_wise_dialogues_count(): return Dialogue.objects.all().values('district__name').annotate( count=Count('district')).order_by('count')
def get_objects_for_user( user, perms, klass=None, use_groups=True, any_perm=False, with_superuser=True, accept_global_perms=True, perms_filter="pk__in", ): """Return queryset with required permissions.""" if isinstance(perms, str): perms = [perms] ctype = None app_label = None codenames = set() # Compute codenames set and ctype if possible for perm in perms: if "." in perm: new_app_label, codename = perm.split(".", 1) if app_label is not None and app_label != new_app_label: raise MixedContentTypeError( "Given perms must have same app label " "({} != {})".format(app_label, new_app_label) ) else: app_label = new_app_label else: codename = perm codenames.add(codename) if app_label is not None: new_ctype = ContentType.objects.get( app_label=app_label, permission__codename=codename ) if ctype is not None and ctype != new_ctype: raise MixedContentTypeError( "ContentType was once computed to be {} and another " "one {}".format(ctype, new_ctype) ) else: ctype = new_ctype # Compute queryset and ctype if still missing if ctype is None and klass is not None: queryset = _get_queryset(klass) ctype = ContentType.objects.get_for_model(queryset.model) elif ctype is not None and klass is None: queryset = _get_queryset(ctype.model_class()) elif klass is None: raise WrongAppError("Cannot determine content type") else: queryset = _get_queryset(klass) if ctype.model_class() != queryset.model and perms_filter == "pk__in": raise MixedContentTypeError( "Content type for given perms and " "klass differs" ) # At this point, we should have both ctype and queryset and they should # match which means: ctype.model_class() == queryset.model # we should also have `codenames` list # First check if user is superuser and if so, return queryset immediately if with_superuser and user.is_superuser: return queryset # Check if the user is anonymous. The # django.contrib.auth.models.AnonymousUser object doesn't work for queries # and it's nice to be able to pass in request.user blindly. if user.is_anonymous: user = get_anonymous_user() global_perms = set() has_global_perms = False # a superuser has by default assigned global perms for any if accept_global_perms and with_superuser: for code in codenames: if user.has_perm(ctype.app_label + "." + code): global_perms.add(code) for code in global_perms: codenames.remove(code) # prerequisite: there must be elements in global_perms otherwise just # follow the procedure for object based permissions only AND # 1. codenames is empty, which means that permissions are ONLY set # globally, therefore return the full queryset. # OR # 2. any_perm is True, then the global permission beats the object # based permission anyway, therefore return full queryset if global_perms and (not codenames or any_perm): return queryset # if we have global perms and still some object based perms differing # from global perms and any_perm is set to false, then we have to flag # that global perms exist in order to merge object based permissions by # user and by group correctly. Scenario: global perm change_xx and # object based perm delete_xx on object A for user, and object based # permission delete_xx on object B for group, to which user is # assigned. # get_objects_for_user(user, [change_xx, delete_xx], use_groups=True, # any_perm=False, accept_global_perms=True) must retrieve object A and # B. elif global_perms and codenames: has_global_perms = True # Now we should extract list of pk values for which we would filter # queryset user_model = get_user_obj_perms_model(queryset.model) user_obj_perms_queryset = user_model.objects.filter( Q(user=user) | Q(user=get_anonymous_user()) ).filter(permission__content_type=ctype) if codenames: user_obj_perms_queryset = user_obj_perms_queryset.filter( permission__codename__in=codenames ) direct_fields = ["content_object__pk", "permission__codename"] generic_fields = ["object_pk", "permission__codename"] if user_model.objects.is_generic(): user_fields = generic_fields else: user_fields = direct_fields if use_groups: group_model = get_group_obj_perms_model(queryset.model) group_filters = { "permission__content_type": ctype, "group__{}".format( get_user_model().groups.field.related_query_name() ): user, } if codenames: group_filters.update( { "permission__codename__in": codenames, } ) groups_obj_perms_queryset = group_model.objects.filter(**group_filters) if group_model.objects.is_generic(): group_fields = generic_fields else: group_fields = direct_fields if not any_perm and codenames and not has_global_perms: user_obj_perms = user_obj_perms_queryset.values_list(*user_fields) groups_obj_perms = groups_obj_perms_queryset.values_list(*group_fields) data = list(user_obj_perms) + list(groups_obj_perms) # sorting/grouping by pk (first in result tuple) data = sorted(data, key=lambda t: t[0]) pk_list = [] for pk, group in groupby(data, lambda t: t[0]): obj_codenames = set((e[1] for e in group)) if codenames.issubset(obj_codenames): pk_list.append(pk) objects = queryset.filter(**{perms_filter: pk_list}) return objects if not any_perm and len(codenames) > 1: counts = user_obj_perms_queryset.values(user_fields[0]).annotate( object_pk_count=Count(user_fields[0]) ) user_obj_perms_queryset = counts.filter(object_pk_count__gte=len(codenames)) values = user_obj_perms_queryset.values_list(user_fields[0], flat=True) if user_model.objects.is_generic(): values = list(values) query = Q(**{perms_filter: values}) if use_groups: values = groups_obj_perms_queryset.values_list(group_fields[0], flat=True) if group_model.objects.is_generic(): values = list(values) query |= Q(**{perms_filter: values}) return queryset.filter(query)
def profile_distribution(course_id, feature): """ Retrieve distribution of students over a given feature. feature is one of AVAILABLE_PROFILE_FEATURES. Returns a ProfileDistribution instance. NOTE: no_data will appear as a key instead of None/null to adhere to the json spec. data types are EASY_CHOICE or OPEN_CHOICE """ if feature not in AVAILABLE_PROFILE_FEATURES: raise ValueError( u"unsupported feature requested for distribution u'{}'".format( feature) ) prd = ProfileDistribution(feature) if feature in _EASY_CHOICE_FEATURES: prd.type = 'EASY_CHOICE' if feature == 'gender': raw_choices = UserProfile.GENDER_CHOICES elif feature == 'level_of_education': raw_choices = UserProfile.LEVEL_OF_EDUCATION_CHOICES # short name and display name (full) of the choices. choices = [(short, full) for (short, full) in raw_choices] + [('no_data', 'No Data')] def get_filter(feature, value): """ Get the orm filter parameters for a feature. """ return { 'gender': {'user__profile__gender': value}, 'level_of_education': {'user__profile__level_of_education': value}, }[feature] def get_count(feature, value): """ Get the count of enrolled students matching the feature value. """ return CourseEnrollment.objects.filter( course_id=course_id, is_active=True, **get_filter(feature, value) ).count() distribution = {} for (short, full) in choices: # handle no data case if short == 'no_data': distribution['no_data'] = 0 distribution['no_data'] += get_count(feature, None) distribution['no_data'] += get_count(feature, '') else: distribution[short] = get_count(feature, short) prd.data = distribution prd.choices_display_names = dict(choices) elif feature in _OPEN_CHOICE_FEATURES: prd.type = 'OPEN_CHOICE' profiles = UserProfile.objects.filter( user__courseenrollment__course_id=course_id, user__courseenrollment__is_active=True ) query_distribution = profiles.values( feature).annotate(Count(feature)).order_by() # query_distribution is of the form [{'featureval': 'value1', 'featureval__count': 4}, # {'featureval': 'value2', 'featureval__count': 2}, ...] distribution = dict((vald[feature], vald[feature + '__count']) for vald in query_distribution) # distribution is of the form {'value1': 4, 'value2': 2, ...} # change none to no_data for valid json key if None in distribution: # django does not properly count NULL values when using annotate Count # so # distribution['no_data'] = distribution.pop(None) # would always be 0. # Correctly count null values distribution['no_data'] = profiles.filter( **{feature: None} ).count() prd.data = distribution prd.validate() return prd
def get_daily_count_list(): today = datetime.date.today() fifteen_days_ago = today - datetime.timedelta(days=15) return Dialogue.objects.filter( dialogue_date__gte=fifteen_days_ago).values('dialogue_date').annotate( count=Count('dialogue_date')).order_by('dialogue_date')
def get_all_nickname(request, person_id): person = get_object_or_404(Person, id=person_id) personnick_list = PersonNick.objects.filter(person=person).annotate( num_userlike=Count('user_like')).order_by('-num_userlike') return render(request, 'nickname/partial/nicknames.html', {'personnick_list':personnick_list})
def bill(request): """ It generates a bill for the user which lists all the items, their quantity , subtotal and then adds it to the surcharges and generates the Grand total. Argument:Http Request Return:Render Bill """ id = request.GET["order_id"] purchase_order = PurchaseOrder.objects.filter(id=id) purchased_item = (PurchasedItem.objects.filter( purchase_order__in=purchase_order).values( "item__category__name", "item__category", "item__category__parent__name", "item__category__unit", "item__category__unit__unit", ).order_by("item__category").distinct()) purchased_item_obj = (PurchasedItem.objects.filter( purchase_order__in=purchase_order).values( "item__name", "item__category", "qty", "price_per_unit").order_by("item__category")) cost = (PurchasedItem.objects.filter( purchase_order__in=purchase_order).values( "price", "item__category", "item__name", "item__category__parent__name", ).order_by("item__category")) bill_values = [] for category in purchased_item: flag1 = 1 list = [] list.append(category["item__category__name"]) item_names = [] for item in purchased_item_obj: if category["item__category"] == item["item__category"]: if flag1 == 1: item_names.append(":") item_names.append(item["item__name"]) flag1 = 0 else: item_names.append(",") item_names.append(item["item__name"]) flag1 = 1 item_qty = [] for qty in purchased_item_obj: if category["item__category"] == qty["item__category"]: if flag1 == 1: item_qty.append(qty["qty"]) flag1 = 0 else: item_qty.append(",") item_qty.append(qty["qty"]) if category["item__category__unit"]: item_qty.append(category["item__category__unit__unit"]) else: item_qty.append("no_unit") flag1 = 1 price_unit = [] for price_per in purchased_item_obj: if category["item__category"] == price_per["item__category"]: if flag1 == 1: price_unit.append(price_per["price_per_unit"]) flag1 = 0 else: price_unit.append(",") price_unit.append(price_per["price_per_unit"]) total = 0 for itemcost in cost: if category["item__category"] == itemcost["item__category"]: total = total + itemcost["price"] list.append(item_names) list.append(item_qty) list.append(price_unit) list.append(total) bill_values.append(list) taxes_applied = TaxesApplied.objects.filter( purchase_order__in=purchase_order).values("surcharge", "tax", "surcharge_name", "surcharge_value") TaxesApplied.objects.filter(purchase_order__in=purchase_order).aggregate( Count("id")) bill = Bill.objects.values("total_cost", "totalplusdelivery", "grand_total", "delivery_charges").get(purchase_order=id) total_cost = bill["total_cost"] totalplusdelivery = bill["totalplusdelivery"] grand_total = bill["grand_total"] delivery_charges = bill["delivery_charges"] purchase_order_obj = PurchaseOrder.objects.values( "buyer", "buyer__first_name", "buyer__last_name", "reference", "reference_date", "delivery_address", "organisation", "date_time", "total_discount", "buyer__customer__title", ).get(id=id) total_discount = purchase_order_obj["total_discount"] taxes_applied_obj = TaxesApplied.objects.filter( purchase_order__in=purchase_order).aggregate(Count("id")) suspense_order = SuspenseOrder.objects.values("distance_estimated").filter( purchase_order=id) total_distance = 0 if suspense_order: for distance in suspense_order: total_distance = total_distance + distance["distance_estimated"] if total_distance == 0: if total_discount == 0: tax_count = taxes_applied_obj["id__count"] + 2 else: tax_count = taxes_applied_obj["id__count"] + 3 else: if total_discount == 0: tax_count = taxes_applied_obj["id__count"] + 4 else: tax_count = taxes_applied_obj["id__count"] + 5 else: if total_discount == 0: tax_count = taxes_applied_obj["id__count"] + 2 else: tax_count = taxes_applied_obj["id__count"] + 3 if taxes_applied_obj["id__count"] == 0: tax_count = tax_count + 1 buyer = purchase_order_obj["buyer"] address = Customer.objects.values( "address__street_address", "address__district", "address__pin", "address__province", ).get(user=buyer) organisation_id = purchase_order_obj["organisation"] date = purchase_order_obj["date_time"] customer_obj = Customer.objects.values("company").get(user=buyer) customer_gst_details = Customer.objects.values( "gst_in", "state", "state_code").get(user=buyer) admin_organisations = AdminOrganisations.objects.values( "pan_no", "stc_no", "gst_in", "state", "state_code").get(id=organisation_id) voucherid = VoucherId.objects.values("purchase_order_of_session").filter( purchase_order=id)[0] total_in_words = num2eng(grand_total) ref_letter = YOUR_LETTER_No header = HeaderFooter.objects.values("header").get(is_active=True) footer = HeaderFooter.objects.values("footer").get(is_active=True) return render( request, "prints/bill.html", { "admin_org": admin_organisations, "id": voucherid["purchase_order_of_session"], "ref": purchase_order_obj, "date": date, "purchase_order": purchase_order, "address": address, "total_cost": total_cost, "grand_cost": grand_total, "taxes_applied": taxes_applied, "customer_gst_details": customer_gst_details, "buyer": purchase_order_obj, "buyer_name": customer_obj, "site": purchase_order_obj, "delivery_charges": delivery_charges, "total_discount": total_discount, "tax_count": tax_count, "bill_values": bill_values, "header": header, "footer": footer, "totalplusdelivery": totalplusdelivery, "total_in_words": total_in_words, "ref_letter": ref_letter, }, )
def get_context_data(self, *args, **kwargs): context = super(StatsView,self).get_context_data(*args, **kwargs) resource = self.object completion_counts = resource.attempts.values('completion_status').order_by('completion_status').annotate(number=Count('completion_status')) completion_dict = {x['completion_status']: x['number'] for x in completion_counts} context['completion_counts'] = [ (label, value, completion_dict.get(value,0)) for value,label in COMPLETION_STATUSES ] context['data'] = resource.live_stats_data() return context
from datetime import datetime, timedelta, timezone from django.db.models import Min, Q, Count, Sum, Max #from django.db.models.functions import ExtractWeek, ExtractYear #import time #import urllib #from urllib import request #import json #from fb_app.scores import Scores #from urllib.request import Request, urlopen week = Week.objects.get(current=True) print('****** pick summary before update ******') #for p in Picks.objects.filter(week=week, player__league=l).values('player__name__username').annotate(Count('player')): for p in Picks.objects.filter( week=week).values('player__name__username').annotate(Count('player')): print(p.get('player__name__username'), ' : ', p.get('player__count')) f = open("picksweek" + str(week.week) + "pre-fix.txt", "w") #for pick in Picks.objects.filter(week=week, player__league=l): for pick in Picks.objects.filter(week=week): f.write(pick.player.name.username + ',' + str(pick.pick_num) + ',' + str(pick.team.nfl_abbr) + '\n') #f.write('\n') f.close() leagues = [ 'Golfers', ] games = [ "20205NEDEN",
def handle(self, *args, **options): from_date = options['from_date'] to_date = options['to_date'] static_qs = HouseTS.objects.filter( created__gte=from_date, created__lte=to_date, **self.could_be_house ).values( 'vendor_house_id', *self.should_be_static_fields ).annotate( count=Count('id'), ).order_by( 'vendor_house_id' ) static_houses = {} total_houses = 0 total_invalid_houses = 0 for house in static_qs: house_id = house['vendor_house_id'] # print(' {} x {} - {}'.format(house_id, house['count'], house['building_type'])) if house['vendor_house_id'] in static_houses: static_houses[house_id].append(house['count']) total_invalid_houses += 1 else: static_houses[house_id] = [house['count']] total_houses += 1 for house_id in static_houses: if len(static_houses[house_id]) > 1: print('[STATIC] House {} changed {} ({}) times!!'.format(house_id, len(static_houses[house_id]), static_houses[house_id])) print('[STATIC] Invald house: {}/{}'.format(total_invalid_houses, total_houses)) # min should be bigger than max/2 annotates = {} for field in self.should_be_small_diff_fields: annotates['max_{}'.format(field)] = Max(field) annotates['min_{}'.format(field)] = Min(field) small_diff_qs = HouseTS.objects.filter( created__gte=from_date, created__lte=to_date, **self.could_be_house ).values( 'vendor_house_id', ).annotate( count=Count('id'), **annotates, ).order_by( 'vendor_house_id' ) total_houses = 0 total_invalid_houses = 0 for house in small_diff_qs: is_invalid = False total_houses += 1 for field in self.should_be_small_diff_fields: max_value = house['max_{}'.format(field)] min_value = house['min_{}'.format(field)] if max_value is not None and min_value is not None: if max_value / 2 > min_value and min_value >= 0: is_invalid = True print('[SMALL] House {} field {} change too much, from {} to {}'.format( house['vendor_house_id'], field, min_value, max_value )) if is_invalid: total_invalid_houses += 1 print('[SMALL] Invald house: {}/{}'.format(total_invalid_houses, total_houses))
def get_queryset(self): # Create the queryset qs = super().get_queryset() # Annotate queryset with crime counts per neighborhood called cc, # then return it return qs.annotate(cc=Count("crime_reports"))
def changelist_view(self, request, extra_context=None): self.groupped_ads_count = defaultdict(Counter) query = Ad.objects.filter(bank__isnull=False).values_list('bank_id', 'status').order_by().annotate(Count('bank_id'), Count('status')) for bank_id, status, count, count in query: if status == 1: self.groupped_ads_count[bank_id]['published'] += count self.groupped_ads_count[bank_id]['total'] += count return super(BankAdmin, self).changelist_view(request, extra_context=None)
def get_context_data(self, *args, **kwargs): id_type = self.request.session.get('typeoffer') id_city = self.request.session.get('city') context = super(OfferSearchListView , self).get_context_data(*args, **kwargs) context['destaques'] = Offer.objects.select_related('city').filter(highlight=True, options__start_time__lte=datetime.today(), options__end_time__gte=datetime.today()).distinct() context['categories'] = Category.objects.filter(type__id=id_type).annotate(total=Count('subcategories__offer')) return context
def get_context_data(self, *args, **kwargs): context = super(OfferListView, self).get_context_data(*args, **kwargs) context['destaques'] = Offer.objects.select_related('city').filter(highlight=True, options__start_time__lte=datetime.today(), options__end_time__gte=datetime.today()).distinct() if self.category: context['category'] = self.category context['subcategories'] = SubCategory.objects.filter(category=self.category).annotate(total=Count('offer')) context['interests'] = Interest.objects.filter(category=self.category).annotate(total=Count('offer')) else: id_type = self.request.session.get('typeoffer') id_city = self.request.session.get('city') context['ultimas_count'] = Offer.objects.select_related('city').filter(subcategory__category__type__id=id_type, city__id=id_city).count() context['mais_vendidos_count'] = Offer.objects.select_related('city').filter(subcategory__category__type__id=id_type, city__id=id_city).count() context['categories'] = Category.objects.filter(type__id=id_type).annotate(total=Count('subcategories__offer')) return context
def get_top_tags(): return Tag.objects.annotate(count_of_posts=Count('posts')) \ .order_by('count_of_posts')[:9] #annotate привязывает, order_by return
trial_court_county=c['Trial Court County'], v=c['v.'], appellate_briefs='', calendars='', case_events='', parties='', trial_court_information='') except Exception as e: print(2, e) try: if a.case_type not in ['11.07', '11.07 HC', '11.071', '1107-HC']: a.case_type = ' '.join([word[0].upper() + word[1:].lower() for word in a.case_type.split() if word not in ['to', 'or', 'for', 'of', 'with', 'under']]) a.save() except Exception as e: print(3, e) lawyers_list = Lawyer.objects.annotate(cases_count=Count('case')).order_by('-cases_count') file = open(settings.MEDIA_ROOT + '/lawyers.txt', 'w', encoding='utf-8') for lawyer in lawyers_list: file.write(','.join([lawyer.bar_card, lawyer.full_name, str(lawyer.cases_count)]) + '\n') file.close() cases_list = Case.objects.values('case_type').exclude(case_type='').order_by('case_type') file = open(settings.MEDIA_ROOT + '/case_types.txt', 'w', encoding='utf-8') file.write(str(','.join(sorted(set(list([case['case_type'] for case in cases_list])))))) file.close() file = open(settings.MEDIA_ROOT + '/case_types.txt', encoding='utf-8', errors='ignore') cases = file.read().split(',') file.close() for case in cases:
def get_post_most_comments(count=5): """ add all comment for post and sort and slicing """ return Post.objects.annotate(total_comments=Count('comments'))\ .order_by('-total_comments')[:count]
def search_lawyers(request): user_date = datetime.now() user_date = formats.date_format(user_date, "SHORT_DATETIME_FORMAT") first_name = request.GET.get('first_name', '') last_name = request.GET.get('last_name', '') bar_card = request.GET.get('bar_card', '') request.session['casetype'] = " " if first_name or last_name or bar_card: lawyers_list = Lawyer.objects.filter( first_name__icontains=first_name).filter( last_name__icontains=last_name).filter( bar_card__icontains=bar_card).annotate( cases_count=Count('case')).order_by('-cases_count') else: try: # request.session['paid'] ="True" file = open(settings.MEDIA_ROOT + '/lawyers.txt', encoding='utf-8', errors='ignore') # lawyers_list = [{'bar_card': lawyer.split(',')[0], # 'full_name': lawyer.split(',')[1], # 'cases_count': lawyer.split(',')[2], # 'first_name' : lawyer.split(',')[1].split(' ')[0]+" "+lawyer.split(',')[1].split(' ')[1], # 'last_name' : lawyer.split(',')[1].replace(lawyer.split(',')[1].split(' ')[0]+" "+lawyer.split(',')[1].split(' ')[1],""), # } for lawyer in file.readlines()] lawyers_list = [] index = 0 for lawyer in file.readlines(): index = index + 1 list = {'index': index, 'bar_card': lawyer.split(',')[0], 'full_name': lawyer.split(',')[1], 'cases_count': lawyer.split(',')[2], # 'first_name' : lawyer.split(',')[1].split()[0]+" "+lawyer.split(',')[1].split()[1], # 'last_name' : lawyer.split(',')[1].replace(lawyer.split(',')[1].split(' ')[0]+" "+lawyer.split(',')[1].split(' ')[1],""), } lawyers_list.append(list) # for i in range(5): # lawyers_list.append([{'bar_card':'1','full_name':'1','cases_count':'end_page','first_name':'ddd','last_name':'sssss'}]) # with open(settings.MEDIA_ROOT + '/lawyers.txt', mode="r",encoding="utf-8") as f: # reader=csv.reader(f,delimiter=",") # for row_index, row_data in enumerate(reader): # bar_card,full_name,end_page=row_data # lawyers_list.append([{'bar_card':bar_card,'full_name':full_name,'cases_count':end_page,'first_name':full_name.split(' ')[0]+" "+full_name.split(' ')[1],'last_name':full_name.replace(full_name.split(' ')[0]+" "+full_name.split(' ')[1],"")}]) # for lawyer in file.readlines(): # lawyers_list.append({'bar_card': lawyer.split(',')[0],'full_name': lawyer.split(',')[1],'cases_count': lawyer.split(',')[2],'first_name' : lawyer.split(',')[1].split(' ')[0]+" "+lawyer.split(',')[1].split(' ')[1],'last_name' : lawyer.split(',')[1].replace(lawyer.split(',')[1].split(' ')[0]+" "+lawyer.split(',')[1].split(' ')[1],""),'session': request.session['paid']}) file.close() except: lawyers_list = [] page = request.GET.get('page') paginator = Paginator(lawyers_list, 10) lawyers_list = paginator.get_page(page) page_number = lawyers_list.number request.session['pagenumber'] = page_number if not request.session.session_key: request.session['paid'] = "false" if not request.user.username: request.session['paid'] = "false" else: user_date = request.user.date_joined + timedelta(days=30) user_date = formats.date_format(user_date, "SHORT_DATETIME_FORMAT") now = datetime.now() now = formats.date_format(now, "SHORT_DATETIME_FORMAT") expiredate = datetime.strptime('Feb 1 2019 11:13PM', '%b %d %Y %I:%M%p') if now > user_date: boolean = "true" disAllow_groups = Group.objects.get(name='Disallow') disAllow_groups.user_set.add(request.user) Addgroup = Group.objects.get(name='Allowed') Addgroup.user_set.remove(request.user) else: boolean = "false" disAllow_groups = Group.objects.get(name='Allowed') disAllow_groups.user_set.add(request.user) Addgroup = Group.objects.get(name='Disallow') Addgroup.user_set.remove(request.user) groupname = [] for g in request.user.groups.all(): groupname.append(g.name) allow = "Allowed" if allow in groupname: request.session['paid'] = "true" else: request.session['paid'] = "false" if request.user.username == "admin": request.session['paid'] = "true" context = {'lawyers': lawyers_list, 'session': request.session['paid'], 'expiredate': user_date} return render(request, 'search_lawyers.html', context)
def users_with_translations_counts(start_date=None, query_filters=None, limit=100): """ Returns contributors list, sorted by count of their translations. Every user instance has the following properties: * translations_count * translations_approved_count * translations_rejected_count * translations_unapproved_count * user_role All counts will be returned from start_date to now(). :param date start_date: start date for translations. :param django.db.models.Q query_filters: filters contributors by given query_filters. :param int limit: limit results to this number. """ # Collect data for faster user stats calculation. user_stats = {} translations = Translation.objects.all() if start_date: translations = translations.filter(date__gte=start_date) if query_filters: translations = translations.filter(query_filters) # Count('user') returns 0 if the user is None. # See https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#values. translations = translations.values( "user", "approved", "rejected").annotate(count=Count("approved")) for translation in translations: count = translation["count"] user = translation["user"] if translation["approved"]: status = "approved" elif translation["rejected"]: status = "rejected" else: status = "unreviewed" if user not in user_stats: user_stats[user] = { "total": 0, "approved": 0, "unreviewed": 0, "rejected": 0, } user_stats[user]["total"] += count user_stats[user][status] += count # Collect data for faster user role detection. managers = defaultdict(set) translators = defaultdict(set) locales = Locale.objects.prefetch_related( Prefetch("managers_group__user_set", to_attr="fetched_managers"), Prefetch("translators_group__user_set", to_attr="fetched_translators"), ) for locale in locales: for user in locale.managers_group.fetched_managers: managers[user].add(locale.code) for user in locale.translators_group.fetched_translators: translators[user].add(locale.code) # Assign properties to user objects. contributors = User.objects.filter(pk__in=user_stats.keys()) # Exclude deleted users. contributors = contributors.filter(is_active=True) if None in user_stats.keys(): contributors = list(contributors) contributors.append( User(username="******", first_name="Imported", email="imported")) for contributor in contributors: user = user_stats[contributor.pk] contributor.translations_count = user["total"] contributor.translations_approved_count = user["approved"] contributor.translations_rejected_count = user["rejected"] contributor.translations_unapproved_count = user["unreviewed"] if contributor.pk is None: contributor.user_role = "System User" else: contributor.user_role = contributor.role(managers, translators) contributors_list = sorted(contributors, key=lambda x: -x.translations_count) if limit: contributors_list = contributors_list[:limit] return contributors_list
def lawyers_by_case(request): user_date = datetime.now() user_date = formats.date_format(user_date, "SHORT_DATETIME_FORMAT") case_type = request.GET.get('case_type', '') group = request.GET.get('group', '') if not request.session.session_key: request.session['paid'] = "false" if not request.user.username: request.session['paid'] = "false" else: user_date = request.user.date_joined + timedelta(days=30) user_date = formats.date_format(user_date, "SHORT_DATETIME_FORMAT") now = datetime.now() now = formats.date_format(now, "SHORT_DATETIME_FORMAT") expiredate = datetime.strptime('Feb 1 2019 11:13PM', '%b %d %Y %I:%M%p') if now > user_date: boolean = "true" disAllow_groups = Group.objects.get(name='Disallow') disAllow_groups.user_set.add(request.user) Addgroup = Group.objects.get(name='Allowed') Addgroup.user_set.remove(request.user) else: boolean = "false" disAllow_groups = Group.objects.get(name='Allowed') disAllow_groups.user_set.add(request.user) Addgroup = Group.objects.get(name='Disallow') Addgroup.user_set.remove(request.user) groupname = [] for g in request.user.groups.all(): groupname.append(g.name) allow = "Allowed" if allow in groupname: request.session['paid'] = "true" else: request.session['paid'] = "false" if request.user.username == "admin": request.session['paid'] = "true" if group: cases = case_type.split('|') lawyers_list = Lawyer.objects.filter(case__case_type__in=cases). \ annotate(cases_count=Count('case')).order_by('-cases_count') for l, lawyer in enumerate(lawyers_list): lawyers_list[l].all_cases_count = lawyer.case_set.count() request.session['pagenumber'] = 0 request.session['casetype'] = group page = request.GET.get('page') paginator = Paginator(lawyers_list, 10) lawyers_list = paginator.get_page(page) context = {'lawyers': lawyers_list, 'case_type': group, 'group': case_type, 'session': request.session['paid'], 'casetype': request.session['casetype'], 'expiredate': user_date} else: file_name = case_type.replace('\\', ' ').replace('/', ' ') file = open(settings.MEDIA_ROOT + '\\' + file_name + '.txt', encoding='utf-8', errors='ignore') lawyers_list = [] fullname_lists = [] for lawyer in file.readlines(): lawyers_list.append(json.loads(lawyer)) request.session['pagenumber'] = 0 request.session['casetype'] = case_type page = request.GET.get('page') paginator = Paginator(lawyers_list, 10) lawyers_list = paginator.get_page(page) context = {'lawyers': lawyers_list, 'case_type': case_type, 'session': request.session['paid'], 'casetype': request.session['casetype'], 'expiredate': user_date} return render(request, 'lawyers_by_case.html', context)
def test_annotate_aggregate_order(self): # create a blog of type BlogA blog = BlogA.objects.create(name='B1', info='i1') # create two blog entries in BlogA entry1 = blog.blogentry_set.create(text='bla') entry2 = BlogEntry.objects.create(blog=blog, text='bla2') # create some blogs of type BlogB to make the BlogBase table data really polymorphic o = BlogB.objects.create(name='Bb1') o = BlogB.objects.create(name='Bb2') o = BlogB.objects.create(name='Bb3') qs = BlogBase.objects.annotate(entrycount=Count('BlogA___blogentry')) assert len(qs)==4 for o in qs: if o.name=='B1': assert o.entrycount == 2 else: assert o.entrycount == 0 x = BlogBase.objects.aggregate(entrycount=Count('BlogA___blogentry')) assert x['entrycount'] == 2 # create some more blogs for next test b2 = BlogA.objects.create(name='B2', info='i2') b2 = BlogA.objects.create(name='B3', info='i3') b2 = BlogA.objects.create(name='B4', info='i4') b2 = BlogA.objects.create(name='B5', info='i5') ### test ordering for field in all entries expected = ''' [ <BlogB: id 4, name (CharField) "Bb3">, <BlogB: id 3, name (CharField) "Bb2">, <BlogB: id 2, name (CharField) "Bb1">, <BlogA: id 8, name (CharField) "B5", info (CharField) "i5">, <BlogA: id 7, name (CharField) "B4", info (CharField) "i4">, <BlogA: id 6, name (CharField) "B3", info (CharField) "i3">, <BlogA: id 5, name (CharField) "B2", info (CharField) "i2">, <BlogA: id 1, name (CharField) "B1", info (CharField) "i1"> ]''' x = '\n' + repr(BlogBase.objects.order_by('-name')) assert x == expected ### test ordering for field in one subclass only # MySQL and SQLite return this order expected1=''' [ <BlogA: id 8, name (CharField) "B5", info (CharField) "i5">, <BlogA: id 7, name (CharField) "B4", info (CharField) "i4">, <BlogA: id 6, name (CharField) "B3", info (CharField) "i3">, <BlogA: id 5, name (CharField) "B2", info (CharField) "i2">, <BlogA: id 1, name (CharField) "B1", info (CharField) "i1">, <BlogB: id 2, name (CharField) "Bb1">, <BlogB: id 3, name (CharField) "Bb2">, <BlogB: id 4, name (CharField) "Bb3"> ]''' # PostgreSQL returns this order expected2=''' [ <BlogB: id 2, name (CharField) "Bb1">, <BlogB: id 3, name (CharField) "Bb2">, <BlogB: id 4, name (CharField) "Bb3">, <BlogA: id 8, name (CharField) "B5", info (CharField) "i5">, <BlogA: id 7, name (CharField) "B4", info (CharField) "i4">, <BlogA: id 6, name (CharField) "B3", info (CharField) "i3">, <BlogA: id 5, name (CharField) "B2", info (CharField) "i2">, <BlogA: id 1, name (CharField) "B1", info (CharField) "i1"> ]''' x = '\n' + repr(BlogBase.objects.order_by('-BlogA___info')) assert x == expected1 or x == expected2
def _get_for_month(self, model_manager): return model_manager.values('created_at__month') \ .annotate(total=Count('created_at__month')) \ .order_by('created_at__month')
def quoted_bill(request): """ It generates a proforma bill for the user which lists all the items, their quantity , subtotal and then adds it to the surcharges and generates the Grand total. Argument:Http Request Return:Render Proforma Bill """ quoted_order_id = request.GET["quoted_order_id"] quoted_order = QuotedOrder.objects.filter(id=quoted_order_id) quoted_item = (QuotedItem.objects.filter( quoted_order=quoted_order_id).values( "item__category__name", "item__category", "item__category__unit", "item__category__unit__unit", ).order_by("item__category").distinct()) quoted_item_obj = (QuotedItem.objects.filter( quoted_order=quoted_order_id).values( "item__name", "item__category", "qty", "price_per_unit").order_by("item__category")) cost = (QuotedItem.objects.filter(quoted_order=quoted_order_id).values( "price", "item__category", "item__name").order_by("item__category")) quoted_order_sessionid = QuotedOrderofSession.objects.filter( quoted_order_id=quoted_order_id).values("quoted_order_session")[0] bill_values = [] for category in quoted_item: flag1 = 1 list = [] list.append(category["item__category__name"]) item_names = [] for item in quoted_item_obj: if category["item__category"] == item["item__category"]: if flag1 == 1: item_names.append(":") item_names.append(item["item__name"]) flag1 = 0 else: item_names.append(",") item_names.append(item["item__name"]) flag1 = 1 item_qty = [] for qty in quoted_item_obj: if category["item__category"] == qty["item__category"]: if flag1 == 1: item_qty.append(qty["qty"]) flag1 = 0 else: item_qty.append(",") item_qty.append(qty["qty"]) if category["item__category__unit"]: item_qty.append(category["item__category__unit__unit"]) else: item_qty.append("no_unit") flag1 = 1 price_unit = [] for price_per in quoted_item_obj: if category["item__category"] == price_per["item__category"]: if flag1 == 1: price_unit.append(price_per["price_per_unit"]) flag1 = 0 else: price_unit.append(",") price_unit.append(price_per["price_per_unit"]) total = 0 for itemcost in cost: if category["item__category"] == itemcost["item__category"]: total = total + itemcost["price"] list.append(item_names) list.append(item_qty) list.append(price_unit) list.append(total) bill_values.append(list) taxes_applied = QuotedTaxesApplied.objects.filter( quoted_order=quoted_order).values("surcharge", "tax", "surcharge_name", "surcharge_value") bill = QuotedBill.objects.values( "total_cost", "grand_total", "delivery_charges", "totalplusdelivery").get(quoted_order=quoted_order_id) total_cost = bill["total_cost"] grand_total = bill["grand_total"] delivery_charges = bill["delivery_charges"] totalplusdelivery = bill["totalplusdelivery"] quoted_order_obj = QuotedOrder.objects.values( "buyer", "buyer__first_name", "buyer__last_name", "reference", "delivery_address", "organisation", "date_time", "total_discount", "buyer__customer__title", "reference_date", ).get(id=quoted_order_id) total_discount = quoted_order_obj["total_discount"] taxes_applied_obj = QuotedTaxesApplied.objects.filter( quoted_order=quoted_order).aggregate(Count("id")) try: suspense_order = QuotedSuspenseOrder.objects.values( "distance_estimated").get(quoted_order=quoted_order_id) if suspense_order["distance_estimated"] == 0: if total_discount == 0: tax_count = taxes_applied_obj["id__count"] + 2 else: tax_count = taxes_applied_obj["id__count"] + 3 else: if total_discount == 0: tax_count = taxes_applied_obj["id__count"] + 4 else: tax_count = taxes_applied_obj["id__count"] + 5 except BaseException: if total_discount == 0: tax_count = taxes_applied_obj["id__count"] + 2 else: tax_count = taxes_applied_obj["id__count"] + 3 if taxes_applied_obj["id__count"] == 0: tax_count = tax_count + 1 buyer = quoted_order_obj["buyer"] address = Customer.objects.values( "address__street_address", "address__district", "address__pin", "address__province", ).get(user=buyer) organisation_id = quoted_order_obj["organisation"] date = quoted_order_obj["date_time"] customer_obj = Customer.objects.values("company").get(user=buyer) admin_organisations = AdminOrganisations.objects.values( "pan_no", "stc_no", "gst_in").get(id=organisation_id) header = HeaderFooter.objects.values("header").get(is_active=True) footer = HeaderFooter.objects.values("footer").get(is_active=True) permanent_note = NoteLine.objects.values("note").filter(is_permanent=True) quoted_note = QuotedOrderNote.objects.values("note").filter( quoted_order=quoted_order_id) account_holder = ACCOUNT_HOLDER name_of_bank = NAME_OF_BANK branch = BRANCH online_account = ONLINE_ACCOUNT ifsc_code = IFSC_CODE ref_letter = YOUR_LETTER_No total_in_words = num2eng(grand_total) return render( request, "bills/quote_bill.html", { "admin_org": admin_organisations, "ref": quoted_order_obj, "date": date, "quoted_order": quoted_order, "address": address, "total_cost": total_cost, "grand_cost": grand_total, "taxes_applied": taxes_applied, "buyer": quoted_order_obj, "buyer_name": customer_obj, "site": quoted_order_obj, "delivery_charges": delivery_charges, "total_discount": total_discount, "tax_count": tax_count, "bill_values": bill_values, "total_in_words": total_in_words, "quoted_order_id": quoted_order_sessionid["quoted_order_session"], "header": header, "footer": footer, "permanent_note": permanent_note, "quoted_note": quoted_note, "account_holder": account_holder, "name_of_bank": name_of_bank, "branch": branch, "online_account": online_account, "ifsc_code": ifsc_code, "totalplusdelivery": totalplusdelivery, "ref_letter": ref_letter, }, )
def statistics(request): pie_data = IpAddress.objects.values('country').filter(country__isnull=False).annotate(count=Count("country")) return HttpResponse(json.dumps(list(pie_data)), content_type='application/json')
def get_queryset(self): return User.objects.annotate(num=Count('revision')).order_by('-num')
def get_queryset(self, request): qs = super(TribeAdmin, self).get_queryset(request) return qs.annotate(num_members=Count('demographic'))
def get_district_wise_hv_count(): return HomeVisit.objects.all().values('district__name').annotate( count=Count('district')).order_by('count')
def get_most_commented_posts(count=5): return Post.published.annotate(total_comments=Count('comments')).order_by('-total_comments')[:count]
def sidebar_menu(request): user = request.user resolver_match = request.resolver_match url_name = resolver_match.url_name pk = resolver_match.kwargs.get('pk') components = get_objects_for_user( user=user, perms=('view_component', ), klass=Component).annotate( has_star=Count('usercomponentstar')).order_by('-has_star', 'name') components_stars = list( UserComponentStar.objects.filter(user=request.user, component__in=components).values_list( 'component_id', flat=True)) components_items = [{ 'title': component.name, 'url': reverse('component_base_settings', kwargs={'pk': component.pk}), 'icon': 'angle-right', 'active': url_name in [ 'component_base_settings', 'component_env_settings', 'delete_component', 'update_component', 'update_component_base_settings', 'update_component_env_settings', ] and str(component.pk) == pk, 'has_star': component.pk in components_stars } for component in components[:25]] if components.count() > 25: components_items.append({ 'title': _('Show All'), 'url': reverse('components'), 'icon': 'angle-double-right', 'active': url_name in [ 'components', ] }) if user.is_staff: items = [ { 'title': _('Main menu'), 'header': True }, { 'title': _('Components'), 'url': reverse('components') if not components_items else None, 'icon': 'th-large', 'children': components_items, 'active': url_name in [ 'component_base_settings', 'component_env_settings', 'components', 'create_component', 'delete_component', 'update_component', 'update_component_base_settings', 'update_component_env_settings', ] }, { 'title': _('Environments'), 'url': reverse('environments'), 'icon': 'cloud', 'active': url_name in [ 'environments', 'create_environment', 'update_environment', 'delete_environment' ] }, { 'title': _('Logs'), 'url': reverse('logs'), 'icon': 'bars', 'active': url_name in [ 'logs', 'log_detail', ] }, { 'title': _('Users'), 'url': reverse('users'), 'icon': 'users', 'active': url_name in [ 'users', 'change_user_password', 'create_user', 'update_user', 'update_user_api_settings', 'update_user_permissions', 'update_user_permissions_by_model', 'delete_user', ] }, { 'title': _('Global Settings'), 'url': reverse('update_global_settings'), 'icon': 'cogs', 'active': url_name in [ 'update_global_settings', ] }, ] else: items = components_items return {'items': items}
def get_context_data(self, **kwargs): context = super().get_context_data(**kwargs) trending_question = Question.objects.annotate(like_count=Count('likes')).order_by('-like_count')[:7] context['popular_tags'] = Question.tags.most_common()[:25] context['trending_question'] = trending_question return context
def ComplexAgg(expression): complexagg = Count(expression) * 10 complexagg.default_alias = 'complexagg' return complexagg
def standings(request, title_slug=None, contest_id=None, template='standings.html', extra_context=None): groupby = request.GET.get('groupby') if groupby == 'none': groupby = None 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()}') contests = Contest.objects to_redirect = False contest = None if contest_id is not None: contest = contests.filter(pk=contest_id).first() if title_slug is None: to_redirect = True else: if contest is None or slug(contest.title) != title_slug: contest = None title_slug += f'-{contest_id}' if contest is None and title_slug is not None: contests_iterator = contests.filter(slug=title_slug).iterator() contest = None try: contest = next(contests_iterator) another = next(contests_iterator) except StopIteration: another = None if contest is None: return HttpResponseNotFound() if another is None: to_redirect = True else: return redirect(reverse('ranking:standings_list') + f'?search=slug:{title_slug}') if contest is None: return HttpResponseNotFound() if to_redirect: query = query_transform(request) url = reverse('ranking:standings', kwargs={'title_slug': slug(contest.title), 'contest_id': str(contest.pk)}) if query: query = '?' + query return redirect(url + query) with_detail = request.GET.get('detail', 'true') 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 with_row_num = False 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', 'Advance'), ) fixed_fields += tuple(options.get('fixed_fields', [])) if not with_detail: fixed_fields += (('rating_change', 'Rating change'), ) statistics = statistics \ .select_related('account') \ .select_related('account__resource') \ .prefetch_related('account__coders') has_country = ( 'country' in contest_fields or '_countries' in contest_fields or statistics.filter(account__country__isnull=False).exists() ) division = request.GET.get('division') if division == 'any': with_row_num = True 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_defaults = { 'rating': { 'options': ['rated', 'unrated'], 'noajax': True, 'nomultiply': True, 'nourl': True, }, 'advanced': { 'options': ['true', 'false'], 'noajax': True, 'nomultiply': True, }, } fields_to_select = OrderedDict() map_fields_to_select = {'rating_change': 'rating'} for f in sorted(contest_fields): f = f.strip('_') if f.lower() in [ 'institution', 'room', 'affiliation', 'city', 'languages', 'school', 'class', 'job', 'region', 'rating_change', 'advanced', ]: f = map_fields_to_select.get(f, f) field_to_select = fields_to_select.setdefault(f, {}) field_to_select['values'] = [v for v in request.GET.getlist(f) if v] field_to_select.update(fields_to_select_defaults.get(f, {})) 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 not (k == 'medal' and '_medal_title_field' in contest_fields) and 'country' not in k and not k.startswith('_') ): fields[k] = k for k, field in fields.items(): field = ' '.join(k.split('_')) if field and not field[0].isupper(): field = field.title() fields[k] = field per_page = options.get('per_page', 50) 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} 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()))) elif 'divisions_order' in contest.info: divisions_order = contest.info['divisions_order'] else: divisions_order = [] if divisions_order: divisions_order.append('any') if division not in divisions_order: division = divisions_order[0] params['division'] = division if 'division' in problems: if division == 'any': _problems = OrderedDict() for div in reversed(divisions_order): for p in problems['division'].get(div, []): k = get_problem_short(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: problems = problems['division'][division] if division != 'any': statistics = statistics.filter(addition__division=division) 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 last.get('full_score') and ( 'name' in last and last.get('name') == p.get('name') or 'group' in last and last.get('group') == p.get('group') ): merge_problems = True last['colspan'] = last.get('colspan', 1) + 1 p['skip'] = True else: last = p last['colspan'] = 1 # own_stat = statistics.filter(account__coders=coder).first() if coder else None # filter by search search = request.GET.get('search') if search: with_row_num = True 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: cond = get_iregex_filter(search, 'account__key', 'addition__name', logger=request.logger) statistics = statistics.filter(cond) # filter by country countries = request.GET.getlist('country') countries = set([c for c in countries if c]) if countries: with_row_num = True cond = Q(account__country__in=countries) if 'None' in countries: cond |= Q(account__country__isnull=True) if '_countries' in contest_fields: for code in countries: name = get_country_name(code) if name: cond |= Q(addition___countries__icontains=name) statistics = statistics.filter(cond) params['countries'] = countries # filter by field to select for field, field_to_select in fields_to_select.items(): values = field_to_select.get('values') if not values: continue with_row_num = True filt = Q() if field == 'languages': for lang in values: if lang == 'any': filt = Q(**{f'addition___languages__isnull': False}) break filt |= Q(**{f'addition___languages__contains': [lang]}) elif field == 'rating': for q in values: if q not in field_to_select['options']: continue q = q == 'unrated' if q: filt |= Q(addition__rating_change__isnull=True) & Q(addition__new_rating__isnull=True) else: filt |= Q(addition__rating_change__isnull=False) | Q(addition__new_rating__isnull=False) elif field == 'advanced': for q in values: if q not in field_to_select['options']: continue filt |= Q(addition__advanced=q == 'true') else: query_field = f'addition__{field}' statistics = statistics.annotate(**{f'{query_field}_str': Cast(JSONF(query_field), models.TextField())}) for q in values: if q == 'None': filt |= Q(**{f'{query_field}__isnull': True}) else: filt |= Q(**{f'{query_field}_str': q}) statistics = statistics.filter(filt) # groupby if groupby == 'country' or groupby in fields_to_select: fields = OrderedDict() fields['groupby'] = groupby.title() fields['n_accounts'] = 'Num' fields['avg_score'] = 'Avg' medals = {m['name']: m for m in options.get('medals', [])} if 'medal' in contest_fields: for medal in settings.ORDERED_MEDALS_: fields[f'n_{medal}'] = medals.get(medal, {}).get('value', medal[0].upper()) 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_short(problem) field = f'addition__problems__{key}__language' score = f'addition__problems__{key}__result' qs = statistics \ .filter(**{f'{field}__isnull': False, f'{score}__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 == 'rating': statistics = statistics.annotate( groupby=Case( When(addition__rating_change__isnull=False, then=Value('Rated')), default=Value('Unrated'), output_field=models.TextField(), ) ) elif groupby == 'country': if '_countries' in contest_fields: statistics = statistics.annotate( country=RawSQL('''json_array_elements((("addition" ->> '_countries'))::json)::jsonb''', [])) field = 'country' else: field = 'account__country' statistics = statistics.annotate(groupby=F(field)) else: field = f'addition__{groupby}' types = contest.info.get('fields_types', {}).get(groupby, []) if len(types) == 1 and types[0] == 'int': field_type = models.IntegerField() else: field_type = models.TextField() statistics = statistics.annotate(groupby=Cast(JSONF(field), field_type)) 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), When(~Q(addition__advanced=False) & ~Q(addition__advanced=''), then=1), ) )) statistics = statistics.order_by(*orderby) if groupby == 'languages': query, sql_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) sql_params = sql_params[:-len(before_params)] + language_params with connection.cursor() as cursor: cursor.execute(query, sql_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_advanced': 'Number of advanced', } for medal in settings.ORDERED_MEDALS_: labels_groupby[f'n_{medal}'] = 'Number of ' + medals.get(medal, {}).get('value', medal) num_rows_groupby = statistics.count() map_colors_groupby = {s['groupby']: idx for idx, s in enumerate(statistics)} else: groupby = 'none' labels_groupby = None num_rows_groupby = None map_colors_groupby = None my_statistics = [] if groupby == 'none' and coder: statistics = statistics.annotate(my_stat=SubqueryExists('account__coders', filter=Q(coder=coder))) my_statistics = statistics.filter(account__coders=coder).extra(select={'floating': True}) neighbors = list(contest.neighbors()) context = { 'data_1st_u': data_1st_u, 'participants_info': participants_info, 'standings_options': options, 'mod_penalty': mod_penalty, 'colored_by_group_score': mod_penalty or options.get('colored_by_group_score'), 'contest': contest, 'statistics': statistics, 'my_statistics': my_statistics, 'problems': problems, 'params': params, 'fields': fields, 'divisions_order': divisions_order, 'has_country': has_country, 'per_page': per_page, 'with_row_num': with_row_num, '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_groupby': num_rows_groupby, 'map_colors_groupby': map_colors_groupby, 'advance': contest.info.get('advance'), 'timezone': get_timezone(request), 'timeformat': get_timeformat(request), 'neighbors': { 'visible': request.GET.get('neighbors') == 'on', 'total': len(neighbors), 'before': [c for c in neighbors if c.end_time < contest.end_time], 'after': [c for c in neighbors if c.end_time >= contest.end_time], }, 'with_table_inner_scroll': not request.user_agent.is_mobile, } if extra_context is not None: context.update(extra_context) return render(request, template, context)