示例#1
0
        public static DataTable LayTatCaMauMa()
        {
            string query = "SELECT * FROM DanhMuc";

            dtSanPham = XuLyDuLieu.LayBang(query);
            return(dtSanPham);
        }
示例#2
0
        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));
        }
示例#4
0
        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));
        }
示例#6
0
        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);
        }
示例#7
0
        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);
            }
        }
示例#8
0
        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));
        }
示例#10
0
        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));
        }
示例#12
0
        public static int XoaNhaCungCap(string MaNhaCungCap)
        {
            string query = string.Format("UPDATE NhaCungCap Set TrangThai = 0 WHERE MaNhaCungCap = '{0}'", MaNhaCungCap);

            return(XuLyDuLieu.ThucThiCauLenh(query));
        }
示例#13
0
        public static bool KiemTraTonTaiTenNCC(string tenNhaCungCap)
        {
            string query = string.Format("select count(TenNhaCungCap) from NhaCungCap where TenNhaCungCap = '{0}'", tenNhaCungCap);

            return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
        }
示例#14
0
        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));
        }
示例#15
0
        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);
        }
示例#17
0
        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);
        }
示例#18
0
        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);
        }
示例#20
0
        public static bool XoaTaiKhoan(string tenTaiKhoan)
        {
            string query = string.Format("UPDATE TaiKhoan SET TrangThai = 0 WHERE TenTaiKhoan ='{0}'", tenTaiKhoan);

            return(XuLyDuLieu.ThucThiCauLenh(query) >= 1);
        }
示例#21
0
        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);
        }
示例#22
0
        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));
        }
示例#23
0
        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));
        }
示例#24
0
        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);
        }
示例#25
0
        public static bool KiemTraTonTaiMaCT(string _maCT)
        {
            string query = string.Format("select count(*) from ChiTietSanPham where MaCTSP = '{0}'", _maCT);

            return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
        }
示例#26
0
        public static bool KiemTraTaiKhoanDaTonTai(string tenTaiKhoan)
        {
            string query = string.Format("select count(*) from TaiKhoan where TenTaiKhoan = '{0}'", tenTaiKhoan);

            return(XuLyDuLieu.ThucThiCauLenhWithScalar(query) >= 1);
        }
示例#27
0
        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));
        }
示例#29
0
        public static DataTable LayBang()
        {
            string query = "Select * from PhanLoaiTaiKhoan";

            return(XuLyDuLieu.LayBang(query));
        }
示例#30
0
        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);
        }