public static DataTable LayDSPhanCong()
{
string sql = "select Ca as 'Ca', HoTen as 'Tên Nhân Viên', MaSoBan as 'Mã Số Bàn' from PhanCong, NhanVien where PhanCong.MaNV = NhanVien.MaNV";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable ThongKeHDTheoNgay(DateTime ngay)
{
string sql = "set dateformat DMY select SoHD as 'Số HĐ', ThoiGianLap as 'Thời Gian Lập', MaSoBan as 'Mã Bàn', SoKhach as 'Số Khách', nv1.HoTen as 'Người Lập', nv2.HoTen as 'Tiếp Tân', TongTien as 'Tổng Tiền' from HoaDon, NhanVien nv1, NhanVien nv2 where HoaDon.MaNVLap = nv1.MaNV and HoaDon.MaNVTT = nv2.MaNV and convert(varchar(10), ThoiGianLap,103) = convert(varchar(10), convert(datetime, '" + ngay + "'), 103)";
DataTable kq = SqlDataAccessHelper.ExecuteQuery(sql);
return(kq);
}
//Rút trích dữ liêu: select
public static DataTable LayDSCTHDTuMaHD(int maHD)
{
string sql = "select TenThucDon as 'Tên TĐ', DonGia as 'Đơn Giá', SoLuong as 'Số Lượng' from ChiTietHD ct, ThucDon td where ct.MaThucDon = td.MaThucDon and ct.SoHD = " + maHD;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable LayDanhSachTDTheoMaLoai(int maLoai)
{
string sql = "select td.MaThucDon as 'Mã TĐ', td.TenThucDon as 'Tên Thực Đơn', g.Gia as 'Đơn Giá', g.NgayADGia as 'Ngày AD', td.DonViTinh as 'Đơn Vị Tính', l.TenLoai as 'Loại TĐ' from ThucDon td, Gia g, LoaiThucDon l where td.MaLoai = l.MaLoai and td.MaThucDon = g.MaThucDon and td.MaLoai =" + maLoai;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable LayDSNhanVienTiepTan()
{
string sql = "select MaNV as 'Mã NV', HoTen as 'Họ Tên', NgaySinh as 'Ngày Sinh', TenDN as 'Tên ĐN', Quyen as 'Quyền' from NhanVien where Quyen = N'Tiếp Tân'";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable ThongKeHDTheoThang(int thang, int nam)
{
string sql = string.Format("select hd.SoHD as 'Số HĐ', hd.ThoiGianLap as 'Thời Gian Lập', hd.MaSoBan as 'Mã Bàn', hd.SoKhach as 'Số Khách', nv1.HoTen as 'Người Lập', nv2.HoTen as 'Tiếp Tân', hd.TongTien as 'Tổng Tiền' from HoaDon hd, NhanVien nv1, NhanVien nv2 where hd.MaNVLap = nv1.MaNV and hd.MaNVTT = nv2.MaNV and convert(nvarchar(10), ThoiGianLap, 103) like '%{0}/{1}'", thang, nam);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable LayDSNhanVienCoMK()
{
string sql = "select * from NhanVien";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable TraCuuNhanVienTheoTen(string tenNV)
{
string sql = string.Format("select MaNV as 'Mã NV', HoTen as 'Họ Tên', NgaySinh as 'Ngày Sinh', TenDN as 'Tên ĐN', Quyen as 'Quyền' from NhanVien where HoTen like N'%{0}%'", tenNV);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
//Rút trích dữ liêu: select
public static DataTable LayDSHoaDon()
{
string sql = "select hd.SoHD as 'Số HĐ', hd.ThoiGianLap as 'TG Lập', hd.MaSoBan as 'MS Bàn', hd.SoKhach as 'Số Khách', nv1.HoTen as 'Người Lập', nv2.HoTen as 'Tiếp Tân', hd.TongTien as 'Tổng Tiền' from HoaDon hd, NhanVien nv1, NhanVien nv2 where nv1.MaNV = hd.MaNVLap and nv2.MaNV = hd.MaNVTT";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable ThongKeHDTheoKhoangNgay(DateTime tuNgay, DateTime denNgay)
{
string sql = string.Format("set dateformat DMY select SoHD as 'Số HĐ', ThoiGianLap as 'Thời Gian Lập', MaSoBan as 'Mã Bàn', SoKhach as 'Số Khách', nv1.HoTen as 'Người Lập', nv2.HoTen as 'Tiếp Tân', TongTien as 'Tổng Tiền' from HoaDon, NhanVien nv1, NhanVien nv2 where HoaDon.MaNVLap = nv1.MaNV and HoaDon.MaNVTT = nv2.MaNV and convert(varchar(10), ThoiGianLap,103) >= convert(varchar(10),convert(datetime,'{0}'), 103) and convert(varchar(10), ThoiGianLap,103) <= convert(varchar(10),convert(datetime,'{1}'), 103)", tuNgay, denNgay);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static DataTable TraCuuThucDonTheoTen(string tenTD)
{
string sql = string.Format("select td.MaThucDon as 'Mã TĐ', td.TenThucDon as 'Tên Thực Đơn', g.Gia as 'Đơn Giá', g.NgayADGia as 'Ngày AD', td.DonViTinh as 'Đơn Vị Tính', l.TenLoai as 'Loại TĐ' from ThucDon td, Gia g, LoaiThucDon l where td.TenThucDon like N'%{0}%' and td.MaThucDon = g.MaThucDon and td.MaLoai = l.MaLoai", tenTD);
DataTable kq = SqlDataAccessHelper.ExecuteQuery(sql);
return(kq);
}
public static DataTable LayDSCTHD(int SoHD)
{
string sql = string.Format("select * from ChiTietHD where SoHD = {0}", SoHD);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
return(dt);
}
public static int LayGioLapHDChuaThanhToanTheoMaBan(int maBan)
{
string sql = string.Format("select convert(varchar(2), ThoiGianLap, 108)as 'GioLap' from HoaDon where MaSoban = {0} and TongTien = 0", maBan);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
int gio = int.Parse(dt.Rows[0]["GioLap"].ToString());
return(gio);
}
public static string LayQuyenNVTheoMaNV(int maNV)
{
string sql = "select Quyen from NhanVien where MaNV = " + maNV;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
string quyen = dt.Rows[0]["Quyen"].ToString();
return(quyen);
}
public static string LayTenNVTheoMaNV(int maNV)
{
string sql = string.Format("select HoTen from NhanVien where MaNV = {0}", maNV);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
string tenNV = dt.Rows[0]["HoTen"].ToString();
return(tenNV);
}
public static string LayMatKhauTuTenDN(string TenDN)
{
string sql = "select * from NhanVien where TenDN = N'" + TenDN + "'";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
string MK = dt.Rows[0][4].ToString();
return(MK);
}
public static int LayMaNVTheoMaBanVaCa(int maBan, int ca)
{
string sql = string.Format("select MaNV from PhanCong where MaSoBan = {0} and Ca = {1}", maBan, ca);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
int maNV = int.Parse(dt.Rows[0]["MaNV"].ToString());
return(maNV);
}
public static string LayDonViTinhTheoMaTD(int maTD)
{
string dvt;
string sql = "select DonViTinh from ThucDon where MaThucDon = " + maTD;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
dvt = dt.Rows[0][0].ToString();
return(dvt);
}
public static int LaySoKhachTuSoHD(int soHD)
{
int soKhach = 0;
string sql = "select * from HoaDon where SoHD = " + soHD;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
soKhach = int.Parse(dt.Rows[0]["SoKhach"].ToString());
}
return(soKhach);
}
public static int LaySoHoaDonTuMaBan(int maBan)
{
int soHD = 0;
string sql = "select * from HoaDon where MaSoBan = " + maBan + " and TongTien = 0";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
soHD = int.Parse(dt.Rows[0]["SoHD"].ToString());
}
return(soHD);
}
public static int LayMaGoiMonTuMaBan(int maBan)
{
int maGM = 0;
string sql = "select * from GoiMon where MaSoBan = " + maBan;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
maGM = int.Parse(dt.Rows[0]["MaGoiMon"].ToString());
}
return(maGM);
}
public static List <int> LayDSBanChuaThanhToan()
{
List <int> _ds = new List <int>();
string sql = "select * from HoaDon where TongTien = 0";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
int maBan = int.Parse(dt.Rows[i]["MaSoBan"].ToString());
_ds.Add(maBan);
}
return(_ds);
}
//Rút trích dữ liêu: select
public static List <GoiMonDTO> LayDSGoiMon()
{
List <GoiMonDTO> _ds = new List <GoiMonDTO>();
string sql = "select * from GoiMon";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
GoiMonDTO gm = new GoiMonDTO();
gm.MaGoiMon = int.Parse(dt.Rows[i]["MaGoiMon"].ToString());
gm.MsBan = int.Parse(dt.Rows[i]["MaSoBan"].ToString());
_ds.Add(gm);
}
return(_ds);
}
public static List <BanDTO> LayDSBan()
{
List <BanDTO> _ds = new List <BanDTO>();
string sql = "select * from BanAn";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
BanDTO b = new BanDTO();
b.MaBan = int.Parse(dt.Rows[i]["MaSoBan"].ToString());
b.SoGhe = int.Parse(dt.Rows[i]["SoGhe"].ToString());
_ds.Add(b);
}
return(_ds);
}
public static List <ThucDonDTO> LayDSMaTDVaTenTDTheoMaLoai(int maLoai)
{
List <ThucDonDTO> ds = new List <ThucDonDTO>();
string sql = "select * from ThucDon where MaLoai = " + maLoai;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
ThucDonDTO td = new ThucDonDTO();
td.MaTD = int.Parse(dt.Rows[i]["MaThucDon"].ToString());
td.TenTD = dt.Rows[i]["TenThucDon"].ToString();
ds.Add(td);
}
return(ds);
}
//rút trích dữ liệu: select
public static List <LoaiThucDonDTO> LayDSLoaiThucDon()
{
List <LoaiThucDonDTO> _ds = new List <LoaiThucDonDTO>();
string sql = "select * from LoaiThucDon";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
LoaiThucDonDTO loai = new LoaiThucDonDTO();
loai.MaLoai = int.Parse(dt.Rows[i]["MaLoai"].ToString());
loai.TenLoai = dt.Rows[i]["TenLoai"].ToString();
_ds.Add(loai);
}
return(_ds);
}
public static int MaTuTang()
{
string sql = "select * from HoaDon";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
int maTuTang = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (int.Parse(dt.Rows[i][0].ToString()) != maTuTang)
{
return(maTuTang);
}
maTuTang++;
}
return(maTuTang);
}
public static string LayTenThucDonTuMaThucDon(int maTD)
{
string tenTD;
string sql = "select TenThucDon from ThucDon where MaThucDon = " + maTD;
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
tenTD = dt.Rows[0]["TenThucDon"].ToString();
}
else
{
return("");
}
return(tenTD);
}
public static bool KiemTraTrungTenThucDon(string tenTD)
{
bool kq;
string sql = "select * from ThucDon where TenThucDon = N'" + tenTD + "'";
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
kq = false;
}
else
{
kq = true;
}
return(kq);
}
public static bool KiemTraTenTDCapNhat(string tenTD, int maTD)
{
bool kq;
string sql = string.Format("select * from ThucDon where TenThucDon = N'{0}' and MaThucDon = {1}", tenTD, maTD);
DataTable dt = SqlDataAccessHelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
kq = false;
}
else
{
kq = true;
}
return(kq);
}