public IPagedList <ProductReview> Search(ProductReviewSearchQuery query)
{
IQueryOver <ProductReview, ProductReview> queryOver = _session.QueryOver <ProductReview>();
switch (query.ApprovalStatus)
{
case ApprovalStatus.Pending:
queryOver = queryOver.Where(review => review.Approved == null);
break;
case ApprovalStatus.Rejected:
queryOver = queryOver.Where(review => review.Approved == false);
break;
case ApprovalStatus.Approved:
queryOver = queryOver.Where(review => review.Approved == true);
break;
}
if (!string.IsNullOrWhiteSpace(query.ProductName))
{
ProductVariant productVariantAlias = null;
Product productAlias = null;
queryOver = queryOver.JoinAlias(review => review.ProductVariant, () => productVariantAlias)
.JoinAlias(() => productVariantAlias.Product, () => productAlias)
.Where(
() =>
productVariantAlias.Name.IsInsensitiveLike(query.ProductName, MatchMode.Anywhere) ||
productAlias.Name.IsInsensitiveLike(query.ProductName, MatchMode.Anywhere));
}
if (!string.IsNullOrWhiteSpace(query.Email))
{
User userAlias = null;
queryOver =
queryOver.JoinAlias(review => review.User, () => userAlias, JoinType.LeftOuterJoin)
.Where(
review =>
(review.Email.IsInsensitiveLike(query.Email, MatchMode.Anywhere)) ||
(userAlias.Email.IsInsensitiveLike(query.Email, MatchMode.Anywhere)));
}
if (!string.IsNullOrWhiteSpace(query.Title))
{
queryOver = queryOver.Where(review => review.Title.IsLike(query.Title, MatchMode.Anywhere));
}
if (query.DateFrom.HasValue)
{
queryOver = queryOver.Where(review => review.CreatedOn >= query.DateFrom);
}
if (query.DateTo.HasValue)
{
queryOver = queryOver.Where(review => review.CreatedOn < query.DateTo);
}
return
(queryOver.OrderBy(review => review.Approved)
.Asc.ThenBy(review => review.CreatedOn)
.Desc.Paged(query.Page));
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <Acatalog, Acatalog> Filtering(IQueryOver <Acatalog, Acatalog> query, AcatalogFilter filter)
{
if (!string.IsNullOrEmpty(filter.Name))
{
query.WhereRestrictionOn(x => x.Name).IsLike(filter.Name);
}
if (filter.SectionOfSystem != null && !string.IsNullOrWhiteSpace(filter.SectionOfSystem.UnitCode))
{
query.JoinQueryOver(x => x.SectionOfSystem, JoinType.LeftOuterJoin)
.Where(x => x.Rn == filter.SectionOfSystem.UnitCode);
}
if (filter.UserPrivilege != null)
{
UserPrivilege userPrivilegeALias = null;
query.JoinAlias(x => x.UserPrivileges, () => userPrivilegeALias);
query.JoinQueryOver(() => userPrivilegeALias.Role, JoinType.InnerJoin);
if (filter.UserPrivilege.Role != null)
{
if (filter.UserPrivilege.Role.Rn != 0)
{
query.Where(() => userPrivilegeALias.Role.Rn == filter.UserPrivilege.Role.Rn);
}
if (filter.UserPrivilege.Role.UserRoles != null)
{
UserRole userRoleAlias = null;
query.JoinAlias(() => userPrivilegeALias.Role.UserRoles, () => userRoleAlias)
.EqualsUser(x => userRoleAlias.UserList.AUTHID);
// var fil = Restrictions.EqProperty(
// Projections.SqlFunction("User", NHibernateUtil.String),
// Projections.Property<UserRole>(x => userRoleAlias.UserList.AUTHID));
// query.Where(fil);
}
}
if (filter.UserPrivilege.UnitFunction != null)
{
UnitFunction unitfuncAlias = null;
query.JoinAlias(() => userPrivilegeALias.UnitFunctions, () => unitfuncAlias);
query.Where(x => unitfuncAlias.Standard == filter.UserPrivilege.UnitFunction.Standard);
if (filter.UserPrivilege.UnitFunction.SectionOfSystemUnitcode != null)
{
query.JoinQueryOver(() => unitfuncAlias.SectionOfSystemUnitcode, JoinType.InnerJoin);
if (!string.IsNullOrWhiteSpace(filter.UserPrivilege.UnitFunction.SectionOfSystemUnitcode.UnitCode))
{
query.Where(x => unitfuncAlias.SectionOfSystemUnitcode.Rn == filter.UserPrivilege.UnitFunction.SectionOfSystemUnitcode.UnitCode);
}
}
}
}
return(query);
}
public override IQueryOver <AdditionalDictionary, AdditionalDictionary> Filtering(
IQueryOver <AdditionalDictionary, AdditionalDictionary> query,
AdditionalDictionaryFilter filter)
{
query.FindByRn(filter.Rn);
if (!string.IsNullOrWhiteSpace(filter.Code))
{
query.IsLike(x => x.Code, filter.Code.ReplaceStar(string.Empty));
}
if (filter.AdditionalDictionaryValues != null)
{
AdditionalDictionaryValues additionalDictionaryValuesAlias = null;
query.JoinAlias(
x => x.AdditionalDictionaryValues,
() => additionalDictionaryValuesAlias,
JoinType.LeftOuterJoin);
if (!string.IsNullOrWhiteSpace(filter.AdditionalDictionaryValues.Note))
{
query.Where(() => additionalDictionaryValuesAlias.Note == filter.AdditionalDictionaryValues.Note);
}
}
return(query);
}
public IPagedList <Article> GetArticles(ArticleList page, ArticleSearchModel model)
{
IQueryOver <Article, Article> query = _session.QueryOver <Article>()
.Where(a => a.Parent == page);
if (!string.IsNullOrEmpty(model.Category))
{
Tag tagAlias = null;
query = query.JoinAlias(article => article.Tags, () => tagAlias).Where(() => tagAlias.Name.IsInsensitiveLike(model.Category, MatchMode.Exact));
}
if (model.Month.HasValue)
{
query =
query.Where(
article => article.PublishOn != null && article.PublishOn.Value.MonthPart() == model.Month);
}
if (model.Year.HasValue)
{
query =
query.Where(
article => article.PublishOn != null && article.PublishOn.Value.YearPart() == model.Year);
}
return(query.OrderBy(x => x.PublishOn).Desc.Paged(model.Page, page.PageSize));
}
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);
}
private IQueryOver <Category, Category> FilterByYear(IQueryOver <Category, Category> queryOver)
{
CategorySchema categorySchema = null;
queryOver = queryOver.JoinAlias(x => x.CategorySchema, () => categorySchema)
.Where(x => x.StartDate.Year <= Year.Value && (x.EndDate == null || x.EndDate.Value.Year >= Year.Value) && categorySchema.StartDate <= _today);
return(queryOver);
}
internal static IQueryOver <ExerciseProfileData, ExerciseProfileData> PublicRecordsQueryCriteria(IQueryOver <ExerciseProfileData, ExerciseProfileData> query, Profile profile)
{
ProfileStatistics stat = null;
query = query.JoinAlias(x => profile.Statistics, () => stat);
query = query.Where(x => (stat.StrengthTrainingEntriesCount >= Portable.Constants.StrengthTrainingEntriesCount || stat.TrainingDaysCount >= Portable.Constants.PublicTrainingDaysCount) && x.Customer == null);
return(query);
}
private IQueryOver <Category, Category> FilterByDate(IQueryOver <Category, Category> queryOver)
{
DateTimeOffset dateToCheck = _today;
dateToCheck = StartDate.HasValue ? StartDate.Value : dateToCheck;
dateToCheck = !StartDate.HasValue && EndDate.HasValue ? EndDate.Value : dateToCheck;
CategorySchema categorySchema = null;
queryOver = queryOver.JoinAlias(x => x.CategorySchema, () => categorySchema)
.Where(x => x.StartDate <= dateToCheck && (x.EndDate == null || x.EndDate > dateToCheck) && categorySchema.StartDate <= _today);
return(queryOver);
}
public override IQueryOver <CertificateQuality, CertificateQuality> Filtering(
IQueryOver <CertificateQuality, CertificateQuality> query, CertificateQualityFilter filter)
{
query.FindByRn(filter.Rn);
query.IsBetween(x => x.CreationDate, filter.CreationDate);
query.IsBetween(x => x.StorageDate, filter.StorageDate);
query.IsBetween(x => x.MakingDate, filter.MakingDate);
query.IsLike(x => x.StandardSize, filter.StandardSize.ReplaceStar());
query.IsLike(x => x.NomerCertificata, filter.NomerCertificata.ReplaceStar());
query.IsLike(x => x.ModeThermoTreatment, filter.ModeThermoTreatment.ReplaceStar());
query.IsLike(x => x.Mix, filter.Mix.ReplaceStar());
query.IsLike(x => x.Marka, filter.Marka.ReplaceStar());
query.IsLike(x => x.GostMix, filter.GostMix.ReplaceStar());
query.IsLike(x => x.GostMarka, filter.GostMarka.ReplaceStar());
query.IsLike(x => x.FullRepresentation, filter.FullRepresentation.ReplaceStar());
query.IsLike(x => x.DeliveryCondition, filter.DeliveryCondition.ReplaceStar());
query.IsLike(x => x.Cast, filter.Cast.ReplaceStar());
query.IsIn(x => x.State, filter.State);
if (filter.UserCreator != null)
{
query.Where(x => x.UserCreator.Rn == filter.UserCreator.Rn);
}
if (!string.IsNullOrWhiteSpace(filter.Pref))
{
query.Where(x => x.Pref == filter.Pref);
}
if (filter.Numb != null)
{
query.Where(x => x.Numb == filter.Numb);
}
if (filter.CreatorFactory != null)
{
query.Where(x => x.CreatorFactory.Rn == filter.CreatorFactory.Rn);
}
if (filter.NomenclatureNumber != null)
{
query.JoinQueryOver(x => x.PlanCertificate, JoinType.LeftOuterJoin);
NomenclatureNumberModification nommodifAlias = null;
query.JoinAlias(x => x.PlanCertificate.ModificationNomenclature, () => nommodifAlias, JoinType.LeftOuterJoin);
query.Where(x => nommodifAlias.Code == filter.NomenclatureNumber.Code);
}
return(query);
}
protected override IQueryOver <PECMailBoxUser, PECMailBoxUser> DecorateCriteria(IQueryOver <PECMailBoxUser, PECMailBoxUser> queryOver)
{
PECMailBox pecMailBox = null;
SecurityUsers securityUsers = null;
queryOver.JoinAlias(x => x.PECMailBox, () => pecMailBox)
.Left.JoinAlias(x => x.SecurityUser, () => securityUsers);
if (IdPECMailBox.HasValue)
{
queryOver.Where(() => pecMailBox.Id == IdPECMailBox.Value);
}
return(queryOver);
}
public IQueryOver <ForecastMonth, ForecastMonth> Apply(IQueryOver <ForecastMonth, ForecastMonth> queryOver, ForecastMonth monthAlias, Trex.Server.Core.Model.Forecast.Forecast forecastAlias)
{
ForecastProjectHours projectRegAlias = null;
var projectCrit = QueryOver
.Of <ForecastProjectHours>()
.Where(x => x.Project.ProjectID == _projectId)
.And(x => x.Parent.Id == forecastAlias.Id)
.Select(x => x.Id)
.Take(1);
return(queryOver
.JoinAlias(() => forecastAlias.ProjectRegistrations, () => projectRegAlias)
.Where(Subqueries.WhereExists(projectCrit)));
}
private static IQueryOver <NodeVersion, NodeVersion> AddSchemasToOuterQuery(IQueryOver <NodeVersion, NodeVersion> mainLoaderQuery, NhCriteriaVisitor.FilterResult filteringSubquery)
{
if (filteringSubquery.RequiredSchemaAliases != null && filteringSubquery.RequiredSchemaAliases.Any())
{
AttributeSchemaDefinition schema = null;
mainLoaderQuery = mainLoaderQuery
.JoinAlias(x => x.AttributeSchemaDefinition, () => schema)
.Where(() => schema.Alias.IsIn(filteringSubquery.RequiredSchemaAliases.ToArray()));
}
if (filteringSubquery.RequiredNodeIds != null && filteringSubquery.RequiredNodeIds.Any())
{
mainLoaderQuery = mainLoaderQuery
.Where(x => x.Node.Id.IsIn(filteringSubquery.RequiredNodeIds.ToArray()));
}
return(mainLoaderQuery);
}
public IList <QuotaPlanejadoRealizadoPorDataVm> PlanejadoRealizadoPorData(RelatorioAgendamentoFiltroVm filtro)
{
//OBS: GROUP BY COM ORDER BY CAUSA UM ERRO UTILIZANDO IQUERYABLE (GetQuery).
//Por isso tive que utilizar IQueryOver
//var query = (from quota in _quotas.GetQuery()
// group quota by new { quota.CodigoTerminal, quota.Data, quota.Fornecedor.Codigo, quota.Fornecedor.Nome, quota.FluxoDeCarga }
// into agrupador
// select new
// {
// agrupador.Key,
// PlanejadoTotal = agrupador.Sum(x => x.PesoTotal),
// RealizadoTotal = agrupador.Sum(x => x.PesoRealizado)
// }
// ).ToList()
// .OrderBy(x => new{x.Key.CodigoTerminal, x.Key.Data});
IQueryOver <Quota, Quota> queryOver = ObtemQueryOverComFiltrosAplicados(filtro);
Fornecedor fornec = null;
queryOver = queryOver
.JoinAlias(x => x.Fornecedor, () => fornec)
.SelectList(list => list
.SelectGroup(x => x.CodigoTerminal)
.SelectGroup(x => x.Data)
.SelectGroup(x => x.Fornecedor.Codigo)
.SelectGroup(x => fornec.Nome)
.SelectGroup(x => x.FluxoDeCarga)
.SelectSum(x => x.PesoTotal)
.SelectSum(x => x.PesoRealizado)
).OrderBy(x => x.CodigoTerminal).Asc.OrderBy(x => x.Data).Asc;
//tive que utilizar um array de objetos porque na query que executa no banco ainda não tenho o método Descricao() do Enum.
return(Enumerable.Select <object[], QuotaPlanejadoRealizadoPorDataVm>(queryOver
.List <object[]>(), properties => new QuotaPlanejadoRealizadoPorDataVm
{
CodigoTerminal = (string)properties[0],
Data = ((DateTime)properties[1]).ToShortDateString(),
NomeDoFornecedor = ((string)properties[2]) + " - " + (string)properties[3],
FluxoDeCarga = ((Enumeradores.FluxoDeCarga)properties[4]).Descricao(),
Quota = (decimal)properties[5],
PesoRealizado = (decimal)properties[6]
}).ToList());
}
protected override IQueryOver <DeskStoryBoard, DeskStoryBoard> DecorateCriteria(IQueryOver <DeskStoryBoard, DeskStoryBoard> queryOver)
{
queryOver.JoinAlias(x => x.Desk, () => desk)
.Left.JoinAlias(o => o.DeskDocumentVersion, () => deskDocumentVersion)
.Left.JoinAlias(() => deskDocumentVersion.DeskDocument, () => deskDocument);
if (DeskId.HasValue)
{
SetDeskIdFilter(queryOver);
}
if (DeskDocumentId.HasValue && FindDocumentComments)
{
SetDocumentIdFilter(queryOver);
}
//Filtro per autore commento
if (!string.IsNullOrEmpty(this.Author))
{
queryOver.WhereRestrictionOn(x => x.Author).IsLike(this.Author, MatchMode.Anywhere);
}
//Filtro per descrizione commento
if (!string.IsNullOrEmpty(this.Description))
{
queryOver.WhereRestrictionOn(x => x.Comment).IsLike(this.Description, MatchMode.Anywhere);
}
//Filtro per data
if (this.DateFrom.HasValue && !this.DateFrom.Equals(DateTime.MinValue))
{
queryOver.Where(x => x.DateBoard >= this.DateFrom.Value);
}
if (this.DateTo.HasValue && !this.DateTo.Equals(DateTime.MinValue))
{
queryOver.Where(x => x.DateBoard <= this.DateTo.Value);
}
if (this.CommentType.HasValue)
{
queryOver.Where(x => x.BoardType == this.CommentType.Value);
}
return(queryOver);
}
private static IQueryOver <AggregateNodeStatus, AggregateNodeStatus> AddSchemasToOuterQuery(IQueryOver <AggregateNodeStatus, AggregateNodeStatus> aggQuery, NhAggregateCriteriaVisitor.FilterResult filteringSubquery)
{
if (filteringSubquery.RequiredSchemaAliases != null && filteringSubquery.RequiredSchemaAliases.Any())
{
AttributeSchemaDefinition schema = null;
NodeVersion v = null;
aggQuery = aggQuery
.JoinAlias(x => x.NodeVersion, () => v)
.JoinAlias(() => v.AttributeSchemaDefinition, () => schema)
.Where(() => schema.Alias.IsIn(filteringSubquery.RequiredSchemaAliases.ToArray()));
}
if (filteringSubquery.RequiredNodeIds != null && filteringSubquery.RequiredNodeIds.Any())
{
aggQuery = aggQuery
.Where(x => x.Node.Id.IsIn(filteringSubquery.RequiredNodeIds.ToArray()));
}
return(aggQuery);
}
public ICollection <SMSNotification> GetByTipology(Int16 IdPECMailBox = Int16.MinValue, SMSNotificationType notificationType = SMSNotificationType.PEC, LogicalStateType logicalState = LogicalStateType.Active)
{
PECMail pec = null;
PECMailBox mailBox = null;
IQueryOver <SMSNotification, SMSNotification> expression = NHibernateSession.QueryOver <SMSNotification>();
if (IdPECMailBox != Int16.MinValue)
{
expression = expression
.JoinAlias(x => x.PECMail, () => pec)
.JoinAlias(x => x.PECMail.MailBox, () => mailBox)
.Where(() => mailBox.Id == IdPECMailBox);
}
return(expression
.Where(f => f.NotificationType == notificationType)
.And((f) => f.LogicalState == logicalState)
.List <SMSNotification>());
}
public IQueryOver <ForecastMonth, ForecastMonth> Apply(IQueryOver <ForecastMonth, ForecastMonth> queryOver, ForecastMonth monthAlias, Trex.Server.Core.Model.Forecast.Forecast forecastAlias)
{
Company companyAlias = null;
Project projectAlias = null;
ForecastProjectHours projectRegAlias = null;
var projectCrit = QueryOver
.Of <Company>()
.Where(x => x.CustomerID == _companyId)
.And(x => x.CustomerID == companyAlias.CustomerID)
.Select(x => x.CustomerID)
.Take(1);
return(queryOver
.JoinAlias(() => forecastAlias.ProjectRegistrations, () => projectRegAlias)
.JoinAlias(() => projectRegAlias.Project, () => projectAlias)
.JoinAlias(() => projectAlias.Company, () => companyAlias)
.Where(Subqueries.WhereExists(projectCrit)));
}
protected override IQueryOver <DeskDocumentEndorsement, DeskDocumentEndorsement> MappingProjection(IQueryOver <DeskDocumentEndorsement, DeskDocumentEndorsement> queryOver)
{
DeskEndorsement deskEndorsment = null;
DeskRoleUser deskRoleUser = null;
DeskDocumentVersion deskDocumentVersion = null;
DeskDocument deskDocument = null;
queryOver.JoinAlias(o => o.DeskRoleUser, () => deskRoleUser)
.SelectList(list => list
// Mappatura degli oggetti DeskDocumentEndorsment
.Select(x => x.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(() => deskRoleUser.AccountName).WithAlias(() => deskEndorsment.AccountName));
return(queryOver);
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <GoodsManager, GoodsManager> Filtering(IQueryOver <GoodsManager, GoodsManager> query, GoodsManagerFilter filter)
{
query.FindByRn(filter.Rn);
if (filter.Contractor != null)
{
Contractor contractorAlias = null;
query.JoinAlias(x => x.Contractor, () => contractorAlias, JoinType.LeftOuterJoin);
if (!string.IsNullOrWhiteSpace(filter.Contractor.TableNumber))
{
query.IsLike((x) => contractorAlias.ClockNumber, filter.Contractor.TableNumber.ReplaceStar(string.Empty));
}
if (!string.IsNullOrWhiteSpace(filter.Contractor.OrganizationName))
{
query.IsLike(x => contractorAlias.Name, filter.Contractor.OrganizationName.ReplaceStar(string.Empty));
}
}
return(query);
}
protected override IQueryOver <PECMailLog, PECMailLog> MappingProjection(IQueryOver <PECMailLog, PECMailLog> queryOver)
{
PECMail pec = null;
PECMailLogHeader header = null;
queryOver
.JoinAlias(o => o.Mail, () => pec)
.SelectList(list => list
.Select(x => x.Id).WithAlias(() => header.Id)
.Select(x => x.Description).WithAlias(() => header.Description)
.Select(x => x.Type).WithAlias(() => header.Type)
.Select(x => x.Date).WithAlias(() => header.Date)
.Select(x => x.SystemComputer).WithAlias(() => header.SystemComputer)
.Select(x => x.SystemUser).WithAlias(() => header.SystemUser)
.Select(x => pec.Id).WithAlias(() => header.MailId)
.Select(x => pec.MailSubject).WithAlias(() => header.MailSubject)
)
.TransformUsing(Transformers.AliasToBean <PECMailLogHeader>());
return(queryOver);
}
private static IQueryOver <AggregateNodeStatus, AggregateNodeStatus> ApplyPossibleDbSortOrders(QueryDescription query, IQueryOver <AggregateNodeStatus, AggregateNodeStatus> filteredAggQuery, out bool appliedSort, bool nodeIsJoinedAlready = false)
{
appliedSort = false;
if (query.SortClauses.Any())
{
Node node = null;
foreach (var sortClause in query.SortClauses)
{
switch (sortClause.FieldSelector.FieldName.ToLowerInvariant())
{
case "utccreated":
var joined = nodeIsJoinedAlready ? filteredAggQuery : filteredAggQuery.JoinAlias(x => x.Node, () => node);
nodeIsJoinedAlready = true;
var createOrderBuilder = joined.OrderBy(() => node.DateCreated);
filteredAggQuery = (sortClause.Direction == SortDirection.Ascending)
? createOrderBuilder.Asc
: createOrderBuilder.Desc;
appliedSort = true;
break;
case "utcmodified":
case "utcstatuschanged":
var modifiedOrderBuilder = filteredAggQuery.OrderBy(x => x.StatusDate);
filteredAggQuery = (sortClause.Direction == SortDirection.Ascending)
? modifiedOrderBuilder.Asc
: modifiedOrderBuilder.Desc;
appliedSort = true;
break;
}
}
}
return(filteredAggQuery);
}
public IPagedList <User> GetUsersPaged(UserSearchQuery searchQuery)
{
IQueryOver <User, User> query = _session.QueryOver <User>();
if (!string.IsNullOrWhiteSpace(searchQuery.Email))
{
query =
query.Where(
user =>
user.Email.IsInsensitiveLike(searchQuery.Email, MatchMode.Anywhere));
}
if (!string.IsNullOrWhiteSpace(searchQuery.FirstName))
{
query =
query.Where(
user =>
user.FirstName.IsInsensitiveLike(searchQuery.FirstName, MatchMode.Anywhere));
}
if (!string.IsNullOrWhiteSpace(searchQuery.LastName))
{
query =
query.Where(
user =>
user.LastName.IsInsensitiveLike(searchQuery.LastName, MatchMode.Anywhere));
}
if (searchQuery.UserRoleId != null)
{
UserRole role = null;
query = query.JoinAlias(user => user.Roles, () => role).Where(() => role.Id == searchQuery.UserRoleId);
}
return(query.Paged(searchQuery.Page));
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <PlanReceiptOrder, PlanReceiptOrder> Filtering(
IQueryOver <PlanReceiptOrder, PlanReceiptOrder> query, PlanReceiptOrderFilter filter)
{
query.FindByRn(filter.Rn);
query.IsBetween(x => x.CreationDate, filter.CreationDate);
query.IsBetween(x => x.GroundDocumentDate, filter.GroundDocumentDate);
query.IsLike(x => x.GroundDocumentNumb, filter.GroundDocumentNumb, MatchMode.Start);
query.IsLike(x => x.Note, filter.Note, MatchMode.Start);
query.IsLike(x => x.Pref, filter.Pref, MatchMode.Start);
query.IsBetween(x => x.StateDate, filter.StateDate);
if (filter.Numb != null)
{
query.Where(x => x.Numb == filter.Numb);
}
if (filter.States.Any())
{
query.WhereRestrictionOn(x => x.State)
.IsIn(filter.States.ToArray());
}
if (filter.PlanCertificate != null)
{
if (filter.PlanCertificate.ModificationNomenclature != null)
{
if (filter.PlanCertificate.ModificationNomenclature.NomenclatureNumber != null)
{
if (!string.IsNullOrWhiteSpace(filter.PlanCertificate.ModificationNomenclature.NomenclatureNumber.Code))
{
query.JoinQueryOver(x => x.PlanCertificates, JoinType.LeftOuterJoin);
NomenclatureNumberModification nommodifAlias = null;
query.JoinAlias((x) => filter.PlanCertificate.ModificationNomenclature, () => nommodifAlias, JoinType.LeftOuterJoin);
NomenclatureNumber nomenclatureNumberAlias = null;
query.JoinQueryOver(() => nommodifAlias.NomenclatureNumber, () => nomenclatureNumberAlias, JoinType.LeftOuterJoin);
query.WhereRestrictionOn(() => nomenclatureNumberAlias.NomenCode)
.IsLike(
filter.PlanCertificate.ModificationNomenclature.NomenclatureNumber.Code
.ReplaceStar());
}
}
}
}
if (filter.StaffingDivision != null)
{
if (filter.StaffingDivision.Code != null && filter.StaffingDivision.Code.Any())
{
query.JoinQueryOver(x => x.StaffingDivision, JoinType.LeftOuterJoin)
.WhereRestrictionOn(x => x.Code)
.IsIn(filter.StaffingDivision.Code.ToArray());
}
}
if (filter.StoreGasStationOilDepot != null)
{
if (!string.IsNullOrWhiteSpace(filter.StoreGasStationOilDepot.AzsNumber))
{
query.JoinQueryOver(x => x.StoreGasStationOilDepot, JoinType.LeftOuterJoin)
.Where(x => x.Number == filter.StoreGasStationOilDepot.AzsNumber);
}
}
if (!string.IsNullOrWhiteSpace(filter.GroundTypeOfDocument.DocCode))
{
query.JoinQueryOver(x => x.GroundTypeOfDocument, JoinType.LeftOuterJoin)
.Where(x => x.DocumentCode == filter.GroundTypeOfDocument.DocCode);
}
return(query);
}
public IQueryOver <TRoot, TSubType> JoinAlias(Expression <Func <TSubType, object> > path, Expression <Func <object> > alias)
{
MainQuery.JoinAlias(path, alias);
return(this);
}
private static IQueryOver <SupplementCycleDefinition, SupplementCycleDefinition> getSupplementsCycleDefinitionsCriterias(GetSupplementsCycleDefinitionsParam param, Profile loggedProfile, List <Guid> ids, Profile myProfile,
IQueryOver <SupplementCycleDefinition, SupplementCycleDefinition> queryCustomer)
{
SupplementCycleDosage dosage = null;
SupplementCycleWeek week = null;
queryCustomer = queryCustomer.JoinAlias(x => x.Weeks, () => week)
.JoinAlias(x => x.Weeks.First().Dosages, () => dosage);
if (param.LegalCriteria == CanBeIllegalCriteria.OnlyLegal)
{
queryCustomer = queryCustomer.Where(x => !x.CanBeIllegal);
}
else if (param.LegalCriteria == CanBeIllegalCriteria.OnlyIllegal)
{
queryCustomer = queryCustomer.Where(x => x.CanBeIllegal);
}
if (param.PlanId.HasValue)
{
queryCustomer = queryCustomer.Where(x => x.GlobalId == param.PlanId.Value);
}
if (param.Languages.Count > 0)
{
var langOr = Restrictions.Disjunction();
foreach (var lang in param.Languages)
{
langOr.Add <SupplementCycleDefinition>(x => x.Language == lang);
}
queryCustomer = queryCustomer.And(langOr);
}
if (param.Purposes.Count > 0)
{
var purposeOr = Restrictions.Disjunction();
foreach (var purpose in param.Purposes)
{
purposeOr.Add <SupplementCycleDefinition>(x => x.Purpose == (WorkoutPlanPurpose)purpose);
}
queryCustomer = queryCustomer.And(purposeOr);
}
if (param.Difficults.Count > 0)
{
var mainOr = Restrictions.Disjunction();
foreach (TrainingPlanDifficult diff in param.Difficults)
{
var tt = (BodyArchitect.Model.TrainingPlanDifficult)diff;
mainOr.Add <SupplementCycleDefinition>(x => x.Difficult == tt);
}
queryCustomer = queryCustomer.And(mainOr);
}
if (param.Supplements.Count > 0)
{
Junction supplementsOperations = null;
if (param.SupplementsListOperator == CriteriaOperator.Or)
{
supplementsOperations = Restrictions.Disjunction();
foreach (var supplementId in param.Supplements)
{
supplementsOperations.Add <SupplementCycleDefinition>(x => dosage.Supplement.GlobalId == supplementId);
}
}
else
{
supplementsOperations = Restrictions.Conjunction();
foreach (var supplementId in param.Supplements)
{
var orderIdsCriteria = DetachedCriteria.For <SupplementCycleDosage>();
orderIdsCriteria.SetProjection(Projections.CountDistinct("GlobalId"))
.Add(Restrictions.Where <SupplementCycleDosage>(x => x.Supplement.GlobalId == supplementId))
.Add(Restrictions.Where <SupplementCycleDosage>(x => x.Week.GlobalId == week.GlobalId));
supplementsOperations.Add(Subqueries.Lt(0, orderIdsCriteria));
//supplementsOperations.Add<SupplementCycleDosage>(x => dosage.Supplement.GlobalId == supplementId);
}
}
queryCustomer = queryCustomer.And(supplementsOperations);
}
queryCustomer = queryCustomer.Where(x => x.Profile == loggedProfile || (x.Profile != loggedProfile && x.Status == PublishStatus.Published));
var groupOr = new Disjunction();
if (param.SearchGroups.Count > 0)
{
if (param.SearchGroups.IndexOf(WorkoutPlanSearchCriteriaGroup.Mine) > -1)
{
groupOr.Add <BodyArchitect.Model.SupplementCycleDefinition>(x => x.Profile == myProfile);
}
if (param.SearchGroups.IndexOf(WorkoutPlanSearchCriteriaGroup.Favorites) > -1)
{
if (myProfile.FavoriteSupplementCycleDefinitions.Count > 0)
{
groupOr.Add <BodyArchitect.Model.SupplementCycleDefinition>(x => x.GlobalId.IsIn((ICollection)ids));
}
}
if (param.SearchGroups.IndexOf(WorkoutPlanSearchCriteriaGroup.Other) > -1)
{
var tmpAnd = Restrictions.Conjunction();
tmpAnd.Add <BodyArchitect.Model.SupplementCycleDefinition>(
dto => dto.Profile != null && dto.Profile != myProfile);
if (ids.Count > 0)
{
tmpAnd.Add(Restrictions.On <BodyArchitect.Model.SupplementCycleDefinition>(x => x.GlobalId).Not.IsIn((ICollection)ids));
}
groupOr.Add(tmpAnd);
}
queryCustomer = queryCustomer.Where(groupOr);
}
return(queryCustomer);
}
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()
});
}