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