//单个删除
public int DeleteDisheById(int disheId)
{
using (HotelDBEntities db = new HotelDBEntities())
{
Dishes obj = new Dishes()
{
DishesId = disheId
};
db.Entry <Dishes>(obj).State = EntityState.Deleted;
return(db.SaveChanges());
}
}
//根据菜品Id获取菜品信息
public Dishes GetDishesById(int dishesId)
{
using (HotelDBEntities db = new HotelDBEntities())
{
var obj = (from d in db.Dishes where d.DishesId == dishesId select d).FirstOrDefault();
Dishes dishes = new Dishes()
{
CategoryId = obj.CategoryId, DishesId = obj.DishesId, DishesName = obj.DishesName, UnitPrice = obj.UnitPrice
};
return(dishes);
}
}
///<summary>
///删除招聘信息
///<summary>
public int DeleteRecruitment(int postid)
{
Recruitment recruitment = new Recruitment();
recruitment.PostId = postid;
using (HotelDBEntities hdb = new HotelDBEntities())
{
hdb.Recruitment.Attach(recruitment);
hdb.Recruitment.Remove(recruitment);
return(hdb.SaveChanges());
}
}
//修改新闻
public int ModifyNews(News news)
{
using (HotelDBEntities db = new HotelDBEntities())
{
News obj = new News();
obj.NewsId = news.NewsId;
db.News.Attach(obj);
obj.CategoryId = news.CategoryId;
obj.NewsTitle = news.NewsTitle;
obj.NewsContents = news.NewsContents;
return(db.SaveChanges());
}
}
//修改状态
public int UpdateStatusId(Suggestion sug)
{
using (HotelDBEntities db = new HotelDBEntities())
{
Suggestion s = new Suggestion()
{
SuggestionId = sug.SuggestionId
};
db.Suggestion.Attach(s);
s.StatusId = sug.StatusId;
return(db.SaveChanges());
}
}
/// <summary>
/// 获取菜品， 可获取全部和根据查询条件获取
/// </summary>
/// <returns></returns>
public List <Dishes> GetAllDishes(int categoryId = 0)
{
using (HotelDBEntities db = new HotelDBEntities())
{
if (categoryId == 0)
{
return((from d in db.Dishes select d).ToList());
}
else
{
return((from d in db.Dishes where d.CategoryId == categoryId select d).ToList());
}
}
}
/// <summary>
/// 根据预定ID修改订单状态（审核通过、取消、关单-1）
/// </summary>
/// <param name="bookId"></param>
/// <param name="orderStatus"></param>
/// <returns></returns>
public int ModifyBook(int bookId, string orderStatus)
{
//方式1：封装一个实体，所有字段都写上（最麻烦）
//方式2：查询到要修改的实体，然后修改对应的值（有一个查询的过程）
//方法3：创建一个对象-->给主键赋值-->附加到上下文-->给要修改的字段赋值-->提交保存
using (HotelDBEntities db = new HotelDBEntities())
{
DishesBook dishesBook = new DishesBook();
dishesBook.BookId = bookId;
db.DishesBook.Attach(dishesBook);
//请在下面写出要修改的字段，并赋值
dishesBook.OrderStatus = Convert.ToInt32(orderStatus);
return(db.SaveChanges());
}
}
//批量删除招聘信息
public int DeleteRecruitment(int[] postId)
{
using (HotelDBEntities db = new HotelDBEntities())
{
for (int i = 0; i < postId.Length; i++)
{
Recruitment rec = new Recruitment()
{
PostId = postId[i]
};
db.Entry <Recruitment>(rec).State = EntityState.Deleted;
}
return(db.SaveChanges());
}
}
//批量删除新闻
public int DeleteNews(int[] newId)
{
using (HotelDBEntities db = new HotelDBEntities())
{
for (int i = 0; i < newId.Length; i++)
{
News news = new News()
{
NewsId = newId [i]
};
db.Entry <News>(news).State = EntityState.Deleted;
}
return(db.SaveChanges());
}
}
/// <summary>
/// 根据预定ID修改订单状态(审核通过,撤销,关闭-1)
/// </summary>
/// <param name="bookId"></param>
/// <param name="orderStatus"></param>
/// <returns></returns>
public int ModifyBook(int bookId, string orderStatus)
{
//方式1:封装一个实体类,所有字段都写上(最麻烦)
//方式2:查询到要修改的实体,然后修改对应的值(有一个查询的过程)
//方法3:创建一个对象-->给主键赋值-->附加到上下文-->给要修改的字段赋值-->提交保存
using (HotelDBEntities db = new HotelDBEntities())
{
DishesBook dishesBook = new DishesBook();
dishesBook.BookId = bookId;
db.DishesBook.Attach(dishesBook);
//下面写出要修改的字段,并赋值
dishesBook.OrderStatus = Convert.ToInt32(orderStatus);
return(db.SaveChanges());
}
}
//批量删除菜品
public int DeleteDishes(int[] num)
{
using (HotelDBEntities db = new HotelDBEntities())
{
for (int i = 0; i < num.Length; i++)
{
Dishes obj = new Dishes()
{
DishesId = i
};
db.Entry <Dishes>(obj).State = EntityState.Deleted;
}
return(db.SaveChanges());
}
}
//修改菜品
public int ModifyDishes(Dishes dishes)
{
using (HotelDBEntities db = new HotelDBEntities())
{
Dishes objDish = new Dishes()
{
DishesId = dishes.DishesId
};
db.Dishes.Attach(objDish);
objDish.CategoryId = dishes.CategoryId;
objDish.DishesName = dishes.DishesName;
objDish.UnitPrice = dishes.UnitPrice;
return(db.SaveChanges());
}
}
/// <summary>
/// 获取菜品列表（根据分类编号）
/// </summary>
/// <param name="categoryId">有默认值的参数，调用的时候可以不写</param>
/// <returns></returns>
public List <Dishes> GetAllDishes(int categoryId = 0)
{
#region 前面的写法
//using (HotelDBEntities db = new HotelDBEntities())
//{
// if (categoryId == 0)
// return (from d in db.Dishes select d).ToList();
// else
// return (from d in db.Dishes where d.CategoryId == categoryId select d).ToList();
//}
#endregion
//优化后，通过关联对象查询的写法
using (HotelDBEntities db = new HotelDBEntities())
{
//需要查询关联对象
var dishesList = from d in db.Dishes
select new
{
d.DishesId,
d.DishesName,
d.UnitPrice,
d.CategoryId,
d.DishesCategory.CategoryName
};
if (categoryId != 0)
{
dishesList = from d in dishesList where d.CategoryId == categoryId select d;
}
//转换成具体对象
List <Dishes> list = new List <Dishes>();
foreach (var item in dishesList)
{
list.Add(new Dishes
{
DishesId = item.DishesId,
DishesName = item.DishesName,
UnitPrice = item.UnitPrice,
CategoryId = item.CategoryId,
DishesCategory = new DishesCategory {
CategoryName = item.CategoryName
}
});
}
return(list);
}
}
//获取所有菜品信息
public List <dynamic> GetCountByCategoryName()
{
using (HotelDBEntities db = new HotelDBEntities())
{
var list = from d in db.Dishes group d by d.DishesCategory.CategoryName into a select new { CategoryName = a.Key, Count = a.Count() };
List <dynamic> dishesList = new List <dynamic>();
foreach (var item in list)
{
dishesList.Add(new
{
Count = item.Count,
CategoryName = item.CategoryName
});
}
return(dishesList);
}
}
/// <summary>
/// 发布菜品
/// </summary>
/// <param name="dishes"></param>
/// <returns></returns>
public int AddDishes(Dishes dishes)
{
//编写后台的时候，使用的方法
//using (HotelDBEntities db = new HotelDBEntities())
//{
// db.Dishes.Add(dishes);
// return db.SaveChanges();
//}
//优化后：能返回标识列的值
using (HotelDBEntities db = new HotelDBEntities())
{
db.Dishes.Add(dishes);
db.SaveChanges();
return(dishes.DishesId);//返回标识列的值
}
}
/// <summary>
/// 修改预订状态
/// 0 待审核状态 1 审核通过 -1订单关闭状态
/// </summary>
/// <param name="bookId"></param>
/// <param name="status"></param>
/// <returns></returns>
public int ModifyBook(int bookId, string status)
{
/*
* 1.封装一个实体，所有属性都写上
* 2.查询到实体，修改某些属性
* 3.创建一个对象--》给主键赋值--》附加到上下文对象--》给修改的字段赋值--》提交保存
*
*/
using (HotelDBEntities db = new HotelDBEntities())
{
DishesBook dishesBook = new DishesBook();
dishesBook.BookId = bookId;
db.DishesBook.Attach(dishesBook);
dishesBook.OrderStatus = Convert.ToInt32(status);
return(db.SaveChanges());
}
}
//按照条件查询OrderStatus为0和-1的全部订单
public TableModel <DishesBook> GetAllDishesBook(int page, int limit, string hotelName, string customerName)
{
using (HotelDBEntities db = new HotelDBEntities())
{
var list = from d in db.DishesBook where d.OrderStatus == 0 || d.OrderStatus == -1 select d;
if (hotelName != null && hotelName != "")
{
list = from d in list where d.HotelName == hotelName select d;
}
if (customerName != null && customerName != "")
{
list = from d in list where d.CustomerName.Contains(customerName) select d;
}
TableModel <DishesBook> table = new TableModel <DishesBook>();
table.count = list.Count();
table.data = list.OrderByDescending(s => s.OrderStatus).Skip((page - 1) * limit).Take(limit).ToList <DishesBook>();
return(table);
}
}
//按照id查询新闻详细信息
public News SelectNewsById(int newId)
{
using (HotelDBEntities db = new HotelDBEntities())
{
var list = (from n in db.News where n.NewsId == newId select new { n.NewsId, n.NewsTitle, n.PublishTime, n.CategoryId, n.NewsContents, n.NewsCategory.CategoryName }).FirstOrDefault();
News objnews = new News()
{
NewsId = list.NewsId,
NewsTitle = list.NewsTitle,
PublishTime = list.PublishTime,
CategoryId = list.CategoryId,
NewsCategory = new NewsCategory()
{
CategoryName = list.CategoryName
},
NewsContents = list.NewsContents
};
return(objnews);
}
}
//基于事务将Excel中的数据导入到数据库中
public int InputDataToDB(string filePath)
{
DataSet ds = new NPOIGetDataFromExcel().GetDataFromExcel(filePath);
DataTable dt = ds.Tables[0];
using (HotelDBEntities db = new HotelDBEntities())
{
using (var cusTransaction = new TransactionScope())
{
try
{
foreach (DataRow row in dt.Rows)
{
db.Recruitment.Add(new Recruitment()
{
PostName = row["职位"].ToString(),
PostType = row["类别"].ToString(),
PostPlace = row["工作地点"].ToString(),
PostDesc = row["职位描述"].ToString(),
PostRequire = row["职位要求"].ToString(),
Experience = row["经验"].ToString(),
EduBackground = row["学历"].ToString(),
RequireCount = Convert.ToInt32(row["招聘人数"]),
Email = row["邮箱"].ToString(),
Manager = row["联系人"].ToString(),
PhoneNumber = row["电话"].ToString(),
PublishTime = Convert.ToDateTime(row["发布时间"])
});
}
int res = db.SaveChanges();
cusTransaction.Complete();
return(res);
}
catch (Exception ex)
{
Transaction.Current.Rollback();
throw ex;
}
}
}
}
/// <summary>
/// 受理投诉
/// </summary>
/// <param name="suggestionId"></param>
/// <returns></returns>
public int HandleSuggestion(int suggestionId)
{
//下面的方法，执行后，会出现错误：其他信息: 对一个或多个实体的验证失败。有关详细信息，请参阅“EntityValidationErrors”属性。
//using (HotelDBEntities db = new HotelDBEntities())
//{
// Suggestion suggestion = new Suggestion { SuggestionId = suggestionId };
// db.Suggestion.Attach(suggestion);
// suggestion.StatusId =1;//处理投诉就是将投诉的这条数据状态改成1，这部分也可以在业务逻辑里面写
// return db.SaveChanges();
//}
//错误原因：因为当时我们测试后台的时候，没有给Suggestion实体类添加MVC验证，而当我们添加后，如果更新实体必须给这些字段赋值
//也就是不允许直接更新部分字段（请学员可以先测试上面的方法，然后对比下面的解决办法）
//解决方法：修改前，先查询对象，保证其他字段有数据，这样才能让模型验证通过
using (HotelDBEntities db = new HotelDBEntities())
{
Suggestion suggestion = (from s in db.Suggestion where s.SuggestionId == suggestionId select s).FirstOrDefault();
db.Entry <Suggestion>(suggestion).State = EntityState.Modified;
suggestion.StatusId = 1;//处理投诉就是将投诉的这条数据状态改成1
return(db.SaveChanges());
}
}
//修改招聘信息
public int ModifyRecruitment(Recruitment rec)
{
using (HotelDBEntities db = new HotelDBEntities())
{
Recruitment obj = new Recruitment();
obj.PostId = rec.PostId;
db.Recruitment.Attach(obj);
obj.PostName = rec.PostName;
obj.PostType = rec.PostType;
obj.PostPlace = rec.PostPlace;
obj.PostDesc = rec.PostDesc;
obj.PostRequire = rec.PostRequire;
obj.Experience = rec.Experience;
obj.EduBackground = rec.EduBackground;
obj.RequireCount = rec.RequireCount;
obj.Manager = rec.Manager;
obj.PhoneNumber = rec.PhoneNumber;
obj.Email = rec.Email;
obj.PublishTime = rec.PublishTime;
return(db.SaveChanges());
}
}