Example #1
0
        private string BuilderSearchThreadCondition(TopicFilter filter, IEnumerable<Guid> excludeRoleIDs, SqlQuery query, bool startWithWhere)
        {
            SqlConditionBuilder condition;
            if (startWithWhere)
                condition = new SqlConditionBuilder(SqlConditionStart.Where);
            else
                condition = new SqlConditionBuilder(SqlConditionStart.None);

            condition += (filter.ForumID == null ? "" : ("AND [ForumID] = @ForumID "));
            condition += (filter.UserID == null ? "" : ("AND [PostUserID] = @UserID "));
            condition += (filter.TopicID == null ? "" : ("AND [ThreadID] = @TopicID "));
            condition += (string.IsNullOrEmpty(filter.CreateIP) ? "" : ("AND [IPAddress] = @CreateIP "));

            if (filter.Status == null)
                condition += (filter.IncludeStick == false ? "AND [ThreadStatus] = 1 " : ("AND [ThreadStatus] < 4 "));
            else if (filter.Status.Value == ThreadStatus.Recycled)
                condition += ("AND [ThreadStatus] = 4 ");
            else if (filter.Status.Value == ThreadStatus.UnApproved)
                condition += ("AND [ThreadStatus] = 5 ");

            condition += (filter.IncludeValued == true ? "" : ("AND [IsValued] = 0 "));
            condition += (filter.MinReplyCount == null ? "" : ("AND [TotalReplies] >= @MinReplyCount "));
            condition += (filter.MaxReplyCount == null ? "" : ("AND [TotalReplies] <= @MaxReplyCount "));
            condition += (filter.MinViewCount == null ? "" : ("AND [TotalViews] >= @MinViewCount "));
            condition += (filter.MaxViewCount == null ? "" : ("AND [TotalViews] <= @MaxViewCount "));
            condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
            condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));

            if (string.IsNullOrEmpty(filter.KeyWord) == false)
            {
                if (filter.SearchMode == SearchArticleMethod.Subject)
                {
                    condition += " AND [Subject] LIKE '%'[email protected]+'%' ";
                }
                else if (filter.SearchMode == SearchArticleMethod.FullText)
                {
                    condition += " AND [Content] LIKE '%'[email protected]+'%' ";
                }
                else
                {
                    condition += " AND ([Subject] LIKE '%'[email protected]+'%' OR [Content] LIKE '%'[email protected]+'%') ";
                }

                query.CreateParameter<string>("@keyword", filter.KeyWord, SqlDbType.NVarChar, 256);
            }

            condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[PostUserID]", excludeRoleIDs, query));

            if (filter.ForumID != null)
                query.CreateParameter<int?>("@ForumID", filter.ForumID, SqlDbType.Int);
            if (string.IsNullOrEmpty(filter.CreateIP) == false)
                query.CreateParameter<string>("@CreateIP", filter.CreateIP, SqlDbType.NVarChar, 64);
            if (filter.TopicID != null)
                query.CreateParameter<int?>("@TopicID", filter.TopicID, SqlDbType.Int);
            if (filter.MaxReplyCount != null)
                query.CreateParameter<int?>("@MaxReplyCount", filter.MaxReplyCount, SqlDbType.Int);
            if (filter.MaxViewCount != null)
                query.CreateParameter<int?>("@MaxViewCount", filter.MaxViewCount, SqlDbType.Int);
            if (filter.MinReplyCount != null)
                query.CreateParameter<int?>("@MinReplyCount", filter.MinReplyCount, SqlDbType.Int);
            if (filter.MinViewCount != null)
                query.CreateParameter<int?>("@MinViewCount", filter.MinViewCount, SqlDbType.Int);
            if (filter.UserID != null)
                query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
            if (filter.BeginDate != null)
                query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
            if (filter.EndDate != null)
                query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);

            return condition.ToString();
        }
