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)
