public IQueryable <OmzetYearBuyerComodityViewModel> GetData(int year) { DateTimeOffset dateFrom = new DateTimeOffset(year - 1, 12, 31, 17, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); var invoicequery = shippingInvoiceRepository.ReadAll(); var invoiceitemquery = shippingInvoiceItemRepository.ReadAll(); var packinglistquery = shippingpackinglistRepository.ReadAll() .Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo); var newQ = (from a in packinglistquery join b in invoicequery on a.Id equals b.PackingListId join c in invoiceitemquery on b.Id equals c.GarmentShippingInvoiceId group new { Qty = c.Quantity, Amt = c.Amount, Uom = c.UomUnit } by new { a.BuyerAgentName, c.ComodityName, } into G select new OmzetYearBuyerComodityViewModel { buyerName = G.Key.BuyerAgentName, comodityName = G.Key.ComodityName, pcsQuantity = G.Where(i => i.Uom == "PCS").Sum(i => i.Qty), setsQuantity = G.Where(i => i.Uom == "SETS").Sum(i => i.Qty), amount = G.Sum(i => i.Amt), }); return(newQ); }
public ListResult <GarmentShippingInvoiceViewModel> Read(int page, int size, string filter, string order, string keyword) { var query = _repository.ReadAll(); List <string> SearchAttributes = new List <string>() { "InvoiceNo", "From", "To", "BuyerAgentName" }; query = QueryHelper <GarmentShippingInvoiceModel> .Search(query, SearchAttributes, keyword); Dictionary <string, object> FilterDictionary = JsonConvert.DeserializeObject <Dictionary <string, object> >(filter); query = QueryHelper <GarmentShippingInvoiceModel> .Filter(query, FilterDictionary); Dictionary <string, string> OrderDictionary = JsonConvert.DeserializeObject <Dictionary <string, string> >(order); query = QueryHelper <GarmentShippingInvoiceModel> .Order(query, OrderDictionary); var data = query .Skip((page - 1) * size) .Take(size) .Select(model => MapToViewModel(model)) .ToList(); return(new ListResult <GarmentShippingInvoiceViewModel>(data, page, size, query.Count())); }
public IQueryable <GarmentInvoiceHistoryMonitoringViewModel> GetData(string buyerAgent, string invoiceNo, DateTime?dateFrom, DateTime?dateTo, int offset) { var queryPL = plrepository.ReadAll(); var queryInv = repository.ReadAll(); var querySI = sirepository.ReadAll(); var queryCL = clrepository.ReadAll(); var queryCA = carepository.ReadAll(); if (!string.IsNullOrWhiteSpace(buyerAgent)) { queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent); } if (!string.IsNullOrWhiteSpace(invoiceNo)) { queryInv = queryInv.Where(w => w.InvoiceNo == invoiceNo); } DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date); queryInv = queryInv.OrderBy(w => w.InvoiceNo); var NewQuery = (from a in queryPL join b in queryInv on a.Id equals b.PackingListId join c in querySI on b.Id equals c.InvoiceId into aa from SI in aa.DefaultIfEmpty() join d in queryCL on SI.InvoiceId equals d.InvoiceId into bb from CL in bb.DefaultIfEmpty() join e in queryCA on CL.InvoiceId equals e.InvoiceId into cc from CA in cc.DefaultIfEmpty() select new GarmentInvoiceHistoryMonitoringViewModel { InvoiceNo = a.InvoiceNo, PLDate = a.Date, InvoiceDate = b.InvoiceDate, TruckingDate = a.TruckingDate, BuyerAgentName = b.BuyerAgentCode + " - " + b.BuyerAgentName, ConsigneeName = b.Consignee, SectionCode = a.SectionCode, Destination = a.Destination, PaymentTerm = a.PaymentTerm, LCNo = a.PaymentTerm == "LC" ? a.LCNo : "-", ShippingStaff = a.ShippingStaffName, Status = a.Status.ToString(), PEBNo = b.PEBNo, PEBDate = b.PEBDate, SIDate = SI == null ? "-" : SI.Date.Day.ToString() + "-" + SI.Date.Month.ToString() + "-" + SI.Date.Year.ToString(), CLDate = CL == null ? "-" : CL.Date.Day.ToString() + "-" + CL.Date.Month.ToString() + "-" + CL.Date.Year.ToString(), CADate = CA == null ? "-" : CA.Date.Day.ToString() + "-" + CA.Date.Month.ToString() + "-" + CA.Date.Year.ToString(), PaymentDate = CA == null ? "-" : CA.PaymentDate.Day.ToString() + "-" + CA.PaymentDate.Month.ToString() + "-" + CA.PaymentDate.Year.ToString(), }); return(NewQuery); }
private OmzetYearBuyerViewModel GetData(int year) { DateTimeOffset dateFrom = new DateTimeOffset(year, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); var invoiceQuery = shippingInvoiceRepository.ReadAll(); var packingListQuery = packingListRepository.ReadAll() .Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo); var joinedData = invoiceQuery.Join(packingListQuery, i => i.PackingListId, p => p.Id, (invoice, packingList) => new JoinedData { buyer = invoice.BuyerAgentCode, items = invoice.Items.Select(i => new JoinedDataItem { uom = i.UomUnit, quantity = i.Quantity, amount = i.Amount }).ToList() }).ToList(); //var joinedData = (from packingList in packingListQuery // join invoice in invoiceQuery on packingList.Id equals invoice.PackingListId // select new JoinedData // { // buyer = invoice.BuyerAgentCode, // items = invoice.Items.Select(i => new JoinedDataItem // { // uom = i.UomUnit, // quantity = i.Quantity, // amount = i.Amount // }).ToList() // }).ToList(); var groupedData = joinedData.GroupBy(g => g.buyer).Select(g => new OmzetYearBuyerItemViewModel { buyer = g.Key, pcsQuantity = g.SelectMany(s => s.items.Where(i => i.uom == "PCS")).Sum(i => i.quantity), setsQuantity = g.SelectMany(s => s.items.Where(i => i.uom == "SETS")).Sum(i => i.quantity), amount = g.SelectMany(s => s.items).Sum(i => i.amount) }); var data = groupedData.ToList(); var result = new OmzetYearBuyerViewModel { totalAmount = data.Sum(s => s.amount), Items = data }; foreach (var item in result.Items) { item.percentage = Math.Round(item.amount / result.totalAmount * 100, 2, MidpointRounding.AwayFromZero); } return(result); }
public virtual async Task <MemoryStreamResult> ReadPdfById(int id) { var data = await _packingListRepository.ReadByIdAsync(id); var PdfTemplate = new GarmentPackingListPdfTemplate(_identityProvider); var fob = _invoiceRepository.ReadAll().Where(w => w.PackingListId == data.Id).Select(s => s.CPrice == "FOB" || s.CPrice == "FCA" ? s.From : s.To).FirstOrDefault(); var cPrice = _invoiceRepository.ReadAll().Where(w => w.PackingListId == data.Id).Select(s => s.CPrice).FirstOrDefault(); var viewModel = MapToViewModel(data); viewModel.ShippingMarkImageFile = await _azureImageService.DownloadImage(IMG_DIR, viewModel.ShippingMarkImagePath); viewModel.SideMarkImageFile = await _azureImageService.DownloadImage(IMG_DIR, viewModel.SideMarkImagePath); viewModel.RemarkImageFile = await _azureImageService.DownloadImage(IMG_DIR, viewModel.RemarkImagePath); var stream = PdfTemplate.GeneratePdfTemplate(viewModel, fob, cPrice); return(new MemoryStreamResult(stream, "Packing List " + data.InvoiceNo + ".pdf")); }
private OmzetYearCountryViewModel GetData(int year) { DateTimeOffset dateFrom = new DateTimeOffset(year, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); var invoicequery = shippingInvoiceRepository.ReadAll(); var packinglistquery = shippingpackinglistRepository.ReadAll() .Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo); var joinedData = invoicequery.Join(packinglistquery, i => i.PackingListId, p => p.Id, (invoice, packinglist) => new JoinedData { destination = packinglist.Destination, items = invoice.Items.Select(i => new JoinedDataItem { quantity = i.Quantity, uom = i.UomUnit, amount = i.Amount }).ToList() }).ToList(); var DetailData = joinedData.SelectMany(s => s.items.Select(i => new { countryname = s.destination, uomunit = i.uom, qty = i.quantity, amnt = i.amount, })); var GroupData = DetailData.GroupBy(g => g.countryname).Select(g => new OmzetYearCountryItemViewModel { country = g.Key, pcsQuantity = g.Where(i => i.uomunit == "PCS").Sum(i => i.qty), setsQuantity = g.Where(i => i.uomunit == "SETS").Sum(i => i.qty), amount = g.Sum(i => i.amnt) }); var data = GroupData.ToList(); var result = new OmzetYearCountryViewModel { totalAmount = data.Sum(s => s.amount), Items = data }; foreach (var item in result.Items) { item.percentage = Math.Round(item.amount / result.totalAmount * 100, 2, MidpointRounding.AwayFromZero); } return(result); }
public IQueryable <GarmentShippingGenerateDataViewModel> GetData(DateTime?dateFrom, DateTime?dateTo, int offset) { var queryInv = repository.ReadAll(); var queryItem = itemrepository.ReadAll(); var queryPL = plrepository.ReadAll(); DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date); queryInv = queryInv.OrderBy(w => w.InvoiceNo); var Query = from a in queryPL join b in queryInv on a.Id equals b.PackingListId join c in queryItem on b.Id equals c.GarmentShippingInvoiceId select new GarmentShippingGenerateDataViewModel { //c.Quantity, c.UomUnit, c.CurrencyCode, c.Price, c.CMTPrice, c.Amount InvoiceNo = a.InvoiceNo, InvoiceDate = b.InvoiceDate, TruckingDate = a.TruckingDate, DueDate = b.SailingDate.AddDays(b.PaymentDue), PaymentTerm = a.PaymentTerm, LCNo = a.LCNo, BuyerCode = a.BuyerAgentCode, BuyerName = a.BuyerAgentName, RONo = c.RONo, SCNo = c.SCNo, Destination = a.Destination, SailingDate = b.SailingDate, CurrencyCode = "USD", ComodityCode = c.ComodityCode, ComodityName = c.ComodityDesc, PEBNo = b.PEBNo == null ? "-" : b.PEBNo, PEBDate = b.PEBNo == null ? new DateTime(1970, 1, 1) : b.PEBDate, Def = a.Omzet == false ? "TIDAK" : "YA", Acc = a.Accounting == false ? "TIDAK" : "YA", Amount = b.TotalAmount, ToBePaid = b.AmountToBePaid, Quantity = c.Quantity, UomUnit = c.UomUnit, Price = c.Price, CMTPrice = c.CMTPrice, SubAmount = c.Amount, }; return(Query); }
public IQueryable <GarmentInvoiceMonitoringViewModel> GetData(string buyerAgent, DateTime?dateFrom, DateTime?dateTo, int offset) { var queryInv = repository.ReadAll(); var queryPL = plrepository.ReadAll(); var queryCA = carepository.ReadAll(); if (!string.IsNullOrWhiteSpace(buyerAgent)) { queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent); } DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date); queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo); var newQ = (from a in queryInv join b in queryPL on a.PackingListId equals b.Id join c in queryCA on a.Id equals c.InvoiceId into dd from CA in dd.DefaultIfEmpty() where a.IsDeleted == false && b.IsDeleted == false && CA.IsDeleted == false //(string.IsNullOrWhiteSpace(invoiceType) ? true : (invoiceType == "DL" ? a.InvoiceNo.Substring(0, 2) == "DL" : a.InvoiceNo.Substring(0, 2) == "SM")) select new GarmentInvoiceMonitoringViewModel { InvoiceNo = a.InvoiceNo, InvoiceDate = a.InvoiceDate, TruckingDate = b.TruckingDate, BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName, ConsigneeName = a.Consignee, SailingDate = a.SailingDate, PEBNo = a.PEBNo, PEBDate = a.PEBDate, OrderNo = a.ConfirmationOfOrderNo, Origin = a.From, Destination = a.To, ShippingStaffName = a.ShippingStaff, Amount = a.TotalAmount, ToBePaid = a.AmountToBePaid, CADate = CA == null ? new DateTime(1970, 1, 1) : CA.Date, PaymentDate = CA == null ? new DateTime(1970, 1, 1) : CA.PaymentDate, //AmountPaid = CA == null ? 0 : CA.PaymentTerm == "TT/OA" ? CA.NettNego + CA.BankCharges + CA.OtherCharge : CA.BankComission + CA.DiscrepancyFee + CA.NettNego + CA.CreditInterest + CA.BankCharges, AmountPaid = CA == null ? 0 : CA.NettNego, }); return(newQ); }
public IQueryable <GarmentLetterOfCreditMonitoringViewModel> GetData(string buyerAgent, string lcNo, DateTime?dateFrom, DateTime?dateTo, int offset) { var queryLC = lcrepository.ReadAll(); var queryInv = invrepository.ReadAll(); var queryPL = plrepository.ReadAll(); if (!string.IsNullOrWhiteSpace(buyerAgent)) { queryLC = queryLC.Where(w => w.ApplicantCode == buyerAgent); } if (!string.IsNullOrWhiteSpace(lcNo)) { queryLC = queryLC.Where(w => w.DocumentCreditNo == lcNo); } DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; queryLC = queryLC.Where(w => w.Date.AddHours(offset).Date >= DateFrom.Date && w.Date.AddHours(offset).Date <= DateTo.Date); queryLC = queryLC.OrderBy(w => w.ApplicantCode); var Query = (from a in queryLC join b in queryInv on a.ApplicantId equals b.BuyerAgentId join c in queryPL on b.PackingListId equals c.Id where a.IsDeleted == false && c.IsDeleted == false && c.IsDeleted == false && c.PaymentTerm == "LC" && a.DocumentCreditNo == c.LCNo select new GarmentLetterOfCreditMonitoringViewModel { LCNo = a.DocumentCreditNo, LCDate = a.Date, IssuedBank = a.IssuedBank, ApplicantName = a.ApplicantCode + " - " + a.ApplicantName, InvoiceNo = c.InvoiceNo, TruckingDate = c.TruckingDate, ExpiredDate = a.ExpireDate, ExpiredPlace = a.ExpirePlace, LatestShipment = a.LatestShipment, LCCondition = a.LCCondition, AmountToBePaid = b.AmountToBePaid, Quantity = a.Quantity, UomUnit = a.UomUnit, AmountLC = a.TotalAmount, }); return(Query); }
public IQueryable <GarmentShippingInstructionMonitoringViewModel> GetData(string buyerAgent, DateTime?dateFrom, DateTime?dateTo, int offset) { var querySI = repository.ReadAll(); var queryIV = invrepository.ReadAll(); var queryCL = clrepository.ReadAll(); var queryPL = plrepository.ReadAll(); if (!string.IsNullOrWhiteSpace(buyerAgent)) { querySI = querySI.Where(w => w.BuyerAgentCode == buyerAgent); } DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; querySI = querySI.Where(w => w.Date.AddHours(offset).Date >= DateFrom.Date && w.Date.AddHours(offset).Date <= DateTo.Date); var newQ = (from a in querySI join b in queryIV on a.InvoiceId equals b.Id join c in queryPL on b.PackingListId equals c.Id join d in queryCL on b.Id equals d.InvoiceId select new GarmentShippingInstructionMonitoringViewModel { InvoiceNo = a.InvoiceNo, SIDate = a.Date, ForwarderCode = a.ForwarderCode, ForwarderName = a.ForwarderName, ShippingStaffName = a.ShippingStaffName, BuyerAgentCode = a.BuyerAgentCode, BuyerAgentName = a.BuyerAgentName, CartonNo = c.TotalCartons, TruckingDate = a.TruckingDate, PortOfDischarge = a.PortOfDischarge, PlaceOfDelivery = a.PlaceOfDelivery, ContainerNo = d.ContainerNo, PCSQuantity = d.PCSQuantity, SETSQuantity = d.SETSQuantity, PACKQuantity = d.PACKQuantity, GrossWeight = c.GrossWeight, NettWeight = c.NettWeight, }); return(newQ); }
public IQueryable <GarmentShipmentMonitoringViewModel> GetData(string buyerAgent, string invoiceNo, DateTime?dateFrom, DateTime?dateTo, int offset) { var queryInv = repository.ReadAll(); var queryItm = itemrepository.ReadAll(); var queryAdj = adjrepository.ReadAll(); var queryPL = plrepository.ReadAll(); var queryCL = clrepository.ReadAll(); var queryCA = carepository.ReadAll(); if (!string.IsNullOrWhiteSpace(buyerAgent)) { queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent); } if (!string.IsNullOrWhiteSpace(invoiceNo)) { queryInv = queryInv.Where(w => w.InvoiceNo == invoiceNo); } DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date); queryInv = queryInv.OrderBy(w => w.InvoiceNo); var NewQ1 = (from a in queryInv join b in queryAdj on a.Id equals b.GarmentShippingInvoiceId into cc from InvAdj in cc.DefaultIfEmpty() join c in queryPL on a.PackingListId equals c.Id where a.IsDeleted == false && c.IsDeleted == false && InvAdj.IsDeleted == false select new GarmentShipmentMonitoringViewModel { InvoiceID = a.Id, InvoiceNo = a.InvoiceNo, InvoiceDate = a.InvoiceDate, TruckingDate = c.TruckingDate, BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName, ConsigneeName = a.Consignee, SectionCode = a.SectionCode, SailingDate = a.SailingDate, CONo = a.CO, PaymentDue = a.PaymentDue, PEBNo = a.PEBNo, PEBDate = a.PEBDate, OriginPort = a.From, DestinationPort = a.To, ShippingStaffName = a.ShippingStaff, AdjustmentValue = InvAdj == null ? 0 : InvAdj.AdjustmentValue, }); var NewQ2 = (from aa in NewQ1 group new { AdjAmt = aa.AdjustmentValue } by new { aa.InvoiceID, aa.InvoiceNo, aa.InvoiceDate, aa.TruckingDate, aa.BuyerAgentName, aa.ConsigneeName, aa.SectionCode, aa.SailingDate, aa.CONo, aa.PaymentDue, aa.PEBNo, aa.PEBDate, aa.OriginPort, aa.DestinationPort, aa.ShippingStaffName, } into G select new GarmentShipmentMonitoringViewModel { InvoiceID = G.Key.InvoiceID, InvoiceNo = G.Key.InvoiceNo, InvoiceDate = G.Key.InvoiceDate, TruckingDate = G.Key.TruckingDate, BuyerAgentName = G.Key.BuyerAgentName, ConsigneeName = G.Key.ConsigneeName, SectionCode = G.Key.SectionCode, SailingDate = G.Key.SailingDate, CONo = G.Key.CONo, PaymentDue = G.Key.PaymentDue, PEBNo = G.Key.PEBNo, PEBDate = G.Key.PEBDate, OriginPort = G.Key.OriginPort, DestinationPort = G.Key.DestinationPort, ShippingStaffName = G.Key.ShippingStaffName, AdjustmentAmount = Math.Round(G.Sum(m => m.AdjAmt), 2), }); var NewQ3 = (from aa in NewQ2 join bb in queryItm on aa.InvoiceID equals bb.GarmentShippingInvoiceId join cc in queryCL on aa.InvoiceID equals cc.InvoiceId into dd from CL in dd.DefaultIfEmpty() join ee in queryCA on aa.InvoiceID equals ee.InvoiceId into ff from CA in ff.DefaultIfEmpty() where bb.IsDeleted == false && CL.IsDeleted == false && CA.IsDeleted == false select new GarmentShipmentMonitoringViewModel { InvoiceID = aa.InvoiceID, InvoiceNo = aa.InvoiceNo, InvoiceDate = aa.InvoiceDate, TruckingDate = aa.TruckingDate, BuyerAgentName = aa.BuyerAgentName, ConsigneeName = aa.ConsigneeName, BuyerBrandName = bb.BuyerBrandName, ComodityName = bb.ComodityCode + " - " + bb.ComodityName, SectionCode = aa.SectionCode, SailingDate = aa.SailingDate, BookingDate = CL == null ? new DateTime(1970, 1, 1) : CL.BookingDate, ExpFactoryDate = CL == null ? new DateTime(1970, 1, 1) : CL.ExportEstimationDate, CONo = aa.CONo, PaymentDue = aa.PaymentDue, PEBNo = aa.PEBNo, PEBDate = aa.PEBDate, OriginPort = aa.OriginPort, DestinationPort = aa.DestinationPort, ShippingStaffName = aa.ShippingStaffName, Amount = bb.Amount, CMTAmount = Convert.ToDecimal(bb.Quantity) * bb.CMTPrice, CMTAmountSub = bb.CMTPrice != 0 ? (Convert.ToDecimal(bb.Quantity) * bb.CMTPrice) : bb.Amount, LessfabricCost = bb.CMTPrice == 0 ? 0 : bb.Amount - (Convert.ToDecimal(bb.Quantity) * bb.CMTPrice), AdjustmentValue = 0, AdjustmentAmount = aa.AdjustmentAmount, EMKLName = CL.Name ?? "-", ForwarderName = CL.ForwarderName ?? "-", DocSendDate = CA == null ? new DateTime(1970, 1, 1) : CA.DocumentSendDate, PaymentDate = CA == null ? new DateTime(1970, 1, 1) : CA.PaymentDate, DueDate = aa.SailingDate.AddDays(aa.PaymentDue), DiffBDCL = CL == null ? 0 : (CL.ExportEstimationDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date - CL.BookingDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date).Days, DiffETDDSD = CA == null ? 0 : (aa.SailingDate.AddDays(5).ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date - CA.DocumentSendDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date).Days, DiffDDPD = CA == null ? 0 : (aa.SailingDate.AddDays(aa.PaymentDue).ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date - CA.PaymentDate.ToOffset(TimeSpan.FromHours(_identityProvider.TimezoneOffset)).Date).Days, }); var Query = (from dd in NewQ3 group dd by new { dd.InvoiceID, dd.InvoiceNo, dd.InvoiceDate, dd.TruckingDate, dd.BuyerAgentName, dd.ConsigneeName, dd.BuyerBrandName, dd.ComodityName, dd.SectionCode, dd.SailingDate, dd.BookingDate, dd.ExpFactoryDate, dd.CONo, dd.PaymentDue, dd.PEBNo, dd.PEBDate, dd.OriginPort, dd.DestinationPort, dd.ShippingStaffName, dd.Amount, dd.CMTAmount, dd.CMTAmountSub, dd.LessfabricCost, dd.AdjustmentValue, dd.AdjustmentAmount, dd.EMKLName, dd.ForwarderName, dd.DocSendDate, dd.PaymentDate, dd.DueDate, dd.DiffBDCL, dd.DiffETDDSD, dd.DiffDDPD, } into G select new GarmentShipmentMonitoringViewModel { InvoiceID = G.Key.InvoiceID, InvoiceNo = G.Key.InvoiceNo, InvoiceDate = G.Key.InvoiceDate, TruckingDate = G.Key.TruckingDate, BuyerAgentName = G.Key.BuyerAgentName, ConsigneeName = G.Key.ConsigneeName, BuyerBrandName = G.Key.BuyerBrandName, ComodityName = G.Key.ComodityName, SectionCode = G.Key.SectionCode, SailingDate = G.Key.SailingDate, BookingDate = G.Key.BookingDate, ExpFactoryDate = G.Key.ExpFactoryDate, CONo = G.Key.CONo, PaymentDue = G.Key.PaymentDue, PEBNo = G.Key.PEBNo, PEBDate = G.Key.PEBDate, OriginPort = G.Key.OriginPort, DestinationPort = G.Key.DestinationPort, ShippingStaffName = G.Key.ShippingStaffName, Amount = G.Key.Amount, CMTAmount = G.Key.CMTAmount, CMTAmountSub = G.Key.CMTAmountSub, LessfabricCost = G.Key.LessfabricCost, AdjustmentValue = 0, AdjustmentAmount = G.Key.AdjustmentAmount, EMKLName = G.Key.EMKLName, ForwarderName = G.Key.ForwarderName, DocSendDate = G.Key.DocSendDate, PaymentDate = G.Key.PaymentDate, DueDate = G.Key.DueDate, DiffBDCL = G.Key.DiffBDCL, DiffETDDSD = G.Key.DiffETDDSD, DiffDDPD = G.Key.DiffDDPD, }); return(Query); }
public List <GarmentCMTSalesViewModel> GetData(string buyerAgent, DateTime?dateFrom, DateTime?dateTo, int offset) { var queryInv = repository.ReadAll(); var quaryInvItem = itemrepository.ReadAll(); var queryPL = plrepository.ReadAll(); var queryCA = carepository.ReadAll(); if (!string.IsNullOrWhiteSpace(buyerAgent)) { queryInv = queryInv.Where(w => w.BuyerAgentCode == buyerAgent); } DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date); queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo); var Query = (from a in queryInv join b in queryPL on a.PackingListId equals b.Id join c in queryCA on a.Id equals c.InvoiceId into dd from CA in dd.DefaultIfEmpty() join d in quaryInvItem on a.Id equals d.GarmentShippingInvoiceId where a.IsDeleted == false && b.IsDeleted == false && CA.IsDeleted == false && a.PEBDate != DateTimeOffset.MinValue && d.CMTPrice > 0 select new GarmentCMTSalesViewModel { InvoiceNo = a.InvoiceNo, Ronos = d.RONo, InvoiceDate = a.InvoiceDate, BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName, PEBDate = a.PEBDate, FOB = a.TotalAmount, FAB = Convert.ToDecimal(d.Quantity) * (d.Price - d.CMTPrice), ToBePaid = a.AmountToBePaid, CurrencyCode = d.CurrencyCode }).ToList(); var newQ = Query.GroupBy(s => new { s.InvoiceNo }).Select(d => new GarmentCMTSalesViewModel() { InvoiceNo = d.Key.InvoiceNo, Ronos = string.Join(",", d.Select(x => x.Ronos)), InvoiceDate = d.FirstOrDefault().InvoiceDate, BuyerAgentName = d.FirstOrDefault().BuyerAgentName, PEBDate = d.FirstOrDefault().PEBDate, FOB = d.FirstOrDefault().FOB, FAB = d.Sum(x => x.FAB), ToBePaid = d.FirstOrDefault().ToBePaid, CurrencyCode = d.FirstOrDefault().CurrencyCode, Quantity = d.Sum(x => x.Quantity) }).ToList(); var currencyFilters = newQ .GroupBy(o => new { o.PEBDate, o.CurrencyCode }) .Select(o => new CurrencyFilter { date = o.Key.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.CurrencyCode }) .ToList(); var currencies = GetCurrecncies(currencyFilters).Result; decimal rate; foreach (var data in newQ) { rate = Convert.ToDecimal(currencies.Where(q => q.code == data.CurrencyCode && q.date <= data.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault()); //rate = 0; data.Rate = rate; data.FOBIdr = rate * data.FOB; //data.FAB = data.Quantity * data.CMTPrice; data.FABIdr = rate * data.FAB; data.ToBePaidIdr = rate * data.ToBePaid; } return(newQ); }
private List <RecapOmzetPerMonthMonitoringViewModel> GetData(int month, int year) { var packingListQuery = packingListRepository.ReadAll(); DateTimeOffset dateFrom = new DateTimeOffset(year, month, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); DateTimeOffset dateTo = new DateTimeOffset(month == 12 ? year + 1 : year, month == 12 ? 1 : month + 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); packingListQuery = packingListQuery.Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo); var joinedQuery = from pl in packingListQuery join inv in shippingInvoiceRepository.ReadAll() on pl.Id equals inv.PackingListId select new JoinedData { packingListId = pl.Id, truckingDate = pl.TruckingDate, buyerAgentName = pl.BuyerAgentName, buyerAgentCode = pl.BuyerAgentCode, invoiceId = inv.Id, invoiceNo = inv.InvoiceNo, invoiceDate = inv.InvoiceDate, pebNo = inv.PEBNo, pebDate = inv.PEBDate, items = inv.Items.Select(i => new JoinedDataItem { comodity = i.ComodityName, quantity = i.Quantity, uom = i.UomUnit, amount = i.Amount, currency = i.CurrencyCode, }) }; var orderedData = joinedQuery.OrderBy(o => o.truckingDate).ToList(); foreach (var data in orderedData) { data.items = from od in data.items group od by new { od.comodity, od.currency, od.uom } into groupedItem select new JoinedDataItem { comodity = groupedItem.Key.comodity, quantity = groupedItem.Sum(i => i.quantity), uom = groupedItem.Key.uom, amount = groupedItem.Sum(i => i.amount), currency = groupedItem.Key.currency, }; } var selectedData = orderedData.SelectMany(inv => inv.items.Select(i => new RecapOmzetPerMonthMonitoringViewModel { packingListId = inv.packingListId, truckingDate = inv.truckingDate, buyerAgentName = inv.buyerAgentName, buyerAgentCode = inv.buyerAgentCode, comodity = i.comodity, invoiceId = inv.invoiceId, invoiceNo = inv.invoiceNo, invoiceDate = inv.invoiceDate, pebNo = inv.pebNo, pebDate = inv.pebDate, quantity = i.quantity, uom = i.uom, amount = i.amount, currency = i.currency })).ToList(); var currencyFilters = selectedData .GroupBy(o => new { o.truckingDate, o.currency }) .Select(o => new CurrencyFilter { date = o.Key.truckingDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.currency }) .ToList(); var currencies = GetCurrecncies(currencyFilters).Result; foreach (var data in selectedData) { data.rate = currencies.Where(q => q.code == data.currency && q.date <= data.truckingDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault(); data.idrAmount = (decimal)data.rate * data.amount; } return(selectedData); }
public virtual async Task <int> Update(int id, GarmentPackingListViewModel viewModel) { viewModel.ShippingMarkImagePath = await UploadImage(viewModel.ShippingMarkImageFile, viewModel.Id, viewModel.ShippingMarkImagePath, viewModel.CreatedUtc); viewModel.SideMarkImagePath = await UploadImage(viewModel.SideMarkImageFile, viewModel.Id, viewModel.SideMarkImagePath, viewModel.CreatedUtc); viewModel.RemarkImagePath = await UploadImage(viewModel.RemarkImageFile, viewModel.Id, viewModel.RemarkImagePath, viewModel.CreatedUtc); GarmentPackingListModel garmentPackingListModel = MapToModel(viewModel); var invoice = _invoiceRepository.ReadAll(); GarmentShippingInvoiceModel shippingInvoice = (from a in invoice where a.InvoiceNo == garmentPackingListModel.InvoiceNo select a).FirstOrDefault(); var invoiceItem = await _invoiceRepository.ReadByIdAsync(shippingInvoice.Id); GarmentShippingInvoiceModel shippingInvoiceItem = invoiceItem; if (shippingInvoiceItem != null) { shippingInvoiceItem.InvoiceDate = garmentPackingListModel.Date; await _invoiceRepository.UpdateAsync(shippingInvoiceItem.Id, shippingInvoiceItem); } foreach (var item in garmentPackingListModel.Items) { foreach (var detail in item.Details) { detail.SetNetNetWeight(detail.NetNetWeight == 0 ? 0.9 * detail.NetWeight : detail.NetNetWeight, _identityProvider.Username, UserAgent); } } var totalNnw = garmentPackingListModel.Items .SelectMany(i => i.Details.Where(d => d.IsDeleted == false).Select(d => new { d.Carton1, d.Carton2, totalNetNetWeight = d.CartonQuantity * d.NetNetWeight })) .GroupBy(g => new { g.Carton1, g.Carton2 }, (key, value) => value.First().totalNetNetWeight).Sum(); garmentPackingListModel.SetNetNetWeight(totalNnw, _identityProvider.Username, UserAgent); return(await _packingListRepository.UpdateAsync(id, garmentPackingListModel)); }
public async Task <PaymentDispositionRecapViewModel> ReadById(int id) { var data = await _recapRepository.ReadByIdAsync(id); var viewModel = MapToViewModel(data); foreach (var item in viewModel.items) { var dispoQuery = _paymentDispositionRepository.ReadAll(); item.paymentDisposition = dispoQuery .Where(w => w.Id == item.paymentDisposition.Id) .Select(s => new GarmentShippingPaymentDispositionViewModel { Id = s.Id, dispositionNo = s.DispositionNo, invoiceNumber = s.InvoiceNumber, invoiceDate = s.InvoiceDate, billValue = s.BillValue, vatValue = s.VatValue, incomeTaxValue = s.IncomeTaxValue, invoiceTaxNumber = s.InvoiceTaxNumber, invoiceDetails = s.InvoiceDetails.Select(d => new GarmentShippingPaymentDispositionInvoiceDetailViewModel { Id = d.Id, invoiceNo = d.InvoiceNo, invoiceId = d.InvoiceId, quantity = d.Quantity, volume = d.Volume, grossWeight = d.GrossWeight, chargeableWeight = d.ChargeableWeight, totalCarton = d.TotalCarton, }).ToList(), amount = s.BillValue + s.VatValue, paid = s.BillValue + s.VatValue - s.IncomeTaxValue + Convert.ToDecimal(item.othersPayment), }) .Single(); var qtyByUnits = new Dictionary <string, double>(); item.paymentDisposition.percentage = new Dictionary <string, double>(); item.paymentDisposition.amountPerUnit = new Dictionary <string, double>(); foreach (var detail in item.paymentDisposition.invoiceDetails) { var invQUery = _invoiceRepository.ReadAll(); detail.invoice = invQUery .Where(w => w.Id == detail.invoiceId) .Select(s => new Invoice { packingListId = s.PackingListId, BuyerAgent = new BuyerAgent { Id = s.BuyerAgentId, Code = s.BuyerAgentCode, Name = s.BuyerAgentName }, items = s.Items.Select(i => new InvoiceItem { unit = i.UnitCode, quantity = i.Quantity }).ToList() }) .Single(); var units = detail.invoice.items.Select(i => i.unit).Distinct(); detail.invoice.unit = string.Join(", ", units); var plQuery = _packingListRepository.ReadAll(); detail.packingList = plQuery .Where(w => w.Id == detail.invoice.packingListId) .Select(s => new PackingList { totalCBM = s.Measurements.Sum(m => m.Length * m.Width * m.Height * m.CartonsQuantity / 1000000) }) .Single(); detail.packingList.totalCBM = Math.Round(detail.packingList.totalCBM, 3, MidpointRounding.AwayFromZero); foreach (var unit in units) { var qtyByUnit = detail.invoice.items.Where(i => i.unit == unit).Sum(i => i.quantity); qtyByUnits[unit] = qtyByUnits.GetValueOrDefault(unit) + qtyByUnit; } } var totalQuantity = item.paymentDisposition.invoiceDetails.Sum(d => d.quantity); foreach (var unit in qtyByUnits.Keys) { item.paymentDisposition.percentage[unit] = Math.Round(qtyByUnits[unit] / (double)totalQuantity * 100, 2, MidpointRounding.AwayFromZero); item.paymentDisposition.amountPerUnit[unit] = Math.Round(item.paymentDisposition.percentage[unit] * (double)item.paymentDisposition.paid / 100, 2, MidpointRounding.AwayFromZero); } } return(viewModel); }
private OmzetYearUnitViewModel GetData(int year) { DateTimeOffset dateFrom = new DateTimeOffset(year, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); DateTimeOffset dateTo = new DateTimeOffset(year + 1, 1, 1, 0, 0, 0, new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)); var invoiceQuery = shippingInvoiceRepository.ReadAll(); var packingListQuery = packingListRepository.ReadAll() .Where(w => w.TruckingDate >= dateFrom && w.TruckingDate < dateTo); var joinedData = invoiceQuery.Join(packingListQuery, i => i.PackingListId, p => p.Id, (invoice, packingList) => new JoinedData { month = packingList.TruckingDate, items = invoice.Items.Select(i => new JoinedDataItem { unit = i.UnitId, amount = i.Amount }).ToList() }).ToList(); var filterUnit = new Dictionary <string, string>() { { "(" + string.Join(" || ", joinedData.SelectMany(s => s.items.Select(i => "Id==\"" + i.unit + "\"")).Distinct().OrderBy(o => o).ToHashSet()) + ")", "true" }, }; var masterUnits = GetUnits(filterUnit).Result; var selectedData = joinedData.SelectMany(s => s.items.Select(i => new SelectedData { month = MONTH_NAMES[s.month.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).Month - 1], unit = masterUnits.Where(w => w.Id == i.unit).Select(a => a.Name).FirstOrDefault() ?? "-", amount = i.amount })).ToList(); var units = selectedData.Select(s => s.unit).Distinct().OrderBy(o => o).ToHashSet(); List <OmzetYearUnitTableViewModel> tables = new List <OmzetYearUnitTableViewModel>(); foreach (var month in MONTH_NAMES) { OmzetYearUnitTableViewModel header = new OmzetYearUnitTableViewModel { month = month, items = new Dictionary <string, decimal>() }; foreach (var unit in units) { header.items[unit] = selectedData.Where(w => w.month == month && w.unit == unit).Sum(s => s.amount); } tables.Add(header); } Dictionary <string, decimal> totals = new Dictionary <string, decimal>(); Dictionary <string, decimal> averages = new Dictionary <string, decimal>(); foreach (var unit in units) { var total = selectedData.Where(w => w.unit == unit).Sum(s => s.amount); totals[unit] = total; averages[unit] = Math.Round(total / 12, 2, MidpointRounding.AwayFromZero); } var result = new OmzetYearUnitViewModel { units = units, tables = tables, totals = totals, averages = averages }; return(result); }
public List <GarmentDetailOmzetByUnitReportViewModel> GetData(string unit, DateTime?dateFrom, DateTime?dateTo, int offset) { var queryInv = repository.ReadAll(); var quaryInvItem = itemrepository.ReadAll(); var queryPL = plrepository.ReadAll(); DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; var expendGood = GetExpenditureGood(DateFrom, DateTo, unit, offset); var ROs = expendGood.Select(x => x.RONo).ToArray(); var invo = expendGood.Select(x => x.Invoice).ToArray(); queryInv = queryInv.Where(x => x.PEBDate != DateTimeOffset.MinValue); //queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date); queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo); List <GarmentDetailOmzetByUnitReportViewModel> omzetgmt = new List <GarmentDetailOmzetByUnitReportViewModel>(); var Queryshipping = (from a in queryInv join b in quaryInvItem on a.Id equals b.GarmentShippingInvoiceId join c in queryPL on a.PackingListId equals c.Id where ROs.Contains(b.RONo) && a.PEBDate != DateTimeOffset.MinValue select new GarmentDetailOmzetByUnitReportViewModel { Urutan = "A", PEBDate = a.PEBDate, TruckingDate = c.TruckingDate, RONumber = b.RONo, InvoiceNo = a.InvoiceNo, Quantity = b.UomUnit.Substring(0, 3) == "SET" || b.UomUnit.Substring(0, 3) == "PAC" ? b.Quantity * 2 : b.Quantity, UOMUnit = b.UomUnit, CurrencyCode = b.CurrencyCode, Amount = b.Amount, }).Distinct().ToList(); var Query = (from a in expendGood join b in Queryshipping on new { invoice = a.Invoice.Trim(), rono = a.RONo.Trim() } equals new { invoice = b.InvoiceNo.Trim(), rono = b.RONumber.Trim() } into omzets from bb in omzets.DefaultIfEmpty() select new GarmentDetailOmzetByUnitReportViewModel { Urutan = "A", InvoiceNo = a.Invoice.TrimEnd(), PEBDate = bb == null ? DateTimeOffset.MinValue : bb.PEBDate, TruckingDate = bb == null ? DateTimeOffset.MinValue : bb.TruckingDate, BuyerAgentName = a.Buyer.Code.TrimEnd() + " - " + a.Buyer.Name.TrimEnd(), ComodityName = a.Comodity.Name.TrimEnd(), UnitCode = a.Unit.Code.TrimEnd(), RONumber = a.RONo.TrimEnd(), Quantity = 0, UOMUnit = "PCS", CurrencyCode = bb == null ? "-" : bb.CurrencyCode, Amount = bb == null ? 0 : bb.Amount, ArticleStyle = a.Article.TrimEnd(), //ExpenditureGoodNo = a.ExpenditureGoodNo.TrimEnd(), QuantityInPCS = a.TotalQuantity, }).Distinct().ToList(); //.GroupBy(x=> new { x.Urutan, x.InvoiceNo, x.PEBDate, x.TruckingDate, x.BuyerAgentName, x.ComodityName, x.UnitCode, x.RONumber, x.UOMUnit, x.CurrencyCode, x.ArticleStyle, x.ExpenditureGoodNo, x.QuantityInPCS }, (key, group) => new GarmentDetailOmzetByUnitReportViewModel { // Urutan = key.Urutan, // InvoiceNo = key.InvoiceNo, // PEBDate = key.PEBDate, // TruckingDate = key.TruckingDate, // BuyerAgentName = key.BuyerAgentName, // ComodityName = key.ComodityName, // UnitCode = key.UnitCode, // RONumber = key.RONumber, // Quantity = group.Sum(x => x.Quantity), // UOMUnit = key.UOMUnit, // CurrencyCode = key.CurrencyCode, // Amount = group.Sum(x => x.Amount), // ArticleStyle = key.ArticleStyle, // ExpenditureGoodNo = key.ExpenditureGoodNo, // QuantityInPCS = key.QuantityInPCS, //}) //var Query2 = (from a in expendGood // join b in Queryshipping on new { invoice = a.Invoice.Trim(), rono = a.RONo.Trim() } equals new { invoice = b.InvoiceNo.Trim(), rono = b.RONumber.Trim() } into omzets // from bb in omzets.DefaultIfEmpty() // select new GarmentDetailOmzetByUnitReportViewModel // { // Urutan = "A", // InvoiceNo = a.Invoice.TrimEnd(), // PEBDate = bb == null ? DateTimeOffset.MinValue : bb.PEBDate, // TruckingDate = bb == null ? DateTimeOffset.MinValue : bb.TruckingDate, // BuyerAgentName = a.Buyer.Code + " - " + a.Buyer.Name, // ComodityName = a.Comodity.Name, // UnitCode = a.Unit.Code, // RONumber = a.RONo, // Quantity = 0, // UOMUnit = "PCS", // CurrencyCode = bb == null ? "-" : bb.CurrencyCode, // Amount = bb == null ? 0 : bb.Amount, // ArticleStyle = a.Article, // ExpenditureGoodNo = "", // QuantityInPCS = 0, // }).GroupBy(x=> new { x.Urutan, x.InvoiceNo, x.PEBDate, x.TruckingDate, x.BuyerAgentName, x.ComodityName, x.UnitCode, x.RONumber, x.UOMUnit, x.CurrencyCode, x.ArticleStyle, x.ExpenditureGoodNo, x.Amount }, (key, group) => new GarmentDetailOmzetByUnitReportViewModel { // Urutan = key.Urutan, // InvoiceNo = key.InvoiceNo, // PEBDate = key.PEBDate, // TruckingDate = key.TruckingDate, // BuyerAgentName = key.BuyerAgentName, // ComodityName = key.ComodityName, // UnitCode = key.UnitCode, // RONumber = key.RONumber, // Quantity = group.Sum(x => x.Quantity), // UOMUnit = key.UOMUnit, // CurrencyCode = key.CurrencyCode, // Amount = key.Amount, // ArticleStyle = key.ArticleStyle, // ExpenditureGoodNo = key.ExpenditureGoodNo, // QuantityInPCS = group.Sum(x => x.QuantityInPCS), // }).Where(x => x.InvoiceNo == "DL/211521").Distinct().ToList(); //var dataunion = Query.Union(Query2).AsEnumerable(); //var omzet = dataunion.GroupBy(x => new { x.Urutan, x.InvoiceNo, x.PEBDate, x.TruckingDate, x.BuyerAgentName, x.ComodityName, x.UnitCode, x.RONumber, x.UOMUnit, x.CurrencyCode, x.ArticleStyle, x.ExpenditureGoodNo }, (key, group) => new GarmentDetailOmzetByUnitReportViewModel //{ // Urutan = key.Urutan, // InvoiceNo = key.InvoiceNo, // PEBDate = key.PEBDate, // TruckingDate = key.TruckingDate, // BuyerAgentName = key.BuyerAgentName, // ComodityName = key.ComodityName, // UnitCode = key.UnitCode, // RONumber = key.RONumber, // Quantity = group.Sum(x => x.Quantity), // UOMUnit = key.UOMUnit, // CurrencyCode = key.CurrencyCode, // Amount = group.Sum(x => x.Amount), // ArticleStyle = key.ArticleStyle, // ExpenditureGoodNo = key.ExpenditureGoodNo, // QuantityInPCS = group.Sum(x => x.QuantityInPCS), //}).ToList(); //var Query = (from a in queryInv // join b in quaryInvItem on a.Id equals b.GarmentShippingInvoiceId // join c in queryPL on a.PackingListId equals c.Id // where a.IsDeleted == false && b.IsDeleted == false // && b.UnitCode == (string.IsNullOrWhiteSpace(unit) ? b.UnitCode : unit) // && a.PEBDate != DateTimeOffset.MinValue // select new GarmentDetailOmzetByUnitReportViewModel // { // Urutan = "A", // InvoiceNo = a.InvoiceNo, // PEBDate = a.PEBDate, // TruckingDate = c.TruckingDate, // BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName, // ComodityName = b.ComodityName, // UnitCode = b.UnitCode, // RONumber = b.RONo, // Quantity = b.Quantity, // UOMUnit = b.UomUnit, // CurrencyCode = b.CurrencyCode, // Amount = b.Amount, // }).ToList(); // var currencyFilters = Query .GroupBy(o => new { o.PEBDate, o.CurrencyCode }) .Select(o => new CurrencyFilter { date = o.Key.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.CurrencyCode }) .ToList(); var currencies = GetCurrecncies(currencyFilters).Result; decimal rate; foreach (var data in Query) { rate = Convert.ToDecimal(currencies.Where(q => q.code == data.CurrencyCode && q.date <= data.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault()); data.Rate = rate; data.AmountIDR = rate * data.Amount; //var GarmentExpenditureGood = expendGood.Where(x => x.RONo == data.RONumber && x.Invoice == data.InvoiceNo && x.TotalQuantity == data.QuantityInPCS).FirstOrDefault(); //data.ExpenditureGoodNo = expendGood.Where(x => x.RONo == data.RONumber && x.Invoice == data.InvoiceNo && x.TotalQuantity == data.QuantityInPCS).FirstOrDefault().ExpenditureGoodNo; } // //foreach (GarmentDetailOmzetByUnitReportViewModel i in Query) //{ // var data1 = GetExpenditureGood(i.RONumber); // omzetgmt.Add(new GarmentDetailOmzetByUnitReportViewModel // { // Urutan = i.Urutan, // InvoiceNo = i.InvoiceNo, // PEBDate = i.PEBDate, // TruckingDate = i.TruckingDate, // BuyerAgentName = i.BuyerAgentName, // ComodityName = i.ComodityName, // UnitCode = i.UnitCode, // RONumber = i.RONumber, // UOMUnit = i.UOMUnit, // Quantity = i.Quantity, // CurrencyCode = i.CurrencyCode, // Amount = i.Amount, // Rate = i.Rate, // AmountIDR = i.AmountIDR, // ArticleStyle = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().Article, // ExpenditureGoodNo = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().ExpenditureGoodNo, // QuantityInPCS = i.UOMUnit.Substring(0, 3) == "SET" || i.UOMUnit.Substring(0, 3) == "PAC" ? i.Quantity * 2 : i.Quantity, // }); //}; return(Query.ToList()); }
public List <GarmentDetailOmzetByUnitReportViewModel> GetData(string unit, DateTime?dateFrom, DateTime?dateTo, int offset) { var queryInv = repository.ReadAll(); var quaryInvItem = itemrepository.ReadAll(); var queryPL = plrepository.ReadAll(); DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom; DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo; queryPL = queryPL.Where(w => w.TruckingDate.AddHours(offset).Date >= DateFrom.Date && w.TruckingDate.AddHours(offset).Date <= DateTo.Date); queryInv = queryInv.OrderBy(w => w.BuyerAgentCode).ThenBy(b => b.InvoiceNo); List <GarmentDetailOmzetByUnitReportViewModel> omzetgmt = new List <GarmentDetailOmzetByUnitReportViewModel>(); var Query = (from a in queryInv join b in quaryInvItem on a.Id equals b.GarmentShippingInvoiceId join c in queryPL on a.PackingListId equals c.Id where a.IsDeleted == false && b.IsDeleted == false && b.UnitCode == (string.IsNullOrWhiteSpace(unit) ? b.UnitCode : unit) select new GarmentDetailOmzetByUnitReportViewModel { InvoiceNo = a.InvoiceNo, PEBDate = a.PEBDate, TruckingDate = c.TruckingDate, BuyerAgentName = a.BuyerAgentCode + " - " + a.BuyerAgentName, ComodityName = b.ComodityName, UnitCode = b.UnitCode, RONumber = b.RONo, Quantity = b.UomUnit.Substring(0, 3) == "SET" ? b.Quantity * 2 : b.Quantity, UOMUnit = b.UomUnit.Substring(0, 3) == "SET" ? "PCS" : b.UomUnit, CurrencyCode = b.CurrencyCode, Amount = b.Amount, }).ToList(); // var currencyFilters = Query .GroupBy(o => new { o.PEBDate, o.CurrencyCode }) .Select(o => new CurrencyFilter { date = o.Key.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime, code = o.Key.CurrencyCode }) .ToList(); var currencies = GetCurrecncies(currencyFilters).Result; decimal rate; foreach (var data in Query) { rate = Convert.ToDecimal(currencies.Where(q => q.code == data.CurrencyCode && q.date <= data.PEBDate.ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).DateTime).Select(s => s.rate).LastOrDefault()); data.Rate = rate; data.AmountIDR = rate * data.Amount; } // foreach (GarmentDetailOmzetByUnitReportViewModel i in Query) { var data1 = GetExpenditureGood(i.RONumber); omzetgmt.Add(new GarmentDetailOmzetByUnitReportViewModel { InvoiceNo = i.InvoiceNo, PEBDate = i.PEBDate, TruckingDate = i.TruckingDate, BuyerAgentName = i.BuyerAgentName, ComodityName = i.ComodityName, UnitCode = i.UnitCode, RONumber = i.RONumber, UOMUnit = i.UOMUnit, Quantity = i.Quantity, CurrencyCode = i.CurrencyCode, Amount = i.Amount, Rate = i.Rate, AmountIDR = i.AmountIDR, ArticleStyle = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().Article, ExpenditureGoodNo = data1 == null || data1.Count == 0 ? "-" : data1.FirstOrDefault().ExpenditureGoodNo, }); } ; return(omzetgmt); }
public List <GarmentFinanceExportSalesJournalViewModel> GetReportQuery(int month, int year, int offset) { DateTime dateFrom = new DateTime(year, month, 1); int nextYear = month == 12 ? year + 1 : year; int nextMonth = month == 12 ? 1 : month + 1; DateTime dateTo = new DateTime(nextYear, nextMonth, 1); List <GarmentFinanceExportSalesJournalViewModel> data = new List <GarmentFinanceExportSalesJournalViewModel>(); var queryInv = repository.ReadAll(); var queryPL = plrepository.ReadAll() .Where(w => w.TruckingDate.AddHours(offset).Date >= dateFrom && w.TruckingDate.AddHours(offset).Date < dateTo.Date && (w.InvoiceType == "DL" || w.InvoiceType == "DS" || w.InvoiceType == "DLR" || w.InvoiceType == "SMR")); var joinQuery = from a in queryInv join b in queryPL on a.PackingListId equals b.Id where a.IsDeleted == false && b.IsDeleted == false select new dataQuery { InvoiceType = b.InvoiceType, TotalAmount = a.TotalAmount, PEBDate = a.PEBDate }; List <dataQuery> dataQuery = new List <dataQuery>(); foreach (var invoice in joinQuery.ToList()) { GarmentCurrency currency = GetCurrencyPEBDate(invoice.PEBDate.Date.ToShortDateString()); var rate = currency != null?Convert.ToDecimal(currency.rate) : 0; invoice.Rate = rate; dataQuery.Add(invoice); } var join = from a in dataQuery select new GarmentFinanceExportSalesJournalViewModel { remark = a.InvoiceType == "DL" || a.InvoiceType == "DS" ? " PNJ. BR. JADI EXPORT LANGSUNG" : " PNJ. LAIN-LAIN EXPORT LANGSUNG", credit = a.TotalAmount * a.Rate, debit = 0, account = a.InvoiceType == "DL" || a.InvoiceType == "DS" ? "5024.00.4.00" : "5026.00.4.00" }; var debit = new GarmentFinanceExportSalesJournalViewModel { remark = "PIUTANG USAHA EXPORT", credit = 0, debit = join.Sum(a => a.credit), account = "1103.00.5.00" }; data.Add(debit); var sumquery = join.ToList() .GroupBy(x => new { x.remark, x.account }, (key, group) => new { Remark = key.remark, Account = key.account, Credit = group.Sum(s => s.credit) }).OrderBy(a => a.Remark); foreach (var item in sumquery) { var obj = new GarmentFinanceExportSalesJournalViewModel { remark = item.Remark, credit = item.Credit, debit = 0, account = item.Account }; data.Add(obj); } var total = new GarmentFinanceExportSalesJournalViewModel { remark = "", credit = join.Sum(a => a.credit), debit = join.Sum(a => a.credit), account = "" }; data.Add(total); return(data); }