Example #2
0
        public override ThreadCollectionV5 GetThreads(int pageNumber, TopicFilter filter, Guid[] excludeRoleIDs, ref int totalCount)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.Pager.IsDesc = filter.IsDesc;

                switch (filter.Order)
                {
                    case TopicFilter.OrderBy.TopicID:
                        query.Pager.SortField = "ThreadID";
                        break;
                    case TopicFilter.OrderBy.LastReplyDate:
                        query.Pager.SortField = "UpdateDate";
                        break;
                    case TopicFilter.OrderBy.ReplyCount:
                        query.Pager.SortField = "TotalReplies";
                        break;
                    default:
                        query.Pager.SortField = "TotalViews";
                        break;
                }

                query.Pager.PageNumber = pageNumber;
                query.Pager.PageSize = filter.PageSize;
                query.Pager.TotalRecords = totalCount;
                query.Pager.SelectCount = true;
                query.Pager.TableName = "[bx_TopicsWithContents]";

                query.Pager.Condition = BuilderSearchThreadCondition(filter, excludeRoleIDs, query, false);

                ThreadCollectionV5 threads;
                using (XSqlDataReader reader = query.ExecuteReader())
                {

                    threads = new ThreadCollectionV5(reader);

                    if (reader.NextResult())
                    {
                        if (reader.Read())
                            totalCount = reader.Get<int>(0);
                    }
                    return threads;
                }
            }
        }
Example #3
0
        public override DeleteResult DeleteSearchTopics(TopicFilter filter, IEnumerable<Guid> excludeRoleIDs, bool getDeleteResult, int topCount, out int deletedCount, out List<int> threadIDs)
        {

            using (SqlQuery query = new SqlQuery())
            {

                string conditon = BuilderSearchThreadCondition(filter, excludeRoleIDs, query, false);

                query.CommandText = @"
DECLARE @Table table(TempTopicID int,TempUserID int,TempForumID int,TempThreadStatus tinyint);
INSERT INTO @Table SELECT TOP (@TopCount) [ThreadID],[PostUserID],[ForumID],[ThreadStatus] FROM [bx_TopicsWithContents] WITH (NOLOCK) WHERE " + conditon + @";
SELECT TempTopicID FROM @Table;
--SELECT FileID FROM [bx_Attachments] WHERE PostID IN(SELECT PostID FROM [bx_Posts] WHERE ThreadID IN(SELECT TempTopicID FROM @Table))
--    AND FileID NOT IN(SELECT FileID FROM [bx_Attachments] WHERE PostID NOT IN(SELECT PostID FROM [bx_Posts] WHERE ThreadID IN(SELECT TempTopicID FROM @Table)));
";

                if (getDeleteResult)
                {
                    query.CommandText += @"
SELECT [TempUserID],[TempForumID],COUNT(*) AS [Count] FROM @Table WHERE TempThreadStatus<4 GROUP BY [TempUserID],[TempForumID];";

                }


                query.CommandText = query.CommandText + @"
DELETE [bx_Threads] WHERE ThreadID IN(SELECT TempTopicID FROM @Table);
SELECT @@ROWCOUNT;
";
                query.CreateTopParameter("@TopCount", topCount);

                threadIDs = new List<int>();
                //                fileIDs = new List<string>();
                deletedCount = 0;
                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    DeleteResult deleteResult = new DeleteResult();

                    while (reader.Read())
                    {
                        threadIDs.Add(reader.Get<int>("TempTopicID"));
                    }
                    //if (reader.NextResult())
                    //{
                    //    while (reader.Read())
                    //    {
                    //        fileIDs.Add(reader.Get<string>("FileID"));
                    //    }
                    //}

                    if (getDeleteResult)
                    {
                        if (reader.NextResult())
                        {
                            while (reader.Read())
                            {
                                deleteResult.Add(reader.Get<int>("TempUserID"), reader.Get<int>("Count"), reader.Get<int>("TempForumID"));
                            }
                        }
                    }
                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            deletedCount = reader.Get<int>(0);
                        }
                    }
                    return deleteResult;
                }
            }
        }