public List <Topic> GetSubscribedTopics(int userID, int startRow, int pageSize)
{
var list = new List <Topic>();
const string sql = @"
DECLARE @Counter int
SET @Counter = (@StartRow + @PageSize - 1)
SET ROWCOUNT @Counter;
WITH Entries AS (
SELECT ROW_NUMBER() OVER (ORDER BY IsPinned DESC, LastPostTime DESC)
AS Row, pf_Topic.TopicID, pf_Topic.ForumID, pf_Topic.Title, pf_Topic.ReplyCount, pf_Topic.ViewCount,
pf_Topic.StartedByUserID, pf_Topic.StartedByName, pf_Topic.LastPostUserID, pf_Topic.LastPostName,
pf_Topic.LastPostTime, pf_Topic.IsClosed, pf_Topic.IsPinned, pf_Topic.IsDeleted, pf_Topic.IsIndexed, pf_Topic.UrlName
FROM pf_Topic JOIN pf_SubscribeTopic S ON pf_Topic.TopicID = S.TopicID
WHERE S.UserID = @UserID AND pf_Topic.IsDeleted = 0)
SELECT TopicID, ForumID, Title, ReplyCount, ViewCount,
StartedByUserID, StartedByName, LastPostUserID, LastPostName,
LastPostTime, IsClosed, IsPinned, IsDeleted, IsIndexed, UrlName
FROM Entries
WHERE Row between
@StartRow and @StartRow + @PageSize - 1
SET ROWCOUNT 0";
_sqlObjectFactory.GetConnection().Using(connection =>
connection.Command(sql)
.AddParameter("@UserID", userID)
.AddParameter("@StartRow", startRow)
.AddParameter("@PageSize", pageSize)
.ExecuteReader()
.ReadAll(r => list.Add(TopicRepository.GetTopicFromReader(r))));
return(list);
}
public Topic GetNextTopicForIndexing()
{
Topic topic = null;
_sqlObjectFactory.GetConnection().Using(connection =>
connection.Command("SELECT TOP 1 " + TopicRepository.TopicFields + " FROM pf_Topic WHERE IsDeleted = 0 AND IsIndexed = 0 ORDER BY LastPostTime")
.ExecuteReader()
.ReadOne(r => topic = TopicRepository.GetTopicFromReader(r)));
return(topic);
}
public List <Topic> SearchTopics(string searchTerm, List <int> hiddenForums, SearchType searchType, int startRow, int pageSize, out int topicCount)
{
topicCount = 0;
if (searchTerm.Trim() == String.Empty)
{
return(new List <Topic>());
}
var topics = new List <Topic>();
var wordArray = searchTerm.Split(new [] { ' ' });
var wordList = new List <string>();
var junkWords = GetJunkWords();
var alphaNum = SearchService.SearchWordPattern;
for (var x = 0; x < wordArray.Length; x++)
{
foreach (Match match in alphaNum.Matches(wordArray[x]))
{
if (!junkWords.Contains(match.Value))
{
wordList.Add(match.Value);
}
}
}
var words = wordList.ToArray();
var wordParameters = new Dictionary <string, string>();
var sb = new StringBuilder();
sb.Append("WITH FirstEntries AS (SELECT ROW_NUMBER() OVER (PARTITION BY pf_Topic.TopicID ORDER BY pf_Topic.LastPostTime DESC) AS GroupRow, ");
sb.Append(TopicRepository.TopicFields);
sb.Append(", ((");
for (var x = 0; x < words.Length; x++)
{
sb.Append("q");
sb.Append(x.ToString());
sb.Append(".Rank");
if (x < words.Length - 1)
{
sb.Append("+");
}
}
sb.Append(")/" + words.Length + ") AS CompositeRank FROM pf_Topic ");
for (int x = 0; x < words.Length; x++)
{
string xstring = x.ToString();
sb.Append(" JOIN (SELECT TOP 10000 TopicID, pf_TopicSearchWords.Rank FROM pf_TopicSearchWords WHERE SearchWord = ");
var param = "@w" + x;
wordParameters.Add(param, words[x]);
sb.Append(param);
sb.Append(" ORDER BY pf_TopicSearchWords.Rank DESC) AS q");
sb.Append(xstring);
sb.Append(" ON pf_Topic.TopicID = q");
sb.Append(xstring);
sb.Append(".TopicID");
}
sb.Append(" WHERE NOT pf_Topic.IsDeleted = 1");
if (hiddenForums.Count > 0)
{
sb.Append(" AND");
for (int x = 0; x < hiddenForums.Count; x++)
{
if (x > 0)
{
sb.Append(" AND");
}
sb.Append(String.Format(" NOT ForumID = {0} ", hiddenForums[x]));
}
}
string orderBy;
switch (searchType)
{
case SearchType.Date:
orderBy = "LastPostTime DESC";
break;
case SearchType.Name:
orderBy = "StartedByName";
break;
case SearchType.Replies:
orderBy = "ReplyCount DESC";
break;
case SearchType.Title:
orderBy = "Title";
break;
default:
orderBy = "CompositeRank DESC, LastPostTime DESC";
break;
}
sb.Append("),\r\nEntries as (SELECT *,ROW_NUMBER() OVER (ORDER BY ");
sb.Append(orderBy);
sb.Append(") AS Row, COUNT(*) OVER () as cnt FROM FirstEntries WHERE GroupRow = 1)\r\nSELECT TopicID, ForumID, Title, ReplyCount, ViewCount, StartedByUserID, StartedByName, LastPostUserID, LastPostName, LastPostTime, IsClosed, IsPinned, IsDeleted, IsIndexed, UrlName, AnswerPostID, cnt FROM Entries WHERE Row BETWEEN @StartRow AND @StartRow + @PageSize - 1");
if (words.Length == 0)
{
return(topics);
}
var connection = _sqlObjectFactory.GetConnection();
var command = connection.Command(sb.ToString());
command.AddParameter("@StartRow", startRow);
command.AddParameter("@PageSize", pageSize);
foreach (var item in wordParameters)
{
command.AddParameter(item.Key, item.Value);
}
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
var topic = TopicRepository.GetTopicFromReader(reader);
topics.Add(topic);
topicCount = Convert.ToInt32(reader["cnt"]);
}
reader.Close();
connection.Close();
return(topics);
}