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