MaterialBaseStockController.cs 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240
  1. using System;
  2. using System.IO;
  3. using System.Linq;
  4. using FMSAdmin.Data;
  5. using FMSAdmin.Entities;
  6. using FMSAdmin.Helpers;
  7. using FMSAdmin.Models;
  8. using FMSAdmin.Services;
  9. using Microsoft.AspNetCore.Authorization;
  10. using Microsoft.AspNetCore.Mvc;
  11. using Microsoft.Extensions.Logging;
  12. using OfficeOpenXml;
  13. using Microsoft.AspNetCore.Http;
  14. using System.Collections.Generic;
  15. using System.Drawing;
  16. using OfficeOpenXml.Style;
  17. namespace FMSAdmin.Controllers {
  18. [Authorize]
  19. [ApiController]
  20. [ApiVersion("1")]
  21. [Route("api/[controller]")]
  22. public class MaterialBaseStockController : Controller {
  23. private readonly ILogger<MaterialBaseStockController> _logger;
  24. private readonly FMSContext _context;
  25. private readonly MaterialBaseStockService _service;
  26. public MaterialBaseStockController(
  27. ILogger<MaterialBaseStockController> logger,
  28. FMSContext context,
  29. MaterialBaseStockService service
  30. ) {
  31. _logger = logger;
  32. _context = context;
  33. _service = service;
  34. }
  35. /// <summary>
  36. /// 엑셀파일 익스포트
  37. /// </summary>
  38. /// <param name="req"></param>
  39. /// <returns></returns>
  40. [HttpGet("excel")]
  41. public IActionResult ExportExcel([FromQuery] PagingRequest req, [FromQuery] bool isAdmin) {
  42. req.page = 1;
  43. req.limit = 0;
  44. var query = _FilterAndSort(req);
  45. var list_admin = query.Select(x => new {
  46. SiteName = x.CmSite.Name,
  47. BusinessFieldName = "",
  48. FirstClassName = x.FmsMaterialCodeClass.Name,
  49. SecondClassName = x.FmsMaterialCodeClass1.Name,
  50. ThirdClassName = x.FmsMaterialCodeClass2.Name,
  51. x.Name,
  52. x.Standard,
  53. x.Unit,
  54. StockCount = 0,
  55. WarehouseName = x.FmsMaterialWarehouse.Name,
  56. });
  57. var list = query.Select(x => new {
  58. BusinessFieldName = "",
  59. FirstClassName = x.FmsMaterialCodeClass.Name,
  60. SecondClassName = x.FmsMaterialCodeClass1.Name,
  61. ThirdClassName = x.FmsMaterialCodeClass2.Name,
  62. x.Name,
  63. x.Standard,
  64. x.Unit,
  65. StockCount = 0,
  66. WarehouseName = x.FmsMaterialWarehouse.Name,
  67. });
  68. var stream = new MemoryStream();
  69. using (var package = new ExcelPackage(stream)) {
  70. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  71. if (isAdmin) workSheet.Cells.LoadFromCollection(list_admin, true);
  72. else workSheet.Cells.LoadFromCollection(list, true);
  73. if (req.columns != null && req.columns.Length > 0) {
  74. //workSheet.AddStyle(req.columns, req.sort?.order?.ToLower() == "asc", filename);
  75. for (int i = 1; i <= workSheet.Dimension.End.Column; i++) {
  76. // 배경색
  77. workSheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
  78. workSheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.Orange);
  79. if (req.columns != null) {
  80. var col = req.columns.SingleOrDefault(c => c.field == workSheet.Cells[1, i].Text);
  81. if (col != null) {
  82. workSheet.Cells[1, i].Value = col.name;
  83. workSheet.Column(i).AutoFit();
  84. } else {
  85. workSheet.Column(i).Width = 0;
  86. }
  87. }
  88. }
  89. }
  90. package.Save();
  91. }
  92. stream.Position = 0;
  93. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  94. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  95. }
  96. [HttpPost]
  97. [Route("[action]")]
  98. public IActionResult ImportExcelAdmin(IFormFile excelFile) {
  99. return (ImportExcel(excelFile, true));
  100. }
  101. [HttpPost]
  102. [Route("[action]")]
  103. public IActionResult ImportExcelAuthBusiness(IFormFile excelFile) {
  104. return (ImportExcel(excelFile, false, true));
  105. }
  106. /// <summary>
  107. /// 엑셀파일 임포트
  108. /// </summary>
  109. /// <param name="file"></param>
  110. /// <returns></returns>
  111. [HttpPost]
  112. [Route("[action]")]
  113. public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false, bool authBusiness = false) {
  114. IList<MaterialBaseStock> list = new List<MaterialBaseStock>();
  115. var userInfo = _context.CmUser.Where(x => x.UserId == User.Identity.Name).SingleOrDefault();
  116. using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) {
  117. var worksheet = excelPackage.Workbook.Worksheets[0];
  118. int colCnt = isAdmin ? 10 : 9;
  119. //헤더세팅
  120. ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler();
  121. excelUploadHelper.GenerateColumns(worksheet, 1, colCnt);
  122. for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) {
  123. if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) {
  124. var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "사옥(*)", i, true) : "";
  125. var business = excelUploadHelper.GetValue(worksheet, "업무분야(*)", i, true);
  126. var class1 = excelUploadHelper.GetValue(worksheet, "자재 대분류(*)", i, true);
  127. var class2 = excelUploadHelper.GetValue(worksheet, "자재 중분류", i);
  128. var class3 = excelUploadHelper.GetValue(worksheet, "자재 소분류", i);
  129. var materialName = excelUploadHelper.GetValue(worksheet, "자재명(*)", i, true);
  130. var standard = excelUploadHelper.GetValue(worksheet, "규격", i);
  131. var unit = excelUploadHelper.GetValue(worksheet, "단위", i);
  132. var storedCount = excelUploadHelper.GetValue(worksheet, "기초수량(*)", i, true);
  133. var warehouseName = excelUploadHelper.GetValue(worksheet, "창고명칭(*)", i, true);
  134. _logger.LogInformation("site " + site);
  135. _logger.LogInformation("materilName " + materialName);
  136. var rowName = "(" + (i) + "번 행)";
  137. var siteId = isAdmin ? _context.CmSite.Where(x => x.Name == site && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId;
  138. var businessId = _context.CmBusinessField.Where(x => x.Name == business && x.IsUse == true).Select(x => x.BusinessFieldId).SingleOrDefault();
  139. var classId1 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.Name == class1 && x.ParentMaterialClassId == null).Select(x => x.MaterialClassId).SingleOrDefault();
  140. var classId2 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.ParentMaterialClassId == classId1 && x.Name == class2).Select(x => x.MaterialClassId).SingleOrDefault();
  141. var classId3 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.ParentMaterialClassId == classId2 && x.Name == class3).Select(x => x.MaterialClassId).SingleOrDefault();
  142. var material = new FmsMaterial();
  143. 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();
  144. 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();
  145. 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();
  146. var warehouse = _context.FmsMaterialWarehouse.Where(x => x.SiteId == siteId && x.BusinessFieldId == businessId && x.Name == warehouseName).SingleOrDefault();
  147. if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName);
  148. if (!isAdmin && authBusiness && businessId != userInfo.BusinessFieldId && !business.Equals("공통")) throw new Exception($"업무분야를 확인해주세요. {rowName}");
  149. excelUploadHelper.EntityIdCheck(businessId, "업무분야", rowName);
  150. excelUploadHelper.EntityIdCheck(classId1, "대분류", rowName);
  151. if (!Util.IsNullOrEmpty(material)) excelUploadHelper.EntityIdCheck(material.MaterialId, "자재명", rowName);
  152. else throw new Exception($"자재명을 확인해주세요. {rowName}");
  153. if (!Util.IsNullOrEmpty(warehouse)) excelUploadHelper.EntityIdCheck(warehouse.WarehouseId, "창고명칭", rowName);
  154. else throw new Exception($"창고명칭을 확인해주세요. {rowName}");
  155. if (!Util.IsNullOrEmpty(class2)) excelUploadHelper.EntityIdCheck(classId2, "중분류", rowName);
  156. if (!Util.IsNullOrEmpty(class3)) excelUploadHelper.EntityIdCheck(classId3, "소분류", rowName);
  157. excelUploadHelper.NumericCheck(storedCount, "기초수량", rowName);
  158. if (int.Parse(storedCount) <= 0) {
  159. throw new Exception($"기초수량은 0 이상을 입력해야 합니다. {rowName}");
  160. }
  161. //창고 - 업무분야 체크
  162. if (warehouse == null) {
  163. throw new Exception($"창고의 업무분야와 신청 업무분야와 일치하지 않습니다. {rowName}");
  164. }
  165. var baseStock = new MaterialBaseStock {
  166. SiteId = siteId,
  167. BusinessFieldId = businessId,
  168. Material = material,
  169. Warehouse = warehouse,
  170. StockCount = int.Parse(storedCount),
  171. ExcelRowNum = i
  172. };
  173. //글자수 체크
  174. //TryValidateModel(baseStock);
  175. if (!ModelState.IsValid) throw new ServiceException(string.Join(" ",
  176. ModelState.Values
  177. .SelectMany(x => x.Errors)
  178. .Select(x => x.ErrorMessage)) + rowName);
  179. list.Add(baseStock);
  180. }
  181. }
  182. }
  183. if (list.Count > 0) {
  184. _service.ImportExcel(list, User.Identity.Name);
  185. return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다.");
  186. } else {
  187. throw new ServiceException("등록할 데이터가 없습니다.");
  188. }
  189. }
  190. // 검색 & 정렬 공통
  191. private IQueryable<FmsMaterial> _FilterAndSort(PagingRequest req) {
  192. var query = _service.GetAll();
  193. // 기본 Entity 검색
  194. query = query.Filter(req.conditions);
  195. query = query.Where(x => x.IsUse == true);
  196. if (req.siteId != null) {
  197. query = query.Where(x => x.SiteId == Util.ToInt(req.siteId));
  198. }
  199. // 업무분야 필드가 있는경우만 추가
  200. if (req.businessFieldId != null) {
  201. query = query.Where(x => x.SiteId == req.siteId);
  202. query = query.Where(x => x.BusinessFieldId == req.businessFieldId || x.CmBusinessField.Name == "공통");
  203. }
  204. // 기본 Entity 정렬
  205. query = query.Sort(req.sort);
  206. return query;
  207. }
  208. }
  209. }