public static string ToRPC(this PostOrderBy e)
{
switch (e)
{
case PostOrderBy.Author:
return("author");
case PostOrderBy.Date:
return("date");
case PostOrderBy.ID:
return("id");
case PostOrderBy.Modified:
return("modified");
case PostOrderBy.Name:
return("name");
case PostOrderBy.None:
return("none");
case PostOrderBy.Parent:
return("parent");
case PostOrderBy.Random:
return("random");
case PostOrderBy.Title:
return("title");
}
throw new NotImplementedException();
}
public Post GetLatestPostForThread(Guid threadId, PostOrderBy order)
{
var results = _context.Post
.Include(x => x.User).AsNoTracking()
.Include(x => x.Topic)
.Include(x => x.Topic.Group)
.Where(x => x.ThreadId == threadId && x.Pending != true);
// Sort what order the posts are sorted in
switch (order)
{
case PostOrderBy.Newest:
results = results.OrderByDescending(x => x.DateCreated);
break;
case PostOrderBy.Votes:
results = results.OrderByDescending(x => x.VoteCount).ThenBy(x => x.DateCreated);
break;
default:
results = results.OrderBy(x => x.DateCreated);
break;
}
return(results.FirstOrDefault());
}
/// <summary>
/// Returns a paged list of posts by a topic id
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="amountToTake"></param>
/// <param name="topicId"></param>
/// <param name="order"></param>
/// <returns></returns>
public async Task <PaginatedList <Post> > GetPagedPostsByTopic(int pageIndex, int pageSize, int amountToTake,
Guid topicId, PostOrderBy order)
{
// Get the topics using an efficient
var results = _context.Post
.Include(x => x.User).AsNoTracking()
.Include(x => x.Topic)
.Include(x => x.Topic.Group)
.Where(x => x.Topic.Id == topicId && !x.IsTopicStarter && x.Pending != true && x.ThreadId == null);
// Sort what order the posts are sorted in
switch (order)
{
case PostOrderBy.Newest:
results = results.OrderByDescending(x => x.DateCreated);
break;
case PostOrderBy.Votes:
results = results.OrderByDescending(x => x.VoteCount).ThenBy(x => x.DateCreated);
break;
default:
results = results.OrderBy(x => x.DateCreated);
break;
}
return(await PaginatedList <Post> .CreateAsync(results.AsNoTracking(), pageIndex, pageSize));
}
public virtual ActionResult GetPostDataTable(int page = 0, int count = 10, Order order = Order.Descending,
PostOrderBy orderBy = PostOrderBy.ById)
{
ViewBag.CurrentPage = page + 1;
ViewBag.TotalRecords = _postService.GetPostsCount();
ViewBag.Count = count;
return PartialView(MVC.Admin.Post.Views._PostDataTable,
_postService.GetPostDataTable(page, count, order, orderBy));
}
public virtual ActionResult GetPostDataTable(int page = 0, int count = 10, Order order = Order.Descending,
PostOrderBy orderBy = PostOrderBy.ById)
{
ViewBag.CurrentPage = page + 1;
ViewBag.TotalRecords = _postService.GetPostsCount();
ViewBag.Count = count;
return(PartialView(MVC.Admin.Post.Views._PostDataTable,
_postService.GetPostDataTable(page, count, order, orderBy)));
}
public PagedList <Post> GetPagedPostsByTopic(int pageIndex, int pageSize, int amountToTake, Guid topicId, PostOrderBy order)
{
// We might only want to display the top 100
// but there might not be 100 topics
var total = _context.Post.Count(x => x.Topic.Id == topicId && !x.IsTopicStarter && x.Pending != true);
if (amountToTake < total)
{
total = amountToTake;
}
// Get the topics using an efficient
var results = _context.Post
.Include(x => x.User)
.Include(x => x.Topic)
.Where(x => x.Topic.Id == topicId && !x.IsTopicStarter && x.Pending != true);
// Sort what order the posts are sorted in
switch (order)
{
case PostOrderBy.Newest:
results = results.OrderByDescending(x => x.DateCreated);
break;
case PostOrderBy.Votes:
results = results.OrderByDescending(x => x.VoteCount).ThenBy(x => x.DateCreated);
break;
default:
results = results.OrderBy(x => x.DateCreated);
break;
}
// sort the paging out
var posts = results.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
// Return a paged list
return(new PagedList <Post>(posts, pageIndex, pageSize, total));
}
/// <summary>
/// Returns a paged list of posts by a topic id
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="amountToTake"></param>
/// <param name="topicId"></param>
/// <param name="order"></param>
/// <returns></returns>
public PagedList<Post> GetPagedPostsByTopic(int pageIndex, int pageSize, int amountToTake, Guid topicId, PostOrderBy order)
{
// We might only want to display the top 100
// but there might not be 100 topics
var total = ContextPerRequest.Db.Post.AsNoTracking()
.Include(x => x.Topic)
.Count(x => x.Topic.Id == topicId && !x.IsTopicStarter && x.Pending != true);
if (amountToTake < total)
{
total = amountToTake;
}
// Get the topics using an efficient
var results = ContextPerRequest.Db.Post.AsNoTracking()
.Where(x => x.Topic.Id == topicId && !x.IsTopicStarter && x.Pending != true)
.Include(x => x.Topic)
.Include(x => x.Votes)
.Include(x => x.Files);
// Sort what order the posts are sorted in
switch (order)
{
case PostOrderBy.Newest:
results = results.OrderByDescending(x => x.DateCreated);
break;
case PostOrderBy.Votes:
results = results.OrderByDescending(x => x.VoteCount).ThenBy(x => x.DateCreated);
break;
default:
results = results.OrderBy(x => x.DateCreated);
break;
}
// sort the paging out
var posts = results.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
PopulateMembers(posts);
// Return a paged list
return new PagedList<Post>(posts, pageIndex, pageSize, total);
}
/// <summary>
/// 获取Post预览图
/// </summary>
/// <param name="customSQL">自定义SQL</param>
/// <param name="inputTags">未经处理的标签</param>
/// <param name="ratingArray">分级</param>
/// <param name="preferArray">偏好</param>
/// <param name="column">按此列排序</param>
/// <param name="orderMode">排序模式</param>
/// <param name="siteArray">图站</param>
/// <returns></returns>
public static List <PostPreviewInfo> GetPostPreviewInfo(string customSQL, string inputTags, Rating[] ratingArray, Prefer[] preferArray, PostOrderBy column, OrderMode orderMode, Site[] siteArray)
{
//多线程查询结果集
List <PostPreviewInfo> result = new List <PostPreviewInfo>();
//将参数打包
Dictionary <string, object>[] paramDicArray = new Dictionary <string, object> [siteArray.Length];
//根据查询图站数量创建线程
Task <List <PostPreviewInfo> >[] tasks = new Task <List <PostPreviewInfo> > [siteArray.Length];
for (int x = 0; x < siteArray.Length; x++)
{
paramDicArray[x] = new Dictionary <string, object>();
paramDicArray[x].Add("customSQL", customSQL);
paramDicArray[x].Add("inputTags", inputTags);
paramDicArray[x].Add("ratingArray", ratingArray);
paramDicArray[x].Add("column", column);
paramDicArray[x].Add("site", siteArray[x]);
tasks[x] = new Task <List <PostPreviewInfo> >(p =>
{
Dictionary <string, object> pDic = (Dictionary <string, object>)p;
return(GetOneSitePostPreview((string)pDic["customSQL"], (string)pDic["inputTags"], (Site)pDic["site"], (PostOrderBy)pDic["column"], (Rating[])pDic["ratingArray"]));
}, paramDicArray[x]);
}
//开启多线程查询
foreach (var t in tasks)
{
t.Start();
}
Task.WaitAll(tasks);
//合并多线程结果集
foreach (var t in tasks)
{
result.AddRange(t.Result);
}
IEnumerable <PostPreviewInfo> temp = result;
//偏好
List <PostPreviewInfo> preferList = Preference.GetPostPrefer(preferArray);
if (preferList == null)
{
temp = result;
}
else
{
//差集
if (preferArray.Contains(Prefer.Normal))
{
temp = result.Except(preferList);
}
//交集
else
{
temp = result.Intersect(preferList);
}
}
//去除重复
if (siteArray.Length > 1)
{
//分数相加
if (column == PostOrderBy.score)
{
temp = temp.GroupBy(a => a.file_md5).Select(b => new PostPreviewInfo()
{
file_md5 = b.First().file_md5, order_column = b.Sum(c => Convert.ToInt32(c.order_column))
});
}
//发布日期选最早
else if (column == PostOrderBy.submitted_on)
{
temp = temp.GroupBy(a => a.file_md5).Select(b => b.Min());
}
else
{
temp = temp.GroupBy(a => a.file_md5).Select(b => b.First());
}
}
//排序
if (orderMode == OrderMode.ASC)
{
temp = temp.OrderBy(c => c.order_column);
}
else
{
temp = temp.OrderByDescending(c => c.order_column);
}
return(temp.ToList());
}
/// <summary>
/// 获取单个图站Post预览信息
/// </summary>
/// <param name="customSQL">自定义SQL</param>
/// <param name="inputTags">未经处理的标签</param>
/// <param name="column">按此列排序</param>
/// <param name="site">图站</param>
/// <param name="ratingArray">分级</param>
/// <returns></returns>
private static List <PostPreviewInfo> GetOneSitePostPreview(string customSQL, string inputTags, Site site, PostOrderBy column, Rating[] ratingArray)
{
string treatedTags = Tag.TreatInputTags(inputTags);
DataTable dt = DAL.Post.SelectPostPreviewList(customSQL, treatedTags, site, column, ratingArray);
return(PostPreviewInfo.Convert(dt));
}
/// <summary>
/// Returns a paged list of posts by a topic id
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="amountToTake"></param>
/// <param name="topicId"></param>
/// <param name="order"></param>
/// <returns></returns>
public PagedList <Post> GetPagedPostsByTopic(int pageIndex, int pageSize, int amountToTake, Guid topicId, PostOrderBy order)
{
return(_postRepository.GetPagedPostsByTopic(pageIndex, pageSize, amountToTake, topicId, order));
}
/// <summary>
/// Returns a paged list of posts by a topic id
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="amountToTake"></param>
/// <param name="topicId"></param>
/// <param name="order"></param>
/// <returns></returns>
public PagedList<Post> GetPagedPostsByTopic(int pageIndex, int pageSize, int amountToTake, Guid topicId, PostOrderBy order)
{
return _postRepository.GetPagedPostsByTopic(pageIndex, pageSize, amountToTake, topicId, order);
}
/// <summary>
/// 获取post预览列表
/// </summary>
/// <param name="customSQL">自定义SQL</param>
/// <param name="treatedTags">经处理过的标签</param>
/// <param name="site">图站</param>
/// <param name="column">按此列排序</param>
/// <param name="ratingArray">分级</param>
/// <returns></returns>
public static DataTable SelectPostPreviewList(string customSQL, string treatedTags, Site site, PostOrderBy column, Rating[] ratingArray)
{
string selectColumns = string.Format("file_md5,{0}", column.ToString());
string ratingIN = string.Join(",", ratingArray.Select(s => string.Format("'{0}'", s.ToString().Substring(0, 1).ToLower())));
string tagsMATCH = string.IsNullOrWhiteSpace(treatedTags) ? string.Empty : string.Format(" AND id IN (SELECT docid FROM posts_fts4 WHERE tags MATCH '{0}')", treatedTags);
if (string.IsNullOrWhiteSpace(customSQL) == false)
{
customSQL = string.Format(" AND ({0})", string.Join(" AND ", customSQL.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)));
}
string commandText = string.Format("SELECT {0} FROM posts WHERE rating IN ({1}){2}{3}", selectColumns, ratingIN, tagsMATCH, customSQL);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText);
try
{
DataTable dt = SQLiteHelper.ExecuteDataTable(command);
return(dt);
}
catch
{
return(null);
}
}
public IList <PostDataTableModel> GetPostDataTable(int page, int count, Order order = Order.Asscending,
PostOrderBy orderBy = PostOrderBy.ById)
{
IQueryable <PostDataTableModel> lstPosts =
_posts.Include(post => post.User)
.Include(post => post.Labels)
.Select(
post =>
new PostDataTableModel
{
CommentStatus = post.CommentStatus,
PostAuthor = post.User.UserName,
Title = post.Title,
labels = post.Labels,
Status = post.Status,
VisitedNumber = post.VisitedNumber,
Id = post.Id
})
.AsQueryable();
if (order == Order.Asscending)
{
switch (orderBy)
{
case PostOrderBy.ById:
lstPosts = lstPosts.OrderBy(post => post.Id).AsQueryable();
break;
case PostOrderBy.ByTitle:
lstPosts = lstPosts.OrderBy(post => post.Title).AsQueryable();
break;
case PostOrderBy.ByPostAuthor:
lstPosts = lstPosts.OrderBy(post => post.PostAuthor).AsQueryable();
break;
case PostOrderBy.ByVisitedNumber:
lstPosts = lstPosts.OrderBy(post => post.VisitedNumber).AsQueryable();
break;
case PostOrderBy.ByLabels:
lstPosts = lstPosts.OrderBy(post => post.labels).AsQueryable();
break;
}
}
else
{
switch (orderBy)
{
case PostOrderBy.ById:
lstPosts = lstPosts.OrderByDescending(post => post.Id).AsQueryable();
break;
case PostOrderBy.ByTitle:
lstPosts = lstPosts.OrderByDescending(post => post.Title).AsQueryable();
break;
case PostOrderBy.ByPostAuthor:
lstPosts = lstPosts.OrderByDescending(post => post.PostAuthor).AsQueryable();
break;
case PostOrderBy.ByVisitedNumber:
lstPosts = lstPosts.OrderByDescending(post => post.VisitedNumber).AsQueryable();
break;
case PostOrderBy.ByLabels:
lstPosts = lstPosts.OrderByDescending(post => post.labels).AsQueryable();
break;
}
}
return(lstPosts.ToList());
}
public IList<PostDataTableModel> GetPostDataTable(int page, int count, Order order = Order.Asscending,
PostOrderBy orderBy = PostOrderBy.ById)
{
IQueryable<PostDataTableModel> lstPosts =
_posts.Include(post => post.User)
.Include(post => post.Labels)
.Select(
post =>
new PostDataTableModel
{
CommentStatus = post.CommentStatus,
PostAuthor = post.User.UserName,
Title = post.Title,
labels = post.Labels,
Status = post.Status,
VisitedNumber = post.VisitedNumber,
Id = post.Id
})
.AsQueryable();
if (order == Order.Asscending)
{
switch (orderBy)
{
case PostOrderBy.ById:
lstPosts = lstPosts.OrderBy(post => post.Id).AsQueryable();
break;
case PostOrderBy.ByTitle:
lstPosts = lstPosts.OrderBy(post => post.Title).AsQueryable();
break;
case PostOrderBy.ByPostAuthor:
lstPosts = lstPosts.OrderBy(post => post.PostAuthor).AsQueryable();
break;
case PostOrderBy.ByVisitedNumber:
lstPosts = lstPosts.OrderBy(post => post.VisitedNumber).AsQueryable();
break;
case PostOrderBy.ByLabels:
lstPosts = lstPosts.OrderBy(post => post.labels).AsQueryable();
break;
}
}
else
{
switch (orderBy)
{
case PostOrderBy.ById:
lstPosts = lstPosts.OrderByDescending(post => post.Id).AsQueryable();
break;
case PostOrderBy.ByTitle:
lstPosts = lstPosts.OrderByDescending(post => post.Title).AsQueryable();
break;
case PostOrderBy.ByPostAuthor:
lstPosts = lstPosts.OrderByDescending(post => post.PostAuthor).AsQueryable();
break;
case PostOrderBy.ByVisitedNumber:
lstPosts = lstPosts.OrderByDescending(post => post.VisitedNumber).AsQueryable();
break;
case PostOrderBy.ByLabels:
lstPosts = lstPosts.OrderByDescending(post => post.labels).AsQueryable();
break;
}
}
return lstPosts.ToList();
}
/// <summary>
/// Returns a paged list of posts by a topic id
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="amountToTake"></param>
/// <param name="topicId"></param>
/// <param name="order"></param>
/// <returns></returns>
public PagedList<Post> GetPagedPostsByTopic(int pageIndex, int pageSize, int amountToTake, Guid topicId, PostOrderBy order)
{
var cacheKey = string.Concat(CacheKeys.Post.StartsWith, "GetPagedPostsByTopic-", pageIndex, "-", pageSize, "-", amountToTake, "-",topicId, "-", order);
return _cacheService.CachePerRequest(cacheKey, () =>
{
// We might only want to display the top 100
// but there might not be 100 topics
var total = _context.Post.AsNoTracking().Count(x => x.Topic.Id == topicId && !x.IsTopicStarter && x.Pending != true);
if (amountToTake < total)
{
total = amountToTake;
}
// Get the topics using an efficient
var results = _context.Post
.Include(x => x.User)
.Include(x => x.Topic)
.AsNoTracking()
.Where(x => x.Topic.Id == topicId && !x.IsTopicStarter && x.Pending != true);
// Sort what order the posts are sorted in
switch (order)
{
case PostOrderBy.Newest:
results = results.OrderByDescending(x => x.DateCreated);
break;
case PostOrderBy.Votes:
results = results.OrderByDescending(x => x.VoteCount).ThenBy(x => x.DateCreated);
break;
default:
results = results.OrderBy(x => x.DateCreated);
break;
}
// sort the paging out
var posts = results.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
// Return a paged list
return new PagedList<Post>(posts, pageIndex, pageSize, total);
});
}
