public IActionResult CuisineImport(IFormFile excelfile)
{
var response = ResponseModelFactory.CreateInstance;
using (_dbContext)
{
DateTime beginTime = DateTime.Now;
string sWebRootFolder = _hostingEnvironment.WebRootPath + "\\UploadFiles\\ImportExcel";
//var schoolinfo = _dbContext.SchoolInforManagement.AsQueryable();
string uploadtitle = "菜品信息导入" + DateTime.Now.ToString("yyyyMMddHHmmss");
string sFileName = $"{uploadtitle}.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
//string conStr = ConnectionStrings.DefaultConnection;
string responsemsgsuccess = "";
string responsemsgrepeat = "";
string responsemsgdefault = "";
int successcount = 0;
int repeatcount = 0;
int defaultcount = 0;
string today = DateTime.Now.ToString("yyyy-MM-dd");
try
{
//把excelfile中的数据复制到file中
using (FileStream fs = new FileStream(file.ToString(), FileMode.Create)) //初始化一个指定路径和创建模式的FileStream
{
excelfile.CopyTo(fs);
fs.Flush(); //清空stream的缓存，并且把缓存中的数据输出到file
}
DataTable dt = Haikan3.Utils.ExcelTools.ExcelToDataTable(file.ToString(), "Sheet1", true);
if (dt == null || dt.Rows.Count == 0)
{
response.SetFailed("表格无数据");
return(Ok(response));
}
else
{
if (!dt.Columns.Contains("菜品名称"))
{
response.SetFailed("无‘菜品名称’列");
return(Ok(response));
}
if (!dt.Columns.Contains("价格"))
{
response.SetFailed("无‘价格’列");
return(Ok(response));
}
if (!dt.Columns.Contains("主料"))
{
response.SetFailed("无‘主料’列");
return(Ok(response));
}
if (!dt.Columns.Contains("配料"))
{
response.SetFailed("无‘配料’列");
return(Ok(response));
}
if (!dt.Columns.Contains("类别"))
{
response.SetFailed("无‘类别’列");
return(Ok(response));
}
if (!dt.Columns.Contains("简介"))
{
response.SetFailed("无‘简介’列");
return(Ok(response));
}
for (int i = 0; i < dt.Rows.Count; i++)
{
var entity = new Entities.Cuisine();
entity.CuisineUuid = Guid.NewGuid();
if (!string.IsNullOrEmpty(dt.Rows[i]["菜品名称"].ToString()))
{
var a = dt.Rows[i]["菜品名称"].ToString();
var user = _dbContext.Cuisine.Where(x => x.IsDelete == 0 && x.SchoolUuid == AuthContextService.CurrentUser.SchoolGuid).FirstOrDefault(x => x.CuisineName == dt.Rows[i]["菜品名称"].ToString());
if (user == null)
{
entity.CuisineName = dt.Rows[i]["菜品名称"].ToString();
}
else
{
responsemsgrepeat += "<p style='color:orange'>" + "第" + (i + 2) + "行菜品名称已存在" + "</p></br>";
repeatcount++;
continue;
}
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行菜品名称为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["价格"].ToString()))
{
entity.Price = dt.Rows[i]["价格"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行价格为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["主料"].ToString()))
{
//var uuname = dt.Rows[i]["主料"].ToString().ToUpper().Split('，').ToList();
//var lists = _dbContext.Ingredient.Where(x => uuname.Contains(x.FoodName) && x.IsDelete != 1).ToList();
//if (lists.Count == 0)
//{
//entity.Ingredient = "";
entity.Ingredient = dt.Rows[i]["主料"].ToString();
//responsemsgrepeat += "<p style='color:orange'>" + "第" + (i + 2) + "行主料不存在" + "</p></br>";
//repeatcount++;
//continue;
//}
//else
//{
// var list = _dbContext.Ingredient.Where(x => uuname.Contains(x.FoodName) && x.IsDelete != 1 && x.SchoolUuid == AuthContextService.CurrentUser.SchoolGuid).Select(x => x.IngredientUuid).ToList();
// var uuid = string.Join(',', list);
// entity.Ingredient = uuid;
//}
}
//else
//{
// responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行主料为空" + "</p></br>";
// defaultcount++;
// continue;
//}
if (!string.IsNullOrEmpty(dt.Rows[i]["配料"].ToString()))
{
//var uuname = dt.Rows[i]["配料"].ToString().ToUpper().Split(',').ToList();
//var lists = _dbContext.Ingredient.Where(x => uuname.Contains(x.FoodName) && x.IsDelete != 1).ToList();
//if (lists.Count == 0)
//{
//entity.Burdening = "";
entity.Burdening = dt.Rows[i]["配料"].ToString();
//responsemsgrepeat += "<p style='color:orange'>" + "第" + (i + 2) + "行配料不存在" + "</p></br>";
//repeatcount++;
//continue;
//}
//else
//{
// var list = _dbContext.Ingredient.Where(x => uuname.Contains(x.FoodName) && x.IsDelete != 1 && x.SchoolUuid == AuthContextService.CurrentUser.SchoolGuid).Select(x => x.IngredientUuid).ToList();
// var uuid = string.Join(',', list);
// entity.Burdening = uuid;
//}
}
//else
//{
// responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行配料为空" + "</p></br>";
// defaultcount++;
// continue;
//}
if (!string.IsNullOrEmpty(dt.Rows[i]["类别"].ToString()))
{
string type = dt.Rows[i]["类别"].ToString();
if (type == "荤菜" || type == "半荤菜" || type == "素菜" || type == "其它" || type == "甜品")
{
entity.CuisineType = dt.Rows[i]["类别"].ToString();
}
else
{
responsemsgrepeat += "<p style='color:orange'>" + "第" + (i + 2) + "行类别错误" + "</p></br>";
repeatcount++;
continue;
}
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行类别为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["简介"].ToString()))
{
entity.Abstract = dt.Rows[i]["简介"].ToString();
}
entity.SchoolUuid = AuthContextService.CurrentUser.SchoolGuid;
entity.AddTime = DateTime.Now.ToString("yyyy-MM-dd");
entity.AddPeople = AuthContextService.CurrentUser.DisplayName;
entity.LikeNum = 0;
entity.IsDelete = 0;
_dbContext.Cuisine.Add(entity);
_dbContext.SaveChanges();
successcount++;
}
}
responsemsgsuccess = "<p style='color:green'>导入成功:" + successcount + "条</p></br>" + responsemsgsuccess;
responsemsgrepeat = "<p style='color:orange'>重复需手动修改数据:" + repeatcount + "条</p></br>" + responsemsgrepeat;
responsemsgdefault = "<p style='color:red'>导入失败:" + defaultcount + "条</p></br>" + responsemsgdefault;
DateTime endTime = DateTime.Now;
TimeSpan useTime = endTime - beginTime;
string taketime = "导入时间" + useTime.TotalSeconds.ToString() + "秒 ";
response.SetData(JsonConvert.DeserializeObject(JsonConvert.SerializeObject(new
{
time = taketime,
successmsg = responsemsgsuccess
,
repeatmsg = responsemsgrepeat,
defaultmsg = responsemsgdefault
})));
return(Ok(response));
}
catch (Exception ex)
{
response.SetFailed(ex.Message);
return(Ok(response));
}
}
}
public IActionResult ToSync()
{
var response = ResponseModelFactory.CreateInstance;
using (_dbContext)
{
var schoollist = _dbContext.School.Where(x => x.IsDelete == 0).ToList();
var schoolEnt = schoollist.Find(x => x.SchoolName == "萧山三中");
//var schoolEnt = schoollist.Find(x => x.SchoolName == "xxxx1");
schoollist.Remove(schoolEnt);
var ingredients = _dbContext.Ingredient.Where(x => x.SchoolUuid == schoolEnt.SchoolUuid && x.IsDelete == 0).ToList();
for (int n = 0; n < schoollist.Count; n++)
{
for (int i = 0; i < ingredients.Count; i++)
{
if (!_dbContext.Ingredient.Any(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && x.FoodName == ingredients[i].FoodName))
{
var ing = new Entities.Ingredient()
{
IngredientUuid = Guid.NewGuid(),
SchoolUuid = schoollist[n].SchoolUuid,
AddTime = DateTime.Now.ToString("yyyy-MM-dd"),
AddPeople = "",
FoodName = ingredients[i].FoodName,
TypeUuid = ingredients[i].TypeUuid,
IsDelete = 0,
HeatEnergy = ingredients[i].HeatEnergy,
Protein = ingredients[i].Protein,
Fat = ingredients[i].Fat,
Va = ingredients[i].Va,
Saccharides = ingredients[i].Saccharides,
Accessory = ingredients[i].Accessory,
};
_dbContext.Ingredient.Add(ing);
}
}
//_dbContext.Ingredient.AddRange(ingredients);
_dbContext.SaveChanges();
}
var cuisines = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoolEnt.SchoolUuid && x.IsDelete == 0).ToList();
for (int n = 0; n < schoollist.Count; n++)
{
for (int i = 0; i < cuisines.Count; i++)
{
if (!_dbContext.Cuisine.Any(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && x.CuisineName == cuisines[i].CuisineName))
{
var cui = new Entities.Cuisine()
{
CuisineUuid = Guid.NewGuid(),
SchoolUuid = schoollist[n].SchoolUuid,
AddTime = DateTime.Now.ToString("yyyy-MM-dd"),
AddPeople = "",
LikeNum = 0,
IsDelete = 0,
CuisineName = cuisines[i].CuisineName,
Price = cuisines[i].Price,
Burdening = cuisines[i].Burdening,
Ingredient = cuisines[i].Ingredient,
Abstract = cuisines[i].Abstract,
CuisineType = cuisines[i].CuisineType,
Accessory = cuisines[i].Accessory,
};
_dbContext.Cuisine.Add(cui);
}
}
//_dbContext.Cuisine.AddRange(cuisines);
_dbContext.SaveChanges();
}
var nweekmenus = _dbContext.NweekMenu.Where(x => x.SchoolUuid == schoolEnt.SchoolUuid && x.IsDelete == 0).OrderByDescending(x => x.Datetimes).Take(3).ToList();
for (int n = 0; n < schoollist.Count; n++)
{
for (int i = 0; i < nweekmenus.Count; i++)
{
if (!_dbContext.NweekMenu.Any(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.Datetimes == nweekmenus[0].Datetimes && x.IsDelete == 0))
{
var ids1 = nweekmenus[i].MonMon.ToLower().Split(',');
var names1 = _dbContext.Cuisine.Where(x => ids1.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var MonMonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names1
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var MonMon = string.Join(",", MonMonLs);
var ids2 = nweekmenus[i].MonNoon.ToLower().Split(',');
var names2 = _dbContext.Cuisine.Where(x => ids2.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var MonNoonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names2
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var MonNoon = string.Join(",", MonNoonLs);
var ids3 = nweekmenus[i].MonNight.ToLower().Split(',');
var names3 = _dbContext.Cuisine.Where(x => ids3.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var MonNightLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names3
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var MonNight = string.Join(",", MonNightLs);
var ids4 = nweekmenus[i].TuesMon.ToLower().Split(',');
var names4 = _dbContext.Cuisine.Where(x => ids4.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var TuesMonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names4
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var TuesMon = string.Join(",", TuesMonLs);
var ids5 = nweekmenus[i].TuesNoon.ToLower().Split(',');
var names5 = _dbContext.Cuisine.Where(x => ids5.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var TuesNoonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names5
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var TuesNoon = string.Join(",", TuesNoonLs);
var ids6 = nweekmenus[i].TuesNight.ToLower().Split(',');
var names6 = _dbContext.Cuisine.Where(x => ids6.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var TuesNightLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names6
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var TuesNight = string.Join(",", TuesNightLs);
var ids7 = nweekmenus[i].WedMon.ToLower().Split(',');
var names7 = _dbContext.Cuisine.Where(x => ids7.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var WedMonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names7
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var WedMon = string.Join(",", WedMonLs);
var ids8 = nweekmenus[i].WedNoon.ToLower().Split(',');
var names8 = _dbContext.Cuisine.Where(x => ids8.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var WedNoonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names8
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var WedNoon = string.Join(",", WedNoonLs);
var ids9 = nweekmenus[i].WedNight.ToLower().Split(',');
var names9 = _dbContext.Cuisine.Where(x => ids9.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var WedNightLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names9
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var WedNight = string.Join(",", WedNightLs);
var ids10 = nweekmenus[i].ThursMon.ToLower().Split(',');
var names10 = _dbContext.Cuisine.Where(x => ids10.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var ThursMonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names10
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var ThursMon = string.Join(",", ThursMonLs);
var ids11 = nweekmenus[i].ThursNoon.ToLower().Split(',');
var names11 = _dbContext.Cuisine.Where(x => ids11.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var ThursNoonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names11
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var ThursNoon = string.Join(",", ThursNoonLs);
var ids12 = nweekmenus[i].ThursNight.ToLower().Split(',');
var names12 = _dbContext.Cuisine.Where(x => ids12.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var ThursNightLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names12
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var ThursNight = string.Join(",", ThursNightLs);
var ids13 = nweekmenus[i].FriMon.ToLower().Split(',');
var names13 = _dbContext.Cuisine.Where(x => ids13.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var FriMonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names13
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var FriMon = string.Join(",", FriMonLs);
var ids14 = nweekmenus[i].FriNoon.ToLower().Split(',');
var names14 = _dbContext.Cuisine.Where(x => ids14.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var FriNoonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names14
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var FriNoon = string.Join(",", FriNoonLs);
var ids15 = nweekmenus[i].FriNight.ToLower().Split(',');
var names15 = _dbContext.Cuisine.Where(x => ids15.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var FriNightLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names15
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var FriNight = string.Join(",", FriNightLs);
var ids16 = nweekmenus[i].SatMon.ToLower().Split(',');
var names16 = _dbContext.Cuisine.Where(x => ids16.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var SatMonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names16
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var SatMon = string.Join(",", SatMonLs);
var ids17 = nweekmenus[i].SatNoon.ToLower().Split(',');
var names17 = _dbContext.Cuisine.Where(x => ids17.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var SatNoonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names17
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var SatNoon = string.Join(",", SatNoonLs);
var ids18 = nweekmenus[i].SatNight.ToLower().Split(',');
var names18 = _dbContext.Cuisine.Where(x => ids18.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var SatNightLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names18
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var SatNight = string.Join(",", SatNightLs);
var ids19 = nweekmenus[i].SunMon.ToLower().Split(',');
var names19 = _dbContext.Cuisine.Where(x => ids19.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var SunMonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names19
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var SunMon = string.Join(",", SunMonLs);
var ids20 = nweekmenus[i].SunNoon.ToLower().Split(',');
var names20 = _dbContext.Cuisine.Where(x => ids20.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var SunNoonLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names20
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var SunNoon = string.Join(",", SunNoonLs);
var ids21 = nweekmenus[i].SunNight.ToLower().Split(',');
var names21 = _dbContext.Cuisine.Where(x => ids21.Contains(x.CuisineUuid.ToString().ToLower())).Select(x => x.CuisineName).ToList();
var SunNightLs = _dbContext.Cuisine.Where(x => x.SchoolUuid == schoollist[n].SchoolUuid && x.IsDelete == 0 && names21
.Contains(x.CuisineName)).Select(x => x.CuisineUuid).ToArray();
var SunNight = string.Join(",", SunNightLs);
var menu = new NweekMenu()
{
NweekMenuUuid = Guid.NewGuid(),
SchoolUuid = schoollist[n].SchoolUuid,
MonMon = MonMon,
MonNight = MonNight,
MonNoon = MonNoon,
TuesMon = TuesMon,
TuesNight = TuesNight,
TuesNoon = TuesNoon,
WedMon = WedMon,
WedNight = WedNight,
WedNoon = WedNoon,
ThursMon = ThursMon,
ThursNight = ThursNight,
ThursNoon = ThursNoon,
FriMon = FriMon,
FriNight = FriNight,
FriNoon = FriNoon,
SatMon = SatMon,
SatNight = SatNight,
SatNoon = SatNoon,
SunMon = SunMon,
SunNight = SunNight,
SunNoon = SunNoon,
AddTime = DateTime.Now.ToString("yyyy-MM-dd"),
AddPeople = "",
IsDelete = 0,
Datetimes = nweekmenus[0].Datetimes,
};
_dbContext.NweekMenu.Add(menu);
}
}
_dbContext.SaveChanges();
}
}
return(Ok(response));
}
