public decimal GetCounterpartyDebt(IUnitOfWork uow, int counterpartyId) { VodovozOrder orderAlias = null; OrderItem orderItemAlias = null; Counterparty counterpartyAlias = null; PaymentItem paymentItemAlias = null; CashlessMovementOperation cashlessMovOperationAlias = null; var total = uow.Session.QueryOver(() => orderAlias) .Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias) .Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias) .Where(() => counterpartyAlias.Id == counterpartyId) .And(() => orderAlias.PaymentType == PaymentType.cashless) .AndRestrictionOn(() => orderAlias.OrderStatus).Not.IsIn(OrderRepository.GetUndeliveryAndNewStatuses()) .And(() => orderAlias.OrderPaymentStatus != OrderPaymentStatus.Paid) .Select(OrderRepository.GetOrderSumProjection(orderItemAlias)) .SingleOrDefault <decimal>(); var totalPayPartiallyPaidOrders = uow.Session.QueryOver(() => paymentItemAlias) .Left.JoinAlias(() => paymentItemAlias.CashlessMovementOperation, () => cashlessMovOperationAlias) .Left.JoinAlias(() => paymentItemAlias.Order, () => orderAlias) .Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias) .Where(() => counterpartyAlias.Id == counterpartyId) .And(() => orderAlias.PaymentType == PaymentType.cashless) .AndRestrictionOn(() => orderAlias.OrderStatus).Not.IsIn(OrderRepository.GetUndeliveryAndNewStatuses()) .And(() => orderAlias.OrderPaymentStatus == OrderPaymentStatus.PartiallyPaid) .And(() => paymentItemAlias.PaymentItemStatus != AllocationStatus.Cancelled) .Select(Projections.Sum(() => cashlessMovOperationAlias.Expense)) .SingleOrDefault <decimal>(); return(total - totalPayPartiallyPaidOrders); }
public IList <PaymentItem> GetAllocatedPaymentItemsForOrder(IUnitOfWork uow, int orderId) { CashlessMovementOperation cashlessMovementOperationAlias = null; var paymentItems = uow.Session.QueryOver <PaymentItem>() .Inner.JoinAlias(pi => pi.CashlessMovementOperation, () => cashlessMovementOperationAlias) .Where(pi => pi.Order.Id == orderId) .And(pi => pi.PaymentItemStatus != AllocationStatus.Cancelled) .List(); return(paymentItems); }
public decimal GetCounterpartyDebt(IUnitOfWork uow, int counterpartyId) { VodovozOrder orderAlias = null; OrderItem orderItemAlias = null; Counterparty counterpartyAlias = null; PaymentItem paymentItemAlias = null; CashlessMovementOperation cashlessMovOperationAlias = null; var total = uow.Session.QueryOver(() => orderAlias) .Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias) .Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias) .Where(() => counterpartyAlias.Id == counterpartyId) .And(() => orderAlias.OrderStatus != OrderStatus.NewOrder) .And(() => orderAlias.OrderStatus != OrderStatus.Canceled) .And(() => orderAlias.OrderStatus != OrderStatus.DeliveryCanceled) .And(() => orderAlias.OrderStatus != OrderStatus.NotDelivered) .And(() => orderAlias.PaymentType == PaymentType.cashless) .And(() => orderAlias.OrderPaymentStatus != OrderPaymentStatus.Paid) .Select( Projections.Sum( Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "ROUND(?1 * IFNULL(?2, ?3) - ?4, 2)"), NHibernateUtil.Decimal, new IProjection[] { Projections.Property(() => orderItemAlias.Price), Projections.Property(() => orderItemAlias.ActualCount), Projections.Property(() => orderItemAlias.Count), Projections.Property(() => orderItemAlias.DiscountMoney) }) ) ).SingleOrDefault <decimal>(); var totalPayPartiallyPaidOrders = uow.Session.QueryOver(() => paymentItemAlias) .Left.JoinAlias(() => paymentItemAlias.CashlessMovementOperation, () => cashlessMovOperationAlias) .Left.JoinAlias(() => paymentItemAlias.Order, () => orderAlias) .Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias) .Where(() => counterpartyAlias.Id == counterpartyId) .And(() => orderAlias.OrderStatus != OrderStatus.NewOrder) .And(() => orderAlias.OrderStatus != OrderStatus.Canceled) .And(() => orderAlias.OrderStatus != OrderStatus.DeliveryCanceled) .And(() => orderAlias.OrderStatus != OrderStatus.NotDelivered) .And(() => orderAlias.PaymentType == PaymentType.cashless) .And(() => orderAlias.OrderPaymentStatus == OrderPaymentStatus.PartiallyPaid) .Select( Projections.Sum(() => cashlessMovOperationAlias.Expense) ).SingleOrDefault <decimal>(); return(total - totalPayPartiallyPaidOrders); }
public IList <NotFullyPaidOrderNode> GetAllNotFullyPaidOrdersByClientAndOrg( IUnitOfWork uow, int counterpartyId, int organizationId, int closingDocumentDeliveryScheduleId) { VodovozOrder orderAlias = null; OrderItem orderItemAlias = null; PaymentItem paymentItemAlias = null; CounterpartyContract counterpartyContractAlias = null; Organization orderOrganizationAlias = null; DeliverySchedule deliveryScheduleAlias = null; CashlessMovementOperation cashlessMovementOperationAlias = null; NotFullyPaidOrderNode resultAlias = null; var orderSumProjection = GetOrderSumProjection(orderItemAlias); var allocatedSumProjection = QueryOver.Of(() => paymentItemAlias) .JoinAlias(pi => pi.CashlessMovementOperation, () => cashlessMovementOperationAlias) .Where(pi => pi.Order.Id == orderAlias.Id) .Where(pi => pi.PaymentItemStatus != AllocationStatus.Cancelled) .Select(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "IFNULL(?1, ?2)"), NHibernateUtil.Decimal, Projections.Sum(() => cashlessMovementOperationAlias.Expense), Projections.Constant(0))); return(uow.Session.QueryOver(() => orderAlias) .Inner.JoinAlias(o => o.OrderItems, () => orderItemAlias) .Inner.JoinAlias(o => o.Contract, () => counterpartyContractAlias) .Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias) .Inner.JoinAlias(o => o.DeliverySchedule, () => deliveryScheduleAlias) .Where(() => orderAlias.Client.Id == counterpartyId) .And(() => orderOrganizationAlias.Id == organizationId) .And(() => orderAlias.OrderStatus == OrderStatus.Shipped || orderAlias.OrderStatus == OrderStatus.UnloadingOnStock || orderAlias.OrderStatus == OrderStatus.Closed) .And(() => orderAlias.PaymentType == PaymentType.cashless) .And(() => orderAlias.OrderPaymentStatus != OrderPaymentStatus.Paid) .And(() => deliveryScheduleAlias.Id != closingDocumentDeliveryScheduleId) .SelectList(list => list.SelectGroup(o => o.Id).WithAlias(() => resultAlias.Id) .Select(o => o.DeliveryDate).WithAlias(() => resultAlias.OrderDeliveryDate) .Select(o => o.CreateDate).WithAlias(() => resultAlias.OrderCreationDate) .Select(orderSumProjection).WithAlias(() => resultAlias.OrderSum) .SelectSubQuery(allocatedSumProjection).WithAlias(() => resultAlias.AllocatedSum)) .Where(Restrictions.Gt(orderSumProjection, 0)) .TransformUsing(Transformers.AliasToBean <NotFullyPaidOrderNode>()) .OrderBy(o => o.DeliveryDate).Asc .OrderBy(o => o.CreateDate).Asc .List <NotFullyPaidOrderNode>()); }
public static decimal GetCounterpartyLastBalance(IUnitOfWork uow, int counterpartyId) { CashlessMovementOperation cashlessOperationAlias = null; Payment paymentAlias = null; PaymentItem paymentItemAlias = null; var income = uow.Session.QueryOver(() => paymentAlias) .Left.JoinAlias(() => paymentAlias.CashlessMovementOperation, () => cashlessOperationAlias) .Where(() => paymentAlias.Counterparty.Id == counterpartyId) .Select(Projections.Sum(() => cashlessOperationAlias.Income)) .SingleOrDefault <decimal>(); var expense = uow.Session.QueryOver(() => paymentItemAlias) .Left.JoinAlias(() => paymentItemAlias.Payment, () => paymentAlias) .Where(() => paymentAlias.Counterparty.Id == counterpartyId) .Select(Projections.Sum(() => paymentItemAlias.Sum)) .SingleOrDefault <decimal>(); return(income - expense); }
public decimal GetCounterpartyLastBalance(IUnitOfWork uow, int counterpartyId, int organizationId) { CashlessMovementOperation cashlessIncomeOperationAlias = null; CashlessMovementOperation cashlessExpenseOperationAlias = null; var income = uow.Session.QueryOver(() => cashlessIncomeOperationAlias) .Where(() => cashlessIncomeOperationAlias.Counterparty.Id == counterpartyId) .And(() => cashlessIncomeOperationAlias.Organization.Id == organizationId) .Where(() => cashlessIncomeOperationAlias.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .Select(Projections.Sum(() => cashlessIncomeOperationAlias.Income)) .SingleOrDefault <decimal>(); var expense = uow.Session.QueryOver(() => cashlessExpenseOperationAlias) .Where(() => cashlessExpenseOperationAlias.Counterparty.Id == counterpartyId) .And(() => cashlessExpenseOperationAlias.Organization.Id == organizationId) .Where(() => cashlessExpenseOperationAlias.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .Select(Projections.Sum(() => cashlessExpenseOperationAlias.Expense)) .SingleOrDefault <decimal>(); return(income - expense); }
protected override IQueryOver <Payment> ItemsQuery(IUnitOfWork uow) { PaymentJournalNode resultAlias = null; Payment paymentAlias = null; BaseOrg organizationAlias = null; PaymentItem paymentItemAlias = null; PaymentItem paymentItemAlias2 = null; ProfitCategory profitCategoryAlias = null; Counterparty counterpartyAlias = null; CashlessMovementOperation incomeOperationAlias = null; var paymentQuery = uow.Session.QueryOver(() => paymentAlias) .Left.JoinAlias(p => p.CashlessMovementOperation, () => incomeOperationAlias, cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .Left.JoinAlias(p => p.Organization, () => organizationAlias) .Left.JoinAlias(p => p.ProfitCategory, () => profitCategoryAlias) .Left.JoinAlias(p => p.PaymentItems, () => paymentItemAlias) .Left.JoinAlias(p => p.Counterparty, () => counterpartyAlias); var numOrdersProjection = Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( ?1 SEPARATOR ?2)"), NHibernateUtil.String, Projections.Property(() => paymentItemAlias.Order.Id), Projections.Constant("\n")); var unAllocatedSumProjection = Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.Decimal, "CASE " + $"WHEN ?1 = '{PaymentState.Cancelled}' OR ?1 = '{PaymentState.undistributed}' AND ?2 IS NOT NULL THEN ?6 " + $"WHEN ?1 = '{PaymentState.undistributed}' AND ?2 IS NULL THEN ?3 " + "ELSE IFNULL(?4, ?6) - IFNULL(?5, ?6) END"), NHibernateUtil.Decimal, Projections.Property(() => paymentAlias.Status), Projections.Property(() => paymentAlias.RefundPaymentFromOrderId), Projections.Property(() => paymentAlias.Total), Projections.Property(() => incomeOperationAlias.Income), Projections.Sum(() => paymentItemAlias2.Sum), Projections.Constant(0)); var unAllocatedSum = QueryOver.Of(() => paymentItemAlias2) .Where(pi => pi.Payment.Id == paymentAlias.Id) .And(pi => pi.PaymentItemStatus != AllocationStatus.Cancelled) .Select(unAllocatedSumProjection); var counterpartyNameProjection = Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "IFNULL(?1, '')"), NHibernateUtil.String, Projections.Property(() => counterpartyAlias.Name)); #region filter if (_filterViewModel != null) { if (_filterViewModel.Counterparty != null) { paymentQuery.Where(p => p.Counterparty.Id == _filterViewModel.Counterparty.Id); } if (_filterViewModel.StartDate.HasValue) { paymentQuery.Where(p => p.Date >= _filterViewModel.StartDate); } if (_filterViewModel.EndDate.HasValue) { paymentQuery.Where(p => p.Date <= _filterViewModel.EndDate.Value.AddDays(1).AddMilliseconds(-1)); } if (_filterViewModel.HideCompleted) { paymentQuery.Where(p => p.Status != PaymentState.completed); } if (_filterViewModel.HidePaymentsWithoutCounterparty) { paymentQuery.Where(p => p.Counterparty != null); } if (_filterViewModel.HideAllocatedPayments) { paymentQuery.Where(Restrictions.Gt(Projections.SubQuery(unAllocatedSum), 0)); } if (_filterViewModel.PaymentState.HasValue) { paymentQuery.Where(p => p.Status == _filterViewModel.PaymentState); } if (_filterViewModel.IsManuallyCreated) { paymentQuery.Where(p => p.IsManuallyCreated); } if (_filterViewModel.IsSortingDescByUnAllocatedSum) { paymentQuery = paymentQuery.OrderBy(Projections.SubQuery(unAllocatedSum)).Desc; } } #endregion filter paymentQuery.Where(GetSearchCriterion( () => paymentAlias.PaymentNum, () => paymentAlias.Total, () => paymentAlias.CounterpartyName, () => paymentItemAlias.Order.Id )); var resultQuery = paymentQuery .SelectList(list => list .SelectGroup(() => paymentAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => paymentAlias.PaymentNum).WithAlias(() => resultAlias.PaymentNum) .Select(() => paymentAlias.Date).WithAlias(() => resultAlias.Date) .Select(() => paymentAlias.Total).WithAlias(() => resultAlias.Total) .Select(numOrdersProjection).WithAlias(() => resultAlias.Orders) .Select(() => paymentAlias.CounterpartyName).WithAlias(() => resultAlias.PayerName) .Select(counterpartyNameProjection).WithAlias(() => resultAlias.CounterpartyName) .Select(() => organizationAlias.FullName).WithAlias(() => resultAlias.Organization) .Select(() => paymentAlias.PaymentPurpose).WithAlias(() => resultAlias.PaymentPurpose) .Select(() => profitCategoryAlias.Name).WithAlias(() => resultAlias.ProfitCategory) .Select(() => paymentAlias.Status).WithAlias(() => resultAlias.Status) .Select(() => paymentAlias.IsManuallyCreated).WithAlias(() => resultAlias.IsManualCreated) .SelectSubQuery(unAllocatedSum).WithAlias(() => resultAlias.UnAllocatedSum)) .OrderBy(() => paymentAlias.Status).Asc .OrderBy(() => paymentAlias.CounterpartyName).Asc .OrderBy(() => paymentAlias.Total).Asc .TransformUsing(Transformers.AliasToBean <PaymentJournalNode>()); return(resultQuery); }
protected override IQueryOver <Payment> ItemsQuery(IUnitOfWork uow) { UnallocatedBalancesJournalNode resultAlias = null; VodOrder orderAlias = null; VodOrder orderAlias2 = null; OrderItem orderItemAlias = null; PaymentItem paymentItemAlias = null; Counterparty counterpartyAlias = null; Organization organizationAlias = null; CounterpartyContract counterpartyContractAlias = null; Organization orderOrganizationAlias = null; CashlessMovementOperation cashlessMovementOperationAlias = null; var query = uow.Session.QueryOver <Payment>() .Inner.JoinAlias(cmo => cmo.Counterparty, () => counterpartyAlias) .Inner.JoinAlias(cmo => cmo.Organization, () => organizationAlias); var income = QueryOver.Of <CashlessMovementOperation>() .Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id) .And(cmo => cmo.Organization.Id == organizationAlias.Id) .And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Income)); var expense = QueryOver.Of <CashlessMovementOperation>() .Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id) .And(cmo => cmo.Organization.Id == organizationAlias.Id) .And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Expense)); var balanceProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - ?2"), NHibernateUtil.Decimal, Projections.SubQuery(income), Projections.SubQuery(expense)); var orderSumProjection = OrderRepository.GetOrderSumProjection(orderItemAlias); var totalNotPaidOrders = QueryOver.Of(() => orderAlias) .Inner.JoinAlias(o => o.OrderItems, () => orderItemAlias) .Inner.JoinAlias(o => o.Contract, () => counterpartyContractAlias) .Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias) .Where(() => orderAlias.Client.Id == counterpartyAlias.Id) .And(() => orderOrganizationAlias.Id == organizationAlias.Id) .And(() => orderAlias.PaymentType == PaymentType.cashless) .And(() => orderAlias.OrderPaymentStatus != OrderPaymentStatus.Paid) .Select(orderSumProjection) .Where(Restrictions.Gt(orderSumProjection, 0)); var totalPayPartiallyPaidOrders = QueryOver.Of(() => paymentItemAlias) .JoinEntityAlias(() => orderAlias2, () => paymentItemAlias.Order.Id == orderAlias2.Id, JoinType.InnerJoin) .Inner.JoinAlias(() => orderAlias2.Contract, () => counterpartyContractAlias) .Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias) .Inner.JoinAlias(() => paymentItemAlias.CashlessMovementOperation, () => cashlessMovementOperationAlias) .Where(() => orderAlias2.Client.Id == counterpartyAlias.Id) .And(() => orderOrganizationAlias.Id == organizationAlias.Id) .And(() => cashlessMovementOperationAlias.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .And(() => orderAlias2.PaymentType == PaymentType.cashless) .And(() => orderAlias2.OrderPaymentStatus == OrderPaymentStatus.PartiallyPaid) .Select(Projections.Sum(() => cashlessMovementOperationAlias.Expense)); var counterpartyDebtProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - IFNULL(?2, ?3)"), NHibernateUtil.Decimal, Projections.SubQuery(totalNotPaidOrders), Projections.SubQuery(totalPayPartiallyPaidOrders), Projections.Constant(0)); query.Where(GetSearchCriterion( () => counterpartyAlias.Id, () => counterpartyAlias.Name, () => balanceProjection, () => counterpartyDebtProjection)); #region filter if (_filterViewModel.Counterparty != null) { query.Where(() => counterpartyAlias.Id == _filterViewModel.Counterparty.Id); } if (_filterViewModel.Organization != null) { query.Where(() => organizationAlias.Id == _filterViewModel.Organization.Id); } #endregion return(query.SelectList(list => list .SelectGroup(() => counterpartyAlias.Id).WithAlias(() => resultAlias.CounterpartyId) .SelectGroup(() => organizationAlias.Id).WithAlias(() => resultAlias.OrganizationId) .Select(p => counterpartyAlias.INN).WithAlias(() => resultAlias.CounterpartyINN) .Select(p => counterpartyAlias.Name).WithAlias(() => resultAlias.CounterpartyName) .Select(p => organizationAlias.Name).WithAlias(() => resultAlias.OrganizationName) .Select(balanceProjection).WithAlias(() => resultAlias.CounterpartyBalance) .Select(counterpartyDebtProjection).WithAlias(() => resultAlias.CounterpartyDebt)) .Where(Restrictions.Gt(balanceProjection, 0)) .And(Restrictions.Gt(counterpartyDebtProjection, 0)) .OrderBy(balanceProjection).Desc .TransformUsing(Transformers.AliasToBean <UnallocatedBalancesJournalNode>()) .SetTimeout(120)); }
public IQueryOver <Payment, Payment> GetAllUnallocatedBalances(IUnitOfWork uow, int closingDocumentDeliveryScheduleId) { UnallocatedBalancesJournalNode resultAlias = null; Order orderAlias = null; Order orderAlias2 = null; OrderItem orderItemAlias = null; PaymentItem paymentItemAlias = null; Counterparty counterpartyAlias = null; Organization organizationAlias = null; CounterpartyContract counterpartyContractAlias = null; Organization orderOrganizationAlias = null; DeliverySchedule deliveryScheduleAlias = null; DeliverySchedule deliveryScheduleAlias2 = null; CashlessMovementOperation cashlessMovementOperationAlias = null; var query = uow.Session.QueryOver <Payment>() .Inner.JoinAlias(cmo => cmo.Counterparty, () => counterpartyAlias) .Inner.JoinAlias(cmo => cmo.Organization, () => organizationAlias); var income = QueryOver.Of <CashlessMovementOperation>() .Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id) .And(cmo => cmo.Organization.Id == organizationAlias.Id) .And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Income)); var expense = QueryOver.Of <CashlessMovementOperation>() .Where(cmo => cmo.Counterparty.Id == counterpartyAlias.Id) .And(cmo => cmo.Organization.Id == organizationAlias.Id) .And(cmo => cmo.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .Select(Projections.Sum <CashlessMovementOperation>(cmo => cmo.Expense)); var balanceProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - ?2"), NHibernateUtil.Decimal, Projections.SubQuery(income), Projections.SubQuery(expense)); var orderSumProjection = OrderRepository.GetOrderSumProjection(orderItemAlias); var totalNotPaidOrders = QueryOver.Of(() => orderAlias) .Inner.JoinAlias(o => o.OrderItems, () => orderItemAlias) .Inner.JoinAlias(o => o.Contract, () => counterpartyContractAlias) .Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias) .Inner.JoinAlias(o => o.DeliverySchedule, () => deliveryScheduleAlias) .Where(() => orderAlias.Client.Id == counterpartyAlias.Id) .And(() => orderOrganizationAlias.Id == organizationAlias.Id) .And(() => orderAlias.OrderStatus == OrderStatus.Shipped || orderAlias.OrderStatus == OrderStatus.UnloadingOnStock || orderAlias.OrderStatus == OrderStatus.Closed) .And(() => orderAlias.PaymentType == PaymentType.cashless) .And(() => orderAlias.OrderPaymentStatus != OrderPaymentStatus.Paid) .And(() => deliveryScheduleAlias.Id != closingDocumentDeliveryScheduleId) .Select(orderSumProjection) .Where(Restrictions.Gt(orderSumProjection, 0)); var totalPayPartiallyPaidOrders = QueryOver.Of(() => paymentItemAlias) .JoinEntityAlias(() => orderAlias2, () => paymentItemAlias.Order.Id == orderAlias2.Id, JoinType.InnerJoin) .Inner.JoinAlias(() => orderAlias2.Contract, () => counterpartyContractAlias) .Inner.JoinAlias(() => counterpartyContractAlias.Organization, () => orderOrganizationAlias) .Inner.JoinAlias(() => paymentItemAlias.CashlessMovementOperation, () => cashlessMovementOperationAlias) .Inner.JoinAlias(() => orderAlias2.DeliverySchedule, () => deliveryScheduleAlias2) .Where(() => orderAlias2.Client.Id == counterpartyAlias.Id) .And(() => orderOrganizationAlias.Id == organizationAlias.Id) .And(() => cashlessMovementOperationAlias.CashlessMovementOperationStatus != AllocationStatus.Cancelled) .And(() => orderAlias2.OrderStatus == OrderStatus.Shipped || orderAlias2.OrderStatus == OrderStatus.UnloadingOnStock || orderAlias2.OrderStatus == OrderStatus.Closed) .And(() => orderAlias2.PaymentType == PaymentType.cashless) .And(() => orderAlias2.OrderPaymentStatus == OrderPaymentStatus.PartiallyPaid) .And(() => deliveryScheduleAlias2.Id != closingDocumentDeliveryScheduleId) .Select(Projections.Sum(() => cashlessMovementOperationAlias.Expense)); var counterpartyDebtProjection = Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 - IFNULL(?2, ?3)"), NHibernateUtil.Decimal, Projections.SubQuery(totalNotPaidOrders), Projections.SubQuery(totalPayPartiallyPaidOrders), Projections.Constant(0)); return(query.SelectList(list => list .SelectGroup(() => counterpartyAlias.Id).WithAlias(() => resultAlias.CounterpartyId) .SelectGroup(() => organizationAlias.Id).WithAlias(() => resultAlias.OrganizationId) .Select(p => counterpartyAlias.INN).WithAlias(() => resultAlias.CounterpartyINN) .Select(p => counterpartyAlias.Name).WithAlias(() => resultAlias.CounterpartyName) .Select(p => organizationAlias.Name).WithAlias(() => resultAlias.OrganizationName) .Select(balanceProjection).WithAlias(() => resultAlias.CounterpartyBalance) .Select(counterpartyDebtProjection).WithAlias(() => resultAlias.CounterpartyDebt)) .Where(Restrictions.Gt(balanceProjection, 0)) .And(Restrictions.Gt(counterpartyDebtProjection, 0)) .OrderBy(balanceProjection).Desc .TransformUsing(Transformers.AliasToBean <UnallocatedBalancesJournalNode>()) .SetTimeout(180)); }