a522bd8c0fa85fdba6958abd524cb35503ac9bc7
[cacert-boardvoting.git] / database.php
1 <?php
2 class DB {
3 var $board = "cacert-board@lists.cacert.org";
4
5 function __construct() {
6 $this->dbh = new PDO("sqlite:".dirname(__FILE__)."/database.sqlite");
7 $this->statement = array();
8 $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);");
9 $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);");
10 $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;");
11 $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();");
12 $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");
13 $this->statement['get voter by id'] = $this->dbh->prepare("SELECT voters.id, voters.name FROM voters WHERE id=:id;");
14 $this->statement['get voters'] = $this->dbh->prepare("SELECT voters.id, voters.name FROM voters WHERE voters.enabled=1 ORDER BY name ASC;");
15 $this->statement['del vote'] = $this->dbh->prepare("DELETE FROM votes WHERE decision=:decision AND voter=:voter;");
16 $this->statement['do vote'] = $this->dbh->prepare("INSERT INTO votes (decision, voter, vote, voted, notes) VALUES (:decision, :voter, :vote, datetime('now','utc'), :notes);");
17 $this->statement['stats'] = $this->dbh->prepare("SELECT COUNT(*) AS voters FROM voters WHERE enabled=1;");
18 $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'));");
19 $this->statement['post create'] = $this->dbh->prepare(" UPDATE decisions SET tag='m' || strftime('%Y%m%d','now') || '.' || id WHERE id=last_insert_rowid();");
20 $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;");
21 $this->statement['close decision'] = $this->dbh->prepare("UPDATE decisions SET status=:status, modified=datetime('now','utc') WHERE id=:decision");
22 }
23 function getStatement($name) {
24 return $this->statement[$name];
25 }
26 function closeVotes() {
27 $stmt = $this->getStatement("closed decisions");
28 $upd = $this->getStatement("close decision");
29 if ($stmt->execute()) {
30 while ($decision = $stmt->fetch()) {
31 $votes = $decision['ayes'] + $decision['nayes'] + $decision['abstains'];
32 if ($votes < $decision['quorum']) {
33 $decision['status'] = -1;
34 } else {
35 $votes = $decision['ayes'] + $decision['nayes'];
36 if (($decision['ayes'] / $votes) >= ($decision['majority'] / 100)) {
37 $decision['status'] = 1;
38 } else {
39 $decision['status'] = -1;
40 }
41 }
42 $upd->bindParam(":decision",$decision['id']);
43 $upd->bindParam(":status",$decision['status']);
44 $upd->execute();
45 $state = $decision['status']==1?"accepted":"declined";
46 $tag = $decision['tag'];
47 $title = $decision['title'];
48 $content = $decision['content'];
49 $quorum = $decision['quorum'];
50 $majority = $decision['majority'];
51 $ayes = $decision['ayes'];
52 $nayes = $decision['nayes'];
53 $abstains = $decision['abstains'];
54 $totalvotes = $decision['ayes']+$decision['nayes'];
55 if ($totalvotes <= 0) $percent = 0;
56 else $percent = $decision['ayes'] * 100 / $totalvotes;
57 $body = <<<BODY
58 Dear Board,
59
60 The motion with the identifier $tag has been $state.
61
62 Motion:
63 $title
64 $content
65
66 Votes:
67 Quorum: $quorum
68 Majority: $majority%
69
70 Ayes: $ayes
71 Nayes: $nayes
72 Abstentions: $abstains
73
74 Percentage: $percent%
75
76 Kind regards,
77 the voting system.
78
79 BODY;
80 $this->notify("Re: ".$decision['tag']." - ".$decision['title'],$body);
81 }
82 }
83 }
84 function notify($subject,$body)
85 {
86 mail($this->board,$subject,$body,"From: Voting System <returns@cacert.org>");
87 //mail("testsympa@lists.cacert.org",$subject,$body,"From: Voting System <returns@cacert.org>");
88 }
89 }
90 ?>