using System; using System.Linq; using System.Threading.Tasks; using FMSAdmin.Data; using FMSAdmin.Models; using FMSAdmin.Entities; using FMSAdmin.Services; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using Microsoft.AspNetCore.Authorization; using Microsoft.EntityFrameworkCore; using FMSAdmin.Helpers; using System.Data; using System.IO; using OfficeOpenXml; using Newtonsoft.Json; using Microsoft.AspNetCore.Http; using System.Collections.Generic; using System.Collections; using OfficeOpenXml.Style; using System.Drawing; using System.IO.Compression; namespace FMSAdmin.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/[controller]")] public class MaterialController : Controller { private readonly ILogger _logger; private readonly FMSContext _context; private readonly MaterialService _service; private readonly StorageHelper _storage; private readonly QrCodeHelper _QrCode; public MaterialController( ILogger logger, FMSContext context, MaterialService service, StorageHelper storage, QrCodeHelper QrCode ) { _logger = logger; _context = context; _service = service; _storage = storage; _QrCode = QrCode; } /// /// 리스트 /// /// /// /// [HttpGet] public IActionResult List([FromQuery] PagingRequest req) { var query = _FilterAndSort(req); var list = _ListSelect(query); var paging = PagingList.Pagination(list, req.page, req.limit); return Ok(paging); } /// /// 엑셀파일 익스포트 /// /// /// [HttpGet("excel")] public IActionResult ExportExcel([FromQuery] PagingRequest req, string filename) { req.page = 1; req.limit = 0; var query = _FilterAndSort(req); var list = _ListSelect(query); var stream = new MemoryStream(); using (var package = new ExcelPackage(stream)) { var workSheet = package.Workbook.Worksheets.Add("Sheet1"); workSheet.Cells.LoadFromCollection(list, true); if (req.columns != null && req.columns.Length > 0) { workSheet.AddStyle(req.columns, req.sort != null ? req.sort.order.ToLower() == "asc" : true, filename); } 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)); } /// /// 엑셀파일 임포트 /// /// /// [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(); var useMaterialType = _context.SiteConfig.Single().UseMaterialType; var useMaterialReasonableStockCount = _context.SiteConfig.Single().UseMaterialReasonableStockCount; using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) { var worksheet = excelPackage.Workbook.Worksheets[0]; int colCnt = isAdmin ? 14 : 13; if (!useMaterialType) colCnt -= 1; //헤더세팅 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 name = 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 type = useMaterialType ? excelUploadHelper.GetValue(worksheet, "자재구분(*)", i, true) : ""; //현대해상 자재qr코드 숨김처리 //var qrCode = excelUploadHelper.GetValue(worksheet, "QR코드", i); var unit = excelUploadHelper.GetValue(worksheet, "단위", i); var durableYears = excelUploadHelper.GetValue(worksheet, "내용연수", i); var reasonableStockCount = useMaterialReasonableStockCount ? excelUploadHelper.GetValue(worksheet, "적정재고량", i) : null; var manufacturer = excelUploadHelper.GetValue(worksheet, "제조사", i); var standard = excelUploadHelper.GetValue(worksheet, "규격", i); var finalPrice = excelUploadHelper.GetValue(worksheet, "단가", i); var note = excelUploadHelper.GetValue(worksheet, "비고", i); //var isDiscontinued = excelUploadHelper.GetValue(worksheet, "단종여부(*)[예 or 아니오]", i, true); //var isUse = excelUploadHelper.GetValue(worksheet, "사용여부(*)[사용 or 미사용]", i, true); _logger.LogInformation("site " + site); _logger.LogInformation("type " + type); _logger.LogInformation("codnamee " + name); 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 typeId = useMaterialType ? _context.FmsMaterialCodeType.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.Name == type).Select(x => x.MaterialTypeId).SingleOrDefault() : _context.FmsMaterialCodeType.Where(x => /*x.SiteId == siteId && */x.IsUse == true).Select(x => x.MaterialTypeId).FirstOrDefault(); var classId1 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.Name == class1 && x.ParentMaterialClassId == null).Select(x => x.MaterialClassId).SingleOrDefault(); var classId2 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.ParentMaterialClassId == classId1 && x.Name == class2).Select(x => x.MaterialClassId).SingleOrDefault(); var classId3 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.ParentMaterialClassId == classId2 && x.Name == class3).Select(x => x.MaterialClassId).SingleOrDefault(); var materialCode = class1; if (!Util.IsNullOrEmpty(class2)) materialCode += "-" + class2; if (!Util.IsNullOrEmpty(class2)) materialCode += "-" + class3; if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName); if (!isAdmin && authBusiness && businessId != userInfo.BusinessFieldId && !business.Equals("공통")) throw new Exception($"업무분야를 확인해주세요. {rowName}"); excelUploadHelper.EntityIdCheck(businessId, "업무분야", rowName); excelUploadHelper.EntityIdCheck(typeId, "자재구분", rowName); excelUploadHelper.EntityIdCheck(classId1, "대분류", rowName); if (!Util.IsNullOrEmpty(class2)) excelUploadHelper.EntityIdCheck(classId2, "중분류", rowName); if (!Util.IsNullOrEmpty(class3)) excelUploadHelper.EntityIdCheck(classId3, "소분류", rowName); excelUploadHelper.NumericCheck(durableYears, "내용연수", rowName); excelUploadHelper.NumericCheck(reasonableStockCount, "적정재고량", rowName); excelUploadHelper.NumericCheck(finalPrice, "단가", rowName); //if (!isDiscontinued.Equals("예") && !isDiscontinued.Equals("아니오")) throw new Exception($"단종여부를 정확히 입력해야합니다.[예 or 아니오] {rowName}"); //if (!isUse.Equals("사용") && !isUse.Equals("미사용")) throw new Exception($"사용여부를 정확히 입력해야합니다.[사용 or 미사용] {rowName}"); var material = new FmsMaterial { SiteId = siteId, BusinessFieldId = businessId, MaterialCode = materialCode, Name = name, FirstClassId = classId1, MaterialTypeId = typeId, Rfid = "", //qrCode, Unit = unit, Manufacturer = manufacturer, Standard = standard, Note = note, //IsDiscontinued = isDiscontinued.Equals("아니오") ? false : true, //IsUse = isUse.Equals("미사용") ? false : true, IsDiscontinued = false, IsUse = true, ExcelRowNum = i }; //if (durableYears != null) material.DurableYears = short.Parse(durableYears); if (reasonableStockCount != null) material.ReasonableStockCount = short.Parse(reasonableStockCount); if (finalPrice != null) material.FinalPrice = int.Parse(finalPrice); if (classId2 != 0) material.SecondClassId = classId2; if (classId3 != 0) material.ThirdClassId = classId3; //글자수 체크 TryValidateModel(material); if (!ModelState.IsValid) throw new ServiceException(string.Join(" ", ModelState.Values .SelectMany(x => x.Errors) .Select(x => x.ErrorMessage)) + rowName); //중복체크 var overlapRowNum = list.Where(x => x.SiteId == material.SiteId & x.BusinessFieldId == material.BusinessFieldId && x.Name == material.Name && x.Standard == material.Standard).Select(x => x.ExcelRowNum).SingleOrDefault(); if (overlapRowNum != 0) throw new ServiceException("파일 내 동일 정보가 존재합니다.[업무분야-자재명-규격]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)"); //현대해상 자재qr코드 숨김처리 //overlapRowNum = list.Where(x => x.Rfid == material.Rfid && Util.IsNullOrEmpty(material.Rfid) == false).Select(x => x.ExcelRowNum).SingleOrDefault(); // if (overlapRowNum != 0) // throw new ServiceException("파일 내 동일 정보가 존재합니다.[QR코드]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)"); if (_service.checkName(material, true) > 0) { throw new ServiceException("기존 자재 정보와 동일 정보가 존재합니다.[업무분야-자재명-규격]" + rowName); } //현대해상 자재qr코드 숨김처리 // if (!Util.IsNullOrEmpty(material.Rfid) && _service.checkRfid(material, true) > 0) // throw new ServiceException("기존 자재 정보와 동일 정보가 존재합니다.[QR코드]" + rowName); list.Add(material); } } } if (list.Count > 0) { _service.ImportExcel(list); return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다."); } else { throw new ServiceException("등록할 데이터가 없습니다."); } } /// /// 조회 /// /// /// /// [HttpGet("{siteId}/{id}")] public IActionResult Get(int siteId, int id) { var data = _service.Gets(id, siteId); if (data.Count() == 0) { return NotFound("자재정보를 찾을 수 없습니다."); } var item = data.Select(x => new { x.MaterialId, x.SiteId, CmSite = new { x.CmSite.Name, }, x.BusinessFieldId, CmBusinessField = new { x.CmBusinessField.Name, }, x.MaterialTypeId, FmsMaterialCodeType = new { x.FmsMaterialCodeType.Name, }, x.FirstClassId, FmsMaterialCodeClass = new { x.FmsMaterialCodeClass.MaterialClassId, x.FmsMaterialCodeClass.Name, }, x.SecondClassId, FmsMaterialCodeClass1 = new { x.FmsMaterialCodeClass1.MaterialClassId, x.FmsMaterialCodeClass1.Name, }, x.ThirdClassId, FmsMaterialCodeClass2 = new { x.FmsMaterialCodeClass2.MaterialClassId, x.FmsMaterialCodeClass2.Name, }, x.MaterialCode, CmFile = _storage.ParseFile(x.CmFile), x.Name, x.Standard, x.Unit, x.Rfid, x.Manufacturer, x.DurableYears, x.FinalPrice, x.ReasonableStockCount, x.IsDiscontinued, x.IsUse, x.Note, }); return Ok(item.First()); } /// /// 수정 /// /// /// /// /// [HttpPost("{siteId}/{id}")] public IActionResult Edit(int siteId, int id, [FromBody] FmsMaterial data) { if (id != data.MaterialId) { return BadRequest("잘못된 접근입니다."); } if (ModelState.IsValid) { _service.Edit(id, siteId, data); } else { return BadRequest(ModelState); } return Ok(); } /// /// 등록 /// /// /// [HttpPut] public IActionResult Create([FromBody] FmsMaterial data) { _logger.LogInformation("Create"); //_logger.LogInformation(data.Dump()); if (ModelState.IsValid) { _service.Create(data); } else { return BadRequest(ModelState); } return Ok(); } /// /// 삭제 /// /// /// /// [HttpDelete("{siteId}/{id}")] public IActionResult Delete(int siteId, int id) { _service.Delete(id, siteId); return Ok(); } // 셀렉트 공통 private dynamic _ListSelect(IQueryable query) { var list = query.Select(x => new { MaterialId = x.Material.MaterialId, SiteId = x.Material.SiteId, SiteName = x.Material.CmSite.Name, BusinessFieldId = x.Material.BusinessFieldId, BusinessFieldName = x.Material.CmBusinessField.Name, FirstClassId = x.Material.FirstClassId, FirstClassName = x.Material.FmsMaterialCodeClass.Name, SecondClassId = x.Material.SecondClassId, SecondClassName = x.Material.FmsMaterialCodeClass1.Name, ThirdClassId = x.Material.ThirdClassId, ThirdClassName = x.Material.FmsMaterialCodeClass2.Name, Name = x.Material.Name, Standard = x.Material.Standard, Unit = x.Material.Unit, FinalPrice = x.Material.FinalPrice, ReasonableStockCount = x.Material.ReasonableStockCount, QrCode = x.Material.Rfid, DurableYears = x.Material.DurableYears, Manufacturer = x.Material.Manufacturer, Note = x.Material.Note, IsDiscontinued = x.Material.IsDiscontinued.GetValueOrDefault() ? "Y" : "N", IsUse = x.Material.IsUse ? "Y" : "N", MaterialCode = x.Material.MaterialCode, MaterialCount = 1, //자재팝업에서 기본값 //StockCountByPoint = Util.ToInt(x.FmsMaterialStored.Where(f => f.IsApproval == true).Sum(f => f.StoredCount)) + Util.ToInt(x.FmsMaterialRelease.Where(f => f.IsConfirmed == true).Sum(f => f.AdjustmentTypeId == 2 ? f.MaterialCount : (-1 * f.MaterialCount))), //출고 - 재고수량 //StockCountByPoint = Util.ToInt(x.FmsMaterialStored.Where(f => f.IsApproval == true).OrderByDescending(f => f.StoredDate).Select(f => f.StockCount).FirstOrDefault()) + Util.ToInt(x.FmsMaterialRelease.Where(f => f.IsConfirmed == true).Sum(f => f.AdjustmentTypeId == 2 ? f.MaterialCount : (-1 * f.MaterialCount))), //출고 - 재고수량 //기존 Bems에 맞춤 x.StockCount, x.StockAmount }); return list; } // 검색 & 정렬 공통 private IQueryable _FilterAndSort(PagingRequest req) { //var query = _service.GetAll(); var query = _service.Stock(); // 기본 Entity 검색 query = query.Filter(req.conditions); if (req.siteId != null) { query = query.Where(x => x.Material.SiteId == Util.ToInt(req.siteId)); } if (req.siteIds != null && req.siteIds.Length > 0) { query = query.Where(x => req.siteIds.Contains(x.Material.SiteId)); } // 업무분야 필드가 있는경우만 추가 if (req.businessFieldId != null) { query = query.Where(x => x.Material.SiteId == req.siteId); query = query.Where(x => x.Material.BusinessFieldId == req.businessFieldId || x.Material.CmBusinessField.Name == "공통"); } // 고정정렬 (사옥 준공순) var sortQuery = query.OrderBy(x => x.Material.CmSite.SortOrderNo); // 기본 Entity 정렬 sortQuery = sortQuery.ThenSort(req.sort); // 추가 정렬 if (req.sort.field == "BusinessAndClass") { // 기본 커스텀 정렬 (업무분야,대중소,명칭) sortQuery = sortQuery.ThenBy(x => x.Material.BusinessFieldId); sortQuery = sortQuery.ThenBy(x => x.Material.FmsMaterialCodeClass.Name); sortQuery = sortQuery.ThenBy(x => x.Material.FmsMaterialCodeClass1.Name); sortQuery = sortQuery.ThenBy(x => x.Material.FmsMaterialCodeClass2.Name); sortQuery = sortQuery.ThenBy(x => x.Material.Name); } else { sortQuery = sortQuery.ThenSort(req.sort); } return sortQuery; } [HttpGet("qr")] public IActionResult QRDown([FromQuery] PagingRequest req) { req.page = 1; req.limit = 0; var query = _FilterAndSort(req); var Folder = DateTime.Now.ToString("yyyyMMddhhmmsss"); var FileName = "자재 QR코드_" + Folder; var downFile = "/files/temp/qr/" + FileName + ".zip"; var list = query.Where(x => x.Material.Rfid != null && x.Material.Rfid != "" && x.Material.IsUse == true).Select(x => new { x.Material.SiteId, x.Material.MaterialId, x.Material.Rfid }); if (list.Count() == 0) throw new ServiceException("다운로드 가능한 QR코드가 없습니다."); using (MemoryStream newZip = new MemoryStream()) { //using (FileStream newZip = new FileStream(@"c:\users\exampleuser\release.zip", FileMode.CreateNew)) { using (ZipArchive archive = new ZipArchive(newZip, ZipArchiveMode.Create)) { foreach (var item in list) { archive.CreateEntryFromFile(_storage.GetLocalPath($"/qr/material/{item.SiteId}_{item.MaterialId}.jpg"), $"{item.SiteId}_{item.MaterialId}.jpg"); } } return File(newZip.ToArray(), "application/zip", FileName); } } [HttpGet("QrCodeRegen")] [AllowAnonymous] public IActionResult QrCodeRegen() { var query = _service.GetAll(); var list = query.Where(x => x.Rfid != null && x.Rfid != "").Select(x => new { x.SiteId, x.MaterialId, x.Rfid, }); foreach (var item in list) { _QrCode.Save(item.Rfid, $"{item.SiteId}_{item.MaterialId}", "Material"); } return Content(":)"); } } }