private IQueryOver <Payment> GetPaymentQuery(IUnitOfWork uow)
{
PaymentJournalNode resultAlias = null;
Payment paymentAlias = null;
BaseOrg organizationAlias = null;
PaymentItem paymentItemAlias = null;
CategoryProfit categoryProfitAlias = null;
var paymentQuery = uow.Session.QueryOver(() => paymentAlias)
.Left.JoinAlias(() => paymentAlias.Organization, () => organizationAlias)
.Left.JoinAlias(() => paymentAlias.ProfitCategory, () => categoryProfitAlias)
.Left.JoinAlias(() => paymentAlias.PaymentItems, () => paymentItemAlias);
#region filter
if (FilterViewModel != null)
{
if (FilterViewModel.StartDate.HasValue)
{
paymentQuery.Where(x => x.Date >= FilterViewModel.StartDate);
}
if (FilterViewModel.EndDate.HasValue)
{
paymentQuery.Where(x => x.Date <= FilterViewModel.EndDate);
}
if (FilterViewModel.HideCompleted)
{
paymentQuery.Where(x => x.Status != PaymentState.completed);
}
if (FilterViewModel.PaymentState.HasValue)
{
paymentQuery.Where(x => x.Status == FilterViewModel.PaymentState);
}
}
#endregion filter
var numOrders = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT( ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
Projections.Property(() => paymentItemAlias.Order.Id),
Projections.Constant("\n"));
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(numOrders).WithAlias(() => resultAlias.Orders)
.Select(() => paymentAlias.CounterpartyName).WithAlias(() => resultAlias.Counterparty)
.Select(() => organizationAlias.FullName).WithAlias(() => resultAlias.Organization)
.Select(() => paymentAlias.PaymentPurpose).WithAlias(() => resultAlias.PaymentPurpose)
.Select(() => categoryProfitAlias.Name).WithAlias(() => resultAlias.ProfitCategory)
.Select(() => paymentAlias.Status).WithAlias(() => resultAlias.Status)
)
.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)
{
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);
}
