def get_queryset(self):
self.populate_top_percentile_dict()
self.populate_allegation_dict()
self.populate_award_dict()
self.populate_history_dict()
self.populate_badgenumber_dict()
self.populate_salary_dict()
self.populate_tags_dict()
allegation_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('id'))
sustained_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('id'), final_finding='SU')
unsustained_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('id'), final_finding='NS')
discipline_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('id'), disciplined=True)
trr_count = TRR.objects.filter(officer=models.OuterRef('id'))
return Officer.objects.all()\
.annotate(complaint_count=SQCount(allegation_count.values('id')))\
.annotate(sustained_complaint_count=SQCount(sustained_count.values('id')))\
.annotate(discipline_complaint_count=SQCount(discipline_count.values('id')))\
.annotate(annotated_trr_count=SQCount(trr_count.values('id')))\
.annotate(unsustained_complaint_count=SQCount(unsustained_count.values('id')))\
.annotate(trr_datetimes=ArrayAgg('trr__trr_datetime'))\
.annotate(cr_incident_dates=ArrayAgg('officerallegation__allegation__incident_date'))
def handle(self, *args, **kwargs):
AttachmentFile.objects.all().update(views_count=SQCount(
AttachmentTracking.objects.filter(
attachment_file_id=OuterRef('id'),
kind=constants.VIEW_EVENT_TYPE).values('id')))
AttachmentFile.objects.all().update(downloads_count=SQCount(
AttachmentTracking.objects.filter(
attachment_file_id=OuterRef('id'),
kind=constants.DOWNLOAD_EVENT_TYPE).values('id')))
def _populate_officers_dict(self):
self._officer_dict = dict()
allegation_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('id')
)
sustained_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('id'),
final_finding='SU'
)
queryset = Officer.objects.all()\
.annotate(complaint_count=SQCount(allegation_count.values('id')))\
.annotate(sustained_complaint_count=SQCount(sustained_count.values('id')))
for officer in queryset:
self._officer_dict[officer.id] = self.serializer.serialize(officer.__dict__)
def test_count(self):
officer = OfficerFactory()
OfficerAllegationFactory.create_batch(2,
officer=officer,
final_finding='NS')
OfficerAllegationFactory.create_batch(2,
officer=officer,
final_finding='SU')
subquery = OfficerAllegation.objects.filter(
officer=models.OuterRef('officer_id'))
results = list(OfficerAllegation.objects.all().annotate(
allegation_count=SQCount(subquery.values('id'))).annotate(
sustained_count=SQCount(
subquery.filter(final_finding='SU').values('id'))).values(
'allegation_count', 'sustained_count'))
expect(results).to.have.length(4)
for obj in results:
expect(obj['allegation_count']).to.eq(4)
expect(obj['sustained_count']).to.eq(2)
def populate_coaccused_dict(self):
self.coaccused_dict = dict()
sustained_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('officer_id'), final_finding='SU')
allegation_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('officer_id'))
queryset = OfficerAllegation.objects.all().select_related('officer', 'allegation_category')\
.annotate(allegation_count=SQCount(allegation_count.values('id')))\
.annotate(sustained_count=SQCount(sustained_count.values('id')))\
.values(
'officer_id', 'officer__first_name', 'officer__last_name', 'officer__middle_initial',
'officer__middle_initial2', 'officer__suffix_name', 'officer__gender', 'officer__race',
'officer__birth_year', 'officer__rank', 'officer__complaint_percentile',
'officer__civilian_allegation_percentile', 'officer__internal_allegation_percentile',
'officer__trr_percentile', 'allegation_id', 'final_outcome', 'final_finding',
'recc_outcome', 'start_date', 'end_date', 'disciplined', 'allegation_count',
'sustained_count', 'allegation_category__allegation_name', 'allegation_category__category',
'allegation_category_id',
)
for obj in queryset:
self.coaccused_dict.setdefault(obj['allegation_id'],
[]).append(obj)
def populate_investigator_dict(self):
self.investigator_dict = dict()
num_cases = InvestigatorAllegation.objects.filter(
investigator=models.OuterRef('investigator_id'))
queryset = InvestigatorAllegation.objects.all().select_related('investigator__officer')\
.annotate(num_cases=SQCount(num_cases.values('id'))).values(
'investigator__officer_id', 'allegation_id', 'current_rank', 'investigator__first_name',
'investigator__last_name', 'investigator__officer__first_name',
'investigator__officer__last_name', 'investigator__officer__middle_initial',
'investigator__officer__middle_initial2', 'investigator__officer__suffix_name',
'investigator__officer__complaint_percentile', 'investigator__officer__civilian_allegation_percentile',
'investigator__officer__internal_allegation_percentile', 'investigator__officer__trr_percentile',
'num_cases')
for obj in queryset:
self.investigator_dict.setdefault(obj['allegation_id'],
[]).append(obj)
def populate_policewitness_dict(self):
self.policewitness_dict = dict()
sustained_count = OfficerAllegation.objects.filter(
officer=models.OuterRef('officer_id'), final_finding='SU')
queryset = PoliceWitness.objects.all().select_related('officer')\
.annotate(allegation_count=models.Count('officer__officerallegation'))\
.annotate(sustained_count=SQCount(sustained_count.values('id')))
for obj in queryset.values(
'officer_id', 'allegation_id', 'officer__first_name',
'officer__last_name', 'officer__middle_initial',
'officer__middle_initial2', 'officer__suffix_name',
'officer__complaint_percentile',
'officer__civilian_allegation_percentile',
'officer__internal_allegation_percentile',
'officer__trr_percentile', 'officer__gender', 'officer__race',
'allegation_count', 'sustained_count'):
self.policewitness_dict.setdefault(obj['allegation_id'],
[]).append(obj)
def list(self, request):
queryset = AttachmentFile.objects.annotate(documents_count=SQCount(
AttachmentFile.showing.filter(
allegation=OuterRef('allegation')).values('allegation')))
serializer_class = AttachmentFileListSerializer if request.auth is None else \
AuthenticatedAttachmentFileListSerializer
if 'match' in request.query_params and 'crid' not in request.query_params:
match = request.query_params['match']
es_query = AttachmentFileDocType().search().query(
'multi_match',
query=match,
operator='and',
fields=['crid', 'title', 'text_content'])
if request.auth is None:
es_query = es_query.filter('term', show=True)
paginator = ESQuerysetPagination()
page = paginator.paginate_es_query(es_query, request, queryset)
else:
if 'crid' in request.query_params:
queryset = queryset.filter(
allegation_id=request.query_params['crid'])
if request.auth is None:
queryset = queryset.filter(show=True)
queryset = queryset.order_by('-created_at', '-updated_at', 'id')
paginator = LimitOffsetPagination()
page = paginator.paginate_queryset(queryset, request, view=self)
serializer = serializer_class(page, many=True)
return paginator.get_paginated_response(serializer.data)
def cache_data():
allegations = Allegation.objects.filter(
officerallegation__officer=OuterRef('officer1')).filter(
officerallegation__officer=OuterRef('officer2'))
ActivityPairCard.objects.update(
coaccusal_count=SQCount(allegations.values('crid')))