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 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 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 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 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 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 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 DocKhuVuc()
{
Provider provider = new Provider();
string sql = "select * from KhuVuc";
SqlCommand cm = provider.CreateCommandStringSql(sql);
return provider.ExecSelectCommand(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 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 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 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 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 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 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 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 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 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 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 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 CapNhatMonAn(int mode,MonAn_DTO MonAn, int maMonAn)
{
Provider provider = new Provider();
string store="sp_CapNhatMonAn";
if(mode==3)
store = "sp_CapNhatMonAn_Fix";
//chua xu ly nha hang
// string sql = string.Format("update MonAn set TenMon=N'{0}',Gia='{1}',DonViTinh='{2}' where MaMon='{3}' and MaNH='{4}'", MonAn.tenmon, MonAn.Gia, MonAn.Dvtinh, maMonAn,maNH);
SqlCommand cm = provider.CreateCommandStoreName(store);
cm.Parameters.Add("@TenMon", SqlDbType.NVarChar, 50);
cm.Parameters.Add("@gia", SqlDbType.Money);
cm.Parameters.Add("@DVTinh", SqlDbType.NVarChar, 10);
cm.Parameters.Add("@MaMon", SqlDbType.Int);
cm.Parameters["@TenMon"].Value = MonAn.tenmon;
cm.Parameters["@gia"].Value = MonAn.Gia;
cm.Parameters["@DVTinh"].Value = MonAn.Dvtinh;
cm.Parameters["@MaMon"].Value = maMonAn;
return provider.ExecuteInsertUpdateDelete(cm);
}
public LoaiNhanVien_DAO()
{
provider = new Provider();
}