private void cmdintatca_dakyten_Click(object sender, EventArgs e)
{
if (Conn_Access.State == 0)
{
Conn_Access.Open();
}
OleDbCommand cmdxoa = new OleDbCommand("delete from tblrptbailam", Conn_Access);
cmdxoa.ExecuteNonQuery();
//OleDbDataAdapter apt1 = new OleDbDataAdapter("select * from [minh$]", Conn_Excel);
//if (Conn_Excel.State == 0) Conn_Excel.Open();
Conn_Excel.Open();
OleDbDataAdapter apt1 = new OleDbDataAdapter("select * from [Tá»ng quan $]", Conn_Excel);
DataTable dt1 = new DataTable();
apt1.Fill(dt1);
socau = (dt1.Columns.Count - 5).ToString();
Conn_Excel.Close();
//MessageBox.Show(socau);
cathi = cbocathi.Text;
monthi = cbomonthi.Text;
nghe = cbonghe.Text;
OleDbDataAdapter apt = new OleDbDataAdapter("select * from tblbailam where trangthai='1'", Conn_Access);
DataTable dt = new DataTable();
apt.Fill(dt);
string tenhv;
DateTime dn;
DateTime dn1;
DateTime result;
long so1 = 0;
long so2 = 0;
long so3 = 0;
long gio = 0;
long phut = 0;
long giay = 0;
DataColumn col = dt.Columns["hovaten"];
DataColumn col1 = dt.Columns["batdauvaoluc"];
DataColumn col2 = dt.Columns["dahoanthanh"];
for (int i = 0; i < dt.Rows.Count; i++)
{
dn = DateTime.Parse(dt.Rows[i][col1].ToString());
giothi = getchuoi(dn.Hour) + ":" + getchuoi(dn.Minute) + ":" + getchuoi(dn.Second);
//MessageBox.Show(giothi);
ngaythi = getchuoi(dn.Day) + "/" + getchuoi(dn.Month) + "/" + dn.Year.ToString();
ngay = getchuoi(dn.Day);
thang = getchuoi(dn.Month);
nam = dn.Year.ToString();
if (DateTime.TryParse(dt.Rows[i][col2].ToString(), out result) == false)
{
dn1 = dn.AddHours(2);
}
else
{
dn1 = DateTime.Parse(dt.Rows[i][col2].ToString());
}
so1 = dn.Hour * 3600 + dn.Minute * 60 + dn.Second;
so2 = dn1.Hour * 3600 + dn1.Minute * 60 + dn1.Second;
so3 = so2 - so1;
gio = (so3 / 3600);
so3 = so3 - gio * 3600;
phut = (so3 / 60);
so3 = so3 - phut * 60;
giay = so3;
thoigianlambai = getchuoi((int)gio) + ":" + getchuoi((int)phut) + ":" + getchuoi((int)giay);
//MessageBox.Show(thoigianlambai);
tenhv = dt.Rows[i][col].ToString();
masinhvien = getmasinhvien(tenhv);
getdulieu_report(tenhv);
}
frmrptbailam f = new frmrptbailam();
f.ShowDialog();
/*if (MessageBox.Show("Có muốn lưu học viên?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
* {
* if (Conn_Access.State == 0) Conn_Access.Open();
* OleDbCommand cmdcapnhat = new OleDbCommand("update tblbailam set trangthai='1'", Conn_Access);
* cmdcapnhat.ExecuteNonQuery();
* dakyten();
* chuakyten();
* }*/
}
//////////////////////////////////in tat ca//////////////////////////////////////////////////////////////////
void getdulieu_report()
{
OleDbDataAdapter apt = new OleDbDataAdapter("select * from tblbailam where trangthai='0'", Conn_Access);
DataTable dt = new DataTable();
apt.Fill(dt);
string[] a = new string[127];
for (int i = 0; i < 127; i++)
{
a[i] = "";
}
string[] ten = new string[127];
ten[0] = "hovaten";
ten[1] = "batdauvaoluc";
ten[2] = "dahoanthanh";
ten[3] = "thoigianlambai";
ten[4] = "diemso";
ten[5] = "trangthai";
ten[6] = "id";
int index = 1;
for (int i = 7; i < 127; i++)
{
ten[i] = "cau" + index.ToString();
index++;
}
///////////////////////////////////////////////////////////////////////
//giothi = txtgiothi.Text;
//ngaythi = dateTimePicker_tungay.Value.ToString();
//MessageBox.Show(giothi);
//////////////////////////////////////////////////////////////////////
int k = 0;
string str;
if (Conn_Access.State == 0)
{
Conn_Access.Open();
}
bool kt;
foreach (DataRow row in dt.Rows)
{
//////////////////////////////////////////////////////////////////
//str = "delete from tblrptbailam";
//OleDbCommand cmd2 = new OleDbCommand(str, Conn_Access);
//cmd2.ExecuteNonQuery();
//cmd2.Dispose();
//Conn_Access.Close();
//Conn_Access.Open();
//////////////////////////////////////////////////////////////////
kt = false;
str = "insert into tblrptbailam(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
str += ten[i] + ",";
}
else
{
str += ten[i];
}
}
str = str + ",cathi,nghe,monthi,ngaythi,giothi,ngay,thang,nam,socau,strlambai";
str += ") values(";
k = 1;
int j = 0;
foreach (DataColumn col in dt.Columns)
{
//////////////////////////////////////////////////
/*if (j == 0)
* {
* if (kiemtraten(row[col].ToString()) == true)
* {
* kt = true;
* break;
* }
* }
* j++;*/
if (j == 1)
{
tam = row[col].ToString();
}
j++;
if (k < dt.Columns.Count)
{
str += "'" + row[col].ToString() + "'" + ",";
}
else
{
str += "'" + row[col].ToString() + "'";
}
k++;
////////////////////////////////////////////////////
}
//if (kt == false)
//{
//DateTime date_ngay;
str = str + ",'" + cathi + "','" + nghe + "','" + monthi + "','" + ngaythi + "','" + giothi + "','" + ngay + "','" + thang + "','" + nam + "','" + socau + "','" + thoigianlambai + "'";
str += ")";
OleDbCommand cmd = new OleDbCommand(str, Conn_Access);
cmd.ExecuteNonQuery();
cmd.Dispose();
//}//////////////////////////////////////////////////////////////////////////
//str = "update tblrptbailam set cathi='"+cathi+"',nghe='"+nghe+"',monthi='"+monthi+"',ngaythi='"+ngaythi+"',giothi='"+giothi+"',ngay='"+ngay+"',thang='"+thang+"',nam='"+nam+"'";
//OleDbCommand cmd1 = new OleDbCommand(str, Conn_Access);
//cmd1.ExecuteNonQuery();
///////////////////////////////////////////////////////////////////////////
//bl.Refresh();
//bl.PrintToPrinter(1, true, 1, 1);
////////////////////////////////////////////////////////////////////////////
}
frmrptbailam f = new frmrptbailam();
f.ShowDialog();
if (MessageBox.Show("Có muốn lưu học viên?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (Conn_Access.State == 0)
{
Conn_Access.Open();
}
OleDbCommand cmdcapnhat = new OleDbCommand("update tblbailam set trangthai='1'", Conn_Access);
cmdcapnhat.ExecuteNonQuery();
dakyten();
chuakyten();
}
}