/// <summary> /// 保存赞/踩 /// </summary> /// <param name="?"></param> /// <returns></returns> public PraiseTreadItemVM SavePraiseOrTread(BBSPraiseTreadVM ptVM) { if (ptVM == null || !ptVM.BindTableID.HasValue || string.IsNullOrEmpty(ptVM.BindTableName)) { return(null); } using (var dbContext = new DbContext().ConnectionStringName(ConnectionUtil.connBBS, new MySqlProvider()).UseTransaction(true)) { dbContext.Sql(@" delete from bbspraisetread where BindTableID = @bindTableID and BindTableName = @bindTableName and UserID = @userID ") .Parameter("bindTableID", ptVM.BindTableID) .Parameter("bindTableName", ptVM.BindTableName) .Parameter("userID", ptVM.UserID) .Execute(); dbContext.Insert("bbspraisetread").Column("PraiseOrTread", ptVM.PraiseOrTread) .Column("UserID", ptVM.UserID) .Column("BindTableName", ptVM.BindTableName) .Column("BindTableID", ptVM.BindTableID) .Column("InputTime", ptVM.InputTime) .ExecuteReturnLastId <long>(); PraiseTreadCountVM ptCountVM = dbContext.Sql(@" select SUM(case when pt.PraiseOrTread = 1 then 1 else 0 end)as PraiseCount, SUM(case when pt.PraiseOrTread = 2 then 1 else 0 end)as TreadCount from bbspraisetread pt where pt.BindTableName = @bindTableName and pt.BindTableID = @bindTableID ") .Parameter("bindTableName", ptVM.BindTableName) .Parameter("bindTableID", ptVM.BindTableID) .QuerySingle <PraiseTreadCountVM>((PraiseTreadCountVM vm, IDataReader re) => { vm.PraiseCount = Convert.ToInt32(re["PraiseCount"]); vm.TreadCount = Convert.ToInt32(re["TreadCount"]); }); dbContext.Commit(); PraiseTreadItemVM itemVM = new PraiseTreadItemVM(); itemVM.Item = ptVM; itemVM.PraiseCount = ptCountVM.PraiseCount; itemVM.TreadCount = ptCountVM.TreadCount; return(itemVM); } }
public PageVM <BBSCommentVM> GetComments(CommentQuery query) { if (!query.AnswerID.HasValue) { return(null); } using (var dbContext = new DbContext().ConnectionStringName(ConnectionUtil.connBBS, new MySqlProvider())) { string sql = string.Format(@" DROP PROCEDURE IF EXISTS `Pro_GetComments`; DROP PROCEDURE IF EXISTS `Pro_RecurseGetReply`; -- 获取所有评论的存储过程 CREATE PROCEDURE Pro_GetComments(IN answerId BIGINT) BEGIN DECLARE Finish INT DEFAULT 0; DECLARE CommentID BIGINT DEFAULT 0; DECLARE cur_comment CURSOR FOR SELECT ID FROM bbscomment WHERE BindTableName = 'bbsanswer' AND BindTableID = answerId ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finish = 1; DROP TABLE IF EXISTS TempComment; CREATE TEMPORARY TABLE TempComment SELECT ID FROM bbscomment WHERE BindTableName = 'bbsanswer' AND BindTableID = answerId; OPEN cur_comment; REPEAT FETCH cur_comment INTO CommentID ; CALL Pro_RecurseGetReply(CommentID); UNTIL Finish=1 END REPEAT; CLOSE cur_comment; SELECT * FROM bbscomment b where b.ID in ( select ID from TempComment ); END ; -- 递归获取评论 CREATE PROCEDURE Pro_RecurseGetReply(in rootId BIGINT) BEGIN DECLARE Done BIGINT DEFAULT 1 ; DECLARE TempID BIGINT DEFAULT 0; DECLARE cur_reply CURSOR FOR SELECT c.ID FROM bbscomment c WHERE c.BindTableName = 'bbscomment' and c.BindTableID = rootId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1; OPEN cur_reply; FETCH cur_reply INTO TempID ; INSERT INTO TempComment VALUES(TempID); WHILE TempID!=NULL DO CALL Pro_RecurseFindComment(TempID); END WHILE; CLOSE cur_reply; END ; call Pro_GetComments({0}); ", query.AnswerID); string sqlPage = string.Format(@" limit {0},{1}", (query.PageIndex - 1) * query.PageSize, query.PageSize); List <BBSCommentVM> commentList = dbContext.Sql(sql) .QueryMany <BBSCommentVM>((BBSCommentVM vm, IDataReader reader) => { vm.ID = reader.GetInt64("ID"); vm.BindTableID = reader.GetInt64("BindTableID"); vm.BindTableName = reader.GetString("BindTableName"); vm.UserID = reader.GetInt32("UserID"); vm.UserName = reader.GetString("UserName"); vm.Content = reader.GetString("Content"); vm.InputTime = reader.GetDateTime("InputTime"); }); if (commentList == null || commentList.Count == 0) { return(null); } string commentIds = ""; commentList.ForEach((item) => { commentIds += item.ID + ","; }); commentIds = commentIds.Trim(','); string sqlComment = string.Format(@" select BindTableID as CommentID, sum(case when p.PraiseOrTread = 1 then 1 else 0 end )as PraiseCount, sum(case when p.PraiseOrTread = 2 then 1 else 0 end )as TreadCount, sum(case when p.UserID = {1} and p.PraiseOrTread =1 then 1 when p.UserID = {1} and p.PraiseOrTread = 2 then 2 else 0 end )as VoteStatus from bbspraisetread p where p.BindTableName = 'bbscomment' and p.BindTableID in ({0}) GROUP BY BindTableID ", commentIds, query.UserID); List <PraiseTreadCountVM> voteList = dbContext.Sql(sqlComment).QueryMany <PraiseTreadCountVM>((PraiseTreadCountVM vm, IDataReader re) => { vm.ID = re.GetInt64("CommentID"); vm.PraiseCount = Convert.ToInt32(re["PraiseCount"]); vm.TreadCount = Convert.ToInt32(re["TreadCount"]); vm.VoteStatus = Convert.ToInt32(re["VoteStatus"]); }); //获取每个评论的赞/踩数量 if (voteList != null && voteList.Count > 0) { commentList.ForEach((item) => { PraiseTreadCountVM temp = voteList.Find(v => v.ID == item.ID); if (temp != null) { item.VoteData = temp; } }); } List <BBSCommentVM> resultList = commentList.Where(c => c.BindTableName == "bbsanswer").ToList(); //总条数 int totalCount = dbContext.Sql(sql).QueryMany <int>().Count; //总页数 int totalPages = (int)Math.Ceiling(((double)totalCount / query.PageSize.Value)); PageVM <BBSCommentVM> pgVM = new PageVM <BBSCommentVM>() { Data = commentList, TotalCount = totalCount, TotalPages = totalPages }; return(pgVM); } }