Implement decision closing job
[cacert-boardvoting.git] / models.go
1 package main
2
3 import (
4 "database/sql"
5 "fmt"
6 "github.com/jmoiron/sqlx"
7 "time"
8 )
9
10 type sqlKey int
11
12 const (
13 sqlLoadDecisions sqlKey = iota
14 sqlLoadUnvotedDecisions
15 sqlLoadDecisionByTag
16 sqlLoadDecisionById
17 sqlLoadVoteCountsForDecision
18 sqlLoadVotesForDecision
19 sqlLoadEnabledVoterByEmail
20 sqlCountOlderThanDecision
21 sqlCountOlderThanUnvotedDecision
22 sqlCreateDecision
23 sqlUpdateDecision
24 sqlUpdateDecisionStatus
25 sqlSelectClosableDecisions
26 sqlGetNextPendingDecisionDue
27 )
28
29 var sqlStatements = map[sqlKey]string{
30 sqlLoadDecisions: `
31 SELECT decisions.id, decisions.tag, decisions.proponent,
32 voters.name AS proposer, decisions.proposed, decisions.title,
33 decisions.content, decisions.votetype, decisions.status, decisions.due,
34 decisions.modified
35 FROM decisions
36 JOIN voters ON decisions.proponent=voters.id
37 ORDER BY proposed DESC
38 LIMIT 10 OFFSET 10 * $1`,
39 sqlLoadUnvotedDecisions: `
40 SELECT decisions.id, decisions.tag, decisions.proponent,
41 voters.name AS proposer, decisions.proposed, decisions.title,
42 decisions.content, decisions.votetype, decisions.status, decisions.due,
43 decisions.modified
44 FROM decisions
45 JOIN voters ON decisions.proponent=voters.id
46 WHERE decisions.status = 0 AND decisions.id NOT IN (
47 SELECT votes.decision
48 FROM votes
49 WHERE votes.voter = $1)
50 ORDER BY proposed DESC
51 LIMIT 10 OFFSET 10 * $2;`,
52 sqlLoadDecisionByTag: `
53 SELECT decisions.id, decisions.tag, decisions.proponent,
54 voters.name AS proposer, decisions.proposed, decisions.title,
55 decisions.content, decisions.votetype, decisions.status, decisions.due,
56 decisions.modified
57 FROM decisions
58 JOIN voters ON decisions.proponent=voters.id
59 WHERE decisions.tag=$1;`,
60 sqlLoadDecisionById: `
61 SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
62 decisions.title, decisions.content, decisions.votetype, decisions.status,
63 decisions.due, decisions.modified
64 FROM decisions
65 WHERE decisions.id=$1;`,
66 sqlLoadVoteCountsForDecision: `
67 SELECT vote, COUNT(vote)
68 FROM votes
69 WHERE decision=$1 GROUP BY vote`,
70 sqlLoadVotesForDecision: `
71 SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
72 FROM votes
73 JOIN voters ON votes.voter=voters.id
74 WHERE decision=$1`,
75 sqlLoadEnabledVoterByEmail: `
76 SELECT voters.id, voters.name, voters.enabled, voters.reminder
77 FROM voters
78 JOIN emails ON voters.id=emails.voter
79 WHERE emails.address=$1 AND voters.enabled=1`,
80 sqlCountOlderThanDecision: `
81 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`,
82 sqlCountOlderThanUnvotedDecision: `
83 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1
84 AND status=0
85 AND id NOT IN (SELECT decision FROM votes WHERE votes.voter=$2)`,
86 sqlCreateDecision: `
87 INSERT INTO decisions (
88 proposed, proponent, title, content, votetype, status, due, modified,tag
89 ) VALUES (
90 :proposed, :proponent, :title, :content, :votetype, 0,
91 :due,
92 :proposed,
93 'm' || strftime('%Y%m%d', :proposed) || '.' || (
94 SELECT COUNT(*)+1 AS num
95 FROM decisions
96 WHERE proposed
97 BETWEEN date(:proposed) AND date(:proposed, '1 day')
98 )
99 )`,
100 sqlUpdateDecision: `
101 UPDATE decisions
102 SET proponent=:proponent, title=:title, content=:content,
103 votetype=:votetype, due=:due, modified=:modified
104 WHERE id=:id`,
105 sqlUpdateDecisionStatus: `
106 UPDATE decisions
107 SET status=:status, modified=:modified WHERE id=:id
108 `,
109 sqlSelectClosableDecisions: `
110 SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
111 decisions.title, decisions.content, decisions.votetype, decisions.status,
112 decisions.due, decisions.modified
113 FROM decisions
114 WHERE decisions.status=0 AND :now > due`,
115 sqlGetNextPendingDecisionDue: `
116 SELECT due FROM decisions WHERE status=0 ORDER BY due LIMIT 1`,
117 }
118
119 var db *sqlx.DB
120
121 func init() {
122 for _, sqlStatement := range sqlStatements {
123 var stmt *sqlx.Stmt
124 stmt, err := db.Preparex(sqlStatement)
125 if err != nil {
126 logger.Fatalf("ERROR parsing statement %s: %s", sqlStatement, err)
127 }
128 stmt.Close()
129 }
130 }
131
132 type VoteType uint8
133 type VoteStatus int8
134
135 type Decision struct {
136 Id int
137 Proposed time.Time
138 ProponentId int `db:"proponent"`
139 Title string
140 Content string
141 Quorum int
142 Majority int
143 Status VoteStatus
144 Due time.Time
145 Modified time.Time
146 Tag string
147 VoteType VoteType
148 }
149
150 type Email struct {
151 VoterId int `db:"voter"`
152 Address string
153 }
154
155 type Voter struct {
156 Id int
157 Name string
158 Enabled bool
159 Reminder string // reminder email address
160 }
161
162 type VoteChoice int
163
164 type Vote struct {
165 DecisionId int `db:"decision"`
166 VoterId int `db:"voter"`
167 Vote VoteChoice
168 Voted time.Time
169 Notes string
170 }
171
172 const (
173 voteAye = 1
174 voteNaye = -1
175 voteAbstain = 0
176 )
177
178 const (
179 voteTypeMotion = 0
180 voteTypeVeto = 1
181 )
182
183 func (v VoteType) String() string {
184 switch v {
185 case voteTypeMotion:
186 return "motion"
187 case voteTypeVeto:
188 return "veto"
189 default:
190 return "unknown"
191 }
192 }
193
194 func (v VoteType) QuorumAndMajority() (int, int) {
195 switch v {
196 case voteTypeMotion:
197 return 3, 50
198 default:
199 return 1, 99
200 }
201 }
202
203 func (v VoteChoice) String() string {
204 switch v {
205 case voteAye:
206 return "aye"
207 case voteNaye:
208 return "naye"
209 case voteAbstain:
210 return "abstain"
211 default:
212 return "unknown"
213 }
214 }
215
216 const (
217 voteStatusDeclined = -1
218 voteStatusPending = 0
219 voteStatusApproved = 1
220 voteStatusWithdrawn = -2
221 )
222
223 func (v VoteStatus) String() string {
224 switch v {
225 case voteStatusDeclined:
226 return "declined"
227 case voteStatusPending:
228 return "pending"
229 case voteStatusApproved:
230 return "approved"
231 case voteStatusWithdrawn:
232 return "withdrawn"
233 default:
234 return "unknown"
235 }
236 }
237
238 type VoteSums struct {
239 Ayes int
240 Nayes int
241 Abstains int
242 }
243
244 func (v *VoteSums) VoteCount() int {
245 return v.Ayes + v.Nayes + v.Abstains
246 }
247
248 func (v *VoteSums) TotalVotes() int {
249 return v.Ayes + v.Nayes
250 }
251
252 func (v *VoteSums) Percent() int {
253 totalVotes := v.TotalVotes()
254 if totalVotes == 0 {
255 return 0
256 }
257 return v.Ayes * 100 / totalVotes
258 }
259
260 type VoteForDisplay struct {
261 Vote
262 Name string
263 }
264
265 type DecisionForDisplay struct {
266 Decision
267 Proposer string `db:"proposer"`
268 *VoteSums
269 Votes []VoteForDisplay
270 }
271
272 func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
273 decisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionByTag])
274 if err != nil {
275 logger.Println("Error preparing statement:", err)
276 return
277 }
278 defer decisionStmt.Close()
279
280 decision = &DecisionForDisplay{}
281 if err = decisionStmt.Get(decision, tag); err != nil {
282 if err == sql.ErrNoRows {
283 decision = nil
284 err = nil
285 } else {
286 logger.Printf("Error getting motion %s: %v\n", tag, err)
287 }
288 }
289 decision.VoteSums, err = decision.Decision.VoteSums()
290 return
291 }
292
293 // FindDecisionsForDisplayOnPage loads a set of decisions from the database.
294 //
295 // This function uses OFFSET for pagination which is not a good idea for larger data sets.
296 //
297 // TODO: migrate to timestamp base pagination
298 func FindDecisionsForDisplayOnPage(page int64, unvoted bool, voter *Voter) (decisions []*DecisionForDisplay, err error) {
299 var decisionsStmt *sqlx.Stmt
300 if unvoted && voter != nil {
301 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadUnvotedDecisions])
302 } else {
303 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadDecisions])
304 }
305 if err != nil {
306 logger.Println("Error preparing statement:", err)
307 return
308 }
309 defer decisionsStmt.Close()
310
311 var rows *sqlx.Rows
312 if unvoted && voter != nil {
313 rows, err = decisionsStmt.Queryx(voter.Id, page-1)
314 } else {
315 rows, err = decisionsStmt.Queryx(page - 1)
316 }
317 if err != nil {
318 logger.Printf("Error loading motions for page %d: %v\n", page, err)
319 return
320 }
321 defer rows.Close()
322
323 for rows.Next() {
324 var d DecisionForDisplay
325 if err = rows.StructScan(&d); err != nil {
326 logger.Printf("Error loading motions for page %d: %v\n", page, err)
327 return
328 }
329 d.VoteSums, err = d.Decision.VoteSums()
330 if err != nil {
331 return
332 }
333 decisions = append(decisions, &d)
334 }
335 return
336 }
337
338 func (d *Decision) VoteSums() (sums *VoteSums, err error) {
339 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVoteCountsForDecision])
340 if err != nil {
341 logger.Println("Error preparing statement:", err)
342 return
343 }
344 defer votesStmt.Close()
345
346 voteRows, err := votesStmt.Queryx(d.Id)
347 if err != nil {
348 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
349 return
350 }
351 defer voteRows.Close()
352
353 sums = &VoteSums{}
354 for voteRows.Next() {
355 var vote VoteChoice
356 var count int
357 if err = voteRows.Scan(&vote, &count); err != nil {
358 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
359 return
360 }
361 switch vote {
362 case voteAye:
363 sums.Ayes = count
364 case voteNaye:
365 sums.Nayes = count
366 case voteAbstain:
367 sums.Abstains = count
368 }
369 }
370 return
371 }
372
373 func (d *DecisionForDisplay) LoadVotes() (err error) {
374 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVotesForDecision])
375 if err != nil {
376 logger.Println("Error preparing statement:", err)
377 return
378 }
379 defer votesStmt.Close()
380 err = votesStmt.Select(&d.Votes, d.Id)
381 if err != nil {
382 logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
383 }
384 return
385 }
386
387 func (d *Decision) OlderExists(unvoted bool, voter *Voter) (result bool, err error) {
388 var olderStmt *sqlx.Stmt
389 if unvoted && voter != nil {
390 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanUnvotedDecision])
391 } else {
392 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanDecision])
393 }
394 if err != nil {
395 logger.Println("Error preparing statement:", err)
396 return
397 }
398 defer olderStmt.Close()
399
400 if unvoted && voter != nil {
401 if err = olderStmt.Get(&result, d.Proposed, voter.Id); err != nil {
402 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
403 }
404 } else {
405 if err = olderStmt.Get(&result, d.Proposed); err != nil {
406 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
407 }
408 }
409
410 return
411 }
412
413 func (d *Decision) Create() (err error) {
414 insertDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlCreateDecision])
415 if err != nil {
416 logger.Println("Error preparing statement:", err)
417 return
418 }
419 defer insertDecisionStmt.Close()
420
421 result, err := insertDecisionStmt.Exec(d)
422 if err != nil {
423 logger.Println("Error creating motion:", err)
424 return
425 }
426
427 lastInsertId, err := result.LastInsertId()
428 if err != nil {
429 logger.Println("Error getting id of inserted motion:", err)
430 return
431 }
432 rescheduleChannel <- JobIdCloseDecisions
433
434 getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
435 if err != nil {
436 logger.Println("Error preparing statement:", err)
437 return
438 }
439 defer getDecisionStmt.Close()
440
441 err = getDecisionStmt.Get(d, lastInsertId)
442 if err != nil {
443 logger.Println("Error getting inserted motion:", err)
444 }
445
446 return
447 }
448
449 func (d *Decision) LoadWithId() (err error) {
450 getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
451 if err != nil {
452 logger.Println("Error preparing statement:", err)
453 return
454 }
455 defer getDecisionStmt.Close()
456
457 err = getDecisionStmt.Get(d, d.Id)
458 if err != nil {
459 logger.Println("Error loading updated motion:", err)
460 }
461
462 return
463 }
464
465 func (d *Decision) Update() (err error) {
466 updateDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecision])
467 if err != nil {
468 logger.Println("Error preparing statement:", err)
469 return
470 }
471 defer updateDecisionStmt.Close()
472
473 result, err := updateDecisionStmt.Exec(d)
474 if err != nil {
475 logger.Println("Error updating motion:", err)
476 return
477 }
478 affectedRows, err := result.RowsAffected()
479 if err != nil {
480 logger.Print("Problem determining the affected rows")
481 return
482 } else if affectedRows != 1 {
483 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
484 }
485 rescheduleChannel <- JobIdCloseDecisions
486
487 err = d.LoadWithId()
488 return
489 }
490
491 func (d *Decision) UpdateStatus() (err error) {
492 updateStatusStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecisionStatus])
493 if err != nil {
494 logger.Println("Error preparing statement:", err)
495 return
496 }
497 defer updateStatusStmt.Close()
498
499 result, err := updateStatusStmt.Exec(d)
500 if err != nil {
501 logger.Println("Error setting motion status:", err)
502 return
503 }
504 affectedRows, err := result.RowsAffected()
505 if err != nil {
506 logger.Print("Problem determining the affected rows")
507 return
508 } else if affectedRows != 1 {
509 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
510 }
511 rescheduleChannel <- JobIdCloseDecisions
512
513 err = d.LoadWithId()
514 return
515 }
516
517 func (d *Decision) String() string {
518 return fmt.Sprintf("%s %s (Id %d)", d.Tag, d.Title, d.Id)
519 }
520
521 func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
522 findVoterStmt, err := db.Preparex(sqlStatements[sqlLoadEnabledVoterByEmail])
523 if err != nil {
524 logger.Println("Error preparing statement:", err)
525 return
526 }
527 defer findVoterStmt.Close()
528
529 voter = &Voter{}
530 if err = findVoterStmt.Get(voter, emailAddress); err != nil {
531 if err != sql.ErrNoRows {
532 logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
533 } else {
534 err = nil
535 voter = nil
536 }
537 }
538 return
539 }
540
541 func (d *Decision) Close() (err error) {
542 quorum, majority := d.VoteType.QuorumAndMajority()
543
544 voteSums, err := d.VoteSums()
545
546 if err != nil {
547 logger.Println("Error getting vote sums")
548 return
549 }
550 votes := voteSums.VoteCount()
551
552 if votes < quorum {
553 d.Status = voteStatusDeclined
554 } else {
555 votes = voteSums.TotalVotes()
556 if (voteSums.Ayes / votes) > (majority / 100) {
557 d.Status = voteStatusApproved
558 } else {
559 d.Status = voteStatusDeclined
560 }
561 }
562
563 closeDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecisionStatus])
564 if err != nil {
565 logger.Println("Error preparing statement:", err)
566 return
567 }
568 defer closeDecisionStmt.Close()
569
570 result, err := closeDecisionStmt.Exec(d)
571 if err != nil {
572 logger.Println("Error closing vote:", err)
573 return
574 }
575 affectedRows, err := result.RowsAffected()
576 if err != nil {
577 logger.Println("Error getting affected rows:", err)
578 }
579 if affectedRows != 1 {
580 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
581 }
582
583 notifyMail <- &NotificationClosedDecision{decision: *d, voteSums: *voteSums}
584
585 return
586 }
587
588 func CloseDecisions() (err error) {
589 getClosableDecisionsStmt, err := db.PrepareNamed(sqlStatements[sqlSelectClosableDecisions])
590 if err != nil {
591 logger.Println("Error preparing statement:", err)
592 return
593 }
594 defer getClosableDecisionsStmt.Close()
595
596 decisions := make([]*Decision, 0)
597 rows, err := getClosableDecisionsStmt.Queryx(struct{ Now time.Time }{time.Now().UTC()})
598 if err != nil {
599 logger.Println("Error fetching closable decisions", err)
600 return
601 }
602 defer rows.Close()
603 for rows.Next() {
604 decision := &Decision{}
605 if err = rows.StructScan(decision); err != nil {
606 logger.Println("Error scanning row", err)
607 return
608 }
609 decisions = append(decisions, decision)
610 }
611 rows.Close()
612
613 for _, decision := range decisions {
614 logger.Println("DEBUG found closable decision", decision)
615 if err = decision.Close(); err != nil {
616 logger.Printf("Error closing decision %s: %s\n", decision, err)
617 return
618 }
619 }
620
621 return
622 }
623
624 func GetNextPendingDecisionDue() (due *time.Time, err error) {
625 getNextPendingDecisionDueStmt, err := db.Preparex(sqlStatements[sqlGetNextPendingDecisionDue])
626 if err != nil {
627 logger.Println("Error preparing statement:", err)
628 return
629 }
630 defer getNextPendingDecisionDueStmt.Close()
631
632 row := getNextPendingDecisionDueStmt.QueryRow()
633
634 var dueTimestamp time.Time
635 if err = row.Scan(&dueTimestamp); err != nil {
636 if err == sql.ErrNoRows {
637 logger.Println("DEBUG No pending decisions")
638 return nil, nil
639 }
640 logger.Println("Error parsing result", err)
641 return
642 }
643 due = &dueTimestamp
644
645 return
646 }