def compute_total(self):
aggs = self.pharmapayment_set.all().aggregate(models.Sum('amount'),
models.Count('pharma_company', distinct=True))
self.total = aggs['amount__sum'] or decimal.Decimal(0)
self.total_currency = PharmaPayment.ORIGIN_CURRENCY[self.origin]
self.company_count = aggs['pharma_company__count']
year_aggs = self.pharmapayment_set.all().values('date').annotate(
amount=models.Sum('amount'),
company_count=models.Count('pharma_company', distrinct=True)
)
total_euro = decimal.Decimal(0.0)
for year_agg in year_aggs:
year = year_agg['date'].year
self.aggs['total_%s' % year] = float(year_agg['amount'])
year_euro = convert_currency_to_euro(year_agg['amount'],
self.total_currency, year)
total_euro += year_euro
self.aggs['total_euro_%s' % year] = float(year_euro)
self.aggs['company_count_%s' % year] = year_agg['company_count']
if self.is_zerodoc:
efpia_years = self.zerodoctor.get_confirmed_efpia_years()
for year in efpia_years:
year_key = 'total_%s' % year
if self.aggs.get(year_key):
raise ValueError('Conflict detected at %s' % self.pk)
self.aggs[year_key] = 0
self.aggs['total_euro_%s' % year] = 0
self.total_euro = total_euro
def scorecard_overall(self):
total_count = super(Person, self).active_scorecards().count()
total_score = super(Person, self).active_scorecards().aggregate(
models.Sum('score'))['score__sum']
for constituency in self.constituencies():
constituency_count = constituency.active_scorecards().count()
if constituency_count:
total_count += constituency_count
total_score += constituency.active_scorecards().aggregate(
models.Sum('score'))['score__sum']
return total_score / total_count
def get_aggregates(self):
aggs = Drug.objects.filter(pk=self.pk).aggregate(
sum_patients=models.Sum(
'observationalstudy__patient_count'),
sum_doctors=models.Sum(
'observationalstudy__doc_count'),
total_fee=models.Sum(
models.F('observationalstudy__patient_count') *
models.F('observationalstudy__fee_per_patient'),
output_field=models.DecimalField(decimal_places=2,
max_digits=19)
)
)
return aggs
def get_by_patient_sum(self):
return self.get_queryset().filter(
observationalstudy__patient_count__isnull=False
).annotate(
sum_patients=models.Sum(
'observationalstudy__patient_count')
).order_by('-sum_patients')
def get_by_doc_sum(self):
return self.get_queryset().filter(
observationalstudy__doc_count__isnull=False
).annotate(
doc_sum=models.Sum(
'observationalstudy__doc_count')
).order_by('-doc_sum')
def complainant_gender_aggregation(self):
query = self.officerallegation_set.all()
query = query.values('allegation__complainant__gender').annotate(
complainant_gender=models.Case(
models.When(allegation__complainant__gender='',
then=models.Value('Unknown')),
models.When(allegation__complainant__isnull=True,
then=models.Value('Unknown')),
default='allegation__complainant__gender',
output_field=models.CharField()),
year=ExtractYear('start_date'))
query = query.values('complainant_gender', 'year').order_by(
'complainant_gender', 'year').annotate(
count=models.Count('complainant_gender'),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU', then=1),
default=models.Value(0),
output_field=models.IntegerField())))
data = [{
'name': GENDER_DICT.get(obj['complainant_gender'], 'Unknown'),
'sustained_count': obj['sustained_count'],
'count': obj['count'],
'year': obj['year']
} for obj in query if obj['count'] > 0]
return Officer._group_and_sort_aggregations(data)
def get_aggregated_payments(self, obj):
current_year = CURRENT_YEAR[obj.country]
p = obj.pharmapayment_set.filter(date__year=current_year)
result = (p.annotate(individual_recipient=models.Case(
models.When(recipient__isnull=False, then=True), default=False,
output_field=models.BooleanField())
).values('recipient_kind', 'label', 'individual_recipient')
.annotate(amount=models.Sum('amount'))
)
df = pd.DataFrame(list(result))
if len(df) == 0:
return None
rnd_amount = df[df['label'] == 'research_development']['amount'].sum()
max_kind_amount = df.groupby('recipient_kind')['amount'].sum().max()
if pd.isnull(max_kind_amount):
max_kind_amount = 0.0
max_amount = max(rnd_amount, max_kind_amount)
totals_ind_agg = df.groupby('individual_recipient')['amount'].sum()
total = df['amount'].sum()
return {
'total': total,
'currency': PharmaPayment.ORIGIN_CURRENCY[obj.country],
'rnd': rnd_amount,
'rnd_percent': perc(rnd_amount, max_amount),
'total_individual_percent': perc(totals_ind_agg.get(True, 0), total),
'total_aggregated_percent': perc(totals_ind_agg.get(False, 0), total),
'hcp': self._get_type_aggregation(obj, df, 0, max_amount),
'hco': self._get_type_aggregation(obj, df, 1, max_amount),
}
def complainant_age_aggregation(self):
query = self.officerallegation_set.all()
query = query.annotate(name=get_num_range_case(
'allegation__complainant__age', [0, 20, 30, 40, 50]),
year=ExtractYear('start_date'))
query = query.values('name', 'year').order_by('name', 'year').annotate(
count=models.Count('name'),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU', then=1),
default=models.Value(0),
output_field=models.IntegerField())))
return Officer._group_and_sort_aggregations(list(query))
def get_by_payment_sum(self, country=None):
qs = PharmaCompany.objects.all()
if country is not None:
qs = qs.filter(country=country)
if country is not None:
qs = qs.annotate(
amount=models.Sum('pharmapayment__amount'),
amount_currency=models.Value(
PharmaPayment.ORIGIN_CURRENCY[country],
output_field=models.CharField())
)
else:
qs = qs.annotate(
amount=models.Sum('pharmapayment__amount_euro'),
amount_currency=models.Value(
'EUR',
output_field=models.CharField())
)
qs = qs.filter(amount__isnull=False)
qs = qs.order_by('-amount')
return qs
def complaint_category_aggregation(self):
query_set = OfficerAllegation.objects.filter(
officer__officerhistory__unit=self).distinct().annotate(
name=models.Case(
models.When(allegation_category__category__isnull=True,
then=models.Value('Unknown')),
default='allegation_category__category',
output_field=models.CharField())).values('name').annotate(
count=models.Count('allegation__crid', distinct=True),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU',
then=1),
default=0,
output_field=models.IntegerField())))
return list(query_set)
def police_districts_with_allegation_per_capita(cls):
RacePopulation = apps.get_app_config('data').get_model(
'RacePopulation')
racepopulation = RacePopulation.objects.filter(
area=models.OuterRef('pk')).values('area')
population = racepopulation.annotate(s=models.Sum('count')).values('s')
return cls.objects.filter(area_type='police-districts').annotate(
population=models.Subquery(population),
complaint_count=Count(
'allegation',
distinct=True)).filter(population__isnull=False).annotate(
allegation_per_capita=models.ExpressionWrapper(
Cast(F('complaint_count'), models.FloatField()) /
F('population'),
output_field=models.FloatField()))
def complaint_category_aggregation(self):
query = self.officerallegation_set.all()
query = query.annotate(name=models.Case(
models.When(allegation_category__category__isnull=True,
then=models.Value('Unknown')),
default='allegation_category__category',
output_field=models.CharField()),
year=ExtractYear('start_date'))
query = query.values('name', 'year').order_by('name', 'year').annotate(
count=models.Count('name'),
sustained_count=models.Sum(
models.Case(models.When(final_finding='SU', then=1),
default=models.Value(0),
output_field=models.IntegerField())))
return Officer._group_and_sort_aggregations(list(query))
def _pupil_total(self, attr):
total = self.pupil_set.aggregate(models.Sum(attr))
return total.values()[0] or 0
def annotate_observations(self):
return self.annotate(
observations=models.Sum('contributorrank__observations'),
).filter(observations__gt=0)
def image_bands(self):
return self.images.aggregate(models.Sum('number_of_bands'))['number_of_bands__sum']