public ListResult <GarmentShippingInstructionViewModel> Read(int page, int size, string filter, string order, string keyword)
{
var query = _shippingInstructionRepository.ReadAll();
List <string> SearchAttributes = new List <string>()
{
"InvoiceNo", "ForwarderName", "ATTN", "ShippedBy", "BuyerAgentName"
};
query = QueryHelper <GarmentShippingInstructionModel> .Search(query, SearchAttributes, keyword);
Dictionary <string, object> FilterDictionary = JsonConvert.DeserializeObject <Dictionary <string, object> >(filter);
query = QueryHelper <GarmentShippingInstructionModel> .Filter(query, FilterDictionary);
Dictionary <string, string> OrderDictionary = JsonConvert.DeserializeObject <Dictionary <string, string> >(order);
query = QueryHelper <GarmentShippingInstructionModel> .Order(query, OrderDictionary);
var data = query
.Skip((page - 1) * size)
.Take(size)
.Select(model => MapToViewModel(model))
.ToList();
return(new ListResult <GarmentShippingInstructionViewModel>(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);
}
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);
}