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 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 <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);
}
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 <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());
}