/// <summary> /// 获取归档项目 /// </summary> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="ownerId">所有者id</param> /// <param name="ignoreAudit">是否忽略审核状态(作者或管理员查看时忽略审核状态)</param> /// <param name="ignorePrivate">是否忽略私有</param> /// <returns>归档项目id</returns> public IEnumerable <ArchiveItem> GetArchiveItems(string tenantTypeId, long ownerId, bool ignoreAudit, bool ignorePrivate) { //构建cacheKey StringBuilder cacheKey = new StringBuilder(); cacheKey.AppendFormat("BlogArchiveItems::TenantTypeId-{0}:IgnoreAudit-{1}:IgnorePrivate-{2}:OwnerId-{3}", tenantTypeId, ignoreAudit, ignorePrivate, ownerId); //先从缓存里取归档项目列表,如果缓存里没有就去数据库取 IList <ArchiveItem> archiveItems = cacheService.Get <List <ArchiveItem> >(cacheKey.ToString()); if (archiveItems == null) { archiveItems = new List <ArchiveItem>(); Database dao = CreateDAO(); try { dao.OpenSharedConnection(); //取出最早的日志创建日期 var sql_first = Sql.Builder.Select("DateCreated") .From("spb_BlogThreads") .Where("TenantTypeId=@0", tenantTypeId) .Where("OwnerId=@0", ownerId) .Where("IsDraft=0"); if (!ignoreAudit) { switch (this.PubliclyAuditStatus) { case PubliclyAuditStatus.Again: case PubliclyAuditStatus.Fail: case PubliclyAuditStatus.Pending: case PubliclyAuditStatus.Success: sql_first.Where("AuditStatus=@0", this.PubliclyAuditStatus); break; case PubliclyAuditStatus.Again_GreaterThanOrEqual: case PubliclyAuditStatus.Pending_GreaterThanOrEqual: sql_first.Where("AuditStatus>@0", this.PubliclyAuditStatus); break; default: break; } } if (ignorePrivate) { sql_first.Where("PrivacyStatus<>@0", PrivacyStatus.Private); } sql_first.OrderBy("ThreadId ASC"); DateTime firstDate = dao.ExecuteScalar <DateTime?>(sql_first) ?? DateTime.MinValue; if (firstDate == DateTime.MinValue) { return(archiveItems); } int yearFirst = firstDate.ConvertToUserDate().Year; //取出最新的日志创建日期 var sql_latest = Sql.Builder.Select("DateCreated") .From("spb_BlogThreads") .Where("TenantTypeId=@0", tenantTypeId) .Where("OwnerId=@0", ownerId) .Where("IsDraft=0"); if (!ignoreAudit) { switch (this.PubliclyAuditStatus) { case PubliclyAuditStatus.Again: case PubliclyAuditStatus.Fail: case PubliclyAuditStatus.Pending: case PubliclyAuditStatus.Success: sql_latest.Where("AuditStatus=@0", this.PubliclyAuditStatus); break; case PubliclyAuditStatus.Again_GreaterThanOrEqual: case PubliclyAuditStatus.Pending_GreaterThanOrEqual: sql_latest.Where("AuditStatus>@0", this.PubliclyAuditStatus); break; default: break; } } if (ignorePrivate) { sql_latest.Where("PrivacyStatus<>@0", PrivacyStatus.Private); } sql_latest.OrderBy("ThreadId DESC"); DateTime latestDate = dao.ExecuteScalar <DateTime>(sql_latest); int yearLatest = latestDate.ConvertToUserDate().Year; int monthLatest = latestDate.ConvertToUserDate().Month; //当前年份 int yearNow = DateTime.Now.Year; //如果今年有数据 if (yearLatest == yearNow) { for (int month = monthLatest; month >= 1; month--) { DateTime beginDate = new DateTime(yearNow, month, 1).ToUniversalTime(); DateTime endDate = beginDate.AddMonths(1); var sql_count = Sql.Builder.Select("count(*)") .From("spb_BlogThreads") .Where("TenantTypeId=@0", tenantTypeId) .Where("OwnerId=@0", ownerId) .Where("IsDraft=0") .Where("DateCreated >=@0 and DateCreated<@1", beginDate, endDate); if (!ignoreAudit) { switch (this.PubliclyAuditStatus) { case PubliclyAuditStatus.Again: case PubliclyAuditStatus.Fail: case PubliclyAuditStatus.Pending: case PubliclyAuditStatus.Success: sql_count.Where("AuditStatus=@0", this.PubliclyAuditStatus); break; case PubliclyAuditStatus.Again_GreaterThanOrEqual: case PubliclyAuditStatus.Pending_GreaterThanOrEqual: sql_count.Where("AuditStatus>@0", this.PubliclyAuditStatus); break; default: break; } } if (ignorePrivate) { sql_count.Where("PrivacyStatus<>@0", PrivacyStatus.Private); } long count = dao.ExecuteScalar <long>(sql_count); if (count > 0) { ArchiveItem item = new ArchiveItem() { Year = yearNow, Month = month, Count = count }; archiveItems.Add(item); } } } //统计往年数据 for (int year = yearLatest; year >= yearFirst; year--) { if (year == yearNow) { continue; } DateTime beginDate = new DateTime(year, 1, 1).ToUniversalTime(); DateTime endDate = new DateTime(year + 1, 1, 1).ToUniversalTime(); var sql_count = Sql.Builder.Select("count(*)") .From("spb_BlogThreads") .Where("TenantTypeId=@0", tenantTypeId) .Where("OwnerId=@0", ownerId) .Where("IsDraft=0") .Where("DateCreated >=@0 and DateCreated<@1", beginDate, endDate); if (!ignoreAudit) { switch (this.PubliclyAuditStatus) { case PubliclyAuditStatus.Again: case PubliclyAuditStatus.Fail: case PubliclyAuditStatus.Pending: case PubliclyAuditStatus.Success: sql_count.Where("AuditStatus=@0", this.PubliclyAuditStatus); break; case PubliclyAuditStatus.Again_GreaterThanOrEqual: case PubliclyAuditStatus.Pending_GreaterThanOrEqual: sql_count.Where("AuditStatus>@0", this.PubliclyAuditStatus); break; default: break; } } if (ignorePrivate) { sql_count.Where("PrivacyStatus<>@0", PrivacyStatus.Private); } long count = dao.ExecuteScalar <long>(sql_count); if (count > 0) { ArchiveItem item = new ArchiveItem() { Year = year, Count = count }; archiveItems.Add(item); } } } finally { dao.CloseSharedConnection(); } //加入缓存 cacheService.Add(cacheKey.ToString(), archiveItems, CachingExpirationType.UsualObjectCollection); } return(archiveItems); }
/// <summary> /// 获取存档的日志分页集合 /// </summary> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="ownerId">所有者id</param> /// <param name="ignoreAudit">是否忽略审核状态(作者或管理员查看时忽略审核状态)</param> /// <param name="ignorePrivate">是否忽略私有</param> /// <param name="archivePeriod">归档阶段</param> /// <param name="archiveItem">归档日期标识</param> /// <param name="pageSize">分页大小</param> /// <param name="pageIndex">页码</param> /// <returns>日志分页列表</returns> public PagingDataSet <BlogThread> GetsForArchive(string tenantTypeId, long ownerId, bool ignoreAudit, bool ignorePrivate, ArchivePeriod archivePeriod, ArchiveItem archiveItem, int pageSize = 20, int pageIndex = 1) { return(GetPagingEntities(pageSize, pageIndex, CachingExpirationType.ObjectCollection, () => { StringBuilder cacheKey = new StringBuilder(); cacheKey.Append(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "OwnerId", ownerId)); cacheKey.AppendFormat("BlogArchives::TenantTypeId-{0}:ArchivePeriod-{1}:ArchiveItem-{2}:IgnoreAudit-{3}:IgnorePrivate-{4}", tenantTypeId, archivePeriod, archiveItem, ignoreAudit, ignorePrivate); return cacheKey.ToString(); }, () => { var sql = Sql.Builder.Select("spb_BlogThreads.*") .From("spb_BlogThreads") .Where("TenantTypeId=@0", tenantTypeId) .Where("OwnerId=@0", ownerId) .Where("IsDraft=0"); if (!ignoreAudit) { switch (this.PubliclyAuditStatus) { case PubliclyAuditStatus.Again: case PubliclyAuditStatus.Fail: case PubliclyAuditStatus.Pending: case PubliclyAuditStatus.Success: sql.Where("AuditStatus=@0", this.PubliclyAuditStatus); break; case PubliclyAuditStatus.Again_GreaterThanOrEqual: case PubliclyAuditStatus.Pending_GreaterThanOrEqual: sql.Where("AuditStatus>@0", this.PubliclyAuditStatus); break; default: break; } } if (ignorePrivate) { sql.Where("PrivacyStatus<>@0", PrivacyStatus.Private); } DateTime beginDate = new DateTime(1900, 1, 1); DateTime endDate = DateTime.Now; switch (archivePeriod) { case ArchivePeriod.Year: beginDate = new DateTime(archiveItem.Year, 1, 1).ToUniversalTime(); endDate = new DateTime(archiveItem.Year + 1, 1, 1).ToUniversalTime(); break; case ArchivePeriod.Month: beginDate = new DateTime(archiveItem.Year, archiveItem.Month, 1).ToUniversalTime(); endDate = beginDate.AddMonths(1); break; case ArchivePeriod.Day: beginDate = new DateTime(archiveItem.Year, archiveItem.Month, archiveItem.Day).ToUniversalTime(); endDate = new DateTime(archiveItem.Year, archiveItem.Month, archiveItem.Day + 1).ToUniversalTime(); break; default: break; } sql.Where("DateCreated >= @0 and DateCreated < @1", beginDate, endDate).OrderBy("ThreadId desc"); return sql; })); }
/// <summary> /// 获取存档的日志分页集合 /// </summary> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="ownerId">ownerId</param> /// <param name="ignoreAudit">忽略审核状态</param> /// <param name="ignorePrivate">忽略隐私状态为私有的记录</param> /// <param name="archivePeriod">归档阶段</param> /// <param name="archiveItem">归档统计项目</param> /// <param name="pageSize">每页记录数</param> /// <param name="pageIndex">页码</param> /// <returns>日志分页列表</returns> public PagingDataSet <BlogThread> GetsForArchive(string tenantTypeId, long ownerId, bool ignoreAudit, bool ignorePrivate, ArchivePeriod archivePeriod, ArchiveItem archiveItem, int pageSize = 20, int pageIndex = 1) { //只获取可对外显示审核状态的主题帖 //排除草稿 //缓存周期:对象集合,不用维护即时性 return(blogThreadRepository.GetsForArchive(tenantTypeId, ownerId, ignoreAudit, ignorePrivate, archivePeriod, archiveItem, pageSize, pageIndex)); }