def get_stale_tasks(self):
"""Get stale tasks"""
# pylint: disable=import-outside-toplevel
from muckrock.task.models import (
NewAgencyTask,
OrphanTask,
FlaggedTask,
PortalTask,
SnailMailTask,
)
stale_tasks = OrderedDict()
stale_tasks["Processing Requests"] = (FOIARequest.objects.filter(
status="submitted",
date_processing__lt=(
date.today() -
timedelta(5))).order_by("date_processing").annotate(
days_old=ExtractDay(Now() - F("date_processing"))))[:5]
task_types = [
(NewAgencyTask, 3),
(OrphanTask, 5),
(FlaggedTask, 5),
(PortalTask, 5),
(SnailMailTask, 5),
]
for task_type, days_old in task_types:
stale_tasks[task_type.type] = (task_type.objects.filter(
date_created__lt=(timezone.now() - timedelta(days_old)),
resolved=False,
).order_by("date_created").annotate(days_old=ExtractDay(
Cast(Now() - F("date_created"), DurationField())))[:5])
return stale_tasks
def __init__(self, user_pk, foia_pks):
super(ExportCsv,
self).__init__(user_pk,
''.join(str(pk) for pk in foia_pks[:100]))
self.foias = (FOIARequest.objects.filter(
pk__in=foia_pks).select_related(
'composer__user',
'agency__jurisdiction__parent',
).only(
'composer__user__username',
'title',
'status',
'slug',
'agency__jurisdiction__name',
'agency__jurisdiction__slug',
'agency__jurisdiction__id',
'agency__jurisdiction__parent__name',
'agency__jurisdiction__parent__id',
'agency__name',
'agency__id',
'date_followup',
'date_estimate',
'embargo',
'composer__requested_docs',
).annotate(
days_since_submitted=ExtractDay(
Cast(Now() - F('composer__datetime_submitted'),
DurationField())),
days_since_updated=ExtractDay(
Cast(Now() - F('datetime_updated'), DurationField())),
project_names=StringAgg('projects__title', ',', distinct=True),
tag_names=StringAgg('tags__name', ',', distinct=True),
))
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 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_data(self):
"""Get the oldest flag tasks"""
tasks = (FlaggedTask.objects.filter(
resolved=False).get_undeferred().annotate(days=ExtractDay(
Cast(Now() - F("date_created"), DurationField()))).order_by(
"-days").values("text", "days")[:5])
return [{
"label":
t["text"]
if len(t["text"]) < 28 else "{}...".format(t["text"][:28]),
"value":
t["days"],
} for t in tasks]
def get_data(self):
"""Get the oldest flag tasks"""
tasks = (FlaggedTask.objects.filter(
resolved=False).get_undeferred().annotate(days=ExtractDay(
Cast(Now() - F('date_created'), DurationField()))).order_by(
'-days').values('text', 'days')[:5])
return [{
'label':
t['text']
if len(t['text']) < 28 else u'{}...'.format(t['text'][:28]),
'value':
t['days'],
} for t in tasks]
def render_to_response(self, context, **kwargs):
"""Allow CSV responses"""
wants_csv = self.request.GET.get('content_type') == 'csv'
has_perm = self.request.user.has_perm('foia.export_csv')
if wants_csv and has_perm:
psuedo_buffer = Echo()
fields = (
(lambda f: f.user.username, 'User'),
(lambda f: f.title, 'Title'),
(lambda f: f.get_status_display(), 'Status'),
(lambda f: settings.MUCKROCK_URL + f.get_absolute_url(),
'URL'),
(lambda f: f.jurisdiction.name, 'Jurisdiction'),
(lambda f: f.jurisdiction.pk, 'Jurisdiction ID'),
(
lambda f: f.jurisdiction.get_level_display(),
'Jurisdiction Level',
),
(
lambda f: f.jurisdiction.parent.name
if f.jurisdiction.level == 'l' else f.jurisdiction.name,
'Jurisdiction State',
),
(lambda f: f.agency.name if f.agency else '', 'Agency'),
(lambda f: f.agency.pk if f.agency else '', 'Agency ID'),
(lambda f: f.date_followup, 'Followup Date'),
(lambda f: f.date_estimate, 'Estimated Completion Date'),
(lambda f: f.composer.requested_docs, 'Requested Documents'),
(lambda f: f.current_tracking_id(), 'Tracking Number'),
(lambda f: f.embargo, 'Embargo'),
(lambda f: f.days_since_submitted, 'Days since submitted'),
(lambda f: f.days_since_updated, 'Days since updated'),
(lambda f: f.project_names, 'Projects'),
(lambda f: f.tag_names, 'Tags'),
)
foias = (context['paginator'].object_list.select_related(
None).select_related(
'composer__user',
'agency__jurisdiction',
).prefetch_related('tracking_ids', ).only(
'composer__user__username',
'title',
'status',
'slug',
'agency__jurisdiction__name',
'agency__jurisdiction__slug',
'agency__jurisdiction__id',
'agency__name',
'agency__id',
'date_followup',
'date_estimate',
'embargo',
'composer__requested_docs',
).annotate(
days_since_submitted=ExtractDay(
Cast(Now() - F('composer__datetime_submitted'),
DurationField())),
days_since_updated=ExtractDay(
Cast(Now() - F('datetime_updated'), DurationField())),
project_names=StringAgg('projects__title',
',',
distinct=True),
tag_names=StringAgg('tags__name', ',', distinct=True),
))
writer = csv.writer(psuedo_buffer)
response = StreamingHttpResponse(
chain(
[writer.writerow(f[1] for f in fields)],
(writer.writerow(f[0](foia) for f in fields)
for foia in foias),
),
content_type='text/csv',
)
response[
'Content-Disposition'] = 'attachment; filename="requests.csv"'
return response
else:
return super(RequestList,
self).render_to_response(context, **kwargs)
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 get_processing_days(self):
"""Get total processing days for flagged tasks"""
return (self.exclude(resolved=True).get_undeferred().aggregate(
days=ExtractDay(
Cast(Sum(Now() -
F("date_created")), models.DurationField())))["days"])
def get_data(email_or_fax):
"""Helper function to get email or fax data"""
if email_or_fax == 'email':
address_model = EmailAddress
error_model = EmailError
confirm_rel = 'to_emails'
elif email_or_fax == 'fax':
address_model = PhoneNumber
error_model = FaxError
confirm_rel = 'faxes'
open_requests = (self.agency.foiarequest_set.get_open().order_by(
'%s__status' % email_or_fax,
email_or_fax).exclude(**{
email_or_fax: None
}).select_related(
'agency__jurisdiction',
'composer',
'email',
'fax',
'portal',
).annotate(latest_response=ExtractDay(
Cast(
Now() - Max(
Case(
When(communications__response=True,
then='communications__datetime'))),
models.DurationField(),
))))
grouped_requests = [(k, list(v)) for k, v in groupby(
open_requests, lambda f: getattr(f, email_or_fax))]
# do a seperate query for per email addr/fax number stats
addresses = (address_model.objects.annotate(
error_count=Count('errors', distinct=True),
last_error=Max('errors__datetime'),
last_confirm=Max('%s__confirmed_datetime' % confirm_rel),
).prefetch_related(
Prefetch(
'errors',
error_model.objects.select_related(
'%s__communication__foia__agency__jurisdiction' %
email_or_fax).order_by('-datetime'))))
if email_or_fax == 'email':
addresses = addresses.annotate(
last_open=Max('opens__datetime'), )
addresses = addresses.in_bulk(g[0].pk for g in grouped_requests)
review_data = []
for addr, foias in grouped_requests:
# fetch the address with the annotated stats
addr = addresses[addr.pk]
review_data.append({
'address':
addr,
'error':
addr.status == 'error',
'errors':
addr.errors.all()[:5],
'foias':
foias,
'total_errors':
addr.error_count,
'last_error':
addr.last_error,
'last_confirm':
addr.last_confirm,
'last_open':
addr.last_open if email_or_fax == 'email' else None,
'checkbox_name':
'foias-%d-%s-%d' % (self.pk, email_or_fax, addr.pk),
'email_or_fax':
email_or_fax,
})
return review_data
def get_data(email_or_fax):
"""Helper function to get email or fax data"""
if email_or_fax == 'email':
address_model = EmailAddress
confirm_rel = 'to_emails'
error_fields = [
'email',
'datetime',
'recipient',
'code',
'error',
'event',
'reason',
]
elif email_or_fax == 'fax':
address_model = PhoneNumber
confirm_rel = 'faxes'
error_fields = [
'fax',
'datetime',
'recipient',
'error_type',
'error_code',
'error_id',
]
open_requests = (
self.agency.foiarequest_set.get_open().order_by(
'%s__status' % email_or_fax, email_or_fax
).exclude(**{
email_or_fax: None
}).select_related(
'agency__jurisdiction',
'composer',
'email',
'fax',
'portal',
).annotate(
latest_response=ExtractDay(
Cast(
Now() - Max(
Case(
When(
communications__response=True,
then='communications__datetime'
)
)
),
models.DurationField(),
)
)
).only(
'pk',
'status',
'title',
'slug',
'agency__jurisdiction__slug',
'agency__jurisdiction__id',
'composer__datetime_submitted',
'date_estimate',
'portal__name',
'email__email',
'email__name',
'email__status',
'fax__number',
'fax__status',
)
)
grouped_requests = [
(k, list(v)) for k, v in
groupby(open_requests, lambda f: getattr(f, email_or_fax))
]
# do seperate queries for per email addr/fax number stats
# do annotations separately for performance reasons (limit joins)
addresses = (
address_model.objects.annotate(
error_count=Count('errors', distinct=True),
last_error=Max('errors__datetime'),
)
)
addresses = addresses.in_bulk(g[0].pk for g in grouped_requests)
addresses_confirm = address_model.objects.annotate(
last_confirm=Max('%s__confirmed_datetime' % confirm_rel),
)
addresses_confirm = addresses_confirm.in_bulk(
g[0].pk for g in grouped_requests
)
if email_or_fax == 'email':
addresses_open = address_model.objects.annotate(
last_open=Max('opens__datetime'),
)
addresses_open = addresses_open.in_bulk(
g[0].pk for g in grouped_requests
)
review_data = []
for addr, foias in grouped_requests:
# fetch the address with the annotated stats
addr = addresses[addr.pk]
review_data.append({
'address':
addr,
'error':
addr.status == 'error',
'errors':
addr.errors.select_related(
'%s__communication__foia__agency__jurisdiction' %
email_or_fax
).order_by('-datetime').only(
*error_fields + [
'%s__communication__foia__agency__jurisdiction__slug'
% email_or_fax,
'%s__communication__foia__slug' % email_or_fax,
'%s__communication__foia__title' % email_or_fax,
]
)[:5],
'foias':
foias,
'unacknowledged':
any(f.status == 'ack' for f in foias),
'total_errors':
addr.error_count,
'last_error':
addr.last_error,
'last_confirm':
addresses_confirm[addr.pk].last_confirm,
'last_open':
addresses_open[addr.pk].last_open
if email_or_fax == 'email' else None,
'checkbox_name':
u'foias-%d-%s-%d' % (self.pk, email_or_fax, addr.pk),
'email_or_fax':
email_or_fax,
})
return review_data
def export_csv(foia_pks, user_pk):
"""Export a csv of the selected FOIA requests"""
fields = (
(lambda f: f.user.username, 'User'),
(lambda f: f.title, 'Title'),
(lambda f: f.get_status_display(), 'Status'),
(lambda f: settings.MUCKROCK_URL + f.get_absolute_url(), 'URL'),
(lambda f: f.jurisdiction.name, 'Jurisdiction'),
(lambda f: f.jurisdiction.pk, 'Jurisdiction ID'),
(
lambda f: f.jurisdiction.get_level_display(),
'Jurisdiction Level',
),
(
lambda f: f.jurisdiction.parent.name
if f.jurisdiction.level == 'l' else f.jurisdiction.name,
'Jurisdiction State',
),
(lambda f: f.agency.name if f.agency else '', 'Agency'),
(lambda f: f.agency.pk if f.agency else '', 'Agency ID'),
(lambda f: f.date_followup, 'Followup Date'),
(lambda f: f.date_estimate, 'Estimated Completion Date'),
(lambda f: f.composer.requested_docs, 'Requested Documents'),
(lambda f: f.current_tracking_id(), 'Tracking Number'),
(lambda f: f.embargo, 'Embargo'),
(lambda f: f.days_since_submitted, 'Days since submitted'),
(lambda f: f.days_since_updated, 'Days since updated'),
(lambda f: f.project_names, 'Projects'),
(lambda f: f.tag_names, 'Tags'),
(lambda f: f.price, 'Price'),
(lambda f: f.composer.datetime_submitted, 'Date Submitted'),
(lambda f: f.date_due, 'Date Due'),
(lambda f: f.datetime_done, 'Date Done'),
)
foias = (FOIARequest.objects.filter(pk__in=foia_pks).select_related(
'composer__user',
'agency__jurisdiction__parent',
).only(
'composer__user__username',
'title',
'status',
'slug',
'agency__jurisdiction__name',
'agency__jurisdiction__slug',
'agency__jurisdiction__id',
'agency__jurisdiction__parent__name',
'agency__jurisdiction__parent__id',
'agency__name',
'agency__id',
'date_followup',
'date_estimate',
'embargo',
'composer__requested_docs',
).annotate(
days_since_submitted=ExtractDay(
Cast(Now() - F('composer__datetime_submitted'), DurationField())),
days_since_updated=ExtractDay(
Cast(Now() - F('datetime_updated'), DurationField())),
project_names=StringAgg('projects__title', ',', distinct=True),
tag_names=StringAgg('tags__name', ',', distinct=True),
))
conn = S3Connection(
settings.AWS_ACCESS_KEY_ID,
settings.AWS_SECRET_ACCESS_KEY,
)
bucket = conn.get_bucket(settings.AWS_STORAGE_BUCKET_NAME)
today = date.today()
file_key = 'exported_csv/{y:4d}/{m:02d}/{d:02d}/{md5}/requests.csv'.format(
y=today.year,
m=today.month,
d=today.day,
md5=md5('{}{}'.format(
int(timestamp()),
''.join(str(pk) for pk in foia_pks[:100]),
)).hexdigest(),
)
key = bucket.new_key(file_key)
with smart_open(key, 'wb') as csv_file:
writer = csv.writer(csv_file)
writer.writerow(f[1] for f in fields)
for foia in foias.iterator():
writer.writerow(f[0](foia) for f in fields)
key.set_acl('public-read')
notification = TemplateEmail(
user=User.objects.get(pk=user_pk),
extra_context={'file': file_key},
text_template='message/notification/csv_export.txt',
html_template='message/notification/csv_export.html',
subject='Your CSV Export',
)
notification.send(fail_silently=False)
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(
field_name="jurisdiction__id")
types = django_filters.CharFilter(field_name="types__name",
lookup_expr="iexact")
class Meta:
model = Agency
fields = ("name", "status", "jurisdiction", "types",
"requires_proxy")
filterset_class = Filter
def get_data(email_or_fax):
"""Helper function to get email or fax data"""
if email_or_fax == "email":
address_model = EmailAddress
confirm_rel = "to_emails"
error_fields = [
"email",
"datetime",
"recipient",
"code",
"error",
"event",
"reason",
]
elif email_or_fax == "fax":
address_model = PhoneNumber
confirm_rel = "faxes"
error_fields = [
"fax",
"datetime",
"recipient",
"error_type",
"error_code",
"error_id",
]
open_requests = (
self.agency.foiarequest_set.get_open()
.order_by("%s__status" % email_or_fax, email_or_fax)
.exclude(**{email_or_fax: None})
.select_related(
"agency__jurisdiction", "composer", "email", "fax", "portal"
)
.annotate(
latest_response=ExtractDay(
Cast(
Now()
- Max(
Case(
When(
communications__response=True,
then="communications__datetime",
)
)
),
models.DurationField(),
)
)
)
.only(
"pk",
"status",
"title",
"slug",
"agency__jurisdiction__slug",
"agency__jurisdiction__id",
"composer__datetime_submitted",
"date_estimate",
"portal__name",
"email__email",
"email__name",
"email__status",
"fax__number",
"fax__status",
)
)
grouped_requests = [
(k, list(v))
for k, v in groupby(open_requests, lambda f: getattr(f, email_or_fax))
]
# do seperate queries for per email addr/fax number stats
# do annotations separately for performance reasons (limit joins)
addresses = address_model.objects.annotate(
error_count=Count("errors", distinct=True),
last_error=Max("errors__datetime"),
)
addresses = addresses.in_bulk(g[0].pk for g in grouped_requests)
addresses_confirm = address_model.objects.annotate(
last_confirm=Max("%s__confirmed_datetime" % confirm_rel)
)
addresses_confirm = addresses_confirm.in_bulk(
g[0].pk for g in grouped_requests
)
if email_or_fax == "email":
addresses_open = address_model.objects.annotate(
last_open=Max("opens__datetime")
)
addresses_open = addresses_open.in_bulk(
g[0].pk for g in grouped_requests
)
review_data = []
for addr, foias in grouped_requests:
# fetch the address with the annotated stats
addr = addresses[addr.pk]
review_data.append(
{
"address": addr,
"error": addr.status == "error",
"errors": addr.errors.select_related(
"%s__communication__foia__agency__jurisdiction"
% email_or_fax
)
.order_by("-datetime")
.only(
*error_fields
+ [
"%s__communication__foia__agency__jurisdiction__slug"
% email_or_fax,
"%s__communication__foia__slug" % email_or_fax,
"%s__communication__foia__title" % email_or_fax,
]
)[:5],
"foias": foias,
"unacknowledged": any(f.status == "ack" for f in foias),
"total_errors": addr.error_count,
"last_error": addr.last_error,
"last_confirm": addresses_confirm[addr.pk].last_confirm,
"last_open": addresses_open[addr.pk].last_open
if email_or_fax == "email"
else None,
"checkbox_name": "foias-%d-%s-%d"
% (self.pk, email_or_fax, addr.pk),
"email_or_fax": email_or_fax,
}
)
return review_data
