Beispiel #1
0
		public override ShareCollection GetUserFavorites(int favOwnerID, ShareType? favType, int pageNumber, int pageSize, ref int? totalCount)
		{
			using (SqlQuery query = new SqlQuery())
			{
                query.Pager.TableName = "[bx_SharesView]";
				query.Pager.SortField = "[ShareID]";
				query.Pager.IsDesc = true;
				query.Pager.PageNumber = pageNumber;
				query.Pager.PageSize = pageSize;
				query.Pager.TotalRecords = totalCount;
				query.Pager.SelectCount = true;

				SqlConditionBuilder cb = new SqlConditionBuilder(SqlConditionStart.None);

				cb.Append("[UserID] = @UserID");

				query.CreateParameter<int>("@UserID", favOwnerID, SqlDbType.Int);

				if (favType.HasValue && favType.Value != ShareType.All)
				{
					cb.Append("[Type] = @Type");

					query.CreateParameter<ShareType>("@Type", favType.Value, SqlDbType.TinyInt);
				}

				cb.Append("[PrivacyType] = 2");

				query.Pager.Condition = cb.ToString();

				using (XSqlDataReader reader = query.ExecuteReader())
				{
                    ShareCollection shares = new ShareCollection(reader);

                    if (reader.NextResult())
					{
                        if (totalCount == null && reader.Read())
						{
                            totalCount = reader.Get<int>(0);
						}

						shares.TotalRecords = totalCount.GetValueOrDefault();
					}

					return shares;
				}
			}
		}
Beispiel #2
0
        public override BlogArticleCollection GetUserBlogArticles(int userID, int? categoryID, int? tagID, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int? totalCount)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.Pager.TableName = "[bx_BlogArticles]";
                query.Pager.SortField = "[ArticleID]";
                query.Pager.IsDesc = true;
                query.Pager.PageNumber = pageNumber;
                query.Pager.PageSize = pageSize;
                query.Pager.TotalRecords = totalCount;
                query.Pager.SelectCount = true;

                SqlConditionBuilder cb = new SqlConditionBuilder(SqlConditionStart.None);

                if (categoryID.HasValue)
                {
                    cb.Append("[CategoryID] = @CategoryID");

                    query.CreateParameter<int>("@CategoryID", categoryID.Value, SqlDbType.Int);

                    if (categoryID.Value == 0)
                    {
                        cb.Append("[UserID] = @UserID");

                        query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                    }
                }
                else
                {
                    cb.Append("[UserID] = @UserID");

                    query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                }

                if (tagID.HasValue)
                {
                    cb.Append("[ArticleID] IN (SELECT [TargetID] FROM [bx_TagRelation] WHERE [Type] = 1 AND [TagID] = @TagID)");

                    query.CreateParameter<int>("@TagID", tagID.Value, SqlDbType.Int);
                }

                if (dataAccessLevel == DataAccessLevel.Normal)
                {
                    cb.Append("[PrivacyType] IN (0, 3)");
                }
                else if (dataAccessLevel == DataAccessLevel.Friend)
                {
                    cb.Append("[PrivacyType] IN (0, 1, 3)");
                }

                query.Pager.Condition = cb.ToString();

                using (XSqlDataReader reader = query.ExecuteReader())
                {

                    BlogArticleCollection articles = new BlogArticleCollection(reader);

                    if (reader.NextResult())
                    {
                        if (totalCount == null && reader.Read())
                        {
                            totalCount = reader.Get<int>(0);
                        }

                        articles.TotalRecords = totalCount.GetValueOrDefault();
                    }

                    return articles;
                }
            }
        }
