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