/// <summary>
/// 获取前N条最热的搜索词（仅非人工干预）
/// </summary>
/// <param name="topNumber">获取的数据条数</param>
/// <param name="searchTypeCode">搜索类型编码</param>
/// <returns>用户最多搜索的前topNumber的搜索词</returns>
public IEnumerable <SearchedTerm> GetTops(int topNumber, string searchTypeCode)
{
CountService countService = new CountService(TenantTypeIds.Instance().Search());
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Search());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().SearchCount());
string countType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().SearchCount(), stageCountDays);
string countTableName = countService.GetTableName_Counts();
return(GetTopEntities(topNumber, CachingExpirationType.ObjectCollection,
() =>
{
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.None));
cacheKey.AppendFormat("SearchTypeCode-{0}::CountType-{1}", searchTypeCode, countType);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("*").From("tn_SearchedTerms");
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, countType))
.On("Id = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
if (!String.IsNullOrEmpty(searchTypeCode))
{
whereSql.Where("SearchTypeCode = @0", searchTypeCode);
}
whereSql.Where("IsAddedByAdministrator = @0", false);
sql.Append(whereSql).Append(orderSql);
return sql;
}));
}
/// <summary>
/// 获取匹配的前N条最热的搜索词（仅非人工干预）
/// </summary>
/// <param name="keyword">要匹配的关键字</param>
/// <param name="topNumber">获取的数据条数</param>
/// <param name="searchTypeCode">搜索类型编码</param>
/// <returns>用户最多搜索的前topNumber的搜索词</returns>
public IEnumerable <SearchedTerm> GetTops(string keyword, int topNumber, string searchTypeCode)
{
CountService countService = new CountService(TenantTypeIds.Instance().Search());
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Search());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().SearchCount());
string countType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().SearchCount(), stageCountDays);
string countTableName = countService.GetTableName_Counts();
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("*").From("tn_SearchedTerms");
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, countType))
.On("Id = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
whereSql.Where("IsAddedByAdministrator = @0", false);
if (!string.IsNullOrEmpty(keyword))
{
whereSql.Where("tn_SearchedTerms.Term like @0", StringUtility.StripSQLInjection(keyword) + "%");
}
if (!String.IsNullOrEmpty(searchTypeCode))
{
whereSql.Where("SearchTypeCode = @0", searchTypeCode);
}
sql.Append(whereSql).Append(orderSql);
var primaryKeys = CreateDAO().FetchTopPrimaryKeys <SearchedTerm>(topNumber, sql);
return(PopulateEntitiesByEntityIds(primaryKeys));
}
/// <summary>
/// 获取匹配的前N条最热的搜索词（仅非人工干预）
/// </summary>
/// <param name="keyword">要匹配的关键字</param>
/// <param name="topNumber">获取的数据条数</param>
/// <param name="searchTypeCode">搜索类型编码</param>
/// <returns>用户最多搜索的前topNumber的搜索词</returns>
public IEnumerable <SearchedTerm> GetTops(string keyword, int topNumber, string searchTypeCode)
{
CountService countService = new CountService(TenantTypeIds.Instance().Search());
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Search());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().SearchCount());
string countType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().SearchCount(), stageCountDays);
string countTableName = countService.GetTableName_Counts();
return(GetTopEntities(topNumber, CachingExpirationType.ObjectCollection,
() =>
{
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.None));
cacheKey.AppendFormat("SearchTypeCode-{0}::CountType-{1}::Keyword-{2}", searchTypeCode, countType, keyword);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder;
sql.Select("*")
.From("tn_SearchedTerms")
.InnerJoin(countTableName)
.On("tn_SearchedTerms.Id = " + countTableName + ".ObjectId")
.Where("tn_SearchedTerms.Term like @0", keyword + "%");
if (!String.IsNullOrEmpty(searchTypeCode))
{
sql.Where("SearchTypeCode = @0", searchTypeCode);
}
sql.Where("IsAddedByAdministrator = @0", 0)
.Where("CountType = @0", countType)
.Where("OwnerId = @0", 0)
.OrderBy("StatisticsCount desc");
return sql;
}));
}
/// <summary>
/// 分页获取搜索词（仅非人工干预）
/// </summary>
/// <param name="searchTypeCode">搜索类型编码</param>
/// <param name="term">搜索词</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">结束日期</param>
/// <param name="isRealtime">是否实时缓存</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页条数</param>
/// <returns>按条件检索的热词</returns>
public PagingDataSet <SearchedTerm> Gets(string searchTypeCode, string term, DateTime?startDate, DateTime?endDate, bool isRealtime, int pageSize, int pageIndex)
{
CountService countService = new CountService(TenantTypeIds.Instance().Search());
string countTableName = countService.GetTableName_Counts();
string countType = CountTypes.Instance().SearchCount();
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Search());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().SearchCount());
return(GetPagingEntities(pageSize, pageIndex, CachingExpirationType.ObjectCollection,
() =>
{
//done:zhangp,by mazq cacheKey应该与查询条件关联
StringBuilder cacheKey;
if (isRealtime)
{
cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.GlobalVersion));
}
else
{
cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "SearchTypeCode", searchTypeCode));
}
cacheKey.AppendFormat("SearchTypeCode-{0}::Term-{1}::StartData-{2}::EndData-{3}", searchTypeCode, term, startDate, endDate);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder;
sql.Select("*")
.From("tn_SearchedTerms")
.InnerJoin(countTableName)
.On("tn_SearchedTerms.Id = " + countTableName + ".ObjectId")
.Where("IsAddedByAdministrator = @0", 0)
.Where("CountType = @0", countType)
.Where("OwnerId = @0", 0);
if (!String.IsNullOrEmpty(searchTypeCode))
{
sql.Where("SearchTypeCode = @0", searchTypeCode);
}
if (!String.IsNullOrEmpty(term))
{
sql.Where("Term like @0", term + "%");
}
if (startDate.HasValue)
{
sql.Where("DateCreated >= @0", startDate);
}
if (endDate.HasValue)
{
sql.Where("DateCreated <= @0", endDate);
}
sql.OrderBy("StatisticsCount desc");
return sql;
}
));
}
/// <summary>
/// 分页获取搜索词（仅非人工干预）
/// </summary>
/// <param name="searchTypeCode">搜索类型编码</param>
/// <param name="term">搜索词</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">结束日期</param>
/// <param name="isRealtime">是否实时缓存</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页条数</param>
/// <returns>按条件检索的热词</returns>
public PagingDataSet <SearchedTerm> Gets(string searchTypeCode, string term, DateTime?startDate, DateTime?endDate, bool isRealtime, int pageSize, int pageIndex)
{
CountService countService = new CountService(TenantTypeIds.Instance().Search());
string countTableName = countService.GetTableName_Counts();
string countType = CountTypes.Instance().SearchCount();
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Search());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().SearchCount());
return(GetPagingEntities(pageSize, pageIndex, CachingExpirationType.ObjectCollection,
() =>
{
StringBuilder cacheKey;
if (isRealtime)
{
cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.GlobalVersion));
}
else
{
cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "SearchTypeCode", searchTypeCode));
}
cacheKey.AppendFormat("SearchTypeCode-{0}::Term-{1}::StartData-{2}::EndData-{3}", searchTypeCode, term, startDate, endDate);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("*").From("tn_SearchedTerms");
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, countType))
.On("Id = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
whereSql.Where("IsAddedByAdministrator = @0", false);
if (!String.IsNullOrEmpty(searchTypeCode))
{
whereSql.Where("SearchTypeCode = @0", searchTypeCode);
}
if (!String.IsNullOrEmpty(term))
{
whereSql.Where("Term like @0", "%" + StringUtility.StripSQLInjection(term) + "%");
}
if (startDate.HasValue)
{
whereSql.Where("DateCreated >= @0", startDate);
}
if (endDate.HasValue)
{
whereSql.Where("DateCreated <= @0", endDate);
}
sql.Append(whereSql).Append(orderSql);
return sql;
}
));
}
/// <summary>
/// Gets和GetTops的sql语句
/// </summary>
private Sql Getsqls(string areaCode, long?categoryId, string keyword, SortBy_Group sortBy)
{
Sql sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("spb_Groups.*").From("spb_Groups");
if (categoryId != null && categoryId.Value > 0)
{
CategoryService categoryService = new CategoryService();
IEnumerable <Category> categories = categoryService.GetDescendants(categoryId.Value);
List <long> categoryIds = new List <long> {
categoryId.Value
};
if (categories != null && categories.Count() > 0)
{
categoryIds.AddRange(categories.Select(n => n.CategoryId));
}
sql.InnerJoin("tn_ItemsInCategories")
.On("spb_Groups.GroupId = tn_ItemsInCategories.ItemId");
whereSql.Where("tn_ItemsInCategories.CategoryId in(@categoryIds)", new { categoryIds = categoryIds });
}
if (!string.IsNullOrEmpty(areaCode))
{
if (areaCode.Equals("A1560000", StringComparison.CurrentCultureIgnoreCase))
{
//已修改
whereSql.Where("AreaCode like '1%' or AreaCode like '2%' or AreaCode like '3%' or AreaCode like '4%' or AreaCode like '5%' or AreaCode like '6%' or AreaCode like '7%' or AreaCode like '8%' or AreaCode like '9%' ");
}
else
{
areaCode = areaCode.TrimEnd('0');
if (areaCode.Length % 2 == 1)
{
areaCode = areaCode + "0";
}
whereSql.Where("AreaCode like @0 ", StringUtility.StripSQLInjection(areaCode) + "%");
}
}
if (!string.IsNullOrEmpty(keyword))
{
whereSql.Where("GroupName like @0", StringUtility.StripSQLInjection(keyword) + "%");
}
whereSql.Where("spb_Groups.IsPublic = 1");
//已修改
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
whereSql.Where("AuditStatus=@0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
whereSql.Where("AuditStatus>@0", this.PubliclyAuditStatus);
break;
default:
break;
}
CountService countService = new CountService(TenantTypeIds.Instance().Group());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_Group.DateCreated_Desc:
orderSql.OrderBy("DateCreated desc");
break;
case SortBy_Group.GrowthValue_Desc:
orderSql.OrderBy("GrowthValue desc");
break;
case SortBy_Group.MemberCount_Desc:
orderSql.OrderBy("MemberCount desc");
break;
case SortBy_Group.HitTimes:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().HitTimes()))
.On("GroupId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_Group.StageHitTimes:
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Group());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("GroupId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("DateCreated desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return(sql);
}
/// <summary>
/// 根据排序条件分页显示用户
/// </summary>
/// <param name="sortBy">排序条件</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页记录</param>
/// <returns>根据排序条件倒排序分页显示用户</returns>
public PagingDataSet <User> GetPagingUsers(SortBy_User?sortBy, int pageIndex, int pageSize)
{
PagingDataSet <User> users =
GetPagingEntities(pageSize, pageIndex, Tunynet.Caching.CachingExpirationType.ObjectCollection,
() =>
{
StringBuilder cacheKey = new StringBuilder("PagingTopUsers:");
cacheKey.AppendFormat("SortBy-{0}", sortBy);
return(cacheKey.ToString());
},
() =>
{
var sql = PetaPoco.Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
whereSql.Where("IsActivated =1 and IsBanned = 0");
CountService countService = new CountService(TenantTypeIds.Instance().User());
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().User());
string countTableName = countService.GetTableName_Counts();
int stageCountDays;
string stageCountType;
if (sortBy.HasValue)
{
switch (sortBy)
{
case SortBy_User.FollowerCount:
orderSql.OrderBy("FollowerCount desc");
break;
case SortBy_User.Rank:
orderSql.OrderBy("Rank desc");
break;
case SortBy_User.ReputationPoints:
orderSql.OrderBy("ReputationPoints desc");
break;
case SortBy_User.TradePoints:
orderSql.OrderBy("TradePoints desc");
break;
case SortBy_User.DateCreated:
orderSql.OrderBy("UserId desc");
break;
case SortBy_User.PreWeekHitTimes:
stageCountDays = 7;
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("UserId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_User.HitTimes:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().HitTimes()))
.On("UserId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_User.PreWeekReputationPoints:
stageCountDays = 7;
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().ReputationPointsCounts(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("UserId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("UserId desc");
break;
}
}
return(sql.Append(whereSql).Append(orderSql));
});
return(users);
}
/// <summary>
/// 获取前N个用户
/// </summary>
/// <param name="topNumber">获取用户数</param>
/// <param name="sortBy">排序字段</param>
/// <returns></returns>
public IEnumerable <User> GetTopUsers(int topNumber, SortBy_User sortBy)
{
IEnumerable <User> topUsers = null;
topUsers = GetTopEntities(topNumber, CachingExpirationType.ObjectCollection,
() =>
{
//获取缓存
StringBuilder cacheKey = new StringBuilder("TopUsers:");
cacheKey.AppendFormat("SortBy-{0}", (int)sortBy);
return(cacheKey.ToString());
},
() =>
{
var sql = PetaPoco.Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
whereSql.Where("IsActivated =1 and IsBanned = 0");
CountService countService = new CountService(TenantTypeIds.Instance().User());
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().User());
string countTableName = countService.GetTableName_Counts();
int stageCountDays;
string stageCountType;
switch (sortBy)
{
case SortBy_User.FollowerCount:
orderSql.OrderBy("FollowerCount desc");
break;
case SortBy_User.ReputationPoints:
orderSql.OrderBy("ReputationPoints desc");
break;
case SortBy_User.DateCreated:
orderSql.OrderBy("UserId desc");
break;
case SortBy_User.PreWeekHitTimes:
stageCountDays = 7;
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("UserId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_User.HitTimes:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().HitTimes()))
.On("UserId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_User.PreWeekReputationPoints:
stageCountDays = 7;
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().ReputationPointsCounts(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("UserId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("FollowerCount desc");
break;
}
return(sql.Append(whereSql).Append(orderSql));
});
return(topUsers);
}
/// <summary>
/// 获取日志排行分页集合
/// </summary>
/// <remarks>rss订阅也使用方法</remarks>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">所有者id</param>
/// <param name="categoryId">类别Id</param>
/// <param name="tagName">标签名称</param>
/// <param name="isEssential">是否精华</param>
/// <param name="sortBy">帖子排序依据</param>
/// <param name="pageSize">分页大小</param>
/// <param name="pageIndex">页码</param>
/// <returns>日志分页列表</returns>
public PagingDataSet <BlogThread> Gets(string tenantTypeId, long?ownerId, bool ignoreAudit, bool ignorePrivate, long?categoryId, string tagName, bool?isEssential, SortBy_BlogThread sortBy = SortBy_BlogThread.DateCreated_Desc, int pageSize = 20, int pageIndex = 1)
{
return(GetPagingEntities(pageSize, pageIndex, CachingExpirationType.UsualObjectCollection,
() =>
{
StringBuilder cacheKey = new StringBuilder();
if (ownerId.HasValue)
{
cacheKey.Append(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "OwnerId", ownerId.Value));
}
cacheKey.AppendFormat("GetBlogs::TenantTypeId-{0}:CategoryId-{1}:TagName-{2}:IsEssential-{3}:SortBy-{4}", tenantTypeId, categoryId, tagName, isEssential, sortBy);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder.Select("spb_BlogThreads.*").From("spb_BlogThreads");
var whereSql = Sql.Builder.Where("spb_BlogThreads.IsDraft=0");
var orderSql = Sql.Builder;
if (!string.IsNullOrEmpty(tenantTypeId))
{
whereSql.Where("spb_BlogThreads.TenantTypeId=@0", tenantTypeId);
}
if (ownerId.HasValue)
{
whereSql.Where("spb_BlogThreads.OwnerId=@0", ownerId.Value);
}
if (!ignoreAudit)
{
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
whereSql.Where("spb_BlogThreads.AuditStatus=@0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
whereSql.Where("spb_BlogThreads.AuditStatus>@0", this.PubliclyAuditStatus);
break;
default:
break;
}
}
if (ignorePrivate)
{
whereSql.Where("spb_BlogThreads.PrivacyStatus<>@0", PrivacyStatus.Private);
}
if (categoryId.HasValue && categoryId > 0)
{
sql.InnerJoin("tn_ItemsInCategories").On("spb_BlogThreads.ThreadId=tn_ItemsInCategories.ItemId");
whereSql.Where("tn_ItemsInCategories.CategoryId=@0", categoryId.Value);
}
if (!string.IsNullOrEmpty(tagName))
{
sql.InnerJoin("tn_ItemsInTags").On("spb_BlogThreads.ThreadId = tn_ItemsInTags.ItemId");
whereSql.Where("tn_ItemsInTags.TenantTypeId = @0", TenantTypeIds.Instance().BlogThread())
.Where("tn_ItemsInTags.TagName=@0", tagName);
}
if (isEssential.HasValue)
{
whereSql.Where("spb_BlogThreads.IsEssential=@0", isEssential.Value);
}
CountService countService = new CountService(TenantTypeIds.Instance().BlogThread());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_BlogThread.DateCreated_Desc:
orderSql.OrderBy("spb_BlogThreads.ThreadId desc");
break;
case SortBy_BlogThread.CommentCount:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().CommentCount()))
.On("ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BlogThread.StageHitTimes:
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().BlogThread());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("spb_BlogThreads.ThreadId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return sql;
}));
}
/// <summary>
/// 获取日志的排行数据
/// </summary>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="topNumber">前多少条</param>
/// <param name="categoryId">类别Id</param>
/// <param name="isEssential">是否精华</param>
/// <param name="sortBy">日志排序依据</param>
/// <returns>日志列表</returns>
public IEnumerable <BlogThread> GetTops(string tenantTypeId, int topNumber, long?categoryId, bool?isEssential, SortBy_BlogThread sortBy)
{
return(GetTopEntities(topNumber, CachingExpirationType.UsualObjectCollection,
() =>
{
StringBuilder cacheKey = new StringBuilder();
cacheKey.AppendFormat("BlogTops::TenantTypeId-{0}:CategoryId-{1}:IsEssential-{2}:SortBy-{3}", tenantTypeId, categoryId, isEssential, sortBy);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder.Select("spb_BlogThreads.*")
.From("spb_BlogThreads");
var whereSql = Sql.Builder.Where("spb_BlogThreads.PrivacyStatus<>@0", PrivacyStatus.Private)
.Where("spb_BlogThreads.IsDraft=0");
if (!string.IsNullOrEmpty(tenantTypeId))
{
whereSql.Where("spb_BlogThreads.TenantTypeId=@0", tenantTypeId);
}
if (categoryId.HasValue && categoryId > 0)
{
sql.InnerJoin("tn_ItemsInCategories").On("spb_BlogThreads.ThreadId=tn_ItemsInCategories.ItemId");
whereSql.Where("tn_ItemsInCategories.CategoryId=@0", categoryId.Value);
}
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
whereSql.Where("spb_BlogThreads.AuditStatus=@0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
whereSql.Where("spb_BlogThreads.AuditStatus>@0", this.PubliclyAuditStatus);
break;
default:
break;
}
if (isEssential.HasValue)
{
whereSql.Where("spb_BlogThreads.IsEssential=@0", isEssential);
}
var orderSql = Sql.Builder;
CountService countService = new CountService(TenantTypeIds.Instance().BlogThread());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_BlogThread.DateCreated_Desc:
orderSql.OrderBy("spb_BlogThreads.ThreadId desc");
break;
case SortBy_BlogThread.CommentCount:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().CommentCount()))
.On("ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BlogThread.StageHitTimes:
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().BlogThread());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("spb_BlogThreads.ThreadId desc");
break;
}
return sql.Append(whereSql).Append(orderSql);
}));
}
/// <summary>
/// 获取前topNumber条ContentItem
/// </summary>
public IEnumerable <ContentItem> GetTops(int topNumber, int?contentFolderId, ContentItemSortBy sortBy)
{
return(GetTopEntities(topNumber, CachingExpirationType.UsualObjectCollection,
() =>
{
StringBuilder cacheKey;
if (contentFolderId.HasValue)
{
cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "ContentFolderId", contentFolderId));
}
else
{
cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.GlobalVersion));
}
cacheKey.AppendFormat("ContentFolderId-{0}:SortBy-{1}", contentFolderId.ToString(), (int)sortBy);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder.Select("spb_cms_ContentItems.*").From("spb_cms_ContentItems");
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
if (contentFolderId.HasValue && contentFolderId.Value > 0)
{
ContentFolderService contentFolderService = new ContentFolderService();
IEnumerable <ContentFolder> contentFolders = contentFolderService.GetDescendants(contentFolderId.Value);
IEnumerable <int> descendantFolderIds = contentFolders == null ? new List <int>() : contentFolders.Select(f => f.ContentFolderId);
List <int> folderIds = new List <int>(descendantFolderIds);
folderIds.Add(contentFolderId.Value);
whereSql.Where("spb_cms_ContentItems.ContentFolderId in (@ContentFolderIds)", new { ContentFolderIds = folderIds });
}
switch (PubliclyAuditStatus)
{
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Success:
whereSql.Where("spb_cms_ContentItems.AuditStatus=@0", (int)PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
whereSql.Where("spb_cms_ContentItems.AuditStatus>@0", (int)PubliclyAuditStatus);
break;
default:
break;
}
CountService countService = new CountService(TenantTypeIds.Instance().ContentItem());
string countTableName = countService.GetTableName_Counts();
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().ContentItem());
int stageCountDays = 0;
string stageCountType = string.Empty;
switch (sortBy)
{
case ContentItemSortBy.ReleaseDate_Desc:
orderSql.OrderBy("spb_cms_ContentItems.IsGlobalSticky desc");
if (contentFolderId.HasValue && contentFolderId.Value > 0)
{
orderSql.OrderBy("spb_cms_ContentItems.IsFolderSticky desc");
}
orderSql.OrderBy("spb_cms_ContentItems.ReleaseDate desc");
break;
case ContentItemSortBy.HitTimes:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().HitTimes()))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.StageHitTimes:
stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.CommentCount:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().CommentCount()))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.StageCommentCount:
stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().CommentCount());
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().CommentCount(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.DisplayOrder:
orderSql.OrderBy("spb_cms_ContentItems.DisplayOrder, spb_cms_ContentItems.ContentItemId desc");
break;
default:
orderSql.OrderBy("spb_cms_ContentItems.ReleaseDate desc");
break;
}
whereSql.Where("spb_cms_ContentItems.ReleaseDate < @0", DateTime.UtcNow);
sql.Append(whereSql).Append(orderSql);
return sql;
}));
}
/// <summary>
/// 获取类别下帖吧列表的SQL块
/// </summary>
/// <param name="tenantTypeId"></param>
/// <param name="nameKeyword"></param>
/// <param name="categoryId"></param>
/// <param name="sortBy"></param>
/// <returns></returns>
private Sql GetSql_SectionsByCategoryId(string tenantTypeId, string nameKeyword, long?categoryId, SortBy_BarSection sortBy)
{
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("spb_BarSections.*")
.From("spb_BarSections");
whereSql.Where("TenantTypeId = @0", tenantTypeId)
.Where("AuditStatus=@0", AuditStatus.Success)
.Where("IsEnabled=@0", true);
if (!string.IsNullOrEmpty(nameKeyword))
{
whereSql.Where("Name like @0", StringUtility.StripSQLInjection(nameKeyword) + "%");
}
if (categoryId != null && categoryId.Value > 0)
{
CategoryService categoryService = new CategoryService();
IEnumerable <Category> categories = categoryService.GetDescendants(categoryId.Value);
List <long> categoryIds = new List <long> {
categoryId.Value
};
if (categories != null && categories.Count() > 0)
{
categoryIds.AddRange(categories.Select(n => n.CategoryId));
}
sql.InnerJoin("tn_ItemsInCategories")
.On("spb_BarSections.SectionId = tn_ItemsInCategories.ItemId");
whereSql.Where("tn_ItemsInCategories.CategoryId in(@categoryIds)", new { categoryIds = categoryIds });
}
CountService countService = new CountService(TenantTypeIds.Instance().BarSection());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_BarSection.DateCreated_Desc:
orderSql.OrderBy("DisplayOrder,SectionId desc");
break;
case SortBy_BarSection.ThreadCount:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().ThreadCount()))
.On("SectionId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BarSection.ThreadAndPostCount:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().ThreadAndPostCount()))
.On("SectionId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BarSection.StageThreadAndPostCount:
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().BarSection());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().ThreadAndPostCount());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().ThreadAndPostCount(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("SectionId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BarSection.FollowedCount:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().FollowedCount()))
.On("SectionId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("SectionId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return(sql);
}
/// <summary>
/// 根据帖吧获取主题帖分页集合
/// </summary>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="sectionId">帖吧Id</param>
/// <param name="categoryId">帖吧分类Id</param>
/// <param name="isEssential">是否为精华帖</param>
/// <param name="sortBy">帖子排序依据</param>
/// <param name="pageIndex">页码</param>
/// <returns>主题帖列表</returns>
public PagingDataSet <BarThread> Gets(long sectionId, long?categoryId, bool?isEssential, SortBy_BarThread sortBy, int pageIndex)
{
//只获取可对外显示审核状态的主题帖
//缓存周期：对象集合，需要维护即时性
return(GetPagingEntities(pageSize, pageIndex, CachingExpirationType.ObjectCollection, () =>
{
StringBuilder cacheKey = new StringBuilder();
cacheKey.Append(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "SectionId", sectionId));
cacheKey.AppendFormat("BarThreadsOfSectionId::CategoryId-{0}:IsEssential-{1}:SortBy-{2}", categoryId, isEssential, sortBy);
return cacheKey.ToString();
}
, () =>
{
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("spb_BarThreads.*")
.From("spb_BarThreads");
whereSql.Where("SectionId = @0", sectionId);
if (categoryId.HasValue && categoryId.Value > 0)
{
sql.InnerJoin("tn_ItemsInCategories")
.On("ThreadId = tn_ItemsInCategories.ItemId");
whereSql.Where("tn_ItemsInCategories.CategoryId=@0", categoryId.Value);
}
if (isEssential.HasValue)
{
whereSql.Where("IsEssential = @0", isEssential.Value);
}
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
whereSql.Where("AuditStatus=@0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
whereSql.Where("AuditStatus>@0", this.PubliclyAuditStatus);
break;
default:
break;
}
CountService countService = new CountService(TenantTypeIds.Instance().BarThread());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_BarThread.DateCreated_Desc:
orderSql.OrderBy("ThreadId desc");
break;
case SortBy_BarThread.LastModified_Desc:
orderSql.OrderBy("IsSticky desc,LastModified desc");
break;
case SortBy_BarThread.HitTimes:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().HitTimes()))
.On("ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BarThread.StageHitTimes:
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().BarThread());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BarThread.PostCount:
orderSql.OrderBy("PostCount desc");
break;
default:
orderSql.OrderBy("ThreadId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return sql;
}));
}
/// <summary>
/// 获取群组贴吧的排行数据
/// </summary>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="topNumber">前多少条</param>
/// <param name="sortBy">主题帖排序依据</param>
/// <returns></returns>
public IEnumerable <BarThread> GetTopsThreadOfGroup(string tenantTypeId, int topNumber, bool?isEssential, SortBy_BarThread sortBy)
{
//只获取可对外显示审核状态的主题帖
//缓存周期：常用对象集合，不用维护即时性
return(GetTopEntities(topNumber, CachingExpirationType.UsualObjectCollection, () =>
{
StringBuilder cacheKey = new StringBuilder();
cacheKey.AppendFormat("BarThreadRanks::TenantTypeId-{0}:SortBy-{1}:isEssential-{2}:isPublic-{3}", tenantTypeId, sortBy, isEssential, 1);
return cacheKey.ToString();
}
, () =>
{
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("spb_BarThreads.*")
.From("spb_BarThreads")
.InnerJoin("spb_Groups")
.On("spb_BarThreads.SectionId=spb_Groups.GroupId");
whereSql.Where("spb_BarThreads.TenantTypeId = @0", tenantTypeId);
whereSql.Where("spb_Groups.IsPublic=@0", 1);
if (isEssential.HasValue)
{
whereSql.Where("spb_BarThreads.IsEssential = @0", isEssential.Value);
}
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
whereSql.Where("spb_BarThreads.AuditStatus=@0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
whereSql.Where("spb_BarThreads.AuditStatus>@0", this.PubliclyAuditStatus);
break;
default:
break;
}
CountService countService = new CountService(TenantTypeIds.Instance().BarThread());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_BarThread.DateCreated_Desc:
orderSql.OrderBy("spb_BarThreads.ThreadId desc");
break;
case SortBy_BarThread.LastModified_Desc:
orderSql.OrderBy("spb_BarThreads.LastModified desc");
break;
case SortBy_BarThread.HitTimes:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().HitTimes()))
.On("spb_BarThreads.ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BarThread.StageHitTimes:
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().BarThread());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("spb_BarThreads.ThreadId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_BarThread.PostCount:
orderSql.OrderBy("spb_BarThreads.PostCount desc");
break;
default:
orderSql.OrderBy("spb_BarThreads.ThreadId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return sql;
}));
}
/// <summary>
/// 获取前N个标签
/// </summary>
/// <remarks>智能提示时也使用该方法获取数据</remarks>
///<param name="tenantTypeId">租户类型Id</param>
///<param name="topNumber">前N条数据</param>
///<param name="isFeatured">是否为特色标签</param>
///<param name="sortBy">标签排序字段</param>
///<param name="isTagCloud">为true时则不启用缓存</param>
public IEnumerable <T> GetTopTags(string tenantTypeId, int topNumber, bool?isFeatured, SortBy_Tag?sortBy, bool isTagCloud = false)
{
IEnumerable <T> topTags = new List <T>();
if (!isTagCloud)
{
topTags = GetTopEntities(topNumber, CachingExpirationType.ObjectCollection,
() =>
{
//获取缓存
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "TenantTypeId", tenantTypeId));
//cacheKey.AppendFormat("TopNumber-{0}", topNumber);
if (sortBy.HasValue)
{
cacheKey.AppendFormat(":SortBy-{0}", (int)sortBy);
}
if (isFeatured.HasValue)
{
cacheKey.AppendFormat(":IsFeatured-{0}", isFeatured);
}
return(cacheKey.ToString());
},
() =>
{
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("tn_Tags.*")
.From("tn_Tags");
if (!string.IsNullOrEmpty(tenantTypeId))
{
whereSql.Where("TenantTypeId = @0", tenantTypeId);
}
if (isFeatured.HasValue)
{
whereSql.Where("IsFeatured = @0", isFeatured);
}
PubliclyAuditStatus publiclyAuditStatus = new AuditService().GetPubliclyAuditStatus(0);
whereSql.Where("AuditStatus >= @0", publiclyAuditStatus);
CountService countService = new CountService(TenantTypeIds.Instance().Tag());
string countTableName = countService.GetTableName_Counts();
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Tag());
switch (sortBy)
{
case SortBy_Tag.OwnerCountDesc:
orderSql.OrderBy("OwnerCount desc");
break;
case SortBy_Tag.ItemCountDesc:
orderSql.OrderBy("ItemCount desc");
break;
case SortBy_Tag.PreDayItemCountDesc:
string preDayCountType = stageCountTypeManager.GetStageCountTypes(CountTypes.Instance().ItemCounts()).Min();
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, preDayCountType))
.On("TagId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_Tag.PreWeekItemCountDesc:
string preWeekCountType = stageCountTypeManager.GetStageCountTypes(CountTypes.Instance().ItemCounts()).Max();
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, preWeekCountType))
.On("TagId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("TagId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return(sql);
});
}
else
{
var sql = Sql.Builder;
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
sql.Select("tn_Tags.*")
.From("tn_Tags");
if (!string.IsNullOrEmpty(tenantTypeId))
{
whereSql.Where("TenantTypeId = @0", tenantTypeId);
}
if (isFeatured.HasValue)
{
whereSql.Where("IsFeatured = @0", isFeatured);
}
PubliclyAuditStatus publiclyAuditStatus = new AuditService().GetPubliclyAuditStatus(0);
whereSql.Where("AuditStatus >= @0", publiclyAuditStatus);
CountService countService = new CountService(TenantTypeIds.Instance().Tag());
string countTableName = countService.GetTableName_Counts();
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().Tag());
switch (sortBy)
{
case SortBy_Tag.OwnerCountDesc:
orderSql.OrderBy("OwnerCount desc");
break;
case SortBy_Tag.ItemCountDesc:
orderSql.OrderBy("ItemCount desc");
break;
case SortBy_Tag.PreDayItemCountDesc:
string preDayCountType = stageCountTypeManager.GetStageCountTypes(CountTypes.Instance().ItemCounts()).Min();
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, preDayCountType))
.On("TagId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_Tag.PreWeekItemCountDesc:
string preWeekCountType = stageCountTypeManager.GetStageCountTypes(CountTypes.Instance().ItemCounts()).Max();
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, preWeekCountType))
.On("TagId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("TagId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
IEnumerable <object> ids = CreateDAO().FetchTopPrimaryKeys <T>(topNumber, sql);
topTags = PopulateEntitiesByEntityIds(ids);
}
return(topTags);
}
/// <summary>
/// 根据不同条件分页获取问题列表（用于前台）
/// </summary>
/// <param name="tenantTypeId">租户类型id</param>
/// <param name="tagName">标签</param>
/// <param name="status">问题状态</param>
/// <param name="isEssential">是否精华</param>
/// <param name="sortBy">排序条件</param>
/// <param name="pageSize">分页大小</param>
/// <param name="pageIndex">页码</param>
/// <returns>问题分页列表</returns>
public Tunynet.PagingDataSet <AskQuestion> GetQuestions(string tenantTypeId, string tagName, QuestionStatus?status, bool?isEssential, SortBy_AskQuestion sortBy, int pageSize, int pageIndex)
{
return(GetPagingEntities(pageSize, pageIndex, CachingExpirationType.UsualObjectCollection,
() =>
{
StringBuilder cacheKey = new StringBuilder();
cacheKey.AppendFormat("GetQuestions::tenantTypeId-{0}:tagName-{1}:status-{2}:isEssential-{3}:sortBy-{4}", tenantTypeId, tagName, status, isEssential, sortBy);
return cacheKey.ToString();
},
() =>
{
var sql = Sql.Builder.Select("spb_AskQuestions.*").From("spb_AskQuestions");
var whereSql = Sql.Builder.Where("spb_AskQuestions.Status<>@0", QuestionStatus.Canceled);
var orderSql = Sql.Builder;
//过滤审核条件
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
whereSql.Where("spb_AskQuestions.AuditStatus=@0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
whereSql.Where("spb_AskQuestions.AuditStatus>@0", this.PubliclyAuditStatus);
break;
default:
break;
}
if (!string.IsNullOrEmpty(tenantTypeId))
{
whereSql.Where("spb_AskQuestions.TenantTypeId=@0", tenantTypeId);
}
if (status.HasValue)
{
whereSql.Where("spb_AskQuestions.Status=@0", status.Value);
}
if (isEssential.HasValue)
{
whereSql.Where("spb_AskQuestions.IsEssential=@0", isEssential.Value);
}
if (!string.IsNullOrEmpty(tagName))
{
sql.InnerJoin("tn_ItemsInTags").On("spb_AskQuestions.QuestionId = tn_ItemsInTags.ItemId");
whereSql.Where("tn_ItemsInTags.TenantTypeId = @0", TenantTypeIds.Instance().AskQuestion())
.Where("tn_ItemsInTags.TagName=@0", tagName);
}
switch (sortBy)
{
case SortBy_AskQuestion.AnswerCount:
orderSql.OrderBy("spb_AskQuestions.AnswerCount desc");
break;
case SortBy_AskQuestion.StageHitTimes:
CountService countService = new CountService(TenantTypeIds.Instance().AskQuestion());
string countTableName = countService.GetTableName_Counts();
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().AskQuestion());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("QuestionId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case SortBy_AskQuestion.Reward:
whereSql.Where("spb_AskQuestions.Reward > 0");
orderSql.OrderBy("spb_AskQuestions.Reward desc");
break;
default:
orderSql.OrderBy("spb_AskQuestions.QuestionId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return sql;
}));
}
/// <summary>
/// 依据查询条件获取ContentItem列表
/// </summary>
public PagingDataSet <ContentItem> GetContentItems(bool enableCaching, ContentItemQuery query, int pageSize, int pageIndex)
{
var sql = Sql.Builder.Select("spb_cms_ContentItems.*").From("spb_cms_ContentItems");
var whereSql = Sql.Builder;
var orderSql = Sql.Builder;
if (query.ContentFolderId.HasValue && query.ContentFolderId.Value > 0)
{
if (query.IncludeFolderDescendants.HasValue && query.IncludeFolderDescendants.Value)
{
ContentFolderService contentFolderService = new ContentFolderService();
IEnumerable <ContentFolder> contentFolders = contentFolderService.GetDescendants(query.ContentFolderId.Value);
IEnumerable <int> descendantFolderIds = contentFolders == null ? new List <int>() : contentFolders.Select(f => f.ContentFolderId);
List <int> folderIds = new List <int>(descendantFolderIds);
folderIds.Add(query.ContentFolderId.Value);
whereSql.Where("spb_cms_ContentItems.ContentFolderId in (@ContentFolderIds)", new { ContentFolderIds = folderIds });
}
else
{
whereSql.Where("spb_cms_ContentItems.ContentFolderId=@0", query.ContentFolderId.Value);
}
}
else if (query.ModeratorUserId.HasValue && query.ModeratorUserId.Value > 0)
{
ContentFolderService contentFolderService = new ContentFolderService();
ContentFolderModeratorService contentFolderModeratorService = new ContentFolderModeratorService();
IEnumerable <int> moderatedFolderIds = contentFolderModeratorService.GetModeratedFolderIds(query.ModeratorUserId.Value);
List <int> folderIds = new List <int>(moderatedFolderIds);
if (query.IncludeFolderDescendants.HasValue && query.IncludeFolderDescendants.Value)
{
foreach (var folderId in moderatedFolderIds)
{
IEnumerable <ContentFolder> contentFolders = contentFolderService.GetDescendants(folderId);
IEnumerable <int> descendantFolderIds = contentFolders == null ? new List <int>() : contentFolders.Select(f => f.ContentFolderId);
folderIds.AddRange(descendantFolderIds);
}
}
if (folderIds.Count > 0)
{
whereSql.Where("spb_cms_ContentItems.ContentFolderId in (@ContentFolderIds)", new { ContentFolderIds = folderIds });
}
}
if (query.ContentTypeId.HasValue && query.ContentTypeId.Value > 0)
{
whereSql.Where("spb_cms_ContentItems.ContentTypeId=@0", query.ContentTypeId.Value);
}
if (query.UserId.HasValue && query.UserId.Value > 0)
{
whereSql.Where("spb_cms_ContentItems.UserId=@0", query.UserId.Value);
}
if (query.IsContributed.HasValue)
{
whereSql.Where("spb_cms_ContentItems.IsContributed=@0", query.IsContributed.Value);
}
if (query.IsEssential.HasValue)
{
whereSql.Where("spb_cms_ContentItems.IsContributed=@0", query.IsEssential.Value);
}
if (query.IsSticky.HasValue)
{
whereSql.Where("spb_cms_ContentItems.IsGlobalSticky=@0", query.IsSticky.Value);
}
query.SubjectKeyword = StringUtility.StripSQLInjection(query.SubjectKeyword);
if (!string.IsNullOrWhiteSpace(query.SubjectKeyword))
{
whereSql.Where("spb_cms_ContentItems.Title like @0", "%" + query.SubjectKeyword + "%");
}
query.TagNameKeyword = StringUtility.StripSQLInjection(query.TagNameKeyword);
if (!string.IsNullOrWhiteSpace(query.TagName) || !string.IsNullOrWhiteSpace(query.TagNameKeyword))
{
sql.InnerJoin("tn_ItemsInTags").On("spb_cms_ContentItems.ContentItemId = tn_ItemsInTags.ItemId");
if (!string.IsNullOrWhiteSpace(query.TagName))
{
whereSql.Where("tn_ItemsInTags.TenantTypeId = @0", TenantTypeIds.Instance().ContentItem())
.Where("tn_ItemsInTags.TagName = @0", query.TagName);
}
else if (!string.IsNullOrWhiteSpace(query.TagNameKeyword))
{
whereSql.Where("tn_ItemsInTags.TenantTypeId = @0", TenantTypeIds.Instance().ContentItem())
.Where("tn_ItemsInTags.TagName like @0", "%" + query.TagNameKeyword + "%");
}
}
if (query.PubliclyAuditStatus.HasValue)
{
switch (query.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Success:
whereSql.Where("spb_cms_ContentItems.AuditStatus=@0", (int)query.PubliclyAuditStatus.Value);
break;
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
whereSql.Where("spb_cms_ContentItems.AuditStatus>@0", (int)query.PubliclyAuditStatus.Value);
break;
default:
break;
}
}
if (query.MinDate != null)
{
whereSql.Where("spb_cms_ContentItems.ReleaseDate >= @0", query.MinDate);
}
DateTime maxDate = DateTime.UtcNow;
if (query.MaxDate != null)
{
maxDate = query.MaxDate.Value.AddDays(1);
}
whereSql.Where("spb_cms_ContentItems.ReleaseDate < @0", maxDate);
CountService countService = new CountService(TenantTypeIds.Instance().ContentItem());
string countTableName = countService.GetTableName_Counts();
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().ContentItem());
int stageCountDays = 0;
string stageCountType = string.Empty;
switch (query.SortBy)
{
case ContentItemSortBy.ReleaseDate_Desc:
orderSql.OrderBy("spb_cms_ContentItems.IsGlobalSticky desc");
if (query.ContentFolderId.HasValue && query.ContentFolderId.Value > 0)
{
orderSql.OrderBy("spb_cms_ContentItems.IsFolderSticky desc");
}
orderSql.OrderBy("spb_cms_ContentItems.ReleaseDate desc");
break;
case ContentItemSortBy.HitTimes:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().HitTimes()))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.StageHitTimes:
stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.CommentCount:
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, CountTypes.Instance().CommentCount()))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.StageCommentCount:
stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().CommentCount());
stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().CommentCount(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("ContentItemId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
case ContentItemSortBy.DisplayOrder:
orderSql.OrderBy("spb_cms_ContentItems.DisplayOrder, spb_cms_ContentItems.ContentItemId desc");
break;
default:
orderSql.OrderBy("spb_cms_ContentItems.ReleaseDate desc");
break;
}
sql.Append(whereSql).Append(orderSql);
PagingDataSet <ContentItem> pds = null;
if (enableCaching && string.IsNullOrEmpty(query.SubjectKeyword) && string.IsNullOrEmpty(query.TagNameKeyword) && query.MinDate == null && query.MaxDate == null)
{
pds = GetPagingEntities(pageSize, pageIndex, CachingExpirationType.ObjectCollection,
() =>
{
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(query));
cacheKey.AppendFormat("contentFolderId-{0}:includeFolderDescendants-{1}:contentTypeId-{2}:userId-{3}:isContributed-{4}:isEssential-{5}:isSticky-{6}:PubliclyAuditStatus-{7}:TagName-{8}:ModeratorUserId-{9}",
query.ContentFolderId.ToString(), query.IncludeFolderDescendants.ToString(), query.ContentTypeId.ToString(), query.UserId.ToString(),
query.IsContributed.ToString(), query.IsEssential.ToString(), query.IsSticky.ToString(), query.PubliclyAuditStatus.ToString(), query.TagName, query.ModeratorUserId);
return(cacheKey.ToString());
},
() =>
{
return(sql);
});
}
else
{
pds = GetPagingEntities(pageSize, pageIndex, sql);
}
return(pds);
}
/// <summary>
/// 获取词条排行分页集合
/// </summary>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ignoreAudit">是否忽略审核状态</param>
/// <param name="ownerId">所有者id</param>
/// <param name="categoryId">类别Id</param>
/// <param name="tagName">标签名称</param>
/// <param name="isEssential">是否精华</param>
/// <param name="sortBy">帖子排序依据</param>
/// <param name="pageSize">分页大小</param>
/// <param name="pageIndex">页码</param>
/// <returns>词条分页列表</returns>
public PagingDataSet <WikiPage> Gets(string keyWord, string tenantTypeId, long?ownerId, bool ignoreAudit, long?categoryId, string tagName, bool?isEssential, SortBy_WikiPage sortBy = SortBy_WikiPage.DateCreated_Desc, int pageSize = 20, int pageIndex = 1)
{
var sql = Sql.Builder.Select("spb_WikiPages.*").From("spb_WikiPages");
var whereSql = Sql.Builder.Where("spb_WikiPages.IsLogicalDelete=0");
var orderSql = Sql.Builder;
if (!string.IsNullOrEmpty(tenantTypeId))
{
whereSql.Where("spb_WikiPages.TenantTypeId=@0", tenantTypeId);
}
if (!string.IsNullOrEmpty(keyWord))
{
whereSql.Where("spb_WikiPages.Title like @0", "%" + keyWord + "%");
}
if (ownerId.HasValue)
{
whereSql.Where("spb_WikiPages.OwnerId=@0", ownerId.Value);
}
if (categoryId.HasValue && categoryId > 0)
{
sql.InnerJoin("tn_ItemsInCategories").On("spb_WikiPages.PageId=tn_ItemsInCategories.ItemId");
whereSql.Where("tn_ItemsInCategories.CategoryId=@0", categoryId.Value);
}
if (!string.IsNullOrEmpty(tagName))
{
whereSql.Where("spb_WikiPages.PageId in ( select ItemId from tn_ItemsInTags where tn_ItemsInTags.TenantTypeId = @0 and tn_ItemsInTags.TagName=@1 )", TenantTypeIds.Instance().WikiPage(), tagName);
}
if (!ignoreAudit)
{
switch (this.PubliclyAuditStatus)
{
case PubliclyAuditStatus.Again:
case PubliclyAuditStatus.Fail:
case PubliclyAuditStatus.Pending:
case PubliclyAuditStatus.Success:
whereSql.Where("spb_WikiPages.AuditStatus=@0", this.PubliclyAuditStatus);
break;
case PubliclyAuditStatus.Again_GreaterThanOrEqual:
case PubliclyAuditStatus.Pending_GreaterThanOrEqual:
whereSql.Where("spb_WikiPages.AuditStatus>@0", this.PubliclyAuditStatus);
break;
default:
break;
}
}
if (isEssential.HasValue)
{
whereSql.Where("spb_WikiPages.IsEssential=@0", isEssential.Value);
}
CountService countService = new CountService(TenantTypeIds.Instance().WikiPage());
string countTableName = countService.GetTableName_Counts();
switch (sortBy)
{
case SortBy_WikiPage.DateCreated_Desc:
orderSql.OrderBy("spb_WikiPages.PageId desc");
break;
case SortBy_WikiPage.StageHitTimes:
StageCountTypeManager stageCountTypeManager = StageCountTypeManager.Instance(TenantTypeIds.Instance().WikiPage());
int stageCountDays = stageCountTypeManager.GetMaxDayCount(CountTypes.Instance().HitTimes());
string stageCountType = stageCountTypeManager.GetStageCountType(CountTypes.Instance().HitTimes(), stageCountDays);
sql.LeftJoin(string.Format("(select * from {0} WHERE ({0}.CountType = '{1}')) c", countTableName, stageCountType))
.On("PageId = c.ObjectId");
orderSql.OrderBy("c.StatisticsCount desc");
break;
default:
orderSql.OrderBy("spb_WikiPages.PageId desc");
break;
}
sql.Append(whereSql).Append(orderSql);
return(GetPagingEntities(pageSize, pageIndex, sql));
}
