MaterialStockController.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338
  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. namespace FMSAdmin.Controllers {
  21. [Authorize]
  22. [ApiController]
  23. [ApiVersion("1")]
  24. [Route("api/[controller]")]
  25. public class MaterialStockController : Controller {
  26. private readonly ILogger<MaterialStockController> _logger;
  27. private readonly FMSContext _context;
  28. private readonly MaterialStockService _service;
  29. private readonly StorageHelper _storage;
  30. public MaterialStockController(
  31. ILogger<MaterialStockController> logger,
  32. FMSContext context,
  33. MaterialStockService service,
  34. StorageHelper storage
  35. ) {
  36. _logger = logger;
  37. _context = context;
  38. _service = service;
  39. _storage = storage;
  40. }
  41. [HttpGet("[action]")]
  42. public IActionResult MaterialShortStock([FromQuery] PagingRequest req) {
  43. return List(req, "shortStock");
  44. }
  45. [HttpGet("[action]")]
  46. public IActionResult MaterialShortStockExcel([FromQuery] PagingRequest req) {
  47. return ExportExcel(req, "shortStock");
  48. }
  49. [HttpGet("[action]")]
  50. public IActionResult MaterialWarehouseStock([FromQuery] PagingRequest req) {
  51. return List(req, "warehouseStock");
  52. }
  53. [HttpGet("[action]")]
  54. public IActionResult MaterialWarehouseStockExcel([FromQuery] PagingRequest req) {
  55. return ExportExcel(req, "warehouseStock");
  56. }
  57. /// <summary>
  58. /// 리스트
  59. /// </summary>
  60. /// <param name="req"></param>
  61. /// <param name="siteId"></param>
  62. /// <returns></returns>
  63. [HttpGet]
  64. public IActionResult List([FromQuery] PagingRequest req, string type = "") {
  65. var query = _FilterAndSort(req, type);
  66. var list = _ListSelect(query);
  67. var paging = PagingList.Pagination(list, req.page, req.limit);
  68. return Ok(paging);
  69. }
  70. /// <summary>
  71. /// 엑셀파일 익스포트
  72. /// </summary>
  73. /// <param name="req"></param>
  74. /// <returns></returns>
  75. [HttpGet("excel")]
  76. public IActionResult ExportExcel([FromQuery] PagingRequest req, string type = "", string filename = null) {
  77. req.page = 1;
  78. req.limit = 0;
  79. var query = _FilterAndSort(req, type);
  80. var list = _ListSelect(query);
  81. var stream = new MemoryStream();
  82. using (var package = new ExcelPackage(stream)) {
  83. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  84. workSheet.Cells.LoadFromCollection(list, true);
  85. if (req.columns != null && req.columns.Length > 0) {
  86. workSheet.AddStyle(req.columns, req.sort?.order?.ToLower() == "asc", filename);
  87. }
  88. package.Save();
  89. }
  90. stream.Position = 0;
  91. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  92. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  93. }
  94. /// <summary>
  95. /// 엑셀파일 임포트
  96. /// </summary>
  97. /// <param name="file"></param>
  98. /// <returns></returns>
  99. [HttpPost]
  100. [Route("[action]")]
  101. public IActionResult ImportExcel(IFormFile file) {
  102. IList<Hashtable> list = new List<Hashtable>();
  103. using (ExcelPackage excelPackage = new ExcelPackage(file.OpenReadStream())) {
  104. var worksheet = excelPackage.Workbook.Worksheets[0];
  105. for (int i = worksheet.Dimension.Start.Row; i <= worksheet.Dimension.End.Row; i++) {
  106. var name = worksheet.Cells[i, 1].Value;
  107. var rfid = worksheet.Cells[i, 2].Value;
  108. var code = worksheet.Cells[i, 3].Value;
  109. list.Add(new Hashtable {
  110. {"Name", name}
  111. });
  112. }
  113. }
  114. //_service.ImportExcel(list);
  115. //return BadRequest(ex.Message);
  116. return Ok();
  117. }
  118. /// <summary>
  119. /// 조회
  120. /// </summary>
  121. /// <param name="siteId"></param>
  122. /// <param name="id"></param>
  123. /// <returns></returns>
  124. [HttpGet("{siteId}/{id}")]
  125. public IActionResult Get(int siteId, int id) {
  126. var data = _service.Gets(id, siteId);
  127. if (data.Count() == 0) {
  128. return NotFound("자재정보를 찾을 수 없습니다.");
  129. }
  130. var item = data.Select(x => new {
  131. x.MaterialId,
  132. x.SiteId,
  133. CmSite = new {
  134. x.CmSite.Name,
  135. },
  136. x.BusinessFieldId,
  137. CmBusinessField = new {
  138. x.CmBusinessField.Name,
  139. },
  140. x.MaterialTypeId,
  141. FmsMaterialCodeType = new {
  142. x.FmsMaterialCodeType.Name,
  143. },
  144. x.FirstClassId,
  145. FmsMaterialCodeClass = new {
  146. x.FmsMaterialCodeClass.MaterialClassId,
  147. x.FmsMaterialCodeClass.Name,
  148. },
  149. x.SecondClassId,
  150. FmsMaterialCodeClass1 = new {
  151. x.FmsMaterialCodeClass1.MaterialClassId,
  152. x.FmsMaterialCodeClass1.Name,
  153. },
  154. x.ThirdClassId,
  155. FmsMaterialCodeClass2 = new {
  156. x.FmsMaterialCodeClass2.MaterialClassId,
  157. x.FmsMaterialCodeClass2.Name,
  158. },
  159. x.MaterialCode,
  160. CmFile = _storage.ParseFile(x.CmFile),
  161. x.Name,
  162. x.Standard,
  163. x.Unit,
  164. x.Rfid,
  165. x.Manufacturer,
  166. x.DurableYears,
  167. x.FinalPrice,
  168. x.ReasonableStockCount,
  169. x.IsDiscontinued,
  170. x.IsUse,
  171. x.Note,
  172. });
  173. return Ok(item.First());
  174. }
  175. /// <summary>
  176. /// 수정
  177. /// </summary>
  178. /// <param name="siteId"></param>
  179. /// <param name="id"></param>
  180. /// <param name="data"></param>
  181. /// <returns></returns>
  182. [HttpPost("{siteId}/{id}")]
  183. public IActionResult Edit(int siteId, int id, [FromBody] FmsMaterial data) {
  184. if (id != data.MaterialId) {
  185. return BadRequest("잘못된 접근입니다.");
  186. }
  187. if (ModelState.IsValid) {
  188. _service.Edit(id, siteId, data);
  189. } else {
  190. return BadRequest(ModelState);
  191. }
  192. return Ok();
  193. }
  194. /// <summary>
  195. /// 등록
  196. /// </summary>
  197. /// <param name="data"></param>
  198. /// <returns></returns>
  199. [HttpPut]
  200. public IActionResult Create([FromBody] FmsMaterial data) {
  201. _logger.LogInformation("Create");
  202. //_logger.LogInformation(data.Dump());
  203. if (ModelState.IsValid) {
  204. _service.Create(data);
  205. } else {
  206. return BadRequest(ModelState);
  207. }
  208. return Ok();
  209. }
  210. /// <summary>
  211. /// 삭제
  212. /// </summary>
  213. /// <param name="siteId"></param>
  214. /// <param name="id"></param>
  215. /// <returns></returns>
  216. [HttpDelete("{siteId}/{id}")]
  217. public IActionResult Delete(int siteId, int id) {
  218. _service.Delete(id, siteId);
  219. return Ok();
  220. }
  221. // 셀렉트 공통
  222. private dynamic _ListSelect(IQueryable<MaterialStock> query) {
  223. var list = query.Select(x => new {
  224. x.Material.MaterialId,
  225. x.Material.MaterialCode,
  226. x.Material.SiteId,
  227. SiteName = x.Material.CmSite.Name,
  228. x.Material.BusinessFieldId,
  229. BusinessFieldName = x.Material.CmBusinessField.Name,
  230. x.Material.FirstClassId,
  231. FirstClassName = x.Material.FmsMaterialCodeClass.Name,
  232. x.Material.SecondClassId,
  233. SecondClassName = x.Material.FmsMaterialCodeClass1.Name,
  234. x.Material.ThirdClassId,
  235. ThirdClassName = x.Material.FmsMaterialCodeClass2.Name,
  236. x.Material.Name,
  237. x.Material.Standard,
  238. x.Material.Unit,
  239. x.Material.ReasonableStockCount,
  240. x.StockCount,
  241. x.StockAmount,
  242. InsufficientStock = (Util.ToLong(x.Material.ReasonableStockCount) - x.StockCount > 0) ? Util.ToLong(x.Material.ReasonableStockCount) - x.StockCount : 0,
  243. x.Warehouse.WarehouseId,
  244. WareHouseName = x.Warehouse.Name,
  245. x.Material.FinalPrice,
  246. x.Material.IsDiscontinued,
  247. x.Material.IsUse,
  248. });
  249. return list;
  250. }
  251. // 검색 & 정렬 공통
  252. private IQueryable<MaterialStock> _FilterAndSort(PagingRequest req, string type = "") {
  253. var query = _service.Stock();
  254. if (type.Equals("warehouseStock")) {
  255. query = _service.WarehouseStock();
  256. }
  257. // 기본 Entity 검색
  258. query = query.Filter(req.conditions);
  259. if (req.siteId != null) {
  260. query = query.Where(x => x.Material.SiteId == Util.ToInt(req.siteId));
  261. }
  262. if (req.siteIds != null && req.siteIds.Length > 0) {
  263. query = query.Where(x => req.siteIds.Contains(x.Material.SiteId));
  264. }
  265. // 업무분야 필드가 있는경우만 추가
  266. if (req.businessFieldId != null) {
  267. query = query.Where(x => new int[] { req.businessFieldId.Value, 1 }.Contains(x.Material.BusinessFieldId));
  268. }
  269. if (type.Equals("shortStock")) {
  270. query = query.Where(x => (x.Material.ReasonableStockCount == null ? 0 : x.Material.ReasonableStockCount) > (x.StockCount == null ? 0 : x.StockCount));
  271. }
  272. // 고정정렬 (사옥 준공순)
  273. var sortQuery = query.OrderBy(x => x.Material.CmSite.SortOrderNo);
  274. // 추가 정렬
  275. sortQuery = sortQuery.ThenSort(req.sort);
  276. return sortQuery;
  277. }
  278. /// <summary>
  279. /// 부족수량 조회
  280. /// </summary>
  281. /// <returns></returns>
  282. [HttpGet("GetInsufficientStock")]
  283. public IActionResult GetInsufficientStock([FromQuery] int siteId) {
  284. var query = _service.Stock();
  285. //var list = query.Sum(x => Util.ToLong(x.Material.ReasonableStockCount) - x.StockCount);
  286. query = query.Where(x => x.Material.SiteId == siteId);
  287. var list = query.Select(x => new {
  288. SiteId = x.Material.SiteId,
  289. MaterialId = x.Material.MaterialId,
  290. InsufficientStock = Util.ToLong(x.Material.ReasonableStockCount) - x.StockCount
  291. });
  292. return Ok(list);
  293. }
  294. }
  295. }