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);
}
}
