MaterialStockService.cs 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368
  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 FMSAdmin.Services {
  14. public class MaterialStockService {
  15. private readonly ILogger<MaterialStockService> _logger;
  16. private readonly FMSContext _context;
  17. private readonly StorageHelper _storage;
  18. public MaterialStockService(
  19. ILogger<MaterialStockService> 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. }
  53. var persist = _context.FmsMaterial
  54. .Where(x => x.MaterialId == id && x.SiteId == siteId).FirstOrDefault();
  55. if (persist == null) {
  56. throw new ServiceException("정보를 찾을 수 없습니다.");
  57. }
  58. persist.SiteId = data.SiteId;
  59. persist.BusinessFieldId = data.BusinessFieldId;
  60. persist.Name = data.Name;
  61. persist.MaterialTypeId = data.MaterialTypeId;
  62. persist.Standard = data.Standard;
  63. persist.FirstClassId = data.FirstClassId;
  64. persist.SecondClassId = data.SecondClassId;
  65. persist.ThirdClassId = data.ThirdClassId;
  66. persist.MaterialCode = data.MaterialCode;
  67. persist.Unit = data.Unit;
  68. persist.DurableYears = data.DurableYears;
  69. persist.Rfid = data.Rfid;
  70. persist.Manufacturer = data.Manufacturer;
  71. persist.FinalPrice = data.FinalPrice;
  72. persist.ReasonableStockCount = data.ReasonableStockCount;
  73. persist.IsDiscontinued = data.IsDiscontinued;
  74. persist.IsUse = data.IsUse;
  75. persist.Note = data.Note;
  76. _context.FmsMaterial.Update(persist);
  77. _context.SaveChanges();
  78. if (data.CmFile != null && data.CmFile.IsUpload) {
  79. // 카피
  80. _storage.CopyEntity(data.CmFile.Path, persist.CmFile);
  81. if (persist.CmFile != null) {
  82. // 파일 삭제시 진짜 DB삭제?
  83. }
  84. }
  85. trans.Complete();
  86. }
  87. }
  88. public IQueryable<FmsMaterial> GetAll() {
  89. var query = _context.FmsMaterial;
  90. return query;
  91. }
  92. public FmsMaterial Get(int id, int siteId) {
  93. var data = _context.FmsMaterial.Single(x => x.MaterialId == id && x.SiteId == siteId);
  94. return data;
  95. }
  96. public IQueryable<FmsMaterial> Gets(int id, int siteId) {
  97. var data = _context.FmsMaterial.Where(x => x.MaterialId == id && x.SiteId == siteId);
  98. return data;
  99. }
  100. public void Delete(int id, int siteId) {
  101. var data = _context.FmsMaterial.First(x => x.MaterialId == id && x.SiteId == siteId);
  102. _context.FmsMaterial.Remove(data);
  103. _context.SaveChanges();
  104. }
  105. public IQueryable<MaterialStock> Stock() {
  106. var query = from x in _context.FmsMaterial.AsExpandable()
  107. select new MaterialStock {
  108. Material = x,
  109. StockCount = FmsMaterial.GetStockCount.Invoke(x),
  110. StockAmount = FmsMaterial.GetStockAmount.Invoke(x),
  111. Warehouse = x.FmsMaterialStored.OrderByDescending(s => s.StoredDate).FirstOrDefault().FmsMaterialWarehouse
  112. };
  113. return query;
  114. }
  115. public IQueryable<MaterialStock> WarehouseStock() {
  116. var query = (
  117. from x in _context.FmsMaterialStored
  118. where x.IsApproval == true
  119. select new {
  120. Date = x.StoredDate.Date,
  121. x.FmsMaterial,
  122. x.StockCount,
  123. x.StoredCount,
  124. x.UnitCost,
  125. x.FmsMaterialWarehouse,
  126. MaterialCount = 0,
  127. ReleaseCount = 0,
  128. ReleaseLossCount = 0,
  129. ReleaseReturnCount = 0,
  130. ReleaseAmount = 0,
  131. ReleaseLossAmount = 0,
  132. ReleaseReturnAmount = 0,
  133. })
  134. .Concat(
  135. from x in _context.FmsMaterialReleaseDetail
  136. where x.FmsMaterialRelease.IsConfirmed == true
  137. select new {
  138. Date = x.FmsMaterialRelease.ReleaseDate.Date,
  139. x.FmsMaterialRelease.FmsMaterial,
  140. StockCount = 0,
  141. StoredCount = 0,
  142. UnitCost = 0,
  143. x.FmsMaterialStored.FmsMaterialWarehouse,
  144. MaterialCount = x.FmsMaterialRelease.MaterialCount.Value,
  145. ReleaseCount = x.FmsMaterialRelease.AdjustmentTypeId == 0 ? x.FmsMaterialRelease.MaterialCount.Value : 0,
  146. ReleaseLossCount = x.FmsMaterialRelease.AdjustmentTypeId == 1 ? x.FmsMaterialRelease.MaterialCount.Value : 0,
  147. ReleaseReturnCount = x.FmsMaterialRelease.AdjustmentTypeId == 2 ? x.FmsMaterialRelease.MaterialCount.Value : 0,
  148. ReleaseAmount = x.FmsMaterialRelease.AdjustmentTypeId == 0 ? x.FmsMaterialRelease.ReleaseTotalCost.Value : 0,
  149. ReleaseLossAmount = x.FmsMaterialRelease.AdjustmentTypeId == 1 ? x.FmsMaterialRelease.ReleaseTotalCost.Value : 0,
  150. ReleaseReturnAmount = x.FmsMaterialRelease.AdjustmentTypeId == 2 ? x.FmsMaterialRelease.ReleaseTotalCost.Value : 0,
  151. });
  152. var subquery = from x in query
  153. group x by new { x.FmsMaterial.MaterialId, x.FmsMaterialWarehouse.WarehouseId } into g
  154. select new {
  155. g.Key.MaterialId,
  156. g.Key.WarehouseId,
  157. StockCount = g.Sum(x => x.StoredCount),
  158. StockAmount = g.Sum(x => (long)x.UnitCost * x.StoredCount),
  159. ReleaseCount = g.Sum(x => x.ReleaseCount),
  160. ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
  161. ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
  162. ReleaseAmount = g.Sum(x => x.ReleaseAmount),
  163. ReleaseLossAmount = g.Sum(x => x.ReleaseLossAmount),
  164. ReleaseReturnAmount = g.Sum(x => x.ReleaseReturnAmount),
  165. };
  166. var subquery2 = from stock in subquery
  167. join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
  168. join wareHouse in _context.FmsMaterialWarehouse on stock.WarehouseId equals wareHouse.WarehouseId
  169. select new MaterialStock {
  170. Material = material,
  171. Warehouse = wareHouse,
  172. StockCount = stock.StockCount + stock.ReleaseReturnCount - stock.ReleaseCount - stock.ReleaseLossCount,
  173. StockAmount = stock.StockAmount + stock.ReleaseReturnAmount - stock.ReleaseAmount - stock.ReleaseLossAmount
  174. };
  175. return subquery2;
  176. }
  177. public IQueryable<MaterialStockHistory> DailyStock() {
  178. var query = (
  179. from x in _context.FmsMaterialStored
  180. where x.IsApproval == true
  181. select new {
  182. Date = x.StoredDate.Date,
  183. x.FmsMaterial,
  184. x.StockCount,
  185. x.StoredCount,
  186. MaterialCount = 0,
  187. ReleaseCount = 0,
  188. ReleaseLossCount = 0,
  189. ReleaseReturnCount = 0,
  190. })
  191. .Concat(
  192. from x in _context.FmsMaterialRelease
  193. where x.IsConfirmed == true
  194. select new {
  195. Date = x.ReleaseDate.Date,
  196. x.FmsMaterial,
  197. StockCount = 0,
  198. StoredCount = 0,
  199. MaterialCount = x.MaterialCount.Value,
  200. ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
  201. ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
  202. ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
  203. });
  204. var subquery = from x in query
  205. group x by new { x.FmsMaterial.MaterialId, x.Date } into g
  206. orderby g.Key.Date descending
  207. select new {
  208. g.Key.Date,
  209. g.Key.MaterialId,
  210. LastStockCount = _context.FmsMaterialStored
  211. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  212. .OrderByDescending(x => x.StoredDate)
  213. .Sum(x => x.StoredCount),
  214. LastStockDate = _context.FmsMaterialStored
  215. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  216. .OrderByDescending(x => x.StoredDate)
  217. .Select(x => x.StoredDate)
  218. .FirstOrDefault(),
  219. // StockAmount = _context.FmsMaterialStored
  220. // .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  221. // .OrderByDescending(x => x.StoredDate)
  222. // .Select(x => (long)x.UnitCost * x.StockCount)
  223. // .FirstOrDefault(),
  224. StockAmount = _context.FmsMaterialStored
  225. .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  226. .Sum(x => (long)x.UnitCost * x.StoredCount),
  227. Name = g.Min(x => x.FmsMaterial.Name),
  228. StoredCount = g.Sum(x => x.StoredCount),
  229. ReleaseCount = g.Sum(x => x.ReleaseCount),
  230. ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
  231. ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
  232. };
  233. var subquery2 = from stock in subquery
  234. join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
  235. select new MaterialStockHistory {
  236. Date = stock.Date,
  237. Material = material,
  238. Warehouse = material.FmsMaterialStored.OrderByDescending(x => x.StoredDate).FirstOrDefault().FmsMaterialWarehouse,
  239. StoredCount = stock.StoredCount,
  240. ReleaseCount = stock.ReleaseCount,
  241. ReleaseLossCount = stock.ReleaseLossCount,
  242. ReleaseReturnCount = stock.ReleaseReturnCount,
  243. LastStockCount = stock.LastStockCount,
  244. LastStockDate = stock.LastStockDate,
  245. StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
  246. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
  247. .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
  248. StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
  249. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true)
  250. .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0)
  251. };
  252. return subquery2;
  253. }
  254. public IQueryable<MaterialStockHistory> MonthlyStock() {
  255. var query = (
  256. from x in _context.FmsMaterialStored
  257. where x.IsApproval == true
  258. select new {
  259. Date = x.StoredDate.Date,
  260. Year = x.StoredDate.Year,
  261. Month = x.StoredDate.Month,
  262. x.FmsMaterial,
  263. x.StockCount,
  264. x.StoredCount,
  265. MaterialCount = 0,
  266. ReleaseCount = 0,
  267. ReleaseLossCount = 0,
  268. ReleaseReturnCount = 0,
  269. })
  270. .Concat(
  271. from x in _context.FmsMaterialRelease
  272. where x.IsConfirmed == true
  273. select new {
  274. Date = x.ReleaseDate.Date,
  275. Year = x.ReleaseDate.Year,
  276. Month = x.ReleaseDate.Month,
  277. x.FmsMaterial,
  278. StockCount = 0,
  279. StoredCount = 0,
  280. MaterialCount = x.MaterialCount.Value,
  281. ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0,
  282. ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0,
  283. ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0,
  284. });
  285. var subquery = from x in query
  286. group x by new { x.FmsMaterial.MaterialId, x.Year, x.Month } into g
  287. orderby g.Key.Year descending, g.Key.Month descending
  288. select new {
  289. g.Key.Year,
  290. g.Key.Month,
  291. g.Key.MaterialId,
  292. Date = g.Max(x => x.Date),
  293. LastStockCount = _context.FmsMaterialStored
  294. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  295. .OrderByDescending(x => x.StoredDate)
  296. .Sum(x => x.StoredCount),
  297. LastStockDate = _context.FmsMaterialStored
  298. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  299. .OrderByDescending(x => x.StoredDate)
  300. .Select(x => x.StoredDate)
  301. .FirstOrDefault(),
  302. // StockAmount = _context.FmsMaterialStored
  303. // .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  304. // .OrderByDescending(x => x.StoredDate)
  305. // .Select(x => (long)x.UnitCost * x.StockCount)
  306. // .FirstOrDefault(),
  307. StockAmount = _context.FmsMaterialStored
  308. .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true)
  309. .Sum(x => (long)x.UnitCost * x.StoredCount),
  310. Name = g.Min(x => x.FmsMaterial.Name),
  311. StoredCount = g.Sum(x => x.StoredCount),
  312. ReleaseCount = g.Sum(x => x.ReleaseCount),
  313. ReleaseLossCount = g.Sum(x => x.ReleaseLossCount),
  314. ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount),
  315. };
  316. var subquery2 = from stock in subquery
  317. join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId
  318. select new MaterialStockHistory {
  319. Date = stock.Date,
  320. Year = stock.Year,
  321. Month = stock.Month,
  322. Material = material,
  323. Warehouse = material.FmsMaterialStored.OrderByDescending(x => x.StoredDate).FirstOrDefault().FmsMaterialWarehouse,
  324. StoredCount = stock.StoredCount,
  325. ReleaseCount = stock.ReleaseCount,
  326. ReleaseLossCount = stock.ReleaseLossCount,
  327. ReleaseReturnCount = stock.ReleaseReturnCount,
  328. LastStockCount = stock.LastStockCount,
  329. LastStockDate = stock.LastStockDate,
  330. StockCount = stock.LastStockCount + (_context.FmsMaterialRelease
  331. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
  332. .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0),
  333. StockAmount = stock.StockAmount + (_context.FmsMaterialRelease
  334. .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true)
  335. .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0),
  336. };
  337. return subquery2;
  338. }
  339. }
  340. }