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);
}
public IQueryable <GarmentCreditAdviceMonitoringViewModel> GetData(string buyerAgent, string invoiceNo, string paymentTerm, DateTime?dateFrom, DateTime?dateTo, int offset)
{
var queryCA = carepository.ReadAll();
if (!string.IsNullOrWhiteSpace(buyerAgent))
{
queryCA = queryCA.Where(w => w.BuyerName == buyerAgent);
}
if (!string.IsNullOrWhiteSpace(invoiceNo))
{
queryCA = queryCA.Where(w => w.InvoiceNo == invoiceNo);
}
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
queryCA = queryCA.Where(w => w.PaymentDate.AddHours(offset).Date >= DateFrom.Date && w.PaymentDate.AddHours(offset).Date <= DateTo.Date);
queryCA = queryCA.OrderBy(w => w.InvoiceNo);
var Query = (from a in queryCA
where a.PaymentTerm == (string.IsNullOrWhiteSpace(paymentTerm) ? a.PaymentTerm : paymentTerm)
select new GarmentCreditAdviceMonitoringViewModel
{
CAId = a.Id,
InvoiceNo = a.InvoiceNo,
InvoiceDate = a.Date,
PaymentDate = a.PaymentDate,
DocUploadDate = a.DocumentSendDate,
PaymentTerm = a.PaymentTerm,
Amount = a.Amount,
ToBePaid = a.AmountToBePaid,
PaidAmount = a.AmountPaid,
BalanceAmount = a.BalanceAmount,
NettNego = a.NettNego + a.BankCharges + a.OtherCharge + a.BankComission + a.DiscrepancyFee + a.CreditInterest,
BuyerName = a.BuyerName,
BuyerAddress = a.BuyerAddress,
BankName = a.BankAccountName,
NettNegoTT = a.PaymentTerm == "TT/OA" ? a.NettNego : 0,
BankChargeTT = a.PaymentTerm == "TT/OA" ? a.BankCharges : 0,
OtherChargeTT = a.PaymentTerm == "TT/OA" ? a.OtherCharge : 0,
SRNo = a.PaymentTerm == "LC" ? a.SRNo : "-",
SRDate = a.PaymentTerm == "LC" ? a.NegoDate : new DateTime(1970, 1, 1),
LCNo = a.PaymentTerm == "LC" ? a.LCNo : "-",
NettNegoLC = a.PaymentTerm == "LC" ? a.NettNego : 0,
BankChargeLC = a.PaymentTerm == "LC" ? a.BankCharges : 0,
BankComissionLC = a.PaymentTerm == "LC" ? a.BankComission : 0,
DiscreapancyFeeLC = a.PaymentTerm == "LC" ? a.DiscrepancyFee : 0,
CreditInterestLC = a.PaymentTerm == "LC" ? a.CreditInterest : 0,
});
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 ListResult <IndexViewModel> Read(int page, int size, string filter, string order, string keyword)
{
var query = _repository.ReadAll();
List <string> SearchAttributes = new List <string>()
{
"InvoiceNo", "BuyerName", "BankAccountName"
};
query = QueryHelper <GarmentShippingCreditAdviceModel> .Search(query, SearchAttributes, keyword);
Dictionary <string, object> FilterDictionary = JsonConvert.DeserializeObject <Dictionary <string, object> >(filter);
query = QueryHelper <GarmentShippingCreditAdviceModel> .Filter(query, FilterDictionary);
Dictionary <string, string> OrderDictionary = JsonConvert.DeserializeObject <Dictionary <string, string> >(order);
query = QueryHelper <GarmentShippingCreditAdviceModel> .Order(query, OrderDictionary);
var data = query
.Skip((page - 1) * size)
.Take(size)
.Select(model => new IndexViewModel
{
id = model.Id,
invoiceNo = model.InvoiceNo,
date = model.Date,
amount = model.Amount,
amountToBePaid = model.AmountToBePaid,
buyerName = model.BuyerName,
bankAccountName = model.BankAccountName
})
.ToList();
return(new ListResult <IndexViewModel>(data, page, size, query.Count()));
}
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);
}
