public List <PlayerHistoryViewModel> GetPlayersFromClubs(List <int> clubIds) { var result = new List <PlayerHistoryViewModel>(); PlayerHistoryViewModel player = null; using (OleDbCommand cmd = new OleDbCommand( "SELECT DISTINCT PlayerResult.player_id, player_nr, list_name, Season.season_year, Season.season_period" + " FROM Player,PlayerResult, Team, Poule, Season" + " WHERE PlayerResult.team_id=Team.id AND Team.poule_id=Poule.id AND Poule.season_id=Season.id AND PlayerResult.player_id=Player.id" + " AND Team.club_id IN (" + String.Join(",", clubIds) + ")" + " ORDER BY list_name ASC, player_nr ASC, season_year ASC, season_period ASC", db)) using (OleDbDataReader dbReader = cmd.ExecuteReader()) { int _playerName = dbReader.GetOrdinal("list_name"); int _playerNumber = dbReader.GetOrdinal("player_nr"); int _seasonYear = dbReader.GetOrdinal("season_year"); int _seasonPeriod = dbReader.GetOrdinal("season_period"); while (dbReader.Read()) { string playerNr = dbReader.GetString(_playerNumber); if (player == null || !player.playerNumber.Equals(playerNr)) { player = new PlayerHistoryViewModel(); player.playerName = dbReader.GetString(_playerName); player.playerNumber = dbReader.GetString(_playerNumber); player.seasons = new List <SeasonViewModel>(); result.Add(player); } var season = new SeasonViewModel(); season.seasonYear = dbReader.GetInt32(_seasonYear); season.seasonPeriod = dbReader.GetInt32(_seasonPeriod); player.seasons.Add(season); } } return(result); }
private ClubPlayerHistoryViewModel GetTeamMemberHistory(List <TeamListViewModel> teams, string playerNumber) { var history = new ClubPlayerHistoryViewModel(); history.players = new List <PlayerHistoryViewModel>(); history.seasons = new List <SeasonListViewModel>(); foreach (var team in teams) { foreach (var player in team.teamMembers) { if (player.playerNumber.Equals(playerNumber)) { continue; } var playerHistory = history.players.SingleOrDefault(p => p.playerNumber.Equals(player.playerNumber)); if (playerHistory == null) { playerHistory = new PlayerHistoryViewModel(); playerHistory.playerName = player.playerName; playerHistory.playerNumber = player.playerNumber; playerHistory.seasons = new List <SeasonViewModel>(); history.players.Add(playerHistory); } if (playerHistory.seasons.Count(s => s.seasonPeriod == team.seasonPeriod && s.seasonYear == team.seasonYear) == 0) { playerHistory.seasons.Add(new SeasonViewModel() { seasonPeriod = team.seasonPeriod, seasonYear = team.seasonYear }); } if (history.seasons.Count(s => s.seasonPeriod == team.seasonPeriod && s.seasonYear == team.seasonYear) == 0) { history.seasons.Add(new SeasonListViewModel() { seasonPeriod = team.seasonPeriod, seasonYear = team.seasonYear }); } } } history.headings = new List <SortOptions>(); history.headings.Add(new SortOptions("Bondsnr", "Bondsnummer", _attributes: "class='nr'")); history.headings.Add(new SortOptions("Speler", "Speler", _attributes: "class='name'")); history.headings.Add(new SortOptions("Seizoenen", "Seizoenen", _defaultSortOrder: "Aflopend")); if (history.seasons.Count() > 0) { int seasonYear = history.seasons[history.seasons.Count() - 1].seasonYear; int seasonYearEnd = history.seasons[0].seasonYear; while (seasonYear <= seasonYearEnd) { history.headings.Add(new SortOptions("vj", "vj" + seasonYear, _attributes: "class='season'")); history.headings.Add(new SortOptions("nj", "nj" + seasonYear, _attributes: "class='season'")); seasonYear++; } } return(history); }