def CallDictFunc(request): profile_list = Profile.objects.all() #print(profile_list) for row in profile_list.values_list(): print(row) print(Profile.objects.aggregate(Avg('age'))) print(Profile.objects.aggregate(Max('age'))) print(Profile.objects.aggregate(Sum('age'))) print(Profile.objects.aggregate(Count('age'))) print(Profile.objects.filter(name='홍길동').aggregate( Count('age'))) # filter로 where 조건을 나타낸다. #이름이 홍길동인 튜플들 갯수 출력 print(len(profile_list)) # values() + aggregate() 그룹별 평균 나이는? qs = Profile.objects.values('name').annotate( Avg('age')) # name별로 그룹을 묶어서 age의 평균을 출력. for r in qs: print(r) # 결과를 list로 감싸서 dict type으로 클라이언트에게 출력하기 pro_list = [] for pro in profile_list: pro_dict = {} pro_dict['name'] = pro.name pro_dict['age'] = pro.age pro_list.append(pro_dict) print(pro_list) context = {'pro_dicts': pro_list} return render(request, 'abc.html', context)
def test_aggregations(self): for age, birthday in ( [4, (2007, 12, 25)], [4, (2006, 1, 1)], [1, (2008, 12, 1)], [4, (2006, 6, 1)], [12, (1998, 9, 1)], ): Person.objects.create(age=age, birthday=datetime(*birthday)) aggregates = Person.objects.aggregate(Min('age'), Max('age'), avgage=Avg('age')) self.assertEqual(aggregates, { 'age__min': 1, 'age__max': 12, 'avgage': 5.0 }) # With filters and testing the sqlaggregates->mongoaggregate # conversion. aggregates = Person.objects.filter(age__gte=4).aggregate( Min('birthday'), Max('birthday'), Avg('age'), Count('id')) self.assertEqual( aggregates, { 'birthday__max': datetime(2007, 12, 25, 0, 0), 'birthday__min': datetime(1998, 9, 1, 0, 0), 'age__avg': 6.0, 'id__count': 4, })
def __get_summary(self): """ Returns a dictionary which has summarized statistics of this exercise. The dictionary is generated only once and saved into a private field to improve performance with subsequent calls. @return: a dictionary keys: submission_count, average_grade, average_submissions and submitter_count """ if not hasattr(self, "temp_summary"): submission_count = self.submissions.count() submitter_count = UserProfile.objects.distinct().filter( submissions__exercise=self).count() average_grade = UserProfile.objects.distinct().filter( submissions__exercise=self).annotate( best_grade=Max('submissions__grade')).aggregate( average_grade=Avg('best_grade'))["average_grade"] average_submissions = UserProfile.objects.distinct( ).filter(submissions__exercise=self).annotate( submission_count=Count('submissions')).aggregate( avg_submissions=Avg('submission_count'))["avg_submissions"] if average_grade == None: average_grade = 0 if average_submissions == None: average_submissions = 0 self.temp_summary = { "submission_count": submission_count, "average_grade": average_grade, "submitter_count": submitter_count, "average_submissions": average_submissions, } return self.temp_summary
def run(): """ Runs the script """ # First do it for the cantons which have fetched cities countries = Country.objects.all() country_count = countries.count() for idx1, country in enumerate(countries): nr_of_country_cantons = Canton.objects.filter(country=country).count() cantons = Canton.objects.filter(Q(name='Vaud') | Q(name='Genève')) canton_count = cantons.count() for idx2, canton in enumerate(cantons): metrics = City.objects.filter(canton=canton).aggregate( Avg('tree_density'), Avg('tree_sparsity')) if metrics['tree_density__avg'] is not None: tree_density = metrics['tree_density__avg'] tree_sparsity = metrics['tree_sparsity__avg'] cars_pp = canton.cars / canton.population rel_population = canton.population / country.population * nr_of_country_cantons rel_area = canton.area / country.area * nr_of_country_cantons canton.tree_density = tree_density canton.tree_sparsity = tree_sparsity canton.score = np.dot(weights, [ tree_density, functions.sparsity_to_representable(tree_sparsity), (1 - cars_pp), rel_population, rel_area ]) canton.has_mapping = True canton.save() print('Fetched canton {}/{} ({})'.format(idx2 + 1, canton_count, canton.name)) print('Fetched country {}/{} ({})'.format(idx1 + 1, country_count, country.name))
def rows(self): soh_data = [] if 'data_config' in self.config: data_config = self.config['data_config'] late = data_config.soh_data_total.get('late', 0) on_time = data_config.soh_data_total.get('on_time', 0) soh_data.append( GroupSummary(title=SupplyPointStatusTypes.SOH_FACILITY, responded=late + on_time, on_time=on_time, complete=late + on_time, total=len(data_config.descendants))) return soh_data if self.config['org_summary']: try: sohs = GroupSummary.objects.filter( title=SupplyPointStatusTypes.SOH_FACILITY, org_summary__in=self.config['org_summary']).aggregate( Avg('responded'), Avg('on_time'), Avg('complete'), Max('total')) soh_data.append( GroupSummary(title=SupplyPointStatusTypes.SOH_FACILITY, responded=sohs['responded__avg'], on_time=sohs['on_time__avg'], complete=sohs['complete__avg'], total=sohs['total__max'])) except GroupSummary.DoesNotExist: return soh_data return soh_data
def rows(self): del_data = [] if 'data_config' in self.config: data_config = self.config['data_config'] delivered = data_config.delivery_data_total.get('received', 0) not_delivered = data_config.delivery_data_total.get( 'not_received', 0) del_data.append( GroupSummary(title=SupplyPointStatusTypes.DELIVERY_FACILITY, responded=delivered + not_delivered, on_time=delivered + not_delivered, complete=delivered, total=data_config.delivery_data_total.get( 'total', 0))) return del_data if self.config['org_summary']: try: data = GroupSummary.objects.filter( title=SupplyPointStatusTypes.DELIVERY_FACILITY, org_summary__in=self.config['org_summary']).aggregate( Avg('responded'), Avg('on_time'), Avg('complete'), Max('total')) del_data.append( GroupSummary( title=SupplyPointStatusTypes.DELIVERY_FACILITY, responded=data['responded__avg'], on_time=data['on_time__avg'], complete=data['complete__avg'], total=data['total__max'])) except GroupSummary.DoesNotExist: return del_data return del_data
def get_averages(request): matches = Match.objects.values('team_number').annotate( total_matches=Count('team_number'), auto_yellow_stacked_totes=Avg('auto_yellow_stacked_totes'), auto_yellow_moved_totes=Avg('auto_yellow_moved_totes'), auto_moved_to_auto_zone=Avg('auto_moved_to_auto_zone'), auto_moved_containers=Avg('auto_moved_containers'), tele_pushed_litter=Avg('tele_pushed_litter'), tele_placed_in_container_litter=Avg('tele_placed_in_container_litter'), totestack_start_height=Avg('totestack__start_height'), totestack_totes_added=Avg('totestack__totes_added'), number_of_totestacks=Count('totestack'), containerstack_height=Avg('containerstack__height'), number_of_containerstacks=Avg('containerstack'), ) teams = [] auto_scores = [] tele_scores = [] unprocessed_matches = {} for match in matches: auto_score = 0 # multiply by point value, divide by robots per alliance if match['auto_moved_to_auto_zone'] is not None: auto_score += (match['auto_moved_to_auto_zone'] / 3 * 4) if match['auto_moved_to_auto_zone'] is not None: auto_score += (match['auto_moved_to_auto_zone'] / 3 * 6) if match['auto_moved_to_auto_zone'] is not None: auto_score += (match['auto_moved_to_auto_zone'] / 3 * 20) tele_score = 0 # worth 2 per tote stacked (average) if match['totestack_totes_added'] is not None: tele_score += match['totestack_totes_added'] * ( match['number_of_totestacks'] / match['total_matches']) * 2 if match['containerstack_height'] is not None: tele_score += match['containerstack_height'] * ( match['number_of_containerstacks'] / match['total_matches']) * 4 teams.append(match['team_number']) auto_scores.append(auto_score) tele_scores.append(tele_score) processed_matches = { 'teams': teams, 'auto_scores': auto_scores, 'tele_scores': tele_scores } return HttpResponse(json.dumps(processed_matches))
def run(): """ Runs the script """ # First do it for the cities which have tiles countries = Country.objects.all() country_count = countries.count() for idx1, country in enumerate(countries): nr_of_country_cities = City.objects.filter(country=country).count() # TODO: REMOVE canton__name='Vaud' cities = City.objects.filter(country=country).filter(Q(canton__name='Vaud') | Q(canton__name='Genève')) city_count = cities.count() for idx2, city in enumerate(cities): metrics = Tile.objects.annotate(geom=Cast('mpoly', GeometryField())) \ .filter(geom__within=city.mpoly) \ .annotate(count=Count('*')) \ .filter(count__gt=0) \ .aggregate(Avg('tree_density'), Avg('tree_sparsity')) if metrics['tree_density__avg'] is not None: # Check if result returned, a.k.a has tiles tree_density = metrics['tree_density__avg'] tree_sparsity = metrics['tree_sparsity__avg'] cars_pp = city.cars / city.population rel_population = city.population / country.population * nr_of_country_cities rel_area = city.area / country.area * nr_of_country_cities city.tree_density = tree_density city.tree_sparsity = tree_sparsity city.score = np.dot(weights, [ tree_density, functions.sparsity_to_representable(tree_sparsity), (1 - cars_pp), rel_population, rel_area ]) city.has_mapping = True city.save() print('Fetched city {}/{} ({})'.format(idx2 + 1, city_count, city.name)) print('Fetched country {}/{} ({})'.format(idx1 + 1, country_count, country.name))
def define_prices(self): return self.aggregate( ads_count=Count('id'), max_price_uzs=Max('price_uzs'), min_price_uzs=Min('price_uzs'), avg_price_uzs=Avg('price_uzs'), max_price_usd=Max('price_usd'), min_price_usd=Min('price_usd'), avg_price_usd=Avg('price_usd'), )
def rows(self): product_availability = [] if self.config['org_summary']: product_availability = ProductAvailabilityData.objects.filter( date__range=(self.config['startdate'], self.config['enddate']), location_id=self.config['org_summary'][0].location_id, product__in=self.config['products']).values( 'product').annotate(with_stock=Avg('with_stock'), without_data=Avg('without_data'), without_stock=Avg('without_stock'), total=Max('total')) return product_availability
def get_media(self, initial_time, final_time): if self.is_time: return \ self.points.filter( y_accumulative__gte=initial_time, y_accumulative__lte=final_time)\ .order_by('x').aggregate(avg=Avg('y'))['avg'] return \ self.points.filter( x__gte=initial_time, x__lte=final_time).order_by('x').aggregate(avg=Avg('y'))['avg']
def stats(cls, events=None): ''' Returns stats on the events queryset provided. :param events: A queryset of events, that have the fields sessions, games, players ''' if events is None: events = cls.implicit() if events: # Tailwind's Median aggregator does not work on Durations (PostgreSQL Intervals) # So we have to convert it to Epoch time. Extract is a Django method that can extract # 'epoch' which is the documented method of casting a PostgreSQL interval to epoch time. # https://www.postgresql.org/message-id/19495.1059687790%40sss.pgh.pa.us # Django does not document 'epoch' alas but it works: # https://docs.djangoproject.com/en/4.0/ref/models/database-functions/#extract # We need a Django ExpressionWrapper to cast the uration field to DurationField as # for some reason even though it's a PostgreSQL interval, Django still thinks of it # as a DateTimeField (from the difference of two DateTimeFields I guess and a bug/feature) # that fails tor ecast a difference of DateTimeFiled's as DurationField. epoch_duration = Extract(ExpressionWrapper(F('duration'), output_field=DurationField()), lookup_name='epoch') epoch_gap = Extract(ExpressionWrapper(F('gap_time'), output_field=DurationField()), lookup_name='epoch') result = events.aggregate(Min('sessions'), Avg('sessions'), Median('sessions'), Max('sessions'), Min('games'), Avg('games'), Median('games'), Max('games'), Min('players'), Avg('players'), Median('players'), Max('players'), duration__min=Min('duration'), duration__avg=Avg('duration'), duration__median=Median(epoch_duration), duration__max=Max('duration'), gap__min=Min('gap_time'), gap__avg=Avg('gap_time'), gap__median=Median(epoch_gap), gap__max=Max('gap_time')) # Aggregate is a QuerySet enpoint (i.e results in evaluation of the Query and returns # a standard dict. To wit we can cast teh Epch times back to Durations for the consumer. result['duration__median'] = timedelta(seconds=result['duration__median']) result['gap__median'] = timedelta(seconds=result['gap__median']) else: result = None return result
def get(self, request: WSGIRequest): context = self.get_context(request=request) if not context["is_staff"]: messages.add_message(request, messages.INFO, "Only staff can access the judging panel.") return redirect("timathon:Home") try: challenge = Challenge.objects.get(ended=False, posted=True, type="MO") except Challenge.DoesNotExist: messages.add_message(request, messages.INFO, "There is no ongoing challenge right now.") return redirect("/") submissions = Submission.objects.filter(challenge=challenge) submissions = list(submissions) for submission in submissions: members = submission.team.members.all() discord_members = [] for member in members: new_member = {} try: user = SocialAccount.objects.get(user_id=member.id) except SocialAccount.DoesNotExist: pass else: new_member["user_id"] = user.uid avatar_url = user.get_avatar_url() if avatar_url.endswith("None.png"): random = randint(0, 4) avatar_url = ( f"https://cdn.discordapp.com/embed/avatars/{random}.png" ) new_member["avatar_url"] = avatar_url new_member["username"] = user.extra_data["username"] new_member["discriminator"] = user.extra_data[ "discriminator"] discord_members.append(new_member) submission.team.discord_members = discord_members submission.judges = [vote.user for vote in submission.votes.all()] submission.points = submission.votes.aggregate( average=Avg("c1") + Avg("c2") + Avg("c3") + Avg("c4"))["average"] context["submissions"] = submissions context["challenge"] = challenge return render(request, "timathon/judge.html", context)
def get_ratings(self, obj): score = None if obj.type == USER_TYPE_DEVELOPER: query = Rating.objects.filter( tasks__closed=True, tasks__participants=obj, tasks__participation__accepted=True ).order_by('criteria') details = query.values('criteria').annotate(avg=Avg('score')) criteria_choices = dict(Rating._meta.get_field('criteria').flatchoices) for rating in details: rating['display_criteria'] = criteria_choices[rating['criteria']] rating['display_avg'] = rating['avg'] and '{:0,.0f}%'.format(rating['avg']*10) avg = query.aggregate(avg=Avg('score'))['avg'] score = {'avg': avg, 'display_avg': avg and '{:0,.0f}%'.format(avg*10) or None, 'details': details} return score
def water_consumption_report(args): """ Отчет по воде """ data = {} items = [] report_date = parse(args['date']).date() for branch in Branch.objects.all(): items.append({'name': branch.name, 'is_branch': True}) for thermal in ThermalArea.objects.filter(branch__id=branch.id): outdoor_temp_avg = Environment.objects.filter( date=report_date, boiler__thermalArea__id=thermal.id, outdoor_temp_actual__gt=-273.0).aggregate( Avg('outdoor_temp_actual')) temp_avg = WaterConsumption.objects.filter(date = report_date, category__name = u"Общий расход", category__active = True, category__boiler__thermalArea__id = thermal.id, backward_temperature_actual__gt = -273.0, farward_temperature_actual__gt = -273.0, backward_temperature_estimated__gt = -273.0, farward_temperature_estimated__gt = -273.0) \ .aggregate(Avg('backward_temperature_actual'), Avg('farward_temperature_actual'), Avg('backward_temperature_estimated'), Avg('farward_temperature_estimated')) items.append({ 'name': thermal.name, 'outdoor_temp_actual': outdoor_temp_avg['outdoor_temp_actual__avg'], 'backward_temperature_actual': temp_avg['backward_temperature_actual__avg'], 'farward_temperature_actual': temp_avg['farward_temperature_actual__avg'], 'backward_temperature_estimated': temp_avg['backward_temperature_estimated__avg'], 'farward_temperature_estimated': temp_avg['farward_temperature_estimated__avg'] }) data['items'] = items data['date'] = report_date return (data, 'water/temperature_report.html')
def _get_efficiency_statistics(model, owner, field="efficiency", count=False, worth=False, worth_field='value'): eff_values = {} eff_map = { "efficiency__avg": "Efficiency (Average)", "efficiency__stddev": "Efficiency (Standard Deviation)", "efficiency__min": "Efficiency (Minimum)", "efficiency__perc25": "Efficiency (25th Percentile)", "efficiency__median": "Efficiency (Median)", "efficiency__perc75": "Efficiency (75th Percentile)", "efficiency__max": "Efficiency (Maximum)", "efficiency__count": "Count", "value__sum": "Worth", } aggregations = [ Avg(field), StdDev(field), Min(field), Perc25(field), Median(field), Perc75(field), Max(field) ] if worth: aggregations.insert(0, Sum(worth_field)) if count: aggregations.insert(0, Count(field)) efficiencies = model.objects.filter(owner=owner).aggregate(*aggregations) for eff_key, eff_val in efficiencies.items(): eff_values[eff_map[eff_key]] = round(eff_val or 0, 2) return eff_values
def _forms_per_user_per_month(self): performance_threshold = get_performance_threshold(self.domain) base_queryset = MALTRow.objects.filter( domain_name=self.domain, month__gte=self.date_start, ) if self.date_end: base_queryset.filter(month__lt=self.date_end) user_stat_from_malt = (base_queryset.values('month').annotate( num_users=Count('user_id'), avg_forms=Avg('num_of_forms'), std_dev=StdDev('num_of_forms'))) def _format_rows(query_): return [(row['month'].isoformat(), row['num_users'], row['avg_forms'], row['std_dev']) for row in query_] headers = [ 'Month', 'Active Users', 'Average forms per user', 'Std Dev' ] self.stdout.write('All user stats') self._print_table( headers, _format_rows( user_stat_from_malt.filter( user_type__in=['CommCareUser']).filter( user_id__in=self.active_not_deleted_users).filter( num_of_forms__gte=performance_threshold))) self.stdout.write('System user stats') self._print_table( headers, _format_rows(user_stat_from_malt.filter(username='******')))
def get_rating_average(self, axis=None): """ Renvoyer la note moyenne sur un axe """ ratings = self.ratings.all() if axis is not None: ratings = ratings.filter(axis=axis) result = ratings.aggregate(Avg('rating')) return result['rating__avg']
def chart(request): #sql='select title,avg(point) points from board_movie group by title' #data=Movie.objects.raw(sql) data=Movie.objects.values('title').annotate(point_avg=Avg('point'))[0:10] df=pd.DataFrame(data) bigdataPro.make_graph(df.title, df.point_avg) return render(request,"chart.html",{"data":data})
def range_statistics(start, end): """ Returns the statistics (totals) and the number of data points for a target range. """ queryset = DayStatistics.objects.filter(day__gte=start, day__lt=end) number_of_days = queryset.count() aggregate = queryset.aggregate( total_cost=Sum('total_cost'), fixed_cost=Sum('fixed_cost'), electricity1=Sum('electricity1'), electricity1_cost=Sum('electricity1_cost'), electricity1_returned=Sum('electricity1_returned'), electricity2=Sum('electricity2'), electricity2_cost=Sum('electricity2_cost'), electricity2_returned=Sum('electricity2_returned'), electricity_merged=Sum( models.F('electricity1') + models.F('electricity2')), electricity_cost_merged=Sum( models.F('electricity1_cost') + models.F('electricity2_cost')), electricity_returned_merged=Sum( models.F('electricity1_returned') + models.F('electricity2_returned')), gas=Sum('gas'), gas_cost=Sum('gas_cost'), temperature_min=Min('lowest_temperature'), temperature_max=Max('highest_temperature'), temperature_avg=Avg('average_temperature'), ) aggregate.update(dict(number_of_days=number_of_days)) return aggregate, number_of_days
def add_custom_form_data(self, indicator_name, range_name, xmlns, indicator_type, lower, upper): """ For specific forms add the number of forms completed during the time period (lower to upper) In some cases also add the average duration of the forms. """ aggregation = Avg('duration') if indicator_type == TYPE_DURATION else Count('instance_id') def millis_to_secs(x): return round(x / 1000) transformer = millis_to_secs if indicator_type == TYPE_DURATION else None results = FormData.objects \ .values('user_id') \ .filter( xmlns=xmlns, domain=self.domain, user_id__in=self.users_needing_data) \ .filter(**self._date_filters('time_end', lower, upper)) \ .annotate(count=aggregation) self._add_data( results, '{}{}'.format(indicator_name, range_name.title()), transformer=transformer)
def test_subquery_row_range_rank(self): qs = Employee.objects.annotate( highest_avg_salary_date=Subquery( Employee.objects.filter( department=OuterRef('department'), ).annotate( avg_salary=Window( expression=Avg('salary'), order_by=[F('hire_date').asc()], frame=RowRange(start=-1, end=1), ), ).order_by('-avg_salary', 'hire_date').values('hire_date')[:1], ), ).order_by('department', 'name') self.assertQuerysetEqual(qs, [ ('Adams', 'Accounting', datetime.date(2005, 11, 1)), ('Jenson', 'Accounting', datetime.date(2005, 11, 1)), ('Jones', 'Accounting', datetime.date(2005, 11, 1)), ('Williams', 'Accounting', datetime.date(2005, 11, 1)), ('Moore', 'IT', datetime.date(2011, 3, 1)), ('Wilkinson', 'IT', datetime.date(2011, 3, 1)), ('Johnson', 'Management', datetime.date(2005, 6, 1)), ('Miller', 'Management', datetime.date(2005, 6, 1)), ('Johnson', 'Marketing', datetime.date(2009, 10, 1)), ('Smith', 'Marketing', datetime.date(2009, 10, 1)), ('Brown', 'Sales', datetime.date(2007, 6, 1)), ('Smith', 'Sales', datetime.date(2007, 6, 1)), ], transform=lambda row: (row.name, row.department, row.highest_avg_salary_date))
def management_adviser_timing_ajax(request, context=None): if context is None: context = {} start_date, end_date, referrer = filter_data(request) timing_data = [] adviser_queue_objects = AdviserQueue.objects.filter( interaction_ended__gte=start_date, interaction_ended__lte=end_date).exclude(_claim_time=None) if referrer is not None: user_referrals = UserReferral.objects.filter( referrer=referrer).values('user__email') adviser_queue_objects = adviser_queue_objects.filter( email__in=user_referrals) advisers = User.objects.filter( pk__in=adviser_queue_objects.values_list('adviser', flat=True)) for adviser in advisers: timing_data.append({ 'adviser': adviser.email, 'claim_time': timedelta(seconds=adviser_queue_objects.filter( adviser=adviser).aggregate( claim_time=Avg('_claim_time'))['claim_time']) }) context['timing_data'] = timing_data return render(request, 'concierge/management/adviser_timing.html', context)
def avaregereview(self): reviews = Comment.objects.filter( product=self, status='True').aggregate(avarage=Avg('rate')) avg = 0 if reviews["avarage"] is not None: avg = float(reviews["avarage"]) return avg
def vendas_por_mesa(dias): def split_and_strip(string, sep=","): """Splits a string using `sep` as a separator, and strips whitespace from the splitted results.""" return map(lambda s: s.strip(), string.split(sep)) def primeira_mesa(mesas): try: return int(split_and_strip(mesas)[0]) except: return split_and_strip(mesas)[0] vendas = Venda.objects.filter(dia__in=dias) agrupado = vendas.values("mesa").order_by("mesa") dados = agrupado.annotate(vendas=Count("id"), entrada=Sum("conta"), pessoas=Sum("num_pessoas"), permanencia_media=Avg("permanencia")) headers = ("mesa", "vendas", "entrada", "pessoas", "permanencia_media") for row in dados: row["permanencia_media"] = secs_to_time(row["permanencia_media"]) row["entrada"] = format_currency(row["entrada"]) row["mesa"] = row["mesa"].replace("/", ",") body = [[row[col] for col in headers] for row in dados] body.sort(key=lambda row: primeira_mesa(row[0])) return {"title": "Vendas por mesa", "headers": headers, "body": body}
def rows(self): locations = SQLLocation.objects.filter(parent__location_id=self.config['location_id']) rows = [] for loc in locations: try: org_summary = OrganizationSummary.objects.filter( location_id=loc.location_id, date__range=(self.config['startdate'], self.config['enddate']) ).aggregate(average_lead_time_in_days=Avg('average_lead_time_in_days')) except OrganizationSummary.DoesNotExist: continue avg_lead_time = org_summary['average_lead_time_in_days'] if avg_lead_time: avg_lead_time = "%.1f" % avg_lead_time else: avg_lead_time = "None" url = make_url(DeliveryReport, self.config['domain'], '?location_id=%s&filter_by_program=%s&' 'datespan_type=%s&datespan_first=%s&datespan_second=%s', (loc.location_id, self.config['program'], self.config['datespan_type'], self.config['datespan_first'], self.config['datespan_second'])) rows.append([link_format(loc.name, url), avg_lead_time]) return rows
class CompanyChartsViewSet(GenericViewSet, ListModelMixin): queryset = Company.objects.annotate(__session_average=Subquery( Session.objects.filter(company=OuterRef("id")).annotate( value=ExpressionWrapper(Avg("answered_questions__value") * F("set__weight"), output_field=DecimalField( max_digits=3, decimal_places=2, ))).values("value"))).annotate( date=Case( When(sessions__until__lte=Now(), then=F("sessions__until")), default=Now()), data=ExpressionWrapper( Sum("__session_average") * F("sessions__theme__weight"), output_field=DecimalField( decimal_places=2, max_digits=3))).values( "data", "date") serializer_class = CompanyChartSerializer def filter_queryset(self, queryset): return queryset.filter(id=self.request.user.member.company_id)
def _refresh_title_norms(self, full=False): """ Refreshes the value of the title_norm field. This needs to be set to 'lavg/ld' where: - lavg is the average length of titles in all documents (also in terms) - ld is the length of the title field in this document (in terms) """ lavg = (self.entries.annotate( title_length=Length("title")).filter(title_length__gt=0).aggregate( Avg("title_length"))["title_length__avg"]) if full: # Update the whole table # This is the most accurate option but requires a full table rewrite # so we can't do it too often as it could lead to locking issues. entries = self.entries else: # Only update entries where title_norm is 1.0 # This is the default value set on new entries. # It's possible that other entries could have this exact value but there shouldn't be too many of those entries = self.entries.filter(title_norm=1.0) entries.annotate(title_length=Length("title")).filter( title_length__gt=0).update(title_norm=lavg / F("title_length"))
def add_unit_to_battle_in_progress(battle_organization: BattleOrganization, world_unit: WorldUnit): battle = battle_organization.side.battle if world_unit.owner_character: battle_character = BattleCharacter.objects.get_or_create( battle_organization=battle_organization, character=world_unit.owner_character, present_in_battle=(world_unit.owner_character.location.tile == world_unit.location.tile))[0] battle_character_in_turn = BattleCharacterInTurn.objects.get_or_create( battle_character=battle_character, battle_turn=battle.get_latest_turn()) else: battle_character = None battle_unit = BattleUnit.objects.create( battle_organization=battle_organization, owner=battle_character, world_unit=world_unit, starting_manpower=world_unit.get_fighting_soldiers().count(), battle_side=battle_organization.side, name=world_unit.name, type=world_unit.type) create_contubernia(battle_unit) position_generator = joining_contubernium_position_generator() for contub in battle_unit.battlecontubernium_set.all(): coords = next(position_generator) while battle.get_latest_turn().get_contubernium_in_position( coords) is not None: coords = next(position_generator) contub.x_offset_to_formation = coords.x contub.z_offset_to_formation = coords.z contub.starting_x_pos = coords.x if battle_organization.side.z else -coords.x contub.starting_z_pos = coords.z + 10 if battle_organization.side.z else -coords.z - 10 contub.save() battle_unit.starting_x_pos = math.floor( battle_unit.battlecontubernium_set.all().aggregate( Avg('starting_x_pos'))['starting_x_pos__avg']) battle_unit.starting_z_pos = math.floor( battle_unit.battlecontubernium_set.all().aggregate( Avg('starting_z_pos'))['starting_z_pos__avg']) battle_unit.save() set_contubernia_starting_pos(battle_unit)
def rows(self): rows = [] products_ids = self.get_products_ids() if not self.config['location_id']: return rows location = SQLLocation.objects.get( location_id=self.config['location_id']) sql_locations = SQLLocation.objects.filter( parent__location_id=self.config['location_id']) is_mohsw = False stockouts_map = {} product_availabilities = { (pa['location_id'], pa['product']): (pa['without_stock'], pa['total']) for pa in ProductAvailabilityData.objects.filter( location_id__in=list( sql_locations.values_list('location_id', flat=True)), date__range=( self.config['startdate'], self.config['enddate'])).values('location_id', 'product'). annotate(without_stock=Avg('without_stock'), total=Max('total')) } if location.location_type.name == 'MOHSW': is_mohsw = True stockouts_map = self.get_stockouts_map(self.config['enddate'], location) for sql_location in sql_locations.exclude(is_archived=True): facilities = get_facilities(sql_location, self.config['domain']) facilities_count = facilities.count() soh_late, soh_not_responding, soh_on_time = self.get_soh_data( sql_location, facilities_count) if not is_mohsw: percent_stockouts = self.get_stockouts(facilities) else: if facilities_count > 0: stockouts = stockouts_map.get(sql_location.location_id, 0) percent_stockouts = stockouts * 100 / float( facilities_count) else: percent_stockouts = 0 row_data = self._format_row(percent_stockouts, soh_late, soh_not_responding, soh_on_time, sql_location) for product_id in products_ids: product_availability = product_availabilities.get( (sql_location.location_id, product_id)) if product_availability and product_availability[1] != 0: row_data.append( format_percent(product_availability[0] * 100 / float(product_availability[1]))) else: row_data.append("<span class='no_data'>No Data</span>") rows.append(row_data) return rows