public ActionResult Import(Models.PerformItem.Import vm)
{
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())
{
#region 1、Excel模版校验
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()))
{
Text += a + ",";
}
}
if (!string.IsNullOrEmpty(Text))
{
ModelState.AddModelError("", "上传的EXCEL晚自习内容与预期不一致，缺少对应的字段：" + Text);
return(View(vm));
}
#endregion
#region 2、Excel数据读取
foreach (System.Data.DataRow dr in dt.Rows)
{
var dtoTest = new Dto.PerformItem.Import()
{
No = Convert.ToString(dr["排序"]),
PerformItemName = Convert.ToString(dr["评价项目"]),
PerformGroupName = Convert.ToString(dr["评价分组"]),
ScoreMax = Convert.ToString(dr["满分值"]),
Rate = Convert.ToString(dr["折算比例"]),
IsMany = Convert.ToString(dr["是否多次"]),
IsSelect = Convert.ToString(dr["是否选项"]),
DefaultValue = Convert.ToString(dr["默认分数"]),
OptionNo = Convert.ToString(dr["选项排序"]),
OptionName = Convert.ToString(dr["选项名称"]),
OptionValue = Convert.ToString(dr["选项分值"])
};
if (vm.ImportList.Where(d => d.No == dtoTest.No &&
d.No == dtoTest.No &&
d.PerformItemName == dtoTest.PerformItemName &&
d.PerformGroupName == dtoTest.PerformGroupName &&
d.ScoreMax == dtoTest.ScoreMax &&
d.Rate == dtoTest.Rate &&
d.IsMany == dtoTest.IsMany &&
d.IsSelect == dtoTest.IsSelect &&
d.DefaultValue == dtoTest.DefaultValue &&
d.OptionNo == dtoTest.OptionNo &&
d.OptionName == dtoTest.OptionName &&
d.OptionValue == dtoTest.OptionValue).Count() == 0)
{
vm.ImportList.Add(dtoTest);
}
}
vm.ImportList.RemoveAll(d =>
string.IsNullOrEmpty(d.No) &&
string.IsNullOrEmpty(d.PerformItemName) &&
string.IsNullOrEmpty(d.PerformGroupName) &&
string.IsNullOrEmpty(d.ScoreMax) &&
string.IsNullOrEmpty(d.Rate) &&
string.IsNullOrEmpty(d.IsMany) &&
string.IsNullOrEmpty(d.IsSelect) &&
string.IsNullOrEmpty(d.DefaultValue) &&
string.IsNullOrEmpty(d.OptionNo) &&
string.IsNullOrEmpty(d.OptionName) &&
string.IsNullOrEmpty(d.OptionValue)
);
if (vm.ImportList.Count == 0)
{
ModelState.AddModelError("", "Excel未读取到任何有效数据!");
return(View(vm));
}
#endregion
#region 3、Excel格式校验
//评价分组
var PerformGroup = (from p in db.Table <Perform.Entity.tbPerformGroup>()
.Include(d => d.tbPerform)
where p.tbPerform.Id == vm.PerformId &&
p.tbPerform.IsDeleted == false
select p).ToList();
//评价内容
var PerformItemList = (from p in db.Table <Perform.Entity.tbPerformItem>()
.Include(d => d.tbPerformGroup)
where p.tbPerformGroup.tbPerform.Id == vm.PerformId &&
p.tbPerformGroup.IsDeleted == false &&
p.tbPerformGroup.tbPerform.IsDeleted == false
select p).ToList();
//评价选项
var PerformOptionList = (from p in db.Table <Entity.tbPerformOption>()
.Include(d => d.tbPerformItem)
where p.tbPerformItem.tbPerformGroup.tbPerform.Id == vm.PerformId &&
p.tbPerformItem.IsDeleted == false &&
p.tbPerformItem.tbPerformGroup.IsDeleted == false &&
p.tbPerformItem.tbPerformGroup.tbPerform.IsDeleted == false
select p).ToList();
//是否多次打分
var IsManyList = new List <string>()
{
"是", "否"
};
//是否可选
var IsSelectList = new List <string>()
{
"文本框", "下拉框"
};
foreach (var item in vm.ImportList)
{
int No = 0;
if (int.TryParse(item.No, out No) == false || No <= 0)
{
item.Error = item.Error + "【排序】必须是正整数!";
continue;
}
if (string.IsNullOrEmpty(item.PerformItemName))
{
item.Error = item.Error + "【评价项目】不能为空!";
continue;
}
if (string.IsNullOrEmpty(item.PerformGroupName))
{
item.Error = item.Error + "【评价分组】不能为空!";
continue;
}
else
{
if (PerformGroup.Where(d => d.PerformGroupName == item.PerformGroupName).Count() == decimal.Zero)
{
item.Error = item.Error + "【评价分组】不存在数据库!";
continue;
}
}
if (string.IsNullOrEmpty(item.ScoreMax))
{
item.ScoreMax = "100";
}
else
{
try
{
var ScoreMax = Convert.ToDecimal(item.ScoreMax);
}
catch
{
item.Error = item.Error + "【满分值】必须是正确的数字格式!";
continue;
}
}
if (string.IsNullOrEmpty(item.Rate))
{
item.Rate = "100";
}
else
{
try
{
var rate = Convert.ToDecimal(item.Rate);
}
catch
{
item.Error = item.Error + "【折算比例】必须是正确的数字格式!";
continue;
}
}
if (string.IsNullOrEmpty(item.DefaultValue))
{
item.DefaultValue = "0";
}
else
{
try
{
var DefaultValue = Convert.ToDecimal(item.DefaultValue);
}
catch
{
item.Error = item.Error + "【默认分数】必须是正确的数字格式!";
continue;
}
}
#region 是否多次
if (string.IsNullOrEmpty(item.IsMany))
{
item.Error = item.Error + "【是否多次】不能为空!";
continue;
}
else
{
if (IsManyList.Where(d => d == item.IsMany).Count() == decimal.Zero)
{
item.Error = item.Error + "【是否多次】必须是：是、否!";
continue;
}
else
{
if (item.IsMany == "是")
{
#region 是否选项
if (string.IsNullOrEmpty(item.IsSelect))
{
item.Error = item.Error + "【是否选项】不能为空!";
continue;
}
else
{
if (IsSelectList.Where(d => d == item.IsSelect).Count() == decimal.Zero)
{
item.Error = item.Error + "【是否选项】必须是：文本框、下拉框!";
continue;
}
else
{
if (item.IsSelect == "下拉框")
{
#region 项排序
int KpiNo = 0;
if (int.TryParse(item.OptionNo, out KpiNo) == false || KpiNo <= 0)
{
item.Error = item.Error + "【选项排序】必须是正整数!";
continue;
}
#endregion
#region 项名称
if (string.IsNullOrEmpty(item.OptionName))
{
item.Error = item.Error + "【选项名称】不能为空!";
continue;
}
#endregion
#region 项分值
if (string.IsNullOrEmpty(item.OptionValue))
{
item.Error = item.Error + "【选项分值】不能为空!";
continue;
}
else
{
try
{
var KpiOptionValue = Convert.ToDecimal(item.OptionValue);
}
catch
{
item.Error = item.Error + "【选项分值】必须是正确的数字格式!";
continue;
}
}
#endregion
}
}
}
#endregion
}
else
{
#region 是否选项
if (string.IsNullOrEmpty(item.IsSelect))
{
item.Error = item.Error + "【是否选项】不能为空!";
continue;
}
else
{
if (item.IsSelect != "文本框")
{
item.Error = item.Error + "【是否选项】必须是：文本框!";
continue;
}
}
#endregion
}
}
}
#endregion
if (vm.IsUpdate)
{
if (PerformItemList.Where(d => d.PerformItemName == item.PerformItemName && d.tbPerformGroup.PerformGroupName == item.PerformGroupName).Count() > 1)
{
item.Error += "系统中该评价内容数据存在重复，无法确认需要更新的记录!";
continue;
}
}
else
{
if (PerformItemList.Where(d => d.PerformItemName == item.PerformItemName && d.tbPerformGroup.PerformGroupName == item.PerformGroupName).Count() > 0)
{
item.Error += "系统中已存在该记录!";
continue;
}
}
}
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 5、Excel执行导入
var addPerformItemList = new List <Perform.Entity.tbPerformItem>();
var addPerformOptionList = new List <Entity.tbPerformOption>();
foreach (var item in vm.ImportList)
{
Perform.Entity.tbPerformItem tb = null;
if (PerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).Count() > 0)
{
if (vm.IsUpdate)
{
tb = PerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).FirstOrDefault();
tb.No = item.No.ConvertToInt();
tb.PerformItemName = item.PerformItemName;
tb.IsMany = item.IsMany == "是" ? true : false;
tb.IsSelect = item.IsSelect == "文本框" ? false : true;
tb.DefaultValue = item.DefaultValue.ConvertToDecimal();
if (!string.IsNullOrEmpty(item.PerformGroupName))
{
tb.tbPerformGroup = PerformGroup.Where(d => d.PerformGroupName == item.PerformGroupName).FirstOrDefault();
}
tb.ScoreMax = item.ScoreMax;
tb.Rate = Convert.ToDecimal(item.Rate);
}
}
else
{
if (addPerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).Count() == decimal.Zero)
{
tb = new Perform.Entity.tbPerformItem();
tb.No = item.No.ConvertToInt();
tb.PerformItemName = item.PerformItemName;
tb.IsMany = item.IsMany == "是" ? true : false;
tb.IsSelect = item.IsSelect == "文本框" ? false : true;
tb.DefaultValue = item.DefaultValue.ConvertToDecimal();
if (!string.IsNullOrEmpty(item.PerformGroupName))
{
tb.tbPerformGroup = PerformGroup.Where(d => d.PerformGroupName == item.PerformGroupName).FirstOrDefault();
}
tb.ScoreMax = item.ScoreMax;
tb.Rate = Convert.ToDecimal(item.Rate);
addPerformItemList.Add(tb);
}
else
{
tb = addPerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).FirstOrDefault();
}
}
if (string.IsNullOrEmpty(item.OptionName) == false && item.IsMany == "是" && item.IsSelect == "下拉框")
{
if (PerformOptionList.Where(d => d.tbPerformItem.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.tbPerformItem.PerformItemName == item.PerformItemName && d.OptionName == item.OptionName).Count() == decimal.Zero)
{
if (addPerformOptionList.Where(d => d.tbPerformItem.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.tbPerformItem.PerformItemName == item.PerformItemName && d.OptionName == item.OptionName).Count() == decimal.Zero)
{
var tbOption = new Entity.tbPerformOption();
tbOption.No = item.OptionNo.ConvertToInt();
tbOption.OptionName = item.OptionName;
tbOption.OptionValue = item.OptionValue.ConvertToDecimal();
tbOption.tbPerformItem = tb;
addPerformOptionList.Add(tbOption);
}
}
}
}
db.Set <Entity.tbPerformItem>().AddRange(addPerformItemList);
db.Set <Entity.tbPerformOption>().AddRange(addPerformOptionList);
if (db.SaveChanges() > 0)
{
XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("批量导入了评价项目");
vm.Status = true;
}
#endregion
}
}
vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error));
return(View(vm));
}
public ActionResult Import()
{
var vm = new Models.PerformItem.Import();
return(View(vm));
}