public List <GarmentInternalNoteDto> GetGarmentInternalNotes(string keyword, GarmentInternalNoteFilterDto filter) { //var internalNoteQuery = _dbContext.GarmentInternNotes.Where(entity => entity.Position <= PurchasingGarmentExpeditionPosition.Purchasing || entity.Position == PurchasingGarmentExpeditionPosition.SendToPurchasing); var internalNoteQuery = _dbContext.GarmentInternNotes.AsQueryable(); if (filter.PositionIds == null) { internalNoteQuery = internalNoteQuery.Where(entity => entity.Position <= PurchasingGarmentExpeditionPosition.Purchasing); } else { internalNoteQuery = internalNoteQuery.Where(entity => filter.PositionIds.Contains((int)entity.Position)); } if (!string.IsNullOrWhiteSpace(keyword)) { internalNoteQuery = internalNoteQuery.Where(entity => entity.INNo.Contains(keyword)); } var internalNotes = internalNoteQuery.Select(entity => new { entity.Id, entity.INNo, entity.INDate, entity.SupplierId, entity.SupplierName, entity.CurrencyCode, entity.CurrencyId }).Take(10).ToList(); var internalNoteIds = internalNotes.Select(element => element.Id).ToList(); var internalNoteItems = _dbContext.GarmentInternNoteItems.Where(entity => internalNoteIds.Contains(entity.GarmentINId)).Select(entity => new { entity.Id, entity.GarmentINId, entity.InvoiceId }).ToList(); var internalNoteItemIds = internalNoteItems.Select(element => element.Id).ToList(); var internalNoteDetails = _dbContext.GarmentInternNoteDetails.Where(entity => internalNoteItemIds.Contains(entity.GarmentItemINId)).Select(entity => new { entity.Id, entity.GarmentItemINId, entity.PaymentDueDate, entity.DOId, entity.PaymentType, entity.PaymentMethod, entity.PaymentDueDays }).ToList(); var doIds = internalNoteDetails.Select(element => element.DOId).ToList(); var corrections = _dbContext.GarmentCorrectionNotes.Where(entity => doIds.Contains(entity.DOId)).Select(entity => new { entity.Id, entity.TotalCorrection, entity.CorrectionType, entity.DOId }); var correctionIds = corrections.Select(element => element.Id).ToList(); var correctionItems = _dbContext.GarmentCorrectionNoteItems.Where(entity => correctionIds.Contains(entity.GCorrectionId)).Select(entity => new { entity.Id, entity.PricePerDealUnitAfter, entity.Quantity, entity.GCorrectionId }); var invoiceIds = internalNoteItems.Select(element => element.InvoiceId).ToList(); var invoices = _dbContext.GarmentInvoices.Where(entity => invoiceIds.Contains(entity.Id)).Select(entity => new { entity.Id, entity.IsPayTax, entity.IsPayVat, entity.UseIncomeTax, entity.UseVat, entity.IncomeTaxRate, entity.TotalAmount, entity.InvoiceNo }).ToList(); var result = internalNotes.Select(internalNote => { var selectedInternalNoteItems = internalNoteItems.Where(element => element.GarmentINId == internalNote.Id).ToList(); var selectedInternalNoteItemIds = selectedInternalNoteItems.Select(element => element.Id).ToList(); var selectedInvoiceIds = selectedInternalNoteItems.Select(element => element.InvoiceId).ToList(); var internalNoteDetail = internalNoteDetails.Where(element => selectedInternalNoteItemIds.Contains(element.GarmentItemINId)).OrderByDescending(element => element.PaymentDueDate).FirstOrDefault(); var selectedInternalNoteDetails = internalNoteDetails.Where(element => selectedInternalNoteItemIds.Contains(element.GarmentItemINId)).ToList(); var selectedDOIds = selectedInternalNoteDetails.Select(element => element.DOId).ToList(); var selectedCorrections = corrections.Where(element => selectedDOIds.Contains(element.DOId)).ToList(); var amountDPP = invoices.Where(element => selectedInvoiceIds.Contains(element.Id)).Sum(element => element.TotalAmount); var invoicesNo = string.Join('\n', invoices.Where(element => selectedInvoiceIds.Contains(element.Id)).Select(element => $"- {element.InvoiceNo}")); var correctionAmount = selectedCorrections.Sum(element => { var selectedCorrectionItems = correctionItems.Where(item => item.GCorrectionId == element.Id); var total = 0.0; if (element.CorrectionType.ToUpper() == "RETUR") { total = (double)selectedCorrectionItems.Sum(item => item.PricePerDealUnitAfter * item.Quantity); } else { total = (double)element.TotalCorrection; } return(total); }); var totalAmount = invoices.Where(element => selectedInvoiceIds.Contains(element.Id)).Sum(element => { var total = element.TotalAmount; if (element.UseVat && element.IsPayVat) { total += element.TotalAmount * 0.1; } if (element.UseIncomeTax && element.IsPayTax) { total -= element.TotalAmount * (element.IncomeTaxRate / 100); } return(total); }); totalAmount += correctionAmount; var vatTotal = invoices.Where(element => selectedInvoiceIds.Contains(element.Id)).Sum(element => { var vat = 0.0; if (element.UseVat && element.IsPayVat) { vat += element.TotalAmount * 0.1; } return(vat); }); var incomeTaxTotal = invoices.Where(element => selectedInvoiceIds.Contains(element.Id)).Sum(element => { var incomeTax = 0.0; if (element.UseIncomeTax && element.IsPayTax) { incomeTax += element.TotalAmount * (element.IncomeTaxRate / 100); } return(incomeTax); }); //var productInvoice = invoices.Where(element => selectedInvoiceIds.Contains(element.Id)).Select(element => //{ // var firstProduct = element.entity.Items; // if (firstProduct != null) // return firstProduct.FirstOrDefault().Details.Select(details => new { details.ProductId, details.ProductName, details.ProductCode }).FirstOrDefault(); // else // return null; //}); //var productInvoiceFirst = productInvoice.Count() <= 0 ? null : productInvoice.FirstOrDefault(); //var productNameInvoiceFirst = productInvoiceFirst == null ? string.Empty : productInvoiceFirst.ProductName; //var productIdInvoiceFirst = productInvoiceFirst == null ? 0 : productInvoiceFirst.ProductId; //var productCodeInvoiceFirst = productInvoiceFirst == null ? string.Empty : productInvoiceFirst.ProductCode; var invoiceFirst = invoices.FirstOrDefault(); return(new GarmentInternalNoteDto((int)internalNote.Id, internalNote.INNo, internalNote.INDate, internalNoteDetail.PaymentDueDate, (int)internalNote.SupplierId, internalNote.SupplierName, vatTotal, incomeTaxTotal, totalAmount, (int)internalNote.CurrencyId, internalNote.CurrencyCode, amountDPP, correctionAmount, internalNoteDetail.PaymentType, internalNoteDetail.PaymentMethod, internalNoteDetail.PaymentDueDays, invoicesNo)); }).ToList(); return(result); }
public List <GarmentInternalNoteDetailsDto> GetGarmentInternNotesDetails(string keyword, GarmentInternalNoteFilterDto filter) { var invoiceInfo = _dbContext.GarmentInvoiceDetails.Include(t => t.GarmentInvoiceItem).ThenInclude(t => t.GarmentInvoice); var deliveryOrderInfo = _dbContext.GarmentDeliveryOrders; var externalPurchasOrder = _dbContext.GarmentExternalPurchaseOrders; var invoiceInfoHeader = _dbContext.GarmentInvoices; var internalNoteQuery = _dbContext.GarmentInternNotes.Include(s => s.Items).ThenInclude(s => s.Details).Where(s => !s.IsPphPaid) .Select(element => new GarmentInternalNoteDetailsDto { Active = element.Active, DeletedAgent = element.DeletedAgent, CreatedAgent = element.CreatedAgent, LastModifiedAgent = element.LastModifiedAgent, CreatedBy = element.CreatedBy, CreatedUtc = element.CreatedUtc, CurrencyCode = element.CurrencyCode, CurrencyId = element.CurrencyId.GetValueOrDefault(), CurrencyRate = element.CurrencyRate, DeletedBy = element.DeletedBy, DeletedUtc = element.DeletedUtc, Id = element.Id, INDate = element.INDate, INId = element.Id, INNo = element.INNo, IsCreatedVB = element.IsCreatedVB, SupplierId = element.SupplierId.GetValueOrDefault(), SupplierName = element.SupplierName, IsDeleted = element.IsDeleted, SupplierCode = element.SupplierCode, LastModifiedBy = element.LastModifiedBy, LastModifiedUtc = element.LastModifiedUtc, Position = element.Position, Remark = element.Remark, UId = element.UId, INDueDate = element.Items == null ? DateTimeOffset.MinValue : element.Items.SelectMany(item => item.Details).Count() <= 0 ? DateTimeOffset.MinValue : element.Items.SelectMany(item => item.Details).OrderByDescending(detail => detail.PaymentDueDate).FirstOrDefault().PaymentDueDate, TotalAmount = element.Items == null ? 0 : element.Items.Sum(item => item.TotalAmount), Items = element.Items == null ? new List <GarmentInternNoteItemsInvoiceDto>() : element.Items.Select(elementItem => new GarmentInternNoteItemsInvoiceDto { Active = elementItem.Active, CreatedAgent = elementItem.CreatedAgent, CreatedBy = elementItem.CreatedBy, LastModifiedAgent = elementItem.LastModifiedAgent, TotalAmount = elementItem.TotalAmount, CreatedUtc = elementItem.CreatedUtc, DeletedAgent = elementItem.DeletedAgent, DeletedBy = elementItem.DeletedBy, DeletedUtc = elementItem.DeletedUtc, Id = elementItem.Id, InvoiceDate = elementItem.InvoiceDate, InvoiceId = elementItem.InvoiceId, InvoiceNo = elementItem.InvoiceNo, IsDeleted = elementItem.IsDeleted, LastModifiedBy = elementItem.LastModifiedBy, LastModifiedUtc = elementItem.LastModifiedUtc, GarmentInvoice = invoiceInfoHeader.FirstOrDefault(s => s.Id == elementItem.InvoiceId), TotalIncomeTax = _dbContext.GarmentInvoices.FirstOrDefault(s => s.Id == elementItem.InvoiceId) == null ? 0 : (_dbContext.GarmentInvoices.FirstOrDefault(s => s.Id == elementItem.InvoiceId).IncomeTaxRate / 100) * elementItem.TotalAmount, Details = elementItem.Details == null ? new List <GarmentNoteItemsInvoiceDetailsDto>() : elementItem.Details.Select(elementDetails => new GarmentNoteItemsInvoiceDetailsDto { DOId = elementDetails.DOId, DONo = elementDetails.DONo, EPOId = elementDetails.EPOId, EPONo = elementDetails.EPONo, POSerialNumber = elementDetails.POSerialNumber, RONo = elementDetails.RONo, PaymentMethod = elementDetails.PaymentMethod, PaymentType = elementDetails.PaymentType, PaymentDueDays = elementDetails.PaymentDueDays, PaymentDueDate = elementDetails.PaymentDueDate, DODate = elementDetails.DODate, InvoiceDetailId = elementDetails.InvoiceDetailId, ProductCode = elementDetails.ProductCode, ProductId = elementDetails.ProductId, ProductName = elementDetails.ProductName, ProductCategory = externalPurchasOrder.FirstOrDefault(s => s.Id == elementDetails.EPOId).Category, Quantity = elementDetails.Quantity, UnitId = elementDetails.UnitId, UnitCode = elementDetails.UnitCode, UnitName = elementDetails.UnitName, UOMId = elementDetails.UOMId, UOMUnit = elementDetails.UOMUnit, PricePerDealUnit = elementDetails.PricePerDealUnit, PriceTotal = elementDetails.PriceTotal, InvoiceNo = elementItem.InvoiceNo, InvoiceId = elementItem.InvoiceId, InvoiceDate = elementItem.InvoiceDate, InvoiceTotalAmount = elementItem.TotalAmount, GarmentInvoiceDetail = invoiceInfo.FirstOrDefault(s => s.Id == elementDetails.InvoiceDetailId), GarmentDeliveryOrder = deliveryOrderInfo.FirstOrDefault(s => s.Id == elementDetails.DOId), }).ToList() //elementItem.Details.GroupBy( // productKey => productKey.DOId, // grpProduct => grpProduct, // (productKey, grpProduct) => //new GarmentNoteItemsInvoiceDetailsDto //{ // DOId = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().DOId, // DONo = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().DONo, // EPOId = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().EPOId, // EPONo = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().EPONo, // POSerialNumber = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().POSerialNumber, // RONo = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().RONo, // PaymentMethod = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().PaymentMethod, // PaymentType = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().PaymentType, // PaymentDueDays = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().PaymentDueDays, // PaymentDueDate = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().PaymentDueDate, // DODate = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().DODate, // InvoiceDetailId = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().InvoiceDetailId, // ProductCode = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().ProductCode, // ProductId = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().ProductId.GetValueOrDefault(), // ProductName = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().ProductName, // Quantity = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().Quantity, // UnitId = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().UnitId, // UnitCode = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().UnitCode, // UnitName = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().UnitName, // UOMId = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().UOMId.GetValueOrDefault(), // UOMUnit = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().UOMUnit, // PricePerDealUnit = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().PricePerDealUnit, // PriceTotal = grpProduct.OrderByDescending(t => t.PaymentDueDate).FirstOrDefault().PriceTotal, // InvoiceId = elementItem.InvoiceId, // InvoiceDate = elementItem.InvoiceDate, // InvoiceNo = elementItem.InvoiceNo, // InvoiceTotalAmount = elementItem.TotalAmount, //}).ToList() }) .ToList() }).AsQueryable(); if (filter.PositionIds == null && filter.isPPHMenu != 1) { internalNoteQuery = internalNoteQuery.Where(entity => entity.Position <= PurchasingGarmentExpeditionPosition.Purchasing || entity.Position == PurchasingGarmentExpeditionPosition.SendToPurchasing); } else { if (filter.isPPHMenu != 1) { internalNoteQuery = internalNoteQuery.Where(entity => filter.PositionIds.Contains((int)entity.Position)); } else { internalNoteQuery = internalNoteQuery.Where(entity => (entity.Position == PurchasingGarmentExpeditionPosition.AccountingAccepted || entity.Position == PurchasingGarmentExpeditionPosition.CashierAccepted)); } } if (filter.IncomeTaxId != null) { internalNoteQuery = internalNoteQuery.Where(entity => entity.Items.Any(item => filter.IncomeTaxId.Select(t => Convert.ToInt64(t)).Contains(item.GarmentInvoice.IncomeTaxId))); } if (filter.CurrencyCode != null) { internalNoteQuery = internalNoteQuery.Where(entity => filter.CurrencyCode.Contains(entity.CurrencyCode)); } if (!string.IsNullOrWhiteSpace(keyword)) { internalNoteQuery = internalNoteQuery.Where(entity => entity.INNo.Contains(keyword)); } //var internalNotes = internalNoteQuery.Select(entity => new //{ // entity.Id, // entity.INNo, // entity.INDate, // entity.SupplierId, // entity.SupplierName, // entity.CurrencyCode, // entity.CurrencyId //}).Take(10).ToList(); return(internalNoteQuery.ToList()); }