public static DataTable LayTatCaMauMa()
{
string query = "SELECT * FROM DanhMuc";
dtSanPham = XuLyDuLieu.LayBang(query);
return(dtSanPham);
}
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 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 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 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 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 LayBang()
{
string query = "Select * from PhanLoaiTaiKhoan";
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 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 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 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 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 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));
}