function notifyDraftOrder($leagueID)
{
debug("notifydraftorder.. hello");
$order = getDraftOrder($leagueID);
$season = getSeason();
$sql = "\r\r\n\tSELECT u.email\r\r\n\t\tFROM fch_users u, fch_league_membership m\r\r\n\t\tWHERE u.id = m.userID\r\r\n\t\tAND season = \"{$season}\"\r\r\n\t\tAND m.leagueID = {$leagueID}\r\r\n\t";
debug("notifyDraftOrder: {$sql}");
$draftTime = getSingleton("fch_leagues", "draftTime", " WHERE id = {$leagueID} and season = \"{$season}\"");
$leagueName = getSingleton("fch_leagues", "display_name", " WHERE id = {$leagueID} and season = \"{$season}\"");
$body = <<<BODY
\tHello,
\tThis is an automated notification from Fantasy College Hockey.
\tYou've joined a league - {$leagueName}. The registration deadline has passed, and we've generated a draft order. Here it is:
\t{$order}
\t
\tNote: The draft will occur in order for the first round, then in reverse order for the 2nd round, and vice versa. For example if there are 3 people in your draft, it'll go pick 1-2-3-3-2-1-1-2-3 and so on.
\t
\tYour draft is scheduled for {$draftTime} EST
\t
\tHave a great season and feel free to contact us at fantasycollegehockey@gmail.com.
BODY;
$con = initializeDB();
debug("notifyDraftOrder: {$sql}");
$result = $con->query($sql);
if ($result->num_rows > 0) {
// output data of each row
$order = 1;
while ($row = $result->fetch_assoc()) {
sendEmail($row[email], "Your FCH Draft is Coming! Here's Your Draft Order", $body);
debug("EMAIL: " . $row[email] . " Body: " . $body);
}
} else {
return "";
}
}
debug("In only one league");
if (intval(selectCount("fch_league_membership", " WHERE userID = $userID and season = \"$season\"")) == 1)
{
debug("In only one league. Setting default membership.");
$thisLeague = getSingleton("fch_league_membership","leagueID"," WHERE season = \"$season\" and userID = $userID");
debug("Setting default league for user $userID to $thisLeague");
$sql = "
UPDATE fch_user_preferences SET displayLeague = $thisLeague
WHERE userID = $userID";
executeGenericSql($sql);
}
}
*/
debug("dbLeague " . $dbLeague . " - userID: " . $userID);
if ($leagueCount > 1) {
$con = initializeDB();
$sql = "SELECT m.leagueID AS leagueID, m.teamDisplayName AS teamDisplayName, l.display_name AS leagueDisplayName FROM fch_league_membership m, fch_leagues l WHERE l.id = m.leagueID and m.userID = {$userID} and m.season = \"{$season}\"";
debug($sql);
$result = $con->query($sql);
//echo "debug ". isDebug();
$leagueSelector .= "<form name = \"leagueSelector\" method = \"POST\"><SELECT name = \"league\">";
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
$selectLeague = "";
debug("<B>Checking {$dbLeague}</B> = " . $row["leagueID"]);
if ($row["leagueID"] == $dbLeague) {
$usingAs = "";
//Now viewing your team in " . $row['leagueDisplayName'];
$selectedLeagueID = $row["leagueID"];
$selectLeague = "SELECTED";
<?php
$action = "";
if (isset($_GET['action'])) {
$action = $_GET['action'];
}
if (isset($_POST['action'])) {
$action = $_POST['action'];
}
try {
if (initializeDB() == true) {
doAction($action);
}
} catch (Exception $e) {
if (strcmp($e->getMessage(), "table.initialization.error") == 0) {
$forwardaction = "";
include 'installscript.php';
doAction($forwardaction);
} else {
addError($e->getMessage());
doAction("install");
}
}
<?php
include 'util.php';
header("Pragma: public");
header("Expires: 0");
$filename = 'db.php';
$templine = '';
initializeDB();
if (!file_exists($filename)) {
addError("install.file.missing");
outputJSON($errormsg);
} else {
$lines = file($filename);
foreach ($lines as $line_num => $line) {
if (substr($line, 0, 2) != '--' && $line != '') {
$templine .= $line;
if (substr(trim($line), -1, 1) == ';') {
try {
queryDB($templine);
} catch (Exception $e) {
addError("error", $e->getMessage(), false);
debug_display($e);
}
$templine = '';
}
}
}
if (getErrorCount() > 1) {
addError("error", "install.sql.error");
outputJSON("error");
}
function sqlRefresh()
{
$season = getSeason();
/// /////////////////////////////////////////////////
/// TRUNCATE SCHEDULE
$sql = <<<SQL
\t\ttruncate table fanta66_joomla.fch_schedule;
SQL;
$scheduleTruncateResults = executeGenericSQL($sql);
/// /////////////////////////////////////////////////
/// REBUILD SCHEDULE
$sql = <<<SQL
\t\tinsert into fanta66_joomla.fch_schedule
\t\t(dateStamp,visitor,home)
\t\tselect date_stamp,visitor,home from fanta66_zudnicfchmaster.schedule_master;
SQL;
$scheduleResults = executeGenericSQL($sql);
/// /////////////////////////////////////////////////
/// TRUNCATE SCHEDULE
$sql = <<<SQL
\t\ttruncate table fanta66_joomla.fch_schedule_lastseason;
SQL;
$scheduleTruncateResultsLY = executeGenericSQL($sql);
/// /////////////////////////////////////////////////
/// REBUILD SCHEDULE
$sql = <<<SQL
\t\tinsert into fanta66_joomla.fch_schedule_lastseason
\t\t(dateStamp,visitor,home)
\t\tselect date_stamp,visitor,home from fanta66_zudnicfchmaster.schedule_lastseason;
SQL;
$scheduleResultsLY = executeGenericSQL($sql);
/// /////////////////////////////////////////////////
/// TRUNCATE RESULTS
$sql = <<<SQL
\t\tDELETE FROM fanta66_joomla.fch_consolidated_results where season = "{$season}";
SQL;
$out .= "<HR><B>Truncate Results SQL: </B><br/>{$sql}<p/>\n";
$truncateResultsResults = executeGenericSQL($sql);
/// /////////////////////////////////////////////////
/// REBUILD RESULTS - SKATERS
$sql = <<<SQL
insert into fanta66_joomla.fch_consolidated_results
(dateStamp
,totalPoints
,season
,playerID
,schoolID
,opponent
,opponentDisplayName
,playerDisplayName
,position
,schoolDisplayName
,statLine
,dateLine
,atVs)
SELECT DISTINCT
\t`fanta66_zudnicfchmaster`.`results_skaters`.`date_stamp` AS `date_stamp`
\t,`fanta66_zudnicfchmaster`.`results_skaters`.`total_points` AS `totalPoints`
\t,convert(CONCAT (
\t\t\tsubstr(`fanta66_zudnicfchmaster`.`results_skaters`.`season`, 1, 4)
\t\t\t,"-"
\t\t\t,substr(`fanta66_zudnicfchmaster`.`results_skaters`.`season`, 5, 4)
\t\t\t) using utf8) AS `season`
\t,replace(replace(replace(replace(replace(`fanta66_zudnicfchmaster`.`results_skaters`.`player`, "'", ""), "-", ""), ".", ""), " ", ""), "~", "") AS `playerID`
\t,`fanta66_zudnicfchmaster`.`player_master`.`school_id` AS `schoolID`
\t,
IF (
\t\t(`fanta66_zudnicfchmaster`.`player_master`.`school_id` = `fanta66_zudnicfchmaster`.`results_skaters`.`visitor`)
\t\t,`fanta66_zudnicfchmaster`.`results_skaters`.`home`
\t\t,`fanta66_zudnicfchmaster`.`results_skaters`.`visitor`
\t\t) AS `opponent`
\t,`fanta66_zudnicfchmaster`.`school_master`.`school_shortname` AS `opponentDisplayName`
\t,CONCAT (
\t\t`fanta66_zudnicfchmaster`.`player_master`.`first_name`
\t\t," "
\t\t,`fanta66_zudnicfchmaster`.`player_master`.`last_name`
\t\t,", "
\t\t,`fanta66_zudnicfchmaster`.`player_master`.`school_name`
\t\t) AS `playerDisplayName`
\t,`fanta66_zudnicfchmaster`.`player_master`.`position` as position
\t,`fanta66_zudnicfchmaster`.`player_master`.`school_name` as schoolDisplayName
\t,CONCAT (
\t\tconvert(IF (
\t\t\t\t(1 > `fanta66_zudnicfchmaster`.`results_skaters`.`goals`)
\t\t\t\t,"0"
\t\t\t\t,`fanta66_zudnicfchmaster`.`results_skaters`.`goals`
\t\t\t\t) using latin1 )
\t\t\t," G, "
\t\t\t,convert(IF (
\t\t\t\t\t(1 > `fanta66_zudnicfchmaster`.`results_skaters`.`assists`)
\t\t\t\t\t,"0"
\t\t\t\t\t,`fanta66_zudnicfchmaster`.`results_skaters`.`assists`
\t\t\t\t\t) using latin1 )
\t\t\t\t," A "
\t\t\t\t,`fanta66_zudnicfchmaster`.`results_skaters`.`info` ) AS `statLine`
\t\t\t\t,CONCAT (convert(date_format(`fanta66_zudnicfchmaster`.`results_skaters`.`date_stamp`, "%c/%e") using latin1)) AS `dateLine`
\t\t\t\t,convert(IF (
\t\t\t\t\t\t(`fanta66_zudnicfchmaster`.`player_master`.`school_id` = `fanta66_zudnicfchmaster`.`results_skaters`.`visitor`)
\t\t\t\t\t\t," at "
\t\t\t\t\t\t," vs "
\t\t\t\t\t\t) using latin1 ) AS `atVs` FROM (
\t\t\t\t\t(
\t\t\t\t\t\t`fanta66_zudnicfchmaster`.`results_skaters` JOIN `fanta66_zudnicfchmaster`.`player_master`
\t\t\t\t\t\t) JOIN `fanta66_zudnicfchmaster`.`school_master`
\t\t\t\t\t) WHERE (
\t\t\t\t\t\t(`fanta66_zudnicfchmaster`.`player_master`.`text_id` = `fanta66_zudnicfchmaster`.`results_skaters`.`player`)
\t\t\t\t\t\tAND (
\t\t\t\t\t\t\t`fanta66_zudnicfchmaster`.`school_master`.`school_id` = IF (
\t\t\t\t\t\t\t\t(`fanta66_zudnicfchmaster`.`player_master`.`school_id` = `fanta66_zudnicfchmaster`.`results_skaters`.`visitor`)
\t\t\t\t\t\t\t\t,`fanta66_zudnicfchmaster`.`results_skaters`.`home`
\t\t\t\t\t\t\t\t,`fanta66_zudnicfchmaster`.`results_skaters`.`visitor`
\t\t\t\t\t\t\t\t) ) ) ORDER BY `fanta66_zudnicfchmaster`.`results_skaters`.`date_stamp` DESC;
SQL;
$skaterInsertResults = executeGenericSQL($sql);
$out .= "<HR><B>Skater Results SQL: </B><br/>{$sql}<p/>\n";
/////////////////////////////////////////
// GOALIE RESULTS
$sql = <<<SQL
\tinsert into fanta66_joomla.fch_consolidated_results
(dateStamp
,totalPoints
,season
,playerID
,schoolID
,opponent
,opponentDisplayName
,playerDisplayName
,position
,schoolDisplayName
,statLine
,dateLine
,atVs)
\tSELECT DISTINCT `fanta66_zudnicfchmaster`.`results_goalies`.`date_stamp` AS `date_stamp`
\t,`fanta66_zudnicfchmaster`.`results_goalies`.`points` AS `total_points`
\t,convert(CONCAT (
\t\t\tsubstr(`fanta66_zudnicfchmaster`.`results_goalies`.`season`, 1, 4)
\t\t\t,"-"
\t\t\t,substr(`fanta66_zudnicfchmaster`.`results_goalies`.`season`, 5, 4)
\t\t\t) using utf8) AS `season`
\t,replace(replace(replace(replace(replace(`fanta66_zudnicfchmaster`.`results_goalies`.`player`, "'", ""), "-", ""), ".", ""), " ", ""), "~", "") AS `playerID`
\t,`fanta66_zudnicfchmaster`.`player_master`.`school_id` AS `schoolID`
\t
,IF (
\t\t(`fanta66_zudnicfchmaster`.`player_master`.`school_id` = `fanta66_zudnicfchmaster`.`results_goalies`.`visitor`)
\t\t,`fanta66_zudnicfchmaster`.`results_goalies`.`home`
\t\t,`fanta66_zudnicfchmaster`.`results_goalies`.`visitor`
\t\t) AS `opponent`
\t,`fanta66_zudnicfchmaster`.`school_master`.`school_shortname` AS `opponentDisplayName`
\t,CONCAT (
\t\t`fanta66_zudnicfchmaster`.`player_master`.`first_name`
\t\t," "
\t\t,`fanta66_zudnicfchmaster`.`player_master`.`last_name`
\t\t, ", "
\t\t,`fanta66_zudnicfchmaster`.`player_master`.`school_name`
\t\t) AS `playerDisplayName`
\t,`fanta66_zudnicfchmaster`.`player_master`.`position` AS position
\t,`fanta66_zudnicfchmaster`.`player_master`.`school_name` as schoolDisplayName
\t,CONCAT (
\t\t`fanta66_zudnicfchmaster`.`results_goalies`.`goals_allowed`
\t\t," GA, "
\t\t,`fanta66_zudnicfchmaster`.`results_goalies`.`saves`
\t\t," saves, "
\t\t,round(`fanta66_zudnicfchmaster`.`results_goalies`.`gaa`, 3)
\t\t," GAA, "
\t\t,`fanta66_zudnicfchmaster`.`results_goalies`.`decision`
\t\t) AS `statLine`
\t,CONCAT (convert(date_format(`fanta66_zudnicfchmaster`.`results_goalies`.`date_stamp`, "%c/%e") using latin1)) AS `dateLine`
\t,convert(IF (
\t\t\t(`fanta66_zudnicfchmaster`.`player_master`.`school_id` = `fanta66_zudnicfchmaster`.`results_goalies`.`visitor`)
\t\t\t," at "
\t\t\t," vs "
\t\t\t) using latin1 ) AS `atVs` FROM (
\t\t(
\t\t\t`fanta66_zudnicfchmaster`.`results_goalies` JOIN `fanta66_zudnicfchmaster`.`player_master`
\t\t\t) JOIN `fanta66_zudnicfchmaster`.`school_master`
\t\t) WHERE (
\t\t\t(`fanta66_zudnicfchmaster`.`player_master`.`text_id` = `fanta66_zudnicfchmaster`.`results_goalies`.`player`)
\t\t\tAND (
\t\t\t\t`fanta66_zudnicfchmaster`.`school_master`.`school_id` = IF (
\t\t\t\t\t(`fanta66_zudnicfchmaster`.`player_master`.`school_id` = `fanta66_zudnicfchmaster`.`results_goalies`.`visitor`)
\t\t\t\t\t,`fanta66_zudnicfchmaster`.`results_goalies`.`home`
\t\t\t\t\t,`fanta66_zudnicfchmaster`.`results_goalies`.`visitor`
\t\t\t\t\t) ) ) ORDER BY `fanta66_zudnicfchmaster`.`results_goalies`.`date_stamp` DESC;
SQL;
$goalieInsertResults = executeGenericSQL($sql);
$out .= "<HR><B>Goalie Results SQL: </B><br/>{$sql}<p/>\n";
$out .= "Skater Results Insert: " . $skaterInsertResults["msg"] . "\n<br/>";
$out .= "Goalie Results Insert: " . $goalieInsertResults["msg"] . "\n<br/>";
$playerTruncateResults = executeGenericSQL("truncate table fanta66_joomla.fch_players");
$out .= "Player Truncate: " . $playerTruncateResults["msg"] . "\n";
$sql = <<<SQL
\tinsert into fanta66_joomla.fch_players
(playerID, lastName, firstName, schoolDisplayName, schoolID, playerYear, position, season)
select distinct
\treplace(replace(replace(replace(replace(`fanta66_zudnicfchmaster`.`player_master`.`text_id`, "'", ""), "-", ""), ".", ""), " ", ""), "~", "") AS `playerID`
\t,`fanta66_zudnicfchmaster`.`player_master`.`last_name` as lastName
\t,`fanta66_zudnicfchmaster`.`player_master`.`first_name` as firstName
\t,`fanta66_zudnicfchmaster`.`player_master`.`school_name` as schoolDisplayName
\t, `fanta66_zudnicfchmaster`.`player_master`.`school_id` as schoolID
\t, `fanta66_zudnicfchmaster`.`player_master`.`year_in_school` as playerYear
\t, `fanta66_zudnicfchmaster`.`player_master`.`position` as position
\t, convert(CONCAT (
\t\t\tsubstr(`fanta66_zudnicfchmaster`.`player_master`.`season`, 1, 4)
\t\t\t,"-"
\t\t\t,substr(`fanta66_zudnicfchmaster`.`player_master`.`season`, 5, 4)
\t\t\t) using utf8) AS `season`
\tfrom `fanta66_zudnicfchmaster`.`player_master`
\twhere `fanta66_zudnicfchmaster`.`player_master`.`test` = "prod"
SQL;
$sql .= " and season = \"" . preg_replace("/[^A-Za-z0-9 ]/", '', getSeason()) . "\";";
$playerInsertResults = executeGenericSQL($sql);
$out .= "Player refresh insert: " . $playerInsertResults["msg"] . "\n";
echo "Full Insert playerInsertResults: {$sql}<p>\n\n";
$lastSeason = preg_replace("/[^A-Za-z0-9 ]/", '', getLastSeason());
$season = getSeason();
$sql = <<<SQL
\tinsert into fanta66_joomla.fch_players
(playerID, lastName, firstName, schoolDisplayName, schoolID, playerYear, position, season)
select distinct
\treplace(replace(replace(replace(replace(`fanta66_zudnicfchmaster`.`player_master`.`text_id`, "'", ""), "-", ""), ".", ""), " ", ""), "~", "") AS `playerID`
\t,`fanta66_zudnicfchmaster`.`player_master`.`last_name` as lastName
\t,`fanta66_zudnicfchmaster`.`player_master`.`first_name` as firstName
\t,`fanta66_zudnicfchmaster`.`player_master`.`school_name` as schoolDisplayName
\t, `fanta66_zudnicfchmaster`.`player_master`.`school_id` as schoolID
\t, "?" as playerYear
\t, `fanta66_zudnicfchmaster`.`player_master`.`position` as position
\t, {$season} AS `season`
\tfrom `fanta66_zudnicfchmaster`.`player_master`
\twhere `fanta66_zudnicfchmaster`.`player_master`.`year_in_school` != "Sr"
SQL;
$sql .= " and `fanta66_zudnicfchmaster`.`player_master`.`season` = \"{$lastSeason}\" ";
$sql .= " and `fanta66_zudnicfchmaster`.`player_master`.`school_id` not in (select distinct schoolID from fanta66_joomla.fch_players where season = \"" . getSeason() . "\");";
SQL;
echo "Delta Insert: {$sql}<BR>\n\n";
$deltaInsertResults = executeGenericSQL($sql);
$out .= "Player delta insert: " . $deltaInsertResults["msg"] . "\n";
$deltaUpdateResults = executeGenericSQL("UPDATE fch_players SET season = \"" . getSeason() . "\", playerYear = NULL where playerYear = \"?\"");
$out .= "Player delta update: " . $deltaInsertResults["msg"] . "\n";
// Draft times are in eastern time. No offset.
$sql = "SELECT u.email as email, DATE_FORMAT(l.draftTime ,'%W, %b %d %h:%i %p, Eastern time') as draftTime, l.id as id\r\r\n\tFROM fch_users u, fch_leagues l, fch_league_membership lm\r\r\n\tWHERE l.draftTime > DATE_ADD( NOW( ) , INTERVAL +24 HOUR ) \r\r\n\tAND l.draftTime < DATE_ADD( NOW( ) , INTERVAL +48 HOUR ) \r\r\n\tAND lm.leagueID = l.id\r\r\n\tAND l.draftReminderSent != TRUE\r\r\n\tAND lm.userID = u.id";
/*
$sql = "
SELECT u.email AS email, DATE_FORMAT( l.draftTime, '%W, %b %d %h:%i %p, Eastern time' ), l.id AS draftTime
FROM fch_users u, fch_leagues l, fch_league_membership lm
WHERE lm.leagueID = l.id
AND lm.userID = u.id
AND l.id =35";
*/
$con = initializeDB();
$result = $con->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
$body = "Hi,\n";
$body .= "This is a reminder that your Fantasy College Hockey draft is tomorrow, " . $row[draftTime] . ".\n\n";
$body .= "The draft is live on fantasycollegehockey.com. The draft is available in the League menu and includes a live chat.\n\n";
$body .= "One hour prior to draft time, the system will close new registrations to the league and generate the draft order. The order will be e-mailed to you.\n\n";
$body .= "Your league administrator will need to open the draft. When the draft is over, the administrator will close the draft and your drafted players will be moved onto your roster.\n\n";
$body .= "** DO NOT REPLY ** to this email. Please contact us at fantasycollegehockey@gmail.com if you have questions.\n\n";
$body .= "Have fun!\n";
$updateDraftReminder = "UPDATE fch_leagues SET draftReminderSent = TRUE WHERE id = " . $row[id];
$out .= "<HR>Draft Reminder, SQL: {$updateDraftReminder}";
executeGenericSQL($updateDraftReminder);
sendEmail($row[email], "Reminder: Your Draft Is Tomorrow", $body);
$emailSentTo .= $row[email] . ", ";
}
}
$truncateByTeamResult = executeGenericSql("DELETE FROM fanta66_joomla.fch_schedule_byteam");
$out .= "<hr/>Delete all from fch_scheduleByTeam: " . $truncateByTeamResult["msg"];
$sql = "\r\r\n\t\tinsert into fch_schedule_byteam (id,dateStamp,schoolID,atVs,opponent) \r\r\n\t\t(select NULL, dateStamp, home, \"vs\" , visitor from fch_schedule)\r\r\n\t\tunion all\r\r\n\t\t(select NULL, dateStamp, visitor as schoolID, \"at\" as atVs, home as opponent from fch_schedule)";
$rebuildByTeamResult = executeGenericSql($sql);
$out .= "<hr>Rebuild fch_scheduleByTeam: <p/>\n{$sql}<p/>\nResult:" . $rebuildByTeamResult["msg"];
closeDB($con);
$out .= "<p/>\n\tEmail sent to: {$emailSentTo}";
sendEmail("*****@*****.**", "FCH Cron Results", $out);
echo $out;
}
if ($transactionResult === true) {
echo "Successful insert";
} else {
echo "Failed insert";
}
}
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
//If the client is asking for marks made by a user, find what they're looking for and return it
try {
$username = $_GET['username'];
$externalID = getExternalDocId($_GET['documentID']);
//DB connection
$db = new PDO('sqlite:../annotations.db');
// Set the PDO adapter to throw exceptions if something goes wrong
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
initializeDB($db);
//For demo purposes, insert at least one annotation into the database
$count = $db->exec("Select count(*) from annotation");
// In this sample, each may only have a single set of annotations per unique document ID
$getAnnotationStmt = "select annotations from annotation as a join document as d on a.documentID = d.id where a.username = ? and d.docID = ?";
$getAnnotation = $db->prepare($getAnnotationStmt);
$getAnnotation->execute(array($username, $externalID));
$results = $getAnnotation->fetchAll(PDO::FETCH_ASSOC);
//die(var_dump($results[0]["annotations"]));
if (count($results) > 0) {
//Prep the result
$annotationList = '[';
$annotationList = $results[0]["annotations"];
//Terminate the JSON
//$annotationList .= ']';
//Return the result to the client
function getRawGMList($leagueID)
{
$season = getSeason();
$sql = "select teamDisplayName from fch_league_membership where leagueID = {$leagueID} and status = \"active\" and season = \"{$season}\" ORDER BY teamDisplayName ASC";
$con = initializeDB();
$result = $con->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
$out .= $row["teamDisplayName"] . "<br/>";
}
}
closeDB($con);
return $out;
}