function dbProblemHistory($limit, $sortname, $sortorder, $req = NULL)
{
    global $db_query_info;
    $db_query_info = array();
    if ($req == NULL) {
        $req = $_REQUEST;
    }
    $db_query_info['type'] = 'problem-history';
    $problemname = getSoft($req, "p", "");
    //which problem?
    $user = getSoft($req, "user", "");
    if ($problemname == "") {
        return __t("You must enter a non-empty problem name.");
    }
    $db_query_info['problem'] = $problemname;
    $resultdesc = array('y' => __t('Did not crash.'), 'Y' => __t('Correct!'), 'N' => __t('Incorrect.'), 'E' => __t('Internal error.'), 'S' => __t('Saved.'), 's' => __t('Saved.'));
    if (!is_user_logged_in()) {
        return __t("You must log in to view past submissions.");
    }
    if ((userIsAdmin() || userIsAssistant()) && $user != "") {
        $u = get_userdata($user);
        if ($u === false) {
            return sprintf(__t("User number %s not found."), $u);
        }
        $db_query_info['viewuser'] = $user;
    } else {
        $u = wp_get_current_user();
    }
    $uid = $u->ID;
    $uname = $u->user_login;
    global $wpdb;
    $table_name = $wpdb->prefix . "pb_submissions";
    $counts = $wpdb->get_results($wpdb->prepare("SELECT COUNT(1), COUNT(userinput) from {$table_name}\nWHERE userid = %d AND problem = %s", $uid, $problemname), ARRAY_N);
    $count = $counts[0][0];
    $showInputColumn = $counts[0][1] > 0;
    if ($count == 0) {
        return sprintf(__t('We do not have record of any submissions from user %1$s for problem %2$s.'), $uname . ' (#' . $uid . ')', $problemname);
    }
    $knownFields = array(__t("time & ID") => "beginstamp", __t("user code") => "usercode", __t("user input") => "userinput", __t("result") => "result");
    if (array_key_exists($sortname, $knownFields)) {
        $sortString = $knownFields[$sortname] . " " . $sortorder . ", ";
    } else {
        $sortString = "";
    }
    $prep = $wpdb->prepare("SELECT ID, beginstamp, usercode, userinput, result from {$table_name}\nWHERE userid = %d AND problem = %s ORDER BY {$sortString} ID DESC" . $limit, $uid, $problemname);
    $flexirows = array();
    foreach ($wpdb->get_results($prep, ARRAY_A) as $r) {
        $cell = array();
        $cell[__t('user code')] = preBox($r['usercode'], -1, -1);
        if ($showInputColumn) {
            $cell[__t('user input')] = $r['userinput'] === NULL ? '<i>' . __t('n/a') . '</i>' : preBox($r['userinput'], -1, 100000);
        }
        if ($problemname != "visualizer") {
            $cell[__t('result')] = getSoft($resultdesc, $r['result'], $r['result']);
        }
        $cell[__t('time &amp; ID')] = str_replace(' ', '<br/>', $r['beginstamp']) . '<br/>#' . $r['ID'];
        $flexirows[] = array('id' => $r['ID'], 'cell' => $cell);
    }
    return array('total' => $count, 'rows' => $flexirows);
}
<?php

