/// <summary>
/// Calculate last best quiz score.
/// </summary>
private void CalculateScore()
{
// Declare a UserLessonBL object.
UserLessonBL userLessonBL = new UserLessonBL();
// Get UserLesson data.
DataTable record = userLessonBL.GetRecord(UserID, LessonID);
// Reference number of correct and incorrect answers.
int?correctAnswer = record.Rows[0].Field <int?>("Correct_Answer");
int?incorrectAnswer = record.Rows[0].Field <int?>("Incorrect_Answer");
if (correctAnswer != null && incorrectAnswer != null)
{
// Reference total questions.
int totalQuestions = (int)(correctAnswer) + (int)(incorrectAnswer);
// Down cast correct answer.
int correctAnswerToInt = (int)(correctAnswer);
// Calculate percentage of correct answers.
int percentComplete = (int)Math.Round((double)(100 * correctAnswerToInt) / totalQuestions);
// Reference time take of the last best result quiz.
TimeSpan timeTaken = record.Rows[0].Field <TimeSpan>("Quiz_Time");
// Reference time units.
int hr = timeTaken.Hours;
int min = timeTaken.Minutes;
int sec = timeTaken.Seconds;
string MarkColor = "color:blue;";
if (percentComplete < 50)
{
MarkColor = "color:red;";
}
lblMark.Text = "Best Score was: <span style = " + MarkColor + ">" + percentComplete.ToString() + "%" + "</span>" + "</br>Time taken: " + hr + ":" + min + ":" + sec;
}
}
protected void Page_Load(object sender, EventArgs e)
{
// If the user is not logged in redirect to login page.
if (!Context.User.Identity.IsAuthenticated && Context.Session["UserID"] != null)
{
Response.Redirect("Login.aspx");
}
else
{
UserID = Convert.ToInt32(Context.Session["UserID"]);
// Validate query strings.
Validation();
// Reference Lesson ID.
LessonID = Convert.ToInt32(Request.QueryString["ID"]);
if (!IsPostBack)
{
// Insert record if it doesn't exist.
UserLessonBL userLessonBL = new UserLessonBL();
userLessonBL.InserNewRecord(UserID, LessonID);
}
// Create navigation menu.
BindNavMenu(LessonID, UserID);
// Get lesson data.
DataTable lesson = GetLesson(LessonID);
// If lesson data is not equal to null.
if (lesson.Rows.Count > 0)
{
lblLessonTitle.Text = lesson.Rows[0].Field <string>("Title");
lblLessonContent.Text = lesson.Rows[0].Field <string>("Description");
}
// If final test was completed at least once, retrieve and calculate final test score.
CalculateScore();
}
}
/// <summary>
/// Called on Submit button click.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, EventArgs e)
{
reqField.IsValid = false;
// If viewing the pre last question.
if (mvQuestions.ActiveViewIndex == mvQuestions.Views.Count - 2)
{
// Move to last view.
mvQuestions.ActiveViewIndex = mvQuestions.ActiveViewIndex + 1;
reqField.IsValid = true;
btnSubmit.Visible = false;
// Declare a datatable to bind to the result grid view.
DataTable table = new DataTable();
table.Columns.Add("Question", typeof(string));
table.Columns.Add("Your Answer", typeof(string));
table.Columns.Add("Correct?", typeof(string));
table.Columns.Add("TopicID", typeof(int));
table.Columns.Add("Recomended topic review");
// Stop time.
StopWatch.Stop();
string value = string.Empty;
int correct = 0;
int incorrect = 0;
// Translate boolean to values.
for (int i = 0; i < mvQuestions.Views.Count - 1; i++)
{
value = "chkQuizList" + i + "Value";
if (((string)(ViewState[value])).Equals("True"))
{
ViewState[value] = "Yes";
correct = correct + 1;
}
else
{
ViewState[value] = "No";
incorrect = incorrect + 1;
}
// Get the question.
Label label = (Label)(mvQuestions.Views[i].FindControl("lblViewQ" + i));
// If question text contains these html characters, remove them
if (label.Text.Contains("</br></br>"))
{
label.Text = label.Text.Replace("</br></br>", " ");
}
if (label.Text.Contains("</br>"))
{
label.Text = label.Text.Replace("</br>", " ");
}
table.Rows.Add(label.Text, ViewState["chkQuizList" + i + "Text"].ToString(), ViewState["chkQuizList" + i + "Value"].ToString(), ViewState["Q" + i].ToString());
}
// Bind to gridview.
gvResult.DataSource = table;
gvResult.DataBind();
UserLessonBL userLessonBL = new UserLessonBL();
DataTable record = userLessonBL.GetRecord(UserID, LessonID);
bool betterScore = false;
// Store number of correct and incorrect questions.
int?previousCorrect = record.Rows[0].Field <int?>("Correct_answer");
if (previousCorrect != null && previousCorrect < correct)
{
lblNotification.Text += "Congratulations you obtained a better score";
userLessonBL.InsertMark(UserID, LessonID, correct, incorrect);
betterScore = true;
}
else if (previousCorrect == null)
{
userLessonBL.InsertMark(UserID, LessonID, correct, incorrect);
betterScore = true;
}
// Show score.
int percentCorrect = (int)Math.Round((double)(100 * correct) / (correct + incorrect));
lblScore.Text = percentCorrect + "% Correct";
// If time is null or better and score is better, record it.
TimeSpan?quizTime = record.Rows[0].Field <TimeSpan?>("Quiz_Time");
if (quizTime != null)
{
int hr = ((TimeSpan)(quizTime)).Hours;
int min = ((TimeSpan)(quizTime)).Minutes;
int sec = ((TimeSpan)(quizTime)).Seconds;
bool isBetterTime = false;
if (StopWatch.Elapsed.Hours < hr)
{
if (betterScore)
{
userLessonBL.InsertQuizTime(UserID, LessonID, StopWatch.Elapsed);
}
isBetterTime = true;
}
else if (StopWatch.Elapsed.Hours == hr && StopWatch.Elapsed.Minutes < min)
{
if (betterScore)
{
userLessonBL.InsertQuizTime(UserID, LessonID, StopWatch.Elapsed);
}
isBetterTime = true;
}
else if (StopWatch.Elapsed.Hours == hr && StopWatch.Elapsed.Minutes == min && StopWatch.Elapsed.Seconds < sec)
{
if (betterScore)
{
userLessonBL.InsertQuizTime(UserID, LessonID, StopWatch.Elapsed);
}
isBetterTime = true;
}
if (isBetterTime)
{
if (lblNotification.Text == "")
{
lblNotification.Text += "You achieved a better time.";
}
else
{
lblNotification.Text += ". and achieved a better time.";
}
}
}
// If null time is present, record the time.
else
{
userLessonBL.InsertQuizTime(UserID, LessonID, StopWatch.Elapsed);
}
}
// If not pre-least question.
if (mvQuestions.ActiveViewIndex < 4)
{
// Move to next question.
mvQuestions.ActiveViewIndex = mvQuestions.ActiveViewIndex + 1;
// If an option selection was made, set control to validate.
if (reqField.IsValid == false)
{
if (mvQuestions.ActiveViewIndex == 1)
{
reqField.ControlToValidate = "chkQuizList1";
reqField.IsValid = true;
}
if (mvQuestions.ActiveViewIndex == 2)
{
reqField.ControlToValidate = "chkQuizList2";
reqField.IsValid = true;
}
if (mvQuestions.ActiveViewIndex == 3)
{
reqField.ControlToValidate = "chkQuizList3";
reqField.IsValid = true;
}
if (mvQuestions.ActiveViewIndex == 4)
{
reqField.ControlToValidate = "chkQuizList4";
reqField.IsValid = true;
}
}
}
}
/// <summary>
/// Called on submit button click.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, EventArgs e)
{
string errorMessage = string.Empty;
try
{
// Declare variables.
gvResultTable.DataSource = null;
gvResultTable.DataBind();
lblResult.Visible = false;
QueryBL queryBL = new QueryBL();
EmployeeBL employeeBL = new EmployeeBL();
bool correct = true;
// Retrieve the correct query, execute it against the database, and return a datatable representation.
DataTable compareTable = queryBL.GetQueryResult(TopicID);
// If the query is an UPDATE, INSERT or DELETE query. Get the altered result.
if (compareTable.Rows.Count == 0)
{
compareTable = new DataTable();
compareTable = employeeBL.GetAllEmployee();
}
// Reference the user query.
string query = txtTryItOut.Text;
// Check if user text field contains any characters.
if (!string.IsNullOrEmpty(txtTryItOut.Text))
{
query = txtTryItOut.Text.Trim();
}
// Inform user to type text.
else
{
lblResult.Attributes.Add("class", "label label-warning");
lblResult.Visible = true;
// Retrieve error from XML file.
lblResult.Text = ErrorMessage.GetErrorDesc(9).Replace("|", "<br/>");
return;
}
// Recreate the table before running the user query.
employeeBL.RecreateTable();
// Process the user input on the table.
DataTable resultTable = queryBL.ProcessQuery(query);
// If the query is an UPDATE, INSERT or DELETE, retrieve the altered table.
if (resultTable == null || resultTable.Rows.Count == 0)
{
resultTable = new DataTable();
resultTable = employeeBL.GetAllEmployee();
}
// If table was not dropped by user query.
if (employeeBL.IsEmployee() == 1)
{
// Bind to grid view.
gvResultTable.DataSource = resultTable;
gvResultTable.DataBind();
// Compare no of rows and columns of database's query table to user's query table..
if (compareTable.Rows.Count != resultTable.Rows.Count || compareTable.Columns.Count != resultTable.Columns.Count)
{
correct = false;
errorMessage = ErrorMessage.GetErrorDesc(4).Replace("|", "<br/>");
}
// If rows and columns numbers match, compare their content.
else
{
for (int i = 0; i < compareTable.Rows.Count; i++)
{
for (int j = 0; j < compareTable.Columns.Count; j++)
{
if (!(compareTable.Rows[i][j].Equals(resultTable.Rows[i][j])))
{
errorMessage = ErrorMessage.GetErrorDesc(6).Replace("|", "<br/>");;
correct = false;
break;
}
}
}
}
}
else
{
// If table is dropped set correct to false, bind an empty data table to the 1st grid view.
correct = false;
gvResultTable.DataSource = new DataTable();
gvResultTable.DataBind();
// Retrieve error message from XML file.
errorMessage = ErrorMessage.GetErrorDesc(8).Replace("|", "<br/>");;
}
if (correct)
{
errorMessage = ErrorMessage.GetErrorDesc(5);
lblResult.Visible = true;
lblResult.Attributes.Add("class", "label label-success");
lblResult.Text = errorMessage;
userTopicBL = new UserTopicBL();
// Set Topic completion date.
userTopicBL.SetCompleteDate(UserID, TopicID);
// Check if lesson is complete, by comparing complete topics with the number of topics under current lesson.
TopicBL topicBL = new TopicBL();
int countTopicsUnderLesson = topicBL.GetCountTopicsByLessonID(LessonID);
int countTopicsCompletedByUser = topicBL.GetCountCompletedTopics(LessonID, UserID);
// If all lessons are complete add the lesson completion date.
if (countTopicsCompletedByUser == countTopicsUnderLesson)
{
UserLessonBL userLessonBL = new UserLessonBL();
userLessonBL.SetCompletionDate(UserID, LessonID);
}
}
// If not correct, show the error message to the user.
else
{
lblResult.Attributes.Add("class", "label label-warning");
lblResult.Visible = true;
lblResult.Text = errorMessage;
}
// Drop table and recreate it.
employeeBL.RecreateTable();
}
// This is an attempt to show different messages to the users if the submitted query gives an SQL exception.
catch (SqlException ex)
{
// Error number 102 refers to bad syntax.
if (ex.Number == 102)
{
errorMessage = ErrorMessage.GetErrorDesc(3).Replace("|", "<br/>");
}
// User tries to create a table (currently not implemeted). Since user has no permission to create en exception is thrown.
else if (ex.Number == 262)
{
errorMessage = ErrorMessage.GetErrorDesc(7).Replace("|", "<br/>");
}
// Invalid field name.
else if (ex.Number == 207)
{
errorMessage = ErrorMessage.GetErrorDesc(10).Replace("|", "<br/>");
}
else
// Currently other types of exceptions show a general error.
{
errorMessage = ErrorMessage.GetErrorDesc(3).Replace("|", "</br>");
}
lblResult.Attributes.Add("class", "label label-warning");
lblResult.Visible = true;
lblResult.Text = errorMessage;
}
}
/// <summary>
/// Create side navigation menu.
/// </summary>
private void BindSideMenu()
{
// Create an instance of lesson business logic.
LessonBL lesson = new LessonBL();
// Get all lessons available in table "Lesson"
DataTable lessons = lesson.GetLessons();
// Create an instance of UserLesson business logic.
UserLessonBL userLesson = new UserLessonBL();
DataTable userLessonTable = null;
// If records are present in table Lesson.
if (lessons.Rows.Count > 0)
{
// Loop through the lessons rows.
foreach (DataRow row in lessons.Rows)
{
// Create a list item.
HtmlGenericControl listItem = new HtmlGenericControl("li");
// Create a link Button.
LinkButton linkB = new LinkButton();
// Get the lesson ID from the record.
int lessonID = Convert.ToInt32(row["ID"]);
// Get the record from "User_Lesson" table that contains the user ID and Lesson ID
userLessonTable = userLesson.GetRecord(UserID, lessonID);
// If the record is blank, means that the lesson is not complete by the user, so put a white very good image.
if (userLessonTable.Rows.Count == 0)
{
linkB.Text = "<img src=http://localhost:3787/image/c1.jpg> " + row[1].ToString();
}
// If the record does not contain a complete date it also means that the lesson is not complete.
else if (userLessonTable.Rows[0].Field <DateTime?>("DateCompleted") == null)
{
linkB.Text = "<img src=http://localhost:3787/image/c1.jpg> " + row[1].ToString();
}
// If there is the record with a complete date mark the link button with a green very good sign.
else
{
linkB.Text = "<img src=http://localhost:3787/image/c2.jpg> " + row[1].ToString();
}
// Add runat server attribute to the link button.
linkB.Attributes.Add("runat", "server");
// Pass the lesson ID to the link button.
linkB.CommandArgument = lessonID.ToString();
// Add the Redirect event.
linkB.Click += new EventHandler(Redirect);
// Add the link button to the list item.
listItem.Controls.Add(linkB);
// Add the list item to the unsorted list.
navSideMenu.Controls.Add(listItem);
}
}
}
