public bool CapNhatNguoiDung(clsNguoiDung_DTO nd)
{
if (KiemTraMaNVHopLe(nd.MANV))
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = "";
if (nd.MATKHAU != "")
{
sql = string.Format("UPDATE NGUOIDUNG SET TAIKHOAN = '{0}', MATKHAU = '{1}', LOAIND = '{2}',TRANGTHAI = '{4}' WHERE MANV = '{3}'", nd.TAIKHOAN, nd.MATKHAU, nd.LOAIND, nd.MANV, nd.TRANGTHAI);
}
else
{
sql = string.Format("UPDATE NGUOIDUNG SET TAIKHOAN = '{0}', LOAIND = '{1}',TRANGTHAI = '{3}' WHERE MANV = '{2}'", nd.TAIKHOAN, nd.LOAIND, nd.MANV, nd.TRANGTHAI);
}
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
int rowaff = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(con);
if (rowaff == 0)
{
return(false);
}
return(true);
}
else
{
return(false);
}
}
public List <clsChamCong_DTO> LayBangChamCong()
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = "SELECT * FROM CHAMCONG";
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
List <clsChamCong_DTO> lsCC = new List <clsChamCong_DTO>();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
clsChamCong_DTO CC = new clsChamCong_DTO();
if (!dr.IsDBNull(0))
{
CC.MaCC = dr.GetString(0);
}
if (!dr.IsDBNull(1))
{
CC.Thang = dr.GetInt32(1);
}
if (!dr.IsDBNull(2))
{
CC.Nam = dr.GetInt32(2);
}
if (!dr.IsDBNull(3))
{
CC.Phong = dr.GetString(3);
}
lsCC.Add(CC);
}
ThaoTacDuLieu.DongKetNoi(conn);
return(lsCC);
}
public List <clsNhanVien_DTO> LayNhanVienTheoPhong(string MaPB)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT MANV, HO + ' ' + TEN as HoTen FROM NHANVIEN WHERE PHONG = '{0}'", MaPB);
List <clsNhanVien_DTO> lsNhanVien = new List <clsNhanVien_DTO>();
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
clsNhanVien_DTO nv = new clsNhanVien_DTO();
if (!dr.IsDBNull(0))
{
nv.MaNV = dr.GetString(0);
}
if (!dr.IsDBNull(1))
{
nv.Ho = dr.GetString(1);
}
//if (!dr.IsDBNull(2))
// nv.Ten = dr.GetString(2);
lsNhanVien.Add(nv);
}
ThaoTacDuLieu.DongKetNoi(con);
return(lsNhanVien);
}
public clsQuyDinhLuong_DTO LayQuyDinhLuong()
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = "SELECT * FROM QUYDINHLUONG WHERE MAQD = 'QD1'";
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
clsQuyDinhLuong_DTO QuyDinh = new clsQuyDinhLuong_DTO();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
QuyDinh.MaQD = dr.GetString(0);
}
if (!dr.IsDBNull(1))
{
QuyDinh.LuongToiThieu = dr.GetDecimal(1);
}
if (!dr.IsDBNull(2))
{
QuyDinh.BHXH = dr.GetDouble(2);
}
if (!dr.IsDBNull(3))
{
QuyDinh.BHYT = dr.GetDouble(3);
}
if (!dr.IsDBNull(4))
{
QuyDinh.BHTN = dr.GetDouble(4);
}
}
ThaoTacDuLieu.DongKetNoi(conn);
return(QuyDinh);
}
public clsChamCong_DTO LayBangChamCong(int Thang, int Nam, string MaPB)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT MACC FROM CHAMCONG WHERE THANG = {0} AND NAM = {1} AND PHONG = '{2}'", Thang, Nam, MaPB);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
clsChamCong_DTO CC = new clsChamCong_DTO();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
CC.MaCC = dr.GetString(0);
}
if (!dr.IsDBNull(1))
{
CC.Thang = dr.GetInt32(1);
}
if (!dr.IsDBNull(2))
{
CC.Nam = dr.GetInt32(2);
}
if (!dr.IsDBNull(3))
{
CC.Phong = dr.GetString(3);
}
}
ThaoTacDuLieu.DongKetNoi(conn);
return(CC);
}
public clsNhanVienDangNhap LayThongTinNhanVien(string strTenDN)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT HO, TEN, LOAIND, TAIKHOAN FROM NHANVIEN, NGUOIDUNG WHERE NHANVIEN.MANV = NGUOIDUNG.MANV AND TAIKHOAN = '{0}'", strTenDN);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
clsNhanVienDangNhap nv = new clsNhanVienDangNhap();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
nv.Ho = dr.GetString(0);
}
if (!dr.IsDBNull(1))
{
nv.Ten = dr.GetString(1);
}
if (!dr.IsDBNull(2))
{
nv.Quyen = dr.GetString(2);
}
if (!dr.IsDBNull(3))
{
nv.TaiKhoan = dr.GetString(3);
}
}
ThaoTacDuLieu.DongKetNoi(con);
return(nv);
}
public bool CapNhatChamCong(string sql)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public List <clsHopDong_DTO> LayDanhSachHopDong(string MaNV)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT * FROM HOPDONGLAODONG WHERE MANV = '{0}' ORDER BY TUNGAY", MaNV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
List <clsHopDong_DTO> lsHD = new List <clsHopDong_DTO>();
while (dr.Read())
{
clsHopDong_DTO HD = new clsHopDong_DTO();
if (!dr.IsDBNull(0))
{
HD.MaHDLD = dr.GetString(0);
}
if (!dr.IsDBNull(1))
{
HD.MaNV = dr.GetString(1);
}
if (!dr.IsDBNull(2))
{
HD.LoaiHD = dr.GetString(2);
}
if (!dr.IsDBNull(3))
{
HD.NgayBatDau = dr.GetDateTime(3);
}
if (!dr.IsDBNull(4))
{
HD.NgayKetThuc = dr.GetDateTime(4);
}
if (!dr.IsDBNull(5))
{
HD.DiaDiemLam = dr.GetString(5);
}
if (!dr.IsDBNull(6))
{
HD.CongViec = dr.GetString(6);
}
if (!dr.IsDBNull(7))
{
HD.ThoiGianLam = dr.GetDouble(7);
}
if (!dr.IsDBNull(8))
{
HD.TrangBi = dr.GetString(8);
}
if (!dr.IsDBNull(9))
{
HD.NgayKy = dr.GetDateTime(9);
}
//hợp đồng không xác dịnh thời hạn
lsHD.Add(HD);
}
ThaoTacDuLieu.DongKetNoi(conn);
return(lsHD);
}
public void ThemNhatKy(string TaiKhoan, DateTime ThoiGian, string SuKien)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("INSERT INTO NHATKY(TAIKHOAN,THOIGIAN,SUKIEN) VALUES ('{0}','{1}',N'{2}')", TaiKhoan, ThoiGian, SuKien);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
}
public float LayHeSo(string MaCV, string MaBAC)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT HESO FROM BACLUONG WHERE BAC = '{0}' AND MACV = '{1}'", MaBAC, MaCV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
float kq = float.Parse(cmd.ExecuteScalar().ToString());
ThaoTacDuLieu.DongKetNoi(con);
return(kq);
}
public bool CapNhatThanNhan(clsThanNhan_DTO TN)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("UPDATE THANNHAN SET HOTEN = N'{0}', MOIQH = {1}, NGAYSINH = '{2}', NGHENGHIEP = N'{3}' WHERE MAQHGD = {4}", TN.HoTenTN, TN.MoiQH, TN.NgaySinhTN, TN.NgheNghiepTN, TN.MaQHGD);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public bool ThemThanNhan(clsThanNhan_DTO TN)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("INSERT INTO THANNHAN(MANV,HOTEN,MOIQH,NGAYSINH,NGHENGHIEP) VALUES('{0}',N'{1}',{2},'{3}',N'{4}')", TN.MaNV, TN.HoTenTN, TN.MoiQH, TN.NgaySinhTN, TN.NgheNghiepTN);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = (int)cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public float LayHeSoLuongTheoCongViec(string MaNV)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql_HeSoBacCV = string.Format("select HESO from BACLUONG, NHANVIEN where BACLUONG.BAC = NHANVIEN.MABAC and BACLUONG.MACV = NHANVIEN.MACV and NHANVIEN.MANV = '{0}'", MaNV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql_HeSoBacCV, con);
float HSCV = float.Parse(cmd.ExecuteScalar().ToString());
ThaoTacDuLieu.DongKetNoi(con);
return(HSCV);
}
public bool ThemBangChamCong(clsChamCong_DTO ChamCong)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("INSERT INTO CHAMCONG(MACC,THANG,NAM,PHONG) VALUES('{0}',{1},{2},'{3}')", ChamCong.MaCC, ChamCong.Thang, ChamCong.Nam, ChamCong.Phong);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public bool KiemTraDangNhap(string strTenDN, string strMK)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT COUNT(*) FROM NGUOIDUNG WHERE TAIKHOAN = '{0}' AND MATKHAU = '{1}' AND TrangThai= 1", strTenDN, strMK);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = (int)cmd.ExecuteScalar();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public bool ThemChiTietChamCong(clsChiTietChamCong_DTO ChiTiet)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("INSERT INTO CHITIETCHAMCONG(MACC,MANV) VALUES('{0}','{1}')", ChiTiet.MaCC, ChiTiet.MaNV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public string LayMaPhong(string MaNV)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT MAPB FROM PHONGBAN, NHANVIEN WHERE NHANVIEN.PHONG = PHONGBAN.MAPB AND MANV = '{0}'", MaNV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
string kq = cmd.ExecuteScalar().ToString();
ThaoTacDuLieu.DongKetNoi(con);
return(kq);
}
public bool CapNhatQuyDinhLuong(clsQuyDinhLuong_DTO QuyDinh)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("UPDATE QUYDINHLUONG SET LUONGTOITHIEU = {0}, BHXH = {1},BHYT = {2}, BHTN = {3} WHERE MAQD = 'QD1'", QuyDinh.LuongToiThieu, QuyDinh.BHXH, QuyDinh.BHYT, QuyDinh.BHTN);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = (int)cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public float LayHeSoLuongTheoBangCap(string MaNV)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql_HeSoBangCap = string.Format("select HESO from NHANVIEN, BANGCAP where NHANVIEN.BANGCAP = BANGCAP.MABC and NHANVIEN.MANV = '{0}'", MaNV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql_HeSoBangCap, con);
float HSBC = float.Parse(cmd.ExecuteScalar().ToString());
ThaoTacDuLieu.DongKetNoi(con);
return(HSBC);
}
public bool KiemTraPhongChamCong(int Thang, int Nam, string MaPB)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT COUNT(*) FROM CHAMCONG WHERE THANG = {0} AND NAM = {1} AND PHONG = '{2}'", Thang, Nam, MaPB);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = (int)cmd.ExecuteScalar();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public bool CapNhatNguoiDung(bool TrangThai, string MANV)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("UPDATE NGUOIDUNG SET TRANGTHAI = '{0}' WHERE MANV = '{1}'", TrangThai, MANV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
int rowaff = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(con);
if (rowaff == 0)
{
return(false);
}
return(true);
}
private bool KiemTraMaNVHopLe(string MaNV)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT COUNT(*) FROM NHANVIEN WHERE MANV = '{0}'", MaNV);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
int SoLuongTaiKhoan = (int)cmd.ExecuteScalar();
ThaoTacDuLieu.DongKetNoi(con);
if (SoLuongTaiKhoan != 1)
{
return(false);
}
return(true);
}
public bool SaoLuuCSDL(string NoiLuu)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format(@"BACKUP DATABASE [QLNV_XiNghiep] TO DISK ='{0}' with init", NoiLuu);
//with init: ghi đè
SqlCommand command = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
int kq = command.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(con);
if (kq == -1)
{
return(true);
}
return(false);
}
public bool CapNhatThongTinNhanVien(clsNhanVien_DTO nv)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string ngaysinh = string.Format("{0:yyyy}/{0:MM}/{0:dd}", nv.NgaySinh);
string Ngaybatdaulamviec = string.Format("{0:yyyy}/{0:MM}/{0:dd}", nv.NgayBatDauLamViec);
string sql = string.Format("UPDATE NHANVIEN SET HO = N'{1}', TEN = N'{2}', NGAYSINH = '{3}', DIACHI = N'{4}', CMND = '{5}', GIOITINH = '{6}', NGUYENQUAN = N'{7}', TINHTHANH = '{8}', QUANHUYEN = '{9}', QUOCTICH = '{10}', DANTOC = '{11}', TONGIAO = '{12}', NGAYBATDAU = '{13}', PHONG = '{14}', MABAC = '{15}', MACV = '{16}', BANGCAP = '{17}', TRANGTHAI = '{18}', HINHDAIDIEN = '{19}' WHERE MANV = '{0}'", nv.MaNV, nv.Ho, nv.Ten, ngaysinh, nv.DiaChiThuongTru, nv.CMND, nv.GioiTinh, nv.NguyenQuan, nv.TinhThanh, nv.QuanHuyen, nv.QuocTich, nv.DanToc, nv.TonGiao, Ngaybatdaulamviec, nv.PhongBan, nv.MaBAC, nv.MaCV, nv.BangCap, nv.TrangThai, nv.HINHDAIDIEN);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
int rowAffected = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(con);
if (rowAffected == 0)
{
return(false); // cập nhật thất bại
}
return(true); //cập nhật thành công
}
public bool ThemNhanVien(clsNhanVien_DTO nv)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string ngaysinh = string.Format("{0:yyyy}/{0:MM}/{0:dd}", nv.NgaySinh);
string Ngaybatdaulamviec = string.Format("{0:yyyy}/{0:MM}/{0:dd}", nv.NgayBatDauLamViec);
string sql = string.Format("INSERT INTO NHANVIEN (MANV, HO, TEN, NGAYSINH, DIACHI, CMND, GIOITINH, NGUYENQUAN, TINHTHANH, QUANHUYEN, QUOCTICH, DANTOC, TONGIAO, NGAYBATDAU, PHONG, MABAC, MACV, BANGCAP, TRANGTHAI, HINHDAIDIEN) VALUES ('{0}', N'{1}' ,N'{2}' ,'{3}' ,N'{4}' ,'{5}', '{6}', N'{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', {17}, '{18}','{19}')", nv.MaNV, nv.Ho, nv.Ten, nv.NgaySinh, nv.DiaChiThuongTru, nv.CMND, nv.GioiTinh, nv.NguyenQuan, nv.TinhThanh, nv.QuanHuyen, nv.QuocTich, nv.DanToc, nv.TonGiao, nv.NgayBatDauLamViec, nv.PhongBan, nv.MaBAC, nv.MaCV, nv.BangCap, nv.TrangThai, nv.HINHDAIDIEN);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
int rowAffected = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(con);
if (rowAffected == 0)
{
return(false); // Thêm thất bại
}
return(true); //thêm thành công
}
public List <clsNhanVien_DTO> LayDanhSachNhanVienCC(List <clsPhongBan_DTO> lsPhongBan)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT MANV,HO,TEN,PHONG FROM NHANVIEN WHERE");
for (int i = 0; i < lsPhongBan.Count; i++)
{
if (i == 0)
{
sql += string.Format(" (PHONG = '{0}'", lsPhongBan[i].MAPB);
}
else
{
sql += string.Format(" OR PHONG = '{0}'", lsPhongBan[i].MAPB);
}
}
sql += ") GROUP BY MANV,HO,TEN,PHONG ORDER BY PHONG,TEN";
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
List <clsNhanVien_DTO> lsNhanVien = new List <clsNhanVien_DTO>();
while (dr.Read())
{
clsNhanVien_DTO NhanVien = new clsNhanVien_DTO();
if (!dr.IsDBNull(0))
{
NhanVien.MaNV = dr.GetString(0);
}
if (!dr.IsDBNull(1))
{
NhanVien.Ho = dr.GetString(1);
}
if (!dr.IsDBNull(2))
{
NhanVien.Ten = dr.GetString(2);
}
if (!dr.IsDBNull(3))
{
NhanVien.PhongBan = dr.GetString(3);
}
lsNhanVien.Add(NhanVien);
}
ThaoTacDuLieu.DongKetNoi(conn);
return(lsNhanVien);
}
public bool ThemHopDong(clsHopDong_DTO HD)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = "";
DateTime dt = new DateTime(1900, 1, 1);
if (HD.NgayKetThuc == dt.Date)
{
sql = string.Format("INSERT INTO HOPDONGLAODONG(MAHDLD,MANV,LOAIHD,TUNGAY,DIADIEMLAM,CONGVIEC,THOIGIANLAM,TRANGBILAODONG,NGAYKY,DENNGAY) VALUES ('{0}','{1}',N'{2}','{3}',N'{4}',N'{5}',N'{6}','{7}',N'{8}',{9},{10},'{11}')", "HDLD" + (ThaoTacDuLieu.LaySoLuong("HOPDONGLAODONG", conn) + 1).ToString(), HD.MaNV, HD.LoaiHD, HD.NgayBatDau, HD.DiaDiemLam, HD.CongViec, HD.ThoiGianLam, HD.TrangBi, HD.NgayKy, HD.NgayKetThuc);
}
else
{
sql = string.Format("INSERT INTO HOPDONGLAODONG(MAHDLD,MANV,LOAIHD,TUNGAY,DENNGAY,DIADIEMLAM,CONGVIEC,THOIGIANLAM,TRANGBILAODONG,NGAYKY) VALUES ('{0}','{1}',N'{2}','{3}','{4}',N'{5}',N'{6}',{7},N'{8}','{9}')", "HDLD" + (ThaoTacDuLieu.DemSoLuong("SELECT COUNT(*) FROM HOPDONGLAODONG") + 1).ToString(), HD.MaNV, HD.LoaiHD, HD.NgayBatDau, dt.Date, HD.DiaDiemLam, HD.CongViec, HD.ThoiGianLam, HD.TrangBi, HD.NgayKy);
}
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public bool TaoTaiKhoan(clsNguoiDung_DTO nd)
{
if (KiemTraMaNVHopLe(nd.MANV))
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("INSERT INTO NGUOIDUNG(TAIKHOAN, MATKHAU, LOAIND, MANV, TRANGTHAI) VALUES('{0}','{1}','{2}','{3}','{4}')", nd.TAIKHOAN, nd.MATKHAU, nd.LOAIND, nd.MANV, nd.TRANGTHAI);
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, con);
int rowaff = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(con);
if (rowaff == 0)
{
return(false);
}
return(true);
}
else
{
return(false);
}
}
public bool CapNhatHopDong(clsHopDong_DTO HD)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = "";
DateTime dt = new DateTime(1900, 1, 1);
if (HD.NgayKetThuc == dt.Date)
{
sql = string.Format("UPDATE HOPDONGLAODONG SET LOAIHD = N'{0}', TUNGAY = '{1}', DIADIEMLAM = N'{2}', CONGVIEC = N'{3}', THOIGIANLAM = {4}, TRANGBILAODONG = N'{5}', NGAYKY = '{6}', DENNGAY = '' WHERE MAHDLD = '{7}'", HD.LoaiHD, HD.NgayBatDau, HD.DiaDiemLam, HD.CongViec, HD.ThoiGianLam, HD.TrangBi, HD.NgayKy, HD.MaHDLD);
}
else
{
sql = string.Format("UPDATE HOPDONGLAODONG SET LOAIHD = N'{0}', TUNGAY = '{1}', DENNGAY = '{2}', DIADIEMLAM = N'{3}', CONGVIEC = N'{4}', THOIGIANLAM = {5}, TRANGBILAODONG = N'{6}', NGAYKY = '{7}' WHERE MAHDLD = '{8}'", HD.LoaiHD, HD.NgayBatDau, HD.NgayKetThuc, HD.DiaDiemLam, HD.CongViec, HD.ThoiGianLam, HD.TrangBi, HD.NgayKy, HD.MaHDLD);
}
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
int kq = (int)cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(kq > 0);
}
public List <clsNhatKy_DTO> LayDanhSachNhatKy(DateTime dtBatDau, DateTime dtKetThuc)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = "";
if (dtBatDau == dtKetThuc)
{
sql = string.Format("SELECT * FROM NHATKY WHERE THOIGIAN >= '{0:yyyy}-{0:MM}-{0:dd} 00:00:00' AND THOIGIAN <= '{0:yyyy}-{0:MM}-{0:dd} 23:59:59'", dtKetThuc, dtKetThuc);
}
else
{
sql = string.Format("SELECT * FROM NHATKY WHERE THOIGIAN >= '{0:yyyy}-{0:MM}-{0:dd}' AND THOIGIAN <= '{1:yyyy}-{1:MM}-{1:dd}'", dtBatDau, dtKetThuc);
}
SqlCommand cmd = ThaoTacDuLieu.TaoDoiTuongTruyVan(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
List <clsNhatKy_DTO> lsNK = new List <clsNhatKy_DTO>();
while (dr.Read())
{
clsNhatKy_DTO NK = new clsNhatKy_DTO();
if (!dr.IsDBNull(0))
{
NK.MaNK = dr.GetInt64(0);
}
if (!dr.IsDBNull(1))
{
NK.TaiKhoan = dr.GetString(1);
}
if (!dr.IsDBNull(2))
{
NK.ThoiGian = dr.GetDateTime(2);
}
if (!dr.IsDBNull(3))
{
NK.SuKien = dr.GetString(3);
}
lsNK.Add(NK);
}
ThaoTacDuLieu.DongKetNoi(conn);
return(lsNK);
}