static void ProcessStatsheetDirectory(string connString, string directoryPath, bool assumeATeams, out HashSet <int> years)
{
years = new HashSet <int>();
if (!Directory.Exists(directoryPath))
{
Console.WriteLine(directoryPath + " does not exist!");
}
else
{
Stopwatch timer = new Stopwatch();
DerbyDataImporter importer = new DerbyDataImporter();
foreach (string path in Directory.GetFiles(directoryPath, "*.xlsx"))
{
if (path.Contains('~'))
{
continue;
}
Console.WriteLine("--------------------");
Console.WriteLine("Processing " + path);
timer.Restart();
StatbookModel model = StatbookReader.StatbookReader.ReadStatbook(path);
years.Add(model.Date.Year);
importer.Import(connString, model, assumeATeams);
timer.Stop();
Console.WriteLine("Finished Processing " + path + ": " + timer.Elapsed.TotalSeconds);
}
}
}
private void IntegrateStarPasses(StatbookModel statbookModel, List <JamScoreModel> jamScoreModelList)
{
if (statbookModel.Scores.Count != jamScoreModelList.Count)
{
// this is unexpected
throw new FormatException("score list length mismatch");
}
for (int i = 0; i < jamScoreModelList.Count; i++)
{
if (jamScoreModelList[i].AwayStarPass != null)
{
jamScoreModelList[i].AwayStarPass.PlayerNumber =
statbookModel.Lineups[i].AwayLineup.First(l => l.IsPivot).PlayerNumber;
statbookModel.Scores[i].AwayJammer.JamTotal = jamScoreModelList[i].AwayJammer.JamTotal;
statbookModel.Scores[i].AwayStarPass = jamScoreModelList[i].AwayStarPass;
}
if (jamScoreModelList[i].HomeStarPass != null)
{
jamScoreModelList[i].HomeStarPass.PlayerNumber =
statbookModel.Lineups[i].HomeLineup.First(l => l.IsPivot).PlayerNumber;
statbookModel.Scores[i].HomeJammer.JamTotal = jamScoreModelList[i].HomeJammer.JamTotal;
statbookModel.Scores[i].HomeStarPass = jamScoreModelList[i].HomeStarPass;
}
}
}
//const string Rinxter_Url = "http://rinxter-test.cloudapp.net/rx/ds";
//private string _statbookDownloadDirectory;
public void Import(string connectionString, bool assumeATeams)
{
// figure out bouts to import
List <int> rinxterBoutIdList = GetRinxterBoutList();
foreach (int rinxterBoutId in rinxterBoutIdList)
{
StatbookModel statbookModel = null;
Task t1 = Task.Run(() => {
// download statbook
string path = DownloadRinxterStatbook(rinxterBoutId);
// process statbook via the IGRF translator
statbookModel = StatbookReader.ReadStatbook(path);
});
List <JamScoreModel> jamScoreModel = null;
// fire off thread to get real scoring from rinxter
Task t2 = Task.Run(() => {
RinxterScoresModel rinxterModel = GetRinxterScoringData(rinxterBoutId);
jamScoreModel = TranslateRinxterScoringData(rinxterModel);
});
// wait for both processes to complete
Task.WaitAll(t1, t2);
// delete scoring data from statbook object
IntegrateStarPasses(statbookModel, jamScoreModel);
// inject translated rinxter stat data
Console.WriteLine("Processing " + rinxterBoutId.ToString());
new DerbyDataImporter().Import(connectionString, statbookModel, assumeATeams);
Console.WriteLine(" Finished processing " + rinxterBoutId.ToString());
Console.WriteLine("====================");
}
}
public void Import(string connectionString, StatbookModel statbook, bool assumeATeams)
{
_connection = new SqlConnection(connectionString);
try
{
_connection.Open();
_transaction = _connection.BeginTransaction();
// insert leagues
LeagueGateway leagueGateway = new LeagueGateway(_connection, _transaction);
var leagues = leagueGateway.GetAllLeagues();
League homeLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.HomeTeam.LeagueName.ToLower());
League awayLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.AwayTeam.LeagueName.ToLower());
int maxID = leagues.Select(l => l.ID).Max();
if (homeLeague == null)
{
homeLeague = leagueGateway.GetLeague(maxID + 1, statbook.HomeTeam.LeagueName, statbook.Date, false);
maxID++;
}
if (awayLeague == null)
{
awayLeague = leagueGateway.GetLeague(maxID + 1, statbook.AwayTeam.LeagueName, statbook.Date, false);
maxID++;
}
// insert teams
TeamGateway teamGateway = new TeamGateway(_connection, _transaction);
Team homeTeam, awayTeam;
if (assumeATeams)
{
homeTeam = teamGateway.GetATeam(homeLeague.ID);
awayTeam = teamGateway.GetATeam(awayLeague.ID);
}
else
{
homeTeam = teamGateway.GetTeam(statbook.HomeTeam.Name, homeLeague.ID, "A", false);
awayTeam = teamGateway.GetTeam(statbook.AwayTeam.Name, awayLeague.ID, "A", false);
}
// insert bout
BoutGateway boutGateway = new BoutGateway(_connection, _transaction);
if (!boutGateway.DoesBoutExist(homeTeam.ID, awayTeam.ID, statbook.Date))
{
Bout bout = boutGateway.GetBout(homeTeam.ID, awayTeam.ID, statbook.Date);
BoutDataImport(statbook, bout, homeTeam, awayTeam);
}
else
{
// bout already exists
Console.WriteLine(string.Format("Bout between {0} and {1} on {2} already exists.", homeTeam.Name, awayTeam.Name, statbook.Date));
}
_transaction.Commit();
}
finally
{
_connection.Close();
}
}
protected StatbookModel ProcessIrgf(ExcelWorksheet irgf)
{
StatbookModel statbook = new StatbookModel();
statbook.HomeTeam = new TeamModel();
statbook.AwayTeam = new TeamModel();
statbook.HomeTeam.LeagueName = irgf.Cells[_cells.HomeLeagueCell].Value.ToString().Trim();
statbook.HomeTeam.Name = irgf.Cells[_cells.HomeTeamNameCell].Value.ToString().Trim();
statbook.AwayTeam.LeagueName = irgf.Cells[_cells.AwayLeagueCell].Value.ToString().Trim();
statbook.AwayTeam.Name = irgf.Cells[_cells.AwayTeamNameCell].Value.ToString().Trim();
string dateString = irgf.Cells[_cells.BoutDateCell].Value.ToString().Trim();// +" " + irgf.Cells["H5"].Value.ToString();
if (dateString.Contains("/") || dateString.Contains("-"))
{
statbook.Date = Convert.ToDateTime(dateString);
}
else
{
statbook.Date = DateTime.FromOADate(Convert.ToDouble(dateString));
}
ExcelRange homeStart = irgf.Cells[_cells.HomeRosterCell];
ExcelRange awayStart = irgf.Cells[_cells.AwayRosterCell];
statbook.HomeTeam.Players = new List <PlayerModel>();
statbook.AwayTeam.Players = new List <PlayerModel>();
for (int i = 0; i < 20; i++)
{
object numberCell = homeStart.Offset(i, 0).Value;
// ignore numbers that have * in them; these are alts
if (numberCell != null && !string.IsNullOrWhiteSpace(numberCell.ToString()) && !numberCell.ToString().Contains("*"))
{
PlayerModel player = new PlayerModel();
player.Number = numberCell.ToString().Trim().TrimEnd('*');
player.Name = homeStart.Offset(i, 1).Value.ToString();
statbook.HomeTeam.Players.Add(player);
}
numberCell = awayStart.Offset(i, 0).Value;
if (numberCell != null && !string.IsNullOrWhiteSpace(numberCell.ToString()))
{
PlayerModel player = new PlayerModel();
player.Number = numberCell.ToString().Trim().TrimEnd('*');
player.Name = awayStart.Offset(i, 1).Value.ToString();
statbook.AwayTeam.Players.Add(player);
}
}
return(statbook);
}
protected StatbookModel CreateStatbook(ExcelWorkbook workbook)
{
ExcelWorksheet irgf = workbook.Worksheets["IGRF"];
ExcelWorksheet score = workbook.Worksheets["Score"];
ExcelWorksheet lineups = workbook.Worksheets["Lineups"];
ExcelWorksheet fouls = workbook.Worksheets["Penalties"];
StatbookModel model = ProcessIrgf(irgf);
model.Lineups = ProcessLineups(lineups);
model.Penalties = ProcessPenalties(fouls);
model.Scores = ProcessScores(score);
return(model);
}
private void BoutDataImport(StatbookModel statbook, Bout bout, Team homeTeam, Team awayTeam)
{
// import players
Dictionary <string, Player> homePlayerMap = CreatePlayerMap(homeTeam, statbook.HomeTeam.Players);
Dictionary <string, Player> awayPlayerMap = CreatePlayerMap(awayTeam, statbook.AwayTeam.Players);
// import jams
List <Jam> jamList = CreateJamList(bout, statbook.Lineups);
// import player jams
Dictionary <int, List <JamPlayer> > jamPlayerMap = CreateJamPlayerMap(homePlayerMap, awayPlayerMap, jamList, statbook.Lineups);
// import scores
AddScores(homePlayerMap, awayPlayerMap, jamList, statbook.Scores);
// import penalties/box times
AddPenaltyServices(homePlayerMap, awayPlayerMap, jamList, statbook.Lineups, statbook.Scores, statbook.Penalties);
}
public void Import(string connectionString, StatbookModel statbook)
{
_connection = new SqlConnection(connectionString);
try
{
_connection.Open();
_transaction = _connection.BeginTransaction();
// insert leagues
LeagueGateway leagueGateway = new LeagueGateway(_connection, _transaction);
var leagues = leagueGateway.GetAllLeagues();
League homeLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.HomeTeam.LeagueName.ToLower());
League awayLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.AwayTeam.LeagueName.ToLower());
int maxID = leagues.Select(l => l.ID).Max();
if (homeLeague == null || awayLeague == null)
{
throw new InvalidOperationException("Bad league name");
}
// for the basic importer, we'll just take whatever A team this league has, rather than worrying about validating the team name
TeamGateway teamGateway = new TeamGateway(_connection, _transaction);
Team homeTeam = teamGateway.GetATeam(homeLeague.ID);
Team awayTeam = teamGateway.GetATeam(awayLeague.ID);
// insert bout
BoutGateway boutGateway = new BoutGateway(_connection, _transaction);
if (!boutGateway.DoesBoutExist(homeTeam.ID, awayTeam.ID, statbook.Date))
{
Bout bout = boutGateway.GetBout(homeTeam.ID, awayTeam.ID, statbook.Date);
BoutDataImport(statbook, bout, homeTeam, awayTeam);
}
else
{
// bout already exists
Console.WriteLine(string.Format("Bout between {0} and {1} on {2} already exists.", homeTeam.Name, awayTeam.Name, statbook.Date));
}
_transaction.Commit();
}
finally
{
_connection.Close();
}
}
static void BasicProcessStatsheetDirectory(string connString, string directoryPath)
{
if (!Directory.Exists(directoryPath))
{
Console.WriteLine(directoryPath + " does not exist!");
}
else
{
Stopwatch timer = new Stopwatch();
BasicDataImporter importer = new BasicDataImporter();
foreach (string path in Directory.GetFiles(directoryPath, "*.xlsx"))
{
Console.WriteLine("--------------------");
Console.WriteLine("BASIC PROCESS: " + path);
timer.Restart();
StatbookModel model = StatbookReader.StatbookReader.ReadStatbook(path);
importer.Import(connString, model);
timer.Stop();
Console.WriteLine("Finished BASIC PROCESS " + path + ": " + timer.Elapsed.TotalSeconds);
}
}
}
