public IEnumerable <ReceiptForOrderNode> GetOrdersForCashReceiptServiceToSend(
IUnitOfWork uow,
IOrderParametersProvider orderParametersProvider,
IOrganizationParametersProvider organizationParametersProvider,
DateTime?startDate = null)
{
#region Aliases Restrictions Projections
var paymentByCardFromNotToSendSalesReceipts = orderParametersProvider.PaymentsByCardFromNotToSendSalesReceipts;
var vodovozSouthOrganizationId = organizationParametersProvider.VodovozSouthOrganizationId;
ReceiptForOrderNode resultAlias = null;
ExtendedReceiptForOrderNode extendedReceiptForOrderNodeAlias = null;
OrderItem orderItemAlias = null;
VodovozOrder orderAlias = null;
CashReceipt cashReceiptAlias = null;
Nomenclature nomenclatureAlias = null;
ProductGroup productGroupAlias = null;
Counterparty counterpartyAlias = null;
CounterpartyContract counterpartyContractAlias = null;
Organization organizationAlias = null;
var orderSumProjection = Projections.Sum(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "CAST(IFNULL(?1 * ?2 - ?3, 0) AS DECIMAL(14,2))"),
NHibernateUtil.Decimal,
Projections.Property(() => orderItemAlias.Count),
Projections.Property(() => orderItemAlias.Price),
Projections.Property(() => orderItemAlias.DiscountMoney)
)
);
var orderSumRestriction = Restrictions.Gt(orderSumProjection, 0);
var alwaysSendOrdersRestriction = Restrictions.Disjunction()
.Add(() => productGroupAlias.IsOnlineStore)
.Add(() => counterpartyAlias.AlwaysSendReceitps)
.Add(() => orderAlias.SelfDelivery)
.Add(Restrictions.In(Projections.Property(() => orderAlias.PaymentType),
new[] { PaymentType.ByCard, PaymentType.Terminal }.ToArray()));
var orderDeliveredStatuses = Restrictions.In(Projections.Property(() => orderAlias.OrderStatus),
new[] { OrderStatus.Shipped, OrderStatus.UnloadingOnStock }.ToArray());
var orderPaymentTypesRestriction = Restrictions.In(Projections.Property(() => orderAlias.PaymentType),
new[] { PaymentType.cash, PaymentType.Terminal, PaymentType.ByCard }.ToArray());
var paidByCardRestriction = Restrictions.Disjunction()
.Add(() => orderAlias.PaymentType != PaymentType.ByCard)
.Add(() => organizationAlias.Id != vodovozSouthOrganizationId)
.Add(Restrictions.On(() => orderAlias.PaymentByCardFrom.Id)
.Not.IsIn(paymentByCardFromNotToSendSalesReceipts));
#endregion
#region AlwaysSendOrders
var alwaysSendOrdersQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias)
.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Left.JoinAlias(() => nomenclatureAlias.ProductGroup, () => productGroupAlias)
.Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias)
.Left.JoinAlias(() => counterpartyContractAlias.Organization, () => organizationAlias)
.Where(alwaysSendOrdersRestriction)
.And(paidByCardRestriction)
.And(orderDeliveredStatuses)
.And(orderSumRestriction)
.And(orderPaymentTypesRestriction)
.And(Restrictions.Disjunction()
.Add(Restrictions.IsNull(Projections.Property(() => cashReceiptAlias.Id)))
.Add(() => !cashReceiptAlias.Sent));
if (startDate.HasValue)
{
alwaysSendOrdersQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value);
}
var alwaysSendOrders = alwaysSendOrdersQuery
.SelectList(list => list
.SelectGroup(() => orderAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderId)
.Select(() => orderAlias.PaymentType).WithAlias(() => extendedReceiptForOrderNodeAlias.PaymentType)
.Select(orderSumProjection).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderSum)
.Select(() => cashReceiptAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.ReceiptId)
.Select(() => cashReceiptAlias.Sent).WithAlias(() => extendedReceiptForOrderNodeAlias.WasSent))
.TransformUsing(Transformers.AliasToBean <ExtendedReceiptForOrderNode>())
.Future <ExtendedReceiptForOrderNode>();
#endregion
#region UniqueOrderSumSendOrders
var uniqueOrderSumSendOrdersQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias)
.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias)
.Left.JoinAlias(() => nomenclatureAlias.ProductGroup, () => productGroupAlias)
.Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias)
.Left.JoinAlias(() => counterpartyContractAlias.Organization, () => organizationAlias)
.Where(Restrictions.Not(alwaysSendOrdersRestriction))
.And(paidByCardRestriction)
.And(orderDeliveredStatuses)
.And(orderSumRestriction)
.And(orderPaymentTypesRestriction);
if (startDate.HasValue)
{
uniqueOrderSumSendOrdersQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value);
}
var notUniqueOrderSumSendOrdersTemp = uniqueOrderSumSendOrdersQuery
.SelectList(list => list
.SelectGroup(() => orderAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderId)
.Select(() => orderAlias.PaymentType).WithAlias(() => extendedReceiptForOrderNodeAlias.PaymentType)
.Select(orderSumProjection).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderSum)
.Select(CustomProjections.Date(() => orderAlias.DeliveryDate)).WithAlias(() => extendedReceiptForOrderNodeAlias.DeliveryDate)
.Select(() => cashReceiptAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.ReceiptId)
.Select(() => cashReceiptAlias.Sent).WithAlias(() => extendedReceiptForOrderNodeAlias.WasSent))
.TransformUsing(Transformers.AliasToBean <ExtendedReceiptForOrderNode>())
.Future <ExtendedReceiptForOrderNode>();
var notUniqueOrderSumSendOrders = notUniqueOrderSumSendOrdersTemp.Where(x =>
x.PaymentType != PaymentType.cash ||
x.PaymentType == PaymentType.cash && x.OrderSum < 20000).ToList();
var alreadySentOrders = new List <ExtendedReceiptForOrderNode>(notUniqueOrderSumSendOrders.Where(x => x.WasSent.HasValue && x.WasSent.Value));
var uniqueOrderSumSendNodes = new List <ExtendedReceiptForOrderNode>();
foreach (var node in notUniqueOrderSumSendOrders.Where(x => !x.WasSent.HasValue || !x.WasSent.Value))
{
if (alreadySentOrders.All(x => x.OrderSum != node.OrderSum || x.DeliveryDate != node.DeliveryDate) &&
uniqueOrderSumSendNodes.All(x => x.OrderSum != node.OrderSum || x.DeliveryDate != node.DeliveryDate))
{
uniqueOrderSumSendNodes.Add(node);
}
}
var uniqueOrderSumSendOrderNodes = uniqueOrderSumSendNodes.Select(x => new ReceiptForOrderNode
{
OrderId = x.OrderId, ReceiptId = x.ReceiptId, WasSent = x.WasSent
});
#endregion
//Здесь и выше фильтрация идёт не на уровне запроса, т.к. не NHibernate упорно не хочет клась сложное условие в HAVING
var alwaysSendOrderNodes = alwaysSendOrders
.Where(x =>
x.PaymentType != PaymentType.cash ||
x.PaymentType == PaymentType.cash && x.OrderSum < 20000)
.Select(x => new ReceiptForOrderNode {
OrderId = x.OrderId,
ReceiptId = x.ReceiptId,
WasSent = x.WasSent
});
return(alwaysSendOrderNodes.Union(uniqueOrderSumSendOrderNodes));
}
public IEnumerable <ReceiptForOrderNode> GetOrdersForCashReceiptServiceToSend(
IUnitOfWork uow,
IOrderParametersProvider orderParametersProvider,
DateTime?startDate = null)
{
ReceiptForOrderNode resultAlias = null;
OrderItem orderItemAlias = null;
VodovozOrder orderAlias = null;
CashReceipt cashReceiptAlias = null;
Counterparty counterpartyAlias = null;
var orderSumProjection = Projections.Sum(
Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.Decimal, "CAST(IFNULL(?1 * ?2 - ?3, 0) AS DECIMAL(14,2))"),
NHibernateUtil.Decimal,
Projections.Property(() => orderItemAlias.Count),
Projections.Property(() => orderItemAlias.Price),
Projections.Property(() => orderItemAlias.DiscountMoney)
)
);
var paymentTypeRestriction = Restrictions.Disjunction()
.Add(() => orderAlias.PaymentType == PaymentType.Terminal)
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.PaymentType == PaymentType.cash)
.Add(() => counterpartyAlias.AlwaysSendReceipts))
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.PaymentType == PaymentType.ByCard)
.Add(Restrictions.Disjunction()
.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.PaymentFromTerminalId)
.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.GetPaymentByCardFromFastPaymentServiceId)
.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.GetPaymentByCardFromAvangardId)
//Не выбираем данный источник отправки для чеков, пока нет оплаты на сайте по QR
/*.Add(() => orderAlias.PaymentByCardFrom.Id == orderParametersProvider.GetPaymentByCardFromSiteByQrCode)*/));
var statusRestriction = Restrictions.Disjunction()
.Add(Restrictions.In(Projections.Property(() => orderAlias.OrderStatus),
new[] { OrderStatus.Shipped, OrderStatus.UnloadingOnStock }))
.Add(Restrictions.Conjunction()
.Add(() => orderAlias.SelfDelivery)
.Add(() => orderAlias.IsSelfDeliveryPaid));
var positiveSumRestriction = Restrictions.Gt(orderSumProjection, 0);
var notSentRestriction = Restrictions.Disjunction()
.Add(Restrictions.IsNull(Projections.Property(() => cashReceiptAlias.Id)))
.Add(() => !cashReceiptAlias.Sent);
var ordersToSendQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias)
.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Inner.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Where(paymentTypeRestriction)
.And(statusRestriction)
.And(positiveSumRestriction)
.And(notSentRestriction);
if (startDate.HasValue)
{
ordersToSendQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value);
}
var ordersToSend = ordersToSendQuery
.SelectList(list => list
.SelectGroup(() => orderAlias.Id).WithAlias(() => resultAlias.OrderId)
.Select(() => cashReceiptAlias.Id).WithAlias(() => resultAlias.ReceiptId)
.Select(() => cashReceiptAlias.Sent).WithAlias(() => resultAlias.WasSent))
.TransformUsing(Transformers.AliasToBean <ReceiptForOrderNode>())
.List <ReceiptForOrderNode>();
return(ordersToSend);
}
