Only count votes for a user's district in senate elections

[ermsdev]

When the elections site was originally made we didn't have a way to know which user belonged to which district, so votes were counted on the assumption that voters would vote in their own district.

We now have access to user-district information and can reject votes for a district that a user doesn't belong to.

The desired behavior is for the server to reject votes for a district that a voter doesn't belong to and include a convenient way for uploading and modifying user-district information.

[ermsdev]

For the current (2019-20) senate election we will be running a sql query to make sure only valid votes are counted. For record keeping purposes I'm copying the query here.

district-n.csv files were formatted as:

"Doe, John", John.Doe@wallawalla.edu, John.Doe, "District 1"
"Doe, Jane", Jane.Doe@wallawalla.edu, Jane.Doe, "District 2"

/* create the table for district information */
DROP TABLE IF EXISTS districts;
CREATE TEMP TABLE districts (name TEXT, email TEXT, username TEXT, district TEXT);

/* import district information and set view mode */
.mode csv
.import <absolute path to disctrict-1.csv> districts
/* ... */
.import <absolute path to disctrict-n.csv> districts
.headers on
.mode column

/* count total number of valid votes per candidate */
SELECT votes.vote, positions."position", COUNT(votes.vote) as vote_count
FROM 
    votes 
    JOIN elections ON votes.election=elections.id 
    JOIN positions ON votes."position"=positions.id 
    JOIN districts ON votes.username LIKE districts.username
WHERE positions."position" LIKE districts.district
    AND votes.election='<election_id>'
GROUP BY votes.vote
ORDER BY positions."position", vote_count DESC;

DROP TABLE IF EXISTS districts;