/// <summary>
/// Gets the ORDER BY clause for the select query while searching communities and contents.
/// </summary>
/// <param name="searchQueryDetails">Details about search like filters, sorting and pagination</param>
/// <returns>Order by condition to be used in the query</returns>
private static string GetSearchOrderBy(SearchQueryDetails searchQueryDetails)
{
// default order by is Rating.
var orderBy = string.Empty;
switch (searchQueryDetails.SortBy)
{
case SearchSortBy.Categories:
orderBy = " ORDER BY Category";
break;
case SearchSortBy.DistributedBy:
orderBy = " ORDER BY DistributedBy";
break;
case SearchSortBy.ContentType:
orderBy = " ORDER BY ContentType";
break;
case SearchSortBy.None:
case SearchSortBy.Rating:
default:
orderBy = " ORDER BY Rating DESC, RatedPeople DESC";
break;
}
return(orderBy);
}
/// <summary>
/// Gets the count of items which are satisfying the given search condition.
/// </summary>
/// <param name="searchText">Text to be searched</param>
/// <param name="userId">User who is searching</param>
/// <param name="searchQueryDetails">Details about search like filters, sorting and pagination</param>
/// <returns>Number of items satisfying the given search condition</returns>
public int SearchCount(string searchText, long?userId, SearchQueryDetails searchQueryDetails)
{
var query = new StringBuilder("SELECT COUNT(*) FROM SearchView WHERE ");
// Gets the WHERE clause for the search query
query = query.Append(GetSearchQueryConditions(searchText, userId, searchQueryDetails));
return(EarthOnlineDbContext.Database.SqlQuery <int>(query.ToString()).FirstOrDefault());
}
/// <summary>
/// This returns the results for Search Result View
/// </summary>
/// <param name="searchText">search Text</param>
/// <param name="selectedTab">selected Tab Text</param>
/// <returns>EntityViewModel collection</returns>
private async Task <JsonResult> GetSearchResults(string searchText, string selectedTab, int currentPage, SearchViewModel searchQuery, SearchSortBy sortBy = SearchSortBy.Rating)
{
ViewData["SearchText"] = searchText = (string.IsNullOrWhiteSpace(searchText) || searchText.ToLower(CultureInfo.CurrentCulture).Equals(Resources.DefaultSearchText.ToLower(CultureInfo.CurrentCulture))) ? string.Empty : searchText;
ViewData["SearchMessage"] = string.Empty;
IEnumerable <EntityViewModel> results = null;
var pageDetails = new PageDetails(currentPage);
if (!string.IsNullOrWhiteSpace(searchText))
{
pageDetails.ItemsPerPage = searchQuery.ResultsPerPage;
var searchQueryDetails = new SearchQueryDetails();
if (searchQuery.ContentTypeFilter != null)
{
foreach (var contentFilterValue in searchQuery.ContentTypeFilter.Split(','))
{
searchQueryDetails.ContentTypeFilter.Add(Convert.ToInt32(contentFilterValue, CultureInfo.CurrentCulture));
}
}
if (searchQuery.CategoryFilter != null)
{
foreach (var categoryFilterValue in searchQuery.CategoryFilter.Split(','))
{
searchQueryDetails.CategoryFilter.Add(Convert.ToInt32(categoryFilterValue, CultureInfo.CurrentCulture));
}
}
searchQueryDetails.SortBy = searchQuery.SortBy.ToEnum <string, SearchSortBy>(sortBy);
results = await _searchService.SimpleSearch(searchText.Replace("'", "''"), CurrentUserId, pageDetails, searchQueryDetails);
// If the total count of items are less than the selected per page items, select previous per page items
//ViewData["CurrentPage"] = currentPage;
//ViewData["TotalPage"] = pageDetails.TotalPages;
//ViewData["TotalCount"] = pageDetails.TotalCount;
}
return(Json(new{
searchResults = results,
pageInfo = pageDetails
}));
}
/// <summary>
/// Searches the presence of the given text in communities and contents tables (Name, description and tags fields).
/// </summary>
/// <param name="searchText">Text to be searched</param>
/// <param name="userId">Id of the user who is accessing</param>
/// <param name="pageDetails">Details about the pagination</param>
/// <returns>Communities/Contents which are having the search text</returns>
public async Task <IEnumerable <EntityViewModel> > SimpleSearch(string searchText, long userId, PageDetails pageDetails, SearchQueryDetails searchQueryDetails)
{
// Make sure pageDetails is not null
this.CheckNotNull(() => new { pageDetails });
IList <EntityViewModel> searchResults = new List <EntityViewModel>();
// User Id to be used while searching. This will be used to see whether user is having permission or not.
long?searchUserId = userId;
if (_userRepository.GetUserRole(userId, null) == UserRole.SiteAdmin)
{
// Set user id as 0 for Site Administrators, so that role check will be ignored for private communities
searchUserId = null;
}
// Gets the total communities/contents satisfying the search condition
pageDetails.TotalCount = _searchViewRepository.SearchCount(searchText, searchUserId, searchQueryDetails);
// Set the total pages for the search term
pageDetails.TotalPages = (pageDetails.TotalCount / pageDetails.ItemsPerPage) + ((pageDetails.TotalCount % pageDetails.ItemsPerPage == 0) ? 0 : 1);
// Get the skip count and take count for the given page
var skipCount = (pageDetails.CurrentPage - 1) * pageDetails.ItemsPerPage;
var takeCount = pageDetails.ItemsPerPage;
var results = await _searchViewRepository.SearchAsync(searchText, searchUserId, skipCount, takeCount, searchQueryDetails);
foreach (var entity in results)
{
EntityViewModel entityViewModel;
if (entity.Entity == EntityType.Content.ToString())
{
// In case of Content, need to create ContentViewModel instance which is expected by SearchResultView.
entityViewModel = new ContentViewModel();
// This is needed to avoid the FxCop warning.
var contentViewModel = entityViewModel as ContentViewModel;
// Setting the properties which are specific to Contents.
contentViewModel.IsLink = entity.ContentType == (int)ContentTypes.Link;
contentViewModel.ContentUrl = entity.ContentType == (int)ContentTypes.Link ? entity.ContentUrl : string.Empty;
contentViewModel.ContentAzureID = entity.ContentType == (int)ContentTypes.Link ? Guid.Empty : entity.ContentAzureID;
}
else
{
entityViewModel = new EntityViewModel();
}
Mapper.Map(entity, entityViewModel);
searchResults.Add(entityViewModel);
}
// TODO: Need to send the results based on relevance with following order: Title, Description, Tags and Parent.
return(searchResults);
}
/// <summary>
/// Gets the WHERE clause for the select query to search communities and contents.
/// </summary>
/// <param name="searchText">Text to be searched</param>
/// <param name="userId">User who is searching</param>
/// <param name="searchQueryDetails">Details about search like filters, sorting and pagination</param>
/// <returns>Where condition to be used in the query</returns>
private static string GetSearchQueryConditions(string searchText, long?userId, SearchQueryDetails searchQueryDetails)
{
var queryCondition = new StringBuilder(
string.Format(CultureInfo.InvariantCulture, "(Name LIKE '%{0}%' OR Description LIKE '%{1}%' OR DistributedBy LIKE '%{2}%' OR Producer LIKE '%{3}%' OR Tags LIKE '%{4}%' OR Citation LIKE '%{5}%')", searchText, searchText, searchText, searchText, searchText, searchText));
if (userId != null)
{
queryCondition = queryCondition.Append(string.Format(CultureInfo.InvariantCulture, " AND (AccessType = 'Public' OR Users Like '~{0}~')", userId));
}
if (searchQueryDetails.CategoryFilter.Count > 0)
{
var categoryList = string.Join(",", searchQueryDetails.CategoryFilter);
queryCondition = queryCondition.Append(string.Format(CultureInfo.InvariantCulture, " AND (Category IN ({0}))", categoryList));
}
if (searchQueryDetails.ContentTypeFilter.Count > 0)
{
var contentTypeList = string.Join(",", searchQueryDetails.ContentTypeFilter);
queryCondition = queryCondition.Append(string.Format(CultureInfo.InvariantCulture, " AND (ContentType IN ({0}))", contentTypeList));
}
return(queryCondition.ToString());
}
public async Task <IEnumerable <SearchView> > SearchAsync(string searchText, long?userId, int skipCount, int takeCount, SearchQueryDetails searchQueryDetails)
{
var query = new StringBuilder("SELECT * FROM SearchView WHERE ");
// Gets the WHERE clause for the search query
query = query.Append(GetSearchQueryConditions(searchText, userId, searchQueryDetails));
// Gets the ORDER BY clause for the search query
query = query.Append(GetSearchOrderBy(searchQueryDetails));
var results =
await EarthOnlineDbContext.Database.SqlQuery <SearchView>(query.ToString()).ToListAsync();
return(results.Skip(skipCount)
.Take(takeCount));
}
