public IQueryable <ExternalPurchaseDeliveryOrderDurationReportViewModel> GetEPODODurationReportQuery(string unit, string duration, DateTime?dateFrom, DateTime?dateTo, int offset)
{
DateTime DateFrom = dateFrom == null ? new DateTime(1970, 1, 1) : (DateTime)dateFrom;
DateTime DateTo = dateTo == null ? DateTime.Now : (DateTime)dateTo;
int start = 0;
int end = 0;
if (duration == "31-60 hari")
{
start = 31;
end = 60;
}
else if (duration == "61-90 hari")
{
start = 61;
end = 90;
}
else
{
start = 91;
end = 1000;
}
List <ExternalPurchaseDeliveryOrderDurationReportViewModel> listEPODUration = new List <ExternalPurchaseDeliveryOrderDurationReportViewModel>();
var Query = (from a in dbContext.ExternalPurchaseOrders
join b in dbContext.ExternalPurchaseOrderItems on a.Id equals b.EPOId
join c in dbContext.ExternalPurchaseOrderDetails on b.Id equals c.EPOItemId
join d in dbContext.PurchaseRequests on b.PRId equals d.Id
join e in dbContext.InternalPurchaseOrders on b.POId equals e.Id
join f in dbContext.InternalPurchaseOrderItems on e.Id equals f.POId
join g in dbContext.DeliveryOrderItems on a.Id equals g.EPOId
join h in dbContext.DeliveryOrders on g.DOId equals h.Id
//Conditions
where a.IsDeleted == false
//&& b.Id == e.PRItemId
&& b.IsDeleted == false &&
c.IsDeleted == false &&
d.IsDeleted == false &&
e.IsDeleted == false &&
f.IsDeleted == false &&
g.IsDeleted == false &&
h.IsDeleted == false &&
a.UnitId == (string.IsNullOrWhiteSpace(unit) ? a.UnitId : unit) &&
a.CreatedUtc.AddHours(offset).Date >= DateFrom.Date &&
a.CreatedUtc.AddHours(offset).Date <= DateTo.Date
select new ExternalPurchaseDeliveryOrderDurationReportViewModel
{
prNo = d.No,
prDate = d.Date,
prCreatedDate = d.CreatedUtc,
unit = d.UnitName,
category = d.CategoryName,
division = d.DivisionName,
budget = d.BudgetName,
productCode = c.ProductCode,
productName = c.ProductName,
dealQuantity = c.DealQuantity,
dealUomUnit = c.DealUomUnit,
pricePerDealUnit = c.PricePerDealUnit,
supplierCode = a.SupplierCode,
supplierName = a.SupplierName,
poDate = e.CreatedUtc,
orderDate = a.OrderDate,
ePOCreatedDate = a.CreatedUtc,
deliveryDate = a.DeliveryDate,
ePONo = a.EPONo,
dODate = h.DODate,
arrivalDate = h.ArrivalDate,
dONo = h.DONo,
staff = a.CreatedBy,
}).Distinct();
foreach (var item in Query)
{
var ePODate = new DateTimeOffset(item.ePOCreatedDate.Date, TimeSpan.Zero);
var doDate = new DateTimeOffset(item.dODate.Date, TimeSpan.Zero);
var datediff = (((TimeSpan)(doDate - ePODate)).Days) + 1;
ExternalPurchaseDeliveryOrderDurationReportViewModel _new = new ExternalPurchaseDeliveryOrderDurationReportViewModel
{
prNo = item.prNo,
prDate = item.prDate,
prCreatedDate = item.prCreatedDate,
unit = item.unit,
category = item.category,
division = item.division,
budget = item.budget,
productCode = item.productCode,
productName = item.productName,
dealQuantity = item.dealQuantity,
dealUomUnit = item.dealUomUnit,
pricePerDealUnit = item.pricePerDealUnit,
supplierCode = item.supplierCode,
supplierName = item.supplierName,
poDate = item.poDate,
orderDate = item.orderDate,
ePOCreatedDate = item.ePOCreatedDate,
deliveryDate = item.deliveryDate,
ePONo = item.ePONo,
dODate = item.dODate,
arrivalDate = item.arrivalDate,
dONo = item.dONo,
dateDiff = datediff,
staff = item.staff,
};
listEPODUration.Add(_new);
}
return(listEPODUration.Where(s => s.dateDiff >= start && s.dateDiff <= end).AsQueryable());
}
public void Should_Success_Build_ExternalPurchaseDeliveryOrderDurationReportViewModel()
{
var viewModel = new ExternalPurchaseDeliveryOrderDurationReportViewModel();
Assert.NotNull(viewModel);
}
