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; }
require_once "fch-lib.php"; require_once "fch-lib.js"; $userID = getUserID(); $leagueID = leagueHandler($_POST, $userID); debug("User is {$userID} League is {$leagueID}"); $season = getSeason(); $app = JFactory::getApplication(); $teamDisplayName = getSingleton("fch_league_membership", "teamDisplayName", " WHERE userID = {$userID} and leagueID = {$leagueID} and season = \"{$season}\""); debug("Team display Name: {$teamDisplayName}"); $menu =& JSite::getMenu(); $active = $menu->getActive(); $menuname = $active->params->get('page_heading'); echo $menuname; $thisSeason = getSeason(); $lastSeason = getLastSeason(); $thisSeasonNoDash = preg_replace("/[^A-Za-z0-9 ]/", '', $thisSeason); $lastSeasonNoDash = preg_replace("/[^A-Za-z0-9 ]/", '', $lastSeason); ?> <form name = "get_stats" action = "/get_stats.cgi" method = "get" class="form-validate form-horizontal"> <fieldset> <div class="control-group"> <legend>What Would You Like to Download?</legend> <div class="control-label"> <label id="jform_contact_name-lbl" for="jform_contact_name" class="hasTooltip required" title="<strong>Name</strong><br />Your name."> Player Type</label> </div> <div class="controls"> <select name = "type"> <option value = "skaters">Skaters</option>
function getBestAvailable($leagueID, $position, $limit) { debug("GET BEST AVAILABLE"); $userID = getUserID(); $myPick = false; $transactionID = getNextTransactionID($userID, $leagueID); if (intval($userID) == intval(userPickingNext($leagueID))) { $myPick = true; } else { $passedPick = getSingleton("fch_draft", "overallPick", " WHERE leagueID = {$leagueID} and userID = {$userID} and status = \"P\" ORDER BY overallPick DESC Limit 0,1"); if ($passedPick != "") { $myPick = true; } else { $withdrawnPick = getSingleton("fch_draft", "overallPick", " WHERE leagueID = {$leagueID} and userID = {$userID} and status = \"W\" ORDER BY overallPick DESC Limit 0,1"); if ($withdrawnPick != "") { $myPick = true; } } } $pickButton = ""; $out .= "<table><thead><th>Player</th><th>Pts Last Year</th><th>Points Per Game Last Year</th><th>Action</th></thead>"; $lastSeason = getLastSeason(); $sql = "\r\r\n\t\tselect p.position as position, gamesPlayed, sum(r.totalPoints) as totalPoints, sum(r.totalPoints)/gamesPlayed as ppg, r.playerID as playerID, concat(left(p.firstName,1),\". \", p.lastName, \", \", s.school_shortname) as displayName from fch_consolidated_results r, fch_players p, fch_schools s, (select schoolID, count(schoolID) as gamesPlayed from (\r\r\n\t\tselect dateStamp, home as schoolID from fch_schedule_lastseason\r\r\n\t\tunion\r\r\n\t\tselect dateStamp, visitor as schoolID from fch_schedule_lastseason) as sched\r\r\n\t\twhere sched.dateStamp < date_add(now(),interval + 3 hour)\r\r\n\t\tgroup by schoolID) as gp\r\r\n\r\r\n\t\twhere r.playerID = p.playerID\r\r\n\t\tand p.schoolID = s.school_id\r\r\n\t\tand r.season = \"{$lastSeason}\"\r\r\n\t\tand gp.schoolID = p.schoolID\r\r\n\t\tand r.playerID not in (select playerID from fch_draft where leagueID = {$leagueID})\r\r\n\t\tand r.playerID not in (select playerID from fch_draft_customplayer where leagueID = {$leagueID})\r\r\n\t\tand r.playerID not in (select playerID from fch_players_leftearly)\r\r\n\r\r\n\t\tand p.position = \"{$position}\"\r\r\n\t\tgroup by r.playerID\r\r\n\t\torder by sum(r.totalPoints) desc\r\r\n\t\tlimit 0,{$limit}\r\r\n\t\t"; $con = initializeDB(); $result = $con->query($sql); if ($result->num_rows > 0) { // output data of each row while ($row = $result->fetch_assoc()) { if ($myPick) { $pickButton .= "<form method = \"post\" name = \"submit_" . $row['playerID'] . "_a\" enctype=\"multipart/form-data\"><input name = \"transactionID\" value = \"" . $transactionID . "\" type = \"hidden\"><input value = \"" . $row['position'] . "\" type = \"hidden\" name = \"position\"><input value = \"doAcquire\" type = \"hidden\" name = \"action\"><input type = \"hidden\" name =\"destination\" value = \"act\"><input type = \"hidden\" name = \"playerID\" value = \"" . $row['playerID'] . "\"><button class=\"btn btn-primary validate\" type=\"button\" id = \"button_submit_" . $row['playerID'] . "_a\" onclick=\"javascript:submitForm('submit_" . $row['playerID'] . "_a','button_submit_" . $row['playerID'] . "_a');\">Pick >></button> </form> \n"; } else { $pickButton = ""; } $out .= "<tr><td>" . $row["displayName"] . "</td><TD>" . $row["totalPoints"] . "</td><td>" . $row["ppg"] . "</td></tr>\n"; } } $out .= "</tr></table>"; closeDB($con); return $out; }