Implement reminder job

1 files changed, 84 insertions, 57 deletions

diff --git a/models.go b/models.go
index d27610f..5297cf2 100644
--- a/models.go
+++ b/models.go
@@ -24,96 +24,85 @@ const (
 	sqlUpdateDecisionStatus
 	sqlSelectClosableDecisions
 	sqlGetNextPendingDecisionDue
+	sqlGetReminderVoters
+	sqlFindUnvotedDecisionsForVoter
 )
 
 var sqlStatements = map[sqlKey]string{
 	sqlLoadDecisions: `
-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
+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`,
 	sqlLoadUnvotedDecisions: `
-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.status = 0 AND decisions.id NOT IN (
-  SELECT votes.decision
-  FROM votes
-  WHERE votes.voter = $1)
+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.status = 0 AND decisions.id NOT IN (SELECT votes.decision FROM votes WHERE votes.voter = $1)
 ORDER BY proposed DESC
 LIMIT 10 OFFSET 10 * $2;`,
 	sqlLoadDecisionByTag: `
-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;`,
+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;`,
 	sqlLoadDecisionById: `
-SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
- 	   decisions.title, decisions.content, decisions.votetype, decisions.status,
- 	   decisions.due, decisions.modified
-FROM decisions
-WHERE decisions.id=$1;`,
+SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed, decisions.title, decisions.content,
+       decisions.votetype, decisions.status, decisions.due, decisions.modified
+FROM   decisions
+WHERE  decisions.id=$1;`,
 	sqlLoadVoteCountsForDecision: `
-SELECT vote, COUNT(vote)
-FROM votes
-WHERE decision=$1 GROUP BY vote`,
+SELECT vote, COUNT(vote) FROM votes WHERE decision=$1 GROUP BY vote`,
 	sqlLoadVotesForDecision: `
 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`,
+FROM   votes
+JOIN   voters ON votes.voter=voters.id
+WHERE  decision=$1`,
 	sqlLoadEnabledVoterByEmail: `
 SELECT voters.id, voters.name, voters.enabled, voters.reminder
-FROM voters
-JOIN emails ON voters.id=emails.voter
-WHERE emails.address=$1 AND voters.enabled=1`,
+FROM   voters
+JOIN   emails ON voters.id=emails.voter
+WHERE  emails.address=$1 AND voters.enabled=1`,
 	sqlCountOlderThanDecision: `
 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`,
 	sqlCountOlderThanUnvotedDecision: `
-SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1
-   AND status=0
-   AND id NOT IN (SELECT decision FROM votes WHERE votes.voter=$2)`,
+SELECT COUNT(*) > 0 FROM decisions
+WHERE  proposed < $1 AND status=0 AND id NOT IN (SELECT decision FROM votes WHERE votes.voter=$2)`,
 	sqlCreateDecision: `
-INSERT INTO decisions (
-	proposed, proponent, title, content, votetype, status, due, modified,tag
-) VALUES (
-    :proposed, :proponent, :title, :content, :votetype, 0,
-    :due,
-    :proposed,
+INSERT INTO decisions (proposed, proponent, title, content, votetype, status, due, modified,tag)
+VALUES (
+    :proposed, :proponent, :title, :content, :votetype, 0, :due, :proposed,
     'm' || strftime('%Y%m%d', :proposed) || '.' || (
 	 	SELECT COUNT(*)+1 AS num
   		FROM decisions
-  		WHERE proposed
-  			BETWEEN date(:proposed) AND date(:proposed, '1 day')
+  		WHERE proposed BETWEEN date(:proposed) AND date(:proposed, '1 day')
     )
 )`,
 	sqlUpdateDecision: `
 UPDATE decisions
-SET proponent=:proponent, title=:title, content=:content,
-    votetype=:votetype, due=:due, modified=:modified
-WHERE id=:id`,
+SET    proponent=:proponent, title=:title, content=:content, votetype=:votetype, due=:due, modified=:modified
+WHERE  id=:id`,
 	sqlUpdateDecisionStatus: `
-UPDATE decisions
-SET status=:status, modified=:modified WHERE id=:id
-`,
+UPDATE decisions SET status=:status, modified=:modified WHERE id=:id`,
 	sqlSelectClosableDecisions: `
-SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
-       decisions.title, decisions.content, decisions.votetype, decisions.status,
-       decisions.due, decisions.modified
+SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed, decisions.title, decisions.content,
+       decisions.votetype, decisions.status, decisions.due, decisions.modified
 FROM   decisions
 WHERE  decisions.status=0 AND :now > due`,
 	sqlGetNextPendingDecisionDue: `
 SELECT due FROM decisions WHERE status=0 ORDER BY due LIMIT 1`,
+	sqlGetReminderVoters: `
+SELECT id, name, reminder FROM voters WHERE enabled=1 AND reminder!='' AND reminder IS NOT NULL`,
+	sqlFindUnvotedDecisionsForVoter: `
+SELECT tag, title, votetype, due
+FROM   decisions
+WHERE  status = 0 AND id NOT IN (SELECT decision FROM votes WHERE voter = $1)
+ORDER BY due ASC
+`,
 }
 
 var db *sqlx.DB
@@ -644,3 +633,41 @@ func GetNextPendingDecisionDue() (due *time.Time, err error) {
 
 	return
 }
+
+func GetReminderVoters() (voters *[]Voter, err error) {
+	getReminderVotersStmt, err := db.Preparex(sqlStatements[sqlGetReminderVoters])
+	if err != nil {
+		logger.Println("Error preparing statement:", err)
+		return
+	}
+	defer getReminderVotersStmt.Close()
+
+	voterSlice := make([]Voter, 0)
+
+	if err = getReminderVotersStmt.Select(&voterSlice); err != nil {
+		logger.Println("Error getting voters:", err)
+		return
+	}
+	voters = &voterSlice
+
+	return
+}
+
+func FindUnvotedDecisionsForVoter(voter *Voter) (decisions *[]Decision, err error) {
+	findUnvotedDecisionsForVoterStmt, err := db.Preparex(sqlStatements[sqlFindUnvotedDecisionsForVoter])
+	if err != nil {
+		logger.Println("Error preparing statement:", err)
+		return
+	}
+	defer findUnvotedDecisionsForVoterStmt.Close()
+
+	decisionsSlice := make([]Decision, 0)
+
+	if err = findUnvotedDecisionsForVoterStmt.Select(&decisionsSlice, voter.Id); err != nil {
+		logger.Println("Error getting unvoted decisions:", err)
+		return
+	}
+	decisions = &decisionsSlice
+
+	return
+}