public string TangSanPham()
{
string newMaSP = "SP0001";
string caulenh = "SELECT MAX(MaSP) FROM SanPham";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
int sp = Convert.ToInt32(dr.GetString(0).Remove(0, 2));
sp++;
newMaSP = "SP" + sp.ToString("d4");
dr.Close();
conn.Close();
return(newMaSP);
}
else
{
return(newMaSP);
}
}
return(newMaSP);
}
public List <NhanVienDTO> DanhSachNhanVien()
{
List <NhanVienDTO> lstnhanvien = new List <NhanVienDTO>();
string caulenh = "SELECT MaNV, HoNV, TenNV, LoaiNV, MatKhau, GioiTinh,NgaySinh, DiaChi, SDT, Luong, HinhAnh, nv.TrangThai FROM NhanVien nv INNER JOIN LoaiNhanVien lnv ON nv.MaLoaiNV=lnv.MaLoaiNV WHERE nv.TrangThai = 1";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
NhanVienDTO nhanviendto = new NhanVienDTO();
nhanviendto.MaNV = dr["MaNV"].ToString();
nhanviendto.HoNV = dr[1].ToString();
nhanviendto.TenNV = dr[2].ToString();
nhanviendto.MaLoaiNV = dr[3].ToString();
nhanviendto.MatKhau = dr[4].ToString();
nhanviendto.GioiTinh = dr[5].ToString();
nhanviendto.NgaySinh = DateTime.Parse(dr[6].ToString());
nhanviendto.DiaChi = dr[7].ToString();
nhanviendto.SDT = dr[8].ToString();
nhanviendto.Luong = Convert.ToDouble(dr[9].ToString());
nhanviendto.HinhAnh = dr[10].ToString();
nhanviendto.TrangThai = int.Parse(dr[11].ToString());
lstnhanvien.Add(nhanviendto);
}
dr.Close();
conn.Close();
return(lstnhanvien);
}
public List <SanPhamDTO> DanhSachSanPham()
{
List <SanPhamDTO> lstsanpham = new List <SanPhamDTO>();
string caulenh = "Select MaSP, TenSP, TenLoaiSP, XuatXu, GiaTien, HinhAnh, SoLuong, TenNCC, sp.TrangThai FROM SanPham sp INNER JOIN LoaiSanPham lsp ON sp.MaLoaiSP = lsp.MaLoaiSP INNER JOIN NhaCungCap ncc ON sp.MaNCC = ncc.MaNCC where sp.TrangThai = 1";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
SanPhamDTO sanphamdto = new SanPhamDTO();
sanphamdto.MaSP = dr[0].ToString();
sanphamdto.TenSP = dr[1].ToString();
sanphamdto.MaLoaiSP = dr[2].ToString();
sanphamdto.XuatXu = dr[3].ToString();
sanphamdto.GiaTien = Convert.ToDouble(dr[4].ToString());
sanphamdto.HinhAnh = dr[5].ToString();
sanphamdto.SoLuong = int.Parse(dr[6].ToString());
sanphamdto.MaNCC = dr[7].ToString();
sanphamdto.TrangThai = int.Parse(dr[8].ToString());
lstsanpham.Add(sanphamdto);
}
dr.Close();
conn.Close();
return(lstsanpham);
}
public int ThemHoaDonNhap(HoaDonNhapDTO HoaDonNhapDTO)
{
string CauLenh = "INSERT INTO HoaDonNhap (MaHDN, MaNV, NgayNhap,TongTien,TrangThai) values ('{0}','{1}','{2}','{3}','1')";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(String.Format(CauLenh, HoaDonNhapDTO.MaHDN, HoaDonNhapDTO.MaNV, HoaDonNhapDTO.NgayNhap, HoaDonNhapDTO.TongTien, HoaDonNhapDTO.TrangThai = 1), conn);
return(kq);
}
public int DoiMatKhauDAO(string manv, string matkhau)
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
string query = "Update NhanVien set MatKhau= N'" + matkhau + "' where MaNV='" + manv + "'";
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(query, conn);
return(kq);
}
public bool ThemCTHoaDonNhap(CTHDNhapDTO CTHoaDonNhapDTO)
{
string CauLenh = "INSERT INTO ChiTietHoaDonNhap (MaCTHoaDonNhap, MaSP, GiaNhap, SoLuong, ThanhTien, TrangThai) VALUES ('{0}','{1}','{2}','{3}','{4}','1')";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(String.Format(CauLenh, CTHoaDonNhapDTO.MaCTHoaDonNhap, CTHoaDonNhapDTO.MaSP, CTHoaDonNhapDTO.GiaNhap, CTHoaDonNhapDTO.SoLuong, CTHoaDonNhapDTO.ThanhTien, CTHoaDonNhapDTO.TrangThai = 1), conn);
return(kq != 0);
}
public bool SuaSanPham(SanPhamDTO sanphamdto)
{
string caulenh = "UPDATE SanPham SET TenSP = N'{0}', MaLoaiSP = {1}, XuatXu = N'{2}', GiaTien = {3},HinhAnh = {4}, SoLuong = {5}, MaNCC = {6}, TrangThai = {7} WHERE MaSP = '" + sanphamdto.MaSP + "' ";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh("UPDATE SanPham SET TenSP = N'" + sanphamdto.TenSP + "', MaLoaiSP = '" + sanphamdto.MaLoaiSP + "', XuatXu = N'" + sanphamdto.XuatXu + "', GiaTien = " + sanphamdto.GiaTien + ",HinhAnh = '" + sanphamdto.HinhAnh + "', SoLuong = " + sanphamdto.SoLuong + ", MaNCC = '" + sanphamdto.MaNCC + "' WHERE MaSP = '" + sanphamdto.MaSP + "' ", conn);
return(kq != 0);
}
public int ThemHoaDon(HoaDonXuatDTO hoadonxuatDTO)
{
string CauLenh = "INSERT INTO HoaDonXuat(MaHDX, MaNV, MaKH, NgayXuat, TongTien, TrangThai) VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '1')";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(CauLenh, hoadonxuatDTO.MaHDX, hoadonxuatDTO.MaNV, hoadonxuatDTO.MaKH, hoadonxuatDTO.NgayXuat, hoadonxuatDTO.TongTien, hoadonxuatDTO.TrangThai = 1), conn);
return(kq);
}
public string LayMaSPMax()
{
string masp;
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
string query = "SELECT max(MaSP) from SanPham";
SqlDataReader read = DataProvider_QLCHTapHoa.TruyVanDuLieu(query, conn);
read.Read();
masp = read[0].ToString();
return(masp);
}
public bool ThemSanPham(SanPhamDTO sanphamdto)
{
string caulenh = "INSERT INTO SanPham (MaSP,TenSP,MaLoaiSP,XuatXu,GiaTien,HinhAnh,SoLuong,MaNCC,TrangThai) VALUES('{0}',N'{1}','{2}',N'{3}','{4}','{5}','{6}','{7}',1)";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(caulenh, sanphamdto.MaSP, sanphamdto.TenSP, sanphamdto.MaLoaiSP, sanphamdto.XuatXu, sanphamdto.GiaTien, sanphamdto.HinhAnh, sanphamdto.SoLuong, sanphamdto.MaNCC), conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool ThemKhachhang(KhachHangDTO khachhangdto)
{
string caulenh = "INSERT INTO KhachHang (MaKH, TenKH, DiaChi, SDT, TrangThai)VALUES('{0}',N'{1}',N'{2}','{3}',1)";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(caulenh, khachhangdto.MaKH, khachhangdto.TenKH, khachhangdto.DiaChi, khachhangdto.SDT, khachhangdto.TrangThai), conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool XoaNhaCungCap(string mancc)
{
string caulenh = "UPDATE NhaCungCap set TrangThai = 0 WHERE MaNCC = '" + mancc + "' ";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(caulenh, mancc), conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool XoaCTHoaDonNhap(string CTHoaDonNhapDTO)
{
string CauLenh = "UPDATE ChiTietHoaDonNhap SET TrangThai = 0 WHERE MaCTHoaDonNhap = '" + CTHoaDonNhapDTO + "' ";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(CauLenh, conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool ThemCTHoaDonXuat(CTHDXuatDTO CTHoaDonXuatDTO)
{
string CauLenh = "INSERT INTO ChiTietHoaDonXuat (MaCTHoaDonXuat, MaSP, GiaBan, SoLuong, ThanhTien, TrangThai) VALUES ('{0}','{1}','{2}','{3}','{4}','1')";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(String.Format(CauLenh, CTHoaDonXuatDTO.MaCTHoaDonXuat, CTHoaDonXuatDTO.MaSP, CTHoaDonXuatDTO.GiaBan, CTHoaDonXuatDTO.SoLuong, CTHoaDonXuatDTO.ThanhTien, CTHoaDonXuatDTO.TrangThai = 1), conn);
return(kq != 0);
}
catch (Exception)
{
return(false);
}
}
public bool XoaHoaDonXuat(string HoaDonXuatDTO)
{
string CauLenh = "UPDATE HoaDonXuat SET TrangThai = 0 WHERE MaHDX = '" + HoaDonXuatDTO + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(CauLenh, conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool CongSL(SanPhamDTO tc1)
{
string CauLenh = "UPDATE SanPham SET SoLuong +=" + tc1.SoLuong + " WHERE MaSP = '" + tc1.MaSP + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(CauLenh, conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool XoaKhachHang(string Makhachhang)
{
string caulenh = "UPDATE KhachHang SET TrangThai=0 WHERE MaKH='" + Makhachhang + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(caulenh, Makhachhang), conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool SuaKhachHang(KhachHangDTO khachhangdto)
{
string caulenh = "UPDATE KhachHang SET TenKH=N'" + khachhangdto.TenKH + "',DiaChi=N'" + khachhangdto.DiaChi + "',SDT='" + khachhangdto.SDT + "'WHERE MaKH='" + khachhangdto.MaKH + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(caulenh, conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool SuaNhaCungCap(NhaCungCapDTO nccdto)
{
string caulenh = "UPDATE NhaCungCap SET TenNCC = N'" + nccdto.TenNCC + "', DiaChi = N'" + nccdto.DiaChi + "', SDT = N'" + nccdto.SDT + "', Email = N'" + nccdto.Email + "' WHERE MaNCC = '" + nccdto.MaNCC + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(caulenh, conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool ThemNhaCungCap(NhaCungCapDTO nccdto)
{
string caulenh = "INSERT INTO NhaCungCap (MaNCC, TenNCC, DiaChi, Email, SDT, TrangThai)VALUES('{0}',N'{1}',N'{2}','{3}','{4}',1)";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(caulenh, nccdto.MaNCC, nccdto.TenNCC, nccdto.DiaChi, nccdto.Email, nccdto.SDT, nccdto.TrangThai), conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool XoaSanPham(string MaSanPham)
{
string caulenh = "UPDATE SanPham set TrangThai = 0 WHERE MaSP = '" + MaSanPham + "' ";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh("UPDATE SanPham set TrangThai = 0 WHERE MaSP = '" + MaSanPham + "' ", conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool SuaNhanVien(NhanVienDTO nhanviendto)
{
string caulenh = "UPDATE NhanVien SET HoNV=N'" + nhanviendto.HoNV + "', TenNV=N'" + nhanviendto.TenNV + "', MaLoaiNV='" + nhanviendto.MaLoaiNV + "', MatKhau='" + nhanviendto.MatKhau + "', GioiTinh=N'" + nhanviendto.GioiTinh + "', NgaySinh='" + nhanviendto.NgaySinh + "', DiaChi = N'" + nhanviendto.DiaChi + "', SDT='" + nhanviendto.SDT + "', Luong='" + nhanviendto.Luong + "',HinhAnh='" + nhanviendto.HinhAnh + "' WHERE MaNV='" + nhanviendto.MaNV + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(caulenh, conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool XoaNhanVien(string MaNV)
{
string caulenh = "UPDATE NhanVien SET TrangThai=0 WHERE MaNV = '" + MaNV + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(caulenh, MaNV), conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public bool ThemNhanVien(NhanVienDTO nhanviendto)
{
string caulenh = "INSERT INTO NhanVien (MaNV, HoNV, TenNV, MaLoaiNV, MatKhau, GioiTinh, NgaySinh, DiaChi, SDT, Luong, HinhAnh, TrangThai) VALUES('{0}',N'{1}',N'{2}',N'{3}','{4}',N'{5}','{6}',N'{7}','{8}','{9}','{10}',1)";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
int kq = DataProvider_QLCHTapHoa.ThucThiCauLenh(string.Format(caulenh, nhanviendto.MaNV, nhanviendto.HoNV, nhanviendto.TenNV, nhanviendto.MaLoaiNV, nhanviendto.MatKhau, nhanviendto.GioiTinh, nhanviendto.NgaySinh, nhanviendto.DiaChi, nhanviendto.SDT, nhanviendto.Luong, nhanviendto.HinhAnh), conn);
return(kq != 0);
}
catch
{
return(false);
}
}
public List <LoaiNhanVienDTO> DanhSachLoaiNhanVien()
{
List <LoaiNhanVienDTO> lstlnv = new List <LoaiNhanVienDTO>();
string query = "SELECT * FROM LoaiNhanVien";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(query, conn);
while (dr.Read())
{
LoaiNhanVienDTO lnvdto = new LoaiNhanVienDTO();
lnvdto.MaLoaiNV = dr[0].ToString();
lnvdto.LoaiNV = dr[1].ToString();
lstlnv.Add(lnvdto);
}
dr.Close();
conn.Close();
return(lstlnv);
}
public static List <LoaiSanPhamDTO> DanhSachLoaiSanPham()
{
List <LoaiSanPhamDTO> lstloaisanpham = new List <LoaiSanPhamDTO>();
string caulenh = "SELECT * FROM LoaiSanPham";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
LoaiSanPhamDTO loai = new LoaiSanPhamDTO();
loai.MaLoaiSP = dr[0].ToString();
loai.TenLoaiSP = dr[1].ToString();
loai.TrangThai = int.Parse(dr[2].ToString());
lstloaisanpham.Add(loai);
}
return(lstloaisanpham);
}
public static List <LoaiNhanVienDTO> DanhSachLoaiNhanVien()
{
List <LoaiNhanVienDTO> lstloainhanvien = new List <LoaiNhanVienDTO>();
string caulenh = "SELECT * FROM LoaiNhanVien";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
LoaiNhanVienDTO loai = new LoaiNhanVienDTO();
loai.MaLoaiNV = dr[0].ToString();
loai.LoaiNV = dr[1].ToString();
loai.TrangThai = int.Parse(dr[2].ToString());
lstloainhanvien.Add(loai);
}
return(lstloainhanvien);
}
public string TangNhanVien()
{
string caulenh = "SELECT MAX(MaNV) FROM NhanVien";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
int nv = Convert.ToInt32(dr.GetString(0).Remove(0, 2));
nv++;
dr.Close();
conn.Close();
return("NV" + nv.ToString("d4"));
}
}
return("NV0001");
}
public string TangNhaCungCap()
{
string caulenh = "SELECT MAX(MaNCC) FROM NhaCungCap";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
int ncc = Convert.ToInt32(dr.GetString(0).Remove(0, 3));
ncc++;
dr.Close();
conn.Close();
return("NCC" + ncc.ToString("d4"));
}
}
return("NCC0001");
}
public KhachHangDTO LayKHTheoMa(string MaKH)
{
KhachHangDTO khdto = new KhachHangDTO();
string caulenh = "SELECT *FROM KhachHang WHERE TrangThai=1 AND MaKH='" + MaKH + "'";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
khdto.MaKH = dr["MaKH"].ToString();
khdto.TenKH = dr["TenKH"].ToString();
khdto.DiaChi = dr["DiaChi"].ToString();
khdto.SDT = dr["SDT"].ToString();
khdto.TrangThai = int.Parse(dr["TrangThai"].ToString());
}
dr.Close();
conn.Close();
return(khdto);
}
