sqliteCreateAggregate('csv', 'tuple_step', 'tuple_finalize');
$current = 0;
$rank = 0;
function rank_function($n) {
global $current;
global $rank;
//echo "n: $n, $rank, $current
\n";
if($current != $n) {
++$rank;
$current = $n;
}
return $rank;
}
$dbh->sqliteCreateFunction('rank', 'rank_function');
SQLExec("CREATE TEMP VIEW v1g as SELECT category_id as cat, voter_id, weight, voted_for,
csv(firstname, lastname) as vf, students.gender as g
FROM $votes_table, students
WHERE student_id = voted_for
GROUP BY voter_id, weight, cat, g;");
// Alternate Gender-less view for any-gendered categories
SQLExec("CREATE TEMP VIEW v1 as SELECT category_id as cat, voter_id, weight, voted_for,
csv(firstname, lastname) as vf, students.gender as g
FROM $votes_table, students
WHERE student_id = voted_for
GROUP BY voter_id, weight, cat;");
// count(weight) for all choices counting equally;
// sum(weight) for first-choices to get extra weight
// use a table here because UDFs are called before sorting for selects, even from views or subselects,
// meaning that to apply UDF in sorted order (on votes) we need the table to be presorted
SQLExec("CREATE TEMP TABLE t1g as SELECT cat, vf, g as gender, count(weight) as votes , count(1)
FROM v1g
GROUP BY vf, cat
ORDER BY cat ASC, gender ASC, votes DESC;");
SQLExec("CREATE TEMP TABLE t1 as SELECT cat, vf, count(weight) as votes , count(1)
FROM v1
GROUP BY vf, cat
ORDER BY cat ASC, votes DESC;");
if(isset($_GET['remdupes'])) {
$dup_pairs = SQLArrayQuery("select voter_id, category_id, voted_for, count() from superlatives_votes group by voted_for, voter_id, category_id, weight having count() > 2 order by count() desc;", false, PDO::FETCH_ASSOC);
foreach($dup_pairs as $info) {
$voter = $dbh->quote($info['voter_id']);
$votee = $dbh->quote($info['voted_for']);
$cat = intval($info['category_id']);
SQLExec("delete from superlatives_votes where id in (select id from superlatives_votes where voter_id = $voter and voted_for = $votee and category_id = $cat group by id order by id asc LIMIT -1 OFFSET 1);\n");
}
exit;
}
if(isset($_GET['category_id']) && array_key_exists($_GET['category_id'], $categories)) {
$cat_id = intval($_GET['category_id']);
if(isset($_GET['pairwise'])) {
$query = "create temp table t2 as select csv(firstname, lastname) as people, weight, voter_id, students.gender from superlatives_votes, students where category_id = $cat_id and voted_for = student_id
group by voter_id, weight
order by voter_id;";
SQLExec($query);
$query =" select people, count(people) as votes from t2 group by people order by votes desc;";
$body .= prettyPrint(SQLArrayQuery($query, false, PDO::FETCH_ASSOC));
} else {
$body .= getCategoryResults($cat_id, $categories[$cat_id]) . '
';
}
} else {
// common case, voting on all categories
foreach($categories as $cat_id => $category) {
$body .= getCategoryResults($cat_id, $category) . '
';
}
}
function prettyPrint($a) {
$html = "";
if(!empty($a)) {
$keys = array_keys($a[0]);
$html .= "";
foreach($keys as $key)
$html .= "| $key | ";
$html .= "
\n";
foreach($a as $row) {
$html .= "";
foreach($keys as $key) {
$html .= "| {$row[$key]} | ";
}
$html .= "
\n";
}
$html .= "
\n\n";
}
return $html;
}
function getTable($cat, $gender) {
global $rank; global $current;
$rank = $current = 0;
global $top;
if($gender != '-1') {
$query = "select rank(votes) as Rank, votes as 'Total Votes', vf as 'Name(s)'
from t1g where cat = ? and gender = ? and Rank <= ?
;";
$html = prettyPrint(SQLArrayQuery($query, array($cat, $gender, $top), PDO::FETCH_ASSOC));
} else {
$query = "select rank(votes) as Rank, votes as 'Total Votes', vf as 'Name(s)'
from t1 where cat = ? and Rank <= ?
;";
$html = prettyPrint(SQLArrayQuery($query, array($cat, $top), PDO::FETCH_ASSOC));
}
return str_replace(',', '
& ', $html);
}
function getCategoryResults($category_id, $desc) {
global $sth;
global $top;
$html = "
| Votes for: $desc |
"
. getCatResultBody($category_id, $desc) .
"
";
return $html;
}
function getCatResultBody($category_id, $desc) {
$html = '';
if(config("superlatives.$category_id/anygender", '') == '') {
$html .= "| Male | Female |
| ".getTable($category_id, 'M')." |
".getTable($category_id, 'F')." |
";
} else {
$html .= "| Group |
| ".getTable($category_id, '-1')." |
";
}
return $html;
}
?>
<?php echo "($election) Voting Results -- " . schoolName() . " ($year)"; ?>