diff options
Diffstat (limited to 'database.php')
| -rw-r--r-- | database.php | 82 |
1 files changed, 82 insertions, 0 deletions
diff --git a/database.php b/database.php new file mode 100644 index 0000000..be20eb0 --- /dev/null +++ b/database.php @@ -0,0 +1,82 @@ +<?php + $board = "cacert-board@lists.cacert.org"; + class DB { + function __construct() { + $this->dbh = new PDO("sqlite:".dirname(__FILE__)."/database.sqlite"); + $this->statement = array(); + $this->statement['list decisions'] = $this->dbh->prepare("SELECT decisions.id, decisions.tag, voters.name AS proposer, decisions.proposed, decisions.title, decisions.content, decisions.quorum, decisions.majority, decisions.status, decisions.due, decisions.modified, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=1) AS ayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=-1) AS nayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=0) AS abstains FROM decisions, voters WHERE decisions.proponent=voters.id ORDER BY proposed DESC LIMIT 10 OFFSET 10 * (:page - 1);"); + $this->statement['closed decisions'] = $this->dbh->prepare("SELECT decisions.id, decisions.tag, voters.name AS proposer, decisions.proposed, decisions.title, decisions.content, decisions.quorum, decisions.majority, decisions.status, decisions.due, decisions.modified, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=1) AS ayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=-1) AS nayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=0) AS abstains FROM decisions, voters WHERE decisions.proponent=voters.id AND decisions.status=0 AND datetime('now','utc') > datetime(due);"); + $this->statement['get decision'] = $this->dbh->prepare("SELECT decisions.id, decisions.tag, decisions.proponent, voters.name AS proposer, decisions.proposed, decisions.title, decisions.content, decisions.quorum, decisions.majority, decisions.status, decisions.due, decisions.modified, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=1) AS ayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=-1) AS nayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=0) AS abstains FROM decisions, voters WHERE decisions.proponent=voters.id AND decisions.id=:decision;"); + $this->statement['get new decision'] = $this->dbh->prepare("SELECT decisions.id, decisions.tag, decisions.proponent, voters.name AS proposer, decisions.proposed, decisions.title, decisions.content, decisions.quorum, decisions.majority, decisions.status, decisions.due, decisions.modified, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=1) AS ayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=-1) AS nayes, (SELECT COUNT(*) FROM votes WHERE decision=decisions.id AND vote=0) AS abstains FROM decisions, voters WHERE decisions.proponent=voters.id AND decisions.id=last_insert_rowid();"); + $this->statement['get voter'] = $this->dbh->prepare("SELECT voters.id, voters.name FROM voters, emails WHERE voters.id=emails.voter AND emails.address=? AND voters.enabled=1"); + $this->statement['get voter by id'] = $this->dbh->prepare("SELECT voters.id, voters.name FROM voters WHERE id=:id;"); + $this->statement['get voters'] = $this->dbh->prepare("SELECT voters.id, voters.name FROM voters WHERE voters.enabled=1 ORDER BY name ASC;"); + $this->statement['del vote'] = $this->dbh->prepare("DELETE FROM votes WHERE decision=:decision AND voter=:voter;"); + $this->statement['do vote'] = $this->dbh->prepare("INSERT INTO votes (decision, voter, vote, voted, notes) VALUES (:decision, :voter, :vote, datetime('now','utc'), :notes);"); + $this->statement['stats'] = $this->dbh->prepare("SELECT (SELECT COUNT(*) FROM voters WHERE enabled=1) AS voters;"); + $this->statement['create decision'] = $this->dbh->prepare("INSERT INTO decisions (proposed, proponent, title, content, quorum, majority, status, due, modified) VALUES (datetime('now','utc'), :proponent, :title, :content, :quorum, :majority, 0, datetime('now','utc', :due), datetime('now','utc'));"); + $this->statement['post create'] = $this->dbh->prepare(" UPDATE decisions SET tag='m' || strftime('%Y%m%d','now') || '.' || id WHERE id=last_insert_rowid();"); + $this->statement['update decision'] = $this->dbh->prepare("UPDATE decisions SET proposed=datetime('now','utc'), proponent=:proponent, title=:title, content=:content, quorum=:quorum, majority=:majority, status=0, due=datetime('now','utc',:due), modified=datetime('now','utc') WHERE id=:id;"); + $this->statement['close decision'] = $this->dbh->prepare("UPDATE decisions SET status=:status, modified=datetime('now','utc') WHERE id=:decision"); + } + function getStatement($name) { + return $this->statement[$name]; + } + function closeVotes() { + $stmt = $this->getStatement("closed decisions"); + $upd = $this->getStatement("close decision"); + if ($stmt->execute()) { + while ($decision = $stmt->fetch()) { + $votes = $decision['ayes'] + $decision['nayes'] + $decision['abstains']; + if ($votes < $decision['quorum']) { + $decision['status'] = -1; + } else { + $votes = $decision['ayes'] + $decision['nayes']; + if (($decision['ayes'] / $votes) >= ($decision['majority'] / 100)) { + $decision['status'] = 1; + } else { + $decision['status'] = -1; + } + } + $upd->bindParam(":decision",$decision['id']); + $upd->bindParam(":status",$decision['status']); + $upd->execute(); + $state = $decision['status']==1?"accepted":"declined"; + $tag = $decision['tag']; + $title = $decision['title']; + $content = $decision['content']; + $quorum = $decision['quorum']; + $majority = $decision['majority']; + $ayes = $decision['ayes']; + $nayes = $decision['nayes']; + $abstains = $decision['abstains']; + $percent = $decision['ayes'] * 100 / $decision['ayes']+$decision['nayes']; + $body = <<<BODY +Dear Board, + +The motion with the identifier $tag has been $state. + +Motion: + $title + $content + +Votes: + Quorum: $quorum + Majority: $majority% + + Ayes: $ayes + Nayes: $nayes + Abstentions: $abstains + + Percentage: $percent% + +Kind regards, +the voting system. + +BODY; + mail($board,"Re: ".$decision['tag']." - ".$decision['title'],$body); + } + } + } + } +?>
\ No newline at end of file |