Beispiel #3
0
        /*     [StoredProcedure(Name="bx_InsertBanUserLogs",Script= @"
     CREATE PROCEDURE {name}
         @OperationType varchar(50),
         @OperatorName  nvarchar(50),
         @Cause         nvarchar(1000),
         @ForumInfos    varchar(1000),
         @TargetID      int,
         @TargetName    nvarchar(200),
         @TargetIP      varchar(50)
     AS
     BEGIN
         SET NOCOUNT ON;
         INSERT INTO [bx_BanUserLogs](
             OperationType,
             OperatorName,
             Cause,
             ForumID,
             BeginDate,
             EndDate,
             TargetID,
             TargetName,
             TargetIP
         ) Values(
             @OperationType,
             @OperatorName,
             @Cause,
             @ForumID,
             @BeginDate,
             @EndDate,
             @TargetID,
             @TargetName,
             @TargetIP
         );
     END
     ")]

             public override void InsertBanUserOperationLog(BanUserOperation banuser)
             {
                 using (SqlQuery query = new SqlQuery())
                 {
                     query.CommandText = "bx_InsertBanUserLogs";
                     query.CommandType = CommandType.StoredProcedure;

                     query.CreateParameter<string>("@OperationType", banuser.OperationType, SqlDbType.VarChar, 50);
                     query.CreateParameter<string>("@OperationName", banuser.OperatorName, SqlDbType.NVarChar, 50);
                     query.CreateParameter<string>("@Cause", banuser.Cause, SqlDbType.NVarChar, 200);
                   //  query.CreateParameter<int>("@ForumID", banuser.ForumID, SqlDbType.Int);
                     query.CreateParameter<DateTime>("@BeginDate",banuser.BeginDate,SqlDbType.DateTime);
                     query.CreateParameter<DateTime>("@EndDate", banuser.EndDate, SqlDbType.DateTime);
                     query.CreateParameter<int>("@TargetID",banuser.UserID,SqlDbType.Int);
                     query.CreateParameter<string>("@TargetName",banuser.UserName,SqlDbType.NVarChar,50);
                     query.CreateParameter<string>("@TargetIP",banuser.UserIP,SqlDbType.VarChar,50);

                     query.ExecuteNonQuery();
                 }
             }
     */

        public override OperationLogCollection GetOperationLogsBySearch(int pageNumber, OperationLogFilter filter)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.Pager.TableName = "bx_OperationLogs";
                db.Pager.SortField = "LogID";
                db.Pager.PageSize = filter.PageSize;

                SqlConditionBuilder sb = new SqlConditionBuilder(SqlConditionStart.None);

                if (filter.OperatorID != null)
                {
                    sb.Append("OperatorID = @OperatorID");

                    db.CreateParameter<int>("@OperatorID", filter.OperatorID.Value, SqlDbType.Int);
                }
                else if (filter.OperatorName != null && filter.OperatorName != string.Empty)
                {
                    sb.Append("OperatorID IN (SELECT UserID FROM bx_Users WHERE Username = @OperatorName)");

                    db.CreateParameter<string>("@OperatorName", filter.OperatorName, SqlDbType.NVarChar, 50);
                }

                if (filter.OperatorIP != null && filter.OperatorIP != string.Empty)
                {
                    sb.Append("OperatorIP = @OperatorIP");

                    db.CreateParameter<string>("@OperatorIP", filter.OperatorIP, SqlDbType.VarChar, 50);
                }

                if (filter.OperationType != null && filter.OperationType != string.Empty)
                {
                    sb.Append("OperationType = @OperationType");

                    db.CreateParameter<string>("@OperationType", filter.OperationType, SqlDbType.VarChar, 100);
                }

                if (filter.TargetID_1 != null)
                {
                    sb.Append("TargetID_1 = @TargetID_1");

                    db.CreateParameter<int>("@TargetID_1", filter.TargetID_1.Value, SqlDbType.Int);
                }

                if (filter.TargetID_2 != null)
                {
                    sb.Append("TargetID_2 = @TargetID_2");

                    db.CreateParameter<int>("@TargetID_2", filter.TargetID_2.Value, SqlDbType.Int);
                }

                if (filter.TargetID_3 != null)
                {
                    sb.Append("TargetID_3 = @TargetID_3");

                    db.CreateParameter<int>("@TargetID_3", filter.TargetID_3.Value, SqlDbType.Int);
                }

                if (filter.BeginDate != null)
                {
                    sb.Append("CreateTime >= @BeginDate");

                    db.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
                }

                if (filter.EndDate != null)
                {
                    sb.Append("CreateTime <= @EndDate");

                    db.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
                }

                db.Pager.Condition = sb.ToString();
                db.Pager.PageNumber = pageNumber;
                db.Pager.SelectCount = true;

                using (XSqlDataReader reader = db.ExecuteReader())
                {
                    OperationLogCollection result = new OperationLogCollection(reader);

                    reader.NextResult();

                    reader.Read();

                    result.TotalRecords = reader.Get<int>(0);

                    return result;
                }
            }
        }
