FacilityCodeClassController.cs 14 KB

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