Example #1
0
        /// <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);
        }
Example #2
0
        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);
        }
Example #3
0
        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());
        }
Example #4
0
        /// <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);
        }
Example #6
0
        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);
        }
Example #8
0
        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, "При синхронизации произошла ошибка");
            }
        }
Example #9
0
 /// <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));
 }
Example #10
0
 /// <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));
 }
Example #11
0
 /// <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));
 }
Example #12
0
 public static OrderStatus[] GetStatusesForActualCount(VodovozOrder order)
 {
     return(OrderSingletonRepository.GetInstance().GetStatusesForActualCount(order));
 }
Example #13
0
 /// <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));
        }
Example #15
0
        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);
        }
Example #21
0
        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));
        }