private void DoFulltext()
{
// getting (and setting) params for fulltext ->
string[] includes = new string[0];
string[] excludes = new string[0];
FulltextHelper.CreateIncludesAndExcludes(out includes, out excludes, SchemaTableFulltext, SchemaTableFulltextExclude);
var listsTable = FulltextHelper.CreateFulltextValueLists(includes, excludes, 0);
FulltextHelper.CreateIncludesAndExcludes(out includes, out excludes, SchemaColumnFulltext, SchemaColumnFulltextExclude);
var listsColumn = FulltextHelper.CreateFulltextValueLists(includes, excludes, 1);
var includesList = listsTable[0].Concat(listsColumn[0]).ToList();
var excludesList = listsTable[1].Concat(listsColumn[1]).ToList();
var startingVisibility = includesList.Count() == 0;
if (startingVisibility && excludesList.Count() > 0)
{
startingVisibility = false;
}
// own fulltext action ->
FulltextHelper.DoFulltext(false, SelectedSprint.DbSchemaTables, includesList, excludesList, 0);
}
private void DoFilter()
{
SelectedExcel = null;
SelectedTab = null;
SelectedDbTable = null;
SelectedDbColumn = null;
ExcelVisible = false;
TabVisible = false;
TableVisible = false;
ColumnVisible = false;
// getting (and setting) params for fulltext ->
string[] includes = new string[0];
string[] excludes = new string[0];
FulltextHelper.CreateIncludesAndExcludes(out includes, out excludes, ExcelFulltext, ExcelFulltextExclude);
var listsExcel = FulltextHelper.CreateFulltextValueLists(includes, excludes, 0);
FulltextHelper.CreateIncludesAndExcludes(out includes, out excludes, SheetFulltext, SheetFulltextExclude);
var listsSheet = FulltextHelper.CreateFulltextValueLists(includes, excludes, 1);
FulltextHelper.CreateIncludesAndExcludes(out includes, out excludes, TableFulltext, TableFulltextExclude);
var listsTable = FulltextHelper.CreateFulltextValueLists(includes, excludes, 2);
FulltextHelper.CreateIncludesAndExcludes(out includes, out excludes, ColumnFulltext, ColumnFulltextExclude);
var listsColumn = FulltextHelper.CreateFulltextValueLists(includes, excludes, 3);
var includesList = listsExcel[0].Concat(listsSheet[0]).Concat(listsTable[0]).Concat(listsColumn[0]).ToList();
var excludesList = listsExcel[1].Concat(listsSheet[1]).Concat(listsTable[1]).Concat(listsColumn[1]).ToList();
//var startingVisibility = includesList.Count() == 0;
//if (excludesList.Count() > 0) startingVisibility = true;
// own fulltext action ->
FulltextHelper.DoFulltext(HasIncludesBiggerPriority, NodesExcel, includesList, excludesList, 0);
}
private string GetBaseJobPostQuery(string selectStatement, RepositoryJobPostSearchInputModel searchInputModel, bool shouldSort, out object[] sqlParams)
{
var sqlListParams = new List <object>();
StringBuilder sbQuery = new StringBuilder(string.Format(@"
{0}
FROM {1} jp
JOIN {2} et ON jp.EmploymentTypeId = et.Id
JOIN {3} jc ON jp.CategoryId = jc.Id
JOIN {4} jt ON jp.TitleId = jt.Id
JOIN {5} l ON jp.LocationId = l.Id
WHERE 1=1
",
selectStatement,
DbHelpers.JobPostsTableName,
DbHelpers.EmploymentTypeTableName,
DbHelpers.JobCategoryTableName,
DbHelpers.JobTitleTableName,
DbHelpers.LocationTableName));
var keywordSearchType = searchInputModel.KeywordSearchType;
var keyword = searchInputModel.Keyword;
if ((keywordSearchType != null) && (!string.IsNullOrWhiteSpace(keyword)))
{
keyword = FulltextHelper.PreprocessForFulltextSearch(keyword);
string tablePrefixAndColumn = null;
switch (keywordSearchType)
{
case KeywordSearchType.JobCategory:
tablePrefixAndColumn = "jc.Name";
break;
case KeywordSearchType.Location:
tablePrefixAndColumn = "l.Address";
break;
case KeywordSearchType.Title:
tablePrefixAndColumn = "jt.Name";
break;
}
if (!string.IsNullOrEmpty(tablePrefixAndColumn))
{
sbQuery.AppendFormat(@"
AND (CONTAINS({0}, @keyword))
", tablePrefixAndColumn);
sqlListParams.Add(new SqlParameter("keyword", keyword));
}
}
this.AppendIdListCheckIfNotEmpty(sbQuery, "jc.Id", searchInputModel.CategoryIds);
this.AppendIdListCheckIfNotEmpty(sbQuery, "et.Id", searchInputModel.EmploymentTypeIds);
this.AppendIdListCheckIfNotEmpty(sbQuery, "l.Id", searchInputModel.LocationIds);
if (shouldSort)
{
var sortOrder = searchInputModel.SortOrder;
if (sortOrder == null)
{
sortOrder = JobPortal.Common.Model.SortOrder.ByCreatedDate;
}
string sortColumnName = "";
if (sortOrder == JobPortal.Common.Model.SortOrder.ByViews)
{
sortColumnName = "jp.Views";
}
else
{
sortColumnName = "jp.CreatedAt";
}
sbQuery.AppendFormat(@"
ORDER BY {0} DESC
OFFSET {1} ROWS
FETCH NEXT {2} ROWS ONLY
",
sortColumnName,
searchInputModel.Skip,
searchInputModel.Take);
}
sqlParams = sqlListParams.ToArray();
return(sbQuery.ToString());
}