public override Response Handle(PagedSearchRequest <TDto> request)
{
var response = CreateTypedResponse();
var keyword = request.Keyword + "%";
IQueryOver <TEntity> query = null;
var entityQuery = Session.QueryOver <TEntity>()
.Where(WhereExpress(keyword));
var organizationExpression = GetOrganization();
if (organizationExpression != null)
{
query = entityQuery.JoinQueryOver(organizationExpression)
.Where(o => o.Key == UserContext.OrganizationKey);
}
else
{
query = entityQuery;
}
query = query.Skip(request.Page * request.PageSize)
.Take(request.PageSize);
var result = query.Future <TEntity>() ?? Enumerable.Empty <TEntity> ();
response.TotalCount = query.ToRowCountQuery().FutureValue <int>().Value;
response.Results = Mapper.Map <IEnumerable <TEntity>, IEnumerable <TDto> > (result.ToList());
return(response);
}
public static PagedResultSet <T> ToPagedSet <T>(this IQueryOver <T, T> query, ListOptions listOptions)
{
var count = query.ToRowCountQuery().FutureValue <int>();
var results = query.Skip(listOptions.PageSize * listOptions.Page).Take(listOptions.PageSize).Future();
return(new PagedResultSet <T>(results, count.Value));
}
public void MultiCriteria()
{
var driver = sessions.ConnectionProvider.Driver;
if (!driver.SupportsMultipleQueries)
{
Assert.Ignore("Driver {0} does not support multi-queries", driver.GetType().FullName);
}
SetupPagingData();
using (ISession s = OpenSession())
{
IQueryOver <Person> query =
s.QueryOver <Person>()
.JoinQueryOver(p => p.Children)
.OrderBy(c => c.Age).Desc
.Skip(2)
.Take(1);
var multiCriteria =
s.CreateMultiCriteria()
.Add("page", query)
.Add <int>("count", query.ToRowCountQuery());
var pageResults = (IList <Person>)multiCriteria.GetResult("page");
var countResults = (IList <int>)multiCriteria.GetResult("count");
Assert.That(pageResults.Count, Is.EqualTo(1));
Assert.That(pageResults[0].Name, Is.EqualTo("Name 3"));
Assert.That(countResults.Count, Is.EqualTo(1));
Assert.That(countResults[0], Is.EqualTo(4));
}
using (ISession s = OpenSession())
{
QueryOver <Person> query =
QueryOver.Of <Person>()
.JoinQueryOver(p => p.Children)
.OrderBy(c => c.Age).Desc
.Skip(2)
.Take(1);
var multiCriteria =
s.CreateMultiCriteria()
.Add("page", query)
.Add <int>("count", query.ToRowCountQuery());
var pageResults = (IList <Person>)multiCriteria.GetResult("page");
var countResults = (IList <int>)multiCriteria.GetResult("count");
Assert.That(pageResults.Count, Is.EqualTo(1));
Assert.That(pageResults[0].Name, Is.EqualTo("Name 3"));
Assert.That(countResults.Count, Is.EqualTo(1));
Assert.That(countResults[0], Is.EqualTo(4));
}
}
public void MultiCriteria()
{
SetupPagingData();
using (ISession s = OpenSession())
{
IQueryOver <Person> query =
s.QueryOver <Person>()
.JoinQueryOver(p => p.Children)
.OrderBy(c => c.Age).Desc
.Skip(2)
.Take(1);
var multiCriteria =
s.CreateMultiCriteria()
.Add("page", query)
.Add <int>("count", query.ToRowCountQuery());
var pageResults = (IList <Person>)multiCriteria.GetResult("page");
var countResults = (IList <int>)multiCriteria.GetResult("count");
Assert.That(pageResults.Count, Is.EqualTo(1));
Assert.That(pageResults[0].Name, Is.EqualTo("Name 3"));
Assert.That(countResults.Count, Is.EqualTo(1));
Assert.That(countResults[0], Is.EqualTo(4));
}
using (ISession s = OpenSession())
{
QueryOver <Person> query =
QueryOver.Of <Person>()
.JoinQueryOver(p => p.Children)
.OrderBy(c => c.Age).Desc
.Skip(2)
.Take(1);
var multiCriteria =
s.CreateMultiCriteria()
.Add("page", query)
.Add <int>("count", query.ToRowCountQuery());
var pageResults = (IList <Person>)multiCriteria.GetResult("page");
var countResults = (IList <int>)multiCriteria.GetResult("count");
Assert.That(pageResults.Count, Is.EqualTo(1));
Assert.That(pageResults[0].Name, Is.EqualTo("Name 3"));
Assert.That(countResults.Count, Is.EqualTo(1));
Assert.That(countResults[0], Is.EqualTo(4));
}
}
static public PagedResult <DTO> ToPagedResults <DTO, Model>(this IQueryOver <Model, Model> query, PartialRetrievingInfo retrievingInfo, string countPropertyName = null, Func <IEnumerable <Model>, DTO[]> mappingMethod = null, IQueryOver <Model, Model> fetchQuery = null)
{
IEnumerable <Model> queryEnumerable = null;
if (fetchQuery != null)
{
fetchQuery.ApplyPaging(retrievingInfo).Future();
}
int count = 0;
if (retrievingInfo.PageSize > PartialRetrievingInfo.AllElementsPageSize)
{
IFutureValue <int> rowCountQuery = query.ToRowCountQuery().FutureValue <int>();
IQueryOver <Model> pagedResults = query.ApplyPaging(retrievingInfo);
queryEnumerable = pagedResults.Future();
count = rowCountQuery.Value;
}
else
{
queryEnumerable = query.Future();
count = queryEnumerable.Count();
}
DTO[] list = null;
if (mappingMethod == null)
{
list = Mapper.Map <IEnumerable <Model>, DTO[]>(queryEnumerable);
}
else
{
list = mappingMethod(queryEnumerable);
}
PagedResult <DTO> res = new PagedResult <DTO>(list, count, retrievingInfo.PageIndex);
res.RetrievedDateTime = DateTime.UtcNow;
Log.WriteInfo("Paged result. AllCount:{0},PageIndex:{1},PageSize:{2}", res.AllItemsCount, res.PageIndex, res.Items.Count);
return(res);
}
protected virtual PagedList <T> FindPagedInternal(int pageIndex, int pageSize, Func <IQueryOver <T, T>, IQueryOver <T, T> > func)
{
IQueryOver <T> rowCountQueryOver = func != null?func(CreateQueryOver()) : CreateQueryOver();
IFutureValue <int> rowCount =
rowCountQueryOver
.ToRowCountQuery()
.FutureValue <int>();
IQueryOver <T> pagingQueryOver = func != null?func(CreateQueryOver()) : CreateQueryOver();
IList <T> qryResult =
pagingQueryOver
.Skip((pageIndex - 1) * pageSize)
.Take(pageSize)
.Future <T>()
.ToList <T>();
PagedList <T> result = new PagedList <T>(qryResult, pageIndex, pageSize, rowCount.Value);
return(result);
}
public void TransformQueryOverToRowCount()
{
IQueryOver <Person> expected =
CreateTestQueryOver <Person>()
.Where(p => p.Name == "test")
.JoinQueryOver(p => p.Children)
.Where((Child c) => c.Age == 5)
.Select(Projections.RowCount());
IQueryOver <Person> actual =
CreateTestQueryOver <Person>()
.Where(p => p.Name == "test")
.JoinQueryOver(p => p.Children)
.Where((Child c) => c.Age == 5)
.OrderBy(c => c.Age).Asc
.Skip(20)
.Take(10);
expected = expected.Clone();
actual = actual.ToRowCountQuery();
AssertCriteriaAreEqual(expected.UnderlyingCriteria, actual);
}
/// <summary>
/// Adds <see cref="paging"/> restrictions to <see cref="query"/>.
/// </summary>
protected void AddPaging <T>(IQueryOver <T, T> query, FilterBase paging, bool distinct = false) where T : EntityBase
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (paging == null)
{
return;
}
if (paging.Take.HasValue)
{
query.Take(paging.Take.Value);
}
if (paging.Skip.HasValue)
{
query.Skip(paging.Skip.Value);
}
var rowQuery = distinct ? ToDistinctRowCount(query) : query.ToRowCountQuery();
paging.TotalCount = rowQuery.FutureValue <int>().Value;
}
public static IFutureValue <int> ToRowCountFutureValue <TRoot, TSubType>(this IQueryOver <TRoot, TSubType> query)
{
return(query.ToRowCountQuery().FutureValue <int>());
}
public IQueryOver <TRoot, TRoot> ToRowCountQuery()
{
return(MainQuery.ToRowCountQuery());
}
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()
});
}