protected IEnumerable <SearchCondition> BuildWhoConditions(
[NotNull] string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
bool searchDisplayName)
{
CodeContracts.VerifyNotNull(toSearchFromWho, "toSearchFromWho");
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
var conditions = new List <SearchCondition>();
string conditionSql = string.Empty;
var conditionType = SearchConditionType.AND;
if (searchFromWhoMethod == SearchWhatFlags.AnyWords)
{
conditionType = SearchConditionType.OR;
}
var wordList = new List <string> {
toSearchFromWho
};
if (searchFromWhoMethod == SearchWhatFlags.AllWords || searchFromWhoMethod == SearchWhatFlags.AnyWords)
{
wordList =
toSearchFromWho.Replace(@"""", string.Empty).Split(' ').Where(x => x.IsSet()).Select(x => x.Trim()).ToList();
}
foreach (string word in wordList)
{
int userId;
if (int.TryParse(word, out userId))
{
conditionSql = "c.UserID IN ({0})".FormatWith(userId);
}
else
{
if (searchFromWhoMethod == SearchWhatFlags.ExactMatch)
{
conditionSql = "(c.Username IS NULL AND b.{1} = N'{0}') OR (c.Username = N'{0}')".FormatWith(
word,
searchDisplayName ? "DisplayName" : "Name");
}
else
{
conditionSql = "(c.Username IS NULL AND b.{1} LIKE N'%{0}%') OR (c.Username LIKE N'%{0}%')".FormatWith(
word,
searchDisplayName ? "DisplayName" : "Name");
}
}
conditions.Add(new SearchCondition {
Condition = conditionSql, ConditionType = conditionType
});
}
return(conditions);
}
/// <summary>
/// Initializes a new instance of the <see cref="CompleteSearchContext" /> class.
/// </summary>
/// <param name="toSearchWhat">To search what.</param>
/// <param name="toSearchFromWho">To search from who.</param>
/// <param name="searchFromWhoMethod">The search from who method.</param>
/// <param name="searchWhatMethod">The search what method.</param>
/// <param name="userID">The user identifier.</param>
/// <param name="boardID">The board identifier.</param>
/// <param name="maxResults">The maximum results.</param>
/// <param name="useFullText">if set to <c>true</c> [use full text].</param>
/// <param name="searchDisplayName">if set to <c>true</c> [search display name].</param>
/// <param name="searchTitleOnly">if set to <c>true</c> [search title only].</param>
/// <param name="forumIds">The forum ids.</param>
public CompleteSearchContext(
string toSearchWhat,
string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userID,
int boardID,
int maxResults,
bool useFullText,
bool searchDisplayName,
bool searchTitleOnly,
int[] forumIds = null)
{
if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
this.ToSearchWhat = toSearchWhat;
this.ToSearchFromWho = toSearchFromWho;
this.SearchFromWhoMethod = searchFromWhoMethod;
this.SearchWhatMethod = searchWhatMethod;
this.UserID = userID;
this.BoardID = boardID;
this.MaxResults = maxResults;
this.UseFullText = useFullText;
this.SearchDisplayName = searchDisplayName;
this.SearchTitleOnly = searchTitleOnly;
this.ForumIDs = forumIds;
}
/// <summary>
/// Initializes a new instance of the <see cref="CompleteSearchContext" /> class.
/// </summary>
/// <param name="toSearchWhat">To search what.</param>
/// <param name="toSearchFromWho">To search from who.</param>
/// <param name="searchFromWhoMethod">The search from who method.</param>
/// <param name="searchWhatMethod">The search what method.</param>
/// <param name="userID">The user identifier.</param>
/// <param name="boardID">The board identifier.</param>
/// <param name="maxResults">The maximum results.</param>
/// <param name="useFullText">if set to <c>true</c> [use full text].</param>
/// <param name="searchDisplayName">if set to <c>true</c> [search display name].</param>
/// <param name="startFromForumId">The start from forum identifier.</param>
/// <param name="searchTitleOnly">if set to <c>true</c> [search title only].</param>
public CompleteSearchContext(
string toSearchWhat,
string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userID,
int boardID,
int maxResults,
bool useFullText,
bool searchDisplayName,
int startFromForumId,
bool searchTitleOnly)
: this(
toSearchWhat,
toSearchFromWho,
searchFromWhoMethod,
searchWhatMethod,
userID,
boardID,
maxResults,
useFullText,
searchDisplayName,
searchTitleOnly)
{
this.ForumIDs = GetForumIDsStartingWith(startFromForumId, boardID, userID);
}
public CompleteSearchContext(
string toSearchWhat,
string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userID,
int boardID,
int maxResults,
bool useFullText,
bool searchDisplayName,
int[] forumIds = null)
{
if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
this.ToSearchWhat = toSearchWhat;
this.ToSearchFromWho = toSearchFromWho;
this.SearchFromWhoMethod = searchFromWhoMethod;
this.SearchWhatMethod = searchWhatMethod;
this.UserID = userID;
this.BoardID = boardID;
this.MaxResults = maxResults;
this.UseFullText = useFullText;
this.SearchDisplayName = searchDisplayName;
this.ForumIDs = forumIds;
}
protected IEnumerable <SearchCondition> BuildWhatConditions(
[NotNull] string toSearchWhat, SearchWhatFlags searchWhatMethod, [NotNull] string dbField, bool useFullText)
{
CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
CodeContracts.ArgumentNotNull(dbField, "dbField");
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
var conditions = new List <SearchCondition>();
string conditionSql = string.Empty;
SearchConditionType conditionType = SearchConditionType.AND;
if (searchWhatMethod == SearchWhatFlags.AnyWords)
{
conditionType = SearchConditionType.OR;
}
var wordList = new List <string> {
toSearchWhat
};
if (searchWhatMethod == SearchWhatFlags.AllWords || searchWhatMethod == SearchWhatFlags.AnyWords)
{
wordList =
toSearchWhat.Replace(@"""", string.Empty).Split(' ').Where(x => x.IsSet()).Select(x => x.Trim()).ToList();
}
if (useFullText)
{
var list = new List <SearchCondition>();
list.AddRange(
wordList.Select(
word => new SearchCondition {
Condition = @"""{0}""".FormatWith(word), ConditionType = conditionType
}));
conditions.Add(
new SearchCondition
{
Condition = "CONTAINS ({1}, N' {0} ')".FormatWith(list.BuildSql(false), dbField),
ConditionType = conditionType
});
}
else
{
conditions.AddRange(
wordList.Select(
word =>
new SearchCondition
{
Condition = "{1} LIKE N'%{0}%'".FormatWith(word, dbField), ConditionType = conditionType
}));
}
return(conditions);
}
private void SearchBindData(bool newSearch)
{
try
{
if (newSearch && !CheckSearchRequest())
{
return;
}
else if (newSearch || Mession.SearchData == null)
{
SearchWhatFlags sw = (SearchWhatFlags)System.Enum.Parse(typeof(SearchWhatFlags), listSearchWhat.SelectedValue);
SearchWhatFlags sfw = (SearchWhatFlags)System.Enum.Parse(typeof(SearchWhatFlags), listSearchFromWho.SelectedValue);
int forumID = int.Parse(listForum.SelectedValue);
DataTable searchDataTable = YAF.Classes.Data.DB.GetSearchResult(txtSearchStringWhat.Text,
txtSearchStringFromWho.Text, sfw, sw, forumID,
PageContext.PageUserID, PageContext.PageBoardID,
PageContext.BoardSettings.ReturnSearchMax,
PageContext.BoardSettings.UseFullTextSearch);
Pager.CurrentPageIndex = 0;
Pager.PageSize = int.Parse(listResInPage.SelectedValue);
Pager.Count = searchDataTable.DefaultView.Count;
Mession.SearchData = searchDataTable;
bool bResults = (searchDataTable.DefaultView.Count > 0) ? true : false;
SearchRes.Visible = bResults;
NoResults.Visible = !bResults;
}
PagedDataSource pds = new PagedDataSource();
pds.AllowPaging = true;
pds.DataSource = Mession.SearchData.DefaultView;
pds.PageSize = Pager.PageSize;
pds.CurrentPageIndex = Pager.CurrentPageIndex;
UpdateHistory.AddEntry(Pager.CurrentPageIndex.ToString() + "|" + Pager.PageSize);
SearchRes.DataSource = pds;
SearchRes.DataBind();
}
catch (Exception x)
{
YAF.Classes.Data.DB.eventlog_create(PageContext.PageUserID, this, x);
CreateMail.CreateLogEmail(x);
if (PageContext.IsAdmin)
{
PageContext.AddLoadMessage(string.Format("{0}", x));
}
else
{
PageContext.AddLoadMessage("An error occured while searching.");
}
}
}
/// <summary>
/// Sets up highlighting of the search keywords.
/// </summary>
/// <param name="searchFlags">
/// The search Flags.
/// </param>
private void SetupHighlightWords(SearchWhatFlags searchFlags)
{
this.HighlightSearchWords.Clear();
if (searchFlags == SearchWhatFlags.ExactMatch)
{
this.HighlightSearchWords.Add(this.SearchWhatCleaned);
}
else if (searchFlags == SearchWhatFlags.AnyWords || searchFlags == SearchWhatFlags.AllWords)
{
this.HighlightSearchWords.AddRange(this.SearchWhatCleaned.Split(' ').ToList().Where(x => !x.IsNotSet()));
}
}
/// <summary>
/// Sets up highlighting of the search keywords.
/// </summary>
/// <param name="searchFlags">
/// The search Flags.
/// </param>
private void SetupHighlightWords(SearchWhatFlags searchFlags)
{
this.HighlightSearchWords.Clear();
switch (searchFlags)
{
case SearchWhatFlags.ExactMatch:
this.HighlightSearchWords.Add(this.SearchWhatCleaned);
break;
case SearchWhatFlags.AllWords:
case SearchWhatFlags.AnyWords:
this.HighlightSearchWords.AddRange(this.SearchWhatCleaned.Split(' ').ToList().Where(x => x.IsSet()));
break;
}
}
public CompleteSearchContext(
string toSearchWhat,
string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userID,
int boardID,
int maxResults,
bool useFullText,
bool searchDisplayName,
int startFromForumId)
: this(
toSearchWhat,
toSearchFromWho,
searchFromWhoMethod,
searchWhatMethod,
userID,
boardID,
maxResults,
useFullText,
searchDisplayName)
{
this.ForumIDs = GetForumIDsStartingWith(startFromForumId, boardID, userID);
}
/// <summary>
/// Returns Search results
/// </summary>
/// <param name="ToSearch"></param>
/// <param name="sf">Field to search</param>
/// <param name="sw">Search what</param>
/// <param name="fid"></param>
/// <param name="UserID">ID of user</param>
/// <returns>Results</returns>
public static DataTable GetSearchResult(string toSearchWhat, string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, SearchWhatFlags searchWhatMethod, int forumIDToStartAt, int userID, int boardId, int maxResults, bool useFullText,
bool searchDisplayName)
{
bool bFirst = true;
string forumIds = string.Empty;
DataTable dt_result = null;
// if ( ToSearch.Length == 0 )
// return new DataTable();
if ( toSearchWhat == "*" )
toSearchWhat = "";
string forumIDs = "";
string limitString = "";
string orderString = "";
String paramsString = "";
//Search not in all forums
if ( forumIDToStartAt != 0 )
{
DataTable dt = LegacyDb.forum_listall_sorted( boardId, userID, null, false, forumIDToStartAt);
foreach ( DataRow dr in dt.Rows )
forumIDs = forumIDs + Convert.ToInt32( dr ["ForumID"] ).ToString() + ",";
forumIDs = forumIDs.Substring( 0, forumIDs.Length - 1 );
}
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace( "'", "''" ).Trim();
toSearchFromWho = toSearchFromWho.Replace( "'", "''" ).Trim();
string searchSql = ( maxResults == 0 ) ? "SELECT" : ( "SELECT DISTINCTROW ");
searchSql += " a.ForumID, a.TopicID, a.Topic, b.UserID, IFNULL(c.Username, b.Name) as Name, c.MessageID, c.Posted, '' AS Message, c.Flags";
searchSql += " from "
+ MsSqlDbAccess.GetObjectName("Topic")
+ " a left join " +
MsSqlDbAccess.GetObjectName("Message") +
" c on a.TopicID = c.TopicID left join "
+ MsSqlDbAccess.GetObjectName("User") +
" b on c.UserID = b.UserID where ";
searchSql += " IFNULL(CAST(SIGN(c.Flags & 16) AS SIGNED),0) = 1 AND a.TopicMovedID IS NULL AND IFNULL(CAST(SIGN(a.Flags & 8) AS SIGNED),0) = 0 AND IFNULL(CAST(SIGN(c.Flags & 8) AS SIGNED),0) = 0 ";
orderString += " ORDER BY a.ForumID ";
limitString += " LIMIT @i_Limit ";
string [] words;
if ( !String.IsNullOrEmpty( toSearchFromWho ) )
{
searchSql += "AND (";
bFirst = true;
int userId;
// generate user search sql...
switch ( searchFromWhoMethod )
{
case SearchWhatFlags.AllWords:
words = toSearchFromWho.Split( ' ' );
foreach ( string word in words )
{
if ( !bFirst ) searchSql += " AND "; else bFirst = false;
if (int.TryParse(word, out userId))
{
searchSql +=
searchSql += string.Format(" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(" ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
else
{
searchSql +=
string.Format(" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchFromWho.Split( ' ' );
foreach ( string word in words )
{
if ( !bFirst ) searchSql += " OR "; else bFirst = false;
if (int.TryParse(word, out userId))
{
searchSql +=
string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(" ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
else
{
searchSql +=
string.Format(" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
}
}
break;
case SearchWhatFlags.ExactMatch:
if (int.TryParse(toSearchFromWho, out userId))
{
searchSql +=
string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
if (searchDisplayName)
{
searchSql += string.Format(" ((c.Username IS NULL AND b.DisplayName = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))", toSearchFromWho, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
else
{
searchSql += string.Format(" ((c.Username IS NULL AND b.Name = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))", toSearchFromWho, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
}
break;
}
searchSql += ") ";
}
if ( !String.IsNullOrEmpty( toSearchWhat ) )
{
searchSql += "AND (";
bFirst = true;
// generate message and topic search sql...
switch ( searchWhatMethod )
{
case SearchWhatFlags.AllWords:
words = toSearchWhat.Split( ' ' );
if ( useFullText )
{
string ftInner = "";
// make the inner FULLTEXT search
foreach ( string word in words )
{
if ( !bFirst ) ftInner += " AND "; else bFirst = false;
ftInner += String.Format( @"""{0}""", word );
}
// make final string...
searchSql += string.Format("( CONTAINS (c.Message, CONVERT(' {0} ' USING {1}) OR CONTAINS (a.Topic, CONVERT(' {0} ' USING {1})) )", ftInner, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
else
{
foreach ( string word in words )
{
if ( !bFirst ) searchSql += " AND "; else bFirst = false;
searchSql += String.Format("(c.Message like CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}))", word, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchWhat.Split( ' ' );
if ( useFullText )
{
string ftInner = "";
// make the inner FULLTEXT search
foreach ( string word in words )
{
if ( !bFirst ) ftInner += " OR "; else bFirst = false;
ftInner += String.Format( @"""{0}""", word );
}
// make final string...
searchSql += string.Format("( CONTAINS (c.Message, CONVERT(' {0} ' USING {1})) OR CONTAINS (a.Topic, CONVERT(' {0} ' USING {1})) )", ftInner, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
else
{
foreach ( string word in words )
{
if ( !bFirst ) searchSql += " OR "; else bFirst = false;
searchSql += String.Format("c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1})", word, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
}
break;
case SearchWhatFlags.ExactMatch:
if ( useFullText )
{
searchSql += string.Format("( CONTAINS (c.Message, CONVERT(' \"{0}\" ' USING {1})) OR CONTAINS (a.Topic, CONVERT(' \"{0}\" ' USING {1}) )", toSearchWhat, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
else
{
searchSql += string.Format("c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}) ", toSearchWhat, !string.IsNullOrEmpty(Config.DatabaseEncoding)? Config.DatabaseEncoding : "utf8");
}
break;
}
searchSql += ") ";
}
// Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
if ( forumIDToStartAt > 0 )
{
searchSql += string.Format( "AND a.ForumID IN ( SELECT {0})", forumIDs );
}
if (orderString!="") { orderString += ", "; }
if (!orderString.Contains("ORDER BY"))
{
searchSql += " ORDER BY ";
}
searchSql += orderString + "c.Posted DESC ";
if (!orderString.Contains("LIMIT"))
{
searchSql += limitString;
}
using ( MySqlCommand cmd = MsSqlDbAccess.GetCommand( searchSql, true ) )
{
// string toSearchWhat, string toSearchFromWho int forumIDToStartAt, int userID, int boardId, int maxResults
// cmd.Parameters.Add( "i_UserID", MySqlDbType.Int32 ).Value = userID;
// cmd.Parameters.Add( "i_Word", MySqlDbType.VarChar ).Value = word;
// cmd.Parameters.Add("i_toSearchFromWho", MySqlDbType.VarChar).Value = toSearchFromWho;
// cmd.Parameters.Add("i_forumIDs", MySqlDbType.VarChar).Value = forumIDs;
// cmd.Parameters.Add("i_ftInner", MySqlDbType.VarChar).Value = ftInner;
/* words = toSearchFromWho.Split(' ');
int ii1 = 0;
foreach (string word in words)
{
cmd.Parameters.Add( "i_word"+ii1.ToString(), MySqlDbType.VarChar ).Value = userID;
ii1++;
}*/
cmd.Parameters.Add("@i_Limit", MySqlDbType.Int32).Value = maxResults.ToString(); ;
dt_result = MsSqlDbAccess.Current.GetData(cmd);
}
string old_uid = null;
string old_fid = null;
foreach (DataRow dr in dt_result.Rows)
{
if (old_uid != dr["UserID"].ToString() && old_fid != dr["ForumID"].ToString())
{
using (MySqlCommand cmd1 = MsSqlDbAccess.GetCommand(String.Format("SELECT {0}(@i_UserID,@i_ForumID);", MsSqlDbAccess.GetObjectName("vaccess_s_readaccess_combo")), true))
{
cmd1.Parameters.Add("@i_UserID", MySqlDbType.Int32).Value = dr["UserID"];
cmd1.Parameters.Add("@i_ForumID", MySqlDbType.Int32).Value = dr["ForumID"]; ;
if ( Convert.ToInt32(MsSqlDbAccess.Current.ExecuteScalar(cmd1)) == 0 )
{
dr.Delete();
}
}
old_uid = dr["UserID"].ToString();
old_fid = dr["ForumID"].ToString();
}
}
dt_result.AcceptChanges();
return dt_result;
}
public string Build(string searchSql, SearchWhatFlags searchFromWhoMethod, string toSearchFromWho, bool useFullText, bool searchDisplayName)
{
bool bFirst = true;
int userId;
// generate user search sql...
switch (searchFromWhoMethod)
{
case SearchWhatFlags.AllWords:
string[] words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql += " AND ";
}
else
{
bFirst = false;
}
searchSql +=
string.Format(
@" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))",
word);
if (int.TryParse(word, out userId))
{
searchSql += string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.DisplayName LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))",
word);
}
else
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.Name LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))",
word);
}
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql += " OR ";
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(
" ((c.USERNAME IS NULL AND b.DISPLAYNAME = '{0}') OR (c.Username = '******')",
toSearchFromWho);
}
else
{
searchSql +=
string.Format(
@" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))",
word);
}
}
}
break;
case SearchWhatFlags.ExactMatch:
if (int.TryParse(toSearchFromWho, out userId))
{
searchSql += string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
searchSql +=
string.Format(
@" ((c.USERNAME IS NULL AND b.NAME = '{0}' ) OR (c.USERNAME = '******' ))",
toSearchFromWho);
}
break;
}
return searchSql;
}
/// <summary>
/// Sets up highlighting of the search keywords.
/// </summary>
/// <param name="searchFlags">
/// The search Flags.
/// </param>
private void SetupHighlightWords(SearchWhatFlags searchFlags)
{
this.HighlightSearchWords.Clear();
switch (searchFlags)
{
case SearchWhatFlags.ExactMatch:
this.HighlightSearchWords.Add(this.SearchWhatCleaned);
break;
case SearchWhatFlags.AllWords:
case SearchWhatFlags.AnyWords:
this.HighlightSearchWords.AddRange(this.SearchWhatCleaned.Split(' ').ToList().Where(x => x.IsSet()));
break;
}
}
/// <summary>
/// Returns Search results
/// </summary>
/// <param name="ToSearch"></param>
/// <param name="sf">Field to search</param>
/// <param name="sw">Search what</param>
/// <param name="fid"></param>
/// <param name="UserID">ID of user</param>
/// <returns>Results</returns>
public static DataTable GetSearchResult( string toSearchWhat, string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, SearchWhatFlags searchWhatMethod, int forumIDToStartAt, int userId, int boardId, int maxResults, bool useFullText, bool searchDisplayName)
{
// New access
/* if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
IEnumerable<int> forumIds = new List<int>();
if (forumIDToStartAt != 0)
{
forumIds = ForumListAll(boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
}
string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);
using (SqlCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
{
return MsSqlDbAccess.Current.GetData(cmd);
} */
if (toSearchWhat == "*")
toSearchWhat = "";
string forumIDs = "";
string limitString = "";
string orderString = "";
if (forumIDToStartAt != 0)
{
DataTable dt = forum_listall_sorted(boardId, userId, null, false, forumIDToStartAt);
foreach (DataRow dr in dt.Rows)
forumIDs = forumIDs + Convert.ToInt32(dr["ForumID"]).ToString() + ",";
forumIDs = forumIDs.Substring(0, forumIDs.Length - 1);
}
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
string searchSql = (maxResults == 0) ? "SELECT" : ("SELECT DISTINCT ");
if (maxResults > 0)
{ limitString += String.Format(" LIMIT {0} ", maxResults.ToString()); }
searchSql += " a.forumid, a.topicid, a.topic, b.userid, COALESCE(c.username, b.name) as Name, c.messageid as \"MessageID\", c.posted, c.message as \"Message\", c.flags ";
searchSql += "FROM " + (Config.SchemaName.IsSet() ? Config.SchemaName : "public") +
"." + Config.DatabaseObjectQualifier + "topic a LEFT JOIN " +
(Config.SchemaName.IsSet() ? Config.SchemaName : "public") + "." + Config.DatabaseObjectQualifier +
"message c ON a.topicid = c.topicid LEFT JOIN " + (Config.SchemaName.IsSet() ? Config.SchemaName : "public") + "." + Config.DatabaseObjectQualifier +
"user b ON c.userid = b.userid join " + (Config.SchemaName.IsSet() ? Config.SchemaName : "public") + "." + Config.DatabaseObjectQualifier
+ "vaccess x ON x.forumid=a.forumid ";
searchSql += String.Format(@"WHERE x.readaccess<>0 AND x.userid={0} AND c.isapproved IS TRUE AND a.topicmovedid IS NULL AND a.isdeleted IS FALSE AND c.isdeleted IS FALSE ", userId);
orderString += "ORDER BY a.forumid ";
string[] words;
bool bFirst;
if (!String.IsNullOrEmpty(toSearchFromWho))
{
searchSql += "AND (";
bFirst = true;
// generate user search sql...
switch (searchFromWhoMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst) searchSql += " AND "; else bFirst = false;
// searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
if (!int.TryParse(word, out userId))
{
if (searchDisplayName)
{
searchSql +=
string.Format(" ((c.userdisplayname IS NULL AND b.displayname ~* '.*{0}.*') OR (c.userdisplayname ~* '.*{0}.*'))", word);
}
else
{
searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
}
}
else
{
searchSql +=
string.Format(" (c.userid IN ({0}))", userId);
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst) searchSql += " OR "; else bFirst = false;
searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
}
break;
case SearchWhatFlags.ExactMatch:
// searchSql += string.Format(" ((c.username IS NULL AND b.name = '{0}' ) OR (c.username = '******' ))", toSearchFromWho);
if (!int.TryParse(toSearchFromWho, out userId))
{
if (searchDisplayName)
{
searchSql += string.Format(
" ((c.userdisplayname IS NULL AND b.displayname = '{0}') OR (c.userdisplayname = '{0}'))", toSearchFromWho);
}
else
{
searchSql += string.Format(
" ((c.username IS NULL AND b.name = '{0}') OR (c.username = '******'))", toSearchFromWho);
}
}
else
{
searchSql +=
string.Format(" (c.userid IN ({0})) ", userId);
}
break;
}
searchSql += ") ";
}
if (!String.IsNullOrEmpty(toSearchWhat))
{
searchSql += "AND (";
bFirst = true;
// generate message and topic search sql...
switch (searchWhatMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = "";
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst) ftInner += " AND "; else bFirst = false;
ftInner += String.Format(@"""{0}""", word);
}
// make final string...
searchSql += string.Format("( CONTAINS (c.message, ' {0} ') OR CONTAINS (a.topic, ' {0} ' ) )", ftInner);
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " AND "; else bFirst = false;
searchSql += String.Format("(c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' )", word);
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = "";
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst) ftInner += " OR "; else bFirst = false;
ftInner += String.Format(@"""{0}""", word);
if (int.TryParse(word, out userId))
{
searchSql +=
string.Format(" (c.userid IN ({0}))", userId);
}
else
{
searchSql +=
string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
}
}
// make final string...
searchSql += string.Format("( CONTAINS (c.message, ' {0} ' ) OR CONTAINS (a.topic, ' {0} ' ) )", ftInner);
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " OR "; else bFirst = false;
searchSql += String.Format("c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' ", word);
}
}
break;
case SearchWhatFlags.ExactMatch:
if (useFullText)
{
searchSql += string.Format("( CONTAINS (c.message, ' \"{0}\" ' ) OR CONTAINS (a.topic, ' \"{0}\" ' )", toSearchWhat);
}
else
{
searchSql += string.Format("c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' ", toSearchWhat);
}
break;
}
searchSql += ") ";
}
// Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
if (forumIDToStartAt > 0)
{
searchSql += string.Format("AND a.forumid IN ({0})", forumIDs);
}
if (orderString != "") { orderString += ", "; }
if (!orderString.Contains("ORDER BY"))
{
searchSql += " ORDER BY ";
}
searchSql += orderString + "c.posted DESC ";
if (!orderString.Contains("LIMIT"))
{
searchSql += limitString;
}
using (NpgsqlCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
{
return MsSqlDbAccess.Current.GetData(cmd);
}
}
public string Build(string searchSql, SearchWhatFlags searchWhatMethod, string toSearchWhat, bool useFullText)
{
bool bFirst = true;
// generate message and topic search sql...
switch (searchWhatMethod)
{
case SearchWhatFlags.AllWords:
string[] words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = string.Empty;
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst)
{
ftInner += " AND ";
}
else
{
bFirst = false;
}
ftInner += string.Format(@"""{0}""", word);
}
// make final string...
searchSql +=
string.Format(
@"( CONTAINS (c.MESSAGE, ' {0} ') OR CONTAINS (a.TOPIC, ' {0} ' ) )",
ftInner);
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " AND ";
else bFirst = false;
searchSql += string.Format(@"(c.MESSAGE like '%{0}%' OR a.TOPIC LIKE '%{0}%' )", word);
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = string.Empty;
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst)
{
ftInner += " OR ";
}
else
{
bFirst = false;
}
ftInner += string.Format(@"""{0}""", word);
}
// make final string...
searchSql +=
string.Format(
@"( CONTAINS (c.MESSAGE, ' {0} ' ) OR CONTAINS (a.TOPIC, ' {0} ' ) )",
ftInner);
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " OR ";
else bFirst = false;
searchSql += string.Format(@"c.MESSAGE LIKE '%{0}%' OR a.TOPIC LIKE '%{0}%' ", word);
}
}
break;
case SearchWhatFlags.ExactMatch:
if (useFullText)
{
// searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' \"{0}\" ' ) OR CONTAINS (a.Topic, ' \"{0}\" ' )", toSearchWhat);
}
else
{
searchSql += string.Format(
@"c.MESSAGE LIKE '%{0}%' OR a.TOPIC LIKE '%{0}%' ",
toSearchWhat);
}
break;
}
return searchSql;
}
/// <summary>
/// The build search sql.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="toSearchWhat">
/// The to search what.
/// </param>
/// <param name="toSearchFromWho">
/// The to search from who.
/// </param>
/// <param name="searchFromWhoMethod">
/// The search from who method.
/// </param>
/// <param name="searchWhatMethod">
/// The search what method.
/// </param>
/// <param name="userId">
/// The user Id.
/// </param>
/// <param name="searchDisplayName">
/// The search display name.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="maxResults">
/// The max results.
/// </param>
/// <param name="useFullText">
/// The use full text.
/// </param>
/// <param name="ids">
/// The ids.
/// </param>
/// <param name="forumIdToStartAt">
/// The forum Id To Start At.
/// </param>
/// <returns>
/// The build search sql.
/// </returns>
public string BuildSearchSql(
int? mid,
[NotNull] string toSearchWhat,
[NotNull] string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userId,
bool searchDisplayName,
int boardId,
int maxResults,
bool useFullText,
string categoriesIds,
string ids,
[NotNull] IEnumerable<int> forumIdToStartAt)
{
CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");
string limitString = string.Empty;
string orderString = string.Empty;
IEnumerable<int> forumIds = null;
if (!string.IsNullOrEmpty(ids))
{
forumIds = new[] { ids.ToType<int>() };
}
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
string searchSql = "SELECT ";
if (maxResults > 0)
{
limitString += string.Format(" FIRST {0} ", maxResults);
searchSql += limitString;
}
searchSql +=
@" a.FORUMID AS ""ForumID"", a.TOPICID AS ""TopicID"", a.TOPIC AS ""Topic"", b.USERID AS ""UserID"" , COALESCE(c.USERNAME, b.NAME) AS ""Name"", c.MESSAGEID AS ""MessageID"", c.POSTED AS ""Posted"", '' AS ""Message"", c.FLAGS AS ""Flags""";
searchSql += " FROM " + SqlDbAccess.GetVzfObjectName("TOPIC", mid) + " a LEFT JOIN "
+ SqlDbAccess.GetVzfObjectName("MESSAGE", mid) + @" c ON a.TOPICID = c.TOPICID LEFT JOIN "
+ SqlDbAccess.GetVzfObjectName("USER", mid) + @" b ON c.USERID = b.USERID join "
+ SqlDbAccess.GetVzfObjectName("VACCESS", mid) + @" x ON x.FORUMID=a.FORUMID ";
searchSql +=
string.Format(
@"WHERE x.READACCESS<>0 AND x.USERID={0} AND c.ISAPPROVED <> 0 AND a.TOPICMOVEDID IS NULL AND a.ISDELETED = 0 AND c.ISDELETED = 0 ",
userId);
orderString += @" ORDER BY a.FORUMID ";
bool bFirst;
if (!string.IsNullOrEmpty(toSearchFromWho))
{
searchSql += "AND (";
searchSql = new SearchFromWho().Build(searchSql, searchFromWhoMethod, toSearchFromWho, useFullText, searchDisplayName);
searchSql += ") ";
}
if (!string.IsNullOrEmpty(toSearchWhat))
{
searchSql += "AND (";
searchSql = new SearchWhat().Build(searchSql, searchWhatMethod, toSearchWhat, useFullText);
searchSql += ") ";
}
if (categoriesIds.IsSet())
{
searchSql +=string.Format(" AND f.categoryid IN ({0})", categoriesIds);
}
// Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
if (forumIdToStartAt.Any())
{
searchSql += string.Format(@"AND a.FORUMID IN ({0})", forumIds);
}
if (orderString != string.Empty)
{
orderString += ", ";
}
if (!orderString.Contains("ORDER BY"))
{
searchSql += " ORDER BY ";
}
searchSql += orderString + @"c.POSTED DESC ";
return searchSql;
}
/// <summary>
/// The build search sql.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="toSearchWhat">
/// The to search what.
/// </param>
/// <param name="toSearchFromWho">
/// The to search from who.
/// </param>
/// <param name="searchFromWhoMethod">
/// The search from who method.
/// </param>
/// <param name="searchWhatMethod">
/// The search what method.
/// </param>
/// <param name="pageUserId">
/// The page User Id.
/// </param>
/// <param name="searchDisplayName">
/// The search display name.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="maxResults">
/// The max results.
/// </param>
/// <param name="useFullText">
/// The use full text.
/// </param>
/// <param name="ids">
/// The ids.
/// </param>
/// <param name="forumIdToStartAt">
/// The forum Id To Start At.
/// </param>
/// <returns>
/// The build search sql.
/// </returns>
public string BuildSearchSql(
int? mid,
[NotNull] string toSearchWhat,
[NotNull] string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int pageUserId,
bool searchDisplayName,
int boardId,
int maxResults,
bool useFullText,
string categoriesIds,
string ids,
[NotNull] IEnumerable<int> forumIdToStartAt)
{
CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");
string limitString = string.Empty;
string orderString = string.Empty;
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
bool bFirst = true;
// if ( ToSearch.Length == 0 )
// return new DataTable();
if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
string forumIDs = ids;
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
string searchSql = (maxResults == 0) ? "SELECT" : "SELECT DISTINCTROW ";
searchSql +=
" a.ForumID, a.TopicID, a.Topic, b.UserID, IFNULL(c.Username, b.Name) as Name, c.MessageID, c.Posted, '' AS Message, c.Flags";
searchSql += "{FTS} from " + SqlDbAccess.GetVzfObjectName("Topic", mid) + " a left join "
+ SqlDbAccess.GetVzfObjectName("Message", mid) + " c on a.TopicID = c.TopicID left join "
+ SqlDbAccess.GetVzfObjectName("User", mid) + " b on c.UserID = b.UserID join "
+ SqlDbAccess.GetVzfObjectName("vaccess", mid) + " x ON x.ForumID=a.ForumID ";
searchSql += string.Format(@"WHERE x.ReadAccess<>0 AND x.UserID={0} ", pageUserId);
searchSql +=
" and IFNULL(CAST(SIGN(c.Flags & 16) AS SIGNED),0) = 1 AND a.TopicMovedID IS NULL AND IFNULL(CAST(SIGN(a.Flags & 8) AS SIGNED),0) = 0 AND IFNULL(CAST(SIGN(c.Flags & 8) AS SIGNED),0) = 0 ";
orderString += " ORDER BY a.ForumID ";
limitString += " LIMIT @i_Limit ";
string[] words;
if (!string.IsNullOrEmpty(toSearchFromWho))
{
searchSql += "AND (";
// generate user search sql...
int userId;
switch (searchFromWhoMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql += " AND ";
}
else
{
bFirst = false;
}
if (int.TryParse(word, out userId))
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
word,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
word,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
else
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
word,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql += " OR ";
}
else
{
bFirst = false;
}
if (int.TryParse(word, out userId))
{
searchSql += string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.DisplayName LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
word,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
else
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.Name LIKE CONVERT('{0}' USING {1})) OR (c.Username LIKE CONVERT('{0}' USING {1})))",
word,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
}
}
break;
case SearchWhatFlags.ExactMatch:
if (int.TryParse(toSearchFromWho, out userId))
{
searchSql += string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.DisplayName = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))",
toSearchFromWho,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
else
{
searchSql +=
string.Format(
" ((c.Username IS NULL AND b.Name = CONVERT('{0}' USING {1})) OR (c.Username = CONVERT('{0}' USING {1})))",
toSearchFromWho,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
}
break;
}
searchSql += ") ";
}
if (!string.IsNullOrEmpty(toSearchWhat))
{
searchSql += "AND (";
bFirst = true;
// generate message and topic search sql...
switch (searchWhatMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = string.Empty;
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst)
{
ftInner += " ";
}
else
{
bFirst = false;
}
ftInner += string.Format(@"{0}", word);
}
// make final string...
searchSql = searchSql.Replace(
"{FTS}",
string.Format(
", MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE), MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) ",
ftInner,
!string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8"));
searchSql += string.Format(
"(MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0 OR MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0) ",
ftInner,
!string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " AND ";
else bFirst = false;
searchSql +=
string.Format(
"(c.Message like CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}))",
word,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = string.Empty;
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst)
{
ftInner += " ";
}
else
{
bFirst = false;
}
ftInner += string.Format(@"{0}", word);
}
// make final string...
searchSql = searchSql.Replace(
"{FTS}",
string.Format(
", MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE), MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) ",
ftInner,
!string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8"));
searchSql += string.Format(
"(MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0 OR MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0) ",
ftInner,
!string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " OR ";
else bFirst = false;
searchSql +=
string.Format(
"c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1})",
word,
!string.IsNullOrEmpty(Config.DatabaseEncoding)
? Config.DatabaseEncoding
: "utf8");
}
}
break;
case SearchWhatFlags.ExactMatch:
if (useFullText)
{
// make final string...
searchSql = searchSql.Replace(
"{FTS}",
string.Format(
", MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE), MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) ",
toSearchWhat,
!string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8"));
searchSql += string.Format(
"(MATCH (c.Message, c.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0 OR MATCH (a.Topic, a.Description) AGAINST (CONVERT('{0}' USING {1}) IN NATURAL LANGUAGE MODE) > 0) ",
toSearchWhat,
!string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
else
{
searchSql +=
string.Format(
"c.Message LIKE CONVERT('%{0}%' USING {1}) OR a.Topic LIKE CONVERT('%{0}%' USING {1}) ",
toSearchWhat,
!string.IsNullOrEmpty(Config.DatabaseEncoding) ? Config.DatabaseEncoding : "utf8");
}
break;
}
searchSql += ") ";
}
if (categoriesIds.IsSet())
{
searchSql += string.Format(" AND f.categoryid IN ({0})", categoriesIds);
}
// Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
if (forumIdToStartAt.Any())
{
searchSql += string.Format("AND a.ForumID IN (SELECT {0})", forumIDs);
}
if (orderString != string.Empty)
{
orderString += ", ";
}
if (!orderString.Contains("ORDER BY"))
{
searchSql += " ORDER BY ";
}
searchSql += orderString + "c.Posted DESC ";
if (!orderString.Contains("LIMIT"))
{
searchSql += limitString;
}
return searchSql.Replace("{FTS}", string.Empty);
}
/// <summary>
/// The build search sql.
/// </summary>
/// <param name="toSearchWhat">
/// The to search what.
/// </param>
/// <param name="toSearchFromWho">
/// The to search from who.
/// </param>
/// <param name="searchFromWhoMethod">
/// The search from who method.
/// </param>
/// <param name="searchWhatMethod">
/// The search what method.
/// </param>
/// <param name="userID">
/// The user id.
/// </param>
/// <param name="searchDisplayName">
/// The search display name.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="maxResults">
/// The max results.
/// </param>
/// <param name="useFullText">
/// The use full text.
/// </param>
/// <param name="forumIds">
/// The forum ids.
/// </param>
/// <returns>
/// The build search sql.
/// </returns>
public string BuildSearchSql(
[NotNull] string toSearchWhat,
[NotNull] string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userID,
bool searchDisplayName,
int boardId,
int maxResults,
bool useFullText,
[NotNull] IEnumerable<int> forumIds)
{
CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");
CodeContracts.ArgumentNotNull(forumIds, "forumIds");
var builtStatements = new List<string>();
if (maxResults > 0)
{
builtStatements.Add("SET ROWCOUNT {0};".FormatWith(maxResults));
}
string searchSql =
"SELECT a.ForumID, a.TopicID, a.Topic, b.UserID, IsNull(c.Username, b.Name) as Name, c.MessageID, c.Posted, [Message] = '', c.Flags ";
searchSql +=
"\r\nfrom {databaseOwner}.{objectQualifier}topic a left join {databaseOwner}.{objectQualifier}message c on a.TopicID = c.TopicID left join {databaseOwner}.{objectQualifier}user b on c.UserID = b.UserID join {databaseOwner}.{objectQualifier}vaccess x on x.ForumID=a.ForumID ";
searchSql +=
"\r\nwhere x.ReadAccess<>0 AND x.UserID={0} AND c.IsApproved = 1 AND a.TopicMovedID IS NULL AND a.IsDeleted = 0 AND c.IsDeleted = 0"
.FormatWith(userID);
if (forumIds.Any())
{
searchSql += " AND a.ForumID IN ({0})".FormatWith(forumIds.ToDelimitedString(","));
}
if (toSearchFromWho.IsSet())
{
searchSql +=
"\r\nAND ({0})".FormatWith(
this.BuildWhoConditions(toSearchFromWho, searchFromWhoMethod, searchDisplayName).BuildSql(true));
}
if (toSearchWhat.IsSet())
{
builtStatements.Add(searchSql);
builtStatements.Add(
"AND ({0})".FormatWith(
this.BuildWhatConditions(toSearchWhat, searchWhatMethod, "c.Message", useFullText).BuildSql(true)));
builtStatements.Add("UNION");
builtStatements.Add(searchSql);
builtStatements.Add(
"AND ({0})".FormatWith(
this.BuildWhatConditions(toSearchWhat, searchWhatMethod, "a.Topic", useFullText).BuildSql(true)));
}
else
{
builtStatements.Add(searchSql);
}
builtStatements.Add("ORDER BY c.Posted DESC");
string builtSql = builtStatements.ToDelimitedString("\r\n");
Debug.WriteLine("Build Sql: [{0}]".FormatWith(builtSql));
return builtSql;
}
protected IEnumerable<SearchCondition> BuildWhatConditions(
[NotNull] string toSearchWhat, SearchWhatFlags searchWhatMethod, [NotNull] string dbField, bool useFullText)
{
CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
CodeContracts.ArgumentNotNull(dbField, "dbField");
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
var conditions = new List<SearchCondition>();
string conditionSql = string.Empty;
SearchConditionType conditionType = SearchConditionType.AND;
if (searchWhatMethod == SearchWhatFlags.AnyWords)
{
conditionType = SearchConditionType.OR;
}
var wordList = new List<string> { toSearchWhat };
if (searchWhatMethod == SearchWhatFlags.AllWords || searchWhatMethod == SearchWhatFlags.AnyWords)
{
wordList =
toSearchWhat.Replace(@"""", string.Empty).Split(' ').Where(x => x.IsSet()).Select(x => x.Trim()).ToList();
}
if (useFullText)
{
var list = new List<SearchCondition>();
list.AddRange(
wordList.Select(
word => new SearchCondition { Condition = @"""{0}""".FormatWith(word), ConditionType = conditionType }));
conditions.Add(
new SearchCondition
{
Condition = "CONTAINS ({1}, N' {0} ')".FormatWith(list.BuildSql(false), dbField),
ConditionType = conditionType
});
}
else
{
conditions.AddRange(
wordList.Select(
word =>
new SearchCondition
{
Condition = "{1} LIKE N'%{0}%'".FormatWith(word, dbField), ConditionType = conditionType
}));
}
return conditions;
}
/// <summary>
/// The build search sql.
/// </summary>
/// <param name="toSearchWhat">
/// The to search what.
/// </param>
/// <param name="toSearchFromWho">
/// The to search from who.
/// </param>
/// <param name="searchFromWhoMethod">
/// The search from who method.
/// </param>
/// <param name="searchWhatMethod">
/// The search what method.
/// </param>
/// <param name="userID">
/// The user id.
/// </param>
/// <param name="searchDisplayName">
/// The search display name.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="maxResults">
/// The max results.
/// </param>
/// <param name="useFullText">
/// The use full text.
/// </param>
/// <param name="forumIds">
/// The forum ids.
/// </param>
/// <returns>
/// The build search sql.
/// </returns>
public string BuildSearchSql(
[NotNull] string toSearchWhat,
[NotNull] string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userID,
bool searchDisplayName,
int boardId,
int maxResults,
bool useFullText,
[NotNull] IEnumerable <int> forumIds)
{
CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");
CodeContracts.ArgumentNotNull(forumIds, "forumIds");
var builtStatements = new List <string>();
if (maxResults > 0)
{
builtStatements.Add("SET ROWCOUNT {0};".FormatWith(maxResults));
}
string searchSql =
"SELECT a.ForumID, a.TopicID, a.Topic, b.UserID, IsNull(c.Username, b.Name) as Name, c.MessageID, c.Posted, [Message] = '', c.Flags ";
searchSql +=
"\r\nfrom {databaseOwner}.{objectQualifier}topic a left join {databaseOwner}.{objectQualifier}message c on a.TopicID = c.TopicID left join {databaseOwner}.{objectQualifier}user b on c.UserID = b.UserID join {databaseOwner}.{objectQualifier}vaccess x on x.ForumID=a.ForumID ";
searchSql +=
"\r\nwhere x.ReadAccess<>0 AND x.UserID={0} AND c.IsApproved = 1 AND a.TopicMovedID IS NULL AND a.IsDeleted = 0 AND c.IsDeleted = 0"
.FormatWith(userID);
if (forumIds.Any())
{
searchSql += " AND a.ForumID IN ({0})".FormatWith(forumIds.ToDelimitedString(","));
}
if (toSearchFromWho.IsSet())
{
searchSql +=
"\r\nAND ({0})".FormatWith(
this.BuildWhoConditions(toSearchFromWho, searchFromWhoMethod, searchDisplayName).BuildSql(true));
}
if (toSearchWhat.IsSet())
{
builtStatements.Add(searchSql);
builtStatements.Add(
"AND ({0})".FormatWith(
this.BuildWhatConditions(toSearchWhat, searchWhatMethod, "c.Message", useFullText).BuildSql(true)));
builtStatements.Add("UNION");
builtStatements.Add(searchSql);
builtStatements.Add(
"AND ({0})".FormatWith(
this.BuildWhatConditions(toSearchWhat, searchWhatMethod, "a.Topic", useFullText).BuildSql(true)));
}
else
{
builtStatements.Add(searchSql);
}
builtStatements.Add("ORDER BY c.Posted DESC");
string builtSql = builtStatements.ToDelimitedString("\r\n");
Debug.WriteLine("Build Sql: [{0}]".FormatWith(builtSql));
return(builtSql);
}
/// <summary>
/// The get search result.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="toSearchWhat">
/// The to search what.
/// </param>
/// <param name="toSearchFromWho">
/// The to search from who.
/// </param>
/// <param name="searchFromWhoMethod">
/// The search from who method.
/// </param>
/// <param name="searchWhatMethod">
/// The search what method.
/// </param>
/// <param name="categoryId">
/// The category id.
/// </param>
/// <param name="forumIdToStartAt">
/// The forum id to start at.
/// </param>
/// <param name="userId">
/// The user id.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="maxResults">
/// The max results.
/// </param>
/// <param name="useFullText">
/// The use full text.
/// </param>
/// <param name="searchDisplayName">
/// The search display name.
/// </param>
/// <param name="includeChildren">
/// The include children.
/// </param>
/// <param name="culture">
/// The culture.
/// </param>
/// <returns>
/// The <see cref="T:System.Data.DataTable"/>.
/// </returns>
/// <exception cref="ArgumentOutOfRangeException">
/// </exception>
public static DataTable GetSearchResult(
int? mid,
string toSearchWhat,
string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
List<int> categoryId,
List<int> forumIdToStartAt,
int userId,
int boardId,
int maxResults,
bool useFullText,
bool searchDisplayName,
bool includeChildren,
string culture)
{
string dataEngine;
string connectionString;
string namePattern = string.Empty;
SqlDbAccess.GetConnectionData(mid, namePattern, out dataEngine, out connectionString);
// New access
/* if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
IEnumerable<int> forumIds = new List<int>();
if (forumIdToStartAt != 0)
{
forumIds = ForumListAll(boardId, userID, forumIdToStartAt).Select(f => f.ForumID ?? 0).Distinct();
}
string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);
using (SqlCommand cmd = PostgreDbAccess.GetCommand(searchSql, true))
{
return PostgreDbAccess.Current.GetData(cmd);
} */
if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
string forumIDs = string.Empty;
string categoriesIds = string.Empty;
DataTable dt = null;
if ((categoryId.Any() || forumIdToStartAt.Any()) && !Config.LargeForumTree)
{
dt = CommonDb.forum_listall_sorted(mid, boardId, userId, forumIdToStartAt.ToArray<int>());
}
if (categoryId.Any())
{
if (Config.LargeForumTree)
{
DataTable dt1 = CommonDb.forum_cataccess_actuser(mid, boardId, userId);
foreach (DataRow c in dt1.Rows)
{
foreach (int c1 in categoryId)
{
if (Convert.ToInt32(c["CategoryID"]) == c1)
{
categoriesIds = categoriesIds + "," + c1;
}
}
}
categoriesIds = categoriesIds.Trim(',');
}
else
{
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
// if not a category
if (Convert.ToInt32(dr["ForumID"]) == -1)
{
categoriesIds = categoriesIds + Convert.ToInt32(dr["ForumID"]) + ",";
}
}
categoriesIds = categoriesIds.Substring(0, forumIDs.Length - 1);
categoriesIds = categoriesIds.Trim(',');
}
}
}
if (forumIdToStartAt.Any())
{
if (!Config.LargeForumTree)
{
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
// if not a category
if (Convert.ToInt32(dr["ForumID"]) != -1)
{
forumIDs = forumIDs + Convert.ToInt32(dr["ForumID"]) + ",";
}
}
forumIDs = forumIDs.Substring(0, forumIDs.Length - 1);
}
}
else
{
foreach (int frms in forumIdToStartAt)
{
var d1 = CommonDb.forum_ns_getch_actuser(
mid,
boardId,
0,
frms,
userId,
false,
false,
"-");
foreach (DataRow r in d1.Rows)
{
forumIDs += "," + r["ForumID"];
}
// Parent forum only.
if (!includeChildren)
{
break;
}
}
forumIDs = forumIDs.Trim(',');
}
}
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
string sql;
switch (dataEngine)
{
case SqlDbAccess.MsSql:
sql = new VZF.Data.MsSql.Search.SearchBuilder().BuildSearchSql(
mid,
toSearchWhat,
toSearchFromWho,
searchFromWhoMethod,
searchWhatMethod,
userId,
searchDisplayName,
boardId,
maxResults,
useFullText,
forumIDs,
forumIdToStartAt);
break;
case SqlDbAccess.Npgsql:
sql = new VZF.Data.Postgre.Search.SearchBuilder().BuildSearchSql(
mid,
toSearchWhat,
toSearchFromWho,
searchFromWhoMethod,
searchWhatMethod,
userId,
searchDisplayName,
boardId,
maxResults,
useFullText,
categoriesIds,
forumIDs,
forumIdToStartAt,
culture);
break;
case SqlDbAccess.MySql:
sql = new VZF.Data.MySql.Search.SearchBuilder().BuildSearchSql(
mid,
toSearchWhat,
toSearchFromWho,
searchFromWhoMethod,
searchWhatMethod,
userId,
searchDisplayName,
boardId,
maxResults,
useFullText,
categoriesIds,
forumIDs,
forumIdToStartAt);
break;
case SqlDbAccess.Firebird:
sql = new VZF.Data.Firebird.Search.SearchBuilder().BuildSearchSql(
mid,
toSearchWhat,
toSearchFromWho,
searchFromWhoMethod,
searchWhatMethod,
userId,
searchDisplayName,
boardId,
maxResults,
useFullText,
categoriesIds,
forumIDs,
forumIdToStartAt);
break;
// case SqlDbAccess.Oracle: return VZF.Data.Oracle.Db.GetSearchResult(connectionString, toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, categoryId, forumIdToStartAt, userId, boardId, maxResults, useFullText, searchDisplayName, includeChildren);
// case SqlDbAccess.Db2: return VZF.Data.Db2.Db.GetSearchResult(connectionString, toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, categoryId, forumIdToStartAt, userId, boardId, maxResults, useFullText, searchDisplayName, includeChildren);
// case SqlDbAccess.Other: return VZF.Data.Other.Db.GetSearchResult(connectionString, toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, categoryId, forumIdToStartAt, userId, boardId, maxResults, useFullText, searchDisplayName, includeChildren);
default:
throw new ArgumentOutOfRangeException(dataEngine);
}
DataTable dtResult;
using (var sc = new VzfSqlCommand(mid))
{
if (dataEngine == SqlDbAccess.MySql)
{
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@i_Limit", maxResults.ToString(CultureInfo.InvariantCulture)));
}
sc.CommandText.AppendQuery(sql);
dtResult = sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.Text, true);
}
if (dataEngine == SqlDbAccess.MySql)
{
string oldUid = null;
string oldFid = null;
foreach (DataRow dr in dtResult.Rows)
{
if (oldUid == dr["UserID"].ToString() || oldFid == dr["ForumID"].ToString())
{
continue;
}
using (var sc = new VzfSqlCommand(mid))
{
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@i_UserID", dr["UserID"]));
sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@i_ForumID", dr["ForumID"]));
sc.CommandText.AppendQuery(
string.Format(
"SELECT {0}(@i_UserID,@i_ForumID);",
SqlDbAccess.GetVzfObjectName("vaccess_s_readaccess_combo", mid)));
if (Convert.ToInt32(sc.ExecuteScalar(CommandType.Text)) == 0)
{
dr.Delete();
}
}
oldUid = dr["UserID"].ToString();
oldFid = dr["ForumID"].ToString();
}
dtResult.AcceptChanges();
}
return dtResult;
}
protected IEnumerable<SearchCondition> BuildWhoConditions(
[NotNull] string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, bool searchDisplayName)
{
CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
var conditions = new List<SearchCondition>();
string conditionSql = string.Empty;
SearchConditionType conditionType = SearchConditionType.AND;
if (searchFromWhoMethod == SearchWhatFlags.AnyWords)
{
conditionType = SearchConditionType.OR;
}
var wordList = new List<string> { toSearchFromWho };
if (searchFromWhoMethod == SearchWhatFlags.AllWords || searchFromWhoMethod == SearchWhatFlags.AnyWords)
{
wordList =
toSearchFromWho.Replace(@"""", string.Empty).Split(' ').Where(x => x.IsSet()).Select(x => x.Trim()).ToList();
}
foreach (string word in wordList)
{
int userId;
if (int.TryParse(word, out userId))
{
conditionSql = "c.UserID IN ({0})".FormatWith(userId);
}
else
{
if (searchFromWhoMethod == SearchWhatFlags.ExactMatch)
{
conditionSql = "(c.Username IS NULL AND b.{1} = N'{0}') OR (c.Username = N'{0}')".FormatWith(
word, searchDisplayName ? "DisplayName" : "Name");
}
else
{
conditionSql = "(c.Username IS NULL AND b.{1} LIKE N'%{0}%') OR (c.Username LIKE N'%{0}%')".FormatWith(
word, searchDisplayName ? "DisplayName" : "Name");
}
}
conditions.Add(new SearchCondition { Condition = conditionSql, ConditionType = conditionType });
}
return conditions;
}
/// <summary>
/// Returns Search results
/// </summary>
/// <param name="toSearchWhat">
/// The to Search What.
/// </param>
/// <param name="toSearchFromWho">
/// The to Search From Who.
/// </param>
/// <param name="searchFromWhoMethod">
/// The search From Who Method.
/// </param>
/// <param name="searchWhatMethod">
/// The search What Method.
/// </param>
/// <param name="forumIDToStartAt">
/// The forum ID To Start At.
/// </param>
/// <param name="userID">
/// The user ID.
/// </param>
/// <param name="boardId">
/// The board Id.
/// </param>
/// <param name="maxResults">
/// The max Results.
/// </param>
/// <param name="useFullText">
/// The use Full Text.
/// </param>
/// <param name="searchDisplayName">
/// The search Display Name.
/// </param>
/// <returns>
/// Results
/// </returns>
public static DataTable GetSearchResult(
[NotNull] string toSearchWhat,
[NotNull] string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int forumIDToStartAt,
int userID,
int boardId,
int maxResults,
bool useFullText,
bool searchDisplayName)
{
if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
IEnumerable<int> forumIds = new List<int>();
if (forumIDToStartAt != 0)
{
forumIds = ForumListAll(boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
}
string searchSql = new SearchBuilder().BuildSearchSql(
toSearchWhat,
toSearchFromWho,
searchFromWhoMethod,
searchWhatMethod,
userID,
searchDisplayName,
boardId,
maxResults,
useFullText,
forumIds);
using (var cmd = MsSqlDbAccess.GetCommand(searchSql, true))
{
return MsSqlDbAccess.Current.GetData(cmd);
}
}
/// <summary>
/// The build search sql.
/// </summary>
/// <param name="mid">
/// The mid.
/// </param>
/// <param name="toSearchWhat">
/// The to search what.
/// </param>
/// <param name="toSearchFromWho">
/// The to search from who.
/// </param>
/// <param name="searchFromWhoMethod">
/// The search from who method.
/// </param>
/// <param name="searchWhatMethod">
/// The search what method.
/// </param>
/// <param name="userId">
/// The user id.
/// </param>
/// <param name="searchDisplayName">
/// The search display name.
/// </param>
/// <param name="boardId">
/// The board id.
/// </param>
/// <param name="maxResults">
/// The max results.
/// </param>
/// <param name="useFullText">
/// The use full text.
/// </param>
/// <param name="categoriesIds">
/// The categoriesIds.
/// </param>
/// <param name="ids">
/// The forum ids.
/// </param>
/// <param name="forumIdToStartAt">
/// The forum Id To Start At.
/// </param>
/// <param name="culture">
/// The culture.
/// </param>
/// <returns>
/// The build search sql.
/// </returns>
public string BuildSearchSql(
int? mid,
[NotNull] string toSearchWhat,
[NotNull] string toSearchFromWho,
SearchWhatFlags searchFromWhoMethod,
SearchWhatFlags searchWhatMethod,
int userId,
bool searchDisplayName,
int boardId,
int maxResults,
bool useFullText,
string categoriesIds,
string ids,
[NotNull] IEnumerable<int> forumIdToStartAt,
string culture)
{
CodeContracts.ArgumentNotNull(toSearchWhat, "toSearchWhat");
CodeContracts.ArgumentNotNull(toSearchFromWho, "toSearchFromWho");
string limitString = string.Empty;
string orderString = string.Empty;
var searchSql = new StringBuilder();
string forumIDs = ids;
string ftsLanguage = "simple";
culture = culture.Substring(0, 2);
foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.SpecificCultures))
{
if (ci.Parent.Name == culture)
{
switch (culture)
{
case "en":
ftsLanguage = "english";
break;
case "ru":
ftsLanguage = "russian";
break;
case "sp":
ftsLanguage = "spanish";
break;
case "fr":
ftsLanguage = "french";
break;
case "de":
ftsLanguage = "german";
break;
case "da":
ftsLanguage = "danish";
break;
case "nl":
ftsLanguage = "dutch";
break;
case "fi":
ftsLanguage = "finnish";
break;
case "hu":
ftsLanguage = "hungarian";
break;
case "it":
ftsLanguage = "italian";
break;
case "nn":
ftsLanguage = "norwegian";
break;
case "pt":
ftsLanguage = "portugues";
break;
case "ro":
ftsLanguage = "romanian";
break;
case "se":
ftsLanguage = "swedish";
break;
case "tr":
ftsLanguage = "turkish";
break;
}
}
break;
}
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
searchSql.Append(maxResults == 0 ? "SELECT " : "SELECT DISTINCT ");
if (maxResults > 0)
{
limitString += string.Format(" LIMIT {0} ", maxResults);
}
searchSql.Append(
" a.forumid, a.topicid, a.topic, b.userid, COALESCE(c.username, b.name) as Name, c.messageid as \"MessageID\", c.posted, c.message as \"Message\", c.flags FROM ");
searchSql.Append(SqlDbAccess.GetVzfObjectName("topic", mid));
searchSql.Append(" a JOIN ");
searchSql.Append(SqlDbAccess.GetVzfObjectName("forum", mid));
searchSql.Append(" f ON f.forumid = a.forumid LEFT JOIN ");
searchSql.Append(SqlDbAccess.GetVzfObjectName("message", mid));
searchSql.Append(" c ON a.topicid = c.topicid LEFT JOIN ");
searchSql.Append(SqlDbAccess.GetVzfObjectName("user", mid));
searchSql.Append(" b ON c.userid = b.userid join ");
searchSql.Append(SqlDbAccess.GetVzfObjectName("vaccess", mid));
searchSql.Append(" x ON x.forumid=a.forumid ");
searchSql.Append(
"WHERE x.readaccess<>0 AND x.userid={0} AND c.isapproved IS TRUE AND a.topicmovedid IS NULL AND a.isdeleted IS FALSE AND c.isdeleted IS FALSE "
.FormatWith(userId));
orderString += "ORDER BY a.forumid ";
string[] words;
bool bFirst = false;
if (!string.IsNullOrEmpty(toSearchFromWho) || !string.IsNullOrEmpty(toSearchWhat))
{
searchSql.Append("AND (");
bFirst = true;
}
if (!string.IsNullOrEmpty(toSearchFromWho))
{
// generate user search sql...
switch (searchFromWhoMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql.Append(" AND ");
}
else
{
bFirst = false;
}
// searchSql += string.Format(" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))", word);
if (!int.TryParse(word, out userId))
{
if (searchDisplayName)
{
searchSql.Append(
string.Format(
" ((c.userdisplayname IS NULL AND b.displayname ~* '.*{0}.*') OR (c.userdisplayname ~* '.*{0}.*'))",
word));
}
else
{
searchSql.Append(
string.Format(
" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))",
word));
}
}
else
{
searchSql.Append(string.Format(" (c.userid IN ({0}))", userId));
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql.Append(" OR ");
}
else
{
bFirst = false;
}
if (searchDisplayName)
{
searchSql.Append(
string.Format(
" ((c.userdisplayname IS NULL AND b.displayname ~* '.*{0}.*') OR (c.userdisplayname ~* '.*{0}.*'))",
word));
}
else
{
searchSql.Append(
string.Format(
" ((c.username IS NULL AND b.name ~* '.*{0}.*') OR (c.username ~* '.*{0}.*'))",
word));
}
}
break;
case SearchWhatFlags.ExactMatch:
// searchSql += string.Format(" ((c.username IS NULL AND b.name = '{0}' ) OR (c.username = '******' ))", toSearchFromWho);
if (!int.TryParse(toSearchFromWho, out userId))
{
if (searchDisplayName)
{
searchSql.Append(
string.Format(
" ((c.userdisplayname IS NULL AND b.displayname = '{0}') OR (c.userdisplayname = '{0}'))",
toSearchFromWho));
}
else
{
searchSql.Append(
string.Format(
" ((c.username IS NULL AND b.name = '{0}') OR (c.username = '******'))",
toSearchFromWho));
}
}
else
{
searchSql.Append(string.Format(" (c.userid IN ({0})) ", userId));
}
break;
}
searchSql.Append(") ");
}
if (!string.IsNullOrEmpty(toSearchWhat))
{
// generate message and topic search sql...
switch (searchWhatMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchWhat.RemoveDoubleWhiteSpaces().Split(' ');
if (useFullText)
{
string ftInner = string.Empty;
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst)
{
ftInner += " & ";
}
else
{
bFirst = false;
}
ftInner += string.Format("{0}", word);
}
// make final string...
searchSql.Append(
string.Format(
"select to_tsvector('{0}',c.message) @@ to_tsquery('{0}','{1}') OR to_tsvector('{0}',a.topic) @@ to_tsquery('{0}','{1}') ",
ftsLanguage, ftInner.Trim().Trim('&').Trim()));
}
else
{
foreach (string word in words)
{
if (!bFirst)
{
searchSql.Append(" AND ");
}
else
{
bFirst = false;
}
searchSql.Append(
string.Format("(c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' )", word));
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchWhat.RemoveDoubleWhiteSpaces().Split(' ');
if (useFullText)
{
string ftInner = string.Empty;
string ftInnerBody = string.Empty;
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst)
{
// ftInner += " OR ";
ftInnerBody += " | ";
}
else
{
bFirst = false;
}
// ftInner += string.Format(@"""{0}""", word);
ftInnerBody += string.Format("{0}", word);
if (int.TryParse(word, out userId))
{
// searchSql.Append(string.Format(" (c.userid IN ({0}))", userId));
}
else
{
/* searchSql.Append(
string.Format(
" ((c.username IS NULL AND b.name LIKE '%{0}%') OR (c.username like '{0}%'))",
word)); */
}
}
// make final string...
searchSql.Append(
string.Format(
"SELECT to_tsvector('{0}',c.message) @@ to_tsquery('{0}','{1}') OR to_tsvector('{0}',a.topic) @@ to_tsquery('{0}','{1}') ",
ftsLanguage,
ftInnerBody.Trim().Trim('|').Trim()));
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql.Append(" OR ");
else bFirst = false;
searchSql.Append(
string.Format("c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' ", word));
}
}
break;
case SearchWhatFlags.ExactMatch:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = string.Empty;
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst)
{
ftInner += " & ";
}
else
{
bFirst = false;
}
ftInner += string.Format("{0}", word);
}
// make final string...
searchSql.Append(
string.Format(
"select to_tsvector('{0}',c.message) @@ to_tsquery('{0}','{1}') OR to_tsvector('{0}',a.topic) @@ to_tsquery('{0}','{1}') ",
ftsLanguage, ftInner.Trim().Trim('&').Trim()));
}
else
{
searchSql.Append(
string.Format("c.message ~* '.*{0}.*' OR a.topic ~* '.*{0}.*' ", toSearchWhat));
}
break;
}
searchSql.Append(") ");
}
// vzrus
if (categoriesIds.IsSet())
{
searchSql.Append(string.Format(" AND f.categoryid IN ({0})", categoriesIds));
}
if (forumIDs.IsSet())
{
searchSql.Append(string.Format(" AND a.forumid IN ({0})", forumIDs));
}
if (orderString != string.Empty)
{
orderString += ", ";
}
if (!orderString.Contains("ORDER BY"))
{
searchSql.Append("ORDER BY ");
}
searchSql.Append(orderString + "c.posted DESC ");
if (!orderString.Contains("LIMIT"))
{
searchSql.Append(limitString);
}
return searchSql.ToString();
}
/// <summary>
/// Returns Search results
/// </summary>
/// <param name="ToSearch"></param>
/// <param name="sf">Field to search</param>
/// <param name="sw">Search what</param>
/// <param name="fid"></param>
/// <param name="UserID">ID of user</param>
/// <returns>Results</returns>
public static DataTable GetSearchResult( string toSearchWhat, string toSearchFromWho, SearchWhatFlags searchFromWhoMethod, SearchWhatFlags searchWhatMethod, int forumIDToStartAt, int userID, int boardId, int maxResults, bool useFullText, bool searchDisplayName)
{
/* if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
IEnumerable<int> forumIds = new List<int>();
if (forumIDToStartAt != 0)
{
forumIds = ForumListAll(boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
}
string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);
using (FbCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
{
return MsSqlDbAccess.Current.GetData(cmd);
}
*/
// TODO: to move it to a class
if (toSearchWhat == "*")
{
toSearchWhat = string.Empty;
}
IEnumerable<int> forumIds = new List<int>();
if (forumIDToStartAt != 0)
{
forumIds = ForumListAll( boardId, userID, forumIDToStartAt).Select(f => f.ForumID ?? 0).Distinct();
}
/* string searchSql = new SearchBuilder().BuildSearchSql(toSearchWhat, toSearchFromWho, searchFromWhoMethod, searchWhatMethod, userID, searchDisplayName, boardId, maxResults, useFullText, forumIds);
using (FbCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
{
return MsSqlDbAccess.Current.GetData(cmd);
} */
// if ( ToSearch.Length == 0 )
// return new DataTable();
if (toSearchWhat == "*")
toSearchWhat = "";
string forumIDs = "";
string limitString = "";
string orderString = "";
if (forumIDToStartAt != 0)
{
DataTable dt = forum_listall_sorted( boardId, userID, null, false, forumIDToStartAt);
foreach (DataRow dr in dt.Rows)
forumIDs = forumIDs + Convert.ToInt32(dr["ForumID"]).ToString() + ",";
forumIDs = forumIDs.Substring(0, forumIDs.Length - 1);
}
// fix quotes for SQL insertion...
toSearchWhat = toSearchWhat.Replace("'", "''").Trim();
toSearchFromWho = toSearchFromWho.Replace("'", "''").Trim();
string searchSql = (maxResults == 0) ? "SELECT" : ("SELECT ");
if (maxResults > 0)
{
limitString += String.Format(" FIRST {0} ", maxResults.ToString());
searchSql += limitString;
}
searchSql += @" a.FORUMID AS ""ForumID"", a.TOPICID AS ""TopicID"", a.TOPIC AS ""Topic"", b.USERID AS ""UserID"" , COALESCE(c.USERNAME, b.NAME) AS ""Name"", c.MESSAGEID AS ""MessageID"", c.POSTED AS ""Posted"", '' AS ""Message"", c.FLAGS AS ""Flags""";
searchSql += " FROM " +
MsSqlDbAccess.GetObjectName("TOPIC") + " a LEFT JOIN "
+ MsSqlDbAccess.GetObjectName("MESSAGE") + @" c ON a.TOPICID = c.TOPICID LEFT JOIN "
+ MsSqlDbAccess.GetObjectName("USER") + @" b ON c.USERID = b.USERID join "
+ MsSqlDbAccess.GetObjectName("VACCESS") + @" x ON x.FORUMID=a.FORUMID ";
searchSql += String.Format(@"WHERE x.READACCESS<>0 AND x.USERID={0} AND c.ISAPPROVED <> 0 AND a.TOPICMOVEDID IS NULL AND a.ISDELETED = 0 AND c.ISDELETED = 0 ", userID);
orderString += @" ORDER BY a.FORUMID ";
string[] words;
bool bFirst;
if (!String.IsNullOrEmpty(toSearchFromWho))
{
searchSql += "AND (";
bFirst = true;
int userId;
// generate user search sql...
switch (searchFromWhoMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql += " AND ";
}
else
{
bFirst = false;
}
searchSql += string.Format(@" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))", word);
if (int.TryParse(word, out userId))
{
searchSql +=
string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
if (searchDisplayName)
{
searchSql +=
string.Format(" ((c.Username IS NULL AND b.DisplayName LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))", word);
}
else
{
searchSql +=
string.Format(" ((c.Username IS NULL AND b.Name LIKE '%{0}%') OR (c.Username LIKE '%{0}%'))", word);
}
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchFromWho.Split(' ');
foreach (string word in words)
{
if (!bFirst)
{
searchSql += " OR ";
}
else
{
if (searchDisplayName)
{
searchSql += string.Format(
" ((c.USERNAME IS NULL AND b.DISPLAYNAME = '{0}') OR (c.Username = '******')", toSearchFromWho);
}
else
{
searchSql += string.Format(@" ((c.USERNAME IS NULL AND b.NAME LIKE '%{0}%') OR (c.USERNAME LIKE '%{0}%'))", word);
}
}
}
break;
case SearchWhatFlags.ExactMatch:
if (int.TryParse(toSearchFromWho, out userId))
{
searchSql +=
string.Format(" (c.UserID IN ({0}))", userId);
}
else
{
searchSql += string.Format(@" ((c.USERNAME IS NULL AND b.NAME = '{0}' ) OR (c.USERNAME = '******' ))", toSearchFromWho);
}
break;
}
searchSql += ") ";
}
if (!String.IsNullOrEmpty(toSearchWhat))
{
searchSql += "AND (";
bFirst = true;
// generate message and topic search sql...
switch (searchWhatMethod)
{
case SearchWhatFlags.AllWords:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = "";
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst) ftInner += " AND "; else bFirst = false;
ftInner += String.Format(@"""{0}""", word);
}
// make final string...
searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' {0} ') OR CONTAINS (a.TOPIC, ' {0} ' ) )", ftInner);
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " AND "; else bFirst = false;
searchSql += String.Format(@"(c.MESSAGE like '%{0}%' OR a.TOPIC LIKE '%{0}%' )", word);
}
}
break;
case SearchWhatFlags.AnyWords:
words = toSearchWhat.Split(' ');
if (useFullText)
{
string ftInner = "";
// make the inner FULLTEXT search
foreach (string word in words)
{
if (!bFirst) ftInner += " OR "; else bFirst = false;
ftInner += String.Format(@"""{0}""", word);
}
// make final string...
searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' {0} ' ) OR CONTAINS (a.TOPIC, ' {0} ' ) )", ftInner);
}
else
{
foreach (string word in words)
{
if (!bFirst) searchSql += " OR "; else bFirst = false;
searchSql += String.Format(@"c.MESSAGE LIKE '%{0}%' OR a.TOPIC LIKE '%{0}%' ", word);
}
}
break;
case SearchWhatFlags.ExactMatch:
if (useFullText)
{
// searchSql += string.Format(@"( CONTAINS (c.MESSAGE, ' \"{0}\" ' ) OR CONTAINS (a.Topic, ' \"{0}\" ' )", toSearchWhat);
}
else
{
searchSql += string.Format(@"c.MESSAGE LIKE '%{0}%' OR a.TOPIC LIKE '%{0}%' ", toSearchWhat);
}
break;
}
searchSql += ") ";
}
// Ederon : 6/16/2007 - forum IDs start above 0, if forum id is 0, there is no forum filtering
if (forumIDToStartAt > 0)
{
searchSql += string.Format(@"AND a.FORUMID IN ({0})", forumIDs);
}
if (orderString != "") { orderString += ", "; }
if (!orderString.Contains("ORDER BY"))
{
searchSql += " ORDER BY ";
}
searchSql += orderString + @"c.POSTED DESC ";
using (FbCommand cmd = MsSqlDbAccess.GetCommand(searchSql, true))
{
return MsSqlDbAccess.Current.GetData(cmd);
}
}