예제 #1
0
        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);
        }
예제 #2
0
        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);
        }
예제 #3
0
        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);
        }