private void resetToolStripMenuItem_Click(object sender, EventArgs e)
{
int count = 0;
foreach (DataGridViewRow row in dgvScore.SelectedRows)
{
string name = row.Cells[0].Value.ToString().Trim();
string query = string.Format("insert into TB_IT_EDU_REDO (r_user, r_datetime) values (N'{0}', '{1}')", name, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
DataServiceEdu.GetInstance().ExecuteNonQuery(query);
string delText = string.Format("delete from TB_IT_EDU_HISTORY where h_user = N'{0}'", name);
string delText1 = string.Format("delete from TB_IT_EDU_RECORD where r_user = N'{0}'", name);
string delText2 = string.Format("delete from TB_IT_EDU_REPORT where r_user = N'{0}'", name);
DataServiceEdu.GetInstance().ExecuteNonQuery(delText);
DataServiceEdu.GetInstance().ExecuteNonQuery(delText1);
DataServiceEdu.GetInstance().ExecuteNonQuery(delText2);
count += 1;
}
MessageBox.Show(count + " records have been reset");
LoadData();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in table.Rows)
{
string question = row.ItemArray[0].ToString().Trim();
string category = row.ItemArray[1].ToString().Trim();
string questionjp = row.ItemArray[2].ToString().Trim();
string categoryjp = row.ItemArray[3].ToString().Trim();
string answer = row.ItemArray[4].ToString().Trim();
string answerjp = row.ItemArray[5].ToString().Trim();
string correct = row.ItemArray[6].ToString().Trim();
string id = row.ItemArray[7].ToString().Trim();
string query = string.Format("if not exists (select * from TB_IT_EDU_QUESTION where q_question = N'{0}') insert into TB_IT_EDU_QUESTION (q_question, q_questionjp, q_type, q_typejp)" +
" values (N'{0}', N'{1}', N'{2}', N'{3}')", question, category, questionjp, categoryjp);
DataServiceEdu.GetInstance().ExecuteNonQuery(query);
string text = string.Format("insert into TB_IT_EDU_ANSWER (a_answer, a_answerjp, a_correct, a_questionid) values (N'{0}', N'{1}', '{2}', '{3}')", answer, answerjp, correct, id);
DataServiceEdu.GetInstance().ExecuteNonQuery(text);
}
}
}
private void LoadNonSubmittedData()
{
DataTable table = new DataTable();
string[] headers = { "staff", "dt", "score", "company" };
foreach (string header in headers)
{
table.Columns.Add(header);
}
string query = "select u_staff, u_company from TB_USER where not exists (select * from TB_IT_EDU_RECORD where r_user = u_staff)";
using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
string user = reader.GetString(0).Trim();
string company = reader.GetString(1).Trim();
table.Rows.Add(user, "-", "-", company);
}
}
dgvScore.DataSource = table;
}
private void btnUpdate_Click(object sender, EventArgs e)
{
List <UserData> list = new List <UserData>();
string query = "select u_staffid, u_staff, u_company from TB_USER";
using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
string staffid = reader.GetString(0).Trim();
string name = reader.GetString(1).Trim();
string company = reader.GetString(2).Trim();
list.Add(new UserData {
id = staffid, name = name, company = company
});
}
}
foreach (UserData data in list)
{
string q1 = string.Format("update TB_IT_EDU_RECORD set r_company = '{0}' where r_user = N'{1}'", data.company, data.name);
DataServiceEdu.GetInstance().ExecuteNonQuery(q1);
string q2 = string.Format("update TB_IT_EDU_REPORT set r_company = '{0}' where r_user = N'{1}'", data.company, data.name);
DataServiceEdu.GetInstance().ExecuteNonQuery(q2);
}
}
public static Int32 GetQuestionId(string content)
{
string query = string.Format("select q_id from TB_IT_EDU_QUESTION where q_question = N'{0}'", content);
//string query = "select top 1 q_id from TB_QUESTION order by q_id desc";
object result = DataServiceEdu.GetInstance().ExecuteScalar(query);
return((int)result);
}
private void LoadData()
{
DataTable table = new DataTable();
string query = "select r_user as staff, r_datetime as dt, r_score as score, r_company as company from TB_IT_EDU_RECORD, TB_USER where r_user = u_staff";
SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEdu.GetInstance().Connection);
sda.Fill(table);
dgvScore.DataSource = table;
}
private void LoadData(string company)
{
string query = string.Format("select u_staffid as id, u_staff as name, u_company as company from TB_USER where u_company like '%{0}%'", company);
DataTable table = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEdu.GetInstance().Connection);
sda.Fill(table);
dgvUser.DataSource = table;
}
private void GetKdhkSubmitted()
{
string query = "select count(*), sum(cast(r_score as int)) from TB_IT_EDU_RECORD where r_company = 'KDHK' group by r_company";
using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
_kdhkS = reader.GetInt32(0);
_kdhkSum = reader.GetInt32(1);
}
}
}
private List <string> QuestionList()
{
List <string> list = new List <string>();
string query = "select q_question from TB_IT_EDU_QUESTION where q_id != 7";
using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
list.Add(reader.GetString(0).Trim());
}
}
return(list);
}
private void btnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable tb1 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDTHK");
foreach (DataRow row in tb1.Rows)
{
string id = row.ItemArray[0].ToString().Trim();
string name = row.ItemArray[1].ToString().Trim();
string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDTHK");
DataServiceEdu.GetInstance().ExecuteNonQuery(query);
}
DataTable tb2 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDHK");
foreach (DataRow row in tb2.Rows)
{
string id = row.ItemArray[0].ToString().Trim();
string name = row.ItemArray[1].ToString().Trim();
string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDHK");
DataServiceEdu.GetInstance().ExecuteNonQuery(query);
}
DataTable tb3 = ImportExcel2007.TranslateToTable(ofd.FileName, "KDAS");
foreach (DataRow row in tb3.Rows)
{
string id = row.ItemArray[0].ToString().Trim();
string name = row.ItemArray[1].ToString().Trim();
string query = string.Format("insert into TB_USER (u_staffid, u_staff, u_company) values ('{0}', N'{1}', '{2}')", id, name, "KDAS");
DataServiceEdu.GetInstance().ExecuteNonQuery(query);
}
}
}
private void LoadData(List <string> list)
{
DataTable table = new DataTable();
string[] headers = { "question", "kdthk", "kdthkno", "kdhk", "kdhkno", "kdas", "kdasno", "total" };
foreach (string header in headers)
{
table.Columns.Add(header);
}
List <ResultList> resultList = new List <ResultList>();
foreach (string question in list)
{
table.Rows.Add(question, "0", "0", "0", "0", "0", "0", "0");
}
foreach (DataRow row in table.Rows)
{
int totalCount = 0;
int correctCount = 0;
int questionId = EducationUtil.GetQuestionId(row.ItemArray[0].ToString().Trim());
string query = string.Format("select count(*), sum(case when r_correct = 'Yes' then 1 else 0 end), r_company from TB_IT_EDU_REPORT where r_questionid = '{0}' group by r_company", questionId);
using (IDataReader reader = DataServiceEdu.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
int total = reader.GetInt32(0);
int correct = reader.GetInt32(1);
double rate = (double)correct / (double)total;
rate = Math.Round(rate, 2) * 100;
string company = reader.GetString(2).Trim();
totalCount = totalCount + total;
correctCount = correctCount + correct;
if (company == "KDTHK")
{
row["kdthk"] = correct + "/" + total + " (" + rate + "%)";
row["kdthkno"] = total;
}
if (company == "KDHK")
{
row["kdhk"] = correct + "/" + total + " (" + rate + "%)";
row["kdhkno"] = total;
}
if (company == "KDAS")
{
row["kdas"] = correct + "/" + total + " (" + rate + "%)";
row["kdasno"] = total;
}
}
}
//Debug.WriteLine("Correct Count: " + correctCount);
int kdthk = Convert.ToInt32(row["kdthkno"]);
int kdhk = Convert.ToInt32(row["kdhkno"]);
int kdas = Convert.ToInt32(row["kdasno"]);
int sum = kdthk + kdhk + kdas;
double totalRate = (double)correctCount / (double)sum;
totalRate = Math.Round(totalRate, 2) * 100;
row["total"] = correctCount + "/" + sum + " (" + totalRate + "%)";
}
dgvResult.DataSource = table;
}
private int kdhkTotal()
{
string query = "select count(*) from TB_USER where u_company = 'KDHK'";
return((int)DataServiceEdu.GetInstance().ExecuteScalar(query));
}