Beispiel #4
0
        private string BuilderSearchAttachmentCondition(AttachmentFilter 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 [UserID] = @UserID "));
            condition += (string.IsNullOrEmpty(filter.FileType) ? "" : ("AND [FileType] = @FileType "));
            condition += (string.IsNullOrEmpty(filter.KeyWord) ? "" : ("AND [FileName] LIKE '%'+@keyword+'%' "));
            condition += (filter.MinFileSize == null ? "" : ("AND [FileSize] >= @MinFileSize "));
            condition += (filter.MaxFileSize == null ? "" : ("AND [FileSize] <= @MaxFileSize "));
            condition += (filter.MinTotalDownload == null ? "" : ("AND [TotalDownloads] >= @MinTotalDownload "));
            condition += (filter.MaxTotalDownload == null ? "" : ("AND [TotalDownloads] <= @MaxTotalDownload "));
            condition += (filter.MinPrice == null ? "" : ("AND [Price] >= @MinPrice "));
            condition += (filter.MaxPrice == null ? "" : ("AND [Price] <= @MaxPrice "));
            condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
            condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));

            if (string.IsNullOrEmpty(filter.KeyWord) == false)
            {
                query.CreateParameter<string>("@keyword", filter.KeyWord, SqlDbType.NVarChar, 256);
            }

            if (string.IsNullOrEmpty(filter.FileType) == false)
            {
                query.CreateParameter<string>("@FileType", filter.FileType, SqlDbType.VarChar, 10);
            }

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

            if (filter.ForumID != null)
                query.CreateParameter<int?>("@ForumID", filter.ForumID, SqlDbType.Int);
            if (filter.UserID != null)
                query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);

            if (filter.MaxFileSize != null)
            {
                query.CreateParameter<long>("@MaxFileSize", ConvertUtil.GetFileSize(filter.MaxFileSize.Value, filter.MaxFileSizeUnit), SqlDbType.BigInt);
            }
            if (filter.MinFileSize != null)
                query.CreateParameter<long>("@MinFileSize", ConvertUtil.GetFileSize(filter.MinFileSize.Value, filter.MinFileSizeUnit), SqlDbType.BigInt);

            if (filter.MaxTotalDownload != null)
                query.CreateParameter<int?>("@MaxTotalDownload", filter.MaxTotalDownload, SqlDbType.Int);
            if (filter.MinTotalDownload != null)
                query.CreateParameter<int?>("@MinTotalDownload", filter.MinTotalDownload, SqlDbType.Int);

            if (filter.MaxPrice != null)
                query.CreateParameter<int?>("@MaxPrice", filter.MaxPrice, SqlDbType.Int);
            if (filter.MinPrice != null)
                query.CreateParameter<int?>("@MinPrice", filter.MinPrice, 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();
        }
Beispiel #5
0
        public override void DeleteAttachments(int forumID, IEnumerable<int> attachmentIDs, IEnumerable<Guid> excludeRoleIDs, out List<int> threadIDs)
        {
            using (SqlQuery query = new SqlQuery())
            {
                SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.None);

                if (forumID != 0)
                {
                    condition += " AND ForumID=@ForumID ";
                    query.CreateParameter<int>("@ForumID", forumID, SqlDbType.Int);
                }

                condition += " AND [AttachmentID] in(@AttachmentIDs) ";
                query.CreateInParameter<int>("@AttachmentIDs", attachmentIDs);

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

                query.CommandText = @"
DECLARE @Table table(AttachmentID int,FileID varchar(50),ThreadID int);
INSERT INTO @Table SELECT [AttachmentID],[FileID],[ThreadID] FROM [bx_AttacmentsWithForumID] WHERE " + condition.ToString() + @";
DELETE [bx_Attachments] WHERE AttachmentID IN(SELECT AttachmentID FROM @Table);
SELECT DISTINCT ThreadID FROM @Table;
";
                threadIDs = new List<int>();
                //fileIDs = new List<string>();
                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        threadIDs.Add(reader.Get<int>(0));
                    }
                }
            }
        }
