public override SimpleUserCollection GetBannedUsers(int ForumID, int pageSize, int pageNumber, out int totalCount) { totalCount = 0; using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_SimpleUser"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.SelectCount = true; query.Pager.SelectCount = true; query.Pager.PrimaryKey = "[UserID]"; query.Pager.Condition = " UserID IN(SELECT UserID FROM bx_BannedUsers WHERE ForumID = @ForumID AND EndDate > GETDATE())"; query.CreateParameter <int>("@ForumID", ForumID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { SimpleUserCollection users = new SimpleUserCollection(reader); if (reader.NextResult()) { while (reader.Read()) { totalCount = reader.GetInt32(0); } } return(users); } } }
public override InviteSerialCollection GetInviteSerials(int operatorUserID, InviteSerialStatus status, string filter, int pageNumber, out int totalCount) { using (SqlQuery query = new SqlQuery()) { StringBuffer Condition = new StringBuffer(); Condition += " AND UserID = @UserID"; query.CreateParameter <int>("@UserID", operatorUserID, SqlDbType.Int); if (status != InviteSerialStatus.All) { if (status != InviteSerialStatus.Expires) { Condition += " AND [Status] = @Status"; query.CreateParameter <byte>("@Status", (byte)status, SqlDbType.TinyInt); } else if (status == InviteSerialStatus.Expires) { Condition += " AND Status <> 1 AND ExpiresDate <= GETDATE()"; } } if (string.IsNullOrEmpty(filter) == false) { Condition += " AND (Serial LIKE '%'+ @word +'%' OR ToUserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%'+ @word +'%' OR Realname LIKE '%'+ @word +'%' ))"; query.CreateParameter <string>("@word", filter, SqlDbType.NVarChar, 50); } if (Condition.Length > 0) { Condition.Remove(0, 5); } query.Pager.SortField = "CreateDate"; query.Pager.IsDesc = true; query.Pager.TableName = "[bx_InviteSerials]"; query.Pager.SelectCount = true; query.Pager.PageSize = 20; query.Pager.PageNumber = pageNumber > 0 ? pageNumber : 1; query.Pager.Condition = Condition.ToString(); query.Pager.PrimaryKey = "[ID]"; totalCount = 0; using (XSqlDataReader reader = query.ExecuteReader()) { InviteSerialCollection Serials = new InviteSerialCollection(reader); if (reader.NextResult()) { if (reader.Read()) { totalCount = reader.GetInt32(0); Serials.TotalRecords = totalCount; } } return(Serials); } } }
public override DataBaseInfo GetDataBaseInfo() { string version = ""; using (SqlQuery query = new SqlQuery()) { query.CommandText = "SELECT size*8,(status & 0x40) as type FROM sysfiles;SELECT @@VERSION"; DataBaseInfo dataBaseInfo = new DataBaseInfo(); using (XSqlDataReader reader = query.ExecuteReader()) { int dataSize = 0; int logoSize = 0; while (reader.Read()) { if (reader.GetInt32(1) == 0) { dataSize += reader.GetInt32(0); } else { logoSize += reader.GetInt32(0); } } if (reader.NextResult()) { while (reader.Read()) { version += "(" + reader.GetString(0) + ")"; } } dataBaseInfo.DataSize = dataSize * 1024; dataBaseInfo.LogSize = logoSize * 1024; dataBaseInfo.Version = version; } return(dataBaseInfo); } }
public override UserIPLogCollection GetUserIPLogsByIP(string IP, int pageNumber, int pageSize, out int total) { total = 0; using (SqlQuery query = new SqlQuery()) { query.Pager.IsDesc = true; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; query.Pager.SelectCount = true; query.Pager.TableName = "[bx_IPLogs]"; query.Pager.SortField = "[LogID]"; query.Pager.Condition = " LogID in(SELECT Max(LogID) FROM [bx_IPLogs] WHERE NewIP = @IP GROUP BY UserID) "; query.Pager.AfterExecute = "SELECT * FROM bx_BannedUsers WHERE UserID in(SELECT UserID FROM [bx_IPLogs] WHERE NewIP = @IP GROUP BY UserID)"; //query.CommandText = "SELECT * FROM [bx_IPLogs] LEFT JOIN bx_BannedUsers ON bx_BannedUsers.UserID=bx_IPLogs.UserID WHERE NewIP=@IP"; query.CreateParameter <string>("@IP", IP, SqlDbType.VarChar, 50); using (XSqlDataReader reader = query.ExecuteReader()) { UserIPLogCollection collection = new UserIPLogCollection(reader); if (reader.NextResult()) { while (reader.Read()) { total = reader.GetInt32(0); } } if (reader.NextResult()) { while (reader.Read()) { int userID = reader.Get <int>("UserID"); int forumID = reader.Get <int>("ForumID"); foreach (UserIPLog log in collection) { if (log.UserID == userID) { log.BannedForumID = forumID; } } } } return(collection); } } }
public override InviteSerialCollection GetInviteSerials(int?ownerUserId, InviteSerialFilter filter, int pageNumber, out int totalCount) { using (SqlQuery query = new SqlQuery()) { string Condition = BuildCondition(filter, query); if (ownerUserId != null) { Condition += " AND UserID = @OwnerID"; query.CreateParameter <int>("@OwnerID", ownerUserId.Value, SqlDbType.Int); } if (filter.Pagesize < 1) { filter.Pagesize = 20; } query.Pager.SortField = filter.Order.Value.ToString(); query.Pager.IsDesc = filter.IsDesc.Value; query.Pager.TableName = "[bx_InviteSerials]"; query.Pager.SelectCount = true; query.Pager.PageSize = filter.Pagesize; query.Pager.PageNumber = pageNumber > 0 ? pageNumber : 1; query.Pager.Condition = Condition; query.Pager.PrimaryKey = "[ID]"; totalCount = 0; using (XSqlDataReader reader = query.ExecuteReader()) { InviteSerialCollection Serials = new InviteSerialCollection(reader); if (reader.NextResult()) { if (reader.Read()) { totalCount = reader.GetInt32(0); Serials.TotalRecords = totalCount; } } return(Serials); } } }
public override InviteSerialStatCollection GetStatList(InviteSerialStatus state, int pageSize, int pageNumber, out int rowCount) { UpdateExpiresSerialStatus(); using (SqlQuery query = new SqlQuery()) { string orderField = "[TotalSerial]"; switch (state) { case InviteSerialStatus.Used: orderField = "[Used]"; break; case InviteSerialStatus.Expires: orderField = "Expiress"; break; case InviteSerialStatus.Unused: orderField = "Unused"; break; } query.Pager.TableName = "bx_SerialCounter"; query.Pager.PageNumber = pageNumber > 0 ? pageNumber : 1; query.Pager.PageSize = pageSize > 0 ? pageSize : 20; query.Pager.SelectCount = true; query.Pager.SortField = orderField; query.Pager.PrimaryKey = "[UserID]"; rowCount = 0; using (XSqlDataReader reader = query.ExecuteReader()) { InviteSerialStatCollection stats = new InviteSerialStatCollection(reader); if (reader.NextResult()) { if (reader.Read()) { rowCount = reader.GetInt32(0); } } return(stats); } } }
public override UserMissionCollection GetUserMissions(int userID, int pageNumber, int pageSize, out int totalCount) { using (SqlQuery query = new SqlQuery()) { query.Pager.IsDesc = true; query.Pager.SortField = "[CreateDate]"; query.Pager.PrimaryKey = "[ID]"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = pageSize; //query.Pager.TotalRecords = totalCount; query.Pager.SelectCount = true; query.Pager.TableName = "[bx_UserMissionsView]"; query.Pager.Condition = @"UserID=@UserID AND IsEnable = 1 AND Status <> 1"; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); using (XSqlDataReader reader = query.ExecuteReader()) { UserMissionCollection missions = new UserMissionCollection(reader); if (reader.NextResult()) { if (reader.Read()) { totalCount = reader.GetInt32(0); } else { totalCount = 0; } } else { totalCount = 0; } return(missions); } } }
public override void QueryForBeforeRequestIn3M(out TopicStatusCollection experiesTopicStatus, out List <int> autoFinalQuestionThreadIds, out Dictionary <int, Dictionary <int, int> > autoFinalQuestionForumIDAndRewards) { //供放置查出的已经过期的提问 //List<Question> expiresQuestions = new List<Question>(); List <QuestionThread> expiresQuestions = new List <QuestionThread>(); Dictionary <int, int> threadIDAndPostUserIDs = new Dictionary <int, int>(); Dictionary <int, int> threadIDAndForumIDs = new Dictionary <int, int>(); autoFinalQuestionThreadIds = new List <int>(); using (SqlQuery query = new SqlQuery()) { query.CommandText = "bx_QueryForBeforeRequestIn3M"; query.CommandType = CommandType.StoredProcedure; query.CommandTimeout = 30; try { if (IsAutoFinalQuestion == true) { experiesTopicStatus = new TopicStatusCollection(); autoFinalQuestionForumIDAndRewards = new Dictionary <int, Dictionary <int, int> >(); return; } IsAutoFinalQuestion = true; using (XSqlDataReader reader = query.ExecuteReader()) { experiesTopicStatus = new TopicStatusCollection(reader); reader.NextResult(); while (reader.Read()) { //Question question = ConvertData.ConvertToQuestion(reader); QuestionThread question = new QuestionThread(); question.ThreadID = reader.Get <int>(reader.GetOrdinal("ThreadID")); question.FillQuestion(reader); expiresQuestions.Add(question); int forumID = reader.Get <int>("ForumID"); threadIDAndForumIDs.Add(question.ThreadID, forumID); threadIDAndPostUserIDs.Add(question.ThreadID, reader.GetInt32(reader.GetOrdinal("PostUserID"))); } } query.CommandText = "bx_AutoFinalQuestion"; query.CommandType = CommandType.StoredProcedure; query.CommandTimeout = 30; autoFinalQuestionForumIDAndRewards = new Dictionary <int, Dictionary <int, int> >(); foreach (QuestionThread q in expiresQuestions) { int forumID; if (threadIDAndForumIDs.ContainsKey(q.ThreadID)) { forumID = threadIDAndForumIDs[q.ThreadID]; } else { continue; } query.Parameters.Clear(); query.CreateParameter <int>("@ThreadID", q.ThreadID, SqlDbType.Int); query.CreateParameter <int>("@UserID", threadIDAndPostUserIDs[q.ThreadID], SqlDbType.Int); query.CreateParameter <int>("@RewardCount", q.RewardCount, SqlDbType.Int); query.CreateParameter <int>("@TotalReward", q.Reward, SqlDbType.Int); if (autoFinalQuestionForumIDAndRewards.ContainsKey(forumID) == false) { autoFinalQuestionForumIDAndRewards.Add(forumID, new Dictionary <int, int>()); } using (XSqlDataReader reader = query.ExecuteReader()) { while (reader.Read()) { int userID = reader.Get <int>("UserID"); int reward = reader.Get <int>("Reward"); if (autoFinalQuestionForumIDAndRewards[forumID].ContainsKey(userID)) { autoFinalQuestionForumIDAndRewards[forumID][userID] = autoFinalQuestionForumIDAndRewards[forumID][userID] + reward; } else { autoFinalQuestionForumIDAndRewards[forumID].Add(userID, reward); } } } if (!autoFinalQuestionThreadIds.Contains(q.ThreadID)) { autoFinalQuestionThreadIds.Add(q.ThreadID); } } IsAutoFinalQuestion = false; } catch (Exception ex) { IsAutoFinalQuestion = false; throw ex; } } }
public override ChatSessionCollection AdminGetSessions(ChatSessionFilter filter, int pageNumber, IEnumerable <Guid> excludeRoleIds) { ChatSessionCollection sessions; using (SqlQuery query = new SqlQuery()) { string excludeRoleCondition = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIds, query); StringBuffer buffer = new StringBuffer(); if (filter.UserID != null) { buffer += " AND UserID = @UserID"; query.CreateParameter <int>("@UserID", filter.UserID.Value, SqlDbType.Int); } if (!string.IsNullOrEmpty(filter.Username)) { buffer += " AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%'+@Username+'%' ) OR TargetUserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%'+@Username+'%' )"; query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } //if (!string.IsNullOrEmpty(filter.TargetUsername)) //{ // buffer += " AND TargetUserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%'+@TargetUser+'%' )"; // query.CreateParameter<string>("@TargetUser", filter.TargetUsername, SqlDbType.NVarChar, 50); //} //if (!string.IsNullOrEmpty(filter.Contains)) //{ // buffer +=" AND ChatSessionID IN( SELECT ) " //} if (filter.BeginDate != null) { buffer += " AND CreateDate >= @BeginDate"; query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime); } if (filter.EndDate != null) { buffer += " AND UpdateDate <= @EndDate"; query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime); } if (!string.IsNullOrEmpty(excludeRoleCondition)) { buffer += " AND " + excludeRoleCondition; excludeRoleCondition = DaoUtil.GetExcludeRoleSQL("TargetUserID", excludeRoleIds, query); buffer += " AND " + excludeRoleCondition; } if (buffer.Length > 0) { buffer.Remove(0, 5); } //query.CommandText = "SELECT * FROM bx_ChatSessions"; query.Pager.TableName = "bx_ChatSessions"; query.Pager.PrimaryKey = "ChatSessionID"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = filter.PageSize; query.Pager.SortField = "ChatSessionID"; query.Pager.IsDesc = true; query.Pager.SelectCount = true; query.Pager.Condition = buffer.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { sessions = new ChatSessionCollection(reader); while (reader.NextResult()) { if (reader.Read()) { sessions.TotalRecords = reader.GetInt32(0); } } } } return(sessions); }
public override void AddComment(int userID, int targetID, CommentType type, bool isApproved, string content, /* string contentReverter, */ string createIP, out int targetUserID, out int newCommentId) { //string tableName = string.Empty; targetUserID = 0; string getTargetUserSql; switch (type) { case CommentType.Blog: getTargetUserSql = "(SELECT UserID FROM bx_BlogArticles WHERE [ArticleID] = @TargetID)"; break; case CommentType.Photo: getTargetUserSql = "(SELECT UserID FROM bx_Photos WHERE [PhotoID] = @TargetID)"; break; case CommentType.Doing: getTargetUserSql = "(SELECT UserID FROM bx_Doings WHERE [DoingID] = @TargetID)"; break; case CommentType.Share: getTargetUserSql = "(SELECT UserID FROM bx_UserShares WHERE [UserShareID] = @TargetID)"; break; case CommentType.Board: getTargetUserSql = "@TargetID"; break; default: getTargetUserSql = string.Empty; break; } string sql = string.Format(@" DECLARE @TargetUserID int; SET @TargetUserID = {0}; INSERT INTO bx_Comments([UserID],[TargetID],[TargetUserID],[LastEditUserID],[IsApproved],[Type],[Content],[CreateIP]) VALUES(@UserID,@TargetID,@TargetUserID,@UserID,@IsApproved,@Type,@Content,@CreateIP); SELECT CAST(@@IDENTITY as int) AS NewID, @TargetUserID AS TargetID ", getTargetUserSql); using (SqlQuery query = new SqlQuery()) { query.CommandText = sql; query.CreateParameter <int>("@UserID", userID, SqlDbType.Int); query.CreateParameter <int>("@TargetID", targetID, SqlDbType.Int); query.CreateParameter <bool>("@IsApproved", isApproved, SqlDbType.Bit); query.CreateParameter <int>("@Type", (int)type, SqlDbType.Int); query.CreateParameter <string>("@Content", content, SqlDbType.NVarChar, 3000); query.CreateParameter <string>("@CreateIP", createIP, SqlDbType.VarChar, 50); targetUserID = 0; newCommentId = 0; using (XSqlDataReader reader = query.ExecuteReader()) { if (reader.Read()) { targetUserID = reader.GetInt32(1); newCommentId = reader.GetInt32(0); } } } }
public override void GetDenouncingCount( out int?denouncingPhotoCount, out int?denouncingArticleCount, out int?denouncingShareCount, out int?denouncingUserCount, out int?denouncingTopicCount, out int?denouncingReplyCount) { using (SqlQuery db = new SqlQuery()) { db.CommandText = "bx_Denouncing_GetCount"; db.CommandType = CommandType.StoredProcedure; using (XSqlDataReader reader = db.ExecuteReader()) { if (reader.Read()) { denouncingPhotoCount = reader.GetInt32(0); } else { denouncingPhotoCount = 0; } reader.NextResult(); if (reader.Read()) { denouncingArticleCount = reader.GetInt32(0); } else { denouncingArticleCount = 0; } reader.NextResult(); if (reader.Read()) { denouncingShareCount = reader.GetInt32(0); } else { denouncingShareCount = 0; } reader.NextResult(); if (reader.Read()) { denouncingUserCount = reader.GetInt32(0); } else { denouncingUserCount = 0; } reader.NextResult(); if (reader.Read()) { denouncingTopicCount = reader.GetInt32(0); } else { denouncingTopicCount = 0; } reader.NextResult(); if (reader.Read()) { denouncingReplyCount = reader.GetInt32(0); } else { denouncingReplyCount = 0; } } } }