public ResultDto ChangeEssayLike(long userId, long essayId)
{
var resultDto = new ResultDto();
//多处改动使用事务时则用事务级别隔离read committed加行锁
DynamicParameters pars = new DynamicParameters();
pars.Add("@iuserId", userId);
pars.Add("@iessayId", essayId);
pars.Add("@icreationTime", DateTime.Now);
pars.Add("@olikeNum", 0, DbType.Int32, ParameterDirection.Output);
pars.Add("@oisLike", null, DbType.Byte, ParameterDirection.Output);
try
{
var ee = _defaultRepository.ConnExecute(conn => conn.Execute("changeEssayLike", pars, commandType: CommandType.StoredProcedure));//res2.Count = 80
var likeNum = pars.Get <int>("@olikeNum");
var isLike = pars.Get <byte>("@oisLike") == 1;
resultDto.Result = true;
resultDto.Data = new { LikeNum = likeNum, IsLike = isLike };
}
catch (Exception ex)
{
resultDto.Result = false;
resultDto.Message = ex.Message;
}
return(resultDto);
}
public CoverDto GetDateCover(DateTime showDate)
{
string sql = @"select
cover.*,
essay.CoverMediaType EssayCoverMediaType,
essay.CoverPath EssayCoverPath,
essay.CoverExtension EssayCoverExtension,
essay.Title EssayTitle,
essay.SubContent EssayContent,
essay.PageUrl EssayPageUrl,
essay.Location EssayLocation,
essay.CreationTime EssayCreationTime,
kuser.NickName KuserNickName,
kuser.Introduction KuserIntroduction,
kuser.AvatarUrl KuserAvatarUrl
from
(select * from cover where showdate <= @ShowDate order by showdate desc limit 1 ) cover
left join essay on cover.essayid= essay.id
left join kuser on essay.creatoruserid=kuser.id
where essay.isdeleted=0 ";
return(_defaultRepository.ConnExecute(conn =>
{
return conn.QueryFirstOrDefault <CoverDto>(sql, new { ShowDate = showDate });
}));
}
public IEnumerable <EssayCommentDto> GetEssayCommentList(long id)
{
string sql = @"select essayComment.*,kuser.AvatarUrl KuserAvatarUrl,kuser.NickName KuserNickName
from essayComment
left join kuser on essayComment.CreatorUserId=kuser.Id
where essayComment.EssayId=@EssayId and essayComment.IsDeleted=0
order by essayComment.CreationTime desc";
return(_defaultRepository.ConnExecute(conn => conn.Query <EssayCommentDto>(sql, new { EssayId = id })));
}
public IEnumerable <KuserEntity> GetExistUser(string name, string phone, string nickName)
{
string sql = "select * from kuser where `Name`=@Name or Phone=@Phone or NickName=@NickName";
return(_defaultRepository.ConnExecute(conn => conn.Query <KuserEntity>(sql, new { Name = name, Phone = phone, NickName = nickName })));
}
public IEnumerable <TopMediaDto> GetHomeMediaPictureList(string keyword, int pageIndex, int pageSize, string orderBy)
{
int pageStart = ((pageIndex - 1) * pageSize);
pageStart = pageStart > 0 ? (pageStart - 1) : pageStart;
if (string.IsNullOrEmpty(orderBy))
{
orderBy = "essay.id desc";
}
else
{
switch (orderBy)
{
case "choiceness": orderBy = "(essay.LikeNum+essay.ShareNum+essay.BrowseNum+essay.CommentNum) desc,essay.Score desc,essay.Id desc"; break;
default: break;
}
}
var sql = $@"select essay.Id,essay.Category,essay.Score,essay.ScoreHeadCount,essay.ShareNum,essay.LikeNum,essay.BrowseNum,essay.CommentNum,essay.Title,essay.SubContent,essay.PageUrl,essay.Location,essay.CreatorUserId,essay.CreationTime,essay.CoverPath,essay.CoverMediaType,essay.CoverExtension,
kuser.AvatarUrl,kuser.NickName CreatorNickName,tag.*
from
essay
join kuser on essay.CreatorUserId=kuser.Id
left join tag on essay.Id=tag.EssayId and tag.Sort=1
where essay.IsPublish=1 and essay.IsDeleted=0 {((!string.IsNullOrEmpty(keyword )) ? " and (essay.Category like @Keyword or essay.Title like @Keyword ) " : "")}
order by {orderBy} limit @PageStart,@PageSize
";
var param = new { Keyword = $"%{keyword}%", PageStart = pageStart, PageSize = pageSize };
//return ConnExecute(conn =>
//{
// var dtoList = new List<TopMediaDto>();
// conn.Query<TopMediaDto, TagEntity, bool>(sql, (dto, tag) =>
// {
// var currentDto = dtoList.FirstOrDefault(d => d.Id == dto.Id);
// if (currentDto == null)
// {
// dto.TagList = new List<TagEntity>();
// dto.TagList.Add(tag);
// dtoList.Add(dto);
// }
// else
// {
// currentDto.TagList.Add(tag);
// }
// return true;
// },
// param: param,
// splitOn: "Id");
// return dtoList;
//});
return(_defaultRepository.ConnExecute(conn =>
{
var dtoList = conn.Query <TopMediaDto, TagEntity, TopMediaDto>(sql, (dto, tag) =>
{
if (tag != null)
{
dto.TagList = new List <TagEntity>();
dto.TagList.Add(tag);
}
return dto;
},
param: param,
splitOn: "Id");
return dtoList;
}));
}
