private PositionalStandings fixturePlayedByPlayer(int playerID, int positionID, int?SeasonID, string DivisionID) { PositionalStandings ps = new PositionalStandings(); /* * getting the amount of fixtures the given player played and the given position */ var playerPositions = _context.PlayerPositions.Include(pp => pp.PlayerID); var players = _context.Players.Include(p => p.ID); var fixturesByPlayer = (from pp in playerPositions join p in players on pp.PlayerID equals p.ID where p.ID == playerID && pp.PositionID == positionID select pp.PositionID).Count(); /* * getting the temaID of the given playerID */ var player = _context.Players.Include(p => p.ID); var playerTeamID = (from p in player where p.ID == playerID select new { p.TeamID, p.FirstName, p.LastName }).FirstOrDefault(); /* * getting the amount of fixture the team of the given playerID played as Home */ var fixturesHome = _context.Fixtures.Include(f => f.HomeTeamID); var seadivteaHome = _context.SeasonDivisionTeams.Include(sdt => sdt.TeamID); //var teamPlayedHome = from f in fixtures as HomeTeam var teamPlayedHome = (from f in fixturesHome join sdt1 in seadivteaHome on f.HomeTeamID equals sdt1.TeamID where sdt1.TeamID == playerTeamID.TeamID select f.ID).Count(); /* * getting the amount of fixture the team of the given playerID played as Away */ var fixturesAway = _context.Fixtures.Include(f => f.HomeTeamID); var seadivteaAway = _context.SeasonDivisionTeams.Include(sdt => sdt.TeamID); //var teamPlayedHome = from f in fixtures as AwayTeam var teamPlayedAway = (from f in fixturesAway join sdt2 in seadivteaAway on f.AwayTeamID equals sdt2.TeamID where sdt2.TeamID == playerTeamID.TeamID select f.ID).Count(); //Getting a list of all players var allPlayers = _context.Players; var allSeaDivTea = _context.SeasonDivisionTeams; var allMatches = _context.Matches; var homeMatches = from p in allPlayers join sdt in allSeaDivTea on p.TeamID equals sdt.TeamID join m in allMatches on p.ID equals m.HomePlayerID where sdt.DivisionID == Convert.ToInt32(DivisionID) && sdt.SeasonID == SeasonID && p.ID == playerID select new { p, m }; var awayMatches = from p in allPlayers join sdt in allSeaDivTea on p.TeamID equals sdt.TeamID join m in allMatches on p.ID equals m.AwayPlayerID where sdt.DivisionID == Convert.ToInt32(DivisionID) && sdt.SeasonID == SeasonID && p.ID == playerID select new { p, m }; int forScore = 0; int againstScore = 0; if (homeMatches.Count() > 0) { foreach (var match in homeMatches) { forScore += (int)match.m.HomePlayerScore; againstScore += (int)match.m.AwayPlayerScore; } } if (awayMatches.Count() > 0) { foreach (var match in awayMatches) { forScore += (int)match.m.AwayPlayerScore; againstScore += (int)match.m.HomePlayerScore; } } ps.forScore = forScore; ps.againstScore = againstScore; if (forScore + againstScore > 0) { ps.winPerc = Math.Round(Convert.ToDecimal(forScore) / Convert.ToDecimal(forScore + againstScore) * 100, 0); } else { ps.winPerc = 0; } ps.PlayerID = playerID; ps.PositionID = positionID; ps.PlayerName = playerTeamID.FirstName + " " + playerTeamID.LastName; decimal percTemp = Math.Round(Convert.ToDecimal(fixturesByPlayer) / Convert.ToDecimal(teamPlayedHome + teamPlayedAway) * 100); ps.PercPlayed = percTemp; return(ps); }
public async Task <IActionResult> Standings(int?SeasonID, string DivisionID) { ViewBag.error = ""; if (!SeasonID.HasValue) { ViewData["FirstTime"] = true; } else { ViewData["FirstTime"] = false; } try { //Getting the number of positions by division - I'm using hardcoded Division 1 var nPositions = _context.Divisions.Include(p => p.PositionNo); var pos = (from p in nPositions where p.ID == 1 select p.PositionNo).SingleOrDefault(); //Getting a list of all players var players = from p in _context.Players where p.ID == 0 select p; if (SeasonID.HasValue) { players = from p in _context.Players join t in _context.Teams on p.TeamID equals t.ID join sdt in _context.SeasonDivisionTeams on t.ID equals sdt.TeamID where sdt.DivisionID == Convert.ToInt32(DivisionID) && sdt.SeasonID == SeasonID select p; } List <PositionalStandings> positionalStandingsList = new List <PositionalStandings>(); for (int i = 0; i < pos; i++) { foreach (var player in players) { if (player.ID == 1) { continue; } PositionalStandings p = fixturePlayedByPlayer(player.ID, i + 1, SeasonID, DivisionID); if (p.PercPlayed >= 50) { positionalStandingsList.Add(p); } } } ViewBag.positions = pos; ViewBag.playersByPosition = positionalStandingsList.OrderByDescending(ps => ps.winPerc).ToList(); //ViewBag.playersByPosition = positionalStandingsList; } catch (ArgumentOutOfRangeException err) { ViewBag.error = "No matches have been played. Add fixture results first."; } catch (Exception err) { ViewBag.error = "Some problem happend that need to be analysed. Report it to the team"; } PopulateDropDownListSeason(); int userDivision = 0; ViewData["UserDivision"] = userDivision; ViewData["DivisionID"] = from d in _context.Divisions select d; return(View()); }