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); }