private string GetYearDataInsert(YearData yd) { return (new FluentMySqlInsert("playerYearData") .WithColumns("id", "isRookie", "startingHandicap", "finishingHandicap", "playerId", "yearId", "teamId", "week0Score") .WithValues(yd.Id, yd.Rookie, yd.StartingHandicap, yd.FinishingHandicap, yd.Player.Id, yd.Year.Id, yd.Team.Id, yd.Week0Score) .ToString()); }
/// <summary> /// "new" handicap calculation (2011+) /// </summary> private static void CalculateHandicaps(DataModel dataModel, Player player, YearData yearData, bool isNewestYear) { var week0Score = yearData.Week0Score; var isRookie = yearData.Rookie; // Get all results for a player for the year. var resultsForPlayerForYear = dataModel.Results.Where(x => x.Player.Id == player.Id && x.Matchup.TeamMatchup.Week.Year.Value == yearData.Year.Value).OrderBy(x => x.Matchup.TeamMatchup.Week.SeasonIndex).ToList(); HandicapResult handicapResult = null, firstHandicapResult = null; for (int i = resultsForPlayerForYear.Count - 1; i >= 0; i--) { var subResults = resultsForPlayerForYear.Take(i + 1); handicapResult = HandicapsForResults(subResults, week0Score, isRookie); // bleh this could be cleaned up. if (i == 0) { resultsForPlayerForYear[i].PriorHandicap = week0Score - 36; } if (i == resultsForPlayerForYear.Count - 1) { firstHandicapResult = handicapResult; } else { resultsForPlayerForYear[i + 1].PriorHandicap = handicapResult.Handicap; } } // handicap result will be null if no results for the year yet. if (firstHandicapResult != null) { if (isNewestYear) { player.CurrentHandicap = firstHandicapResult.Handicap; } yearData.FinishingHandicap = firstHandicapResult.Handicap; } }
public void AddYearData(YearData yearData) { this.yearData.Add(yearData); }
/// <summary> /// Not used currently. Old calculation. /// </summary> private static void CalculateIncorrectHandicaps(DataModel dataModel, Player player, YearData yearData, bool isNewestYear) { var week0Score = yearData.Week0Score; var isRookie = yearData.Rookie; // Get all results for a player for the year. var resultsForPlayerForYear = dataModel.Results.Where(x => x.Player.Id == player.Id && x.Matchup.TeamMatchup.Week.Year.Value == yearData.Year.Value).OrderBy(x => x.Matchup.TeamMatchup.Week.SeasonIndex).ToList(); int scoreIndex = 4; List <int> scores = new List <int>(4); for (var i = 0; i < 5; i++) { scores.Add(week0Score); } foreach (var result in resultsForPlayerForYear) { result.PriorHandicap = priorHandicapWithScores(scores, scoreIndex); int handicapForWeek = result.Score - result.Matchup.TeamMatchup.Week.Course.Par; // Can't be larger than 20 if (handicapForWeek > 20) { handicapForWeek = 20; } scores.Add(handicapForWeek); scoreIndex++; } if (isNewestYear) { player.CurrentHandicap = priorHandicapWithScores(scores, scoreIndex); } yearData.FinishingHandicap = priorHandicapWithScores(scores, scoreIndex); }
private static void CalculateHandicapsUnder2009(DataModel dataModel, Player player, YearData yearData, bool isNewestYear) { }
public DataModel CreateDataModel(string databaseDirectory, string fileDsnLocation) { // 99 - 08 // 09 - 11 - added week 0 score to players, added course name to week // 12 - 13 - added status to player const int lastYear = 2015; Dictionary <string, Player> namesToPlayers = new Dictionary <string, Player>(); ICollection <Player> extraInvalidPlayers = new List <Player>(); HashSet <string> setOfPlayers = new HashSet <string>(); int teamIndex = 1, courseIndex = 1, weekIndex = 1, yearIndex = 1, yearDataIndex = 1, teamMatchupIndex = 1, playerIndex = 1, matchupIndex = 1, resultIndex = 1, dataMigrationIndex = 1, teamYearDataIndex = 1; Dictionary <string, Team> teamNameToTeam = new Dictionary <string, Team>(); Dictionary <string, Course> courseNameToCourse = new Dictionary <string, Course>(); Dictionary <int, Year> yearIdToYear = new Dictionary <int, Year>(); Dictionary <int, Year> yearValueToYear = new Dictionary <int, Year>(); Dictionary <int, TeamMatchup> teamMatchupIdMatchup = new Dictionary <int, TeamMatchup>(); ICollection <MatchUp> allMatchUps = new List <MatchUp>(); ICollection <Result> allResults = new List <Result>(); ICollection <YearData> yearDatas = new List <YearData>(); ICollection <TeamYearData> teamYearDatas = new List <TeamYearData>(); ICollection <Week> allWeeks = new List <Week>(); ICollection <DataMigration> dataMigrationDatas = new List <DataMigration>(); // Add fake team and fake players (these will be used later) var teamOfLostPlayers = new Team { Id = teamIndex++, Name = "Dummy Team", ValidTeam = false }; var noShowPlayer = new Player(playerIndex++) { Name = "No Show", CurrentHandicap = 0, ValidPlayer = false }; namesToPlayers[noShowPlayer.Name] = noShowPlayer; var nonLeagueSub = new Player(playerIndex++) { Name = "Non-League Sub", CurrentHandicap = 0, ValidPlayer = false }; namesToPlayers[nonLeagueSub.Name] = nonLeagueSub; for (int year = 1999; year < lastYear; year++) { OdbcCommand cmd; OdbcConnection connection; string yearStr = Convert.ToString(year); string connectionString = @"filedsn=" + fileDsnLocation + "; Uid=Admin; Pwd=bigmatt; DBQ=" + databaseDirectory + @"\golf" + yearStr.Substring(2) + ".mdb"; using (connection = new OdbcConnection(connectionString)) { connection.Open(); cmd = new OdbcCommand(); cmd.Connection = connection; // Year var newYear = new Year { Id = yearIndex++, Value = year, Complete = year == DateTime.Now.Year ? false : true }; yearIdToYear[newYear.Id] = newYear; yearValueToYear[year] = newYear; // add year data for both no show and non-league sub { var noShowPlayerYd = new YearData { FinishingHandicap = 20, Id = yearDataIndex++, Player = noShowPlayer, StartingHandicap = 20, Rookie = false, Team = teamOfLostPlayers, Week0Score = 99, Year = newYear }; yearDatas.Add(noShowPlayerYd); noShowPlayer.AddYearData(noShowPlayerYd); } { var nonLeagueSubYd = new YearData { FinishingHandicap = 20, Id = yearDataIndex++, Player = nonLeagueSub, StartingHandicap = 20, Rookie = false, Team = teamOfLostPlayers, Week0Score = 99, Year = newYear }; yearDatas.Add(nonLeagueSubYd); nonLeagueSub.AddYearData(nonLeagueSubYd); } // Data migration DateTime migrationDate = DateTime.UtcNow; if (year != DateTime.Now.Year) { migrationDate.AddDays(20); } var dataMigration = new DataMigration { Id = dataMigrationIndex++, Year = newYear, Notes = yearStr, DataMigrationDate = migrationDate }; dataMigrationDatas.Add(dataMigration); // Weeks cmd.CommandText = "SELECT * FROM WeekTable"; Dictionary <int, Week> weekTempIdToWeek = new Dictionary <int, Week>(); Dictionary <int, int> weekIdToTeamMatchupIndex = new Dictionary <int, int>(); using (var weekReader = cmd.ExecuteReader()) { while (weekReader.Read()) { string courseName = string.Empty; int coursePar = Convert.ToInt32(weekReader.GetString(1)); Course course = null; if (year < 2009) { courseName = Convert.ToString(year) + " Course " + Convert.ToString(coursePar); } else { courseName = weekReader.GetString(3); //coursePar = Convert.ToInt32(weekReader.GetString(1)); } if (!courseNameToCourse.TryGetValue(courseName, out course)) { course = new Course { Name = courseName, Id = courseIndex++, Par = coursePar }; courseNameToCourse.Add(courseName, course); } var weekDate = weekReader.GetString(2).Replace("Sept.", "September"); Week week = new Week { SeasonIndex = weekReader.GetInt32(0), Course = course, Date = DateTime.Parse(weekDate), Id = weekIndex++, Year = newYear }; if (week.SeasonIndex != 0) { week.PairingId = ((week.SeasonIndex - 1) % 3) + 1; allWeeks.Add(week); } else { week.PairingId = 1; } // // TODO: Manually set some pairing indexes for 2014. // weekTempIdToWeek[week.SeasonIndex] = week; weekIdToTeamMatchupIndex[week.SeasonIndex] = 0; } } if (year == 2014) { weekTempIdToWeek[10].PairingId = 3; weekTempIdToWeek[11].PairingId = 1; weekTempIdToWeek[12].PairingId = 2; weekTempIdToWeek[13].PairingId = 3; weekTempIdToWeek[14].PairingId = 1; weekTempIdToWeek[15].PairingId = 2; weekTempIdToWeek[16].PairingId = 3; weekTempIdToWeek[17].PairingId = 3; weekTempIdToWeek[18].PairingId = 1; } // Now that we are done processing weeks, assign playoff values to each one. var lastTwoWeeksOfYear = weekTempIdToWeek.Values.OrderByDescending(x => x.SeasonIndex).Take(2); foreach (var week in lastTwoWeeksOfYear) { week.IsPlayoff = true; } // Teams cmd.CommandText = "SELECT * FROM TeamTable"; Dictionary <int, Team> teamIdToTeam = new Dictionary <int, Team>(); using (var teamReader = cmd.ExecuteReader()) { while (teamReader.Read()) { var teamId = teamReader.GetInt32(0); var teamName = teamReader.GetString(1); if (string.Equals(teamName, "Mentally Handicapped", StringComparison.OrdinalIgnoreCase)) { teamName = "Golf Gods"; } Team team = null; if (!teamNameToTeam.TryGetValue(teamName, out team)) { bool validTeam = true; // Garbage team from 2005. Throw it awayyy if (string.Equals(teamName, "Handicap", StringComparison.OrdinalIgnoreCase)) { validTeam = false; } team = new Team() { Name = teamName, Id = teamIndex++, ValidTeam = validTeam }; teamNameToTeam.Add(teamName, team); } TeamYearData tyd = new TeamYearData { Id = teamYearDataIndex++, TeamId = team.Id, Year = yearValueToYear[year] }; teamYearDatas.Add(tyd); teamIdToTeam.Add(teamId, team); } } // Add our dummy team :\ if (!teamIdToTeam.ContainsKey(0) && !teamIdToTeam.ContainsKey(99)) { teamIdToTeam[0] = teamIdToTeam[99] = teamOfLostPlayers; TeamYearData tyd = new TeamYearData { Id = teamYearDataIndex++, TeamId = teamOfLostPlayers.Id, Year = yearValueToYear[year] }; teamYearDatas.Add(tyd); } // Players cmd.CommandText = "SELECT * FROM PlayerTable"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { string playerName = reader.GetString(0); int playersTeam = reader.GetInt32(1); int startingHandicap = 0; bool isRookie = false; int week0Score = -1; // If the year is before 2009, we'll update the handicaps later if (year >= 2009) { week0Score = reader.GetInt32(2); startingHandicap = week0Score - 36; } // If we have player status, then set the rookie value based on status. // Otherwise, everyone is assumed a veteran. if (year >= 2011) { var status = reader.GetString(3); isRookie = string.Equals(status, "new", StringComparison.OrdinalIgnoreCase) ? true : false; } Player player = null; if (!namesToPlayers.TryGetValue(playerName, out player)) { player = new Player(playerIndex++) { Name = playerName, CurrentHandicap = startingHandicap, ValidPlayer = true }; namesToPlayers[playerName] = player; setOfPlayers.Add(playerName); } // TODO: Needs to be something else //player.Team = teamIdToTeam[playersTeam]; // Current handicap will be updated by handicap code later player.CurrentHandicap = startingHandicap; // finishing handicap will be updated later. YearData yearData = new YearData { Player = player, Rookie = isRookie, StartingHandicap = startingHandicap, FinishingHandicap = startingHandicap, Year = yearValueToYear[year], Id = yearDataIndex++, Team = teamIdToTeam[playersTeam], Week0Score = week0Score == -1 ? 0 : week0Score // only years > 2009 will have a valid value here. }; yearDatas.Add(yearData); player.AddYearData(yearData); } } var playoffWeeksInOrder = weekTempIdToWeek.Values.Where(x => x.IsPlayoff).OrderBy(x => x.SeasonIndex).ToList(); // Matches cmd.CommandText = "SELECT * FROM MatchTable"; using (var matchReader = cmd.ExecuteReader()) { LinkedList <Tuple <int, TeamMatchup> > allTeamMatchupsForYear = new LinkedList <Tuple <int, TeamMatchup> >(); while (matchReader.Read()) { int weekId = matchReader[2] == System.DBNull.Value ? -1 : (matchReader.GetInt32(2)); int matchId = matchReader[1] == System.DBNull.Value ? -1 : Convert.ToInt32(matchReader.GetString(1)); if (matchId == -1 || weekId == -1) { continue; } var team1IdType = matchReader[3].GetType(); var team2IdType = matchReader[4].GetType(); if (team1IdType == System.DBNull.Value.GetType() || team2IdType == System.DBNull.Value.GetType()) { continue; } int team1Id = typeof(string) == team1IdType?Convert.ToInt32(matchReader.GetString(3)) : matchReader.GetInt32(3); int team2Id = typeof(string) == team2IdType?Convert.ToInt32(matchReader.GetString(4)) : matchReader.GetInt32(4); // TODO/NOTE: Treating null as "false". Change if it should be treated as true. bool matchComplete = matchReader[0].GetType() == DBNull.Value.GetType() || string.Equals(matchReader.GetString(0), "N", StringComparison.OrdinalIgnoreCase) ? false : true; if (!matchComplete && team1Id == 0 && team2Id == 0) { continue; } var weekForMatchup = weekTempIdToWeek[weekId]; var matchOrderInWeek = weekIdToTeamMatchupIndex[weekId]; var teamMatchup = new TeamMatchup { //MatchOrderInWeek = matchOrderInWeek, Week = weekForMatchup, Id = teamMatchupIndex++, MatchComplete = matchComplete, Team1 = teamIdToTeam[team1Id], Team2 = teamIdToTeam[team2Id], MatchId = matchId, PlayoffType = weekForMatchup.IsPlayoff ? GetPlayoffType(playoffWeeksInOrder.IndexOf(weekForMatchup), matchOrderInWeek) : null }; allTeamMatchupsForYear.AddLast(new Tuple <int, TeamMatchup>(weekId, teamMatchup)); weekIdToTeamMatchupIndex[weekId] = matchOrderInWeek + 1; teamMatchupIdMatchup[teamMatchup.Id] = teamMatchup; } var lookups = allTeamMatchupsForYear.ToLookup(x => x.Item1, x => x.Item2); foreach (var l in lookups) { var orderedMatches = l.OrderBy(x => x.MatchId).ToList(); for (int i = 0; i < orderedMatches.Count; i++) { orderedMatches[i].MatchOrderInWeek = i; } } } // Results cmd.CommandText = "SELECT * FROM ResultsTable"; using (var resultsReader = cmd.ExecuteReader()) { while (resultsReader.Read()) { var weekId = resultsReader.GetInt32(0); var team1Id = resultsReader.GetInt32(1); var score1 = resultsReader.GetInt32(3); var points1 = resultsReader.GetInt32(4); var team2Id = resultsReader.GetInt32(5); var score2 = resultsReader.GetInt32(7); var points2 = resultsReader.GetInt32(8); var player1Name = resultsReader.GetString(2); var player2Name = resultsReader.GetString(6); // TODO: Can we throw this out? It doesn't seem to make sense //if (team1Id == team2Id && weekId != 0) //{ // throw new ArgumentException("team1: " + team1Id + ", team2: " + team2Id + ", week: " + weekId); //} Player player1 = null; Player player2 = null; if (!setOfPlayers.Contains(player1Name)) { //Console.WriteLine("player missing: " + player1Name); } if (!setOfPlayers.Contains(player2Name)) { //Console.WriteLine("player missing: " + player2Name); } if (!namesToPlayers.TryGetValue(player1Name, out player1)) { var invalidPlayer = GetProperInvalidPlayer(player1Name, noShowPlayer, nonLeagueSub); // this is a special 2011 if (invalidPlayer == null) { if (year != 2011) { throw new InvalidOperationException("invalid player found in year not 2011: " + player1Name); } invalidPlayer = new Player(playerIndex++) { Name = player1Name, ValidPlayer = false, CurrentHandicap = 0 }; var invalidPlayerYd = new YearData { Year = newYear, Week0Score = 99, StartingHandicap = 20, FinishingHandicap = 20, Id = yearDataIndex++, Team = teamOfLostPlayers, Rookie = false, Player = invalidPlayer }; invalidPlayer.AddYearData(invalidPlayerYd); yearDatas.Add(invalidPlayerYd); extraInvalidPlayers.Add(invalidPlayer); } player1 = invalidPlayer; } if (!namesToPlayers.TryGetValue(player2Name, out player2)) { var invalidPlayer = GetProperInvalidPlayer(player2Name, noShowPlayer, nonLeagueSub); // this is a special 2011 if (invalidPlayer == null) { if (year != 2011) { throw new InvalidOperationException("invalid player found in year not 2011: " + player2Name); } invalidPlayer = new Player(playerIndex++) { Name = player2Name, ValidPlayer = false, CurrentHandicap = 0 }; var invalidPlayerYd = new YearData { Year = newYear, Week0Score = 99, StartingHandicap = 20, FinishingHandicap = 20, Id = yearDataIndex++, Team = teamOfLostPlayers, Rookie = false, Player = invalidPlayer }; invalidPlayer.AddYearData(invalidPlayerYd); yearDatas.Add(invalidPlayerYd); extraInvalidPlayers.Add(invalidPlayer); } player2 = invalidPlayer; } // okay, now that we have valid players, continue on. Team team1 = teamIdToTeam[team1Id]; if (!teamIdToTeam.ContainsKey(team2Id)) { //Console.WriteLine("Where is dis team: " + team2Id); } Team team2 = teamIdToTeam[team2Id]; Week week = weekTempIdToWeek[weekId]; // Week 0's are a really special case... we allow team IDs to match here. if (weekId == 0) { // Take player1 and player2 and set their current handicaps // Set the week0 score in to the year data's starting handicap. int player1Handicap = score1 - 36; int player2Handicap = score2 - 36; var player1YearDatas = yearDatas.Where(y => y.Player.Id == player1.Id && y.Year.Value == year); var player2YearDatas = yearDatas.Where(y => y.Player.Id == player2.Id && y.Year.Value == year); // Some folks show up in the results table for week 0 even though they aren't valid // for the year... if that makes sense. In this case, we'll just throw out the handicap value. if (player1.ValidPlayer && team1Id != 99) { // current handicap for a player will be overwritten with each year we pass. // So, eventually the last year iteration that will be seen will replace the current handicap // with the correct value. player1.CurrentHandicap = player1Handicap; var player1YearDataForThisYear = player1YearDatas.First(); player1YearDataForThisYear.Week0Score = score1; // finishing handicap will be fixed later when we process handicaps. player1YearDataForThisYear.StartingHandicap = player1YearDataForThisYear.FinishingHandicap = player1Handicap; } if (player2.ValidPlayer && team2Id != 99) { player2.CurrentHandicap = player2Handicap; var player2YearDataForThisYear = player2YearDatas.First(); player2YearDataForThisYear.Week0Score = score2; // finishing handicap will be fixed later when we process handicaps. player2YearDataForThisYear.StartingHandicap = player2YearDataForThisYear.FinishingHandicap = player2Handicap; } // Console.WriteLine("could not find matchup."); continue; } // Team matchups should be unique based on team ID and week ID for a year. var teamMatchups = teamMatchupIdMatchup.Values.Where(t => ((t.Team1.Id == teamIdToTeam[team1Id].Id || t.Team2.Id == teamIdToTeam[team2Id].Id) || (t.Team1.Id == teamIdToTeam[team2Id].Id || t.Team2.Id == teamIdToTeam[team1Id].Id)) && t.Week.SeasonIndex == weekId && t.Week.Year.Value == year); if (teamMatchups.Count() == 0) { throw new InvalidOperationException("Should've found a team matchup... " + Convert.ToString(team1Id) + " " + Convert.ToString(weekId)); } if (team1Id == team2Id && year != 2011) { throw new ArgumentException("Teams can only play eachother in week 0 and 2011."); } // AGerber - 7/3/2014 allow no show/no show matches to show up in data. // Don't do anything with invalid players that play themselves! //if (player1.Id == 1 && player2.Id == 1) //{ // continue; //} var teamMatchup = teamMatchups.First(); MatchUp matchup = new MatchUp { Id = matchupIndex++, TeamMatchup = teamMatchup, Player1 = player1, Player2 = player2 }; allMatchUps.Add(matchup); Result player1Result = new Result { Year = newYear, Player = player1, Matchup = matchup, Points = points1, Score = score1, Id = resultIndex++, Team = team1 }; allResults.Add(player1Result); team1.AddResult(player1Result); player1.AddResult(player1Result); Result player2Result = new Result { Year = newYear, Player = player2, Matchup = matchup, Points = points2, Score = score2, Id = resultIndex++, Team = team2 }; allResults.Add(player2Result); team2.AddResult(player2Result); player2.AddResult(player2Result); matchup.Result1 = player1Result; matchup.Result2 = player2Result; } } connection.Close(); } } DataModel dm = new DataModel { Teams = new List <Team>(teamNameToTeam.Values) { teamOfLostPlayers }, Years = yearValueToYear.Values, Players = namesToPlayers.Values.Concat(extraInvalidPlayers).ToList(), MatchUp = allMatchUps, TeamMatchup = teamMatchupIdMatchup.Values, Weeks = allWeeks, YearDatas = yearDatas, Results = allResults, Courses = courseNameToCourse.Values, LeaderBoardDatas = new List <LeaderBoardData>(), LeaderBoards = new List <LeaderBoard>(), DataMigrations = dataMigrationDatas, TeamYearData = teamYearDatas }; ProcessHandicaps(dm); BuildLeaderboards(dm); return(dm); }