# Path: pupa/models.py
# class SessionDataQualityReport(models.Model):
#     legislative_session = models.ForeignKey(LegislativeSession, on_delete=models.CASCADE)
# 
#     bills_missing_actions = models.PositiveIntegerField()
#     bills_missing_sponsors = models.PositiveIntegerField()
#     bills_missing_versions = models.PositiveIntegerField()
# 
#     votes_missing_voters = models.PositiveIntegerField()
#     votes_missing_bill = models.PositiveIntegerField()
#     votes_missing_yes_count = models.PositiveIntegerField()
#     votes_missing_no_count = models.PositiveIntegerField()
#     votes_with_bad_counts = models.PositiveIntegerField()
# 
#     # these fields store lists of names mapped to numbers of occurances
#     unmatched_sponsor_people = JSONField()
#     unmatched_sponsor_organizations = JSONField()
#     unmatched_voters = JSONField()


from django.db.models import Count, Subquery, OuterRef, Q, F
from opencivicdata.legislative.models import (Bill, VoteEvent, VoteCount, PersonVote,
                                              BillSponsorship)
from ..models import SessionDataQualityReport
        'bills_missing_versions': _simple_count(Bill, session, versions__isnull=True),
        'votes_missing_bill': _simple_count(VoteEvent, session, bill__isnull=True),
        'votes_missing_voters': _simple_count(VoteEvent, session, votes__isnull=True),
        'votes_missing_yes_count': 0,
        'votes_missing_no_count': 0,
        'votes_with_bad_counts': 0,
    }

    voteevents = VoteEvent.objects.filter(legislative_session_id=session)
    queryset = voteevents.annotate(
        yes_sum=Count('pk', filter=Q(votes__option='yes')),
        no_sum=Count('pk', filter=Q(votes__option='no')),
        other_sum=Count('pk', filter=Q(votes__option='other')),
        yes_count=Subquery(VoteCount.objects.filter(vote_event=OuterRef('pk'),
                                                    option='yes').values('value')),
        no_count=Subquery(VoteCount.objects.filter(vote_event=OuterRef('pk'),
                                                   option='no').values('value')),
        other_count=Subquery(VoteCount.objects.filter(vote_event=OuterRef('pk'),
                                                      option='other').values('value')),
    )

    for vote in queryset:
        if vote.yes_count is None:
            report['votes_missing_yes_count'] += 1
            vote.yes_count = 0
        if vote.no_count is None:
            report['votes_missing_no_count'] += 1
            vote.no_count = 0
        if vote.other_count is None:
            vote.other_count = 0

# Path: pupa/models.py
# class SessionDataQualityReport(models.Model):
#     legislative_session = models.ForeignKey(LegislativeSession, on_delete=models.CASCADE)
# 
#     bills_missing_actions = models.PositiveIntegerField()
#     bills_missing_sponsors = models.PositiveIntegerField()
#     bills_missing_versions = models.PositiveIntegerField()
# 
#     votes_missing_voters = models.PositiveIntegerField()
#     votes_missing_bill = models.PositiveIntegerField()
#     votes_missing_yes_count = models.PositiveIntegerField()
#     votes_missing_no_count = models.PositiveIntegerField()
#     votes_with_bad_counts = models.PositiveIntegerField()
# 
#     # these fields store lists of names mapped to numbers of occurances
#     unmatched_sponsor_people = JSONField()
#     unmatched_sponsor_organizations = JSONField()
#     unmatched_voters = JSONField()


# Path: pupa/reports/session.py
from django.db.models import Count, Subquery, OuterRef, Q, F
from opencivicdata.legislative.models import (Bill, VoteEvent, VoteCount, PersonVote,
                                              BillSponsorship)
from ..models import SessionDataQualityReport

        'bills_missing_versions': _simple_count(Bill, session, versions__isnull=True),
        'votes_missing_bill': _simple_count(VoteEvent, session, bill__isnull=True),
        'votes_missing_voters': _simple_count(VoteEvent, session, votes__isnull=True),
        'votes_missing_yes_count': 0,
        'votes_missing_no_count': 0,
        'votes_with_bad_counts': 0,
    }

    voteevents = VoteEvent.objects.filter(legislative_session_id=session)
    queryset = voteevents.annotate(
        yes_sum=Count('pk', filter=Q(votes__option='yes')),
        no_sum=Count('pk', filter=Q(votes__option='no')),
        other_sum=Count('pk', filter=Q(votes__option='other')),
        yes_count=Subquery(VoteCount.objects.filter(vote_event=OuterRef('pk'),
                                                    option='yes').values('value')),
        no_count=Subquery(VoteCount.objects.filter(vote_event=OuterRef('pk'),
                                                   option='no').values('value')),
        other_count=Subquery(VoteCount.objects.filter(vote_event=OuterRef('pk'),
                                                      option='other').values('value')),
    )

    for vote in queryset:
        if vote.yes_count is None:
            report['votes_missing_yes_count'] += 1
            vote.yes_count = 0
        if vote.no_count is None:
            report['votes_missing_no_count'] += 1
            vote.no_count = 0
        if vote.other_count is None:
            vote.other_count = 0

        if (vote.yes_sum != vote.yes_count or