public List <Player> Player_FindByTeamID(string teamID)
{
using (var context = new FSISContext())
{
IEnumerable <Player> results = context.Database.SqlQuery <Player>
("Player_GetByTeam @TeamID", new SqlParameter("TeamID", teamID));
return(results.ToList());
}
}
public int Player_Add(Player player)
{
using (var context = new FSISContext())
{
context.Players.Add(player);
context.SaveChanges();
return(player.PlayerID);
}
}
public List <Player> FindByPartialName(string partialname)
{
using (var context = new FSISContext())
{
IEnumerable <Player> results =
context.Database.SqlQuery <Player>("Products_GetByPartialProductName @PartialName",
new SqlParameter("PartialName", partialname));
return(results.ToList());
}
}
public List <Player> FindByID(int id)
{
using (var context = new FSISContext())
{
IEnumerable <Player> results =
context.Database.SqlQuery <Player>("Player_GetByTeam @ID"
, new SqlParameter("ID", id));
return(results.ToList());
}
}
public List <Player> Player_GetByAgeGender(int age, string gender)
{
using (var context = new FSISContext())
{
IEnumerable <Player> results =
context.Database.SqlQuery <Player>("Player_GetByAgeGender @Age, @Gender", new SqlParameter("Age", age), new SqlParameter("Gender", gender));
return(results.ToList());
}
}
public List <Team> Teams_FindByID(int teamID)
{
using (var context = new FSISContext())
{
IEnumerable <Team> results =
context.Database.SqlQuery <Team>("Team_GetByTeam @ID"
, new SqlParameter("ID", teamID));
return(results.ToList());
//return context.Players.Find(PlayerID);
}
}
public int Team_Delete(int teamid)
{
using (var context = new FSISContext())
{
var existing = context.Teams.Find(teamid);
context.Teams.Remove(existing);
return(context.SaveChanges());
}
}
public int Team_Add(Team item)
{
using (var context = new FSISContext())
{
context.Teams.Add(item);
context.SaveChanges();
return(item.TeamID);
}
}
/*public int Discontinue(int productid)
* {
* using (var context = new FSISContext())
* {
* var existing = context.Players.Find(productid);
* existing.Discontinued = true;
* context.Entry(existing).State = System.Data.Entity.EntityState.Modified;
* return context.SaveChanges();
* }
* }*/
public int Delete(int productid)
{
using (var context = new FSISContext())
{
var existing = context.Players.Find(productid);
if (existing == null)
{
throw new Exception("Record has been removed from database");
}
context.Players.Remove(existing);
return(context.SaveChanges());
}
}
public List <ForeignKeyList> Get_TeamList()
{
using (var context = new FSISContext())
{
var results = from x in context.Teams
orderby x.TeamName
select new ForeignKeyList
{
PFKeyIdentifier = x.TeamID,
DisplayText = x.TeamName
};
return(results.ToList());
}
}
public int Delete(int playerid)
{
using (var context = new FSISContext())
{
var existingPlayer = context.Players.Find(playerid);
if (existingPlayer == null)
{
throw new Exception("Player has been removed from the database");
}
context.Players.Remove(existingPlayer);
return(context.SaveChanges());
}
}
public List <PlayerGameStat> GetTeamRosterforPlayerGameStats(int teamid)
{
using (var context = new FSISContext())
{
var results = from x in context.Players
where x.TeamID == teamid
orderby x.LastName, x.FirstName
select new PlayerGameStat
{
PlayerID = x.PlayerID,
PlayerName = x.LastName + ", " + x.FirstName,
Rostered = true
};
return(results.ToList());
}
}
public List <GamesPlayed> GetGamesPlayed()
{
using (var context = new FSISContext())
{
var results = from x in context.Games
orderby x.GameDate
where x.PlayerStats.Count == 0
select new GamesPlayed
{
GameID = x.GameID,
GameDate = x.GameDate,
HomeTeamID = x.HomeTeamID,
HomeTeamName = x.HomeTeam.TeamName,
HomeTeamScore = x.HomeTeamScore,
VisitingTeamID = x.VisitingTeamID,
VisitingTeamName = x.VisitingTeam.TeamName,
VisitingTeamScore = x.VisitingTeamScore
};
return(results.ToList());
}
}
public void RecordGamePlayerStats(int gameid, List <PlayerGameStat> hometeam, List <PlayerGameStat> visitingteam)
{
using (var context = new FSISContext())
{
var gameexists = (from x in context.PlayerStats
where x.GameID == gameid
select x).Count();
/*
* if the game does not exist (count is 0) record the player stats for each team.
* if the game does exist, throw an error message.
* All work must be done as a single transaction.
*
* For each player in the data collection (Home or Visiting):
* a) increment the GamesPlayed on the Player record by 1.
* b) if the player has a goal, assist, a yellow card, or a red card
* create a PlayerStat record for that player.
*/
//Your code goes here
}
}
public TeamGameStats GetTeamStatus(int teamid)
{
using (var context = new FSISContext())
{
TeamGameStats results = null;
Team theTeam = null;
List <GameStats> teamGames = null;
//TODO: place your code after the comments
/* Code a Linq query which will retrieve the team
* matching the incoming teamid parameter.
* Save the results in a variable called theTeam
*/
//Your code goes here
theTeam = (from x in context.Teams
where x.TeamID == teamid
select x).FirstOrDefault();
//new Team
//{
// TeamName = x.TeamName,
// Coach = x.Coach,
// AssistantCoach = x.AssistantCoach,
// Wins = x.Wins,
// Losses = x.Losses
//}).FirstOrDefault();
/* code a Linq query which will retrieve all games
* played by the incoming teamid parameter.
* Use the POCO GameStats to define your saved
* data collection.
* Save the results in a variable called teamGames
*/
//Your code goes here
teamGames = (from y in context.Games
where y.HomeTeamID == teamid || y.VisitingTeamID == teamid
select new GameStats
{
DatePlayed = y.GameDate,
HomeTeam = y.HomeTeam.TeamName,
HomeScore = y.HomeTeamScore,
VisitingTeam = y.VisitingTeam.TeamName,
VisitingScore = y.VisitingTeamScore,
}).ToList();
if (theTeam != null)
{
results = new TeamGameStats()
{
TeamName = theTeam.TeamName,
Wins = theTeam.Wins.HasValue ? (int)theTeam.Wins : 0,
Losses = theTeam.Losses.HasValue ? (int)theTeam.Losses : 0,
theGames = teamGames
};
}
return(results);
}
}//eom