public IList <UndeliveredOrderCountNode> GetListOfUndeliveriesCountOnDptForDates(IUnitOfWork uow, DateTime?start = null, DateTime?end = null) { UndeliveredOrderCountNode resultAlias = null; UndeliveredOrder undeliveredOrderAlias = null; Subdivision subdivisionAlias = null; Order orderAlias = null; GuiltyInUndelivery guiltyInUndeliveryAlias = null; var query = uow.Session.QueryOver <UndeliveredOrder>(() => undeliveredOrderAlias) .Where(() => guiltyInUndeliveryAlias.GuiltySide == GuiltyTypes.Department) .Left.JoinAlias(u => u.OldOrder, () => orderAlias) .Left.JoinAlias(() => guiltyInUndeliveryAlias.GuiltyDepartment, () => subdivisionAlias) .Left.JoinAlias(() => undeliveredOrderAlias.GuiltyInUndelivery, () => guiltyInUndeliveryAlias); if (start != null && end != null) { query.Where(() => orderAlias.DeliveryDate >= start) .Where(u => orderAlias.DeliveryDate <= end); } var result = query.SelectList(list => list .SelectGroup(() => subdivisionAlias.Id).WithAlias(() => resultAlias.SubdivisionId) .Select(() => subdivisionAlias.Name).WithAlias(() => resultAlias.Subdivision) .SelectCount(() => undeliveredOrderAlias.Id).WithAlias(() => resultAlias.Count) ) .TransformUsing(Transformers.AliasToBean <UndeliveredOrderCountNode>()) .List <UndeliveredOrderCountNode>(); return(result); }
public Dictionary <GuiltyTypes, int> GetDictionaryWithUndeliveriesCountForDates(IUnitOfWork uow, DateTime?start = null, DateTime?end = null) { UndeliveredOrder undeliveredOrderAlias = null; Order orderAlias = null; GuiltyInUndelivery guiltyInUndeliveryAlias = null; var query = uow.Session.QueryOver <UndeliveredOrder>(() => undeliveredOrderAlias); if (start != null && end != null) { query.Left.JoinAlias(u => u.OldOrder, () => orderAlias) .Where(() => orderAlias.DeliveryDate >= start) .Where(u => orderAlias.DeliveryDate <= end); } var result = query .Left.JoinAlias(() => undeliveredOrderAlias.GuiltyInUndelivery, () => guiltyInUndeliveryAlias) .SelectList(list => list .SelectGroup(() => guiltyInUndeliveryAlias.GuiltySide) .SelectCount(() => undeliveredOrderAlias.Id) ) .List <object[]>(); return(result.ToDictionary(x => (GuiltyTypes)x[0], x => (int)x[1])); }
public IList <object[]> GetGuiltyAndCountForDates(IUnitOfWork uow, DateTime?start = null, DateTime?end = null) { UndeliveredOrder undeliveredOrderAlias = null; Subdivision subdivisionAlias = null; Order orderAlias = null; GuiltyInUndelivery guiltyInUndeliveryAlias = null; var query = uow.Session.QueryOver <UndeliveredOrder>(() => undeliveredOrderAlias) .Left.JoinAlias(u => u.OldOrder, () => orderAlias) .Left.JoinAlias(() => undeliveredOrderAlias.GuiltyInUndelivery, () => guiltyInUndeliveryAlias) .Left.JoinAlias(() => guiltyInUndeliveryAlias.GuiltyDepartment, () => subdivisionAlias); if (start != null && end != null) { query.Where(() => orderAlias.DeliveryDate >= start) .Where(u => orderAlias.DeliveryDate <= end); } int i = 0; var result = query.SelectList(list => list .SelectGroup(u => u.Id) .Select( Projections.SqlFunction( new SQLFunctionTemplate( NHibernateUtil.String, "GROUP_CONCAT(CASE ?1 WHEN 'Department' THEN IFNULL(CONCAT('Отд: ', ?2), 'Отдел ВВ') WHEN 'Client' THEN 'Клиент' WHEN 'Driver' THEN 'Водитель' WHEN 'ServiceMan' THEN 'Мастер СЦ' WHEN 'None' THEN 'Нет (не недовоз)' WHEN 'Unknown' THEN 'Неизвестно' ELSE ?1 END ORDER BY ?1 ASC SEPARATOR '\n')" ), NHibernateUtil.String, Projections.Property(() => guiltyInUndeliveryAlias.GuiltySide), Projections.Property(() => subdivisionAlias.ShortName) ) ) ) .List <object[]>() .GroupBy(x => x[1]) .Select(r => new[] { r.Key, r.Count(), i++ }) .ToList(); return(result); }
public IList <object[]> GetGuilties(UndeliveredOrdersFilterViewModel filter) { OrderItem orderItemAlias = null; Nomenclature nomenclatureAlias = null; UndeliveredOrder undeliveredOrderAlias = null; Domain.Orders.Order oldOrderAlias = null; Domain.Orders.Order newOrderAlias = null; Employee oldOrderAuthorAlias = null; Counterparty counterpartyAlias = null; DeliveryPoint undeliveredOrderDeliveryPointAlias = null; Subdivision subdivisionAlias = null; GuiltyInUndelivery guiltyInUndeliveryAlias = null; Employee authorAlias = null; var subquery19LWatterQty = QueryOver.Of <OrderItem>(() => orderItemAlias) .Where(() => orderItemAlias.Order.Id == oldOrderAlias.Id) .Left.JoinQueryOver(i => i.Nomenclature, () => nomenclatureAlias) .Where(n => n.Category == NomenclatureCategory.water && n.TareVolume == TareVolume.Vol19L) .Select(Projections.Sum(() => orderItemAlias.Count)); var query = _uow.Session.QueryOver <UndeliveredOrder>(() => undeliveredOrderAlias) .Left.JoinAlias(u => u.OldOrder, () => oldOrderAlias) .Left.JoinAlias(u => u.NewOrder, () => newOrderAlias) .Left.JoinAlias(() => oldOrderAlias.Client, () => counterpartyAlias) .Left.JoinAlias(() => oldOrderAlias.Author, () => oldOrderAuthorAlias) .Left.JoinAlias(() => oldOrderAlias.DeliveryPoint, () => undeliveredOrderDeliveryPointAlias) .Left.JoinAlias(() => undeliveredOrderAlias.GuiltyInUndelivery, () => guiltyInUndeliveryAlias) .Left.JoinAlias(() => guiltyInUndeliveryAlias.GuiltyDepartment, () => subdivisionAlias) .Left.JoinAlias(u => u.Author, () => authorAlias); if (filter?.RestrictDriver != null) { var oldOrderIds = _undeliveredOrdersRepository.GetListOfUndeliveryIdsForDriver(_uow, filter.RestrictDriver); query.Where(() => oldOrderAlias.Id.IsIn(oldOrderIds.ToArray())); } if (filter?.RestrictOldOrder != null) { query.Where(() => oldOrderAlias.Id == filter.RestrictOldOrder.Id); } if (filter?.RestrictClient != null) { query.Where(() => counterpartyAlias.Id == filter.RestrictClient.Id); } if (filter?.RestrictAddress != null) { query.Where(() => undeliveredOrderDeliveryPointAlias.Id == filter.RestrictAddress.Id); } if (filter?.RestrictOldOrderAuthor != null) { query.Where(() => oldOrderAuthorAlias.Id == filter.RestrictOldOrderAuthor.Id); } if (filter?.RestrictOldOrderStartDate != null) { query.Where(() => oldOrderAlias.DeliveryDate >= filter.RestrictOldOrderStartDate); } if (filter?.RestrictOldOrderEndDate != null) { query.Where(() => oldOrderAlias.DeliveryDate <= filter.RestrictOldOrderEndDate.Value.AddDays(1).AddTicks(-1)); } if (filter?.RestrictNewOrderStartDate != null) { query.Where(() => newOrderAlias.DeliveryDate >= filter.RestrictNewOrderStartDate); } if (filter?.RestrictNewOrderEndDate != null) { query.Where(() => newOrderAlias.DeliveryDate <= filter.RestrictNewOrderEndDate.Value.AddDays(1).AddTicks(-1)); } if (filter?.RestrictGuiltySide != null) { query.Where(() => guiltyInUndeliveryAlias.GuiltySide == filter.RestrictGuiltySide); } if (filter != null && filter.RestrictIsProblematicCases) { query.Where(() => !guiltyInUndeliveryAlias.GuiltySide.IsIn(filter.ExcludingGuiltiesForProblematicCases)); } if (filter?.RestrictGuiltyDepartment != null) { query.Where(() => subdivisionAlias.Id == filter.RestrictGuiltyDepartment.Id); } if (filter?.RestrictInProcessAtDepartment != null) { query.Where(u => u.InProcessAtDepartment.Id == filter.RestrictInProcessAtDepartment.Id); } if (filter?.NewInvoiceCreated != null) { if (filter.NewInvoiceCreated.Value) { query.Where(u => u.NewOrder != null); } else { query.Where(u => u.NewOrder == null); } } if (filter?.RestrictUndeliveryStatus != null) { query.Where(u => u.UndeliveryStatus == filter.RestrictUndeliveryStatus); } if (filter?.RestrictUndeliveryAuthor != null) { query.Where(u => u.Author == filter.RestrictUndeliveryAuthor); } if (filter?.RestrictAuthorSubdivision != null) { query.Where(() => authorAlias.Subdivision.Id == filter.RestrictAuthorSubdivision.Id); } int position = 0; var result = query.SelectList(list => list .SelectGroup(u => u.Id) .Select(Projections.SqlFunction( new SQLFunctionTemplate( NHibernateUtil.String, "GROUP_CONCAT(" + "CASE ?1 " + $"WHEN '{nameof(GuiltyTypes.Department)}' THEN IFNULL(CONCAT('Отд: ', ?2), 'Отдел ВВ') " + $"WHEN '{nameof(GuiltyTypes.Client)}' THEN 'Клиент' " + $"WHEN '{nameof(GuiltyTypes.Driver)}' THEN 'Водитель' " + $"WHEN '{nameof(GuiltyTypes.ServiceMan)}' THEN 'Мастер СЦ' " + $"WHEN '{nameof(GuiltyTypes.ForceMajor)}' THEN 'Форс-мажор' " + $"WHEN '{nameof(GuiltyTypes.None)}' THEN 'Нет (не недовоз)' " + "ELSE ?1 " + "END ORDER BY ?1 ASC SEPARATOR '\n')" ), NHibernateUtil.String, Projections.Property(() => guiltyInUndeliveryAlias.GuiltySide), Projections.Property(() => subdivisionAlias.ShortName))) .SelectSubQuery(subquery19LWatterQty)) .List <object[]>() .GroupBy(x => x[1]) .Select(r => new[] { r.Key, r.Count(), position++, r.Sum(x => x[2] == null ? 0 : (decimal)x[2]) }) .ToList(); return(result); }
private IQueryOver <UndeliveredOrder> GetUndeliveredOrdersQuery(IUnitOfWork uow) { UndeliveredOrderJournalNode resultAlias = null; UndeliveredOrder undeliveredOrderAlias = null; Domain.Orders.Order oldOrderAlias = null; Domain.Orders.Order newOrderAlias = null; Employee driverAlias = null; Employee oldOrderAuthorAlias = null; Employee authorAlias = null; Employee editorAlias = null; Employee registratorAlias = null; Nomenclature nomenclatureAlias = null; OrderItem orderItemAlias = null; OrderEquipment orderEquipmentAlias = null; Counterparty counterpartyAlias = null; DeliveryPoint undeliveredOrderDeliveryPointAlias = null; DeliverySchedule undeliveredOrderDeliveryScheduleAlias = null; DeliverySchedule newOrderDeliveryScheduleAlias = null; RouteList routeListAlias = null; RouteListItem routeListItemAlias = null; Subdivision subdivisionAlias = null; Fine fineAlias = null; FineItem fineItemAlias = null; Employee finedEmployeeAlias = null; Subdivision inProcessAtSubdivisionAlias = null; Subdivision authorSubdivisionAlias = null; GuiltyInUndelivery guiltyInUndeliveryAlias = null; var subqueryDrivers = QueryOver.Of <RouteListItem>(() => routeListItemAlias) .Where(() => routeListItemAlias.Order.Id == oldOrderAlias.Id) .Left.JoinQueryOver(i => i.RouteList, () => routeListAlias) .Left.JoinAlias(i => i.Driver, () => driverAlias) .Select( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(CONCAT(?1, ' ', LEFT(?2,1),'.',LEFT(?3,1)) ORDER BY ?4 DESC SEPARATOR '\n\t↑\n')"), //⬆ NHibernateUtil.String, Projections.Property(() => driverAlias.LastName), Projections.Property(() => driverAlias.Name), Projections.Property(() => driverAlias.Patronymic), Projections.Property(() => routeListItemAlias.Id) ) ); var subquery19LWaterQty = QueryOver.Of <OrderItem>(() => orderItemAlias) .Where(() => orderItemAlias.Order.Id == oldOrderAlias.Id) .Left.JoinQueryOver(i => i.Nomenclature, () => nomenclatureAlias) .Where(n => n.Category == NomenclatureCategory.water && n.TareVolume == TareVolume.Vol19L) .Select(Projections.Sum(() => orderItemAlias.Count)); var subqueryGoodsToClient = QueryOver.Of <OrderEquipment>(() => orderEquipmentAlias) .Where(() => orderEquipmentAlias.Order.Id == oldOrderAlias.Id) .Where(() => orderEquipmentAlias.Direction == Direction.Deliver) .Left.JoinQueryOver(i => i.Nomenclature, () => nomenclatureAlias) .Select( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "TRIM(GROUP_CONCAT(CONCAT(IF(?1 IS NULL, ?2, ?1),':',?3) SEPARATOR ?4))"), NHibernateUtil.String, Projections.Property(() => nomenclatureAlias.ShortName), Projections.Property(() => nomenclatureAlias.Name), Projections.Property(() => orderEquipmentAlias.Count), Projections.Constant("\n") ) ); var subqueryGoodsFromClient = QueryOver.Of <OrderEquipment>(() => orderEquipmentAlias) .Where(() => orderEquipmentAlias.Order.Id == oldOrderAlias.Id) .Where(() => orderEquipmentAlias.Direction == Direction.PickUp) .Left.JoinQueryOver(i => i.Nomenclature, () => nomenclatureAlias) .Select( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "TRIM(GROUP_CONCAT(CONCAT(IF(?1 IS NULL, ?2, ?1),':',?3) SEPARATOR ?4))"), NHibernateUtil.String, Projections.Property(() => nomenclatureAlias.ShortName), Projections.Property(() => nomenclatureAlias.Name), Projections.Property(() => orderEquipmentAlias.Count), Projections.Constant("\n") ) ); var subqueryGuilty = QueryOver.Of <GuiltyInUndelivery>(() => guiltyInUndeliveryAlias) .Where(() => undeliveredOrderAlias.Id == guiltyInUndeliveryAlias.UndeliveredOrder.Id) .Left.JoinQueryOver(g => g.GuiltyDepartment, () => subdivisionAlias) .Select( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(CONCAT(" + "CASE ?1 " + $"WHEN '{nameof(GuiltyTypes.Client)}' THEN 'Клиент' " + $"WHEN '{nameof(GuiltyTypes.Driver)}' THEN 'Водитель' " + $"WHEN '{nameof(GuiltyTypes.Department)}' THEN 'Отд' " + $"WHEN '{nameof(GuiltyTypes.ServiceMan)}' THEN 'Мастер СЦ' " + $"WHEN '{nameof(GuiltyTypes.ForceMajor)}' THEN 'Форс-мажор' " + $"WHEN '{nameof(GuiltyTypes.None)}' THEN 'Нет (не недовоз)' " + "ELSE 'Неизвестно' " + "END, " + "IF(?1 = 'Department' AND ?2 = '', ':Неизвестно', " + "IF(?1 = 'Department' AND ?2 != '', CONCAT(':', ?2), ''))) " + "SEPARATOR '\n')"), NHibernateUtil.String, Projections.Property(() => guiltyInUndeliveryAlias.GuiltySide), Projections.Property(() => subdivisionAlias.ShortName) ) ); var subqueryFined = QueryOver.Of <Fine>(() => fineAlias) .Where(() => fineAlias.UndeliveredOrder.Id == undeliveredOrderAlias.Id) .Left.JoinAlias(() => fineAlias.Items, () => fineItemAlias) .Left.JoinAlias(() => fineItemAlias.Employee, () => finedEmployeeAlias) .Select( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "GROUP_CONCAT(CONCAT_WS(': ', ?1, ?2) SEPARATOR '\n')"), NHibernateUtil.String, Projections.Property(() => finedEmployeeAlias.LastName), Projections.Property(() => fineItemAlias.Money) ) ); var query = uow.Session.QueryOver <UndeliveredOrder>(() => undeliveredOrderAlias) .Left.JoinAlias(u => u.OldOrder, () => oldOrderAlias) .Left.JoinAlias(u => u.NewOrder, () => newOrderAlias) .Left.JoinAlias(() => oldOrderAlias.Client, () => counterpartyAlias) .Left.JoinAlias(() => newOrderAlias.DeliverySchedule, () => newOrderDeliveryScheduleAlias) .Left.JoinAlias(() => oldOrderAlias.Author, () => oldOrderAuthorAlias) .Left.JoinAlias(() => oldOrderAlias.DeliveryPoint, () => undeliveredOrderDeliveryPointAlias) .Left.JoinAlias(() => oldOrderAlias.DeliverySchedule, () => undeliveredOrderDeliveryScheduleAlias) .Left.JoinAlias(u => u.Author, () => authorAlias) .Left.JoinAlias(u => u.LastEditor, () => editorAlias) .Left.JoinAlias(u => u.EmployeeRegistrator, () => registratorAlias) .Left.JoinAlias(u => u.InProcessAtDepartment, () => inProcessAtSubdivisionAlias) .Left.JoinAlias(u => u.Author.Subdivision, () => authorSubdivisionAlias) .Left.JoinAlias(() => undeliveredOrderAlias.GuiltyInUndelivery, () => guiltyInUndeliveryAlias) .Left.JoinAlias(() => guiltyInUndeliveryAlias.GuiltyDepartment, () => subdivisionAlias); if (FilterViewModel?.RestrictDriver != null) { var oldOrderIds = _undeliveredOrdersRepository.GetListOfUndeliveryIdsForDriver(UoW, FilterViewModel.RestrictDriver); query.Where(() => oldOrderAlias.Id.IsIn(oldOrderIds.ToArray())); } if (FilterViewModel?.RestrictOldOrder != null) { query.Where(() => oldOrderAlias.Id == FilterViewModel.RestrictOldOrder.Id); } if (FilterViewModel?.RestrictClient != null) { query.Where(() => counterpartyAlias.Id == FilterViewModel.RestrictClient.Id); } if (FilterViewModel?.RestrictAddress != null) { query.Where(() => undeliveredOrderDeliveryPointAlias.Id == FilterViewModel.RestrictAddress.Id); } if (FilterViewModel?.RestrictAuthorSubdivision != null) { query.Where(() => authorAlias.Subdivision.Id == FilterViewModel.RestrictAuthorSubdivision.Id); } if (FilterViewModel?.RestrictOldOrderAuthor != null) { query.Where(() => oldOrderAuthorAlias.Id == FilterViewModel.RestrictOldOrderAuthor.Id); } if (FilterViewModel?.RestrictOldOrderStartDate != null) { query.Where(() => oldOrderAlias.DeliveryDate >= FilterViewModel.RestrictOldOrderStartDate); } if (FilterViewModel?.RestrictOldOrderEndDate != null) { query.Where(() => oldOrderAlias.DeliveryDate <= FilterViewModel.RestrictOldOrderEndDate.Value.AddDays(1).AddTicks(-1)); } if (FilterViewModel?.RestrictNewOrderStartDate != null) { query.Where(() => newOrderAlias.DeliveryDate >= FilterViewModel.RestrictNewOrderStartDate); } if (FilterViewModel?.RestrictNewOrderEndDate != null) { query.Where(() => newOrderAlias.DeliveryDate <= FilterViewModel.RestrictNewOrderEndDate.Value.AddDays(1).AddTicks(-1)); } if (FilterViewModel?.RestrictGuiltySide != null) { query.Where(() => guiltyInUndeliveryAlias.GuiltySide == FilterViewModel.RestrictGuiltySide); } if (FilterViewModel != null && FilterViewModel.RestrictIsProblematicCases) { query.Where(() => !guiltyInUndeliveryAlias.GuiltySide.IsIn(FilterViewModel.ExcludingGuiltiesForProblematicCases)); } if (FilterViewModel?.RestrictGuiltyDepartment != null) { query.Where(() => subdivisionAlias.Id == FilterViewModel.RestrictGuiltyDepartment.Id); } if (FilterViewModel?.RestrictInProcessAtDepartment != null) { query.Where(u => u.InProcessAtDepartment.Id == FilterViewModel.RestrictInProcessAtDepartment.Id); } if (FilterViewModel?.NewInvoiceCreated != null) { if (FilterViewModel.NewInvoiceCreated.Value) { query.Where(u => u.NewOrder != null); } else { query.Where(u => u.NewOrder == null); } } if (FilterViewModel?.RestrictUndeliveryStatus != null) { query.Where(u => u.UndeliveryStatus == FilterViewModel.RestrictUndeliveryStatus); } if (FilterViewModel?.RestrictUndeliveryAuthor != null) { query.Where(u => u.Author == FilterViewModel.RestrictUndeliveryAuthor); } var addressProjection = Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.String, "CONCAT_WS(', ', ?1, CONCAT('д.', ?2), CONCAT('лит.', ?3), CONCAT('кв/оф ', ?4))"), NHibernateUtil.String, Projections.Property(() => undeliveredOrderDeliveryPointAlias.Street), Projections.Property(() => undeliveredOrderDeliveryPointAlias.Building), Projections.Property(() => undeliveredOrderDeliveryPointAlias.Letter), Projections.Property(() => undeliveredOrderDeliveryPointAlias.Room)); var oldOrderAuthorProjection = CustomProjections.Concat_WS(" ", () => oldOrderAuthorAlias.LastName, () => oldOrderAuthorAlias.Name, () => oldOrderAuthorAlias.Patronymic); var registratorProjection = CustomProjections.Concat_WS(" ", () => registratorAlias.LastName, () => registratorAlias.Name, () => registratorAlias.Patronymic); var authorProjection = CustomProjections.Concat_WS(" ", () => authorAlias.LastName, () => authorAlias.Name, () => authorAlias.Patronymic); query.Where(GetSearchCriterion( () => undeliveredOrderAlias.Id, () => addressProjection, () => counterpartyAlias.Name, () => undeliveredOrderAlias.Reason, () => oldOrderAuthorProjection, () => registratorProjection, () => authorProjection) ); var itemsQuery = query.SelectList(list => list .SelectGroup(() => undeliveredOrderAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => newOrderAlias.Id).WithAlias(() => resultAlias.NewOrderId) .Select(() => newOrderAlias.DeliveryDate).WithAlias(() => resultAlias.NewOrderDeliveryDate) .Select(() => newOrderDeliveryScheduleAlias.Name).WithAlias(() => resultAlias.NewOrderDeliverySchedule) .Select(() => oldOrderAlias.Id).WithAlias(() => resultAlias.OldOrderId) .Select(() => oldOrderAlias.DeliveryDate).WithAlias(() => resultAlias.OldOrderDeliveryDateTime) .Select(() => undeliveredOrderAlias.DispatcherCallTime).WithAlias(() => resultAlias.DispatcherCallTime) .Select(() => undeliveredOrderAlias.DriverCallNr).WithAlias(() => resultAlias.DriverCallNr) .Select(() => undeliveredOrderAlias.DriverCallTime).WithAlias(() => resultAlias.DriverCallTime) .Select(() => undeliveredOrderAlias.DriverCallType).WithAlias(() => resultAlias.DriverCallType) .Select(() => counterpartyAlias.Name).WithAlias(() => resultAlias.Client) .Select(() => oldOrderAuthorAlias.LastName).WithAlias(() => resultAlias.OldOrderAuthorLastName) .Select(() => oldOrderAuthorAlias.Name).WithAlias(() => resultAlias.OldOrderAuthorFirstName) .Select(() => oldOrderAuthorAlias.Patronymic).WithAlias(() => resultAlias.OldOrderAuthorMiddleName) .Select(() => undeliveredOrderDeliveryScheduleAlias.Name).WithAlias(() => resultAlias.OldDeliverySchedule) .Select(() => authorAlias.LastName).WithAlias(() => resultAlias.AuthorLastName) .Select(() => authorAlias.Name).WithAlias(() => resultAlias.AuthorFirstName) .Select(() => authorAlias.Patronymic).WithAlias(() => resultAlias.AuthorMiddleName) .Select(() => registratorAlias.LastName).WithAlias(() => resultAlias.RegistratorLastName) .Select(() => registratorAlias.Name).WithAlias(() => resultAlias.RegistratorFirstName) .Select(() => registratorAlias.Patronymic).WithAlias(() => resultAlias.RegistratorMiddleName) .Select(() => editorAlias.LastName).WithAlias(() => resultAlias.EditorLastName) .Select(() => editorAlias.Name).WithAlias(() => resultAlias.EditorFirstName) .Select(() => editorAlias.Patronymic).WithAlias(() => resultAlias.EditorMiddleName) .Select(() => undeliveredOrderAlias.Reason).WithAlias(() => resultAlias.Reason) .Select(() => undeliveredOrderAlias.UndeliveryStatus).WithAlias(() => resultAlias.UndeliveryStatus) .Select(() => undeliveredOrderAlias.OldOrderStatus).WithAlias(() => resultAlias.StatusOnOldOrderCancel) .Select(() => oldOrderAlias.OrderStatus).WithAlias(() => resultAlias.OldOrderCurStatus) .Select(() => inProcessAtSubdivisionAlias.Name).WithAlias(() => resultAlias.InProcessAt) .SelectSubQuery(subqueryDrivers).WithAlias(() => resultAlias.OldRouteListDriverName) .SelectSubQuery(subquery19LWaterQty).WithAlias(() => resultAlias.OldOrder19LBottleQty) .SelectSubQuery(subqueryGoodsToClient).WithAlias(() => resultAlias.OldOrderGoodsToClient) .SelectSubQuery(subqueryGoodsFromClient).WithAlias(() => resultAlias.OldOrderGoodsFromClient) .SelectSubQuery(subqueryFined).WithAlias(() => resultAlias.Fined) .SelectSubQuery(subqueryGuilty).WithAlias(() => resultAlias.Guilty) .Select(addressProjection).WithAlias(() => resultAlias.Address) ).OrderBy(() => oldOrderAlias.DeliveryDate).Asc .TransformUsing(Transformers.AliasToBean <UndeliveredOrderJournalNode>()); return(itemsQuery); }