private void sua1Nv()
{
if (GridNhanvien.MainView.RowCount > 0)
{
HoSoNhanVien nv = new HoSoNhanVien();
nv.Gioitinh = gioTinhTextEdit.Text;
nv.Atm = aTMTextEdit.Text;
nv.Bangcap = bangCapTextEdit.Text;
nv.Bophan = maBoPhanTextEdit.Text;
nv.Chucvu = txtchucvu.Text;
nv.Cmnd = cMNDTextEdit.Text;
nv.Dantoc = danTocTextEdit.Text;
nv.Gioitinh = gioTinhTextEdit.Text;
nv.Hktt = hKTTTextEdit.Text;
nv.Holot = hoLotTextEdit.Text;
nv.Ten = tenTextEdit.Text;
nv.Tthonnhan = tTHonNhanTextEdit.Text;
nv.Manv = maNVTextEdit.Text;
nv.Mathe = maTheTextEdit.Text;
nv.Ngaylamviec = ngayLamViecDateEdit.Text;
nv.Ngaysinh = ngaySinhDateEdit.Text;
nv.Quequan = queQuanTextEdit.Text;
nv.Quoctich = quocTichTextEdit.Text;
nv.Sdt = sDTTextEdit.Text;
frmSuaNhanvien suanv = new frmSuaNhanvien(nv);
suanv.reload = new frmSuaNhanvien.ReloadNhanvien(dataselectNV);
suanv.Show();
}
}
private void btCapnhat_Click(object sender, EventArgs e)
{
if (KT_Insert())
{
HoSoNhanVien nv = new HoSoNhanVien();
ChungminhChitiet cmnd = new ChungminhChitiet();
nv.Manv = maNVTextEdit.Text;
nv.Holot = hoLotTextEdit.Text;
nv.Ten = tenTextEdit.Text;
nv.Mathe = maTheTextEdit.Text;
nv.Bophan = combBophan.Text;
nv.Atm = aTMTextEdit.Text;
nv.Thoiviec = "";
nv.Chucvu = cmbChucvu.Text;
if (gioTinhTextEdit.Text == "Nam")
{
nv.Gioitinh = "1";
}
else
{
nv.Gioitinh = "0";
}
nv.Ngaysinh = ngaySinhDateEdit.Text;
nv.Quequan = queQuanTextEdit.Text;
nv.Hktt = hKTTTextEdit.Text;
nv.Cmnd = cMNDTextEdit.Text;
nv.Ngaylamviec = ngayLamViecDateEdit.Text;
nv.Bangcap = bangCapTextEdit.Text;
if (cmbHonNhan.Text == "Đã kết hôn")
{
nv.Tthonnhan = "1";
}
else
{
nv.Tthonnhan = "0";
}
nv.Dantoc = cmbDantoc.Text;
nv.Quoctich = quocTichTextEdit.Text;
nv.Sdt = sDTTextEdit.Text;
cmnd.Socmnd = sochungminh;
cmnd.Noicap = noicap;
cmnd.Ngaycap = ngaycap.ToString();
if (BUS.BUS_Nhanvien.InsertNhanVien(nv) == "true")
{
MessageBox.Show("Thêm nhân viên thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
reload();
this.Close();
}
else
{
MessageBox.Show("Xin lỗi bạn quá đen không thể thêm sinh viên!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
// thêm 1 nhân viên từ file excel
#region thêm nhân viên từ file excel
public static string InsertNhanVien_Exccel(HoSoNhanVien nv)
{
nv.Ngaysinh = clConver.ConverDMY_MDY(DateTime.Parse(nv.Ngaysinh));
nv.Ngaylamviec = clConver.ConverDMY_MDY(DateTime.Parse(nv.Ngaylamviec));
string s = "insert into NhanVien (manv,holot,ten,mabophan,machucvu,dantoc,mathe,atm,gioitinh,ngaysinh,hktt,cmnd,QueQuan,ngaylamviec,bangcap,tthonnhan,quoctich,sdt";
s += ",anh,thoiviec) values ('{0}',N'{1}',N'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',N'{10}','{11}',N'{12}',";
s += "'{13}',N'{14}','{15}',N'{16}','{17}','','1')";
string sql = string.Format(s, nv.Manv, nv.Holot, nv.Ten, nv.Bophan, nv.Chucvu, nv.Dantoc, nv.Mathe, nv.Atm, nv.Gioitinh,
nv.Ngaysinh, nv.Hktt, nv.Cmnd, nv.Quequan, nv.Ngaylamviec, nv.Bangcap, nv.Tthonnhan, nv.Quoctich, nv.Sdt);
return(DA.Ketnoi.ExcuteNonQuery(sql));
}
// hàm liet ke nhan vien
#region Liệt kê nhân viên nhan vien
public static List <HoSoNhanVien> selectNhanvienfull(int luachon, string k)
{
List <HoSoNhanVien> nv = new List <HoSoNhanVien>();
HoSoNhanVien nv1;
DataTable dt;
string sql = "";
string s = "";
if (luachon == 1)
{
s += " and bp.tenbophan=N'" + k + "'";
}
else if (luachon == 2)
{
s += " and cv.tenchucvu=N'" + k + "'";
}
sql += "select nv.*";
sql += " from NhanVien nv,bophan bp, chucvu cv ";
sql += " where nv.mabophan=bp.mabophan and nv.machucvu=cv.machucvu " + s + "";
dt = DA.Ketnoi.ExcecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
nv1 = new HoSoNhanVien();
nv1.Manv = dt.Rows[i]["Manv"].ToString();
nv1.Mathe = dt.Rows[i]["mathe"].ToString();
nv1.Holot = dt.Rows[i]["holot"].ToString();
nv1.Ten = dt.Rows[i]["ten"].ToString();
nv1.Gioitinh = dt.Rows[i]["gioitinh"].ToString();
nv1.Ngaysinh = dt.Rows[i]["ngaysinh"].ToString();
String.Format("{0:d}", nv1.Ngaysinh);
nv1.Quequan = dt.Rows[i]["quequan"].ToString();
nv1.Chucvu = dt.Rows[i]["machucvu"].ToString();
nv1.Hktt = dt.Rows[i]["Hktt"].ToString();
nv1.Cmnd = dt.Rows[i]["Cmnd"].ToString();
nv1.Bophan = dt.Rows[i]["mabophan"].ToString();
nv1.Hoten = dt.Rows[i]["Holot"].ToString() + " " + dt.Rows[i]["ten"].ToString();
nv1.Atm = dt.Rows[i]["Atm"].ToString();
nv1.Quoctich = dt.Rows[i]["Quoctich"].ToString();
nv1.Ngaylamviec = dt.Rows[i]["Ngaylamviec"].ToString();
nv1.Bangcap = dt.Rows[i]["Bangcap"].ToString();
nv1.Tthonnhan = dt.Rows[i]["tthonnhan"].ToString();
nv1.Dantoc = dt.Rows[i]["Dantoc"].ToString();
nv1.Sdt = dt.Rows[i]["Sdt"].ToString();
System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
string s1 = dt.Rows[i]["Anh"].ToString();
nv1.Anh = encoding.GetBytes(s1);
nv1.Thoiviec = dt.Rows[i]["thoiviec"].ToString();
nv.Add(nv1);
}
return(nv);
}
// sửa nhân viên
#region Sửa nhân viên
public static string updateNhanVien(HoSoNhanVien nv)
{
nv.Bophan = LayMaBoPhan(nv.Bophan);
nv.Chucvu = LayChucVu(nv.Chucvu);
nv.Dantoc = LayDanToc(nv.Dantoc);
nv.Ngaysinh = clConver.ConverDMY_MDY(DateTime.Parse(nv.Ngaysinh));
nv.Ngaylamviec = clConver.ConverDMY_MDY(DateTime.Parse(nv.Ngaylamviec));
string s1 = "Update Nhanvien set holot=N'" + nv.Holot + "',ten=N'" + nv.Ten + "',mabophan='" + nv.Bophan + "',machucvu='" + nv.Chucvu + "',dantoc='" + nv.Dantoc + "',mathe='" + nv.Mathe + "',atm='" + nv.Atm + "',gioitinh='" + nv.Gioitinh + "',ngaysinh='" + nv.Ngaysinh + "',";
s1 += "hktt=N'" + nv.Hktt + "',cmnd='" + nv.Cmnd + "',QueQuan=N'" + nv.Quequan + "',ngaylamviec='" + nv.Ngaylamviec + "',bangcap=N'" + nv.Bangcap + "',tthonnhan='" + nv.Tthonnhan + "',quoctich=N'" + nv.Quoctich + "',sdt='" + nv.Sdt + "',thoiviec='1'";
s1 += " where manv='" + nv.Manv + "'";
string sql = string.Format(s1);
return(DA.Ketnoi.ExcuteNonQuery(sql));
}
// hàm li?t kê nhân viên(ma,ten,bophan,gioitinh)
#region Liệt kê nhân viên
public static List <HoSoNhanVien> selectNhanvien(string bophan, string ten_ma, int ktcheck, int checkbar)
{
List <HoSoNhanVien> nv = new List <HoSoNhanVien>();
HoSoNhanVien nv1;
DataTable dt;
string s1, s2; s1 = s2 = "";
string sql_hopdong = "";
string sql_baohiem = "";
string sql_thoiviec = "";
switch (checkbar)
{
case 1:
sql_hopdong = "and nv.manv not in (select manv from HopDong)";
break;
case 2:
sql_hopdong = " and GETDATE()>= all (select MAX(ngayhh) from HopDong where manv=nv.manv)";
break;
case 3:
sql_hopdong = "and year(getdate())=(select year(MAX(ngayhh)) from HopDong where manv=nv.manv) and MONTH(getdate())+1 = (select MONTH(MAX(ngayhh)) from HopDong where manv=nv.manv) ";
break;
case 4:
sql_hopdong = "and getdate()< (select MAX(ngayhh) from HopDong where manv=nv.manv)";
break;
case 5:
sql_baohiem = "and nv.manv not in (select manv from DMBaoHiem)";
break;
case 6:
sql_baohiem = "and( (year(getdate()) >(select year(MAX(ngaycap))+1 from DMBaoHiem where manv=nv.manv)) or ";
sql_baohiem += " (year(getdate()) =(select year(MAX(ngaycap))+1 from DMBaoHiem where manv=nv.manv) ";
sql_baohiem += " and (month(getdate()) >(select month(MAX(ngaycap)) from DMBaoHiem where manv=nv.manv))) ";
sql_baohiem += " or(year(getdate()) =(select year(MAX(ngaycap))+1 from DMBaoHiem where manv=nv.manv) ";
sql_baohiem += " and (month(getdate()) =(select month(MAX(ngaycap)) from DMBaoHiem where manv=nv.manv)) ";
sql_baohiem += " and (day(getdate()) >(select day(MAX(ngaycap)) from DMBaoHiem where manv=nv.manv))) )";
break;
case 7:
sql_baohiem = "and( (year(getdate()) =(select year(MAX(ngaycap))+1 from DMBaoHiem where manv=nv.manv) ";
sql_baohiem += " and (month(getdate())+1 =(select month(MAX(ngaycap)) from DMBaoHiem where manv=nv.manv))) )";
break;
case 8:
sql_baohiem = "and( (year(getdate()) <(select year(MAX(ngaycap))+1 from DMBaoHiem where manv=nv.manv)) or ";
sql_baohiem += " (year(getdate()) =(select year(MAX(ngaycap))+1 from DMBaoHiem where manv=nv.manv) ";
sql_baohiem += " and (month(getdate()) <(select month(MAX(ngaycap)) from DMBaoHiem where manv=nv.manv))) ";
sql_baohiem += " or(year(getdate()) =(select year(MAX(ngaycap))+1 from DMBaoHiem where manv=nv.manv) ";
sql_baohiem += " and (month(getdate()) =(select month(MAX(ngaycap)) from DMBaoHiem where manv=nv.manv)) ";
sql_baohiem += " and (day(getdate()) <(select day(MAX(ngaycap)) from DMBaoHiem where manv=nv.manv))) )";
break;
case 9:
sql_thoiviec = " and nv.thoiviec=0";
break;
default:
sql_baohiem = sql_hopdong = "";
break;
}
if (ktcheck == 1)
{
s1 = " and bp.tenbophan =N'" + bophan + "' ";
}
s2 = "and (nv.ten like N'%" + ten_ma + "%'or nv.manv like N'%" + ten_ma + "%') ";
string sql = "select nv.*, dt.tendantoc,bp.tenbophan, cv.tenchucvu ";
sql += "from NhanVien nv,bophan bp,dantoc dt, chucvu cv ";
sql += " where cv.machucvu=nv.machucvu and dt.id = nv.dantoc and nv.mabophan=bp.mabophan ";
sql += " " + sql_hopdong + " " + sql_baohiem + " " + sql_thoiviec + " " + s1 + " " + s2 + " ";
dt = DA.Ketnoi.ExcecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
nv1 = new HoSoNhanVien();
nv1.Manv = dt.Rows[i]["Manv"].ToString();
nv1.Mathe = dt.Rows[i]["mathe"].ToString();
nv1.Holot = dt.Rows[i]["holot"].ToString();
nv1.Ten = dt.Rows[i]["ten"].ToString();
if (dt.Rows[i]["gioitinh"].ToString() == "1")
{
nv1.Gioitinh = "Nam";
}
else
{
nv1.Gioitinh = "Nữ";
}
nv1.Ngaysinh = dt.Rows[i]["ngaysinh"].ToString();
String.Format("{0:d}", nv1.Ngaysinh);
nv1.Quequan = dt.Rows[i]["quequan"].ToString();
nv1.Chucvu = dt.Rows[i]["tenchucvu"].ToString();
nv1.Hktt = dt.Rows[i]["Hktt"].ToString();
nv1.Cmnd = dt.Rows[i]["Cmnd"].ToString();
nv1.Bophan = dt.Rows[i]["tenbophan"].ToString();
nv1.Hoten = dt.Rows[i]["Holot"].ToString() + " " + dt.Rows[i]["ten"].ToString();
nv1.Atm = dt.Rows[i]["Atm"].ToString();
nv1.Quoctich = dt.Rows[i]["Quoctich"].ToString();
nv1.Ngaylamviec = dt.Rows[i]["Ngaylamviec"].ToString();
nv1.Bangcap = dt.Rows[i]["Bangcap"].ToString();
if (dt.Rows[i]["Tthonnhan"].ToString() == "0")
{
nv1.Tthonnhan = "Chưa kết hôn";
}
else
{
nv1.Tthonnhan = "Ðã kết hôn";
}
nv1.Dantoc = dt.Rows[i]["tenDantoc"].ToString();
nv1.Sdt = dt.Rows[i]["Sdt"].ToString();
System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
string s = dt.Rows[i]["Anh"].ToString();
nv1.Anh = encoding.GetBytes(s);
nv1.Thoiviec = dt.Rows[i]["thoiviec"].ToString();
nv.Add(nv1);
}
return(nv);
}
// select theo ngay/thang/nam
#region select ngay/thang/nam
public static List <HoSoNhanVien> NgayVaoLam(int thang, int nam)
{
List <HoSoNhanVien> nv = new List <HoSoNhanVien>();
HoSoNhanVien nv1;
string dk = "";
if (thang >= 1 && thang <= 12)
{
dk = " month(ngaylamviec)=thang and year(ngaylamviec)=nam";
}
else
{
dk = " month(ngaylamviec)=thang ";
}
string s = "slect * from NhanVien where " + dk + " ";
string sql = string.Format(s);
DataTable dt = DA.Ketnoi.ExcecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
nv1 = new HoSoNhanVien();
nv1.Manv = dt.Rows[i]["Manv"].ToString();
nv1.Mathe = dt.Rows[i]["mathe"].ToString();
nv1.Holot = dt.Rows[i]["holot"].ToString();
nv1.Ten = dt.Rows[i]["ten"].ToString();
if (dt.Rows[i]["gioitinh"].ToString() == "1")
{
nv1.Gioitinh = "Nam";
}
else
{
nv1.Gioitinh = "Nữ";
}
nv1.Ngaysinh = dt.Rows[i]["ngaysinh"].ToString();
String.Format("{0:d}", nv1.Ngaysinh);
nv1.Quequan = dt.Rows[i]["quequan"].ToString();
nv1.Chucvu = dt.Rows[i]["tenchucvu"].ToString();
nv1.Hktt = dt.Rows[i]["Hktt"].ToString();
nv1.Cmnd = dt.Rows[i]["Cmnd"].ToString();
nv1.Bophan = dt.Rows[i]["tenbophan"].ToString();
nv1.Hoten = dt.Rows[i]["Holot"].ToString() + " " + dt.Rows[i]["ten"].ToString();
nv1.Atm = dt.Rows[i]["Atm"].ToString();
nv1.Quoctich = dt.Rows[i]["Quoctich"].ToString();
nv1.Ngaylamviec = dt.Rows[i]["Ngaylamviec"].ToString();
nv1.Bangcap = dt.Rows[i]["Bangcap"].ToString();
if (dt.Rows[i]["Tthonnhan"].ToString() == "0")
{
nv1.Tthonnhan = "Chưa kết hôn";
}
else
{
nv1.Tthonnhan = "Ðã kết hôn";
}
nv1.Dantoc = dt.Rows[i]["tenDantoc"].ToString();
nv1.Sdt = dt.Rows[i]["Sdt"].ToString();
System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
string s1 = dt.Rows[i]["Anh"].ToString();
nv1.Anh = encoding.GetBytes(s1);
nv1.Thoiviec = dt.Rows[i]["thoiviec"].ToString();
nv.Add(nv1);
}
return(nv);
}
public static string InsertNhanVien_Excel(HoSoNhanVien nv)
{
return(DAO.DAO_Nhanvien.InsertNhanVien_Exccel(nv));
}
// sửa nhân viên
#region sửa nhân viên
public static string updateNhanVien(HoSoNhanVien nv)
{
return(DAO_Nhanvien.updateNhanVien(nv));
}
public frmSuaNhanvien(HoSoNhanVien nv)
{
InitializeComponent();
this.nv = nv;
}
