using System; using System.Collections.Generic; using System.Linq; using System.Linq.Dynamic.Core; using FMSAdmin.Data; using FMSAdmin.Helpers; using FMSAdmin.Entities; using Microsoft.Extensions.Logging; using FMSAdmin.Models; using System.Linq.Expressions; using LinqKit; using System.Transactions; namespace FMSApp.Services { public class MaterialService { private readonly ILogger _logger; private readonly FMSContext _context; private readonly StorageHelper _storage; public MaterialService( ILogger logger, FMSContext context, StorageHelper storage) { _logger = logger; _context = context; _storage = storage; } public void Create(FmsMaterial data) { using (var trans = new TransactionScope()) { checkName(data); if (data.CmFile != null && data.CmFile.IsUpload) { data.CmFile.SiteId = data.SiteId; var category = _context.CmFileCategory.First(x => x.Name == "material"); data.CmFile.FileCategory = category; data.CmFile.CreatedDate = DateTime.Now; } else { data.CmFile = null; } _context.FmsMaterial.Add(data); _context.SaveChanges(); if (data.CmFile != null && data.CmFile.IsUpload) { // 카피 _storage.CopyEntity(data.CmFile.Path, data.CmFile); } trans.Complete(); } } public int checkName(FmsMaterial data, bool isExcel = false) { var check = _context.FmsMaterial.Where(x => x.SiteId == data.SiteId && x.BusinessFieldId == data.BusinessFieldId && x.Name == data.Name && x.Standard == data.Standard).Count(); if (!isExcel && check > 0) { throw new ServiceException("동일 정보가 존재합니다. [업무분야-자재명-규격]"); } return check; } public void Edit(int id, int siteId, FmsMaterial data) { using (var trans = new TransactionScope()) { if (data.CmFile != null && data.CmFile.IsUpload) { data.CmFile.SiteId = data.SiteId; var category = _context.CmFileCategory.First(x => x.Name == "material"); data.CmFile.FileCategory = category; data.CmFile.CreatedDate = DateTime.Now; } else { data.CmFile = null; } var persist = _context.FmsMaterial .Where(x => x.MaterialId == id && x.SiteId == siteId).FirstOrDefault(); if (persist == null) { throw new ServiceException("정보를 찾을 수 없습니다."); } if (!persist.BusinessFieldId.Equals(data.BusinessFieldId)) { checkName(data); } if (!persist.Name.Equals(data.Name)) { checkName(data); } if (!Util.IsNullOrEmpty(data.Standard)) { if (!persist.Standard.Equals(data.Standard)) checkName(data); } persist.SiteId = data.SiteId; persist.BusinessFieldId = data.BusinessFieldId; persist.Name = data.Name; persist.MaterialTypeId = data.MaterialTypeId; persist.Standard = data.Standard; persist.FirstClassId = data.FirstClassId; persist.SecondClassId = data.SecondClassId; persist.ThirdClassId = data.ThirdClassId; persist.MaterialCode = data.MaterialCode; persist.Unit = data.Unit; persist.DurableYears = data.DurableYears; //persist.Rfid = data.Rfid; persist.Manufacturer = data.Manufacturer; persist.FinalPrice = data.FinalPrice; persist.ReasonableStockCount = data.ReasonableStockCount; persist.IsDiscontinued = data.IsDiscontinued; persist.IsUse = data.IsUse; persist.Note = data.Note; _context.FmsMaterial.Update(persist); _context.SaveChanges(); if (data.CmFile != null && data.CmFile.IsUpload) { // 카피 _storage.CopyEntity(data.CmFile.Path, persist.CmFile); if (persist.CmFile != null) { // 파일 삭제시 진짜 DB삭제? } } trans.Complete(); } } public IQueryable GetAll() { var query = _context.FmsMaterial; return query; } public FmsMaterial Get(int id, int siteId) { var data = _context.FmsMaterial.Single(x => x.MaterialId == id && x.SiteId == siteId); return data; } public IQueryable Gets(int id, int siteId) { var data = _context.FmsMaterial.Where(x => x.MaterialId == id && x.SiteId == siteId); return data; } public void Delete(int id, int siteId) { var data = _context.FmsMaterial.First(x => x.MaterialId == id && x.SiteId == siteId); _context.FmsMaterial.Remove(data); _context.SaveChanges(); } public IQueryable Stock() { var query = from x in _context.FmsMaterial.AsExpandable() select new MaterialStock { Material = x, StockCount = FmsMaterial.GetStockCount.Invoke(x), StockAmount = FmsMaterial.GetStockAmount.Invoke(x), }; return query; } public IQueryable DailyStock() { var query = ( from x in _context.FmsMaterialStored where x.IsApproval == true select new { Date = x.StoredDate.Date, x.FmsMaterial, x.StockCount, x.StoredCount, MaterialCount = 0, ReleaseCount = 0, ReleaseLossCount = 0, ReleaseReturnCount = 0, }) .Concat( from x in _context.FmsMaterialRelease where x.IsConfirmed == true select new { Date = x.ReleaseDate.Date, x.FmsMaterial, StockCount = 0, StoredCount = 0, MaterialCount = x.MaterialCount.Value, ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0, ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0, ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0, }); var subquery = from x in query group x by new { x.FmsMaterial.MaterialId, x.Date } into g orderby g.Key.Date descending select new { g.Key.Date, g.Key.MaterialId, LastStockCount = _context.FmsMaterialStored .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true) .OrderByDescending(x => x.StoredDate) .Select(x => x.StockCount) .FirstOrDefault(), LastStockDate = _context.FmsMaterialStored .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true) .OrderByDescending(x => x.StoredDate) .Select(x => x.StoredDate) .FirstOrDefault(), StockAmount = _context.FmsMaterialStored .Where(x => x.StoredDate.Date <= g.Key.Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true) .OrderByDescending(x => x.StoredDate) .Select(x => (long)x.UnitCost * x.StockCount) .FirstOrDefault(), Name = g.Min(x => x.FmsMaterial.Name), StoredCount = g.Sum(x => x.StoredCount), ReleaseCount = g.Sum(x => x.ReleaseCount), ReleaseLossCount = g.Sum(x => x.ReleaseLossCount), ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount), }; var subquery2 = from stock in subquery join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId select new MaterialStockHistory { Date = stock.Date, Material = material, StoredCount = stock.StoredCount, ReleaseCount = stock.ReleaseCount, ReleaseLossCount = stock.ReleaseLossCount, ReleaseReturnCount = stock.ReleaseReturnCount, LastStockCount = stock.LastStockCount, LastStockDate = stock.LastStockDate, StockCount = stock.LastStockCount + (_context.FmsMaterialRelease .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true) .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0), StockAmount = stock.StockAmount + (_context.FmsMaterialRelease .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == material.MaterialId && x.IsConfirmed == true) .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0) }; return subquery2; } public IQueryable MonthlyStock() { var query = ( from x in _context.FmsMaterialStored where x.IsApproval == true select new { Date = x.StoredDate.Date, Year = x.StoredDate.Year, Month = x.StoredDate.Month, x.FmsMaterial, x.StockCount, x.StoredCount, MaterialCount = 0, ReleaseCount = 0, ReleaseLossCount = 0, ReleaseReturnCount = 0, }) .Concat( from x in _context.FmsMaterialRelease where x.IsConfirmed == true select new { Date = x.ReleaseDate.Date, Year = x.ReleaseDate.Year, Month = x.ReleaseDate.Month, x.FmsMaterial, StockCount = 0, StoredCount = 0, MaterialCount = x.MaterialCount.Value, ReleaseCount = x.AdjustmentTypeId == 0 ? x.MaterialCount.Value : 0, ReleaseLossCount = x.AdjustmentTypeId == 1 ? x.MaterialCount.Value : 0, ReleaseReturnCount = x.AdjustmentTypeId == 2 ? x.MaterialCount.Value : 0, }); var subquery = from x in query group x by new { x.FmsMaterial.MaterialId, x.Year, x.Month } into g orderby g.Key.Year descending, g.Key.Month descending select new { g.Key.Year, g.Key.Month, g.Key.MaterialId, Date = g.Max(x => x.Date), LastStockCount = _context.FmsMaterialStored .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true) .OrderByDescending(x => x.StoredDate) .Select(x => x.StockCount) .FirstOrDefault(), LastStockDate = _context.FmsMaterialStored .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true) .OrderByDescending(x => x.StoredDate) .Select(x => x.StoredDate) .FirstOrDefault(), StockAmount = _context.FmsMaterialStored .Where(x => x.StoredDate.Date <= g.Max(x => x.Date).Date && x.MaterialId == g.Key.MaterialId && x.IsApproval == true) .OrderByDescending(x => x.StoredDate) .Select(x => (long)x.UnitCost * x.StockCount) .FirstOrDefault(), Name = g.Min(x => x.FmsMaterial.Name), StoredCount = g.Sum(x => x.StoredCount), ReleaseCount = g.Sum(x => x.ReleaseCount), ReleaseLossCount = g.Sum(x => x.ReleaseLossCount), ReleaseReturnCount = g.Sum(x => x.ReleaseReturnCount), }; var subquery2 = from stock in subquery join material in _context.FmsMaterial on stock.MaterialId equals material.MaterialId select new MaterialStockHistory { Date = stock.Date, Year = stock.Year, Month = stock.Month, Material = material, StoredCount = stock.StoredCount, ReleaseCount = stock.ReleaseCount, ReleaseLossCount = stock.ReleaseLossCount, ReleaseReturnCount = stock.ReleaseReturnCount, LastStockCount = stock.LastStockCount, LastStockDate = stock.LastStockDate, StockCount = stock.LastStockCount + (_context.FmsMaterialRelease .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true) .Sum(x => x.AdjustmentTypeId == 2 ? x.MaterialCount : -x.MaterialCount) ?? 0), StockAmount = stock.StockAmount + (_context.FmsMaterialRelease .Where(x => x.ReleaseDate.Date <= stock.Date && x.MaterialId == stock.MaterialId && x.IsConfirmed == true) .Sum(x => x.AdjustmentTypeId == 2 ? x.ReleaseTotalCost : -x.ReleaseTotalCost) ?? 0), }; return subquery2; } } }