123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240 |
- 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 MaterialBaseStockController : Controller {
- private readonly ILogger<MaterialBaseStockController> _logger;
- private readonly FMSContext _context;
- private readonly MaterialBaseStockService _service;
- public MaterialBaseStockController(
- ILogger<MaterialBaseStockController> logger,
- FMSContext context,
- MaterialBaseStockService service
- ) {
- _logger = logger;
- _context = context;
- _service = service;
- }
- /// <summary>
- /// 엑셀파일 익스포트
- /// </summary>
- /// <param name="req"></param>
- /// <returns></returns>
- [HttpGet("excel")]
- public IActionResult ExportExcel([FromQuery] PagingRequest req, [FromQuery] bool isAdmin) {
- req.page = 1;
- req.limit = 0;
- var query = _FilterAndSort(req);
- var list_admin = query.Select(x => new {
- SiteName = x.CmSite.Name,
- BusinessFieldName = "",
- FirstClassName = x.FmsMaterialCodeClass.Name,
- SecondClassName = x.FmsMaterialCodeClass1.Name,
- ThirdClassName = x.FmsMaterialCodeClass2.Name,
- x.Name,
- x.Standard,
- x.Unit,
- StockCount = 0,
- WarehouseName = x.FmsMaterialWarehouse.Name,
- });
- var list = query.Select(x => new {
- BusinessFieldName = "",
- FirstClassName = x.FmsMaterialCodeClass.Name,
- SecondClassName = x.FmsMaterialCodeClass1.Name,
- ThirdClassName = x.FmsMaterialCodeClass2.Name,
- x.Name,
- x.Standard,
- x.Unit,
- StockCount = 0,
- WarehouseName = x.FmsMaterialWarehouse.Name,
- });
- var stream = new MemoryStream();
- using (var package = new ExcelPackage(stream)) {
- var workSheet = package.Workbook.Worksheets.Add("Sheet1");
- if (isAdmin) workSheet.Cells.LoadFromCollection(list_admin, true);
- else workSheet.Cells.LoadFromCollection(list, true);
- if (req.columns != null && req.columns.Length > 0) {
- //workSheet.AddStyle(req.columns, req.sort?.order?.ToLower() == "asc", filename);
- for (int i = 1; i <= workSheet.Dimension.End.Column; i++) {
- // 배경색
- workSheet.Cells[1, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
- workSheet.Cells[1, i].Style.Fill.BackgroundColor.SetColor(Color.Orange);
- if (req.columns != null) {
- var col = req.columns.SingleOrDefault(c => c.field == workSheet.Cells[1, i].Text);
- if (col != null) {
- workSheet.Cells[1, i].Value = col.name;
- workSheet.Column(i).AutoFit();
- } else {
- workSheet.Column(i).Width = 0;
- }
- }
- }
- }
- package.Save();
- }
- stream.Position = 0;
- string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
- return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
- }
- [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<MaterialBaseStock> list = new List<MaterialBaseStock>();
- 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 ? 10 : 9;
- //헤더세팅
- ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler();
- excelUploadHelper.GenerateColumns(worksheet, 1, colCnt);
- for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) {
- if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) {
- var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "사옥(*)", i, true) : "";
- var business = excelUploadHelper.GetValue(worksheet, "업무분야(*)", i, true);
- var class1 = excelUploadHelper.GetValue(worksheet, "자재 대분류(*)", i, true);
- var class2 = excelUploadHelper.GetValue(worksheet, "자재 중분류", i);
- var class3 = excelUploadHelper.GetValue(worksheet, "자재 소분류", i);
- var materialName = excelUploadHelper.GetValue(worksheet, "자재명(*)", i, true);
- var standard = excelUploadHelper.GetValue(worksheet, "규격", i);
- var unit = excelUploadHelper.GetValue(worksheet, "단위", i);
- var storedCount = excelUploadHelper.GetValue(worksheet, "기초수량(*)", i, true);
- var warehouseName = excelUploadHelper.GetValue(worksheet, "창고명칭(*)", i, true);
- _logger.LogInformation("site " + site);
- _logger.LogInformation("materilName " + materialName);
- var rowName = "(" + (i) + "번 행)";
- var siteId = isAdmin ? _context.CmSite.Where(x => x.Name == site && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId;
- var businessId = _context.CmBusinessField.Where(x => x.Name == business && x.IsUse == true).Select(x => x.BusinessFieldId).SingleOrDefault();
- var classId1 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.Name == class1 && x.ParentMaterialClassId == null).Select(x => x.MaterialClassId).SingleOrDefault();
- var classId2 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.ParentMaterialClassId == classId1 && x.Name == class2).Select(x => x.MaterialClassId).SingleOrDefault();
- var classId3 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.ParentMaterialClassId == classId2 && x.Name == class3).Select(x => x.MaterialClassId).SingleOrDefault();
- var material = new FmsMaterial();
- if (classId2 != 0 && classId3 != 0) material = _context.FmsMaterial.Where(x => x.SiteId == siteId && x.IsUse == true && x.FirstClassId == classId1 && x.SecondClassId == classId2 && x.ThirdClassId == classId3 && x.Name == materialName && x.Standard == standard).SingleOrDefault();
- else if (classId2 != 0 && classId3 == 0) material = _context.FmsMaterial.Where(x => x.SiteId == siteId && x.IsUse == true && x.FirstClassId == classId1 && x.SecondClassId == classId2 && x.ThirdClassId == null && x.Name == materialName && x.Standard == standard).SingleOrDefault();
- else material = _context.FmsMaterial.Where(x => x.SiteId == siteId && x.IsUse == true && x.FirstClassId == classId1 && x.SecondClassId == null && x.ThirdClassId == null && x.Name == materialName && x.Standard == standard).SingleOrDefault();
- var warehouse = _context.FmsMaterialWarehouse.Where(x => x.SiteId == siteId && x.BusinessFieldId == businessId && x.Name == warehouseName).SingleOrDefault();
- if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName);
- if (!isAdmin && authBusiness && businessId != userInfo.BusinessFieldId && !business.Equals("공통")) throw new Exception($"업무분야를 확인해주세요. {rowName}");
- excelUploadHelper.EntityIdCheck(businessId, "업무분야", rowName);
- excelUploadHelper.EntityIdCheck(classId1, "대분류", rowName);
- if (!Util.IsNullOrEmpty(material)) excelUploadHelper.EntityIdCheck(material.MaterialId, "자재명", rowName);
- else throw new Exception($"자재명을 확인해주세요. {rowName}");
- if (!Util.IsNullOrEmpty(warehouse)) excelUploadHelper.EntityIdCheck(warehouse.WarehouseId, "창고명칭", rowName);
- else throw new Exception($"창고명칭을 확인해주세요. {rowName}");
- if (!Util.IsNullOrEmpty(class2)) excelUploadHelper.EntityIdCheck(classId2, "중분류", rowName);
- if (!Util.IsNullOrEmpty(class3)) excelUploadHelper.EntityIdCheck(classId3, "소분류", rowName);
- excelUploadHelper.NumericCheck(storedCount, "기초수량", rowName);
- if (int.Parse(storedCount) <= 0) {
- throw new Exception($"기초수량은 0 이상을 입력해야 합니다. {rowName}");
- }
- //창고 - 업무분야 체크
- if (warehouse == null) {
- throw new Exception($"창고의 업무분야와 신청 업무분야와 일치하지 않습니다. {rowName}");
- }
- var baseStock = new MaterialBaseStock {
- SiteId = siteId,
- BusinessFieldId = businessId,
- Material = material,
- Warehouse = warehouse,
- StockCount = int.Parse(storedCount),
- ExcelRowNum = i
- };
- //글자수 체크
- //TryValidateModel(baseStock);
- if (!ModelState.IsValid) throw new ServiceException(string.Join(" ",
- ModelState.Values
- .SelectMany(x => x.Errors)
- .Select(x => x.ErrorMessage)) + rowName);
- list.Add(baseStock);
- }
- }
- }
- if (list.Count > 0) {
- _service.ImportExcel(list, User.Identity.Name);
- return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다.");
- } else {
- throw new ServiceException("등록할 데이터가 없습니다.");
- }
- }
- // 검색 & 정렬 공통
- private IQueryable<FmsMaterial> _FilterAndSort(PagingRequest req) {
- var query = _service.GetAll();
- // 기본 Entity 검색
- query = query.Filter(req.conditions);
- query = query.Where(x => x.IsUse == true);
- if (req.siteId != null) {
- query = query.Where(x => x.SiteId == Util.ToInt(req.siteId));
- }
- // 업무분야 필드가 있는경우만 추가
- if (req.businessFieldId != null) {
- query = query.Where(x => x.SiteId == req.siteId);
- query = query.Where(x => x.BusinessFieldId == req.businessFieldId || x.CmBusinessField.Name == "공통");
- }
- // 기본 Entity 정렬
- query = query.Sort(req.sort);
- return query;
- }
- }
- }
|