diff options
| -rw-r--r-- | database.php | 6 | ||||
| -rw-r--r-- | database.sql | 4 | ||||
| -rw-r--r-- | motions.php | 17 |
3 files changed, 20 insertions, 7 deletions
diff --git a/database.php b/database.php index 33a8130..3bba8c3 100644 --- a/database.php +++ b/database.php @@ -1,11 +1,13 @@ <?php class DB { - var $board = "cacert-board@lists.cacert.org"; + //var $board = "cacert-board@lists.cacert.org"; + var $board = "testsympa@lists.cacert.org"; function __construct() { $this->dbh = new PDO("sqlite:".dirname(__FILE__)."/database.sqlite"); $this->statement = array(); - $this->statement['list decisions'] = $this->dbh->prepare("SELECT decisions.id AS 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['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);"); + $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;"); $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();"); diff --git a/database.sql b/database.sql new file mode 100644 index 0000000..bf5b801 --- /dev/null +++ b/database.sql @@ -0,0 +1,4 @@ +CREATE TABLE decisions (id INTEGER PRIMARY KEY, proposed DATETIME, proponent INTEGER, title VARCHAR(255), content TEXT, quorum INTEGER, majority INTEGER, status INTEGER, due DATETIME, modified DATETIME, tag varchar(255)); +CREATE TABLE emails (voter INT4, address VARCHAR(255)); +CREATE TABLE voters (id INTEGER PRIMARY KEY, name VARCHAR(255), enabled INTEGER default 0); +CREATE TABLE votes (decision INT4, voter INT4, vote INT4, voted DATETIME, notes text default ''); diff --git a/motions.php b/motions.php index bbf1825..a4df212 100644 --- a/motions.php +++ b/motions.php @@ -18,8 +18,13 @@ <th>Actions</th> </tr> <?php - $stmt = $db->getStatement("list decisions"); - $stmt->execute(array($page)); + if ($_REQUEST['id']) { + $stmt = $db->getStatement("list decision"); + $stmt->execute(array($_REQUEST['id'])); + } else { + $stmt = $db->getStatement("list decisions"); + $stmt->execute(array($page)); + } $items = 0; while ($row = $stmt->fetch()) { $items++; @@ -34,7 +39,7 @@ ?> </td> <td> - <i><?php echo $row['tag'] ?></i><br/> + <i><a href="motions.php?id=<?php echo $row['tag'].'">'.$row['tag']; ?></a></i><br/> <b><?php echo $row['title']; ?></b><br/> <pre><?php echo $row['content']; ?></pre> <br/> @@ -52,10 +57,12 @@ while ($vrow = $vstmt->fetch()) { echo "<i>".$vrow['name']." ".$state[$vrow['vote']+1]."</i><br/>"; } + } else { + echo '<i><a href="motions.php?id='.$row['tag'].'&showvotes=1">Show Votes</a></i><br/>'; } ?> - </td><?php - ?><td class="actions"> + </td> + <td class="actions"> <?php if ($row['status'] == 0) { ?> |