/// <summary>
/// 命令执行
/// </summary>
/// <param name="context"></param>
public override void Execute(DataContext context)
{
byte[] cmdData = context.CmdData;
if (cmdData.Length == 0)
{
context.Flush(RespondCode.CmdDataLack);
return;
}
TopicQuery query = cmdData.ProtoBufDeserialize<TopicQuery>();
if (Compiled.Debug)
query.Debug("=== Bbs.QueryUserTopic 上行数据 ===");
if (query.OwnerId == 0 || query.OwnerId == context.UserId)
{
context.Flush(RespondCode.DataInvalid);
return;
}
query.AttachContent = string.Empty;
query.ForumId = 0;
query.HasBestAnswer = true;
query.Keyword = string.Empty;
query.OrderType = OrderType.Default;
UserCacheInfo userCache = UserBiz.ReadUserCacheInfo(context.UserId);
TopicQueryConditions condition = new TopicQueryConditions(userCache.UserSite, query, 1);
PageResult<TopicInfo> pageResult = BbsBiz.GetPageTopicList(condition, query.QueryIndex, query.QuerySize);
//TopicList topicList = pageResult.ToTopicList();
context.Flush<TopicList>(pageResult.ToTopicList());
}
/// <summary>
/// 获取符合查询条件的帖子总数
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public static int GetTopicCount(TopicQueryConditions condition)
{
Tuple <string, string, List <string>, List <object> > tuples = CreateSqlPartionByCondition(condition);
string querySql = string.Format("SELECT COUNT(0) FROM Forum_Topics WHERE {0}", tuples.Item1);
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql))
{
cmd.AddInputParameters(string.Join(",", tuples.Item3), tuples.Item4.ToArray());
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
/// <summary>
/// 查询符合指定条件的帖子信息分页列表
/// </summary>
/// <param name="condition"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static PageResult <TopicInfo> GetPageTopicList(TopicQueryConditions condition, int pageIndex, int pageSize)
{
PageResult <TopicInfo> pageResult = new PageResult <TopicInfo>
{
RecordCount = BbsData.GetTopicCount(condition),
PageIndex = pageIndex,
PageSize = pageSize
};
pageResult.Data = BbsData.GetTopicInfoList(condition, pageResult.PageIndex, pageResult.PageSize);
return(pageResult);
}
/// <summary>
/// 命令执行
/// </summary>
/// <param name="context"></param>
public override void Execute(DataContext context)
{
byte[] cmdData = context.CmdData;
if (cmdData.Length == 0)
{
context.Flush(RespondCode.CmdDataLack);
return;
}
TopicQuery query = cmdData.ProtoBufDeserialize <TopicQuery>();
if (Compiled.Debug)
{
query.Debug("=== Bbs.QueryTopic 上行数据 ===");
}
UserCacheInfo userCache = UserBiz.ReadUserCacheInfo(context.UserId);
TopicQueryConditions condition = new TopicQueryConditions(userCache.UserSite, query, 1);
PageResult <TopicInfo> pageResult = BbsBiz.GetPageTopicList(condition, query.QueryIndex, query.QuerySize);
//TopicList topicList = pageResult.ToTopicList();
context.Flush <TopicList>(pageResult.ToTopicList());
}
/// <summary>
/// 获取符合查询条件的帖子信息列表
/// </summary>
/// <param name="condition"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IEnumerable <TopicInfo> GetTopicInfoList(TopicQueryConditions condition, int pageIndex, int pageSize)
{
Tuple <string, string, List <string>, List <object> > tuples = CreateSqlPartionByCondition(condition);
string querySql = @"SET ROWCOUNT @PageSize
SELECT *
FROM ( SELECT TopicId ,
UserId ,
AttachContent ,
Title ,
Intro ,
Icon ,
Voice ,
Remark ,
Reward ,
IsQuestion ,
IsAllowReply ,
IsStick ,
IsRefined ,
AttrChangedMark ,
ExpChanged ,
VirtualCoinChanged ,
BestAnswerId ,
FavouredCount ,
RepliedCount ,
ViewCount ,
FavoritedCount ,
CreateDate ,
ROW_NUMBER() OVER ( {1} ) AS RowNum
FROM Forum_Topics
WHERE {0}
) AS List
WHERE List.RowNum > ( @PageIndex - 1 ) * @PageSize
{1}";
querySql = string.Format(querySql, tuples.Item1, tuples.Item2);
tuples.Item3.AddRange(new List <string>(2)
{
"PageSize", "PageIndex"
});
tuples.Item4.AddRange(new List <object>(2)
{
pageSize, pageIndex
});
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql))
{
cmd.AddInputParameters(string.Join(",", tuples.Item3), tuples.Item4.ToArray());
List <TopicInfo> resultData = new List <TopicInfo>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new TopicInfo
{
TopicId = (int)reader["TopicId"],
UserId = (int)reader["UserId"],
AttachContent = (string)reader["AttachContent"],
Title = (string)reader["Title"],
Intro = (string)reader["Intro"],
Icon = (string)reader["Icon"],
Voice = (string)reader["Voice"],
Remark = (string)reader["Remark"],
Reward = (int)reader["Reward"],
IsQuestion = (bool)reader["IsQuestion"],
IsAllowReply = (bool)reader["IsAllowReply"],
IsStick = (bool)reader["IsStick"],
IsRefined = (bool)reader["IsRefined"],
AttrChangedMark = (string)reader["AttrChangedMark"],
ExpChanged = (int)reader["ExpChanged"],
VirtualCoinChanged = (int)reader["VirtualCoinChanged"],
BestAnswerId = (int)reader["BestAnswerId"],
FavouredCount = (int)reader["FavouredCount"],
RepliedCount = (int)reader["RepliedCount"],
ViewCount = (int)reader["ViewCount"],
FavoritedCount = (int)reader["FavoritedCount"],
CreateDate = (DateTime)reader["CreateDate"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// <para>根据查询条件，获取查询语句补充块，返回结果：Tuple<string, string, List<string>, List<object>> </para>
/// <para>Item1：查询WHERE语句部分</para>
/// <para>Item2：查询ORDER BY 语句部分</para>
/// <para>Item3：查询参数集合</para>
/// <para>Item4：查询参数值集合</para>
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
private static Tuple <string, string, List <string>, List <object> > CreateSqlPartionByCondition(TopicQueryConditions condition)
{
string sqlWhere = "SchoolId = @SchoolId";
List <string> pNameList = new List <string> {
"SchoolId"
};
List <object> pValueList = new List <object> {
condition.SchoolId
};
if (condition.Status >= 0)
{
sqlWhere += " AND [Status] = @Status ";
pNameList.Add("Status");
pValueList.Add(condition.Status);
}
if (condition.ForumId > 0)
{
sqlWhere += " AND ForumId = @ForumId ";
pNameList.Add("ForumId");
pValueList.Add(condition.ForumId);
}
if (condition.OwnerId > 0)
{
sqlWhere += " AND UserId = @OwnerId ";
pNameList.Add("OwnerId");
pValueList.Add(condition.OwnerId);
}
if (!string.IsNullOrEmpty(condition.AttachContent))
{
sqlWhere += " AND AttachContent = @AttachContent ";
pNameList.Add("AttachContent");
pValueList.Add(condition.AttachContent);
}
if (!condition.HasBestAnswer)
{
sqlWhere += " AND BestAnswerId = 0 ";
}
if (!string.IsNullOrEmpty(condition.Keyword))
{
sqlWhere += " AND ( AttachContent LIKE @Keyword OR Title LIKE @Keyword OR Intro LIKE @Keyword ) ";
pNameList.Add("Keyword");
pValueList.Add(string.Format("%{0}%", condition.Keyword));
}
string[] orders = new string[4]
{
" ORDER BY CreateDate DESC, TopicId DESC ",
" ORDER BY CreateDate DESC, TopicId DESC ",
" ORDER BY RepliedCount DESC, CreateDate DESC, TopicId DESC ",
" ORDER BY FavouredCount DESC, CreateDate DESC, TopicId DESC "
};
string sqlOrder = orders[condition.Sort];
return(new Tuple <string, string, List <string>, List <object> >(sqlWhere, sqlOrder, pNameList, pValueList));
}