// this file is used for debugging purposes, so site admins can quickly see a problem's description
require_once "include-to-load-wp.php";
global $wpdb;
$row = $wpdb->get_results($wpdb->prepare("\nSELECT * FROM " . $wpdb->prefix . "pb_problems WHERE hash = %s", getSoft($_GET, "hash", "")), ARRAY_A);
foreach ($row[0] as $field => $value) {
    if ($field == 'shortcodeArgs' || $field == 'graderArgs') {
        echo "<b>{$field}</b><br/>";
        foreach (json_decode($value) as $sf => $sv) {
            echo '---' . $sf . preBox($sv);
        }
    } else {
        echo "<b>{$field}</b><br/>" . preBox($value);
    }
}
function pbmailpage($options, $content)
{
    if (!is_user_logged_in()) {
        return __t("You must login to view the mail page.");
    }
    $v = validate();
    if ($v[0] != 'success') {
        return $v[1];
    }
    // error message
    extract($v[1]);
    // $student, $problem, $focus, $sid
    $name = nicefiedUsername($sid, FALSE);
    $r = '';
    global $wpdb;
    $students = getStudents();
    $cstudents = count($students);
    $r .= reselector($students, $cstudents);
    $r .= '<hr style="width:80%;align:center;">';
    if ($problem !== NULL) {
        $finished = $wpdb->get_var($wpdb->prepare("SELECT time FROM " . $wpdb->prefix . "pb_completed WHERE userid = %d AND problem = %s", $sid, $problem['slug']));
        $r .= '<div class="history-note">' . sprintf(__t('Mail about %1$s [%3$s] for %2$s'), $problem['publicname'], userString($sid), '<a href="' . $problem['url'] . '">' . __t('link to original page') . '</a>');
        $r .= '</div>';
        if ($finished !== NULL) {
            $r .= "<img title='" . $student->user_login . __t(" has completed this problem.") . "' src='" . UFILES . "checked.png' class='pycheck'/>";
        } else {
            $r .= "<img title='" . $student->user_login . __t(" has not completed this problem.") . "' src='" . UFILES . "icon.png' class='pycheck'/>";
        }
        if ($finished !== NULL) {
            $r .= "<div class='history-prenote'>" . sprintf(__t('Note: this student completed the problem at %s'), $finished) . "</div>";
        }
        $r .= '<i>' . __t('Click on a message title to toggle the message open or closed.') . '</i>';
        global $mailcond;
        $messages = $wpdb->get_results($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "pb_mail WHERE ustudent = %d AND problem = %s AND {$mailcond} ORDER BY ID desc", $sid, $problem['slug']), ARRAY_A);
        foreach ($messages as $i => $message) {
            $c = $message['ID'] == $focus ? " showing" : " hiding";
            $idp = $message['ID'] == $focus ? " id='m' " : '';
            $r .= "<div {$idp} class='collapseContain{$c}' style='border-radius: 5px;'>";
            $title = __t("From") . " " . nicefiedUsername($message['ufrom'], FALSE) . ' ' . __t('to') . ' ' . nicefiedUsername($message['uto'], FALSE) . ', ' . $message['time'];
            if (count($messages) > 1 && $i == 0) {
                $title .= " " . __t("(newest)");
            }
            if (count($messages) > 1 && $i == count($messages) - 1) {
                $title .= " " . __t("(oldest)");
            }
            $r .= "<div class='collapseHead'><span class='icon'></span>{$title}</div>";
            $r .= "<div class='collapseBody'><span class='quoth'>" . __t("Quote/<br/>Reply") . "</span>" . preBox($message['body'], -1, 10000, "font-size:12px; line-height:14px; white-space: pre-wrap;") . '</div>';
            $r .= '</div>';
        }
        $to = "";
        if (getUserID() == $sid) {
            $guru_login = get_the_author_meta('pbguru', get_current_user_id());
            $guru = get_user_by('login', $guru_login);
            // FALSE if does not exist
            $to .= '<div style="text-align: center">';
            $to .= __t('Send a question by e-mail to: ');
            if ($guru !== FALSE) {
                $to .= "<select class='recipient'>\n<option value='1'>" . __t("My guru") . " ({$guru_login})</option>\n<option value='-1'>" . __t("CS Circles Assistant") . "</option>\n</select></div>";
            } else {
                $to .= "<select class='recipient'>\n<option value='-1'>" . __t("CS Circles Assistant") . "</option>\n<option value='0'>" . __t("(No guru is specified)") . "</option>\n</select>";
                $to .= '<br/></div>';
            }
        }
        $r .= '<div class="pybox fixsresize mailform" id="mailform">
<div id="bodyarea" class="pyboxTextwrap">
<textarea name="body" class="resizy" placeholder="' . __t('Type here to send a reply about this problem') . '" style="width:100%; white-space: pre-wrap; font-size: 11px; line-height:13px" rows=12></textarea>
</div>
' . $to;
        if (getUserID() != $sid) {
            $r .= '<input style="position:relative; top:2px" type="checkbox" id="noreply" onclick="toggleVisibility(\'bodyarea\')">' . ' <label style="font-size:75%" for="noreply">' . __t('Just mark as read without replying') . '</label><br>';
        }
        $r .= '<button onclick="mailReply(' . $sid . ',\'' . $problem['slug'] . '\');">' . __t('Send this message!') . '</button>
</div>';
        $problemname = $problem['publicname'];
        $r .= '<hr style="width:80%;align:center;">';
        if (getUserID() != $sid) {
            $r .= "<div class='history-note'><a href='" . cscurl('progress') . '?user='******'>" . sprintf(__t("%s's progress page (new window)"), $name) . "</a></div>";
        }
        $r .= "\n<div class='collapseContain hiding'>\n<div class='collapseHead'><span class='icon'></span>" . __t("Problem description for") . " " . $problem['publicname'] . "</div>\n<div class='collapseBody'>" . apply_filters('the_content', pyBoxHandler(json_decode($problem['shortcodeArgs'], TRUE), $problem['content'])) . "</div>\n</div>";
        if (getUserID() != $sid) {
            $r .= niceFlex('us', sprintf(__t('%1$s\'s submissions for %2$s'), $name, $problemname), 'problem-history', 'dbProblemHistory', array('user' => $sid, 'p' => $problem['slug']));
        }
        $r .= niceFlex('ms', sprintf(__t("My previous submissions for %s"), $problemname), 'problem-history', 'dbProblemHistory', array('p' => $problem['slug']));
        if (getUserID() != $sid) {
            $r .= niceFlex('omp', sprintf(__t("My other messages about %s"), $problemname), 'mail', 'dbMail', array('what' => $problem['slug'], 'xwho' => $sid));
        }
        $r .= niceFlex('oms', getUserID() == $sid ? __t("My messages for other programs") : sprintf(__t("Messages to/from %s for other problems"), $name), 'mail', 'dbMail', array('who' => $sid, 'xwhat' => $problem['slug']));
    }
    if ($cstudents > 0 || userIsAssistant()) {
        $r .= niceFlex('allstu', sprintf(__t("All messages ever about %s's work"), $name), 'mail', 'dbMail', array('who' => $sid));
    }
    $r .= niceFlex('allme', __t("All messages ever to or from me"), 'mail', 'dbMail', array());
    return $r;
}
function preBoxHinted($s, $len = -1, $lenlimit = 2000, $style = '')
{
    return preBox($s, $len, $lenlimit, $style . " widepophint hintedError", true);
}
function dbEntireHistory($limit, $sortname, $sortorder, $req = NULL)
{
    global $db_query_info;
    $db_query_info = array();
    if ($req == NULL) {
        $req = $_REQUEST;
    }
    $db_query_info['type'] = 'entire-history';
    $user = getSoft($req, "user", "");
    $problem = getSoft($req, "problemhash", "");
    $resultdesc = array('y' => __t('Did not crash.'), 'Y' => __t('Correct!'), 'N' => __t('Incorrect.'), 'E' => __t('Internal error.'), 'S' => __t('Saved.'), 's' => __t('Saved.'));
    global $current_user;
    get_currentuserinfo();
    global $wpdb;
    if (!is_user_logged_in()) {
        return __t("You must log in to view past submissions.");
    }
    if ($user == "all") {
        $u = "all";
    } elseif ($user == "") {
        $u = $current_user;
    } elseif (userIsAdmin() || userIsAssistant()) {
        $u = get_userdata($user);
        if ($u === false) {
            return __t("User number not found.");
        }
    } else {
        $u = get_userdata($user);
        if ($u === false) {
            return __t("User number not found.");
        }
        if (strcasecmp(get_user_meta($user, 'pbguru', true), $current_user->user_login) != 0) {
            return sprintf(__t("User %s does not have you as their guru."), $user);
        }
    }
    if ($user != "") {
        $db_query_info['viewuser'] = $user;
    }
    // make an associative array indexed by slug
    $problemTable = $wpdb->get_results("SELECT slug, publicname, url FROM " . $wpdb->prefix . "pb_problems WHERE slug IS NOT NULL AND lang = '" . currLang2() . "'", OBJECT_K);
    $whereProblem = "1";
    if ($problem != '') {
        if (!array_key_exists($problem, $problemTable)) {
            return sprintf(__t("Problem %s is unknown."), $problem);
        }
        $whereProblem = $wpdb->prepare("problem = %s", $problem);
    }
    $db_query_info['problem'] = $problem;
    $knownFields = array(__t("userid") => "userid", __t("time &amp; ID") => "beginstamp", __t("problem") => "problem", __t("user code") => "usercode", __t("user input") => "userinput", __t("result") => "result");
    if (array_key_exists($sortname, $knownFields)) {
        $sortString = $knownFields[$sortname] . " " . $sortorder . ", ";
    } else {
        $sortString = "";
    }
    $whereStudent = NULL;
    if ($u == "all") {
        $whereStudent = userIsAdmin() ? "1" : "userid in " . getStudentList();
    } else {
        $uid = $u->ID;
        $whereStudent = $wpdb->prepare("userid = %d", $uid);
    }
    $count = $wpdb->get_var("\nSELECT COUNT(1)\nFROM " . $wpdb->prefix . "pb_submissions \nWHERE {$whereStudent} AND {$whereProblem}");
    if ($count == 0) {
        return __t("We do not have record of any submissions.");
    }
    $prep = "\nSELECT userid, ID, beginstamp, usercode, userinput, result, problem\nFROM " . $wpdb->prefix . "pb_submissions \nWHERE {$whereStudent} AND {$whereProblem}\nORDER BY {$sortString} ID DESC " . $limit;
    $flexirows = array();
    foreach ($wpdb->get_results($prep, ARRAY_A) as $r) {
        $cell = array();
        if ($u == "all") {
            $cell[__t('userid')] = str_replace(' ', "<br>", userString($r['userid'], true));
        }
        $p = $r['problem'];
        if (array_key_exists($p, $problemTable)) {
            $cell[__t('problem')] = '<a class="open-same-window" href="' . $problemTable[$p]->url . '">' . $problemTable[$p]->publicname . '</a>';
        } else {
            $cell[__t('problem')] = $p;
        }
        $cell[__t('user code')] = preBox($r['usercode'], -1, -1);
        $cell[__t('user input')] = $r['userinput'] == NULL ? '<i>' . __t('n/a') . '</i>' : preBox($r['userinput'], -1, 100000);
        if ($p != 'visualizer' && $p != 'visualizer-iframe') {
            $cell[__t('result')] = getSoft($resultdesc, $r['result'], '???');
        } else {
            $cell[__t('result')] = '<i>n/a</i>';
        }
        $cell[__t('time &amp; ID')] = str_replace(' ', '<br/>', $r['beginstamp']) . '<br/>#' . $r['ID'];
        $flexirows[] = array('id' => $r['ID'], 'cell' => $cell);
    }
    return array('total' => $count, 'rows' => $flexirows);
}
function pberror($errmsg)
{
    pyboxlog("[pyBoxHandler] " . $errmsg);
    return "<b>" . sprintf(__t("Internal error, details below; please <a href='%s'>contact staff</a>."), cscurl('contact')) . "</b> " . __t("Timestamp:") . " " . date("y-m-d H:i:s", time()) . "<br/>" . preBox($errmsg);
}
function dbMail($limit, $sortname, $sortorder, $req = NULL)
{
    global $db_query_info;
    $db_query_info = array();
    $who = getSoft($req === NULL ? $_REQUEST : $req, "who", "");
    $xwho = getSoft($req === NULL ? $_REQUEST : $req, "xwho", "");
    $what = getSoft($req === NULL ? $_REQUEST : $req, "what", "");
    $xwhat = getSoft($req === NULL ? $_REQUEST : $req, "xwhat", "");
    $unans = getSoft($req === NULL ? $_REQUEST : $req, "unans", "");
    $db_query_info['type'] = 'mail-history';
    $db_query_info['who'] = $who;
    $db_query_info['xwho'] = $xwho;
    $db_query_info['what'] = $what;
    $db_query_info['xwhat'] = $xwhat;
    $db_query_info['unans'] = $unans;
    if (!is_user_logged_in()) {
        return __t("You must log in to view past mail.");
    }
    $where = 'WHERE 1';
    if (userIsAdmin()) {
        $where .= ' AND (uto = ' . getUserID() . ' OR uto = 0 OR ufrom = ' . getUserID() . ' OR ufrom = 0)';
    } else {
        $students = getStudents();
        $students[] = getUserID();
        $where .= ' AND (ustudent IN (' . implode(',', $students) . ') OR uto = ' . getUserID() . ' OR ufrom = ' . getUserID() . ' )';
    }
    if ($who != '') {
        if (!is_numeric($who)) {
            return sprintf(__t("%s must be numeric."), "'who'");
        }
        $who = (int) $who;
        if (userIsAdmin() || getUserID() == $who || getUserID() == guruIDID($who) || userIsAssistant()) {
            $where .= ' AND ustudent = ' . $who;
        } else {
            return __t("Access denied.");
        }
    } else {
        if ($xwho != '') {
            if (!is_numeric($xwho)) {
                return sprintf(__t("%s must be numeric."), "'xwho'");
            }
            $xwho = (int) $xwho;
            $where .= ' AND ustudent != ' . $xwho;
        }
    }
    if ($unans != '') {
        if (!is_numeric($unans)) {
            return sprintf(__t("%s must be numeric."), "'unans'");
        }
        $unans = (int) $unans;
        $where .= ' AND unanswered = ' . $unans;
    }
    global $wpdb;
    if ($what != '') {
        $where .= $wpdb->prepare(' AND problem = %s', $what);
    }
    if ($xwhat != '') {
        $where .= $wpdb->prepare(' AND problem != %s', $xwhat);
    }
    $table_name = $wpdb->prefix . "pb_mail";
    $knownFields = array(__t("from") => "ufrom", __t("to") => "uto", __t("when") => "time", __t("message") => "body", __t("problem") => "problem", __t("replied?") => "unanswered");
    $sortString = array_key_exists($sortname, $knownFields) ? $knownFields[$sortname] . " " . $sortorder . ", " : "";
    $count = $wpdb->get_var("SELECT COUNT(1) from {$table_name} {$where}");
    $prep = "SELECT * from {$table_name} {$where} ORDER BY {$sortString} ID DESC" . $limit;
    //   pyboxlog($prep);
    $flexirows = array();
    foreach ($wpdb->get_results($prep, ARRAY_A) as $r) {
        $cell = array();
        $cell[__t('from')] = nicefiedUsername($r['ufrom']);
        $cell[__t('to')] = nicefiedUsername($r['uto']);
        $url = cscurl('mail') . "?who=" . $r['ustudent'] . "&what=" . $r['problem'] . "&which=" . $r['ID'] . "#m";
        $cell[__t('when')] = str_replace(' ', '<br>', $r['time']);
        if ($what == '') {
            $cell[__t('problem')] = $r['problem'];
        }
        if ($unans == '') {
            $cell[__t('replied?')] = $r['unanswered'] == 1 ? __t('no') : __t('yes');
        }
        $cell[__t('message')] = "<a href='{$url}'>" . preBox($r['body']) . "</a>";
        $flexirows[] = array('id' => $r['ID'], 'cell' => $cell);
    }
    return array('total' => $count, 'rows' => $flexirows);
}
function merror($message, $errmsg, $suppress = -1)
{
    global $beginstamp;
    pyboxlog("[main error] " . $errmsg . " (partial message: " . $message . ")", $suppress);
    return "E" . "<b>Internal error or HTTP error, details below. " . "You can <a href=\"" . cscurl('contact') . "\">contact us</a>." . "</b> Timestamp: " . date("y-m-d H:i:s", $beginstamp) . "<br/>" . preBox($errmsg);
}