public ActionResult Import(Models.Room.Import vm)
{
vm.ImportList = new List <Dto.Room.Import>();
if (ModelState.IsValid)
{
var file = Request.Files[nameof(vm.UploadFile)];
var fileSave = System.IO.Path.GetTempFileName();
file.SaveAs(fileSave);
using (var db = new XkSystem.Models.DbContext())
{
if (Code.Common.GetFileType(file.FileName) != Code.FileType.Excel)
{
ModelState.AddModelError("", "上传的文件不是正确的EXCLE文件!");
return(View(vm));
}
var dt = Code.NpoiHelper.ExcelToDataTable(fileSave, System.IO.Path.GetExtension(file.FileName), string.Empty);
if (dt == null)
{
ModelState.AddModelError("", "无法读取上传的文件，请检查文件格式是否正确!");
return(View(vm));
}
var tbList = new List <string>()
{
"房间号", "教室", "容纳人数", "教学楼", "教室类型", "考勤机"
};
var Text = string.Empty;
foreach (var a in tbList)
{
if (dt.Columns.Contains(a.ToString()) == false)
{
Text += a + ",";
}
}
if (string.IsNullOrEmpty(Text) == false)
{
ModelState.AddModelError("", "上传的EXCEL内容与预期不一致，缺少对应的字段：" + Text);
return(View(vm));
}
//将DataTable转为List
foreach (System.Data.DataRow dr in dt.Rows)
{
var dto = new Dto.Room.Import();
dto.No = dr["房间号"].ConvertToString();
dto.RoomName = dr["教室"].ConvertToString();
dto.MaxUser = dr["容纳人数"].ConvertToString();
dto.BuildName = dr["教学楼"].ConvertToString();
dto.RoomTypeName = dr["教室类型"].ConvertToString();
dto.AttendanceMachine = dr["考勤机"].ConvertToString();
vm.ImportList.Add(dto);
}
vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.RoomName + d.MaxUser));
if (vm.ImportList.Count == 0)
{
ModelState.AddModelError("", "未读取到任何有效数据!");
return(View(vm));
}
var roomList = (from p in db.Table <Basis.Entity.tbRoom>()
select p).ToList();
var BuildList = db.Table <Basis.Entity.tbBuild>().ToList();
var roomTypeList = db.Table <Basis.Entity.tbRoomType>().ToList();
#region 验证数据格式是否正确
foreach (var item in vm.ImportList)
{
if (!string.IsNullOrEmpty(item.BuildName) &&
BuildList.Where(d => d.BuildName == item.BuildName).Count() == 0)
{
item.Error += "教学楼不存在；";
}
if (!string.IsNullOrEmpty(item.RoomTypeName) &&
roomTypeList.Where(d => d.RoomTypeName == item.RoomTypeName).Count() == 0)
{
item.Error += "教室类型不存在；";
}
if (string.IsNullOrEmpty(item.RoomName))
{
item.Error = item.Error + "教室不能为空!";
}
if (vm.ImportList.Where(d => d.RoomName == item.RoomName).Count() > 1)
{
item.Error = item.Error + "该条数据重复!";
}
if (string.IsNullOrEmpty(item.No) == false)
{
int no = 0;
if (int.TryParse(item.No, out no) == false && no >= 0)
{
item.Error = item.Error + "房间号必须是正整数!";
}
}
if (string.IsNullOrEmpty(item.MaxUser) == false)
{
int maxUser = 0;
if (int.TryParse(item.MaxUser, out maxUser) == false && maxUser >= 0)
{
item.Error = item.Error + "容纳人数必须是正整数!";
}
}
if (vm.IsUpdate == false && roomList.Where(d => d.RoomName == item.RoomName).Count() > 0)
{
item.Error = item.Error + "系统中已存在该记录!";
}
}
if (vm.ImportList.Where(d => string.IsNullOrEmpty(d.Error) == false).Count() > 0)
{
vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error));
return(View(vm));
}
#endregion
#region 数据导入，判断关键字，存在的数据做更新，不存在的做新增
List <Basis.Entity.tbRoom> listRoom = new List <Basis.Entity.tbRoom>();
foreach (var item in vm.ImportList)
{
if (roomList.Where(d => d.RoomName == item.RoomName).Count() > 0)
{
if (vm.IsUpdate)
{
if (roomList.Where(d => d.RoomName == item.RoomName).Count() > 1)
{
item.Error = item.Error + "系统中该教室数据存在重复，无法确认需要更新的记录!";
}
else
{
var tb = roomList.Where(d => d.RoomName == item.RoomName).FirstOrDefault();
tb.No = item.No.ConvertToInt();
tb.RoomName = item.RoomName;
tb.MaxUser = item.MaxUser.ConvertToInt();
tb.AttendanceMachine = item.AttendanceMachine;
if (!string.IsNullOrEmpty(item.RoomTypeName))
{
tb.tbRoomType = roomTypeList.Where(d => d.RoomTypeName == item.RoomTypeName).FirstOrDefault();
}
if (!string.IsNullOrEmpty(item.BuildName))
{
tb.tbBuild = BuildList.Where(d => d.BuildName == item.BuildName).FirstOrDefault();
}
}
}
else
{
item.Error += "教室已经存在；";
}
}
else
{
var tb = new Basis.Entity.tbRoom();
tb.No = item.No.ConvertToInt();
tb.RoomName = item.RoomName;
tb.MaxUser = item.MaxUser.ConvertToInt();
tb.AttendanceMachine = item.AttendanceMachine;
if (!string.IsNullOrEmpty(item.RoomTypeName))
{
tb.tbRoomType = roomTypeList.Where(d => d.RoomTypeName == item.RoomTypeName).FirstOrDefault();
}
if (!string.IsNullOrEmpty(item.BuildName))
{
tb.tbBuild = BuildList.Where(d => d.BuildName == item.BuildName).FirstOrDefault();
}
listRoom.Add(tb);
}
}
if (vm.ImportList.Where(d => string.IsNullOrEmpty(d.Error) == false).Count() > 0)
{
vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error));
return(View(vm));
}
#endregion
db.Set <Basis.Entity.tbRoom>().AddRange(listRoom);
if (db.SaveChanges() > 0)
{
vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error));
XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("导入教室信息");
vm.Status = true;
}
}
}
return(View(vm));
}
public ActionResult Edit(Models.Room.Edit vm)
{
using (var db = new XkSystem.Models.DbContext())
{
var error = new List <string>();
if (error.Count == decimal.Zero)
{
if (db.Table <Basis.Entity.tbRoom>().Where(d => d.RoomName == vm.RoomEdit.RoomName && d.Id != vm.RoomEdit.Id).Any())
{
error.AddError("该教室已存在!");
}
else
{
if (vm.RoomEdit.Id == 0)
{
var tb = new Basis.Entity.tbRoom();
tb.No = vm.RoomEdit.No == null?db.Table <Basis.Entity.tbRoom>().Select(d => d.No).DefaultIfEmpty(0).Max() + 1 : (int)vm.RoomEdit.No;
tb.RoomName = vm.RoomEdit.RoomName;
tb.tbBuild = db.Set <Basis.Entity.tbBuild>().Find(vm.RoomEdit.BuildId);
tb.tbRoomType = db.Set <Basis.Entity.tbRoomType>().Find(vm.RoomEdit.RoomTypeId);
tb.MaxUser = vm.RoomEdit.MaxUser;
tb.AttendanceMachine = vm.RoomEdit.AttendanceMachine;
db.Set <Basis.Entity.tbRoom>().Add(tb);
if (db.SaveChanges() > 0)
{
XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("添加了教室");
}
}
else
{
var tb = (from p in db.Table <Basis.Entity.tbRoom>()
where p.Id == vm.RoomEdit.Id
select p).FirstOrDefault();
if (tb != null)
{
tb.No = vm.RoomEdit.No == null?db.Table <Basis.Entity.tbRoom>().Select(d => d.No).DefaultIfEmpty(0).Max() + 1 : (int)vm.RoomEdit.No;
tb.RoomName = vm.RoomEdit.RoomName;
tb.tbBuild = db.Set <Basis.Entity.tbBuild>().Find(vm.RoomEdit.BuildId);
tb.tbRoomType = db.Set <Basis.Entity.tbRoomType>().Find(vm.RoomEdit.RoomTypeId);
tb.MaxUser = vm.RoomEdit.MaxUser;
tb.AttendanceMachine = vm.RoomEdit.AttendanceMachine;
if (db.SaveChanges() > 0)
{
XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("修改了教室");
}
}
else
{
error.AddError(Resources.LocalizedText.MsgNotFound);
}
}
}
}
return(Code.MvcHelper.Post(error));
}
}