protected void PlayerSearchGridView_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable temp = (DataTable)PlayerSearchGridView.DataSource;
temp.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);
PlayerSearchGridView.DataSource = temp;
PlayerSearchGridView.DataBind();
ModifyRows();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!HttpContext.Current.User.Identity.IsAuthenticated)
{
//Server.Transfer("Default.aspx", true);
Response.Redirect("/Account/Login");
}
String strConnString = ConfigurationManager.ConnectionStrings["FantasySoccerConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
//Populate the grid with all fantasy points earned including each month
cmd.CommandText =
"SELECT " +
"Players.PlayerId AS PlayerId, " +
"FirstName AS First, " +
"LastName AS Last, " +
"Cost, " +
"Clubs.ClubName, " +
"Leagues.LeagueName, " +
"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), "+
" Players.PositionRef) AS 'Total Fantasy Pts' "+
"FROM Players " +
"LEFT OUTER JOIN PlayerStats ON PlayerStats.PlayerId = Players.PlayerId " +
"LEFT OUTER JOIN [Positions] ON [Positions].[PositionRef] = Players.PositionRef " +
"LEFT OUTER JOIN Clubs ON Players.ClubId = Clubs.ClubId " +
"LEFT OUTER JOIN Leagues ON Clubs.LeagueId = Leagues.LeagueId ";
//System.Diagnostics.Debug.WriteLine("Inside the page load and clear variable is:" + clearButtonPress);
if (IsPostBack && !clearButtonPress)
{
//System.Diagnostics.Debug.WriteLine("Inside the WHERE CLAUSE. ");
bool anotherQuery = false;
if (FirstNameTextBox.Text != "" || LastNameTextBox.Text != "" || ClubTextBox.Text != "" ||
LeagueTextBox.Text != "" || PositionDropDown.SelectedIndex != 0)
{
//System.Diagnostics.Debug.WriteLine("Apparently they are not empty. ");
cmd.CommandText += "WHERE ";
if (FirstNameTextBox.Text != "")
{
cmd.CommandText += "FirstName LIKE '%" + FirstNameTextBox.Text + "%' ";
anotherQuery = true;
}
if (LastNameTextBox.Text != "")
{
if (anotherQuery)
{
cmd.CommandText += " AND ";
}
cmd.CommandText += "LastName LIKE '%" + LastNameTextBox.Text + "%' ";
anotherQuery = true;
}
if (ClubTextBox.Text != "")
{
if (anotherQuery)
{
cmd.CommandText += " AND ";
}
cmd.CommandText += "Clubs.ClubName LIKE '%" + ClubTextBox.Text + "%' ";
anotherQuery = true;
}
if (LeagueTextBox.Text != "")
{
if (anotherQuery)
{
cmd.CommandText += " AND ";
}
cmd.CommandText += "Leagues.LeagueName LIKE '%" + LeagueTextBox.Text + "%' ";
anotherQuery = true;
}
if (PositionDropDown.SelectedIndex != 0)
{
if (anotherQuery)
{
cmd.CommandText += " AND ";
}
cmd.CommandText += "Positions.PositionRef = " + PositionDropDown.SelectedValue + " ";
anotherQuery = true;
}
}
}
clearButtonPress = false;
cmd.CommandText +=
"GROUP BY FirstName, LastName, Players.PositionRef, Positions.PositionName, Cost, " +
"Clubs.ClubName, Leagues.LeagueName, Players.PlayerId " +
"ORDER BY LastName, FirstName";
cmd.Connection = con;
try {
DataTable temp = new DataTable();
con.Open();
PlayerSearchGridView.EmptyDataText = "No Records Found";
temp.Load(cmd.ExecuteReader());
/*HyperLinkColumn hplink = new HyperLinkColumn();
* hplink.Text = "Link";
* temp.Columns.Add(hplink.Text);
*
* for(int i = 0; i < temp.Rows.Count; i++) {
* int playerId = (int)temp.Rows[i]["PlayerId"];
* //temp.Rows[i]["Link"] = "<a href=\"./Players/ViewPlayer.aspx?player=" + playerId + "\">Click Here</a>";
* //HyperLink tempo = new HyperLink();
*
*
*
* temp.Rows[i][hplink.Text] = String.Format("<a href='./Players/ViewPlayer.aspx?player="
+ playerId +"'>HAHA</a>");
+ //tempo.NavigateUrl = "./Players/ViewPlayer.aspx?player=" + playerId;
+ //tempo.Text = "Click Here";
+ //temp.Rows[i]["Link"] = tempo;
+
+ }*/
//temp.Columns.Remove("PlayerId");
PlayerSearchGridView.DataSource = temp;
PlayerSearchGridView.DataBind();
} catch (System.Data.SqlClient.SqlException ex) {
} finally {
con.Close();
}
ModifyRows();
}