add empty SQL structure. Add links to single entities
authorcommunity.cacert.org <community.cacert.org@d4452222-2f33-11de-9270-010000000000>
Wed, 27 May 2009 05:39:41 +0000 (05:39 +0000)
committercommunity.cacert.org <community.cacert.org@d4452222-2f33-11de-9270-010000000000>
Wed, 27 May 2009 05:39:41 +0000 (05:39 +0000)
git-svn-id: http://svn.cacert.cl/Software/Voting/vote@41 d4452222-2f33-11de-9270-010000000000

database.php
database.sql [new file with mode: 0644]
motions.php

index 33a8130..3bba8c3 100644 (file)
@@ -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 (file)
index 0000000..bf5b801
--- /dev/null
@@ -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 '');
index bbf1825..a4df212 100644 (file)
                                <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/>
                                                        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) {
                                                                ?>