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