public List <CoachRoster> SelectAllCoaches()
{
List <CoachRoster> roster = new List <CoachRoster>();
CoachRoster coachRoster = null;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BlueJaysConnection"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("spSelectCoachRoster", conn);
cmd.CommandType = CommandType.StoredProcedure;
int islocked;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
coachRoster = new CoachRoster()
{
Name = reader["Name"].ToString(),
Position = reader["Position"].ToString(),
CoachNumber = Convert.ToInt32(reader["CoachNumber"]),
IsLocked = (reader["IsLocked"] != DBNull.Value) ? "Locked" : "Access"
};
roster.Add(coachRoster);
}
}
return(roster);
}
protected void CoachRosterGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
if (Cache["CoachRoster"] != null)
{
List <CoachRoster> roster = (List <CoachRoster>)Cache["CoachRoster"];
string coachNum = CoachRosterGridView.Rows[e.RowIndex].Cells[2].Text;
Debug.WriteLine(coachNum);
CoachRoster coach = roster.SingleOrDefault(x => x.CoachNumber == Convert.ToInt32(coachNum));
if (coach != null)
{
roster.Remove(coach);
Cache.Insert("CoachRoster", roster);
}
if ((bool)Session["CoachChanges"] == false)
{
Session["CoachChanges"] = true;
SaveCoachChanges.Enabled = true;
SaveCoachChanges.Visible = true;
}
CoachRosterGridView.EditIndex = -1;
CoachRosterGridView.DataSource = (List <CoachRoster>)Cache["CoachRoster"];
CoachRosterGridView.DataBind();
}
}
public List <CoachRoster> SelectAllCoaches()
{
List <CoachRoster> roster = new List <CoachRoster>();
CoachRoster coachRoster = null;
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["BlueJaysConnection"].ConnectionString))
{
OracleCommand cmd = new OracleCommand("selectAllCoaches_sp", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("ref_cur", OracleDbType.RefCursor));
cmd.Parameters["ref_cur"].Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
OracleDataReader reader = ((OracleRefCursor)cmd.Parameters["ref_cur"].Value).GetDataReader();
while (reader.Read())
{
coachRoster = new CoachRoster()
{
Name = reader["Name"].ToString(),
Position = reader["Position"].ToString(),
CoachNumber = Convert.ToInt32(reader["CoachNumber"]),
IsLocked = (reader["IsLocked"] != DBNull.Value) ? "Locked" : "Access"
};
roster.Add(coachRoster);
}
}
return(roster);
}
private static int UpdateCoachRoster(OracleConnection con, CoachRoster c)
{
OracleCommand cmd = new OracleCommand("updateCoachRoster_sp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("coachNum", c.CoachNumber));
cmd.Parameters.Add(new OracleParameter("name", c.Name));
cmd.Parameters.Add(new OracleParameter("position", c.Position));
return(cmd.ExecuteNonQuery());
}
private static int InsertIntoCoachRoster(OracleConnection con, CoachRoster c, int i)
{
OracleCommand cmd = new OracleCommand("spInsertIntoCoachRoster", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("@coachID", i));
cmd.Parameters.Add(new OracleParameter("@coachNumber", c.CoachNumber));
cmd.Parameters.Add(new OracleParameter("@name", c.Name));
cmd.Parameters.Add(new OracleParameter("@position", c.Position));
return(cmd.ExecuteNonQuery());
}
protected void AddCoachButton_Click(object sender, EventArgs e)
{
CoachRoster _newCoach = new CoachRoster();
bool existCoach = false;
coachRoster = (List <CoachRoster>)Cache["CoachRoster"];
foreach (CoachRoster coach in coachRoster)
{
if (coach.CoachNumber == int.Parse(CoachNum.Text))
{
existCoach = true;
break;
}
}
if (!existCoach)
{
_newCoach.CoachRosterID = coachRoster.Count + 10;
_newCoach.CoachNumber = int.Parse(CoachNum.Text);
_newCoach.Name = FirstName.Text + " " + LastName.Text;
_newCoach.Position = pos.Text;
_newCoach.IsLocked = "Access";
bool updated = DatabaseUpdate.AddNewCoach(_newCoach);
if (updated)
{
coachRoster.Add(_newCoach);
Cache["CoachRoster"] = coachRoster;
Server.Transfer("Coaches.aspx");
}
else
{
CoachExists.Text = "There was an issue adding coach to Roster";
}
}
else
{
CoachExists.Text = "There is already an existing coach with the number " + CoachNum.Text + "!";
}
}
protected void EnableUser(object sender, GridViewCommandEventArgs e)
{
string name = e.CommandArgument.ToString();
string[] names = name.Split(' ');
string firstName = names.First();
string lastName = names.Last();
int unlocked = 0;
List <LockedUser> user = AdminUserDataLayer.GetLockedUsers();
foreach (LockedUser lockedUser in user)
{
if (lockedUser.FirstName == firstName && lockedUser.LastName == lastName)
{
if (lockedUser.Role == "coach")
{
unlocked = AdminUserDataLayer.EnableUserAccount(firstName, lastName);
if (unlocked > 0)
{
Models.Correspondence.Email.AccountUnlockedConfirmation(lockedUser.FirstName, lockedUser.LastName, lockedUser.Email);
List <CoachRoster> roster = (List <CoachRoster>)Cache["CoachRoster"];
CoachRoster coach = roster.SingleOrDefault(x => x.Name == firstName + " " + lastName);
int index = roster.IndexOf(coach);
roster.RemoveAt(index);
coach.IsLocked = "Access";
roster.Insert(index, coach);
CoachRosterGridView.DataSource = roster;
CoachRosterGridView.DataBind();
LblError.Text = "Account unlocked. Email notification has been sent to user";
LblError.ForeColor = System.Drawing.Color.Green;
}
}
else
{
LblError.Text = "If you are a manager you will need to contact the IT Department to unlock your account";
LblError.ForeColor = System.Drawing.Color.Red;
}
}
}
}
protected void CoachRosterGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
bool deleted = false;
if (Cache["CoachRoster"] != null)
{
List <CoachRoster> roster = (List <CoachRoster>)Cache["CoachRoster"];
string coachNum = CoachRosterGridView.Rows[e.RowIndex].Cells[2].Text;
CoachRoster coach = roster.SingleOrDefault(x => x.CoachNumber == Convert.ToInt32(coachNum));
if (coach != null)
{
deleted = DatabaseUpdate.DeleteCoach(coach.CoachNumber);
if (deleted)
{
roster.Remove(coach);
Cache.Insert("CoachRoster", roster);
LblError.Text = "Coach has been deleted successfully.";
LblError.ForeColor = System.Drawing.Color.Green;
}
else
{
LblError.Text = "There was an issue in deleting coach. Please see IT Department.";
LblError.ForeColor = System.Drawing.Color.Red;
}
}
//if ((bool)Session["CoachChanges"] == false)
//{
// Session["CoachChanges"] = true;
// SaveCoachChanges.Enabled = true;
// SaveCoachChanges.Visible = true;
//}
CoachRosterGridView.EditIndex = -1;
CoachRosterGridView.DataSource = (List <CoachRoster>)Cache["CoachRoster"];
CoachRosterGridView.DataBind();
}
}
public static bool AddNewCoach(CoachRoster _newCoach)
{
string val = null;
int valid = 0;
using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["BlueJaysConnection"].ConnectionString))
{
OracleCommand cmd = new OracleCommand("addNewCoach_sp", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("coachID", _newCoach.CoachRosterID));
cmd.Parameters.Add(new OracleParameter("coachNum", _newCoach.CoachNumber));
cmd.Parameters.Add(new OracleParameter("c_name", _newCoach.Name));
cmd.Parameters.Add(new OracleParameter("c_position", _newCoach.Position));
con.Open();
valid = cmd.ExecuteNonQuery();
}
return(Convert.ToBoolean(valid));
}
protected void CoachRosterGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (Cache["CoachRoster"] != null)
{
List <CoachRoster> roster = (List <CoachRoster>)Cache["CoachRoster"];
IOrderedDictionary rowValues = e.NewValues;
int coachNum = Convert.ToInt32(CoachRosterGridView.Rows[e.RowIndex].Cells[2].Text);
CoachRoster coach = roster.SingleOrDefault(x => x.CoachNumber == Convert.ToInt32(coachNum));
int indexOfCoach = roster.IndexOf(coach);
coach.Name = rowValues["Name"].ToString();
coach.Position = rowValues["Position"].ToString();
roster.RemoveAt(indexOfCoach);
roster.Insert(indexOfCoach, coach);
if ((bool)Session["CoachChanges"] == false)
{
Session["CoachChanges"] = true;
SaveCoachChanges.Enabled = true;
SaveCoachChanges.Visible = true;
}
CoachRosterGridView.EditIndex = -1;
CoachRosterGridView.DataSource = roster;
CoachRosterGridView.DataBind();
Cache["CoachRoster"] = roster;
}
}
