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())); } }