private List <SuggestedRoute> GetHistoryRoutes(string location1C, string location2C, string containerSizeI, string containerTypeC)
{
var expenses = from p in _expenseDetailRepository.GetAllQueryable()
where p.IsIncluded == "1" && p.IsRequested == "0"
group p by new { p.OrderD, p.OrderNo, p.DetailNo, p.DispatchNo }
into g
select new
{
g.Key.OrderD,
g.Key.OrderNo,
g.Key.DetailNo,
g.Key.DispatchNo,
TotalExpense = g.Sum(p => p.Amount)
};
var driverAllowances = from p in _allowanceDetailRepository.GetAllQueryable()
group p by new { p.OrderD, p.OrderNo, p.DetailNo, p.DispatchNo }
into g
select new
{
g.Key.OrderD,
g.Key.OrderNo,
g.Key.DetailNo,
g.Key.DispatchNo,
TotalExpense = g.Sum(p => p.Amount)
};
var dispatches = from a in _dispatchRepository.GetAllQueryable()
join b in _orderDRepository.GetAllQueryable() on new { a.OrderD, a.OrderNo, a.DetailNo }
equals new { b.OrderD, b.OrderNo, b.DetailNo } into t1
from b in t1.DefaultIfEmpty()
join c in _orderHRepository.GetAllQueryable() on new { a.OrderD, a.OrderNo }
equals new { c.OrderD, c.OrderNo } into t2
from c in t2.DefaultIfEmpty()
join d in _customerRepository.GetAllQueryable() on new { c.CustomerMainC, c.CustomerSubC }
equals new { d.CustomerMainC, d.CustomerSubC } into t3
from d in t3.DefaultIfEmpty()
join f in _fuelConsumptionDetailRepository.GetAllQueryable() on new { a.OrderD, a.OrderNo, a.DetailNo, a.DispatchNo }
equals new { f.OrderD, f.OrderNo, f.DetailNo, f.DispatchNo } into t4
from f in t4.DefaultIfEmpty()
join e in expenses on new { a.OrderD, a.OrderNo, a.DetailNo, a.DispatchNo }
equals new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo } into t5
from e in t5.DefaultIfEmpty()
join w in driverAllowances on new { a.OrderD, a.OrderNo, a.DetailNo, a.DispatchNo }
equals new { w.OrderD, w.OrderNo, w.DetailNo, w.DispatchNo } into t6
from w in t6.DefaultIfEmpty()
where ((string.IsNullOrEmpty(a.Location1C) & !string.IsNullOrEmpty(a.Location2C) &
(a.Location2C == location1C ||
(!string.IsNullOrEmpty(a.Location3C) && a.Location3C == location2C))) ||
(!string.IsNullOrEmpty(a.Location1C) & (a.Location1C == location1C ||
(!string.IsNullOrEmpty(a.Location3C) && a.Location3C == location2C) ||
(string.IsNullOrEmpty(a.Location3C) && !string.IsNullOrEmpty(a.Location2C) && a.Location2C == location2C)))) &
b.ContainerSizeI == containerSizeI &
b.ContainerTypeC == containerTypeC & a.DispatchStatus == Constants.CONFIRMED
select new SuggestedRoute()
{
OrderD = a.OrderD,
OrderNo = a.OrderNo,
DetailNo = a.DetailNo,
DispatchNo = a.DispatchNo,
CustomerN = d.CustomerN,
Location1C = !string.IsNullOrEmpty(a.Location1C) ? a.Location1C : a.Location2C,
Location1N = !string.IsNullOrEmpty(a.Location1N) ? a.Location1N : a.Location2N,
Location2C = !string.IsNullOrEmpty(a.Location3C) ? a.Location3C : a.Location2C,
Location2N = !string.IsNullOrEmpty(a.Location3N) ? a.Location3N : a.Location2N,
ContainerSizeI = b.ContainerSizeI,
ContainerTypeC = b.ContainerTypeC,
IsEmpty = f != null ? f.IsEmpty : "",
IsHeavy = f != null ? f.IsHeavy : "",
IsSingle = f != null ? f.IsSingle : "",
IsHistoryRoute = true,
TotalExpense = (e != null ? e.TotalExpense : 0) + (w != null ? w.TotalExpense : 0)
};
if (dispatches.Any())
{
var suggestedRoutes = dispatches.ToList();
return(suggestedRoutes);
}
return(null);
}
public FuelConsumptionDetailDatatable GetFuelConsumptionDetail(FuelConsumptionDetailSearchParams searchParams)
{
var searchInfo = searchParams.SearchInfo;
var newTruckCList = "," + searchInfo.TruckCList + ",";
var newDriverCList = "," + searchInfo.DriverCList + ",";
var dispatches = from a in _dispatchRepository.GetAllQueryable()
join b in _orderHRepository.GetAllQueryable() on new { a.OrderD, a.OrderNo }
equals new { b.OrderD, b.OrderNo } into ab
from b in ab.DefaultIfEmpty()
where ((searchInfo.TransportDFrom == null || a.TransportD >= searchInfo.TransportDFrom) &&
(searchInfo.TransportDTo == null || a.TransportD <= searchInfo.TransportDTo)) &
(string.IsNullOrEmpty(searchInfo.TruckCList) || newTruckCList.Contains("," + a.TruckC + ",")) &
(string.IsNullOrEmpty(searchInfo.DriverCList) || newDriverCList.Contains("," + a.DriverC + ",")) &
(string.IsNullOrEmpty(searchInfo.DepC) || searchInfo.DepC == "0" || b.OrderDepC == searchInfo.DepC) &
((searchInfo.DispatchStatus && a.DispatchStatus == Constants.DISPATCH) ||
(searchInfo.TransportedStatus && (a.DispatchStatus == Constants.TRANSPORTED || a.DispatchStatus == Constants.CONFIRMED))) &
(a.DispatchI == "0")
select new
{
a.OrderD,
a.OrderNo,
a.DispatchNo,
a.DetailNo,
a.TransportD,
a.DriverC,
a.TruckC,
a.ContainerStatus,
a.Location1C,
a.Location1N,
a.Location2C,
a.Location2N,
a.Location3C,
a.Location3N
};
var dispatchOrdered = dispatches.OrderBy("TransportD desc");
var dispatchPaged = dispatchOrdered.Skip((searchParams.page - 1) * searchParams.itemsPerPage).Take(searchParams.itemsPerPage).ToList();
var fuelConsumptionDetail = from a in dispatchPaged
join b in _orderDRepository.GetAllQueryable() on new { a.OrderD, a.OrderNo, a.DetailNo }
equals new { b.OrderD, b.OrderNo, b.DetailNo } into ab
from b in ab.DefaultIfEmpty()
join c in _driverRepository.GetAllQueryable() on a.DriverC equals c.DriverC into abc
from c in abc.DefaultIfEmpty()
join d in _containerTypeRepository.GetAllQueryable() on b.ContainerTypeC equals d.ContainerTypeC into abcd
from d in abcd.DefaultIfEmpty()
join e in _truckRepository.GetAllQueryable() on a.TruckC equals e.TruckC into abcde
from e in abcde.DefaultIfEmpty()
join f in _fuelConsumptionDetailRepository.GetAllQueryable() on new { a.OrderD, a.OrderNo, a.DetailNo, a.DispatchNo }
equals new { f.OrderD, f.OrderNo, f.DetailNo, f.DispatchNo } into abcdef
from f in abcdef.DefaultIfEmpty()
select new FuelConsumptionDetailViewModel()
{
OrderD = a.OrderD,
OrderNo = a.OrderNo,
DispatchNo = a.DispatchNo,
DetailNo = a.DetailNo,
TransportD = a.TransportD,
ContainerStatus = a.ContainerStatus,
DriverN = c != null ? c.LastN + " " + c.FirstN : "",
TruckC = a.TruckC,
RegisteredNo = e != null ? e.RegisteredNo : "",
ContainerSizeI = b.ContainerSizeI,
ContainerTypeN = d != null ? d.ContainerTypeN : "",
GrossWeight = b != null ? b.GrossWeight : 0,
Location1C = a.Location1C,
Location1N = a.Location1N,
Location2C = a.Location2C,
Location2N = a.Location2N,
Location3C = a.Location3C,
Location3N = a.Location3N,
ApproximateDistance = f != null ? f.Distance : 0,
IsEmpty = f != null ? f.IsEmpty : "",
IsHeavy = f != null ? f.IsHeavy : "",
IsSingle = f != null ? f.IsSingle : "",
FuelConsumption = f != null ? f.FuelConsumption : 0,
UnitPrice = f != null ? f.UnitPrice : 0,
Amount = f != null ? f.Amount : 0,
};
return(new FuelConsumptionDetailDatatable()
{
Data = fuelConsumptionDetail.ToList(),
Total = dispatches.Count()
});
}
