private IQueryOver <Premium> GetPremiumsQuery(IUnitOfWork uow) { PremiumJournalNode resultAlias = null; Premium premiumAlias = null; PremiumItem premiumItemAlias = null; Employee employeeAlias = null; var query = uow.Session.QueryOver <Premium>(() => premiumAlias) .JoinAlias(f => f.Items, () => premiumItemAlias) .JoinAlias(() => premiumItemAlias.Employee, () => employeeAlias); if (FilterViewModel.Subdivision != null) { query.Where(() => employeeAlias.Subdivision.Id == FilterViewModel.Subdivision.Id); } if (FilterViewModel.StartDate.HasValue) { query.Where(() => premiumAlias.Date >= FilterViewModel.StartDate.Value); } if (FilterViewModel.EndDate.HasValue) { query.Where(() => premiumAlias.Date <= FilterViewModel.EndDate.Value); } var employeeProjection = CustomProjections.Concat_WS( " ", () => employeeAlias.LastName, () => employeeAlias.Name, () => employeeAlias.Patronymic ); query.Where( GetSearchCriterion( () => premiumAlias.Id, () => premiumAlias.PremiumReasonString, () => employeeProjection, () => premiumAlias.TotalMoney ) ); var resultQuery = query .SelectList(list => list .SelectGroup(() => premiumAlias.Id).WithAlias(() => resultAlias.Id) .Select(() => premiumAlias.Date).WithAlias(() => resultAlias.Date) .Select(CustomProjections.GroupConcat( employeeProjection, false, employeeProjection, OrderByDirection.Asc, "\n") ).WithAlias(() => resultAlias.EmployeesName) .Select(() => premiumAlias.PremiumReasonString).WithAlias(() => resultAlias.PremiumReason) .Select(() => premiumAlias.TotalMoney).WithAlias(() => resultAlias.PremiumSum) ).OrderBy(o => o.Date).Desc .TransformUsing(Transformers.AliasToBean <PremiumJournalNode <Premium> >()); return(resultQuery); }
public IProjection GroupAndConcatDia() { return(CustomProjections.SqlGroupAndConcatFunction("DAY({alias}.Data) as DIA", "DAY({alias}.Data)", new[] { "DIA" }, new[] { NHibernateUtil.String }, "/", GroupAndConcatMensal())); }
public IProjection GroupAndConcatTrimestral() { return(CustomProjections.SqlGroupAndConcatFunction("QUARTER({alias}.Data) as TRIMESTRE", "QUARTER({alias}.Data)", new[] { "TRIMESTRE" }, new[] { NHibernateUtil.String }, "º Trimestre de ", GroupAndConcatAnual())); }
public IProjection GroupAndConcatMensal() { return(CustomProjections.SqlGroupAndConcatFunction("MONTH({alias}.Data) as MES", "MONTH({alias}.Data)", new[] { "MES" }, new[] { NHibernateUtil.String }, "/", GroupAndConcatAnual())); }
public IProjection GroupAndConcatSemanal() { return(CustomProjections.SqlGroupAndConcatFunction("WEEK({alias}.Data) as SEMANA", "WEEK({alias}.Data)", new[] { "SEMANA" }, new[] { NHibernateUtil.String }, "ª Semana do Ano de ", GroupAndConcatAnual())); }
private IProjection GroupAndConcatSemestral() { return(CustomProjections.Concat(Projections.Conditional(Restrictions.Ge(Projections.SqlFunction("MONTH", NHibernateUtil.Int16, Projections.Property("Data")), 7), Projections.Constant("2º Semestre de ", NHibernateUtil.String), Projections.Constant("1º Semestre de ", NHibernateUtil.String)), GroupAndConcatAnual())); }
protected override IQueryOver <EmployeeCard> ItemsQuery(IUnitOfWork uow) { EmployeeProcessingJournalNode resultAlias = null; Post postAlias = null; Subdivision subdivisionAlias = null; EmployeeCard employeeAlias = null; Norm normAlias = null; var employees = uow.Session.QueryOver <EmployeeCard>(() => employeeAlias); if (Filter.ShowOnlyWork) { employees.Where(x => x.DismissDate == null); } if (Filter.Subdivision != null) { employees.Where(x => x.Subdivision.Id == Filter.Subdivision.Id); } if (Filter.Department != null) { employees.Where(x => x.Department.Id == Filter.Department.Id); } var normProjection = CustomProjections.GroupConcat(Projections.SqlFunction("coalesce", NHibernateUtil.String, Projections.Property(() => normAlias.Name), Projections.Property(() => normAlias.Id)), separator: "\n"); return(employees .Where(GetSearchCriterion( () => employeeAlias.Id, () => employeeAlias.CardNumber, () => employeeAlias.PersonnelNumber, () => employeeAlias.LastName, () => employeeAlias.FirstName, () => employeeAlias.Patronymic, () => postAlias.Name, () => subdivisionAlias.Name )) .JoinAlias(() => employeeAlias.Post, () => postAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin) .JoinAlias(() => employeeAlias.Subdivision, () => subdivisionAlias, NHibernate.SqlCommand.JoinType.LeftOuterJoin) .Left.JoinAlias(() => employeeAlias.UsedNorms, () => normAlias) .SelectList((list) => list .SelectGroup(x => x.Id).WithAlias(() => resultAlias.Id) .Select(x => x.CardNumber).WithAlias(() => resultAlias.CardNumber) .Select(x => x.PersonnelNumber).WithAlias(() => resultAlias.PersonnelNumber) .Select(x => x.FirstName).WithAlias(() => resultAlias.FirstName) .Select(x => x.LastName).WithAlias(() => resultAlias.LastName) .Select(x => x.Patronymic).WithAlias(() => resultAlias.Patronymic) .Select(() => employeeAlias.DismissDate).WithAlias(() => resultAlias.DismissDate) .Select(() => postAlias.Name).WithAlias(() => resultAlias.Post) .Select(() => subdivisionAlias.Name).WithAlias(() => resultAlias.Subdivision) .Select(normProjection).WithAlias(() => resultAlias.Norms) ) .OrderBy(() => employeeAlias.LastName).Asc .ThenBy(() => employeeAlias.FirstName).Asc .ThenBy(() => employeeAlias.Patronymic).Asc .TransformUsing(Transformers.AliasToBean <EmployeeProcessingJournalNode>())); }
protected override void RegisterFunctions() { base.RegisterFunctions(); RegisterFunction("contains", new StandardSQLFunction("contains", null)); RegisterFunction(RandomOrder, new StandardSQLFunction("NEWID", NHibernateUtil.Guid)); //RegisterFunction("NEWID()", new StandardSQLFunction("NEWID()", NHibernateUtil.Guid)); CustomProjections.Register(); }
protected override IQueryOver <IssuanceSheet> ItemsQuery(IUnitOfWork uow) { IssuanceSheetJournalNode resultAlias = null; IssuanceSheet issuanceSheetAlias = null; IssuanceSheetItem issuanceSheetItemAlias = null; Organization organizationAlias = null; Subdivision subdivisionAlias = null; EmployeeCard employeeCardAlias = null; var employeesSubquery = QueryOver.Of <IssuanceSheetItem>(() => issuanceSheetItemAlias) .Where(() => issuanceSheetItemAlias.IssuanceSheet.Id == issuanceSheetAlias.Id) .JoinQueryOver(x => x.Employee, () => employeeCardAlias) .Select(CustomProjections .GroupConcat(Projections .Property(() => employeeCardAlias.LastName), useDistinct: true, separator: ", ")); var query = uow.Session.QueryOver <IssuanceSheet>(() => issuanceSheetAlias); if (Filter.StartDate != null) { query.Where(x => x.Date >= Filter.StartDate); } if (Filter.EndDate != null) { query.Where(x => x.Date <= Filter.EndDate); } return(query .Where(GetSearchCriterion( () => issuanceSheetAlias.Id, () => issuanceSheetAlias.Expense.Id, () => issuanceSheetAlias.CollectiveExpense.Id, () => organizationAlias.Name, () => subdivisionAlias.Name, () => subdivisionAlias.Code )) .Left.JoinAlias(s => s.Organization, () => organizationAlias) .Left.JoinAlias(s => s.Subdivision, () => subdivisionAlias) .SelectList((list) => list .Select(x => x.Id).WithAlias(() => resultAlias.Id) .Select(x => x.Date).WithAlias(() => resultAlias.Date) .Select(() => organizationAlias.Name).WithAlias(() => resultAlias.Organigation) .Select(() => subdivisionAlias.Name).WithAlias(() => resultAlias.Subdivision) .Select(() => subdivisionAlias.Code).WithAlias(() => resultAlias.SubdivisionCode) .Select(x => x.Expense.Id).WithAlias(() => resultAlias.DocExpenseId) .Select(x => x.CollectiveExpense.Id).WithAlias(() => resultAlias.DocCollectiveExpenseId) .SelectSubQuery(employeesSubquery).WithAlias(() => resultAlias.Employees) ) .OrderBy(() => issuanceSheetAlias.Date).Desc .ThenBy(() => issuanceSheetAlias.Id).Desc .TransformUsing(Transformers.AliasToBean <IssuanceSheetJournalNode>())); }
private void ConfigureFilter() { var subdivisionsFilter = _filter.CreateParameterSet( "Подразделения", "subdivision", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <Subdivision> resultAlias = null; var query = UoW.Session.QueryOver <Subdivision>(); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.Name).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <Subdivision> >()); return(query.List <SelectableParameter>()); }) ); var orderAuthorsFilter = _filter.CreateParameterSet( "Авторы заказов", "order_author", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <Employee> resultAlias = null; var query = UoW.Session.QueryOver <Employee>(); if (filters != null && filters.Any()) { foreach (var f in filters) { var criterion = f(); if (criterion != null) { query.Where(criterion); } } } var authorProjection = CustomProjections.Concat_WS( " ", Projections.Property <Employee>(x => x.LastName), Projections.Property <Employee>(x => x.Name), Projections.Property <Employee>(x => x.Patronymic) ); query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(authorProjection).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <Employee> >()); var paremetersSet = query.List <SelectableParameter>(); return(paremetersSet); }) ); orderAuthorsFilter.AddFilterOnSourceSelectionChanged(subdivisionsFilter, () => { var selectedValues = subdivisionsFilter.GetSelectedValues().ToArray(); return(!selectedValues.Any() ? null : subdivisionsFilter.FilterType == SelectableFilterType.Include ? Restrictions.On <Employee>(x => x.Subdivision).IsIn(selectedValues) : Restrictions.On <Employee>(x => x.Subdivision).Not.IsIn(selectedValues)); } ); var viewModel = new SelectableParameterReportFilterViewModel(_filter); var filterWidget = new SelectableParameterReportFilterView(viewModel); vboxMultiParameters.Add(filterWidget); filterWidget.Show(); }
protected override IQueryOver <Subdivision> ItemsQuery(IUnitOfWork uow) { SubdivisionJournalNode resultAlias = null; Subdivision parent1SubdivisionAlias = null; Subdivision parent2SubdivisionAlias = null; Subdivision parent3SubdivisionAlias = null; Subdivision parent4SubdivisionAlias = null; Subdivision parent5SubdivisionAlias = null; Subdivision parent6SubdivisionAlias = null; Subdivision subdivisionAlias = null; var orderProjection = CustomProjections.Concat_WS("", () => parent6SubdivisionAlias.Name, () => parent5SubdivisionAlias.Name, () => parent4SubdivisionAlias.Name, () => parent3SubdivisionAlias.Name, () => parent2SubdivisionAlias.Name, () => parent1SubdivisionAlias.Name, () => subdivisionAlias.Name); var query = uow.Session.QueryOver(() => subdivisionAlias) .Where(GetSearchCriterion <Subdivision>( x => x.Code, x => x.Name, x => x.Address )) .JoinAlias(() => subdivisionAlias.ParentSubdivision, () => parent1SubdivisionAlias, JoinType.LeftOuterJoin) .JoinAlias(() => parent1SubdivisionAlias.ParentSubdivision, () => parent2SubdivisionAlias, JoinType.LeftOuterJoin) .JoinAlias(() => parent2SubdivisionAlias.ParentSubdivision, () => parent3SubdivisionAlias, JoinType.LeftOuterJoin) .JoinAlias(() => parent3SubdivisionAlias.ParentSubdivision, () => parent4SubdivisionAlias, JoinType.LeftOuterJoin) .JoinAlias(() => parent4SubdivisionAlias.ParentSubdivision, () => parent5SubdivisionAlias, JoinType.LeftOuterJoin) .JoinAlias(() => parent5SubdivisionAlias.ParentSubdivision, () => parent6SubdivisionAlias, JoinType.LeftOuterJoin); if (Filter.SortByParent) { return(query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.Id) .Select(x => x.Code).WithAlias(() => resultAlias.Code) .Select(x => x.Name).WithAlias(() => resultAlias.Name) .Select(x => x.Address).WithAlias(() => resultAlias.Address) .Select(() => parent1SubdivisionAlias.Id).WithAlias(() => resultAlias.Parent1Id) .Select(() => parent2SubdivisionAlias.Id).WithAlias(() => resultAlias.Parent2Id) .Select(() => parent3SubdivisionAlias.Id).WithAlias(() => resultAlias.Parent3Id) .Select(() => parent4SubdivisionAlias.Id).WithAlias(() => resultAlias.Parent4Id) .Select(() => parent5SubdivisionAlias.Id).WithAlias(() => resultAlias.Parent5Id) .Select(() => parent6SubdivisionAlias.Id).WithAlias(() => resultAlias.Parent6Id) ).OrderBy(orderProjection).Asc .TransformUsing(Transformers.AliasToBean <SubdivisionJournalNode>())); } else { return(query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.Id) .Select(x => x.Code).WithAlias(() => resultAlias.Code) .Select(x => x.Name).WithAlias(() => resultAlias.Name) .Select(x => x.Address).WithAlias(() => resultAlias.Address) .Select(() => parent1SubdivisionAlias.Name).WithAlias(() => resultAlias.ParentName) ).OrderBy(x => x.Name).Asc .TransformUsing(Transformers.AliasToBean <SubdivisionJournalNode>())); } }
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); }
public IEnumerable <ReceiptForOrderNode> GetOrdersForCashReceiptServiceToSend( IUnitOfWork uow, IOrderParametersProvider orderParametersProvider, IOrganizationParametersProvider organizationParametersProvider, ISalesReceiptsParametersProvider salesReceiptsParametersProvider, DateTime?startDate = null) { #region Aliases Restrictions Projections var paymentByCardFromNotToSendSalesReceipts = orderParametersProvider.PaymentsByCardFromNotToSendSalesReceipts; var vodovozSouthOrganizationId = organizationParametersProvider.VodovozSouthOrganizationId; ExtendedReceiptForOrderNode extendedReceiptForOrderNodeAlias = null; OrderItem orderItemAlias = null; VodovozOrder orderAlias = null; CashReceipt cashReceiptAlias = null; Nomenclature nomenclatureAlias = null; ProductGroup productGroupAlias = null; Counterparty counterpartyAlias = null; CounterpartyContract counterpartyContractAlias = null; Organization organizationAlias = null; var orderSumProjection = Projections.Sum( Projections.SqlFunction( new SQLFunctionTemplate(NHibernateUtil.Decimal, "CAST(IFNULL(?1 * ?2 - ?3, 0) AS DECIMAL(14,2))"), NHibernateUtil.Decimal, Projections.Property(() => orderItemAlias.Count), Projections.Property(() => orderItemAlias.Price), Projections.Property(() => orderItemAlias.DiscountMoney) ) ); var orderSumRestriction = Restrictions.Gt(orderSumProjection, 0); var alwaysSendOrdersRestriction = Restrictions.Disjunction() .Add(() => productGroupAlias.OnlineStore != null) .Add(() => counterpartyAlias.AlwaysSendReceitps) .Add(() => orderAlias.SelfDelivery) .Add(Restrictions.In(Projections.Property(() => orderAlias.PaymentType), new[] { PaymentType.ByCard, PaymentType.Terminal }.ToArray())); var orderDeliveredStatuses = Restrictions.In(Projections.Property(() => orderAlias.OrderStatus), new[] { OrderStatus.Shipped, OrderStatus.UnloadingOnStock }.ToArray()); var orderPaymentTypesRestriction = Restrictions.In(Projections.Property(() => orderAlias.PaymentType), new[] { PaymentType.cash, PaymentType.Terminal, PaymentType.ByCard }.ToArray()); var paidByCardRestriction = Restrictions.Disjunction() .Add(() => orderAlias.PaymentType != PaymentType.ByCard) .Add(() => organizationAlias.Id != vodovozSouthOrganizationId) .Add(Restrictions.On(() => orderAlias.PaymentByCardFrom.Id) .Not.IsIn(paymentByCardFromNotToSendSalesReceipts)); #endregion #region AlwaysSendOrders var alwaysSendOrdersQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias) .JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin) .Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias) .Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias) .Left.JoinAlias(() => nomenclatureAlias.ProductGroup, () => productGroupAlias) .Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias) .Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias) .Left.JoinAlias(() => counterpartyContractAlias.Organization, () => organizationAlias) .Where(alwaysSendOrdersRestriction) .And(paidByCardRestriction) .And(Restrictions.Disjunction() .Add(orderDeliveredStatuses) .Add(Restrictions.Conjunction() .Add(() => orderAlias.SelfDelivery) .Add(() => orderAlias.IsSelfDeliveryPaid))) .And(orderSumRestriction) .And(orderPaymentTypesRestriction) .And(Restrictions.Disjunction() .Add(Restrictions.IsNull(Projections.Property(() => cashReceiptAlias.Id))) .Add(() => !cashReceiptAlias.Sent)); if (startDate.HasValue) { alwaysSendOrdersQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value); } var alwaysSendOrders = alwaysSendOrdersQuery .SelectList(list => list .SelectGroup(() => orderAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderId) .Select(() => orderAlias.PaymentType).WithAlias(() => extendedReceiptForOrderNodeAlias.PaymentType) .Select(orderSumProjection).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderSum) .Select(() => cashReceiptAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.ReceiptId) .Select(() => cashReceiptAlias.Sent).WithAlias(() => extendedReceiptForOrderNodeAlias.WasSent)) .TransformUsing(Transformers.AliasToBean <ExtendedReceiptForOrderNode>()) .Future <ExtendedReceiptForOrderNode>(); //Здесь фильтрация идёт не на уровне запроса, т.к. не NHibernate упорно не хочет клась сложное условие в HAVING var result = alwaysSendOrders .Where(x => x.PaymentType != PaymentType.cash || x.PaymentType == PaymentType.cash && x.OrderSum < 20000) .Select(x => new ReceiptForOrderNode { OrderId = x.OrderId, ReceiptId = x.ReceiptId, WasSent = x.WasSent }); #endregion #region UniqueOrderSumOrders if (salesReceiptsParametersProvider.SendUniqueOrderSumOrders) { var uniqueOrderSumSendOrdersQuery = uow.Session.QueryOver <VodovozOrder>(() => orderAlias) .JoinEntityAlias(() => cashReceiptAlias, () => cashReceiptAlias.Order.Id == orderAlias.Id, JoinType.LeftOuterJoin) .Left.JoinAlias(() => orderAlias.OrderItems, () => orderItemAlias) .Left.JoinAlias(() => orderItemAlias.Nomenclature, () => nomenclatureAlias) .Left.JoinAlias(() => nomenclatureAlias.ProductGroup, () => productGroupAlias) .Left.JoinAlias(() => orderAlias.Client, () => counterpartyAlias) .Left.JoinAlias(() => orderAlias.Contract, () => counterpartyContractAlias) .Left.JoinAlias(() => counterpartyContractAlias.Organization, () => organizationAlias) .Where(Restrictions.Not(alwaysSendOrdersRestriction)) .And(paidByCardRestriction) .And(orderDeliveredStatuses) .And(orderSumRestriction) .And(orderPaymentTypesRestriction); if (startDate.HasValue) { uniqueOrderSumSendOrdersQuery.Where(() => orderAlias.DeliveryDate >= startDate.Value); } var notUniqueOrderSumSendOrdersTemp = uniqueOrderSumSendOrdersQuery .SelectList(list => list .SelectGroup(() => orderAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderId) .Select(() => orderAlias.PaymentType).WithAlias(() => extendedReceiptForOrderNodeAlias.PaymentType) .Select(orderSumProjection).WithAlias(() => extendedReceiptForOrderNodeAlias.OrderSum) .Select(CustomProjections.Date(() => orderAlias.DeliveryDate)) .WithAlias(() => extendedReceiptForOrderNodeAlias.DeliveryDate) .Select(() => cashReceiptAlias.Id).WithAlias(() => extendedReceiptForOrderNodeAlias.ReceiptId) .Select(() => cashReceiptAlias.Sent).WithAlias(() => extendedReceiptForOrderNodeAlias.WasSent)) .TransformUsing(Transformers.AliasToBean <ExtendedReceiptForOrderNode>()) .Future <ExtendedReceiptForOrderNode>(); var notUniqueOrderSumSendOrders = notUniqueOrderSumSendOrdersTemp.Where(x => x.PaymentType != PaymentType.cash || x.PaymentType == PaymentType.cash && x.OrderSum < 20000).ToList(); var alreadySentOrders = new List <ExtendedReceiptForOrderNode>(notUniqueOrderSumSendOrders.Where(x => x.WasSent.HasValue && x.WasSent.Value)); var uniqueOrderSumSendNodes = new List <ExtendedReceiptForOrderNode>(); foreach (var node in notUniqueOrderSumSendOrders.Where(x => !x.WasSent.HasValue || !x.WasSent.Value)) { if (alreadySentOrders.All(x => x.OrderSum != node.OrderSum || x.DeliveryDate != node.DeliveryDate) && uniqueOrderSumSendNodes.All(x => x.OrderSum != node.OrderSum || x.DeliveryDate != node.DeliveryDate)) { uniqueOrderSumSendNodes.Add(node); } } var uniqueOrderSumSendOrderNodes = uniqueOrderSumSendNodes.Select(x => new ReceiptForOrderNode { OrderId = x.OrderId, ReceiptId = x.ReceiptId, WasSent = x.WasSent }); result = result.Union(uniqueOrderSumSendOrderNodes); } #endregion return(result); }
private void SetupFilter() { var nomenclatureTypeParam = _filter.CreateParameterSet( "Типы номенклатур", "nomenclature_type", new ParametersEnumFactory <NomenclatureCategory>() ); var nomenclatureParam = _filter.CreateParameterSet( "Номенклатуры", "nomenclature", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <Nomenclature> resultAlias = null; var query = UoW.Session.QueryOver <Nomenclature>() .Where(x => !x.IsArchive); if (filters != null && filters.Any()) { foreach (var f in filters) { var filterCriterion = f(); if (filterCriterion != null) { query.Where(filterCriterion); } } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.OfficialName).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <Nomenclature> >()); return(query.List <SelectableParameter>()); }) ); nomenclatureParam.AddFilterOnSourceSelectionChanged(nomenclatureTypeParam, () => { var selectedValues = nomenclatureTypeParam.GetSelectedValues().ToArray(); return(!selectedValues.Any() ? null : nomenclatureTypeParam.FilterType == SelectableFilterType.Include ? Restrictions.On <Nomenclature>(x => x.Category).IsIn(selectedValues) : Restrictions.On <Nomenclature>(x => x.Category).Not.IsIn(selectedValues)); } ); //Предзагрузка. Для избежания ленивой загрузки UoW.Session.QueryOver <ProductGroup>().Fetch(SelectMode.Fetch, x => x.Childs).List(); _filter.CreateParameterSet( "Группы товаров", "product_group", new RecursiveParametersFactory <ProductGroup>(UoW, (filters) => { var query = UoW.Session.QueryOver <ProductGroup>(); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } return(query.List()); }, x => x.Name, x => x.Childs) ); _filter.CreateParameterSet( "Контрагенты", "counterparty", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <Counterparty> resultAlias = null; var query = UoW.Session.QueryOver <Counterparty>() .Where(x => !x.IsArchive); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.FullName).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <Counterparty> >()); return(query.List <SelectableParameter>()); }) ); _filter.CreateParameterSet( "Организации", "organization", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <Organization> resultAlias = null; var query = UoW.Session.QueryOver <Organization>(); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.FullName).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <Organization> >()); return(query.List <SelectableParameter>()); }) ); _filter.CreateParameterSet( "Основания скидок", "discount_reason", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <DiscountReason> resultAlias = null; var query = UoW.Session.QueryOver <DiscountReason>(); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.Name).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <DiscountReason> >()); return(query.List <SelectableParameter>()); }) ); _filter.CreateParameterSet( "Подразделения", "subdivision", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <Subdivision> resultAlias = null; var query = UoW.Session.QueryOver <Subdivision>(); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.Name).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <Subdivision> >()); return(query.List <SelectableParameter>()); }) ); if (!_userIsSalesRepresentative) { _filter.CreateParameterSet( "Авторы заказов", "order_author", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <Employee> resultAlias = null; var query = UoW.Session.QueryOver <Employee>(); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } var authorProjection = CustomProjections.Concat_WS( " ", Projections.Property <Employee>(x => x.LastName), Projections.Property <Employee>(x => x.Name), Projections.Property <Employee>(x => x.Patronymic) ); query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(authorProjection).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <Employee> >()); var paremetersSet = query.List <SelectableParameter>(); return(paremetersSet); }) ); } _filter.CreateParameterSet( "Части города", "geographic_group", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <GeographicGroup> resultAlias = null; var query = UoW.Session.QueryOver <GeographicGroup>(); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.Name).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <GeographicGroup> >()); return(query.List <SelectableParameter>()); }) ); _filter.CreateParameterSet( "Тип оплаты", "payment_type", new ParametersEnumFactory <PaymentType>() ); _filter.CreateParameterSet( "Промонаборы", "promotional_set", new ParametersFactory(UoW, (filters) => { SelectableEntityParameter <PromotionalSet> resultAlias = null; var query = UoW.Session.QueryOver <PromotionalSet>() .Where(x => !x.IsArchive); if (filters != null && filters.Any()) { foreach (var f in filters) { query.Where(f()); } } query.SelectList(list => list .Select(x => x.Id).WithAlias(() => resultAlias.EntityId) .Select(x => x.Name).WithAlias(() => resultAlias.EntityTitle) ); query.TransformUsing(Transformers.AliasToBean <SelectableEntityParameter <PromotionalSet> >()); return(query.List <SelectableParameter>()); }) ); var viewModel = new SelectableParameterReportFilterViewModel(_filter); var filterWidget = new SelectableParameterReportFilterView(viewModel); vboxParameters.Add(filterWidget); filterWidget.Show(); }