/// <summary> /// Оборудование заказа от клиента /// </summary> /// <returns>Список оборудования от клиенту</returns> /// <param name="uow">Uow.</param> /// <param name="order">Заказ</param> public IList <ClientEquipmentNode> GetEquipmentFromClientForOrder(IUnitOfWork uow, VodovozOrder order) { OrderEquipment orderEquipmentAlias = null; Nomenclature nomenclatureAlias = null; ClientEquipmentNode resultAlias = null; var equipFromClient = uow.Session.QueryOver(() => orderEquipmentAlias) .Where(() => orderEquipmentAlias.Order.Id == order.Id) .Where(() => orderEquipmentAlias.Direction == Direction.PickUp) .Left.JoinQueryOver(i => i.Nomenclature, () => nomenclatureAlias) .SelectList(list => list .Select(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.Name) .Select(() => nomenclatureAlias.ShortName).WithAlias(() => resultAlias.ShortName) .Select(() => orderEquipmentAlias.Count).WithAlias(() => resultAlias.Count) ) .TransformUsing(Transformers.AliasToBean <ClientEquipmentNode>()) .List <ClientEquipmentNode>(); return(equipFromClient); }
public IEnumerable <ReceiptForOrderNode> GetOrdersForCashReceiptServiceToSend( IUnitOfWork uow, IOrderParametersProvider orderParametersProvider, IOrganizationParametersProvider organizationParametersProvider, ISalesReceiptsParametersProvider salesReceiptsParametersProvider, DateTime?startDate = null) { #region Aliases Restrictions Projections var paymentByCardFromNotToSendSalesReceipts = orderParametersProvider.PaymentsByCardFromNotToSendSalesReceipts; var vodovozSouthOrganizationId = organizationParametersProvider.VodovozSouthOrganizationId; 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.OnlineStore != null) .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(Restrictions.Disjunction() .Add(orderDeliveredStatuses) .Add(Restrictions.Conjunction() .Add(() => orderAlias.SelfDelivery) .Add(() => orderAlias.IsSelfDeliveryPaid))) .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>(); //Здесь фильтрация идёт не на уровне запроса, т.к. не NHibernate упорно не хочет клась сложное условие в HAVING var result = 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 }); #endregion #region UniqueOrderSumOrders if (salesReceiptsParametersProvider.SendUniqueOrderSumOrders) { 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 }); result = result.Union(uniqueOrderSumSendOrderNodes); } #endregion return(result); }
public IList <VodovozOrder> GetOrdersToExport1c8( IUnitOfWork uow, IOrderParametersProvider orderParametersProvider, Export1cMode mode, DateTime startDate, DateTime endDate, int?organizationId = null) { VodovozOrder orderAlias = null; OrderItem orderItemAlias = null; var export1CSubquerySum = QueryOver.Of(() => orderItemAlias) .Where(() => orderItemAlias.Order.Id == orderAlias.Id) .Select(Projections.Sum( Projections.SqlFunction(new VarArgsSQLFunction("", " * ", ""), NHibernateUtil.Decimal, Projections.Conditional( Restrictions.IsNotNull(Projections.Property <OrderItem>(x => x.ActualCount)), Projections.Property <OrderItem>(x => x.ActualCount), Projections.Property <OrderItem>(x => x.Count) ), Projections.Property <OrderItem>(x => x.Price), Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Decimal, "( 1 - ?1 / 100 )"), NHibernateUtil.Decimal, Projections.Property <OrderItem>(x => x.Discount) ) ) )) ; var query = uow.Session.QueryOver(() => orderAlias) .Where(() => orderAlias.OrderStatus.IsIn(VodovozOrder.StatusesToExport1c)) .Where(() => startDate <= orderAlias.DeliveryDate && orderAlias.DeliveryDate <= endDate); if (organizationId.HasValue) { CounterpartyContract counterpartyContractAlias = null; query.Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias) .Where(() => counterpartyContractAlias.Organization.Id == organizationId); } switch (mode) { case Export1cMode.BuhgalteriaOOO: query.Where(o => o.PaymentType == PaymentType.cashless) .And(Subqueries.Le(0.01, export1CSubquerySum.DetachedCriteria)); break; case Export1cMode.BuhgalteriaOOONew: CashReceipt cashReceiptAlias = null; query.JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin) .Where(Restrictions.Disjunction() .Add(() => orderAlias.PaymentType == PaymentType.cashless) .Add(Restrictions.Conjunction() .Add(Restrictions.On(() => orderAlias.PaymentType) .IsIn(new[] { PaymentType.Terminal, PaymentType.cash })) .Add(Restrictions.IsNotNull(Projections.Property(() => cashReceiptAlias.Id)))) .Add(Restrictions.Conjunction() .Add(() => orderAlias.PaymentType == PaymentType.ByCard) .Add(Restrictions.Disjunction() .Add(Restrictions.On(() => orderAlias.PaymentByCardFrom.Id) .IsIn(orderParametersProvider.PaymentsByCardFromNotToSendSalesReceipts)) .Add(Restrictions.IsNotNull(Projections.Property(() => cashReceiptAlias.Id)))) ) ); break; case Export1cMode.IPForTinkoff: query.Where(o => o.PaymentType == PaymentType.ByCard) .And(o => o.OnlineOrder != null) .And(Subqueries.Le(0.01, export1CSubquerySum.DetachedCriteria)); break; default: throw new ArgumentOutOfRangeException(nameof(mode), mode, null); } return(query.List()); }
/// <summary> /// Первый заказ контрагента, который можно считать выполненым. /// </summary> /// <returns>Первый заказ</returns> /// <param name="uow">UoW</param> /// <param name="counterparty">Контрагент</param> public VodovozOrder GetFirstRealOrderForClientForActionBottle(IUnitOfWork uow, VodovozOrder order, Counterparty counterparty) { if (uow == null) { throw new ArgumentNullException(nameof(uow)); } if (order == null) { throw new ArgumentNullException(nameof(order)); } if (counterparty == null) { throw new ArgumentNullException(nameof(counterparty)); } if (counterparty?.FirstOrder != null && GetValidStatusesToUseActionBottle().Contains(counterparty.FirstOrder.OrderStatus)) { return(counterparty.FirstOrder); } var query = uow.Session.QueryOver <VodovozOrder>() .Where(o => o.Id != order.Id) .Where(o => o.Client == counterparty) .Where(o => o.OrderStatus.IsIn(GetValidStatusesToUseActionBottle())) .OrderBy(o => o.DeliveryDate).Asc .Take(1) ; return(query.List().FirstOrDefault()); }
private IQueryOver <OrderWithoutShipmentForPayment> GetOrdersWithoutShipmentForPaymentQuery(IUnitOfWork uow) { RetailOrderJournalNode resultAlias = null; OrderWithoutShipmentForPayment orderWSPAlias = null; OrderWithoutShipmentForPaymentItem orderWSPItemAlias = null; VodovozOrder orderAlias = null; Nomenclature nomenclatureAlias = null; OrderItem orderItemAlias = null; Counterparty counterpartyAlias = null; Employee authorAlias = null; var query = uow.Session.QueryOver(() => orderWSPAlias) .Left.JoinAlias(o => o.Author, () => authorAlias); if (FilterViewModel.ViewTypes != ViewTypes.OrderWSFP && FilterViewModel.ViewTypes != ViewTypes.All || FilterViewModel.RestrictStatus != null && FilterViewModel.RestrictStatus != OrderStatus.Closed || FilterViewModel.RestrictPaymentType != null || FilterViewModel.DeliveryPoint != null || FilterViewModel.RestrictOnlyService != null || FilterViewModel.RestrictOnlySelfDelivery != null || FilterViewModel.RestrictLessThreeHours == true || FilterViewModel.OrderPaymentStatus != null || FilterViewModel.Organisation != null || FilterViewModel.PaymentByCardFrom != null) { query.Where(o => o.Id == -1); } if (FilterViewModel != null && FilterViewModel.IsForRetail != null) { query.JoinEntityAlias( () => counterpartyAlias, () => counterpartyAlias.Id == orderWSPAlias.Client.Id && counterpartyAlias.IsForRetail == FilterViewModel.IsForRetail, NHibernate.SqlCommand.JoinType.InnerJoin); } else { query.Left.JoinAlias(o => o.Client, () => counterpartyAlias); } if (FilterViewModel.RestrictStartDate != null) { query.Where(o => o.CreateDate >= FilterViewModel.RestrictStartDate); } if (FilterViewModel.RestrictEndDate != null) { query.Where(o => o.CreateDate <= FilterViewModel.RestrictEndDate.Value.AddDays(1).AddTicks(-1)); } if (FilterViewModel.RestrictCounterparty != null) { query.Where(o => o.Client == FilterViewModel.RestrictCounterparty); } var bottleCountSubquery = QueryOver.Of(() => orderWSPItemAlias) .Where(() => orderWSPAlias.Id == orderWSPItemAlias.OrderWithoutDeliveryForPayment.Id) .Left.JoinAlias(() => orderWSPItemAlias.Order, () => orderAlias) .Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias) .Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias) .Where(() => nomenclatureAlias.Category == NomenclatureCategory.water && nomenclatureAlias.TareVolume == TareVolume.Vol19L) .Select(Projections.Sum(() => orderItemAlias.Count)); var orderSumSubquery = QueryOver.Of(() => orderWSPItemAlias) .Where(() => orderWSPAlias.Id == orderWSPItemAlias.OrderWithoutDeliveryForPayment.Id) .Left.JoinAlias(() => orderWSPItemAlias.Order, () => orderAlias) .Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias) .Select( Projections.Sum( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.Decimal, "?1 * ?2 - IF(?3 IS NULL OR ?3 = 0, IFNULL(?4, 0), ?3)"), NHibernateUtil.Decimal, Projections.Property(() => orderItemAlias.Count), Projections.Property(() => orderItemAlias.Price), Projections.Property(() => orderItemAlias.DiscountMoney), Projections.Property(() => orderItemAlias.OriginalDiscountMoney) ) ) ); query.Where(GetSearchCriterion( () => orderWSPAlias.Id, () => counterpartyAlias.Name, () => authorAlias.LastName )); var resultQuery = query .SelectList(list => list .Select(() => orderWSPAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => orderWSPAlias.CreateDate).WithAlias(() => resultAlias.CreateDate) .Select(() => orderWSPAlias.CreateDate).WithAlias(() => resultAlias.Date) .Select(() => authorAlias.LastName).WithAlias(() => resultAlias.AuthorLastName) .Select(() => authorAlias.Name).WithAlias(() => resultAlias.AuthorName) .Select(() => authorAlias.Patronymic).WithAlias(() => resultAlias.AuthorPatronymic) .Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.Counterparty) .SelectSubQuery(orderSumSubquery).WithAlias(() => resultAlias.Sum) .SelectSubQuery(bottleCountSubquery).WithAlias(() => resultAlias.BottleAmount) ) .OrderBy(x => x.CreateDate).Desc .SetTimeout(60) .TransformUsing(Transformers.AliasToBean <RetailOrderJournalNode <OrderWithoutShipmentForPayment> >()); 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 FastDeliveryAvailabilityHistory GetRouteListsForFastDelivery( IUnitOfWork uow, double latitude, double longitude, bool isGetClosestByRoute, IDeliveryRulesParametersProvider deliveryRulesParametersProvider, IEnumerable <NomenclatureAmountNode> nomenclatureNodes, Order fastDeliveryOrder = null) { var maxDistanceToTrackPoint = deliveryRulesParametersProvider.MaxDistanceToLatestTrackPointKm; var driverGoodWeightLiftPerHand = deliveryRulesParametersProvider.DriverGoodWeightLiftPerHandInKg; var maxFastOrdersPerSpecificTime = deliveryRulesParametersProvider.MaxFastOrdersPerSpecificTime; var maxTimeForFastDeliveryTimespan = deliveryRulesParametersProvider.MaxTimeForFastDelivery; //Переводим всё в минуты var trackPointTimeOffset = (int)deliveryRulesParametersProvider.MaxTimeOffsetForLatestTrackPoint.TotalMinutes; var maxTimeForFastDelivery = (int)maxTimeForFastDeliveryTimespan.TotalMinutes; var minTimeForNewOrder = (int)deliveryRulesParametersProvider.MinTimeForNewFastDeliveryOrder.TotalMinutes; var driverUnloadTime = (int)deliveryRulesParametersProvider.DriverUnloadTime.TotalMinutes; var specificTimeForFastOrdersCount = (int)deliveryRulesParametersProvider.SpecificTimeForMaxFastOrdersCount.TotalMinutes; var fastDeliveryAvailabilityHistory = new FastDeliveryAvailabilityHistory { IsGetClosestByRoute = isGetClosestByRoute, Order = fastDeliveryOrder, MaxDistanceToLatestTrackPointKm = maxDistanceToTrackPoint, DriverGoodWeightLiftPerHandInKg = driverGoodWeightLiftPerHand, MaxFastOrdersPerSpecificTime = maxFastOrdersPerSpecificTime, MaxTimeForFastDelivery = maxTimeForFastDeliveryTimespan, MinTimeForNewFastDeliveryOrder = deliveryRulesParametersProvider.MinTimeForNewFastDeliveryOrder, DriverUnloadTime = deliveryRulesParametersProvider.DriverUnloadTime, SpecificTimeForMaxFastOrdersCount = deliveryRulesParametersProvider.SpecificTimeForMaxFastOrdersCount, }; var order = fastDeliveryAvailabilityHistory.Order; if (order != null) { fastDeliveryAvailabilityHistory.Order = order.Id == 0 ? null : order; fastDeliveryAvailabilityHistory.Author = order.Author; fastDeliveryAvailabilityHistory.DeliveryPoint = order.DeliveryPoint; fastDeliveryAvailabilityHistory.District = order.DeliveryPoint.District; fastDeliveryAvailabilityHistory.Counterparty = order.Client; } var fastDeliveryHistoryConverter = new FastDeliveryHistoryConverter(); if (nomenclatureNodes != null) { fastDeliveryAvailabilityHistory.OrderItemsHistory = fastDeliveryHistoryConverter.ConvertNomenclatureAmountNodesToOrderItemsHistory(nomenclatureNodes, fastDeliveryAvailabilityHistory); } var distributions = uow.GetAll <AdditionalLoadingNomenclatureDistribution>(); fastDeliveryAvailabilityHistory.NomenclatureDistributionHistoryItems = fastDeliveryHistoryConverter.ConvertNomenclatureDistributionToDistributionHistory(distributions, fastDeliveryAvailabilityHistory); var district = GetDistrict(uow, (decimal)latitude, (decimal)longitude); if (district?.TariffZone == null || !district.TariffZone.IsFastDeliveryAvailableAtCurrentTime) { fastDeliveryAvailabilityHistory.AdditionalInformation = new List <string> { "Не найден район, у района отсутствует тарифная зона, либо недоступна экспресс-доставка в текущее время." }; return(fastDeliveryAvailabilityHistory); } var neededNomenclatures = nomenclatureNodes.ToDictionary(x => x.NomenclatureId, x => x.Amount); Track t = null; TrackPoint tp = null; RouteList rl = null; TrackPoint tpInner = null; FastDeliveryVerificationDetailsNode result = null; Employee e = null; RouteListItem rla = null; RouteListItem rlaTransfered = null; Order o = null; OrderItem oi = null; OrderEquipment oe = null; CarLoadDocument scld = null; CarLoadDocumentItem scldi = null; CountUnclosedFastDeliveryAddressesNode countUnclosedFastDeliveryAddressesAlias = null; RouteListNomenclatureAmount ordersAmountAlias = null; RouteListNomenclatureAmount loadDocumentsAmountAlias = null; var lastTimeTrackQuery = QueryOver.Of(() => tpInner) .Where(() => tpInner.Track.Id == t.Id) .Select(Projections.Max(() => tpInner.TimeStamp)); //МЛ только в пути и с погруженным запасом var routeListNodes = uow.Session.QueryOver(() => rl) .JoinEntityAlias(() => t, () => t.RouteList.Id == rl.Id) .Inner.JoinAlias(() => t.TrackPoints, () => tp) .Inner.JoinAlias(() => rl.Driver, () => e) .WithSubquery.WhereProperty(() => tp.TimeStamp).Eq(lastTimeTrackQuery) .And(() => rl.Status == RouteListStatus.EnRoute) .And(() => rl.AdditionalLoadingDocument.Id != null) // только с погруженным запасом .SelectList(list => list .Select(() => tp.TimeStamp).WithAlias(() => result.TimeStamp) .Select(() => tp.Latitude).WithAlias(() => result.Latitude) .Select(() => tp.Longitude).WithAlias(() => result.Longitude) .Select(Projections.Entity(() => rl)).WithAlias(() => result.RouteList)) .TransformUsing(Transformers.AliasToBean <FastDeliveryVerificationDetailsNode>()) .List <FastDeliveryVerificationDetailsNode>(); //Последняя координата в указанном радиусе foreach (var node in routeListNodes) { var distance = DistanceHelper.GetDistanceKm(node.Latitude, node.Longitude, latitude, longitude); var deliveryPoint = new PointOnEarth(latitude, longitude); var proposedRoute = OsrmClientFactory.Instance .GetRoute(new List <PointOnEarth> { new PointOnEarth(node.Latitude, node.Longitude), deliveryPoint }, false, GeometryOverview.False, _globalSettings.ExcludeToll)?.Routes? .FirstOrDefault(); node.DistanceByLineToClient.ParameterValue = (decimal)distance; node.DistanceByRoadToClient.ParameterValue = decimal.Round((decimal)(proposedRoute?.TotalDistance ?? int.MaxValue) / 1000, 2); if (distance < maxDistanceToTrackPoint) { node.DistanceByLineToClient.IsValidParameter = node.DistanceByRoadToClient.IsValidParameter = true; } else { node.DistanceByLineToClient.IsValidParameter = node.DistanceByRoadToClient.IsValidParameter = false; node.IsValidRLToFastDelivery = false; } //Выставляем время последней координаты var timeSpan = DateTime.Now - node.TimeStamp; node.LastCoordinateTime.ParameterValue = timeSpan.TotalHours > 838 ? new TimeSpan(838, 0, 0) : timeSpan; if (node.LastCoordinateTime.ParameterValue.TotalMinutes <= trackPointTimeOffset) { node.LastCoordinateTime.IsValidParameter = true; } else { node.LastCoordinateTime.IsValidParameter = false; node.IsValidRLToFastDelivery = false; } } routeListNodes = routeListNodes .OrderBy(x => isGetClosestByRoute ? x.DistanceByRoadToClient.ParameterValue : x.DistanceByLineToClient.ParameterValue) .ToList(); //Не более определённого кол-ва заказов с быстрой доставкой в определённый промежуток времени var addressCountSubquery = QueryOver.Of(() => rla) .Inner.JoinAlias(() => rla.Order, () => o) .Where(() => rla.RouteList.Id == rl.Id) .And(() => rla.Status == RouteListItemStatus.EnRoute) .And(() => o.IsFastDelivery) .And(Restrictions.GtProperty( Projections.Property(() => rla.CreationDate), Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.DateTime, $"TIMESTAMPADD(MINUTE, -{specificTimeForFastOrdersCount}, CURRENT_TIMESTAMP)"), NHibernateUtil.DateTime))) .Select(Projections.Count(() => rla.Id)); var routeListsWithCountUnclosedFastDeliveries = uow.Session.QueryOver(() => rl) .WhereRestrictionOn(() => rl.Id).IsInG(routeListNodes.Select(x => x.RouteList.Id)) .SelectList(list => list .Select(() => rl.Id).WithAlias(() => countUnclosedFastDeliveryAddressesAlias.RouteListId) .SelectSubQuery(addressCountSubquery).WithAlias(() => countUnclosedFastDeliveryAddressesAlias.UnclosedFastDeliveryAddresses)) .TransformUsing(Transformers.AliasToBean <CountUnclosedFastDeliveryAddressesNode>()) .List <CountUnclosedFastDeliveryAddressesNode>(); var rlsWithCountUnclosedFastDeliveries = routeListsWithCountUnclosedFastDeliveries.ToDictionary(x => x.RouteListId, x => x.UnclosedFastDeliveryAddresses); foreach (var node in routeListNodes) { var countUnclosedFastDeliveryAddresses = rlsWithCountUnclosedFastDeliveries[node.RouteList.Id]; node.UnClosedFastDeliveries.ParameterValue = countUnclosedFastDeliveryAddresses; if (countUnclosedFastDeliveryAddresses < maxFastOrdersPerSpecificTime) { node.UnClosedFastDeliveries.IsValidParameter = true; } else { node.UnClosedFastDeliveries.IsValidParameter = false; node.IsValidRLToFastDelivery = false; } } //Время доставки следующего (текущего) заказа позволяет взять быструю доставку foreach (var routeListNode in routeListNodes) { RouteListItem latestAddress = null; var orderedEnRouteAddresses = routeListNode.RouteList.Addresses .Where(x => x.Status == RouteListItemStatus.EnRoute).OrderBy(x => x.IndexInRoute).ToList(); var orderedCompletedAddresses = routeListNode.RouteList.Addresses .Where(x => x.Status == RouteListItemStatus.Completed).OrderBy(x => x.IndexInRoute).ToList(); var latestCompletedAddress = orderedCompletedAddresses.OrderByDescending(x => x.StatusLastUpdate).FirstOrDefault(); if (latestCompletedAddress != null) { latestAddress = orderedEnRouteAddresses.FirstOrDefault(x => x.IndexInRoute > latestCompletedAddress.IndexInRoute); } if (latestAddress == null) { latestAddress = orderedEnRouteAddresses.FirstOrDefault(); } if (latestAddress != null) { var neededTime1 = maxTimeForFastDelivery - latestAddress.Order.DeliveryPoint.MinutesToUnload; if (neededTime1 < minTimeForNewOrder) { routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = new TimeSpan(0, neededTime1, 0); routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = false; routeListNode.IsValidRLToFastDelivery = false; continue; } var water19Count = latestAddress.Order.OrderItems .Where(x => x.Nomenclature.TareVolume == TareVolume.Vol19L && x.Nomenclature.Category == NomenclatureCategory.water) .Sum(x => x.Count); var orderItemsSummaryWeight = latestAddress.Order.OrderItems .Where(x => x.Nomenclature.TareVolume != TareVolume.Vol19L || x.Nomenclature.Category != NomenclatureCategory.water) .Sum(x => x.Nomenclature.Weight * x.Count); var orderEquipmentsSummaryWeight = latestAddress.Order.OrderEquipments .Where(x => x.Direction == Direction.Deliver) .Sum(x => x.Nomenclature.Weight * x.Count); var goodsSummaryWeight = orderItemsSummaryWeight + orderEquipmentsSummaryWeight; //Время выгрузки след. заказа: //(Суммарный вес прочих товаров / кол-во кг, которое водитель может унести в одной руке + кол-во 19л) / 2 руки * время выгрузки в 2 руках 2 бутылей или товара var unloadTime = (goodsSummaryWeight / driverGoodWeightLiftPerHand + water19Count) / 2 * driverUnloadTime; var neededTime2 = maxTimeForFastDelivery - (int)unloadTime; if (neededTime2 < minTimeForNewOrder) { routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = new TimeSpan(0, neededTime2, 0); routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = false; routeListNode.IsValidRLToFastDelivery = false; } else { routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = new TimeSpan(0, neededTime2, 0); routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = true; } } else { routeListNode.RemainingTimeForShipmentNewOrder.ParameterValue = maxTimeForFastDeliveryTimespan; routeListNode.RemainingTimeForShipmentNewOrder.IsValidParameter = true; } } var rlIds = routeListNodes.Select(x => x.RouteList.Id).ToArray(); //OrderItems var orderItemsToDeliver = uow.Session.QueryOver <RouteListItem>(() => rla) .Inner.JoinAlias(() => rla.Order, () => o) .Inner.JoinAlias(() => o.OrderItems, () => oi) .Left.JoinAlias(() => rla.TransferedTo, () => rlaTransfered) .WhereRestrictionOn(() => rla.RouteList.Id).IsIn(rlIds) .WhereRestrictionOn(() => oi.Nomenclature.Id).IsIn(neededNomenclatures.Keys) .Where(() => //не отменённые и не недовозы rla.Status != RouteListItemStatus.Canceled && rla.Status != RouteListItemStatus.Overdue // и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки) && (!rla.WasTransfered || rla.NeedToReload || o.IsFastDelivery) // и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся) && (rla.Status != RouteListItemStatus.Transfered || (!rlaTransfered.NeedToReload && !o.IsFastDelivery))) .SelectList(list => list .SelectGroup(() => rla.RouteList.Id).WithAlias(() => ordersAmountAlias.RouteListId) .SelectGroup(() => oi.Nomenclature.Id).WithAlias(() => ordersAmountAlias.NomenclatureId) .SelectSum(() => oi.Count).WithAlias(() => ordersAmountAlias.Amount)) .TransformUsing(Transformers.AliasToBean <RouteListNomenclatureAmount>()) .Future <RouteListNomenclatureAmount>(); //OrderEquipments var orderEquipmentsToDeliver = uow.Session.QueryOver <RouteListItem>(() => rla) .Inner.JoinAlias(() => rla.Order, () => o) .Inner.JoinAlias(() => o.OrderEquipments, () => oe) .Left.JoinAlias(() => rla.TransferedTo, () => rlaTransfered) .WhereRestrictionOn(() => rla.RouteList.Id).IsIn(rlIds) .WhereRestrictionOn(() => oe.Nomenclature.Id).IsIn(neededNomenclatures.Keys) .Where(() => //не отменённые и не недовозы rla.Status != RouteListItemStatus.Canceled && rla.Status != RouteListItemStatus.Overdue // и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки) && (!rla.WasTransfered || rla.NeedToReload || o.IsFastDelivery) // и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся) && (rla.Status != RouteListItemStatus.Transfered || (!rlaTransfered.NeedToReload && !o.IsFastDelivery))) .And(() => oe.Direction == Direction.Deliver) .SelectList(list => list .SelectGroup(() => rla.RouteList.Id).WithAlias(() => ordersAmountAlias.RouteListId) .SelectGroup(() => oe.Nomenclature.Id).WithAlias(() => ordersAmountAlias.NomenclatureId) .Select(Projections.Sum(Projections.Cast(NHibernateUtil.Decimal, Projections.Property(() => oe.Count))) ).WithAlias(() => ordersAmountAlias.Amount)) .TransformUsing(Transformers.AliasToBean <RouteListNomenclatureAmount>()) .Future <RouteListNomenclatureAmount>(); //CarLoadDocuments var allLoaded = uow.Session.QueryOver <CarLoadDocument>(() => scld) .Inner.JoinAlias(() => scld.Items, () => scldi) .WhereRestrictionOn(() => scld.RouteList.Id).IsIn(rlIds) .WhereRestrictionOn(() => scldi.Nomenclature.Id).IsIn(neededNomenclatures.Keys) .SelectList(list => list .SelectGroup(() => scld.RouteList.Id).WithAlias(() => loadDocumentsAmountAlias.RouteListId) .SelectGroup(() => scldi.Nomenclature.Id).WithAlias(() => loadDocumentsAmountAlias.NomenclatureId) .SelectSum(() => scldi.Amount).WithAlias(() => loadDocumentsAmountAlias.Amount)) .TransformUsing(Transformers.AliasToBean <RouteListNomenclatureAmount>()) .Future <RouteListNomenclatureAmount>(); var allToDeliver = orderItemsToDeliver .Union(orderEquipmentsToDeliver) .GroupBy(x => new { x.RouteListId, x.NomenclatureId }) .Select(group => new RouteListNomenclatureAmount { RouteListId = group.Key.RouteListId, NomenclatureId = group.Key.NomenclatureId, Amount = group.Sum(x => x.Amount) }) .ToList(); //Выбираем МЛ, в котором хватает запаса номенклатур на поступивший быстрый заказ foreach (var routeListNode in routeListNodes) { var toDeliverForRL = allToDeliver.Where(x => x.RouteListId == routeListNode.RouteList.Id).ToList(); var loadedForRL = allLoaded.Where(x => x.RouteListId == routeListNode.RouteList.Id).ToList(); foreach (var need in neededNomenclatures) { var toDeliver = toDeliverForRL.FirstOrDefault(x => x.NomenclatureId == need.Key)?.Amount ?? 0; var loaded = loadedForRL.FirstOrDefault(x => x.NomenclatureId == need.Key)?.Amount ?? 0; var onBoard = loaded - toDeliver; if (onBoard < need.Value) { routeListNode.IsGoodsEnough.ParameterValue = false; routeListNode.IsGoodsEnough.IsValidParameter = false; routeListNode.IsValidRLToFastDelivery = false; break; } } } if (routeListNodes != null) { fastDeliveryAvailabilityHistory.Items = fastDeliveryHistoryConverter .ConvertVerificationDetailsNodesToAvailabilityHistoryItems(routeListNodes, fastDeliveryAvailabilityHistory); } return(fastDeliveryAvailabilityHistory); }
public void SynchronizePaymentStatuses() { try { _logger.Info("Запущен процесс синхронизации статусов платежей"); using (var uow = UnitOfWorkFactory.CreateWithoutRoot()) { RouteListItem routeListItemAlias = null; Order orderAlias = null; SmsPayment smsPaymentAlias = null; RouteList routeListAlias = null; var payments = uow.Session.QueryOver <SmsPayment>(() => smsPaymentAlias) .Inner.JoinAlias(() => smsPaymentAlias.Order, () => orderAlias) .JoinEntityAlias(() => routeListItemAlias, () => routeListItemAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin) .Left.JoinAlias(() => routeListItemAlias.RouteList, () => routeListAlias) .Where(() => smsPaymentAlias.SmsPaymentStatus == SmsPaymentStatus.WaitingForPayment) .And(Restrictions.Disjunction() .Add(Restrictions.Eq(Projections.Property(() => routeListAlias.Id), null)) .Add(() => routeListAlias.Status != RouteListStatus.Closed)) .List <SmsPayment>(); int count = 0; foreach (var payment in payments) { var actualStatus = _paymentController.GetPaymentStatus(payment.ExternalId); if (actualStatus == null || actualStatus == payment.SmsPaymentStatus) { continue; } switch (actualStatus.Value) { case SmsPaymentStatus.WaitingForPayment: payment.SetWaitingForPayment(); break; case SmsPaymentStatus.Paid: payment.SetPaid(uow, DateTime.Now, uow.GetById <PaymentFrom>(_orderParametersProvider.PaymentByCardFromSmsId)); break; case SmsPaymentStatus.Cancelled: payment.SetCancelled(); break; default: throw new ArgumentOutOfRangeException(); } uow.Save(payment); uow.Commit(); count++; } _logger.Info(payments.Count == 0 ? $"Не найдено ни одного платежа для синхронизации" : $"Синхронизировано {count} статусов платежей"); } } catch (Exception ex) { _logger.Error(ex, "При синхронизации произошла ошибка"); } }
/// <summary> /// Кол-во 19л. воды в заказе /// </summary> /// <returns>Кол-во 19л. воды в заказе</returns> /// <param name="uow">Uow.</param> /// <param name="order">Заказ</param> public static int Get19LWatterQtyForOrder(IUnitOfWork uow, VodovozOrder order) { return(OrderSingletonRepository.GetInstance().Get19LWatterQtyForOrder(uow, order)); }
/// <summary> /// Оборудование заказа от клиента /// </summary> /// <returns>Список оборудования от клиенту</returns> /// <param name="uow">Uow.</param> /// <param name="order">Заказ</param> public static IList <ClientEquipmentNode> GetEquipmentFromClientForOrder(IUnitOfWork uow, VodovozOrder order) { return(OrderSingletonRepository.GetInstance().GetEquipmentFromClientForOrder(uow, order)); }
/// <summary> /// Первый заказ контрагента, который можно считать выполненым. /// </summary> /// <returns>Первый заказ</returns> /// <param name="uow">UoW</param> /// <param name="counterparty">Контрагент</param> public static VodovozOrder GetFirstRealOrderForClientForActionBottle(IUnitOfWork uow, VodovozOrder order, Counterparty counterparty) { return(OrderSingletonRepository.GetInstance().GetFirstRealOrderForClientForActionBottle(uow, order, counterparty)); }
public static OrderStatus[] GetStatusesForActualCount(VodovozOrder order) { return(OrderSingletonRepository.GetInstance().GetStatusesForActualCount(order)); }
/// <summary> /// Список МЛ для заказа, отсортированный в порядке владения этим заказом, в случае переносов /// </summary> /// <returns>Список МЛ</returns> /// <param name="UoW">UoW</param> /// <param name="order">Заказ</param> public static IList <RouteList> GetAllRLForOrder(IUnitOfWork UoW, VodovozOrder order) { return(OrderSingletonRepository.GetInstance().GetAllRLForOrder(UoW, order)); }
public IList <UndeliveredOrder> GetListOfUndeliveriesForOrder(IUnitOfWork uow, int orderId) { Order order = uow.GetById <Order>(orderId); return(GetListOfUndeliveriesForOrder(uow, order)); }
public bool HasEnoughQuantityForFastDelivery(IUnitOfWork uow, RouteListItem routeListItemFrom, RouteList routeListTo) { RouteListItem routeListItemAlias = null; RouteListItem routeListItemTransferedAlias = null; OrderItem orderItemAlias = null; NomenclatureAmountNode nomenclatureAmountAlias = null; Order orderAlias = null; OrderEquipment orderEquipmentAlias = null; CarLoadDocument carLoadDocumentAlias = null; CarLoadDocumentItem carLoadDocumentItemAlias = null; var nomenclaturesToDeliver = routeListItemFrom.Order.GetAllGoodsToDeliver(); var neededIds = nomenclaturesToDeliver.Select(x => x.NomenclatureId).ToArray(); var orderItemsToDeliver = uow.Session.QueryOver <RouteListItem>(() => routeListItemAlias) .Inner.JoinAlias(() => routeListItemAlias.Order, () => orderAlias) .Inner.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias) .Left.JoinAlias(() => routeListItemAlias.TransferedTo, () => routeListItemTransferedAlias) .Where(() => routeListItemAlias.RouteList.Id == routeListTo.Id) .WhereRestrictionOn(() => orderItemAlias.Nomenclature.Id).IsIn(neededIds) .Where(() => //не отменённые и не недовозы routeListItemAlias.Status != RouteListItemStatus.Canceled && routeListItemAlias.Status != RouteListItemStatus.Overdue // и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки) && (!routeListItemAlias.WasTransfered || routeListItemAlias.NeedToReload || orderAlias.IsFastDelivery) // и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся) && (routeListItemAlias.Status != RouteListItemStatus.Transfered || (!routeListItemTransferedAlias.NeedToReload && !orderAlias.IsFastDelivery))) .SelectList(list => list .SelectGroup(() => orderItemAlias.Nomenclature.Id).WithAlias(() => nomenclatureAmountAlias.NomenclatureId) .SelectSum(() => orderItemAlias.Count).WithAlias(() => nomenclatureAmountAlias.Amount) ).TransformUsing(Transformers.AliasToBean <NomenclatureAmountNode>()) .Future <NomenclatureAmountNode>(); var orderEquipmentsToDeliver = uow.Session.QueryOver <RouteListItem>(() => routeListItemAlias) .Inner.JoinAlias(() => routeListItemAlias.Order, () => orderAlias) .Inner.JoinAlias(() => orderAlias.OrderEquipments, () => orderEquipmentAlias) .Left.JoinAlias(() => routeListItemAlias.TransferedTo, () => routeListItemTransferedAlias) .Where(() => routeListItemAlias.RouteList.Id == routeListTo.Id) .WhereRestrictionOn(() => orderEquipmentAlias.Nomenclature.Id).IsIn(neededIds) .Where(() => //не отменённые и не недовозы routeListItemAlias.Status != RouteListItemStatus.Canceled && routeListItemAlias.Status != RouteListItemStatus.Overdue // и не перенесённые к водителю; либо перенесённые с погрузкой; либо перенесённые и это экспресс-доставка (всегда без погрузки) && (!routeListItemAlias.WasTransfered || routeListItemAlias.NeedToReload || orderAlias.IsFastDelivery) // и не перенесённые от водителя; либо перенесённые и не нужна погрузка и не экспресс-доставка (остатки по экспресс-доставке не переносятся) && (routeListItemAlias.Status != RouteListItemStatus.Transfered || (!routeListItemTransferedAlias.NeedToReload && !orderAlias.IsFastDelivery))) .And(() => orderEquipmentAlias.Direction == Domain.Orders.Direction.Deliver) .SelectList(list => list .SelectGroup(() => orderEquipmentAlias.Nomenclature.Id).WithAlias(() => nomenclatureAmountAlias.NomenclatureId) .Select(Projections.Cast(NHibernateUtil.Decimal, Projections.Sum(Projections.Property(() => orderEquipmentAlias.Count)))).WithAlias(() => nomenclatureAmountAlias.Amount) ).TransformUsing(Transformers.AliasToBean <NomenclatureAmountNode>()) .Future <NomenclatureAmountNode>(); var allToDeliver = orderItemsToDeliver .Union(orderEquipmentsToDeliver) .GroupBy(x => new { x.NomenclatureId }) .Select(group => new NomenclatureAmountNode() { NomenclatureId = group.Key.NomenclatureId, Amount = group.Sum(x => x.Amount) }) .ToList(); var allLoaded = uow.Session.QueryOver <CarLoadDocument>(() => carLoadDocumentAlias) .Inner.JoinAlias(() => carLoadDocumentAlias.Items, () => carLoadDocumentItemAlias) .Where(() => carLoadDocumentAlias.RouteList.Id == routeListTo.Id) .WhereRestrictionOn(() => carLoadDocumentItemAlias.Nomenclature.Id).IsIn(neededIds) .SelectList(list => list .SelectGroup(() => carLoadDocumentItemAlias.Nomenclature.Id).WithAlias(() => nomenclatureAmountAlias.NomenclatureId) .SelectSum(() => carLoadDocumentItemAlias.Amount).WithAlias(() => nomenclatureAmountAlias.Amount) ).TransformUsing(Transformers.AliasToBean <NomenclatureAmountNode>()) .List <NomenclatureAmountNode>(); foreach (var need in nomenclaturesToDeliver) { var toDeliver = allToDeliver.FirstOrDefault(x => x.NomenclatureId == need.NomenclatureId)?.Amount ?? 0; var loaded = allLoaded.FirstOrDefault(x => x.NomenclatureId == need.NomenclatureId)?.Amount ?? 0; if (loaded - toDeliver < need.Amount) { return(false); } } return(true); }
public IList <UndeliveredOrder> GetListOfUndeliveriesForOrder(IUnitOfWork uow, Order order) { var query = uow.Session.QueryOver <UndeliveredOrder>() .Where(u => u.OldOrder == order) .List <UndeliveredOrder>(); return(query); }
/// <summary> /// Возврат словаря, у которого ключ это <see cref="PromotionalSet.Id"/>, /// а значение - массив с <see cref="VodovozOrder.Id"/>, для всех точек доставок /// похожих по полям <see cref="DeliveryPoint.City"/>, /// <see cref="DeliveryPoint.Street"/>, <see cref="DeliveryPoint.Building"/>, /// <see cref="DeliveryPoint.Room"/> /// </summary> /// <returns>Словарь</returns> /// <param name="uow">Unit Of Work</param> /// <param name="currOrder">Заказ, из которого берётся точка доставки</param> /// <param name="ignoreCurrentOrder">Если <c>true</c>, то в выборке будет /// игнорироваться заказ передаваемы в качестве параметра <paramref name="currOrder"/></param> public Dictionary <int, int[]> GetPromotionalSetsAndCorrespondingOrdersForDeliveryPoint(IUnitOfWork uow, VodovozOrder currOrder, bool ignoreCurrentOrder = false) { if (GetPromotionalSetsAndCorrespondingOrdersForDeliveryPointTestGap != null) { return(GetPromotionalSetsAndCorrespondingOrdersForDeliveryPointTestGap(uow, currOrder, ignoreCurrentOrder)); } VodovozOrder ordersAlias = null; PromotionalSet promotionalSetAlias = null; DeliveryPoint deliveryPointAlias = null; var dp = currOrder.DeliveryPoint; var oId = !ignoreCurrentOrder ? -1 : currOrder.Id; var acceptableStatuses = new[] { OrderStatus.Accepted, OrderStatus.InTravelList, OrderStatus.WaitForPayment, OrderStatus.OnLoading, OrderStatus.OnTheWay, OrderStatus.Shipped, OrderStatus.UnloadingOnStock, OrderStatus.Closed }; var subQuerySimilarDP = QueryOver.Of(() => deliveryPointAlias) .Where(p => p.City == dp.City) .Where(p => p.Street == dp.Street) .Where(p => p.Building == dp.Building) .Where(p => p.Room == dp.Room) .Select(Projections.Property(() => deliveryPointAlias.Id)) ; var result = uow.Session.QueryOver(() => promotionalSetAlias) .JoinAlias(() => promotionalSetAlias.Orders, () => ordersAlias) .JoinAlias(() => ordersAlias.DeliveryPoint, () => deliveryPointAlias) .Where(() => ordersAlias.Id != oId) .Where(() => ordersAlias.OrderStatus.IsIn(acceptableStatuses)) .WithSubquery.WhereProperty(() => deliveryPointAlias.Id).In(subQuerySimilarDP) .SelectList(list => list.Select(() => promotionalSetAlias.Id) .Select(() => ordersAlias.Id)) .List <object[]>() .GroupBy(x => (int)x[0]) .ToDictionary(g => g.Key, g => g.Select(x => (int)x[1]).ToArray()); return(result); }
private IQueryOver <VodovozOrder> GetOrdersQuery(IUnitOfWork uow) { RetailOrderJournalNode resultAlias = null; VodovozOrder orderAlias = null; Nomenclature nomenclatureAlias = null; OrderItem orderItemAlias = null; Counterparty counterpartyAlias = null; DeliveryPoint deliveryPointAlias = null; DeliverySchedule deliveryScheduleAlias = null; Employee authorAlias = null; Employee lastEditorAlias = null; District districtAlias = null; CounterpartyContract contractAlias = null; Nomenclature sanitizationNomenclature = _nomenclatureRepository.GetSanitisationNomenclature(uow); var query = uow.Session.QueryOver <VodovozOrder>(() => orderAlias); if (FilterViewModel != null && FilterViewModel.IsForRetail != null) { query.JoinEntityAlias( () => counterpartyAlias, () => counterpartyAlias.Id == orderAlias.Client.Id && counterpartyAlias.IsForRetail == FilterViewModel.IsForRetail, NHibernate.SqlCommand.JoinType.InnerJoin); } else { query.Left.JoinAlias(o => o.Client, () => counterpartyAlias); } if (FilterViewModel.ViewTypes != ViewTypes.Order && FilterViewModel.ViewTypes != ViewTypes.All) { query.Where(o => o.Id == -1); } if (FilterViewModel.RestrictStatus != null) { query.Where(o => o.OrderStatus == FilterViewModel.RestrictStatus); } if (FilterViewModel.RestrictPaymentType != null) { query.Where(o => o.PaymentType == FilterViewModel.RestrictPaymentType); } if (FilterViewModel.HideStatuses != null) { query.WhereRestrictionOn(o => o.OrderStatus).Not.IsIn(FilterViewModel.HideStatuses); } if (FilterViewModel.RestrictOnlySelfDelivery != null) { query.Where(o => o.SelfDelivery == FilterViewModel.RestrictOnlySelfDelivery); } if (FilterViewModel.RestrictWithoutSelfDelivery != null) { query.Where(o => o.SelfDelivery != FilterViewModel.RestrictWithoutSelfDelivery); } if (FilterViewModel.RestrictCounterparty != null) { query.Where(o => o.Client == FilterViewModel.RestrictCounterparty); } if (FilterViewModel.DeliveryPoint != null) { query.Where(o => o.DeliveryPoint == FilterViewModel.DeliveryPoint); } if (FilterViewModel.RestrictStartDate != null) { query.Where(o => o.DeliveryDate >= FilterViewModel.RestrictStartDate); } if (FilterViewModel.RestrictEndDate != null) { query.Where(o => o.DeliveryDate <= FilterViewModel.RestrictEndDate.Value.AddDays(1).AddTicks(-1)); } if (FilterViewModel.RestrictLessThreeHours == true) { query.Where(Restrictions .GtProperty(Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.Time, "ADDTIME(?1, ?2)"), NHibernateUtil.Time, Projections.Property(() => deliveryScheduleAlias.From), Projections.Constant("3:0:0")), Projections.Property(() => deliveryScheduleAlias.To))); } if (FilterViewModel.RestrictHideService != null) { if (FilterViewModel.RestrictHideService.Value) { query.Where(o => o.OrderAddressType != OrderAddressType.Service); } else { query.Where(o => o.OrderAddressType == OrderAddressType.Service); } } if (FilterViewModel.RestrictOnlyService != null) { if (FilterViewModel.RestrictOnlyService.Value) { query.Where(o => o.OrderAddressType == OrderAddressType.Service); } else { query.Where(o => o.OrderAddressType != OrderAddressType.Service); } } if (FilterViewModel.OrderPaymentStatus != null) { query.Where(o => o.OrderPaymentStatus == FilterViewModel.OrderPaymentStatus); } if (FilterViewModel.Organisation != null) { query.Where(() => contractAlias.Organization.Id == FilterViewModel.Organisation.Id); } if (FilterViewModel.PaymentByCardFrom != null) { query.Where(o => o.PaymentByCardFrom.Id == FilterViewModel.PaymentByCardFrom.Id); } var bottleCountSubquery = QueryOver.Of <OrderItem>(() => orderItemAlias) .Where(() => orderAlias.Id == orderItemAlias.Order.Id) .JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias) .Where(() => nomenclatureAlias.Category == NomenclatureCategory.water && nomenclatureAlias.TareVolume == TareVolume.Vol19L) .Select(Projections.Sum(() => orderItemAlias.Count)); var sanitisationCountSubquery = QueryOver.Of <OrderItem>(() => orderItemAlias) .Where(() => orderAlias.Id == orderItemAlias.Order.Id) .Where(() => orderItemAlias.Nomenclature.Id == sanitizationNomenclature.Id) .Select(Projections.Sum(() => orderItemAlias.Count)); var orderSumSubquery = QueryOver.Of <OrderItem>(() => orderItemAlias) .Where(() => orderItemAlias.Order.Id == orderAlias.Id) .Select( Projections.Sum( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.Decimal, "ROUND(IFNULL(?1, ?2) * ?3 - ?4, 2)"), NHibernateUtil.Decimal, Projections.Property <OrderItem>(x => x.ActualCount), Projections.Property <OrderItem>(x => x.Count), Projections.Property <OrderItem>(x => x.Price), Projections.Property <OrderItem>(x => x.DiscountMoney) ) ) ); query.Left.JoinAlias(o => o.DeliveryPoint, () => deliveryPointAlias) .Left.JoinAlias(o => o.DeliverySchedule, () => deliveryScheduleAlias) .Left.JoinAlias(o => o.Author, () => authorAlias) .Left.JoinAlias(o => o.LastEditor, () => lastEditorAlias) .Left.JoinAlias(() => deliveryPointAlias.District, () => districtAlias) .Left.JoinAlias(o => o.Contract, () => contractAlias); query.Where(GetSearchCriterion( () => orderAlias.Id, () => counterpartyAlias.Name, () => deliveryPointAlias.CompiledAddress, () => authorAlias.LastName, () => orderAlias.DriverCallId, () => orderAlias.OnlineOrder, () => orderAlias.EShopOrder, () => orderAlias.OrderPaymentStatus )); var resultQuery = query .SelectList(list => list .Select(() => orderAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => orderAlias.SelfDelivery).WithAlias(() => resultAlias.IsSelfDelivery) .Select(() => orderAlias.DeliveryDate).WithAlias(() => resultAlias.Date) .Select(() => orderAlias.CreateDate).WithAlias(() => resultAlias.CreateDate) .Select(() => deliveryScheduleAlias.Name).WithAlias(() => resultAlias.DeliveryTime) .Select(() => orderAlias.OrderStatus).WithAlias(() => resultAlias.StatusEnum) .Select(() => orderAlias.Address1c).WithAlias(() => resultAlias.Address1c) .Select(() => authorAlias.LastName).WithAlias(() => resultAlias.AuthorLastName) .Select(() => authorAlias.Name).WithAlias(() => resultAlias.AuthorName) .Select(() => authorAlias.Patronymic).WithAlias(() => resultAlias.AuthorPatronymic) .Select(() => lastEditorAlias.LastName).WithAlias(() => resultAlias.LastEditorLastName) .Select(() => lastEditorAlias.Name).WithAlias(() => resultAlias.LastEditorName) .Select(() => lastEditorAlias.Patronymic).WithAlias(() => resultAlias.LastEditorPatronymic) .Select(() => orderAlias.LastEditedTime).WithAlias(() => resultAlias.LastEditedTime) .Select(() => orderAlias.DriverCallId).WithAlias(() => resultAlias.DriverCallId) .Select(() => orderAlias.OnlineOrder).WithAlias(() => resultAlias.OnlineOrder) .Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.Counterparty) .Select(() => districtAlias.DistrictName).WithAlias(() => resultAlias.DistrictName) .Select(() => deliveryPointAlias.CompiledAddress).WithAlias(() => resultAlias.CompilledAddress) .Select(() => deliveryPointAlias.City).WithAlias(() => resultAlias.City) .Select(() => deliveryPointAlias.Street).WithAlias(() => resultAlias.Street) .Select(() => deliveryPointAlias.Building).WithAlias(() => resultAlias.Building) .Select(() => orderAlias.EShopOrder).WithAlias(() => resultAlias.EShopOrder) .Select(() => orderAlias.OrderPaymentStatus).WithAlias(() => resultAlias.OrderPaymentStatus) .SelectSubQuery(orderSumSubquery).WithAlias(() => resultAlias.Sum) .SelectSubQuery(bottleCountSubquery).WithAlias(() => resultAlias.BottleAmount) .SelectSubQuery(sanitisationCountSubquery).WithAlias(() => resultAlias.SanitisationAmount) ) .OrderBy(x => x.CreateDate).Desc .SetTimeout(60) .TransformUsing(Transformers.AliasToBean <RetailOrderJournalNode <VodovozOrder> >()); return(resultQuery); }
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); }
/// <summary> /// Запрос выбирающий количество добавленное на склад, отгруженное со склада /// и зарезервированное в заказах каждой номенклатуры по выбранному типу оборудования /// </summary> public QueryOver <Nomenclature, Nomenclature> QueryAvailableNonSerialEquipmentForRent(EquipmentKind kind) { Nomenclature nomenclatureAlias = null; WarehouseMovementOperation operationAddAlias = null; //Подзапрос выбирающий по номенклатуре количество добавленное на склад var subqueryAdded = QueryOver.Of(() => operationAddAlias) .Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id) .Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.IncomingWarehouse))) .Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount)); //Подзапрос выбирающий по номенклатуре количество отгруженное со склада var subqueryRemoved = QueryOver.Of(() => operationAddAlias) .Where(() => operationAddAlias.Nomenclature.Id == nomenclatureAlias.Id) .Where(Restrictions.IsNotNull(Projections.Property <WarehouseMovementOperation>(o => o.WriteoffWarehouse))) .Select(Projections.Sum <WarehouseMovementOperation>(o => o.Amount)); //Подзапрос выбирающий по номенклатуре количество зарезервированное в заказах до отгрузки со склада Vodovoz.Domain.Orders.Order localOrderAlias = null; OrderEquipment localOrderEquipmentAlias = null; Equipment localEquipmentAlias = null; var subqueryReserved = QueryOver.Of(() => localOrderAlias) .JoinAlias(() => localOrderAlias.OrderEquipments, () => localOrderEquipmentAlias) .JoinAlias(() => localOrderEquipmentAlias.Equipment, () => localEquipmentAlias) .Where(() => localEquipmentAlias.Nomenclature.Id == nomenclatureAlias.Id) .Where(() => localOrderEquipmentAlias.Direction == Direction.Deliver) .Where(() => localOrderAlias.OrderStatus == OrderStatus.Accepted || localOrderAlias.OrderStatus == OrderStatus.InTravelList || localOrderAlias.OrderStatus == OrderStatus.OnLoading) .Select(Projections.Sum(() => localOrderEquipmentAlias.Count)); NomenclatureForRentNode resultAlias = null; MeasurementUnits unitAlias = null; EquipmentKind equipmentKindAlias = null; //Запрос выбирающий количество добавленное на склад, отгруженное со склада //и зарезервированное в заказах каждой номенклатуры по выбранному типу оборудования var query = QueryOver.Of(() => nomenclatureAlias) .JoinAlias(() => nomenclatureAlias.Unit, () => unitAlias) .JoinAlias(() => nomenclatureAlias.Kind, () => equipmentKindAlias); if (kind != null) { query = query.Where(() => equipmentKindAlias.Id == kind.Id); } query = query.SelectList( list => list .SelectGroup(() => nomenclatureAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => nomenclatureAlias.Name).WithAlias(() => resultAlias.NomenclatureName) .Select(() => equipmentKindAlias.Id).WithAlias(() => resultAlias.TypeId) .Select(() => equipmentKindAlias.Name).WithAlias(() => resultAlias.EquipmentKindName) .Select(() => unitAlias.Name).WithAlias(() => resultAlias.UnitName) .Select(() => unitAlias.Digits).WithAlias(() => resultAlias.UnitDigits) .SelectSubQuery(subqueryAdded).WithAlias(() => resultAlias.Added) .SelectSubQuery(subqueryRemoved).WithAlias(() => resultAlias.Removed) .SelectSubQuery(subqueryReserved).WithAlias(() => resultAlias.Reserved)) .OrderBy(x => x.Name).Asc .TransformUsing(Transformers.AliasToBean <NomenclatureForRentNode>()); return(query); }
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)); }