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