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