MaterialService.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Linq.Dynamic.Core;
  5. using FMSAdmin.Data;
  6. using FMSAdmin.Helpers;
  7. using FMSAdmin.Entities;
  8. using Microsoft.Extensions.Logging;
  9. using FMSAdmin.Models;
  10. using System.Linq.Expressions;
  11. using LinqKit;
  12. using System.Transactions;
  13. namespace FMSApp.Services {
  14. public class MaterialService {
  15. private readonly ILogger<MaterialService> _logger;
  16. private readonly FMSContext _context;
  17. private readonly StorageHelper _storage;
  18. public MaterialService(
  19. ILogger<MaterialService> logger,
  20. FMSContext context,
  21. StorageHelper storage) {
  22. _logger = logger;
  23. _context = context;
  24. _storage = storage;
  25. }
  26. public void Create(FmsMaterial data) {
  27. using (var trans = new TransactionScope()) {
  28. checkName(data);
  29. if (data.CmFile != null && data.CmFile.IsUpload) {
  30. data.CmFile.SiteId = data.SiteId;
  31. var category = _context.CmFileCategory.First(x => x.Name == "material");
  32. data.CmFile.FileCategory = category;
  33. data.CmFile.CreatedDate = DateTime.Now;
  34. } else {
  35. data.CmFile = null;
  36. }
  37. _context.FmsMaterial.Add(data);
  38. _context.SaveChanges();
  39. if (data.CmFile != null && data.CmFile.IsUpload) {
  40. // 카피
  41. _storage.CopyEntity(data.CmFile.Path, data.CmFile);
  42. }
  43. trans.Complete();
  44. }
  45. }
  46. public int checkName(FmsMaterial data, bool isExcel = false) {
  47. var check = _context.FmsMaterial.Where(x => x.SiteId == data.SiteId && x.BusinessFieldId == data.BusinessFieldId && x.Name == data.Name && x.Standard == data.Standard).Count();
  48. if (!isExcel && check > 0) {
  49. throw new ServiceException("동일 정보가 존재합니다. [업무분야-자재명-규격]");
  50. }
  51. return check;
  52. }
  53. public void Edit(int id, int siteId, FmsMaterial data) {
  54. using (var trans = new TransactionScope()) {
  55. if (data.CmFile != null && data.CmFile.IsUpload) {
  56. data.CmFile.SiteId = data.SiteId;
  57. var category = _context.CmFileCategory.First(x => x.Name == "material");
  58. data.CmFile.FileCategory = category;
  59. data.CmFile.CreatedDate = DateTime.Now;
  60. } else {
  61. data.CmFile = null;
  62. }
  63. var persist = _context.FmsMaterial
  64. .Where(x => x.MaterialId == id && x.SiteId == siteId).FirstOrDefault();
  65. if (persist == null) {
  66. throw new ServiceException("정보를 찾을 수 없습니다.");
  67. }
  68. if (!persist.BusinessFieldId.Equals(data.BusinessFieldId)) {
  69. checkName(data);
  70. }
  71. if (!persist.Name.Equals(data.Name)) {
  72. checkName(data);
  73. }
  74. if (!Util.IsNullOrEmpty(data.Standard)) {
  75. if (!persist.Standard.Equals(data.Standard))
  76. checkName(data);
  77. }
  78. persist.SiteId = data.SiteId;
  79. persist.BusinessFieldId = data.BusinessFieldId;
  80. persist.Name = data.Name;
  81. persist.MaterialTypeId = data.MaterialTypeId;
  82. persist.Standard = data.Standard;
  83. persist.FirstClassId = data.FirstClassId;
  84. persist.SecondClassId = data.SecondClassId;
  85. persist.ThirdClassId = data.ThirdClassId;
  86. persist.MaterialCode = data.MaterialCode;
  87. persist.Unit = data.Unit;
  88. persist.DurableYears = data.DurableYears;
  89. //persist.Rfid = data.Rfid;
  90. persist.Manufacturer = data.Manufacturer;
  91. persist.FinalPrice = data.FinalPrice;
  92. persist.ReasonableStockCount = data.ReasonableStockCount;
  93. persist.IsDiscontinued = data.IsDiscontinued;
  94. persist.IsUse = data.IsUse;
  95. persist.Note = data.Note;
  96. _context.FmsMaterial.Update(persist);
  97. _context.SaveChanges();
  98. if (data.CmFile != null && data.CmFile.IsUpload) {
  99. // 카피
  100. _storage.CopyEntity(data.CmFile.Path, persist.CmFile);
  101. if (persist.CmFile != null) {
  102. // 파일 삭제시 진짜 DB삭제?
  103. }
  104. }
  105. trans.Complete();
  106. }
  107. }
  108. public IQueryable<FmsMaterial> GetAll() {
  109. var query = _context.FmsMaterial;
  110. return query;
  111. }
  112. public FmsMaterial Get(int id, int siteId) {
  113. var data = _context.FmsMaterial.Single(x => x.MaterialId == id && x.SiteId == siteId);
  114. return data;
  115. }
  116. public IQueryable<FmsMaterial> Gets(int id, int siteId) {
  117. var data = _context.FmsMaterial.Where(x => x.MaterialId == id && x.SiteId == siteId);
  118. return data;
  119. }
  120. public void Delete(int id, int siteId) {
  121. var data = _context.FmsMaterial.First(x => x.MaterialId == id && x.SiteId == siteId);
  122. _context.FmsMaterial.Remove(data);
  123. _context.SaveChanges();
  124. }
  125. public IQueryable<MaterialStock> Stock() {
  126. var query = from x in _context.FmsMaterial.AsExpandable()
  127. select new MaterialStock {
  128. Material = x,
  129. StockCount = FmsMaterial.GetStockCount.Invoke(x),
  130. StockAmount = FmsMaterial.GetStockAmount.Invoke(x),
  131. };
  132. return query;
  133. }
  134. public IQueryable<MaterialStockHistory> DailyStock() {
  135. var query = (
  136. from x in _context.FmsMaterialStored
  137. where x.IsApproval == true
  138. select new {
  139. Date = x.StoredDate.Date,
  140. x.FmsMaterial,
  141. x.StockCount,
  142. x.StoredCount,
  143. MaterialCount = 0,
  144. ReleaseCount = 0,
  145. ReleaseLossCount = 0,
  146. ReleaseReturnCount = 0,
  147. })
  148. .Concat(
  149. from x in _context.FmsMaterialRelease
  150. where x.IsConfirmed == true
  151. select new {
  152. Date = x.ReleaseDate.Date,
  153. x.FmsMaterial,
  154. StockCount = 0,
  155. StoredCount = 0,
  156. MaterialCount = x.MaterialCount.Value,
  157. ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
  158. ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
  159. ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
  160. });
  161. var subquery = from x in query
  162. group x by new { x.FmsMaterial.MaterialId, x.Date } into g
  163. orderby g.Key.Date descending
  164. select new {
  165. g.Key.Date,
  166. g.Key.MaterialId,
  167. LastStockCount = _context.FmsMaterialStored
  168. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  169. .OrderByDescending(x => x.StoredDate)
  170. .Select(x => x.StockCount)
  171. .FirstOrDefault(),
  172. LastStockDate = _context.FmsMaterialStored
  173. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  174. .OrderByDescending(x => x.StoredDate)
  175. .Select(x => x.StoredDate)
  176. .FirstOrDefault(),
  177. StockAmount = _context.FmsMaterialStored
  178. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  179. .OrderByDescending(x => x.StoredDate)
  180. .Select(x => (long)x.UnitCost * x.StockCount)
  181. .FirstOrDefault(),
  182. Name = g.Min(x => x.FmsMaterial.Name),
  183. StoredCount = g.Sum(x => x.StoredCount),
  184. ReleaseCount = g.Sum(x => x.ReleaseCount),
  185. ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
  186. ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
  187. };
  188. var subquery2 = from stock in subquery
  189. join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
  190. select new MaterialStockHistory {
  191. Date = stock.Date,
  192. Material = material,
  193. StoredCount = stock.StoredCount,
  194. ReleaseCount = stock.ReleaseCount,
  195. ReleaseLossCount = stock.ReleaseLossCount,
  196. ReleaseReturnCount = stock.ReleaseReturnCount,
  197. LastStockCount = stock.LastStockCount,
  198. LastStockDate = stock.LastStockDate,
  199. StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
  200. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
  201. .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
  202. StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
  203. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
  204. .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0)
  205. };
  206. return subquery2;
  207. }
  208. public IQueryable<MaterialStockHistory> MonthlyStock() {
  209. var query = (
  210. from x in _context.FmsMaterialStored
  211. where x.IsApproval == true
  212. select new {
  213. Date = x.StoredDate.Date,
  214. Year = x.StoredDate.Year,
  215. Month = x.StoredDate.Month,
  216. x.FmsMaterial,
  217. x.StockCount,
  218. x.StoredCount,
  219. MaterialCount = 0,
  220. ReleaseCount = 0,
  221. ReleaseLossCount = 0,
  222. ReleaseReturnCount = 0,
  223. })
  224. .Concat(
  225. from x in _context.FmsMaterialRelease
  226. where x.IsConfirmed == true
  227. select new {
  228. Date = x.ReleaseDate.Date,
  229. Year = x.ReleaseDate.Year,
  230. Month = x.ReleaseDate.Month,
  231. x.FmsMaterial,
  232. StockCount = 0,
  233. StoredCount = 0,
  234. MaterialCount = x.MaterialCount.Value,
  235. ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
  236. ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
  237. ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
  238. });
  239. var subquery = from x in query
  240. group x by new { x.FmsMaterial.MaterialId, x.Year, x.Month } into g
  241. orderby g.Key.Year descending, g.Key.Month descending
  242. select new {
  243. g.Key.Year,
  244. g.Key.Month,
  245. g.Key.MaterialId,
  246. Date = g.Max(x => x.Date),
  247. LastStockCount = _context.FmsMaterialStored
  248. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  249. .OrderByDescending(x => x.StoredDate)
  250. .Select(x => x.StockCount)
  251. .FirstOrDefault(),
  252. LastStockDate = _context.FmsMaterialStored
  253. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  254. .OrderByDescending(x => x.StoredDate)
  255. .Select(x => x.StoredDate)
  256. .FirstOrDefault(),
  257. StockAmount = _context.FmsMaterialStored
  258. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  259. .OrderByDescending(x => x.StoredDate)
  260. .Select(x => (long)x.UnitCost * x.StockCount)
  261. .FirstOrDefault(),
  262. Name = g.Min(x => x.FmsMaterial.Name),
  263. StoredCount = g.Sum(x => x.StoredCount),
  264. ReleaseCount = g.Sum(x => x.ReleaseCount),
  265. ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
  266. ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
  267. };
  268. var subquery2 = from stock in subquery
  269. join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
  270. select new MaterialStockHistory {
  271. Date = stock.Date,
  272. Year = stock.Year,
  273. Month = stock.Month,
  274. Material = material,
  275. StoredCount = stock.StoredCount,
  276. ReleaseCount = stock.ReleaseCount,
  277. ReleaseLossCount = stock.ReleaseLossCount,
  278. ReleaseReturnCount = stock.ReleaseReturnCount,
  279. LastStockCount = stock.LastStockCount,
  280. LastStockDate = stock.LastStockDate,
  281. StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
  282. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
  283. .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
  284. StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
  285. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
  286. .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0),
  287. };
  288. return subquery2;
  289. }
  290. }
  291. }