Implement motion creation mail template
[cacert-boardvoting.git] / models.go
1 package main
2
3 import (
4 "database/sql"
5 "github.com/jmoiron/sqlx"
6 "time"
7 )
8
9 type sqlKey int
10
11 const (
12 sqlLoadDecisions sqlKey = iota
13 sqlLoadUnvotedDecisions
14 sqlLoadDecisionByTag
15 sqlLoadDecisionById
16 sqlLoadVoteCountsForDecision
17 sqlLoadVotesForDecision
18 sqlLoadEnabledVoterByEmail
19 sqlCountOlderThanDecision
20 sqlCountOlderThanUnvotedDecision
21 sqlCreateDecision
22 )
23
24 var sqlStatements = map[sqlKey]string{
25 sqlLoadDecisions: `
26 SELECT decisions.id, decisions.tag, decisions.proponent,
27 voters.name AS proposer, decisions.proposed, decisions.title,
28 decisions.content, decisions.votetype, decisions.status, decisions.due,
29 decisions.modified
30 FROM decisions
31 JOIN voters ON decisions.proponent=voters.id
32 ORDER BY proposed DESC
33 LIMIT 10 OFFSET 10 * $1`,
34 sqlLoadUnvotedDecisions: `
35 SELECT decisions.id, decisions.tag, decisions.proponent,
36 voters.name AS proposer, decisions.proposed, decisions.title,
37 decisions.content, decisions.votetype, decisions.status, decisions.due,
38 decisions.modified
39 FROM decisions
40 JOIN voters ON decisions.proponent=voters.id
41 WHERE decisions.status = 0 AND decisions.id NOT IN (
42 SELECT votes.decision
43 FROM votes
44 WHERE votes.voter = $1)
45 ORDER BY proposed DESC
46 LIMIT 10 OFFSET 10 * $2;`,
47 sqlLoadDecisionByTag: `
48 SELECT decisions.id, decisions.tag, decisions.proponent,
49 voters.name AS proposer, decisions.proposed, decisions.title,
50 decisions.content, decisions.votetype, decisions.status, decisions.due,
51 decisions.modified
52 FROM decisions
53 JOIN voters ON decisions.proponent=voters.id
54 WHERE decisions.tag=$1;`,
55 sqlLoadDecisionById: `
56 SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
57 decisions.title, decisions.content, decisions.votetype, decisions.status,
58 decisions.due, decisions.modified
59 FROM decisions
60 WHERE decisions.id=$1;`,
61 sqlLoadVoteCountsForDecision: `
62 SELECT vote, COUNT(vote)
63 FROM votes
64 WHERE decision=$1 GROUP BY vote`,
65 sqlLoadVotesForDecision: `
66 SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
67 FROM votes
68 JOIN voters ON votes.voter=voters.id
69 WHERE decision=$1`,
70 sqlLoadEnabledVoterByEmail: `
71 SELECT voters.id, voters.name, voters.enabled, voters.reminder
72 FROM voters
73 JOIN emails ON voters.id=emails.voter
74 WHERE emails.address=$1 AND voters.enabled=1`,
75 sqlCountOlderThanDecision: `
76 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`,
77 sqlCountOlderThanUnvotedDecision: `
78 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1
79 AND status=0
80 AND id NOT IN (SELECT decision FROM votes WHERE votes.voter=$2)`,
81 sqlCreateDecision: `
82 INSERT INTO decisions (
83 proposed, proponent, title, content, votetype, status, due, modified,tag
84 ) VALUES (
85 :proposed, :proponent, :title, :content, :votetype, 0,
86 :due,
87 :proposed,
88 'm' || strftime('%Y%m%d', :proposed) || '.' || (
89 SELECT COUNT(*)+1 AS num
90 FROM decisions
91 WHERE proposed
92 BETWEEN date(:proposed) AND date(:proposed, '1 day')
93 )
94 )`,
95 }
96
97 var db *sqlx.DB
98
99 func init() {
100 for _, sqlStatement := range sqlStatements {
101 var stmt *sqlx.Stmt
102 stmt, err := db.Preparex(sqlStatement)
103 if err != nil {
104 logger.Fatalf("ERROR parsing statement %s: %s", sqlStatement, err)
105 }
106 stmt.Close()
107 }
108 }
109
110 type VoteType uint8
111 type VoteStatus int8
112
113 type Decision struct {
114 Id int
115 Proposed time.Time
116 ProponentId int `db:"proponent"`
117 Title string
118 Content string
119 Quorum int
120 Majority int
121 Status VoteStatus
122 Due time.Time
123 Modified time.Time
124 Tag string
125 VoteType VoteType
126 }
127
128 type Email struct {
129 VoterId int `db:"voter"`
130 Address string
131 }
132
133 type Voter struct {
134 Id int
135 Name string
136 Enabled bool
137 Reminder string // reminder email address
138 }
139
140 type VoteChoice int
141
142 type Vote struct {
143 DecisionId int `db:"decision"`
144 VoterId int `db:"voter"`
145 Vote VoteChoice
146 Voted time.Time
147 Notes string
148 }
149
150 const (
151 voteAye = 1
152 voteNaye = -1
153 voteAbstain = 0
154 )
155
156 const (
157 voteTypeMotion = 0
158 voteTypeVeto = 1
159 )
160
161 func (v VoteType) String() string {
162 switch v {
163 case voteTypeMotion:
164 return "motion"
165 case voteTypeVeto:
166 return "veto"
167 default:
168 return "unknown"
169 }
170 }
171
172 func (v VoteType) QuorumAndMajority() (int, int) {
173 switch v {
174 case voteTypeMotion:
175 return 3, 50
176 default:
177 return 1, 99
178 }
179 }
180
181 func (v VoteChoice) String() string {
182 switch v {
183 case voteAye:
184 return "aye"
185 case voteNaye:
186 return "naye"
187 case voteAbstain:
188 return "abstain"
189 default:
190 return "unknown"
191 }
192 }
193
194 const (
195 voteStatusDeclined = -1
196 voteStatusPending = 0
197 voteStatusApproved = 1
198 voteStatusWithdrawn = -2
199 )
200
201 func (v VoteStatus) String() string {
202 switch v {
203 case voteStatusDeclined:
204 return "declined"
205 case voteStatusPending:
206 return "pending"
207 case voteStatusApproved:
208 return "approved"
209 case voteStatusWithdrawn:
210 return "withdrawn"
211 default:
212 return "unknown"
213 }
214 }
215
216 type VoteSums struct {
217 Ayes int
218 Nayes int
219 Abstains int
220 }
221
222 func (v *VoteSums) VoteCount() int {
223 return v.Ayes + v.Nayes + v.Abstains
224 }
225
226 type VoteForDisplay struct {
227 Vote
228 Name string
229 }
230
231 type DecisionForDisplay struct {
232 Decision
233 Proposer string `db:"proposer"`
234 *VoteSums
235 Votes []VoteForDisplay
236 }
237
238 func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
239 decisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionByTag])
240 if err != nil {
241 logger.Println("Error preparing statement:", err)
242 return
243 }
244 defer decisionStmt.Close()
245
246 decision = &DecisionForDisplay{}
247 if err = decisionStmt.Get(decision, tag); err != nil {
248 if err == sql.ErrNoRows {
249 decision = nil
250 err = nil
251 } else {
252 logger.Printf("Error getting motion %s: %v\n", tag, err)
253 }
254 }
255 decision.VoteSums, err = decision.Decision.VoteSums()
256 return
257 }
258
259 // FindDecisionsForDisplayOnPage loads a set of decisions from the database.
260 //
261 // This function uses OFFSET for pagination which is not a good idea for larger data sets.
262 //
263 // TODO: migrate to timestamp base pagination
264 func FindDecisionsForDisplayOnPage(page int64, unvoted bool, voter *Voter) (decisions []*DecisionForDisplay, err error) {
265 var decisionsStmt *sqlx.Stmt
266 if unvoted && voter != nil {
267 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadUnvotedDecisions])
268 } else {
269 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadDecisions])
270 }
271 if err != nil {
272 logger.Println("Error preparing statement:", err)
273 return
274 }
275 defer decisionsStmt.Close()
276
277 var rows *sqlx.Rows
278 if unvoted && voter != nil {
279 rows, err = decisionsStmt.Queryx(voter.Id, page-1)
280 } else {
281 rows, err = decisionsStmt.Queryx(page - 1)
282 }
283 if err != nil {
284 logger.Printf("Error loading motions for page %d: %v\n", page, err)
285 return
286 }
287 defer rows.Close()
288
289 for rows.Next() {
290 var d DecisionForDisplay
291 if err = rows.StructScan(&d); err != nil {
292 logger.Printf("Error loading motions for page %d: %v\n", page, err)
293 return
294 }
295 d.VoteSums, err = d.Decision.VoteSums()
296 if err != nil {
297 return
298 }
299 decisions = append(decisions, &d)
300 }
301 return
302 }
303
304 func (d *Decision) VoteSums() (sums *VoteSums, err error) {
305 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVoteCountsForDecision])
306 if err != nil {
307 logger.Println("Error preparing statement:", err)
308 return
309 }
310 defer votesStmt.Close()
311
312 voteRows, err := votesStmt.Queryx(d.Id)
313 if err != nil {
314 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
315 return
316 }
317 defer voteRows.Close()
318
319 sums = &VoteSums{}
320 for voteRows.Next() {
321 var vote VoteChoice
322 var count int
323 if err = voteRows.Scan(&vote, &count); err != nil {
324 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
325 return
326 }
327 switch vote {
328 case voteAye:
329 sums.Ayes = count
330 case voteNaye:
331 sums.Nayes = count
332 case voteAbstain:
333 sums.Abstains = count
334 }
335 }
336 return
337 }
338
339 func (d *DecisionForDisplay) LoadVotes() (err error) {
340 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVotesForDecision])
341 if err != nil {
342 logger.Println("Error preparing statement:", err)
343 return
344 }
345 defer votesStmt.Close()
346 err = votesStmt.Select(&d.Votes, d.Id)
347 if err != nil {
348 logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
349 }
350 return
351 }
352
353 func (d *Decision) OlderExists(unvoted bool, voter *Voter) (result bool, err error) {
354 var olderStmt *sqlx.Stmt
355 if unvoted && voter != nil {
356 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanUnvotedDecision])
357 } else {
358 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanDecision])
359 }
360 if err != nil {
361 logger.Println("Error preparing statement:", err)
362 return
363 }
364 defer olderStmt.Close()
365
366 if unvoted && voter != nil {
367 if err = olderStmt.Get(&result, d.Proposed, voter.Id); err != nil {
368 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
369 }
370 } else {
371 if err = olderStmt.Get(&result, d.Proposed); err != nil {
372 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
373 }
374 }
375
376 return
377 }
378
379 func (d *Decision) Save() (err error) {
380 insertDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlCreateDecision])
381 if err != nil {
382 logger.Println("Error preparing statement:", err)
383 return
384 }
385 defer insertDecisionStmt.Close()
386
387 result, err := insertDecisionStmt.Exec(d)
388 if err != nil {
389 logger.Println("Error creating motion:", err)
390 return
391 }
392
393 lastInsertId, err := result.LastInsertId()
394 if err != nil {
395 logger.Println("Error getting id of inserted motion:", err)
396 }
397 logger.Println("DEBUG new motion has id", lastInsertId)
398
399 getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
400 if err != nil {
401 logger.Println("Error preparing statement:", err)
402 return
403 }
404 defer getDecisionStmt.Close()
405
406 err = getDecisionStmt.Get(d, lastInsertId)
407 if err != nil {
408 logger.Println("Error getting inserted motion:", err)
409 }
410
411 return
412 }
413
414 func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
415 findVoterStmt, err := db.Preparex(sqlStatements[sqlLoadEnabledVoterByEmail])
416 if err != nil {
417 logger.Println("Error preparing statement:", err)
418 return
419 }
420 defer findVoterStmt.Close()
421
422 voter = &Voter{}
423 if err = findVoterStmt.Get(voter, emailAddress); err != nil {
424 if err != sql.ErrNoRows {
425 logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
426 } else {
427 err = nil
428 voter = nil
429 }
430 }
431 return
432 }