public static DateTime GetDeadline(int week, int year)
{
using (PickemDBContext db = new PickemDBContext())
{
/* The deadline should be the day before the first game of the week, at 8pm.
* Because our Game model does not contain a game date, we must calculate the game date based
* on the date the season started, the week of the game, and the day of the game.
*/
// First get the season start date
var season = db.Seasons.SingleOrDefault(s => s.Year == year);
if (season == null)
return DateTime.MinValue;
// Calculate the date for this week of the season
var weekOf = season.StartDate.AddDays((week - 1) * 7);
// Get the first game of the week
var firstGame = db.Games.Where(q => q.Week == week && q.Year == year && q.GameType == "REG").ToList()
.OrderBy(o => o.Eid.Substring(0, 8))
.ThenBy(o => o.Time.PadLeft(5, '0'))
.ThenBy(o => o.Gsis)
.FirstOrDefault();
if (firstGame == null)
return weekOf.AddDays(-1).AddHours(20);
// Calculate the date of the game based on the day of the game
var shortDayNames = CultureInfo.CurrentCulture.DateTimeFormat.AbbreviatedDayNames;
var dayOfIndex = Array.IndexOf(shortDayNames, firstGame.Day);
var gameDate = weekOf.AddDays(-Convert.ToInt32(weekOf.DayOfWeek)).AddDays(dayOfIndex);
return gameDate.AddDays(-1).AddHours(20);
}
}
public static DateTime GetDeadline(int week, int year)
{
using (PickemDBContext db = new PickemDBContext())
{
/* The deadline should be the day before the first game of the week, at 8pm.
* Because our Game model does not contain a game date, we must calculate the game date based
* on the date the season started, the week of the game, and the day of the game.
*/
// First get the season start date
var season = db.Seasons.SingleOrDefault(s => s.Year == year);
if (season == null)
{
return(DateTime.MinValue);
}
// Calculate the date for this week of the season
var weekOf = season.StartDate.AddDays((week - 1) * 7);
// Get the first game of the week
var firstGame = db.Games.Where(q => q.Week == week && q.Year == year && q.GameType == "REG").ToList()
.OrderBy(o => o.Eid.Substring(0, 8))
.ThenBy(o => o.Time.PadLeft(5, '0'))
.ThenBy(o => o.Gsis)
.FirstOrDefault();
if (firstGame == null)
{
return(weekOf.AddDays(-1).AddHours(20));
}
// Calculate the date of the game based on the day of the game
var shortDayNames = CultureInfo.CurrentCulture.DateTimeFormat.AbbreviatedDayNames;
var dayOfIndex = Array.IndexOf(shortDayNames, firstGame.Day);
var gameDate = weekOf.AddDays(-Convert.ToInt32(weekOf.DayOfWeek)).AddDays(dayOfIndex);
return(gameDate.AddDays(-1).AddHours(20));
}
}
public static List<WeeklyPlayerPicks> GetWeeklyLeaders(int week, int year, bool completed = false)
{
var lookup = new Dictionary<int, WeeklyPlayerPicks>();
using (PickemDBContext db = new PickemDBContext())
using (var conn = db.Database.Connection)
{
string sql = @"select wpp.Id, wpp.WeekNumber, wpp.Year, wpp.PlayerId, wpp.PlayerName, wpp.CorrectPicks, wpp.TieBreaker
, pl.*, p.*, g.*
from fnWeeklyPlayerPicks(@year, @week, @completed) wpp
inner join Players pl on wpp.PlayerId = pl.Id
inner join Games g on g.Week = wpp.WeekNumber and g.Year = wpp.Year
inner join Picks p on p.PlayerId = wpp.PlayerId and p.GameId = g.Id
order by wpp.Rank";
conn.Query<WeeklyPlayerPicks, Player, Pick, Game, WeeklyPlayerPicks>(sql, (wpp, pl, p, g) =>
{
WeeklyPlayerPicks weeklyPlayerPick;
if (!lookup.TryGetValue(wpp.Id, out weeklyPlayerPick))
{
wpp.Player = pl;
lookup.Add(wpp.Id, weeklyPlayerPick = wpp);
}
if (weeklyPlayerPick.Picks == null)
{
weeklyPlayerPick.Picks = new List<Pick>();
}
p.Game = g;
weeklyPlayerPick.Picks.Add(p);
return weeklyPlayerPick;
},
param: new { year = year, week = week, completed = completed },
splitOn: "Id").AsQueryable();
}
return lookup.Values.ToList();
}
public static List <WeeklyPlayerPicks> GetWeeklyLeaders(int week, int year, bool completed = false)
{
var lookup = new Dictionary <int, WeeklyPlayerPicks>();
using (PickemDBContext db = new PickemDBContext())
using (var conn = db.Database.Connection)
{
string sql = @"select wpp.Id, wpp.WeekNumber, wpp.Year, wpp.PlayerId, wpp.PlayerName, wpp.CorrectPicks, wpp.TieBreaker
, pl.*, p.*, g.*
from fnWeeklyPlayerPicks(@year, @week, @completed) wpp
inner join Players pl on wpp.PlayerId = pl.Id
inner join Games g on g.Week = wpp.WeekNumber and g.Year = wpp.Year
inner join Picks p on p.PlayerId = wpp.PlayerId and p.GameId = g.Id
order by wpp.Rank";
conn.Query <WeeklyPlayerPicks, Player, Pick, Game, WeeklyPlayerPicks>(sql, (wpp, pl, p, g) =>
{
WeeklyPlayerPicks weeklyPlayerPick;
if (!lookup.TryGetValue(wpp.Id, out weeklyPlayerPick))
{
wpp.Player = pl;
lookup.Add(wpp.Id, weeklyPlayerPick = wpp);
}
if (weeklyPlayerPick.Picks == null)
{
weeklyPlayerPick.Picks = new List <Pick>();
}
p.Game = g;
weeklyPlayerPick.Picks.Add(p);
return(weeklyPlayerPick);
},
param: new { year = year, week = week, completed = completed },
splitOn: "Id").AsQueryable();
}
return(lookup.Values.ToList());
}
public static void UpdateGames(string xmlLocation)
{
//The XML document is expected to be in the same format as http://www.nfl.com/liveupdate/scorestrip/ss.xml
XDocument xml = XDocument.Load(xmlLocation);
var games = xml.Descendants("g");
using (PickemDBContext db = new PickemDBContext())
{
foreach (var g in games)
{
Game game = new Game();
game.Eid = g.Attribute("eid").Value.ToString();
game.Gsis = g.Attribute("gsis").Value.ToString();
game.Week = Convert.ToInt32(g.Parent.Attribute("w").Value.ToString());
game.Year = Convert.ToInt32(g.Parent.Attribute("y").Value.ToString());
game.Day = g.Attribute("d").Value.ToString();
game.Time = g.Attribute("t").Value.ToString();
game.Quarter = g.Attribute("q").Value.ToString();
game.TimeRemaining = (g.Attribute("k") != null) ? g.Attribute("k").Value.ToString() : null;
game.HomeTeam = g.Attribute("h").Value.ToString().Replace("JAX", "JAC");
game.HomeTeamScore = Convert.ToInt32(g.Attribute("hs").Value.ToString());
game.VisitorTeam = g.Attribute("v").Value.ToString().Replace("JAX", "JAC");
game.VisitorTeamScore = Convert.ToInt32(g.Attribute("vs").Value.ToString());
game.GameType = g.Attribute("gt").Value.ToString();
if (game.HomeTeamScore == game.VisitorTeamScore)
game.WinningTeam = null;
else if (game.HomeTeamScore > game.VisitorTeamScore)
game.WinningTeam = game.HomeTeam;
else if (game.VisitorTeamScore > game.HomeTeamScore)
game.WinningTeam = game.VisitorTeam;
int gameId = (from t in db.Games
where t.Eid == game.Eid
select t.Id).SingleOrDefault();
if (gameId != 0)
{
game.Id = Convert.ToInt32(gameId);
db.Entry(game).State = System.Data.Entity.EntityState.Modified;
}
else
{
db.Games.Add(game);
}
}
db.SaveChanges();
var week = xml.Descendants("gms").FirstOrDefault();
if (week != null)
{
//Update pick results
UpdatePicks(Convert.ToInt32(week.Attribute("w").Value), Convert.ToInt32(week.Attribute("y").Value));
//Save XML to file if all games are completed
xml.Save(HttpContext.Current.Server.MapPath(VirtualPathUtility.ToAbsolute(string.Format("~/Content/datafiles/{0}Week{1}.xml", week.Attribute("y").Value, week.Attribute("w").Value))));
}
}
}
public static void UpdatePicksXls(string xlsfile)
{
using (PickemDBContext db = new PickemDBContext())
{
//Get the year from filename (YYYY_NFL_Weeks.xls)
int year;
if (!int.TryParse(xlsfile.Substring(0, 4), out year))
{
year = 0;
}
int week = 1;
week = db.Games.Where(g => g.Year == year && g.GameType == "REG").Select(g => g.Week).Max();
HttpContext.Current.Response.Write(string.Format("{0} {1}", year, week));
xlsfile = HttpContext.Current.Server.MapPath(VirtualPathUtility.ToAbsolute("~/Content/datafiles/" + xlsfile));
DataSet ds = ImportExcelXLS(xlsfile, false);
DataTable dt;
var tableNameLookup = string.Format("'WEEK {0}$'", week);
if (ds.Tables.Contains(tableNameLookup))
{
dt = ds.Tables[tableNameLookup];
}
else
{
dt = ds.Tables[ds.Tables.Count - 1];
}
//Year and Week data will be in the first row, column F3, formatted "YYYY / WEEK #"
string[] weekData = dt.Rows[0]["F3"].ToString().Split('/');
if (weekData.Length == 2)
{
if (year < 0)
{
year = Convert.ToInt32(weekData[0].Trim());
}
week = Convert.ToInt32(weekData[1].ToUpper().Replace("WEEK", "").Trim());
}
else if (weekData.Length == 1)
{
week = Convert.ToInt32(weekData[0].ToUpper().Replace("WEEK", "").Trim());
}
HttpContext.Current.Response.Write(string.Format("{0} {1}", year, week));
//Create a dictionary of games, where the key is the column name
//Games will start in column 2, last column is for scores
int gameColIndexLower = 2,
gameColIndexUpper = dt.Columns.Count - 2; //-2 because index is zero-based
Dictionary<string, Game> dictGames = new Dictionary<string, Game>();
for (int i = gameColIndexLower; i <= gameColIndexUpper; i++)
{
//Visitor team is row 4, Home team is row 5
string homeTeam = dt.Rows[4][i].ToString();
string visitorTeam = dt.Rows[3][i].ToString();
//Find the game record
Game game = (from g in db.Games
where g.Week == week && g.Year == year && g.GameType == "REG" && g.HomeTeam == homeTeam && g.VisitorTeam == visitorTeam
select g).FirstOrDefault();
dictGames.Add(dt.Columns[i].ColumnName, game);
}
List<Pick> newPicks = new List<Pick>();
foreach (DataRow row in dt.Rows)
{
if (!string.IsNullOrEmpty(row["F2"].ToString()))
{
var playerName = row["F2"].ToString();
Player player = db.Players.Where(q => q.Name.ToLower() == playerName.ToLower()).FirstOrDefault();
if (player != null)
{
for (int i = gameColIndexLower; i <= gameColIndexUpper; i++)
{
string teamPicked = row[dt.Columns[i].ColumnName].ToString();
Game game = dictGames[dt.Columns[i].ColumnName];
if (game != null && !string.IsNullOrEmpty(teamPicked))
{
double totalPoints = 0;
//Total points will be in the column next to the final game
if (i == gameColIndexUpper)
{
totalPoints = Convert.ToDouble(row[i + 1].ToString());
}
Pick newPick = new Pick
{
PlayerId = player.Id,
Player = player,
GameId = game.Id,
Game = game,
TeamPicked = teamPicked,
TotalPoints = totalPoints
};
newPick.Id = (from o in db.Picks
where o.PlayerId == player.Id && o.GameId == game.Id
select o.Id).FirstOrDefault();
newPick.PickResult = (newPick.TeamPicked == game.WinningTeam) ? "W" : null;
newPicks.Add(newPick);
}
}
}
}
}
foreach (var item in newPicks)
{
if (item.Id != 0)
{
db.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
else
{
db.Picks.Add(item);
}
}
db.SaveChanges();
}
}
public static void UpdatePicks(string htmldoc)
{
HtmlDocument doc = new HtmlDocument();
htmldoc = HttpContext.Current.Server.MapPath(VirtualPathUtility.ToAbsolute("~/Content/datafiles/" + htmldoc));
doc.Load(htmldoc);
// The node containing the week will look something like: <p class="c2 title"><a name="h.gc180lrgofzr"></a><span>Week 1</span></p>
int week = 1;
var weekNode = doc.DocumentNode.Descendants().Where(n => n.Name == "p" && n.Attributes["class"].Value.Contains("title")).FirstOrDefault();
if (weekNode != null)
{
week = Convert.ToInt32(weekNode.InnerText.Replace("Week", "").Trim());
}
// The node containing the year will look something like: <p class="c2 subtitle"> <a name="h.154hsp1t7nbe"></a><span>2012</span></p>
int year = 2012;
var yearNode = doc.DocumentNode.Descendants().Where(n => n.Name == "p" && n.Attributes["class"].Value.Contains("subtitle")).FirstOrDefault();
if (yearNode != null)
{
year = Convert.ToInt32(yearNode.InnerText.Trim());
}
using (PickemDBContext db = new PickemDBContext())
{
List<Pick> newPicks = new List<Pick>();
// Select pick container
var playerpicks = doc.DocumentNode.Descendants().Where(n => n.Name == "div" && n.Attributes["class"].Value == "playerpick");
foreach (var pp in playerpicks)
{
// Player name node looks like: <h2 class="c2"> <a name="h.tid79cr1nhcg"></a><span>ANDY</span></h2>
var playerName = pp.SelectSingleNode("h2").InnerText.Trim();
Player player = db.Players.Where(q => q.Name.ToLower() == playerName.ToLower()).FirstOrDefault();
// Loop through picks. Each pick node will look like: <p class="c2"><span class="c0">SD@ </span><span class="c0 c1">OAK (41)</span></p>
var picks = pp.Descendants().Where(n => n.Name == "p");
foreach (var p in picks)
{
// Parse node inner text to retrive home team, visitor team, and total points
string innerText = p.InnerText;
int totalPoints = 0;
string homeTeam;
string visitorTeam;
string teamPicked = "";
// Start with total points, and then remove it from inner text
Match m = Regex.Match(innerText, @"(\(([\d]*)\))");
if (m.Success)
{
totalPoints = Convert.ToInt32(m.Groups[2].Value);
innerText = innerText.Replace(m.Value, "");
}
// We should be left with "SD@ OAK", so extract teams from there
homeTeam = innerText.Split('@')[1].Trim();
visitorTeam = innerText.Split('@')[0].Trim();
// Get team picked from span node with class=c1 (<span class="c0 c1">OAK (41)</span>)
var teamPickedNode = p.Descendants().Where(n => n.Name == "span" && n.Attributes["class"].Value.Contains("c1")).FirstOrDefault();
if (teamPickedNode != null)
{
teamPicked = Regex.Replace(teamPickedNode.InnerText, @"[^a-zA-Z]*", "");
}
// Find the game record
Game game = (from g in db.Games
where g.Week == week && g.Year == year && g.GameType == "REG" && g.HomeTeam == homeTeam && g.VisitorTeam == visitorTeam
select g).FirstOrDefault();
if (player != null && game != null && !string.IsNullOrEmpty(teamPicked))
{
Pick newPick = new Pick
{
PlayerId = player.Id,
Player = player,
GameId = game.Id,
Game = game,
TeamPicked = teamPicked,
TotalPoints = totalPoints
};
newPick.Id = (from o in db.Picks
where o.PlayerId == player.Id && o.GameId == game.Id
select o.Id).FirstOrDefault();
newPick.PickResult = (newPick.TeamPicked == game.WinningTeam) ? "W" : null;
newPicks.Add(newPick);
}
}
}
foreach (var item in newPicks)
{
if (item.Id != 0)
{
db.Entry(item).State = System.Data.Entity.EntityState.Modified;
}
else
{
db.Picks.Add(item);
}
}
db.SaveChanges();
}
}
public static void UpdatePicks(int week, int year)
{
using (PickemDBContext db = new PickemDBContext())
{
var picks = (from p in db.Picks
join g in db.Games.Where(q => q.Week == week && q.Year == year && q.GameType == "REG") on p.GameId equals g.Id
select p).ToList<Pick>();
foreach (Pick p in picks)
{
p.PickResult = (p.TeamPicked == p.Game.WinningTeam) ? "W" : null;
db.Entry(p).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
}
}
public ActionResult ForgotPassword(AccountForgotPassword form)
{
if (ModelState.IsValid)
{
using (PickemDBContext db = new PickemDBContext())
{
var user = db.Players.FirstOrDefault(p => p.Email == form.Email && p.Username.Length > 0);
if (user == null)
{
ModelState.AddModelError("nouser", "An account does not exist for that email address.");
return View(form);
}
try
{
if (!OAuthWebSecurity.HasLocalAccount(user.Id))
{
WebSecurity.CreateAccount(user.Username, "T3mpP@ssw0rd");
}
var token = WebSecurity.GeneratePasswordResetToken(user.Username);
string resetLink = Request.Url.GetLeftPart(UriPartial.Authority) + "/account/resetpassword?token=" + token;
StringBuilder sbEmail = new StringBuilder();
sbEmail.AppendLine("Forgot your password? No problem!" + Environment.NewLine);
sbEmail.AppendLine("Your username is: " + user.Username + Environment.NewLine);
sbEmail.AppendLine("Reset your password by clicking the link below or copying and pasting it into your browser." + Environment.NewLine);
sbEmail.AppendLine(resetLink + Environment.NewLine);
sbEmail.AppendLine("(Heads up! This link expires in 24 hours. After that, you'll have to request a new one.)");
var mailgun = new Mailgun("*****@*****.**", user.Email, "Reset your password", sbEmail.ToString());
mailgun.Send();
}
catch (Exception ex)
{
ModelState.AddModelError("mailerror", ex.Message);
return View(form);
}
return RedirectToAction("ForgotPassword", new { message = ManageMessageId.ForgotPasswordSuccess });
}
}
return View(form);
}
public PickemDBContext()
: base(PickemDBContext.GetConnectionString())
{
}