Implement proper model, actions and template structure
[cacert-boardvoting.git] / models.go
1 package main
2
3 import (
4 "database/sql"
5 "github.com/jmoiron/sqlx"
6 "time"
7 )
8
9 const (
10 sqlGetDecisions = `
11 SELECT decisions.id, decisions.tag, decisions.proponent,
12 voters.name AS proposer, decisions.proposed, decisions.title,
13 decisions.content, decisions.votetype, decisions.status, decisions.due,
14 decisions.modified
15 FROM decisions
16 JOIN voters ON decisions.proponent=voters.id
17 ORDER BY proposed DESC
18 LIMIT 10 OFFSET 10 * $1`
19 sqlGetDecision = `
20 SELECT decisions.id, decisions.tag, decisions.proponent,
21 voters.name AS proposer, decisions.proposed, decisions.title,
22 decisions.content, decisions.votetype, decisions.status, decisions.due,
23 decisions.modified
24 FROM decisions
25 JOIN voters ON decisions.proponent=voters.id
26 WHERE decisions.tag=$1;`
27 sqlGetVoter = `
28 SELECT voters.id, voters.name
29 FROM voters
30 JOIN emails ON voters.id=emails.voter
31 WHERE emails.address=$1 AND voters.enabled=1`
32 sqlVoteCount = `
33 SELECT vote, COUNT(vote)
34 FROM votes
35 WHERE decision=$1 GROUP BY vote`
36 sqlCountOlderThanDecision = `
37 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`
38 sqlGetVotesForDecision = `
39 SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
40 FROM votes
41 JOIN voters ON votes.voter=voters.id
42 WHERE decision=$1`
43 sqlListUnvotedDecisions = `
44 SELECT decisions.id, decisions.tag, decisions.proponent,
45 voters.name AS proposer, decisions.proposed, decisions.title,
46 decisions.content AS content, decisions.votetype, decisions.status, decisions.due,
47 decisions.modified
48 FROM decisions
49 JOIN voters ON decisions.proponent=voters.id
50 WHERE decisions.status=0 AND decisions.id NOT IN (
51 SELECT decision FROM votes WHERE votes.voter=$2)
52 ORDER BY proposed DESC
53 LIMIT 10 OFFSET 10 * $1`
54 )
55
56 var db *sqlx.DB
57
58 type VoteType int
59 type VoteStatus int
60
61 type Decision struct {
62 Id int
63 Proposed time.Time
64 ProponentId int `db:"proponent"`
65 Title string
66 Content string
67 Quorum int
68 Majority int
69 Status VoteStatus
70 Due time.Time
71 Modified time.Time
72 Tag string
73 VoteType VoteType
74 }
75
76 type Email struct {
77 VoterId int `db:"voter"`
78 Address string
79 }
80
81 type Voter struct {
82 Id int
83 Name string
84 Enabled bool
85 Reminder string // reminder email address
86 }
87
88 type VoteChoice int
89
90 type Vote struct {
91 DecisionId int `db:"decision"`
92 VoterId int `db:"voter"`
93 Vote VoteChoice
94 Voted time.Time
95 Notes string
96 }
97
98 const (
99 voteAye = 1
100 voteNaye = -1
101 voteAbstain = 0
102 )
103
104 const (
105 voteTypeMotion = 0
106 voteTypeVeto = 1
107 )
108
109 func (v VoteType) String() string {
110 switch v {
111 case voteTypeMotion:
112 return "motion"
113 case voteTypeVeto:
114 return "veto"
115 default:
116 return "unknown"
117 }
118 }
119
120 func (v VoteType) QuorumAndMajority() (int, int) {
121 switch v {
122 case voteTypeMotion:
123 return 3, 50
124 default:
125 return 1, 99
126 }
127 }
128
129 func (v VoteChoice) String() string {
130 switch v {
131 case voteAye:
132 return "aye"
133 case voteNaye:
134 return "naye"
135 case voteAbstain:
136 return "abstain"
137 default:
138 return "unknown"
139 }
140 }
141
142 func (v VoteStatus) String() string {
143 switch v {
144 case -1:
145 return "declined"
146 case 0:
147 return "pending"
148 case 1:
149 return "approved"
150 case -2:
151 return "withdrawn"
152 default:
153 return "unknown"
154 }
155 }
156
157 type VoteSums struct {
158 Ayes int
159 Nayes int
160 Abstains int
161 }
162
163 func (v *VoteSums) VoteCount() int {
164 return v.Ayes + v.Nayes + v.Abstains
165 }
166
167 type VoteForDisplay struct {
168 Vote
169 Name string
170 }
171
172 type DecisionForDisplay struct {
173 Decision
174 Proposer string `db:"proposer"`
175 *VoteSums
176 Votes []VoteForDisplay
177 }
178
179 func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
180 decisionStmt, err := db.Preparex(sqlGetDecision)
181 if err != nil {
182 logger.Println("Error preparing statement:", err)
183 return
184 }
185 defer decisionStmt.Close()
186
187 decision = &DecisionForDisplay{}
188 if err = decisionStmt.Get(decision, tag); err != nil {
189 if err == sql.ErrNoRows {
190 decision = nil
191 err = nil
192 } else {
193 logger.Printf("Error getting motion %s: %v\n", tag, err)
194 }
195 }
196 decision.VoteSums, err = decision.Decision.VoteSums()
197 return
198 }
199
200 // FindDecisionsForDisplayOnPage loads a set of decisions from the database.
201 //
202 // This function uses OFFSET for pagination which is not a good idea for larger data sets.
203 //
204 // TODO: migrate to timestamp base pagination
205 func FindDecisionsForDisplayOnPage(page int64) (decisions []*DecisionForDisplay, err error) {
206 decisionsStmt, err := db.Preparex(sqlGetDecisions)
207 if err != nil {
208 logger.Println("Error preparing statement:", err)
209 return
210 }
211 defer decisionsStmt.Close()
212
213 rows, err := decisionsStmt.Queryx(page - 1)
214 if err != nil {
215 logger.Printf("Error loading motions for page %d: %v\n", page, err)
216 return
217 }
218 defer rows.Close()
219
220 for rows.Next() {
221 var d DecisionForDisplay
222 if err = rows.StructScan(&d); err != nil {
223 logger.Printf("Error loading motions for page %d: %v\n", page, err)
224 return
225 }
226 d.VoteSums, err = d.Decision.VoteSums()
227 if err != nil {
228 return
229 }
230 decisions = append(decisions, &d)
231 }
232 return
233 }
234
235 func FindVotersUnvotedDecisionsForDisplayOnPage(page int64, voter *Voter) (decisions []*DecisionForDisplay, err error) {
236 decisionsStmt, err := db.Preparex(sqlListUnvotedDecisions)
237 if err != nil {
238 logger.Println("Error preparing statement:", err)
239 return
240 }
241 defer decisionsStmt.Close()
242
243 rows, err := decisionsStmt.Queryx(page - 1, voter.Id)
244 if err != nil {
245 logger.Printf("Error loading motions for page %d: %v\n", page, err)
246 return
247 }
248 defer rows.Close()
249
250 for rows.Next() {
251 var d DecisionForDisplay
252 if err = rows.StructScan(&d); err != nil {
253 logger.Printf("Error loading motions for page %d: %v\n", page, err)
254 return
255 }
256 d.VoteSums, err = d.Decision.VoteSums()
257 if err != nil {
258 return
259 }
260 decisions = append(decisions, &d)
261 }
262 return
263 }
264
265 func (d *Decision) VoteSums() (sums *VoteSums, err error) {
266 votesStmt, err := db.Preparex(sqlVoteCount)
267 if err != nil {
268 logger.Println("Error preparing statement:", err)
269 return
270 }
271 defer votesStmt.Close()
272
273 voteRows, err := votesStmt.Queryx(d.Id)
274 if err != nil {
275 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
276 return
277 }
278 defer voteRows.Close()
279
280 sums = &VoteSums{}
281 for voteRows.Next() {
282 var vote VoteChoice
283 var count int
284 if err = voteRows.Scan(&vote, &count); err != nil {
285 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
286 return
287 }
288 switch vote {
289 case voteAye:
290 sums.Ayes = count
291 case voteNaye:
292 sums.Nayes = count
293 case voteAbstain:
294 sums.Abstains = count
295 }
296 }
297 return
298 }
299
300 func (d *DecisionForDisplay) LoadVotes() (err error) {
301 votesStmt, err := db.Preparex(sqlGetVotesForDecision)
302 if err != nil {
303 logger.Println("Error preparing statement:", err)
304 return
305 }
306 defer votesStmt.Close()
307 err = votesStmt.Select(&d.Votes, d.Id)
308 if err != nil {
309 logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
310 }
311 return
312 }
313
314 func (d *Decision) OlderExists() (result bool, err error) {
315 olderStmt, err := db.Preparex(sqlCountOlderThanDecision)
316 if err != nil {
317 logger.Println("Error preparing statement:", err)
318 return
319 }
320 defer olderStmt.Close()
321
322 if err := olderStmt.Get(&result, d.Proposed); err != nil {
323 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
324 }
325 return
326 }
327
328 func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
329 findVoterStmt, err := db.Preparex(sqlGetVoter)
330 if err != nil {
331 logger.Println("Error preparing statement:", err)
332 return
333 }
334 defer findVoterStmt.Close()
335
336 voter = &Voter{}
337 if err = findVoterStmt.Get(voter, emailAddress); err != nil {
338 if err != sql.ErrNoRows {
339 logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
340 } else {
341 err = nil
342 voter = nil
343 }
344 }
345 return
346 }