public List <GroupSparepartStockCardViewModel> RetrieveStockCards(DateTime fromDate, DateTime toDate, int sparepartId) { List <GroupSparepartStockCard> result = new List <GroupSparepartStockCard>(); DateTime lastDay = toDate.AddDays(1).AddSeconds(-1); //result = _sparepartStockCardDetailRepository.RetrieveFIFOCurrentSparepart(fromDate, toDate, sparepartId); List <SparepartStockCardDetail> list = _sparepartStockCardDetailRepository.GetMany(sp => sp.ParentStockCard.PurchaseDate >= fromDate && sp.ParentStockCard.PurchaseDate <= lastDay && (sparepartId > 0 ? sp.ParentStockCard.SparepartId == sparepartId : true)).ToList(); if (list != null) { var spp = from sp in list group sp by new { sp.ParentStockCard.Sparepart, sp.ParentStockCard.SparepartId, sp.Purchasing, sp.PurchasingId, sp.SparepartManualTransaction, sp.SparepartManualTransactionId } into gsp select new GroupSparepartStockCard { LastPurchaseDate = gsp.FirstOrDefault().Purchasing != null?gsp.FirstOrDefault().Purchasing.Date : gsp.FirstOrDefault().SparepartManualTransaction.CreateDate, Sparepart = gsp.Key.Sparepart, SparepartId = gsp.Key.SparepartId, Purchasing = gsp.Key.Purchasing, PurchasingId = gsp.Key.PurchasingId, SparepartManualTransaction = gsp.Key.SparepartManualTransaction, SparepartManualTransactionId = gsp.Key.SparepartManualTransactionId, PricePerItem = gsp.LastOrDefault().PricePerItem, TotalQtyFirst = gsp.FirstOrDefault().QtyFirst, TotalQtyFirstPrice = gsp.FirstOrDefault().QtyFirstPrice, TotalQtyIn = gsp.Sum(g => g.QtyIn), TotalQtyInPrice = gsp.Sum(g => g.QtyInPrice), TotalQtyOut = gsp.Sum(g => g.QtyOut), TotalQtyOutPrice = gsp.Sum(g => g.QtyOutPrice), TotalQtyLast = gsp.LastOrDefault().QtyLast, TotalQtyLastPrice = gsp.LastOrDefault().QtyLastPrice }; result = spp.ToList(); } List <GroupSparepartStockCard> reportResult = result; //check if there are sparepartID not in range of filter, just fill with totalqtyfirst from the day close to start date filter if (sparepartId != 0) { var itemSparepart = _sparepartRepository.GetById(sparepartId); var listPurchasing = _purchasingDetailRepository.GetMany(x => x.SparepartId == sparepartId).Select(x => x.Purchasing); var listSparepartManual = _sparepartManualTransactionRepository.GetMany(x => x.SparepartId == sparepartId); if (reportResult == null || reportResult.Count() == 0) { reportResult = new List <GroupSparepartStockCard>(); } foreach (var itemPurchasing in listPurchasing) { if (reportResult.Where(x => x.PurchasingId == itemPurchasing.Id).Count() == 0) { SparepartStockCardDetail firstInitData = _sparepartStockCardDetailRepository.GetMany(x => x.PurchasingId == itemPurchasing.Id && x.ParentStockCard.PurchaseDate < fromDate).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; newItem.LastPurchaseDate = firstInitData.Purchasing.CreateDate; newItem.Sparepart = firstInitData.ParentStockCard.Sparepart; newItem.SparepartId = firstInitData.ParentStockCard.SparepartId; newItem.Purchasing = firstInitData.Purchasing; newItem.PurchasingId = firstInitData.PurchasingId; newItem.PricePerItem = firstInitData.PricePerItem; newItem.TotalQtyIn = 0; newItem.TotalQtyInPrice = 0; newItem.TotalQtyOut = 0; newItem.TotalQtyOutPrice = 0; newItem.TotalQtyLast = firstInitData.QtyLast; newItem.TotalQtyLastPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } foreach (var itemSpManual in listSparepartManual) { if (reportResult.Where(x => x.SparepartManualTransactionId == itemSpManual.Id).Count() == 0) { SparepartStockCardDetail firstInitData = _sparepartStockCardDetailRepository.GetMany(x => x.SparepartManualTransactionId == itemSpManual.Id && x.ParentStockCard.PurchaseDate < fromDate).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; newItem.LastPurchaseDate = firstInitData.SparepartManualTransaction.CreateDate; newItem.Sparepart = firstInitData.ParentStockCard.Sparepart; newItem.SparepartId = firstInitData.ParentStockCard.SparepartId; newItem.SparepartManualTransaction = firstInitData.SparepartManualTransaction; newItem.SparepartManualTransactionId = firstInitData.SparepartManualTransactionId; newItem.PricePerItem = firstInitData.PricePerItem; newItem.TotalQtyIn = 0; newItem.TotalQtyInPrice = 0; newItem.TotalQtyOut = 0; newItem.TotalQtyOutPrice = 0; newItem.TotalQtyLast = firstInitData.QtyLast; newItem.TotalQtyLastPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } } List <GroupSparepartStockCardViewModel> mappedResult = new List <GroupSparepartStockCardViewModel>(); mappedResult = Map(reportResult, mappedResult); return(mappedResult.OrderBy(x => x.Sparepart.Code).ToList()); }
public List <GroupSparepartStockCardViewModel> RetrieveStockCards(DateTime fromDate, DateTime toDate, int sparepartId) { List <GroupSparepartStockCard> result = new List <GroupSparepartStockCard>(); DateTime lastDay = toDate.AddDays(1).AddSeconds(-1); //result = _sparepartStockCardRepository.RetrieveCurrentStock(fromDate, toDate, sparepartId); List <SparepartStockCard> list = _sparepartStockCardRepository.GetMany(sp => sp.PurchaseDate >= fromDate && sp.PurchaseDate <= lastDay && (sparepartId > 0 ? sp.SparepartId == sparepartId : true)).ToList(); if (list != null && list.Count > 0) { var spp = from sp in list group sp by new { sp.Sparepart, sp.SparepartId, } into gsp select new GroupSparepartStockCard { LastPurchaseDate = gsp.Max(g => g.PurchaseDate), Sparepart = gsp.Key.Sparepart, SparepartId = gsp.Key.SparepartId, PricePerItem = gsp.LastOrDefault().PricePerItem, TotalQtyFirst = gsp.FirstOrDefault().QtyFirst, TotalQtyFirstPrice = gsp.FirstOrDefault().QtyFirstPrice, TotalQtyIn = gsp.Sum(g => g.QtyIn), TotalQtyInPrice = gsp.Sum(g => g.QtyInPrice), TotalQtyOut = gsp.Sum(g => g.QtyOut), TotalQtyOutPrice = gsp.Sum(g => g.QtyOutPrice), TotalQtyLast = gsp.LastOrDefault().QtyLast, TotalQtyLastPrice = gsp.LastOrDefault().QtyLastPrice }; result = spp.ToList(); } List <GroupSparepartStockCard> reportResult = result; //check if there are sparepartID not in range of filter, just fill with totalqtyfirst from the day close to start date filter if (sparepartId == 0) { var listSparepart = _sparepartRepository.GetAll(); if (reportResult == null || reportResult.Count() == 0) { reportResult = new List <GroupSparepartStockCard>(); foreach (var itemSparepart in listSparepart) { SparepartStockCard firstInitData = _sparepartStockCardRepository.GetMany(x => x.SparepartId == itemSparepart.Id && x.PurchaseDate < fromDate).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; newItem.LastPurchaseDate = firstInitData.PurchaseDate; newItem.Sparepart = firstInitData.Sparepart; newItem.SparepartId = firstInitData.SparepartId; newItem.PricePerItem = firstInitData.PricePerItem; newItem.TotalQtyIn = 0; newItem.TotalQtyInPrice = 0; newItem.TotalQtyOut = 0; newItem.TotalQtyOutPrice = 0; newItem.TotalQtyLast = firstInitData.QtyLast; newItem.TotalQtyLastPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } else if (reportResult.Count != listSparepart.Count()) { foreach (var itemSparepart in listSparepart) { if (reportResult.Where(x => x.SparepartId == itemSparepart.Id).Count() == 0) { SparepartStockCard firstInitData = _sparepartStockCardRepository.GetMany(x => x.SparepartId == itemSparepart.Id && x.PurchaseDate < fromDate).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; newItem.LastPurchaseDate = firstInitData.PurchaseDate; newItem.Sparepart = firstInitData.Sparepart; newItem.SparepartId = firstInitData.SparepartId; newItem.PricePerItem = firstInitData.PricePerItem; newItem.TotalQtyIn = 0; newItem.TotalQtyInPrice = 0; newItem.TotalQtyOut = 0; newItem.TotalQtyOutPrice = 0; newItem.TotalQtyLast = firstInitData.QtyLast; newItem.TotalQtyLastPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } } } else { var itemSparepart = _sparepartRepository.GetById(sparepartId); if (reportResult == null || reportResult.Count() == 0) { reportResult = new List <GroupSparepartStockCard>(); SparepartStockCard firstInitData = _sparepartStockCardRepository.GetMany(x => x.SparepartId == sparepartId && x.PurchaseDate < fromDate).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; newItem.LastPurchaseDate = firstInitData.PurchaseDate; newItem.Sparepart = firstInitData.Sparepart; newItem.SparepartId = firstInitData.SparepartId; newItem.PricePerItem = firstInitData.PricePerItem; newItem.TotalQtyIn = 0; newItem.TotalQtyInPrice = 0; newItem.TotalQtyOut = 0; newItem.TotalQtyOutPrice = 0; newItem.TotalQtyLast = firstInitData.QtyLast; newItem.TotalQtyLastPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } List <GroupSparepartStockCardViewModel> mappedResult = new List <GroupSparepartStockCardViewModel>(); mappedResult = Map(reportResult, mappedResult); return(mappedResult.OrderBy(x => x.Sparepart.Code).ToList()); }
public List <GroupSparepartStockCard> RetrieveFIFOCurrentSparepart(DateTime dateFrom, DateTime dateTo, int sparepartId) { var result = from sp in DbSet where sp.ParentStockCard.PurchaseDate >= dateFrom && sp.ParentStockCard.PurchaseDate <= dateTo && (sparepartId > 0 ? sp.ParentStockCard.SparepartId == sparepartId : true) group sp by new { sp.ParentStockCard.Sparepart, sp.ParentStockCard.SparepartId, sp.Purchasing, sp.PurchasingId, sp.SparepartManualTransaction, sp.SparepartManualTransactionId } into gsp select new GroupSparepartStockCard { LastPurchaseDate = gsp.FirstOrDefault().Purchasing != null?gsp.FirstOrDefault().Purchasing.Date : gsp.FirstOrDefault().SparepartManualTransaction.CreateDate, Sparepart = gsp.Key.Sparepart, SparepartId = gsp.Key.SparepartId, PricePerItem = gsp.LastOrDefault().PricePerItem, TotalQtyFirst = gsp.FirstOrDefault().QtyFirst, TotalQtyFirstPrice = gsp.FirstOrDefault().QtyFirstPrice, TotalQtyIn = gsp.Sum(g => g.QtyIn), TotalQtyInPrice = gsp.Sum(g => g.QtyInPrice), TotalQtyOut = gsp.Sum(g => g.QtyOut), TotalQtyOutPrice = gsp.Sum(g => g.QtyOutPrice), TotalQtyLast = gsp.LastOrDefault().QtyLast, TotalQtyLastPrice = gsp.LastOrDefault().QtyLastPrice }; List <GroupSparepartStockCard> reportResult = result.ToList(); //check if there are sparepartID not in range of filter, just fill with totalqtyfirst from the day close to start date filter if (sparepartId == 0) { var listSparepartID = DbSet.Select(x => x.ParentStockCard.SparepartId).Distinct(); if (reportResult == null || reportResult.Count() == 0) { reportResult = new List <GroupSparepartStockCard>(); foreach (var itemSparepartID in listSparepartID) { SparepartStockCardDetail firstInitData = DbSet.Where(x => x.ParentStockCard.SparepartId == itemSparepartID && x.ParentStockCard.PurchaseDate < dateFrom).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } else if (reportResult.Count != listSparepartID.Count()) { foreach (var itemSparepartID in listSparepartID) { if (reportResult.Where(x => x.SparepartId == itemSparepartID).Count() == 0) { SparepartStockCardDetail firstInitData = DbSet.Where(x => x.ParentStockCard.SparepartId == itemSparepartID && x.ParentStockCard.PurchaseDate < dateFrom).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } } } else { if (reportResult == null || reportResult.Count() == 0) { reportResult = new List <GroupSparepartStockCard>(); SparepartStockCardDetail firstInitData = DbSet.Where(x => x.ParentStockCard.SparepartId == sparepartId && x.ParentStockCard.PurchaseDate < dateFrom).LastOrDefault(); if (firstInitData != null) { GroupSparepartStockCard newItem = new GroupSparepartStockCard(); newItem.TotalQtyFirst = firstInitData.QtyLast; newItem.TotalQtyFirstPrice = firstInitData.QtyLastPrice; reportResult.Add(newItem); } } } return(reportResult); }