def place_list(self, limit, offset):
hardcoded = [x.place for x in NeighborhoodEntry.objects.filter(neighborhood=self).order_by('rank')]
if offset == 0:
to_fetch = (limit - len(hardcoded)) + 1
else:
to_fetch = limit + 1
if self.bounds:
close_by = Place.objects.filter(
Q(geom__within=self.bounds)
).annotate(
has_card=models.Count('gift_card_url')
).exclude(
place_id__in=[x.place_id for x in hardcoded]
).order_by('-has_card', '-num_ratings')[offset:offset + to_fetch]
else:
close_by = Place.objects.filter(
Q(geom__distance_lt=(self.geom, D(m=2500)))
).exclude(
place_id__in=[x.place_id for x in hardcoded]
).annotate(
has_card=models.Count('gift_card_url')
).annotate(
distance=Distance('geom', self.geom)
).order_by('-has_card', 'distance')[offset:offset + to_fetch]
more_available = len(close_by) == to_fetch
if offset == 0:
joined = (hardcoded + list(close_by))
else:
joined = list(close_by)
end_list = -1 if more_available else len(joined)
return joined[0:end_list], more_available
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 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 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 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 statistics(self):
queryset = self.viewpoints.annotate(
missing=models.Count('pk', filter=models.Q(
pictures__isnull=True
)),
pending=models.Count('pictures', filter=models.Q(
pictures__state=STATES.DRAFT
)),
refused=models.Count('pictures', filter=models.Q(
pictures__state=STATES.REFUSED
)),
).values('missing', 'pending', 'refused')
try:
return queryset[0]
except IndexError:
return {'missing': 0, 'pending': 0, 'refused': 0}
def with_stats(self):
return self.annotate(
viewpoints_total=models.Count("viewpoints", distinct=True),
pictures_submited=models.Count(
"pictures__pk",
filter=models.Q(pictures__state=Picture.SUBMITTED),
distinct=True,
),
pictures_accepted=models.Count(
"pictures__pk",
filter=models.Q(pictures__state=Picture.ACCEPTED),
distinct=True,
),
pictures_missing=models.F("viewpoints_total")
- models.F("pictures_submited")
- models.F("pictures_accepted"),
)
def _honorable_mention_count_query(_, max_datetime):
return models.Count(models.Case(
models.When(award__start_date__lte=max_datetime.date(),
award__award_type='Honorable Mention',
then='award'),
output_field=models.CharField(),
),
distinct=True)
def _allegation_internal_count_query(min_datetime, max_datetime):
return models.Count(models.Case(
models.When(Q(
officerallegation__allegation__is_officer_complaint=True,
officerallegation__allegation__incident_date__gte=min_datetime,
officerallegation__allegation__incident_date__lte=max_datetime),
then='officerallegation')),
distinct=True)
def _trr_count_query(min_datetime, max_datetime):
return models.Count(models.Case(
models.When(Q(trr__trr_datetime__date__gte=min_datetime,
trr__trr_datetime__date__lte=max_datetime),
then='trr'),
output_field=models.CharField(),
),
distinct=True)
def _allegation_count_query(min_datetime, max_datetime):
return models.Count(models.Case(
models.When(Q(
officerallegation__allegation__incident_date__gte=min_datetime,
officerallegation__allegation__incident_date__lte=max_datetime),
then='officerallegation'),
output_field=models.CharField(),
),
distinct=True)
def member_age_aggregation(self):
query_set = Officer.objects.filter(officerhistory__unit=self).distinct(
).annotate(member_age=models.Case(
models.When(birth_year__isnull=True, then=None),
default=now().year - F('birth_year'),
output_field=models.IntegerField())).annotate(
name=get_num_range_case('member_age', [0, 20, 30, 40, 50])
).values('name').annotate(count=models.Count('id', distinct=True))
return list(query_set)
def with_rank(self):
ranked = UrbanInstituteRentalCrisisData.objects.annotate(
rank=models.Window(expression=Rank(),
partition_by=[models.F('year')],
order_by=(models.F('aaa_units') /
models.F('eli_renters')).desc()),
total=models.Window(expression=models.Count(['year']),
partition_by=[models.F('year')]))
return ranked
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 date_counts(self):
"""
Returns a dictionary mapping {item_date: count}.
"""
# TODO: values + annotate doesn't seem to play nice with GeoQuerySet
# at the moment. This is the changeset where it broke:
# http://code.djangoproject.com/changeset/10326
from django.db.models.query import QuerySet
qs = QuerySet.values(self,
'item_date').annotate(count=models.Count('id'))
return dict([(v['item_date'], v['count']) for v in qs])
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 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 t_shirt_size_counts(self):
if not self.pk:
package_transactions = self.campaign.user_attendances_for_delivery(
)
t_shirts = TShirtSize.objects.filter(
userattendance__in=package_transactions)
t_shirts = t_shirts.annotate(
size_count=models.Count('userattendance'))
else:
package_transactions = PackageTransaction.objects.filter(
team_package__box__delivery_batch=self)
t_shirts = TShirtSize.objects.filter(
packagetransaction__in=package_transactions)
t_shirts = t_shirts.annotate(
size_count=models.Count('packagetransaction'))
t_shirt_counts = t_shirts.values_list('name', 'size_count')
ordered_t_shirt_counts = collections.OrderedDict([
(size.name, 0) for size in TShirtSize.objects.filter(
campaign=self.campaign, ).order_by('name')
])
for count in t_shirt_counts:
ordered_t_shirt_counts[count[0]] = count[1]
return ordered_t_shirt_counts.items()
def complainant_age_aggregation(self):
query_set = Complainant.objects.filter(
allegation__officerallegation__officer__officerhistory__unit=self
).distinct().annotate(name=get_num_range_case(
'age', [0, 20, 30, 40, 50])).values('name').annotate(
count=models.Count('id', distinct=True)).order_by('name')
sustained_count_query_set = Complainant.objects.filter(
allegation__officerallegation__officer__officerhistory__unit=self,
allegation__officerallegation__final_finding='SU').distinct(
).annotate(name=get_num_range_case(
'age', [0, 20, 30, 40, 50])).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 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 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 organisations(self):
"""
Return a qs of organisations, with the most frequently related first.
Each organisation is also annotated with 'position_count' which might be
useful.
This is intended as an alternative to assigning a org to each
position_title. Instead we can deduce it from the postions.
"""
orgs = (Organisation.objects.filter(position__title=self).annotate(
position_count=models.Count('position')).order_by(
'-position_count'))
return orgs
def with_rank(self):
ranked = HudPitData.objects.annotate(
rank=models.Window(expression=Rank(),
partition_by=[
models.F('datatype'),
models.F('geography'),
models.F('year')
],
order_by=models.F('value').asc()),
total=models.Window(expression=models.Count(
['datatype', 'geography', 'year']),
partition_by=[
models.F('datatype'),
models.F('geography'),
models.F('year')
]))
return ranked
def with_rank(self):
ranked = JCHSData.objects.exclude(datapoint='United States').annotate(
asc_rank=models.Window(expression=Rank(),
partition_by=[
models.F('datatype'),
models.F('source'),
models.F('date')
],
order_by=models.F('value').asc()),
desc_rank=models.Window(expression=Rank(),
partition_by=[
models.F('datatype'),
models.F('source'),
models.F('date')
],
order_by=models.F('value').desc()),
total=models.Window(expression=models.Count(
['datatype', 'source', 'date']),
partition_by=[
models.F('datatype'),
models.F('source'),
models.F('date')
]))
return ranked
def add_annotations(self, queryset):
return queryset.annotate(
study_count=models.Count('observationalstudy'))
def cluster_points(self, precision):
"""Cluster modeles by geohash value with provided `precision`."""
field = self._geohash_field()
return self._annotate_geohash(precision).values(field).annotate(
cluster_count=models.Count(field),
)
def get_list(self):
return self.get_visible().annotate(
num_publicbodies=models.Count('publicbody'))
def get_category_list(self):
count = models.Count('categorized_publicbodies')
return (self.get_queryset().filter(
depth=1,
is_topic=True).order_by('name').annotate(num_publicbodies=count))
def get_topic_list(self):
return (self.get_queryset().filter(is_topic=True).order_by(
'rank',
'name').annotate(num_publicbodies=models.Count('publicbodies')))
