AUR Voting Application

Added support for TU voting through AUR

Signed-off-by: Callan Barrett <wizzomafizzo@gmail.com>

3 files changed, 531 insertions, 0 deletions

diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql
index 11ad5a56..d48917c4 100644
--- a/support/schema/aur-schema.sql
+++ b/support/schema/aur-schema.sql
@@ -210,3 +210,24 @@ CREATE TABLE CommentNotify (
 	FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
 );
 
+-- Vote information
+--
+CREATE TABLE IF NOT EXISTS TU_VoteInfo (
+  ID int(10) unsigned NOT NULL auto_increment,
+  Agenda text collate latin1_general_ci NOT NULL,
+  User char(32) collate latin1_general_ci NOT NULL,
+  Submitted bigint(20) unsigned NOT NULL,
+  SubmitterID int(10) unsigned NOT NULL,
+  Yes tinyint(3) unsigned NOT NULL default '0',
+  No tinyint(3) unsigned NOT NULL default '0',
+  Abstain tinyint(3) unsigned NOT NULL default '0',
+  PRIMARY KEY  (ID)
+)
+
+-- Individual vote records
+--
+CREATE TABLE IF NOT EXISTS TU_Votes (
+  VoteID int(10) unsigned NOT NULL,
+  UserID int(10) unsigned NOT NULL
+)
+
diff --git a/web/html/addvote.php b/web/html/addvote.php
new file mode 100644
index 00000000..91a06583
--- /dev/null
+++ b/web/html/addvote.php
@@ -0,0 +1,79 @@
+<?php
+
+set_include_path(get_include_path() . PATH_SEPARATOR . '../lib' . PATH_SEPARATOR . '../lang');
+
+include("pkgfuncs_po.inc");
+include("aur.inc");
+set_lang();
+check_sid();
+html_header();
+
+if (isset($_COOKIE["AURSID"])) {
+  $atype = account_from_sid($_COOKIE["AURSID"]);
+} else {
+  $atype = "";
+}
+
+if ($atype == "Trusted User" OR $atype == "Developer") {
+	$dbh = db_connect();
+	
+	if (!empty($_POST['addVote'])) {
+		$aweek = 60*60*24*7;
+		$error = "";
+		
+		if (!empty($_REQUEST['user'])) {
+			$qcheck = "SELECT * FROM Users WHERE Username = '" . mysql_real_escape_string($_REQUEST['user']) . "'";
+			$check = mysql_num_rows(db_query($qcheck, $dbh));
+
+			if ($check == 0) {
+				$error.= "<div style='color: red; font-weight: bold'>Username does not exist.</div>";
+			} else {
+				$qcheck = "SELECT * FROM TU_VoteInfo WHERE User = '" . mysql_real_escape_string($_REQUEST['user']) . "'";
+				$qcheck.= " AND Submitted + " . $aweek . " > UNIX_TIMESTAMP()";
+				$check = mysql_num_rows(db_query($qcheck, $dbh));
+
+				if ($check != 0) {
+					$error.= "<div style='color: red; font-weight: bold'>" . mysql_real_escape_string($_REQUEST['user']) . " already has proposal running for them.</div>";
+				}
+			}
+		}
+
+		if (empty($_REQUEST['agenda'])) {
+			$error.= "<div style='color: red; font-weight: bold'>Proposal cannot be empty.</div>";
+		}
+	}
+
+	if (!empty($_POST['addVote']) && empty($error)) {
+		$q = "INSERT INTO TU_VoteInfo (Agenda, User, Submitted, SubmitterID) VALUES ";
+		$q.= "('" . mysql_real_escape_string($_REQUEST['agenda']) . "', ";
+		$q.= "'" . mysql_real_escape_string($_REQUEST['user']) . "', ";
+		$q.= "UNIX_TIMESTAMP(), " . uid_from_sid($_COOKIE["AURSID"]) . ")";
+
+		db_query($q, $dbh);
+		print "<p>New proposal submitted.</p>\n";
+	} else {
+?>
+<p>Submit a proposal to vote on.</p>
+<?php if (!empty($error)) { print $error . "<br />"; } ?>
+<form action='addvote.php' method='post'>
+<b>Applicant/TU:</b>
+<input type='text' name='user' value='<?php if (!empty($_POST['user'])) { print htmlentities($_POST['user'], ENT_QUOTES); } ?>'>
+(empty if not applicable)
+<br />
+<b>Proposal:</b><br />
+<textarea name='agenda' rows='10' cols='50'><?php if (!empty($_POST['agenda'])) { print htmlentities($_POST['agenda']); } ?></textarea><br />
+<input type='hidden' name='addVote' value='1'>
+<input type='submit' class='button' value='Submit'>
+</form>
+<br />
+<?php
+	}
+	print "<a href='tu.php'>Back</a>";
+} else {
+	print "You are not allowed to access this area.\n";
+}
+
+html_footer(AUR_VERSION);
+# vim: ts=2 sw=2 noet ft=php
+
+?>
diff --git a/web/html/tu.php b/web/html/tu.php
new file mode 100644
index 00000000..9ed00bc3
--- /dev/null
+++ b/web/html/tu.php
@@ -0,0 +1,431 @@
+<?php
+
+set_include_path(get_include_path() . PATH_SEPARATOR . '../lib' . PATH_SEPARATOR . '../lang');
+
+include("pkgfuncs_po.inc");
+include("aur.inc");
+set_lang();
+check_sid();
+html_header();
+
+# get login privileges
+#
+if (isset($_COOKIE["AURSID"])) {
+  # Only logged in users can do stuff
+  #
+  $atype = account_from_sid($_COOKIE["AURSID"]);
+} else {
+  $atype = "";
+}
+
+if ($atype == "Trusted User" OR $atype == "Developer") {
+	# Show the TU interface
+	# 
+
+	# Temp value for results per page
+	$pp = 5;
+	# This needs to be changed for variable length votes I guess, TODO
+	$aweek = 60*60*24*7;
+
+	if (isset($_REQUEST['id'])) {
+		# Show application details
+		# depending on action and time frame will show either
+		# sponsor button, comments and vote buttons
+		#
+
+		if (intval($_REQUEST['id'])) {
+
+			$q = "SELECT * FROM TU_VoteInfo ";
+			$q.= "WHERE ID = " . $_REQUEST['id'];
+
+			$dbh = db_connect();
+			$results = db_query($q, $dbh);
+			$row = mysql_fetch_assoc($results);
+			
+			if (empty($row)) {
+				print "Could not retrieve proposal details.\n";
+			} else {
+				# Print out application details, thanks again AUR
+				#
+
+				$isrunning = (($row['Submitted'] + $aweek) > time()) ? 1 : 0;
+				
+				$qvoted = "SELECT * FROM TU_Votes WHERE ";
+				$qvoted.= "VoteID = " . $row['ID'] . " AND ";
+				$qvoted.= "UserID = " . uid_from_sid($_COOKIE["AURSID"]);
+				$hasvoted = mysql_num_rows(db_query($qvoted, $dbh));
+
+				# Can this person vote?
+				#
+				$canvote = 1; // we assume they can
+				$errorvote = ""; // error message to give
+				if ($isrunning == 0) {
+					$canvote = 0;
+					$errorvote = "Voting is closed for this proposal.";
+				} else if ($row['User'] == username_from_sid($_COOKIE["AURSID"])) {
+					$canvote = 0;
+					$errorvote = "You cannot vote in an proposal regarding you.";
+				} else if ($hasvoted != 0) {
+					$canvote = 0;
+					$errorvote = "You've already voted in this proposal.";
+				}
+
+				# have to put this here so results are correct immediately after voting	
+				if ($canvote == 1) {
+					if (isset($_POST['doVote'])) {
+						if (isset($_POST['voteYes'])) {
+							$myvote = "Yes";
+						} else if (isset($_POST['voteNo'])) {
+							$myvote = "No";
+						} else if (isset($_POST['voteAbstain'])) {
+							$myvote = "Abstain";
+						}
+
+						$qvote = "UPDATE TU_VoteInfo SET " . $myvote . " = " . ($row[$myvote] + 1) . " WHERE ID = " . $row['ID'];
+						db_query($qvote, $dbh);
+						$qvote = "INSERT INTO TU_Votes (VoteID, UserID) VALUES (" . $row['ID'] . ", " . uid_from_sid($_COOKIE["AURSID"]) . ")";
+						db_query($qvote, $dbh);
+
+						# Can't vote anymore
+						#
+						$canvote = 0;
+						$errorvote = "You've already voted for this proposal.";
+						# Update if they voted
+						$hasvoted = mysql_num_rows(db_query($qvoted, $dbh));
+						
+						$results = db_query($q, $dbh);
+						$row = mysql_fetch_assoc($results);
+					}
+				}
+
+				echo "<div class=\"pgbox\">\n";
+				echo "  <div class=\"pgboxtitle\"><span class=\"f3\">Proposal Details</span></div>\n";
+				echo "  <div class=\"pgboxbody\">\n";
+
+				if ($isrunning == 1) {
+					print "<div style='text-align: center; font-weight: bold; color: red'>This vote is still running.</div>";
+					print "<br />";
+				}
+
+				print "User: <b>";
+				
+				if (!empty($row['User'])) {
+					print "<a href='packages.php?K=" . $row['User'] . "&SeB=m'>" . $row['User'] . "</a>";
+				} else {
+					print "N/A";
+				}	
+
+				print "</b><br />\n";
+				
+				print "Submitted: <b>" . gmdate("r", $row['Submitted']) . "</b> by ";
+				print "<b>" . username_from_id($row['SubmitterID']) . "</b><br />\n";
+				print "<br />\n";
+
+				$row['Agenda'] = htmlentities($row['Agenda']);
+				# str_replace seems better than <pre> because it still maintains word wrapping
+				print str_replace("\n", "<br />\n", $row['Agenda']);
+
+				print "<br />\n";
+				print "<br />\n";
+				
+				print "<center>\n";
+				print "<table cellspacing='3' class='boxSoft' style='width: 50%'>\n";
+				print "</tr>\n";
+				print "<tr>\n";
+				print "  <td class='boxSoft'>\n";
+				print "<table width='100%' cellspacing='0' cellpadding='2'>\n";
+
+				print "<tr>\n";
+				print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+				print " bottom'><span class='f2'>";
+				print "Yes";
+				print "</span></th>\n";
+				print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+				print " bottom'><span class='f2'>";
+				print "No";
+				print "</span></th>\n";
+				print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+				print " bottom'><span class='f2'>";
+				print "Abstain";
+				print "</span></th>\n";
+				print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+				print " bottom'><span class='f2'>";
+				print "Total";
+				print "</span></th>\n";
+				print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+				print " bottom'><span class='f2'>";
+				print "Voted?";
+				print "</span></th>\n";
+				print "</tr>\n";
+
+				$c = "data1";
+
+				print "<tr>\n";
+				print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+				print $row['Yes'];
+				print "</span></span></td>\n";
+				print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+				print $row['No'];
+				print "</span></span></td>\n";
+				print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+				print $row['Abstain'];
+				print "</span></span></td>\n";
+				print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+				print ($row['Yes'] + $row['No'] + $row['Abstain']);
+				print "</span></span></td>\n";
+				print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+
+				if ($hasvoted == 0) {
+					print "<span style='color: red; font-weight: bold'>No</span>";
+				} else {
+					print "<span style='color: green; font-weight: bold'>Yes</span>";
+				}
+
+				print "</span></span></td>\n";
+				print "</tr>\n";
+
+				print "</table>\n";
+				print "</table>\n";
+
+				echo "</div></div>\n";
+
+				# Actions, vote buttons
+				#
+				print "<br />\n";
+				print "<div class='pgbox'>\n";
+				print "<div class='pgboxtitle'><span class='f3'>Vote Actions</span></div>\n";
+				print "<div class='pgboxbody'>\n";
+
+				if ($canvote == 1) {
+					print "<center><form action='tu.php?id=" . $row['ID'] . "' method='post'>\n";
+					print "<input type='submit' class='button' name='voteYes' value='Yes'>\n";
+					print "<input type='submit' class='button' name='voteNo' value='No'>\n";
+					print "<input type='submit' class='button' name='voteAbstain' value='Abstain'>\n";
+					print "<input type='hidden' name='doVote' value='1'>\n";
+					print "</form></center>\n";
+				} else {
+					print "<center>$errorvote</center>";
+				}
+
+				print "</div></div>\n";
+				print "<br /><center><a href='tu.php'>Back</a></center>\n";
+
+			}
+
+		} else {
+			print "Vote ID not valid.\n";
+		}
+
+	} else {
+		# page listing applications being discussed, voted on and all those finished
+		# 
+
+		# I guess there should be a function since I use this a few times
+		function gen_results($offset, $limit, $sort, $by, $type="normal") {
+			
+			$dbh = db_connect();
+			$aweek = 60*60*24*7;
+
+    	if (!empty($offset) AND is_numeric($offset)) {
+      	if ($offset >= 1) {
+      	  $off = $offset;
+     	 	} else {
+      	  $off = 0;
+    	  }
+  	  } else {
+  	    $off = 0;
+	    }
+
+			$q = "SELECT * FROM TU_VoteInfo";
+			if ($type == "new") {	
+				$q.= " WHERE Submitted + " . $aweek . " > " . time();
+				$application = "Current Votes";
+			} else {
+				$q.= " WHERE Submitted + " . $aweek . " < " . time();
+				$application = "Old Votes";
+			}
+
+    	$order = ($by == 'down') ? 'DESC' : 'ASC';
+
+    	# not much to sort, I'm unsure how to sort by username
+    	# when we only store the userid, someone come up with a nifty
+    	# way to do this
+    	#
+    	switch ($sort) {
+    	  case 'sub':
+    	    $q.= " ORDER BY Submitted $order";
+    	    break;
+    	  default:
+    	    $q.= " ORDER BY Submitted $order";
+     	   break;
+    	}
+
+    	if ($limit != 0) {
+				$q.= " LIMIT " . $off . ", ". $limit;
+			}
+
+    	$result = db_query($q, $dbh);
+			
+			if ($by == "down") {
+    	  $by_next = "up";
+    	} else {
+    	  $by_next = "down";
+    	}
+
+    	print "<center>\n";
+    	print "<table cellspacing='3' class='boxSoft'>\n";
+    	print "<tr>\n";
+    	print "  <td class='boxSoftTitle' align='right'>\n";
+    	print "    <span class='f3'>$application</span>\n";
+    	print "  </td>\n";
+    	print "</tr>\n";
+    	print "<tr>\n";
+    	print "  <td class='boxSoft'>\n";
+    	print "<table width='100%' cellspacing='0' cellpadding='2'>\n";
+
+    	print "<tr>\n";
+    	print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+    	print " bottom'><span class='f2'>";
+    	print "Proposal";
+    	print "</span></th>\n";
+    	print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+    	print " bottom'><span class='f2'>";
+    	print "<a href='?off=$off&sort=sub&by=$by_next'>Submitted</a>";
+    	print "</span></th>\n";
+    	print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+    	print " bottom'><span class='f2'>";
+    	print "User";
+    	print "</span></th>\n";
+     	print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+    	print " bottom'><span class='f2'>";
+    	print "Yes";
+    	print "</span></th>\n";
+  	 	print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+    	print " bottom'><span class='f2'>";
+    	print "No";
+    	print "</span></th>\n";
+# I'm not sure if abstains are necessary inthis view, it's just extra clutter			
+#   		print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+#    	print " bottom'><span class='f2'>";
+#    	print "Abstain";
+#    	print "</span></th>\n";
+   		print "  <th style='border-bottom: #666 1px solid; vertical-align:";
+    	print " bottom'><span class='f2'>";
+    	print "Voted?";
+    	print "</span></th>\n";
+			print "</tr>\n";
+
+    	if (mysql_num_rows($result) == 0) {
+     		print "<tr><td align='center' colspan='0'>No results found.</td></tr>\n";
+    	} else {
+    		for ($i = 0; $row = mysql_fetch_assoc($result); $i++) {
+      		# Thankyou AUR
+      		(($i % 2) == 0) ? $c = "data1" : $c = "data2";
+      		print "<tr>\n";
+      		print "  <td class='".$c."'><span class='f4'><span class='blue'>";
+			
+					$prev_Len = 100;
+
+      		if (strlen($row["Agenda"]) >= $prev_Len) {
+        		$row["Agenda"] = htmlentities(substr($row["Agenda"], 0, $prev_Len)) . "...";
+      		} else {
+        		$row["Agenda"] = htmlentities($row["Agenda"]);
+      		}
+
+					print $row["Agenda"];
+					print " <a href='/tu.php?id=" . $row['ID'] . "'>[More]</a>";
+      		print "</span></span></td>\n";
+      		print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+      		print gmdate("r", intval($row["Submitted"]));
+      		print "</span></span></td>\n";
+      		print "  <td class='".$c."'><span class='f6'><span class='blue'>";
+
+					if (!empty($row['User'])) {
+      			print "<a href='packages.php?K=" . $row['User'] . "&SeB=m'>";
+						print $row['User'] . "</a>";
+					} else {
+						print "N/A";
+					}
+
+					print "</span></span></td>\n";
+          print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+          print $row['Yes'];
+          print "</span></span></td>\n";
+          print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+          print $row['No'];
+          print "</span></span></td>\n";
+					print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+					# See above
+          # print $row['Abstain'];
+          # print "</span></span></td>\n";
+          # print "  <td class='".$c."'><span class='f5'><span class='blue'>";
+
+					$qvoted = "SELECT * FROM TU_Votes WHERE ";
+          $qvoted.= "VoteID = " . $row['ID'] . " AND ";
+          $qvoted.= "UserID = " . uid_from_sid($_COOKIE["AURSID"]);
+ 					$hasvoted = mysql_num_rows(db_query($qvoted, $dbh));
+
+					if ($hasvoted == 0) {
+						print "<span style='color: red; font-weight: bold'>No</span>";
+					} else {
+						print "<span style='color: green; font-weight: bold'>Yes</span>";
+					}
+
+          print "</span></span></td>\n";
+					print "</tr>\n";
+    		}
+    	}
+		
+		print "</table>\n";
+    print "</table>\n";
+
+   	if ($type == "old" AND $limit != 0) { 
+			$qnext = "SELECT ID FROM TU_VoteInfo";
+    	$nextresult = db_query($qnext, $dbh);
+
+    	print "<table style='width: 90%'>\n";
+
+    	if (mysql_num_rows($result)) {
+      	$sort = htmlentities($sort, ENT_QUOTES);
+      	$by = htmlentities($by, ENT_QUOTES);
+
+      	print "<tr>\n";
+      	print "<td align='left'>\n";
+      	if ($off != 0) {
+      	  $back = (($off - $limit) <= 0) ? 0 : $off - $limit;
+      	  print "<a href='tu.php?off=$back&sort=" . $sort . "&by=" . $by . "'>Back</a>";
+      	}
+      	print "</td>\n";
+
+      	print "<td align='right'>\n";
+      	if (($off + $limit) < mysql_num_rows($nextresult)) {
+      	  $forw = $off + $limit;
+      	  print "<a href='tu.php?off=$forw&sort=" . $sort . "&by=" . $by . "'>Next</a>";
+      	}
+      	print "</td>\n";
+      	print "</tr>\n";
+    	}
+    	print "</table>\n";
+		}
+
+    print "</center>\n";
+		}
+
+	# stop notices, ythanku Xilon
+	if (empty($_REQUEST['sort'])) { $_REQUEST['sort'] = ""; }
+	if (empty($_REQUEST['by'])) { $_REQUEST['by'] = ""; }
+	if (empty($_REQUEST['off'])) { $_REQUEST['off'] = ""; }
+
+	gen_results(0, 0, $_REQUEST['sort'], $_REQUEST['by'], "new");
+	print "<center><a href='addvote.php'>Add</a></center><br />";
+	gen_results($_REQUEST['off'], $pp, $_REQUEST['sort'], $_REQUEST['by'], "old");
+
+	}
+} else {
+	print "You are not allowed to access this area.\n";
+}
+
+html_footer(AUR_VERSION);
+# vim: ts=2 sw=2
+
+?>