ec501e942d3e8bb65c536cc335cad6b0827621b4
[cacert-boardvoting.git] / database.php
1 <?php
2 class DB {
3 //var $board = "cacert-board@lists.cacert.org";
4 var $board = "testsympa@lists.cacert.org";
5
6 function __construct() {
7 $this->dbh = new PDO("sqlite:".dirname(__FILE__)."/database.sqlite");
8 $this->statement = array();
9 $this->statement['list decisions'] = $this->dbh->prepare("SELECT decisions.id AS id, decisions.tag AS 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);");
10 $this->statement['list decision'] = $this->dbh->prepare("SELECT decisions.id AS id, decisions.tag AS 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.tag=:id ORDER BY proposed DESC;");
11 $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);");
12 $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;");
13 $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();");
14 $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");
15 $this->statement['get voter by id'] = $this->dbh->prepare("SELECT voters.id, voters.name FROM voters WHERE id=:id;");
16 $this->statement['get voters'] = $this->dbh->prepare("SELECT voters.id, voters.name FROM voters WHERE voters.enabled=1 ORDER BY name ASC;");
17 $this->statement['del vote'] = $this->dbh->prepare("DELETE FROM votes WHERE decision=:decision AND voter=:voter;");
18 $this->statement['do vote'] = $this->dbh->prepare("INSERT INTO votes (decision, voter, vote, voted, notes) VALUES (:decision, :voter, :vote, datetime('now','utc'), :notes);");
19 $this->statement['stats'] = $this->dbh->prepare("SELECT COUNT(*) AS voters FROM voters WHERE enabled=1;");
20 $this->statement['list votes'] = $this->dbh->prepare("SELECT voters.name AS name, votes.vote AS vote FROM voters,votes WHERE voters.id=votes.voter AND votes.decision=:id;");
21 $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'));");
22 $this->statement['post create'] = $this->dbh->prepare(" UPDATE decisions SET tag='m' || strftime('%Y%m%d','now') || '.' || id WHERE id=last_insert_rowid();");
23 $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;");
24 $this->statement['close decision'] = $this->dbh->prepare("UPDATE decisions SET status=:status, modified=datetime('now','utc') WHERE id=:decision");
25 }
26 function getStatement($name) {
27 return $this->statement[$name];
28 }
29 function closeVotes() {
30 $stmt = $this->getStatement("closed decisions");
31 $upd = $this->getStatement("close decision");
32 if ($stmt->execute()) {
33 while ($decision = $stmt->fetch()) {
34 $votes = $decision['ayes'] + $decision['nayes'] + $decision['abstains'];
35 if ($votes < $decision['quorum']) {
36 $decision['status'] = -1;
37 } else {
38 $votes = $decision['ayes'] + $decision['nayes'];
39 if (($decision['ayes'] / $votes) >= ($decision['majority'] / 100)) {
40 $decision['status'] = 1;
41 } else {
42 $decision['status'] = -1;
43 }
44 }
45 $upd->bindParam(":decision",$decision['id']);
46 $upd->bindParam(":status",$decision['status']);
47 $upd->execute();
48 $state = $decision['status']==1?"accepted":"declined";
49 $tag = $decision['tag'];
50 $title = $decision['title'];
51 $content = $decision['content'];
52 $quorum = $decision['quorum'];
53 $majority = $decision['majority'];
54 $ayes = $decision['ayes'];
55 $nayes = $decision['nayes'];
56 $abstains = $decision['abstains'];
57 $totalvotes = $decision['ayes']+$decision['nayes'];
58 if ($totalvotes <= 0) $percent = 0;
59 else $percent = $decision['ayes'] * 100 / $totalvotes;
60 $body = <<<BODY
61 Dear Board,
62
63 The motion with the identifier $tag has been $state.
64
65 Motion:
66 $title
67 $content
68
69 Votes:
70 Quorum: $quorum
71 Majority: $majority%
72
73 Ayes: $ayes
74 Nayes: $nayes
75 Abstentions: $abstains
76
77 Percentage: $percent%
78
79 Kind regards,
80 the voting system.
81
82 BODY;
83 $this->notify("Re: ".$decision['tag']." - ".$decision['title']." - finalised",$body,$decision['tag']);
84 }
85 }
86 }
87 function notify($subject,$body,$tag,$first=FALSE)
88 {
89 if ($first) {
90 $header = "Message-id: <".$tag.">\r\n";
91 } else {
92 $header = "References: <".$tag.">\r\nIn-reply-to: <".$tag.">\r\n";
93 }
94 mail($this->board,$subject,$body,$header."From: Voting System <returns@cacert.org>");
95 //mail("testsympa@lists.cacert.org",$subject,$body,"From: Voting System <returns@cacert.org>");
96 }
97 function auth()
98 {
99 $stmt = $this->getStatement("get voter");
100 $stmt->execute(array($_SERVER['REMOTE_USER']));
101 $user = $stmt->fetch();
102 if ($user) return $user;
103 if ($_SERVER['SSL_CLIENT_S_DN_EMAIL']) {
104 $stmt->execute(array($_SERVER['SSL_CLIENT_S_DN_EMAIL']));
105 $user = $stmt->fetch();
106 if ($user) return $user;
107 }
108 $d=0;
109 while ($email=$_SERVER["SSL_CLIENT_S_DN_EMAIL_$d"]) {
110 $stmt->execute(array($email));
111 $user = $stmt->fetch();
112 if ($user) return $user;
113 ++$d;
114 }
115 if (preg_match_all('/\/emailAddress=([^\/]*)/',$dn,$reg,PREG_SET_ORDER)) {
116 foreach ($reg as $emailarr) {
117 $stmt->execute(array($emailarr[1]));
118 $user = $stmt->fetch();
119 if ($user) return $user;
120 }
121 }
122 if ($_SERVER['SSL_CLIENT_CERT']) {
123 # subjectAltName unpresented by Apache http://httpd.apache.org/docs/trunk/mod/mod_ssl.html
124 # subjectAltName http://tools.ietf.org/html/rfc5280#section-4.2.1.6
125 # WARNING WARNING openssl_x509_parse is an unstable PHP API
126 $x509 = openssl_x509_parse($_SERVER['SSL_CLIENT_CERT']);
127 $subjectAltName = $x509['extensions']['subjectAltName']; // going off https://foaf.me/testSSL.php
128 #print_r(split("[, ]",$subjectAltName));
129 #print_r($x509);
130 #echo $subjectAltName;
131 if (preg_match_all('/email:([^, ]*)/',$subjectAltName,$reg,PREG_SET_ORDER)) {
132 foreach ($reg as $emailarr) {
133 $stmt->execute(array($emailarr[1]));
134 $user = $stmt->fetch();
135 if ($user) return $user;
136 }
137 }
138 }
139 return FALSE;
140 }
141 }
142 ?>