public DataTable LayBangNCCPhieuNhap(string strMaPhieu)
{
return(ThaoTacDuLieu.LayBang(string.Format(@"select *
from ChiTietPhieuNhap ct, PhieuNhap pn, NhaCungCap ncc
where ct.MaPhieuNhap = pn.MaPhieuNhap and pn.MaNhaCungCap = ncc.MaNhaCungCap
and pn.MaPhieuNhap = '{0}'", strMaPhieu)));
}
public DataTable LayBangPhieuNhap()
{
string query = "select * from PhieuNhap pn, NhanVien nv, NhaCungCap ncc where pn.NhanVienLap = nv.MaNhanVien and pn.MaNhaCungCap = ncc.MaNhaCungCap and pn.TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangKhachHang()
{
string query = "select * from KhachHang where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangKhachHang(string strMaKH)
{
string query = string.Format("select * from KhachHang where TrangThai=1 and MaKhachHang='{0}'", strMaKH);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangKhachHangPX(string strMaPhieu)
{
return(ThaoTacDuLieu.LayBang(string.Format(@"select *
from ChiTietPhieuXuat ct, PhieuXuat px, KhachHang kh
where ct.MaPhieuXuat = px.MaPhieuXuat and px.MaKhachHang = kh.MaKhachHang
and px.MaPhieuXuat = '{0}'", strMaPhieu)));
}
public DataTable LayBangPhieuXuat()
{
string query = "select * from PhieuXuat px, NhanVien nv, KhachHang kh where px.NhanVienLap = nv.MaNhanVien and px.MaKhachHang = kh.MaKhachHang and px.TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangSanPham(string strMaSP)
{
string query = string.Format("select * from SanPham sp,HangSanXuat hsx,LoaiSanPham lsp where sp.TrangThai=1 and sp.MaHangSanXuat = hsx.MaHangSanXuat and sp.MaLoaiSanPham = lsp.MaLoaiSanPham and sp.MaSanPham='{0}'", strMaSP);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangLoaiSanPham()
{
string query = "select * from LoaiSanPham where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public bool ThemSanPham(clsSanPham_DTO sanPham)
{
string strMaSP = "SP" + (ThaoTacDuLieu.DemSoDongCuaBang("SanPham") + 1);
string query = string.Format("insert into SanPham values('{0}',N'{1}','{2}','{3}','{4}','{5}',N'{6}','{7}','{8}','{9}','{10}','{11}',N'{12}','{13}')", strMaSP, sanPham.TenSanPham, sanPham.Hinh, sanPham.GiaMua, sanPham.GiaBan, sanPham.KhuyenMai, sanPham.MoTa, sanPham.BaoHanh, sanPham.SoLuong, sanPham.DonViTinh, sanPham.MaLoaiSanPham, sanPham.MaHangSanXuat, sanPham.GhiChu, 1);
return(ThaoTacDuLieu.ThucThi(query));
}
public DataTable LayBangSanPham()
{
string query = "select * from SanPham sp,HangSanXuat hsx,LoaiSanPham lsp where sp.TrangThai=1 and sp.MaHangSanXuat = hsx.MaHangSanXuat and sp.MaLoaiSanPham = lsp.MaLoaiSanPham";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public bool ThemLoai(clsLoai_DTO loai)
{
string strMaLoai = "LSP" + (ThaoTacDuLieu.DemSoDongCuaBang("LoaiSanPham") + 1);
string query = string.Format("insert into LoaiSanPham values('{0}',N'{1}',N'{2}','{3}')", strMaLoai, loai.TenLoaiSanPham, loai.GhiChu, 1);
return(ThaoTacDuLieu.ThucThi(query));
}
public bool ThemHSX(clsHangSanXuat_DTO HSX)
{
string strMaHSX = "HSX" + (ThaoTacDuLieu.DemSoDongCuaBang("HangSanXuat") + 1);
string query = string.Format("insert into HangSanXuat values('{0}',N'{1}',N'{2}','{3}')", strMaHSX, HSX.TenHangSanXuat, HSX.GhiChu, 1);
return(ThaoTacDuLieu.ThucThi(query));
}
public DataTable LayBangHangSanXuat()
{
string query = "select * from HangSanXuat where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangNhaCungCapTheoMa(string MaNCC)
{
string query = string.Format("select * from NhaCungCap where TrangThai=1 and MaNhaCungCap='{0}'", MaNCC);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
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 <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 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 DataTable LayNVTheoMaNV(string MaNV)
{
string query = "select * from NhanVien where TrangThai = 1 and MaNhanVien='" + MaNV + "'";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangNhaCungCap()
{
string query = "select * from NhaCungCap where TrangThai=1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayBangChucVu()
{
string query = "select Quyen from NhanVien where TrangThai = 1";
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public bool ThemNhanVien(clsNhanVien_DTO nhanvien)
{
string MaNV = "NV" + (ThaoTacDuLieu.DemSoDongCuaBang("NhanVien") + 1);
string query = string.Format("insert into NhanVien values('{0}',N'{1}','{2}','{3}','{4}',N'{5}','{6}','{7}','{8}','{9}','{10}','{11}')", MaNV, nhanvien.TenNV, nhanvien.TenDangNhap, nhanvien.MatKhau, nhanvien.Hinh, nhanvien.DiaChi, nhanvien.CMND, nhanvien.SDT, nhanvien.Email, nhanvien.Quyen, "", 1);
return(ThaoTacDuLieu.ThucThi(query));
}
public DataTable LayThongTinNV(string strTenDN, string strMatKhau)
{
string query = string.Format("select * from NhanVien where TenDangNhap='{0}' and MatKhau='{1}' and TrangThai=1", strTenDN, strMatKhau);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayThongTinNVTheoMa(string strMaNV)
{
string query = string.Format("select * from NhanVien where MaNhanVien='{0}' and TrangThai=1", strMaNV);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
public DataTable LayChiTietPhieu(string strMaPhieu)
{
string query = string.Format("select * from ChiTietPhieuXuat ct,SanPham sp where ct.MaSanPham = sp.MaSanPham and ct.MaPhieuXuat = '{0}'", strMaPhieu);
DataTable dt = ThaoTacDuLieu.LayBang(query);
return(dt);
}
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 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 void CapNhapBaoHanh(string strSoSerial)
{
string strMaSerial = _SerialDAO.LayMaSerial(strSoSerial);
string query = string.Format("update ChiTietBaoHanh set TinhTrang=1 where MaSerial='{0}'", strMaSerial);
ThaoTacDuLieu.ThucThi(query);
}
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 void ThemChiTietBaoHanh(List <clsChiTietBaoHanh_DTO> dsChiTiet, string strMaBH)
{
foreach (clsChiTietBaoHanh_DTO chiTiet in dsChiTiet)
{
string query = string.Format("insert into ChiTietBaoHanh values('{0}','{1}','{2}','{3}',N'{4}',N'{5}')", strMaBH, chiTiet.MaSerial, chiTiet.NgayHenTra, chiTiet.TinhTrang, chiTiet.MotaLoi, chiTiet.GhiChu);
ThaoTacDuLieu.ThucThi(query);
}
}