public int SelectMaHoaDonTheoBan(int maban)
{
SqlCommand cm = provider.CreateCommandStoreName("sp_SelectMaHoaDonTheoBan");
cm.Parameters.Add("@maban", SqlDbType.Int);
cm.Parameters["@maban"].Value = maban;
dtHoaDon = provider.ExecSelectCommand(cm);
if (dtHoaDon.Rows.Count <= 0)
{
return(-1);
}
return((int)dtHoaDon.Rows[0].ItemArray[0]);
}
//TAN
public static DataTable DocNhanVienTheoUsername(string username)
{
Provider provider = new Provider();
string str_sql = "select * from NhanVien where Username = '******'";
SqlCommand cm = provider.CreateCommandStringSql(str_sql);
return provider.ExecSelectCommand(cm);
}
public static DataTable DocKhuVuc()
{
Provider provider = new Provider();
string sql = "select * from KhuVuc";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand(cm);
}
// SqlCommand cm;
//đọc danh sách bàn Ăn
public DataTable DocBanTrong(int maNH)
{
DataTable tb = new DataTable();
string sql = " ";
sql += " SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
sql = " select b.MaBan,k.TenKhuVuc,k.ViTri,lb.SucChua,b.TrangThai,nh.TenNH";
sql += " from BanAn b,KhuVuc k,NhaHang n,LoaiBan lb,NhaHang nh";
sql += " where b.MaKhuVuc=k.MaKhuVuc and b.LoaiBan=lb.MaLoai and nh.MaNH=n.MaNH and n.MaNH=" + maNH;
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
public static DataTable LayLoaiNhanVien()
{
Provider provider = new Provider();
string sql = "select * from LoaiNV";
SqlCommand cm = provider.CreateCommandStringSql(sql);
DataTable tb = new DataTable();
return provider.ExecSelectCommand(cm);
}
public static DataTable LayNhaHang()
{
Provider provider = new Provider();
// DataTable tbLoaiMonAn = new DataTable();
string sql = "select * from NhaHang";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand(cm);
}
public static DataTable DocKhachHang()
{
Provider provider = new Provider();
DataTable tb = new DataTable();
string sql = "select cmnd,TenKhachHang from KhachHang";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand(cm);
}
public static DataTable DocLoaiMonAn()
{
Provider provider = new Provider();
DataTable tbLoaiMonAn = new DataTable();
string sql = "select MaLoai,TenLoai from LoaiMonAn";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand(cm);
}
public static DataTable DocKhuVuc()
{
Provider provider = new Provider();
string sql = "select * from KhuVuc";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
//TAN
public static DataTable DocNhanVienTheoUsername(string username)
{
Provider provider = new Provider();
string str_sql = "select * from NhanVien where Username = '******'";
SqlCommand cm = provider.CreateCommandStringSql(str_sql);
return(provider.ExecSelectCommand(cm));
}
public static DataTable LayNhaHang()
{
Provider provider = new Provider();
// DataTable tbLoaiMonAn = new DataTable();
string sql = "select * from NhaHang";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
public static DataTable DocBanAn(string maNH)
{
Provider provider = new Provider();
string sql = "select MaBan,TenKhuVuc,SucChua,TenNH,case(TrangThai) when 0 then N'Chưa đến ăn' else N'Đã đến ăn' end as TrangThai";
sql += " from BanAn ba,KhuVuc kv,LoaiBan lb,NhaHang nh";
sql += " where ba.MaKhuVuc=kv.MaKhuVuc and ba.LoaiBan=lb.Maloai and ba.MaNH=nh.MaNH and nh.MaNH=" + maNH;
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand(cm);
}
public static DataTable DocKhachHang()
{
Provider provider = new Provider();
DataTable tb = new DataTable();
string sql = "select cmnd,TenKhachHang from KhachHang";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
public static DataTable LayLoaiMon()
{
Provider provider = new Provider();
// DataTable tbLoaiMonAn = new DataTable();
string sql = "select MaLoai,TenLoai from LoaiMonAn";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
public static DataTable LayLoaiNhanVien()
{
Provider provider = new Provider();
string sql = "select * from LoaiNV";
SqlCommand cm = provider.CreateCommandStringSql(sql);
DataTable tb = new DataTable();
return(provider.ExecSelectCommand(cm));
}
public DataTable SelectMonAnTheoLoai(String maloai, String manh)
{
SqlCommand cm = provider.CreateCommandStoreName("sp_SelectMonAnTheoLoai");
cm.Parameters.Add("@maloai", SqlDbType.NChar);
cm.Parameters.Add("@manh", SqlDbType.NChar);
cm.Parameters["@maloai"].Value = maloai;
cm.Parameters["@manh"].Value = manh;
return(provider.ExecSelectCommand(cm));
}
public static DataTable DocLoaiNV()
{
Provider provider = new Provider();
string str_sql = "select MaLoai, TenLoai";
str_sql += " from LoaiNV";
SqlCommand cm = provider.CreateCommandStringSql(str_sql);
DataTable tb = new DataTable();
return provider.ExecSelectCommand(cm);
}
public static DataTable DocNhanVien(string maNH)
{
Provider provider = new Provider();
string str_sql = "select MaNV,TenNV,TenNH,TenLoai,nv.sdt";
str_sql += " from NhanVien nv,LoaiNV lnv,NhaHang nh";
str_sql += " where nv.Maloai=lnv.MaLoai and nv.MaNH=nh.MaNH and nh.MaNH=" + maNH;
SqlCommand cm = provider.CreateCommandStringSql(str_sql);
DataTable tb = new DataTable();
return provider.ExecSelectCommand(cm);
}
public static DataTable DocLoaiMonAn_XD()
{
Provider provider = new Provider();
DataTable tbLoaiMonAn = new DataTable();
string sql = " begin tran select MaLoai,TenLoai from LoaiMonAn";
sql += " commit tran";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
public List <ChiTietHoaDon_DTO> SelectCTHDtheoMaHD(int mahd, int mode)
{
String store = "sp_SelectChiTietHD_0";
switch (mode)
{
case 0:
store = "sp_SelectChiTietHD_0";
break;
case 3:
store = "sp_SelectChiTietHD_3"; //unrepeatable read
break;
}
SqlCommand cm = provider.CreateCommandStoreName(store);
cm.Parameters.Add("@mahd", SqlDbType.Int);
cm.Parameters["@mahd"].Value = mahd;
return(ConvertToList(provider.ExecSelectCommand(cm)));
}
public static DataTable DocBanAn(string maNH)
{
Provider provider = new Provider();
string sql = "select MaBan,TenKhuVuc,SucChua,TenNH,case(TrangThai) when 0 then N'Chưa đến ăn' else N'Đã đến ăn' end as TrangThai";
sql += " from BanAn ba,KhuVuc kv,LoaiBan lb,NhaHang nh";
sql += " where ba.MaKhuVuc=kv.MaKhuVuc and ba.LoaiBan=lb.Maloai and ba.MaNH=nh.MaNH and nh.MaNH=" + maNH;
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
public static DataTable DocNhanVien(string maNH)
{
Provider provider = new Provider();
string str_sql = "select MaNV,TenNV,TenNH,TenLoai,nv.sdt";
str_sql += " from NhanVien nv,LoaiNV lnv,NhaHang nh";
str_sql += " where nv.Maloai=lnv.MaLoai and nv.MaNH=nh.MaNH and nh.MaNH=" + maNH;
SqlCommand cm = provider.CreateCommandStringSql(str_sql);
DataTable tb = new DataTable();
return(provider.ExecSelectCommand(cm));
}
public static DataTable DocLoaiNV()
{
Provider provider = new Provider();
string str_sql = "select MaLoai, TenLoai";
str_sql += " from LoaiNV";
SqlCommand cm = provider.CreateCommandStringSql(str_sql);
DataTable tb = new DataTable();
return(provider.ExecSelectCommand(cm));
}
//public static DataTable DocMonAn(string maNH)
//{
// Provider provider=new Provider();
// string str_sql = "select MaMon, TenMon,TenLoai,Gia,DonViTinh,TenNH";
// str_sql+= " from MonAn m,LoaiMonAn lm,NhaHang nh";
// str_sql+= " where m.LoaiMon=lm.MaLoai and m.MaNH=nh.MaNH and nh.MaNH="+maNH;
// SqlCommand cm = provider.CreateCommandStringSql(str_sql);
// DataTable tb = new DataTable();
// return provider.ExecSelectCommand(cm);
//}
public static DataTable DocMonAn(int mode,string maNH)
{
Provider provider = new Provider();
string store = "sp_DocMonAn";
if (mode == 3)
store = "sp_DocMonAn_Fix";
SqlCommand cm = provider.CreateCommandStoreName(store);
cm.Parameters.Add("@MaNH", SqlDbType.VarChar, 5);
cm.Parameters["@MaNH"].Value = maNH;
return provider.ExecSelectCommand(cm);
}
public int SelectUser(string username, string pass)
{
SqlCommand cm = provider.CreateCommandStoreName("sp_SelectUser");
cm.Parameters.Add("@username", SqlDbType.NVarChar);
cm.Parameters.Add("@pass", SqlDbType.NVarChar);
cm.Parameters.Add("@kq", SqlDbType.Int).Direction = ParameterDirection.Output;
cm.Parameters["@username"].Value = username;
cm.Parameters["@pass"].Value = pass;
provider.ExecSelectCommand(cm);
return((int)cm.Parameters["@kq"].Value);
}
public static int KiemTraTenLoai(string tenLoai)
{
Provider provider = new Provider();
string sql = string.Format("select * from LoaiNV where TenLoai like 'N{0}'", tenLoai);
SqlCommand cm = provider.CreateCommandStringSql(sql);
DataTable tb = new DataTable();
tb = provider.ExecSelectCommand(cm);
if (tb.Rows.Count > 0)
{
return 1;
}
return 0;
}
public static int KiemTraTenLoai(string tenLoai)
{
Provider provider = new Provider();
string sql = string.Format("select * from LoaiNV where TenLoai like 'N{0}'", tenLoai);
SqlCommand cm = provider.CreateCommandStringSql(sql);
DataTable tb = new DataTable();
tb = provider.ExecSelectCommand(cm);
if (tb.Rows.Count > 0)
{
return(1);
}
return(0);
}
//public static DataTable DocMonAn(string maNH)
//{
// Provider provider=new Provider();
// string str_sql = "select MaMon, TenMon,TenLoai,Gia,DonViTinh,TenNH";
// str_sql+= " from MonAn m,LoaiMonAn lm,NhaHang nh";
// str_sql+= " where m.LoaiMon=lm.MaLoai and m.MaNH=nh.MaNH and nh.MaNH="+maNH;
// SqlCommand cm = provider.CreateCommandStringSql(str_sql);
// DataTable tb = new DataTable();
// return provider.ExecSelectCommand(cm);
//}
public static DataTable DocMonAn(int mode, string maNH)
{
Provider provider = new Provider();
string store = "sp_DocMonAn";
if (mode == 3)
{
store = "sp_DocMonAn_Fix";
}
SqlCommand cm = provider.CreateCommandStoreName(store);
cm.Parameters.Add("@MaNH", SqlDbType.VarChar, 5);
cm.Parameters["@MaNH"].Value = maNH;
return(provider.ExecSelectCommand(cm));
}
public List <BanAn_DTO> SelectBanAnTheoTrangThai(int trangthai, int manh, int mode)
{
String store = "sp_selectBanAn_0";
switch (mode)
{
case 0:
store = "sp_selectBanAn_0";
break;
case 3:
store = "sp_selectBanAn_3";
break;
}
SqlCommand cm = provider.CreateCommandStoreName(store);
cm.Parameters.Add("@trangthai", SqlDbType.Int);
cm.Parameters.Add("@manh", SqlDbType.Int);
cm.Parameters["@trangthai"].Value = trangthai;
cm.Parameters["@manh"].Value = manh;
return(ConvertToList(provider.ExecSelectCommand(cm)));
}