summaryrefslogtreecommitdiff
path: root/models.go
diff options
context:
space:
mode:
authorJan Dittberner <jandd@cacert.org>2017-04-17 16:24:37 +0200
committerJan Dittberner <jan@dittberner.info>2017-04-22 00:12:24 +0200
commit6fe515ea52493ea79a07efe9e1fc652dea272e32 (patch)
treeabb381406580fe6c9d7f29f6bf9cf7f3b2177653 /models.go
parentf4360b98c8b3012bf27c75d9c04e7f9b737c9694 (diff)
downloadcacert-boardvoting-6fe515ea52493ea79a07efe9e1fc652dea272e32.tar.gz
cacert-boardvoting-6fe515ea52493ea79a07efe9e1fc652dea272e32.tar.xz
cacert-boardvoting-6fe515ea52493ea79a07efe9e1fc652dea272e32.zip
Implement proper model, actions and template structure
Diffstat (limited to 'models.go')
-rw-r--r--models.go346
1 files changed, 346 insertions, 0 deletions
diff --git a/models.go b/models.go
new file mode 100644
index 0000000..fe6cd23
--- /dev/null
+++ b/models.go
@@ -0,0 +1,346 @@
+package main
+
+import (
+ "database/sql"
+ "github.com/jmoiron/sqlx"
+ "time"
+)
+
+const (
+ sqlGetDecisions = `
+SELECT decisions.id, decisions.tag, decisions.proponent,
+ voters.name AS proposer, decisions.proposed, decisions.title,
+ decisions.content, decisions.votetype, decisions.status, decisions.due,
+ decisions.modified
+FROM decisions
+JOIN voters ON decisions.proponent=voters.id
+ORDER BY proposed DESC
+LIMIT 10 OFFSET 10 * $1`
+ sqlGetDecision = `
+SELECT decisions.id, decisions.tag, decisions.proponent,
+ voters.name AS proposer, decisions.proposed, decisions.title,
+ decisions.content, decisions.votetype, decisions.status, decisions.due,
+ decisions.modified
+FROM decisions
+JOIN voters ON decisions.proponent=voters.id
+WHERE decisions.tag=$1;`
+ sqlGetVoter = `
+SELECT voters.id, voters.name
+FROM voters
+JOIN emails ON voters.id=emails.voter
+WHERE emails.address=$1 AND voters.enabled=1`
+ sqlVoteCount = `
+SELECT vote, COUNT(vote)
+FROM votes
+WHERE decision=$1 GROUP BY vote`
+ sqlCountOlderThanDecision = `
+SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`
+ sqlGetVotesForDecision = `
+SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
+FROM votes
+JOIN voters ON votes.voter=voters.id
+WHERE decision=$1`
+ sqlListUnvotedDecisions = `
+SELECT decisions.id, decisions.tag, decisions.proponent,
+ voters.name AS proposer, decisions.proposed, decisions.title,
+ decisions.content AS content, decisions.votetype, decisions.status, decisions.due,
+ decisions.modified
+FROM decisions
+JOIN voters ON decisions.proponent=voters.id
+WHERE decisions.status=0 AND decisions.id NOT IN (
+ SELECT decision FROM votes WHERE votes.voter=$2)
+ORDER BY proposed DESC
+LIMIT 10 OFFSET 10 * $1`
+)
+
+var db *sqlx.DB
+
+type VoteType int
+type VoteStatus int
+
+type Decision struct {
+ Id int
+ Proposed time.Time
+ ProponentId int `db:"proponent"`
+ Title string
+ Content string
+ Quorum int
+ Majority int
+ Status VoteStatus
+ Due time.Time
+ Modified time.Time
+ Tag string
+ VoteType VoteType
+}
+
+type Email struct {
+ VoterId int `db:"voter"`
+ Address string
+}
+
+type Voter struct {
+ Id int
+ Name string
+ Enabled bool
+ Reminder string // reminder email address
+}
+
+type VoteChoice int
+
+type Vote struct {
+ DecisionId int `db:"decision"`
+ VoterId int `db:"voter"`
+ Vote VoteChoice
+ Voted time.Time
+ Notes string
+}
+
+const (
+ voteAye = 1
+ voteNaye = -1
+ voteAbstain = 0
+)
+
+const (
+ voteTypeMotion = 0
+ voteTypeVeto = 1
+)
+
+func (v VoteType) String() string {
+ switch v {
+ case voteTypeMotion:
+ return "motion"
+ case voteTypeVeto:
+ return "veto"
+ default:
+ return "unknown"
+ }
+}
+
+func (v VoteType) QuorumAndMajority() (int, int) {
+ switch v {
+ case voteTypeMotion:
+ return 3, 50
+ default:
+ return 1, 99
+ }
+}
+
+func (v VoteChoice) String() string {
+ switch v {
+ case voteAye:
+ return "aye"
+ case voteNaye:
+ return "naye"
+ case voteAbstain:
+ return "abstain"
+ default:
+ return "unknown"
+ }
+}
+
+func (v VoteStatus) String() string {
+ switch v {
+ case -1:
+ return "declined"
+ case 0:
+ return "pending"
+ case 1:
+ return "approved"
+ case -2:
+ return "withdrawn"
+ default:
+ return "unknown"
+ }
+}
+
+type VoteSums struct {
+ Ayes int
+ Nayes int
+ Abstains int
+}
+
+func (v *VoteSums) VoteCount() int {
+ return v.Ayes + v.Nayes + v.Abstains
+}
+
+type VoteForDisplay struct {
+ Vote
+ Name string
+}
+
+type DecisionForDisplay struct {
+ Decision
+ Proposer string `db:"proposer"`
+ *VoteSums
+ Votes []VoteForDisplay
+}
+
+func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
+ decisionStmt, err := db.Preparex(sqlGetDecision)
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer decisionStmt.Close()
+
+ decision = &DecisionForDisplay{}
+ if err = decisionStmt.Get(decision, tag); err != nil {
+ if err == sql.ErrNoRows {
+ decision = nil
+ err = nil
+ } else {
+ logger.Printf("Error getting motion %s: %v\n", tag, err)
+ }
+ }
+ decision.VoteSums, err = decision.Decision.VoteSums()
+ return
+}
+
+// FindDecisionsForDisplayOnPage loads a set of decisions from the database.
+//
+// This function uses OFFSET for pagination which is not a good idea for larger data sets.
+//
+// TODO: migrate to timestamp base pagination
+func FindDecisionsForDisplayOnPage(page int64) (decisions []*DecisionForDisplay, err error) {
+ decisionsStmt, err := db.Preparex(sqlGetDecisions)
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer decisionsStmt.Close()
+
+ rows, err := decisionsStmt.Queryx(page - 1)
+ if err != nil {
+ logger.Printf("Error loading motions for page %d: %v\n", page, err)
+ return
+ }
+ defer rows.Close()
+
+ for rows.Next() {
+ var d DecisionForDisplay
+ if err = rows.StructScan(&d); err != nil {
+ logger.Printf("Error loading motions for page %d: %v\n", page, err)
+ return
+ }
+ d.VoteSums, err = d.Decision.VoteSums()
+ if err != nil {
+ return
+ }
+ decisions = append(decisions, &d)
+ }
+ return
+}
+
+func FindVotersUnvotedDecisionsForDisplayOnPage(page int64, voter *Voter) (decisions []*DecisionForDisplay, err error) {
+ decisionsStmt, err := db.Preparex(sqlListUnvotedDecisions)
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer decisionsStmt.Close()
+
+ rows, err := decisionsStmt.Queryx(page - 1, voter.Id)
+ if err != nil {
+ logger.Printf("Error loading motions for page %d: %v\n", page, err)
+ return
+ }
+ defer rows.Close()
+
+ for rows.Next() {
+ var d DecisionForDisplay
+ if err = rows.StructScan(&d); err != nil {
+ logger.Printf("Error loading motions for page %d: %v\n", page, err)
+ return
+ }
+ d.VoteSums, err = d.Decision.VoteSums()
+ if err != nil {
+ return
+ }
+ decisions = append(decisions, &d)
+ }
+ return
+}
+
+func (d *Decision) VoteSums() (sums *VoteSums, err error) {
+ votesStmt, err := db.Preparex(sqlVoteCount)
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer votesStmt.Close()
+
+ voteRows, err := votesStmt.Queryx(d.Id)
+ if err != nil {
+ logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
+ return
+ }
+ defer voteRows.Close()
+
+ sums = &VoteSums{}
+ for voteRows.Next() {
+ var vote VoteChoice
+ var count int
+ if err = voteRows.Scan(&vote, &count); err != nil {
+ logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
+ return
+ }
+ switch vote {
+ case voteAye:
+ sums.Ayes = count
+ case voteNaye:
+ sums.Nayes = count
+ case voteAbstain:
+ sums.Abstains = count
+ }
+ }
+ return
+}
+
+func (d *DecisionForDisplay) LoadVotes() (err error) {
+ votesStmt, err := db.Preparex(sqlGetVotesForDecision)
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer votesStmt.Close()
+ err = votesStmt.Select(&d.Votes, d.Id)
+ if err != nil {
+ logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
+ }
+ return
+}
+
+func (d *Decision) OlderExists() (result bool, err error) {
+ olderStmt, err := db.Preparex(sqlCountOlderThanDecision)
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer olderStmt.Close()
+
+ if err := olderStmt.Get(&result, d.Proposed); err != nil {
+ logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
+ }
+ return
+}
+
+func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
+ findVoterStmt, err := db.Preparex(sqlGetVoter)
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer findVoterStmt.Close()
+
+ voter = &Voter{}
+ if err = findVoterStmt.Get(voter, emailAddress); err != nil {
+ if err != sql.ErrNoRows {
+ logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
+ } else {
+ err = nil
+ voter = nil
+ }
+ }
+ return
+}