summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorcommunity.cacert.org <community.cacert.org@d4452222-2f33-11de-9270-010000000000>2009-05-27 05:39:41 +0000
committercommunity.cacert.org <community.cacert.org@d4452222-2f33-11de-9270-010000000000>2009-05-27 05:39:41 +0000
commit4ce4fa2e05b3a1a0dd56996ef5b540196c96760a (patch)
tree7bf639cf62e1e9023bca7dd652d2ab109bf42eb3
parent2877a513fed891d90878f0f672b67f2582ab48e8 (diff)
downloadcacert-boardvoting-4ce4fa2e05b3a1a0dd56996ef5b540196c96760a.tar.gz
cacert-boardvoting-4ce4fa2e05b3a1a0dd56996ef5b540196c96760a.tar.xz
cacert-boardvoting-4ce4fa2e05b3a1a0dd56996ef5b540196c96760a.zip
add empty SQL structure. Add links to single entities
git-svn-id: http://svn.cacert.cl/Software/Voting/vote@41 d4452222-2f33-11de-9270-010000000000
-rw-r--r--database.php6
-rw-r--r--database.sql4
-rw-r--r--motions.php17
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) {
?>