public PageSearchResult <CommentViewInfo> CommentSearchResult(CommentSearchModel search)
{
// string sql = string.Format(@"select cm.JokeId,cm.ID as CommentId,cm.Content as Comment,cm.UserID,us.UserName,cm.Floor from T_Comment cm
// inner join T_Joke jk on cm.JokeId = jk.ID
// inner join T_User us on us.id=cm.UserID where cm.JokeId={0}",search.JokeID);
string sql = string.Format(@"declare @@pagenum int ={1};
declare @@pagesize int = {2};
with tmp as(
select ROW_NUMBER() OVER(ORDER by cm.ID desc) as Num, cm.JokeId,cm.ID as CommentId,cm.Content as Comment,cm.UserID,us.UserName,cm.Floor from T_Comment cm
inner join T_Joke jk on cm.JokeId = jk.ID
inner join T_User us on us.id=cm.UserID
where cm.JokeId={0})
select JokeId,CommentId,Comment,UserID,UserName,UserName,Floor from tmp where Num>(@@pagenum-1)*@@pagesize and Num<=@@pagenum*@@pagesize;
select count(1) from T_Comment with(nolock) where JokeId={0}", search.JokeID, search.Page, search.PageSize);
var items = this.jokeDatabase.FetchMultiple <CommentViewInfo, int>(sql, search.Page, search.PageSize);
var pageResult = new PageSearchResult <CommentViewInfo>()
{
Items = items.Item1,
Page = search.Page,
PageSize = search.PageSize,
TotalCount = items.Item2[0]
};
return(pageResult);
}
public PageSearchResult <T_Comment> CommentManageSearch(CommentManageSearch search)
{
Sql where = Sql.Builder.Where("1=1").OrderBy("ID Desc");
var page = this.jokeDatabase.Page <T_Comment>(search.Page, search.PageSize, where);
var pageResult = new PageSearchResult <T_Comment>()
{
Items = page.Items,
Page = search.Page,
PageSize = search.PageSize,
TotalCount = (int)page.TotalItems
};
return(pageResult);
}
public PageSearchResult <T_UserLog> UserLogSearch(UserLogSearchModel search)
{
Sql where = Sql.Builder.Where("1=1").OrderBy("ID DESC");
var pageResult = this.jokeDatabase.Page <T_UserLog>(search.Page, search.PageSize, where);
PageSearchResult <T_UserLog> pageViewResult = new PageSearchResult <T_UserLog>()
{
Items = pageResult.Items,
Page = (int)pageResult.CurrentPage,
PageSize = (int)pageResult.ItemsPerPage,
TotalCount = (int)pageResult.TotalItems
};
return(pageViewResult);
}
public PageSearchResult <T_UserLog> UserLogSearch(UserLogSearchModel search)
{
var data = db.UserLogs.OrderBy(x => x.ID).Where(x => 1 == 1);
var pageResult = data.ToList();
PageSearchResult <T_UserLog> pageViewResult = new PageSearchResult <T_UserLog>()
{
Items = pageResult,
Page = search.Page,
PageSize = search.PageSize,
TotalCount = pageResult.Count
};
return(pageViewResult);
}
public PageSearchResult <JokePostInfo> UserJokesSearch(UserJokesSearchModel search)
{
StringBuilder where = new StringBuilder();
if (search.UserId != null)
{
where.AppendFormat(" and j.postid={0}", search.UserId);
}
if (search.JokeState != null)
{
where.AppendFormat(" and j.State={0}", search.JokeState);
}
if (search.JokeType != null)
{
where.AppendFormat(" and j.Type={0}", search.JokeType);
}
string sql = string.Format(@"declare @@pagenum int=@0;
declare @@pagesize int = @1;
with tmp as
(
SELECT ROW_NUMBER() over(order by j.ID DESC) as Num, j.ID as JokeId,j.Title,j.Content,j.LikeCount,j.HateCount,u.NikeName,u.ID as UserId,j.Type as JokeType,j.AddDate as PostDate,u.UserName,j.State as JokeState,c.Name as Category,c.Pinyin as CategoryPinyin from T_Joke j with(NOLOCK)
inner join T_User u on u.ID = j.PostID
inner join T_Category c on c.ID = j.Category
where 1=1 {0}
)
SELECT JokeId,Title,Content,LikeCount,HateCount,NikeName,UserId,JokeType,PostDate,UserName,JokeState,CategoryPinyin,Category from tmp where Num>(@@pagenum-1)*@@pagesize and Num<=@@pagenum*@@pagesize;
select COUNT(1) from T_Joke j where 1=1 {0}", where.ToString());
var items = this.jokeDatabase.FetchMultiple <JokePostInfo, int>(sql, search.Page, search.PageSize);
PageSearchResult <JokePostInfo> pageResult = new PageSearchResult <JokePostInfo>()
{
Items = items.Item1,
Page = search.Page,
PageSize = search.PageSize,
TotalCount = items.Item2[0]
};
return(pageResult);
}
