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 WorkBaseController : Controller { private readonly ILogger _logger; private readonly FMSContext _context; private readonly WorkBaseService _service; public WorkBaseController( ILogger logger, FMSContext context, WorkBaseService service ) { _logger = logger; _context = context; _service = service; } [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 ? 14 : 13; //헤더세팅 ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler(); excelUploadHelper.GenerateColumns(worksheet, 1, colCnt); for (int i = worksheet.Dimension.Start.Row + 2; i <= worksheet.Dimension.End.Row; i++) { if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) { var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "현장ID *(필수)", i, true) : ""; var reqUserId = excelUploadHelper.GetValue(worksheet, "작업자 ID *(필수)", i, true); var workTypeId = excelUploadHelper.GetValue(worksheet, "작업구분 *(필수)", i, true); var workDetailTypeId1 = excelUploadHelper.GetValue(worksheet, "수시계획구분", i); var workDetailTypeId2 = excelUploadHelper.GetValue(worksheet, "공사계획구분", i); var business = excelUploadHelper.GetValue(worksheet, "업무분야 *(필수)", i, true); var title = excelUploadHelper.GetValue(worksheet, "업무명 *(필수)", i, true); var content = excelUploadHelper.GetValue(worksheet, "업무(신청)내용", i); var facilityName = excelUploadHelper.GetValue(worksheet, "대상설비", i); var partnerName = excelUploadHelper.GetValue(worksheet, "업체명", i); var workPrice = excelUploadHelper.GetValue(worksheet, "금액", i); var workUser = excelUploadHelper.GetValue(worksheet, "작업자 *(필수)", i, true); var workStartDate = excelUploadHelper.GetValue(worksheet, "작업일시 시작일 *(필수)", i, true).Replace("-", ""); var workEndDate = excelUploadHelper.GetValue(worksheet, "작업일시 종료일 *(필수)", i, true).Replace("-", ""); _logger.LogInformation("site " + site); _logger.LogInformation("worEndDate : " + workEndDate); var rowName = "(" + (i) + "번 행)"; var siteId = isAdmin ? _context.CmSite.Where(x => x.SiteId == Util.ToInt(site) && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId; var businessId = _context.CmBusinessField.Where(x => x.BusinessFieldId == Util.ToInt(business) && x.IsUse == true).Select(x => x.BusinessFieldId).SingleOrDefault(); var reqUser = _context.CmUser.Where(x => x.UserId == reqUserId && x.IsUse == true).Select(x => x.Name).SingleOrDefault(); var workType = _context.FmsWorkCodeType.Where(x => x.WorkTypeId == Util.ToInt(workTypeId)).Select(x => x.Name).SingleOrDefault(); var partner = (partnerName != null) ? _context.CmPartner.Where(x => x.SiteId == siteId && x.Name == partnerName).Select(x => x.PartnerId).ToList() : null; if (partner != null && partner.Count > 1) { throw new ServiceException($"등록된 업체명 중 동일한 업체명이 2개 이상 있습니다. {rowName}"); } var partnerId = (partnerName != null) ? _context.CmPartner.Where(x => x.SiteId == siteId && x.Name == partnerName).Select(x => x.PartnerId).SingleOrDefault() : 0; // 계획구분 (수시(5), 공사(6)) int? workDetailTypeId = null; if (workTypeId == "5") { string[] tmpA = { "41", "42" }; workDetailTypeId = (Util.S(workDetailTypeId1) == "" || !tmpA.Contains(workDetailTypeId1)) ? 42 : Util.ToInt(workDetailTypeId1); } else if (workTypeId == "6") { string[] tmpA = { "43", "44", "45", "46", "47", "48" }; workDetailTypeId = (Util.S(workDetailTypeId2) == "" || !tmpA.Contains(workDetailTypeId2)) ? 48 : Util.ToInt(workDetailTypeId2); } if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName); excelUploadHelper.EntityIdCheck(reqUser, "작업자ID", rowName); excelUploadHelper.EntityIdCheck(workType, "작업구분", rowName); excelUploadHelper.NumericCheck(workTypeId, "작업구분", rowName); excelUploadHelper.EntityIdCheck(businessId, "업무분야", rowName); excelUploadHelper.NumericCheck(business, "업무분야", rowName); excelUploadHelper.EntityIdCheck(title, "업무명", rowName); excelUploadHelper.EntityIdCheck(workUser, "작업자", rowName); excelUploadHelper.NumericCheck(workPrice, "금액", rowName); workStartDate = excelUploadHelper.DateTimeCheck(workStartDate, "작업일시 시작일", rowName); workEndDate = excelUploadHelper.DateTimeCheck(workEndDate, "작업일시 종료일", rowName); if (partnerName != null) { excelUploadHelper.EntityIdCheck(partnerId, "업체명", rowName); } var baseRequest = new FmsWorkRequest { SiteId = siteId, WorkTypeId = Util.ToInt(workTypeId), WorkDetailTypeId = workDetailTypeId, BusinessFieldId = businessId, WorkProgressId = 1, Title = title, Content = content, RequestUserId = reqUserId, RequestDate = DateTime.Parse(workStartDate), StartWorkDate = DateTime.Parse(workStartDate), WorkUsers = workUser, WorkPartnerId = partnerId, WorkEndDate = DateTime.Parse(workEndDate), }; if (workPrice != null) baseRequest.WorkPrice = Util.ToInt(workPrice); if (facilityName != null) { var arrFacilityName = facilityName.Split(','); for (var arrNum = 0; arrNum < arrFacilityName.Length; arrNum++) { var facilityCode = _context.CmFacility.Where(x => x.SiteId == siteId && x.Name == arrFacilityName[arrNum] && x.IsUse == true).Select(x => x.FacilityCode).SingleOrDefault(); excelUploadHelper.EntityIdCheck(facilityCode, "설비명", rowName); baseRequest.FmsWorkRequestToFacility.Add(new FmsWorkRequestToFacility() { SiteId = siteId, FacilityCode = facilityCode, }); } } if (!ModelState.IsValid) throw new ServiceException(string.Join(" ", ModelState.Values .SelectMany(x => x.Errors) .Select(x => x.ErrorMessage)) + rowName); list.Add(baseRequest); } } } if (list.Count > 0) { _service.ImportExcel(list, User.Identity.Name); return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다."); } else { throw new ServiceException("등록할 데이터가 없습니다."); } } } }