def area_wise_vote_count_query(self): area_wise_valid_vote_count_subquery = self.area_wise_valid_vote_count_query( ).subquery() area_wise_rejected_vote_count_subquery = self.area_wise_rejected_vote_count_query( ).subquery() area_wise_registered_vote_count_subquery = self.area_wise_registered_vote_count_query( ).subquery() return db.session.query( area_wise_valid_vote_count_subquery.c.areaId, area_wise_valid_vote_count_subquery.c.areaName, func.sum( sqlalchemy_num_or_zero( area_wise_valid_vote_count_subquery.c.validVoteCount)). label("validVoteCount"), func.sum(area_wise_rejected_vote_count_subquery.c.rejectedVoteCount ).label("rejectedVoteCount"), func.sum( sqlalchemy_num_or_zero( area_wise_valid_vote_count_subquery.c.validVoteCount) + sqlalchemy_num_or_zero(area_wise_rejected_vote_count_subquery. c.rejectedVoteCount)).label( "totalVoteCount"), func.sum(area_wise_registered_vote_count_subquery.c. registeredVotersCount).label("registeredVotersCount"), ).join( area_wise_rejected_vote_count_subquery, area_wise_rejected_vote_count_subquery.c.areaId == area_wise_valid_vote_count_subquery.c.areaId).join( area_wise_registered_vote_count_subquery, area_wise_registered_vote_count_subquery.c.areaId == area_wise_valid_vote_count_subquery.c.areaId).group_by( area_wise_valid_vote_count_subquery.c.areaId).order_by( area_wise_valid_vote_count_subquery.c.areaName)
def summary(self): area_wise_valid_vote_count_subquery = self.area_wise_valid_vote_count( ).subquery() area_wise_rejected_vote_count_subquery = self.area_wise_rejected_vote_count( ).subquery() return db.session.query( func.count(TallySheetVersion.Model.tallySheetVersionId).label( "areaCount"), func.sum( area_wise_valid_vote_count_subquery.c.validVoteCount).label( "validVoteCount"), func.sum(area_wise_rejected_vote_count_subquery.c.rejectedVoteCount ).label("rejectedVoteCount"), func.sum( sqlalchemy_num_or_zero( area_wise_valid_vote_count_subquery.c.validVoteCount) + sqlalchemy_num_or_zero(area_wise_rejected_vote_count_subquery. c.rejectedVoteCount)).label( "totalVoteCount") ).join(area_wise_valid_vote_count_subquery, area_wise_valid_vote_count_subquery.c.tallySheetVersionId == TallySheetVersion.Model.tallySheetVersionId, isouter=True).join( area_wise_rejected_vote_count_subquery, area_wise_rejected_vote_count_subquery.c.tallySheetVersionId == TallySheetVersion.Model.tallySheetVersionId, isouter=True).filter( TallySheetVersion.Model.tallySheetVersionId == self.tallySheetVersionId).one_or_none()
def non_postal_area_wise_vote_count_query(self): non_postal_area_wise_valid_vote_count_subquery = self.non_postal_area_wise_valid_vote_count_query( ).subquery() non_postal_area_wise_rejected_vote_count_subquery = self.non_postal_area_wise_rejected_vote_count_query( ).subquery() return db.session.query( non_postal_area_wise_valid_vote_count_subquery.c.areaId, non_postal_area_wise_valid_vote_count_subquery.c.areaName, func.sum( sqlalchemy_num_or_zero( non_postal_area_wise_valid_vote_count_subquery.c. validVoteCount)).label("validVoteCount"), func.sum(non_postal_area_wise_rejected_vote_count_subquery.c. rejectedVoteCount).label("rejectedVoteCount"), func.sum( sqlalchemy_num_or_zero( non_postal_area_wise_valid_vote_count_subquery.c. validVoteCount) + sqlalchemy_num_or_zero( non_postal_area_wise_rejected_vote_count_subquery.c. rejectedVoteCount)).label("totalVoteCount") ).join( non_postal_area_wise_rejected_vote_count_subquery, non_postal_area_wise_rejected_vote_count_subquery.c.areaId == non_postal_area_wise_valid_vote_count_subquery.c.areaId).group_by( non_postal_area_wise_valid_vote_count_subquery.c.areaId)
def candidate_wise_vote_count(self): candidate_and_area_wise_valid_vote_count_subquery = self.candidate_and_area_wise_valid_vote_count_query( ).subquery() vote_count_result = self.vote_count_query().one_or_none() return db.session.query( candidate_and_area_wise_valid_vote_count_subquery.c.candidateId, candidate_and_area_wise_valid_vote_count_subquery.c.candidateName, Party.Model.partyAbbreviation, Party.Model.partyName, func.sum( sqlalchemy_num_or_zero( candidate_and_area_wise_valid_vote_count_subquery.c. validVoteCount)).label("validVoteCount"), func.sum((sqlalchemy_num_or_zero( candidate_and_area_wise_valid_vote_count_subquery.c. validVoteCount) / vote_count_result.validVoteCount) * 100).label("validVotePercentage") ).join( ElectionCandidate.Model, ElectionCandidate.Model.candidateId == candidate_and_area_wise_valid_vote_count_subquery.c.candidateId ).join( Candidate.Model, Candidate.Model.candidateId == ElectionCandidate.Model.candidateId, isouter=True).join( Party.Model, Party.Model.partyId == ElectionCandidate.Model.partyId, isouter=True).group_by( candidate_and_area_wise_valid_vote_count_subquery.c. candidateId).group_by( candidate_and_area_wise_valid_vote_count_subquery.c. candidateId).order_by( candidate_and_area_wise_valid_vote_count_subquery. c.candidateId)
def candidate_wise_vote_count(self): non_postal_candidate_wise_vote_count_subquery = self.non_postal_candidate_wise_vote_count( ).subquery() postal_candidate_wise_vote_count_subquery = self.postal_candidate_wise_vote_count( ).subquery() return db.session.query( non_postal_candidate_wise_vote_count_subquery.c.candidateId, non_postal_candidate_wise_vote_count_subquery.c.candidateName, func.sum( sqlalchemy_num_or_zero( non_postal_candidate_wise_vote_count_subquery.c. validVoteCount)).label("nonPostalValidVoteCount"), func.sum( sqlalchemy_num_or_zero( postal_candidate_wise_vote_count_subquery.c.validVoteCount) ).label("postalValidVoteCount"), func.sum( sqlalchemy_num_or_zero( non_postal_candidate_wise_vote_count_subquery.c. validVoteCount) + sqlalchemy_num_or_zero( postal_candidate_wise_vote_count_subquery.c.validVoteCount) ).label("validVoteCount")).join( postal_candidate_wise_vote_count_subquery, postal_candidate_wise_vote_count_subquery.c.candidateId == non_postal_candidate_wise_vote_count_subquery.c.candidateId ).group_by( non_postal_candidate_wise_vote_count_subquery.c.candidateId)
def vote_count_query(self): area_wise_vote_count_subquery = self.area_wise_vote_count_query().subquery() return db.session.query( func.sum( sqlalchemy_num_or_zero(area_wise_vote_count_subquery.c.validVoteCount) ).label("validVoteCount"), func.sum( sqlalchemy_num_or_zero(area_wise_vote_count_subquery.c.rejectedVoteCount) ).label("rejectedVoteCount"), func.sum( sqlalchemy_num_or_zero(area_wise_vote_count_subquery.c.totalVoteCount) ).label("totalVoteCount") )
def rejected_vote_count_query(self): return db.session.query( func.sum( sqlalchemy_num_or_zero(TallySheetVersionRow_RejectedVoteCount.Model.rejectedVoteCount) ).label("rejectedVoteCount"), ).filter( TallySheetVersionRow_RejectedVoteCount.Model.tallySheetVersionId == self.tallySheetVersionId )
def area_wise_valid_vote_count_query(self): candidate_and_area_wise_valid_vote_count_subquery = self.candidate_and_area_wise_valid_vote_count_query().subquery() return db.session.query( candidate_and_area_wise_valid_vote_count_subquery.c.areaId, candidate_and_area_wise_valid_vote_count_subquery.c.areaName, func.sum( sqlalchemy_num_or_zero(candidate_and_area_wise_valid_vote_count_subquery.c.validVoteCount) ).label("validVoteCount"), ).group_by( candidate_and_area_wise_valid_vote_count_subquery.c.areaId ).order_by( candidate_and_area_wise_valid_vote_count_subquery.c.areaName )
def postal_candidate_wise_vote_count(self): postal_candidate_and_area_wise_valid_vote_count_subquery = self.postal_candidate_and_area_wise_valid_vote_count_query( ).subquery() return db.session.query( postal_candidate_and_area_wise_valid_vote_count_subquery.c. candidateId, postal_candidate_and_area_wise_valid_vote_count_subquery.c. candidateName, func.sum( sqlalchemy_num_or_zero( postal_candidate_and_area_wise_valid_vote_count_subquery.c. validVoteCount)).label("validVoteCount") ).group_by(postal_candidate_and_area_wise_valid_vote_count_subquery.c. candidateId)
def valid_vote_count_query(self): return db.session.query( func.count(ElectionCandidate.Model.candidateId).label("candidateCount"), func.sum( sqlalchemy_num_or_zero(TallySheetVersionRow_PRE_ALL_ISLAND_RESULT.Model.count) ).label("validVoteCount") ).join( TallySheetVersionRow_PRE_ALL_ISLAND_RESULT.Model, and_( TallySheetVersionRow_PRE_ALL_ISLAND_RESULT.Model.candidateId == ElectionCandidate.Model.candidateId, TallySheetVersionRow_PRE_ALL_ISLAND_RESULT.Model.tallySheetVersionId == self.tallySheetVersionId, ), isouter=True ).filter( ElectionCandidate.Model.electionId == self.submission.electionId )
def candidate_wise_vote_count(self): non_postal_candidate_wise_vote_count_subquery = self.non_postal_candidate_wise_vote_count( ).subquery() postal_candidate_wise_vote_count_subquery = self.postal_candidate_wise_vote_count( ).subquery() vote_count_result = self.vote_count_query().one_or_none() return db.session.query( non_postal_candidate_wise_vote_count_subquery.c.candidateId, non_postal_candidate_wise_vote_count_subquery.c.candidateName, Party.Model.partyAbbreviation, Party.Model.partyName, func.sum( sqlalchemy_num_or_zero( non_postal_candidate_wise_vote_count_subquery.c. validVoteCount)).label("nonPostalValidVoteCount"), func.sum( sqlalchemy_num_or_zero( postal_candidate_wise_vote_count_subquery.c.validVoteCount) ).label("postalValidVoteCount"), func.sum( sqlalchemy_num_or_zero( non_postal_candidate_wise_vote_count_subquery.c. validVoteCount) + sqlalchemy_num_or_zero( postal_candidate_wise_vote_count_subquery.c.validVoteCount) ).label("validVoteCount"), func.sum(((sqlalchemy_num_or_zero( non_postal_candidate_wise_vote_count_subquery.c.validVoteCount ) + sqlalchemy_num_or_zero( postal_candidate_wise_vote_count_subquery.c.validVoteCount)) / vote_count_result.validVoteCount) * 100).label("validVotePercentage") ).join( ElectionCandidate.Model, ElectionCandidate.Model.candidateId == non_postal_candidate_wise_vote_count_subquery.c.candidateId).join( Candidate.Model, Candidate.Model.candidateId == ElectionCandidate.Model.candidateId, isouter=True).join( Party.Model, Party.Model.partyId == ElectionCandidate.Model.partyId, isouter=True).join( postal_candidate_wise_vote_count_subquery, postal_candidate_wise_vote_count_subquery.c.candidateId == non_postal_candidate_wise_vote_count_subquery.c. candidateId).group_by( non_postal_candidate_wise_vote_count_subquery.c. candidateId)