protected void Search_Click(object sender, EventArgs e)
{
if (TeamList.SelectedIndex == 0)
{
errormsgs.Add(" Please select a Player.");
LoadMessageDisplay(errormsgs, "alert alert-info");
TeamGridView.DataSource = null;
TeamGridView.DataBind();
}
else
{
try
{
PlayerController sysmgrP = new PlayerController();
TeamController sysmgrT = new TeamController();
List <Player> infoP = sysmgrP.Player_FindByTeamID(TeamList.SelectedValue);
Team infoT = sysmgrT.Team_Find(int.Parse(TeamList.SelectedValue));
infoP.Sort((x, y) => x.FullName.CompareTo(y.FullName));
TeamGridView.DataSource = infoP;
TeamGridView.DataBind();
Coach.Text = infoT.Coach;
AssistantCoach.Text = infoT.AssistantCoach;
Wins.Text = infoT.Wins == null ? "0" : infoT.Wins.ToString();
Losses.Text = infoT.Losses == null ? "0" : infoT.Losses.ToString();
}
catch (Exception ex)
{
ErrorMsg.Text = ex.Message;
}
}
}
protected void TeamGridView_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable temp = (DataTable)TeamGridView.DataSource;
temp.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);
TeamGridView.DataSource = temp;
TeamGridView.DataBind();
ModifyRows();
}
protected void GetTeams()
{
// connect to EF DB
using (DefaultConnection db = new DefaultConnection())
{
// query the Students table using EF and LINQ
var Teams = (from allTeams in db.Teams
select allTeams);
//bind the result to the GridView
TeamGridView.DataSource = Teams.ToList();
TeamGridView.DataBind();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!HttpContext.Current.User.Identity.IsAuthenticated)
{
//Server.Transfer("Default.aspx", true);
Response.Redirect("/Account/Login");
}
else
{
//MembershipUser CurrentUser = Membership.GetUser(User.Identity.Name);
if (!IsPostBack)
{
//Response.Write(@"<script language='javascript'>alert('" + HttpContext.Current.User.Identity.Name + "|" +
//User.Identity.GetUserId() + "|');</script>");
}
}
String strConnString = ConfigurationManager.ConnectionStrings["FantasySoccerConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
//Populate the grid of players with fantasy points
cmd.CommandText =
"SELECT " +
"Players.PlayerId AS PlayerId, FirstName AS First, LastName AS Last, [Cost], [ClubName] AS Club, [Positions].[PositionName] AS Position," +
"SUM([PlayerStats].Goals) AS Goals, SUM([PlayerStats].Shots) AS Shots, SUM([PlayerStats].Assists) AS Assists," +
"SUM([PlayerStats].MinPlayed) AS 'Min Played', SUM([PlayerStats].Fouls) AS Fouls, " +
"SUM([PlayerStats].YellowCards) AS YC, SUM([PlayerStats].RedCards) AS RC, SUM([PlayerStats].GoalsAllowed) AS GA, " +
"SUM([PlayerStats].SavesMade) AS Saves, SUM([PlayerStats].CleanSheets) AS CS, " +
"dbo.CalculateTotalFantasyPoints(SUM([PlayerStats].Goals), " +
" SUM([PlayerStats].Shots), "+
" SUM([PlayerStats].Assists), "+
" SUM([PlayerStats].MinPlayed), "+
" SUM([PlayerStats].Fouls), "+
" SUM([PlayerStats].YellowCards), "+
" SUM([PlayerStats].RedCards), "+
" SUM([PlayerStats].GoalsAllowed), "+
" SUM([PlayerStats].SavesMade), "+
" SUM([PlayerStats].CleanSheets), "+
" Positions.PositionRef) AS 'Total Fantasy Pts' "+
"FROM [Players] " +
"INNER JOIN LineupHistory ON LineupHistory.PlayerId = Players.PlayerId " +
"INNER JOIN [Positions] ON [Positions].[PositionRef] = Players.PositionRef " +
"LEFT OUTER JOIN PlayerStats ON PlayerStats.PlayerId = Players.PlayerId " +
"INNER JOIN Clubs ON Clubs.ClubId = Players.ClubId " +
"WHERE LineupHistory.Month = DATEPART(MONTH, GETDATE()) " +
"AND LineupHistory.UserId = '" + User.Identity.GetUserId() + "' " +
"GROUP BY Players.PlayerId, FirstName, LastName, Players.Cost, Clubs.ClubName, Positions.PositionName, Positions.PositionRef, Players.PlayerId " +
"ORDER BY Last";
cmd.Connection = con;
try {
DataTable temp = new DataTable();
con.Open();
TeamGridView.EmptyDataText = "No Records Found";
temp.Load(cmd.ExecuteReader());
for (int i = 0; i < temp.Rows.Count; i++)
{
TOTALCAP -= (int)temp.Rows[i]["Cost"];
}
AvailCap = TOTALCAP.ToString("C");
TeamGridView.DataSource = temp;
TeamGridView.DataBind();
ModifyRows();
//TeamGridView.Columns[0].Visible = false;
} catch (System.Data.SqlClient.SqlException ex) {
} finally {
con.Close();
}
//Populate the fantasy points earned fields
cmd.CommandText =
"SELECT " +
"Players.PositionRef AS Position," +
"SUM([PlayerStats].Goals) AS Goals," +
"dbo.CalculateFantasyPointsByPosition(1, SUM([PlayerStats].Goals), Players.PositionRef) AS GoalsPts," +
"SUM([PlayerStats].Shots) AS Shots," +
"dbo.CalculateFantasyPointsByPosition(2, SUM([PlayerStats].Shots), Players.PositionRef) AS ShotsPts," +
"SUM([PlayerStats].Assists) AS Assists," +
"dbo.CalculateFantasyPointsByPosition(6, SUM([PlayerStats].Assists), Players.PositionRef) AS AssistsPts," +
"SUM([PlayerStats].MinPlayed) AS MinPlayed," +
"dbo.CalculateFantasyPointsByPosition(10, SUM([PlayerStats].MinPlayed), Players.PositionRef) AS MinPlayedPts," +
"SUM([PlayerStats].Fouls) AS Fouls," +
"dbo.CalculateFantasyPointsByPosition(3, SUM([PlayerStats].Fouls), Players.PositionRef) AS FoulsPts," +
"SUM([PlayerStats].YellowCards) AS YC," +
"dbo.CalculateFantasyPointsByPosition(4, SUM([PlayerStats].YellowCards), Players.PositionRef) AS YCPts," +
"SUM([PlayerStats].RedCards) AS RC," +
"dbo.CalculateFantasyPointsByPosition(5, SUM([PlayerStats].RedCards), Players.PositionRef) AS RCPts," +
"SUM([PlayerStats].GoalsAllowed) AS GA," +
"dbo.CalculateFantasyPointsByPosition(8, SUM([PlayerStats].GoalsAllowed), Players.PositionRef) AS GAPts," +
"SUM([PlayerStats].SavesMade) AS Saves," +
"dbo.CalculateFantasyPointsByPosition(9, SUM([PlayerStats].SavesMade), Players.PositionRef) AS SavesPts," +
"SUM([PlayerStats].CleanSheets) AS CleanSheets," +
"dbo.CalculateFantasyPointsByPosition(7, SUM([PlayerStats].CleanSheets), Players.PositionRef) AS CleanSheetPts," +
"dbo.CalculateTotalFantasyPoints(SUM([PlayerStats].Goals)," +
" SUM([PlayerStats].Shots), "+
" SUM([PlayerStats].Assists),"+
" SUM([PlayerStats].MinPlayed),"+
" SUM([PlayerStats].Fouls),"+
" SUM([PlayerStats].YellowCards),"+
" SUM([PlayerStats].RedCards),"+
" SUM([PlayerStats].GoalsAllowed),"+
" SUM([PlayerStats].SavesMade),"+
" SUM([PlayerStats].CleanSheets),"+
" Players.PositionRef) AS 'Total Fantasy Pts' "+
"FROM LineupHistory " +
"INNER JOIN Players ON LineupHistory.PlayerId = Players.PlayerId " +
"INNER JOIN PlayerStats ON PlayerStats.PlayerId = LineupHistory.PlayerId AND PlayerStats.Month = LineupHistory.Month " +
"WHERE LineupHistory.UserId = '" + User.Identity.GetUserId() + "' " +
"GROUP BY Players.PositionRef";
cmd.Connection = con;
try {
decimal goals = 0, goalPts = 0, shots = 0, shotPts = 0, assists = 0, assistPts = 0,
minPlayed = 0, minPlayedPts = 0, fouls = 0, foulPts = 0, YC = 0, YCPts = 0,
RC = 0, RCPts = 0, GC = 0, GCPts = 0, saves = 0, savePts = 0, cleanSheets = 0,
cleanSheetPts = 0;
DataTable temp = new DataTable();
con.Open();
temp.Load(cmd.ExecuteReader());
DataRow[] foundRows;
for (int i = 1; i <= 4; i++)
{
foundRows = temp.Select("Position = " + i);
if (foundRows.Length > 0)
{
if (i == 1)
{
StrikersPtsLabel.Text = foundRows[0]["Total Fantasy Pts"].ToString() + " pts";
}
else if (i == 2)
{
MidfieldersPtsLabel.Text = foundRows[0]["Total Fantasy Pts"].ToString() + " pts";
}
else if (i == 3)
{
DefendersPtsLabel.Text = foundRows[0]["Total Fantasy Pts"].ToString() + " pts";
}
else if (i == 4)
{
GoaliesPtsLabel.Text = foundRows[0]["Total Fantasy Pts"].ToString() + " pts";
}
goals += (int)foundRows[0]["Goals"];
goalPts += (decimal)foundRows[0]["GoalsPts"];
shots += (int)foundRows[0]["Shots"];
shotPts += (decimal)foundRows[0]["ShotsPts"];
assists += (int)foundRows[0]["Assists"];
assistPts += (decimal)foundRows[0]["AssistsPts"];
minPlayed += (int)foundRows[0]["MinPlayed"];
minPlayedPts += (decimal)foundRows[0]["MinPlayedPts"];
fouls += (int)foundRows[0]["Fouls"];
foulPts += (decimal)foundRows[0]["FoulsPts"];
YC += (int)foundRows[0]["YC"];
YCPts += (decimal)foundRows[0]["YCPts"];
RC += (int)foundRows[0]["RC"];
RCPts += (decimal)foundRows[0]["RCPts"];
GC += (int)foundRows[0]["GA"];
GCPts += (decimal)foundRows[0]["GAPts"];
saves += (int)foundRows[0]["Saves"];
savePts += (decimal)foundRows[0]["SavesPts"];
cleanSheets += (int)foundRows[0]["CleanSheets"];
cleanSheetPts += (decimal)foundRows[0]["CleanSheetPts"];
}
}
GoalsPtsLabel.Text = goals.ToString() + " = " + goalPts.ToString() + " pts";
ShotsPtsLabel.Text = shots.ToString() + " = " + shotPts.ToString() + " pts";
AssistsPtsLabel.Text = assists.ToString() + " = " + assistPts.ToString() + " pts";
MinPlayedPtsLabel.Text = minPlayed.ToString() + " = " + minPlayedPts.ToString() + " pts";
FoulsPtsLabel.Text = fouls.ToString() + " = " + foulPts.ToString() + " pts";
YCPtsLabel.Text = YC.ToString() + " = " + YCPts.ToString() + " pts";
RCPtsLabel.Text = RC.ToString() + " = " + RCPts.ToString() + " pts";
GCPtsLabel.Text = GC.ToString() + " = " + GCPts.ToString() + " pts";
SavesMadePtsLabel.Text = saves.ToString() + " = " + savePts.ToString() + " pts";
CleanSheetsPtsLabel.Text = cleanSheets.ToString() + " = " + cleanSheetPts.ToString() + " pts";
} catch (System.Data.SqlClient.SqlException ex) {
} finally {
con.Close();
}
}
