def complainant_gender_aggregation(self):
query_set = Complainant.objects.filter(
allegation__officerallegation__officer__officerhistory__unit=self
).distinct().annotate(complainant_gender=models.Case(
models.When(gender='', then=models.Value('Unknown')),
default='gender',
output_field=models.CharField())).values(
'complainant_gender').annotate(
count=models.Count('id', distinct=True))
sustained_count_query_set = Complainant.objects.filter(
allegation__officerallegation__officer__officerhistory__unit=self,
allegation__officerallegation__final_finding='SU').distinct(
).annotate(complainant_gender=models.Case(
models.When(gender='', then=models.Value('Unknown')),
default='gender',
output_field=models.CharField())).values(
'complainant_gender').annotate(
sustained_count=models.Count('id', distinct=True))
sustained_count_results = {
obj['complainant_gender']: obj['sustained_count']
for obj in sustained_count_query_set
}
return [{
'name':
GENDER_DICT.get(obj['complainant_gender'], 'Unknown'),
'count':
obj['count'],
'sustained_count':
sustained_count_results.get(obj['complainant_gender'], 0)
} for obj in query_set if obj['count'] > 0]
def _officer_service_year(min_date, max_date):
query = Officer.objects.filter(appointed_date__isnull=False)
query = query.annotate(
end_date=models.Case(models.When(Q(resignation_date__isnull=False,
resignation_date__lt=max_date),
then='resignation_date'),
default=models.Value(max_date),
output_field=models.DateField()),
start_date=models.Case(models.When(appointed_date__lt=min_date,
then=models.Value(min_date)),
default='appointed_date',
output_field=models.DateField()),
)
# filter-out officer has service time smaller than 1 year
query = query.filter(end_date__gte=F('start_date') + timedelta(days=365))
return query.annotate(
officer_id=F('id'),
service_year=(
Func(
F('end_date') - F('start_date'),
template=
"ROUND(CAST(%(function)s('day', %(expressions)s) / 365.0 as numeric), 4)",
function='DATE_PART',
output_field=models.FloatField()
) # in order to easy to test and calculate, we only get 4 decimal points
))
def complainant_race_aggregation(self):
query_set = Complainant.objects.filter(
allegation__officerallegation__officer__officerhistory__unit=self
).distinct().annotate(name=models.Case(
models.When(race__isnull=True, then=models.Value('Unknown')),
models.When(race__in=['n/a', 'n/a ', 'nan', ''],
then=models.Value('Unknown')),
default='race',
output_field=models.CharField())).values('name').annotate(
count=models.Count('id', distinct=True))
sustained_count_query_set = Complainant.objects.filter(
allegation__officerallegation__officer__officerhistory__unit=self,
allegation__officerallegation__final_finding='SU').distinct(
).annotate(name=models.Case(
models.When(race__isnull=True, then=models.Value('Unknown')),
models.When(race__in=['n/a', 'n/a ', 'nan', ''],
then=models.Value('Unknown')),
default='race',
output_field=models.CharField())).values('name').annotate(
sustained_count=models.Count('id', distinct=True))
sustained_count_results = {
obj['name']: obj['sustained_count']
for obj in sustained_count_query_set
}
return [{
'name': obj['name'],
'count': obj['count'],
'sustained_count': sustained_count_results.get(obj['name'], 0)
} for obj in query_set if obj['count'] > 0]
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 member_race_aggregation(self):
query_set = Officer.objects.filter(
officerhistory__unit=self).distinct().annotate(name=models.Case(
models.When(race__isnull=True, then=models.Value('Unknown')),
models.When(race__in=['n/a', 'n/a ', 'nan', ''],
then=models.Value('Unknown')),
default='race',
output_field=models.CharField())).values('name').annotate(
count=models.Count('id', distinct=True))
return list(query_set)
def dmw_value_list(dmw):
dmw = dmw.annotate(
point=models.Value(None, output_field=models.PointField()))
dmw = dmw.annotate(rc=models.Value("dmw", output_field=models.CharField()))
#dmw = dmw.annotate(record_type=models.Value(None, output_field=models.PointField()))
dmw = dmw.values_list("mpoly", "metadata__identifier",
"metadata__project_name", "metadata__project_pdf",
"metadata__expected_start_date",
"metadata__audience", "metadata__outline",
"metadata__location", "metadata__ballpark",
"metadata__committee", "metadata__category__code",
"point", "rc")
return dmw
def member_gender_aggregation(self):
query_set = Officer.objects.filter(officerhistory__unit=self).distinct(
).annotate(member_gender=models.Case(
models.When(gender='', then=models.Value('Unknown')),
models.When(gender__isnull=True, then=models.Value('Unknown')),
default='gender',
output_field=models.CharField())).values('member_gender').annotate(
count=models.Count('id', distinct=True))
return [{
'name': GENDER_DICT.get(obj['member_gender'], 'Unknown'),
'count': obj['count']
} for obj in query_set if obj['count'] > 0]
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 _compute_metric(year_end, percentile_group):
data_range = PERCENTILE_MAP[percentile_group]['range']
if not data_range:
return Officer.objects.none()
min_datetime, max_datetime = data_range
if year_end:
max_datetime = min(max_datetime,
datetime(year_end, 12, 31, tzinfo=pytz.utc))
if min_datetime + timedelta(days=365) > max_datetime:
return Officer.objects.none()
query = _officer_service_year(min_datetime.date(), max_datetime.date())
query = query.annotate(
year=models.Value(year_end, output_field=IntegerField()))
func_map = PERCENTILE_MAP[percentile_group]['percentile_funcs']
for metric, func in func_map.items():
num_key = f'num_{metric}'
metric_key = f'metric_{metric}'
query = query.annotate(**{num_key: func(min_datetime, max_datetime)})
query = query.annotate(
**{
metric_key:
models.ExpressionWrapper(Round(F(num_key) / F('service_year')),
output_field=models.FloatField())
})
return query
def get_random_geometry_point(self) -> Point:
points: List[Point] = self.__class__.objects.filter(
id=self.id).annotate( # type: ignore
rand_point=GeneratePoints(
'geometry',
models.Value(1) # type: Any
)).first().rand_point
return points[0]
def comm_value_list(comm):
comm = comm.annotate(
rc=models.Value("comm", output_field=models.CharField()))
comm = comm.values_list("activity__mpoly", "code", "project_name",
"document_url", "start_date", "audience",
"objective", "address", "estimation", "group_name",
"category__code", "point", "rc")
return comm
def category_names(self):
query = self.officer_allegations.annotate(
name=models.Case(
models.When(allegation_category__isnull=True, then=models.Value('Unknown')),
default='allegation_category__category',
output_field=models.CharField()))
query = query.values('name').distinct()
results = sorted([result['name'] for result in query])
return results if results else ['Unknown']
def complainant_races(self):
query = self.complainant_set.annotate(
name=models.Case(
models.When(race__in=['n/a', 'n/a ', 'nan', ''], then=models.Value('Unknown')),
default='race',
output_field=models.CharField()))
query = query.values('name').distinct()
results = sorted([result['name'] for result in query])
return results if results else ['Unknown']
def complainant_genders(self):
query = self.complainant_set.annotate(
name=models.Case(
models.When(gender='', then=models.Value('Unknown')),
default='gender',
output_field=models.CharField()))
query = query.values('name').distinct()
results = [GENDER_DICT.get(result['name'], 'Unknown') for result in query]
return results if results else ['Unknown']
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)
