private string GetTeamYearDataInsert(TeamYearData p)
{
return(new FluentMySqlInsert("teamyeardata")
.WithColumns("id", "yearId", "teamId")
.WithValues(p.Id, p.Year.Id, p.TeamId)
.ToString());
}
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);
}
