public static int GetKDHGCount()
{
string query = "select count(*) from TB_STAFF where st_company = 'KDHK'";
object result = DataServiceEducation.GetInstance().ExecuteScalar(query);
return((int)result);
}
private void btnSave_Click(object sender, EventArgs e)
{
string mgtno = txtRef.Text;
string content = txtContent.Text;
string contentjp = txtContentJp.Text;
string ansA = txtAnsA.Text;
string ansB = txtAnsB.Text;
string ansC = txtAnsC.Text;
string ansD = txtAnsD.Text;
string ansJpA = txtAnsJpA.Text;
string ansJpB = txtAnsJpB.Text;
string ansJpC = txtAnsJpC.Text;
string ansJpD = txtAnsJpD.Text;
string correctA = ckbAnsA.Checked ? "True" : "False";
string correctB = ckbAnsB.Checked ? "True" : "False";
string correctC = ckbAnsC.Checked ? "True" : "False";
string correctD = ckbAnsD.Checked ? "True" : "False";
string qText = string.Format("update TB_QUESTION set q_mgtno = '{0}', q_content = N'{1}'" +
", q_contentjp = N'{2}' where q_id = '{3}'", mgtno, content, contentjp, _id);
DataServiceEducation.GetInstance().ExecuteNonQuery(qText);
UpdateAnsA(ansA, ansJpA, correctA, _id);
UpdateAnsB(ansB, ansJpB, correctB, _id);
UpdateAnsC(ansC, ansJpC, correctC, _id);
UpdateAnsD(ansD, ansJpD, correctD, _id);
MessageBox.Show("Record has been saved");
this.DialogResult = DialogResult.OK;
}
public static int GetEducation()
{
string query = "select top 1 e_id from TB_EDUCATION where e_enabled = 'True' order by e_id desc";
object result = DataServiceEducation.GetInstance().ExecuteScalar(query);
return((int)result);
}
private void LoadData(string filter, int educationId)
{
DataTable table = new DataTable();
string[] headers = { "company", "staffid", "name", "select" };
foreach (string header in headers)
{
table.Columns.Add(header);
}
string query = filter == "Failed" ? string.Format("select st_company as company, st_staffid as staffid, st_name as name from TB_STAFF, TB_RECORD" +
" where st_name = r_name and r_result = 'Failed' and r_locked = 'locked' and r_educationid = '{0}'", educationId) : string.Format("select st_company as company, st_staffid as staffid, st_name as name from TB_STAFF" +
" where not exists (select * from TB_RECORD where st_name = r_name and r_educationid = '{0}')", educationId);
using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
string company = reader.GetString(0);
string staffId = reader.GetString(1);
string name = reader.GetString(2);
table.Rows.Add(new object[] { company, staffId, name, "False" });
}
}
dgvFailedList.DataSource = table;
}
public static int GetKDHGRecordCount(int educationid)
{
string query = string.Format("select count(*) from TB_RECORD where r_company = 'kdhk' and r_educationid = '{0}'", educationid);
object result = DataServiceEducation.GetInstance().ExecuteScalar(query);
return((int)result);
}
public static int GetEducationId(string title)
{
string query = string.Format("select e_id from TB_EDUCATION where e_title = N'{0}'", title);
object result = DataServiceEducation.GetInstance().ExecuteScalar(query);
return((int)result);
}
private void tsbtnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable tmptable = new DataTable();
tmptable = ImportExcel2007.TranslateToTable(ofd.FileName);
foreach (DataRow row in tmptable.Rows)
{
string staffid = row.ItemArray[0].ToString();
staffid = staffid.ToLower();
string name = staffid.StartsWith("hk") ? AdUtil.getUsernameByUserId(staffid, "kmhk.local") : AdUtil.getUsernameByUserId(staffid, "kmas.local");
string company = staffid.StartsWith("hk") ? "KDTHK" : staffid.StartsWith("as") ? "KDAS" : "KDHK";
string query = string.Format("if exists (select * from TB_STAFF where st_staffid = '{0}')" +
" update TB_STAFF set st_name = N'{1}', st_company = '{2}' where st_staffid = '{0}' else" +
" insert into TB_STAFF (st_staffid, st_name, st_company) values ('{0}', N'{1}', '{2}')", staffid, name, company);
DataServiceEducation.GetInstance().ExecuteNonQuery(query);
}
}
MessageBox.Show("Record has been saved.");
}
private void LoadData()
{
dgvQuestion.Rows.Clear();
DataTable table = new DataTable();
string query = "select e_id, e_title, e_from, e_to, e_enabled, e_notice from TB_EDUCATION";
SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection);
sda.Fill(table);
foreach (DataRow row in table.Rows)
{
string id = row.ItemArray[0].ToString();
string title = row.ItemArray[1].ToString();
string from = row.ItemArray[2].ToString();
string to = row.ItemArray[3].ToString();
string enabled = row.ItemArray[4].ToString();
string notice = row.ItemArray[5].ToString();
Image img = enabled == "True" ? Properties.Resources.tick_icon : Properties.Resources.cross_icon;
string kdthk = EducationUtil.GetKDTHKRecordCount(Convert.ToInt32(id)) + " / " + EducationUtil.GetKDTHKCount();
string kdas = EducationUtil.GetKDASRecordCount(Convert.ToInt32(id)) + " / " + EducationUtil.GetKDASCount();
string kdhg = EducationUtil.GetKDHGRecordCount(Convert.ToInt32(id)) + " / " + EducationUtil.GetKDHGCount();
dgvQuestion.Rows.Add(id, title, from, to, enabled, img, notice, kdthk, kdas, kdhg);
}
}
private void UpdateAnsD(string ansD, string ansjpD, string correct, string id)
{
string query = string.Format("update TB_ANSWER set a_answer = N'{0}', a_answerjp = N'{1}'" +
", a_correct = '{2}' where a_questionid = '{3}' and a_symbol = 'D.'", ansD, ansjpD, correct, id);
DataServiceEducation.GetInstance().ExecuteNonQuery(query);
}
private void LoadData(string company)
{
DataTable table = new DataTable();
string query = string.Format("select st_staffid as staffid, st_name as name, st_company as company from TB_STAFF where st_company like '%{0}%'", company);
SqlDataAdapter sda = new SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection);
sda.Fill(table);
dgvStaff.DataSource = table;
}
public static bool IsUserExists(string staffid)
{
string query = string.Format("select count(*) from TB_STAFF where st_staffid = '{0}'", staffid);
object result = DataServiceEducation.GetInstance().ExecuteScalar(query);
if (result is DBNull || (int)result == 0)
{
return(false);
}
return(true);
}
public static bool IsEducationOpened()
{
string query = "select count(*) from TB_EDUCATION where e_enabled = 'True'";
object result = DataServiceEducation.GetInstance().ExecuteScalar(query);
if (result is DBNull || (int)result == 0)
{
return(false);
}
return(true);
}
public static bool IsTitleExist(string title)
{
string query = string.Format("select count(*) from TB_EDUCATION where e_title = N'{0}'", title);
object result = DataServiceEducation.GetInstance().ExecuteScalar(query);
if (result is DBNull || (int)result == 0)
{
return(false);
}
return(true);
}
private void LoadData()
{
string query = "select q_id as id, q_content as content, q_contentjp as contentjp" +
", q_mgtno as mgtno from TB_QUESTION order by q_mgtno";
DataTable table = new DataTable();
SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection);
sda.Fill(table);
dgvQuestion.DataSource = table;
}
private void SearchData(string type)
{
DataTable table = new DataTable();
string query = string.Format("select q_id as id, q_content as content, t_type as type" +
" from TB_QUESTION, TB_MASTER_TYPE where q_type = t_tag and q_type like '%{0}%'", type);
SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(query, DataServiceEducation.GetInstance().Connection);
sda.Fill(table);
dgvQuestion.DataSource = table;
}
private void enableToolStripMenuItem_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(dgvQuestion.SelectedRows[0].Cells[0].Value);
string query = "update TB_EDUCATION set e_enabled = 'False'";
DataServiceEducation.GetInstance().ExecuteNonQuery(query);
string text = string.Format("update TB_EDUCATION set e_enabled = 'True' where e_id = '{0}'", id);
DataServiceEducation.GetInstance().ExecuteNonQuery(text);
this.LoadData();
}
private void tsbtnDelete_Click(object sender, EventArgs e)
{
switch (MessageBox.Show("Delete user " + dgvStaff.SelectedRows[0].Cells[0].Value.ToString() + " ? All related record will be deleted.", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
{
case DialogResult.Yes:
string query = string.Format("delete from TB_STAFF where st_staffid = '{0}'", dgvStaff.SelectedRows[0].Cells[0].Value.ToString());
DataServiceEducation.GetInstance().ExecuteNonQuery(query);
MessageBox.Show("Record has been saved");
this.LoadData("");
break;
case DialogResult.No:
break;
}
}
private void LoadData(string source)
{
DataTable table = new DataTable();
string[] headers = { "education", "company", "name", "score", "id" };
foreach (string header in headers)
{
table.Columns.Add(header);
}
string query = string.Format("select e_title, st_company, st_name, r_score, st_id" +
" from TB_EDUCATION, TB_RECORD, TB_STAFF where e_id = r_educationid and r_name = st_name and (e_title like '%{0}%' or st_company like '%{0}%')", source);
using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
string title = reader.GetString(0);
string company = reader.GetString(1);
string name = reader.GetString(2);
string score = reader.GetString(3);
int id = reader.GetInt32(4);
table.Rows.Add(new object[] { title, company, name, score, id });
}
}
string text = string.Format("select e_title, st_company, st_name, st_id from TB_EDUCATION, TB_STAFF, TB_RECORD" +
" where (e_title like '%{0}%' or st_company like '%{0}%') and r_educationid = e_id and not exists" +
" (select * from TB_RECORD where r_name = st_name and (e_title like '%{0}%' or st_company like '%{0}%'))", source);
using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(text))
{
while (reader.Read())
{
string title = reader.GetString(0);
string company = reader.GetString(1);
string name = reader.GetString(2);
int id = reader.GetInt32(3);
table.Rows.Add(new object[] { title, company, name, "Waiting", id });
}
}
DataTable tb = DeleteDuplicateFromDataTable(table, "id");
dgvRecord.DataSource = tb;
}
private void LoadAnswer(string id)
{
string query = string.Format("select a_symbol, a_answer, a_answerjp, a_correct from TB_ANSWER where a_questionid = '{0}'", id);
using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
string symbol = reader.GetString(0);
string answer = reader.GetString(1);
string answerjp = reader.GetString(2);
string correct = reader.GetString(3);
if (symbol == "A.")
{
txtAnsA.Text = answer;
txtAnsJpA.Text = answerjp;
ckbAnsA.Checked = correct == "True" ? true : false;
ckbAnsJpA.Checked = correct == "True" ? true : false;
}
else if (symbol == "B.")
{
txtAnsB.Text = answer;
txtAnsJpB.Text = answerjp;
ckbAnsB.Checked = correct == "True" ? true : false;
ckbAnsJpB.Checked = correct == "True" ? true : false;
}
else if (symbol == "C.")
{
txtAnsC.Text = answer;
txtAnsJpC.Text = answerjp;
ckbAnsC.Checked = correct == "True" ? true : false;
ckbAnsJpC.Checked = correct == "True" ? true : false;
}
else
{
txtAnsD.Text = answer;
txtAnsJpD.Text = answerjp;
ckbAnsD.Checked = correct == "True" ? true : false;
ckbAnsJpD.Checked = correct == "True" ? true : false;
}
}
}
}
public static List <int> GetAllTypeId()
{
List <int> list = new List <int>();
string query = "select t_id from TB_MASTER_TYPE";
using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
int id = reader.GetInt32(0);
list.Add(id);
}
}
return(list);
}
private void LoadQuestion(string id)
{
string query = string.Format("select q_mgtno, q_content, q_contentjp from TB_QUESTION where q_id = '{0}'", id);
using (IDataReader reader = DataServiceEducation.GetInstance().ExecuteReader(query))
{
while (reader.Read())
{
string mgtno = reader.GetString(0);
string content = reader.GetString(1);
string contentjp = reader.GetString(2);
txtRef.Text = mgtno;
txtContent.Text = content;
txtContentJp.Text = contentjp;
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (EducationUtil.IsUserExists(txtStaffId.Text))
{
MessageBox.Show("User already exists.");
return;
}
string staffId = txtStaffId.Text;
string userName = lblUsername.Text;
string company = lblCompany.Text;
string query = string.Format("insert into TB_STAFF (st_staffid, st_name, st_company)" +
" values ('{0}', N'{1}', '{2}')", staffId, userName, company);
DataServiceEducation.GetInstance().ExecuteNonQuery(query);
MessageBox.Show("Record has been saved.");
this.DialogResult = DialogResult.OK;
}