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 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 <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 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 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 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 HoaDonNhapDTO LayHoaDonNhapTheoMa(string maHD)
{
HoaDonNhapDTO hoadonnhap = new HoaDonNhapDTO();
string caulenh = "SELECT MaHDN, nv.TenNV, NgayNhap, TongTien, hdn.TrangThai FROM HoaDonNhap hdn INNER JOIN NhanVien nv ON hdn.MaNV = nv.MaNV Where hdn.TrangThai = 1 AND hdn.MaHDN='" + maHD + "'";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
hoadonnhap.MaHDN = dr[0].ToString();
hoadonnhap.MaNV = dr[1].ToString();
hoadonnhap.NgayNhap = Convert.ToDateTime(dr[2].ToString());
hoadonnhap.TongTien = int.Parse(dr[3].ToString());
hoadonnhap.TrangThai = int.Parse(dr[4].ToString());
}
dr.Close();
conn.Close();
return(hoadonnhap);
}
public string TangHD()
{
string caulenh = "SELECT MAX(MaHDX) FROM HoaDonXuat";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
int hd = Convert.ToInt32(dr.GetString(0).Remove(0, 3));
hd++;
dr.Close();
conn.Close();
return("HDX" + hd.ToString("d4"));
}
}
return("HDX0001");
}
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 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 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);
}
public string TangKhachHang()
{
string caulenh = "SELECT MAX(MaKH) FROM KhachHang";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
if (dr.Read())
{
if (!dr.IsDBNull(0))
{
int kh = Convert.ToInt32(dr.GetString(0).Remove(0, 2));
kh++;
dr.Close();
conn.Close();
return("KH" + kh.ToString("d4"));
}
}
return("KH0001");
}
public HoaDonXuatDTO LayHoaDonTheoMa(string maHD)
{
HoaDonXuatDTO hoadon = new HoaDonXuatDTO();
string caulenh = "SELECT MaHDX, nv.TenNV, kh.TenKH, NgayXuat, TongTien, hdx.TrangThai FROM HoaDonXuat hdx INNER JOIN NhanVien nv ON hdx.MaNV=nv.MaNV INNER JOIN KhachHang kh ON hdx.MaKH=kh.MaKH WHERE hdx.TrangThai=1 AND hdx.MaHDX='" + maHD + "'";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
hoadon.MaHDX = dr[0].ToString();
hoadon.MaNV = dr[1].ToString();
hoadon.MaKH = dr[2].ToString();
hoadon.NgayXuat = Convert.ToDateTime(dr[3].ToString());
hoadon.TongTien = int.Parse(dr[4].ToString());
hoadon.TrangThai = int.Parse(dr[5].ToString());
}
dr.Close();
conn.Close();
return(hoadon);
}
public List <SanPhamDTO> DanhSachSanPhamHetHang()
{
List <SanPhamDTO> lstsanpham = new List <SanPhamDTO>();
string caulenh = "Select MaSP, TenSP, TenLoaiSP, SoLuong, sp.TrangThai FROM SanPham sp INNER JOIN LoaiSanPham lsp ON sp.MaLoaiSP = lsp.MaLoaiSP where sp.TrangThai = 1 AND SoLuong < 20";
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.SoLuong = int.Parse(dr[3].ToString());
sanphamdto.TrangThai = int.Parse(dr[4].ToString());
lstsanpham.Add(sanphamdto);
}
dr.Close();
conn.Close();
return(lstsanpham);
}
public List <HoaDonNhapDTO> DSHoaDonNhap()
{
List <HoaDonNhapDTO> lsthoadonhap = new List <HoaDonNhapDTO>();
string caulenh = "SELECT MaHDN, nv.TenNV, NgayNhap, TongTien, hdn.TrangThai FROM HoaDonNhap hdn INNER JOIN NhanVien nv ON hdn.MaNV = nv.MaNV Where hdn.TrangThai = 1";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
HoaDonNhapDTO hdndto = new HoaDonNhapDTO();
hdndto.MaHDN = dr[0].ToString();
hdndto.MaNV = dr[1].ToString();
hdndto.NgayNhap = Convert.ToDateTime(dr[2].ToString());
hdndto.TongTien = int.Parse(dr[3].ToString());
hdndto.TrangThai = int.Parse(dr[4].ToString());
lsthoadonhap.Add(hdndto);
}
dr.Close();
conn.Close();
return(lsthoadonhap);
}
public List <HoaDonNhapDTO> TimHoaDonNhap(DateTime tungay, DateTime denngay)
{
List <HoaDonNhapDTO> lsthoadonhap = new List <HoaDonNhapDTO>();
string caulenh = "SELECT * FROM HoaDonNhap Where TrangThai = 1 and NgayNhap >= convert (nvarchar,'" + tungay + "',103) and NgayNhap <= convert (nvarchar,'" + denngay + "',103)";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
HoaDonNhapDTO hdndto = new HoaDonNhapDTO();
hdndto.MaHDN = dr[0].ToString();
hdndto.MaNV = dr[1].ToString();
hdndto.NgayNhap = Convert.ToDateTime(dr[2].ToString());
hdndto.TongTien = int.Parse(dr[3].ToString());
hdndto.TrangThai = int.Parse(dr[4].ToString());
lsthoadonhap.Add(hdndto);
}
dr.Close();
conn.Close();
return(lsthoadonhap);
}
public List <CTHDXuatDTO> DanhSachCTHDXuat()
{
List <CTHDXuatDTO> lstcthdx = new List <CTHDXuatDTO>();
string caulenh = "SELECT MaCTHoaDonXuat, sp.TenSP, GiaBan, cthdx.SoLuong, ThanhTien, cthdx.TrangThai FROM ChiTietHoaDonXuat cthdx INNER JOIN SanPham sp ON cthdx.MaSP=sp.MaSP WHERE cthdx.TrangThai=1";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
CTHDXuatDTO chitiet = new CTHDXuatDTO();
chitiet.MaCTHoaDonXuat = dr[0].ToString();
chitiet.MaSP = dr[1].ToString();
chitiet.GiaBan = int.Parse(dr[2].ToString());
chitiet.SoLuong = int.Parse(dr[3].ToString());
chitiet.ThanhTien = int.Parse(dr[4].ToString());
chitiet.TrangThai = int.Parse(dr[5].ToString());
lstcthdx.Add(chitiet);
}
dr.Close();
conn.Close();
return(lstcthdx);
}
public bool TimMaSP(string MaSP)
{
string caulenh = "SELECT * FROM SanPham WHERE MaSP='" + MaSP + "'";
try
{
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
if (dr.Read())
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
public List <SanPhamDTO> DanhSachSanPhamBanChay()
{
List <SanPhamDTO> lstsanpham = new List <SanPhamDTO>();
string caulenh = "SELECT TOP 10 cthd.MaSP, sp.TenSP, sp.GiaTien, sp.XuatXu, ncc.TenNCC , SUM( cthd.SoLuong ) as SoLuongDaBan FROM SanPham sp, NhaCungCap ncc, ChiTietHoaDonXuat cthd, HoaDonXuat hd WHERE hd.MaHDX = cthd.MaCTHoaDonXuat AND sp.MaSP = cthd.MaSP AND sp.MaNCC = ncc.MaNCC and sp.TrangThai = 1 GROUP BY cthd.MaSP, sp.TenSP, ncc.TenNCC, sp.GiaTien, sp.XuatXu ORDER BY SUM( cthd.SoLuong) desc";
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.GiaTien = Convert.ToDouble(dr[2].ToString());
sanphamdto.XuatXu = dr[3].ToString();
sanphamdto.MaNCC = dr[4].ToString();
sanphamdto.TrangThai = int.Parse(dr[5].ToString());
lstsanpham.Add(sanphamdto);
}
dr.Close();
conn.Close();
return(lstsanpham);
}
public List <KhachHangDTO> DanhSachKhachHang()
{
List <KhachHangDTO> lstkh = new List <KhachHangDTO>();
string caulenh = "SELECT *FROM KhachHang WHERE TrangThai=1";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
KhachHangDTO khdto = new KhachHangDTO();
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());
lstkh.Add(khdto);
}
dr.Close();
conn.Close();
return(lstkh);
}
public List <CTHDNhapDTO> DSCTHDNhap(string MaHDN)
{
List <CTHDNhapDTO> lscthdn = new List <CTHDNhapDTO>();
string caulenh = "SELECT MaCTHoaDonNhap, sp.TenSP, GiaNhap, cthdn.SoLuong, ThanhTien, cthdn.TrangThai FROM ChiTietHoaDonNhap cthdn INNER JOIN SanPham sp ON cthdn.MaSP = sp.MaSP WHERE MaCTHoaDonNhap='" + MaHDN + "' AND cthdn.TrangThai=1";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
CTHDNhapDTO chitiet = new CTHDNhapDTO();
chitiet.MaCTHoaDonNhap = dr[0].ToString();
chitiet.MaSP = dr[1].ToString();
chitiet.GiaNhap = int.Parse(dr[2].ToString());
chitiet.SoLuong = int.Parse(dr[3].ToString());
chitiet.ThanhTien = int.Parse(dr[4].ToString());
chitiet.TrangThai = int.Parse(dr[5].ToString());
lscthdn.Add(chitiet);
}
dr.Close();
conn.Close();
return(lscthdn);
}
public List <HoaDonXuatDTO> TimDanhSachHoaDon(DateTime tungay, DateTime denngay)
{
List <HoaDonXuatDTO> lsthoadonxuat = new List <HoaDonXuatDTO>();
string caulenh = "SELECT MaHDX, nv.TenNV, kh.TenKH, NgayXuat, TongTien, hdx.TrangThai FROM HoaDonXuat hdx INNER JOIN NhanVien nv ON hdx.MaNV=nv.MaNV INNER JOIN KhachHang kh ON hdx.MaKH=kh.MaKH WHERE hdx.TrangThai=1 and NgayXuat >= convert (nvarchar,'" + tungay + "',103) and NgayXuat <= convert (nvarchar,'" + denngay + "',103)";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
HoaDonXuatDTO hoadon = new HoaDonXuatDTO();
hoadon.MaHDX = dr[0].ToString();
hoadon.MaNV = dr[1].ToString();
hoadon.MaKH = dr[2].ToString();
hoadon.NgayXuat = DateTime.Parse(dr[3].ToString());
hoadon.TongTien = int.Parse(dr[4].ToString());
hoadon.TrangThai = int.Parse(dr[5].ToString());
lsthoadonxuat.Add(hoadon);
}
dr.Close();
conn.Close();
return(lsthoadonxuat);
}
public static List <NhaCungCapDTO> DanhSachNhaCungCap()
{
List <NhaCungCapDTO> lstncc = new List <NhaCungCapDTO>();
string caulenh = "SELECT * FROM NhaCungCap WHERE TrangThai=1";
SqlConnection conn = DataProvider_QLCHTapHoa.TaoKetNoi();
SqlDataReader dr = DataProvider_QLCHTapHoa.TruyVanDuLieu(caulenh, conn);
while (dr.Read())
{
NhaCungCapDTO ncc = new NhaCungCapDTO();
ncc.MaNCC = dr["MaNCC"].ToString();
ncc.TenNCC = dr["TenNCC"].ToString();
ncc.DiaChi = dr["DiaChi"].ToString();
ncc.SDT = dr["SDT"].ToString();
ncc.Email = dr["Email"].ToString();
ncc.TrangThai = int.Parse(dr["TrangThai"].ToString());
lstncc.Add(ncc);
}
dr.Close();
conn.Close();
return(lstncc);
}
