123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175 |
- 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<WorkBaseController> _logger;
- private readonly FMSContext _context;
- private readonly WorkBaseService _service;
- public WorkBaseController(
- ILogger<WorkBaseController> 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));
- }
- /// <summary>
- /// 엑셀파일 임포트
- /// </summary>
- /// <param name="file"></param>
- /// <returns></returns>
- [HttpPost]
- [Route("[action]")]
- public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false, bool authBusiness = false) {
- IList<FmsWorkRequest> list = new List<FmsWorkRequest>();
- 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("등록할 데이터가 없습니다.");
- }
- }
- }
- }
|