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