public ActionResult Index()
{
MonthReportSearchParam param = new MonthReportSearchParam();
param.Year = DateTime.Today.Year;
param.Month = DateTime.Today.Month;
InitUI();
return(View(param));
}
public ActionResult Index(int?year = null, int?month = null)
{
MonthReportSearchParam param = new MonthReportSearchParam();
param.Year = year ?? DateTime.Today.Year;
param.Month = month ?? DateTime.Today.Month;
InitUI();
return(View(param));
}
public JsonResult Get(MonthReportSearchParam param, int pageIndex = 1, int pageSize = 20)
{
try
{
var list = service.Get(param);
return(Json(ResultUtil.List <MonthReportDto>(list)));
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
return(Json(ResultUtil.Fail()));
}
}
/// <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 Export(int year, int month, int?deptId)
{
if (year < 2000 || year > DateTime.Today.Year)
{
return(Content("年度参数必须大于等于2000且小于当前年份"));
}
if (month < 1 || month > 12)
{
return(Content("月度参数必须大于等于1，小于等于12"));
}
var param = new MonthReportSearchParam();
param.Year = year;
param.Month = month;
param.DeptId = deptId;
var data = service.Get(param);
try
{
var list = service.Get(param);
string folderName = DateTime.Today.ToString("yyyyMM");
string fileName = $"{year}年{month}月销售月报.xlsx";
string baseFolderName = $"{Directory.GetCurrentDirectory()}//wwwroot//report";
if (!Directory.Exists(baseFolderName))
{
Directory.CreateDirectory(baseFolderName);
}
string savePath = $"{baseFolderName}//{fileName}";
if (System.IO.File.Exists(savePath))
{
System.IO.File.Delete(savePath);
}
using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(savePath)))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("sheet1");
workSheet.Cells.Style.Font.Name = "microsoft yahei";
workSheet.Cells.Style.Font.Size = 9;
workSheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
workSheet.Cells[1, 1, 1, 28].Style.Border.Top.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 28].Style.Border.Left.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 28].Style.Border.Right.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 28].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 28].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
workSheet.Cells[2, 1, 2, 28].Style.Border.Top.Style = ExcelBorderStyle.Thin;
workSheet.Cells[2, 1, 2, 28].Style.Border.Left.Style = ExcelBorderStyle.Thin;
workSheet.Cells[2, 1, 2, 28].Style.Border.Right.Style = ExcelBorderStyle.Thin;
workSheet.Cells[2, 1, 2, 28].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells[2, 1, 2, 28].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
workSheet.Cells[1, 1, 1, 28].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells[1, 1, 1, 28].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
workSheet.Cells[1, 1, 1, 28].Style.Font.Bold = true;
workSheet.Cells[1, 1, 1, 28].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells[2, 1, 2, 28].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells[2, 1, 2, 28].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
workSheet.Cells[2, 1, 2, 28].Style.Font.Bold = true;
workSheet.Cells[2, 1, 2, 28].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells[1, 1].Value = "部门";
workSheet.Cells[1, 2].Value = "业务员";
workSheet.Cells[1, 3].Value = "职位";
workSheet.Cells[1, 4].Value = "年";
workSheet.Cells[1, 5].Value = "月";
workSheet.Cells[1, 1, 2, 1].Merge = true;
workSheet.Cells[1, 2, 2, 2].Merge = true;
workSheet.Cells[1, 3, 2, 3].Merge = true;
workSheet.Cells[1, 4, 2, 4].Merge = true;
workSheet.Cells[1, 5, 2, 5].Merge = true;
workSheet.Cells[1, 6].Value = "所乘交通工具";
workSheet.Cells[1, 6, 1, 10].Merge = true;
workSheet.Cells[2, 6].Value = "飞机";
workSheet.Cells[2, 7].Value = "火车";
workSheet.Cells[2, 8].Value = "巴士";
workSheet.Cells[2, 9].Value = "自驾车";
workSheet.Cells[2, 10].Value = "的士费";
workSheet.Cells[1, 11].Value = "业务招待费";
workSheet.Cells[1, 12].Value = "住宿费";
workSheet.Cells[1, 13].Value = "住宿节约奖励";
workSheet.Cells[1, 11, 2, 11].Merge = true;
workSheet.Cells[1, 11, 2, 12].Merge = true;
workSheet.Cells[1, 11, 2, 13].Merge = true;
workSheet.Cells[1, 14].Value = "出差补贴";
workSheet.Cells[1, 14, 1, 15].Merge = true;
workSheet.Cells[2, 14].Value = "招标费用";
workSheet.Cells[2, 15].Value = "出差补贴";
workSheet.Cells[1, 16].Value = "通讯费";
workSheet.Cells[1, 17].Value = "其他费用";
workSheet.Cells[1, 18].Value = "薪资";
workSheet.Cells[1, 19].Value = "合计";
workSheet.Cells[1, 20].Value = "业绩";
workSheet.Cells[1, 21].Value = "服务费";
workSheet.Cells[1, 22].Value = "账面毛利";
workSheet.Cells[1, 23].Value = "毛利";
workSheet.Cells[1, 24].Value = "毛利率";
workSheet.Cells[1, 25].Value = "销售费用占业绩";
workSheet.Cells[1, 26].Value = "效益";
workSheet.Cells[1, 27].Value = "上个月效益";
workSheet.Cells[1, 28].Value = "去年同月效益";
workSheet.Cells[1, 16, 2, 16].Merge = true;
workSheet.Cells[1, 17, 2, 17].Merge = true;
workSheet.Cells[1, 18, 2, 18].Merge = true;
workSheet.Cells[1, 19, 2, 19].Merge = true;
workSheet.Cells[1, 20, 2, 20].Merge = true;
workSheet.Cells[1, 21, 2, 21].Merge = true;
workSheet.Cells[1, 22, 2, 22].Merge = true;
workSheet.Cells[1, 23, 2, 23].Merge = true;
workSheet.Cells[1, 24, 2, 24].Merge = true;
workSheet.Cells[1, 25, 2, 25].Merge = true;
workSheet.Cells[1, 26, 2, 26].Merge = true;
workSheet.Cells[1, 27, 2, 27].Merge = true;
workSheet.Cells[1, 28, 2, 28].Merge = true;
workSheet.Row(1).Height = 16;
var rowIndex = 3;
foreach (var report in list)
{
workSheet.Cells[rowIndex, 1].Value = report.DeptName;
workSheet.Cells[rowIndex, 2].Value = report.SalerName;
workSheet.Cells[rowIndex, 3].Value = report.Job;
workSheet.Cells[rowIndex, 4].Value = report.Year;
workSheet.Cells[rowIndex, 5].Value = report.Month;
workSheet.Cells[rowIndex, 6].Value = report.FJ;
workSheet.Cells[rowIndex, 7].Value = report.HC;
workSheet.Cells[rowIndex, 8].Value = report.BS;
workSheet.Cells[rowIndex, 9].Value = report.ZJC;
workSheet.Cells[rowIndex, 10].Value = report.DSF;
workSheet.Cells[rowIndex, 11].Value = report.YWZDF;
workSheet.Cells[rowIndex, 12].Value = report.ZSF;
workSheet.Cells[rowIndex, 13].Value = report.ZSJYJL;
workSheet.Cells[rowIndex, 14].Value = report.ZBFY;
workSheet.Cells[rowIndex, 15].Value = report.CCBT;
workSheet.Cells[rowIndex, 16].Value = report.TXF;
workSheet.Cells[rowIndex, 17].Value = report.QTFY;
workSheet.Cells[rowIndex, 18].Value = report.XZ;
workSheet.Cells[rowIndex, 19].Value = report.HJ;
workSheet.Cells[rowIndex, 20].Value = report.YJ;
workSheet.Cells[rowIndex, 21].Value = report.FWF;
workSheet.Cells[rowIndex, 22].Value = report.ZMML;
workSheet.Cells[rowIndex, 23].Value = report.ML;
workSheet.Cells[rowIndex, 24].Value = report.MLL;
workSheet.Cells[rowIndex, 25].Value = report.XSFYZYJ;
workSheet.Cells[rowIndex, 26].Value = report.XY;
workSheet.Cells[rowIndex, 27].Value = report.LastMonthXY;
workSheet.Cells[rowIndex, 28].Value = report.LastYearXY;
workSheet.Cells[rowIndex, 1, rowIndex, 28].Style.Border.Top.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 28].Style.Border.Left.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 28].Style.Border.Right.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 28].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 28].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
workSheet.Cells[rowIndex, 1, rowIndex, 28].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Row(rowIndex).Height = 20;
rowIndex++;
}
package.Save();
return(Redirect($"~/report/{HttpUtility.UrlEncode(fileName)}"));
}
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
return(Json(ResultUtil.Fail()));
}
}
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));
}
}
public IActionResult Template(int year, int month)
{
try
{
MonthReportSearchParam param = new MonthReportSearchParam();
param.Year = year;
param.Month = month;
var list = service.GetSecurityList(param);
string folderName = DateTime.Today.ToString("yyyyMM");
string fileName = $"证券事务部-{year}{month}.xlsx";
string baseFolderName = $"{Directory.GetCurrentDirectory()}//wwwroot//template";
if (!Directory.Exists(baseFolderName))
{
Directory.CreateDirectory(baseFolderName);
}
string savePath = $"{baseFolderName}//{fileName}";
if (System.IO.File.Exists(savePath))
{
System.IO.File.Delete(savePath);
}
using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(savePath)))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("sheet1");
workSheet.Cells.Style.Font.Name = "microsoft yahei";
workSheet.Cells.Style.Font.Size = 9;
workSheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
workSheet.Cells[1, 1, 1, 8].Style.Border.Top.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 8].Style.Border.Left.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells[1, 1, 1, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
int rowIndex = 1;
workSheet.Cells[1, 1, 1, 8].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells[1, 1, 1, 8].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
workSheet.Cells[rowIndex, 1].Value = "编号";
workSheet.Cells[rowIndex, 2].Value = "年";
workSheet.Cells[rowIndex, 3].Value = "月";
workSheet.Cells[rowIndex, 4].Value = "部门";
workSheet.Cells[rowIndex, 5].Value = "员工编号";
workSheet.Cells[rowIndex, 6].Value = "业务员";
workSheet.Cells[rowIndex, 7].Value = "职位";
workSheet.Cells[rowIndex, 8].Value = "服务费";
workSheet.Row(rowIndex).Height = 28;
rowIndex++;
foreach (var report in list)
{
workSheet.Cells[rowIndex, 1].Value = report.Id;
workSheet.Cells[rowIndex, 2].Value = report.Year;
workSheet.Cells[rowIndex, 3].Value = report.Month;
workSheet.Cells[rowIndex, 4].Value = report.DeptName;
workSheet.Cells[rowIndex, 5].Value = report.EmployeeNumber;
workSheet.Cells[rowIndex, 6].Value = report.SalerName;
workSheet.Cells[rowIndex, 7].Value = report.Job;
workSheet.Cells[rowIndex, 8].Value = report.FWF;
workSheet.Cells[rowIndex, 1, rowIndex, 8].Style.Border.Top.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 8].Style.Border.Left.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
workSheet.Cells[rowIndex, 1, rowIndex, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
workSheet.Cells[rowIndex, 1, rowIndex, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
workSheet.Row(rowIndex).Height = 24;
rowIndex++;
}
package.Save();
return(Redirect($"~/template/{HttpUtility.UrlEncode(fileName)}"));
}
}
catch (Exception ex)
{
Log.Error(ex.Message, ex);
return(Json(ResultUtil.Fail()));
}
}