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 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 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 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 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 int LaySoLuongTN()
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
int SL = ThaoTacDuLieu.LaySoLuong("THANNHAN", conn);
ThaoTacDuLieu.DongKetNoi(conn);
return(SL);
}
public DataTable LayTheNhanVien(string MaNV)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("SELECT * FROM NHANVIEN, CHUCVU WHERE NHANVIEN.MACV = CHUCVU.MACV AND MANV = '{0}'", MaNV);
DataTable thenv = ThaoTacDuLieu.ThucThiTruyVan(sql, con);
return(thenv);
}
public int LaySoLuongNhanVien()
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
int slnv = ThaoTacDuLieu.LaySoLuong("NHANVIEN", con);
ThaoTacDuLieu.DongKetNoi(con);
return(slnv);
}
public void DatLaiSoLuongSP(string strMaSP, int iSL)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string sql = string.Format("update SanPham set SoLuong={0} where MaSanPham='{1}'", iSL, strMaSP);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
}
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 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 static int ThucThiCauLenh(string sql)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
SqlCommand cmd = new SqlCommand(sql, con);
int kq = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(con);
return(kq);
}
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 static int DemSoLuong(string sql)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
SqlCommand cmd = new SqlCommand(sql, con);
int kq = (int)cmd.ExecuteScalar();
ThaoTacDuLieu.DongKetNoi(con);
return(kq);
}
// Để sử dụng cho ReportViewer
public static DataTable LayBang(string strSql)
{
SqlConnection con = ThaoTacDuLieu.TaoVaMoKetNoi();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(strSql, con);
da.Fill(dt);
ThaoTacDuLieu.DongKetNoi(con);
return(dt);
}
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 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 bool XoaKhachHang(string MaKH)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string query = string.Format("update KhachHang set TrangThai=0 where MaKhachHang='{0}'", MaKH);
SqlCommand cmd = new SqlCommand(query, conn);
int thucthi = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(thucthi == 1);
}
public DataTable TimKiemKhachHang(string query)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
SqlDataAdapter da = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
da.Fill(dt);
ThaoTacDuLieu.DongKetNoi(conn);
return(dt);
}
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 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 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 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 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 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 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 SuaKhachHang(clsKhachHang_DTO khdto)
{
SqlConnection conn = ThaoTacDuLieu.TaoVaMoKetNoi();
string query = string.Format("update KhachHang set TenKhachHang=N'{0}',CMND='{1}',SoDT='{2}',DiaChi=N'{3}' where MaKhachHang='{4}'", khdto.TenKhachHang, khdto.CMND, khdto.SoDT, khdto.DiaChi, khdto.MaKhachHang);
SqlCommand cmd = new SqlCommand(query, conn);
int thucthi = cmd.ExecuteNonQuery();
ThaoTacDuLieu.DongKetNoi(conn);
return(thucthi == 1);
}
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 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);
}