public static void getSectionDescription()
{
var conn = HelperTools.dbConnection();
try
{
conn.Open();
string query = "SELECT SECTION_ID,SECTION_DESC FROM section";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.CommandText = query;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
HelperTools.sectionDescription.Add(Convert.ToInt32(reader.GetString("SECTION_ID")), reader.GetString("SECTION_DESC"));
}
}
catch (MySqlException ex) { MessageBox.Show("Failed to connect to the server \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
catch (InvalidOperationException ex) { MessageBox.Show("An error occured while running the test:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
catch (Exception ex) { MessageBox.Show("An error occured while running the the test:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally
{
if ((conn.State & ConnectionState.Open) != 0)
{
conn.Close();
}
}
}
private void reports_Load(object sender, EventArgs e)
{
conn = HelperTools.dbConnection();
try
{
conn.Open();
string query = "SELECT CATEGORY_ID,CATEGORY_DESC FROM question_category ORDER BY CATEGORY_ID";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
adapter.Dispose();
cmd.Dispose();
try
{
comboBox2.DataSource = ds.Tables[0];
comboBox2.DisplayMember = "CATEGORY_DESC";
comboBox2.ValueMember = "CATEGORY_ID";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
catch (MySqlException ex) { MessageBox.Show("Failed to connect to the server \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Dispose(); }
catch (InvalidOperationException ex) { MessageBox.Show("An error occured while running the reporting service:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Dispose(); }
catch (Exception ex) { MessageBox.Show("An error occured while running the reporing service:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Dispose(); }
finally
{
if ((conn.State & ConnectionState.Open) != 0)
{
conn.Close();
}
}
}
public static void getScoreperSection(int catID)
{
var conn = HelperTools.dbConnection();
try
{
scorePerSection.Clear();
conn.Open();
//string query = "SELECT questions_per_section.SECTION_ID,questions_per_section.REQUIRED_SCORE,section.SECTION_DESC FROM questions_per_section INNER JOIN section ON section.SECTION_ID = questions_per_section.SECTION_ID WHERE questions_per_section.CATEGORY_ID=@catID;";
string query = "SELECT question_section_mapping.SECTION_ID,question_section_mapping.REQUIRED_SCORE,";
query = query + " section.SECTION_DESC FROM question_section_mapping ";
query = query + " INNER JOIN section ON section.SECTION_ID=question_section_mapping.SECTION_ID ";
query = query + " WHERE question_section_mapping.CATEGORY_ID=@catID; ";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@catID", catID);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
scorePerSection.Add(Convert.ToInt32(reader.GetString("SECTION_ID")), Convert.ToInt32(reader.GetString("REQUIRED_SCORE")));
sectionDescription.Add(Convert.ToInt32(reader.GetString("SECTION_ID")), reader.GetString("SECTION_DESC"));
}
}
catch (MySqlException ex) { MessageBox.Show("Failed to connect to the server \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
catch (InvalidOperationException ex) { MessageBox.Show("An error occured while running the test:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
catch (Exception ex) { MessageBox.Show("An error occured while running the the test:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally
{
if ((conn.State & ConnectionState.Open) != 0)
{
conn.Close();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
conn = HelperTools.dbConnection();
try
{
conn.Open();
if (comboBox1.SelectedItem.ToString() != "Select")
{
if (comboBox2.SelectedIndex > -1)
{
DateTime sDate = dateTimePicker1.Value;
DateTime eDate = dateTimePicker2.Value;
if (sDate < eDate)
{
int catID = Int32.Parse(comboBox2.SelectedValue.ToString());
string query = "";
if (comboBox1.SelectedItem.ToString() == "Test Results")
{
query = query + " SELECT ";
query = query + " CONCAT(test_schedule.CANDIDATE_FNAME, ' ', test_schedule.CANDIDATE_LNAME) AS NAME,";
query = query + " section.SECTION_DESC AS TEST_SECTION, ";
query = query + " test_results.PASS AS RESULT,";
query = query + " test_results.TEST_DATE";
query = query + " FROM test_results";
query = query + " INNER JOIN section ON test_results.SECTION_ID = section.SECTION_ID";
query = query + " INNER JOIN test_schedule ON test_schedule.SCHEDULE_ID = test_results.TEST_SCHEDULE_ID";
query = query + " ORDER BY test_results.TEST_DATE DESC";
}
else if (comboBox1.SelectedItem.ToString() == "Test Schedules")
{
MessageBox.Show("Test Schedules");
}
MySqlDataAdapter mySqlDataAdapter;
mySqlDataAdapter = new MySqlDataAdapter(query, conn);
DataSet DS = new DataSet();
mySqlDataAdapter.Fill(DS);
dataGridView1.DataSource = DS.Tables[0];
if (dataGridView1.RowCount > 0)
{
dataGridView1.Visible = true;
}
else
{
MessageBox.Show("There is no data matching specified criteria", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("The start date cannot be equal to or later than end date", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Please select report category", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Please select report type", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
comboBox1.Focus();
}
}
catch (MySqlException ex) { MessageBox.Show("Failed to connect to the server \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Dispose(); }
catch (InvalidOperationException ex) { MessageBox.Show("An error occured while running the reporting service:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Dispose(); }
catch (Exception ex) { MessageBox.Show("An error occured while running the reporing service:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); this.Dispose(); }
finally
{
if ((conn.State & ConnectionState.Open) != 0)
{
conn.Close();
}
}
}
public static void markTest(int ScheduleID)
{
allScheduleResults.Clear();
string message = "";
ArrayList scheduleResult = new ArrayList();
bool oneSectionFailed = false;
decimal percentage_score = 0;
int attemptedSectionsCount = 0;
StringBuilder insertQuery = new StringBuilder("INSERT INTO test_results (TEST_SCHEDULE_ID,TEST_SEQ_NO,SECTION_ID,TEST_SCORE,PASS) VALUES");
string savevalues = "";
foreach (var SECTION_SCORE in sectionScores)
{
savevalues += "(";
attemptedSectionsCount++;
scheduleResult.Clear();
string section_result = "FAILED";
int SID = Convert.ToInt32(SECTION_SCORE.Key);
string SDE = sectionDescription[SID];
int SEC_SCORE = Convert.ToInt32(SECTION_SCORE.Value);
int MIN_SEC_SCORE = scorePerSection[SID];
int totalSectionQuestions = questionsPerSection[SID];
if (SEC_SCORE >= MIN_SEC_SCORE)
{
section_result = "PASSED";
}
else
{
oneSectionFailed = true;
}
decimal markPercent = Decimal.Divide(SECTION_SCORE.Value, totalSectionQuestions) * 100;
percentage_score += markPercent;
message += "Section: " + SDE + " , Score: " + SECTION_SCORE.Value + "/" + totalSectionQuestions + " , Result: " + section_result + "\n";
scheduleResult.Add(ScheduleID);
scheduleResult.Add(SID);
scheduleResult.Add(markPercent);
scheduleResult.Add(section_result);
allScheduleResults.Add(scheduleResult);
savevalues += scheduleResult[0].ToString() + "," + HelperTools.testSequenceNumber + "," + scheduleResult[1].ToString() + "," + Math.Round(Convert.ToDecimal(scheduleResult[2].ToString()), 2) + "," + "'" + scheduleResult[3].ToString() + "'),";
}
message += "OVERALL RESULT:" + Math.Round(Decimal.Divide(percentage_score, sectionScores.Count), 2) + "%, (" + (oneSectionFailed || attemptedSectionsCount == 0 ? "FAILED" : "PASS") + ")\n";
MessageBox.Show(message, "Test Results");
//saving results;
insertQuery.Append(string.Join(",", savevalues).TrimEnd(','));
insertQuery.Append(";");
MessageBox.Show(insertQuery.ToString());
var conn = HelperTools.dbConnection();
try
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand(insertQuery.ToString(), conn))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
MessageBox.Show("Test results successfully submitted", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (MySqlException ex) { MessageBox.Show("Failed to save results \n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
catch (InvalidOperationException ex) { MessageBox.Show("An error occured while saving results:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
catch (Exception ex) { MessageBox.Show("An error occured while saving results:\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
finally
{
if ((conn.State & ConnectionState.Open) != 0)
{
conn.Close();
}
}
}