function test_3_more_inserts()
{
DB::insert('`accounts`', array('username' => 'Bart', 'password' => 'hello', 'age' => 15, 'height' => 10.371));
$dbname = DB::$dbName;
DB::insert("`{$dbname}`.`accounts`", array('username' => 'Charlie\'s Friend', 'password' => 'goodbye', 'age' => 30, 'height' => 155.23, 'favorite_word' => null));
$this->assert(DB::insertId() === 3);
$counter = DB::queryFirstField("SELECT COUNT(*) FROM accounts");
$this->assert($counter === strval(3));
DB::insert('`accounts`', array('username' => 'Deer', 'password' => '', 'age' => 15, 'height' => 10.371));
$username = DB::queryFirstField("SELECT username FROM accounts WHERE password=%s0", null);
$this->assert($username === 'Deer');
$password = DB::queryFirstField("SELECT password FROM accounts WHERE favorite_word IS NULL");
$this->assert($password === 'goodbye');
DB::$usenull = false;
DB::insertUpdate('accounts', array('id' => 3, 'favorite_word' => null));
$password = DB::queryFirstField("SELECT password FROM accounts WHERE favorite_word=%s AND favorite_word=%s", null, '');
$this->assert($password === 'goodbye');
DB::$usenull = true;
DB::insertUpdate('accounts', array('id' => 3, 'favorite_word' => null));
DB::$param_char = '###';
$bart = DB::queryFirstRow("SELECT * FROM accounts WHERE age IN ###li AND height IN ###ld AND username IN ###ls", array(15, 25), array(10.371, 150.123), array('Bart', 'Barts'));
$this->assert($bart['username'] === 'Bart');
DB::insert('accounts', array('username' => 'f_u'));
DB::query("DELETE FROM accounts WHERE username=###s", 'f_u');
DB::$param_char = '%';
$charlie_password = DB::queryFirstField("SELECT password FROM accounts WHERE username IN %ls AND username = %s", array('Charlie', 'Charlie\'s Friend'), 'Charlie\'s Friend');
$this->assert($charlie_password === 'goodbye');
$charlie_password = DB::queryOneField('password', "SELECT * FROM accounts WHERE username IN %ls AND username = %s", array('Charlie', 'Charlie\'s Friend'), 'Charlie\'s Friend');
$this->assert($charlie_password === 'goodbye');
$passwords = DB::queryFirstColumn("SELECT password FROM accounts WHERE username=%s", 'Bart');
$this->assert(count($passwords) === 1);
$this->assert($passwords[0] === 'hello');
$username = $password = $age = null;
list($age, $username, $password) = DB::queryOneList("SELECT age,username,password FROM accounts WHERE username=%s", 'Bart');
$this->assert($username === 'Bart');
$this->assert($password === 'hello');
$this->assert($age == 15);
$mysqli_result = DB::queryRaw("SELECT * FROM accounts WHERE favorite_word IS NULL");
$this->assert($mysqli_result instanceof MySQLi_Result);
$row = $mysqli_result->fetch_assoc();
$this->assert($row['password'] === 'goodbye');
$this->assert($mysqli_result->fetch_assoc() === null);
}
function do_move()
{
if ($_GET['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
if (isset($_GET['Up'])) {
$operator = ' < ';
$sql_order = 'DESC';
$modifier = -1;
} else {
if (isset($_GET['Down'])) {
$operator = ' > ';
$sql_order = 'ASC';
$modifier = 1;
} else {
trigger_error('Neither Up nor Down specified', E_USER_ERROR);
}
}
$row = DB::queryFirstRow('SELECT order_num FROM pages WHERE page_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '"');
$order = $row['order_num'];
$row = DB::queryFirstRow('SELECT page_id, order_num FROM pages WHERE order_num' . $operator . $order . ' ORDER BY order_num ' . $sql_order . ' LIMIT 1');
$other_id = $row['page_id'];
$new_order = (int) $order + $modifier;
DB::queryRaw('UPDATE pages SET order_num="' . mysqli_real_escape_string(DB::get(), $new_order) . '" WHERE page_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" LIMIT 1');
DB::queryRaw('UPDATE pages SET order_num="' . mysqli_real_escape_string(DB::get(), $order) . '" WHERE page_id="' . mysqli_real_escape_string(DB::get(), $other_id) . '" LIMIT 1');
header('Location: List');
}
function download_csv()
{
// Get Data
$file = "Team ID,Team Name,School\n";
$result = DB::queryRaw('SELECT team_id, teams.name AS team_name, schools.name AS school_name FROM teams ' . 'LEFT JOIN schools ON teams.school=schools.school_id WHERE teams.deleted="0" ORDER BY team_id');
$row = mysqli_fetch_assoc($result);
while ($row) {
$id = htmlentities($row['team_id']);
$team_name = htmlentities($row['team_name']);
$school_name = htmlentities($row['school_name']);
if ($school_name == '') {
$school_name = 'None';
}
$file .= $id . "," . $team_name . "," . $school_name . "\n";
$row = mysqli_fetch_assoc($result);
}
// Download File
header('Content-Description: File Transfer');
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="Team List.csv"');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . strlen($file));
cancel_templateify();
ob_clean();
flush();
echo $file;
}
function process_form()
{
if ($_POST['xsrf_token'] != $_SESSION['xsrf_token']) {
show_page('Huh? ERROR: big kablooie');
return;
}
$query = 'SELECT id, name FROM users WHERE id="' . mysqli_real_escape_string(DB::get(), $_POST['account_id']) . '"';
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) != 1) {
show_page('Nonexistent ID');
return;
}
$row = mysqli_fetch_assoc($result);
$id = $row['id'];
$name = $row['name'];
// ** FORM VALIDATED AT THIS POINT **
// perform elevation
$query = 'UPDATE users SET permissions="A", approved="1" WHERE id="' . $id . '" LIMIT 1';
DB::queryRaw($query);
// show confirmation page
page_header('Super-Admin');
echo <<<HEREDOC
<h1>Super-Admin</h1>
<span class="b">{$name}</span> was approved and elevated. Now clear the Super-Admin password.
HEREDOC;
//$names[0] = 'Super-Admin';
//$pages[0] = '';
//page_footer($names, $pages);
}
function generate_results()
{
if ($_GET['term'] == '') {
die;
}
$query = mysqli_real_escape_string(DB::get(), $_GET['term']);
$query = str_replace(" ", "%", $query);
$comma = "";
echo "[";
if (isset($_GET['Individual'])) {
$result = DB::queryRaw('SELECT DISTINCT name FROM individuals WHERE name LIKE "%' . $query . '%" OR id="' . $query . '" AND deleted="0" LIMIT 5');
$row = mysqli_fetch_assoc($result);
while ($row) {
echo $comma . "\n" . ' { "label": "' . $row['name'] . '", "category": "Individuals" }';
$comma = ",";
$row = mysqli_fetch_assoc($result);
}
} else {
if (isset($_GET['Unaffiliated'])) {
$result = DB::queryRaw('SELECT DISTINCT name FROM individuals WHERE name LIKE "%' . $query . '%" OR id="' . $query . '" AND email <> "" AND deleted="0" LIMIT 5');
$row = mysqli_fetch_assoc($result);
while ($row) {
echo $comma . "\n" . ' { "label": "' . $row['name'] . '", "category": "Individuals" }';
$comma = ",";
$row = mysqli_fetch_assoc($result);
}
}
}
if (isset($_GET['Team'])) {
$result = DB::queryRaw('SELECT DISTINCT name FROM teams WHERE name LIKE "%' . $query . '%" OR team_id="' . $query . '" AND deleted="0" LIMIT 5');
$row = mysqli_fetch_assoc($result);
while ($row) {
echo $comma . "\n" . ' { "label": "' . $row['name'] . '", "category": "Teams" }';
$comma = ",";
$row = mysqli_fetch_assoc($result);
}
}
if (isset($_GET['School'])) {
$result = DB::queryRaw('SELECT DISTINCT name FROM schools WHERE name LIKE "%' . $query . '%" OR school_id="' . $query . '" AND deleted="0" LIMIT 5');
$row = mysqli_fetch_assoc($result);
while ($row) {
echo $comma . "\n" . ' { "label": "' . $row['name'] . '", "category": "Schools" }';
$comma = ",";
$row = mysqli_fetch_assoc($result);
}
}
if (isset($_GET['Coach'])) {
$result = DB::queryRaw('SELECT DISTINCT coach_email FROM schools WHERE coach_email LIKE "%' . $query . '%" AND deleted="0" LIMIT 5');
$row = mysqli_fetch_assoc($result);
while ($row) {
echo $comma . "\n" . ' { "label": "' . $row['coach_email'] . '", "category": "Coaches" }';
$comma = ",";
$row = mysqli_fetch_assoc($result);
}
}
echo "\n]";
}
function do_school()
{
DB::queryRaw('UPDATE schools SET deleted="0" WHERE school_id="' . mysqli_real_escape_string(DB::get(), $_GET['School']) . '" LIMIT 1');
global $LMT_DB;
if (mysqli_affected_rows($LMT_DB) != 1) {
trigger_error('School not found', E_USER_ERROR);
}
header('Location: School?ID=' . $_GET['School']);
}
function do_add_separator()
{
if ($_GET['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
$row = DB::queryFirstRow('SELECT MIN(order_num - 1) AS new_order FROM pages');
$new_order = $row['new_order'];
DB::queryRaw('INSERT INTO pages (name, content, order_num) VALUES ("", "", "' . mysqli_real_escape_string(DB::get(), $new_order) . '")');
header('Location: List');
}
function process_form()
{
if ($_POST['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
$members = preg_split('#[\\n\\r\\s]+#', $_POST['new_members'], PREG_SPLIT_NO_EMPTY);
$invalid_emails = '';
foreach ($members as $email) {
$email = strtolower($email);
$valid = true;
// Check that address is valid
if (!preg_match('/^([\\w\\!\\#$\\%\\&\'\\*\\+\\-\\/\\=\\?\\^\\`{\\|\\}\\~]+\\.)*[\\w\\!\\#$\\%\\&\'\\*\\+\\-\\/\\=\\?\\^\\`{\\|\\}\\~]' . '+@((((([a-z0-9]{1}[a-z0-9\\-]{0,62}[a-z0-9]{1})|[a-z])\\.)+[a-z]{2,6})|(\\d{1,3}\\.){3}\\d{1,3}(\\:\\d{1,5})?)$/i', $email)) {
$valid = false;
}
// Check that account does not already exist
$sql_email = mysqli_real_escape_string(DB::get(), $email);
$query = 'SELECT COUNT(*) FROM users WHERE LOWER(email)="' . $sql_email . '"';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
if ($row['COUNT(*)'] != 0) {
$valid = false;
}
if (!$valid) {
$invalid_emails .= $email . "\n";
} else {
// email address is valid; send invitation
// Generate pre-approval code (the year and month are hashed in)
global $SECRET_SALT;
$code = sha1(hash_pass($email, $SECRET_SALT) . 'KJincsaio09j87po8h6CAlo8tesojesai' . date('YF'));
// Generate link
$protocol = $_SERVER['HTTPS'] == 'on' ? 'https' : 'http';
$url_pieces = parse_url($_SERVER['REQUEST_URI']);
$dir = dirname(dirname($url_pieces['path']));
if ($dir == '/') {
$dir = '';
}
$link = $protocol . '://' . $_SERVER['HTTP_HOST'] . $dir . '/Account/Pre_Approval?email=' . $email . '&approval=' . $code;
// Send email
global $WEBMASTER_EMAIL;
$to = $email;
$subject = 'Welcome';
$body = <<<HEREDOC
Welcome to the LHS Math Club!
The Math Club website allows members to download handouts, view test scores, and
subscribe to the mailing list. To sign up for an account, click the link below:
{$link}
HEREDOC;
send_email($to, $subject, $body, $WEBMASTER_EMAIL);
}
}
$_SESSION['INVITE_done'] = $invalid_emails;
header('Location: Invite_Members');
}
function add_individual()
{
$result = DB::queryRaw('SELECT id FROM individuals WHERE name="New Individual~" AND deleted="0"');
$row = mysqli_fetch_assoc($result);
if (!$row) {
DB::queryRaw('INSERT INTO individuals (name) VALUES ("New Individual~")');
$result = DB::queryRaw('SELECT id FROM individuals WHERE name="New Individual~"');
$row = mysqli_fetch_assoc($result);
}
header('Location: Individual?ID=' . $row['id']);
die;
}
function process_form()
{
if ($_POST['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
$yog = (int) date('Y') - 1;
if ((int) date('n') > 4) {
$yog++;
}
$query = 'UPDATE users SET permissions="L", mailings="0" WHERE permissions="R" AND yog=' . $yog;
DB::queryRaw($query);
$_SESSION['ALUMNI_set'] = true;
header('Location: Alumni');
}
function show_detail_page()
{
page_title('Registration Log');
$ip = htmlentities($_GET['IP']);
echo <<<HEREDOC
<h1>Registration Log</h1>
<a href="Registration_Log" class="small">< Back to Full List</a><br /><br />
<span class="i">IP addresses that are spamming the site can be banned by editing /.lib/CONFIG.php</span><br /><br />
<span class="b">Accounts Created From {$ip}</span>
<table class="visible">
<tr>
<th>Name</th>
<th>Email Address</th>
<th>YOG</th>
<th>Account Creation</th>
<th>Status</th>
</tr>
HEREDOC;
$query = 'SELECT name, email, yog, creation_date, DATE_FORMAT(creation_date, "%M %e, %Y") AS formatted_creation, approved FROM users WHERE registration_ip="' . mysqli_real_escape_string(DB::get(), $_GET['IP']) . '" ORDER BY creation_date DESC';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
while ($row) {
$color = '#a00';
$status = 'Banned';
if ($row['approved'] == 0) {
$color = '#000';
$status = 'Pending';
} else {
if ($row['approved'] == 1) {
$color = '#0a0';
$status = 'Approved';
}
}
$email = trim_email($row['email']);
echo <<<HEREDOC
<tr>
<td>{$row['name']}</td>
<td>{$email}</td>
<td>{$row['yog']}</td>
<td>{$row['formatted_creation']}</td>
<td style="color: {$color}">{$status}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo " </table>\n";
}
function do_delete_page()
{
if ((int) $_GET['ID'] == -1) {
trigger_error('Cannot delete Registration page', E_USER_ERROR);
}
if ($_POST['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
$row = DB::queryFirstRow('SELECT name FROM pages WHERE page_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '"');
$page_name = htmlentities($row['name']);
DB::queryRaw('DELETE FROM pages WHERE page_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" LIMIT 1');
alert('The page "' . $page_name . '" has been deleted', 1);
header('Location: List');
}
function preview_message()
{
if (!validate_message()) {
return;
}
global $subject, $bb_body, $body, $email, $EMAIL_ADDRESS, $LMT_EMAIL;
// Get info for the byline
$query = 'SELECT name, email FROM users WHERE id="' . $_SESSION['user_id'] . '"';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$disp_subject = '[LMT ' . htmlentities(map_value('year')) . '] ' . $subject;
lmt_page_header('Email Coaches');
echo <<<HEREDOC
<h1>Email Coaches</h1>
<table class="spacious">
<tr>
<td>From:</td>
<td><span class="b">LMT Mailbot <{$EMAIL_ADDRESS}></span></td>
</tr><tr>
<td>Reply To: </td>
<td><span class="b">{$LMT_EMAIL}</span><br /></td>
</tr><tr>
<td>Subject:</td>
<td><span class="b">{$disp_subject}</span><br /><br /></td>
</tr><tr>
<td>Body:</td>
<td>{$bb_body}<br /><br /></td>
</tr><tr>
<td></td>
<td>
<form id="composeMessage" method="post" action="{$_SERVER['REQUEST_URI']}"><div>
<input type="hidden" name="subject" value="{$subject}" />
<input type="hidden" name="body" value="{$body}" />
<input type="hidden" name="email" value="{$email}" />
<input type="hidden" name="xsrf_token" value="{$_SESSION['xsrf_token']}" />
<input type="submit" name="lmtc_do_reedit_message" value="Back to Editing" />
<input type="submit" name="lmtc_do_post_message" value="Send Message" />
</div></form>
</td>
</tr><tr>
<td></td>
<td><span class="small">Please do not click the "Send Message" button twice!</span></td>
</tr>
</table>
HEREDOC;
}
function do_download()
{
$backup_name = 'LMT Backup ' . time() . '.sql';
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="' . $backup_name . '"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
cancel_templateify();
ob_clean();
flush();
echo 'CREATE DATABASE `lmt-bak` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;' . "\n" . 'USE `lmt-bak`;' . "\n\n\n";
$tables = array();
$result = DB::queryRaw('SHOW TABLES');
while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
foreach ($tables as $table) {
$result = DB::queryRaw('SELECT * FROM ' . $table);
$num_fields = mysqli_field_count($result);
echo 'DROP TABLE IF EXISTS ' . $table . ';';
$row2 = mysqli_fetch_row(DB::queryRaw('SHOW CREATE TABLE ' . $table));
echo "\n\n" . $row2[1] . ";\n\n";
for ($i = 0; $i < $num_fields; $i++) {
while ($row = mysqli_fetch_row($result)) {
echo 'INSERT INTO ' . $table . ' VALUES(';
for ($j = 0; $j < $num_fields; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
echo 'NULL';
} else {
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("\n", "\\n", $row[$j]);
echo '"' . $row[$j] . '"';
}
if ($j < $num_fields - 1) {
echo ',';
}
}
echo ");\n";
}
}
echo "\n\n\n";
}
}
function show_page()
{
// Check XSRF token
if ($_SESSION['xsrf_token'] != $_GET['xsrf_token']) {
trigger_error('Archive: XSRF token invalid', E_USER_ERROR);
}
// Check that test exists
$query = 'SELECT user_id FROM test_scores WHERE score_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '"';
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) != 1) {
trigger_error('Incorrect number of results found');
}
$row = mysqli_fetch_assoc($result);
$user_id = $row['user_id'];
$query = 'DELETE FROM test_scores WHERE score_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" LIMIT 1';
DB::queryRaw($query);
header('Location: View_User?ID=' . $user_id);
}
function show_page()
{
global $header_noprint;
$header_noprint = true;
lmt_page_header('Attendance Sheets');
echo <<<HEREDOC
<h1 class="noPrint">Attendance Sheets</h1>
<div class="text-centered b noPrint">To generate attendance sheets for the coaches, please print<br />
this page single-sided in portrait mode at normal size.</div>
<div class="printOnly">
HEREDOC;
$result = DB::queryRaw('SELECT team_id, teams.name AS team_name, teams.school AS school_id,' . ' schools.name AS school_name FROM teams LEFT JOIN schools' . ' ON teams.school=schools.school_id WHERE teams.deleted="0" ORDER BY school_name, team_name');
$row = mysqli_fetch_assoc($result);
while ($row) {
$team_id = htmlentities($row['team_id']);
$team_name = htmlentities($row['team_name']);
$school = htmlentities($row['school_name']);
if ($school == '') {
$school = 'None';
}
echo <<<HEREDOC
\t\t<h2 style="float: right;">{$team_id}</h2>
<h1 style="text-align: left; margin: 0;">{$team_name}</h1>
<h3 class="i noMargin">{$school}</h3>
<br /><br />
HEREDOC;
$result2 = DB::queryRaw('SELECT name FROM individuals WHERE team="' . mysqli_real_escape_string(DB::get(), $team_id) . '" AND deleted="0" ORDER BY name');
$row2 = mysqli_fetch_assoc($result2);
if (!$row2) {
echo "\n" . ' <h3 class="text-centered">No Members</span>' . "\n\n";
}
while ($row2) {
$name = htmlentities($row2['name']);
echo "\n" . ' <div class="attendPerson"><div class="checkBox"></div>' . $name . '</div>';
$row2 = mysqli_fetch_assoc($result2);
}
echo "\n" . ' <div class="pageBreak"></div>' . "\n\n";
$row = mysqli_fetch_assoc($result);
}
echo " </div>";
}
function show_page()
{
$query = 'SELECT * FROM users WHERE id="' . $_SESSION['user_id'] . '" LIMIT 1';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$cell = format_phone_number($row['cell']);
if ($cell == '') {
$cell = 'None';
}
page_title('Approve');
?>
<h1>Account Approval</h1>
Your account has been verified, but it must be approved by a captain. Please print this page and bring it to practice.<br />
<br />
<div class="scrhide">
<span class="b">ID: </span><?php
echo $row['id'];
?>
<br />
<span class="b">Name: </span><?php
echo $row['name'];
?>
<br />
<span class="b">Cell: </span><?php
echo $cell;
?>
<br />
<span class="b">Email: </span><?php
echo $row['email'];
?>
<br />
<span class="b">YOG: </span><?php
echo $row['yog'];
?>
<br />
<span class="b">Account Type: </span><?php
echo $row['permissions'];
?>
</div>
<?php
}
function show_page_for_members()
{
// Make certain links open in a new tab/window
global $use_rel_external_script, $CAPTAIN_EMAIL, $WEBMASTER_EMAIL;
$use_rel_external_script = true;
page_header('Contact');
echo <<<HEREDOC
<h1>Contact</h1>
<ul>
<li>
<span class="b">All Captains</span><br />
<a href="mailto:{$CAPTAIN_EMAIL}" rel="external">{$CAPTAIN_EMAIL}</a><br />
<br />
\t\t <br />
</li>
HEREDOC;
// Fetch Data
$query = 'SELECT name, email, cell FROM users WHERE permissions="C"';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
while ($row) {
echo " <li>\n" . " <span class=\"b\">{$row['name']}</span><br />\n" . " <a href=\"mailto:{$row['email']}\" rel=\"external\">{$row['email']}</a><br />\n";
$cell = format_phone_number($row['cell']);
if ($cell != 'None') {
echo " {$cell}<br />\n";
}
echo " </li>\n";
$row = mysqli_fetch_assoc($result);
}
echo <<<HEREDOC
<br><li>
If you experience difficulty using this site, please
<a href="mailto:{$WEBMASTER_EMAIL}" rel="external">
email the Webmaster</a>
</li>
</ul>
<br />
HEREDOC;
}
function do_add_page()
{
if ($_POST['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
$name = $_POST['name'];
$content = $_POST['content'];
if ($name == '') {
show_form('Please choose a name for the page');
}
if (strlen($name) > 25) {
show_form('The page name may not be longer than 25 characters');
}
if (strlen($content) > 20000) {
show_form('The content may not be longer than 20,000 characters');
}
// ** VALIDATION COMPLETE ** \\
$row = DB::queryFirstRow('SELECT MIN(order_num - 1) AS new_order FROM pages');
$new_order = $row['new_order'];
DB::queryRaw('INSERT INTO pages (name, content, order_num) VALUES ("' . mysqli_real_escape_string(DB::get(), $name) . '", "' . mysqli_real_escape_string(DB::get(), $content) . '", "' . mysqli_real_escape_string(DB::get(), $new_order) . '")');
$row = DB::queryFirstRow('SELECT page_id FROM pages WHERE order_num="' . mysqli_real_escape_string(DB::get(), $new_order) . '"');
header('Location: View?ID=' . $row['page_id']);
}
function do_edit_page()
{
if ((int) $_GET['ID'] == -1) {
trigger_error('Cannot edit Registration page', E_USER_ERROR);
}
if ($_POST['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
$name = $_POST['name'];
$content = $_POST['content'];
if ($name == '') {
show_form('Please choose a name for the page');
}
if (strlen($name) > 25) {
show_form('The page name may not be longer than 25 characters');
}
str_replace($content, "{INDIVCOST}", map_value());
if (strlen($content) > 20000) {
show_form('The content may not be longer than 20,000 characters');
}
// ** VALIDATION COMPLETE ** \\
DB::queryRaw('UPDATE pages SET name="' . mysqli_real_escape_string(DB::get(), $name) . '", content="' . mysqli_real_escape_string(DB::get(), $content) . '" WHERE page_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" LIMIT 1');
header('Location: View?ID=' . $_GET['ID']);
}
function show_page()
{
page_header('My Scores');
echo <<<HEREDOC
<h1>My Scores</h1>
HEREDOC;
$query = 'SELECT test_scores.score AS score, tests.name AS name, tests.total_points AS total, DATE_FORMAT(tests.date, "%M %e, %Y") AS formatted_date' . ' FROM test_scores' . ' INNER JOIN tests ON tests.test_id=test_scores.test_id' . ' WHERE test_scores.user_id="' . mysqli_real_escape_string(DB::get(), $_SESSION['user_id']) . '" AND archived="0"' . ' ORDER BY tests.date DESC';
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) > 0) {
echo <<<HEREDOC
<h4>Recent Tests</h4>
<table class="contrasting">
<tr>
<th>Test</th>
<th>Score</th>
<th>Date</th>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
while ($row) {
$score_display = $row['score'] . ' <span class="scorepart">/ ' . $row['total'] . '</span>';
if ($row['total'] == 1) {
$score_display = $row['score'] == 1 ? 'Yes' : 'No';
}
echo <<<HEREDOC
<tr>
<td>{$row['name']}</td>
<td class="text-centered">{$score_display}</td>
<td>{$row['formatted_date']}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo <<<HEREDOC
</table>
<br /><br />
HEREDOC;
} else {
echo <<<HEREDOC
<h4 class="smbottom">Recent Tests</h4><div class="halfbreak"></div>
There are no recent tests to display.
<br /><br />
HEREDOC;
}
$query = 'SELECT test_scores.score AS score, tests.name AS name, tests.total_points AS total, DATE_FORMAT(tests.date, "%M %e, %Y") AS formatted_date' . ' FROM test_scores' . ' INNER JOIN tests ON tests.test_id=test_scores.test_id' . ' WHERE test_scores.user_id="' . mysqli_real_escape_string(DB::get(), $_SESSION['user_id']) . '" AND archived="1"' . ' ORDER BY tests.date DESC';
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) > 0) {
echo <<<HEREDOC
<h4 class="smbottom">Old Tests</h4><div class="halfbreak"></div>
<table class="contrasting">
<tr>
<th>Test</th>
<th>Score</th>
<th>Date</th>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
while ($row) {
$score_display = $row['score'] . ' <span class="scorepart">/ ' . $row['total'] . '</span>';
if ($row['total'] == 1) {
$score_display = $row['score'] == 1 ? 'Yes' : 'No';
}
echo <<<HEREDOC
<tr>
<td>{$row['name']}</td>
<td class="text-centered">{$score_display}</td>
<td>{$row['formatted_date']}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo <<<HEREDOC
</table>
HEREDOC;
} else {
echo <<<HEREDOC
<h4 class="smbottom">Old Tests</h4><div class="halfbreak"></div>
There are no old tests to display.
HEREDOC;
}
}
$inF = fopen($file, "w");
while (list($cle, $val) = each($fileArray)) {
fputs($inF, decrypt($val, $key) . "\n");
}
fclose($inF);
} else {
$file = $_SESSION['settings']['path_to_files_folder'] . "/" . $file;
}
//read sql file
if ($handle = fopen($file, "r")) {
$query = "";
while (!feof($handle)) {
$query .= fgets($handle, 4096);
if (substr(rtrim($query), -1) == ';') {
//launch query
DB::queryRaw($query);
$query = '';
}
}
fclose($handle);
}
//delete file
unlink($file);
//Show done
echo '[{"result":"db_restore"}]';
break;
###########################################################
#CASE for optimizing the DB
###########################################################
#CASE for optimizing the DB
case "admin_action_db_optimize":
function do_enter_clarified_score()
{
if (!validate_theme_score($_GET['Score'])) {
trigger_error('Score isn\'t valid this time?!', E_USER_ERROR);
}
$row = DB::queryFirstRow('SELECT name, score_theme FROM individuals WHERE id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '"');
if (!is_null($row['score_theme']) && !isset($_GET['Overwrite'])) {
if (isset($_GET['xsrf_token'])) {
header('Location: Theme?ID=' . $_GET['ID'] . '&Score=' . $_GET['Score']);
die;
} else {
$msg = 'A score of ' . htmlentities($row['score_theme']) . ' has already been entered for ' . htmlentities($row['name']);
if ($row['score_theme'] != $_GET['Score']) {
$msg .= ' (<a href="Theme?Overwrite&ID=' . htmlentities($_GET['ID']) . '&Score=' . htmlentities($_GET['Score']) . '&xsrf_token=' . $_SESSION['xsrf_token'] . '">change to ' . htmlentities($_GET['Score']) . '</a>)';
}
show_page($msg, '');
}
}
// we check this later so we can go here without a token, too - so we can show an override message
// if the individual already has a score entered
if ($_GET['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
DB::queryRaw('UPDATE individuals SET score_theme="' . mysqli_real_escape_string(DB::get(), $_GET['Score']) . '" WHERE id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" LIMIT 1');
$msg = 'A score of ' . htmlentities($_GET['Score']) . ' was entered for ' . htmlentities($row['name']);
show_page($msg, '');
}
function show_page()
{
// Get data about user
$query = 'SELECT *, DATE_FORMAT(creation_date, "%M %e, %Y") AS formatted_creation FROM users WHERE id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" LIMIT 1';
$result = DB::queryRaw($query);
// have MySQL format the date for us
if (mysqli_num_rows($result) != 1) {
trigger_error('User not found', E_USER_ERROR);
}
// ** User Found, info valid at this point **
$row = mysqli_fetch_assoc($result);
// Page header
global $use_rel_external_script;
// direct page_header to include some javascript that will make links
$use_rel_external_script = true;
// marked as rel="external" open in a new tab while remaining XHTML-valid
page_header($row['name']);
// the title of the page is the user's name; helpful if you open multiple users in different tabs
echo <<<HEREDOC
<h1>View User</h1>
HEREDOC;
// Format Data
$email_verified = 'No';
if ($row['email_verification'] == '1') {
$email_verified = 'Yes';
}
$cell = format_phone_number($row['cell']);
$permissions = $row['permissions'];
$account_type = 'Regular';
if ($permissions == 'C') {
$account_type = 'Captain';
} else {
if ($permissions == 'A') {
$account_type = 'Non-Captain Admin';
} else {
if ($permissions == 'L') {
$account_type = 'Alumnus';
} else {
if ($permissions == 'T') {
$account_type = 'Temporary';
}
}
}
}
// mailing list status
$mailings = 'No';
if ($row['mailings'] == '1') {
$mailings = 'Yes';
}
// Format Approval Status line
//
// depending on whether the user is approved, banned, or in limbo, the link next to that
// information needs to un-approve, un-ban, or approve/ban the user
// eg. "Approval Status: Approved (to un-approve, click here)"
if ($row['approved'] == '-1') {
$approval_status = 'Banned';
$approval_line = " <span class=\"small\">(<a href=\"Edit_User?Approve&ID={$row['id']}&xsrf_token={$_SESSION['xsrf_token']}&Return=View\">approve</a> | <a href=\"Edit_User?Unapprove&ID={$row['id']}&xsrf_token={$_SESSION['xsrf_token']}&Return=View\">make pending</a>)</span>";
} else {
if ($row['approved'] == '0') {
$approval_status = 'Pending';
$approval_line = " <span class=\"small\">(<a href=\"Edit_User?Approve&ID={$row['id']}&xsrf_token={$_SESSION['xsrf_token']}&Return=View\">approve</a> | <a href=\"Edit_User?Ban&ID={$row['id']}&xsrf_token={$_SESSION['xsrf_token']}&Return=View\">ban</a>)</span>";
} else {
if ($row['approved'] == '1') {
$approval_status = 'Approved';
$approval_line = " <span class=\"small\">(<a href=\"Edit_User?Ban&ID={$row['id']}&xsrf_token={$_SESSION['xsrf_token']}&Return=View\">ban</a>)</span>";
}
}
}
echo <<<HEREDOC
<table class="spacious">
<tr>
<td>Name:</td>
<td>
<span class="b">{$row['name']}</span>
<span class="small">(<a href="Edit_User?Change_Name&ID={$row['id']}&Return=View">change</a>)</span>
</td>
</tr><tr>
<td>Email Address:</td>
<td class="b"><a href="mailto:{$row['email']}" rel="external">{$row['email']}</a></td>
</tr><tr>
<td>Cell Phone Number: </td>
<td class="b">{$cell}</td>
</tr><tr>
<td>Year of Graduation:</td>
<td>
<span class="b">{$row['yog']}</span>
<span class="small">(<a href="Edit_User?Change_YOG&ID={$row['id']}&Return=View">change</a>)</span>
<br /><br />
</td>
</tr><tr>
<td>ID:</td>
<td><span class="b">{$row['id']}</span></td>
</tr><tr>
<td>Account Type:</td>
<td>
<span class="b">{$account_type}</span>
<span class="small">(<a href="Edit_User?Change_Permissions&ID={$row['id']}&Return=View">change</a>)</span>
</td>
</tr><tr>
<td>Mailing List:</td>
<td><span class="b">{$mailings}</span></td>
</tr><tr>
<td>Approval Status:</td>
<td>
<span class="b">{$approval_status}</span>
{$approval_line}
</td>
</tr><tr>
<td>Email Verified:</td>
<td class="b">{$email_verified}</td>
</tr><tr>
<td>Creation Date:</td>
<td><span class="b">{$row['formatted_creation']}</span></td>
</tr><tr>
<td>Registered From:</td>
<td class="b">{$row['registration_ip']}</td>
</tr>
</table>
<br />
<span class="small i">Only users are able to edit their email address and cell phone number.</span>
HEREDOC;
// Show test scores
$query = 'SELECT test_scores.score AS score, tests.name AS name, tests.total_points AS total, DATE_FORMAT(tests.date, "%M %e, %Y") AS formatted_date, test_scores.score_id AS score_id' . ' FROM test_scores' . ' INNER JOIN tests ON tests.test_id=test_scores.test_id' . ' WHERE test_scores.user_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" AND archived="0"' . ' ORDER BY tests.date DESC';
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) > 0) {
echo <<<HEREDOC
<br /><br /><br /><br /><br />
<h4>Recent Test Scores</h4>
<table class="contrasting">
<tr>
<th>Test</th>
<th>Score</th>
<th>Maximum</th>
<th>Date</th>
<th></th>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
while ($row) {
echo <<<HEREDOC
<tr>
<td>{$row['name']}</td>
<td class="text-centered">{$row['score']}</td>
<td class="text-centered">{$row['total']}</td>
<td>{$row['formatted_date']}</td>
<td><a href="Delete_Score?ID={$row['score_id']}&xsrf_token={$_SESSION['xsrf_token']}">Delete</a></td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo <<<HEREDOC
</table>
HEREDOC;
}
$query = 'SELECT test_scores.score AS score, tests.name AS name, tests.total_points AS total, DATE_FORMAT(tests.date, "%M %e, %Y") AS formatted_date, test_scores.score_id AS score_id' . ' FROM test_scores' . ' INNER JOIN tests ON tests.test_id=test_scores.test_id' . ' WHERE test_scores.user_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" AND archived="1"' . ' ORDER BY tests.date DESC';
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) > 0) {
echo <<<HEREDOC
<br /><br />
<h4>Old Test Scores</h4>
<table class="contrasting">
<tr>
<th>Test</th>
<th>Score</th>
<th>Maximum</th>
<th>Date</th>
<th></th>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
while ($row) {
echo <<<HEREDOC
<tr>
<td>{$row['name']}</td>
<td class="text-centered">{$row['score']}</td>
<td class="text-centered">{$row['total']}</td>
<td>{$row['formatted_date']}</td>
<td><a href="Delete_Score?ID={$row['score_id']}&xsrf_token={$_SESSION['xsrf_token']}">Delete</a></td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo <<<HEREDOC
</table>
HEREDOC;
}
}
function cancel_score()
{
// Cancel the last round's score
if ($_POST['xsrf_token'] != $_SESSION['xsrf_token']) {
trigger_error('XSRF code incorrect', E_USER_ERROR);
}
$set = (int) htmlentities($_POST['set']) - 1;
if ($set < 1 || $set > 12) {
trigger_error('Invalid set?!', E_USER_ERROR);
}
if ($set == 12) {
// Scores are as text entered into TEAMS table
DB::queryRaw('UPDATE teams SET guts_ans_a=NULL, guts_ans_b=NULL, guts_ans_c=NULL WHERE team_id="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" LIMIT 1');
} else {
DB::queryRaw('DELETE FROM guts WHERE team="' . mysqli_real_escape_string(DB::get(), $_GET['ID']) . '" AND problem_set="' . mysqli_real_escape_string(DB::get(), $set) . '" LIMIT 3');
}
show_scoring_page();
}
function show_page()
{
if (scoring_is_enabled()) {
$message = '<div class="error">Score entry is still enabled! Disable it <a href="../Scoring/Refrigerator">here</a>.</div><br />';
}
lmt_page_header('Top Scorers');
echo <<<HEREDOC
<h1>Top Scorers</h1>
{$message}
<div class="text-centered b">
<span class="noPrint">
<a href="Full">Full Results</a>
<a href="Print">Scores for Coaches</a>
<br /><br />
</span>
<span class="red">Reminder: Do not copy data locally!</span><br />
Ties are listed in random order.
<br /><br />
</div>
<h2>Top 5 Individuals by Individual Round</h2>
<table class="contrasting">
<tr>
<th>Place</th>
<th>Name</th>
<th>School</th>
<th>Individual Round</th>
</tr>
HEREDOC;
score_guts();
// INDIVIDUAL ROUND
$query = 'SELECT id, individuals.name AS name, (SELECT name FROM schools WHERE school_id=teams.school) AS school_name, ' . 'RAND() AS rand, score_individual FROM individuals LEFT JOIN teams ON team=teams.team_id WHERE individuals.deleted="0" AND attendance="1" ORDER BY score_individual DESC, rand';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$place = 0;
$num = 0;
$last_score = null;
while ($row) {
$num++;
if ($row['score_individual'] != $last_score) {
$place = $num;
}
$last_score = $row['score_individual'];
if ($place > 5) {
break;
}
$id = htmlentities($row['id']);
$name = htmlentities($row['name']);
$school = htmlentities($row['school_name']);
if ($school == '') {
$school = '<span class="i">None</span>';
}
$score_individual = htmlentities($row['score_individual']);
echo <<<HEREDOC
<tr>
<td>{$place}</td>
<td><a href="../Data/Individual?ID={$id}">{$name}</a></td>
<td>{$school}</td>
<td class="b">{$score_individual}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo " </table>\n";
// Theme ROUND
echo <<<HEREDOC
<h2>Top 5 Individuals by Theme Round</h2>
<table class="contrasting">
<tr>
<th>Place</th>
<th>Name</th>
<th>School</th>
<th>Theme Round</th>
</tr>
HEREDOC;
$query = 'SELECT id, individuals.name AS name, (SELECT name FROM schools WHERE school_id=teams.school) AS school_name, ' . 'RAND() AS rand, score_theme FROM individuals LEFT JOIN teams ON team=teams.team_id WHERE individuals.deleted="0" AND attendance="1" ORDER BY score_theme DESC, rand';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$place = 0;
$num = 0;
$last_score = null;
while ($row) {
$num++;
if ($row['score_theme'] != $last_score) {
$place = $num;
}
$last_score = $row['score_theme'];
if ($place > 5) {
break;
}
$id = htmlentities($row['id']);
$name = htmlentities($row['name']);
$school = htmlentities($row['school_name']);
if ($school == '') {
$school = '<span class="i">None</span>';
}
$score_theme = htmlentities($row['score_theme']);
echo <<<HEREDOC
<tr>
<td>{$place}</td>
<td><a href="../Data/Individual?ID={$id}">{$name}</a></td>
<td>{$school}</td>
<td class="b">{$score_theme}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo " </table>\n";
// INDIVIDUAL COMPOSITE
echo <<<HEREDOC
<h2>Top 10 Individuals by Composite</h2>
<table class="contrasting">
<tr>
<th>Place</th>
<th>Name</th>
<th>School</th>
<th>Composite</th>
</tr>
HEREDOC;
$query = individual_composite('id, individuals.name AS name, (SELECT name FROM schools WHERE school_id=teams.school) AS school_name, ' . 'RAND() AS rand,', 'LEFT JOIN teams ON team=teams.team_id WHERE individuals.deleted="0" AND attendance="1" ORDER BY score_composite DESC, rand');
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$place = 0;
$num = 0;
$last_score = null;
while ($row) {
$num++;
if ($row['score_composite'] != $last_score) {
$place = $num;
}
$last_score = $row['score_composite'];
if ($place > 10) {
break;
}
$id = htmlentities($row['id']);
$name = htmlentities($row['name']);
$school = htmlentities($row['school_name']);
if ($school == '') {
$school = '<span class="i">None</span>';
}
$score_composite = htmlentities($row['score_composite']);
echo <<<HEREDOC
<tr>
<td>{$place}</td>
<td><a href="../Data/Individual?ID={$id}">{$name}</a></td>
<td>{$school}</td>
<td class="b">{$score_composite}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo " </table>\n";
// TEAM ROUND
echo <<<HEREDOC
<h2>Top 5 Teams by Team Round</h2>
<table class="contrasting">
<tr>
<th>Place</th>
<th>Team Name</th>
<th>Team Round</th>
</tr>
HEREDOC;
$query = 'SELECT team_id, name, IFNULL(score_team_short, 0) + IFNULL(score_team_long, 0) AS score_team, RAND() AS rand FROM teams WHERE deleted="0" ORDER BY score_team DESC, rand';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$place = 0;
$num = 0;
$last_score = null;
while ($row) {
$num++;
if ($row['score_team'] != $last_score) {
$place = $num;
}
$last_score = $row['score_team'];
if ($place > 5) {
break;
}
$id = htmlentities($row['team_id']);
$name = htmlentities($row['name']);
$score_team = htmlentities($row['score_team']);
if (is_null($row['score_team'])) {
$score_team = '<span class="i">None</span>';
}
echo <<<HEREDOC
<tr>
<td>{$place}</td>
<td><a href="../Data/Team?ID={$id}">{$name}</a></td>
<td class="b">{$score_team}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo " </table>\n";
// GUTS ROUND
echo <<<HEREDOC
<h2>Top 5 Teams by Guts Round</h2>
<table class="contrasting">
<tr>
<th>Place</th>
<th>Team Name</th>
<th>Guts Round</th>
</tr>
HEREDOC;
$query = 'SELECT team_id, name, score_guts, RAND() AS rand FROM teams WHERE deleted="0" ORDER BY score_guts DESC, rand';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$place = 0;
$num = 0;
$last_score = null;
while ($row) {
$num++;
if ($row['score_guts'] != $last_score) {
$place = $num;
}
$last_score = $row['score_guts'];
if ($place > 5) {
break;
}
$id = htmlentities($row['team_id']);
$name = htmlentities($row['name']);
$score_guts = htmlentities($row['score_guts']);
if (is_null($row['score_guts'])) {
$score_guts = '<span class="i">None</span>';
}
echo <<<HEREDOC
<tr>
<td>{$place}</td>
<td><a href="../Data/Team?ID={$id}">{$name}</a></td>
<td class="b">{$score_guts}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo " </table>\n";
// TEAM COMPOSITE
echo <<<HEREDOC
<h2>Top 5 Teams by Composite</h2>
<table class="contrasting">
<tr>
<th>Place</th>
<th>Team Name</th>
<th>Team Round</th>
<th>Guts Round</th>
<th>Composite</th>
</tr>
HEREDOC;
$query = team_composite('team_id, name, IFNULL(score_team_short, 0) + IFNULL(score_team_long, 0) AS score_team, score_guts, RAND() AS rand,', 'WHERE deleted="0" ORDER BY team_composite DESC, rand');
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$place = 0;
$num = 0;
$last_score = null;
while ($row) {
$num++;
if ($row['team_composite'] != $last_score) {
$place = $num;
}
$last_score = $row['team_composite'];
if ($place > 5) {
break;
}
$id = htmlentities($row['team_id']);
$name = htmlentities($row['name']);
$score_team = htmlentities($row['score_team']);
$score_guts = htmlentities($row['score_guts']);
$score_composite = htmlentities($row['team_composite']);
if (is_null($row['score_team'])) {
$score_team = '<span class="i">None</span>';
}
if (is_null($row['score_guts'])) {
$score_guts = '<span class="i">None</span>';
}
echo <<<HEREDOC
<tr>
<td>{$place}</td>
<td><a href="../Data/Team?ID={$id}">{$name}</a></td>
<td>{$score_team}</td>
<td>{$score_guts}</td>
<td class="b">{$score_composite}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
echo " </table>\n";
die;
}
function show_page()
{
page_header('Files');
echo <<<HEREDOC
<h1>Files</h1>
<br />
\t <div style='font-weight:bold'>2010-2013 files have been archived to <a href='https://www.dropbox.com/sh/6wo6f5i8il42m1c/RxpAYq6Pb1'>the Dropbox</a>.</div>
<br />
HEREDOC;
$admin_sql = '';
if (user_access('A')) {
$admin_sql = ' OR files.permissions="A"';
}
if (isset($_SESSION['is_captain'])) {
$admin_sql .= ' OR files.permissions="C"';
}
$query = 'SELECT files.file_id, files.name, files.category, file_categories.name AS category_name, files.permissions FROM files' . ' INNER JOIN file_categories ON files.category=file_categories.category_id' . ' WHERE ( files.permissions="P" OR files.permissions="M"' . $admin_sql . ' ) ' . ' AND ( files.category <> 2 && files.category <> 5 && files.category <> 8 && files.category <> 9 ) ' . ' ORDER BY category_name, category_id, order_num';
$result = DB::queryRaw($query);
$row = mysqli_fetch_assoc($result);
$current_category = -1;
while ($row) {
$category_name = $row['category_name'];
// If this row is the beginning of a new category
if ($row['category'] != $current_category) {
if ($current_category != -1) {
echo ' </table><br />' . "\n";
}
echo <<<HEREDOC
<h4 class="smbottom">{$category_name}</h4>
<table class="contrasting files">
HEREDOC;
$current_category = $row['category'];
}
// Normal stuff
$admin_only_styling = $row['permissions'] == 'A' ? ' class="i"' : '';
echo ' <tr><td' . $admin_only_styling . '><a href="Download?ID=' . $row['file_id'] . '">' . $row['name'] . '</a></td></tr>' . "\n";
$row = mysqli_fetch_assoc($result);
}
// Last footer
if ($current_category != -1) {
echo ' </table>' . "\n";
}
// Misc. table
$query = 'SELECT * FROM files WHERE category="0"' . ' AND (files.permissions="P" OR files.permissions="M"' . $admin_sql . ') ORDER BY order_num';
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) > 0) {
echo <<<HEREDOC
<h4 class="smbottom">Miscellaneous</h4>
<table class="contrasting files">
HEREDOC;
$row = mysqli_fetch_assoc($result);
while ($row) {
$admin_only_styling = $row['permissions'] == 'A' ? ' class="i"' : '';
echo ' <tr><td' . $admin_only_styling . '><a href="Download?ID=' . $row['file_id'] . '">' . $row['name'] . '</a></td></tr>' . "\n";
$row = mysqli_fetch_assoc($result);
}
echo ' </table>' . "\n";
}
}
function generate_user_table($query)
{
// If the query includes FORMAT(creation_date, '...') AS formatted_creation, add that to the standard table
// Note that this does not work for SELECT * - it searches for the string 'creation_date' in the query
$creation_date_th = '';
$add_creation_date = false;
if (strpos($query, 'formatted_creation')) {
$creation_date_th = "\n <th>Account Creation</th>";
$add_creation_date = true;
}
$html_table = <<<HEREDOC
<table class="contrasting">
<tr>
<th>Name</th>
<th>Email Address</th>
<th>YOG</th>{$creation_date_th}
</tr>
HEREDOC;
$result = DB::queryRaw($query);
if (mysqli_num_rows($result) == 0) {
if ($add_creation_date) {
$html_table .= " <tr><td colspan=\"4\" class=\"text-centered\">None</td></tr>\n";
} else {
$html_table .= " <tr><td colspan=\"3\" class=\"text-centered\">None</td></tr>\n";
}
// if no results returned
} else {
$row = mysqli_fetch_assoc($result);
while ($row) {
$creation_date_td = '';
$trimmed_email = trim_email($row['email']);
if ($add_creation_date) {
$creation_date_td = "\n <td>{$row['formatted_creation']}</td>";
}
$html_table .= <<<HEREDOC
\t <tr>
\t <td><a href="View_User?ID={$row['id']}">{$row['name']}</a></td>
\t <td><a href="mailto:{$row['email']}" rel="external">{$trimmed_email}</a></td>
\t <td>{$row['yog']}</td>{$creation_date_td}
\t </tr>
\t
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
}
$html_table .= " </table><br />\n";
return $html_table;
}
function show_page()
{
score_guts();
cancel_templateify();
header('X-LMT-Guts-Data: 42');
//:)
?>
<meta http-equiv="refresh" content="7">
<script src="https://code.jquery.com/jquery-2.1.3.min.js"></script>
<style type="text/css">
body{
font-family:"Georgia";
}
.box{
vertical-align:top;
border: solid 2px #000;
border-radius: 10px;
height: 50px;
width: 270px;
display: inline-block;
margin: 5px;
padding: 10px;
text-align:left;
display:none;
}
.box .place{
font-size: 1.4em;
}
.box .team{
font-weight: bold;
font-size: 1.2em;
}
.box .school{
font-size: 0.8em;
}
.box .score{
float: right;
font-size: 2.3em;
position:relative;
top:-30px;
}
.box .set{
}
h1{
font-size: 3.2em;
position:relative;
top:-30px;
margin-bottom: -10px;
}
h1 img{
position:relative;
top:30px;
}
h1 #timer{
width: 400px;
min-height: 1.2em;
border: solid 1px #000;
display: inline-block;
}
.suspense{
font-size: 3em;
}
</style>
<script>
console.log((new Date()).getTime());
function formatSeconds(secs){
var h = Math.floor(secs / (60*60));
var m = Math.floor((secs % 3600) / 60);
var s = Math.floor(secs % 60);
h=h.toString();
m=m.toString();
s=s.toString();
while(h.length < 2) h = "0"+h;
while(m.length < 2) m = "0"+m;
while(s.length < 2) s = "0"+s;
return h+":"+m+":"+s;
}
var targetTime = 1460180400 + 90 * 60 + 12 * 60 * 60 + 10 * 60 + 60 + 60 + 2 * 60;
function updateTime(){
var currTime = (new Date()).getTime()/1000;
if(targetTime - currTime > 90*60){
timerOut(formatSeconds(targetTime-currTime-90*60)+" <small style='display:block;font-size:0.4em;'>before start</small>");
}
if(targetTime - currTime <= 90*60){
timerOut(formatSeconds(targetTime-currTime));
}
if(targetTime - currTime <= 0){
timerOut("END!");
}
if(targetTime - currTime <=300){
$(".box").fadeOut();
$(".suspense").fadeIn();
}
else{
$(".box").css("display","inline-block");
$(".suspense").hide();
}
setTimeout(updateTime,300);
}
function timerOut(a){
document.getElementById("timer").innerHTML=a;
}
</script>
<center>
<h1>
<img src="../../../../res/lmt/header.png" alt="LMT" width="525" height="110">
Guts Round
<span id="timer">(timer here)</span>
</h1>
<div class="suspense"><br><br><br>Boxes hidden for awards ceremony suspense. ;)</div>
<?php
$result = DB::queryRaw('SELECT name, guts_ans_a, (SELECT name FROM schools WHERE schools.school_id=teams.school) AS school_name, ' . '(SELECT MAX(problem_set) FROM guts WHERE team=team_id) AS current_problem, score_guts FROM teams WHERE deleted="0" ORDER BY score_guts DESC');
$n = 1;
$row = mysqli_fetch_assoc($result);
while ($row) {
$place = htmlentities($n++);
$team = htmlentities($row['name']);
$school = htmlentities($row['school_name']);
if ($school == '') {
$school = 'Individuals';
}
$score = htmlentities($row['score_guts']);
$curr = htmlentities($row['current_problem']);
if ($curr == '') {
$curr = '0';
}
if (!is_null($row['guts_ans_a'])) {
$curr = '12';
}
?>
<div class="box">
<span class="place">[<?php
echo $place;
?>
]</span>
<span class="team"><?php
echo $team;
?>
</span>
<span class="set">(<?php
echo $curr;
?>
/12)</span><br>
<span class="school"><?php
echo $school;
?>
</span>
<span class="score"><?php
echo $score;
?>
</span>
</div>
<?php
$row = mysqli_fetch_assoc($result);
}
?>
<script>
$(function(){
updateTime();
});
</script>
<?php
}
function create_score_table($id)
{
$query = 'SELECT test_scores.score AS score, tests.name AS name, tests.total_points AS total' . ' FROM test_scores' . ' INNER JOIN tests ON tests.test_id=test_scores.test_id' . ' WHERE test_scores.user_id="' . mysqli_real_escape_string(DB::get(), $id) . '"' . ' ORDER BY tests.test_id';
$result = DB::queryRaw($query);
$table = <<<HEREDOC
<table class="contrasting">
<tr>
<th>Test</th>
<th>Score</th>
<th>Total Points</th>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
while ($row) {
$table .= <<<HEREDOC
<tr>
<td>{$row['name']}</td>
<td>{$row['score']}</td>
<td>{$row['total']}</td>
</tr>
HEREDOC;
$row = mysqli_fetch_assoc($result);
}
$table .= <<<HEREDOC
</table>
HEREDOC;
return $table;
}
