public static void AddPlayersToDB() { var mainPath = "http://www.uefa.com/uefaeuro/season=2016/teams/index.html"; var allPlayers = ExtractAllPlayers(mainPath); Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-us"); using (var db = new sakilaEntities4()) { foreach (var player in allPlayers) { var nationalTeamID = db.team.First(x => x.TeamName == player.Team).TeamID; var alreadyExist = db.player.Where(x => x.PlayerName == player.Name && x.NationalTeamID == nationalTeamID).ToList(); if (alreadyExist.Any()) { continue; } Console.WriteLine(player.Team + ": " + player.Name); var p = new player(); p.NationalTeamID = nationalTeamID; p.PlayerName = player.Name; p.PositionID = 1; //NA db.player.Add(p); db.SaveChanges(); } } }
public static void AddNewTeams() { var teams = new List <string> { "Brighton & Hove Albion", "Huddersfield Town", }; using (var db = new sakilaEntities4()) { var leagueTeamID = 1; foreach (var team in teams) { var newTeam = new team { TeamName = team, TeamTypeID = leagueTeamID }; db.team.Add(newTeam); } db.SaveChanges(); } }
public static void AddGoalsDetailsToDb(DataObjects.MatchDetails match, sakilaEntities4 db, int homeTeamID, int awayTeamID) { var ownGoalID = 553; foreach (var goal in match.HomeTeam.GoalsDetails) { var newGoal = new matchgoal(); newGoal.MatchID = match.MatchID; newGoal.ScoringMinute = goal.Minute; newGoal.TeamID = homeTeamID; if (goal.IsOwnGoal) { newGoal.ScorerID = ownGoalID; } else { newGoal.ScorerID = GetPlayerId(goal.Scorer, homeTeamID, db); } db.matchgoal.Add(newGoal); db.SaveChanges(); } foreach (var goal in match.AwayTeam.GoalsDetails) { var newGoal = new matchgoal(); newGoal.MatchID = match.MatchID; newGoal.ScoringMinute = goal.Minute; newGoal.TeamID = awayTeamID; if (goal.IsOwnGoal) { newGoal.ScorerID = ownGoalID; } else { newGoal.ScorerID = GetPlayerId(goal.Scorer, awayTeamID, db); } db.matchgoal.Add(newGoal); db.SaveChanges(); } }
public static void AddEventToDb(int eventTypeId, int teamId, int matchId, int value, sakilaEntities4 db) { var newEv = new matchevent(); newEv.EventTypeID = eventTypeId; newEv.TeamID = teamId; newEv.eventvalue = value; newEv.MatchID = matchId; db.matchevent.Add(newEv); db.SaveChanges(); }
public static int AddPlayerNameAsNa(string name, int teamId, sakilaEntities4 db) { var newPlayer = new player { TeamID = teamId, PlayerName = "NA " + name, PositionID = 1 //NA }; db.player.Add(newPlayer); db.SaveChanges(); return(newPlayer.PlayerID); }
public static int AddPlayerToDb(PremierLeagueMainProject.PlayerDetails p, sakilaEntities4 db, int teamId) { var positionId = GetPositionId(p.PlayerPosition); var pl = new player(); if (IsPlayerAlreadyExists(p.PlayerName, teamId, db, out pl)) { pl.TeamID = teamId; pl.PositionID = positionId; db.SaveChanges(); return(pl.PlayerID); } var newPlayer = new player(); newPlayer.PlayerName = p.PlayerName; newPlayer.PositionID = positionId; newPlayer.TeamID = teamId; db.player.Add(newPlayer); db.SaveChanges(); return(newPlayer.PlayerID); }
public static void AddMatchDetailsToDb(sakilaEntities4 db, int homeTeamID, int awayTeamID, int homeTeamGoals, int awayTeamGoals, DateTime matchDate, int competitionMatchID) { var newMatch = new competitionmatch(); newMatch.HomeTeamID = homeTeamID; newMatch.AwayTeamID = awayTeamID; newMatch.HomeGoals = homeTeamGoals; newMatch.AwayGoals = awayTeamGoals; newMatch.WinnerTeamID = Helper.GetWinnerTeamID(homeTeamID, homeTeamGoals, awayTeamID, awayTeamGoals); newMatch.MatchDate = matchDate; newMatch.CompetitionID = competitionMatchID; db.competitionmatch.Add(newMatch); db.SaveChanges(); }
public static void SolvePossessionIssue() { var i = 0; using (var db = new sakilaEntities4()) { var evs = db.matchevent.Where(x => x.competitionmatch.CompetitionID == 2 && x.EventTypeID == 7 && x.eventvalue == 0).ToList(); Console.WriteLine(evs.Count()); foreach (var e in evs) { Console.WriteLine(i++); e.EventTypeID = 16; db.SaveChanges(); } } }
public static void AddMatchDetailsToDb(DataObjects.MatchDetails match, sakilaEntities4 db, int homeTeamID, int awayTeamID, int competitionID = 10) { var newMatch = new competitionmatch(); newMatch.HomeTeamID = homeTeamID; newMatch.AwayTeamID = awayTeamID; newMatch.HomeGoals = match.HomeTeam.Goals; newMatch.AwayGoals = match.AwayTeam.Goals; newMatch.WinnerTeamID = Helper.GetWinnerTeamID(homeTeamID, match.HomeTeam.Goals, awayTeamID, match.AwayTeam.Goals); newMatch.MatchDate = match.Date; newMatch.CompetitionID = competitionID; db.competitionmatch.Add(newMatch); db.SaveChanges(); match.MatchID = newMatch.CompetitionMatchID; }
public static void AddTeamsToDb(List <string> teamNames) { var leagueTeamID = 1; using (var db = new sakilaEntities4()) { foreach (var teamName in teamNames) { var t = new team(); t.TeamName = teamName; t.TeamTypeID = leagueTeamID; db.team.Add(t); } db.SaveChanges(); } }
public static void AddOffsidesDetailsToDb(DataObjects.MatchDetails match, sakilaEntities4 db, int homeTeamID, int awayTeamID) { //2 var homeEv = new matchevent(); homeEv.EventTypeID = 2; homeEv.TeamID = homeTeamID; homeEv.eventvalue = match.HomeTeam.Offsides; homeEv.MatchID = match.MatchID; var awayEv = new matchevent(); awayEv.EventTypeID = 2; awayEv.TeamID = awayTeamID; awayEv.eventvalue = match.AwayTeam.Offsides; awayEv.MatchID = match.MatchID; db.matchevent.Add(homeEv); db.matchevent.Add(awayEv); db.SaveChanges(); }
public static void UpdateMarketValue() { var allValues = new Dictionary <string, int>(); allValues.Add("Arsenal", 398); allValues.Add("Manchester United", 454); allValues.Add("Manchester City", 440); allValues.Add("Liverpool", 329); allValues.Add("Tottenham Hotspur", 310); allValues.Add("Leicester City", 178); allValues.Add("Southampton", 169); allValues.Add("West Ham United", 205); allValues.Add("Stoke City", 146); allValues.Add("Chelsea", 437); allValues.Add("Everton", 203); allValues.Add("Swansea City", 90); allValues.Add("Watford", 110); allValues.Add("West Bromwich Albion", 91); allValues.Add("Crystal Palace", 134); allValues.Add("Bournemouth", 103); allValues.Add("Sunderland", 77); allValues.Add("Burnley", 56); allValues.Add("Middlesbrough", 89); allValues.Add("Hull City", 70); allValues.Add("Newcastle United", 102); allValues.Add("Norwich City", 61); allValues.Add("Aston Villa", 72); using (var db = new sakilaEntities4()) { foreach (var value in allValues) { Console.WriteLine(value.Key); var team = db.team.First(x => x.TeamName == value.Key); team.MarketValue = value.Value; } db.SaveChanges(); } }
public static void FixShotsIssue() { var competitionId = 3; using (var db = new sakilaEntities4()) { var events = db.matchevent .Where(x => x.competitionmatch.CompetitionID == competitionId) .Where(x => x.EventTypeID == 5) .GroupBy(x => new { x.MatchID, x.TeamID }) .ToList(); foreach (var ev in events) { if (ev.Count() == 2) { var larger = ev.OrderByDescending(x => x.eventvalue).First(); larger.EventTypeID = 10; db.SaveChanges(); } } } }
public static void AddShotsDetailsToDb(DataObjects.MatchDetails match, sakilaEntities4 db, int homeTeamID, int awayTeamID) { var homeEv = new matchevent(); homeEv.EventTypeID = 5; homeEv.TeamID = homeTeamID; homeEv.eventvalue = match.HomeTeam.OnTarget; homeEv.MatchID = match.MatchID; var awayEv = new matchevent(); awayEv.EventTypeID = 5; awayEv.TeamID = awayTeamID; awayEv.eventvalue = match.AwayTeam.OnTarget; awayEv.MatchID = match.MatchID; var homeEv2 = new matchevent(); homeEv2.EventTypeID = 6; homeEv2.TeamID = homeTeamID; homeEv2.eventvalue = match.HomeTeam.OffTarget; homeEv2.MatchID = match.MatchID; var awayEv2 = new matchevent(); awayEv2.EventTypeID = 6; awayEv2.TeamID = awayTeamID; awayEv2.eventvalue = match.AwayTeam.OffTarget; awayEv2.MatchID = match.MatchID; db.matchevent.Add(homeEv); db.matchevent.Add(homeEv2); db.matchevent.Add(homeEv); db.matchevent.Add(awayEv2); db.SaveChanges(); }
public static void UpdateBetsOdds(string season, int competitionId) { var i = 1; var page = $"http://www.betexplorer.com/soccer/england/premier-league-{season}/results/"; var dom = CQ.CreateFromUrl(page); var allResults = dom[".table-main tr"].Skip(1).ToList(); using (var db = new sakilaEntities4()) { foreach (var result in allResults) { var resultOdds = new List <String>(); var odds = result.ChildElements.Where(x => x.ClassName == "table-main__odds" || x.ClassName == "table-main__odds colored").ToList(); foreach (var element in odds) { var text = element.InnerText; var odd = element.GetAttribute("data-odd"); if (odd == null) { var e = element.FirstElementChild.FirstElementChild.FirstElementChild; odd = e.GetAttribute("data-odd"); } resultOdds.Add(odd); } var elements = result.ChildElements.Select(x => x.Cq().Text()).Where(x => !string.IsNullOrWhiteSpace(x)).ToList(); if (elements.Count != 3) { continue; } Console.WriteLine(i++); var teams = elements[0]; var matchResult = elements[1]; var date = elements[2]; if (matchResult.Equals("POSTP.")) { continue; } var homeTeam = teams.Split('-').First(); var awayTeam = teams.Split('-').Last(); var homeGoals = int.Parse(matchResult.Split(':').First().Trim()); var awayGoals = int.Parse(matchResult.Split(':').Last().Trim()); var normalizedHomeTeamName = Helper.NormalizeTeamName(homeTeam); var normalizedAwayTeamName = Helper.NormalizeTeamName(awayTeam); Console.WriteLine(normalizedHomeTeamName + " VS. " + normalizedAwayTeamName); var homeTeamId = db.team.First(x => x.TeamName == normalizedHomeTeamName).TeamID; var awayTeamId = db.team.First(x => x.TeamName == normalizedAwayTeamName).TeamID; var parsedDate = DateTime.Today.AddDays(-1); try { parsedDate = DateTime.Parse(date); } catch { try { var year = season.Split('-').First(); var dateMonth = int.Parse(date.Split('.')[1]); if (dateMonth <= 5) { year = season.Split('-').Last(); } parsedDate = DateTime.Parse(date + year); } catch { if (date.Equals("Today")) { parsedDate = DateTime.Today; } else if (date.Equals("Yesterday")) { parsedDate = DateTime.Today.AddDays(-1); } else { throw new Exception("Failed to parse date!"); } } } var match = db.competitionmatch .FirstOrDefault(x => x.HomeTeamID == homeTeamId && x.AwayTeamID == awayTeamId && x.MatchDate == parsedDate); if (match == null) { throw new Exception(); } var oddsAlreadyExists = db.matchodds.FirstOrDefault(x => x.MatchID == match.CompetitionMatchID); if (oddsAlreadyExists != null) { continue; } var newOdds = new matchodds(); newOdds.HomeTeamOdds = decimal.Parse(resultOdds[0]); newOdds.DrawOdds = decimal.Parse(resultOdds[1]); newOdds.AwayTeamOdds = decimal.Parse(resultOdds[2]); newOdds.MatchID = match.CompetitionMatchID; db.matchodds.Add(newOdds); db.SaveChanges(); } } }