public IActionResult Create(PurchaseRecordViewModel model) { var response = ResponseModelFactory.CreateInstance; if (model.SchoolUuid == null && AuthContextService.CurrentUser.SchoolGuid == null) { response.SetFailed("请登录学校账号"); return(Ok(response)); } using (_dbContext) { var entity = _dbContext.Ingredient.FirstOrDefault(x => x.FoodName == model.FoodName.Trim() && x.IsDelete == 0 && x.SchoolUuid == AuthContextService.CurrentUser.SchoolGuid); if (entity == null) { response.SetFailed("该食材不存在"); return(Ok(response)); } var purchaseRecord = new Entities.PurchaseRecord() { PurchaseUuid = Guid.NewGuid(), IngredientUuid = model.IngredientUuid, Supplier = model.Supplier, //PurchaseDate = Convert.ToDateTime(model.PurchaseDate).AddHours(-8).ToString("yyyy-MM-dd HH:mm:ss"), PurchaseDate = Convert.ToDateTime(model.PurchaseDate).ToString("yyyy-MM-dd HH:mm:ss"), PurchaseNum = model.PurchaseNum, HeatEnergy = model.HeatEnergy, Protein = model.Protein, Fat = model.Fat, Saccharides = model.Saccharides, Va = model.Va, State = model.State, Accessory = model.Accessory, SystemUserUuid = model.SystemUserUuid, AddTime = DateTime.Now.ToString("yyyy-MM-dd"), AddPeople = model.AddPeople, IsDelete = 0, Price = model.Price, Unit = model.Unit, SchoolUuid = model.SchoolUuid != null ? model.SchoolUuid : AuthContextService.CurrentUser.SchoolGuid, }; _dbContext.PurchaseRecord.Add(purchaseRecord); var num = _dbContext.SaveChanges(); if (num > 0) { response.SetSuccess("添加成功"); } else { response.SetFailed("添加失败"); } return(Ok(response)); } }
public IActionResult Import(IFormFile excelfile) { var response = ResponseModelFactory.CreateInstance; using (_dbContext) { DateTime beginTime = DateTime.Now; string sWebRootFolder = _hostEnv.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(), "食材采购记录", 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++) { if (!string.IsNullOrEmpty(dt.Rows[i]["采购时间"].ToString()) && !string.IsNullOrEmpty(dt.Rows[i]["采购人"].ToString())) { var entity = new Entities.PurchaseRecord(); entity.PurchaseUuid = Guid.NewGuid(); if (!string.IsNullOrEmpty(dt.Rows[i]["食材名称"].ToString())) { var food = _dbContext.Ingredient.Where(x => x.IsDelete == 0).FirstOrDefault(x => x.FoodName == dt.Rows[i]["食材名称"].ToString()); if (food != null) { entity.IngredientUuid = food.IngredientUuid; } 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 = Convert.ToDouble(dt.Rows[i]["单价(元/斤)"].ToString()); } else { responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行单价为空" + "</p></br>"; defaultcount++; continue; } if (!string.IsNullOrEmpty(dt.Rows[i]["供应商"].ToString())) { entity.Supplier = dt.Rows[i]["供应商"].ToString(); } if (!string.IsNullOrEmpty(dt.Rows[i]["采购时间"].ToString())) { DateTime time; if (DateTime.TryParse(dt.Rows[i]["采购时间"].ToString(), out time)) { entity.PurchaseDate = Convert.ToDateTime(dt.Rows[i]["采购时间"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); } else { responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行采购时间格式有错:举例(2020-07-01)" + "</p></br>"; defaultcount++; continue; } } else { responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行采购时间为空" + "</p></br>"; defaultcount++; continue; } if (!string.IsNullOrEmpty(dt.Rows[i]["采购数量"].ToString())) { entity.PurchaseNum = dt.Rows[i]["采购数量"].ToString(); } else { responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行采购数量为空" + "</p></br>"; defaultcount++; continue; } if (!string.IsNullOrEmpty(dt.Rows[i]["采购人"].ToString())) { entity.SystemUserUuid = dt.Rows[i]["采购人"].ToString(); } else { responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行采购人为空" + "</p></br>"; defaultcount++; continue; } entity.SchoolUuid = AuthContextService.CurrentUser.SchoolGuid; entity.AddTime = DateTime.Now.ToString("yyyy-MM-dd"); entity.AddPeople = AuthContextService.CurrentUser.DisplayName; entity.IsDelete = 0; entity.State = "0"; _dbContext.PurchaseRecord.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)); } } }