public async Task OnGetAsync() { Dictionary <int, string> teamNameLookup = new Dictionary <int, string>(); // build an ID-to-name mapping to improve perf var names = await _context.Teams.Where(t => t.Event == Event) .Select(t => new { t.ID, t.Name }) .ToListAsync(); names.ForEach(t => teamNameLookup[t.ID] = t.Name); // get the page data: puzzle, solve count, top three fastest var puzzlesData = await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null) .Where(s => s.SolvedTime != null && s.Puzzle.IsPuzzle) .GroupBy(state => state.Puzzle) .Select(g => new { Puzzle = g.Key, SolveCount = g.Count(), Fastest = g.OrderBy(s => s.SolvedTime - s.UnlockedTime).Take(3).Select(s => new { s.Team.ID, Time = s.SolvedTime - s.UnlockedTime }) }) .OrderByDescending(p => p.SolveCount).ThenBy(p => p.Puzzle.Name) .ToListAsync(); var puzzles = new List <PuzzleStats>(puzzlesData.Count); for (int i = 0; i < puzzlesData.Count; i++) { var data = puzzlesData[i]; var stats = new PuzzleStats() { Puzzle = data.Puzzle, SolveCount = data.SolveCount, SortOrder = i, Fastest = data.Fastest.Select(f => new FastRecord() { ID = f.ID, Name = teamNameLookup[f.ID], Time = f.Time }).ToArray() }; puzzles.Add(stats); } this.Puzzles = puzzles; }
public async Task OnGetAsync() { var teamsData = await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null) .Where(s => s.SolvedTime != null) .GroupBy(state => state.Team) .Select(g => new { Team = g.Key, SolveCount = g.Count(), Score = g.Sum(s => s.Puzzle.SolveValue), FinalMetaSolveTime = g.Where(s => s.Puzzle.IsFinalPuzzle).Select(s => s.SolvedTime).FirstOrDefault() }) .OrderBy(t => t.FinalMetaSolveTime).ThenByDescending(t => t.Score).ThenBy(t => t.Team.Name) .ToListAsync(); var teams = new List <TeamStats>(teamsData.Count); TeamStats prevStats = null; for (int i = 0; i < teamsData.Count; i++) { var data = teamsData[i]; var stats = new TeamStats() { Team = data.Team, SolveCount = data.SolveCount, Score = data.Score, FinalMetaSolveTime = data.FinalMetaSolveTime ?? DateTime.MaxValue }; if (prevStats == null || stats.FinalMetaSolveTime != prevStats.FinalMetaSolveTime || stats.Score != prevStats.Score) { stats.Rank = i + 1; } teams.Add(stats); prevStats = stats; } this.Teams = teams; }
public async Task OnGetAsync() { // get the puzzles and teams // TODO: Filter puzzles if an author; no need to filter teams. Revisit when authors exist. var puzzles = await _context.Puzzles.Where(p => p.Event == this.Event).Select(p => new PuzzleStats() { Puzzle = p }).ToListAsync(); var teams = await _context.Teams.Where(t => t.Event == this.Event).Select(t => new TeamStats() { Team = t }).ToListAsync(); // build an ID-based lookup for puzzles and teams var puzzleLookup = new Dictionary <int, PuzzleStats>(); puzzles.ForEach(p => puzzleLookup[p.Puzzle.ID] = p); var teamLookup = new Dictionary <int, TeamStats>(); teams.ForEach(t => teamLookup[t.Team.ID] = t); // tabulate solve counts and team scores var states = await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null).ToListAsync(); var stateList = new List <StateStats>(states.Count); foreach (var state in states) { // TODO: Is it more performant to prefilter the states if an author, or is this sufficient? if (!puzzleLookup.TryGetValue(state.PuzzleID, out PuzzleStats puzzle) || !teamLookup.TryGetValue(state.TeamID, out TeamStats team)) { continue; } stateList.Add(new StateStats() { Puzzle = puzzle, Team = team, UnlockedTime = state.UnlockedTime, SolvedTime = state.SolvedTime }); if (state.IsSolved) { puzzle.SolveCount++; team.SolveCount++; team.Score += puzzle.Puzzle.SolveValue; if (puzzle.Puzzle.IsFinalPuzzle) { team.FinalMetaSolveTime = state.SolvedTime.Value; } } } // sort puzzles by solve count, add the sort index to the lookup puzzles = puzzles.OrderByDescending(p => p.SolveCount).ThenBy(p => p.Puzzle.Name).ToList(); for (int i = 0; i < puzzles.Count; i++) { puzzles[i].SortOrder = i; } // sort teams by metameta/score, add the sort index to the lookup teams = teams.OrderBy(t => t.FinalMetaSolveTime).ThenByDescending(t => t.Score).ThenBy(t => t.Team.Name).ToList(); for (int i = 0; i < teams.Count; i++) { if (i == 0 || teams[i].FinalMetaSolveTime != teams[i - 1].FinalMetaSolveTime || teams[i].Score != teams[i - 1].Score) { teams[i].Rank = i + 1; } teams[i].SortOrder = i; } // Build the map var stateMap = new StateStats[puzzles.Count, teams.Count]; stateList.ForEach(state => stateMap[state.Puzzle.SortOrder, state.Team.SortOrder] = state); this.Puzzles = puzzles; this.Teams = teams; this.StateMap = stateMap; }
public async Task OnGetAsync(SortOrder?sort, PuzzleStateFilter?stateFilter) { this.Sort = sort; this.StateFilter = stateFilter; this.CurrentTeam = (await UserEventHelper.GetTeamForPlayer(_context, Event, LoggedInUser)); Dictionary <int, string> teamNameLookup = new Dictionary <int, string>(); // build an ID-to-name mapping to improve perf var names = await _context.Teams.Where(t => t.Event == Event) .Select(t => new { t.ID, t.Name }) .ToListAsync(); names.ForEach(t => teamNameLookup[t.ID] = t.Name); // get the page data: puzzle, solve count, top three fastest var puzzlesData = await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null) .Where(s => s.SolvedTime != null && s.Puzzle.IsPuzzle) .GroupBy(state => state.Puzzle) .Select(g => new { Puzzle = g.Key, SolveCount = g.Count(), Fastest = g.OrderBy(s => s.SolvedTime - s.UnlockedTime).Take(3).Select(s => new { s.Team.ID, Time = s.SolvedTime - s.UnlockedTime }), IsSolvedByUserTeam = g.Where(s => s.Team == this.CurrentTeam).Any() }) .OrderByDescending(p => p.SolveCount).ThenBy(p => p.Puzzle.Name) .ToListAsync(); var unlockedData = this.CurrentTeam == null ? null : (new HashSet <int>( await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null) .Where(state => state.Team == this.CurrentTeam && state.UnlockedTime != null) .Select(s => s.PuzzleID) .ToListAsync())); var puzzles = new List <PuzzleStats>(puzzlesData.Count); for (int i = 0; i < puzzlesData.Count; i++) { var data = puzzlesData[i]; // For players, we will hide puzzles they have not unlocked yet. if (EventRole == EventRole.play && (unlockedData == null || !unlockedData.Contains(data.Puzzle.ID))) { continue; } var stats = new PuzzleStats() { Puzzle = data.Puzzle, SolveCount = data.SolveCount, SortOrder = i, Fastest = data.Fastest.Select(f => new FastRecord() { ID = f.ID, Name = teamNameLookup[f.ID], Time = f.Time }).ToArray(), IsSolved = data.IsSolvedByUserTeam }; puzzles.Add(stats); } if (this.StateFilter == PuzzleStateFilter.Unsolved) { puzzles = puzzles.Where(stats => !stats.IsSolved).ToList(); } switch (sort ?? DefaultSort) { case SortOrder.RankAscending: puzzles.Sort((rhs, lhs) => (rhs.SortOrder - lhs.SortOrder)); break; case SortOrder.RankDescending: puzzles.Sort((rhs, lhs) => (lhs.SortOrder - rhs.SortOrder)); break; case SortOrder.CountAscending: puzzles.Sort((rhs, lhs) => (rhs.SolveCount - lhs.SolveCount)); break; case SortOrder.CountDescending: puzzles.Sort((rhs, lhs) => (lhs.SolveCount - rhs.SolveCount)); break; case SortOrder.PuzzleAscending: puzzles.Sort((rhs, lhs) => (String.Compare(rhs.Puzzle.Name, lhs.Puzzle.Name, StringComparison.OrdinalIgnoreCase))); break; case SortOrder.PuzzleDescending: puzzles.Sort((rhs, lhs) => (String.Compare(lhs.Puzzle.Name, rhs.Puzzle.Name, StringComparison.OrdinalIgnoreCase))); break; default: throw new ArgumentException($"unknown sort: {sort}"); } this.Puzzles = puzzles; }
public async Task <IActionResult> OnGetAsync(int?refresh) { if (refresh != null) { Refresh = refresh; } // get the puzzles and teams List <PuzzleStats> puzzles; if (EventRole == EventRole.admin) { puzzles = await _context.Puzzles.Where(p => p.Event == Event) .Select(p => new PuzzleStats() { Puzzle = p }) .ToListAsync(); } else { puzzles = await UserEventHelper.GetPuzzlesForAuthorAndEvent(_context, Event, LoggedInUser) .Select(p => new PuzzleStats() { Puzzle = p }) .ToListAsync(); } List <TeamStats> teams = await _context.Teams.Where(t => t.Event == Event) .Select(t => new TeamStats() { Team = t }) .ToListAsync(); // build an ID-based lookup for puzzles and teams Dictionary <int, PuzzleStats> puzzleLookup = new Dictionary <int, PuzzleStats>(); puzzles.ForEach(p => puzzleLookup[p.Puzzle.ID] = p); Dictionary <int, TeamStats> teamLookup = new Dictionary <int, TeamStats>(); teams.ForEach(t => teamLookup[t.Team.ID] = t); // tabulate solve counts and team scores List <PuzzleStatePerTeam> states = await PuzzleStateHelper.GetSparseQuery( _context, Event, null, null, EventRole == EventRole.admin?null : LoggedInUser).ToListAsync(); List <StateStats> stateList = new List <StateStats>(states.Count); foreach (PuzzleStatePerTeam state in states) { // TODO: Is it more performant to prefilter the states if an author, or is this sufficient? if (!puzzleLookup.TryGetValue(state.PuzzleID, out PuzzleStats puzzle) || !teamLookup.TryGetValue(state.TeamID, out TeamStats team)) { continue; } stateList.Add(new StateStats() { Puzzle = puzzle, Team = team, UnlockedTime = state.UnlockedTime, SolvedTime = state.SolvedTime, LockedOut = state.IsEmailOnlyMode }); if (state.SolvedTime != null) { puzzle.SolveCount++; team.SolveCount++; team.Score += puzzle.Puzzle.SolveValue; if (puzzle.Puzzle.IsCheatCode) { team.CheatCodeUsed = true; team.FinalMetaSolveTime = DateTime.MaxValue; } if (puzzle.Puzzle.IsFinalPuzzle && !team.CheatCodeUsed) { team.FinalMetaSolveTime = state.SolvedTime.Value; } } } // sort puzzles by group, then solve count, add the sort index to the lookup // but put non-puzzles to the end puzzles = puzzles.OrderByDescending(p => p.Puzzle.IsPuzzle) .ThenByDescending(p => p.Puzzle.Group) .ThenBy(p => p.SolveCount) .ThenBy(p => p.Puzzle.Name) .ToList(); for (int i = 0; i < puzzles.Count; i++) { puzzles[i].SortOrder = i; } // sort teams by metameta/score, add the sort index to the lookup teams = teams.OrderBy(t => t.FinalMetaSolveTime) .ThenByDescending(t => t.Score) .ThenBy(t => t.Team.Name) .ToList(); for (int i = 0; i < teams.Count; i++) { if (i == 0 || teams[i].FinalMetaSolveTime != teams[i - 1].FinalMetaSolveTime || teams[i].Score != teams[i - 1].Score) { teams[i].Rank = i + 1; } teams[i].SortOrder = i; } // Build the map var stateMap = new StateStats[puzzles.Count, teams.Count]; stateList.ForEach(state => stateMap[state.Puzzle.SortOrder, state.Team.SortOrder] = state); Puzzles = puzzles; Teams = teams; StateMap = stateMap; return(Page()); }
public async Task OnGetAsync(SortOrder?sort) { Sort = sort; var puzzleData = await _context.Puzzles .Where(p => p.Event == Event && p.IsPuzzle) .ToDictionaryAsync(p => p.ID, p => new { p.SolveValue, p.IsCheatCode, p.IsFinalPuzzle }); DateTime submissionEnd = Event.AnswerSubmissionEnd; var stateData = await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null) .Where(pspt => pspt.SolvedTime != null && pspt.SolvedTime <= submissionEnd) .Select(pspt => new { pspt.PuzzleID, pspt.TeamID, pspt.SolvedTime }) .ToListAsync(); // Hide disqualified teams from the standings page. var teams = await _context.Teams .Where(t => t.Event == Event && t.IsDisqualified == false) .ToListAsync(); Dictionary <int, TeamStats> teamStats = new Dictionary <int, TeamStats>(teams.Count); foreach (var t in teams) { teamStats[t.ID] = new TeamStats { Team = t, FinalMetaSolveTime = DateTime.MaxValue }; } foreach (var s in stateData) { if (!puzzleData.TryGetValue(s.PuzzleID, out var p) || !teamStats.TryGetValue(s.TeamID, out var ts)) { continue; } ts.Score += p.SolveValue; ts.SolveCount++; if (p.IsCheatCode) { ts.Cheated = true; ts.FinalMetaSolveTime = DateTime.MaxValue; } if (p.IsFinalPuzzle && !ts.Cheated) { ts.FinalMetaSolveTime = s.SolvedTime.Value; } } var teamsFinal = teamStats.Values.OrderBy(t => t.FinalMetaSolveTime).ThenByDescending(t => t.Score).ThenBy(t => t.Team.Name).ToList(); TeamStats prevStats = null; for (int i = 0; i < teamsFinal.Count; i++) { var stats = teamsFinal[i]; if (prevStats == null || stats.FinalMetaSolveTime != prevStats.FinalMetaSolveTime || stats.Score != prevStats.Score) { stats.Rank = i + 1; } else { stats.Rank = prevStats.Rank; } prevStats = stats; } switch (sort) { case SortOrder.RankAscending: break; case SortOrder.RankDescending: teamsFinal.Reverse(); break; case SortOrder.NameAscending: teamsFinal = teamsFinal.OrderBy(ts => ts.Team.Name).ToList(); break; case SortOrder.NameDescending: teamsFinal = teamsFinal.OrderByDescending(ts => ts.Team.Name).ToList(); break; case SortOrder.PuzzlesAscending: teamsFinal = teamsFinal.OrderBy(ts => ts.SolveCount).ThenBy(ts => ts.Rank).ThenBy(ts => ts.Team.Name).ToList(); break; case SortOrder.PuzzlesDescending: teamsFinal = teamsFinal.OrderByDescending(ts => ts.SolveCount).ThenByDescending(ts => ts.Rank).ThenByDescending(ts => ts.Team.Name).ToList(); break; case SortOrder.ScoreAscending: teamsFinal = teamsFinal.OrderBy(ts => ts.Score).ThenBy(ts => ts.Rank).ThenBy(ts => ts.Team.Name).ToList(); break; case SortOrder.ScoreDescending: teamsFinal = teamsFinal.OrderByDescending(ts => ts.Score).ThenByDescending(ts => ts.Rank).ThenByDescending(ts => ts.Team.Name).ToList(); break; case SortOrder.HintsUsedAscending: teamsFinal = teamsFinal.OrderBy(ts => ts.Team.HintCoinsUsed).ThenBy(ts => ts.Rank).ThenBy(ts => ts.Team.Name).ToList(); break; case SortOrder.HintsUsedDescending: teamsFinal = teamsFinal.OrderByDescending(ts => ts.Team.HintCoinsUsed).ThenByDescending(ts => ts.Rank).ThenByDescending(ts => ts.Team.Name).ToList(); break; } this.Teams = teamsFinal; }
public async Task OnGetAsync(SortOrder?sort, PuzzleStateFilter?stateFilter) { this.Sort = sort; this.StateFilter = stateFilter; this.CurrentTeam = (await UserEventHelper.GetTeamForPlayer(_context, Event, LoggedInUser)); Dictionary <int, string> teamNameLookup = new Dictionary <int, string>(); // build an ID-to-name mapping to improve perf var names = await _context.Teams.Where(t => t.Event == Event) .Select(t => new { t.ID, t.Name }) .ToListAsync(); names.ForEach(t => teamNameLookup[t.ID] = t.Name); DateTime submissionEnd = Event.AnswerSubmissionEnd; // Get the page data: puzzle, solve count, top three fastest // Puzzle solve counts var solveCounts = await(from pspt in _context.PuzzleStatePerTeam where pspt.SolvedTime != null && pspt.Puzzle.IsPuzzle && pspt.UnlockedTime != null && pspt.SolvedTime <= submissionEnd && !pspt.Team.IsDisqualified && pspt.Puzzle.Event == Event let puzzleToGroup = new { PuzzleID = pspt.Puzzle.ID, PuzzleName = pspt.Puzzle.Name } // Using 'let' to work around EF grouping limitations (https://www.codeproject.com/Questions/5266406/Invalidoperationexception-the-LINQ-expression-for) group puzzleToGroup by puzzleToGroup.PuzzleID into puzzleGroup orderby puzzleGroup.Count() descending, puzzleGroup.Max(puzzleGroup => puzzleGroup.PuzzleName) // Using Max(PuzzleName) because only aggregate operators are allowed select new { PuzzleID = puzzleGroup.Key, PuzzleName = puzzleGroup.Max(puzzleGroup => puzzleGroup.PuzzleName), SolveCount = puzzleGroup.Count() }).ToListAsync(); // Getting the top 3 requires working around EF limitations translating sorting results within a group to SQL. Workaround from https://github.com/dotnet/efcore/issues/13805 // Filter to solved puzzles var psptToQuery = _context.PuzzleStatePerTeam.Where(pspt => pspt.Puzzle.EventID == Event.ID && pspt.Puzzle.IsPuzzle && pspt.UnlockedTime != null && pspt.SolvedTime != null && pspt.SolvedTime < submissionEnd && !pspt.Team.IsDisqualified ); // Sort by time and get the top 3 var fastestResults = _context.PuzzleStatePerTeam .Select(pspt => pspt.PuzzleID).Distinct() .SelectMany(puzzleId => psptToQuery .Where(pspt => pspt.PuzzleID == puzzleId) .OrderBy(pspt => EF.Functions.DateDiffSecond(pspt.UnlockedTime, pspt.SolvedTime)) .Take(3), (puzzleId, pspt) => pspt) .ToLookup(pspt => pspt.PuzzleID, pspt => new { pspt.TeamID, pspt.SolvedTime, pspt.UnlockedTime }); var unlockedData = this.CurrentTeam == null ? null : ( await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null) .Where(state => state.Team == this.CurrentTeam && state.UnlockedTime != null) .Select(s => new { s.PuzzleID, IsSolvedByUserTeam = (s.SolvedTime < submissionEnd) }) .ToDictionaryAsync(s => s.PuzzleID)); var puzzles = new List <PuzzleStats>(solveCounts.Count); for (int i = 0; i < solveCounts.Count; i++) { var data = solveCounts[i]; // For players, we will hide puzzles they have not unlocked yet. if (EventRole == EventRole.play && (unlockedData == null || !unlockedData.ContainsKey(data.PuzzleID))) { continue; } FastRecord[] fastest; if (fastestResults.Contains(data.PuzzleID)) { fastest = fastestResults[data.PuzzleID].Select(f => new FastRecord() { ID = f.TeamID, Name = teamNameLookup[f.TeamID], Time = f.SolvedTime - f.UnlockedTime }).ToArray(); } else { fastest = Array.Empty <FastRecord>(); } bool isSolved = false; if (unlockedData != null) { isSolved = unlockedData[data.PuzzleID].IsSolvedByUserTeam; } var stats = new PuzzleStats() { PuzzleName = data.PuzzleName, SolveCount = data.SolveCount, SortOrder = i, Fastest = fastest, IsSolved = isSolved }; puzzles.Add(stats); } if (this.StateFilter == PuzzleStateFilter.Unsolved) { puzzles = puzzles.Where(stats => !stats.IsSolved).ToList(); } switch (sort ?? DefaultSort) { case SortOrder.RankAscending: puzzles.Sort((rhs, lhs) => (rhs.SortOrder - lhs.SortOrder)); break; case SortOrder.RankDescending: puzzles.Sort((rhs, lhs) => (lhs.SortOrder - rhs.SortOrder)); break; case SortOrder.CountAscending: puzzles.Sort((rhs, lhs) => (rhs.SolveCount - lhs.SolveCount)); break; case SortOrder.CountDescending: puzzles.Sort((rhs, lhs) => (lhs.SolveCount - rhs.SolveCount)); break; case SortOrder.PuzzleAscending: puzzles.Sort((rhs, lhs) => (String.Compare(rhs.PuzzleName, lhs.PuzzleName, StringComparison.OrdinalIgnoreCase))); break; case SortOrder.PuzzleDescending: puzzles.Sort((rhs, lhs) => (String.Compare(lhs.PuzzleName, rhs.PuzzleName, StringComparison.OrdinalIgnoreCase))); break; default: throw new ArgumentException($"unknown sort: {sort}"); } this.Puzzles = puzzles; }
public async Task OnGetAsync(SortOrder?sort) { Sort = sort; var teamsData = await PuzzleStateHelper.GetSparseQuery(_context, this.Event, null, null) .Where(s => s.SolvedTime != null && s.Puzzle.IsPuzzle) .GroupBy(state => state.Team) .Select(g => new { Team = g.Key, SolveCount = g.Count(), Score = g.Sum(s => s.Puzzle.SolveValue), FinalMetaSolveTime = g.Where(s => s.Puzzle.IsCheatCode).Any() ? DateTime.MaxValue : (g.Where(s => s.Puzzle.IsFinalPuzzle).Select(s => s.SolvedTime).FirstOrDefault() ?? DateTime.MaxValue) }) .OrderBy(t => t.FinalMetaSolveTime).ThenByDescending(t => t.Score).ThenBy(t => t.Team.Name) .ToListAsync(); var teams = new List <TeamStats>(teamsData.Count); TeamStats prevStats = null; for (int i = 0; i < teamsData.Count; i++) { var data = teamsData[i]; var stats = new TeamStats() { Team = data.Team, SolveCount = data.SolveCount, Score = data.Score, FinalMetaSolveTime = data.FinalMetaSolveTime }; if (prevStats == null || stats.FinalMetaSolveTime != prevStats.FinalMetaSolveTime || stats.Score != prevStats.Score) { stats.Rank = i + 1; } else { stats.Rank = prevStats.Rank; } teams.Add(stats); prevStats = stats; } switch (sort) { case SortOrder.RankAscending: break; case SortOrder.RankDescending: teams.Reverse(); break; case SortOrder.NameAscending: teams.Sort((a, b) => a.Team.Name.CompareTo(b.Team.Name)); break; case SortOrder.NameDescending: teams.Sort((a, b) => - a.Team.Name.CompareTo(b.Team.Name)); break; case SortOrder.PuzzlesAscending: teams.Sort((a, b) => a.SolveCount.CompareTo(b.SolveCount)); break; case SortOrder.PuzzlesDescending: teams.Sort((a, b) => - a.SolveCount.CompareTo(b.SolveCount)); break; case SortOrder.ScoreAscending: teams.Sort((a, b) => a.Score.CompareTo(b.Score)); break; case SortOrder.ScoreDescending: teams.Sort((a, b) => - a.Score.CompareTo(b.Score)); break; case SortOrder.HintsUsedAscending: teams.Sort((a, b) => a.Score.CompareTo(b.Team.HintCoinsUsed)); break; case SortOrder.HintsUsedDescending: teams.Sort((a, b) => - a.Score.CompareTo(b.Team.HintCoinsUsed)); break; } this.Teams = teams; }