Update votes summary report

[JohnMwashuma]

Context

---

The votes summary report is used for displaying votes summary per administrative area. Administrative areas of interest are Regions, Constituencies and Sub Constituencies.

Proposed changes

---

• [ ] Create filter drop-downs for filtering the report per the Administrative area's selected. The Administrative ares list with be based the one's that have result forms in archived state. The filters will the following in the exact order:
  • Regions.
  • Constituencies.
  • Sub Constituencies.
• [ ] Selecting region's should update the Constituencies and Sub Constituencies list with the one's in the region's selected.
• [ ] The votes summary report will consist of the following columns:
  • Administrative area
  • Valid votes
  • Invalid votes
  • Cancelled votes
  • [ ] The Administrative area column value's should be the last Administrative Area selected so that we can display turn out per Administrative Area i.e.
  • On initial page load, no filter would have been selected so the report will be showing votes summary per region(s).
  • If a region(s) is selected, the report will still show votes summary per the selected regions.
  • If a constituenciey(s) is selected, the report will still show votes summary per the selected constituenciey(s).
  • If a sub constituenciey(s) is selected, the report will still show votes summary per the selected sub constituenciey(s).

Task check list

---

• [ ] Create filter drop-downs for filtering the report per the Administrative area's. The filters will the following in the exact order:

  • Regions.
  • Constituencies.
  • Sub Constituencies.

  The following Django queries should help with logic:

  processed_region_names = list(ResultForm.objects.filter(tally=tally).values_list('center__region', flat=True).distinct())
  
  selected_constuencies_names_by_regions = list(ResultForm.objects.filter(tally=tally, center__region__in=processed_region_names).values_list('center__constituency__name',flat=True).distinct())
  
  selected_sub_constuencies_codes_by_regions = list(ResultForm.objects.filter(tally=tally, center__region__in=processed_region_names).values_list('center__sub_constituency__code', flat=True).distinct())

• [ ] Create a votes summary report per Administrative areas selected. The following Django queries will help with this logic:

  Regions votes summary report:

  from tally_ho.libs.utils.query_set_helpers import Round
  
  processed_region_names = list(ResultForm.objects.filter(tally=tally).values_list('center__region', flat=True).distinct())
  
  admin_area_column_name = 'center__region'
  
  ResultForm.objects.filter(tally=tally, form_state=FormState.ARCHIVED, center__region__in=processed_region_names).annotate(admin_area=F(admin_area_column_name)).distinct().values('admin_area').annotate(valid_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__region').annotate(
                  num_valid_votes=Coalesce(Sum('number_valid_votes'),V(0))
               ).values('num_valid_votes')[:1],
               output_field=IntegerField()
           ),
           invalid_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__region').annotate(
                   num_invalid_votes=Coalesce(Sum('number_invalid_votes'),V(0))
               ).values('num_invalid_votes')[:1],
               output_field=IntegerField()
           ),
           cancelled_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__region').annotate(
                   num_cancelled_votes=Coalesce(Sum('number_cancelled_ballots'),V(0))
               ).values('num_cancelled_votes')[:1],
               output_field=IntegerField()
           ))

  Constituencies votes summary report:

  from tally_ho.libs.utils.query_set_helpers import Round
  
  selected_constuencies_names_by_regions = list(ResultForm.objects.filter(tally=tally, center__region__in=processed_region_names).values_list('center__constituency__name',flat=True).distinct())
  
  admin_area_column_name = 'center__constituency__name'
  
  ResultForm.objects.filter(tally=tally, form_state=FormState.ARCHIVED, center__region__in=processed_region_names, center__constituency__name__in=selected_constuencies_names_by_regions).annotate(admin_area=F(admin_area_column_name)).distinct().values('admin_area').annotate(valid_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   result_form__center__constituency__name=OuterRef('center__constituency__name'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__constituency__name').annotate(
                  num_valid_votes=Coalesce(Sum('number_valid_votes'),V(0))
               ).values('num_valid_votes')[:1],
               output_field=IntegerField()
           ),
           invalid_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   result_form__center__constituency__name=OuterRef('center__constituency__name'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__constituency__name').annotate(
                   num_invalid_votes=Coalesce(Sum('number_invalid_votes'),V(0))
               ).values('num_invalid_votes')[:1],
               output_field=IntegerField()
           ),
           cancelled_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   result_form__center__constituency__name=OuterRef('center__constituency__name'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__constituency__name').annotate(
                   num_cancelled_votes=Coalesce(Sum('number_cancelled_ballots'),V(0))
               ).values('num_cancelled_votes')[:1],
               output_field=IntegerField()
           ))

  Sub Constituencies votes summary report:

  from tally_ho.libs.utils.query_set_helpers import Round
  
  selected_sub_constuencies_codes_by_regions = list(ResultForm.objects.filter(tally=tally, center__region__in=processed_region_names).values_list('center__sub_constituency__code', flat=True).distinct())
  
  admin_area_column_name = 'center__sub_constituency__code'
  
  ResultForm.objects.filter(tally=tally, form_state=FormState.ARCHIVED, center__region__in=processed_region_names, center__constituency__name__in=selected_constuencies_names_by_regions, center__sub_constituency__code__in=selected_sub_constuencies_codes_by_regions).annotate(admin_area=F(admin_area_column_name)).values('admin_area').distinct().annotate(valid_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   result_form__center__constituency__name=OuterRef('center__constituency__name'),
                   result_form__center__sub_constituency__code=OuterRef('center__sub_constituency__code'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__sub_constituency__code').annotate(
                  num_valid_votes=Coalesce(Sum('number_valid_votes'),V(0))
               ).values('num_valid_votes')[:1],
               output_field=IntegerField()
           ),
           invalid_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   result_form__center__constituency__name=OuterRef('center__constituency__name'),
                   result_form__center__sub_constituency__code=OuterRef('center__sub_constituency__code'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__sub_constituency__code').annotate(
                   num_invalid_votes=Coalesce(Sum('number_invalid_votes'),V(0))
               ).values('num_invalid_votes')[:1],
               output_field=IntegerField()
           ),
           cancelled_votes=
           Subquery(
               ReconciliationForm.objects.filter(
                   result_form__tally=tally,
                   result_form__form_state=FormState.ARCHIVED, 
                   result_form__center__region=OuterRef('center__region'),
                   result_form__center__constituency__name=OuterRef('center__constituency__name'),
                   result_form__center__sub_constituency__code=OuterRef('center__sub_constituency__code'),
                   entry_version=EntryVersion.FINAL
               ).values('result_form__center__sub_constituency__code').annotate(
                   num_cancelled_votes=Coalesce(Sum('number_cancelled_ballots'),V(0))
               ).values('num_cancelled_votes')[:1],
               output_field=IntegerField()
           ))

• [ ] Update the SummaryReportDataView view filter_queryset method with the above logic