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()); }