public NBAStatsForm()
{
InitializeComponent();
this.ExeDirectory = AppDomain.CurrentDomain.BaseDirectory;
var dbContext = new NBAStatisticsDbContext();
if (dbContext.Conferences.Count() == 0)
{
dbContext.Conferences.Add(new NBAStatistics.Models.Conference
{
Name = "East"
});
dbContext.Conferences.Add(new NBAStatistics.Models.Conference
{
Name = "West"
});
}
if (dbContext.Seasons.Count() == 0)
{
dbContext.Seasons.Add(new NBAStatistics.Models.Season
{
SeasonId = "2016-17"
});
dbContext.Seasons.Add(new NBAStatistics.Models.Season
{
SeasonId = "2015-16"
});
}
dbContext.SaveChanges();
}
private void button1_Click(object sender, EventArgs e)
{
var nbaStastsContext = new NBAStatisticsDbContext();
var teamsRepository = new EfRepository <NBAStatistics.Models.StandingsByDay>(nbaStastsContext);
var pdfService = new PdfReportService(teamsRepository);
pdfService.GeneratePdf();
}
private void button4_Click(object sender, EventArgs e)
{
var xmlHandler = new PlayedGamesXmlHandler();
var reportService = new XmlReportService(xmlHandler);
var nbaStatsContext = new NBAStatisticsDbContext();
var dailyStandingsRepository = new EfRepository <NBAStatistics.Models.StandingsByDay>(nbaStatsContext);
reportService.CreatePlayedGamesReport(dailyStandingsRepository);
}
public static async Task Import()
{
var credentials = MongoCredential.CreateCredential(DbName, User, Pass);
var settings = new MongoClientSettings
{
Server = new MongoServerAddress(DbHost, DbPort),
Credentials = new List <MongoCredential> {
credentials
}
};
var client = new MongoClient(settings);
var mongoDb = client.GetDatabase(DbName);
var dbContext = new NBAStatisticsDbContext();
await Task.Run(async() =>
{
var mongoSeasons = mongoDb.GetCollection <NBAStatistics.Data.FillMongoDB.Models.Season>("Seasons").AsQueryable().ToList();
// Load all teams from the database into the context
dbContext.Teams.Load();
foreach (var season in mongoSeasons)
{
foreach (var team in season.Teams)
{
//var teamInDb = dbContext.Teams.Local
// .FirstOrDefault(t => t.TeamId == team.TeamId);
var teamInDb = dbContext.Teams.Local
.SingleOrDefault(t => t.TeamId == team.TeamId); // runs in memory
if (teamInDb == null)
{
dbContext.Teams.Add(new Team
{
TeamId = team.TeamId,
Name = team.Name
});
}
}
}
await dbContext.SaveChangesAsync();
var mongoPlayers = mongoDb.GetCollection <NBAStatistics.Data.FillMongoDB.Models.Player>("Players").AsQueryable().ToList();
// Load all players from the database into the context
dbContext.Players.Load();
dbContext.Teams.Load();
foreach (var player in mongoPlayers)
{
var playerNameParts = player.PlayerName.Split(new char[] { ' ' });
// throws an exception if there is more than 1 element in the sequence
var playerInDb = dbContext.Players.Local
.SingleOrDefault(p => p.AdditionalInfo.PlayerId == player.PlayerId); // runs in memory
if (playerInDb == null)
{
dbContext.Players.Add(new Player
{
FirstName = playerNameParts.Length > 0 ? playerNameParts[0] : "",
LastName = playerNameParts.Length > 1 ? playerNameParts[1] : "",
AdditionalInfo = new PlayerInfo
{
PlayerId = player.PlayerId,
Birthday = DateTime.ParseExact(player.BirthDate, "MMM dd, yyyy", CultureInfo.InvariantCulture),
Height = ConvertHeightFromFeetsInchesToCentimeters(player.Height),
Weight = PoundsToKilogram(player.Weight)
},
School = string.IsNullOrEmpty(player.School) ? null : new School
{
Name = player.School
},
Position = player.Position,
TeamId = dbContext.Teams.Local
.Single(t => t.TeamId == player.TeamId)
.Id
});
}
}
await dbContext.SaveChangesAsync();
});
}
public static async Task ImportFromZipFile(string zipPath)
{
var dbContext = new NBAStatisticsDbContext();
await Task.Run(() =>
{
try
{
var directoryPath = zipPath.Substring(0, zipPath.LastIndexOf('\\'));
string directoryWithReports = $"{directoryPath}\\Reports\\";
if (Directory.Exists(directoryWithReports))
{
Directory.Delete(directoryWithReports, true);
}
// If the directory already exists, this method does not create a new directory
DirectoryInfo di = Directory.CreateDirectory(directoryWithReports);
using (ZipArchive archive = ZipFile.OpenRead(zipPath))
{
foreach (ZipArchiveEntry entry in archive.Entries)
{
var directoryName = entry.FullName.Substring(0, 11);
var dateOfTheReport = DateTime.ParseExact(
directoryName,
"dd-MMM-yyyy",
CultureInfo.InvariantCulture);
var xlsFileName = entry.FullName.Substring(12);
var xlsPath = $"{directoryWithReports}{directoryName}_{xlsFileName}";
entry.ExtractToFile(xlsPath);
var connectionString = GetConnectionString(xlsPath, false);
using (var oleDbConnection = new OleDbConnection(connectionString))
{
oleDbConnection.Open();
var sheetNames = GetSheetNames(oleDbConnection);
var oleDbCommand =
new OleDbCommand("SELECT * FROM [" + sheetNames.First() + "]", oleDbConnection);
using (var oleDbAdapter = new OleDbDataAdapter(oleDbCommand))
{
var dataSet = new DataSet();
oleDbAdapter.Fill(dataSet);
// Load into the dbContext
dbContext.StandingsByDays.Load();
dbContext.Teams.Load();
dbContext.Conferences.Load();
using (var reader = dataSet.CreateDataReader())
{
while (reader.Read())
{
var teamId = (int)(double)reader["TEAM_ID"]; // TEAM_ID
var leagueId = reader["LEAGUE_ID"]; // LEAGUE_ID
var seasonId = (int)(double)reader["SEASON_ID"]; // SEASON_ID
var standingsDate = (DateTime)reader["STANDINGSDATE"]; // STANDINGSDATE
var conference = (string)reader["CONFERENCE"]; // CONFERENCE
var team = (string)reader["TEAM"]; // TEAM
var games = (byte)(double)reader["G"]; // G
var wins = (byte)(double)reader["W"]; // W
var losses = (byte)(double)reader["L"]; // L
var winningsPercentage = (double)reader["W_PCT"]; // W_PCT
var homeRecord = (string)reader["HOME_RECORD"]; // HOME_RECORD
var roadRecord = (string)reader["ROAD_RECORD"]; // ROAD_RECORD
var standingsByDayInDb = dbContext.StandingsByDays.Local
.SingleOrDefault(sbd => (sbd.TeamId == teamId) && (sbd.Date == standingsDate)); // runs in memory
// for tests only
//var t1 = dbContext.Teams.Local
// .SingleOrDefault(t => t.TeamId == teamId)
// .Id;
//var t2 = seasonId;
//var t3 = standingsDate;
//var t4 = dbContext.Conferences.Local
// .SingleOrDefault(c => c.Name == conference)
// .Id;
//var t5 = games;
//var t6 = wins;
//var t7 = losses;
//var t8 = winningsPercentage;
//var t9 = (byte)homeRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
// .Select(int.Parse)
// .ToArray()[0];
//var t10 = (byte)roadRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
// .Select(int.Parse)
// .ToArray()[0];
if (standingsByDayInDb == null)
{
dbContext.StandingsByDays.Add(new StandingsByDay
{
TeamId = dbContext.Teams.Local
.SingleOrDefault(t => t.TeamId == teamId)
.Id,
SeasonId = seasonId,
Date = standingsDate,
ConferenceId = dbContext.Conferences.Local
.SingleOrDefault(c => c.Name == conference)
.Id,
Games = games,
Wins = wins,
Loses = losses,
SuccessRate = Math.Round(winningsPercentage, 2),
HomeRecordWins = (byte)homeRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[0],
HomeRecordLosses = (byte)homeRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[1],
RoadRecordWins = (byte)roadRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[0],
RoadRecordLosses = (byte)roadRecord.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
.Select(int.Parse)
.ToArray()[1]
});
}
}
}
}
}
// Force clean up to release file handles
// source: http://stackoverflow.com/questions/2225087/the-process-cannot-access-the-file-because-it-is-being-used-by-another-process
GC.Collect();
}
}
dbContext.SaveChanges();
Directory.Delete(directoryWithReports, true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
});
}
public static async Task ImportFromMongoDB()
{
var credentials = MongoCredential.CreateCredential(DbName, User, Pass);
var settings = new MongoClientSettings
{
Server = new MongoServerAddress(DbHost, DbPort),
Credentials = new List <MongoCredential> {
credentials
}
};
var client = new MongoClient(settings);
var mongoDb = client.GetDatabase(DbName);
var dbContext = new NBAStatisticsDbContext();
await Task.Run(async() =>
{
var mongoSeasons = mongoDb.GetCollection <NBAStatistics.Data.FillMongoDB.Models.Season>("Seasons").AsQueryable().ToList();
// Load all teams from the database into the dbContext
dbContext.Teams.Load();
var nullTeamInDb = dbContext.Teams.Local
.SingleOrDefault(t => t.TeamId == 0); // runs in memory
if (nullTeamInDb == null)
{
// add fake team for players without team
dbContext.Teams.Add(new Team
{
TeamId = 0,
Name = "NoName",
Abbreviation = "",
Founded = 0,
City = new City
{
Name = "NoName",
Country = new Country
{
Name = "NoName"
}
},
Arena = new Arena
{
Name = "NoName"
},
HeadCoach = new HeadCoach
{
Name = "NoName"
}
});
}
foreach (var season in mongoSeasons)
{
foreach (var team in season.Teams)
{
//var teamInDb = dbContext.Teams.Local
// .FirstOrDefault(t => t.TeamId == team.TeamId);
var teamInDb = dbContext.Teams.Local
.SingleOrDefault(t => t.TeamId == team.TeamId); // runs in memory
if (teamInDb == null)
{
dbContext.Teams.Add(new Team
{
TeamId = team.TeamId,
Name = team.Name,
Abbreviation = team.Abbreviation,
Founded = team.Founded,
City = new City
{
Name = team.City,
Country = new Country
{
Name = team.Country
}
},
Arena = new Arena
{
Name = team.Arena
},
HeadCoach = new HeadCoach
{
Name = team.HeadCoach
}
});
}
}
}
await dbContext.SaveChangesAsync();
var mongoPlayers = mongoDb.GetCollection <NBAStatistics.Data.FillMongoDB.Models.Player>("Players").AsQueryable().ToList();
// Load all players from the database into the dbContext
dbContext.Players.Load();
dbContext.Teams.Load();
foreach (var playerMongoDB in mongoPlayers)
{
// throws an exception if there is more than 1 element in the sequence
var playerInDb = dbContext.Players.Local
.SingleOrDefault(p => p.AdditionalInfo.PlayerId == playerMongoDB.PlayerId); // runs in memory
if (playerInDb == null)
{
var player = new Player
{
FirstName = playerMongoDB.FirstName,
LastName = playerMongoDB.LastName,
AdditionalInfo = new PlayerInfo
{
PlayerId = playerMongoDB.PlayerId,
Birthday = playerMongoDB.BirthDate,
Height = string.IsNullOrEmpty(playerMongoDB.Height) ? null : ConvertHeightFromFeetsInchesToCentimeters(playerMongoDB.Height),
Weight = string.IsNullOrEmpty(playerMongoDB.Weight) ? null : PoundsToKilogram(playerMongoDB.Weight)
},
School = string.IsNullOrEmpty(playerMongoDB.School) ? null : new School
{
Name = playerMongoDB.School
},
Country = string.IsNullOrEmpty(playerMongoDB.Country) ?
new Country {
Name = "NoName"
} :
string.IsNullOrEmpty(playerMongoDB.Country.Trim()) ?
new Country {
Name = "NoName"
} :
new Country {
Name = playerMongoDB.Country
},
Position = playerMongoDB.Position,
RosterStatus = playerMongoDB.RosterStatus,
TeamId = dbContext.Teams.Local
.Single(t => t.TeamId == playerMongoDB.TeamId)
.Id
};
dbContext.Players.Local.Add(player);
}
}
await dbContext.SaveChangesAsync();
// Load all players and seasons from the database into the dbContext
dbContext.Players.Load();
dbContext.Seasons.Load();
dbContext.PlayerSeasonPointsPerGame.Load();
foreach (var playerMongoDB in mongoPlayers)
{
var player = dbContext.Players.Local
.FirstOrDefault(p => p.AdditionalInfo.PlayerId == playerMongoDB.PlayerId);
foreach (var sppg in playerMongoDB.SeasonPointsPerGame)
{
var seasonIdInMongoDB = sppg.Key;
var pointsPerGameInMongoDB = sppg.Value;
var seasonId = dbContext.Seasons.Local
.FirstOrDefault(s => s.SeasonId == seasonIdInMongoDB)
.Id;
var playerSeasonPointsPerGameRecordExists =
dbContext.PlayerSeasonPointsPerGame.Local
.Where(ps => ps.PlayerId == player.Id && ps.SeasonId == seasonId)
.Count() > 0;
if (playerSeasonPointsPerGameRecordExists)
{
// skip this record to DB
continue;
}
var playerSeasonPointsPerGame = new PlayerSeasonPointsPerGame
{
PlayerId = player.Id,
SeasonId = seasonId,
PointsPerGame = pointsPerGameInMongoDB
};
player.PlayerSeasonPointsPerGame.Add(playerSeasonPointsPerGame);
}
}
await dbContext.SaveChangesAsync();
});
}
