def get_queryset(self):
queryset = super(RatingStatisticsManager, self).get_queryset()
queryset = queryset.annotate(
num_ratings=Coalesce(
Sum(
Case(
When(ratings__score__isnull=False, then=1),
output_field=models.PositiveIntegerField(),
), ), 0),
mean_score=Avg('ratings__score'),
score_stddev=StdDev('ratings__score', sample=True),
).annotate(score_sem=F('score_stddev') / Sqrt(
F('num_ratings'), output_field=models.FloatField()), ).annotate(
score_95ci_lower=Greatest(
Coalesce(
F('mean_score') - self.z_crit * F('score_sem'),
settings.DEFAULT_MIN_SCORE),
settings.DEFAULT_MIN_SCORE),
score_95ci_upper=Least(
Coalesce(
F('mean_score') + self.z_crit * F('score_sem'),
settings.DEFAULT_MAX_SCORE),
settings.DEFAULT_MAX_SCORE),
)
return queryset
def average_response_time(self):
"""Get the average response time from a submitted to completed request"""
requests = self.get_requests()
avg = (requests.aggregate(avg=Coalesce(
ExtractDay(
Avg(F('datetime_done') -
F('composer__datetime_submitted'))), Value(0)))['avg'])
return avg
def get_queryset(self):
weighted_avg = (
Sum(F('rating__rating') * F('rating__benchmark__weight'),
output_field=FloatField()) /
Sum(F('rating__benchmark__weight'), output_field=FloatField()))
days = Cast(Coalesce(F('deadline'), Now()) - Now(),
output_field=FloatField()) / 1000 / 1000 / (60 * 60 * 24)
return Offer.objects.order_by('deadline').annotate(
weighted_avg=weighted_avg, days=days)
def lead_browser_totals(user: User,
date_from: date,
date_to: date,
projects: list = None,
label_type=None,
label_values=None,
os_groups=None,
browser_groups=None,
traffic_channels=None):
leads_qs = Lead.objects.filter(pixel__project__user=user)
leads_qs = _apply_lead_common_filters(leads_qs, date_from, date_to,
projects, label_type, label_values,
os_groups, browser_groups,
traffic_channels)
leads_qs = leads_qs.annotate(group_name=Coalesce(
F('browser__family__group__name'), Value(_('Unknown'))))
leads_qs = leads_qs.values('group_name')
leads_qs = leads_qs.annotate(leads_count=Count('id'))
leads_qs = leads_qs.order_by('group_name')
return leads_qs
def annotated_gallery(self):
"""Annotate gallery with
- ``linked_content``, which contains the pk of the associated content if any ;
- ``image_count``, which contains the number of images.
:rtype: QuerySet
"""
from zds.tutorialv2.models.database import PublishableContent
from zds.gallery.models import Image
linked_content = PublishableContent.objects.filter(
gallery__pk=OuterRef('pk')).values('pk')
images = Image.objects\
.filter(gallery__pk=OuterRef('pk'))\
.values('gallery')\
.annotate(count=Count('pk'))\
.values('count')
return self\
.annotate(linked_content=Subquery(linked_content))\
.annotate(image_count=Coalesce(Subquery(images), 0))
def lead_os_totals(user: User,
date_from: date,
date_to: date,
is_mobile,
projects: list = None,
label_type=None,
label_values=None,
os_groups=None,
browser_groups=None,
traffic_channels=None):
leads_qs = Lead.objects.filter(pixel__project__user=user)
leads_qs = _apply_lead_common_filters(leads_qs, date_from, date_to,
projects, label_type, label_values,
os_groups, browser_groups,
traffic_channels)
leads_qs = leads_qs.filter(os_version__family__group__is_mobile=is_mobile)
leads_qs = leads_qs.annotate(lead_duration=ExpressionWrapper(
F('created') - F('session_started'), output_field=DurationField()))
leads_qs = leads_qs.annotate(group_name=Coalesce(
F('os_version__family__group__name'), Value(_('Unknown'))))
leads_qs = leads_qs.values('group_name')
leads_qs = leads_qs.annotate(leads_count=Count('id'))
leads_qs = leads_qs.order_by('group_name')
return leads_qs
def get_leads(user: User,
date_from: date,
date_to: date,
projects: list = None,
label_type=None,
label_values=None,
os_groups=None,
browser_groups=None,
traffic_channels=None):
leads_qs = Lead.objects.filter(pixel__project__user=user)
leads_qs = _apply_lead_common_filters(leads_qs, date_from, date_to,
projects, label_type, label_values,
os_groups, browser_groups,
traffic_channels)
leads_qs = leads_qs.order_by('-created')
leads_qs = leads_qs.annotate(os_group=F('os_version__family__group__name'))
device_types = dict(DeviceType.TYPES)
leads_qs = leads_qs.annotate(device=Concat(
Case(When(device_model__device_type__category=DeviceType.PHONE,
then=Value(str(device_types[DeviceType.PHONE]))),
When(device_model__device_type__category=DeviceType.TABLET,
then=Value(str(device_types[DeviceType.TABLET]))),
When(device_model__device_type__category=DeviceType.DESKTOP,
then=Value(str(device_types[DeviceType.DESKTOP]))),
default=Value('Unknown')), Value(' '),
F('device_model__brand__name'), Value(' '), F('device_model__model')))
cur_language = get_language()
if cur_language == 'ru-ru':
leads_qs = leads_qs.annotate(city=Coalesce(
Case(When(geo__name_ru__exact='', then=None),
When(geo__name_ru__isnull=False, then='geo__name_ru'),
default=None), F('geo__name')))
else:
leads_qs = leads_qs.annotate(city=F('geo__name'))
leads_qs = leads_qs.select_related('geo')
return leads_qs
def _load_period_ipstat(fill_date: date, period: timedelta):
end_time = timezone.make_aware(
time(hour=23, minute=59, second=59, microsecond=999999))
end_date = datetime.combine(fill_date, end_time)
start_date = (end_date - period).replace(hour=0,
minute=0,
second=0,
microsecond=0)
return Lead.objects \
.filter(session_started__range=(start_date, end_date)) \
.filter(ip_addr__isnull=False) \
.values('ip_addr', 'geo', 'geo__country', 'geo__postal_code', 'provider') \
.annotate(s_cnt=Count('id')) \
.annotate(s_time=Sum(ExpressionWrapper(
Coalesce('created', 'last_event_time') - F('session_started'),
output_field=DurationField()))) \
.annotate(s0_cnt=Count(Case(
When(created__isnull=True, then=F('id')),
default=None, output_field=UUIDField()))) \
.annotate(s_beg=Cast(Avg(
Cast(F('session_started'), output_field=TimeField())
), output_field=TimeField())) \
.annotate(user_ids=ArrayAgg('pixel__project__user__id', distinct=True))\
.annotate(cnt_dev=Count('device_id'))
class AgencyViewSet(viewsets.ModelViewSet):
"""API views for Agency"""
# pylint: disable=too-many-public-methods
queryset = (Agency.objects.order_by('id').select_related(
'jurisdiction', 'parent', 'appeal_agency').prefetch_related(
Prefetch(
'emails',
queryset=EmailAddress.objects.filter(
status='good',
agencyemail__request_type='primary',
agencyemail__email_type='to',
),
to_attr='primary_emails',
),
Prefetch(
'phones',
queryset=PhoneNumber.objects.filter(
type='fax',
status='good',
agencyphone__request_type='primary',
),
to_attr='primary_faxes',
),
Prefetch(
'addresses',
queryset=Address.objects.filter(
agencyaddress__request_type='primary', ),
to_attr='primary_addresses',
),
'types',
).annotate(
average_response_time_=Coalesce(
ExtractDay(
Avg(
F('foiarequest__datetime_done') -
F('foiarequest__composer__datetime_submitted'))),
Value(0)),
fee_rate_=Coalesce(
100 * CountWhen(foiarequest__price__gt=0,
output_field=FloatField()) / NullIf(
Count('foiarequest'),
Value(0),
output_field=FloatField(),
), Value(0)),
success_rate_=Coalesce(
100 * CountWhen(foiarequest__status__in=['done', 'partial'],
output_field=FloatField()) / NullIf(
Count('foiarequest'),
Value(0),
output_field=FloatField(),
), Value(0)),
number_requests=Count('foiarequest'),
number_requests_completed=CountWhen(foiarequest__status='done'),
number_requests_rejected=CountWhen(foiarequest__status='rejected'),
number_requests_no_docs=CountWhen(foiarequest__status='no_docs'),
number_requests_ack=CountWhen(foiarequest__status='ack'),
number_requests_resp=CountWhen(foiarequest__status='processed'),
number_requests_fix=CountWhen(foiarequest__status='fix'),
number_requests_appeal=CountWhen(foiarequest__status='appealing'),
number_requests_pay=CountWhen(foiarequest__status='payment'),
number_requests_partial=CountWhen(foiarequest__status='partial'),
number_requests_lawsuit=CountWhen(foiarequest__status='lawsuit'),
number_requests_withdrawn=CountWhen(
foiarequest__status='abandoned'),
))
serializer_class = AgencySerializer
# don't allow ordering by computed fields
ordering_fields = [
f for f in AgencySerializer.Meta.fields if f not in (
'absolute_url',
'average_response_time',
'fee_rate',
'success_rate',
) and not f.startswith(('has_', 'number_'))
]
def get_queryset(self):
"""Filter out non-approved agencies for non-staff"""
if self.request.user.is_staff:
return self.queryset
else:
return self.queryset.filter(status='approved')
class Filter(django_filters.FilterSet):
"""API Filter for Agencies"""
jurisdiction = django_filters.NumberFilter(name='jurisdiction__id')
types = django_filters.CharFilter(
name='types__name',
lookup_expr='iexact',
)
class Meta:
model = Agency
fields = ('name', 'status', 'jurisdiction', 'types',
'requires_proxy')
filter_class = Filter
def total_points(self):
return self.aggregate(_sum=Coalesce(Sum('points'), 0))['_sum']