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; }