public Dictionary <FuelType, decimal> GetAllFuelsBalanceForSubdivision(IUnitOfWork uow, Subdivision subdivision)
{
if (uow == null)
{
throw new ArgumentNullException(nameof(uow));
}
if (subdivision == null)
{
throw new ArgumentNullException(nameof(subdivision));
}
FuelType fuelTypeAlias = null;
FuelExpenseOperation fuelExpenseOperationAlias = null;
FuelIncomeOperation fuelIncomeOperationAlias = null;
Subdivision expenseSubdivisionAlias = null;
Subdivision incomeSubdivisionAlias = null;
var fuelExpenseSubquery = QueryOver.Of <FuelExpenseOperation>(() => fuelExpenseOperationAlias)
.Left.JoinAlias(() => fuelExpenseOperationAlias.RelatedToSubdivision, () => expenseSubdivisionAlias)
.Where(() => fuelExpenseOperationAlias.FuelType.Id == fuelTypeAlias.Id)
.Where(() => expenseSubdivisionAlias.Id == subdivision.Id)
.Select(Projections.Sum(Projections.Property(() => fuelExpenseOperationAlias.FuelLiters)))
.DetachedCriteria;
var fuelIncomeSubquery = QueryOver.Of <FuelIncomeOperation>(() => fuelIncomeOperationAlias)
.Left.JoinAlias(() => fuelIncomeOperationAlias.RelatedToSubdivision, () => incomeSubdivisionAlias)
.Where(Restrictions.Where(() => fuelIncomeOperationAlias.FuelType.Id == fuelTypeAlias.Id))
.Where(() => incomeSubdivisionAlias.Id == subdivision.Id)
.Select(Projections.Sum(Projections.Property(() => fuelIncomeOperationAlias.FuelLiters)))
.DetachedCriteria;
var resultList = uow.Session.QueryOver <FuelType>(() => fuelTypeAlias)
.SelectList(list => list
.SelectGroup(() => fuelTypeAlias.Id)
.Select(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "( IFNULL(?1, 0) - IFNULL(?2, 0) )"),
NHibernateUtil.Decimal,
Projections.SubQuery(fuelIncomeSubquery),
Projections.SubQuery(fuelExpenseSubquery)
)
)
).TransformUsing(Transformers.PassThrough)
.List <object[]>()
.ToDictionary(key => (int)key[0], value => (decimal)(value[1] ?? 0m));
var fuelTypes = uow.GetAll <FuelType>();
Dictionary <FuelType, decimal> result = new Dictionary <FuelType, decimal>();
foreach (var fuelType in fuelTypes)
{
result.Add(fuelType, resultList[fuelType.Id]);
}
return(result);
}
public decimal GetFuelBalanceForSubdivision(IUnitOfWork uow, Subdivision subdivision, FuelType fuelType)
{
if (uow == null)
{
throw new ArgumentNullException(nameof(uow));
}
if (subdivision == null)
{
throw new ArgumentNullException(nameof(subdivision));
}
if (fuelType == null)
{
throw new ArgumentNullException(nameof(fuelType));
}
FuelExpenseOperation fuelExpenseOperationAlias = null;
FuelType fuelTypeAlias = null;
Subdivision cashSubdivision = null;
var fuelExpenseSubquery = QueryOver.Of <FuelExpenseOperation>(() => fuelExpenseOperationAlias)
.Left.JoinAlias(() => fuelExpenseOperationAlias.RelatedToSubdivision, () => cashSubdivision)
.Left.JoinAlias(() => fuelExpenseOperationAlias.FuelType, () => fuelTypeAlias)
.Where(() => cashSubdivision.Id == subdivision.Id)
.Where(() => fuelTypeAlias.Id == fuelType.Id)
.Select(Projections.Sum(Projections.Property <FuelExpenseOperation>(x => x.FuelLiters)))
.DetachedCriteria;
FuelIncomeOperation fuelIncomeOperationAlias = null;
var balance = uow.Session.QueryOver <FuelIncomeOperation>(() => fuelIncomeOperationAlias)
.Left.JoinAlias(() => fuelIncomeOperationAlias.RelatedToSubdivision, () => cashSubdivision)
.Left.JoinAlias(() => fuelIncomeOperationAlias.FuelType, () => fuelTypeAlias)
.Where(() => cashSubdivision.Id == subdivision.Id)
.Where(() => fuelTypeAlias.Id == fuelType.Id)
.Select(Projections.SqlFunction(
new SQLFunctionTemplate(
NHibernateUtil.Decimal, "( IFNULL(?1, 0) - IFNULL(?2, 0) )"),
NHibernateUtil.Decimal,
Projections.Sum(Projections.Property <FuelIncomeOperation>(x => x.FuelLiters)),
Projections.SubQuery(fuelExpenseSubquery)
)
).SingleOrDefault <decimal>();
return(balance);
}
