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