def getAll():
"""
return all tally sheets
"""
# Create the list of tally sheets from our data
people = TallySheetVersion.query.all()
# Serialize the data for the response
person_schema = TallySheetVersionSchema(many=True)
data = person_schema.dump(people).data
return data
def create(tallySheetId):
tallySheetVersion = TallySheetVersion_PRE_ALL_ISLAND_RESULT.create(
tallySheetId=tallySheetId)
electoralDistricts = tallySheetVersion.submission.area.get_associated_areas(
areaType=AreaTypeEnum.ElectoralDistrict,
electionId=tallySheetVersion.submission.electionId)
query = db.session.query(
TallySheetVersionRow_PRE_30_ED.Model.candidateId,
func.sum(TallySheetVersionRow_PRE_30_ED.Model.count).label("count"),
).join(
SubmissionVersion.Model, SubmissionVersion.Model.submissionVersionId ==
TallySheetVersionRow_PRE_30_ED.Model.tallySheetVersionId).join(
Submission.Model, Submission.Model.submissionId ==
SubmissionVersion.Model.submissionId).filter(
TallySheetVersionRow_PRE_30_ED.Model.tallySheetVersionId ==
Submission.Model.latestVersionId,
Submission.Model.areaId.in_([
area.areaId for area in electoralDistricts
])).group_by(
TallySheetVersionRow_PRE_30_ED.Model.candidateId).order_by(
TallySheetVersionRow_PRE_30_ED.Model.candidateId).all(
)
for row in query:
tallySheetVersion.add_row(candidateId=row.candidateId, count=row.count)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create_empty(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_empty_version(
tallySheetId=tallySheetId
)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheetVersion = TallySheetVersionCE201.create(
tallySheetId=tallySheetId
)
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
for party_count_body in tally_sheet_content:
party_count_body = RequestBody(party_count_body)
tallySheetVersionRow = tallySheetVersion.add_row(
areaId=party_count_body.get("areaId"),
ballotsIssued=party_count_body.get("ballotsIssued"),
ballotsReceived=party_count_body.get("ballotsReceived"),
ballotsSpoilt=party_count_body.get("ballotsSpoilt"),
ballotsUnused=party_count_body.get("ballotsUnused"),
ordinaryBallotCountFromBoxCount=party_count_body.get("ordinaryBallotCountFromBoxCount"),
tenderedBallotCountFromBoxCount=party_count_body.get("tenderedBallotCountFromBoxCount"),
ordinaryBallotCountFromBallotPaperAccount=party_count_body.get(
"ordinaryBallotCountFromBallotPaperAccount"),
tenderedBallotCountFromBallotPaperAccount=party_count_body.get(
"tenderedBallotCountFromBallotPaperAccount")
)
for issued_ballot_box_id in party_count_body.get("ballotBoxesIssued"):
tallySheetVersionRow.add_issued_ballot_box(issued_ballot_box_id)
for received_ballot_box_id in party_count_body.get("ballotBoxesReceived"):
tallySheetVersionRow.add_received_ballot_box(received_ballot_box_id)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
tallySheet = TallySheet.get_by_id(tallySheetId=tallySheetId)
if tallySheet is None:
raise NotFoundException("Tally sheet not found. (tallySheetId=%d)" % tallySheetId)
request_body = RequestBody(body)
tallySheetVersion = TallySheetVersionPRE41.create(
tallySheetId=tallySheetId
)
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
for row in tally_sheet_content:
party_count_body = RequestBody(row)
tallySheetVersion.add_row(
candidateId=party_count_body.get("candidateId"),
count=party_count_body.get("count"),
countInWords=party_count_body.get("countInWords")
)
tally_sheet_summary_body = request_body.get("summary")
if tally_sheet_summary_body is not None:
tallySheetVersion.add_invalid_vote_count(
electionId=tallySheetVersion.submission.electionId,
rejectedVoteCount=tally_sheet_summary_body.get("rejectedVoteCount")
)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def get_by_id(tallySheetId, tallySheetVersionId):
result = TallySheetVersion.get_by_id(
tallySheetId=tallySheetId,
tallySheetVersionId=tallySheetVersionId
)
return TallySheetVersionSchema().dump(result).data
def create(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId,
tallySheetCode=TallySheetCodeEnum.PRE_ALL_ISLAND_RESULTS)
tallySheetVersion.set_complete(
) # TODO: valid before setting complete. Refer to PRE_30_PD
electoralDistricts = db.session.query(Area.Model.areaId).filter(
Area.Model.areaType == AreaTypeEnum.ElectoralDistrict,
Area.Model.electionId ==
tallySheetVersion.submission.electionId).all()
query = db.session.query(
TallySheetVersionRow_PRE_30_ED.Model.candidateId,
func.sum(TallySheetVersionRow_PRE_30_ED.Model.count).label("count"),
).join(
SubmissionVersion.Model, SubmissionVersion.Model.submissionVersionId ==
TallySheetVersionRow_PRE_30_ED.Model.tallySheetVersionId).join(
Submission.Model, Submission.Model.submissionId ==
SubmissionVersion.Model.submissionId).filter(
TallySheetVersionRow_PRE_30_ED.Model.tallySheetVersionId ==
Submission.Model.lockedVersionId,
Submission.Model.areaId.in_([
area.areaId for area in electoralDistricts
])).group_by(
TallySheetVersionRow_PRE_30_ED.Model.candidateId).order_by(
TallySheetVersionRow_PRE_30_ED.Model.candidateId).all(
)
for row in query:
tallySheetVersion.add_row(candidateId=row.candidateId, count=row.count)
rejected_vote_count_query = db.session.query(
func.count(Submission.Model.areaId).label("areaCount"),
func.sum(TallySheetVersionRow_RejectedVoteCount.Model.rejectedVoteCount
).label("rejectedVoteCount"),
).join(
TallySheet.Model,
TallySheet.Model.tallySheetId == Submission.Model.submissionId).join(
TallySheetVersionRow_RejectedVoteCount.Model,
TallySheetVersionRow_RejectedVoteCount.Model.tallySheetVersionId ==
Submission.Model.lockedVersionId).filter(
Submission.Model.areaId.in_([
area.areaId for area in electoralDistricts
]), TallySheet.Model.tallySheetCode ==
TallySheetCodeEnum.PRE_30_ED).order_by(
Submission.Model.areaId).all()
for row in rejected_vote_count_query:
if row.areaCount > 0:
tallySheetVersion.add_invalid_vote_count(
electionId=tallySheetVersion.submission.electionId,
rejectedVoteCount=row.rejectedVoteCount)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheetVersion = TallySheetVersion_PRE_30_PD.create(
tallySheetId=tallySheetId)
countingCentres = tallySheetVersion.submission.area.get_associated_areas(
areaType=AreaTypeEnum.CountingCentre,
electionId=tallySheetVersion.submission.electionId)
query = db.session.query(
TallySheetVersionRow_PRE_41.Model.candidateId,
Submission.Model.areaId,
func.sum(TallySheetVersionRow_PRE_41.Model.count).label("count"),
).join(
SubmissionVersion.Model, SubmissionVersion.Model.submissionVersionId ==
TallySheetVersionRow_PRE_41.Model.tallySheetVersionId).join(
Submission.Model, Submission.Model.submissionId ==
SubmissionVersion.Model.submissionId).filter(
TallySheetVersionRow_PRE_41.Model.tallySheetVersionId ==
Submission.Model.latestVersionId,
Submission.Model.areaId.in_([
area.areaId for area in countingCentres
])).group_by(TallySheetVersionRow_PRE_41.Model.candidateId,
Submission.Model.areaId).order_by(
TallySheetVersionRow_PRE_41.Model.candidateId,
Submission.Model.areaId).all()
for row in query:
tallySheetVersion.add_row(candidateId=row.candidateId,
countingCentreId=row.areaId,
count=row.count)
rejected_vote_count_query = db.session.query(
Submission.Model.areaId,
func.sum(TallySheetVersionRow_RejectedVoteCount.Model.rejectedVoteCount
).label("rejectedVoteCount"),
).join(
TallySheet.Model,
TallySheet.Model.tallySheetId == Submission.Model.submissionId).join(
TallySheetVersionRow_RejectedVoteCount.Model,
TallySheetVersionRow_RejectedVoteCount.Model.tallySheetVersionId ==
Submission.Model.latestVersionId).filter(
Submission.Model.areaId.in_([
area.areaId for area in countingCentres
]), TallySheet.Model.tallySheetCode ==
TallySheetCodeEnum.PRE_41).group_by(
Submission.Model.areaId).order_by(
Submission.Model.areaId).all()
for row in rejected_vote_count_query:
tallySheetVersion.add_invalid_vote_count(
electionId=tallySheetVersion.submission.electionId,
areaId=row.areaId,
rejectedVoteCount=row.rejectedVoteCount)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def update(tallySheetId, body):
"""
Append new version to the tally sheet.
"""
# Get the tally sheet
tallySheet = TallySheet.query.filter(
TallySheet.tallySheetId == tallySheetId).one_or_none()
if tallySheet is None:
abort(
404,
"Tally Sheet not found for Id: {tallySheetId}".format(
tallySheetId=tallySheetId),
)
create_tallysheet_version(body, tallySheet)
schema = TallySheetVersionSchema()
return schema.dump(new_tallysheet).data, 201
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId,
content=request_body.get("content")
)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def get_by_id(tallySheetId, tallySheetVersionId):
tallySheet = TallySheet.get_by_id(tallySheetId=tallySheetId)
if tallySheet is None:
raise NotFoundException(
message="Tally sheet not found (tallySheetId=%d)" % tallySheetId,
code=MESSAGE_CODE_TALLY_SHEET_NOT_FOUND)
result = TallySheetVersion.get_by_id(
tallySheetId=tallySheetId, tallySheetVersionId=tallySheetVersionId)
return TallySheetVersionSchema().dump(result).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheetVersion = TallySheetVersionPRE21.create(
tallySheetId=tallySheetId)
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
for row in tally_sheet_content:
party_count_body = RequestBody(row)
tallySheetVersion.add_row(
count=party_count_body.get("count"),
invalidVoteCategoryId=party_count_body.get(
"invalidVoteCategoryId"))
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheetVersion = TallySheetVersionPRE41.create(
tallySheetId=tallySheetId)
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
for row in tally_sheet_content:
party_count_body = RequestBody(row)
tallySheetVersion.add_row(
candidateId=party_count_body.get("candidateId"),
count=party_count_body.get("count"),
countInWords=party_count_body.get("countInWords"))
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_21)
tallySheetVersion.set_complete(
) # TODO: valid before setting complete. Refer to PRE_30_PD
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
for row in tally_sheet_content:
party_count_body = RequestBody(row)
tallySheetVersion.add_row(
count=party_count_body.get("count"),
invalidVoteCategoryId=party_count_body.get(
"invalidVoteCategoryId"))
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheetVersion = TallySheetVersion_CE_201_PV.create(
tallySheetId=tallySheetId)
total_number_of_a_packets_found = 0
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
for row in tally_sheet_content:
tally_sheet_content_item = RequestBody(row)
row = tallySheetVersion.add_row(
ballotBoxStationaryItemId=tally_sheet_content_item.get(
"ballotBoxStationaryItemId"),
numberOfPacketsInserted=tally_sheet_content_item.get(
"numberOfPacketsInserted"),
numberOfAPacketsFound=tally_sheet_content_item.get(
"numberOfAPacketsFound"))
total_number_of_a_packets_found = total_number_of_a_packets_found + row.numberOfAPacketsFound
tally_sheet_summary = request_body.get("summary")
time_of_commencement_of_count = tally_sheet_summary.get(
"timeOfCommencementOfCount")
# To remove the colon between time zone comming from openapi date-time.
# This is since the colon is not accepted in python datetime
# if ":" == time_of_commencement_of_count[-3:-2]:
# time_of_commencement_of_count = time_of_commencement_of_count[:-3] + time_of_commencement_of_count[-2:]
tallySheetVersion.add_summary(
situation=tally_sheet_summary.get("situation"),
timeOfCommencementOfCount=datetime.datetime.strptime(
time_of_commencement_of_count, '%Y-%m-%dT%H:%M:%S.%fZ'),
numberOfAPacketsFound=total_number_of_a_packets_found,
numberOfACoversRejected=tally_sheet_summary.get(
"numberOfACoversRejected"),
numberOfBCoversRejected=tally_sheet_summary.get(
"numberOfBCoversRejected"),
numberOfValidBallotPapers=tally_sheet_summary.get(
"numberOfValidBallotPapers"))
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheetVersion = TallySheetVersion_PRE_30_PD.create(
tallySheetId=tallySheetId
)
countingCentres = tallySheetVersion.submission.area.get_associated_areas(
areaType=AreaTypeEnum.CountingCentre, electionId=tallySheetVersion.submission.electionId
)
query = db.session.query(
TallySheetVersionRow_PRE_41.Model.candidateId,
Submission.Model.areaId,
func.sum(TallySheetVersionRow_PRE_41.Model.count).label("count"),
).join(
SubmissionVersion.Model,
SubmissionVersion.Model.submissionVersionId == TallySheetVersionRow_PRE_41.Model.tallySheetVersionId
).join(
Submission.Model,
Submission.Model.submissionId == SubmissionVersion.Model.submissionId
).filter(
TallySheetVersionRow_PRE_41.Model.tallySheetVersionId == Submission.Model.latestVersionId,
Submission.Model.areaId.in_([area.areaId for area in countingCentres])
).group_by(
TallySheetVersionRow_PRE_41.Model.candidateId,
Submission.Model.areaId
).order_by(
TallySheetVersionRow_PRE_41.Model.candidateId,
Submission.Model.areaId
).all()
print("################ query ", query)
for row in query:
print("################ row ", row)
tallySheetVersion.add_row(
candidateId=row.candidateId,
countingCentreId=row.areaId,
count=row.count
)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
tallySheet = TallySheet.get_by_id(tallySheetId=tallySheetId)
if tallySheet is None:
raise NotFoundException("Tally sheet not found. (tallySheetId=%d)" %
tallySheetId)
request_body = RequestBody(body)
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_34_CO)
tally_sheet_content = request_body.get("content")
print(request_body.body)
if tally_sheet_content is not None:
for row in tally_sheet_content:
party_count_body = RequestBody(row)
row = tallySheetVersion.add_row(
candidateId=party_count_body.get("candidateId"),
notCountedBallotPapers=party_count_body.get(
"notCountedBallotPapers"),
remainingBallotPapers=party_count_body.get(
"remainingBallotPapers"))
# Lets add the preferences for the above row
preferences = party_count_body.get("preferences")
for preference in preferences:
print(preference)
TallySheetVersionRow_PRE_34_CO_PREFERENCES.create(
tallySheetVersionRowId=row.tallySheetVersionRowId,
candidateId=preference.get('candidateId'),
no2ndPreferences=preference.get('no2rdPreferences'),
no3rdPreferences=preference.get('no3rdPreferences'))
tally_sheet_summary_body = request_body.get("summary")
if tally_sheet_summary_body is not None:
tallySheetVersion.add_invalid_vote_count(
electionId=tallySheetVersion.submission.electionId,
rejectedVoteCount=tally_sheet_summary_body.get(
"rejectedVoteCount"))
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheetVersion = TallySheetVersion_PRE_30_ED.create(
tallySheetId=tallySheetId
)
pollingDivisions = tallySheetVersion.submission.area.get_associated_areas(AreaTypeEnum.PollingDivision)
query = db.session.query(
TallySheetVersionRow_PRE_30_PD.Model.candidateId,
Submission.Model.areaId,
func.sum(TallySheetVersionRow_PRE_30_PD.Model.count).label("count"),
).join(
SubmissionVersion.Model,
SubmissionVersion.Model.submissionVersionId == TallySheetVersionRow_PRE_30_PD.Model.tallySheetVersionId
).join(
Submission.Model,
Submission.Model.submissionId == SubmissionVersion.Model.submissionId
).filter(
TallySheetVersionRow_PRE_30_PD.Model.tallySheetVersionId == Submission.Model.latestVersionId,
Submission.Model.areaId.in_([area.areaId for area in pollingDivisions])
).group_by(
TallySheetVersionRow_PRE_30_PD.Model.candidateId,
Submission.Model.areaId
).order_by(
TallySheetVersionRow_PRE_30_PD.Model.candidateId,
Submission.Model.areaId
).all()
for row in query:
tallySheetVersion.add_row(
candidateId=row.candidateId,
pollingDivisionId=row.areaId,
count=row.count
)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_41)
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
for row in tally_sheet_content:
party_count_body = RequestBody(row)
tallySheetVersion.add_row(
candidateId=party_count_body.get("candidateId"),
count=party_count_body.get("count"),
countInWords=party_count_body.get("countInWords"))
tally_sheet_summary_body = request_body.get("summary")
if tally_sheet_summary_body is not None:
tallySheetVersion.add_invalid_vote_count(
electionId=tallySheetVersion.submission.electionId,
rejectedVoteCount=tally_sheet_summary_body.get(
"rejectedVoteCount"))
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_21)
tally_sheet_content = request_body.get("content")
if tally_sheet_content is not None:
is_complete = True
for row in tally_sheet_content:
party_count_body = RequestBody(row)
count = party_count_body.get("count")
invalidVoteCategoryId = party_count_body.get(
"invalidVoteCategoryId")
if (count and invalidVoteCategoryId) is not None:
tallySheetVersion.add_row(
count=count, invalidVoteCategoryId=invalidVoteCategoryId)
else:
is_complete = False
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_34_AI)
electoral_districts = db.session.query(
AreaMap.Model.electoralDistrictId).filter(
AreaMap.Model.countryId == tallySheet.submission.areaId).group_by(
AreaMap.Model.electoralDistrictId).all()
electoral_district_ids = []
for electoral_district in electoral_districts:
electoral_district_ids.append(electoral_district.electoralDistrictId)
query = db.session.query(
func.count(Area.Model.areaId).label("areaCount"),
ElectionCandidate.Model.candidateId,
TallySheetVersionRow_PRE_34_preference.Model.preferenceNumber,
func.sum(TallySheetVersionRow_PRE_34_preference.Model.preferenceCount).
label("preferenceCount"), Submission.Model.electionId).join(
Submission.Model, Submission.Model.areaId == Area.Model.areaId
).join(Election.Model,
Election.Model.electionId == Area.Model.electionId).join(
ElectionCandidate.Model,
or_(
ElectionCandidate.Model.electionId ==
Election.Model.electionId,
ElectionCandidate.Model.electionId ==
Election.Model.parentElectionId)
).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId ==
Submission.Model.submissionId,
TallySheet.Model.tallySheetCode ==
TallySheetCodeEnum.PRE_34_ED)
).join(
TallySheetVersionRow_PRE_34_preference.Model,
and_(
TallySheetVersionRow_PRE_34_preference.Model.
tallySheetVersionId == Submission.Model.lockedVersionId,
TallySheetVersionRow_PRE_34_preference.Model.candidateId
== ElectionCandidate.Model.candidateId),
).filter(Submission.Model.areaId.in_(
electoral_district_ids)).group_by(
ElectionCandidate.Model.candidateId,
TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber).order_by(
TallySheetVersionRow_PRE_34_preference.Model.
candidateId, TallySheetVersionRow_PRE_34_preference.
Model.preferenceNumber).all()
is_complete = True # TODO:Change other reports to validate like this
for row in query:
if row.candidateId is not None and row.preferenceNumber is not None and row.preferenceCount:
print("========== row ======== ", row)
tallySheetVersion.add_row(electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=row.preferenceNumber,
preferenceCount=row.preferenceCount)
else:
is_complete = False
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId,
tallySheetCode=TallySheetCodeEnum.
PRE_ALL_ISLAND_RESULTS_BY_ELECTORAL_DISTRICTS)
electionId = tallySheetVersion.submission.electionId
query = db.session.query(
func.count(Area.Model.areaId).label("areaCount"),
ElectionCandidate.Model.candidateId,
Submission.Model.areaId.label("electoralDistrictId"),
func.sum(TallySheetVersionRow_PRE_30_ED.Model.count).label("count"),
).join(
Election.Model, Election.Model.electionId == Area.Model.electionId
).join(
ElectionCandidate.Model,
ElectionCandidate.Model.electionId == Election.Model.electionId).join(
Submission.Model,
Submission.Model.areaId == Area.Model.areaId).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId ==
Submission.Model.submissionId,
TallySheet.Model.tallySheetCode ==
TallySheetCodeEnum.PRE_30_ED)
).join(TallySheetVersionRow_PRE_30_ED.Model,
and_(
TallySheetVersionRow_PRE_30_ED.Model.tallySheetVersionId
== Submission.Model.lockedVersionId,
TallySheetVersionRow_PRE_30_ED.Model.candidateId ==
ElectionCandidate.Model.candidateId),
isouter=True).filter(
Area.Model.areaType == AreaTypeEnum.ElectoralDistrict,
Area.Model.electionId == electionId).group_by(
ElectionCandidate.Model.candidateId,
Area.Model.areaId).order_by(
ElectionCandidate.Model.candidateId,
Area.Model.areaId).all()
is_complete = True
for row in query:
if (row.candidateId and row.electoralDistrictId
and row.count) is not None:
tallySheetVersion.add_row(
candidateId=row.candidateId,
electoralDistrictId=row.electoralDistrictId,
count=row.count)
else:
is_complete = False
rejected_vote_count_query = db.session.query(
Area.Model.areaId,
Submission.Model.electionId,
func.sum(TallySheetVersionRow_RejectedVoteCount.Model.rejectedVoteCount
).label("rejectedVoteCount"),
).join(
Submission.Model, Submission.Model.areaId == Area.Model.areaId).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId == Submission.Model.submissionId,
TallySheet.Model.tallySheetCode ==
TallySheetCodeEnum.PRE_30_ED)).join(
TallySheetVersionRow_RejectedVoteCount.Model,
TallySheetVersionRow_RejectedVoteCount.Model.
tallySheetVersionId == Submission.Model.lockedVersionId,
isouter=True).filter(
Area.Model.areaType == AreaTypeEnum.ElectoralDistrict,
Area.Model.electionId == electionId,
).group_by(Submission.Model.electionId,
Submission.Model.areaId).order_by(
Submission.Model.electionId,
Submission.Model.areaId).all()
for row in rejected_vote_count_query:
if (tallySheetVersion.submission.electionId and row.areaId
and row.rejectedVoteCount) is not None:
tallySheetVersion.add_invalid_vote_count(
electionId=tallySheetVersion.submission.electionId,
areaId=row.areaId,
rejectedVoteCount=row.rejectedVoteCount)
else:
is_complete = False
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def get_all(tallySheetId):
result = TallySheetVersion.get_all(tallySheetId=tallySheetId)
result = get_paginated_query(result).all()
return TallySheetVersionSchema(many=True).dump(result).data
def create(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_34_PD)
query = db.session.query(
Election.Model.electionId,
Area.Model.areaId,
ElectionCandidate.Model.candidateId,
func.sum(
func.IF(TallySheetVersionRow_PRE_30_PD.Model.count == None, 0,
TallySheetVersionRow_PRE_30_PD.Model.count)).label(
"firstPreferenceCount"),
func.sum(
func.IF(
and_(TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber == 2),
TallySheetVersionRow_PRE_34_preference.Model.preferenceCount,
0)).label("secondPreferenceCount"),
func.sum(
func.IF(
and_(TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber == 3),
TallySheetVersionRow_PRE_34_preference.Model.preferenceCount,
0)).label("thirdPreferenceCount"),
).join(
Submission.Model, Submission.Model.areaId == Area.Model.areaId).join(
Election.Model,
Election.Model.electionId == Area.Model.electionId).join(
ElectionCandidate.Model,
or_(
ElectionCandidate.Model.electionId ==
Election.Model.electionId,
ElectionCandidate.Model.electionId ==
Election.Model.parentElectionId)).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId ==
Submission.Model.submissionId,
TallySheet.Model.tallySheetCode.in_([
TallySheetCodeEnum.PRE_30_PD,
TallySheetCodeEnum.PRE_34_I_RO
]))
).join(
TallySheetVersionRow_PRE_30_PD.Model,
and_(
TallySheetVersionRow_PRE_30_PD.Model.
tallySheetVersionId == Submission.Model.
lockedVersionId,
TallySheetVersionRow_PRE_30_PD.Model.candidateId ==
ElectionCandidate.Model.candidateId),
isouter=True).join(
TallySheetVersionRow_PRE_34_preference.Model,
and_(
TallySheetVersionRow_PRE_34_preference.Model.
tallySheetVersionId ==
Submission.Model.lockedVersionId,
TallySheetVersionRow_PRE_34_preference.Model.
candidateId ==
ElectionCandidate.Model.candidateId),
isouter=True).filter(
Area.Model.areaId ==
tallySheet.submission.areaId,
ElectionCandidate.Model.qualifiedForPreferences
== True).group_by(
ElectionCandidate.Model.candidateId,
Submission.Model.areaId).order_by(
ElectionCandidate.Model.candidateId,
Submission.Model.areaId).all()
is_complete = True # TODO:Change other reports to validate like this
for row in query:
if row.candidateId is not None and row.firstPreferenceCount is not None and row.secondPreferenceCount is not None and row.thirdPreferenceCount is not None:
tallySheetVersion.add_row(electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=1,
preferenceCount=row.firstPreferenceCount)
tallySheetVersion.add_row(
electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=2,
preferenceCount=row.secondPreferenceCount)
tallySheetVersion.add_row(electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=3,
preferenceCount=row.thirdPreferenceCount)
else:
is_complete = False
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_30_ED)
polling_division_and_electoral_district_subquery = tallySheetVersion.polling_division_and_electoral_district_query(
).subquery()
query = db.session.query(
polling_division_and_electoral_district_subquery.c.areaId,
ElectionCandidate.Model.candidateId,
Submission.Model.electionId,
func.sum(TallySheetVersionRow_PRE_30_PD.Model.count).label("count"),
).join(
Election.Model, Election.Model.electionId ==
polling_division_and_electoral_district_subquery.c.electionId).join(
ElectionCandidate.Model,
ElectionCandidate.Model.electionId == Election.Model.electionId
).join(
Submission.Model, Submission.Model.areaId ==
polling_division_and_electoral_district_subquery.c.areaId
).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId == Submission.Model.submissionId,
TallySheet.Model.tallySheetCode == TallySheetCodeEnum.PRE_30_PD
)).join(
TallySheetVersionRow_PRE_30_PD.Model,
and_(
TallySheetVersionRow_PRE_30_PD.Model.tallySheetVersionId ==
Submission.Model.lockedVersionId,
TallySheetVersionRow_PRE_30_PD.Model.candidateId ==
ElectionCandidate.Model.candidateId),
isouter=True).group_by(ElectionCandidate.Model.candidateId,
Submission.Model.electionId,
Submission.Model.areaId).order_by(
ElectionCandidate.Model.candidateId,
Submission.Model.electionId,
Submission.Model.areaId).all()
is_complete = True
for row in query:
print("###### row [1] ##### ", row)
if (row.candidateId and row.areaId and row.count
and row.electionId) is not None:
tallySheetVersion.add_row(candidateId=row.candidateId,
areaId=row.areaId,
count=row.count,
electionId=row.electionId)
else:
is_complete = False
rejected_vote_count_query = db.session.query(
polling_division_and_electoral_district_subquery.c.areaId,
Submission.Model.electionId,
func.sum(TallySheetVersionRow_RejectedVoteCount.Model.rejectedVoteCount
).label("rejectedVoteCount"),
).join(
Submission.Model, Submission.Model.areaId ==
polling_division_and_electoral_district_subquery.c.areaId).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId == Submission.Model.submissionId,
TallySheet.Model.tallySheetCode ==
TallySheetCodeEnum.PRE_30_PD)).join(
TallySheetVersionRow_RejectedVoteCount.Model,
TallySheetVersionRow_RejectedVoteCount.Model.
tallySheetVersionId == Submission.Model.lockedVersionId,
isouter=True).group_by(Submission.Model.electionId,
Submission.Model.areaId).order_by(
Submission.Model.electionId,
Submission.Model.areaId).all()
for row in rejected_vote_count_query:
print("###### row [2] ##### ", row)
if (row.electionId and row.areaId
and row.rejectedVoteCount) is not None:
tallySheetVersion.add_invalid_vote_count(
electionId=row.electionId,
areaId=row.areaId,
rejectedVoteCount=row.rejectedVoteCount)
else:
is_complete = False
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def get_tallysheet_response(new_tallysheet):
if new_tallysheet.code == "PRE-41":
return TallySheet_PRE_41_Schema().dump(new_tallysheet).data
else:
return TallySheetVersionSchema().dump(new_tallysheet).data
def create(tallySheetId, body):
request_body = RequestBody(body)
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_41)
election = tallySheet.submission.election
voteType = election.electionName
status = "Entered"
electionId = election.parentElectionId
countingCentreId = tallySheet.areaId
area = Area.get_by_id(areaId=countingCentreId)
electoralDistrictId = area.get_associated_areas(
AreaTypeEnum.ElectoralDistrict, electionId=electionId)[0].areaId
pollingDivisionId = None
pollingDivisionResult = area.get_associated_areas(
AreaTypeEnum.PollingDivision, electionId=electionId)
if len(pollingDivisionResult) > 0:
pollingDivisionId = area.get_associated_areas(
AreaTypeEnum.PollingDivision, electionId=electionId)[0].areaId
tally_sheet_content = request_body.get("content")
is_complete = True
if tally_sheet_content is not None:
for row in tally_sheet_content:
party_count_body = RequestBody(row)
candidateId = party_count_body.get("candidateId")
count = party_count_body.get("count")
countInWords = party_count_body.get("countInWords")
if (candidateId and count and countInWords) is not None:
tallySheetVersion.add_row(candidateId=candidateId,
count=count,
countInWords=countInWords)
else:
is_complete = False
voteCount = party_count_body.get("count")
pollingStationId = party_count_body.get("areaId")
candidateId = party_count_body.get("candidateId")
if election is not None:
existingStatus = StatusPRE41.get_status_record(
electionId=electionId,
electoralDistrictId=electoralDistrictId,
pollingDivisionId=pollingDivisionId,
countingCentreId=countingCentreId,
pollingStationId=pollingStationId,
candidateId=candidateId)
if existingStatus is None:
StatusPRE41.create(
voteType=voteType,
status=status,
electionId=electionId,
electoralDistrictId=electoralDistrictId,
pollingDivisionId=pollingDivisionId,
countingCentreId=countingCentreId,
pollingStationId=pollingStationId,
voteCount=voteCount,
candidateId=candidateId,
)
else:
existingStatus.voteType = voteType,
existingStatus.electionId = electionId,
existingStatus.electoralDistrictId = electoralDistrictId,
existingStatus.pollingDivisionId = pollingDivisionId,
existingStatus.countingCentreId = countingCentreId,
existingStatus.pollingStationId = pollingStationId,
existingStatus.voteCount = voteCount,
existingStatus.candidateId = candidateId
tally_sheet_summary_body = request_body.get("summary")
if tally_sheet_summary_body is not None:
electionId = tallySheetVersion.submission.electionId
rejectedVoteCount = tally_sheet_summary_body.get("rejectedVoteCount")
if (electionId and rejectedVoteCount) is not None:
tallySheetVersion.add_invalid_vote_count(
electionId=electionId, rejectedVoteCount=rejectedVoteCount)
else:
is_complete = False
voteCount = tally_sheet_summary_body.get("rejectedVoteCount")
candidateId = None
pollingStationId = None
if election is not None:
existingStatus = StatusPRE41.get_status_record(
electionId=election.parentElectionId,
electoralDistrictId=electoralDistrictId,
pollingDivisionId=pollingDivisionId,
countingCentreId=countingCentreId,
candidateId=candidateId)
if existingStatus is None:
StatusPRE41.create(
voteType=voteType,
status=status,
electionId=election.parentElectionId,
electoralDistrictId=electoralDistrictId,
pollingDivisionId=pollingDivisionId,
countingCentreId=countingCentreId,
pollingStationId=pollingStationId,
voteCount=voteCount,
candidateId=candidateId,
)
else:
existingStatus.voteType = voteType,
existingStatus.electionId = election.parentElectionId,
existingStatus.electoralDistrictId = electoralDistrictId,
existingStatus.pollingDivisionId = pollingDivisionId,
existingStatus.countingCentreId = countingCentreId,
existingStatus.voteCount = voteCount,
existingStatus.pollingStationId = pollingStationId,
existingStatus.candidateId = candidateId
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_34_PD)
polling_division_id = tallySheet.submission.areaId
query = db.session.query(
Election.Model.electionId,
Area.Model.areaId,
ElectionCandidate.Model.candidateId,
ElectionCandidate.Model.qualifiedForPreferences,
func.sum(TallySheetVersionRow_PRE_30_PD.Model.count).label(
"firstPreferenceCount"),
func.sum(
func.IF(
and_(TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber == 2),
TallySheetVersionRow_PRE_34_preference.Model.preferenceCount,
None)).label("secondPreferenceCount"),
func.sum(
func.IF(
and_(TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber == 3),
TallySheetVersionRow_PRE_34_preference.Model.preferenceCount,
None)).label("thirdPreferenceCount"),
).join(
Submission.Model, Submission.Model.areaId == Area.Model.areaId).join(
Election.Model,
Election.Model.electionId == Area.Model.electionId).join(
ElectionCandidate.Model,
or_(
ElectionCandidate.Model.electionId ==
Election.Model.electionId,
ElectionCandidate.Model.electionId ==
Election.Model.parentElectionId)).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId ==
Submission.Model.submissionId,
TallySheet.Model.tallySheetCode.in_([
TallySheetCodeEnum.PRE_30_PD,
TallySheetCodeEnum.PRE_34_I_RO
]))
).join(
TallySheetVersionRow_PRE_30_PD.Model,
and_(
TallySheetVersionRow_PRE_30_PD.Model.
tallySheetVersionId == Submission.Model.
lockedVersionId,
TallySheetVersionRow_PRE_30_PD.Model.candidateId ==
ElectionCandidate.Model.candidateId),
isouter=True
).join(
TallySheetVersionRow_PRE_34_preference.Model,
and_(
TallySheetVersionRow_PRE_34_preference.Model.
tallySheetVersionId == Submission.Model.
lockedVersionId,
TallySheetVersionRow_PRE_34_preference.Model.
candidateId ==
ElectionCandidate.Model.candidateId),
isouter=True
).filter(
Area
.Model.
areaId
== polling_division_id,
# ElectionCandidate.Model.qualifiedForPreferences == True
).group_by(ElectionCandidate.Model.candidateId,
Submission.Model.areaId).order_by(
ElectionCandidate.Model.candidateId,
Submission.Model.areaId).all()
is_complete = True
for row in query:
print("###### ABCD #### ", [
row.candidateId, row.qualifiedForPreferences,
row.firstPreferenceCount, row.secondPreferenceCount,
row.thirdPreferenceCount
])
if (row.candidateId and row.firstPreferenceCount) is not None:
tallySheetVersion.add_row(electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=1,
preferenceCount=row.firstPreferenceCount,
areaId=polling_division_id)
if row.qualifiedForPreferences is True:
if (row.secondPreferenceCount
and row.thirdPreferenceCount) is not None:
tallySheetVersion.add_row(
electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=2,
preferenceCount=row.secondPreferenceCount,
areaId=polling_division_id)
tallySheetVersion.add_row(
electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=3,
preferenceCount=row.thirdPreferenceCount,
areaId=polling_division_id)
else:
is_complete = False
else:
is_complete = False
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheet, tallySheetVersion = TallySheet.create_latest_version(
tallySheetId=tallySheetId, tallySheetCode=TallySheetCodeEnum.PRE_34_AI)
electoral_districts = db.session.query(
AreaMap.Model.electoralDistrictId).filter(
AreaMap.Model.countryId == tallySheet.submission.areaId).group_by(
AreaMap.Model.electoralDistrictId).all()
electoral_district_ids = []
for electoral_district in electoral_districts:
electoral_district_ids.append(electoral_district.electoralDistrictId)
query = db.session.query(
Area.Model.areaId,
ElectionCandidate.Model.candidateId,
ElectionCandidate.Model.qualifiedForPreferences,
Submission.Model.electionId,
func.sum(
func.IF(
and_(TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber == 1),
TallySheetVersionRow_PRE_34_preference.Model.preferenceCount,
None)).label("firstPreferenceCount"),
func.sum(
func.IF(
and_(TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber == 2),
TallySheetVersionRow_PRE_34_preference.Model.preferenceCount,
None)).label("secondPreferenceCount"),
func.sum(
func.IF(
and_(TallySheetVersionRow_PRE_34_preference.Model.
preferenceNumber == 3),
TallySheetVersionRow_PRE_34_preference.Model.preferenceCount,
None)).label("thirdPreferenceCount"),
).join(Submission.Model,
Submission.Model.areaId == Area.Model.areaId).join(
Election.Model,
Election.Model.electionId == Area.Model.electionId).join(
ElectionCandidate.Model,
or_(
ElectionCandidate.Model.electionId ==
Election.Model.electionId,
ElectionCandidate.Model.electionId ==
Election.Model.parentElectionId)
).join(
TallySheet.Model,
and_(
TallySheet.Model.tallySheetId ==
Submission.Model.submissionId,
TallySheet.Model.tallySheetCode ==
TallySheetCodeEnum.PRE_34_ED)
).join(
TallySheetVersionRow_PRE_34_preference.Model,
and_(
TallySheetVersionRow_PRE_34_preference.Model.
tallySheetVersionId == Submission.Model.lockedVersionId,
TallySheetVersionRow_PRE_34_preference.Model.candidateId
== ElectionCandidate.Model.candidateId),
isouter=True).filter(
Area.Model.areaId.in_(electoral_district_ids)).group_by(
ElectionCandidate.Model.candidateId,
Area.Model.areaId).order_by(
ElectionCandidate.Model.candidateId,
Area.Model.areaId).all()
is_complete = True
for row in query:
if (row.candidateId and row.firstPreferenceCount) is not None:
tallySheetVersion.add_row(electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=1,
preferenceCount=row.firstPreferenceCount,
areaId=row.areaId)
if row.qualifiedForPreferences is True:
if (row.secondPreferenceCount
and row.thirdPreferenceCount) is not None:
tallySheetVersion.add_row(
electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=2,
preferenceCount=row.secondPreferenceCount,
areaId=row.areaId)
tallySheetVersion.add_row(
electionId=row.electionId,
candidateId=row.candidateId,
preferenceNumber=3,
preferenceCount=row.thirdPreferenceCount,
areaId=row.areaId)
else:
is_complete = False
else:
is_complete = False
if is_complete:
tallySheetVersion.set_complete()
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data
def create(tallySheetId):
tallySheetVersion = TallySheetVersion_PRE_ALL_ISLAND_RESULTS_BY_ELECTORAL_DISTRICTS.create(
tallySheetId=tallySheetId
)
electoralDistricts = tallySheetVersion.submission.area.get_associated_areas(
areaType=AreaTypeEnum.ElectoralDistrict, electionId=tallySheetVersion.submission.electionId
)
query = db.session.query(
TallySheetVersionRow_PRE_30_ED.Model.candidateId,
Submission.Model.areaId.label("electoralDistrictId"),
func.sum(TallySheetVersionRow_PRE_30_ED.Model.count).label("count"),
).join(
SubmissionVersion.Model,
SubmissionVersion.Model.submissionVersionId == TallySheetVersionRow_PRE_30_ED.Model.tallySheetVersionId
).join(
Submission.Model,
Submission.Model.submissionId == SubmissionVersion.Model.submissionId
).filter(
TallySheetVersionRow_PRE_30_ED.Model.tallySheetVersionId == Submission.Model.latestVersionId,
Submission.Model.areaId.in_([area.areaId for area in electoralDistricts])
).group_by(
TallySheetVersionRow_PRE_30_ED.Model.candidateId,
Submission.Model.areaId
).order_by(
TallySheetVersionRow_PRE_30_ED.Model.candidateId,
Submission.Model.areaId
).all()
for row in query:
tallySheetVersion.add_row(
candidateId=row.candidateId,
electoralDistrictId=row.electoralDistrictId,
count=row.count
)
rejected_vote_count_query = db.session.query(
Submission.Model.areaId,
func.sum(TallySheetVersionRow_RejectedVoteCount.Model.rejectedVoteCount).label("rejectedVoteCount"),
).join(
TallySheet.Model,
TallySheet.Model.tallySheetId == Submission.Model.submissionId
).join(
TallySheetVersionRow_RejectedVoteCount.Model,
TallySheetVersionRow_RejectedVoteCount.Model.tallySheetVersionId == Submission.Model.latestVersionId
).filter(
Submission.Model.areaId.in_([area.areaId for area in electoralDistricts]),
TallySheet.Model.tallySheetCode == TallySheetCodeEnum.PRE_30_ED
).group_by(
Submission.Model.areaId
).order_by(
Submission.Model.areaId
).all()
for row in rejected_vote_count_query:
tallySheetVersion.add_invalid_vote_count(
electionId=tallySheetVersion.submission.electionId,
areaId=row.areaId,
rejectedVoteCount=row.rejectedVoteCount
)
db.session.commit()
return TallySheetVersionSchema().dump(tallySheetVersion).data