*
verticalSlice
1. For an array of assoc rays, return an array of values for a particular key
2. if $keyfield is given, same as above but use that hash key as the key in new array
| public static verticalSlice ( $array, $field, $keyfield = null ) |
function displayAllMainPageCategoryItems()
{
$category_ids = DBHelper::verticalSlice(Category::getCategories(), "id");
$html = "";
for ($i = 0; $i < count($category_ids); $i++) {
$html .= sprintf('<div data-tab="%d" class="category-items hidden">', $i + 1);
$html .= displayMainPageCategoryItems($category_ids[$i]);
$html .= '</div>';
}
return $html;
}
function test_4_null()
{
DB::query("UPDATE accounts SET password = NULL WHERE username=%s", 'Bart');
$all = DB::query("SELECT * FROM accounts ORDER BY id ASC");
$ages = DBHelper::verticalSlice($all, 'age', 'password');
$this->assert(count($ages) === 5);
$this->assert($ages[''] === '15');
$passwords = DBHelper::reIndex($all, 'password');
$this->assert(count($passwords) === 5);
$this->assert($passwords['']['username'] === 'Bart');
$this->assert($passwords['']['password'] === NULL);
}
public function getItems($use_1d)
{
if (count($this->items) != 0) {
if ($use_1d) {
return $this->items_1d;
}
return $this->items;
}
$data = DB::query("SELECT * FROM category_items WHERE category_id=%d", $this->category_id);
$ids = DBHelper::verticalSlice($data, "id");
foreach ($ids as $id) {
$item = new categoryItem($this->category_id, $id);
$values = array($this->category_id, $id, $item->name, $item->image);
array_push($this->items_1d, $values);
array_push($this->items, $item);
}
if ($use_1d) {
return $this->items_1d;
}
return $this->items;
}
die;
}
$email_bb = '';
foreach ($current_events as $event) {
$description = $event["description"];
if (empty(trim($description))) {
$description = "[no description]";
}
$email_bb .= "[subheading][i]{$event["title"]}[/i] on {$event["date"]}[/subheading]{$description}\n\n";
}
$email_bb = <<<HEREDOC
Hi captains!
This is a reminder that [b]{$count}[/b] events are coming up within a couple of weeks:
{$email_bb}
Thanks!
Your friendly LHS Math Club Mailbot
P.S. An event reminder will be sent every Sunday.
HEREDOC;
if ($count > 1) {
$subject = $count . " Events coming up";
} else {
$subject = "'" . $current_events[0]["title"] . "' coming up";
}
$status = send_email(array("*****@*****.**"), $subject, $email_bb, "*****@*****.**", "[LHS Math Club Captains]", NULL);
if ($status !== true) {
die("Error: {$status}");
}
$ids = DBHelper::verticalSlice($current_events, 'event_id');
DB::update('events', array('auto_remind' => DB::sqleval('auto_remind + 1')), 'event_id in %li', $ids);
$sid = $_GET["sid"];
if ($user === 0) {
header("Location: /index.php");
exit;
}
if ($user->data["permission"] != 4) {
if ($user->data["service_id"] != $sid && $user->data["permission"] == 3) {
echo "Invalid permissions";
// return;
}
}
$id = intval($id);
if ($id !== -1) {
$item = DB::queryOneRow("SELECT * FROM menu_items WHERE id=%d", $id);
$side_link = DB::query("SELECT * FROM menu_sides_item_link WHERE item_id=%d", $id);
$used = DBHelper::verticalSlice($side_link, "sides_id");
if (count($used) == 0) {
$used = array(-1);
}
$osides = DB::query('SELECT * FROM menu_sides WHERE id NOT IN (' . implode(',', array_map('intval', $used)) . ') AND service_id=%d', $sid);
} else {
$side_link = array();
$osides = DB::query("SELECT * FROM menu_sides WHERE service_id=%d", $sid);
}
$iname = $id == -1 ? "Name" : "Name: " . $item["name"];
$iprice = $id == -1 ? "Price" : "Price: " . $item["price"];
$idesc = $id == -1 ? "Description" : "Description: " . $item["desc"];
$checked = $id == -1 ? "" : ($item["has_side"] === "1" ? "checked" : "");
if ($id === -1) {
$id = intval(DB::queryOneRow("SELECT * FROM settings WHERE name='lastitem'")["value"]) + 1;
DB::update("settings", array("value" => $id), "name=%s", "lastitem");
function get_bcc_list()
{
static $list = 0;
//Caching, for efficiency.
if ($list === 0) {
$result = DB::query('SELECT name, email FROM users WHERE mailings="1" AND permissions!="T" AND email_verification="1"');
//Doesn't have to be approved. Includes you.
$list = DBHelper::verticalSlice($result, 'name', 'email');
}
return $list;
}
function show_scores()
{
/*
//What's the difference between inner, right, left, and full?
//NO IDEA STILL.
SELECT * FROM (
SELECT score_id, tests.test_id FROM (test_scores INNER JOIN tests ON tests.test_id = test_scores.test_id)
UNION ALL
SELECT score_id, tests.test_id FROM (test_scores RIGHT JOIN tests ON tests.test_id = test_scores.test_id)
) tbl
GROUP BY score_id HAVING COUNT(*) =1
*/
/*
//Valiant attempt:
SELECT tests.test_id, tests.name, tests.total_points, (test_scores.score - stats.average) / stats.st AS zvalue
FROM (test_scores
INNER JOIN tests ON test_scores.test_id = tests.test_id
CROSS JOIN (SELECT STDDEV(test_scores.score) AS st, AVG(test_scores.score) AS average) stats)
WHERE test_scores.test_id IN (1,2,3,4,5,6,7,8)
GROUP BY test_scores.test_id ORDER BY tests.date DESC
*/
$teststats = DB::query('SELECT
tests.test_id as test_id,
tests.name as name,
tests.total_points as total_points,
STDDEV(test_scores.score) AS stddev,
AVG(test_scores.score) AS avg
FROM (test_scores INNER JOIN tests ON test_scores.test_id = tests.test_id)
WHERE test_scores.test_id IN %li
GROUP BY test_scores.test_id ORDER BY tests.date DESC', $_GET['Test']);
$test_ids = DBHelper::verticalSlice($teststats, 'test_id');
$test_names = DBHelper::verticalSlice($teststats, 'name', 'test_id');
$avgs = DBHelper::verticalSlice($teststats, 'avg', 'test_id');
$stddevs = DBHelper::verticalSlice($teststats, 'stddev', 'test_id');
/*
Original, readable one:
select users.name,
sum(case tests.test_id when 248 then score/12 end) as t1,
sum(case tests.test_id when 249 then score/16 end) as t2,
sum(case tests.test_id when 248 then score/12 when 249 then score/16 end) as total
from test_scores
inner join tests
on test_scores.test_id = tests.test_id
inner join users
on test_scores.user_id = users.id
where tests.test_id in (248, 249)
group by test_scores.user_id
order by sum(test_scores.score) desc, users.name asc
*/
//Generating HTML in my SQL. LOLZ.
$query = "select users.name as name, ";
$sums = "";
$total = "sum(case tests.test_id ";
/*if(isSet($$$))//add a select for the CanGo
$sums .= ",GROUP_CONCAT(
case tests.test_id
when {$test_id} then CONCAT('<div style=\'height:4em;\' onmouseover=\"$(this).find(\'small\').css({display:\'inline\'});\"><b>',score,'</b> <small style=\"float:right;display:none;\">(z: ',round((score-{$avgs[$test_id]})/{$stddevs[$test_id]},3),')</small></div>')
else '' end SEPARATOR '') as t{$test_id} ";*/
foreach ($test_ids as $test_id) {
$sums .= ",GROUP_CONCAT(\n\t\t\t\tcase tests.test_id\n\t\t\t\twhen {$test_id} then CONCAT('<div style=\\'height:4em;\\' onmouseover=\"\$(this).find(\\'small\\').css({display:\\'inline\\'});\"><b>',score,'</b> <small style=\"float:right;display:none;\">(z: ',round((score-{$avgs[$test_id]})/{$stddevs[$test_id]},3),')</small></div>')\n\t\t\t\telse '' end SEPARATOR '') as t{$test_id} ";
$total .= "when {$test_id} then (score-{$avgs[$test_id]})/{$stddevs[$test_id]} ";
}
$total .= "end ) as zsum,\n\t\t\tsum(case when score is null then 0 else 1 end) as count,\n\t\t\tsum(score) as sum";
//zsum is bonkers
$query .= "{$total} {$sums} from test_scores\n\tinner join tests\n\t on test_scores.test_id = tests.test_id\n inner join users\n on test_scores.user_id = users.id\n where tests.test_id in %li\n\tgroup by test_scores.user_id";
foreach ($test_ids as $id) {
$test_names['t' . $id] = $test_names[$id];
unset($test_names[$id]);
$avgs['t' . $id] = round($avgs[$id], 3);
unset($avgs[$id]);
$stddevs['t' . $id] = round($stddevs[$id], 3);
unset($stddevs[$id]);
}
//Sets each assoc array key from ID to tID, just like in the SQL above.
$headers = array('name' => 'Name', 'total' => 'Total') + $test_names;
//Assoc array with key test_id and value test name
$headdata = array();
$headdata[] = array('name' => 'Average', 'total' => '') + $avgs;
$headdata[] = array('name' => 'Pop Std Dev', 'total' => '') + $stddevs;
$headdata[] = array();
$querydata = DB::query($query, $test_ids);
//var_dump($querydata);
foreach ($querydata as &$row) {
//The extra addition creates a significant bias toward people who have taken more tests.
$row['finalscore'] = $row['zsum'] / $row['count'] + $row['count'] / 2.5;
$row['total'] = "score: <b>" . round($row['finalscore'], 3) . "</b>" . "<br>Σz: " . round($row['zsum'], 3) . "<br>Σx: " . $row['sum'];
}
unset($row);
//php, why do I actually have to do this?
usort($querydata, function ($r1, $r2) {
if ($r1['finalscore'] != $r2['finalscore']) {
//First in order of z-average
return $r1['finalscore'] < $r2['finalscore'] ? 1 : -1;
} else {
//Then in random order.
return mt_rand(-1000, 1000);
}
});
$data = array_merge($headdata, $querydata);
$namestr = '<div><b>Tests:</b> ' . implode(', ', $test_names) . '</div><br>';
foreach ($querydata as $row) {
$namestr .= $row['name'] . '<br>';
}
echo <<<HEREDOC
<h1>View Scores</h1>
\t
\t <h3>TL;DR</h3>
\t <div>{$namestr}</div>
\t <br>
\t
\t <h3>Stats</h3>
<p>Z-scores are a very good way of standardizing scores. It is sorted by a (slightly modified) z-average, which is the average of the <i>available</i> z-scores only. The final sorted-by score has a bit of weight placed toward attending more tryouts. Ties, as always, are broken in random order.</p>
\t
<a href="Tests">< Back</a>
HEREDOC
. make_table('contrasting', $headers, $data);
}
function lmt_send_coaches_email($subject, $bb_body)
{
$result = DB::query('SELECT name, email FROM coaches WHERE email != "" AND deleted="0"');
$list = DBHelper::verticalSlice($result, 'email', 'name');
return lmt_send_list_email($list, $subject, $bb_body, 'coaches');
}
