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)