private void TestLoginCheck()
{
string Redirect = "false";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfDB"].ToString());
conn.Open();
SqlCommand login = new SqlCommand(
"SELECT TC.ConferenceID, C.RegionalTestsID, M.MemberID, FirstName+' '+LastName AS Name " +
"FROM TestCredentials TC" +
" INNER JOIN Conferences C ON TC.ConferenceID=C.ConferenceID AND C.OnlineTestingEnd >= CONVERT(date, GETDATE()+(@TZO/1440.0))" +
" INNER JOIN NationalMembers M ON TC.MemberID=M.MemberID " +
"WHERE LOWER(TestUsername)=@username AND TestPassword=@password", conn);
login.Parameters.Add("@username", SqlDbType.VarChar).Value = Username.Text.ToLower();
login.Parameters.Add("@password", SqlDbType.VarChar).Value = Password.Text;
login.Parameters.Add("@TZO", SqlDbType.VarChar).Value = (Request.Cookies["TZO"] != null) ? Request.Cookies["TZO"].Value.ToString() : "0";
SqlDataAdapter adapter = new SqlDataAdapter(login);
DataTable tblLogins = new DataTable();
adapter.Fill(tblLogins);
if (tblLogins.Rows.Count != 0)
{
NotFound.Visible = false;
((Panel)Master.FindControl("LogoutLink")).Visible = true;
Session["TakingTest"] = "true";
Session["ConferenceID"] = tblLogins.Rows[0]["ConferenceID"];
Session["RegionalTestsID"] = tblLogins.Rows[0]["RegionalTestsID"];
Session["MemberID"] = tblLogins.Rows[0]["MemberID"];
Session["Name"] = tblLogins.Rows[0]["Name"];
// Determine if this member is in the process of taking an event, which means they were taking one within the past 4 minutes
SqlCommand cmd = new SqlCommand("SELECT ISNULL(MIN(EventID),0) FROM ConferenceMemberEvents " +
"WHERE (ObjectiveScore=-1 OR SYSDATETIME()<DATEADD(minute,4,TestingTime))" +
" AND ConferenceID=" + Session["ConferenceID"].ToString() +
" AND MemberID=" + Session["MemberID"].ToString(), conn);
int TestInProgress = (Int32)cmd.ExecuteScalar();
conn.Close();
if (TestInProgress != 0)
{
Session["EventID"] = TestInProgress;
Redirect = "Test-ConfEvent.aspx";
}
else
{
Session["EventID"] = "0";
Redirect = "Test-Selection.aspx";
}
Response.Cookies["FCSdata"].Value = Global.Encrypt("true;" +
Session["ConferenceID"].ToString() + ";" +
Session["RegionalTestsID"].ToString() + ";" +
Session["MemberID"].ToString() + ";" +
Session["Name"].ToString() + ";" +
Session["EventID"].ToString() + ";I");
}
if (Redirect != "false")
{
Response.Redirect(Redirect);
}
}
protected void btnStartTest_Click(object sender, EventArgs e)
{
Session["EventID"] = TestList.SelectedValue;
// As a backup of the session state, store an encrypted cookie
Response.Cookies["FCSdata"].Value = Global.Encrypt("true;" +
Session["ConferenceID"].ToString() + ";" +
Session["RegionalTestsID"].ToString() + ";" +
Session["MemberID"].ToString() + ";" +
Session["Name"].ToString() + ";" +
Session["EventID"].ToString() + ";I");
Response.Redirect("Test-ConfEvent.aspx");
}
private void JudgeLoginCheck()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConfDB"].ToString());
conn.Open();
SqlCommand login = new SqlCommand(
"SELECT JudgeID, ConferenceID, E.EventID, E.EventType, JudgeUsername " +
"FROM JudgeCredentials J" +
" INNER JOIN NationalEvents E ON J.EventID=E.EventID " +
"WHERE LOWER(JudgeUsername)=@username AND JudgePassword=@password", conn);
login.Parameters.Add("@username", SqlDbType.VarChar).Value = Username.Text.ToLower();
login.Parameters.Add("@password", SqlDbType.VarChar).Value = Password.Text;
SqlDataAdapter adapter = new SqlDataAdapter(login);
DataTable tblLogins = new DataTable();
adapter.Fill(tblLogins);
if (tblLogins.Rows.Count != 0)
{
NotFound.Visible = false;
((Panel)Master.FindControl("LogoutLink")).Visible = true;
Session["JudgingEvent"] = "true";
Session["JudgeID"] = tblLogins.Rows[0]["JudgeID"];
Session["ConferenceID"] = tblLogins.Rows[0]["ConferenceID"];
Session["EventID"] = tblLogins.Rows[0]["EventID"];
Session["EventType"] = tblLogins.Rows[0]["EventType"];
Session["Name"] = tblLogins.Rows[0]["JudgeUsername"];
// Save session state variables in a cookie in case the session gets interrupted
Response.Cookies["FCSdata"].Value = Global.Encrypt("true;" +
Session["JudgeID"].ToString() + ";" +
Session["ConferenceID"].ToString() + ";" +
Session["EventID"].ToString() + ";" +
Session["EventType"].ToString() + ";" +
Session["Name"].ToString());
// Go to the judge score entry page
Response.Redirect("Judge-ScoreEntry.aspx");
}
}
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();
}