protected void Page_Load(object sender, EventArgs e)
{
#region This page is restricted to those who have signed in to take a test
if ((string)Session["TakingTest"] != "true")
{
// If the session state is lost, check for the existence of a backup cookie
HttpCookie SessionBackup = Request.Cookies.Get("FCSdata");
if (SessionBackup != null)
{
string[] FCSdata = Global.Decrypt(SessionBackup.Value).Split(';');
if (FCSdata[0] == "true")
{
Session["ConferenceID"] = FCSdata[1];
Session["RegionalTestsID"] = FCSdata[2];
Session["MemberID"] = FCSdata[3];
Session["Name"] = FCSdata[4];
Session["EventID"] = FCSdata[5];
Session["EventType"] = FCSdata[6];
}
else
{
// If the cookie is not valid, go to login in page
Server.Transfer("default.aspx");
}
}
else
{
// If the cookie is missing , go to login in page
Server.Transfer("default.aspx");
}
}
// Display menu
((SiteMapDataSource)Master.FindControl("FCSSiteMapData")).StartingNodeUrl = "#TakingTest";
((Menu)Master.FindControl("FCSMenu")).DataBind();
#endregion
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfDB"].ToString());
SqlCommand sqlCmd = new SqlCommand("", cnn);
cnn.Open();
if (!IsPostBack)
{
#region Get the name of the conference and the event being tested on
SqlDataAdapter sqlConfEvent = new SqlDataAdapter(
"SELECT ConferenceName, EventName, EventType FROM Conferences INNER JOIN NationalEvents E ON" +
" E.EventID=" + Session["EventID"] + " WHERE ConferenceID=" + Session["ConferenceID"], cnn);
DataTable tblConfEvent = new DataTable();
sqlConfEvent.Fill(tblConfEvent);
lblConferenceName.Text = tblConfEvent.Rows[0]["ConferenceName"].ToString();
lblEventName.Text = tblConfEvent.Rows[0]["EventName"].ToString();
Session["EventType"] = tblConfEvent.Rows[0]["EventType"].ToString();
// As a backup of the session state, store an encrypted cookie
HttpCookie cookie = new HttpCookie("FCSdata");
cookie.Value = Global.Encrypt("true;" +
Session["ConferenceID"].ToString() + ";" +
Session["RegionalTestsID"].ToString() + ";" +
Session["MemberID"].ToString() + ";" +
Session["Name"].ToString() + ";" +
Session["EventID"].ToString() + ";" +
Session["EventType"].ToString());
Response.Cookies.Add(cookie);
#endregion
#region Determine if the student is re-entering the test by seeing if they have an elapsed time for this event
sqlCmd.CommandText =
"SELECT ElapsedTime=ISNULL(ObjectiveElapsedTime,0) FROM ConferenceMemberEvents " +
"WHERE ConferenceID=" + Session["ConferenceID"] +
" AND EventID=" + Session["EventID"] +
" AND MemberID=" + Session["MemberID"];
int ElapsedTime = (Int32)sqlCmd.ExecuteScalar();
#endregion
#region Initialize new test
Session.Remove("Questions");
if (ElapsedTime == 0)
{
// Assign a default score to indicate that the test has been taken
// For team events, update the objective score for all team members; otherwise, just update the member's individual score
string sqlMember;
if (Session["EventType"].ToString() == "T")
{
sqlMember =
"MemberID IN (SELECT TM.MemberID FROM ConferenceMemberEvents CME" +
" INNER JOIN NationalMembers M ON CME.MemberID=M.MemberID" +
" INNER JOIN ConferenceMemberEvents TEAM ON CME.ConferenceID=TEAM.ConferenceID AND CME.EventID=TEAM.EventID AND CME.TeamName=TEAM.TeamName" +
" INNER JOIN NationalMembers TM ON TEAM.MemberID=TM.MemberID AND M.ChapterID=TM.ChapterID " +
"WHERE" +
" CME.ConferenceID=" + Session["ConferenceID"] +
" AND CME.EventID=" + Session["EventID"] +
" AND CME.MemberID=" + Session["MemberID"] + ")";
}
else
{
sqlMember = "MemberID=" + Session["MemberID"];
}
// Give team members a default score of -9
sqlCmd.CommandText =
"UPDATE ConferenceMemberEvents SET ObjectiveScore=-9 " +
"WHERE ConferenceID=" + Session["ConferenceID"] + " AND EventID=" + Session["EventID"] + " AND " + sqlMember;
sqlCmd.ExecuteNonQuery();
// Give the person who signed in a default score of -10
sqlCmd.CommandText =
"UPDATE ConferenceMemberEvents SET ObjectiveScore=-10 " +
"WHERE ConferenceID=" + Session["ConferenceID"] + " AND EventID=" + Session["EventID"] + " AND MemberID=" + Session["MemberID"];
sqlCmd.ExecuteNonQuery();
// Set Testing Time for only the person who is signed in for the test -- for team events, this is just one of the team members
sqlCmd.CommandText =
"UPDATE ConferenceMemberEvents SET TestingTime=SYSDATETIME()" +
" WHERE ConferenceID=" + Session["ConferenceID"] + " AND EventID=" + Session["EventID"] + " AND MemberID=" + Session["MemberID"];
sqlCmd.ExecuteNonQuery();
// Delete any previous test responses for this event for this member, just in case
sqlCmd.CommandText =
"DELETE R FROM TestResponses R INNER JOIN TestQuestions Q ON R.QuestionID=Q.QuestionID " +
"WHERE ConferenceID=" + Session["ConferenceID"] + " AND EventID=" + Session["EventID"] + " AND MemberID=" + Session["MemberID"];
sqlCmd.ExecuteNonQuery();
// Generate default answers for this test for this member
sqlCmd.CommandText =
"INSERT INTO TestResponses" +
" SELECT MemberID=" + Session["MemberID"] + ", ConferenceID=" + Session["ConferenceID"] + ", QuestionID, Response=5" +
" FROM TestQuestions Q" +
" WHERE Q.RegionalTestsID=" + Session["RegionalTestsID"] + " AND Q.EventID=" + Session["EventID"];
sqlCmd.ExecuteNonQuery();
// Start the test time limit countdown
Session["TestTimeLimit"] = DateTime.Now.AddMinutes(60);
}
#endregion
#region Re-enter a test in progress
if (ElapsedTime != 0)
{
// If there is a non-zero elapsed time, then the student is re-entering a test already in progress.
// The TestResponses table contains all the answers they've entered so far.
// We just need to reset the objective score (in case it's been set to -1) and set the countdown clock to give them as much time as they had remaining.
sqlCmd.CommandText =
"UPDATE ConferenceMemberEvents SET ObjectiveScore=-10 " +
"WHERE ConferenceID=" + Session["ConferenceID"] + " AND EventID=" + Session["EventID"] + " AND MemberID=" + Session["MemberID"];
sqlCmd.ExecuteNonQuery();
TimeSpan time1 = new TimeSpan(0, 0, 0, 0, ElapsedTime);
Session["TestTimeLimit"] = DateTime.Now.AddMinutes(60).Subtract(time1);
}
#endregion
#region Populate the form with the questions for this test and the responses for this student
sqlTestQuestions.SelectParameters["RegionalTestsID"].DefaultValue = Session["RegionalTestsID"].ToString();
sqlTestQuestions.SelectParameters["ConferenceID"].DefaultValue = Session["ConferenceID"].ToString();
sqlTestQuestions.SelectParameters["EventID"].DefaultValue = Session["EventID"].ToString();
sqlTestQuestions.SelectParameters["MemberID"].DefaultValue = Session["MemberID"].ToString();
sqlTestQuestions.DataBind();
fvTestQuestions.DataBind();
lblCurrentQuestion.Text = (fvTestQuestions.PageIndex + 1).ToString();
lblTotalQuestions.Text = fvTestQuestions.PageCount.ToString();
#endregion
}
#region Every time the page loads, see if we have a cached table containing the current responses; generate one if needed
DataTable tblCurrentResponses = new DataTable();
if (Session["Responses"] == null)
{
SqlDataAdapter sqlQuestionResponses = new SqlDataAdapter(
"SELECT R.* FROM TestResponses R INNER JOIN TestQuestions Q ON R.QuestionID=Q.QuestionID " +
"WHERE Q.EventID=" + Session["EventID"] +
" AND R.ConferenceID=" + Session["ConferenceID"] +
" AND R.MemberID=" + Session["MemberID"], cnn);
sqlQuestionResponses.Fill(tblCurrentResponses);
Session["Responses"] = tblCurrentResponses;
}
else
{
tblCurrentResponses = (DataTable)Session["Responses"];
}
#endregion
#region Every time the page loads, display the number of unanswered questions remaining and how much time is left
DataRow[] NumUnanswered = tblCurrentResponses.Select("Response=5");
lblUnanswered.Text = NumUnanswered.Length.ToString();
Timer1_Tick(null, null);
#endregion
cnn.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
#region This page is restricted to performance event judges
if ((string)Session["JudgingEvent"] != "true")
{
// If the session state is lost, check for the existence of a backup cookie
HttpCookie SessionBackup = Request.Cookies.Get("FCSdata");
if (SessionBackup != null)
{
string[] FCSdata = Global.Decrypt(SessionBackup.Value).Split(';');
if (FCSdata[0] == "true")
{
Session["JudgeID"] = FCSdata[1];
Session["ConferenceID"] = FCSdata[2];
Session["EventID"] = FCSdata[3];
Session["EventType"] = FCSdata[4];
Session["Name"] = FCSdata[5];
}
else
{
// If the cookie is not valid, go to login in page
Server.Transfer("default.aspx");
}
}
else
{
// If the cookie is missing , go to login in page
Server.Transfer("default.aspx");
}
}
// Display menu
((SiteMapDataSource)Master.FindControl("FCSSiteMapData")).StartingNodeUrl = "#JudgingEvent";
((Menu)Master.FindControl("FCSMenu")).DataBind();
#endregion
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfDB"].ToString());
SqlCommand sqlCmd = new SqlCommand("", cnn);
cnn.Open();
if (!IsPostBack)
{
// After the page fully loads, run a client-side javascript to display the current total score
Page.ClientScript.RegisterStartupScript(this.GetType(), "go",
"<script type=text/javascript>calcScore()</script>");
// Populate the forms based on the currently signed-in judge and the event they're judging
sqlPerfScoresMaint.SelectParameters["JudgeID"].DefaultValue = Session["JudgeID"].ToString();
sqlPerfScoresMaint.SelectParameters["EventID"].DefaultValue = Session["EventID"].ToString();
sqlPerfCommentsMaint.SelectParameters["JudgeID"].DefaultValue = Session["JudgeID"].ToString();
#region Get the name of the conference and the event being tested on
SqlDataAdapter sqlConfEvent = new SqlDataAdapter(
"SELECT ConferenceName, EventName, EventType " +
"FROM Conferences" +
" INNER JOIN NationalEvents E ON E.EventID=" + Session["EventID"] + " " +
"WHERE ConferenceID=" + Session["ConferenceID"], cnn);
DataTable tblConfEvent = new DataTable();
sqlConfEvent.Fill(tblConfEvent);
lblConferenceName.Text = tblConfEvent.Rows[0]["ConferenceName"].ToString();
lblEventName.Text = tblConfEvent.Rows[0]["EventName"].ToString();
// As a backup of the session state, store an encrypted cookie
/*
* HttpCookie cookie = new HttpCookie("FCSdata");
* cookie.Value = Global.Encrypt("true;" +
* Session["ConferenceID"].ToString() + ";" +
* Session["RegionalTestsID"].ToString() + ";" +
* Session["MemberID"].ToString() + ";" +
* Session["Name"].ToString() + ";" +
* Session["EventID"].ToString() + ";" +
* Session["EventType"].ToString());
* Response.Cookies.Add(cookie);
*/
#endregion
#region Fill the selection drop-down box with a list of competitors, either individuals or teams
DataTable tblCompetitors = new DataTable();
if (Session["EventType"].ToString() == "T")
{
#region Populate drop-down with team names
// Get a list of students competing in this event, multiple rows per team
SqlDataAdapter sqlStudents = new SqlDataAdapter(
"select cme.MemberID, Team=ChapterName+', '+TeamName, LastName " +
"from ConferenceMemberEvents cme" +
" inner join NationalMembers m on cme.MemberID=m.MemberID" +
" inner join Chapters c on m.ChapterID=c.ChapterID " +
"where ConferenceID=" + Session["ConferenceID"] + " and EventID=" + Session["EventID"] + " " +
"order by ChapterName, TeamName, LastName, FirstName", cnn);
DataTable tblStudents = new DataTable();
sqlStudents.Fill(tblStudents);
// Create a table of teams for this event, combining the appropriate students together
tblCompetitors.Columns.Add("MemberID");
tblCompetitors.Columns.Add("Competitor");
string prevMemberID = tblStudents.Rows[0]["MemberID"].ToString();
string prevTeam = tblStudents.Rows[0]["Team"].ToString();
string members = " (";
foreach (DataRow rwStudents in tblStudents.Rows)
{
if (prevTeam != rwStudents["Team"].ToString())
{
tblCompetitors.Rows.Add(prevMemberID, prevTeam + members.Substring(0, members.Length - 2) + ")");
prevMemberID = rwStudents["MemberID"].ToString();
prevTeam = rwStudents["Team"].ToString();
members = " (" + rwStudents["Lastname"] + ", ";
}
else
{
members += rwStudents["Lastname"] + ", ";
}
}
tblCompetitors.Rows.Add(prevMemberID, prevTeam + members.Substring(0, members.Length - 2) + ")");
#endregion
}
else
{
#region Populate drop-down with student names
SqlDataAdapter sqlCompetitors = new SqlDataAdapter(
"select cme.MemberID, Competitor=ChapterName+', '+FirstName+' '+LastName " +
"from ConferenceMemberEvents cme" +
" inner join NationalMembers m on cme.MemberID=m.MemberID" +
" inner join Chapters c on m.ChapterID=c.ChapterID " +
"where ConferenceID=" + Session["ConferenceID"] + " and EventID=" + Session["EventID"] + " " +
"order by ChapterName, FirstName, LastName", cnn);
sqlCompetitors.Fill(tblCompetitors);
#endregion
}
ddCompetitors.DataSource = tblCompetitors;
ddCompetitors.DataBind();
#endregion
}
// The page reloads every time the student/team is changed
#region Determine if this event judge has entered scores for this student/team
sqlCmd.CommandText =
"SELECT Score=ISNULL(SUM(Response),0) FROM JudgeResponses " +
"WHERE JudgeID=" + Session["JudgeID"].ToString() +
" AND MemberID=" + ddCompetitors.SelectedValue;
// If the score is zero, then we can start with a fresh slate
if ((Int32)sqlCmd.ExecuteScalar() == 0)
{
// Just in case there's already a set of zeros, delete any existing responses
sqlCmd.CommandText =
"DELETE FROM JudgeResponses " +
"WHERE JudgeID=" + Session["JudgeID"].ToString() +
" AND MemberID=" + ddCompetitors.SelectedValue;
sqlCmd.ExecuteNonQuery();
// Generate default responses of 0 for this judge for this competitor
sqlCmd.CommandText =
"INSERT INTO JudgeResponses" +
" SELECT " + Session["JudgeID"].ToString() + "," + ddCompetitors.SelectedValue + ",PerfCriteriaID,0" +
" FROM JudgeCriteria C WHERE C.EventID=" + Session["EventID"].ToString();
sqlCmd.ExecuteNonQuery();
}
#endregion
#region Determine if this event judge has entered comments for this student/team
sqlCmd.CommandText =
"SELECT ISNULL(CO.PerfComments,'<empty>') " +
"FROM JudgeCredentials CR" +
" LEFT JOIN JudgeComments CO ON CR.JudgeID=CO.JudgeID AND CO.MemberID=" + ddCompetitors.SelectedValue + " " +
"WHERE CR.JudgeID=" + Session["JudgeID"].ToString();
string PerfComments = sqlCmd.ExecuteScalar().ToString();
// If there are no comments, then we can start with a fresh slate
if (PerfComments == "<empty>")
{
// Just in case, delete any existing record
sqlCmd.CommandText =
"DELETE FROM JudgeComments " +
"WHERE JudgeID=" + Session["JudgeID"].ToString() +
" AND MemberID=" + ddCompetitors.SelectedValue;
sqlCmd.ExecuteNonQuery();
// Generate default responses for this judge for this competitor
// Insert the conference and event ID because after the conference is over we delete the judge credentials
sqlCmd.CommandText =
"INSERT INTO JudgeComments (JudgeID,MemberID,ConferenceID,EventID,PerfComments) VALUES (" +
Session["JudgeID"].ToString() + "," +
ddCompetitors.SelectedValue + "," +
Session["ConferenceID"].ToString() + "," +
Session["EventID"].ToString() + "," +
"'(No comments)')";
sqlCmd.ExecuteNonQuery();
}
#endregion
cnn.Close();
}