public virtual IQueryable <Article> PrepareArticleSearchQuery(
ArticleSearchContext ctx,
IEnumerable <int> allowedUserRolesIds = null,
bool searchLocalizedValue = false)
{
return(PrepareArticleSearchQuery <Article>(ctx, x => x, allowedUserRolesIds, searchLocalizedValue));
}
public virtual int CountArticles(ArticleSearchContext ctx)
{
Guard.ArgumentNotNull(() => ctx);
var query = this.PrepareArticleSearchQuery(ctx);
return(query.Distinct().Count());
}
public virtual IQueryable <TResult> PrepareArticleSearchQuery <TResult>(
ArticleSearchContext ctx,
Expression <Func <Article, TResult> > selector,
IEnumerable <int> allowedUserRolesIds = null,
bool searchLocalizedValue = false)
{
Guard.ArgumentNotNull(() => ctx);
Guard.ArgumentNotNull(() => selector);
if (allowedUserRolesIds == null)
{
allowedUserRolesIds = _workContext.CurrentUser.UserRoles.Where(cr => cr.Active).Select(cr => cr.Id).ToList();
}
// articles
var query = ctx.Query ?? _articleRepository.Table;
query = query.Where(p => !p.Deleted);
if (!ctx.ShowHidden)
{
query = query.Where(p => p.StatusFormat == StatusFormat.Norma);
}
if (ctx.IsHot.HasValue)
{
query = query.Where(p => p.IsHot == ctx.IsHot.Value);
}
if (ctx.IsRed.HasValue)
{
query = query.Where(p => p.IsRed == ctx.IsRed.Value);
}
if (ctx.IsSlide.HasValue)
{
query = query.Where(p => p.IsSlide == ctx.IsSlide.Value);
}
if (ctx.IsTop.HasValue)
{
query = query.Where(p => p.IsTop == ctx.IsTop.Value);
}
if (ctx.ArticleIds != null && ctx.ArticleIds.Count > 0)
{
query = query.Where(x => ctx.ArticleIds.Contains(x.Id));
}
//The function 'CurrentUtcDateTime' is not supported by SQL Server Compact.
//That's why we pass the date value
var nowUtc = DateTime.UtcNow;
if (!ctx.ShowHidden)
{
//available dates
query = query.Where(p =>
(!p.StartDateUtc.HasValue || p.StartDateUtc.Value < nowUtc) &&
(!p.EndDateUtc.HasValue || p.EndDateUtc.Value > nowUtc));
}
// searching by keyword
if (!String.IsNullOrWhiteSpace(ctx.Keywords))
{
query = from p in query
join lp in _localizedPropertyRepository.Table on p.Id equals lp.EntityId into p_lp
from lp in p_lp.DefaultIfEmpty()
// from pt in p.ArticleTags.DefaultIfEmpty()
where (p.Title.Contains(ctx.Keywords)) ||
(ctx.SearchDescriptions && p.ShortContent.Contains(ctx.Keywords)) ||
(ctx.SearchDescriptions && p.FullContent.Contains(ctx.Keywords)) ||
// (ctx.SearchArticleTags && pt.Name.Contains(ctx.Keywords)) ||
//localized values
(searchLocalizedValue && lp.LanguageId == ctx.LanguageId && lp.LocaleKeyGroup == "Article" && lp.LocaleKey == "Name" && lp.LocaleValue.Contains(ctx.Keywords)) ||
(ctx.SearchDescriptions && searchLocalizedValue && lp.LanguageId == ctx.LanguageId && lp.LocaleKeyGroup == "Article" && lp.LocaleKey == "ShortContent" && lp.LocaleValue.Contains(ctx.Keywords)) ||
(ctx.SearchDescriptions && searchLocalizedValue && lp.LanguageId == ctx.LanguageId && lp.LocaleKeyGroup == "Article" && lp.LocaleKey == "FullContent" && lp.LocaleValue.Contains(ctx.Keywords))
//UNDONE search localized values in associated article tags
select p;
}
//if (!ctx.ShowHidden && !QuerySettings.IgnoreAcl)
//{
// query =
// from p in query
// join acl in _aclRepository.Table on new { pid = p.Id, pname = "Article" } equals new { pid = acl.EntityId, pname = acl.EntityName } into pacl
// from acl in pacl.DefaultIfEmpty()
// where !p.SubjectToAcl || allowedUserRolesIds.Contains(acl.UserRoleId)
// select p;
//}
if (ctx.SiteId > 0 && !QuerySettings.IgnoreMultiSite)
{
query =
from p in query
join sm in _siteMappingRepository.Table on new { pid = p.Id, pname = "Article" } equals new { pid = sm.EntityId, pname = sm.EntityName } into psm
from sm in psm.DefaultIfEmpty()
where !p.LimitedToSites || ctx.SiteId == sm.SiteId
select p;
}
// category filtering
//if (ctx.WithoutCategories)
//{
// query = query.Where(x => x.ArticleCategories.Count == 0);
//}
//else if (ctx.CategoryIds != null && ctx.CategoryIds.Count > 0)
//{
// //search in subcategories
// if (ctx.MatchAllcategories)
// {
// query = from p in query
// where ctx.CategoryIds.All(i => p.ArticleCategories.Any(p2 => p2.CategoryId == i))
// from pc in p.ArticleCategories
// where (!ctx.FeaturedArticles.HasValue || ctx.FeaturedArticles.Value == pc.IsFeaturedArticle)
// select p;
// }
// else
// {
// query = from p in query
// from pc in p.ArticleCategories.Where(pc => ctx.CategoryIds.Contains(pc.CategoryId))
// where (!ctx.FeaturedArticles.HasValue || ctx.FeaturedArticles.Value == pc.IsFeaturedArticle)
// select p;
// }
//}
// related articles filtering
//if (relatedToArticleId > 0)
//{
// query = from p in query
// join rp in _relatedArticleRepository.Table on p.Id equals rp.ArticleId2
// where (relatedToArticleId == rp.ArticleId1)
// select p;
//}
// tag filtering
//if (ctx.ArticleTagId > 0)
//{
// query = from p in query
// from pt in p.ArticleTags.Where(pt => pt.Id == ctx.ArticleTagId)
// select p;
//}
return(query.Select(selector));
}
public virtual IPagedList <Article> SearchArticles(ArticleSearchContext ctx)
{
_eventPublisher.Publish(new ArticlesSearchingEvent(ctx));
//search by keyword
bool searchLocalizedValue = false;
if (ctx.LanguageId > 0)
{
if (ctx.ShowHidden)
{
searchLocalizedValue = true;
}
else
{
//ensure that we have at least two published languages
var totalPublishedLanguages = _languageService.GetAllLanguages(siteId: ctx.SiteId).Count;
searchLocalizedValue = totalPublishedLanguages >= 2;
}
}
//validate "categoryIds" parameter
if (ctx.CategoryIds != null && ctx.CategoryIds.Contains(0))
{
ctx.CategoryIds.Remove(0);
}
//Access control list. Allowed customer roles
var allowedUserRolesIds = _workContext.CurrentUser.UserRoles
.Where(cr => cr.Active).Select(cr => cr.Id).ToList();
if (_commonSettings.UseStoredProceduresIfSupported && _dataProvider.StoredProceduresSupported)
{
//sited procedures are enabled and supported by the database.
//It's much faster than the LINQ implementation below
#region Use sited procedure
//pass categry identifiers as comma-delimited string
string commaSeparatedCategoryIds = "";
if (ctx.CategoryIds != null && !ctx.WithoutCategories)
{
for (int i = 0; i < ctx.CategoryIds.Count; i++)
{
commaSeparatedCategoryIds += ctx.CategoryIds[i].ToString();
if (i != ctx.CategoryIds.Count - 1)
{
commaSeparatedCategoryIds += ",";
}
}
}
//pass customer role identifiers as comma-delimited string
string commaSeparatedAllowedUserRoleIds = "";
for (int i = 0; i < allowedUserRolesIds.Count; i++)
{
commaSeparatedAllowedUserRoleIds += allowedUserRolesIds[i].ToString();
if (i != allowedUserRolesIds.Count - 1)
{
commaSeparatedAllowedUserRoleIds += ",";
}
}
//some databases don't support int.MaxValue
if (ctx.PageSize == int.MaxValue)
{
ctx.PageSize = int.MaxValue - 1;
}
//prepare parameters
var pCategoryIds = _dataProvider.GetParameter();
pCategoryIds.ParameterName = "CategoryIds";
pCategoryIds.Value = commaSeparatedCategoryIds != null ? (object)commaSeparatedCategoryIds : DBNull.Value;
pCategoryIds.DbType = DbType.String;
var pSiteId = _dataProvider.GetParameter();
pSiteId.ParameterName = "SiteId";
pSiteId.Value = QuerySettings.IgnoreMultiSite ? 0 : ctx.SiteId;
pSiteId.DbType = DbType.Int32;
var pArticleTagId = _dataProvider.GetParameter();
pArticleTagId.ParameterName = "ArticleTagId";
pArticleTagId.Value = ctx.ArticleTagId;
pArticleTagId.DbType = DbType.Int32;
var pFeaturedArticles = _dataProvider.GetParameter();
pFeaturedArticles.ParameterName = "FeaturedArticles";
pFeaturedArticles.Value = ctx.FeaturedArticles.HasValue ? (object)ctx.FeaturedArticles.Value : DBNull.Value;
pFeaturedArticles.DbType = DbType.Boolean;
var pKeywords = _dataProvider.GetParameter();
pKeywords.ParameterName = "Keywords";
pKeywords.Value = ctx.Keywords != null ? (object)ctx.Keywords : DBNull.Value;
pKeywords.DbType = DbType.String;
var pSearchDescriptions = _dataProvider.GetParameter();
pSearchDescriptions.ParameterName = "SearchDescriptions";
pSearchDescriptions.Value = ctx.SearchDescriptions;
pSearchDescriptions.DbType = DbType.Boolean;
var pSearchArticleTags = _dataProvider.GetParameter();
pSearchArticleTags.ParameterName = "SearchArticleTags";
pSearchArticleTags.Value = ctx.SearchDescriptions;
pSearchArticleTags.DbType = DbType.Boolean;
var pUseFullTextSearch = _dataProvider.GetParameter();
pUseFullTextSearch.ParameterName = "UseFullTextSearch";
pUseFullTextSearch.Value = _commonSettings.UseFullTextSearch;
pUseFullTextSearch.DbType = DbType.Boolean;
var pFullTextMode = _dataProvider.GetParameter();
pFullTextMode.ParameterName = "FullTextMode";
pFullTextMode.Value = (int)_commonSettings.FullTextMode;
pFullTextMode.DbType = DbType.Int32;
var pLanguageId = _dataProvider.GetParameter();
pLanguageId.ParameterName = "LanguageId";
pLanguageId.Value = searchLocalizedValue ? ctx.LanguageId : 0;
pLanguageId.DbType = DbType.Int32;
var pOrderBy = _dataProvider.GetParameter();
pOrderBy.ParameterName = "OrderBy";
pOrderBy.Value = (int)ctx.OrderBy;
pOrderBy.DbType = DbType.Int32;
var pAllowedUserRoleIds = _dataProvider.GetParameter();
pAllowedUserRoleIds.ParameterName = "AllowedUserRoleIds";
pAllowedUserRoleIds.Value = commaSeparatedAllowedUserRoleIds;
pAllowedUserRoleIds.DbType = DbType.String;
var pPageIndex = _dataProvider.GetParameter();
pPageIndex.ParameterName = "PageIndex";
pPageIndex.Value = ctx.PageIndex;
pPageIndex.DbType = DbType.Int32;
var pPageSize = _dataProvider.GetParameter();
pPageSize.ParameterName = "PageSize";
pPageSize.Value = ctx.PageSize;
pPageSize.DbType = DbType.Int32;
var pShowHidden = _dataProvider.GetParameter();
pShowHidden.ParameterName = "ShowHidden";
pShowHidden.Value = ctx.ShowHidden;
pShowHidden.DbType = DbType.Boolean;
var pWithoutCategories = _dataProvider.GetParameter();
pWithoutCategories.ParameterName = "WithoutCategories";
pWithoutCategories.Value = ctx.WithoutCategories;
pWithoutCategories.DbType = DbType.Boolean;
#region »ù´¡ÊôÐÔ¹ýÂË
var IsHot = _dataProvider.GetParameter();
IsHot.ParameterName = "IsHot";
IsHot.Value = ctx.IsHot.HasValue ? (object)ctx.IsHot.Value : DBNull.Value;
IsHot.DbType = DbType.Boolean;
var IsRed = _dataProvider.GetParameter();
IsRed.ParameterName = "IsRed";
IsRed.Value = ctx.IsRed.HasValue ? (object)ctx.IsRed.Value : DBNull.Value;
IsRed.DbType = DbType.Boolean;
var IsTop = _dataProvider.GetParameter();
IsTop.ParameterName = "IsTop";
IsTop.Value = ctx.IsTop.HasValue ? (object)ctx.IsTop.Value : DBNull.Value;
IsTop.DbType = DbType.Boolean;
var IsSlide = _dataProvider.GetParameter();
IsSlide.ParameterName = "IsSlide";
IsSlide.Value = ctx.IsSlide.HasValue ? (object)ctx.IsSlide.Value : DBNull.Value;
IsSlide.DbType = DbType.Boolean;
#endregion
var pTotalRecords = _dataProvider.GetParameter();
pTotalRecords.ParameterName = "TotalRecords";
pTotalRecords.Direction = ParameterDirection.Output;
pTotalRecords.DbType = DbType.Int32;
//invoke sited procedure
var articles = _dbContext.ExecuteStoredProcedureList <Article>(
"ArticleLoadAllPaged",
pCategoryIds,
pSiteId,
pArticleTagId,
pFeaturedArticles,
pKeywords,
pSearchDescriptions,
pSearchArticleTags,
pUseFullTextSearch,
pFullTextMode,
pLanguageId,
pOrderBy,
pAllowedUserRoleIds,
pPageIndex,
pPageSize,
pShowHidden,
IsHot,
IsRed,
IsTop,
IsSlide,
pWithoutCategories,
pTotalRecords);
// return articles
int totalRecords = (pTotalRecords.Value != DBNull.Value) ? Convert.ToInt32(pTotalRecords.Value) : 0;
return(new PagedList <Article>(articles, ctx.PageIndex, ctx.PageSize, totalRecords));
#endregion
}
else
{
//sited procedures aren't supported. Use LINQ
#region Search articles
var query = this.PrepareArticleSearchQuery(ctx, allowedUserRolesIds, searchLocalizedValue);
// only distinct articles (group by ID)
// if we use standard Distinct() method, then all fields will be compared (low performance)
// it'll not work in SQL Server Compact when searching articles by a keyword)
query = from p in query
group p by p.Id into pGroup
orderby pGroup.Key
select pGroup.FirstOrDefault();
//sort articles
if (ctx.OrderBy == ArticleSortingEnum.Position && ctx.CategoryIds != null && ctx.CategoryIds.Count > 0)
{
//category position
var firstCategoryId = ctx.CategoryIds[0];
// query = query.OrderBy(p => p.ArticleCategories.Where(pc => pc.CategoryId == firstCategoryId).FirstOrDefault().DisplayOrder);
}
else if (ctx.OrderBy == ArticleSortingEnum.Position)
{
//parent article specified (sort associated articles)
query = query.OrderBy(p => p.DisplayOrder);
}
else if (ctx.OrderBy == ArticleSortingEnum.Position)
{
//otherwise sort by name
query = query.OrderBy(p => p.Title);
}
else if (ctx.OrderBy == ArticleSortingEnum.NameAsc)
{
//Name: A to Z
query = query.OrderBy(p => p.Title);
}
else if (ctx.OrderBy == ArticleSortingEnum.NameDesc)
{
//Name: Z to A
query = query.OrderByDescending(p => p.Title);
}
else if (ctx.OrderBy == ArticleSortingEnum.CreatedOn)
{
//creation date
query = query.OrderByDescending(p => p.CreatedOnUtc);
}
else if (ctx.OrderBy == ArticleSortingEnum.CreatedOnAsc)
{
// creation date: old to new
query = query.OrderBy(p => p.CreatedOnUtc);
}
else
{
//actually this code is not reachable
query = query.OrderBy(p => p.Title);
}
var articles = new PagedList <Article>(query, ctx.PageIndex, ctx.PageSize);
return(articles);
#endregion
}
}
public ArticlesSearchingEvent(ArticleSearchContext ctx)
{
SearchContext = ctx;
}
