We need to define and align on the validation rules that need to be applied on the csv files used in creating tallies.
Proposed validation rules
General validation rules applied across all files:
[ ] Check if the CSV file has all the required column headers. If any headers are missing, then the file should be rejected.
[ ] Check if the CSV file has any missing data. If any required fields are empty, then the file should be rejected.
[ ] Check if the CSV file has any invalid data. For example, if a column is expected to contain numbers, but some values are strings, then the file should be rejected.
[ ] Check if the CSV file has any duplicated rows. If any rows are duplicated, then the file should be rejected.
[ ] Check if the CSV file has any invalid characters. If any characters are not allowed, such as special characters or emojis, then the file should be rejected.
[ ] Check if the CSV file has any inconsistent data types. For example, if a column is expected to contain dates, but some values are strings or numbers, then the file should be rejected.
Specific validation rules applicable per file type:
Sub Constituency file:
Validation rules:
[ ] sub_constituency_code column should only contain positive integer values.
[ ] number_of_ballots column values should match with the expected number of ballot types. For example, if a row in the sub constituency file has number_of_ballots column value as 2 then it can only have two ballot types e.g. ballot_number_presidential and ballot_number_general.
[ ] field_office_name column should only contain string/varchar values.
[ ] constituency_name column should only contain string/varchar values.
[ ] If a constituency was not found with the provided constituency_name, fail with an error.
[ ] ballot types columns ( ballot_number_presidential, ballot_number_general e.t.c.) should only contain positive integer values.
Required fields/columns:
sub_constituency_code
field_office_name
number_of_ballots
constituency_name
Center file:
Validation rules:
[ ] center_id column should only contain positive integer values.
[ ] name column should only contain string/varchar values.
[ ] region_name column should only contain string/varchar values.
[ ] If a region was not found with the provided region_name, fail with an error.
[ ] office_id column should only contain positive integer values.
[ ] office_name column should only contain string/varchar values.
[ ] If an office was not found with the provided office_name, 'regionandoffice_id`, fail with an error.
[ ] constituency_name column should only contain string/varchar values.
[ ] If a constituency was not found with the provided constituency_name, fail with an error.
[ ] subconstituency_id column should only contain positive integer values. Is there a code for no constituency?, because I have seen a center file that has the subconstituency_id value of 999 with the corresponding sub constituency name as No Sub Constituency.
[ ] If a sub constituency was not found with the provided subconstituency_id, fail with an error.
Required fields/columns:
[ ] center_id
[ ] name
[ ] office_id
[ ] office_name
[ ] subconstituency_id
[ ] constituency_name
[ ] region_name
Stations file:
Validation rules:
[ ] station_number column should only contain positive integer values.
[ ] station_gender column should only contain string/varchar values.
[ ] station_registrants column should only contain positive integer values unless the gender type is neither male or female.
[ ] sub_constituency_code column should only contain positive integer values.
[ ] If a subconstiency was not found with the provided sub_constituency_code, fail with an error.
[ ] center_code column should only contain positive integer values.
[ ] center_name column should only contain string/varchar values.
[ ] If a center was not found with the provided center_name/center_code, fail with an error.
Required fields/columns:
[ ] station_number
[ ] station_gender
[ ] sub_constituency_code
[ ] center_code
[ ] center_name
Candidates file:
Validation rules:
[ ] candidate_id column should only contain positive integer values.
[ ] ballot_number column should only contain positive integer values.
[ ] candidate_full_name column should only contain string/varchar values.
[ ] If a ballot was not found with the provided ballot_number, fail with an error.
Required fields/columns:
[ ] candidate_id
[ ] candidate_full_name
[ ] ballot_number
Result form file:
Validation rules:
[ ] barcode column should only contain string/varchar values.
[ ] ballot_number column should only contain positive integer values.
[ ] If the ballot retrieved via the ballot_number is not active, fail with an error.
[ ] If a ballot was not found with the provided ballot_number, fail with an error.
[ ] center_code column should only contain positive integer values.
[ ] If the center retrieved via the center_code is not active, fail with an error.
[ ] If the center was not found with the provided center_code, fail with an error.
[ ] station_number column should only contain positive integer values.
[ ] If the station retrieved via the station_number and center is not active, fail with an error.
[ ] If the station was not found with the provided station_number and center, fail with an error.
[ ] office_name column should only contain string/varchar values.
[ ] If an office was not found with the provided office_name, fail with an error.
[ ] name column should only contain string/varchar values.
JohnMwashuma
commented
11 months ago
User story
We need to define and align on the validation rules that need to be applied on the csv files used in creating tallies.
Proposed validation rules
General validation rules applied across all files:
Specific validation rules applicable per file type:
sub_constituency_codecolumn should only contain positive integer values.number_of_ballotscolumn values should match with the expected number of ballot types. For example, if a row in the sub constituency file hasnumber_of_ballotscolumn value as2then it can only have two ballot types e.g.ballot_number_presidentialandballot_number_general.field_office_namecolumn should only contain string/varchar values.constituency_namecolumn should only contain string/varchar values.constituencywas not found with the providedconstituency_name, fail with an error.ballot_number_presidential,ballot_number_generale.t.c.) should only contain positive integer values.center_idcolumn should only contain positive integer values.namecolumn should only contain string/varchar values.region_namecolumn should only contain string/varchar values.regionwas not found with the providedregion_name, fail with an error.office_idcolumn should only contain positive integer values.office_namecolumn should only contain string/varchar values.officewas not found with the providedoffice_name, 'regionandoffice_id`, fail with an error.constituency_namecolumn should only contain string/varchar values.constituencywas not found with the providedconstituency_name, fail with an error.subconstituency_idcolumn should only contain positive integer values. Is there a code forno constituency?, because I have seen a center file that has thesubconstituency_idvalue of999with the corresponding sub constituency name asNo Sub Constituency.sub constituencywas not found with the providedsubconstituency_id, fail with an error.station_numbercolumn should only contain positive integer values.station_gendercolumn should only contain string/varchar values.station_registrantscolumn should only contain positive integer values unless the gender type is neither male or female.sub_constituency_codecolumn should only contain positive integer values.subconstiencywas not found with the providedsub_constituency_code, fail with an error.center_codecolumn should only contain positive integer values.center_namecolumn should only contain string/varchar values.centerwas not found with the providedcenter_name/center_code, fail with an error.candidate_idcolumn should only contain positive integer values.ballot_numbercolumn should only contain positive integer values.candidate_full_namecolumn should only contain string/varchar values.ballotwas not found with the providedballot_number, fail with an error.barcodecolumn should only contain string/varchar values.ballot_numbercolumn should only contain positive integer values.ballotretrieved via theballot_numberis not active, fail with an error.ballotwas not found with the providedballot_number, fail with an error.center_codecolumn should only contain positive integer values.centerretrieved via thecenter_codeis not active, fail with an error.centerwas not found with the providedcenter_code, fail with an error.station_numbercolumn should only contain positive integer values.stationretrieved via thestation_numberandcenteris not active, fail with an error.stationwas not found with the providedstation_numberandcenter, fail with an error.office_namecolumn should only contain string/varchar values.officewas not found with the providedoffice_name, fail with an error.namecolumn should only contain string/varchar values.Related to:
https://github.com/onaio/tally-ho/issues/372