summaryrefslogtreecommitdiff
path: root/database.php
diff options
context:
space:
mode:
authorPhilipp Dunkel <Philipp Dunkel@d4452222-2f33-11de-9270-010000000000>2009-05-21 11:20:15 +0000
committerPhilipp Dunkel <Philipp Dunkel@d4452222-2f33-11de-9270-010000000000>2009-05-21 11:20:15 +0000
commit99ee0407525ab98e56479f183af573c3a3451a69 (patch)
treede4748eefcb13504c4c5e6d9cc29bdce17a7cbd4 /database.php
downloadcacert-boardvoting-99ee0407525ab98e56479f183af573c3a3451a69.tar.gz
cacert-boardvoting-99ee0407525ab98e56479f183af573c3a3451a69.tar.xz
cacert-boardvoting-99ee0407525ab98e56479f183af573c3a3451a69.zip
Voting Tool
git-svn-id: http://svn.cacert.cl/Software/Voting/vote@34 d4452222-2f33-11de-9270-010000000000
Diffstat (limited to 'database.php')
-rw-r--r--database.php82
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