/// <summary>
/// 创建搜索缓存
/// </summary>
/// <param name="cacheinfo">搜索缓存信息</param>
/// <returns>搜索缓存id</returns>
public int CreateSearchCache(SearchCacheInfo cacheinfo)
{
IDataParameter[] prams = {
DbHelper.MakeInParam("@keywords",(DbType)SqlDbType.NVarChar,255,cacheinfo.Keywords),
DbHelper.MakeInParam("@searchstring",(DbType)SqlDbType.NVarChar,255,cacheinfo.Searchstring),
DbHelper.MakeInParam("@ip",(DbType)SqlDbType.VarChar,15,cacheinfo.Ip),
DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,cacheinfo.Uid),
DbHelper.MakeInParam("@groupid",(DbType)SqlDbType.Int,4,cacheinfo.Groupid),
DbHelper.MakeInParam("@postdatetime", (DbType)SqlDbType.DateTime, 8, DateTime.Parse(cacheinfo.Postdatetime)),
DbHelper.MakeInParam("@expiration",(DbType)SqlDbType.VarChar,19,cacheinfo.Expiration),
DbHelper.MakeInParam("@topics",(DbType)SqlDbType.Int,4,cacheinfo.Topics),
DbHelper.MakeInParam("@tids",(DbType)SqlDbType.Text,0,cacheinfo.Tids)
};
return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}createsearchcache", BaseConfigs.GetTablePrefix), prams).ToString(), -1);
}
/// <summary>
/// 根据指定条件进行搜索
/// </summary>
/// <param name="posttableid">帖子表id</param>
/// <param name="userid">用户id</param>
/// <param name="usergroupid">用户组id</param>
/// <param name="keyword">关键字</param>
/// <param name="posterid">发帖者id</param>
/// <param name="type">搜索类型</param>
/// <param name="searchforumid">搜索版块id</param>
/// <param name="keywordtype">关键字类型</param>
/// <param name="searchtime">搜索时间</param>
/// <param name="searchtimetype">搜索时间类型</param>
/// <param name="resultorder">结果排序方式</param>
/// <param name="resultordertype">结果类型类型</param>
/// <returns>如果成功则返回searchid, 否则返回-1</returns>
public int Search(int posttableid, int userid, int usergroupid, string keyword, int posterid, string type, string searchforumid, int keywordtype, int searchtime, int searchtimetype, int resultorder, int resultordertype)
{
// 超过30分钟的缓存纪录将被删除
DatabaseProvider.GetInstance().DeleteExpriedSearchCache();
string sql = string.Empty;
StringBuilder strTids = new StringBuilder();
SearchType searchType = SearchType.TopicTitle;
switch (keywordtype)
{
case 0:
searchType = SearchType.PostTitle;
if (type == "digest")
{
searchType = SearchType.DigestTopic;
}
break;
case 1:
searchType = SearchType.PostContent;
break;
case 2:
searchType = SearchType.SpacePostTitle;
break;
case 3:
searchType = SearchType.AlbumTitle;
break;
case 8:
searchType = SearchType.ByPoster;
break;
}
switch (searchType)
{
case SearchType.All:
break;
case SearchType.DigestTopic:
sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 1, keyword);
break;
case SearchType.TopicTitle:
sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 0, keyword);
break;
case SearchType.PostTitle:
sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 0, keyword);
break;
case SearchType.PostContent:
sql = GetSearchPostContentSQL(posterid, searchforumid, resultorder, resultordertype, searchtime, searchtimetype, posttableid, new StringBuilder(keyword));
break;
case SearchType.SpacePostTitle:
sql = GetSearchSpacePostTitleSQL(posterid, resultorder, resultordertype, searchtime, searchtimetype, keyword);
break;
case SearchType.AlbumTitle:
sql = GetSearchAlbumTitleSQL(posterid, resultorder, resultordertype, searchtime, searchtimetype, keyword);
break;
case SearchType.ByPoster:
sql = GetSearchByPosterSQL(posterid, posttableid);
break;
default:
sql = GetSearchTopicTitleSQL(posterid, searchforumid, resultorder, resultordertype, 0, keyword);
break;
}
#region
/*
// 关键词与作者至少有一个条件不为空
if (keyword.Equals(""))//按作者搜索
{
if (type == "digest")
{
strSQL.AppendFormat("SELECT [tid] FROM [{0}topics] WHERE [digest]>0 AND [posterid]=", BaseConfigs.GetTablePrefix);
strSQL.Append(posterid);
}
else if (type == "post")
{
strSQL.AppendFormat("SELECT [pid] FROM [{0}posts{1}] WHERE [posterid]=", BaseConfigs.GetTablePrefix, posttableid);
strSQL.Append(posterid);
}
else
{
strSQL.AppendFormat("SELECT [tid] FROM [{0}topics] WHERE [posterid]=", BaseConfigs.GetTablePrefix);
strSQL.Append(posterid);
}
//所属板块判断
if (!searchforumid.Equals(""))
{
strSQL.Append(" AND [fid] IN (");
strSQL.Append(searchforumid);
strSQL.Append(")");
}
strSQL.Append(" ORDER BY ");
switch (resultorder)
{
case 1:
strSQL.Append("[tid]");
break;
case 2:
strSQL.Append("[replies]");
break;
case 3:
strSQL.Append("[views]");
break;
default:
strSQL.Append("[postdatetime]");
break;
}
if (resultordertype == 1)
{
strSQL.Append(" ASC");
}
else
{
strSQL.Append(" DESC");
}
}
else
{
// 过滤危险字符
keyword = Regex.Replace(keyword, "--|;|'|\"", "", RegexOptions.Compiled | RegexOptions.Multiline);
StringBuilder strKeyWord = new StringBuilder(keyword);
// 替换转义字符
strKeyWord.Replace("'", "''");
strKeyWord.Replace("%", "[%]");
strKeyWord.Replace("_", "[_]");
strKeyWord.Replace("[", "[[]");
// 将SQL查询条件循序指定为"forumid, posterid, 搜索时间范围, 关键词"
if (keywordtype == 0)
{
strSQL.AppendFormat("SELECT [tid] FROM [{0}topics] WHERE", BaseConfigs.GetTablePrefix);
if (!searchforumid.Equals(""))
{
strSQL.Append(" [fid] IN (");
strSQL.Append(searchforumid);
strSQL.Append(") AND ");
}
if (posterid != -1)
{
strSQL.Append("[posterid]=");
strSQL.Append(posterid);
strSQL.Append(" AND ");
}
if (searchtime != 0)
{
strSQL.Append("[postdatetime]");
if (searchtimetype == 1)
{
strSQL.Append("<");
}
else
{
strSQL.Append(">'");
}
strSQL.Append(DateTime.Now.AddDays(searchtime * -1).ToString("yyyy-MM-dd 00:00:00"));
strSQL.Append("'AND ");
}
string[] keywordlist = Utils.SplitString(strKeyWord.ToString(), " ");
strKeyWord = new StringBuilder();
for (int i = 0; i < keywordlist.Length; i++)
{
strKeyWord.Append(" OR [title] ");
strKeyWord.Append("LIKE '%");
strKeyWord.Append(keywordlist[i]);
strKeyWord.Append("%' ");
}
strSQL.Append(strKeyWord.ToString().Substring(3));
strSQL.Append("ORDER BY ");
switch (resultorder)
{
case 1:
strSQL.Append("[tid]");
break;
case 2:
strSQL.Append("[replies]");
break;
case 3:
strSQL.Append("[views]");
break;
default:
strSQL.Append("[lastpost]");
break;
}
if (resultordertype == 1)
{
strSQL.Append(" ASC");
}
else
{
strSQL.Append(" DESC");
}
}
else
{
string orderfield = "lastpost";
switch (resultorder)
{
case 1:
orderfield = "tid";
break;
case 2:
orderfield = "replies";
break;
case 3:
orderfield = "views";
break;
default:
orderfield = "lastpost";
break;
}
strSQL.AppendFormat("SELECT DISTINCT [{0}posts{1}].[tid],[{0}topics].[{2}] FROM [{0}posts{1}] LEFT JOIN [{0}topics] ON [{0}topics].[tid]=[{0}posts{1}].[tid] WHERE ", BaseConfigs.GetTablePrefix, posttableid, orderfield);
if (!searchforumid.Equals(""))
{
strSQL.AppendFormat("[{0}posts{1}].[fid] IN (", BaseConfigs.GetTablePrefix, posttableid);
strSQL.Append(searchforumid);
strSQL.Append(") AND ");
}
if (posterid != -1)
{
strSQL.AppendFormat("[{0}posts{1}].[posterid]=", BaseConfigs.GetTablePrefix, posttableid);
strSQL.Append(posterid);
strSQL.Append(" AND ");
}
if (searchtime != 0)
{
strSQL.AppendFormat("[{0}posts{1}].[postdatetime]", BaseConfigs.GetTablePrefix, posttableid);
if (searchtimetype == 1)
{
strSQL.Append("<");
}
else
{
strSQL.Append(">'");
}
strSQL.Append(DateTime.Now.AddDays(searchtime).ToString("yyyy-MM-dd 00:00:00"));
strSQL.Append("'AND ");
}
string[] keywordlist = Utils.SplitString(strKeyWord.ToString(), " ");
strKeyWord = new StringBuilder();
for (int i = 0; i < keywordlist.Length; i++)
{
strKeyWord.Append(" OR ");
if (GeneralConfigs.GetConfig().Fulltextsearch == 1)
{
strKeyWord.AppendFormat("CONTAINS(message, '\"*", BaseConfigs.GetTablePrefix, posttableid);
strKeyWord.Append(keywordlist[i]);
strKeyWord.Append("*\"') ");
}
else
{
strKeyWord.AppendFormat("[{0}posts{1}].[message] LIKE '%", BaseConfigs.GetTablePrefix, posttableid);
strKeyWord.Append(keywordlist[i]);
strKeyWord.Append("%' ");
}
}
strSQL.Append(strKeyWord.ToString().Substring(3));
strSQL.AppendFormat("ORDER BY [{0}topics].", BaseConfigs.GetTablePrefix);
switch (resultorder)
{
case 1:
strSQL.Append("[tid]");
break;
case 2:
strSQL.Append("[replies]");
break;
case 3:
strSQL.Append("[views]");
break;
default:
strSQL.Append("[lastpost]");
break;
}
if (resultordertype == 1)
{
strSQL.Append(" ASC");
}
else
{
strSQL.Append(" DESC");
}
}
}
*/
#endregion
if (sql == string.Empty)
{
return -1;
}
IDataParameter[] prams2 = {
DbHelper.MakeInParam("@searchstring",(DbType)SqlDbType.VarChar,255, sql),
DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,userid),
DbHelper.MakeInParam("@groupid",(DbType)SqlDbType.Int,4,usergroupid)
};
int searchid = Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, string.Format(@"SELECT TOP 1 [searchid] FROM [{0}searchcaches] WHERE [searchstring]=@searchstring AND [groupid]=@groupid", BaseConfigs.GetTablePrefix), prams2), -1);
if (searchid > -1)
{
return searchid;
}
IDataReader reader;
try
{
reader = DbHelper.ExecuteReader(CommandType.Text, sql);
}
catch
{
ConfirmFullTextEnable();
reader = DbHelper.ExecuteReader(CommandType.Text, sql);
}
int rowcount = 0;
if (reader != null)
{
switch (searchType)
{
case SearchType.All:
case SearchType.DigestTopic:
case SearchType.TopicTitle:
case SearchType.PostTitle:
case SearchType.PostContent:
strTids.Append("<ForumTopics>");
break;
case SearchType.SpacePostTitle:
strTids.Append("<SpacePosts>");
break;
case SearchType.AlbumTitle:
strTids.Append("<Albums>");
break;
case SearchType.ByPoster:
strTids = GetSearchByPosterResult(reader);
SearchCacheInfo cacheinfo = new SearchCacheInfo();
cacheinfo.Keywords = keyword;
cacheinfo.Searchstring = sql;
cacheinfo.Postdatetime = Utils.GetDateTime();
cacheinfo.Topics = rowcount;
cacheinfo.Tids = strTids.ToString();
cacheinfo.Uid = userid;
cacheinfo.Groupid = usergroupid;
cacheinfo.Ip = DNTRequest.GetIP();
cacheinfo.Expiration = Utils.GetDateTime();
reader.Close();
return CreateSearchCache(cacheinfo);
}
while (reader.Read())
{
strTids.Append(reader[0].ToString());
strTids.Append(",");
rowcount++;
}
if (rowcount > 0)
{
strTids.Remove(strTids.Length - 1, 1);
switch (searchType)
{
case SearchType.All:
case SearchType.DigestTopic:
case SearchType.TopicTitle:
case SearchType.PostTitle:
case SearchType.PostContent:
strTids.Append("</ForumTopics>");
break;
case SearchType.SpacePostTitle:
strTids.Append("</SpacePosts>");
break;
case SearchType.AlbumTitle:
strTids.Append("</Albums>");
break;
}
SearchCacheInfo cacheinfo = new SearchCacheInfo();
cacheinfo.Keywords = keyword;
cacheinfo.Searchstring = sql;
cacheinfo.Postdatetime = Utils.GetDateTime();
cacheinfo.Topics = rowcount;
cacheinfo.Tids = strTids.ToString();
cacheinfo.Uid = userid;
cacheinfo.Groupid = usergroupid;
cacheinfo.Ip = DNTRequest.GetIP();
cacheinfo.Expiration = Utils.GetDateTime();
reader.Close();
return CreateSearchCache(cacheinfo);
}
reader.Close();
}
return -1;
}
/// <summary>
/// 创建搜索缓存
/// </summary>
/// <param name="cacheinfo">搜索缓存信息</param>
/// <returns>搜索缓存id</returns>
public int CreateSearchCache(SearchCacheInfo cacheInfo)
{
DbParameter[] parms = {
DbHelper.MakeInParam("@keywords",(DbType)NpgsqlDbType.Varchar,255,cacheInfo.Keywords),
DbHelper.MakeInParam("@searchstring",(DbType)NpgsqlDbType.Varchar,255,cacheInfo.Searchstring),
DbHelper.MakeInParam("@ip",(DbType)NpgsqlDbType.Varchar,15,cacheInfo.Ip),
DbHelper.MakeInParam("@uid",(DbType)NpgsqlDbType.Integer,4,cacheInfo.Uid),
DbHelper.MakeInParam("@groupid",(DbType)NpgsqlDbType.Integer,4,cacheInfo.Groupid),
DbHelper.MakeInParam("@postdatetime", (DbType)NpgsqlDbType.Timestamp, 8, DateTime.Parse(cacheInfo.Postdatetime)),
DbHelper.MakeInParam("@expiration",(DbType)NpgsqlDbType.Varchar,19,cacheInfo.Expiration),
DbHelper.MakeInParam("@topics",(DbType)NpgsqlDbType.Integer,4,cacheInfo.Topics),
DbHelper.MakeInParam("@tids",(DbType)NpgsqlDbType.Text,0,cacheInfo.Tids)
};
return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure, string.Format("{0}createsearchcache", BaseConfigs.GetTablePrefix), parms), -1);
}
/// <summary>
/// 搜索
/// </summary>
/// <param name="spaceenabled">空间是否开启</param>
/// <param name="albumenable">相册是否开启</param>
/// <param name="posttableid">帖子表id</param>
/// <param name="userid">用户id</param>
/// <param name="usergroupid">用户组id</param>
/// <param name="keyword">关键字</param>
/// <param name="posterid">发帖者id</param>
/// <param name="searchType">搜索类型</param>
/// <param name="searchforumid">搜索版块id</param>
/// <param name="searchtime">搜索时间</param>
/// <param name="searchtimetype">搜索时间类型</param>
/// <param name="resultorder">结果排序方式</param>
/// <param name="resultordertype">结果类型类型</param>
/// <returns>如果成功则返回searchid, 否则返回-1</returns>
public int Search(bool spaceEnabled, bool albumEnabled, int postTableId, int userId, int userGroupId, string keyWord, int posterId, SearchType searchType, string searchForumId, int searchTime, int searchTimeType, int resultOrder, int resultOrderType)
{
// 超过30分钟的缓存纪录将被删除
DatabaseProvider.GetInstance().DeleteExpriedSearchCache();
string sql = string.Empty;
StringBuilder strTids = new StringBuilder();
switch (searchType)
{
case SearchType.TopicTitle:
sql = GetSearchTopicTitleSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, 0, keyWord, postTableId);
break;
case SearchType.DigestTopic:
sql = GetSearchTopicTitleSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, 1, keyWord, postTableId);
break;
case SearchType.ByPoster:
sql = GetSearchByPosterSQL(spaceEnabled, albumEnabled, posterId, postTableId);
break;
case SearchType.PostContent:
sql = GetSearchPostContentSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, postTableId, new StringBuilder(keyWord));
break;
case SearchType.SpacePostTitle:
sql = GetSearchSpacePostTitleSQL(posterId, resultOrder, resultOrderType, searchTime, searchTimeType, keyWord);
break;
case SearchType.AlbumTitle:
sql = GetSearchAlbumTitleSQL(posterId, resultOrder, resultOrderType, searchTime, searchTimeType, keyWord);
break;
default:
sql = GetSearchTopicTitleSQL(posterId, searchForumId, resultOrder, resultOrderType, searchTime, searchTimeType, 0, keyWord, postTableId);
break;
}
if (Utils.StrIsNullOrEmpty(sql))
return -1;
DbParameter[] prams2 = {
DbHelper.MakeInParam("@searchstring",(DbType)SqlDbType.VarChar,255, sql),
DbHelper.MakeInParam("@uid",(DbType)SqlDbType.Int,4,userId),
DbHelper.MakeInParam("@groupid",(DbType)SqlDbType.Int,4,userGroupId)
};
int searchid = Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, string.Format(@"SELECT TOP 1 [searchid] FROM [{0}searchcaches] WHERE [searchstring]=@searchstring AND [groupid]=@groupid", BaseConfigs.GetTablePrefix), prams2), -1);
if (searchid > -1)
return searchid;
IDataReader reader;
try
{
reader = DbHelper.ExecuteReader(CommandType.Text, sql);
}
catch
{
ConfirmFullTextEnable();
reader = DbHelper.ExecuteReader(CommandType.Text, sql);
}
int rowcount = 0;
if (reader != null)
{
switch (searchType)
{
case SearchType.DigestTopic:
case SearchType.TopicTitle:
case SearchType.PostContent:
strTids.Append("<ForumTopics>");
break;
case SearchType.SpacePostTitle:
strTids.Append("<SpacePosts>");
break;
case SearchType.AlbumTitle:
strTids.Append("<Albums>");
break;
case SearchType.ByPoster:
strTids = GetSearchByPosterResult(reader);
SearchCacheInfo cacheinfo = new SearchCacheInfo();
cacheinfo.Keywords = keyWord;
cacheinfo.Searchstring = sql;
cacheinfo.Postdatetime = Utils.GetDateTime();
cacheinfo.Topics = rowcount;
cacheinfo.Tids = strTids.ToString();
cacheinfo.Uid = userId;
cacheinfo.Groupid = userGroupId;
cacheinfo.Ip = DNTRequest.GetIP();
cacheinfo.Expiration = Utils.GetDateTime();
reader.Close();
return CreateSearchCache(cacheinfo);
}
while (reader.Read())
{
strTids.Append(reader[0].ToString());
strTids.Append(",");
rowcount++;
}
if (rowcount > 0)
{
strTids.Remove(strTids.Length - 1, 1);
switch (searchType)
{
case SearchType.DigestTopic:
case SearchType.TopicTitle:
case SearchType.PostContent:
strTids.Append("</ForumTopics>");
break;
case SearchType.SpacePostTitle:
strTids.Append("</SpacePosts>");
break;
case SearchType.AlbumTitle:
strTids.Append("</Albums>");
break;
}
SearchCacheInfo cacheinfo = new SearchCacheInfo();
cacheinfo.Keywords = keyWord;
cacheinfo.Searchstring = sql;
cacheinfo.Postdatetime = Utils.GetDateTime();
cacheinfo.Topics = rowcount;
cacheinfo.Tids = strTids.ToString();
cacheinfo.Uid = userId;
cacheinfo.Groupid = userGroupId;
cacheinfo.Ip = DNTRequest.GetIP();
cacheinfo.Expiration = Utils.GetDateTime();
reader.Close();
return CreateSearchCache(cacheinfo);
}
reader.Close();
}
return -1;
}
/// <summary>
/// 创建搜索缓存
/// </summary>
/// <param name="cacheinfo">搜索缓存信息</param>
/// <returns>搜索缓存id</returns>
public static int CreateSearchCache(SearchCacheInfo cacheinfo)
{
return DatabaseProvider.GetInstance().CreateSearchCache(cacheinfo);
}
