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 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 decimal CalculateTeamStrength(sakilaEntities4 db, int teamId, string homeOrAway, int competitionId, DateTime date) { var marketValue = db.team.First(x => x.TeamID == teamId).MarketValue; var seasonalBalance = PointsCalculator.GetTeamBalance(db, teamId, competitionId, 50, date); var seasonalPointsPace = PointsCalculator.CalculatePointPace(seasonalBalance); var latestBalance = PointsCalculator.GetTeamBalance(db, teamId, competitionId, 3, date); var latestPointsPace = PointsCalculator.CalculatePointPace(latestBalance); var homeOrAwayBalance = new TeamBalance(); if (homeOrAway == "Home") { homeOrAwayBalance = PointsCalculator.GetTeamBalanceHome(db, teamId, competitionId, 50, date); } else { homeOrAwayBalance = PointsCalculator.GetTeamBalanceAway(db, teamId, competitionId, 50, date); } var homeOrAwayPointsPace = PointsCalculator.CalculatePointPace(homeOrAwayBalance); return(CalculateTeamStrength(marketValue.Value, seasonalPointsPace, latestPointsPace, homeOrAwayPointsPace)); }
public static double GetWeightedBalance(sakilaEntities4 db, List <competitionmatch> latestMatches, int teamId, int competitionId, int gamesToTake = 50, DateTime?endDate = null) { if (!endDate.HasValue) { endDate = DateTime.Now; } var res = 0.0d; latestMatches = latestMatches.Take(gamesToTake).ToList(); foreach (var match in latestMatches) { if (match.WinnerTeamID != null && match.WinnerTeamID != teamId) { continue; } var oponentTeamID = match.HomeTeamID == teamId ? match.AwayTeamID : match.HomeTeamID; var oponentTeamValue = db.team.First(x => x.TeamID == oponentTeamID).MarketValue.Value; if (match.WinnerTeamID == teamId) { res += oponentTeamValue * 3; } //if it ended with draw else { res += oponentTeamValue * 1; } } return(res / latestMatches.Count); }
public static List <MainCalculator.PositionGoals> GetTeamScorersByPosition(sakilaEntities4 db, int teamId, int competitionId, int gamesToTake = 50) { var returnVal = new List <MainCalculator.PositionGoals>(); var latestMatches = MainCalculator.GetTeamLatesMatches(db, teamId, competitionId, gamesToTake); var matchesIds = latestMatches.Select(x => x.CompetitionMatchID); var teamGoalsConceded = db.matchgoal .Where(x => matchesIds.Contains(x.MatchID) && x.TeamID == teamId) .ToList(); var groupByPosition = teamGoalsConceded .GroupBy(x => x.player.PositionID) .OrderByDescending(x => x.Count()) .ToList(); foreach (var g in groupByPosition) { var position = db.playerposition.First(x => x.PlayerPositionID == g.Key).PlayerPositionName; var cnt = g.Count(); var newItem = new MainCalculator.PositionGoals { Goals = cnt, Position = position }; returnVal.Add(newItem); } return(returnVal); }
public static void AddFullMatchDetailsToDb(DataObjects.MatchDetails match, sakilaEntities4 db, int homeTeamID, int awayTeamID, int competitionID = 10) { Helper.AddMatchDetailsToDb(match, db, homeTeamID, awayTeamID, competitionID); Helper.AddGoalsDetailsToDb(match, db, homeTeamID, awayTeamID); addOnlyStatisticsToDb(match, db, homeTeamID, awayTeamID); }
public static void PrintTableOfEvent(int competitionId, DataObjects.EventType eventType, int gamesToTake = 50, DateTime?endDate = null) { var eventTypeId = (int)eventType; var path = $@"C:\Users\user\Desktop\DataProjects\EventTable{eventType}.tsv"; if (!endDate.HasValue) { endDate = DateTime.Now; } using (var db = new sakilaEntities4()) { var allEvents = db.matchevent .Where(x => x.competitionmatch.CompetitionID == competitionId) .Where(x => x.EventTypeID == eventTypeId) .Where(x => x.competitionmatch.MatchDate < endDate.Value) .ToList(); var groupByTeams = allEvents.GroupBy(x => x.team.TeamName) .Select(x => new { TeamName = x.Key, Average = x.OrderBy(z => z.competitionmatch.MatchDate).Select(y => (int)y.eventvalue).WeightedAverage(5) }) .ToList() .OrderByDescending(x => x.Average) .Select(x => x.TeamName + "\t" + Math.Round((double)x.Average, 2)) .ToList(); File.WriteAllLines(path, groupByTeams); } }
public static void GetAllStdDevForEvent(int eventTyepId, string eventName, int competitionId) { var l = new List <MainCalculator.TeamStdDevAndAverage>(); using (var db = new sakilaEntities4()) { var teams = db.team.Where(x => x.TeamTypeID == 1 && x.TeamName != "NA").ToList(); foreach (var team in teams) { var allValues = db.matchevent.Where( x => x.EventTypeID == eventTyepId && x.TeamID == team.TeamID && x.competitionmatch.CompetitionID == competitionId && x.eventvalue != null) .Select(x => x.eventvalue).ToList(); if (!allValues.Any()) { continue; } var avg = (double)allValues.Average(); var result = MainCalculator.CalculateStdDev(allValues.Select(x => x.Value).ToList()); l.Add(new MainCalculator.TeamStdDevAndAverage { StdDev = result, TeamName = team.TeamName, Average = avg }); } } var path = @"C:\Users\user\Desktop\DataProjects\AverageAndStdDev" + eventName + ".tsv"; File.WriteAllLines(path, l.OrderByDescending(x => x.Average).Select(x => x.TeamName + "\t" + Math.Round(x.Average, 2) + "\t" + Math.Round(x.StdDev, 2))); }
public static List <matchevent> GetEventsAgainstTeam(sakilaEntities4 db, int teamId, int competitionId, int eventTypeId, int gamesToTake = 50, DateTime?endDate = null, int weighted = 0) { if (!endDate.HasValue) { endDate = DateTime.Now; } var matches = db.competitionmatch .Where(x => x.CompetitionID == competitionId) .Where(x => x.HomeTeamID == teamId || x.AwayTeamID == teamId) .Where(x => x.MatchDate < endDate) .OrderByDescending(x => x.MatchDate) .Take(gamesToTake) .OrderBy(x => x.MatchDate) .Select(x => x.CompetitionMatchID); var evs = db.matchevent .Where( x => x.EventTypeID == eventTypeId && x.TeamID != teamId && x.competitionmatch.CompetitionID == competitionId && x.eventvalue != null && matches.Contains(x.MatchID)) .OrderByDescending(x => x.competitionmatch.MatchDate) .ToList(); return(evs); }
public void BuildTrainingLine() { using (var db = new sakilaEntities4()) { var match = db.competitionmatch.First(x => x.HomeTeamID == HomeTeamID && x.AwayTeamID == AwayTeamID && x.CompetitionID == CompetitionID); CompetitionMatchId = match.CompetitionMatchID; if (!AggregateStats(db)) { return; } var over2_5 = false; if (match.HomeGoals + match.AwayGoals > 2.5) { over2_5 = true; } Over2_5 = over2_5 ? 1 : 0; var winner = "D"; if (match.HomeGoals > match.AwayGoals) { winner = "H"; } else if (match.AwayGoals > match.HomeGoals) { winner = "A"; } Winner = winner; } }
public static double?GetAverageEventValueAgainstTeam(sakilaEntities4 db, int teamId, int competitionId, int eventTypeId, int gamesToTake = 50, DateTime?endDate = null, int weighted = 0) { if (!endDate.HasValue) { endDate = DateTime.Now; } var matches = db.competitionmatch .Where(x => x.CompetitionID == competitionId) .Where(x => x.HomeTeamID == teamId || x.AwayTeamID == teamId) .Where(x => x.MatchDate < endDate) .OrderByDescending(x => x.MatchDate) .Take(gamesToTake) .OrderBy(x => x.MatchDate) .Select(x => x.CompetitionMatchID); var average = db.matchevent.Where( x => x.EventTypeID == eventTypeId && x.TeamID != teamId && x.competitionmatch.CompetitionID == competitionId && x.eventvalue != null && matches.Contains(x.MatchID)) .OrderByDescending(x => x.competitionmatch.MatchDate) .Select(x => (int)x.eventvalue) .WeightedAverage(weighted); return(Math.Round(average, 2)); }
public static List <int> GetTeamsListAccuracyInFrontOfGoalAgainst(sakilaEntities4 db, int competitionId, int gamesToTake = 50, DateTime?endDate = null) { if (!endDate.HasValue) { endDate = DateTime.Now; } var participatedTeams = Helper.GetParticipatedTeamList(db, competitionId); var averageEventList = new List <TeamAverageEvent>(); //Fill Table foreach (var teamId in participatedTeams) { var shotsOnTargetAverageAgainstTeam = GetAverageShotsOnTargetAgainstTeam(db, teamId, competitionId, 50, endDate); var goalConcedingAverage = GoalsCalculator.GetGoalsConcededAverage(db, teamId, competitionId, 50, endDate); var rate = Math.Round((double)(shotsOnTargetAverageAgainstTeam / goalConcedingAverage.Average), 2); var newItem = new TeamAverageEvent { TeamID = teamId, Rate = rate }; averageEventList.Add(newItem); } var orderedTeamList = averageEventList.OrderByDescending(x => x.Rate); return(orderedTeamList.Select(x => x.TeamID).ToList()); }
public static List <StrengthDiff> GetMatchStrengthMapForCompetition(int competitionId) { var result = new List <StrengthDiff>(); using (var db = new sakilaEntities4()) { var allStrengthMatches = GetAllMatchStengthDetailsForCompetition(db, competitionId); var ordered = allStrengthMatches.GroupBy(x => x.HomeTeamStrength - x.AwayTeamStength) .OrderByDescending(x => x.Count()); foreach (var strengthBalance in ordered) { var winnerGroup = strengthBalance.GroupBy(x => x.Winner); var newItem = new StrengthDiff { Diff = strengthBalance.Key, Winners = winnerGroup.Select(y => new StrengthWinner { Winner = y.Key, Count = y.Count(), Percent = Math.Round((decimal)y.Count() / strengthBalance.Count(), 2) }).ToList() }; result.Add(newItem); } } result = result.OrderByDescending(x => x.Winners.FirstOrDefault().Percent *x.Winners.FirstOrDefault().Count).ToList(); return(result); }
public static List <TeamPoints> CalculateTableOnDate(string dateStr, int competitionId) { var date = DateTime.Parse(dateStr); var allTeamsPoints = new List <TeamPoints>(); using (var db = new sakilaEntities4()) { var allMatches = db.competitionmatch .Where(x => x.CompetitionID == competitionId && x.MatchDate <= date).ToList(); Console.WriteLine(allMatches.Count); var i = 0; foreach (var match in allMatches) { Console.WriteLine(i++); if (match.WinnerTeamID != null) { AddPointsToTeam(allTeamsPoints, match.WinnerTeamID.Value, 3); } else { AddPointsToTeam(allTeamsPoints, match.HomeTeamID, 1); AddPointsToTeam(allTeamsPoints, match.AwayTeamID, 1); } } } var path = @"C:\Users\user\Desktop\DataProjects\PremierLeagueTable"; var ordered = allTeamsPoints.OrderByDescending(x => x.TeamPointsValue).ToList(); File.WriteAllLines(path + dateStr + ".tsv", ordered.Select(x => x.TeamName + "\t" + x.TeamPointsValue)); return(ordered); }
public static string PrintMatchDetails(int competitionMatchId, sakilaEntities4 db) { var match = db.competitionmatch.First(x => x.CompetitionMatchID == competitionMatchId); var homeTeam = db.team.First(x => x.TeamID == match.HomeTeamID).TeamName; var awayTeam = db.team.First(x => x.TeamID == match.AwayTeamID).TeamName; return($"{homeTeam} Vs. {awayTeam} {match.HomeGoals}:{match.AwayGoals}"); }
public static List <int> GetParticipatedTeamList(sakilaEntities4 db, int competitionID) { var result = db.competitionmatch.Where(x => x.CompetitionID == competitionID) .Select(x => x.HomeTeamID) .Distinct() .ToList(); return(result); }
public static List <string> GetTeamLetterSequence(sakilaEntities4 db, int teamId, int competitionId, int gamesToTake = 50) { var latestMatchesLetters = MainCalculator.GetTeamLatesMatches(db, teamId, competitionId, gamesToTake) .OrderBy(x => x.MatchDate) .Select(x => GetCompetitionMatchResultLetter(x, teamId)) .ToList(); return(latestMatchesLetters); }
public static decimal GetAverageGoalForCompetition(int competitionId) { using (var db = new sakilaEntities4()) { var matches = MainCalculator.GetAllMatchesForCompetition(competitionId, db); var sumHomeGoals = matches.Sum(x => x.HomeGoals); var sumAwayGoals = matches.Sum(x => x.AwayGoals); return((decimal)(sumAwayGoals + sumHomeGoals) / matches.Count); } }
public static List <int> GetTeamGoals(int teamId) { using (var db = new sakilaEntities4()) { var allHomeMtaches = db.competitionmatch.Where(x => x.HomeTeamID == teamId).Select(x => x.HomeGoals).ToList(); var allAwayMatches = db.competitionmatch.Where(x => x.AwayTeamID == teamId).Select(x => x.AwayGoals).ToList(); allHomeMtaches.AddRange(allAwayMatches); return(allHomeMtaches); } }
public static void PrintTableOfAccuracyInFrontOfGoalAgainst(int competitionId) { var path = $@"C:\Users\user\Desktop\DataProjects\EventTableAccuracyInFrontOfGoalAgainst.tsv"; using (var db = new sakilaEntities4()) { var table = GetTeamNamesListAccuracyInFrontOfGoalAgainst(db, competitionId); var linesToWrite = table.Select(x => x.TeamName + "\t" + x.Rate).ToList(); File.WriteAllLines(path, linesToWrite); } }
public static void UpdateResultsOnly(string season, int competitionId) { var page = $"http://www.betexplorer.com/soccer/england/premier-league-{season}/results/"; var dom = CQ.CreateFromUrl(page); var allResults = dom[".result-table tr"].Skip(1).ToList(); using (var db = new sakilaEntities4()) { foreach (var result in allResults) { var elements = result.ChildElements.Select(x => x.Cq().Text()).Where(x => !string.IsNullOrWhiteSpace(x)).ToList(); if (elements.Count != 3) { continue; } var teams = elements[0]; var matchResult = elements[1]; var date = elements[2]; 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.Parse(date); var matchAlreadyExists = db.competitionmatch .FirstOrDefault(x => x.HomeTeamID == homeTeamId && x.AwayTeamID == awayTeamId && x.MatchDate == parsedDate); if (matchAlreadyExists != null) { Console.WriteLine("Match Exists!"); continue; } Helper.AddMatchDetailsToDb(db, homeTeamId, awayTeamId, homeGoals, awayGoals, parsedDate, competitionId); } } }
public void BuildTestLine() { using (var db = new sakilaEntities4()) { if (AggregateStats(db, isTest: true)) { var winner = "D"; Winner = winner; Over2_5 = 0; } } }
public static decimal GetTeamFormInLatestMatches(sakilaEntities4 db, int teamId, int competitionId, int matchesToTake = 50, DateTime?endDate = null) { if (!endDate.HasValue) { endDate = DateTime.Now; } var latestMatches = MainCalculator.GetTeamLatesMatches(db, teamId, competitionId, matchesToTake, endDate); var pointsAggregated = CalculatePointsForTeamInMatches(latestMatches, teamId); return(Math.Round((decimal)pointsAggregated / latestMatches.Count, 2)); }
public void init(string homeTeam, string awayTeam, DateTime date, int competitionId) { Date = date; CompetitionID = competitionId; using (var db = new sakilaEntities4()) { var homeTeamObj = db.team.First(x => x.TeamName == homeTeam); var awayTeamObj = db.team.First(x => x.TeamName == awayTeam); HomeTeamID = homeTeamObj.TeamID; AwayTeamID = awayTeamObj.TeamID; } }
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 bool IsPlayerAlreadyExists(string playerName, int teamID, sakilaEntities4 db, out player p) { var potential = db.player.FirstOrDefault(x => x.PlayerName == playerName && (x.TeamID == teamID || x.TeamID == null)); if (potential != null) { p = potential; return(true); } p = null; return(false); }
public static void MainUpdator() { var i = 1; var allMatchDetails = GetAllMatchesDetails(); using (var db = new sakilaEntities4()) { foreach (var matchDetails in allMatchDetails) { AddAllMatchDeatilsToDb(matchDetails, db); Console.WriteLine(i++); } } }
public static void addOnlyStatisticsToDb(DataObjects.MatchDetails match, sakilaEntities4 db, int homeTeamID, int awayTeamID) { Helper.AddEventToDb((int)DataObjects.EventType.Possession, homeTeamID, match.MatchID, match.HomeTeam.Possession, db); Helper.AddEventToDb((int)DataObjects.EventType.Corner, homeTeamID, match.MatchID, match.HomeTeam.Corners, db); Helper.AddEventToDb((int)DataObjects.EventType.Fouls, homeTeamID, match.MatchID, match.HomeTeam.Fouls, db); Helper.AddEventToDb((int)DataObjects.EventType.TotalShots, homeTeamID, match.MatchID, match.HomeTeam.TotalShots, db); Helper.AddEventToDb((int)DataObjects.EventType.ShotsOnTarget, homeTeamID, match.MatchID, match.HomeTeam.OnTarget, db); Helper.AddEventToDb((int)DataObjects.EventType.Possession, awayTeamID, match.MatchID, match.AwayTeam.Possession, db); Helper.AddEventToDb((int)DataObjects.EventType.Corner, awayTeamID, match.MatchID, match.AwayTeam.Corners, db); Helper.AddEventToDb((int)DataObjects.EventType.Fouls, awayTeamID, match.MatchID, match.AwayTeam.Fouls, db); Helper.AddEventToDb((int)DataObjects.EventType.TotalShots, awayTeamID, match.MatchID, match.AwayTeam.TotalShots, db); Helper.AddEventToDb((int)DataObjects.EventType.ShotsOnTarget, awayTeamID, match.MatchID, match.AwayTeam.OnTarget, db); }
public static int GetPlayerId(string name, int teamID, sakilaEntities4 db) { var nameArr = name.Replace("Ä\u0087", "c").Split().ToList(); var lastName = nameArr.Last(); var p = db.player.FirstOrDefault( x => x.TeamID == teamID && x.PlayerName.EndsWith(lastName)); if (p != null) { return(p.PlayerID); } return(AddPlayerNameAsNa(name, teamID, db)); }
public static List <AttributesMatch> GetAttributeMatchesForCompetition(int competitionId) { using (var db = new sakilaEntities4()) { var allAttributeMatches = db.competitionmatch .Where(x => x.CompetitionID == competitionId) .OrderBy(x => x.MatchDate) .Skip(20) .ToList() .Select(x => GetAttributeMatch(db, x)) .ToList(); return(allAttributeMatches); } }