/// <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));
}