/// <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> /// 分页获取搜索词(仅非人工干预) /// </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> /// 获取匹配的前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, () => { 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> /// 判定搜索词的DisplayOrder可以做哪些调整 /// </summary> /// <param name="id">Id</param> /// <param name="isFirst">在人工干预同类型搜索词中是否处于首位</param> /// <param name="isLast">在人工干预同类型搜索词中是否处于末位</param> /// <param name="decreaseReferenceId">降低DisplayOrder时参照的Id</param> /// <param name="increaseReferenceId">增加DisplayOrder时参照的Id</param> public void JudgeDisplayOrder(long id, out bool isFirst, out bool isLast, out long decreaseReferenceId, out long increaseReferenceId) { CountService countService = new CountService(TenantTypeIds.Instance().Search()); string countTableName = countService.GetTableName_Counts(); int index; isFirst = false; isLast = false; decreaseReferenceId = 0; increaseReferenceId = 0; SearchedTerm searchedTerm = Get(id); if (searchedTerm == null) { return; } IEnumerable <SearchedTerm> isearchedTerms = this.GetManuals(searchedTerm.SearchTypeCode); if (isearchedTerms == null || isearchedTerms.Count() == 0) { return; } List <SearchedTerm> searchedTerms = isearchedTerms.ToList(); searchedTerm = searchedTerms.FirstOrDefault(n => n.Id == id); index = searchedTerms.IndexOf(searchedTerm); if (searchedTerms.Count() == 1) { isFirst = true; isLast = true; } else if (index == 0) { isFirst = true; increaseReferenceId = searchedTerms.ElementAt(index + 1).Id; } else if (index == searchedTerms.Count() - 1) { isLast = true; decreaseReferenceId = searchedTerms.ElementAt(index - 1).Id; } else { increaseReferenceId = searchedTerms.ElementAt(index + 1).Id; decreaseReferenceId = searchedTerms.ElementAt(index - 1).Id; } }
/// <summary> /// 构建用于GetTopInterestedQuestions方法的Sql /// </summary> /// <param name="tenantTypeId">租户类型id</param> /// <param name="sql"></param> /// <param name="whereSql"></param> /// <param name="orderSql"></param> private void BuildSqlForGetTopInterestedQuestions(string tenantTypeId, out Sql sql, out Sql whereSql, out Sql orderSql) { sql = Sql.Builder.Select("spb_AskQuestions.*") .From("spb_AskQuestions"); whereSql = Sql.Builder.Where("spb_AskQuestions.Status<>@0", QuestionStatus.Canceled); if (!string.IsNullOrEmpty(tenantTypeId)) { whereSql.Where("spb_AskQuestions.TenantTypeId=@0", tenantTypeId); } //过滤审核状态 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; } //优先获取待解决问题(时间倒序) orderSql = Sql.Builder.OrderBy("spb_AskQuestions.QuestionId desc"); //其次是热门问题(关注数倒序) CountService countService = new CountService(TenantTypeIds.Instance().AskQuestion()); string countTableName = countService.GetTableName_Counts(); sql.LeftJoin(countTableName).On("spb_AskQuestions.QuestionId = " + countTableName + ".ObjectId"); whereSql.Where(countTableName + ".CountType = @0 or " + countTableName + ".CountType is null", CountTypes.Instance().QuestionFollowerCount()); orderSql.OrderBy(countTableName + ".StatisticsCount desc"); }
/// <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> /// 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> /// 通用的获取SQL的方法 /// </summary> /// <param name="tenantTypeId">租户类型id</param> /// <param name="descriptionKeyword">描述性字段</param> /// <param name="userId">用户id</param> /// <param name="ownerId">拥有者id</param> /// <param name="isEssential">是否精华</param> /// <param name="auditStatus">审核状态</param> /// <param name="tagName">标签名</param> /// <param name="ignoreAuditAndPrivacy">是否忽略审核和隐私状态</param> /// <param name="albumId">相册id</param> /// <param name="sortBy">排序方式</param> /// <returns>SQL</returns> private Sql GetSql_GetPhotos(string tenantTypeId, string descriptionKeyword, long?userId, long?ownerId, bool?isEssential, DateTime?createDateTime , AuditStatus?auditStatus, string tagName, bool?ignoreAuditAndPrivacy, long?albumId, SortBy_Photo sortBy) { Sql sql = Sql.Builder; Sql sql_Where = Sql.Builder; Sql sql_Orderby = Sql.Builder; sql.Select("spb_Photos.*") .From("spb_Photos"); if (!string.IsNullOrEmpty(tenantTypeId)) { sql_Where.Where("spb_Photos.TenantTypeId = @0", tenantTypeId); } if (!string.IsNullOrEmpty(descriptionKeyword)) { sql_Where.Where("spb_Photos.Description like @0", "%" + StringUtility.StripSQLInjection(descriptionKeyword) + "%"); } if (userId.HasValue) { sql_Where.Where("spb_Photos.UserId = @0", userId.Value); } if (ownerId.HasValue) { sql_Where.Where("spb_Photos.OwnerId = @0", ownerId.Value); } if (auditStatus.HasValue) { sql_Where.Where("spb_Photos.AuditStatus = @0", auditStatus.Value); } if (isEssential.HasValue) { sql_Where.Where("spb_Photos.IsEssential = @0", isEssential); } if (createDateTime.HasValue) { sql_Where.Where("spb_Photos.DateCreated > @0", createDateTime); } if (!string.IsNullOrEmpty(tagName)) { sql.InnerJoin("tn_ItemsInTags") .On("spb_Photos.PhotoId = tn_ItemsInTags.ItemId"); sql_Where.Where("tn_ItemsInTags.TagName=@0", tagName) .Where("tn_ItemsInTags.TenantTypeId = @0", TenantTypeIds.Instance().Photo()); } if (ignoreAuditAndPrivacy.HasValue && !ignoreAuditAndPrivacy.Value) { switch (this.PubliclyAuditStatus) { case PubliclyAuditStatus.Again: case PubliclyAuditStatus.Fail: case PubliclyAuditStatus.Pending: case PubliclyAuditStatus.Success: sql_Where.Where("spb_Photos.AuditStatus = @0", this.PubliclyAuditStatus); break; case PubliclyAuditStatus.Pending_GreaterThanOrEqual: case PubliclyAuditStatus.Again_GreaterThanOrEqual: sql_Where.Where("spb_Photos.AuditStatus > @0", this.PubliclyAuditStatus); break; } sql_Where.Where("spb_Photos.PrivacyStatus <> @0", PrivacyStatus.Private); } if (albumId.HasValue) { sql_Where.Where("spb_Photos.AlbumId = @0", albumId); } CountService countService = new CountService(TenantTypeIds.Instance().Photo()); string countTableName = countService.GetTableName_Counts(); switch (sortBy) { case SortBy_Photo.DateCreated_Desc: sql_Orderby.OrderBy("spb_Photos.PhotoId desc"); break; case SortBy_Photo.HitTimes_Desc: sql.LeftJoin(countTableName).On("spb_Photos.PhotoId = " + countTableName + ".ObjectId"); sql_Where.Where(countTableName + ".CountType = @0 or " + countTableName + ".CountType is null", CountTypes.Instance().HitTimes()); sql_Orderby.OrderBy(countTableName + ".StatisticsCount desc"); break; case SortBy_Photo.CommentCount_Desc: sql.LeftJoin(countTableName).On("spb_Photos.PhotoId = " + countTableName + ".ObjectId"); sql_Where.Where(countTableName + ".CountType = @0 or " + countTableName + ".CountType is null", CountTypes.Instance().CommentCount()); sql_Orderby.OrderBy(countTableName + ".StatisticsCount desc"); break; case SortBy_Photo.SupportCount_Desc: sql.LeftJoin("tn_Attitudes").On("spb_Photos.PhotoId = tn_Attitudes.ObjectId"); sql_Where.Where("tn_Attitudes.TenantTypeId = @0", TenantTypeIds.Instance().Photo()); sql_Orderby.OrderBy("tn_Attitudes.SupportCount desc"); break; } sql.Append(sql_Where) .Append(sql_Orderby); return(sql); }
/// <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> /// 根据不同条件分页获取问题列表(用于前台) /// </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> /// 根据帖吧获取主题帖分页集合 /// </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> /// 获取Sql语句 /// </summary> /// <param name="nameKeyword">商品名称关键字</param> /// <param name="categoryId">类别ID</param> /// <param name="isEnabled">是否上架</param> /// <param name="maxPrice">最大单价</param> /// <param name="minPrice">最小单价</param> /// <param name="sortBy">排序依据</param> /// <param name="topNumber">取出所需条数</param> /// <returns></returns> private Sql GetSql_GetPointGifts(string nameKeyword, long?categoryId, bool?isEnabled, int maxPrice, int minPrice, SortBy_PointGift sortBy, int topNumber) { Sql sql = Sql.Builder; Sql sql_Where = Sql.Builder; Sql sql_Orderby = Sql.Builder; sql.Select("spb_PointGifts.*") .From("spb_PointGifts"); if (!string.IsNullOrEmpty(nameKeyword)) { sql_Where.Where("spb_PointGifts.Name like @0", "%" + StringUtility.StripSQLInjection(nameKeyword) + "%"); } if (categoryId.HasValue) { if (categoryId != 0) { IEnumerable <Category> categories = new 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.LeftJoin(string.Format("(select tn_ItemsInCategories.*,tn_Categories.CategoryName from tn_ItemsInCategories left join tn_Categories on tn_ItemsInCategories.CategoryId=tn_Categories.CategoryId where tn_ItemsInCategories.CategoryId in({0})) tn_ItemsInCategories", string.Join(",", categoryIds))) .On("spb_PointGifts.GiftId=tn_ItemsInCategories.ItemId"); sql_Where.Where("tn_ItemsInCategories.CategoryId in (@categoryIds)", new { categoryIds = categoryIds }); } } if (isEnabled.HasValue) { sql_Where.Where("spb_PointGifts.IsEnabled = @0", isEnabled); } if (maxPrice != 0 && minPrice != 0) { sql_Where.Where("spb_PointGifts.Price>= @0 and spb_PointGifts.Price<=@1", minPrice, maxPrice); } if (minPrice != 0 && maxPrice == 0) { sql_Where.Where("spb_PointGifts.Price>= @0", minPrice); } if (minPrice == 0 && maxPrice != 0) { sql_Where.Where("spb_PointGifts.Price>= 0 and spb_PointGifts.Price<=@0", maxPrice); } CountService countService = new CountService(TenantTypeIds.Instance().PointGift()); string countTableName = countService.GetTableName_Counts(); switch (sortBy) { case SortBy_PointGift.DateCreated_Desc: sql_Orderby.OrderBy("spb_PointGifts.LastModified desc"); break; case SortBy_PointGift.HitTimes_Desc: sql.LeftJoin(countTableName).On("spb_PointGifts.GiftId = " + countTableName + ".ObjectId"); sql_Where.Where(countTableName + ".CountType = @0 or " + countTableName + ".CountType is null", CountTypes.Instance().HitTimes()); sql_Orderby.OrderBy(countTableName + ".StatisticsCount desc"); break; case SortBy_PointGift.Price_Asc: sql_Orderby.OrderBy("spb_PointGifts.Price"); break; case SortBy_PointGift.Price_Desc: sql_Orderby.OrderBy("spb_PointGifts.Price desc"); break; case SortBy_PointGift.Sales_Desc: sql_Orderby.OrderBy("spb_PointGifts.ExchangedCount desc"); break; } sql.Append(sql_Where) .Append(sql_Orderby); 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> /// 依据查询条件获取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)); }