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;
}
}
}
}