WorkBaseController.cs 9.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175
  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 WorkBaseController : Controller {
  23. private readonly ILogger<WorkBaseController> _logger;
  24. private readonly FMSContext _context;
  25. private readonly WorkBaseService _service;
  26. public WorkBaseController(
  27. ILogger<WorkBaseController> logger,
  28. FMSContext context,
  29. WorkBaseService service
  30. ) {
  31. _logger = logger;
  32. _context = context;
  33. _service = service;
  34. }
  35. [HttpPost]
  36. [Route("[action]")]
  37. public IActionResult ImportExcelAdmin(IFormFile excelFile) {
  38. return (ImportExcel(excelFile, true));
  39. }
  40. [HttpPost]
  41. [Route("[action]")]
  42. public IActionResult ImportExcelAuthBusiness(IFormFile excelFile) {
  43. return (ImportExcel(excelFile, false, true));
  44. }
  45. /// <summary>
  46. /// 엑셀파일 임포트
  47. /// </summary>
  48. /// <param name="file"></param>
  49. /// <returns></returns>
  50. [HttpPost]
  51. [Route("[action]")]
  52. public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false, bool authBusiness = false) {
  53. IList<FmsWorkRequest> list = new List<FmsWorkRequest>();
  54. var userInfo = _context.CmUser.Where(x => x.UserId == User.Identity.Name).SingleOrDefault();
  55. using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) {
  56. var worksheet = excelPackage.Workbook.Worksheets[0];
  57. int colCnt = isAdmin ? 14 : 13;
  58. //헤더세팅
  59. ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler();
  60. excelUploadHelper.GenerateColumns(worksheet, 1, colCnt);
  61. for (int i = worksheet.Dimension.Start.Row + 2; i <= worksheet.Dimension.End.Row; i++) {
  62. if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) {
  63. var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "현장ID *(필수)", i, true) : "";
  64. var reqUserId = excelUploadHelper.GetValue(worksheet, "작업자 ID *(필수)", i, true);
  65. var workTypeId = excelUploadHelper.GetValue(worksheet, "작업구분 *(필수)", i, true);
  66. var workDetailTypeId1 = excelUploadHelper.GetValue(worksheet, "수시계획구분", i);
  67. var workDetailTypeId2 = excelUploadHelper.GetValue(worksheet, "공사계획구분", i);
  68. var business = excelUploadHelper.GetValue(worksheet, "업무분야 *(필수)", i, true);
  69. var title = excelUploadHelper.GetValue(worksheet, "업무명 *(필수)", i, true);
  70. var content = excelUploadHelper.GetValue(worksheet, "업무(신청)내용", i);
  71. var facilityName = excelUploadHelper.GetValue(worksheet, "대상설비", i);
  72. var partnerName = excelUploadHelper.GetValue(worksheet, "업체명", i);
  73. var workPrice = excelUploadHelper.GetValue(worksheet, "금액", i);
  74. var workUser = excelUploadHelper.GetValue(worksheet, "작업자 *(필수)", i, true);
  75. var workStartDate = excelUploadHelper.GetValue(worksheet, "작업일시 시작일 *(필수)", i, true).Replace("-", "");
  76. var workEndDate = excelUploadHelper.GetValue(worksheet, "작업일시 종료일 *(필수)", i, true).Replace("-", "");
  77. _logger.LogInformation("site " + site);
  78. _logger.LogInformation("worEndDate : " + workEndDate);
  79. var rowName = "(" + (i) + "번 행)";
  80. var siteId = isAdmin ? _context.CmSite.Where(x => x.SiteId == Util.ToInt(site) && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId;
  81. var businessId = _context.CmBusinessField.Where(x => x.BusinessFieldId == Util.ToInt(business) && x.IsUse == true).Select(x => x.BusinessFieldId).SingleOrDefault();
  82. var reqUser = _context.CmUser.Where(x => x.UserId == reqUserId && x.IsUse == true).Select(x => x.Name).SingleOrDefault();
  83. var workType = _context.FmsWorkCodeType.Where(x => x.WorkTypeId == Util.ToInt(workTypeId)).Select(x => x.Name).SingleOrDefault();
  84. var partner = (partnerName != null) ? _context.CmPartner.Where(x => x.SiteId == siteId && x.Name == partnerName).Select(x => x.PartnerId).ToList() : null;
  85. if (partner != null && partner.Count > 1) {
  86. throw new ServiceException($"등록된 업체명 중 동일한 업체명이 2개 이상 있습니다. {rowName}");
  87. }
  88. var partnerId = (partnerName != null) ? _context.CmPartner.Where(x => x.SiteId == siteId && x.Name == partnerName).Select(x => x.PartnerId).SingleOrDefault() : 0;
  89. // 계획구분 (수시(5), 공사(6))
  90. int? workDetailTypeId = null;
  91. if (workTypeId == "5") {
  92. string[] tmpA = { "41", "42" };
  93. workDetailTypeId = (Util.S(workDetailTypeId1) == "" || !tmpA.Contains(workDetailTypeId1)) ? 42 : Util.ToInt(workDetailTypeId1);
  94. } else if (workTypeId == "6") {
  95. string[] tmpA = { "43", "44", "45", "46", "47", "48" };
  96. workDetailTypeId = (Util.S(workDetailTypeId2) == "" || !tmpA.Contains(workDetailTypeId2)) ? 48 : Util.ToInt(workDetailTypeId2);
  97. }
  98. if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName);
  99. excelUploadHelper.EntityIdCheck(reqUser, "작업자ID", rowName);
  100. excelUploadHelper.EntityIdCheck(workType, "작업구분", rowName);
  101. excelUploadHelper.NumericCheck(workTypeId, "작업구분", rowName);
  102. excelUploadHelper.EntityIdCheck(businessId, "업무분야", rowName);
  103. excelUploadHelper.NumericCheck(business, "업무분야", rowName);
  104. excelUploadHelper.EntityIdCheck(title, "업무명", rowName);
  105. excelUploadHelper.EntityIdCheck(workUser, "작업자", rowName);
  106. excelUploadHelper.NumericCheck(workPrice, "금액", rowName);
  107. workStartDate = excelUploadHelper.DateTimeCheck(workStartDate, "작업일시 시작일", rowName);
  108. workEndDate = excelUploadHelper.DateTimeCheck(workEndDate, "작업일시 종료일", rowName);
  109. if (partnerName != null) {
  110. excelUploadHelper.EntityIdCheck(partnerId, "업체명", rowName);
  111. }
  112. var baseRequest = new FmsWorkRequest {
  113. SiteId = siteId,
  114. WorkTypeId = Util.ToInt(workTypeId),
  115. WorkDetailTypeId = workDetailTypeId,
  116. BusinessFieldId = businessId,
  117. WorkProgressId = 1,
  118. Title = title,
  119. Content = content,
  120. RequestUserId = reqUserId,
  121. RequestDate = DateTime.Parse(workStartDate),
  122. StartWorkDate = DateTime.Parse(workStartDate),
  123. WorkUsers = workUser,
  124. WorkPartnerId = partnerId,
  125. WorkEndDate = DateTime.Parse(workEndDate),
  126. };
  127. if (workPrice != null) baseRequest.WorkPrice = Util.ToInt(workPrice);
  128. if (facilityName != null) {
  129. var arrFacilityName = facilityName.Split(',');
  130. for (var arrNum = 0; arrNum < arrFacilityName.Length; arrNum++) {
  131. var facilityCode = _context.CmFacility.Where(x => x.SiteId == siteId && x.Name == arrFacilityName[arrNum] && x.IsUse == true).Select(x => x.FacilityCode).SingleOrDefault();
  132. excelUploadHelper.EntityIdCheck(facilityCode, "설비명", rowName);
  133. baseRequest.FmsWorkRequestToFacility.Add(new FmsWorkRequestToFacility() {
  134. SiteId = siteId,
  135. FacilityCode = facilityCode,
  136. });
  137. }
  138. }
  139. if (!ModelState.IsValid) throw new ServiceException(string.Join(" ",
  140. ModelState.Values
  141. .SelectMany(x => x.Errors)
  142. .Select(x => x.ErrorMessage)) + rowName);
  143. list.Add(baseRequest);
  144. }
  145. }
  146. }
  147. if (list.Count > 0) {
  148. _service.ImportExcel(list, User.Identity.Name);
  149. return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다.");
  150. } else {
  151. throw new ServiceException("등록할 데이터가 없습니다.");
  152. }
  153. }
  154. }
  155. }