public static DataTable LayTatCaMauMa()
{
string query = "SELECT * FROM DanhMuc";
dtSanPham = XuLyDuLieu.LayBang(query);
return(dtSanPham);
}
public static SqlDataReader LayThongTinMotSanPham(string idSanPham, string idChiTiet)
{
string query = string.Format("SELECT SanPham.*,DanhMuc.*,ChiTietSanPham.*,HinhAnh.Url,HinhAnh.MaHinhAnh FROM SanPham, DanhMuc, ChiTietSanPham, HinhAnh WHERE SanPham.MaDanhMuc = DanhMuc.MaDanhMuc AND ChiTietSanPham.MaSP = SanPham.MaSP AND ChiTietSanPham.MaHinhAnh = HinhAnh.MaHinhAnh AND SanPham.MaSP='{0}' AND ChiTietSanPham.MaCTSP ='{1}'", idSanPham, idChiTiet);
drSanPham = XuLyDuLieu.LayDuLieu(query);
return(drSanPham);
}
public static DataTable LayBangSanPhamTuHDBan(string tuNgay, string denNgay)
{
string query = string.Format("Select sp.MaDanhMuc as MaDanhMuc,TenDanhMuc,Url, sp.MaSP as MaSP, TenSP, MauSac, ThuongHieu, ChongNuoc, TrongLuong, ChatLieu, GiaVon, GiaBanLe, cthd.SoLuong as SoLuong, cthd.GiamTru as GiamTru, cthd.TongTien as TongTien" +
" from HoaDon as hd, ChiTietHoaDon as cthd, SanPham as sp, ChiTietSanPham as ctsp, HinhAnh as ha, DanhMuc as dm" +
" where hd.NgayKhoiTao >= '{0}' AND hd.NgayKhoiTao <= dateadd(day,1,'{1}') AND hd.MaHD = cthd.MaHD AND cthd.MaCTSP = ctsp.MaCTSP AND ctsp.MaSP = sp.MaSP AND ctsp.MaHinhAnh = ha.MaHinhAnh AND dm.MaDanhMuc = sp.MaDanhMuc ORDER BY hd.NgayKhoiTao DESC", tuNgay, denNgay);
return(XuLyDuLieu.LayBang(query));
}
public static DataTable LayBang(string tuNgay, string denNgay)
{
string query = string.Format("Select MaPhieuNhapKho, HoTen, PhieuNhapKho.NgayKhoiTao as NgayKhoiTao,GhiChu, TenNhaCungCap" +
" from PhieuNhapKho,NhanVien,NhaCungCap" +
" where PhieuNhapKho.MaNV = NhanVien.MaNV AND PhieuNhapKho.MaNhaCungCap = NhaCungCap.MaNhaCungCap AND PhieuNhapKho.NgayKhoiTao >= '{0}' AND PhieuNhapKho.NgayKhoiTao <= dateadd(day,1,'{1}') ORDER BY PhieuNhapKho.NgayKhoiTao DESC", tuNgay, denNgay);
return(XuLyDuLieu.LayBang(query));
}
public static DataTable LayBangNhanVien()
{
string query = "SELECT HoTen,NgaySinh,DiaChi,QueQuan,SoDienThoai,NgayKhoiTao,Url,MoTa " +
"from NhanVien,HinhAnh,TaiKhoan,PhanLoaiTaiKhoan where " +
"NhanVien.MaHinhAnh = HinhAnh.MaHinhAnh AND NhanVien.MaNV = TaiKhoan.MaNV and TaiKhoan.MaPhanLoaiTK = PhanLoaiTaiKhoan.MaPhanLoaiTK ";
return(XuLyDuLieu.LayBang(query));
}
public static SqlDataReader LayChiTiet(string maSP, string mauSac)
{
SqlDataReader drChiTiet;
string query = string.Format("SELECT * FROM ChiTietSanPham WHERE MaSP = '{0}' AND MauSac =N'{1}'", maSP, mauSac);
drChiTiet = XuLyDuLieu.LayDuLieu(query);
return(drChiTiet);
}
public static void CapNhatKhuyenMai()
{
string query = string.Format("Select * from SanPham as sp,KhuyenMai as km where sp.MaKhuyenMai = km.MaKhuyenMai AND sp.MaKhuyenMai != 4 AND km.NgayKetThuc <= '{0}' ", DateTime.Now.ToString("yyyy-MM-dd"));
DataTable sanPhamHetHan = XuLyDuLieu.LayBang(query);
foreach (DataRow row in sanPhamHetHan.Rows)
{
string maSP = row["MaSP"].ToString();
query = string.Format("Update SanPham set MaKhuyenMai = {0} where MaSP = '{1}'", 4, maSP);
XuLyDuLieu.ThucThiCauLenh(query);
}
}
public static bool CapNhatSoLuong(string maCTSP, int soLuong)
{
clsChiTietSP_DTO chiTiet = LayChiTiet(maCTSP);
if (chiTiet != null)
{
int tongSLSP = chiTiet.SoLuong - soLuong;
string query = string.Format("UPDATE ChiTietSanPham SET SoLuong = {0} WHERE MaCTSP = '{1}'", tongSLSP, maCTSP);
return(XuLyDuLieu.ThucThiCauLenh(query) >= 1);
}
else
{
return(false);
}
}
public static DataTable LayBangKhuyenMai(bool apDungHD = true)
{
string query = "";
if (!apDungHD)
{
query = "Select * from KhuyenMai where TrangThai = 1 AND ApDungHD = 0";
}
else
{
query = "Select * from KhuyenMai where TrangThai = 1";
}
return(XuLyDuLieu.LayBang(query));
}
public static DataTable LaySPTheoDK(int i = 0)
{
string query = "";
if (i == 1)
{
query = "SELECT SanPham.*,TenDanhMuc ,ChiTietSanPham.MaCTSP, ChiTietSanPham.MauSac, ChiTietSanPham.TrangThai, ChiTietSanPham.SoLuong, HinhAnh.Url,TenKhuyenMai FROM SanPham, DanhMuc, ChiTietSanPham, HinhAnh,KhuyenMai WHERE SanPham.MaKhuyenMai = KhuyenMai.MaKhuyenMai AND SanPham.MaDanhMuc = DanhMuc.MaDanhMuc AND ChiTietSanPham.MaSP = SanPham.MaSP AND ChiTietSanPham.MaHinhAnh = HinhAnh.MaHinhAnh AND ChiTietSanPham.TrangThai = 1 AND ChiTietSanPham.SoLuong>0 order by SanPham.TenSP ";
}
else if (i == 2)
{
query = "SELECT SanPham.*,TenDanhMuc ,ChiTietSanPham.MaCTSP, ChiTietSanPham.MauSac, ChiTietSanPham.TrangThai, ChiTietSanPham.SoLuong, HinhAnh.Url,TenKhuyenMai FROM SanPham, DanhMuc, ChiTietSanPham, HinhAnh, KhuyenMai WHERE SanPham.MaKhuyenMai = KhuyenMai.MaKhuyenMai AND SanPham.MaDanhMuc = DanhMuc.MaDanhMuc AND ChiTietSanPham.MaSP = SanPham.MaSP AND ChiTietSanPham.MaHinhAnh = HinhAnh.MaHinhAnh AND ChiTietSanPham.TrangThai = 1 AND ChiTietSanPham.SoLuong = 0 order by SanPham.TenSP";
}
else if (i == 0)
{
query = "SELECT SanPham.*,TenDanhMuc ,ChiTietSanPham.MaCTSP, ChiTietSanPham.MauSac, ChiTietSanPham.TrangThai, ChiTietSanPham.SoLuong, HinhAnh.Url,TenKhuyenMai FROM SanPham, DanhMuc, ChiTietSanPham, HinhAnh,KhuyenMai WHERE SanPham.MaKhuyenMai = KhuyenMai.MaKhuyenMai AND SanPham.MaDanhMuc = DanhMuc.MaDanhMuc AND ChiTietSanPham.MaSP = SanPham.MaSP AND ChiTietSanPham.MaHinhAnh = HinhAnh.MaHinhAnh AND ChiTietSanPham.TrangThai = 1 order by SanPham.TenSP";
}
else
{
query = "SELECT SanPham.*,TenDanhMuc ,ChiTietSanPham.MaCTSP, ChiTietSanPham.MauSac, ChiTietSanPham.TrangThai, ChiTietSanPham.SoLuong, HinhAnh.Url,TenKhuyenMai FROM SanPham, DanhMuc, ChiTietSanPham, HinhAnh,KhuyenMai WHERE SanPham.MaKhuyenMai = KhuyenMai.MaKhuyenMai AND SanPham.MaDanhMuc = DanhMuc.MaDanhMuc AND ChiTietSanPham.MaSP = SanPham.MaSP AND ChiTietSanPham.MaHinhAnh = HinhAnh.MaHinhAnh AND ChiTietSanPham.TrangThai = 0 order by SanPham.TenSP";
}
dtSanPham = XuLyDuLieu.LayBang(query);
return(dtSanPham);
}
public static DataTable LayHoaDonTheoMaHD(string maHD)
{
string query = string.Format("SELECT MaHD,MaNV,HoaDon.MaKH as MaKhachHang,TenKH,SDT,HoaDon.NgayKhoiTao as NgayKhoiTao,TenKhuyenMai,GiamTru,GhiChu,ThanhTien FROM HoaDon,KhuyenMai,KhachHang WHERE HoaDon.MaHD = '{0}' AND KhuyenMai.MaKhuyenMai = HoaDon.MaKhuyenMai AND KhachHang.MaKH = HoaDon.MaKH ORDER BY HoaDon.NgayKhoiTao DESC", maHD);
return(XuLyDuLieu.LayBang(query));
}
public static int XoaNhaCungCap(string MaNhaCungCap)
{
string query = string.Format("UPDATE NhaCungCap Set TrangThai = 0 WHERE MaNhaCungCap = '{0}'", MaNhaCungCap);
return(XuLyDuLieu.ThucThiCauLenh(query));
}
public static bool KiemTraTonTaiTenNCC(string tenNhaCungCap)
{
string query = string.Format("select count(TenNhaCungCap) from NhaCungCap where TenNhaCungCap = '{0}'", tenNhaCungCap);
return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
}
public static DataTable LayNhaCC()
{
string query = "SELECT NhaCungCap.*,HinhAnh.Url FROM NhaCungCap,HinhAnh WHERE NhaCungCap.TrangThai = 1 AND NhaCungCap.MaHinhAnh = HinhAnh.MaHinhAnh ";
return(XuLyDuLieu.LayBang(query));
}
public static int XoaSanPham(string idSanPham)
{
string query = string.Format("UPDATE ChiTietSanPham SET TrangThai = 0 WHERE MaCTSP ='{0}'", idSanPham);
return(XuLyDuLieu.ThucThiCauLenh(query));
}
public static bool KiemTraTonTaiTenDanhMuc(string TenDanhMuc)
{
string query = string.Format("SELECT count(TenDanhMuc) FROM DanhMuc WHERE TenDanhMuc = N'{0}'", TenDanhMuc);
return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
}
public static bool KiemTraTonTaiMau(string maSanPham, string mauSac)
{
string query = string.Format("SELECT count(MaSP) FROM ChiTietSanPham WHERE MauSac = N'{0}' AND MaSP = '{1}'", mauSac, maSanPham);
return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
}
public static DataTable LayBang()
{
string query = "Select Url,NhanVien.MaNV as MaNV,HoTen,NgaySinh,QueQuan,DiaChi,NgayKhoiTao,LastLogon,TenTaiKhoan,MatKhau,PhanLoaiTaiKhoan.MoTa as MoTa,TaiKhoan.TrangThai as TrangThai from TaiKhoan, NhanVien, HinhAnh, PhanLoaiTaiKhoan where TaiKhoan.MaNV = NhanVien.MaNV AND NhanVien.MaHinhAnh = HinhAnh.MaHinhAnh AND TaiKhoan.MaPhanLoaiTK = PhanLoaiTaiKhoan.MaPhanLoaiTK AND TaiKhoan.TrangThai = 1";
return(XuLyDuLieu.LayBang(query));
}
public static bool Xoa(int _maKhuyenMai)
{
string query = string.Format("UPDATE KhuyenMai set TrangThai = 0 where MaKhuyenMai = '{0}'", _maKhuyenMai);
return(XuLyDuLieu.ThucThiCauLenh(query) >= 1);
}
public static bool XoaTaiKhoan(string tenTaiKhoan)
{
string query = string.Format("UPDATE TaiKhoan SET TrangThai = 0 WHERE TenTaiKhoan ='{0}'", tenTaiKhoan);
return(XuLyDuLieu.ThucThiCauLenh(query) >= 1);
}
public static bool KiemTraTrungSanPham(string tenSanPham)
{
string query = string.Format("SELECT count(TenSP) FROM SanPham WHERE TenSP = N'{0}'", tenSanPham);
return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
}
public static DataTable LaySanPham()
{
string query = string.Format("SELECT * FROM SanPham,DanhMuc,ChiTietSanPham where SanPham.MaDanhMuc = DanhMuc.MaDanhMuc AND SanPham.MaSP = ChiTietSanPham.MaSP AND ChiTietSanPham.TrangThai = 1");
return(XuLyDuLieu.LayBang(query));
}
public static DataTable LayChiTiet(string maHD)
{
string query = string.Format("select Url,TenSP,ChiTietSanPham.MaCTSP as MaCTSP,MauSac,ThuongHieu,GiaBanLe,TenKhuyenMai,GiamTru,SoNamBH,ChiTietHoaDon.SoLuong as SoLuong,TongTien from SanPham,ChiTietHoaDon,ChiTietSanPham,HinhAnh,KhuyenMai where ChiTietHoaDon.MaHD = '{0}' AND ChiTietHoaDon.MaCTSP = ChiTietSanPham.MaCTSP AND ChiTietSanPham.MaHinhAnh = HinhAnh.MaHinhAnh AND ChiTietSanPham.MaSP = SanPham.MaSP AND ChiTietHoaDon.MaKhuyenMai = KhuyenMai.MaKhuyenMai", maHD);
return(XuLyDuLieu.LayBang(query));
}
public static bool CapNhatDangNhap(string maNV)
{
string query = string.Format("Update TaiKhoan Set LastLogon = '{0}' where MaNV = '{1}'", DateTime.Now, maNV);
return(XuLyDuLieu.ThucThiCauLenh(query) >= 1);
}
public static bool KiemTraTonTaiMaCT(string _maCT)
{
string query = string.Format("select count(*) from ChiTietSanPham where MaCTSP = '{0}'", _maCT);
return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
}
public static bool KiemTraTaiKhoanDaTonTai(string tenTaiKhoan)
{
string query = string.Format("select count(*) from TaiKhoan where TenTaiKhoan = '{0}'", tenTaiKhoan);
return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
}
public static bool CapNhatKhuyenMai(int maCu, int maMoi)
{
string query = string.Format("Update SanPham set MaKhuyenMai = {0} where MaKhuyenMai = {1}", maMoi, maCu);
return(XuLyDuLieu.ThucThiCauLenh(query) >= 1);
}
public static DataTable LayBangDanhMuc()
{
string query = "SELECT DanhMuc.MaDanhMuc,TenDanhMuc,count(TenSP) as TongSP from DanhMuc LEFT JOIN SanPham ON DanhMuc.MaDanhMuc = SanPham.MaDanhMuc WHERE TrangThai = 1 GROUP BY TenDanhMuc,DanhMuc.MaDanhMuc";
return(XuLyDuLieu.LayBang(query));
}
public static DataTable LayBang()
{
string query = "Select * from PhanLoaiTaiKhoan";
return(XuLyDuLieu.LayBang(query));
}
public static bool CapNhatKhuyenMai(string maSP, int maKhuyenMai)
{
string query = string.Format("Update SanPham set MaKhuyenMai = {0} where MaSP = '{1}'", maKhuyenMai, maSP);
return(XuLyDuLieu.ThucThiCauLenh(query) >= 1);
}