protected override IQueryOver <Desk, Desk> DecorateCriteria(IQueryOver <Desk, Desk> queryOver) { DeskDocumentEndorsement deskDocumentEndorsement = null; DeskRoleUser deskRoleUser = null; DeskDocumentVersion deskDocumentVersion = null; DeskDocument deskDocument = null; queryOver.JoinAlias(o => o.DeskDocuments, () => deskDocument) .JoinAlias(() => deskDocument.DeskDocumentVersions, () => deskDocumentVersion) .JoinAlias(o => o.DeskRoleUsers, () => deskRoleUser) .Left.JoinAlias(() => deskRoleUser.DeskDocumentEndorsements, () => deskDocumentEndorsement, () => deskDocumentEndorsement.DeskDocumentVersion.Id == deskDocumentVersion.Id); if (DeskId.HasValue) { queryOver.Where(desk => desk.Id == DeskId.Value); } FilterByUserPermission(queryOver); queryOver.SelectList(select => select.SelectGroup(() => deskDocument.Id).SelectMax(() => deskDocumentVersion.Version)); if (!VersionFilters.Any()) { return(queryOver); } foreach (KeyValuePair <Guid, decimal> versionFilter in VersionFilters) { queryOver.Where(() => deskDocument.Id == versionFilter.Key) .And(() => deskDocumentVersion.Version == versionFilter.Value); } return(queryOver); }
/// <summary> /// Mappo gli oggetti di DeskDocumentEndorsement su DeskEndorsement. /// <see cref="IsApprove">IsApprove viene settato intero poichè in una PivotGrid ho la necessità di aggregare questa informazione</see> /// </summary> /// <param name="queryOver"></param> /// <returns></returns> protected override IQueryOver <Role, Role> MappingProjection(IQueryOver <Role, Role> queryOver) { CategoryUserProcedure categoryUserProcedure = null; RoleUser roleUser = null; queryOver .SelectList(list => list .SelectGroup(s => s.Name).WithAlias(() => categoryUserProcedure.RoleName) .SelectGroup(() => roleUser.Description).WithAlias(() => categoryUserProcedure.ProcedureUserName)); return(queryOver); }
public IList <Tuple <TKey, TResult, TResult> > WhereMinMax <TKey, TResult>(Expression <Func <T, object> > min, Expression <Func <T, object> > max, Expression <Func <T, bool> > where, Expression <Func <T, object> > groupBy) { return(_query.SelectList(x => x.SelectMin(min).SelectMax(max).SelectGroup(groupBy)) .Where(where) .List <object[]>() .Select(m => new Tuple <TKey, TResult, TResult>((TKey)m[2], (TResult)m[0], (TResult)m[1])).ToList()); }
public List <ProductOptionSortingData> Search(ProductOptionSortingSearchQuery searchQuery) { ProductOption productOptionAlias = null; IQueryOver <ProductOption, ProductOption> queryOver = _session.QueryOver(() => productOptionAlias); ProductOptionSortingData sortingDataAlias = null; queryOver.SelectList(builder => builder .Select(() => productOptionAlias.Id).WithAlias(() => sortingDataAlias.Id) .Select(() => productOptionAlias.Name).WithAlias(() => sortingDataAlias.Name) ); return(queryOver.TransformUsing(Transformers.AliasToBean <ProductOptionSortingData>()) .List <ProductOptionSortingData>().ToList()); }
protected override IQueryOver <DeskStoryBoard, DeskStoryBoard> MappingProjection(IQueryOver <DeskStoryBoard, DeskStoryBoard> queryOver) { DeskComment deskComment = null; DeskDocument deskDocument = null; queryOver .SelectList(list => list .Select(x => x.Id).WithAlias(() => deskComment.DeskCommentId) .Select(x => x.Comment).WithAlias(() => deskComment.Description) .Select(x => x.Author).WithAlias(() => deskComment.Author) .Select(x => x.DateBoard).WithAlias(() => deskComment.CreationDate) .Select(x => x.BoardType).WithAlias(() => deskComment.CommentType) .Select(x => x.Desk.Id).WithAlias(() => deskComment.DeskId) .Select(() => deskDocument.IdDocument).WithAlias(() => deskComment.IdDocument)); return(queryOver); }
public IList <UserNotification> GetNotifications() { User user = CurrentRequestData.CurrentUser; IQueryOver <UserNotification, UserNotification> queryOver = _session.QueryOver <UserNotification>(); if (user == null) { return(new List <UserNotification>()); } if (user.LastNotificationReadDate.HasValue) { queryOver = queryOver.Where( notification => notification.CreatedOn >= user.LastNotificationReadDate && notification.UserId == user.Guid.ToString()); } UserNotification notificationModelAlias = null; IList <UserNotification> response = queryOver.SelectList( builder => builder.Select(notification => notification.Message) .WithAlias(() => notificationModelAlias.Message) .Select(notification => notification.UserId) .WithAlias(() => notificationModelAlias.UserId) .Select(notification => notification.ClickAction) .WithAlias(() => notificationModelAlias.ClickAction) .Select(notification => notification.Type) .WithAlias(() => notificationModelAlias.Type) .Select(notification => notification.Target) .WithAlias(() => notificationModelAlias.Target) .Select(notification => notification.CreatedOn) .WithAlias(() => notificationModelAlias.CreatedOn)).Where(notification => notification.UserId == user.Guid.ToString()) .OrderBy(notification => notification.CreatedOn).Desc .TransformUsing(Transformers.AliasToBean <UserNotification>()) .Take(15) .List <UserNotification>(); return(response); }
protected override IQueryOver <UDSRepository, UDSRepository> MappingProjection(IQueryOver <UDSRepository, UDSRepository> queryOver) { UDSRepositoryModel udsModel = null; queryOver .SelectList(list => list .Select(x => x.Id).WithAlias(() => udsModel.Id) .Select(x => x.ActiveDate).WithAlias(() => udsModel.ActiveDate) .Select(x => x.ExpiredDate).WithAlias(() => udsModel.ExpiredDate) .Select(x => x.ModuleXML).WithAlias(() => udsModel.ModuleXML) .Select(x => x.Name).WithAlias(() => udsModel.Name) .Select(Projections.Cast( NHibernateUtil.Enum(typeof(UDSRepositoryStatus)), Projections.Property("Status")).WithAlias(() => udsModel.Status)) .Select(x => x.Version).WithAlias(() => udsModel.Version)); return(queryOver); }
protected override IQueryOver <PECMailBoxUser, PECMailBoxUser> MappingProjection( IQueryOver <PECMailBoxUser, PECMailBoxUser> queryOver) { PECMailBoxUserDto mailBoxDto = null; SecurityUsers securityUsers = null; PECMailBox pecMailBox = null; queryOver .SelectList(list => list // Mappatura degli oggetti Desk .Select(x => x.Id).WithAlias(() => mailBoxDto.Id) .Select(x => x.AccountName).WithAlias(() => mailBoxDto.Account) .Select(() => securityUsers.Account).WithAlias(() => mailBoxDto.SecurityAccount) .Select(() => securityUsers.Id).WithAlias(() => mailBoxDto.SecurityId) .Select(() => pecMailBox.Id).WithAlias(() => mailBoxDto.MailBoxId) .Select(() => pecMailBox.MailBoxName).WithAlias(() => mailBoxDto.MailBoxName)); return(queryOver); }
public List <KodIsim> GetCariKodsBySubeKodu(string subeKodu, string cariKodIsim, FTIRSIP ftirsip) { KodIsim kodIsim = null; IQueryOver <Cari, Cari> over = Session.QueryOver <Cari>().Where(x => (x.Sube.Id == subeKodu || x.SubelerdeOrtak == true) && (x.Id.IsLike(cariKodIsim, MatchMode.Anywhere) || x.CariIsim.IsLike(cariKodIsim, MatchMode.Anywhere))); if (FTIRSIP.AlisFat == ftirsip || FTIRSIP.AlisIrs == ftirsip) { over.And(x => (x.CariTip == "S" || x.CariTip == "AS")); } else if (FTIRSIP.SatisFat == ftirsip || FTIRSIP.SatisIrs == ftirsip) { over.And(x => (x.CariTip == "A" || x.CariTip == "AS")); } return(over.SelectList(liste => liste.SelectGroup(c => c.Id).WithAlias(() => kodIsim.Kod) .SelectGroup(c => c.CariIsim).WithAlias(() => kodIsim.Isim) ).TransformUsing(Transformers.AliasToBean <KodIsim>()) .Take(GetMaxResult).List <KodIsim>() as List <KodIsim>); }
/// <summary> /// Mappo gli oggetti di DeskDocumentEndorsement su DeskEndorsement. /// <see cref="IsApprove">IsApprove viene settato intero poichè in una PivotGrid ho la necessità di aggregare questa informazione</see> /// </summary> /// <param name="queryOver"></param> /// <returns></returns> protected override IQueryOver <Desk, Desk> MappingProjection(IQueryOver <Desk, Desk> queryOver) { DeskEndorsement deskEndorsment = null; DeskDocumentEndorsement deskDocumentEndorsement = null; DeskRoleUser deskRoleUser = null; DeskDocumentVersion deskDocumentVersion = null; DeskDocument deskDocument = null; queryOver .SelectList(list => list // Mappatura degli oggetti DeskDocumentEndorsmen //.Select(Projections.Cast(NHibernateUtil.Int32, Projections.Property(() => deskDocumentEndorsement.Endorsement))).WithAlias(() => deskEndorsment.IsApprove) .Select(() => deskDocumentEndorsement.Endorsement).WithAlias(() => deskEndorsment.IsApprove) // Mappatura degli oggetti DeskDocumentVersion .Select(() => deskDocumentVersion.Version).WithAlias(() => deskEndorsment.Version) // Mappatura degli oggetti DeskDocument .Select(() => deskDocument.Id).WithAlias(() => deskEndorsment.DeskDocumentId) .Select(() => deskDocument.IdDocument).WithAlias(() => deskEndorsment.IdChainBiblos) .Select(() => deskRoleUser.AccountName).WithAlias(() => deskEndorsment.AccountName)); return(queryOver); }
public static int TotalViewsFor(this HtmlHelper helper, Webpage webpage) { if (webpage == null) { return(0); } var session = helper.ViewContext.HttpContext.Get <ISession>(); AnalyticsPageView pageView = null; AnalyticsSession analyticsSession = null; AnalyticsUser analyticsUser = null; IQueryOver <AnalyticsPageView, AnalyticsPageView> queryOver = session.QueryOver(() => pageView) .JoinAlias(() => pageView.AnalyticsSession, () => analyticsSession) .JoinAlias(() => analyticsSession.AnalyticsUser, () => analyticsUser) .Where(x => x.Webpage.Id == webpage.Id); PageViewResult result = null; var viewResult = queryOver .SelectList(builder => builder .SelectGroup(() => pageView.Url) .WithAlias(() => result.Url) .SelectGroup(() => pageView.Webpage.Id) .WithAlias(() => result.WebpageId) .SelectCountDistinct(() => analyticsUser.Id) .WithAlias(() => result.Unique) .SelectCountDistinct(() => analyticsSession.Id) .WithAlias(() => result.Sessions) .SelectCountDistinct(() => pageView.Id) .WithAlias(() => result.Total) ) .TransformUsing(Transformers.AliasToBean <PageViewResult>()) .Take(1).SingleOrDefault <PageViewResult>(); return(viewResult == null ? 0 : viewResult.Total); }
public List <ImageSortItem> GetFilesToSort(MediaCategory category = null) { IQueryOver <MediaFile, MediaFile> query = _session.QueryOver <MediaFile>(); query = category != null ? query.Where(file => file.MediaCategory.Id == category.Id) : query.Where(file => file.MediaCategory == null); query = query.OrderBy(x => x.DisplayOrder).Asc; ImageSortItem item = null; return(query.SelectList(builder => { builder.Select(file => file.FileName).WithAlias(() => item.Name); builder.Select(file => file.Id).WithAlias(() => item.Id); builder.Select(file => file.DisplayOrder).WithAlias(() => item.Order); builder.Select(file => file.FileExtension).WithAlias(() => item.FileExtension); builder.Select(file => file.FileUrl).WithAlias(() => item.ImageUrl); return builder; }).TransformUsing(Transformers.AliasToBean <ImageSortItem>()) .List <ImageSortItem>().ToList()); }
public IList <NotificationModel> GetNotifications() { Entities.People.User user = CurrentRequestData.CurrentUser; IQueryOver <Notification, Notification> queryOver = _session.QueryOver <Notification>(); if (user.LastNotificationReadDate.HasValue) { queryOver = queryOver.Where(notification => notification.CreatedOn >= user.LastNotificationReadDate); } NotificationModel notificationModelAlias = null; return(queryOver.SelectList( builder => builder.Select(notification => notification.Message) .WithAlias(() => notificationModelAlias.Message) .Select(notification => notification.CreatedOn) .WithAlias(() => notificationModelAlias.DateValue)) .OrderBy(notification => notification.CreatedOn).Desc .TransformUsing(Transformers.AliasToBean <NotificationModel>()) .Take(15) .List <NotificationModel>()); }
protected override IQueryOver <WorkflowActivity, WorkflowActivity> MappingProjection(IQueryOver <WorkflowActivity, WorkflowActivity> queryOver) { WorkflowActivityResult workflowResult = null; WorkflowRepository workflowRepository = null; WorkflowInstance workflowInstance = null; queryOver .SelectList(list => list // Mappatura degli oggetti Desk .Select(x => x.Id).WithAlias(() => workflowResult.WorkflowActivityId) .Select(x => x.Name).WithAlias(() => workflowResult.WorkflowActivityName) .Select(x => x.Status).WithAlias(() => workflowResult.WorkflowActivityStatus) .Select(x => x.ActivityType).WithAlias(() => workflowResult.WorkflowActivityType) .Select(x => x.RegistrationDate).WithAlias(() => workflowResult.WorkflowActivityPublicationDate) .Select(x => x.RegistrationUser).WithAlias(() => workflowResult.WorkflowActivityRequestorUser) .Select(x => x.LastChangedDate).WithAlias(() => workflowResult.WorkflowActivityLastChangedDate) .Select(x => x.Subject).WithAlias(() => workflowResult.WorkflowSubject) // Mappatura degli oggetti WorkflowRepository .Select(() => workflowRepository.Name).WithAlias(() => workflowResult.WorkflowRepositoryName) .Select(() => workflowInstance.Id).WithAlias(() => workflowResult.WorkflowInstanceId) ); return(queryOver); }
public void Should_Find_Page_And_Return_ViewModel_Successfully() { // DEBUG START var request = new SearchableGridOptions { SearchQuery = "a", PageNumber = 2, PageSize = 3, Column = "PageUrl", Direction = SortDirection.Descending }; var sessionFactory = this.Container.Resolve <ISessionFactoryProvider>(); // DEBUG END SearchableGridViewModel <SiteSettingRedirectViewModel> model; using (var session = sessionFactory.OpenSession()) { BetterCms.Module.Pages.Models.Redirect alias = null; SiteSettingRedirectViewModel modelAlias = null; IQueryOver <BetterCms.Module.Pages.Models.Redirect, BetterCms.Module.Pages.Models.Redirect> query = session.QueryOver(() => alias); // Filter if (!string.IsNullOrWhiteSpace(request.SearchQuery)) { var searchQuery = string.Format("%{0}%", request.SearchQuery); query = query .Where( Restrictions.InsensitiveLike(Projections.Property(() => alias.PageUrl), searchQuery) || Restrictions.InsensitiveLike(Projections.Property(() => alias.RedirectUrl), searchQuery)); } // Select fields query = query .SelectList(select => select .Select(() => alias.Id).WithAlias(() => modelAlias.Id) .Select(() => alias.PageUrl).WithAlias(() => modelAlias.PageUrl) .Select(() => alias.RedirectUrl).WithAlias(() => modelAlias.RedirectUrl) .Select(() => alias.Version).WithAlias(() => modelAlias.Version)) .TransformUsing(Transformers.AliasToBean <SiteSettingRedirectViewModel>()); // Count var count = query.RowCount(); // Sorting and paging query.AddSortingAndPaging(request); // Get results var redirects = query.List <SiteSettingRedirectViewModel>(); model = new SearchableGridViewModel <SiteSettingRedirectViewModel> { GridOptions = request, Items = new CustomPagination <SiteSettingRedirectViewModel>(redirects, request.PageNumber, request.PageSize, count) }; } // DEBUG START Assert.IsNotNull(model); // DEBUG END }
public KendoGridVm ConsultarResumo(PaginacaoVm paginacaoVm, EficienciaNegociacaoFiltroVm filtro) { IQueryOver <ProcessoDeCotacao, ProcessoDeCotacao> queryOver = _processosDeCotacaoDeMaterial.GetQueryOver(); queryOver = queryOver .Where(x => x.GetType() == typeof(ProcessoDeCotacaoDeMaterial)) .And(x => x.Status == Enumeradores.StatusProcessoCotacao.Fechado); Usuario compradorAlias = null; queryOver = queryOver .JoinAlias(x => x.Comprador, () => compradorAlias); if (!string.IsNullOrEmpty(filtro.LoginComprador)) { queryOver = queryOver.Where(() => compradorAlias.Login == filtro.LoginComprador); } if (filtro.DataDeFechamentoInicial.HasValue) { queryOver = queryOver.Where(x => x.DataDeFechamento >= filtro.DataDeFechamentoInicial.Value); } if (filtro.DataDeFechamentoFinal.HasValue) { queryOver = queryOver.Where(x => x.DataDeFechamento <= filtro.DataDeFechamentoFinal.Value); } ProcessoDeCotacaoDeMaterialItem processoDeCotacaoItem = null; queryOver = queryOver .JoinAlias(x => x.Itens, () => processoDeCotacaoItem); //para contabilizar os registros a query deve ser feita antes de fazer junção com as outras tabelas, para evitar repetições. //Precisa ter junção apenas com os itens do processo de cotação var contadorDeRegistros = queryOver.ToRowCountQuery().SingleOrDefault <int>(); EficienciaDeNegociacaoResumoVm eficienciaDeNegociacaoResumoVm = null; FornecedorParticipante fornecedorParticipante = null; Cotacao cotacao = null; CotacaoMaterialItem cotacaoItem = null; Produto produto = null; RequisicaoDeCompra requisicaoDeCompra = null; queryOver = queryOver .JoinAlias(() => processoDeCotacaoItem.Produto, () => produto) .JoinAlias(() => processoDeCotacaoItem.RequisicaoDeCompra, () => requisicaoDeCompra) .JoinAlias(x => x.FornecedoresParticipantes, () => fornecedorParticipante) .JoinAlias(() => fornecedorParticipante.Cotacao, () => cotacao) .JoinAlias(() => cotacao.Itens, () => cotacaoItem) .Where(() => cotacaoItem.Selecionada); //Para calcular o percentual e o valor de eficiência tive que utilizar SQLProjection porque o método SelectSum() // não entende uma expressão lambda, como por exemplo, "() => (PrecoInicial - Preco) * QuantidadeAdquirida)". //O método espera uma expressao lambda com o nome de uma propriedade que leve, para uma coluna do mapeamento. IProjection projecaoValorDeEficiencia = Projections.SqlProjection("SUM((PrecoInicial - Preco) * QuantidadeAdquirida) AS ValorDeEficiencia", new[] { "ValorDeEficiencia" }, new IType[] { NHibernateUtil.Decimal }); IProjection projecaoPercentualDeEficiencia = Projections.SqlProjection( "ROUND(SUM((PrecoInicial - Preco) * QuantidadeAdquirida) / SUM(PrecoInicial * QuantidadeAdquirida) * 100, 2) AS PercentualDeEficiencia", new[] { "PercentualDeEficiencia" }, new IType[] { NHibernateUtil.Decimal }); IList <EficienciaDeNegociacaoResumoVm> eficiencias = queryOver .SelectList(list => list .SelectGroup(x => x.Id).WithAlias(() => eficienciaDeNegociacaoResumoVm.IdProcessoCotacao) .SelectGroup(() => processoDeCotacaoItem.Id).WithAlias(() => eficienciaDeNegociacaoResumoVm.IdProcessoCotacaoItem) .SelectGroup(x => compradorAlias.Nome).WithAlias(() => eficienciaDeNegociacaoResumoVm.Comprador) .SelectGroup(() => produto.Descricao).WithAlias(() => eficienciaDeNegociacaoResumoVm.Produto) .SelectGroup(() => requisicaoDeCompra.Numero).WithAlias(() => eficienciaDeNegociacaoResumoVm.NumeroDaRequisicao) .SelectGroup(() => requisicaoDeCompra.NumeroItem).WithAlias(() => eficienciaDeNegociacaoResumoVm.NumeroDoItem) .Select(projecaoValorDeEficiencia) .Select(projecaoPercentualDeEficiencia) ) .TransformUsing(Transformers.AliasToBean <EficienciaDeNegociacaoResumoVm>()) .Skip(paginacaoVm.Skip) .Take(paginacaoVm.Take) .List <EficienciaDeNegociacaoResumoVm>(); var eficienciaTotal = queryOver .SelectList(list => list .Select(Projections.Constant("TOTAL")).WithAlias(() => eficienciaDeNegociacaoResumoVm.Produto) .Select(projecaoValorDeEficiencia) .Select(projecaoPercentualDeEficiencia) ) .TransformUsing(Transformers.AliasToBean <EficienciaDeNegociacaoResumoVm>()) .SingleOrDefault <EficienciaDeNegociacaoResumoVm>(); eficiencias.Add(eficienciaTotal); return(new KendoGridVm { QuantidadeDeRegistros = contadorDeRegistros + 1, //soma 1 por causa do registro de total Registros = eficiencias.Cast <ListagemVm>().ToList() }); }
public IPagedList <PageViewResult> Search(PageViewSearchQuery query) { AnalyticsPageView pageView = null; AnalyticsSession analyticsSession = null; AnalyticsUser analyticsUser = null; IQueryOver <AnalyticsPageView, AnalyticsPageView> queryOver = _session.QueryOver(() => pageView) .JoinAlias(() => pageView.AnalyticsSession, () => analyticsSession) .JoinAlias(() => analyticsSession.AnalyticsUser, () => analyticsUser); if (!string.IsNullOrWhiteSpace(query.Url)) { queryOver = queryOver.Where(view => view.Url.IsInsensitiveLike(query.Url, MatchMode.Anywhere)); } queryOver = queryOver.Where(() => pageView.CreatedOn >= query.From && pageView.CreatedOn <= query.To); switch (query.SearchType) { case PageViewSearchType.UsersOnly: queryOver = queryOver.Where(() => analyticsUser.User != null); break; case PageViewSearchType.AnonymousOnly: queryOver = queryOver.Where(() => analyticsUser.User == null); break; } PageViewResult result = null; IPagedList <PageViewResult> pageViewResults = queryOver .SelectList(builder => builder .SelectGroup(() => pageView.Url) .WithAlias(() => result.Url) .SelectGroup(() => pageView.Webpage.Id) .WithAlias(() => result.WebpageId) .SelectCountDistinct(() => analyticsUser.Id) .WithAlias(() => result.Unique) .SelectCountDistinct(() => analyticsSession.Id) .WithAlias(() => result.Sessions) .SelectCountDistinct(() => pageView.Id) .WithAlias(() => result.Total) ) .TransformUsing(Transformers.AliasToBean <PageViewResult>()) .OrderBy(Projections.CountDistinct(() => analyticsUser.Id)).Desc .ThenBy(Projections.CountDistinct(() => analyticsSession.Id)).Desc .ThenBy(Projections.CountDistinct(() => pageView.Id)).Desc .Paged <AnalyticsPageView, PageViewResult>(Projections.CountDistinct(() => pageView.Url), query.Page, enableCache: false); //todo enable cache when Nhibernate is updated to 4.1 List <int?> ids = pageViewResults.Select(viewResult => viewResult.WebpageId).Where(i => i.HasValue).ToList(); Dictionary <int, Webpage> webpages = _session.QueryOver <Webpage>() .Where(webpage => webpage.Id.IsIn(ids)) .Cacheable() .List() .ToDictionary(webpage => webpage.Id, webpage => webpage); foreach (PageViewResult pageViewResult in pageViewResults) { if (!pageViewResult.WebpageId.HasValue) { continue; } if (!webpages.ContainsKey(pageViewResult.WebpageId.Value)) { continue; } pageViewResult.WebpageName = webpages[pageViewResult.WebpageId.Value].Name; } return(pageViewResults); }