public DataTable DocLoaiNhanVien_begin()
{
//Provider provider = new Provider();
string sql = "begin tran";
sql += " select MaLoai,TenLoai from LoaiNV";
provider.cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand_OpenConnection(provider.cm));
}
public static int ThemKhuVuc(KhuVuc_DTO KhuVuc, string maNH)
{
Provider provider = new Provider();
string sql = string.Format("insert into KhuVuc values ({0},'{1}','{2}','{3}')", KhuVuc.MaKhuVuc, KhuVuc.TenKhuVuc, KhuVuc.ViTri, maNH);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static DataTable DocLoaiNhanVien_commit()
{
Provider provider = new Provider();
string sql = "select MaLoai,TenLoai from LoaiNV commit tran";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand_CloseConnection(cm);
}
public static int CapNhatNhanVien(NhanVien_DTO NhanVien, int MaNV)
{
Provider provider = new Provider();
string sql = string.Format("update NhanVien set TenNV=N'{0}',sdt={1} where MaNV={2}", NhanVien.TenNV, NhanVien.Sdt, MaNV);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int XoaKhuVuc(string maKhuVuc)
{
Provider provider = new Provider();
string sql = string.Format("delete KhuVuc where MaKhuVuc={0}", maKhuVuc);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(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 int CapNhatKhuVuc(KhuVuc_DTO KhuVuc)
{
Provider provider = new Provider();
string sql = string.Format("update KhuVuc set TenKhuVuc=N'{0}',ViTri=N'{1}' where MaKhuVuc={2}", KhuVuc.TenKhuVuc, KhuVuc.ViTri, KhuVuc.MaKhuVuc);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int ThemBanAn(BanAn_DTO BanAn, string maNH)
{
Provider provider = new Provider();
string sql = string.Format("insert into BanAn values ({0},'{1}','{2}',{3})", BanAn.MaKhuVuc, BanAn.LoaiBan, maNH, BanAn.TrangThai);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int XoatLoaiMonAn(string ma)
{
Provider provider = new Provider();
string sql = string.Format("delete LoaiMonAn where MaLoai='{0}'", ma);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int XoaBanAn(string maBan)
{
Provider provider = new Provider();
string sql = string.Format("delete BanAn where MaBan={0}", maBan);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int CapNhatLoaiBA(LoaiBanAn_DTO loaiBA)
{
Provider provider = new Provider();
string sql = string.Format("update LoaiBan set SucChua={0} where Maloai='{1}'", loaiBA.SucChua, loaiBA.MaLoai);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int ThemLoaiBanAn(LoaiBanAn_DTO LoaiBA)
{
Provider provider = new Provider();
string sql = string.Format("insert into LoaiBan values ('{0}',{1})", LoaiBA.MaLoai, LoaiBA.SucChua);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int CapNhatBanAn(BanAn_DTO BanAn)
{
Provider provider = new Provider();
string sql = string.Format("update BanAn set TrangThai={0} where MaBan={1}", BanAn.TrangThai, BanAn.MaBan);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int CapNhatLoaiNhanVien(LoaiNhanVien_DTO loaiNV)
{
Provider provider = new Provider();
string sql = string.Format("update LoaiNV set TenLoai=N'{0}' where MaLoai='{1}'", loaiNV.TenLoaiNV, loaiNV.MaLoaiNV);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int ThemNhanVien(NhanVien_DTO NhanVien, string maNH)
{
Provider provider = new Provider();
string sql = string.Format("insert into NhanVien values (N'{0}','{1}','{2}',{3},{4}) ", NhanVien.TenNV, maNH, NhanVien.MaLoaiNhanVien, NhanVien.Cmnd, NhanVien.Sdt);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static DataTable DocKhuVuc()
{
Provider provider = new Provider();
string sql = "select * from KhuVuc";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand(cm);
}
public static int XoaNhanVien(int MaNV)
{
Provider provider = new Provider();
string sql = string.Format("delete NhanVien where MaNV={0}", MaNV);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(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 int CapNhatKhuVuc(KhuVuc_DTO KhuVuc)
{
Provider provider = new Provider();
string sql = string.Format("update KhuVuc set TenKhuVuc=N'{0}',ViTri=N'{1}' where MaKhuVuc={2}", KhuVuc.TenKhuVuc, KhuVuc.ViTri, KhuVuc.MaKhuVuc);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static DataTable DocKhuVuc()
{
Provider provider = new Provider();
string sql = "select * from KhuVuc";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand(cm));
}
public static int CapNhatLoaiBA(LoaiBanAn_DTO loaiBA)
{
Provider provider = new Provider();
string sql = string.Format("update LoaiBan set SucChua={0} where Maloai='{1}'", loaiBA.SucChua, loaiBA.MaLoai);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int XoaLoaiNhanVien(string maLoai)
{
Provider provider = new Provider();
string sql = string.Format("delete LoaiNV where MaLoai='{0}'", maLoai);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int XoaNhanVien(int MaNV)
{
Provider provider = new Provider();
string sql = string.Format("delete NhanVien where MaNV={0}", MaNV);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static DataTable DocLoaiNhanVien_commit()
{
Provider provider = new Provider();
string sql = "select MaLoai,TenLoai from LoaiNV commit tran";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecSelectCommand_CloseConnection(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 int ThemLoaiNV(LoaiNhanVien_DTO loaiNV)
{
Provider provider = new Provider();
string sql = string.Format("insert into LoaiNV(MaLoai,TenLoai,Luong) values ('{0}','{1}',{2})", loaiNV.MaLoaiNV, loaiNV.TenLoaiNV, loaiNV.Luong);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int ThemNhanVien(NhanVien_DTO NhanVien, string maNH)
{
Provider provider = new Provider();
string sql = string.Format("insert into NhanVien values (N'{0}','{1}','{2}',{3},{4}) ", NhanVien.TenNV, maNH, NhanVien.MaLoaiNhanVien, NhanVien.Cmnd, NhanVien.Sdt);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int ThemLoaiBanAn(LoaiBanAn_DTO LoaiBA)
{
Provider provider = new Provider();
string sql = string.Format("insert into LoaiBan values ('{0}',{1})", LoaiBA.MaLoai, LoaiBA.SucChua);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int CapNhatNhanVien(NhanVien_DTO NhanVien, int MaNV)
{
Provider provider = new Provider();
string sql = string.Format("update NhanVien set TenNV=N'{0}',sdt={1} where MaNV={2}", NhanVien.TenNV, NhanVien.Sdt, MaNV);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int ThemKhuVuc(KhuVuc_DTO KhuVuc, string maNH)
{
Provider provider = new Provider();
string sql = string.Format("insert into KhuVuc values ({0},'{1}','{2}','{3}')", KhuVuc.MaKhuVuc, KhuVuc.TenKhuVuc, KhuVuc.ViTri, maNH);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int CapNhatLoaiNhanVien(LoaiNhanVien_DTO loaiNV)
{
Provider provider = new Provider();
string sql = string.Format("update LoaiNV set TenLoai=N'{0}' where MaLoai='{1}'", loaiNV.TenLoaiNV, loaiNV.MaLoaiNV);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(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 int XoaBanAn(string maBan)
{
Provider provider = new Provider();
string sql = string.Format("delete BanAn where MaBan={0}", maBan);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(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 int ThemLoaiMonAn_XungDot(LoaiMonAn_DTO loaiMonAn)
{
Provider provider = new Provider();
string sql = string.Format("begin tran insert into LoaiMonAn values('{0}',N'{1}') ", loaiMonAn.Maloai, loaiMonAn.Tenloai);
sql += " waitfor delay '0:0:20' Rollback Commit tran";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}
public static int ThemBanAn(BanAn_DTO BanAn, string maNH)
{
Provider provider = new Provider();
string sql = string.Format("insert into BanAn values ({0},'{1}','{2}',{3})", BanAn.MaKhuVuc, BanAn.LoaiBan, maNH, BanAn.TrangThai);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(cm));
}
public static int CapNhatBanAn(BanAn_DTO BanAn)
{
Provider provider = new Provider();
string sql = string.Format("update BanAn set TrangThai={0} where MaBan={1}", BanAn.TrangThai, BanAn.MaBan);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(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 int CapNhatMonAn_Commit(MonAn_DTO MonAn, int maMonAn)
{
Provider provider = new Provider();
string sql = string.Format("update MonAn set TenMon=N'{0}',Gia='{1}',DonViTinh='{2}' where MaMon='{3}'", MonAn.tenmon, MonAn.Gia, MonAn.Dvtinh, maMonAn);
sql += " commit tran";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(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 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 int ThemLoaiMonAn(LoaiMonAn_DTO loaiMonAn)
{
Provider provider = new Provider();
string sql = string.Format("insert into LoaiMonAn values('{0}',N'{1}') ", loaiMonAn.Maloai, loaiMonAn.Tenloai);
// sql += " waitfor delay '0:0:20' Rollback Commit tran";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(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 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 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 int CapNhatMonAn_Commit(MonAn_DTO MonAn, int maMonAn)
{
Provider provider = new Provider();
string sql = string.Format("update MonAn set TenMon=N'{0}',Gia='{1}',DonViTinh='{2}' where MaMon='{3}'", MonAn.tenmon, MonAn.Gia, MonAn.Dvtinh, maMonAn);
sql += " commit tran";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return(provider.ExecuteInsertUpdateDelete(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 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 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 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 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);
}
// 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 int ThemLoaiNV(LoaiNhanVien_DTO loaiNV)
{
Provider provider = new Provider();
string sql = string.Format("insert into LoaiNV(MaLoai,TenLoai,Luong) values ('{0}','{1}',{2})", loaiNV.MaLoaiNV, loaiNV.TenLoaiNV, loaiNV.Luong);
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecuteInsertUpdateDelete(cm);
}