Beispiel #1
0
        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();
            }
        }
Beispiel #2
0
        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();
                }
            }
        }
Beispiel #3
0
        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));
        }
Beispiel #4
0
        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);
        }
Beispiel #6
0
 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);
        }
Beispiel #10
0
            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());
        }
Beispiel #13
0
        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);
        }
Beispiel #14
0
        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);
            }
        }
Beispiel #21
0
        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);
                }
            }
        }
Beispiel #22
0
 public void BuildTestLine()
 {
     using (var db = new sakilaEntities4())
     {
         if (AggregateStats(db, isTest: true))
         {
             var winner = "D";
             Winner  = winner;
             Over2_5 = 0;
         }
     }
 }
Beispiel #23
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));
        }
Beispiel #24
0
            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);
        }
Beispiel #27
0
        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++);
                }
            }
        }
Beispiel #28
0
        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));
        }
Beispiel #30
0
        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);
            }
        }