410ed3ee2c0d12a8be53d530c57fc130c605130e
[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, voters.enabled, voters.reminder
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 sqlCreateDecision = `
55 INSERT INTO decisions (
56 proposed, proponent, title, content, votetype, status, due, modified,tag
57 ) VALUES (
58 datetime('now','utc'), :proponent, :title, :content, :votetype, 0,
59 :due,
60 datetime('now','utc'),
61 'm' || strftime('%Y%m%d','now') || '.' || (
62 SELECT COUNT(*)+1 AS num
63 FROM decisions
64 WHERE proposed BETWEEN date('now') AND date('now','1 day')
65 )
66 )
67 `
68 )
69
70 var db *sqlx.DB
71
72 type VoteType uint8
73 type VoteStatus int8
74
75 type Decision struct {
76 Id int
77 Proposed time.Time
78 ProponentId int `db:"proponent"`
79 Title string
80 Content string
81 Quorum int
82 Majority int
83 Status VoteStatus
84 Due time.Time
85 Modified time.Time
86 Tag string
87 VoteType VoteType
88 }
89
90 type Email struct {
91 VoterId int `db:"voter"`
92 Address string
93 }
94
95 type Voter struct {
96 Id int
97 Name string
98 Enabled bool
99 Reminder string // reminder email address
100 }
101
102 type VoteChoice int
103
104 type Vote struct {
105 DecisionId int `db:"decision"`
106 VoterId int `db:"voter"`
107 Vote VoteChoice
108 Voted time.Time
109 Notes string
110 }
111
112 const (
113 voteAye = 1
114 voteNaye = -1
115 voteAbstain = 0
116 )
117
118 const (
119 voteTypeMotion = 0
120 voteTypeVeto = 1
121 )
122
123 func (v VoteType) String() string {
124 switch v {
125 case voteTypeMotion:
126 return "motion"
127 case voteTypeVeto:
128 return "veto"
129 default:
130 return "unknown"
131 }
132 }
133
134 func (v VoteType) QuorumAndMajority() (int, int) {
135 switch v {
136 case voteTypeMotion:
137 return 3, 50
138 default:
139 return 1, 99
140 }
141 }
142
143 func (v VoteChoice) String() string {
144 switch v {
145 case voteAye:
146 return "aye"
147 case voteNaye:
148 return "naye"
149 case voteAbstain:
150 return "abstain"
151 default:
152 return "unknown"
153 }
154 }
155
156 const (
157 voteStatusDeclined = -1
158 voteStatusPending = 0
159 voteStatusApproved = 1
160 voteStatusWithdrawn = -2
161 )
162
163 func (v VoteStatus) String() string {
164 switch v {
165 case voteStatusDeclined:
166 return "declined"
167 case voteStatusPending:
168 return "pending"
169 case voteStatusApproved:
170 return "approved"
171 case voteStatusWithdrawn:
172 return "withdrawn"
173 default:
174 return "unknown"
175 }
176 }
177
178 type VoteSums struct {
179 Ayes int
180 Nayes int
181 Abstains int
182 }
183
184 func (v *VoteSums) VoteCount() int {
185 return v.Ayes + v.Nayes + v.Abstains
186 }
187
188 type VoteForDisplay struct {
189 Vote
190 Name string
191 }
192
193 type DecisionForDisplay struct {
194 Decision
195 Proposer string `db:"proposer"`
196 *VoteSums
197 Votes []VoteForDisplay
198 }
199
200 func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
201 decisionStmt, err := db.Preparex(sqlGetDecision)
202 if err != nil {
203 logger.Println("Error preparing statement:", err)
204 return
205 }
206 defer decisionStmt.Close()
207
208 decision = &DecisionForDisplay{}
209 if err = decisionStmt.Get(decision, tag); err != nil {
210 if err == sql.ErrNoRows {
211 decision = nil
212 err = nil
213 } else {
214 logger.Printf("Error getting motion %s: %v\n", tag, err)
215 }
216 }
217 decision.VoteSums, err = decision.Decision.VoteSums()
218 return
219 }
220
221 // FindDecisionsForDisplayOnPage loads a set of decisions from the database.
222 //
223 // This function uses OFFSET for pagination which is not a good idea for larger data sets.
224 //
225 // TODO: migrate to timestamp base pagination
226 func FindDecisionsForDisplayOnPage(page int64) (decisions []*DecisionForDisplay, err error) {
227 decisionsStmt, err := db.Preparex(sqlGetDecisions)
228 if err != nil {
229 logger.Println("Error preparing statement:", err)
230 return
231 }
232 defer decisionsStmt.Close()
233
234 rows, err := decisionsStmt.Queryx(page - 1)
235 if err != nil {
236 logger.Printf("Error loading motions for page %d: %v\n", page, err)
237 return
238 }
239 defer rows.Close()
240
241 for rows.Next() {
242 var d DecisionForDisplay
243 if err = rows.StructScan(&d); err != nil {
244 logger.Printf("Error loading motions for page %d: %v\n", page, err)
245 return
246 }
247 d.VoteSums, err = d.Decision.VoteSums()
248 if err != nil {
249 return
250 }
251 decisions = append(decisions, &d)
252 }
253 return
254 }
255
256 func FindVotersUnvotedDecisionsForDisplayOnPage(page int64, voter *Voter) (decisions []*DecisionForDisplay, err error) {
257 decisionsStmt, err := db.Preparex(sqlListUnvotedDecisions)
258 if err != nil {
259 logger.Println("Error preparing statement:", err)
260 return
261 }
262 defer decisionsStmt.Close()
263
264 rows, err := decisionsStmt.Queryx(page-1, voter.Id)
265 if err != nil {
266 logger.Printf("Error loading motions for page %d: %v\n", page, err)
267 return
268 }
269 defer rows.Close()
270
271 for rows.Next() {
272 var d DecisionForDisplay
273 if err = rows.StructScan(&d); err != nil {
274 logger.Printf("Error loading motions for page %d: %v\n", page, err)
275 return
276 }
277 d.VoteSums, err = d.Decision.VoteSums()
278 if err != nil {
279 return
280 }
281 decisions = append(decisions, &d)
282 }
283 return
284 }
285
286 func (d *Decision) VoteSums() (sums *VoteSums, err error) {
287 votesStmt, err := db.Preparex(sqlVoteCount)
288 if err != nil {
289 logger.Println("Error preparing statement:", err)
290 return
291 }
292 defer votesStmt.Close()
293
294 voteRows, err := votesStmt.Queryx(d.Id)
295 if err != nil {
296 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
297 return
298 }
299 defer voteRows.Close()
300
301 sums = &VoteSums{}
302 for voteRows.Next() {
303 var vote VoteChoice
304 var count int
305 if err = voteRows.Scan(&vote, &count); err != nil {
306 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
307 return
308 }
309 switch vote {
310 case voteAye:
311 sums.Ayes = count
312 case voteNaye:
313 sums.Nayes = count
314 case voteAbstain:
315 sums.Abstains = count
316 }
317 }
318 return
319 }
320
321 func (d *DecisionForDisplay) LoadVotes() (err error) {
322 votesStmt, err := db.Preparex(sqlGetVotesForDecision)
323 if err != nil {
324 logger.Println("Error preparing statement:", err)
325 return
326 }
327 defer votesStmt.Close()
328 err = votesStmt.Select(&d.Votes, d.Id)
329 if err != nil {
330 logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
331 }
332 return
333 }
334
335 func (d *Decision) OlderExists() (result bool, err error) {
336 olderStmt, err := db.Preparex(sqlCountOlderThanDecision)
337 if err != nil {
338 logger.Println("Error preparing statement:", err)
339 return
340 }
341 defer olderStmt.Close()
342
343 if err := olderStmt.Get(&result, d.Proposed); err != nil {
344 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
345 }
346 return
347 }
348
349 func (d *Decision) Save() (err error) {
350 insertDecisionStmt, err := db.PrepareNamed(sqlCreateDecision)
351 if err != nil {
352 logger.Println("Error preparing statement:", err)
353 return
354 }
355 defer insertDecisionStmt.Close()
356
357 result, err := insertDecisionStmt.Exec(d)
358 if err != nil {
359 logger.Println("Error creating motion:", err)
360 return
361 }
362 logger.Println(result)
363 // TODO: implement fetch last id from result
364 // TODO: load decision from DB
365 return
366 }
367
368 func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
369 findVoterStmt, err := db.Preparex(sqlGetVoter)
370 if err != nil {
371 logger.Println("Error preparing statement:", err)
372 return
373 }
374 defer findVoterStmt.Close()
375
376 voter = &Voter{}
377 if err = findVoterStmt.Get(voter, emailAddress); err != nil {
378 if err != sql.ErrNoRows {
379 logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
380 } else {
381 err = nil
382 voter = nil
383 }
384 }
385 return
386 }