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