From b6ad5d8ad327066b21bdb690f5a5017f6bed9740 Mon Sep 17 00:00:00 2001 From: Jan Dittberner Date: Thu, 20 Apr 2017 20:58:22 +0200 Subject: Implement reminder job --- models.go | 141 +++++++++++++++++++++++++++++++++++++------------------------- 1 file changed, 84 insertions(+), 57 deletions(-) (limited to 'models.go') 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 +} -- cgit v1.2.1