MaterialController.cs 23 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494
  1. using System;
  2. using System.Linq;
  3. using System.Threading.Tasks;
  4. using FMSAdmin.Data;
  5. using FMSAdmin.Models;
  6. using FMSAdmin.Entities;
  7. using FMSAdmin.Services;
  8. using Microsoft.AspNetCore.Mvc;
  9. using Microsoft.Extensions.Logging;
  10. using Microsoft.AspNetCore.Authorization;
  11. using Microsoft.EntityFrameworkCore;
  12. using FMSAdmin.Helpers;
  13. using System.Data;
  14. using System.IO;
  15. using OfficeOpenXml;
  16. using Newtonsoft.Json;
  17. using Microsoft.AspNetCore.Http;
  18. using System.Collections.Generic;
  19. using System.Collections;
  20. using OfficeOpenXml.Style;
  21. using System.Drawing;
  22. using System.IO.Compression;
  23. namespace FMSAdmin.Controllers {
  24. [Authorize]
  25. [ApiController]
  26. [ApiVersion("1")]
  27. [Route("api/[controller]")]
  28. public class MaterialController : Controller {
  29. private readonly ILogger<MaterialController> _logger;
  30. private readonly FMSContext _context;
  31. private readonly MaterialService _service;
  32. private readonly StorageHelper _storage;
  33. private readonly QrCodeHelper _QrCode;
  34. public MaterialController(
  35. ILogger<MaterialController> logger,
  36. FMSContext context,
  37. MaterialService service,
  38. StorageHelper storage,
  39. QrCodeHelper QrCode
  40. ) {
  41. _logger = logger;
  42. _context = context;
  43. _service = service;
  44. _storage = storage;
  45. _QrCode = QrCode;
  46. }
  47. /// <summary>
  48. /// 리스트
  49. /// </summary>
  50. /// <param name="req"></param>
  51. /// <param name="siteId"></param>
  52. /// <returns></returns>
  53. [HttpGet]
  54. public IActionResult List([FromQuery] PagingRequest req) {
  55. var query = _FilterAndSort(req);
  56. var list = _ListSelect(query);
  57. var paging = PagingList.Pagination(list, req.page, req.limit);
  58. return Ok(paging);
  59. }
  60. /// <summary>
  61. /// 엑셀파일 익스포트
  62. /// </summary>
  63. /// <param name="req"></param>
  64. /// <returns></returns>
  65. [HttpGet("excel")]
  66. public IActionResult ExportExcel([FromQuery] PagingRequest req, string filename) {
  67. req.page = 1;
  68. req.limit = 0;
  69. var query = _FilterAndSort(req);
  70. var list = _ListSelect(query);
  71. var stream = new MemoryStream();
  72. using (var package = new ExcelPackage(stream)) {
  73. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  74. workSheet.Cells.LoadFromCollection(list, true);
  75. if (req.columns != null && req.columns.Length > 0) {
  76. workSheet.AddStyle(req.columns, req.sort != null ? req.sort.order.ToLower() == "asc" : true, filename);
  77. }
  78. package.Save();
  79. }
  80. stream.Position = 0;
  81. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  82. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  83. }
  84. [HttpPost]
  85. [Route("[action]")]
  86. public IActionResult ImportExcelAdmin(IFormFile excelFile) {
  87. return (ImportExcel(excelFile, true));
  88. }
  89. [HttpPost]
  90. [Route("[action]")]
  91. public IActionResult ImportExcelAuthBusiness(IFormFile excelFile) {
  92. return (ImportExcel(excelFile, false, true));
  93. }
  94. /// <summary>
  95. /// 엑셀파일 임포트
  96. /// </summary>
  97. /// <param name="file"></param>
  98. /// <returns></returns>
  99. [HttpPost]
  100. [Route("[action]")]
  101. public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false, bool authBusiness = false) {
  102. IList<FmsMaterial> list = new List<FmsMaterial>();
  103. var userInfo = _context.CmUser.Where(x => x.UserId == User.Identity.Name).SingleOrDefault();
  104. var useMaterialType = _context.SiteConfig.Single().UseMaterialType;
  105. var useMaterialReasonableStockCount = _context.SiteConfig.Single().UseMaterialReasonableStockCount;
  106. using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) {
  107. var worksheet = excelPackage.Workbook.Worksheets[0];
  108. int colCnt = isAdmin ? 14 : 13;
  109. if (!useMaterialType) colCnt -= 1;
  110. //헤더세팅
  111. ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler();
  112. excelUploadHelper.GenerateColumns(worksheet, 1, colCnt);
  113. for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) {
  114. if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) {
  115. var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "사옥(*)", i, true) : "";
  116. var business = excelUploadHelper.GetValue(worksheet, "업무분야(*)", i, true);
  117. var name = excelUploadHelper.GetValue(worksheet, "자재명(*)", i, true);
  118. var class1 = excelUploadHelper.GetValue(worksheet, "자재 대분류(*)", i, true);
  119. var class2 = excelUploadHelper.GetValue(worksheet, "자재 중분류", i);
  120. var class3 = excelUploadHelper.GetValue(worksheet, "자재 소분류", i);
  121. var type = useMaterialType ? excelUploadHelper.GetValue(worksheet, "자재구분(*)", i, true) : "";
  122. //현대해상 자재qr코드 숨김처리
  123. //var qrCode = excelUploadHelper.GetValue(worksheet, "QR코드", i);
  124. var unit = excelUploadHelper.GetValue(worksheet, "단위", i);
  125. var durableYears = excelUploadHelper.GetValue(worksheet, "내용연수", i);
  126. var reasonableStockCount = useMaterialReasonableStockCount ? excelUploadHelper.GetValue(worksheet, "적정재고량", i) : null;
  127. var manufacturer = excelUploadHelper.GetValue(worksheet, "제조사", i);
  128. var standard = excelUploadHelper.GetValue(worksheet, "규격", i);
  129. var finalPrice = excelUploadHelper.GetValue(worksheet, "단가", i);
  130. var note = excelUploadHelper.GetValue(worksheet, "비고", i);
  131. //var isDiscontinued = excelUploadHelper.GetValue(worksheet, "단종여부(*)[예 or 아니오]", i, true);
  132. //var isUse = excelUploadHelper.GetValue(worksheet, "사용여부(*)[사용 or 미사용]", i, true);
  133. _logger.LogInformation("site " + site);
  134. _logger.LogInformation("type " + type);
  135. _logger.LogInformation("codnamee " + name);
  136. var rowName = "(" + (i) + "번 행)";
  137. var siteId = isAdmin ? _context.CmSite.Where(x => x.Name == site && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId;
  138. var businessId = _context.CmBusinessField.Where(x => x.Name == business && x.IsUse == true).Select(x => x.BusinessFieldId).SingleOrDefault();
  139. var typeId = useMaterialType ? _context.FmsMaterialCodeType.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.Name == type).Select(x => x.MaterialTypeId).SingleOrDefault()
  140. : _context.FmsMaterialCodeType.Where(x => /*x.SiteId == siteId && */x.IsUse == true).Select(x => x.MaterialTypeId).FirstOrDefault();
  141. var classId1 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.Name == class1 && x.ParentMaterialClassId == null).Select(x => x.MaterialClassId).SingleOrDefault();
  142. var classId2 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.ParentMaterialClassId == classId1 && x.Name == class2).Select(x => x.MaterialClassId).SingleOrDefault();
  143. var classId3 = _context.FmsMaterialCodeClass.Where(x => /*x.SiteId == siteId && */x.IsUse == true && x.ParentMaterialClassId == classId2 && x.Name == class3).Select(x => x.MaterialClassId).SingleOrDefault();
  144. var materialCode = class1;
  145. if (!Util.IsNullOrEmpty(class2)) materialCode += "-" + class2;
  146. if (!Util.IsNullOrEmpty(class2)) materialCode += "-" + class3;
  147. if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName);
  148. if (!isAdmin && authBusiness && businessId != userInfo.BusinessFieldId && !business.Equals("공통")) throw new Exception($"업무분야를 확인해주세요. {rowName}");
  149. excelUploadHelper.EntityIdCheck(businessId, "업무분야", rowName);
  150. excelUploadHelper.EntityIdCheck(typeId, "자재구분", rowName);
  151. excelUploadHelper.EntityIdCheck(classId1, "대분류", rowName);
  152. if (!Util.IsNullOrEmpty(class2)) excelUploadHelper.EntityIdCheck(classId2, "중분류", rowName);
  153. if (!Util.IsNullOrEmpty(class3)) excelUploadHelper.EntityIdCheck(classId3, "소분류", rowName);
  154. excelUploadHelper.NumericCheck(durableYears, "내용연수", rowName);
  155. excelUploadHelper.NumericCheck(reasonableStockCount, "적정재고량", rowName);
  156. excelUploadHelper.NumericCheck(finalPrice, "단가", rowName);
  157. //if (!isDiscontinued.Equals("예") && !isDiscontinued.Equals("아니오")) throw new Exception($"단종여부를 정확히 입력해야합니다.[예 or 아니오] {rowName}");
  158. //if (!isUse.Equals("사용") && !isUse.Equals("미사용")) throw new Exception($"사용여부를 정확히 입력해야합니다.[사용 or 미사용] {rowName}");
  159. var material = new FmsMaterial {
  160. SiteId = siteId,
  161. BusinessFieldId = businessId,
  162. MaterialCode = materialCode,
  163. Name = name,
  164. FirstClassId = classId1,
  165. MaterialTypeId = typeId,
  166. Rfid = "", //qrCode,
  167. Unit = unit,
  168. Manufacturer = manufacturer,
  169. Standard = standard,
  170. Note = note,
  171. //IsDiscontinued = isDiscontinued.Equals("아니오") ? false : true,
  172. //IsUse = isUse.Equals("미사용") ? false : true,
  173. IsDiscontinued = false,
  174. IsUse = true,
  175. ExcelRowNum = i
  176. };
  177. //if (durableYears != null) material.DurableYears = short.Parse(durableYears);
  178. if (reasonableStockCount != null) material.ReasonableStockCount = short.Parse(reasonableStockCount);
  179. if (finalPrice != null) material.FinalPrice = int.Parse(finalPrice);
  180. if (classId2 != 0) material.SecondClassId = classId2;
  181. if (classId3 != 0) material.ThirdClassId = classId3;
  182. //글자수 체크
  183. TryValidateModel(material);
  184. if (!ModelState.IsValid) throw new ServiceException(string.Join(" ",
  185. ModelState.Values
  186. .SelectMany(x => x.Errors)
  187. .Select(x => x.ErrorMessage)) + rowName);
  188. //중복체크
  189. var overlapRowNum = list.Where(x => x.SiteId == material.SiteId
  190. & x.BusinessFieldId == material.BusinessFieldId
  191. && x.Name == material.Name
  192. && x.Standard == material.Standard).Select(x => x.ExcelRowNum).SingleOrDefault();
  193. if (overlapRowNum != 0)
  194. throw new ServiceException("파일 내 동일 정보가 존재합니다.[업무분야-자재명-규격]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)");
  195. //현대해상 자재qr코드 숨김처리
  196. //overlapRowNum = list.Where(x => x.Rfid == material.Rfid && Util.IsNullOrEmpty(material.Rfid) == false).Select(x => x.ExcelRowNum).SingleOrDefault();
  197. // if (overlapRowNum != 0)
  198. // throw new ServiceException("파일 내 동일 정보가 존재합니다.[QR코드]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)");
  199. if (_service.checkName(material, true) > 0) {
  200. throw new ServiceException("기존 자재 정보와 동일 정보가 존재합니다.[업무분야-자재명-규격]" + rowName);
  201. }
  202. //현대해상 자재qr코드 숨김처리
  203. // if (!Util.IsNullOrEmpty(material.Rfid) && _service.checkRfid(material, true) > 0)
  204. // throw new ServiceException("기존 자재 정보와 동일 정보가 존재합니다.[QR코드]" + rowName);
  205. list.Add(material);
  206. }
  207. }
  208. }
  209. if (list.Count > 0) {
  210. _service.ImportExcel(list);
  211. return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다.");
  212. } else {
  213. throw new ServiceException("등록할 데이터가 없습니다.");
  214. }
  215. }
  216. /// <summary>
  217. /// 조회
  218. /// </summary>
  219. /// <param name="siteId"></param>
  220. /// <param name="id"></param>
  221. /// <returns></returns>
  222. [HttpGet("{siteId}/{id}")]
  223. public IActionResult Get(int siteId, int id) {
  224. var data = _service.Gets(id, siteId);
  225. if (data.Count() == 0) {
  226. return NotFound("자재정보를 찾을 수 없습니다.");
  227. }
  228. var item = data.Select(x => new {
  229. x.MaterialId,
  230. x.SiteId,
  231. CmSite = new {
  232. x.CmSite.Name,
  233. },
  234. x.BusinessFieldId,
  235. CmBusinessField = new {
  236. x.CmBusinessField.Name,
  237. },
  238. x.MaterialTypeId,
  239. FmsMaterialCodeType = new {
  240. x.FmsMaterialCodeType.Name,
  241. },
  242. x.FirstClassId,
  243. FmsMaterialCodeClass = new {
  244. x.FmsMaterialCodeClass.MaterialClassId,
  245. x.FmsMaterialCodeClass.Name,
  246. },
  247. x.SecondClassId,
  248. FmsMaterialCodeClass1 = new {
  249. x.FmsMaterialCodeClass1.MaterialClassId,
  250. x.FmsMaterialCodeClass1.Name,
  251. },
  252. x.ThirdClassId,
  253. FmsMaterialCodeClass2 = new {
  254. x.FmsMaterialCodeClass2.MaterialClassId,
  255. x.FmsMaterialCodeClass2.Name,
  256. },
  257. x.MaterialCode,
  258. CmFile = _storage.ParseFile(x.CmFile),
  259. x.Name,
  260. x.Standard,
  261. x.Unit,
  262. x.Rfid,
  263. x.Manufacturer,
  264. x.DurableYears,
  265. x.FinalPrice,
  266. x.ReasonableStockCount,
  267. x.IsDiscontinued,
  268. x.IsUse,
  269. x.Note,
  270. });
  271. return Ok(item.First());
  272. }
  273. /// <summary>
  274. /// 수정
  275. /// </summary>
  276. /// <param name="siteId"></param>
  277. /// <param name="id"></param>
  278. /// <param name="data"></param>
  279. /// <returns></returns>
  280. [HttpPost("{siteId}/{id}")]
  281. public IActionResult Edit(int siteId, int id, [FromBody] FmsMaterial data) {
  282. if (id != data.MaterialId) {
  283. return BadRequest("잘못된 접근입니다.");
  284. }
  285. if (ModelState.IsValid) {
  286. _service.Edit(id, siteId, data);
  287. } else {
  288. return BadRequest(ModelState);
  289. }
  290. return Ok();
  291. }
  292. /// <summary>
  293. /// 등록
  294. /// </summary>
  295. /// <param name="data"></param>
  296. /// <returns></returns>
  297. [HttpPut]
  298. public IActionResult Create([FromBody] FmsMaterial data) {
  299. _logger.LogInformation("Create");
  300. //_logger.LogInformation(data.Dump());
  301. if (ModelState.IsValid) {
  302. _service.Create(data);
  303. } else {
  304. return BadRequest(ModelState);
  305. }
  306. return Ok();
  307. }
  308. /// <summary>
  309. /// 삭제
  310. /// </summary>
  311. /// <param name="siteId"></param>
  312. /// <param name="id"></param>
  313. /// <returns></returns>
  314. [HttpDelete("{siteId}/{id}")]
  315. public IActionResult Delete(int siteId, int id) {
  316. _service.Delete(id, siteId);
  317. return Ok();
  318. }
  319. // 셀렉트 공통
  320. private dynamic _ListSelect(IQueryable<MaterialStock> query) {
  321. var list = query.Select(x => new {
  322. MaterialId = x.Material.MaterialId,
  323. SiteId = x.Material.SiteId,
  324. SiteName = x.Material.CmSite.Name,
  325. BusinessFieldId = x.Material.BusinessFieldId,
  326. BusinessFieldName = x.Material.CmBusinessField.Name,
  327. FirstClassId = x.Material.FirstClassId,
  328. FirstClassName = x.Material.FmsMaterialCodeClass.Name,
  329. SecondClassId = x.Material.SecondClassId,
  330. SecondClassName = x.Material.FmsMaterialCodeClass1.Name,
  331. ThirdClassId = x.Material.ThirdClassId,
  332. ThirdClassName = x.Material.FmsMaterialCodeClass2.Name,
  333. Name = x.Material.Name,
  334. Standard = x.Material.Standard,
  335. Unit = x.Material.Unit,
  336. FinalPrice = x.Material.FinalPrice,
  337. ReasonableStockCount = x.Material.ReasonableStockCount,
  338. QrCode = x.Material.Rfid,
  339. DurableYears = x.Material.DurableYears,
  340. Manufacturer = x.Material.Manufacturer,
  341. Note = x.Material.Note,
  342. IsDiscontinued = x.Material.IsDiscontinued.GetValueOrDefault() ? "Y" : "N",
  343. IsUse = x.Material.IsUse ? "Y" : "N",
  344. MaterialCode = x.Material.MaterialCode,
  345. MaterialCount = 1, //자재팝업에서 기본값
  346. //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))), //출고 - 재고수량
  347. //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에 맞춤
  348. x.StockCount,
  349. x.StockAmount
  350. });
  351. return list;
  352. }
  353. // 검색 & 정렬 공통
  354. private IQueryable<MaterialStock> _FilterAndSort(PagingRequest req) {
  355. //var query = _service.GetAll();
  356. var query = _service.Stock();
  357. // 기본 Entity 검색
  358. query = query.Filter(req.conditions);
  359. if (req.siteId != null) {
  360. query = query.Where(x => x.Material.SiteId == Util.ToInt(req.siteId));
  361. }
  362. if (req.siteIds != null && req.siteIds.Length > 0) {
  363. query = query.Where(x => req.siteIds.Contains(x.Material.SiteId));
  364. }
  365. // 업무분야 필드가 있는경우만 추가
  366. if (req.businessFieldId != null) {
  367. query = query.Where(x => x.Material.SiteId == req.siteId);
  368. query = query.Where(x => x.Material.BusinessFieldId == req.businessFieldId || x.Material.CmBusinessField.Name == "공통");
  369. }
  370. // 고정정렬 (사옥 준공순)
  371. var sortQuery = query.OrderBy(x => x.Material.CmSite.SortOrderNo);
  372. // 기본 Entity 정렬
  373. sortQuery = sortQuery.ThenSort(req.sort);
  374. // 추가 정렬
  375. if (req.sort.field == "BusinessAndClass") { // 기본 커스텀 정렬 (업무분야,대중소,명칭)
  376. sortQuery = sortQuery.ThenBy(x => x.Material.BusinessFieldId);
  377. sortQuery = sortQuery.ThenBy(x => x.Material.FmsMaterialCodeClass.Name);
  378. sortQuery = sortQuery.ThenBy(x => x.Material.FmsMaterialCodeClass1.Name);
  379. sortQuery = sortQuery.ThenBy(x => x.Material.FmsMaterialCodeClass2.Name);
  380. sortQuery = sortQuery.ThenBy(x => x.Material.Name);
  381. } else {
  382. sortQuery = sortQuery.ThenSort(req.sort);
  383. }
  384. return sortQuery;
  385. }
  386. [HttpGet("qr")]
  387. public IActionResult QRDown([FromQuery] PagingRequest req) {
  388. req.page = 1;
  389. req.limit = 0;
  390. var query = _FilterAndSort(req);
  391. var Folder = DateTime.Now.ToString("yyyyMMddhhmmsss");
  392. var FileName = "자재 QR코드_" + Folder;
  393. var downFile = "/files/temp/qr/" + FileName + ".zip";
  394. var list = query.Where(x => x.Material.Rfid != null && x.Material.Rfid != "" && x.Material.IsUse == true).Select(x => new {
  395. x.Material.SiteId,
  396. x.Material.MaterialId,
  397. x.Material.Rfid
  398. });
  399. if (list.Count() == 0) throw new ServiceException("다운로드 가능한 QR코드가 없습니다.");
  400. using (MemoryStream newZip = new MemoryStream()) {
  401. //using (FileStream newZip = new FileStream(@"c:\users\exampleuser\release.zip", FileMode.CreateNew)) {
  402. using (ZipArchive archive = new ZipArchive(newZip, ZipArchiveMode.Create)) {
  403. foreach (var item in list) {
  404. archive.CreateEntryFromFile(_storage.GetLocalPath($"/qr/material/{item.SiteId}_{item.MaterialId}.jpg"), $"{item.SiteId}_{item.MaterialId}.jpg");
  405. }
  406. }
  407. return File(newZip.ToArray(), "application/zip", FileName);
  408. }
  409. }
  410. [HttpGet("QrCodeRegen")]
  411. [AllowAnonymous]
  412. public IActionResult QrCodeRegen() {
  413. var query = _service.GetAll();
  414. var list = query.Where(x => x.Rfid != null && x.Rfid != "").Select(x => new {
  415. x.SiteId,
  416. x.MaterialId,
  417. x.Rfid,
  418. });
  419. foreach (var item in list) {
  420. _QrCode.Save(item.Rfid, $"{item.SiteId}_{item.MaterialId}", "Material");
  421. }
  422. return Content(":)");
  423. }
  424. }
  425. }