Beispiel #6
0
        private string BuilderSearchPostCondition(PostFilter 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 += "AND PostType <> 1 ";
            condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
            condition += (filter.PostID == null ? "" : ("AND [PostID] = @PostID "));
            condition += (string.IsNullOrEmpty(filter.CreateIP) ? "" : ("AND [IPAddress] = @CreateIP "));

            if (filter.IsUnapproved != null && filter.IsUnapproved.Value)
                condition += (" AND [SortOrder] >  " + SortOrder_Unapproved);
            else if (filter.IsUnapproved != null && filter.IsUnapproved.Value == false)
                condition += (" AND [ThreadStatus] < " + SortOrder_Unapproved);

            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 '%'+@keyword+'%' ";
                }
                else if (filter.SearchMode == SearchArticleMethod.FullText)
                {
                    condition += " AND [Content] LIKE '%'+@keyword+'%' ";
                }
                else
                {
                    condition += " AND ([Subject] LIKE '%'+@keyword+'%' OR [Content] LIKE '%'+@keyword+'%') ";
                }

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

            condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", 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.PostID != null)
                query.CreateParameter<int?>("@PostID", filter.PostID, 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();
        }
Beispiel #7
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 '%'+@keyword+'%' ";
                }
                else if (filter.SearchMode == SearchArticleMethod.FullText)
                {
                    condition += " AND [Content] LIKE '%'+@keyword+'%' ";
                }
                else
                {
                    condition += " AND ([Subject] LIKE '%'+@keyword+'%' OR [Content] LIKE '%'+@keyword+'%') ";
                }

                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();
        }
Beispiel #8
0
        /*     [StoredProcedure(Name="bx_InsertBanUserLogs",Script= @"
         * CREATE PROCEDURE {name}
         * @OperationType varchar(50),
         * @OperatorName  nvarchar(50),
         * @Cause         nvarchar(1000),
         * @ForumInfos    varchar(1000),
         * @TargetID      int,
         * @TargetName    nvarchar(200),
         * @TargetIP      varchar(50)
         * AS
         * BEGIN
         * SET NOCOUNT ON;
         * INSERT INTO [bx_BanUserLogs](
         *   OperationType,
         *   OperatorName,
         *   Cause,
         *   ForumID,
         *   BeginDate,
         *   EndDate,
         *   TargetID,
         *   TargetName,
         *   TargetIP
         * ) Values(
         *   @OperationType,
         *   @OperatorName,
         *   @Cause,
         *   @ForumID,
         *   @BeginDate,
         *   @EndDate,
         *   @TargetID,
         *   @TargetName,
         *   @TargetIP
         * );
         * END
         * ")]
         *
         *   public override void InsertBanUserOperationLog(BanUserOperation banuser)
         *   {
         *       using (SqlQuery query = new SqlQuery())
         *       {
         *           query.CommandText = "bx_InsertBanUserLogs";
         *           query.CommandType = CommandType.StoredProcedure;
         *
         *           query.CreateParameter<string>("@OperationType", banuser.OperationType, SqlDbType.VarChar, 50);
         *           query.CreateParameter<string>("@OperationName", banuser.OperatorName, SqlDbType.NVarChar, 50);
         *           query.CreateParameter<string>("@Cause", banuser.Cause, SqlDbType.NVarChar, 200);
         *         //  query.CreateParameter<int>("@ForumID", banuser.ForumID, SqlDbType.Int);
         *           query.CreateParameter<DateTime>("@BeginDate",banuser.BeginDate,SqlDbType.DateTime);
         *           query.CreateParameter<DateTime>("@EndDate", banuser.EndDate, SqlDbType.DateTime);
         *           query.CreateParameter<int>("@TargetID",banuser.UserID,SqlDbType.Int);
         *           query.CreateParameter<string>("@TargetName",banuser.UserName,SqlDbType.NVarChar,50);
         *           query.CreateParameter<string>("@TargetIP",banuser.UserIP,SqlDbType.VarChar,50);
         *
         *           query.ExecuteNonQuery();
         *       }
         *   }
         */

        public override OperationLogCollection GetOperationLogsBySearch(int pageNumber, OperationLogFilter filter)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.Pager.TableName = "bx_OperationLogs";
                db.Pager.SortField = "LogID";
                db.Pager.PageSize  = filter.PageSize;

                SqlConditionBuilder sb = new SqlConditionBuilder(SqlConditionStart.None);

                if (filter.OperatorID != null)
                {
                    sb.Append("OperatorID = @OperatorID");

                    db.CreateParameter <int>("@OperatorID", filter.OperatorID.Value, SqlDbType.Int);
                }
                else if (filter.OperatorName != null && filter.OperatorName != string.Empty)
                {
                    sb.Append("OperatorID IN (SELECT UserID FROM bx_Users WHERE Username = @OperatorName)");

                    db.CreateParameter <string>("@OperatorName", filter.OperatorName, SqlDbType.NVarChar, 50);
                }

                if (filter.OperatorIP != null && filter.OperatorIP != string.Empty)
                {
                    sb.Append("OperatorIP = @OperatorIP");

                    db.CreateParameter <string>("@OperatorIP", filter.OperatorIP, SqlDbType.VarChar, 50);
                }

                if (filter.OperationType != null && filter.OperationType != string.Empty)
                {
                    sb.Append("OperationType = @OperationType");

                    db.CreateParameter <string>("@OperationType", filter.OperationType, SqlDbType.VarChar, 100);
                }

                if (filter.TargetID_1 != null)
                {
                    sb.Append("TargetID_1 = @TargetID_1");

                    db.CreateParameter <int>("@TargetID_1", filter.TargetID_1.Value, SqlDbType.Int);
                }

                if (filter.TargetID_2 != null)
                {
                    sb.Append("TargetID_2 = @TargetID_2");

                    db.CreateParameter <int>("@TargetID_2", filter.TargetID_2.Value, SqlDbType.Int);
                }

                if (filter.TargetID_3 != null)
                {
                    sb.Append("TargetID_3 = @TargetID_3");

                    db.CreateParameter <int>("@TargetID_3", filter.TargetID_3.Value, SqlDbType.Int);
                }

                if (filter.BeginDate != null)
                {
                    sb.Append("CreateTime >= @BeginDate");

                    db.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
                }

                if (filter.EndDate != null)
                {
                    sb.Append("CreateTime <= @EndDate");

                    db.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
                }

                db.Pager.Condition   = sb.ToString();
                db.Pager.PageNumber  = pageNumber;
                db.Pager.SelectCount = true;

                using (XSqlDataReader reader = db.ExecuteReader())
                {
                    OperationLogCollection result = new OperationLogCollection(reader);

                    reader.NextResult();

                    reader.Read();

                    result.TotalRecords = reader.Get <int>(0);

                    return(result);
                }
            }
        }
