using System; using System.IO; using System.Linq; using FMSAdmin.Data; using FMSAdmin.Entities; using FMSAdmin.Helpers; using FMSAdmin.Models; using FMSAdmin.Services; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using OfficeOpenXml; using Microsoft.AspNetCore.Http; using System.Collections.Generic; using System.Drawing; using OfficeOpenXml.Style; namespace FMSAdmin.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/[controller]")] public class MaterialBaseStockController : Controller { private readonly ILogger _logger; private readonly FMSContext _context; private readonly MaterialBaseStockService _service; public MaterialBaseStockController( ILogger logger, FMSContext context, MaterialBaseStockService service ) { _logger = logger; _context = context; _service = service; } /// /// 엑셀파일 익스포트 /// /// /// [HttpGet("excel")] public IActionResult ExportExcel([FromQuery] PagingRequest req, [FromQuery] bool isAdmin) { req.page = 1; req.limit = 0; var query = _FilterAndSort(req); var list_admin = query.Select(x => new { SiteName = x.CmSite.Name, BusinessFieldName = "", FirstClassName = x.FmsMaterialCodeClass.Name, SecondClassName = x.FmsMaterialCodeClass1.Name, ThirdClassName = x.FmsMaterialCodeClass2.Name, x.Name, x.Standard, x.Unit, StockCount = 0, WarehouseName = x.FmsMaterialWarehouse.Name, }); var list = query.Select(x => new { BusinessFieldName = "", FirstClassName = x.FmsMaterialCodeClass.Name, SecondClassName = x.FmsMaterialCodeClass1.Name, ThirdClassName = x.FmsMaterialCodeClass2.Name, x.Name, x.Standard, x.Unit, StockCount = 0, WarehouseName = x.FmsMaterialWarehouse.Name, }); var stream = new MemoryStream(); using (var package = new ExcelPackage(stream)) { var workSheet = package.Workbook.Worksheets.Add("Sheet1"); if (isAdmin) workSheet.Cells.LoadFromCollection(list_admin, true); else workSheet.Cells.LoadFromCollection(list, true); if (req.columns != null && req.columns.Length > 0) { //workSheet.AddStyle(req.columns, req.sort?.order?.ToLower() == "asc", filename); for (int i = 1; i <= workSheet.Dimension.End.Column; i++) { // 배경색 workSheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid; workSheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.Orange); if (req.columns != null) { var col = req.columns.SingleOrDefault(c => c.field == workSheet.Cells[1, i].Text); if (col != null) { workSheet.Cells[1, i].Value = col.name; workSheet.Column(i).AutoFit(); } else { workSheet.Column(i).Width = 0; } } } } package.Save(); } stream.Position = 0; string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx"; return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName); } [HttpPost] [Route("[action]")] public IActionResult ImportExcelAdmin(IFormFile excelFile) { return (ImportExcel(excelFile, true)); } [HttpPost] [Route("[action]")] public IActionResult ImportExcelAuthBusiness(IFormFile excelFile) { return (ImportExcel(excelFile, false, true)); } /// /// 엑셀파일 임포트 /// /// /// [HttpPost] [Route("[action]")] public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false, bool authBusiness = false) { IList list = new List(); var userInfo = _context.CmUser.Where(x => x.UserId == User.Identity.Name).SingleOrDefault(); using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) { var worksheet = excelPackage.Workbook.Worksheets[0]; int colCnt = isAdmin ? 10 : 9; //헤더세팅 ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler(); excelUploadHelper.GenerateColumns(worksheet, 1, colCnt); for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) { if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) { var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "사옥(*)", i, true) : ""; var business = excelUploadHelper.GetValue(worksheet, "업무분야(*)", i, true); var class1 = excelUploadHelper.GetValue(worksheet, "자재 대분류(*)", i, true); var class2 = excelUploadHelper.GetValue(worksheet, "자재 중분류", i); var class3 = excelUploadHelper.GetValue(worksheet, "자재 소분류", i); var materialName = excelUploadHelper.GetValue(worksheet, "자재명(*)", i, true); var standard = excelUploadHelper.GetValue(worksheet, "규격", i); var unit = excelUploadHelper.GetValue(worksheet, "단위", i); var storedCount = excelUploadHelper.GetValue(worksheet, "기초수량(*)", i, true); var warehouseName = excelUploadHelper.GetValue(worksheet, "창고명칭(*)", i, true); _logger.LogInformation("site " + site); _logger.LogInformation("materilName " + materialName); var rowName = "(" + (i) + "번 행)"; var siteId = isAdmin ? _context.CmSite.Where(x => x.Name == site && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId; var businessId = _context.CmBusinessField.Where(x => x.Name == business && x.IsUse == true).Select(x => x.BusinessFieldId).SingleOrDefault(); var classId1 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.Name == class1 && x.ParentMaterialClassId == null).Select(x => x.MaterialClassId).SingleOrDefault(); var classId2 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.ParentMaterialClassId == classId1 && x.Name == class2).Select(x => x.MaterialClassId).SingleOrDefault(); var classId3 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.ParentMaterialClassId == classId2 && x.Name == class3).Select(x => x.MaterialClassId).SingleOrDefault(); var material = new FmsMaterial(); if (classId2 != 0 && classId3 != 0) material = _context.FmsMaterial.Where(x => x.SiteId == siteId && x.IsUse == true && x.FirstClassId == classId1 && x.SecondClassId == classId2 && x.ThirdClassId == classId3 && x.Name == materialName && x.Standard == standard).SingleOrDefault(); else if (classId2 != 0 && classId3 == 0) material = _context.FmsMaterial.Where(x => x.SiteId == siteId && x.IsUse == true && x.FirstClassId == classId1 && x.SecondClassId == classId2 && x.ThirdClassId == null && x.Name == materialName && x.Standard == standard).SingleOrDefault(); else material = _context.FmsMaterial.Where(x => x.SiteId == siteId && x.IsUse == true && x.FirstClassId == classId1 && x.SecondClassId == null && x.ThirdClassId == null && x.Name == materialName && x.Standard == standard).SingleOrDefault(); var warehouse = _context.FmsMaterialWarehouse.Where(x => x.SiteId == siteId && x.BusinessFieldId == businessId && x.Name == warehouseName).SingleOrDefault(); if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName); if (!isAdmin && authBusiness && businessId != userInfo.BusinessFieldId && !business.Equals("공통")) throw new Exception($"업무분야를 확인해주세요. {rowName}"); excelUploadHelper.EntityIdCheck(businessId, "업무분야", rowName); excelUploadHelper.EntityIdCheck(classId1, "대분류", rowName); if (!Util.IsNullOrEmpty(material)) excelUploadHelper.EntityIdCheck(material.MaterialId, "자재명", rowName); else throw new Exception($"자재명을 확인해주세요. {rowName}"); if (!Util.IsNullOrEmpty(warehouse)) excelUploadHelper.EntityIdCheck(warehouse.WarehouseId, "창고명칭", rowName); else throw new Exception($"창고명칭을 확인해주세요. {rowName}"); if (!Util.IsNullOrEmpty(class2)) excelUploadHelper.EntityIdCheck(classId2, "중분류", rowName); if (!Util.IsNullOrEmpty(class3)) excelUploadHelper.EntityIdCheck(classId3, "소분류", rowName); excelUploadHelper.NumericCheck(storedCount, "기초수량", rowName); if (int.Parse(storedCount) <= 0) { throw new Exception($"기초수량은 0 이상을 입력해야 합니다. {rowName}"); } //창고 - 업무분야 체크 if (warehouse == null) { throw new Exception($"창고의 업무분야와 신청 업무분야와 일치하지 않습니다. {rowName}"); } var baseStock = new MaterialBaseStock { SiteId = siteId, BusinessFieldId = businessId, Material = material, Warehouse = warehouse, StockCount = int.Parse(storedCount), ExcelRowNum = i }; //글자수 체크 //TryValidateModel(baseStock); if (!ModelState.IsValid) throw new ServiceException(string.Join(" ", ModelState.Values .SelectMany(x => x.Errors) .Select(x => x.ErrorMessage)) + rowName); list.Add(baseStock); } } } if (list.Count > 0) { _service.ImportExcel(list, User.Identity.Name); return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다."); } else { throw new ServiceException("등록할 데이터가 없습니다."); } } // 검색 & 정렬 공통 private IQueryable _FilterAndSort(PagingRequest req) { var query = _service.GetAll(); // 기본 Entity 검색 query = query.Filter(req.conditions); query = query.Where(x => x.IsUse == true); if (req.siteId != null) { query = query.Where(x => x.SiteId == Util.ToInt(req.siteId)); } // 업무분야 필드가 있는경우만 추가 if (req.businessFieldId != null) { query = query.Where(x => x.SiteId == req.siteId); query = query.Where(x => x.BusinessFieldId == req.businessFieldId || x.CmBusinessField.Name == "공통"); } // 기본 Entity 정렬 query = query.Sort(req.sort); return query; } } }