private static IQueryOver<Webpage, Webpage> ApplySort(DocumentMetadata metaData, IQueryOver<Webpage, Webpage> query)
{
switch (metaData.SortBy)
{
case SortBy.DisplayOrder:
query = query.OrderBy(webpage => webpage.DisplayOrder).Asc;
break;
case SortBy.DisplayOrderDesc:
query = query.OrderBy(webpage => webpage.DisplayOrder).Desc;
break;
case SortBy.PublishedOn:
query =
query.OrderBy(Projections.Conditional(Restrictions.IsNull(Projections.Property<Webpage>(x => x.PublishOn)), Projections.Constant(1), Projections.Constant(0))).Desc.ThenBy(webpage => webpage.PublishOn)
.Asc;
break;
case SortBy.PublishedOnDesc:
query =
query.OrderBy(Projections.Conditional(Restrictions.IsNull(Projections.Property<Webpage>(x => x.PublishOn)), Projections.Constant(1), Projections.Constant(0))).Desc.ThenBy(webpage => webpage.PublishOn)
.Desc;
break;
case SortBy.CreatedOn:
query = query.OrderBy(webpage => webpage.CreatedOn).Asc;
break;
case SortBy.CreatedOnDesc:
query = query.OrderBy(webpage => webpage.CreatedOn).Desc;
break;
default:
throw new ArgumentOutOfRangeException();
}
return query;
}
private void AplicarFiltroDeAreaDeVenda(IQueryOver<Fornecedor, Fornecedor> queryOver, int idDaAreaDeVenda)
{
ClienteVenda areaDeVenda = _clienteVendas.ObterPorId(idDaAreaDeVenda);
FornecedorDaEmpresa fornecedorDaEmpresa = null;
Fornecedor fornecedor = null;
QueryOver<FornecedorDaEmpresa, FornecedorDaEmpresa> subQuery = QueryOver.Of(() => fornecedorDaEmpresa)
.Where(Restrictions.EqProperty(Projections.Property(() => fornecedor.Codigo), Projections.Property(() => fornecedorDaEmpresa.Fornecedor.Codigo)))
.And(x => x.Empresa == areaDeVenda.Org_vendas)
.Select(Projections.Property(() => fornecedorDaEmpresa.Empresa));
queryOver.WithSubquery.WhereExists(subQuery);
}
protected virtual IQueryOver <PagesView, PagesView> FilterQuery(IQueryOver <PagesView, PagesView> query,
PagesFilter request, Junction hasnotSeoDisjunction)
{
PageProperties alias = null;
if (!request.IncludeArchived)
{
query = query.Where(() => !alias.IsArchived);
}
if (request.OnlyMasterPages)
{
query = query.Where(() => alias.IsMasterPage);
}
else if (!request.IncludeMasterPages)
{
query = query.Where(() => !alias.IsMasterPage);
}
if (!string.IsNullOrWhiteSpace(request.SearchQuery))
{
var searchQuery = string.Format("%{0}%", request.SearchQuery);
query = query.Where(Restrictions.Disjunction()
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.Title), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.PageUrl), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.MetaTitle), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.MetaDescription), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.MetaKeywords), searchQuery)));
}
if (request.CategoryId.HasValue)
{
query = query.Where(Restrictions.Eq(Projections.Property(() => alias.Category.Id), request.CategoryId.Value));
}
if (request.LanguageId.HasValue)
{
if (request.LanguageId.Value.HasDefaultValue())
{
query = query.Where(Restrictions.IsNull(Projections.Property(() => alias.Language.Id)));
}
else
{
query = query.Where(Restrictions.Eq(Projections.Property(() => alias.Language.Id), request.LanguageId.Value));
}
}
if (request.Tags != null)
{
foreach (var tagKeyValue in request.Tags)
{
var id = tagKeyValue.Key.ToGuidOrDefault();
query = query.WithSubquery.WhereExists(QueryOver.Of <PageTag>().Where(tag => tag.Tag.Id == id && tag.Page.Id == alias.Id).Select(tag => 1));
}
}
if (request.Status.HasValue)
{
if (request.Status.Value == PageStatusFilterType.OnlyPublished)
{
query = query.Where(() => alias.Status == PageStatus.Published);
}
else if (request.Status.Value == PageStatusFilterType.OnlyUnpublished)
{
query = query.Where(() => alias.Status != PageStatus.Published);
}
else if (request.Status.Value == PageStatusFilterType.ContainingUnpublishedContents)
{
const ContentStatus draft = ContentStatus.Draft;
Root.Models.Content contentAlias = null;
var subQuery = QueryOver.Of <PageContent>()
.JoinAlias(p => p.Content, () => contentAlias)
.Where(pageContent => pageContent.Page.Id == alias.Id)
.And(() => contentAlias.Status == draft)
.And(() => !contentAlias.IsDeleted)
.Select(pageContent => 1);
query = query.WithSubquery.WhereExists(subQuery);
}
}
if (request.SeoStatus.HasValue)
{
if (request.SeoStatus.Value == SeoStatusFilterType.HasNotSeo)
{
query = query.Where(hasnotSeoDisjunction);
}
else
{
query = query.Where(Restrictions.Not(hasnotSeoDisjunction));
}
}
if (!string.IsNullOrWhiteSpace(request.Layout))
{
Guid id;
var length = request.Layout.Length - 2;
if (request.Layout.StartsWith("m-") && Guid.TryParse(request.Layout.Substring(2, length), out id))
{
query = query.Where(() => alias.MasterPage.Id == id);
}
if (request.Layout.StartsWith("l-") && Guid.TryParse(request.Layout.Substring(2, length), out id))
{
query = query.Where(() => alias.Layout.Id == id);
}
}
if (request.ContentId.HasValue)
{
Root.Models.Content contentAlias = null;
ChildContent childContentAlias = null;
HtmlContent htmlContentAlias = null;
PageContent pageContentAlias = null;
var htmlChildContentSubQuery =
QueryOver.Of(() => htmlContentAlias)
.JoinAlias(h => h.ChildContents, () => childContentAlias)
.Where(() => htmlContentAlias.Id == contentAlias.Id)
.And(() => childContentAlias.Child.Id == request.ContentId.Value)
.Select(pageContent => 1);
var pageContentSubQuery = QueryOver.Of(() => pageContentAlias)
.JoinAlias(() => pageContentAlias.Content, () => contentAlias)
.And(() => pageContentAlias.Page.Id == alias.Id)
.And(() => !contentAlias.IsDeleted)
.And(() => !pageContentAlias.IsDeleted)
.And(Restrictions.Or(
Restrictions.Where(() => contentAlias.Id == request.ContentId.Value),
Subqueries.WhereExists(htmlChildContentSubQuery)
))
.Select(pageContent => 1);
query = query.WithSubquery.WhereExists(pageContentSubQuery);
}
return(query);
}
protected override IQueryOver <DSW.Role, DSW.Role> MappingProjection(IQueryOver <DSW.Role, DSW.Role> queryOver)
{
throw new NotImplementedException();
}
private static IQueryOver<Item, Item> AddSortingCriterias(PagedEntityListFilterDto filter, IQueryOver<Item, Item> query)
{
IQueryOverOrderBuilder<Item, Item> builder = null;
switch (filter.Column)
{
case "Id":
builder = query.OrderBy(x => x.Id);
break;
case "Name":
builder = query.OrderBy(x => x.Name);
break;
case "SerialNumber":
builder = query.OrderBy(x => x.SerialNumber);
break;
case "CreatedOn":
builder = query.OrderBy(x => x.CreatedOn);
break;
case "ModifiedOn":
builder = query.OrderBy(x => x.ModifiedOn);
break;
}
if (builder != null)
{
query = filter.AscendingOrder ? builder.Asc : builder.Desc;
}
return query;
}
private static IQueryOver<Contact, Contact> ApplyInvalidNameFilter(IQueryOver<Contact, Contact> q1)
{
return q1.Where(x => x.Surname != null && x.Forename != null);
}
public IQueryOver <KoordinierteMassnahmeGIS, KoordinierteMassnahmeGIS> BuildFilter(IQueryOver <KoordinierteMassnahmeGIS, KoordinierteMassnahmeGIS> source)
{
return(source.Where(s => s.Mandant == CurrentMandant));
}
protected override IQueryOver <DSW.ProtocolUser, DSW.ProtocolUser> MappingProjection(IQueryOver <DSW.ProtocolUser, DSW.ProtocolUser> queryOver)
{
throw new NotImplementedException();
}
/// <summary>
/// Adds a query to the batch, returning it as an <see cref="IFutureEnumerable{T}"/>.
/// </summary>
/// <param name="batch">The batch.</param>
/// <param name="query">The query.</param>
/// <typeparam name="TResult">The type of the query result elements.</typeparam>
/// <returns>A future query which execution will be handled by the batch.</returns>
public static IFutureEnumerable <TResult> AddAsFuture <TResult>(this IQueryBatch batch, IQueryOver query)
{
return(AddAsFuture(batch, For <TResult>(query)));
}
private static IQueryOver<Recipes, Recipes> SearchBySkill(
RecipeQuery query,
IQueryOver<Recipes, Recipes> recipesQuery,
RecipeMetadata metadata)
{
if (query.Skill.Common)
{
recipesQuery = recipesQuery.Where(() => metadata.SkillCommon).OrderBy(() => metadata.Commonality).Desc();
}
if (query.Skill.Easy)
{
recipesQuery = recipesQuery.Where(() => metadata.SkillEasy);
}
if (query.Skill.Quick)
{
recipesQuery = recipesQuery.Where(() => metadata.SkillQuick);
}
return recipesQuery;
}
private static IQueryOver<Recipes, Recipes> SearchByTaste(
RecipeQuery query,
IQueryOver<Recipes, Recipes> recipesQuery,
RecipeMetadata metadata)
{
if (query.Taste.MildToSpicy != SpicinessLevel.Medium)
{
recipesQuery = query.Taste.MildToSpicy < SpicinessLevel.Medium
? recipesQuery.Where(() => metadata.TasteMildToSpicy <= query.Taste.Spiciness)
.OrderBy(() => metadata.TasteMildToSpicy)
.Asc()
: recipesQuery.Where(() => metadata.TasteMildToSpicy >= query.Taste.Spiciness)
.OrderBy(() => metadata.TasteMildToSpicy)
.Desc();
}
if (query.Taste.SavoryToSweet != SweetnessLevel.Medium)
{
recipesQuery = query.Taste.SavoryToSweet < SweetnessLevel.Medium
? recipesQuery.Where(() => metadata.TasteSavoryToSweet <= query.Taste.Sweetness)
.OrderBy(() => metadata.TasteSavoryToSweet)
.Asc()
: recipesQuery.Where(() => metadata.TasteSavoryToSweet >= query.Taste.Sweetness)
.OrderBy(() => metadata.TasteSavoryToSweet)
.Desc();
}
return recipesQuery;
}
private static IQueryOver<Recipes, Recipes> SearchByMeal(
RecipeQuery query,
IQueryOver<Recipes, Recipes> recipesQuery,
RecipeMetadata metadata)
{
if (query.Meal != MealFilter.All)
{
if (query.Meal == MealFilter.Breakfast)
{
recipesQuery = recipesQuery.Where(() => metadata.MealBreakfast);
}
if (query.Meal == MealFilter.Dessert)
{
recipesQuery = recipesQuery.Where(() => metadata.MealDessert);
}
if (query.Meal == MealFilter.Dinner)
{
recipesQuery = recipesQuery.Where(() => metadata.MealDinner);
}
if (query.Meal == MealFilter.Lunch)
{
recipesQuery = recipesQuery.Where(() => metadata.MealLunch);
}
}
return recipesQuery;
}
private static IQueryOver<Recipes, Recipes> SearchByNutrition(
RecipeQuery query,
IQueryOver<Recipes, Recipes> recipesQuery,
RecipeMetadata metadata)
{
if (query.Nutrition.LowCalorie)
{
recipesQuery = recipesQuery.Where(() => metadata.NutritionLowCalorie);
}
if (query.Nutrition.LowCarb)
{
recipesQuery = recipesQuery.Where(() => metadata.NutritionLowCarb);
}
if (query.Nutrition.LowFat)
{
recipesQuery = recipesQuery.Where(() => metadata.NutritionLowFat);
}
if (query.Nutrition.LowSodium)
{
recipesQuery = recipesQuery.Where(() => metadata.NutritionLowSodium);
}
if (query.Nutrition.LowSugar)
{
recipesQuery = recipesQuery.Where(() => metadata.NutritionLowSugar);
}
return recipesQuery;
}
private static IQueryOver<Recipes, Recipes> SearchByHighResolutionPhotos(
RecipeQuery query,
IQueryOver<Recipes, Recipes> recipesQuery,
RecipeMetadata metadata)
{
int highResolution = 1024 * 768;
if (query.Photos == PhotoFilter.HighRes)
{
recipesQuery = recipesQuery.Where(() => metadata.PhotoRes >= highResolution);
}
return recipesQuery;
}
private static IQueryOver<Recipes, Recipes> SearchByDiet(
RecipeQuery query,
IQueryOver<Recipes, Recipes> recipesQuery,
RecipeMetadata metadata)
{
if (query.Diet.GlutenFree)
{
recipesQuery = recipesQuery.Where(() => metadata.DietGlutenFree);
}
if (query.Diet.NoAnimals)
{
recipesQuery = recipesQuery.Where(() => metadata.DietNoAnimals);
}
if (query.Diet.NoMeat)
{
recipesQuery = recipesQuery.Where(() => metadata.DietNomeat);
}
if (query.Diet.NoPork)
{
recipesQuery = recipesQuery.Where(() => metadata.DietNoPork);
}
if (query.Diet.NoRedMeat)
{
recipesQuery = recipesQuery.Where(() => metadata.DietNoRedMeat);
}
return recipesQuery;
}
private static IQueryOver<Recipes, Recipes> AddIngredientsToInclude(
RecipeQuery query,
IQueryOver<Recipes, Recipes> subQuery,
Recipes recipe)
{
if (query.Include != null && query.Include.Length > 0)
{
// Add ingredients to include
// Create a sub-query for ingredients to include
subQuery =
subQuery.WithSubquery.WhereExists(
QueryOver.Of<RecipeIngredients>()
.Where(item => item.Recipe.RecipeId == recipe.RecipeId)
.Where(Restrictions.InG("Ingredient", query.Include.Select(Ingredients.FromId).ToArray()))
.Select(i => i.RecipeIngredientId)
.Take(1));
}
return subQuery;
}
private IQueryOver <UserEntity, UserEntity> AddOrderingByLastNameAndFirstNameToQuery(IQueryOver <UserEntity, UserEntity> query, UserEntity userAlias)
{
UserDataEntity userDataAlias = null;
UserDataTypeEntity userDataTypeAlias = null;
var lastNameUserData = QueryOver.Of(() => userDataAlias)
.JoinAlias(x => x.UserDataType, () => userDataTypeAlias)
.Where(x => userDataAlias.ActiveTo == null && userDataAlias.User.Id == userAlias.Id && userDataTypeAlias.DataTypeValue == UserDataTypes.LastName)
.Select(x => x.Value);
var firstNameUserData = QueryOver.Of(() => userDataAlias)
.JoinAlias(x => x.UserDataType, () => userDataTypeAlias)
.Where(x => userDataAlias.ActiveTo == null && userDataAlias.User.Id == userAlias.Id && userDataTypeAlias.DataTypeValue == UserDataTypes.FirstName)
.Select(x => x.Value);
query = query
.OrderBy(Projections.SubQuery(lastNameUserData)).Asc
.ThenBy(Projections.SubQuery(firstNameUserData)).Asc;
return(query);
}
private static bool LoadIndividualLists(
IList<ShoppingList> lists,
bool defaultsLoaded,
ref IQueryOver<ShoppingLists, ShoppingLists> query)
{
if (lists != null)
{
defaultsLoaded = lists.Contains(ShoppingList.Default);
var ids = lists.Where(list => list.Id.HasValue).Select(l => l.Id.Value).ToArray();
query = query.AndRestrictionOn(x => x.ShoppingListId).IsInG(ids);
}
return defaultsLoaded;
}
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);
}
private static IQueryOver<Menus, Menus> LoadIndividualMenus(IList<Menu> menus, IQueryOver<Menus, Menus> query)
{
var ids = menus.Where(m => m.Id.HasValue).Select(m => m.Id.Value).ToArray();
query = query.AndRestrictionOn(p => p.MenuId).IsInG(ids);
return query;
}
public IList <Enrollee> QueryAllenrollee(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, string srcPolicynumber, string scrOthername, string scrLastname, string scrMobilenumber, string scrProvider, string scrCompany, string scrCompanySubsidiary, bool useDate, DateTime scrFromDate, DateTime scrToDate, int showExpunge, string scrUsers, int enrolleetype, int otherFilters, int zones, int state, int plantype, int planmode)
{
IQueryOver <Enrollee, Enrollee> query = _session.QueryOver <Enrollee>().Where(x => x.IsDeleted == false);
if (!string.IsNullOrEmpty(srcPolicynumber))
{
//search policy number
srcPolicynumber = "%" + srcPolicynumber + "%";
query = query.Where(Restrictions.On <Enrollee>(x => x.Policynumber).IsInsensitiveLike(srcPolicynumber) || Restrictions.On <Enrollee>(x => x.RefPolicynumber).IsInsensitiveLike(srcPolicynumber));
//.JoinAlias(p => p.Primaryprovider, () => providerAlias)
//.JoinAlias(p => p.Stateid, () => stateAlias)
//.JoinAlias(p=>p.Staffprofileid,()=> staffAlias)
//.JoinAlias(()=>staffAlias.CompanySubsidiary,()=> subsidiaryAlias)
//.Where(()=>companyAlias.Name =="")
//Restrictions.On<Enrollee>(x => x.Policynumber).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Surname).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Othernames).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Occupation).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Mobilenumber).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Residentialaddress).IsInsensitiveLike(search));
;
}
if (!string.IsNullOrEmpty(scrOthername))
{
string content = Regex.Replace(scrOthername.Trim(), @"\s+", ":");
string[] namelist = content.Split(':');
foreach (string namesearch in namelist)
{
string seartxt = "%" + namesearch + "%";
query.Where(Restrictions.InsensitiveLike(
Projections.SqlFunction("concat",
NHibernateUtil.String,
Projections.Property("Othernames"),
Projections.Constant(" "),
Projections.Property("Surname")),
seartxt,
MatchMode.Anywhere));
}
}
if (!string.IsNullOrEmpty(scrMobilenumber))
{
scrMobilenumber = "%" + scrMobilenumber + "%";
query = query.Where(Restrictions.On <Enrollee>(x => x.Mobilenumber).IsInsensitiveLike(scrMobilenumber));
}
if (!string.IsNullOrEmpty(scrProvider) && Convert.ToInt32(scrProvider) > -1)
{
int provider = Convert.ToInt32(scrProvider);
query = query.Where(x => x.Primaryprovider == provider);
}
if (!string.IsNullOrEmpty(scrCompany) && Convert.ToInt32(scrCompany) > -1)
{
int company = Convert.ToInt32(scrCompany);
query = query.Where(x => x.Companyid == company);
}
if (!string.IsNullOrEmpty(scrCompanySubsidiary) && Convert.ToInt32(scrCompanySubsidiary) > -1)
{
int companysub = Convert.ToInt32(scrCompanySubsidiary);
List <int> stafflist = (List <int>)_session.QueryOver <Staff>().Where(x => x.IsDeleted == false && x.CompanySubsidiary == companysub).SelectList(a => a.Select(p => p.Id)).List <int>();
query = query.WhereRestrictionOn(w => w.Staffprofileid).IsIn(stafflist);
}
if (plantype > -1 && !string.IsNullOrEmpty(scrCompany) && Convert.ToInt32(scrCompany) > 0)
{
List <int> companyplan = (List <int>)_session.QueryOver <CompanyPlan>().Where(x => x.Planid == plantype && x.Companyid == Convert.ToInt32(scrCompany)).Select(a => a.Id).List <int>();
if (planmode == 0)
{
//single
companyplan = (List <int>)_session.QueryOver <CompanyPlan>().Where(x => x.Planid == plantype && x.AllowChildEnrollee == false && x.Companyid == Convert.ToInt32(scrCompany)).Select(a => a.Id).List <int>();
}
if (planmode == 1)
{
//family
companyplan = (List <int>)_session.QueryOver <CompanyPlan>().Where(x => x.Planid == plantype && x.AllowChildEnrollee == true && x.Companyid == Convert.ToInt32(scrCompany)).Select(a => a.Id).List <int>();
}
List <int> stafflist = (List <int>)_session.QueryOver <Staff>().Where(x => x.IsDeleted == false).WhereRestrictionOn(w => w.StaffPlanid).IsIn(companyplan).SelectList(a => a.Select(p => p.Id)).List <int>();
query = query.WhereRestrictionOn(w => w.Staffprofileid).IsIn(stafflist);
}
if (useDate)
{
DateTime datete = Convert.ToDateTime(scrToDate);
int dd = datete.Day;
int month = datete.Month;
int year = datete.Year;
string time = "23:59";
DateTime enddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", month, dd, year, time));
query.Where(Restrictions.On <Enrollee>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate));
}
if (!string.IsNullOrEmpty(scrUsers))
{
int toint = Convert.ToInt32(scrUsers);
if (toint > 0)
{
query.Where(x => x.Createdby == toint);
}
}
if (showExpunge > -1)
{
switch (showExpunge)
{
case 0:
//all
break;
case 1:
//not expunged
query = query.Where(x => x.Isexpundged == false);
break;
case 2:
query = query.Where(x => x.Isexpundged == true);
break;
}
}
if (enrolleetype > -1)
{
switch (enrolleetype)
{
case 0:
//all
break;
case 1:
//not expunged
query = query.Where(x => x.Parentid <= 0);
break;
case 2:
query = query.Where(x => x.Parentid > 0);
break;
}
}
if (zones > -1)
{
List <int> states = (List <int>)_session.QueryOver <State>().Where(x => x.Zone == zones).SelectList(a => a.Select(p => p.Id)).List <int>();
//var ids = new List<int> { 1, 2, 5, 7 };
//var query2 = _session.QueryOver<Provider>().Where(x => x.IsDeleted == false && x.AuthorizationStatus == 2 );
query
.WhereRestrictionOn(w => w.Stateid).IsIn(states);
}
if (state > -1)
{
query = query.Where(x => x.Stateid == state);
}
DateTime today = CurrentRequestData.Now;
DateTime childagelimit = today.Date.AddYears(-21);
DateTime parentagelimit = today.Date.AddYears(-70);
if (otherFilters > -1)
{
switch (otherFilters)
{
case 0:
//all
break;
case 1:
//female only
query = query.Where(x => x.Sex == (int)Sex.Female);
break;
case 2:
//Male only
query = query.Where(x => x.Sex == (int)Sex.Male);
break;
case 3:
//With Mobile Numbers only
query = query.Where(Restrictions.Gt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Mobilenumber)),
9
));
break;
case 4:
//Without Mobile Numbers only
query.Where(Restrictions.Lt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Mobilenumber)),
9
));
break;
case 5:
//With email only
query.Where(Restrictions.Gt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Emailaddress)),
5
));
break;
case 6:
//Without email only
query.Where(Restrictions.Lt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Emailaddress)),
5
));
break;
case 7:
//with age
query = query.Where(x => x.Dob <= childagelimit && x.Parentid > 0 && x.Parentrelationship == (int)Relationship.Child || x.Dob <= parentagelimit && x.Parentid == 0);
break;
case 8:
//with birthdays for the day
string etoday = today.ToString("MM-dd");
query = query.Where(x => x.Dob.Month == today.Month && x.Dob.Day == today.Day);
break;
case 9:
//idcard not printed
query = query.Where(x => x.IdCardPrinted == false);
break;
case 10:
query = query.Where(x => x.IdCardPrinted == true);
break;
}
}
//sort order
//return normal list.
totalRecord = query.RowCount();
totalcountinresult = totalRecord;
return(query.Skip(start).Take(lenght).List());
}
private static IQueryOver<User, User> AddSearchCriterias(string search, IQueryOver<User, User> query)
{
IList<ICriterion> searchCriterias = new List<ICriterion>();
searchCriterias.Add(Restrictions.On<User>(x => x.UserName ).IsInsensitiveLike(string.Format("%{0}%", search.ToLower())));
searchCriterias.Add(Restrictions.On<User>(x => x.FirstName).IsInsensitiveLike(string.Format("%{0}%", search.ToLower())));
searchCriterias.Add(Restrictions.On<User>(x => x.LastName).IsInsensitiveLike(string.Format("%{0}%", search.ToLower())));
int searchInteger;
if (int.TryParse(search, out searchInteger))
{
searchCriterias.Add(Restrictions.On<User>(x => x.Id).IsLike(searchInteger));
}
query = CommonUtils.AddQueryOverSearchCriterias(query, searchCriterias);
return query;
}
/// <summary>
/// Adds a query to the batch.
/// </summary>
/// <param name="batch">The batch.</param>
/// <param name="query">The query.</param>
/// <param name="afterLoad">Callback to execute when query is loaded. Loaded results are provided as action parameter.</param>
/// <typeparam name="TResult">The type of the query result elements.</typeparam>
/// <exception cref="InvalidOperationException">Thrown if the batch has already been executed.</exception>
/// <exception cref="ArgumentNullException">Thrown if <paramref name="query"/> is <see langword="null"/>.</exception>
/// <returns>The batch instance for method chain.</returns>
public static IQueryBatch Add <TResult>(this IQueryBatch batch, IQueryOver <TResult> query, Action <IList <TResult> > afterLoad = null)
{
return(batch.Add(For <TResult>(query), afterLoad));
}
private static IQueryOver<User, User> AddSortingCriterias(PagedEntityListFilterDto filter, IQueryOver<User, User> query)
{
IQueryOverOrderBuilder<User, User> builder = null;
switch (filter.Column)
{
case "Id":
builder = query.OrderBy(x => x.Id);
break;
case "UserName":
builder = query.OrderBy(x => x.UserName);
break;
case "LastName":
builder = query.OrderBy(x => x.LastName);
break;
case "LastLogin":
builder = query.OrderBy(x => x.LastLogin);
break;
}
if (builder != null)
{
query = filter.AscendingOrder ? builder.Asc : builder.Desc;
}
return query;
}
public IQueryOver <ZustandsabschnittGIS, ZustandsabschnittGIS> BuildFilter(IQueryOver <ZustandsabschnittGIS, ZustandsabschnittGIS> source)
{
StrassenabschnittGIS sa = null;
return(source.Where(() => sa.Mandant == CurrentMandant));
}
public IMultiCriteria Add <U>(IQueryOver queryOver)
{
return(Add <U>(queryOver.RootCriteria));
}
public void FunctionExtensions()
{
var date = new DateTime(1970, 1, 1);
ICriteria expected =
CreateTestCriteria(typeof(Person))
.Add(Restrictions.Eq(Projections.SqlFunction("year", NHibernateUtil.Int32, Projections.Property("BirthDate")), 1970))
.Add(Restrictions.Eq(Projections.SqlFunction("day", NHibernateUtil.Int32, Projections.Property("BirthDate")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("month", NHibernateUtil.Int32, Projections.Property("BirthDate")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("hour", NHibernateUtil.Int32, Projections.Property("BirthDate")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("minute", NHibernateUtil.Int32, Projections.Property("BirthDate")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("second", NHibernateUtil.Int32, Projections.Property("BirthDate")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("date", NHibernateUtil.Date, Projections.Property("BirthDate")), date))
.Add(Restrictions.Eq(Projections.SqlFunction("date", NHibernateUtil.Date, Projections.Property("BirthDateAsDateTimeOffset")), date))
.Add(Restrictions.Eq(Projections.SqlFunction("year", NHibernateUtil.Int32, Projections.Property("BirthDateAsDateTimeOffset")), 1970))
.Add(Restrictions.Eq(Projections.SqlFunction("day", NHibernateUtil.Int32, Projections.Property("BirthDateAsDateTimeOffset")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("month", NHibernateUtil.Int32, Projections.Property("BirthDateAsDateTimeOffset")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("hour", NHibernateUtil.Int32, Projections.Property("BirthDateAsDateTimeOffset")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("minute", NHibernateUtil.Int32, Projections.Property("BirthDateAsDateTimeOffset")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("second", NHibernateUtil.Int32, Projections.Property("BirthDateAsDateTimeOffset")), 1))
.Add(Restrictions.Eq(Projections.SqlFunction("sqrt", NHibernateUtil.Double, Projections.Property("Height")), 10d))
.Add(Restrictions.Eq(Projections.SqlFunction("lower", NHibernateUtil.String, Projections.Property("Name")), "test"))
.Add(Restrictions.Eq(Projections.SqlFunction("upper", NHibernateUtil.String, Projections.Property("Name")), "TEST"))
.Add(Restrictions.Eq(Projections.SqlFunction("abs", NHibernateUtil.Int32, Projections.Property("Height")), 150))
.Add(Restrictions.Eq(Projections.SqlFunction("trim", NHibernateUtil.String, Projections.Property("Name")), "test"))
.Add(Restrictions.Eq(Projections.SqlFunction("length", NHibernateUtil.String, Projections.Property("Name")), 4))
.Add(Restrictions.Eq(Projections.SqlFunction("bit_length", NHibernateUtil.String, Projections.Property("Name")), 32))
.Add(Restrictions.Eq(Projections.SqlFunction("substring", NHibernateUtil.String, Projections.Property("Name"), Projections.Constant(1), Projections.Constant(2)), "te"))
.Add(Restrictions.Eq(Projections.SqlFunction("locate", NHibernateUtil.String, Projections.Constant("e"), Projections.Property("Name"), Projections.Constant(1)), 2))
.Add(Restrictions.Eq(Projections.SqlFunction("coalesce", NHibernateUtil.Object, Projections.Property("Name"), Projections.Constant("not-null-val")), "test"))
.Add(Restrictions.Eq(Projections.SqlFunction("coalesce", NHibernateUtil.Object, Projections.Property("NullableIsParent"), Projections.Constant(true)), true))
.Add(Restrictions.Eq(Projections.SqlFunction("concat", NHibernateUtil.String, Projections.Property("Name"), Projections.Constant(", "), Projections.Property("Name")), "test, test"))
.Add(Restrictions.Eq(Projections.SqlFunction("mod", NHibernateUtil.Int32, Projections.Property("Height"), Projections.Constant(10)), 0));
IQueryOver <Person> actual =
CreateTestQueryOver <Person>()
.Where(p => p.BirthDate.Year == 1970)
.And(p => p.BirthDate.Day == 1)
.And(p => p.BirthDate.Month == 1)
.And(p => p.BirthDate.Hour == 1)
.And(p => p.BirthDate.Minute == 1)
.And(p => p.BirthDate.Second == 1)
.And(p => p.BirthDate.Date == date)
.And(p => p.BirthDateAsDateTimeOffset.Date == date)
.And(p => p.BirthDateAsDateTimeOffset.Year == 1970)
.And(p => p.BirthDateAsDateTimeOffset.Day == 1)
.And(p => p.BirthDateAsDateTimeOffset.Month == 1)
.And(p => p.BirthDateAsDateTimeOffset.Hour == 1)
.And(p => p.BirthDateAsDateTimeOffset.Minute == 1)
.And(p => p.BirthDateAsDateTimeOffset.Second == 1)
.And(p => p.Height.Sqrt() == 10)
.And(p => p.Name.Lower() == "test")
.And(p => p.Name.Upper() == "TEST")
.And(p => p.Height.Abs() == 150)
.And(p => p.Name.TrimStr() == "test")
.And(p => p.Name.StrLength() == 4)
.And(p => p.Name.BitLength() == 32)
.And(p => p.Name.Substr(1, 2) == "te")
.And(p => p.Name.CharIndex("e", 1) == 2)
.And(p => p.Name.Coalesce("not-null-val") == "test")
.And(p => p.NullableIsParent.Coalesce(true) == true)
.And(p => Projections.Concat(p.Name, ", ", p.Name) == "test, test")
.And(p => p.Height.Mod(10) == 0);
AssertCriteriaAreEqual(expected, actual);
}
OrderByRandom <TRoot, TSubType>(this IQueryOver <TRoot, TSubType> query)
{
query.UnderlyingCriteria.AddOrder(new RandomOrder());
return(query);
}
public IMultiCriteria Add(System.Type resultGenericListType, IQueryOver queryOver)
{
return(Add(resultGenericListType, queryOver.RootCriteria));
}
private static IQueryOver<Item, Item> AddSearchCriterias(string search, IQueryOver<Item, Item> query)
{
IList<ICriterion> searchCriterias = new List<ICriterion>();
searchCriterias.Add(Restrictions.On<Item>(x => x.Name).IsInsensitiveLike(string.Format("%{0}%", search.ToLower())));
searchCriterias.Add(Restrictions.On<Item>(x => x.SerialNumber).IsInsensitiveLike(string.Format("%{0}%", search.ToLower())));
Guid guid;
if (Guid.TryParse(search, out guid))
{
searchCriterias.Add(Restrictions.On<User>(x => x.Id).IsLike(guid));
}
query = CommonUtils.AddQueryOverSearchCriterias(query, searchCriterias);
return query;
}
public IMultiCriteria Add <U>(string key, IQueryOver queryOver)
{
return(Add <U>(key, queryOver.RootCriteria));
}
public IList <AuthorizationCode> QueryAuthorization(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, int scrProvider, int addedBy, int authorizedby, string Policynumber, int companyid, bool useDate, DateTime scrFromDate, DateTime scrToDate, int otherFilters, int opmode)
{
IQueryOver <AuthorizationCode, AuthorizationCode> query = _session.QueryOver <AuthorizationCode>().Where(x => x.IsDeleted == false);
if (useDate)
{
DateTime datete = Convert.ToDateTime(scrToDate);
int dd = datete.Day;
int month = datete.Month;
int year = datete.Year;
string time = "23:59";
DateTime enddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", month, dd, year, time));
if (opmode > 0)
{
query.Where(Restrictions.On <AuthorizationCode>(a => a.AdmissionDate).IsBetween(scrFromDate).And(enddate));
}
else
{
query.Where(Restrictions.On <AuthorizationCode>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate));
}
}
if (scrProvider > 0)
{
query.Where(x => x.provider == scrProvider);
}
if (addedBy > 0)
{
query.Where(x => x.generatedby == addedBy);
}
if (authorizedby > 0)
{
query.Where(x => x.Authorizedby == authorizedby);
}
if (!string.IsNullOrEmpty(Policynumber))
{
query.Where(x => x.policyNumber == Policynumber);
}
if (companyid > 0)
{
query.Where(x => x.EnrolleeCompany == Convert.ToString(companyid));
}
if (!string.IsNullOrEmpty(search))
{
search = "%" + search + "%";
query.WhereRestrictionOn(x => x.authorizationCode).IsInsensitiveLike(search);
}
if (opmode > 0)
{
query.Where(x => x.Isadmission);
}
totalRecord = query.RowCount();
totalcountinresult = totalRecord;
IList <AuthorizationCode> list = query.OrderBy(x => x.Id).Desc.Skip(start).Take(lenght).List();
return(list);
}
public IPagedList <PageTemplate> Search(PageTemplateSearchQuery query)
{
IQueryOver <PageTemplate, PageTemplate> queryOver = _session.QueryOver <PageTemplate>();
return(queryOver.Paged(query.Page));
}
public IList <VettingProtocol> GetallVettingProtocol()
{
IQueryOver <VettingProtocol, VettingProtocol> query = _session.QueryOver <VettingProtocol>().Where(x => x.IsDeleted == false);
return(query.List());
}
protected override IQueryOver <DSW.Category, DSW.Category> MappingProjection(IQueryOver <DSW.Category, DSW.Category> queryOver)
{
throw new NotImplementedException();
}
public VettingProtocol getVettingPRotocol(int id)
{
IQueryOver <VettingProtocol, VettingProtocol> query = _session.QueryOver <VettingProtocol>().Where(x => x.IsDeleted == false && x.Id == id);
return(query.SingleOrDefault());
}
private static IQueryOver <Webpage, Webpage> ApplySort(DocumentMetadata metaData, IQueryOver <Webpage, Webpage> query)
{
switch (metaData.SortBy)
{
case SortBy.DisplayOrder:
query = query.OrderBy(webpage => webpage.DisplayOrder).Asc;
break;
case SortBy.DisplayOrderDesc:
query = query.OrderBy(webpage => webpage.DisplayOrder).Desc;
break;
case SortBy.PublishedOn:
query =
query.OrderBy(Projections.Conditional(Restrictions.IsNull(Projections.Property <Webpage>(x => x.PublishOn)), Projections.Constant(1), Projections.Constant(0))).Desc.ThenBy(webpage => webpage.PublishOn)
.Asc;
break;
case SortBy.PublishedOnDesc:
query =
query.OrderBy(Projections.Conditional(Restrictions.IsNull(Projections.Property <Webpage>(x => x.PublishOn)), Projections.Constant(1), Projections.Constant(0))).Desc.ThenBy(webpage => webpage.PublishOn)
.Desc;
break;
case SortBy.CreatedOn:
query = query.OrderBy(webpage => webpage.CreatedOn).Asc;
break;
case SortBy.CreatedOnDesc:
query = query.OrderBy(webpage => webpage.CreatedOn).Desc;
break;
default:
throw new ArgumentOutOfRangeException();
}
return(query);
}
public long GetNumberofAdmission(DateTime start, DateTime End, ref Dictionary <int, int> theplan)
{
IQueryOver <AuthorizationCode, AuthorizationCode> query = _session.QueryOver <AuthorizationCode>().Where(x => x.IsDeleted == false && x.Isadmission == true);
IList <Plan> genericlist = _session.QueryOver <Plan>().Where(x => x.IsDeleted == false).List();
//var placount = new Dictionary<int, int>();
foreach (Plan item in genericlist)
{
theplan.Add(item.Id, 0);
}
if (true)
{
DateTime datete = Convert.ToDateTime(End);
int dd = datete.Day;
int month = datete.Month;
int year = datete.Year;
string time = "23:59";
DateTime enddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", month, dd, year, time));
query.Where(Restrictions.On <AuthorizationCode>(a => a.AdmissionDate).IsBetween(start).And(enddate));
}
//added else
else
{
}
IList <AuthorizationCode> totaladd = query.List();
foreach (AuthorizationCode item in totaladd)
{
Enrollee enrollee = _session.QueryOver <Enrollee>().Where(x => x.IsDeleted == false && x.Id == item.enrolleeID).SingleOrDefault();
if (enrollee != null)
{
Staff staff = _session.QueryOver <Staff>().Where(x => x.Id == enrollee.Staffprofileid).SingleOrDefault();
if (staff != null)
{
CompanyPlan plan = _session.QueryOver <CompanyPlan>().Where(x => x.Id == staff.StaffPlanid).SingleOrDefault();
if (plan != null)
{
int count = theplan[plan.Planid];
count++;
theplan[plan.Planid] = count;
}
else
{
}
}
else
{
}
}
//added else
else
{
}
}
return(query.RowCount());
}
protected override IQueryOver <ResolutionKind, ResolutionKind> DecorateCriteria(IQueryOver <ResolutionKind, ResolutionKind> queryOver)
{
ResolutionKindDocumentSeries resolutionKindDocumentSeries = null;
DocumentSeries documentSeries = null;
return(queryOver
.Left.JoinAlias(x => x.ResolutionKindDocumentSeries, () => resolutionKindDocumentSeries)
.Left.JoinAlias(() => resolutionKindDocumentSeries.DocumentSeries, () => documentSeries)
.Where(x => x.IsActive == 1));
}
public IList <IncomingClaims> QueryAllIncomingClaims(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, int scrProvider, int addedBy, string scrDeliveredBy, int month, int year, int transferedTo, bool useDate, DateTime scrFromDate, DateTime scrToDate, int otherFilters, int trackingid)
{
IQueryOver <IncomingClaims, IncomingClaims> query = _session.QueryOver <IncomingClaims>().Where(x => x.IsDeleted == false);
//if(addedBy > -1)
//{
// query.Where(x => x.receivedBy == addedBy);
//}
if (scrProvider > -1)
{
query.Where(x => x.providerid == scrProvider);
}
//added else
else
{
}
if (!string.IsNullOrEmpty(scrDeliveredBy))
{
query.Where(x => x.deliveredby == scrDeliveredBy.ToUpper() && x.deliveredby == scrDeliveredBy.ToLower());
}
else
{
}
if (useDate)
{
DateTime datete = Convert.ToDateTime(scrToDate);
int dd = datete.Day;
int dmonth = datete.Month;
int dyear = datete.Year;
string time = "23:59";
DateTime enddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", dmonth, dd, dyear, time));
query.Where(Restrictions.On <Enrollee>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate));
}
else
{
}
if (year > -1 && trackingid < 0)
{
query.Where(x => x.year == year);
}
else
{
}
if (month > -1)
{
//query.Where(x => x.month == month);
string monthstr = month.ToString();
query.WhereRestrictionOn(x => x.month_string).IsInsensitiveLike(monthstr, MatchMode.Anywhere);
//if(query.WhereRestrictionOn(x => x.month_string).IsInsensitiveLike(",", MatchMode.Anywhere).RowCount() > 0)
//{
// //contains the coma guys
// foreach(var item in )
// var states = (List<int>)query.WhereRestrictionOn(x => x.month_string).IsInsensitiveLike(",", MatchMode.Anywhere).SelectList(a => a.Select(p => p.Id)).List<int>();
//}
}
//added else
else
{
}
if (trackingid > -1)
{
IList <Entities.ClaimBatch> claimlist = _session.QueryOver <Entities.ClaimBatch>().Where(v => v.Id == trackingid).List <Entities.ClaimBatch>();
List <int> intlist = new List <int>();
if (claimlist.Any())
{
IncomingClaims first = claimlist.FirstOrDefault().IncomingClaims.FirstOrDefault();
if (first != null)
{
intlist.Add(first.Id);
}
//added else
else
{
}
}
query.WhereRestrictionOn(x => x.Id).IsIn(intlist);
}
totalRecord = query.RowCount();
totalcountinresult = totalRecord;
IList <IncomingClaims> list = query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List();
return(list);
}
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);
}
protected override IQueryOver <Invoice, Invoice> BuildQuery(IQueryOver <Invoice, Invoice> query, QueryArguments args)
{
return(query);
}
public TempEnrollee GetTempEnrolleebystaffProfileid(int staffid)
{
IQueryOver <TempEnrollee, TempEnrollee> query = _session.QueryOver <TempEnrollee>().Where(x => x.Staffprofileid == staffid);
return(query.Take(1).SingleOrDefault());
}
public IQueryOver <MassnahmenvorschlagTeilsystemeGIS, MassnahmenvorschlagTeilsystemeGIS> BuildFilter(IQueryOver <MassnahmenvorschlagTeilsystemeGIS, MassnahmenvorschlagTeilsystemeGIS> source)
{
return(source.Where(s => s.Mandant == CurrentMandant));
}
/// <summary>
/// Adds a query to the batch, returning it as an <see cref="IFutureValue{T}"/>.
/// </summary>
/// <param name="batch">The batch.</param>
/// <param name="query">The query.</param>
/// <typeparam name="TResult">The type of the query result elements.</typeparam>
/// <returns>A future query which execution will be handled by the batch.</returns>
public static IFutureValue <TResult> AddAsFutureValue <TResult>(this IQueryBatch batch, IQueryOver <TResult> query)
{
return(AddAsFutureValue(batch, For <TResult>(query)));
}
public IQueryOver <StrassenabschnittGIS, StrassenabschnittGIS> BuildFilter(IQueryOver <StrassenabschnittGIS, StrassenabschnittGIS> source)
{
return(source.Where(s => s.Mandant == CurrentMandant));
}
protected virtual IQueryOver<PagesView, PagesView> FilterQuery(IQueryOver<PagesView, PagesView> query,
PagesFilter request, Junction hasnotSeoDisjunction)
{
PageProperties alias = null;
if (!request.IncludeArchived)
{
query = query.Where(() => !alias.IsArchived);
}
if (request.OnlyMasterPages)
{
query = query.Where(() => alias.IsMasterPage);
}
else if (!request.IncludeMasterPages)
{
query = query.Where(() => !alias.IsMasterPage);
}
if (!string.IsNullOrWhiteSpace(request.SearchQuery))
{
var searchQuery = string.Format("%{0}%", request.SearchQuery);
query = query.Where(Restrictions.Disjunction()
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.Title), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.PageUrl), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.MetaTitle), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.MetaDescription), searchQuery))
.Add(Restrictions.InsensitiveLike(Projections.Property(() => alias.MetaKeywords), searchQuery)));
}
if (request.LanguageId.HasValue)
{
if (request.LanguageId.Value.HasDefaultValue())
{
query = query.Where(Restrictions.IsNull(Projections.Property(() => alias.Language.Id)));
}
else
{
query = query.Where(Restrictions.Eq(Projections.Property(() => alias.Language.Id), request.LanguageId.Value));
}
}
if (request.Tags != null)
{
foreach (var tagKeyValue in request.Tags)
{
var id = tagKeyValue.Key.ToGuidOrDefault();
query = query.WithSubquery.WhereExists(QueryOver.Of<PageTag>().Where(tag => tag.Tag.Id == id && tag.Page.Id == alias.Id).Select(tag => 1));
}
}
if (request.Categories != null)
{
var categories = request.Categories.Select(c => new Guid(c.Key)).Distinct().ToList();
foreach (var category in categories)
{
var childCategories = categoryService.GetChildCategoriesIds(category).ToArray();
query = query.WithSubquery.WhereExists(QueryOver.Of<PageCategory>().Where(cat => !cat.IsDeleted && cat.Page.Id == alias.Id).WhereRestrictionOn(cat => cat.Category.Id).IsIn(childCategories).Select(cat => 1));
}
}
if (request.Status.HasValue)
{
if (request.Status.Value == PageStatusFilterType.OnlyPublished)
{
query = query.Where(() => alias.Status == PageStatus.Published);
}
else if (request.Status.Value == PageStatusFilterType.OnlyUnpublished)
{
query = query.Where(() => alias.Status != PageStatus.Published);
}
else if (request.Status.Value == PageStatusFilterType.ContainingUnpublishedContents)
{
const ContentStatus draft = ContentStatus.Draft;
Root.Models.Content contentAlias = null;
var subQuery = QueryOver.Of<PageContent>()
.JoinAlias(p => p.Content, () => contentAlias)
.Where(pageContent => pageContent.Page.Id == alias.Id)
.And(() => contentAlias.Status == draft)
.And(() => !contentAlias.IsDeleted)
.Select(pageContent => 1);
query = query.WithSubquery.WhereExists(subQuery);
}
}
if (request.SeoStatus.HasValue)
{
if (request.SeoStatus.Value == SeoStatusFilterType.HasNotSeo)
{
query = query.Where(hasnotSeoDisjunction);
}
else
{
query = query.Where(Restrictions.Not(hasnotSeoDisjunction));
}
}
if (!string.IsNullOrWhiteSpace(request.Layout))
{
Guid id;
var length = request.Layout.Length - 2;
if (request.Layout.StartsWith("m-") && Guid.TryParse(request.Layout.Substring(2, length), out id))
{
query = query.Where(() => alias.MasterPage.Id == id);
}
if (request.Layout.StartsWith("l-") && Guid.TryParse(request.Layout.Substring(2, length), out id))
{
query = query.Where(() => alias.Layout.Id == id);
}
}
if (request.ContentId.HasValue)
{
Root.Models.Content contentAlias = null;
ChildContent childContentAlias = null;
HtmlContent htmlContentAlias = null;
PageContent pageContentAlias = null;
var htmlChildContentSubQuery =
QueryOver.Of(() => htmlContentAlias)
.JoinAlias(h => h.ChildContents, () => childContentAlias)
.Where(() => htmlContentAlias.Id == contentAlias.Id)
.And(() => childContentAlias.Child.Id == request.ContentId.Value)
.Select(pageContent => 1);
var pageContentSubQuery = QueryOver.Of(() => pageContentAlias)
.JoinAlias(() => pageContentAlias.Content, () => contentAlias)
.And(() => pageContentAlias.Page.Id == alias.Id)
.And(() => !contentAlias.IsDeleted)
.And(() => !pageContentAlias.IsDeleted)
.And(Restrictions.Or(
Restrictions.Where(() => contentAlias.Id == request.ContentId.Value),
Subqueries.WhereExists(htmlChildContentSubQuery)
))
.Select(pageContent => 1);
query = query.WithSubquery.WhereExists(pageContentSubQuery);
}
return query;
}
private IQueryOver<ProductReview, ProductReview> GetBaseProductVariantReviewsQuery(
IQueryOver<ProductReview, ProductReview> query, ProductVariant productVariant)
{
return query.Where(review => review.ProductVariant.Id == productVariant.Id && review.Approved == true);
}