MaterialService.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299
  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. if (data.CmFile != null && data.CmFile.IsUpload) {
  29. data.CmFile.SiteId = data.SiteId;
  30. var category = _context.CmFileCategory.First(x => x.Name == "material");
  31. data.CmFile.FileCategory = category;
  32. data.CmFile.CreatedDate = DateTime.Now;
  33. } else {
  34. data.CmFile = null;
  35. }
  36. _context.FmsMaterial.Add(data);
  37. _context.SaveChanges();
  38. if (data.CmFile != null && data.CmFile.IsUpload) {
  39. // 카피
  40. _storage.CopyEntity(data.CmFile.Path, data.CmFile);
  41. }
  42. trans.Complete();
  43. }
  44. }
  45. public void Edit(int id, int siteId, FmsMaterial data) {
  46. using (var trans = new TransactionScope()) {
  47. if (data.CmFile != null && data.CmFile.IsUpload) {
  48. data.CmFile.SiteId = data.SiteId;
  49. var category = _context.CmFileCategory.First(x => x.Name == "material");
  50. data.CmFile.FileCategory = category;
  51. data.CmFile.CreatedDate = DateTime.Now;
  52. } else {
  53. data.CmFile = null;
  54. }
  55. var persist = _context.FmsMaterial
  56. .Where(x => x.MaterialId == id && x.SiteId == siteId).FirstOrDefault();
  57. if (persist == null) {
  58. throw new ServiceException("정보를 찾을 수 없습니다.");
  59. }
  60. persist.SiteId = data.SiteId;
  61. persist.BusinessFieldId = data.BusinessFieldId;
  62. persist.Name = data.Name;
  63. persist.MaterialTypeId = data.MaterialTypeId;
  64. persist.Standard = data.Standard;
  65. persist.FirstClassId = data.FirstClassId;
  66. persist.SecondClassId = data.SecondClassId;
  67. persist.ThirdClassId = data.ThirdClassId;
  68. persist.MaterialCode = data.MaterialCode;
  69. persist.Unit = data.Unit;
  70. persist.DurableYears = data.DurableYears;
  71. persist.Rfid = data.Rfid;
  72. persist.Manufacturer = data.Manufacturer;
  73. persist.FinalPrice = data.FinalPrice;
  74. persist.ReasonableStockCount = data.ReasonableStockCount;
  75. persist.IsDiscontinued = data.IsDiscontinued;
  76. persist.IsUse = data.IsUse;
  77. persist.Note = data.Note;
  78. _context.FmsMaterial.Update(persist);
  79. _context.SaveChanges();
  80. if (data.CmFile != null && data.CmFile.IsUpload) {
  81. // 카피
  82. _storage.CopyEntity(data.CmFile.Path, persist.CmFile);
  83. if (persist.CmFile != null) {
  84. // 파일 삭제시 진짜 DB삭제?
  85. }
  86. }
  87. trans.Complete();
  88. }
  89. }
  90. public IQueryable<FmsMaterial> GetAll() {
  91. var query = _context.FmsMaterial;
  92. return query;
  93. }
  94. public FmsMaterial Get(int id, int siteId) {
  95. var data = _context.FmsMaterial.Single(x => x.MaterialId == id && x.SiteId == siteId);
  96. return data;
  97. }
  98. public IQueryable<FmsMaterial> Gets(int id, int siteId) {
  99. var data = _context.FmsMaterial.Where(x => x.MaterialId == id && x.SiteId == siteId);
  100. return data;
  101. }
  102. public void Delete(int id, int siteId) {
  103. var data = _context.FmsMaterial.First(x => x.MaterialId == id && x.SiteId == siteId);
  104. _context.FmsMaterial.Remove(data);
  105. _context.SaveChanges();
  106. }
  107. public IQueryable<MaterialStock> Stock() {
  108. var query = from x in _context.FmsMaterial.AsExpandable()
  109. select new MaterialStock {
  110. Material = x,
  111. StockCount = FmsMaterial.GetStockCount.Invoke(x),
  112. StockAmount = FmsMaterial.GetStockAmount.Invoke(x),
  113. };
  114. return query;
  115. }
  116. public IQueryable<MaterialStockHistory> DailyStock() {
  117. var query = (
  118. from x in _context.FmsMaterialStored
  119. where x.IsApproval == true
  120. select new {
  121. Date = x.StoredDate.Date,
  122. x.FmsMaterial,
  123. x.StockCount,
  124. x.StoredCount,
  125. MaterialCount = 0,
  126. ReleaseCount = 0,
  127. ReleaseLossCount = 0,
  128. ReleaseReturnCount = 0,
  129. })
  130. .Concat(
  131. from x in _context.FmsMaterialRelease
  132. where x.IsConfirmed == true
  133. select new {
  134. Date = x.ReleaseDate.Date,
  135. x.FmsMaterial,
  136. StockCount = 0,
  137. StoredCount = 0,
  138. MaterialCount = x.MaterialCount.Value,
  139. ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
  140. ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
  141. ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
  142. });
  143. var subquery = from x in query
  144. group x by new { x.FmsMaterial.MaterialId, x.Date } into g
  145. orderby g.Key.Date descending
  146. select new {
  147. g.Key.Date,
  148. g.Key.MaterialId,
  149. LastStockCount = _context.FmsMaterialStored
  150. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  151. .OrderByDescending(x => x.StoredDate)
  152. .Select(x => x.StockCount)
  153. .FirstOrDefault(),
  154. LastStockDate = _context.FmsMaterialStored
  155. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  156. .OrderByDescending(x => x.StoredDate)
  157. .Select(x => x.StoredDate)
  158. .FirstOrDefault(),
  159. StockAmount = _context.FmsMaterialStored
  160. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  161. .OrderByDescending(x => x.StoredDate)
  162. .Select(x => (long)x.UnitCost * x.StockCount)
  163. .FirstOrDefault(),
  164. Name = g.Min(x => x.FmsMaterial.Name),
  165. StoredCount = g.Sum(x => x.StoredCount),
  166. ReleaseCount = g.Sum(x => x.ReleaseCount),
  167. ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
  168. ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
  169. };
  170. var subquery2 = from stock in subquery
  171. join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
  172. select new MaterialStockHistory {
  173. Date = stock.Date,
  174. Material = material,
  175. StoredCount = stock.StoredCount,
  176. ReleaseCount = stock.ReleaseCount,
  177. ReleaseLossCount = stock.ReleaseLossCount,
  178. ReleaseReturnCount = stock.ReleaseReturnCount,
  179. LastStockCount = stock.LastStockCount,
  180. LastStockDate = stock.LastStockDate,
  181. StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
  182. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
  183. .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
  184. StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
  185. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
  186. .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0)
  187. };
  188. return subquery2;
  189. }
  190. public IQueryable<MaterialStockHistory> MonthlyStock() {
  191. var query = (
  192. from x in _context.FmsMaterialStored
  193. where x.IsApproval == true
  194. select new {
  195. Date = x.StoredDate.Date,
  196. Year = x.StoredDate.Year,
  197. Month = x.StoredDate.Month,
  198. x.FmsMaterial,
  199. x.StockCount,
  200. x.StoredCount,
  201. MaterialCount = 0,
  202. ReleaseCount = 0,
  203. ReleaseLossCount = 0,
  204. ReleaseReturnCount = 0,
  205. })
  206. .Concat(
  207. from x in _context.FmsMaterialRelease
  208. where x.IsConfirmed == true
  209. select new {
  210. Date = x.ReleaseDate.Date,
  211. Year = x.ReleaseDate.Year,
  212. Month = x.ReleaseDate.Month,
  213. x.FmsMaterial,
  214. StockCount = 0,
  215. StoredCount = 0,
  216. MaterialCount = x.MaterialCount.Value,
  217. ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
  218. ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
  219. ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
  220. });
  221. var subquery = from x in query
  222. group x by new { x.FmsMaterial.MaterialId, x.Year, x.Month } into g
  223. orderby g.Key.Year descending, g.Key.Month descending
  224. select new {
  225. g.Key.Year,
  226. g.Key.Month,
  227. g.Key.MaterialId,
  228. Date = g.Max(x => x.Date),
  229. LastStockCount = _context.FmsMaterialStored
  230. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  231. .OrderByDescending(x => x.StoredDate)
  232. .Select(x => x.StockCount)
  233. .FirstOrDefault(),
  234. LastStockDate = _context.FmsMaterialStored
  235. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  236. .OrderByDescending(x => x.StoredDate)
  237. .Select(x => x.StoredDate)
  238. .FirstOrDefault(),
  239. StockAmount = _context.FmsMaterialStored
  240. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  241. .OrderByDescending(x => x.StoredDate)
  242. .Select(x => (long)x.UnitCost * x.StockCount)
  243. .FirstOrDefault(),
  244. Name = g.Min(x => x.FmsMaterial.Name),
  245. StoredCount = g.Sum(x => x.StoredCount),
  246. ReleaseCount = g.Sum(x => x.ReleaseCount),
  247. ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
  248. ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
  249. };
  250. var subquery2 = from stock in subquery
  251. join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
  252. select new MaterialStockHistory {
  253. Date = stock.Date,
  254. Year = stock.Year,
  255. Month = stock.Month,
  256. Material = material,
  257. StoredCount = stock.StoredCount,
  258. ReleaseCount = stock.ReleaseCount,
  259. ReleaseLossCount = stock.ReleaseLossCount,
  260. ReleaseReturnCount = stock.ReleaseReturnCount,
  261. LastStockCount = stock.LastStockCount,
  262. LastStockDate = stock.LastStockDate,
  263. StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
  264. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
  265. .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
  266. StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
  267. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
  268. .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0),
  269. };
  270. return subquery2;
  271. }
  272. }
  273. }