MaterialCodeClassController.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360
  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. namespace FMSAdmin.Controllers {
  16. [Authorize]
  17. [ApiController]
  18. [ApiVersion("1")]
  19. [Route("api/[controller]")]
  20. public class MaterialCodeClassController : Controller {
  21. private readonly ILogger<MaterialCodeClassController> _logger;
  22. private readonly FMSContext _context;
  23. private readonly MaterialCodeClassService _service;
  24. public MaterialCodeClassController(
  25. ILogger<MaterialCodeClassController> logger,
  26. FMSContext context,
  27. MaterialCodeClassService service
  28. ) {
  29. _logger = logger;
  30. _context = context;
  31. _service = service;
  32. }
  33. /// <summary>
  34. /// 목록
  35. /// </summary>
  36. [HttpGet]
  37. //public IActionResult List([FromQuery]PagingRequest req, [FromQuery]string siteId, [FromQuery]string depth) {
  38. public IActionResult List([FromQuery] PagingRequest req, [FromQuery] string depth) {
  39. var query = _FilterAndSort(req);
  40. /*
  41. if (!string.IsNullOrEmpty(siteId)) {
  42. query = query.Where(x => x.SiteId == Util.ToInt(siteId));
  43. }
  44. */
  45. if (!string.IsNullOrEmpty(depth)) {
  46. query = query.Where(x => x.Depth == Util.ToInt(depth));
  47. }
  48. var list = _ListSelect(query);
  49. var paging = PagingList.Pagination(list, req.page, req.limit);
  50. return Ok(paging);
  51. }
  52. /// <summary>
  53. /// 조회
  54. /// </summary>
  55. //[HttpGet("{siteId}/{id}/{depth}")]
  56. [HttpGet("{id}/{depth}")]
  57. public IActionResult Get(int id, int depth) {
  58. var data = _service.Gets(id, depth);
  59. if (data.Count() == 0) {
  60. return NotFound("자재 분류 코드 정보를 찾을 수 없습니다.");
  61. }
  62. var item = data.Select(x => new {
  63. x.MaterialClassId,
  64. /*
  65. x.SiteId,
  66. CmSite = new {
  67. x.Site.Name,
  68. },
  69. */
  70. x.ParentMaterialClassId,
  71. x.Depth,
  72. x.Name,
  73. x.Abbreviation,
  74. x.IsUse,
  75. });
  76. return Ok(item.First());
  77. }
  78. /// <summary>
  79. /// 엑셀파일 익스포트
  80. /// </summary>
  81. /// <param name="req"></param>
  82. /// <returns></returns>
  83. [HttpGet("excel")]
  84. public IActionResult ExportExcel([FromQuery] PagingRequest req, string filename) {
  85. req.page = 1;
  86. req.limit = 0;
  87. var query = _FilterAndSort(req);
  88. var list = query.Select(x => new {
  89. //x.SiteId,
  90. x.MaterialClassId,
  91. //SiteName = x.Site.Name,
  92. ClassName1 = x.Depth == 0 ? x.Name : x.Depth == 1 ? x.FmsMaterialCodeClass2.Name : x.FmsMaterialCodeClass2.FmsMaterialCodeClass2.Name,
  93. ClassName2 = x.Depth == 1 ? x.Name : x.Depth == 2 ? x.FmsMaterialCodeClass2.Name : "",
  94. ClassName3 = x.Depth == 2 ? x.Name : "",
  95. });
  96. var stream = new MemoryStream();
  97. using (var package = new ExcelPackage(stream)) {
  98. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  99. workSheet.Cells.LoadFromCollection(list, true);
  100. if (req.columns != null && req.columns.Length > 0) {
  101. workSheet.AddStyle(req.columns, req.sort?.order?.ToLower() == "asc", filename);
  102. }
  103. package.Save();
  104. }
  105. stream.Position = 0;
  106. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  107. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  108. }
  109. [HttpPost]
  110. [Route("[action]")]
  111. public IActionResult ImportExcelAdmin(IFormFile excelFile) {
  112. return (ImportExcel(excelFile, true));
  113. }
  114. /// <summary>
  115. /// 엑셀파일 임포트
  116. /// </summary>
  117. /// <param name="file"></param>
  118. /// <returns></returns>
  119. [HttpPost]
  120. [Route("[action]")]
  121. public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false) {
  122. IList<FmsMaterialCodeClass> list = new List<FmsMaterialCodeClass>();
  123. var userInfo = _context.CmUser.Where(x => x.UserId == User.Identity.Name).SingleOrDefault();
  124. using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) {
  125. var worksheet = excelPackage.Workbook.Worksheets[0];
  126. int colCnt = isAdmin ? 4 : 3;
  127. //헤더세팅
  128. ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler();
  129. excelUploadHelper.GenerateColumns(worksheet, 1, colCnt);
  130. for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) {
  131. if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) {
  132. //var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "사이트명(*)", i, true) : "";
  133. var class1 = excelUploadHelper.GetValue(worksheet, "자재 대분류(*)", i, true);
  134. var class2 = excelUploadHelper.GetValue(worksheet, "자재 중분류", i);
  135. var class3 = excelUploadHelper.GetValue(worksheet, "자재 소분류", i);
  136. //var isUse = excelUploadHelper.GetValue(worksheet, "사용여부(*)[사용 or 미사용]", i, true);
  137. //_logger.LogInformation("site " + site);
  138. var rowName = "(" + (i) + "번 행)";
  139. //var siteId = isAdmin ? _context.CmSite.Where(x => x.Name == site && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId;
  140. var classId1 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.Name == class1 && x.ParentMaterialClassId == null).Select(x => x.MaterialClassId).SingleOrDefault();
  141. var classId2 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.ParentMaterialClassId == classId1 && x.Name == class2).Select(x => x.MaterialClassId).SingleOrDefault();
  142. //if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사이트명", rowName);
  143. //if (!isUse.Equals("사용") && !isUse.Equals("미사용")) throw new Exception($"사용여부를 정확히 입력해야합니다.[사용 or 미사용] {rowName}");
  144. var depth = (class2 == "" || class2 == null) ? 0 : (class3 == "" || class3 == null) ? 1 : 2;
  145. var name = depth == 0 ? class1 : depth == 1 ? class2 : class3;
  146. var materialCodeClass = new FmsMaterialCodeClass();
  147. materialCodeClass = new FmsMaterialCodeClass {
  148. //SiteId = siteId,
  149. Depth = depth,
  150. Name = name,
  151. IsUse = true,
  152. ExcelRowNum = i
  153. };
  154. if (depth == 1) {
  155. if (classId1 != 0) {
  156. materialCodeClass.ParentMaterialClassId = classId1;
  157. } else {
  158. materialCodeClass.FmsMaterialCodeClass2 = new FmsMaterialCodeClass {
  159. //SiteId = siteId,
  160. Depth = 0,
  161. Name = class1,
  162. Abbreviation = rowName,
  163. IsUse = true
  164. };
  165. }
  166. } else if (depth == 2) {
  167. if (classId1 != 0 && classId2 != 0) {
  168. materialCodeClass.ParentMaterialClassId = classId2;
  169. } else {
  170. materialCodeClass.FmsMaterialCodeClass2 = new FmsMaterialCodeClass {
  171. //SiteId = siteId,
  172. Depth = 1,
  173. Name = class2,
  174. Abbreviation = rowName,
  175. IsUse = true,
  176. FmsMaterialCodeClass2 = new FmsMaterialCodeClass {
  177. //SiteId = siteId,
  178. Depth = 0,
  179. Name = class1,
  180. IsUse = true
  181. }
  182. };
  183. }
  184. }
  185. var check = 0;
  186. if (depth == 0) check = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.Depth == depth && x.Name == name).Count();
  187. else check = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.Depth == depth && x.ParentMaterialClassId == materialCodeClass.ParentMaterialClassId && x.Name == name).Count();
  188. if (check > 0) {
  189. throw new ServiceException("기존 분류코드와 동일 정보가 존재합니다." + rowName);
  190. }
  191. //글자수 체크
  192. TryValidateModel(materialCodeClass);
  193. if (!ModelState.IsValid) throw new ServiceException(string.Join(" ",
  194. ModelState.Values
  195. .SelectMany(x => x.Errors)
  196. .Select(x => x.ErrorMessage)) + rowName);
  197. //중복체크
  198. var overlapRowNum = list.Where(x => /*x.SiteId == materialCodeClass.SiteId
  199. && */x.ParentMaterialClassId == materialCodeClass.ParentMaterialClassId
  200. && x.Depth == materialCodeClass.Depth
  201. && x.Name == materialCodeClass.Name).Select(x => x.ExcelRowNum).SingleOrDefault();
  202. if (overlapRowNum != 0)
  203. throw new ServiceException("파일 내 동일 정보가 존재합니다." + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)");
  204. list.Add(materialCodeClass);
  205. }
  206. }
  207. }
  208. if (list.Count > 0) {
  209. _service.ImportExcel(list);
  210. return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다.");
  211. } else {
  212. throw new ServiceException("등록할 데이터가 없습니다.");
  213. }
  214. }
  215. /// <summary>
  216. /// 수정
  217. /// </summary>
  218. //[HttpPost("{siteId}/{id}")]
  219. [HttpPost("{id}")]
  220. public IActionResult Edit(int id, [FromBody] FmsMaterialCodeClass data) {
  221. if (id != data.MaterialClassId) {
  222. return BadRequest("잘못된 접근입니다.");
  223. }
  224. if (ModelState.IsValid) {
  225. _service.Edit(id, data);
  226. } else {
  227. return BadRequest(ModelState);
  228. }
  229. return Ok();
  230. }
  231. /// <summary>
  232. /// 등록
  233. /// </summary>
  234. [HttpPut]
  235. public IActionResult Create([FromBody] FmsMaterialCodeClass data) {
  236. _logger.LogInformation("Create");
  237. //_logger.LogInformation(data.Dump());
  238. if (ModelState.IsValid) {
  239. _service.Create(data);
  240. } else {
  241. return BadRequest(ModelState);
  242. }
  243. return Ok();
  244. }
  245. /// <summary>
  246. /// 삭제
  247. /// </summary>
  248. //[HttpDelete("{siteId}/{id}/{depth}")]
  249. [HttpDelete("{id}/{depth}")]
  250. public IActionResult Delete(int id, int depth) {
  251. _service.Delete(id, depth);
  252. return Ok();
  253. }
  254. // 셀렉트 공통
  255. private dynamic _ListSelect(IQueryable<FmsMaterialCodeClass> query) {
  256. var list = query.Select(x => new {
  257. /*
  258. x.SiteId,
  259. SiteName = x.Site.Name,
  260. */
  261. x.MaterialClassId,
  262. x.ParentMaterialClassId,
  263. x.Depth,
  264. x.Name,
  265. x.Abbreviation,
  266. x.IsUse
  267. });
  268. return list;
  269. }
  270. // 검색 & 정렬 공통
  271. private IQueryable<FmsMaterialCodeClass> _FilterAndSort(PagingRequest req) {
  272. var query = _service.GetAll();
  273. // 기본 Entity 검색
  274. query = query.Filter(req.conditions);
  275. query = query.Where(x => x.IsUse == true);
  276. /*
  277. if (req.siteId != null) {
  278. query = query.Where(x => x.SiteId == Util.ToInt(req.siteId));
  279. }
  280. */
  281. // 기본 Entity 정렬
  282. query = query.Sort(req.sort);
  283. return query;
  284. }
  285. /// <summary>
  286. /// 자식 노드 리스트
  287. /// </summary>
  288. /// <param name="parentId"></param>
  289. /// <param name="type"></param>
  290. /// <returns></returns>
  291. [AllowAnonymous]
  292. [HttpGet("childs/{parentId?}")]
  293. public IActionResult Childs(int? parentId) {
  294. var query = _service.GetAll().Where(x => x.ParentMaterialClassId == parentId);
  295. // 최대 3뎁스
  296. var list = query.OrderBy(x => x.Name).Select(c => new {
  297. id = c.MaterialClassId,
  298. name = c.Name,
  299. children = c.FmsMaterialCodeClass1.OrderBy(x => x.Name).Select(c1 => new {
  300. id = c1.MaterialClassId,
  301. name = c1.Name,
  302. children = c1.FmsMaterialCodeClass1.OrderBy(x => x.Name).Select(c2 => new {
  303. id = c2.MaterialClassId,
  304. name = c2.Name,
  305. })
  306. })
  307. });
  308. return Ok(list);
  309. }
  310. }
  311. }