public DateTime?GetRevenueDateMax(string customerMainC, string customerSubC)
{
var orderD = from h in _orderRepository.GetAllQueryable()
join d in _containerRepository.GetAllQueryable() on new { h.OrderD, h.OrderNo }
equals new { d.OrderD, d.OrderNo } into t2
from d in t2.DefaultIfEmpty()
where (h.CustomerMainC == customerMainC & h.CustomerSubC == customerSubC)
select new { Date = d.RevenueD };
var revenueD = orderD.OrderByDescending(i => i.Date).FirstOrDefault();
return(revenueD != null ? revenueD.Date : null);
}
private List <LiabilitiesExpenseViewModel> GetExpenseList(DateTime date, string driverC, int liabilitiesNo)
{
//var result = (from o in _containerRepository .GetAllQueryable()
// join d in _dispatchRepository.GetAllQueryable()
// on new { o.OrderD, o.OrderNo, o.DetailNo } equals new { d.OrderD, d.OrderNo, d.DetailNo }
// join e in _expenseDetailRepository.GetAllQueryable()
// on new { d.OrderD, d.OrderNo, d.DetailNo, d.DispatchNo } equals new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo }
// join m in _expenseRepository.GetAllQueryable()
// on e.ExpenseC equals m.ExpenseC
// where (d.DriverC == driverC &&
// d.TransportD < EntityFunctions.AddDays(date,1) &&
// e.PaymentMethodI == Constants.CASH)
// select new LiabilitiesExpenseViewModel()
// {
// ContainerNo = o.ContainerNo,
// TransportD =d.TransportD,
// ContainerSizeN = o.ContainerSizeI == Constants.CONTAINERSIZE1 ? Constants.CONTAINERSIZE1N :
// o.ContainerSizeI == Constants.CONTAINERSIZE2 ? Constants.CONTAINERSIZE2N :
// Constants.CONTAINERSIZE3N,
// ExpenseC = m.ExpenseC,
// ExpenseN =m.ExpenseN,
// Amount = e.Amount
// }
// );
var result = (from e in _expenseDetailRepository.GetAllQueryable()
join d in _dispatchRepository.GetAllQueryable()
on new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo } equals new { d.OrderD, d.OrderNo, d.DetailNo, d.DispatchNo } into ed
from d in ed.DefaultIfEmpty()
join o in _containerRepository.GetAllQueryable()
on new { d.OrderD, d.OrderNo, d.DetailNo } equals new { o.OrderD, o.OrderNo, o.DetailNo } into edo
from o in edo.DefaultIfEmpty()
join or in _orderRepository.GetAllQueryable()
on new { e.OrderD, e.OrderNo } equals new { or.OrderD, or.OrderNo } into edor
from or in edor.DefaultIfEmpty()
join c in _customerRepository.GetAllQueryable()
on new { or.CustomerMainC, or.CustomerSubC } equals new { c.CustomerMainC, c.CustomerSubC } into cedor
from c in cedor.DefaultIfEmpty()
join m in _expenseRepository.GetAllQueryable()
on e.ExpenseC equals m.ExpenseC into mcedor
from m in mcedor.DefaultIfEmpty()
join f in _liabilitiesItemRepository.GetAllQueryable()
on new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo, e.ExpenseNo } equals new { f.OrderD, f.OrderNo, f.DetailNo, f.DispatchNo, f.ExpenseNo } into fmcedor
from f in fmcedor.DefaultIfEmpty()
where (d.DriverC == driverC &&
d.TransportD < EntityFunctions.AddDays(date, 1) &&
e.PaymentMethodI == Constants.CASH) &&
(f == null || (f.LiabilitiesD == date && f.LiabilitiesNo == liabilitiesNo))
select new LiabilitiesExpenseViewModel()
{
OrderD = e.OrderD,
OrderNo = e.OrderNo,
DetailNo = e.DetailNo,
DispatchNo = e.DispatchNo,
ExpenseNo = e.ExpenseNo,
ContainerNo = o.ContainerNo,
TransportD = d.TransportD,
ContainerSizeI = o.ContainerSizeI,
//ContainerSizeN = o.ContainerSizeI == Constants.CONTAINERSIZE1 ? Constants.CONTAINERSIZE1N :
// o.ContainerSizeI == Constants.CONTAINERSIZE2 ? Constants.CONTAINERSIZE2N :
// Constants.CONTAINERSIZE3N,
ExpenseC = m.ExpenseC,
ExpenseN = m.ExpenseN,
//Amount = e.Amount,
Amount = (e.Amount ?? 0) + (e.TaxAmount ?? 0),
TaxAmount = e.TaxAmount,
CustomerN = c != null ? c.CustomerN : "",
LiabilitiesStatusI = f == null ? "0" : f.LiabilitiesStatusI,
LiabilitiesD = f == null ? (DateTime?)null : f.LiabilitiesD,
LiabilitiesNo = f == null ? 0 : f.LiabilitiesNo
}
);
//var previousDate = _liabilitiesRepository.Query(i => i.DriverC == driverC && i.LiabilitiesI == "1" && i.LiabilitiesD < date).OrderBy("LiabilitiesD desc").FirstOrDefault();
//if (previousDate != null)
//{
// return result.AsQueryable().Where(i => i.TransportD >= previousDate.LiabilitiesD).ToList();
//}
//else
//{
return(result.ToList());
//}
}
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 List <RouteExpenseViewModel> GetExpensesHistory(string expenseC, string categoryI, string departureC, string destinationC, string conSizeI, string conTypeC)
{
if (categoryI == Convert.ToInt32(ExpenseCategory.Expense).ToString())
{
var historyExpenses = from e in _expenseDetailRepository.GetAllQueryable()
join d in _dispatchRepository.GetAllQueryable() on new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo } equals
new { d.OrderD, d.OrderNo, d.DetailNo, d.DispatchNo } into ed
from d in ed.DefaultIfEmpty()
join c in _containerRepository.GetAllQueryable() on new { d.OrderD, d.OrderNo, d.DetailNo } equals
new { c.OrderD, c.OrderNo, c.DetailNo } into edc
from c in edc.DefaultIfEmpty()
where e.ExpenseC == expenseC && c.ContainerSizeI == conSizeI && c.ContainerTypeC == conTypeC && d.DispatchStatus == Constants.CONFIRMED &&
((string.IsNullOrEmpty(d.Location1C) & !string.IsNullOrEmpty(d.Location2C) &
(d.Location2C == departureC ||
(!string.IsNullOrEmpty(d.Location3C) && d.Location3C == destinationC))) ||
(!string.IsNullOrEmpty(d.Location1C) & (d.Location1C == departureC ||
(!string.IsNullOrEmpty(d.Location3C) && d.Location3C == destinationC) ||
(string.IsNullOrEmpty(d.Location3C) && !string.IsNullOrEmpty(d.Location2C) && d.Location2C == destinationC))))
select new RouteExpenseViewModel()
{
UsedExpenseD = e.OrderD,
Quantity = e.Quantity,
Unit = e.Unit,
UnitPrice = e.UnitPrice,
Amount = e.Amount
};
if (historyExpenses.Any())
{
historyExpenses = historyExpenses.OrderBy("UsedExpenseD descending");
var limitedHistoryExpenses = historyExpenses.Skip(0).Take(10).ToList();
return(limitedHistoryExpenses);
}
}
else if (categoryI == Convert.ToInt32(ExpenseCategory.Allowance).ToString())
{
var historyAllowances = from e in _allowanceDetailRepository.GetAllQueryable()
join d in _dispatchRepository.GetAllQueryable() on new { e.OrderD, e.OrderNo, e.DetailNo, e.DispatchNo } equals
new { d.OrderD, d.OrderNo, d.DetailNo, d.DispatchNo } into ed
from d in ed.DefaultIfEmpty()
join c in _containerRepository.GetAllQueryable() on new { d.OrderD, d.OrderNo, d.DetailNo } equals
new { c.OrderD, c.OrderNo, c.DetailNo } into edc
from c in edc.DefaultIfEmpty()
where e.AllowanceC == expenseC && c.ContainerSizeI == conSizeI && c.ContainerTypeC == conTypeC && d.DispatchStatus == Constants.CONFIRMED &&
((string.IsNullOrEmpty(d.Location1C) & !string.IsNullOrEmpty(d.Location2C) &
(d.Location2C == departureC ||
(!string.IsNullOrEmpty(d.Location3C) && d.Location3C == destinationC))) ||
(!string.IsNullOrEmpty(d.Location1C) & (d.Location1C == departureC ||
(!string.IsNullOrEmpty(d.Location3C) && d.Location3C == destinationC) ||
(string.IsNullOrEmpty(d.Location3C) && !string.IsNullOrEmpty(d.Location2C) && d.Location2C == destinationC))))
select new RouteExpenseViewModel()
{
UsedExpenseD = e.OrderD,
Quantity = e.Quantity,
Unit = e.Unit,
UnitPrice = e.UnitPrice,
Amount = e.Amount
};
if (historyAllowances.Any())
{
historyAllowances = historyAllowances.OrderBy("UsedExpenseD descending");
var limitedhistoryAllowances = historyAllowances.Skip(0).Take(10).ToList();
return(limitedhistoryAllowances);
}
}
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()
});
}