public ActionResult Create(Pick pick)
{
if (ModelState.IsValid)
{
db.Picks.Add(pick);
db.SaveChanges();
return RedirectToAction("Index");
}
ViewBag.PlayerId = new SelectList(db.Players, "Id", "Name", pick.PlayerId);
ViewBag.GameId = new SelectList(db.Games, "Id", "Eid", pick.GameId);
return View(pick);
}
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 ActionResult Edit(Pick pick)
{
if (ModelState.IsValid)
{
db.Entry(pick).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
ViewBag.PlayerId = new SelectList(db.Players, "Id", "Name", pick.PlayerId);
ViewBag.GameId = new SelectList(db.Games, "Id", "Eid", pick.GameId);
return View(pick);
}