Update the current turnout report.

[JohnMwashuma]

Context

---

The turnout report is used for displaying the current voter turnout per administrative area. Administrative areas of interest are Regions, Constituencies and Sub Constituencies. That said, the current report structure does support Admin area reports but with a bad user experience, hence the need for updating it.

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 and results in the final entry. 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 turnout report will consist of the following columns:
  • Administrative area
  • Total voters
  • Voters voted
  • Male voters
  • Female voters
  • Turnout
  • [ ] 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 turnout per region(s).
  • If a region(s) is selected, the report will still show turnout per the selected regions.
  • If a constituenciey(s) is selected, the report will still show turnout per the selected constituenciey(s).
  • If a sub constituenciey(s) is selected, the report will still show turnout 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 turnout report per Administrative areas selected. The following Django queries will help with this logic:

  Regions turnout 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)).values('admin_area').annotate(voters_voted=
           Subquery(
               Result.objects.filter(
               result_form__tally=tally,
               result_form__center__region=OuterRef('center__region'),
               result_form__form_state=FormState.ARCHIVED,
               entry_version=EntryVersion.FINAL
           ).values('result_form__center__region').annotate(
              valid_votes=Sum('votes')
           ).values('valid_votes')[:1],
               output_field=IntegerField()
           ), total_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT')), male_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'), filter=Q(center__stations__gender=0)),unisex_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'),filter=Q(center__stations__gender=3)),female_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'), filter=Q(center__stations__gender=1)), turnout_percentage=Round(V(100.00) * F('voters_voted') / F('total_voters'), digits=2))

  Constituencies turnout 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)).values('admin_area').annotate(voters_voted=
           Subquery(
               Result.objects.filter(
           result_form__tally=tally,
           result_form__center__region=OuterRef('center__region'),
           result_form__center__constituency__name=OuterRef('center__constituency__name'),
           result_form__form_state=FormState.ARCHIVED,
           entry_version=EntryVersion.FINAL
       ).values('result_form__center__constituency__name').annotate(
          valid_votes=Sum('votes')
       ).values('valid_votes')[:1],
               output_field=IntegerField()
           ), total_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT')), male_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'), filter=Q(center__stations__gender=0)),unisex_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'),filter=Q(center__stations__gender=3)),female_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'), filter=Q(center__stations__gender=1)), turnout_percentage=Round(V(100.00) * F('voters_voted') / F('total_voters'), digits=2))

  Sub Constituencies turnout 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').annotate(voters_voted=
           Subquery(
               Result.objects.filter(
           result_form__tally=tally,
           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'),
           result_form__form_state=FormState.ARCHIVED,
           entry_version=EntryVersion.FINAL
       ).values('result_form__center__sub_constituency__code').annotate(
          valid_votes=Sum('votes')
       ).values('valid_votes')[:1],
               output_field=IntegerField()
           ), total_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT')), male_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'), filter=Q(center__stations__gender=0)),unisex_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'),filter=Q(center__stations__gender=3)),female_voters=Sum(Func(F('center__stations__registrants'), function='DISTINCT'), filter=Q(center__stations__gender=1)), turnout_percentage=Round(V(100.00) * F('voters_voted') / F('total_voters'), digits=2))

• [ ] Update the TurnoutReportDataView view filterqueryset method with the above logic

Related to

---

https://github.com/onaio/tally-ho/issues/394

[peterMuriuki]

@JohnMwashuma for the sections: male_voters, female_voters and unisex_voters. I would like to confirm that we are showing the number of voters registered for each of these categories and not not the number of votes by people in these categories.

[JohnMwashuma]

@JohnMwashuma for the sections: male_voters, female_voters and unisex_voters. I would like to confirm that we are showing the number of voters registered for each of these categories and not not the number of votes from people in these categories.

Actually thinking about this, it should be number of votes from people in these categories.

[peterMuriuki]

ok

[peterMuriuki]

@JohnMwashuma for the filters, do we want to allow the ux workflow where:

1. Users can just load the page and filter by either consituencies/sub_constituencies without having to select regions first.