Beispiel #9
0
        public override DeleteResult DeleteSearchShares(ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, bool getDeleteResult)
        {

            using (SqlQuery query = new SqlQuery())
            {

                //string condition = @" 1 = 1 "
                //        + (filter.UserID == null ? "" : ("AND [UserID] = @UserID "))
                //        + (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "))
                //        + (filter.ShareCatagory == ShareCatagory.All ? "" : ("AND [Type] = @Type "))
                //        + (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "))
                //        + (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "))
                //        + (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "))
                //        + "AND " + DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query);

                SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.Where);
                condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
                condition += (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "));
                condition += (filter.ShareType == ShareType.All ? "" : ("AND [Type] = @Type "));
                condition += (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "));
                condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
                condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
                condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));

                if (getDeleteResult)
                {
                    query.CommandText = @"
SELECT [UserID],COUNT(*) AS [Count] FROM [bx_SharesView] " + condition.ToString() + " GROUP BY [UserID];";

                }
                else
                    query.CommandText = string.Empty;


                query.CommandText = query.CommandText + @"
DELETE [bx_SharesView] " + condition.ToString();


                query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
                query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
                query.CreateParameter<int>("@Type", (int)filter.ShareType, SqlDbType.Int);
                query.CreateParameter<int?>("@PrivacyType", filter.PrivacyType == null ? 0 : (int)filter.PrivacyType, SqlDbType.Int);
                query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
                query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);



                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    DeleteResult deleteResult = new DeleteResult();

                    while (reader.Read())
                    {
                        deleteResult.Add(reader.Get<int>("UserID"), reader.Get<int>("Count"));
                    }
                    return deleteResult;
                }
            }
        }
