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