/// <summary>
/// 月详情
/// </summary>
/// <param name="salerId"></param>
/// <param name="year"></param>
/// <param name="month"></param>
/// <returns></returns>
public IActionResult Details(int salerId, int year, int month)
{
var service = new MonthReportService();
var entity = service.LoadDto(salerId, year, month);
return(View(entity));
}
/// <summary>
/// 记录列表
/// </summary>
/// <param name="salerId"></param>
/// <param name="year"></param>
/// <param name="quarter"></param>
/// <returns></returns>
public IActionResult Items(int salerId, int year, int?quarter)
{
var service = new MonthReportService();
var param = new MonthReportSearchParam
{
SalerId = salerId,
Year = year
};
if (quarter.HasValue)
{
param.Quarter = quarter;
}
var list = service.Get(param);
return(View(list));
}
public IActionResult Import(int year, int month, IFormFile excelFile)
{
ViewBag.ReturnUrl = $"/Security/Index?year={year}&month={month}";
ViewBag.ImportUrl = $"/Security/Import?year={year}&month={month}";
// 基础验证
if (excelFile == null || Path.GetExtension(excelFile.FileName) != ".xlsx")
{
return(Content("不受支持的文件"));
}
// 获取原始数据
MonthReportSearchParam param = new MonthReportSearchParam();
param.Month = month;
param.Year = year;
MonthReportService service = new MonthReportService();
var source = service.GetSecurityList(param);
if (source.Count() == 0)
{
return(Content("当前日期原数据为空"));
}
// 解析Excel中的记录
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string folderName = Path.Combine(sWebRootFolder, "reports");
if (!Directory.Exists(folderName))
{
Directory.CreateDirectory(folderName);
}
FileInfo file = new FileInfo(Path.Combine(folderName, excelFile.FileName));
try
{
using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
{
excelFile.CopyTo(fs);
fs.Flush();
}
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
if (ColCount != 8)
{
return(Content("不合法的数据列"));
}
List <ImportResultViewModel> result = new List <ImportResultViewModel>();
// 获取转换后的数据列表
List <MonthReportEntity> content = new List <MonthReportEntity>();
for (int row = 2; row <= rowCount; row++)
{
Convert(worksheet, row);
}
// 内部方法，单条处理数据
void Convert(ExcelWorksheet sheet, int idx)
{
try
{
var entity = new MonthReportEntity();
int id = 0;
decimal fwf = 0;
int y = 0;
int m = 0;
int.TryParse(sheet.Cells[idx, 2].Value.ToString(), out y);
int.TryParse(sheet.Cells[idx, 3].Value.ToString(), out m);
int.TryParse(sheet.Cells[idx, 1].Value.ToString(), out id);
decimal.TryParse(sheet.Cells[idx, 8].Value.ToString(), out fwf);
entity.Id = id;
entity.FWF = fwf;
entity.Year = y;
entity.Month = m;
var deptName = sheet.Cells[idx, 4].Value.ToString();
var salerName = sheet.Cells[idx, 6].Value.ToString();
var r = new ImportResultViewModel
{
Id = id,
DeptName = deptName,
SalerName = salerName,
Result = "清洗失败",
Remark = "源数据中不存在此记录"
};
var s = source.Where(mr => mr.Id == id).SingleOrDefault();
if (s == null)
{
r.Result = "数据清洗失败";
r.Remark = "元数据中不存在此记录";
}
else if (s.Year != year || s.Month != month)
{
r.Result = "数据清洗失败";
r.Remark = "年度和月度与源数据不符";
}
else if (s.SalerName != salerName.Trim() || s.DeptName != deptName.Trim())
{
r.Result = "数据清洗失败";
r.Remark = "部门和业务员不匹配";
}
else
{
var submitResult = service.SecuritySubmit(entity, AppUser);
if (submitResult.Code == 200)
{
r.Result = "更新成功";
r.Remark = "";
}
else
{
r.Result = "数据更新失败";
r.Remark = submitResult.Message;
}
}
result.Add(r);
}
catch (Exception ex)
{
result.Add(new ImportResultViewModel
{
Id = 0,
DeptName = "",
SalerName = "",
Result = "系统错误",
Remark = $"第{idx}条记录发生错误：{ex.Message}"
});
}
}
return(View("Result", result));
}
}
catch (Exception ex)
{
return(Content(ex.Message));
}
}