public int GetPageIndexForCommentInParentCommens(long commentId, long parentId, SortBy_Comment sortBy)
{
int pageIndex = 1;
string cacheKey = GetCacheKey_GetChildren(parentId, sortBy);
PagingEntityIdCollection peic = cacheService.Get <PagingEntityIdCollection>(cacheKey);
if (peic == null)
{
peic = CreateDAO().FetchPagingPrimaryKeys <Comment>(PrimaryMaxRecords, PageSize * CacheablePageCount, 1, GetSql_CommentPageIndexInParentComments(parentId, sortBy));
peic.IsContainsMultiplePages = true;
cacheService.Add(cacheKey, peic, CachingExpirationType.ObjectCollection);
}
if (peic != null)
{
IList <long> commentIds = peic.GetTopEntityIds(peic.Count).Cast <long>().ToList();
int commentIndex = commentIds.IndexOf(commentId);
if (commentIndex > 0)
{
pageIndex = commentIndex / ChildPageSize + 1;
}
else
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder
.Select("Count(Id)")
.From("tn_Comments")
.Where("ParentId=@0", parentId);
switch (sortBy)
{
case SortBy_Comment.DateCreated:
sql.Where("Id<@0", commentId);
break;
case SortBy_Comment.DateCreatedDesc:
sql.Where("Id>@0", commentId);
break;
default:
sql.Where("Id<@0", commentId);
break;
}
commentIndex = CreateDAO().FirstOrDefault <int>(sql);
if (commentIndex > 0)
{
pageIndex = commentIndex / ChildPageSize + 1;
}
}
}
return(pageIndex);
}
private PetaPoco.Sql GetSql_CommentPageIndex(string tenantType, long commentedObjectId, SortBy_Comment sortBy)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Where("tenantTypeId=@0", tenantType);
sql.Where("commentedObjectId=@0", commentedObjectId);
sql.Where("parentId=0");
if (sortBy == SortBy_Comment.DateCreated)
{
sql.OrderBy("id");
}
else
{
sql.OrderBy("id desc");
}
return(sql);
}
/// <summary>
/// 从UserQuery构建PetaPoco.Sql的where条件
/// </summary>
/// <param name="userQuery">UserQuery查询条件</param>
/// <param name="sql">PetaPoco.Sql对象</param>
private void buildSqlWhere(UserQuery userQuery, ref PetaPoco.Sql sql)
{
if (sql == null)
{
sql = PetaPoco.Sql.Builder;
}
if (!string.IsNullOrEmpty(userQuery.Keyword))
{
sql.Where("UserName like @0", "%" + StringUtility.StripSQLInjection(userQuery.Keyword) + "%");
}
if (userQuery.RegisterTimeLowerLimit.HasValue)
{
sql.Where("DateCreated >= @0", userQuery.RegisterTimeLowerLimit.Value.ToUniversalTime());
}
if (userQuery.RegisterTimeUpperLimit.HasValue)
{
sql.Where("DateCreated <= @0", userQuery.RegisterTimeUpperLimit.Value.AddDays(1).ToUniversalTime());
}
}
private PetaPoco.Sql GetSql_CommentPageIndexInParentComments(long parentId, SortBy_Comment sortBy)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Where("parentId=@0", parentId);
if (sortBy == SortBy_Comment.DateCreated)
{
sql.OrderBy("id");
}
else
{
sql.OrderBy("id desc");
}
return(sql);
}
/// <summary>
/// 从UserQuery构建PetaPoco.Sql的where条件
/// </summary>
/// <param name="userQuery">UserQuery查询条件</param>
/// <param name="sql">PetaPoco.Sql对象</param>
private void buildSqlWhere(UserQuery userQuery, ref PetaPoco.Sql sql)
{
if (sql == null)
{
sql = PetaPoco.Sql.Builder;
}
if (!string.IsNullOrEmpty(userQuery.AccountEmailFilter))
{
sql.Where("AccountEmail like @0", "%" + StringUtility.StripSQLInjection(userQuery.AccountEmailFilter) + "%");
}
if (userQuery.IsActivated.HasValue)
{
sql.Where("IsActivated = @0", userQuery.IsActivated);
}
if (userQuery.IsBanned.HasValue)
{
sql.Where("IsBanned = @0", userQuery.IsBanned);
}
if (userQuery.IsModerated.HasValue)
{
if (userQuery.IsModerated.Value)
{
sql.Where("IsModerated = @0 or IsForceModerated = @0", userQuery.IsModerated);
}
else
{
sql.Where("IsModerated = @0 and IsForceModerated = @0", userQuery.IsModerated);
}
}
if (!string.IsNullOrEmpty(userQuery.Keyword))
{
sql.Where("UserName like @0 or TrueName like @0 or NickName like @0", "%" + StringUtility.StripSQLInjection(userQuery.Keyword) + "%");
}
if (!string.IsNullOrEmpty(userQuery.RoleName))
{
sql.Where("UserId in (Select UserId from tn_UsersInRoles where RoleName = @0)", userQuery.RoleName);
}
if (userQuery.RegisterTimeLowerLimit.HasValue)
{
sql.Where("DateCreated >= @0", userQuery.RegisterTimeLowerLimit.Value.ToUniversalTime());
}
if (userQuery.RegisterTimeUpperLimit.HasValue)
{
sql.Where("DateCreated <= @0", userQuery.RegisterTimeUpperLimit.Value.AddDays(1).ToUniversalTime());
}
if (userQuery.UserRankLowerLimit.HasValue)
{
sql.Where("Rank >= @0", userQuery.UserRankLowerLimit);
}
if (userQuery.UserRankUpperLimit.HasValue)
{
sql.Where("Rank<=@0", userQuery.UserRankUpperLimit);
}
}
/// <summary>
/// 从MicroblogQuery构建PetaPoco.Sql的where条件
/// </summary>
/// <param name="query">MicroblogQuery查询条件</param>
/// <param name="sql">PetaPoco.Sql对象</param>
private void BuildSqlWhere_MicroblogQuery(MicroblogQuery query, ref PetaPoco.Sql sql)
{
if (sql == null)
{
sql = PetaPoco.Sql.Builder;
}
if (query.UserId.HasValue)
{
sql.Where("UserId = @0", query.UserId);
}
if (!string.IsNullOrEmpty(query.TenantTypeId))
{
sql.Where("TenantTypeId = @0", query.TenantTypeId);
}
if (query.OwnerId.HasValue)
{
sql.Where("OwnerId = @0", query.OwnerId);
}
if (query.isOriginal.HasValue && query.isOriginal.Value)
{
sql.Where("OriginalMicroblogId = 0");
}
else if (query.MediaType.HasValue)
{
switch (query.MediaType)
{
case MediaType.Image:
sql.Where("HasPhoto = 1");
break;
case MediaType.Video:
sql.Where("HasVideo = 1");
break;
case MediaType.Audio:
sql.Where("HasMusic = 1");
break;
}
}
if (!string.IsNullOrEmpty(query.Keyword))
{
sql.Where("Body like @0", "%" + StringUtility.StripSQLInjection(query.Keyword) + "%");
}
if (query.StartDate.HasValue)
{
sql.Where("DateCreated >= @0", query.StartDate);
}
//reply:已修改
if (query.EndDate.HasValue)
{
sql.Where("DateCreated < @0", query.EndDate.Value.AddDays(1));
}
if (query.AuditStatus.HasValue)
{
sql.Where("AuditStatus = @0", (int)query.AuditStatus);
}
sql.OrderBy("MicroblogId desc");
}
