public IEnumerable <OperationNode> CurrentCashForGivenSubdivisions(IUnitOfWork uow, int[] subdivisionIds)
{
Subdivision subdivisionAlias = null;
Income incomeAlias = null;
Expense expenseAlias = null;
OperationNode resultAlias = null;
var expenseSub = QueryOver.Of(() => expenseAlias)
.Where(x => x.RelatedToSubdivision.Id == subdivisionAlias.Id)
.Select(Projections.Sum <Expense>(o => o.Money));
var incomeSub = QueryOver.Of(() => incomeAlias)
.Where(x => x.RelatedToSubdivision.Id == subdivisionAlias.Id)
.Select(Projections.Sum <Income>(o => o.Money));
var projection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "( IFNULL(?1, 0) - IFNULL(?2, 0) )"),
NHibernateUtil.Decimal,
Projections.SubQuery(incomeSub),
Projections.SubQuery(expenseSub)
);
var results = uow.Session
.QueryOver(() => subdivisionAlias)
.Where(() => subdivisionAlias.Id.IsIn(subdivisionIds)).SelectList(list => list
.Select(() => subdivisionAlias.Name).WithAlias(() => resultAlias.Name)
.Select(projection).WithAlias(() => resultAlias.Balance)
)
.TransformUsing(Transformers.AliasToBean <OperationNode>())
.List <OperationNode>();
return(results);
}
public IList <OperationNode> GetCashBalanceForOrganizations(IUnitOfWork uow)
{
Organization organizationAlias = null;
OrganisationCashMovementOperation operationAlias = null;
OperationNode resultAlias = null;
var query = uow.Session.QueryOver(() => organizationAlias)
.JoinEntityAlias(() => operationAlias,
() => organizationAlias.Id == operationAlias.Organisation.Id,
JoinType.LeftOuterJoin
)
.SelectList(list => list
.SelectGroup(() => organizationAlias.Id)
.Select(Projections.Sum(() => operationAlias.Amount)).WithAlias(() => resultAlias.Balance)
.Select(() => organizationAlias.Name).WithAlias(() => resultAlias.Name))
.TransformUsing(Transformers.AliasToBean <OperationNode>())
.List <OperationNode>();
return(query);
}