public ProcessingResult ProcessAccessTableToJsonFile(string connString, string queryBegin, string queryEnd, string table, string file)
{
var result = new ProcessingResult();
Debug.Print("ProcessAccessTableToJsonFile: Processing " + table);
var last = DateTime.Now;
var sql = queryBegin + " " + table + " " + queryEnd;
var dsView = new DataSet();
var adp = new OleDbDataAdapter(sql, connString);
adp.Fill(dsView, "AccessData");
adp.Dispose();
var tbl = dsView.Tables["AccessData"];
result.toProcess = tbl.Rows.Count;
SaveObjToJsonFile(tbl, _folderPath + file + ".json");
result.modified = tbl.Rows.Count;
Debug.Print("ProcessAccessTableToJsonFile: Processed " + table);
var diffFromLast = DateTime.Now - last;
Debug.Print("TimeToProcess: " + diffFromLast.ToString());
return result;
}
public ProcessingResult ProcessScoreSheetEntriesIntoGameResults(int startingGameId, int endingGameId)
{
var result = new ProcessingResult();
DateTime last = DateTime.Now;
TimeSpan diffFromLast = new TimeSpan();
try
{
// get list of game entries for these games (use game just in case there was no score sheet entries...0-0 game with no penalty minutes)
var games = _context.Games.Where(s => s.GameId >= startingGameId && s.GameId <= endingGameId).ToList();
result.toProcess = games.Count;
// get a list of periods
var periods = new int[] { 1, 2, 3, 4 };
var modifiedCount = 0;
// loop through each game
for (var g = 0; g < games.Count; g++)
{
var gameId = games[g].GameId;
int scoreHomeTeamTotal = 0;
int scoreAwayTeamTotal = 0;
int penaltyHomeTeamTotal = 0;
int penaltyAwayTeamTotal = 0;
// look up the home and away team id
var homeGameTeam = _context.GameTeams.Where(x => x.GameId == gameId && x.HomeTeam == true).FirstOrDefault();
var awayGameTeam = _context.GameTeams.Where(x => x.GameId == gameId && x.HomeTeam == false).FirstOrDefault();
if (homeGameTeam == null || awayGameTeam == null)
{
throw new ArgumentNullException("homeGameTeam and/or awayGameTeam cannot be null");
}
#region loop through each period
for (var p = 0; p < periods.Length; p++)
{
var period = periods[p];
var scoreHomeTeamPeriod = 0;
var scoreAwayTeamPeriod = 0;
#region process all score sheet entries for this specific game/period
var scoreSheetEntries = _context.ScoreSheetEntryProcessedGoals.Where(s => s.GameId == gameId && s.Period == period).ToList();
for (var s = 0; s < scoreSheetEntries.Count; s++)
{
var scoreSheetEntry = scoreSheetEntries[s];
if (scoreSheetEntry.HomeTeam)
{
scoreHomeTeamPeriod++;
scoreHomeTeamTotal++;
}
else
{
scoreAwayTeamPeriod++;
scoreAwayTeamTotal++;
}
}
#endregion
#region create and save (or update) the home and away teams GameScore by period
var homeGameScore = new GameScore(sid: homeGameTeam.SeasonId, tid: homeGameTeam.TeamId, gid: gameId, per: period, score: scoreHomeTeamPeriod);
_context.GameScores.Add(homeGameScore);
var awayGameScore = new GameScore(sid: awayGameTeam.SeasonId, tid: awayGameTeam.TeamId, gid: gameId, per: period, score: scoreAwayTeamPeriod);
_context.GameScores.Add(awayGameScore);
#endregion
#region process all score sheet entry penalties for this specific game/period
var scoreSheetEntryPenalties = _context.ScoreSheetEntryPenalties.Where(s => s.GameId == gameId && s.Period == period).ToList();
for (var s = 0; s < scoreSheetEntryPenalties.Count; s++)
{
var scoreSheetEntryPenalty = scoreSheetEntryPenalties[s];
if (scoreSheetEntryPenalty.HomeTeam)
{
penaltyHomeTeamTotal = penaltyHomeTeamTotal + scoreSheetEntryPenalty.PenaltyMinutes;
}
else
{
penaltyAwayTeamTotal = penaltyAwayTeamTotal + scoreSheetEntryPenalty.PenaltyMinutes;
}
}
#endregion
}
#endregion
#region create and save (or update) the home and away teams GameScore for game
var finalPeriod = 0;
var homeFinalGameScore = new GameScore(sid: homeGameTeam.SeasonId, tid: homeGameTeam.TeamId, gid: gameId, per: finalPeriod, score: scoreHomeTeamTotal);
_context.GameScores.Add(homeFinalGameScore);
var awayFinalGameScore = new GameScore(sid: awayGameTeam.SeasonId, tid: awayGameTeam.TeamId, gid: gameId, per: finalPeriod, score: scoreAwayTeamTotal);
_context.GameScores.Add(awayFinalGameScore);
#endregion
#region create and save (or update) the home and away teams GameOutcome for game
// save game results for the game
string homeResult = "T";
string awayResult = "T";
if (scoreHomeTeamTotal > scoreAwayTeamTotal)
{
homeResult = "W";
awayResult = "L";
}
else if (scoreHomeTeamTotal < scoreAwayTeamTotal)
{
homeResult = "L";
awayResult = "W";
}
var gameRosters = _context.GameRosters.Where(x=>x.GameId == gameId).ToList();
if (gameRosters == null)
{
throw new ArgumentNullException("gameRosters");
}
var gameSubCounts = gameRosters
.GroupBy(x => new { x.TeamId })
.Select(grp => new
{
TeamId = grp.Key.TeamId,
Subs = grp.Sum(x => Convert.ToInt32(x.Sub))
})
.ToList();
var homeSubCount = gameSubCounts.Where(x => x.TeamId == homeGameTeam.TeamId).FirstOrDefault().Subs;
var awaySubCount = gameSubCounts.Where(x => x.TeamId == awayGameTeam.TeamId).FirstOrDefault().Subs;
var homeGameOutcome = new GameOutcome(
sid: homeGameTeam.SeasonId,
tid: homeGameTeam.TeamId,
gid: gameId,
res: homeResult,
gf: scoreHomeTeamTotal,
ga: scoreAwayTeamTotal,
pim: penaltyHomeTeamTotal,
over: false,
otid: awayGameTeam.TeamId,
subs: homeSubCount
);
var awayGameOutcome = new GameOutcome(
sid: awayGameTeam.SeasonId,
tid: awayGameTeam.TeamId,
gid: gameId,
res: awayResult,
gf: scoreAwayTeamTotal,
ga: scoreHomeTeamTotal,
pim: penaltyAwayTeamTotal,
over: false,
otid: homeGameTeam.TeamId,
subs: awaySubCount
);
_context.GameOutcomes.Add(homeGameOutcome);
_context.GameOutcomes.Add(awayGameOutcome);
modifiedCount += _context.SaveChanges();
#endregion
}
//_logger.Write("ProcessScoreSheetEntriesIntoGameResults: savedGameOutcomes:" + modifiedCount);
result.modified = modifiedCount;
}
catch (Exception ex)
{
result.modified = -2;
result.error = ex.Message;
//_logger.Write(ex);
}
diffFromLast = DateTime.Now - last;
result.time = diffFromLast.ToString();
return result;
}
public ProcessingResult ProcessScoreSheetEntrySubs(int startingGameId, int endingGameId)
{
var result = new ProcessingResult();
DateTime last = DateTime.Now;
TimeSpan diffFromLast = new TimeSpan();
try
{
var scoreSheetEntrySubs = _context.ScoreSheetEntrySubs.Where(x => x.GameId >= startingGameId && x.GameId <= endingGameId).ToList();
var players = _context.Players.ToList();
var games = _context.Games.Where(x => x.GameId >= startingGameId && x.GameId <= endingGameId).ToList();
var gameTeams = _context.GameTeams.Where(x => x.GameId >= startingGameId && x.GameId <= endingGameId).ToList();
var teamRosters = _context.TeamRosters.ToList();
result.toProcess = scoreSheetEntrySubs.Count;
var scoreSheetEntrySubsProcessed = DeriveScoreSheetEntryProcessedSubs(scoreSheetEntrySubs, games, gameTeams, teamRosters, players);
_context.ScoreSheetEntryProcessedSubs.AddRange(scoreSheetEntrySubsProcessed);
result.modified = _context.SaveChanges();
// AUDIT ScoreSheetEntrySubs and ScoreSheetEntrySubsProcessed should have same ids
var inputs = _context.ScoreSheetEntrySubs.Where(x => x.GameId >= startingGameId && x.GameId <= endingGameId).ToList();
var outputs = _context.ScoreSheetEntryProcessedSubs.Where(x => x.GameId >= startingGameId && x.GameId <= endingGameId).ToList();
if (inputs.Count != outputs.Count)
{
result.error = "Error processing ScoreSheetEntrySubs. The ScoreSheetEntrySubs count (" + inputs.Count + ") does not match ScoreSheetEntryProcessedSubs count (" + outputs.Count + ")";
}
else
{
foreach (var input in inputs)
{
var output = outputs.Where(x => x.ScoreSheetEntrySubId == input.ScoreSheetEntrySubId).FirstOrDefault();
if (output == null)
{
result.error = "Error processing ScoreSheetEntrySubs. The ScoreSheetEntrySubId (" + input.ScoreSheetEntrySubId + ") is missing from ScoreSheetEntryProcessedSubs";
}
}
}
}
catch (Exception ex)
{
result.modified = -2;
result.error = ex.Message;
//_logger.Write(ex);
//ErrorHandlingService.PrintFullErrorMessage(ex);
}
diffFromLast = DateTime.Now - last;
result.time = diffFromLast.ToString();
return result;
}
public ProcessingResult UpdateScoreSheetEntriesWithPPAndSH(int startingGameId, int endingGameId)
{
var result = new ProcessingResult();
DateTime last = DateTime.Now;
TimeSpan diffFromLast = new TimeSpan();
try
{
var scoreSheetEntryGoals = _context.ScoreSheetEntryProcessedGoals.Where(x => x.GameId >= startingGameId && x.GameId <= endingGameId).ToList();
var scoreSheetEntryPenalties = _context.ScoreSheetEntryProcessedPenalties.Where(x => x.GameId >= startingGameId && x.GameId <= endingGameId).ToList();
result.toProcess = scoreSheetEntryGoals.Count;
var scoreSheetEntryProcessedGoals = UpdateScoreSheetEntryProcessedGoalsWithPPAndSH(scoreSheetEntryGoals, scoreSheetEntryPenalties);
_context.ScoreSheetEntryProcessedGoals.AddRange(scoreSheetEntryProcessedGoals);
result.modified = _context.SaveChanges();
}
catch (Exception ex)
{
result.modified = -2;
result.error = ex.Message;
//_logger.Write(ex);
//ErrorHandlingService.PrintFullErrorMessage(ex);
}
diffFromLast = DateTime.Now - last;
result.time = diffFromLast.ToString();
return result;
}
public HttpResponseMessage Post([FromBody]AdminDataProcessingModel model)
{
ProcessingResult results = new ProcessingResult();
//ProcessingResult result1, result2, result3, result4, result5;
//switch (model.action)
//{
// case "ProcessScoreSheetEntries":
// results = _repo.ProcessScoreSheetEntryPenalties(model.startingGameId, model.endingGameId);
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result2 = _repo.ProcessScoreSheetEntries(model.startingGameId, model.endingGameId);
// results.error = result2.error;
// results.toProcess += result2.toProcess;
// results.modified += result2.modified;
// }
// break;
// case "ProcessScoreSheetEntriesIntoGameResults":
// results = _repo.ProcessScoreSheetEntriesIntoGameResults(model.startingGameId, model.endingGameId);
// break;
// case "ProcessGameResultsIntoTeamStandings":
// results = _repo.ProcessGameResultsIntoTeamStandings(model.seasonId, model.playoffs, model.startingGameId, model.endingGameId);
// break;
// case "ProcessScoreSheetEntriesIntoPlayerStats":
// results = _repo.ProcessScoreSheetEntriesIntoPlayerStats(model.startingGameId, model.endingGameId);
// break;
// case "ProcessPlayerStatsIntoWebStats":
// results = _repo.ProcessPlayerStatsIntoWebStats();
// break;
// case "ProcessAll":
// result1 = _repo.ProcessScoreSheetEntryPenalties(model.startingGameId, model.endingGameId);
// results.error = result1.error;
// results.toProcess = result1.toProcess;
// results.modified = result1.modified;
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result2 = _repo.ProcessScoreSheetEntries(model.startingGameId, model.endingGameId);
// results.error = result2.error;
// results.toProcess += result2.toProcess;
// results.modified += result2.modified;
// }
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result2 = _repo.ProcessScoreSheetEntriesIntoGameResults(model.startingGameId, model.endingGameId);
// results.error = result2.error;
// results.toProcess += result2.toProcess;
// results.modified += result2.modified;
// }
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result3 = _repo.ProcessGameResultsIntoTeamStandings(model.seasonId, model.playoffs, model.startingGameId, model.endingGameId);
// results.error = result3.error;
// results.toProcess += result3.toProcess;
// results.modified += result3.modified;
// }
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result4 = _repo.ProcessScoreSheetEntriesIntoPlayerStats(model.startingGameId, model.endingGameId);
// results.error = result4.error;
// results.toProcess += result4.toProcess;
// results.modified += result4.modified;
// }
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result5 = _repo.ProcessPlayerStatsIntoWebStats();
// results.error = result5.error;
// results.toProcess += result5.toProcess;
// results.modified += result5.modified;
// }
// break;
// case "AccessDbToJson":
// results = _accessDbService.SaveTablesToJson();
// break;
// case "LoadScoreSheetEntriesFromAccessDbToJson":
// results = _contextService.LoadScoreSheetEntriesFromAccessDBJson();
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result1 = _contextService.LoadScoreSheetEntryPenaltiesFromAccessDBJson();
// results.error = result1.error;
// results.toProcess += result1.toProcess;
// results.modified += result1.modified;
// }
// if (string.IsNullOrWhiteSpace(results.error))
// {
// result1 = _contextService.LoadGameRostersFromAccessDBJson();
// results.error = result1.error;
// results.toProcess += result1.toProcess;
// results.modified += result1.modified;
// }
// break;
// default:
// results = new ProcessingResult() { toProcess = -2, modified = -2, time = "n/a", error = "The model.Action (" + model.action + ") is not implemented!" };
// break;
//}
return Request.CreateResponse(HttpStatusCode.Accepted, new { results = results });
}
public ProcessingResult SaveTablesToJson()
{
var results = new ProcessingResult();
results.toProcess = 0;
results.modified = 0;
DateTime first = DateTime.Now;
DateTime last = DateTime.Now;
TimeSpan diffFromFirst = new TimeSpan();
var connString = System.Configuration.ConfigurationManager.ConnectionStrings["LO30AccessDB"].ConnectionString;
List<AccessTableList> accessTables = new List<AccessTableList>()
{
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="WHERE SEASON_ID>=54 ORDER BY SEASON_ID, GAME_ID", TableName="GAME", FileName="Games"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="WHERE SEASON_ID>=54 ORDER BY SEASON_ID, GAME_ID", TableName="GAME_ROSTER", FileName="GameRosters"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="WHERE SEASON_ID>=54 ORDER BY SEASON_ID, GAME_ID, PERIOD, TIME_REMAINING DESC", TableName="PENALTY_DETAIL", FileName="PenaltyDetails"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT PLAYER_ID, PLAYER_FIRST_NAME, PLAYER_LAST_NAME, PLAYER_SUFFIX, PLAYER_POSITION, SHOOTS FROM", QueryEnd="ORDER BY PLAYER_ID", TableName="PLAYER", FileName="Players"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="WHERE SEASON_ID>=54 ORDER BY SEASON_ID, PLAYER_ID", TableName="PLAYER_RATING", FileName="PlayerRatings"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY PLAYER_ID", TableName="PLAYER_STATUS", FileName="PlayerStatuses"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY PENALTY_ID", TableName="REF_PENALTY", FileName="Penalties"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SEASON_ID", TableName="REF_SEASON", FileName="Seasons"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY STATUS_ID", TableName="REF_STATUS", FileName="Statuses"},
new AccessTableList(){ConnString=_connStringSSE, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SCORE_SHEET_ENTRY_ID", TableName="SCORE_SHEET_ENTRY", FileName="ScoreSheetEntries"},
new AccessTableList(){ConnString=_connStringSSE, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SCORE_SHEET_ENTRY_PENALTY_ID", TableName="SCORE_SHEET_ENTRY_PENALTY", FileName="ScoreSheetEntryPenalties"},
new AccessTableList(){ConnString=_connStringSSE, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SCORE_SHEET_ENTRY_SUB_ID", TableName="SCORE_SHEET_ENTRY_SUB", FileName="ScoreSheetEntrySubs"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="WHERE SEASON_ID>=54 ORDER BY SEASON_ID, GAME_ID, PERIOD, TIME_REMAINING DESC", TableName="SCORING_DETAIL", FileName="ScoringDetails"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SEASON_ID, TEAM_ID", TableName="TEAM", FileName="Teams"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="WHERE SEASON_ID>=54 ORDER BY SEASON_ID, TEAM_ID, PLAYOFF_SEASON_IND DESC", TableName="TEAM_ROSTER", FileName="TeamRosters"},
// addtl tables
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SEASON_ID, PLAYER_ID", TableName="FACT_PLAYER_STATS", FileName="FactPlayerStats"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SEASON_ID, PLAYER_ID", TableName="FACT_SUB_PLAYER_STATS", FileName="FactSubPlayerStats"},
new AccessTableList(){ConnString=_connString, QueryBegin="SELECT * FROM", QueryEnd="ORDER BY SEASON_ID, TEAM_ID", TableName="FACT_TEAM_STATS", FileName="FactTeamStats"}
};
foreach (var table in accessTables)
{
var result = ProcessAccessTableToJsonFile(table.ConnString, table.QueryBegin, table.QueryEnd, table.TableName, table.FileName);
results.error = result.error;
results.toProcess += result.toProcess;
results.modified += result.modified;
if (!string.IsNullOrWhiteSpace(result.error))
{
break;
}
}
diffFromFirst = DateTime.Now - first;
Debug.Print("Total TimeToProcess: " + diffFromFirst.ToString());
results.time = diffFromFirst.ToString();
return results;
}
