private IEnumerable <PromosetDuplicateInfoNode> GetDeliveryPointResult(IUnitOfWork uow, DeliveryPoint deliveryPoint)
{
DeliveryPoint deliveryPointAlias = null;
Domain.Orders.Order orderAlias = null;
Domain.Orders.OrderItem orderItemAlias = null;
Counterparty counterpartyAlias = null;
Phone counterpartyPhoneAlias = null;
Phone deliveryPointPhoneAlias = null;
PromosetDuplicateInfoNode resultAlias = null;
var query = uow.Session.QueryOver(() => orderAlias)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
.Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Left.JoinAlias(() => counterpartyAlias.Phones, () => counterpartyPhoneAlias)
.Left.JoinAlias(() => deliveryPointAlias.Phones, () => deliveryPointPhoneAlias)
.Where(
Restrictions.And(
Restrictions.Where(() =>
deliveryPointAlias.City == deliveryPoint.City &&
deliveryPointAlias.Street == deliveryPoint.Street &&
deliveryPointAlias.Building == deliveryPoint.Building &&
deliveryPointAlias.Room == deliveryPoint.Room
),
Restrictions.Ge(Projections.Property(() => deliveryPointAlias.Room), 1)
)
)
.Where(Restrictions.IsNotNull(Projections.Property(() => orderItemAlias.PromoSet)));
var deliveryPointsResult = query.SelectList(list => list
.SelectGroup(() => orderAlias.Id)
.Select(() => orderAlias.DeliveryDate).WithAlias(() => resultAlias.Date)
.Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.Client)
.Select(() => deliveryPointAlias.CompiledAddress).WithAlias(() => resultAlias.Address)
).TransformUsing(Transformers.AliasToBean <PromosetDuplicateInfoNode>())
.List <PromosetDuplicateInfoNode>();
return(deliveryPointsResult);
}
private IEnumerable <PromosetDuplicateInfoNode> GetPhonesResult(IUnitOfWork uow, IEnumerable <Phone> phones)
{
Domain.Orders.Order orderAlias = null;
Domain.Orders.OrderItem orderItemAlias = null;
Counterparty counterpartyAlias = null;
DeliveryPoint deliveryPointAlias = null;
Phone counterpartyPhoneAlias = null;
Phone deliveryPointPhoneAlias = null;
PromosetDuplicateInfoNode resultAlias = null;
var phonesArray = phones.Select(x => x.DigitsNumber).ToArray();
var nullProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "NULLIF(1,1)"),
NHibernateUtil.String
);
var counterpartyPhoneProjection = Projections.Conditional(
Restrictions.In(Projections.Property(() => counterpartyPhoneAlias.DigitsNumber), phonesArray),
Projections.Property(() => counterpartyPhoneAlias.DigitsNumber),
nullProjection
);
var deliveryPointPhoneProjection = Projections.Conditional(
Restrictions.In(Projections.Property(() => deliveryPointPhoneAlias.DigitsNumber), phonesArray),
Projections.Property(() => deliveryPointPhoneAlias.DigitsNumber),
nullProjection
);
var counterpartyConcatPhoneProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
counterpartyPhoneProjection,
Projections.Constant(", ")
);
var deliveryPointConcatPhoneProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(DISTINCT ?1 SEPARATOR ?2)"),
NHibernateUtil.String,
deliveryPointPhoneProjection,
Projections.Constant(", ")
);
var concatPhoneProjection = Projections.SqlFunction(
new SQLFunctionTemplate(NHibernateUtil.String, "CONCAT_WS(', ', ?1, ?2)"),
NHibernateUtil.String,
counterpartyConcatPhoneProjection,
deliveryPointConcatPhoneProjection
);
var phoneResult = uow.Session.QueryOver(() => orderAlias)
.Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias)
.Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias)
.Left.JoinAlias(() => orderAlias.DeliveryPoint, () => deliveryPointAlias)
.Left.JoinAlias(() => counterpartyAlias.Phones, () => counterpartyPhoneAlias)
.Left.JoinAlias(() => deliveryPointAlias.Phones, () => deliveryPointPhoneAlias)
.Where(
Restrictions.Or(
Restrictions.In(Projections.Property(() => counterpartyPhoneAlias.DigitsNumber), phonesArray),
Restrictions.In(Projections.Property(() => deliveryPointPhoneAlias.DigitsNumber), phonesArray)
)
)
.Where(Restrictions.IsNotNull(Projections.Property(() => orderItemAlias.PromoSet)))
.SelectList(list => list
.SelectGroup(() => orderAlias.Id)
.Select(() => orderAlias.DeliveryDate).WithAlias(() => resultAlias.Date)
.Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.Client)
.Select(() => deliveryPointAlias.CompiledAddress).WithAlias(() => resultAlias.Address)
.Select(concatPhoneProjection).WithAlias(() => resultAlias.Phone)
).TransformUsing(Transformers.AliasToBean <PromosetDuplicateInfoNode>())
.List <PromosetDuplicateInfoNode>();
return(phoneResult);
}
