Example #1
0
$query = "CREATE TRIGGER bi_grade_response BEFORE INSERT ON player_responses\nFOR EACH ROW SET NEW.grade = grade_response(NEW.clue_id, NEW.response_text);";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE TRIGGER bu_grade_response BEFORE UPDATE ON player_responses\nFOR EACH ROW SET NEW.grade = grade_response(NEW.clue_id, NEW.response_text);";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE TRIGGER ai_grade_all_responses AFTER INSERT ON responses\nFOR EACH ROW CALL grade_all_responses(NEW.clue_id);";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE TRIGGER au_grade_all_responses AFTER UPDATE ON responses\nFOR EACH ROW\nBEGIN\n    CALL grade_all_responses(OLD.clue_id);\n    IF NEW.clue_id != OLD.clue_id THEN\n        CALL grade_all_responses(NEW.clue_id);\n    END IF;\nEND;";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE TRIGGER ad_grade_all_responses AFTER DELETE ON responses\nFOR EACH ROW CALL grade_all_responses(OLD.clue_id);";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE VIEW scores AS SELECT player_id, clue_id,\n  categories.id AS category_id, days.id AS day_id, rounds.id AS round_id,\n  IF(grade IS NULL, 0, IF(grade=1, point_value, wrong_point_value)) AS score,\n  IF(grade IS NULL AND response_text IS NOT NULL AND response_text != '', 1, 0)\n  AS ungraded FROM player_responses\n  INNER JOIN clues ON clue_id = clues.id\n  LEFT JOIN categories ON clues.category_id = categories.id\n  LEFT JOIN days ON categories.day_id = days.id LEFT JOIN\n  rounds ON days.round_id = rounds.id;";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE VIEW category_scores AS\n  SELECT player_id, category_id, day_id, round_id, SUM(score) AS category_score,\n  SUM(ungraded) AS category_ungraded\n  FROM scores GROUP BY player_id, category_id;";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE VIEW daily_scores AS\n  SELECT player_id, day_id, round_id, SUM(score) AS daily_score,\n  SUM(ungraded) AS daily_ungraded,\n  1 AS days_played FROM scores\n  GROUP BY player_id, day_id;";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE VIEW round_scores AS\n  SELECT player_id, round_id, SUM(score) AS round_score,\n  SUM(ungraded) AS round_ungraded,\n  COUNT(DISTINCT day_id) AS days_played FROM scores\n  GROUP BY player_id, round_id;";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
$query = "CREATE VIEW overall_scores AS\n  SELECT player_id, SUM(score) AS overall_score,\n  SUM(ungraded) AS overall_ungraded,\n  COUNT(DISTINCT day_id) AS days_played FROM scores\n  INNER JOIN rounds ON scores.round_id = rounds.id\n  WHERE rounds.is_regular = 1\n  GROUP BY player_id;";
$mysqli->query($query) or die(sprintf("Error executing query {$query}: %s", $mysqli->error));
Database::add_toc_berth('Champ', 'Previous SHC champion');
Database::add_toc_berth('WC', 'Wildcard');
Database::add_toc_berth('K/T', 'Kids/Teens wildcard');
Database::add_toc_berth('College', 'College wildcard');
Database::add_toc_berth('Rookie', 'Rookie wildcard');
echo "<!DOCTYPE html>\n<HTML><HEAD></HEAD><BODY><h1>Database successfully created!\n</h1></BODY></HTML>";
$mysqli->close();
echo copyright();
?>
</body>
Example #2
0
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*/
require_once 'common.inc';
startpage(RESTRICTED);
$name = $_POST['newroundname'];
$seq = null;
$is_regular = $_POST['newroundregular'] == 'yes' ? 1 : 0;
try {
    Database::add_round($name, $seq, $is_regular);
    $abbr = $_POST['newroundtocname'];
    Database::add_toc_berth($abbr, $name . ' winner');
    $title = "Round successfully added";
    $message = "<p>Round successfully added!</p>";
} catch (Exception $e) {
    $title = "Error adding round";
    $message = sprintf("<p class=\"error\">Error adding round: %s</p>", $e->getMessage());
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> 
<link rel="stylesheet" type="text/css" href="theme.css" />
<link rel="icon" type="image/png" href="shcicon.png" />
<title><?php 
echo $title;