Beispiel #10
0
        public override ShareCollection SearchShares(int pageNumber, ShareFilter filter, IEnumerable<Guid>  excludeRoleIDs, ref int totalCount)
        {

            using (SqlQuery query = new SqlQuery())
            {
                query.Pager.IsDesc = filter.IsDesc;

                if (filter.Order == ShareFilter.OrderBy.ShareID)
                    query.Pager.SortField = filter.Order.ToString();

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

                SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.None);
                condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
                condition += (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "));
                condition += (filter.ShareType == ShareType.All ? "" : ("AND [Type] = @Type "));
                condition += (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "));
                condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
                condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
                condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));

                query.Pager.Condition = condition.ToString();



                query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
                query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
                query.CreateParameter<int>("@Type", (int)filter.ShareType, SqlDbType.Int);
                query.CreateParameter<int?>("@PrivacyType", filter.PrivacyType == null ? 0 : (int)filter.PrivacyType, SqlDbType.Int);
                query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
                query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);



                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    ShareCollection shares = new ShareCollection(reader);

                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            totalCount = reader.Get<int>(0);
                        }
                    }
                    return shares;
                }
            }
        }
Beispiel #11
0
		private string BuildConditionsByFilter(SqlQuery query, ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, bool startWithWhere)
		{
			SqlConditionBuilder condition = new SqlConditionBuilder(startWithWhere ? SqlConditionStart.Where : SqlConditionStart.None);

			if (filter.UserID != null)
			{
				condition.AppendAnd("[UserID] = @UserID");

				query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
			}

			if (filter.ShareID != null)
			{
				condition.AppendAnd("[ShareID] = @ShareID");

				query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
			}

			if (filter.ShareType != null)
			{
				condition.AppendAnd("[Type] = @Type");

				query.CreateParameter<ShareType?>("@Type", filter.ShareType, SqlDbType.Int);
			}

			if (filter.PrivacyType != null)
			{
				condition.AppendAnd("[PrivacyType] = @PrivacyType");

				query.CreateParameter<PrivacyType?>("@PrivacyType", filter.PrivacyType, SqlDbType.Int);
			}

			if (filter.BeginDate != null)
			{
				condition.AppendAnd("[CreateDate] > @BeginDate");

				query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
			}

			if (filter.EndDate != null)
			{
				condition.AppendAnd("[CreateDate] < @EndDate");

				query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
			}

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

			return condition.ToString();
		}
Beispiel #12
0
		public override ShareCollection GetUserShares(int shareOwnerID, ShareType? shareType, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int? totalCount)
		{
			using (SqlQuery query = new SqlQuery())
			{
                query.Pager.TableName = "[bx_SharesView]";
				query.Pager.SortField = "[UserShareID]";
				query.Pager.IsDesc = true;
				query.Pager.PageNumber = pageNumber;
				query.Pager.PageSize = pageSize;
				query.Pager.TotalRecords = totalCount;
				query.Pager.SelectCount = true;

				SqlConditionBuilder cb = new SqlConditionBuilder(SqlConditionStart.None);

				cb.Append("[UserID2] = @UserID");

				query.CreateParameter<int>("@UserID", shareOwnerID, SqlDbType.Int);

				if (shareType.HasValue && shareType.Value != ShareType.All)
				{
					cb.Append("[Type] = @Type");

					query.CreateParameter<ShareType>("@Type", shareType.Value, SqlDbType.TinyInt);
				}

				if (dataAccessLevel == DataAccessLevel.Normal)
				{
					cb.Append("[PrivacyType] IN (0, 3)");
				}
				else if (dataAccessLevel == DataAccessLevel.Friend)
				{
					cb.Append("[PrivacyType] IN (0, 1, 3)");
				}
				else if(dataAccessLevel == DataAccessLevel.DataOwner)
				{
					cb.Append("[PrivacyType] != 2");
				}

				query.Pager.Condition = cb.ToString();

                ShareCollection shares;
				using (XSqlDataReader reader = query.ExecuteReader())
				{
                    shares = new ShareCollection(reader);

                    if (reader.NextResult())
					{
                        if (totalCount == null && reader.Read())
						{
                            totalCount = reader.Get<int>(0);
						}

						shares.TotalRecords = totalCount.GetValueOrDefault();
					}

				}
                FillComments(shares, query);

				return shares;
			}
		}