public override List <Website> GetWebsites(int pageSize, int pageNumber, out int totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "Chinaz_Websites";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.PrimaryKey = "WebsiteID";
query.Pager.SortField = "WebsiteID";
query.Pager.SelectCount = true;
query.Pager.IsDesc = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
totalCount = 0;
List <Website> results = new List <Website>();
while (reader.Next)
{
results.Add(new Website(reader));
}
if (reader.NextResult())
{
while (reader.Next)
{
totalCount = reader.Get <int>(00);
}
}
return(results);
}
}
}
public override UserCollection GetRoleMembers(int roleID, int pageSize, int pageNumber, out int totalCount)
{
UserCollection users = new UserCollection();
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_Users]";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.SelectCount = true;
query.Pager.SortField = "[UserID]";
query.Pager.PrimaryKey = "[UserID]";
query.Pager.Condition = " UserID IN( SELECT UserID FROM bx_UsersInRoles WHERE RoleID = @RoleID AND BeginDate <= GETDATE() AND EndDate >= GETDATE())";
query.CreateParameter <int>("@RoleID", roleID, SqlDbType.Int);
totalCount = 0;
using (XSqlDataReader reader = query.ExecuteReader())
{
users = new UserCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
totalCount = reader.Get <int>(0);
}
}
}
}
return(users);
}
public override EmoticonCollection GetEmoticons(int userID, int GroupID, int pageSize, int pageNumber, bool isDesc, out int totalCount)
{
totalCount = 0;
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_Emoticons";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.PrimaryKey = "EmoticonID";
query.Pager.SelectCount = true;
query.Pager.SortField = "SortOrder";
query.Pager.IsDesc = isDesc;
query.Pager.Condition = " GroupID = @GroupID AND EXISTS( SELECT * FROM bx_EmoticonGroups WHERE GroupID = @GroupID AND UserID = @UserID)";
query.CreateParameter <int>("@GroupID", GroupID, SqlDbType.Int);
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
EmoticonCollection emoticons = new EmoticonCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
emoticons.TotalRecords = reader.Get <int>(0);
}
}
totalCount = emoticons.TotalRecords;
return(emoticons);
}
}
}
public override ImpressionRecordCollection GetTargetUserImpressionRecords(int targetUserID, int pageNumber, int pageSize, ref int?totalCount)
{
using (SqlQuery db = new SqlQuery())
{
db.Pager.TableName = "bx_ImpressionRecordsWithTypeInfo";
db.Pager.Condition = "TargetUserID = @TargetUserID";
db.Pager.SortField = "RecordID";
db.Pager.PageNumber = pageNumber;
db.Pager.PageSize = pageSize;
db.Pager.TotalRecords = totalCount;
db.Pager.SelectCount = true;
db.CreateParameter <int>("@TargetUserID", targetUserID, System.Data.SqlDbType.Int);
using (XSqlDataReader reader = db.ExecuteReader())
{
ImpressionRecordCollection result = new ImpressionRecordCollection(reader);
if (reader.NextResult() && reader.Read())
{
totalCount = reader.Get <int>(0);
result.TotalRecords = totalCount.Value;
}
return(result);
}
}
}
public override CommentCollection GetCommentsByFilter(AdminCommentFilter filter, int operatorUserID, IEnumerable <Guid> excludeRoleIDs, int pageNumber, out int totalCount)
{
totalCount = 0;
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_Comments";
query.Pager.SortField = filter.Order.ToString();
if (filter.Order != AdminCommentFilter.OrderBy.CommentID)
{
query.Pager.PrimaryKey = "CommentID";
}
query.Pager.IsDesc = filter.IsDesc;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.SelectCount = true;
query.Pager.Condition = BuildConditionsByFilter(query, filter, false, operatorUserID, excludeRoleIDs);
using (XSqlDataReader reader = query.ExecuteReader())
{
CommentCollection comments = new CommentCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
totalCount = reader.Get <int>(0);
}
}
return(comments);
}
}
}
public override CommentCollection GetCommentsBySearch(int operatorID, Guid[] excludeRoleIDs, AdminCommentFilter filter, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
string conditions = BuildConditionsByFilter(query, filter, false, operatorID, excludeRoleIDs);
query.Pager.TableName = "[bx_Comments]";
query.Pager.SortField = filter.Order.ToString();
query.Pager.IsDesc = filter.IsDesc;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.SelectCount = true;
query.Pager.Condition = conditions.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
CommentCollection comments = new CommentCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
comments.TotalRecords = reader.Get <int>(0);
}
}
return(comments);
}
}
}
public override EmoticonCollection AdminGetUserEmoticons(int userID, int pageSize, int pageIndex)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_Emoticons";
query.Pager.Condition = " GroupID IN (SELECT GroupID FROM bx_EmoticonGroups WHERE UserID = @UserID)";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageIndex;
query.Pager.SortField = "SortOrder";
query.Pager.PrimaryKey = "EmoticonID";
query.Pager.SelectCount = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
EmoticonCollection Emoticons = new EmoticonCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
Emoticons.TotalRecords = reader.Get <int>(0);
}
}
return(Emoticons);
}
}
}
public override AuctionBidInfoCollection GetMyAuctionBids(int userID, int auctionID, int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "Chinaz_AuctionBids";
query.Pager.PrimaryKey = "BidID";
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.Condition = "UserID = @UserID AND AuctionID = @AuctionID";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
query.CreateParameter <int>("@AuctionID", auctionID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
AuctionBidInfoCollection bids = new AuctionBidInfoCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
bids.TotalRecords = reader.Get <int>(0);
}
}
return(bids);
}
}
}
public override PointShowCollection GetPointShowList(int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.SortField = "[Price]";
query.Pager.IsDesc = true;
query.Pager.PrimaryKey = "[UserID]";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.SelectCount = true;
query.Pager.TableName = "bx_PointShows";
using (XSqlDataReader reader = query.ExecuteReader())
{
PointShowCollection result = new PointShowCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
result.TotalRecords = reader.Get <int>(0);
}
}
return(result);
}
}
}
/// <summary>
/// 高级搜索
/// </summary>
public override NotifyCollection AdminGetNotifiesBySearch(AdminNotifyFilter notifyFilter, int pageNumber, IEnumerable <Guid> excludeRoleIds)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = FilterToCondition(query, notifyFilter);
string exlcludeUserIDs = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIds, query);
if (!string.IsNullOrEmpty(exlcludeUserIDs))
{
condition.Append(" AND " + exlcludeUserIDs);
}
query.Pager.IsDesc = true;
query.Pager.TableName = "[bx_Notify]";
query.Pager.SortField = "[NotifyID]";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = notifyFilter.PageSize;
query.Pager.SelectCount = true;
query.Pager.Condition = condition.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
NotifyCollection notifies = new NotifyCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
notifies.TotalRecords = reader.Get <int>(0);
}
}
return(notifies);
}
}
}
/// <summary>
/// 获取指定用户/所有用户的所有通知
/// </summary>
/// <param name="userID">指定用户的ID,可以为空,为空则为要获取所有用户</param>
/// <returns>返回指定用户的所有通知集合</returns>
public override NotifyCollection GetNotifies(int?userID, int pageSize, int pageNumber, ref int?count)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.IsDesc = true;
query.Pager.TableName = "[bx_Notify]";
query.Pager.SortField = "[NotifyID]";
query.Pager.PrimaryKey = "[NotifyID]";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = count;
query.Pager.SelectCount = true;
if (userID != null)
{
query.Pager.Condition = @"[UserID] = @UserID";
}
query.CreateParameter <int?>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
NotifyCollection notifies = new NotifyCollection(reader);
if (count == null && reader.NextResult())
{
if (reader.Read())
{
count = reader.Get <int>(0);
}
}
return(notifies);
}
}
}
/// <summary>
/// 获取标签
/// </summary>
public override TagCollection GetMostTags(bool isLock, int pageNumber, int pageSize, ref int?count)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_Tags";
query.Pager.SortField = "ID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = count;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
query.Pager.Condition = "[IsLock] = @IsLock";
query.CreateParameter <bool>("@IsLock", isLock, SqlDbType.Bit);
using (XSqlDataReader reader = query.ExecuteReader())
{
TagCollection tags = new TagCollection(reader);
if (count == null && reader.NextResult())
{
if (reader.Read())
{
count = reader.Get <int>(0);
}
}
return(tags);
}
}
}
/// <summary>
/// 获取指定类型的标签
/// </summary>
/// <param name="type">类型,如日志标签等</param>
public override TagCollection GetTags(TagType type, int pageNumber, int pageSize, ref int?count)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_Tags";
query.Pager.SortField = "ID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = count;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
query.Pager.Condition = "[ID] IN (SELECT [TagID] FROM [bx_TagRelation] WHERE [Type] = @Type)";
query.CreateParameter <TagType>("@Type", type, SqlDbType.TinyInt);
using (XSqlDataReader reader = query.ExecuteReader())
{
TagCollection tags = new TagCollection(reader);
if (count == null && reader.NextResult())
{
if (reader.Read())
{
count = reader.Get <int>(0);
}
}
return(tags);
}
}
}
public override CommentCollection GetCommentsByUserID(int userID, CommentType type, int pageNumber, int pageSize, out int totalCount)
{
totalCount = 0;
string sqlCondition = "Type = @Type AND UserID = @UserID AND IsApproved = 1";
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_Comments";
query.Pager.Condition = sqlCondition;
query.Pager.SortField = "CommentID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.ResultFields = "*";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
query.CreateParameter <int>("@Type", (int)type, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
CommentCollection comments = new CommentCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
totalCount = reader.Get <int>(0);
}
}
return(comments);
}
}
}
/// <summary>
/// 获取所有标签
/// </summary>
public override TagCollection GetAllTags(int pageNumber, int pageSize, ref int?count)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_Tags";
query.Pager.SortField = "ID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = count;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
TagCollection tags = new TagCollection(reader);
if (count == null && reader.NextResult())
{
if (reader.Read())
{
count = reader.Get <int>(0);
}
}
return(tags);
}
}
}
public override VisitorCollection GetSpaceVisitors(int spaceOwnerID, int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.SortField = "CreateDate";
query.Pager.PrimaryKey = "[ID]";
query.Pager.TableName = "bx_Visitors";
query.Pager.Condition = "UserID = @UserID";
query.Pager.IsDesc = true;
query.CreateParameter <int>("@UserID", spaceOwnerID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
VisitorCollection visitors = new VisitorCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
visitors.TotalRecords = reader.Get <int>(0);
}
}
return(visitors);
}
}
}
public override UserTempAvatarCollection GetUserTempAvatars(int pageSize, int pageNumber, out int totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_UserTempAvatar";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "UserID";
query.Pager.SortField = "[CreateDate]";
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
totalCount = 0;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserTempAvatarCollection tempAvatars = new UserTempAvatarCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
totalCount = reader.Get <int>(0);
}
}
return(tempAvatars);
}
}
}
public override PointLogCollection GetPointLogs(PointLogFilter filter, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(filter.Username))
{
sb.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username = @Username )");
query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
if (filter.OperateID != null)
{
sb.Append(" AND OperateID = @OperateID");
query.CreateParameter <int>("@OperateID", filter.OperateID.Value, SqlDbType.Int);
}
if (filter.BeginDate != null)
{
sb.Append(" AND CreateTime >= @BeginDate");
query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
sb.Append(" AND CreateTime <= @EndDate");
query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
if (sb.Length > 0)
{
sb.Remove(0, 4);
}
query.Pager.TableName = "bx_PointLogs";
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "LogID";
query.Pager.SortField = "LogID";
query.Pager.IsDesc = true;
query.Pager.PageSize = filter.PageSize;
query.Pager.SelectCount = true;
query.Pager.Condition = sb.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
PointLogCollection logs = new PointLogCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
logs.TotalRecords = reader.Get <int>(0);
}
}
return(logs);
}
}
}
public override VisitorCollection SelectVisitors(int userID, int pageNumber, int pageSize, out int totalCount)
{
totalCount = 0;
VisitorCollection visitors = new VisitorCollection();
using (SqlQuery query = new SqlQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.SortField = "CreateDate";
query.Pager.TableName = "bx_UserVisitors";
query.Pager.Condition = "UserID = @UserID";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
visitors = new VisitorCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
totalCount = reader.Get <int>(0);
}
}
}
}
return(visitors);
}
public override ChatSessionCollection GetChatSessions(int userID, int pageNumber, int pageSize)
{
ChatSessionCollection results = new ChatSessionCollection();
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_ChatSessions";
query.Pager.PrimaryKey = "ChatSessionID";
query.Pager.SortField = "UpdateDate";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
query.Pager.Condition = "UserID = @UserID";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
results = new ChatSessionCollection(reader);
while (reader.NextResult())
{
while (reader.Read())
{
results.TotalRecords = reader.Get <int>(00);
}
}
}
}
return(results);
}
public override DoingCollection GetEveryoneDoingsWithComments(int pageNumber, int pageSize, ref int?totalCount)
{
DoingCollection doings = null;
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_Doings]";
query.Pager.SortField = "[DoingID]";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
doings = new DoingCollection(reader);
if (totalCount == null && reader.NextResult() && reader.Read())
{
totalCount = reader.Get <int>(0);
}
doings.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillDoingComments(doings, db);
}
return(doings);
}
public override IList <int> SelectMayFriendIDs(int userID, int count)
{
List <int> mayFriendIDs = new List <int>();
string sql = @"SELECT DISTINCT TOP (@Count) FriendUserID
FROM bx_Friends WITH (NOLOCK)
WHERE UserID IN(SELECT FriendUserID FROM bx_Friends WHERE UserID=@UserID)
AND FriendUserID NOT IN(SELECT FriendUserID FROM bx_Friends WITH (NOLOCK) WHERE UserID=@UserID) AND FriendUserID <> @UserID;";
using (SqlQuery query = new SqlQuery())
{
query.CommandText = sql;
query.CreateTopParameter("@Count", count);
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
mayFriendIDs.Add(reader.Get <int>("FriendUserID"));
}
}
}
return(mayFriendIDs);
}
/// <summary>
/// 高级搜索
/// </summary>
public override NotifyCollection GetNotifiesBySearch(AdminNotifyFilter notifyFilter, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = FilterToCondition(query, notifyFilter);
query.Pager.IsDesc = true;
query.Pager.TableName = "[bx_Notify]";
query.Pager.SortField = "[NotifyID]";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = notifyFilter.PageSize;
query.Pager.SelectCount = true;
query.Pager.Condition = condition.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
NotifyCollection notifies = new NotifyCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
notifies.TotalRecords = reader.Get <int>(0);
}
}
return(notifies);
}
}
}
public override DeleteResult DeleteDoingsBySearch(AdminDoingFilter filter, IEnumerable <Guid> excludeRoleIDs, int deleteTopCount, out int deletedCount)
{
deletedCount = 0;
using (SqlQuery query = new SqlQuery())
{
string conditions = BuildConditionsByFilter(query, filter, excludeRoleIDs, true);
StringBuffer sql = new StringBuffer();
sql += @"
DECLARE @DeleteData table (UserID int, DoingID int);
INSERT INTO @DeleteData SELECT TOP (@TopCount) [UserID],[DoingID] FROM [bx_Doings] " + conditions + @";
DELETE [bx_Doings] WHERE DoingID IN (SELECT [DoingID] FROM @DeleteData);
SELECT @@ROWCOUNT;
SELECT [UserID],COUNT(*) AS [Count] FROM @DeleteData GROUP BY [UserID];";
query.CreateTopParameter("@TopCount", deleteTopCount);
query.CommandText = sql.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
DeleteResult deleteResult = new DeleteResult();
if (reader.Read())
{
deletedCount = reader.Get <int>(0);
}
if (reader.NextResult())
{
while (reader.Read())
{
deleteResult.Add(reader.Get <int>("UserID"), reader.Get <int>("Count"));
}
}
return(deleteResult);
}
}
}
public override DenouncingCollection GetDenouncingWithReply(DenouncingFilter filter, int pageNumber)
{
using (SqlSession db = new SqlSession())
{
DenouncingCollection denouncings = null;
using (SqlQuery query = db.CreateQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_Denouncings";
query.Pager.SortField = "DenouncingID";
query.Pager.IsDesc = filter.IsDesc;
query.Pager.SelectCount = true;
filter.Type = DenouncingType.Reply;
GetSearchDenouncingsCondition(query, filter);
using (XSqlDataReader reader = query.ExecuteReader())
{
denouncings = new DenouncingCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
denouncings.TotalRecords = reader.Get <int>(0);
}
}
}
}
FillDenouncingContents(denouncings, db);
if (denouncings.Count > 0)
{
int[] targetIDs = GetTargetIDs(denouncings);
PostCollectionV5 posts = PostBOV5.Instance.GetPosts(targetIDs);
for (int i = 0; i < denouncings.Count; i++)
{
for (int j = 0; j < posts.Count; j++)
{
if (denouncings[i].TargetID == posts[j].ThreadID)
{
denouncings[i].TargetReply = posts[j];
break;
}
}
}
}
return(denouncings);
}
}
public override Dictionary <string, DateTime> GetAllJobStatus()
{
Dictionary <string, DateTime> times = new Dictionary <string, DateTime>(StringComparer.CurrentCultureIgnoreCase);
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "bx_GetAllJobStatus";
query.CommandType = CommandType.StoredProcedure;
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
times.Add(reader.Get <string>("Type"), reader.Get <DateTime>("LastExecuteTime"));
}
}
return(times);
}
}
public override UserPayCollection GetUserPays(int userID, PaylogFilter filter, int pageSize, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = new StringBuilder(" AND UserID = @UserID");
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
if (filter != null)
{
if (filter.State < 2)
{
condition.Append(" AND State=@State");
query.CreateParameter <bool>("@State", (filter.State == 1), SqlDbType.Bit);
}
if (filter.BeginDate != null)
{
condition.Append(" AND PayDate>= @BeginDate");
query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND PayDate<= @EndDate");
query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
}
if (condition.Length > 5)
{
condition.Remove(0, 5);
}
query.Pager.TableName = "bx_Pay";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "PayID";
query.Pager.SelectCount = true;
query.Pager.SortField = "PayID";
query.Pager.Condition = condition.ToString();
query.Pager.IsDesc = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserPayCollection result = new UserPayCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
result.TotalRecords = reader.Get <int>(0);
}
}
return(result);
}
}
}
public override MissionCollection GetNewMissions(int userID, int?categoryID, int pageNumber, int pageSize, out int totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.IsDesc = false;
query.Pager.SortField = "[SortOrder]";
query.Pager.PrimaryKey = "[ID]";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
//query.Pager.TotalRecords = totalCount;
query.Pager.SelectCount = true;
query.Pager.TableName = "[bx_Missions]";
if (categoryID != null)
{
query.Pager.Condition = "CategoryID = @CategoryID AND ";
query.CreateParameter <int>("@CategoryID", categoryID.Value, SqlDbType.Int);
}
query.Pager.Condition += @"
ParentID IS NULL AND
( getdate() > [BeginDate] AND getdate() < [EndDate] AND [IsEnable] = 1 AND [ID] NOT IN (SELECT [MissionID] FROM [bx_UserMissions] WHERE [UserID] = @UserID)
OR
([CycleTime] > 0 AND [IsEnable] = 1 AND [ID] IN(
SELECT [MissionID] FROM [bx_UserMissions] WHERE [UserID] = @UserID AND [FinishPercent]=1 AND [Status]<>3 AND [CreateDate]<DATEADD(second,0-[CycleTime],getdate()) AND getdate() > [BeginDate] AND getdate() < [EndDate]
)
))
";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
MissionCollection missions = new MissionCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
totalCount = reader.Get <int>(0);
}
else
{
totalCount = 0;
}
}
else
{
totalCount = 0;
}
return(missions);
}
}
}
public override UserEmoticonInfoCollection AdminGetUserEmoticonInfos(EmoticonFilter filter, int pageIndex, IEnumerable <Guid> excludeRoleIDs)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "bx_UserEmoticonInfo";
query.Pager.PageSize = filter.Pagesize;
query.Pager.PageNumber = pageIndex;
query.Pager.SortField = "[UserID]";
if (filter.UserName == null)
{
filter.UserName = string.Empty;
}
if (filter.Order != null)
{
switch (filter.Order.Value)
{
case EmoticonFilter.OrderBy.SpaceSize:
query.Pager.SortField = "[TotalSizes]";
break;
case EmoticonFilter.OrderBy.EmoticonCount:
query.Pager.SortField = "[TotalEmoticons]";
break;
}
}
query.Pager.Condition = " Username LIKE '%'+@Username+'%'";
string excludeRoleUserIds = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIDs, query);
if (!string.IsNullOrEmpty(excludeRoleUserIds))
{
query.Pager.Condition += " AND " + excludeRoleUserIds;
}
query.CreateParameter <string>("@Username", filter.UserName, SqlDbType.NVarChar, 50);
query.Pager.SelectCount = true;
query.Pager.PrimaryKey = "[UserID]";
query.Pager.IsDesc = filter.IsDesc;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserEmoticonInfoCollection groups = new UserEmoticonInfoCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
groups.TotalRecords = reader.Get <int>(0);
}
}
return(groups);
}
}
}
public override ChatMessageCollection GetChatMessages(int userID, int targetUserID, int pageNumber, int pageSize, bool updateIsReaded)
{
ChatMessageCollection result;
using (SqlQuery query = new SqlQuery())
{
if (pageNumber != 0)
{
query.Pager.BeforeExecuteDealcre.Add("@TotalMessages", SqlDbType.Int);
query.Pager.BeforeExecute = @"SELECT @TotalMessages = TotalMessages FROM bx_ChatSessions WHERE UserID = @UserID AND TargetUserID = @TargetUserID;";
query.Pager.TableName = "bx_ChatMessages";
query.Pager.SortField = "MessageID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
query.Pager.TotalRecordsVariable = "@TotalMessages";
query.Pager.Condition = "UserID = @UserID AND TargetUserID = @TargetUserID";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
query.CreateParameter <int>("@TargetUserID", targetUserID, SqlDbType.Int);
if (updateIsReaded)
{
query.Pager.AfterExecute = @"
UPDATE bx_ChatMessages SET IsRead = 1 WHERE UserID = @UserID AND TargetUserID = @TargetUserID AND IsRead = 0;
";
}
}
else
{
query.CommandText = "SELECT Top " + pageSize + " * FROM bx_ChatMessages WHERE UserID = @UserID AND TargetUserID = @TargetUserID ORDER BY MessageID Desc;";
query.CommandText += "SELECT COUNT(*) FROM bx_ChatMessages WHERE UserID = @UserID AND TargetUserID = @TargetUserID;";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
query.CreateParameter <int>("@TargetUserID", targetUserID, SqlDbType.Int);
}
using (XSqlDataReader reader = query.ExecuteReader())
{
result = new ChatMessageCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
result.TotalRecords = reader.Get <int>(0);
}
}
}
return(result);
}
}
private BasicThread GetThread(XSqlDataReader reader, DateTime? expiresDate)
{
ThreadType threadType = reader.Get<ThreadType>("ThreadType");
switch (threadType)
{
case ThreadType.Poll:
PollThreadV5 poll = new PollThreadV5(reader);
if (expiresDate != null)
poll.IsClosed = expiresDate.Value <= DateTimeUtil.Now;
return (BasicThread)poll;
case ThreadType.Polemize:
PolemizeThreadV5 polemize = new PolemizeThreadV5(reader);
if (expiresDate != null)
polemize.IsClosed = expiresDate.Value <= DateTimeUtil.Now;
return (BasicThread)polemize;
case ThreadType.Question:
QuestionThread question = new QuestionThread(reader);
if (question.IsClosed == false)
{
if (expiresDate != null)
question.IsClosed = expiresDate.Value <= DateTimeUtil.Now;
}
return (BasicThread)question;
default:
return new BasicThread(reader);
}
}
private PostCollectionV5 GetPosts(XSqlDataReader reader, bool isFirstRead)
{
PostCollectionV5 posts = new PostCollectionV5();
List<int> replyIDs = new List<int>();
if (isFirstRead)
{
while (reader.Read())
{
PostV5 post = new PostV5(reader);
post.Attachments = new AttachmentCollection();
post.PostMarks = new PostMarkCollection();
posts.Add(post);
replyIDs.Add(post.PostID);
}
}
else
{
if (reader.NextResult())
{
while (reader.Read())
{
PostV5 post = new PostV5(reader);
post.Attachments = new AttachmentCollection();
post.PostMarks = new PostMarkCollection();
posts.Add(post);
replyIDs.Add(post.PostID);
}
}
}
//读取下一个结果集
if (reader.NextResult())
{
while (reader.Read()) //附件列表
{
Attachment attachment = new Attachment(reader);
int replyIndex = replyIDs.IndexOf(attachment.PostID);
if (replyIndex != -1)
{
posts[replyIndex].Attachments.Add(attachment);
}
}
}
//历史附件
if (reader.NextResult())
{
while (reader.Read()) //附件列表
{
Attachment attachment = new Attachment(reader);
attachment.AttachType = AttachType.History;
int hpostID = reader.Get<int>("HPostID");
int replyIndex = replyIDs.IndexOf(hpostID);
if (replyIndex != -1)
{
posts[replyIndex].Attachments.Add(attachment);
}
}
}
if (reader.NextResult())//评分列表
{
while (reader.Read())
{
PostMark postMark = new PostMark(reader);
int replyIndex = replyIDs.IndexOf(postMark.PostID);
if (replyIndex != -1)
{
posts[replyIndex].PostMarks.Add(postMark);
}
}
}
if (reader.NextResult())
{
string s = null;
while (reader.Read())
{
s = reader.Get<string>(0);
}
#if !Publish
if (reader.SqlQuery.TempInfo != null)
reader.SqlQuery.TempInfo += "-----" + s;
else
reader.SqlQuery.TempInfo = s;
#endif
}
return posts;
}
private void GetThread(XSqlDataReader reader, AttachmentCollection attachments, DateTime? expiresDate, out BasicThread thread, out PostV5 post, out List<int> attachmentIDs, out Dictionary<string, int> fileIDs)
{
attachmentIDs = new List<int>();
fileIDs = new Dictionary<string, int>();
thread = null;
post = null;
while (reader.Read())
{
thread = GetThread(reader, expiresDate);
}
if (attachments.Count > 0)
{
//本地刚上传的附件 ID
if (reader.NextResult())
{
while (reader.Read())
{
attachmentIDs.Add(reader.Get<int>(0));
}
}
//所有附件
if (reader.NextResult())
{
while (reader.Read())
{
string fileID = reader.Get<string>("fileID");
if (fileIDs.ContainsKey(fileID) == false)
fileIDs.Add(fileID, reader.Get<int>("attachmentID"));
}
}
}
if (reader.NextResult())
{
while (reader.Read())
{
post = new PostV5(reader);
}
}
}
private string GetExtendData(BasicThread thread, ThreadType threadType, XSqlDataReader reader, bool isFirstRead)
{
string extendData = null;
switch (threadType)
{
case ThreadType.Poll:
PollThreadV5 poll = thread == null ? new PollThreadV5() : (PollThreadV5)thread;
if (isFirstRead || reader.NextResult())
{
while (reader.Read())
{
poll.FillPoll(reader);
}
}
if (reader.NextResult())
{
poll.PollItems = new PollItemCollectionV5(reader);
}
if (thread == null || poll.VotedUserIDs == null)
poll.VotedUserIDs = new List<int>();
if (reader.NextResult())
{
while (reader.Read())
{
poll.VotedUserIDs.Add(reader.Get<int>(0));
}
}
extendData = poll.GetExtendData();
break;
case ThreadType.Question:
QuestionThread question = thread == null ? new QuestionThread() : (QuestionThread)thread;
if (isFirstRead || reader.NextResult())
{
while (reader.Read())
{
question.FillQuestion(reader);
}
}
if (thread == null || question.Rewards == null)
question.Rewards = new Dictionary<int, int>();
if (reader.NextResult())
{
while (reader.Read())
{
question.Rewards.Add(reader.Get<int>("PostID"), reader.Get<int>("Reward"));
}
}
extendData = question.GetExtendData();
break;
case ThreadType.Polemize:
PolemizeThreadV5 polemize = thread == null ? new PolemizeThreadV5() : (PolemizeThreadV5)thread;
if (isFirstRead || reader.NextResult())
{
while (reader.Read())
{
polemize.FillPolemize(reader);
}
}
if (reader.NextResult())
{
polemize.FillPolemizeUsers(reader);
}
extendData = polemize.GetExtendData();
break;
default: break;
}
if (thread != null)
thread.ExtendDataIsNull = false;
return extendData;
}