public override ShareCollection GetUserFavorites(int favOwnerID, ShareType? favType, int pageNumber, int pageSize, ref int? totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_SharesView]";
query.Pager.SortField = "[ShareID]";
query.Pager.IsDesc = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.SelectCount = true;
SqlConditionBuilder cb = new SqlConditionBuilder(SqlConditionStart.None);
cb.Append("[UserID] = @UserID");
query.CreateParameter<int>("@UserID", favOwnerID, SqlDbType.Int);
if (favType.HasValue && favType.Value != ShareType.All)
{
cb.Append("[Type] = @Type");
query.CreateParameter<ShareType>("@Type", favType.Value, SqlDbType.TinyInt);
}
cb.Append("[PrivacyType] = 2");
query.Pager.Condition = cb.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
ShareCollection shares = new ShareCollection(reader);
if (reader.NextResult())
{
if (totalCount == null && reader.Read())
{
totalCount = reader.Get<int>(0);
}
shares.TotalRecords = totalCount.GetValueOrDefault();
}
return shares;
}
}
}
public override BlogArticleCollection GetUserBlogArticles(int userID, int? categoryID, int? tagID, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int? totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_BlogArticles]";
query.Pager.SortField = "[ArticleID]";
query.Pager.IsDesc = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.SelectCount = true;
SqlConditionBuilder cb = new SqlConditionBuilder(SqlConditionStart.None);
if (categoryID.HasValue)
{
cb.Append("[CategoryID] = @CategoryID");
query.CreateParameter<int>("@CategoryID", categoryID.Value, SqlDbType.Int);
if (categoryID.Value == 0)
{
cb.Append("[UserID] = @UserID");
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
}
}
else
{
cb.Append("[UserID] = @UserID");
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
}
if (tagID.HasValue)
{
cb.Append("[ArticleID] IN (SELECT [TargetID] FROM [bx_TagRelation] WHERE [Type] = 1 AND [TagID] = @TagID)");
query.CreateParameter<int>("@TagID", tagID.Value, SqlDbType.Int);
}
if (dataAccessLevel == DataAccessLevel.Normal)
{
cb.Append("[PrivacyType] IN (0, 3)");
}
else if (dataAccessLevel == DataAccessLevel.Friend)
{
cb.Append("[PrivacyType] IN (0, 1, 3)");
}
query.Pager.Condition = cb.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
BlogArticleCollection articles = new BlogArticleCollection(reader);
if (reader.NextResult())
{
if (totalCount == null && reader.Read())
{
totalCount = reader.Get<int>(0);
}
articles.TotalRecords = totalCount.GetValueOrDefault();
}
return articles;
}
}
}
/* [StoredProcedure(Name="bx_InsertBanUserLogs",Script= @"
CREATE PROCEDURE {name}
@OperationType varchar(50),
@OperatorName nvarchar(50),
@Cause nvarchar(1000),
@ForumInfos varchar(1000),
@TargetID int,
@TargetName nvarchar(200),
@TargetIP varchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [bx_BanUserLogs](
OperationType,
OperatorName,
Cause,
ForumID,
BeginDate,
EndDate,
TargetID,
TargetName,
TargetIP
) Values(
@OperationType,
@OperatorName,
@Cause,
@ForumID,
@BeginDate,
@EndDate,
@TargetID,
@TargetName,
@TargetIP
);
END
")]
public override void InsertBanUserOperationLog(BanUserOperation banuser)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "bx_InsertBanUserLogs";
query.CommandType = CommandType.StoredProcedure;
query.CreateParameter<string>("@OperationType", banuser.OperationType, SqlDbType.VarChar, 50);
query.CreateParameter<string>("@OperationName", banuser.OperatorName, SqlDbType.NVarChar, 50);
query.CreateParameter<string>("@Cause", banuser.Cause, SqlDbType.NVarChar, 200);
// query.CreateParameter<int>("@ForumID", banuser.ForumID, SqlDbType.Int);
query.CreateParameter<DateTime>("@BeginDate",banuser.BeginDate,SqlDbType.DateTime);
query.CreateParameter<DateTime>("@EndDate", banuser.EndDate, SqlDbType.DateTime);
query.CreateParameter<int>("@TargetID",banuser.UserID,SqlDbType.Int);
query.CreateParameter<string>("@TargetName",banuser.UserName,SqlDbType.NVarChar,50);
query.CreateParameter<string>("@TargetIP",banuser.UserIP,SqlDbType.VarChar,50);
query.ExecuteNonQuery();
}
}
*/
public override OperationLogCollection GetOperationLogsBySearch(int pageNumber, OperationLogFilter filter)
{
using (SqlQuery db = new SqlQuery())
{
db.Pager.TableName = "bx_OperationLogs";
db.Pager.SortField = "LogID";
db.Pager.PageSize = filter.PageSize;
SqlConditionBuilder sb = new SqlConditionBuilder(SqlConditionStart.None);
if (filter.OperatorID != null)
{
sb.Append("OperatorID = @OperatorID");
db.CreateParameter<int>("@OperatorID", filter.OperatorID.Value, SqlDbType.Int);
}
else if (filter.OperatorName != null && filter.OperatorName != string.Empty)
{
sb.Append("OperatorID IN (SELECT UserID FROM bx_Users WHERE Username = @OperatorName)");
db.CreateParameter<string>("@OperatorName", filter.OperatorName, SqlDbType.NVarChar, 50);
}
if (filter.OperatorIP != null && filter.OperatorIP != string.Empty)
{
sb.Append("OperatorIP = @OperatorIP");
db.CreateParameter<string>("@OperatorIP", filter.OperatorIP, SqlDbType.VarChar, 50);
}
if (filter.OperationType != null && filter.OperationType != string.Empty)
{
sb.Append("OperationType = @OperationType");
db.CreateParameter<string>("@OperationType", filter.OperationType, SqlDbType.VarChar, 100);
}
if (filter.TargetID_1 != null)
{
sb.Append("TargetID_1 = @TargetID_1");
db.CreateParameter<int>("@TargetID_1", filter.TargetID_1.Value, SqlDbType.Int);
}
if (filter.TargetID_2 != null)
{
sb.Append("TargetID_2 = @TargetID_2");
db.CreateParameter<int>("@TargetID_2", filter.TargetID_2.Value, SqlDbType.Int);
}
if (filter.TargetID_3 != null)
{
sb.Append("TargetID_3 = @TargetID_3");
db.CreateParameter<int>("@TargetID_3", filter.TargetID_3.Value, SqlDbType.Int);
}
if (filter.BeginDate != null)
{
sb.Append("CreateTime >= @BeginDate");
db.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
sb.Append("CreateTime <= @EndDate");
db.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
db.Pager.Condition = sb.ToString();
db.Pager.PageNumber = pageNumber;
db.Pager.SelectCount = true;
using (XSqlDataReader reader = db.ExecuteReader())
{
OperationLogCollection result = new OperationLogCollection(reader);
reader.NextResult();
reader.Read();
result.TotalRecords = reader.Get<int>(0);
return result;
}
}
}
private string BuilderSearchAttachmentCondition(AttachmentFilter filter, IEnumerable<Guid> excludeRoleIDs, SqlQuery query, bool startWithWhere)
{
SqlConditionBuilder condition;
if (startWithWhere)
condition = new SqlConditionBuilder(SqlConditionStart.Where);
else
condition = new SqlConditionBuilder(SqlConditionStart.None);
condition += (filter.ForumID == null ? "" : ("AND [ForumID] = @ForumID "));
condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
condition += (string.IsNullOrEmpty(filter.FileType) ? "" : ("AND [FileType] = @FileType "));
condition += (string.IsNullOrEmpty(filter.KeyWord) ? "" : ("AND [FileName] LIKE '%'+@keyword+'%' "));
condition += (filter.MinFileSize == null ? "" : ("AND [FileSize] >= @MinFileSize "));
condition += (filter.MaxFileSize == null ? "" : ("AND [FileSize] <= @MaxFileSize "));
condition += (filter.MinTotalDownload == null ? "" : ("AND [TotalDownloads] >= @MinTotalDownload "));
condition += (filter.MaxTotalDownload == null ? "" : ("AND [TotalDownloads] <= @MaxTotalDownload "));
condition += (filter.MinPrice == null ? "" : ("AND [Price] >= @MinPrice "));
condition += (filter.MaxPrice == null ? "" : ("AND [Price] <= @MaxPrice "));
condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
if (string.IsNullOrEmpty(filter.KeyWord) == false)
{
query.CreateParameter<string>("@keyword", filter.KeyWord, SqlDbType.NVarChar, 256);
}
if (string.IsNullOrEmpty(filter.FileType) == false)
{
query.CreateParameter<string>("@FileType", filter.FileType, SqlDbType.VarChar, 10);
}
condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));
if (filter.ForumID != null)
query.CreateParameter<int?>("@ForumID", filter.ForumID, SqlDbType.Int);
if (filter.UserID != null)
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
if (filter.MaxFileSize != null)
{
query.CreateParameter<long>("@MaxFileSize", ConvertUtil.GetFileSize(filter.MaxFileSize.Value, filter.MaxFileSizeUnit), SqlDbType.BigInt);
}
if (filter.MinFileSize != null)
query.CreateParameter<long>("@MinFileSize", ConvertUtil.GetFileSize(filter.MinFileSize.Value, filter.MinFileSizeUnit), SqlDbType.BigInt);
if (filter.MaxTotalDownload != null)
query.CreateParameter<int?>("@MaxTotalDownload", filter.MaxTotalDownload, SqlDbType.Int);
if (filter.MinTotalDownload != null)
query.CreateParameter<int?>("@MinTotalDownload", filter.MinTotalDownload, SqlDbType.Int);
if (filter.MaxPrice != null)
query.CreateParameter<int?>("@MaxPrice", filter.MaxPrice, SqlDbType.Int);
if (filter.MinPrice != null)
query.CreateParameter<int?>("@MinPrice", filter.MinPrice, SqlDbType.Int);
if (filter.BeginDate != null)
query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
if (filter.EndDate != null)
query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
return condition.ToString();
}
public override void DeleteAttachments(int forumID, IEnumerable<int> attachmentIDs, IEnumerable<Guid> excludeRoleIDs, out List<int> threadIDs)
{
using (SqlQuery query = new SqlQuery())
{
SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.None);
if (forumID != 0)
{
condition += " AND ForumID=@ForumID ";
query.CreateParameter<int>("@ForumID", forumID, SqlDbType.Int);
}
condition += " AND [AttachmentID] in(@AttachmentIDs) ";
query.CreateInParameter<int>("@AttachmentIDs", attachmentIDs);
condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));
query.CommandText = @"
DECLARE @Table table(AttachmentID int,FileID varchar(50),ThreadID int);
INSERT INTO @Table SELECT [AttachmentID],[FileID],[ThreadID] FROM [bx_AttacmentsWithForumID] WHERE " + condition.ToString() + @";
DELETE [bx_Attachments] WHERE AttachmentID IN(SELECT AttachmentID FROM @Table);
SELECT DISTINCT ThreadID FROM @Table;
";
threadIDs = new List<int>();
//fileIDs = new List<string>();
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
threadIDs.Add(reader.Get<int>(0));
}
}
}
}
private string BuilderSearchPostCondition(PostFilter filter, IEnumerable<Guid> excludeRoleIDs, SqlQuery query, bool startWithWhere)
{
SqlConditionBuilder condition;
if (startWithWhere)
condition = new SqlConditionBuilder(SqlConditionStart.Where);
else
condition = new SqlConditionBuilder(SqlConditionStart.None);
condition += (filter.ForumID == null ? "" : ("AND [ForumID] = @ForumID "));
condition += "AND PostType <> 1 ";
condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
condition += (filter.PostID == null ? "" : ("AND [PostID] = @PostID "));
condition += (string.IsNullOrEmpty(filter.CreateIP) ? "" : ("AND [IPAddress] = @CreateIP "));
if (filter.IsUnapproved != null && filter.IsUnapproved.Value)
condition += (" AND [SortOrder] > " + SortOrder_Unapproved);
else if (filter.IsUnapproved != null && filter.IsUnapproved.Value == false)
condition += (" AND [ThreadStatus] < " + SortOrder_Unapproved);
condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
if (string.IsNullOrEmpty(filter.KeyWord) == false)
{
if (filter.SearchMode == SearchArticleMethod.Subject)
{
condition += " AND [Subject] LIKE '%'+@keyword+'%' ";
}
else if (filter.SearchMode == SearchArticleMethod.FullText)
{
condition += " AND [Content] LIKE '%'+@keyword+'%' ";
}
else
{
condition += " AND ([Subject] LIKE '%'+@keyword+'%' OR [Content] LIKE '%'+@keyword+'%') ";
}
query.CreateParameter<string>("@keyword", filter.KeyWord, SqlDbType.NVarChar, 256);
}
condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));
if (filter.ForumID != null)
query.CreateParameter<int?>("@ForumID", filter.ForumID, SqlDbType.Int);
if (string.IsNullOrEmpty(filter.CreateIP) == false)
query.CreateParameter<string>("@CreateIP", filter.CreateIP, SqlDbType.NVarChar, 64);
if (filter.PostID != null)
query.CreateParameter<int?>("@PostID", filter.PostID, SqlDbType.Int);
if (filter.UserID != null)
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
if (filter.BeginDate != null)
query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
if (filter.EndDate != null)
query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
return condition.ToString();
}
private string BuilderSearchThreadCondition(TopicFilter filter, IEnumerable<Guid> excludeRoleIDs, SqlQuery query, bool startWithWhere)
{
SqlConditionBuilder condition;
if (startWithWhere)
condition = new SqlConditionBuilder(SqlConditionStart.Where);
else
condition = new SqlConditionBuilder(SqlConditionStart.None);
condition += (filter.ForumID == null ? "" : ("AND [ForumID] = @ForumID "));
condition += (filter.UserID == null ? "" : ("AND [PostUserID] = @UserID "));
condition += (filter.TopicID == null ? "" : ("AND [ThreadID] = @TopicID "));
condition += (string.IsNullOrEmpty(filter.CreateIP) ? "" : ("AND [IPAddress] = @CreateIP "));
if (filter.Status == null)
condition += (filter.IncludeStick == false ? "AND [ThreadStatus] = 1 " : ("AND [ThreadStatus] < 4 "));
else if (filter.Status.Value == ThreadStatus.Recycled)
condition += ("AND [ThreadStatus] = 4 ");
else if (filter.Status.Value == ThreadStatus.UnApproved)
condition += ("AND [ThreadStatus] = 5 ");
condition += (filter.IncludeValued == true ? "" : ("AND [IsValued] = 0 "));
condition += (filter.MinReplyCount == null ? "" : ("AND [TotalReplies] >= @MinReplyCount "));
condition += (filter.MaxReplyCount == null ? "" : ("AND [TotalReplies] <= @MaxReplyCount "));
condition += (filter.MinViewCount == null ? "" : ("AND [TotalViews] >= @MinViewCount "));
condition += (filter.MaxViewCount == null ? "" : ("AND [TotalViews] <= @MaxViewCount "));
condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
if (string.IsNullOrEmpty(filter.KeyWord) == false)
{
if (filter.SearchMode == SearchArticleMethod.Subject)
{
condition += " AND [Subject] LIKE '%'+@keyword+'%' ";
}
else if (filter.SearchMode == SearchArticleMethod.FullText)
{
condition += " AND [Content] LIKE '%'+@keyword+'%' ";
}
else
{
condition += " AND ([Subject] LIKE '%'+@keyword+'%' OR [Content] LIKE '%'+@keyword+'%') ";
}
query.CreateParameter<string>("@keyword", filter.KeyWord, SqlDbType.NVarChar, 256);
}
condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[PostUserID]", excludeRoleIDs, query));
if (filter.ForumID != null)
query.CreateParameter<int?>("@ForumID", filter.ForumID, SqlDbType.Int);
if (string.IsNullOrEmpty(filter.CreateIP) == false)
query.CreateParameter<string>("@CreateIP", filter.CreateIP, SqlDbType.NVarChar, 64);
if (filter.TopicID != null)
query.CreateParameter<int?>("@TopicID", filter.TopicID, SqlDbType.Int);
if (filter.MaxReplyCount != null)
query.CreateParameter<int?>("@MaxReplyCount", filter.MaxReplyCount, SqlDbType.Int);
if (filter.MaxViewCount != null)
query.CreateParameter<int?>("@MaxViewCount", filter.MaxViewCount, SqlDbType.Int);
if (filter.MinReplyCount != null)
query.CreateParameter<int?>("@MinReplyCount", filter.MinReplyCount, SqlDbType.Int);
if (filter.MinViewCount != null)
query.CreateParameter<int?>("@MinViewCount", filter.MinViewCount, SqlDbType.Int);
if (filter.UserID != null)
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
if (filter.BeginDate != null)
query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
if (filter.EndDate != null)
query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
return condition.ToString();
}
/* [StoredProcedure(Name="bx_InsertBanUserLogs",Script= @"
* CREATE PROCEDURE {name}
* @OperationType varchar(50),
* @OperatorName nvarchar(50),
* @Cause nvarchar(1000),
* @ForumInfos varchar(1000),
* @TargetID int,
* @TargetName nvarchar(200),
* @TargetIP varchar(50)
* AS
* BEGIN
* SET NOCOUNT ON;
* INSERT INTO [bx_BanUserLogs](
* OperationType,
* OperatorName,
* Cause,
* ForumID,
* BeginDate,
* EndDate,
* TargetID,
* TargetName,
* TargetIP
* ) Values(
* @OperationType,
* @OperatorName,
* @Cause,
* @ForumID,
* @BeginDate,
* @EndDate,
* @TargetID,
* @TargetName,
* @TargetIP
* );
* END
* ")]
*
* public override void InsertBanUserOperationLog(BanUserOperation banuser)
* {
* using (SqlQuery query = new SqlQuery())
* {
* query.CommandText = "bx_InsertBanUserLogs";
* query.CommandType = CommandType.StoredProcedure;
*
* query.CreateParameter<string>("@OperationType", banuser.OperationType, SqlDbType.VarChar, 50);
* query.CreateParameter<string>("@OperationName", banuser.OperatorName, SqlDbType.NVarChar, 50);
* query.CreateParameter<string>("@Cause", banuser.Cause, SqlDbType.NVarChar, 200);
* // query.CreateParameter<int>("@ForumID", banuser.ForumID, SqlDbType.Int);
* query.CreateParameter<DateTime>("@BeginDate",banuser.BeginDate,SqlDbType.DateTime);
* query.CreateParameter<DateTime>("@EndDate", banuser.EndDate, SqlDbType.DateTime);
* query.CreateParameter<int>("@TargetID",banuser.UserID,SqlDbType.Int);
* query.CreateParameter<string>("@TargetName",banuser.UserName,SqlDbType.NVarChar,50);
* query.CreateParameter<string>("@TargetIP",banuser.UserIP,SqlDbType.VarChar,50);
*
* query.ExecuteNonQuery();
* }
* }
*/
public override OperationLogCollection GetOperationLogsBySearch(int pageNumber, OperationLogFilter filter)
{
using (SqlQuery db = new SqlQuery())
{
db.Pager.TableName = "bx_OperationLogs";
db.Pager.SortField = "LogID";
db.Pager.PageSize = filter.PageSize;
SqlConditionBuilder sb = new SqlConditionBuilder(SqlConditionStart.None);
if (filter.OperatorID != null)
{
sb.Append("OperatorID = @OperatorID");
db.CreateParameter <int>("@OperatorID", filter.OperatorID.Value, SqlDbType.Int);
}
else if (filter.OperatorName != null && filter.OperatorName != string.Empty)
{
sb.Append("OperatorID IN (SELECT UserID FROM bx_Users WHERE Username = @OperatorName)");
db.CreateParameter <string>("@OperatorName", filter.OperatorName, SqlDbType.NVarChar, 50);
}
if (filter.OperatorIP != null && filter.OperatorIP != string.Empty)
{
sb.Append("OperatorIP = @OperatorIP");
db.CreateParameter <string>("@OperatorIP", filter.OperatorIP, SqlDbType.VarChar, 50);
}
if (filter.OperationType != null && filter.OperationType != string.Empty)
{
sb.Append("OperationType = @OperationType");
db.CreateParameter <string>("@OperationType", filter.OperationType, SqlDbType.VarChar, 100);
}
if (filter.TargetID_1 != null)
{
sb.Append("TargetID_1 = @TargetID_1");
db.CreateParameter <int>("@TargetID_1", filter.TargetID_1.Value, SqlDbType.Int);
}
if (filter.TargetID_2 != null)
{
sb.Append("TargetID_2 = @TargetID_2");
db.CreateParameter <int>("@TargetID_2", filter.TargetID_2.Value, SqlDbType.Int);
}
if (filter.TargetID_3 != null)
{
sb.Append("TargetID_3 = @TargetID_3");
db.CreateParameter <int>("@TargetID_3", filter.TargetID_3.Value, SqlDbType.Int);
}
if (filter.BeginDate != null)
{
sb.Append("CreateTime >= @BeginDate");
db.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
sb.Append("CreateTime <= @EndDate");
db.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
db.Pager.Condition = sb.ToString();
db.Pager.PageNumber = pageNumber;
db.Pager.SelectCount = true;
using (XSqlDataReader reader = db.ExecuteReader())
{
OperationLogCollection result = new OperationLogCollection(reader);
reader.NextResult();
reader.Read();
result.TotalRecords = reader.Get <int>(0);
return(result);
}
}
}
public override DeleteResult DeleteSearchShares(ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, bool getDeleteResult)
{
using (SqlQuery query = new SqlQuery())
{
//string condition = @" 1 = 1 "
// + (filter.UserID == null ? "" : ("AND [UserID] = @UserID "))
// + (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "))
// + (filter.ShareCatagory == ShareCatagory.All ? "" : ("AND [Type] = @Type "))
// + (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "))
// + (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "))
// + (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "))
// + "AND " + DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query);
SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.Where);
condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
condition += (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "));
condition += (filter.ShareType == ShareType.All ? "" : ("AND [Type] = @Type "));
condition += (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "));
condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));
if (getDeleteResult)
{
query.CommandText = @"
SELECT [UserID],COUNT(*) AS [Count] FROM [bx_SharesView] " + condition.ToString() + " GROUP BY [UserID];";
}
else
query.CommandText = string.Empty;
query.CommandText = query.CommandText + @"
DELETE [bx_SharesView] " + condition.ToString();
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
query.CreateParameter<int>("@Type", (int)filter.ShareType, SqlDbType.Int);
query.CreateParameter<int?>("@PrivacyType", filter.PrivacyType == null ? 0 : (int)filter.PrivacyType, SqlDbType.Int);
query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
using (XSqlDataReader reader = query.ExecuteReader())
{
DeleteResult deleteResult = new DeleteResult();
while (reader.Read())
{
deleteResult.Add(reader.Get<int>("UserID"), reader.Get<int>("Count"));
}
return deleteResult;
}
}
}
public override ShareCollection SearchShares(int pageNumber, ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, ref int totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.IsDesc = filter.IsDesc;
if (filter.Order == ShareFilter.OrderBy.ShareID)
query.Pager.SortField = filter.Order.ToString();
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.SelectCount = true;
query.Pager.TableName = "[bx_SharesView]";
SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.None);
condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
condition += (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "));
condition += (filter.ShareType == ShareType.All ? "" : ("AND [Type] = @Type "));
condition += (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "));
condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));
query.Pager.Condition = condition.ToString();
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
query.CreateParameter<int>("@Type", (int)filter.ShareType, SqlDbType.Int);
query.CreateParameter<int?>("@PrivacyType", filter.PrivacyType == null ? 0 : (int)filter.PrivacyType, SqlDbType.Int);
query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
using (XSqlDataReader reader = query.ExecuteReader())
{
ShareCollection shares = new ShareCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
totalCount = reader.Get<int>(0);
}
}
return shares;
}
}
}
private string BuildConditionsByFilter(SqlQuery query, ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, bool startWithWhere)
{
SqlConditionBuilder condition = new SqlConditionBuilder(startWithWhere ? SqlConditionStart.Where : SqlConditionStart.None);
if (filter.UserID != null)
{
condition.AppendAnd("[UserID] = @UserID");
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
}
if (filter.ShareID != null)
{
condition.AppendAnd("[ShareID] = @ShareID");
query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
}
if (filter.ShareType != null)
{
condition.AppendAnd("[Type] = @Type");
query.CreateParameter<ShareType?>("@Type", filter.ShareType, SqlDbType.Int);
}
if (filter.PrivacyType != null)
{
condition.AppendAnd("[PrivacyType] = @PrivacyType");
query.CreateParameter<PrivacyType?>("@PrivacyType", filter.PrivacyType, SqlDbType.Int);
}
if (filter.BeginDate != null)
{
condition.AppendAnd("[CreateDate] > @BeginDate");
query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.AppendAnd("[CreateDate] < @EndDate");
query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
}
condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));
return condition.ToString();
}
public override ShareCollection GetUserShares(int shareOwnerID, ShareType? shareType, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int? totalCount)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_SharesView]";
query.Pager.SortField = "[UserShareID]";
query.Pager.IsDesc = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.SelectCount = true;
SqlConditionBuilder cb = new SqlConditionBuilder(SqlConditionStart.None);
cb.Append("[UserID2] = @UserID");
query.CreateParameter<int>("@UserID", shareOwnerID, SqlDbType.Int);
if (shareType.HasValue && shareType.Value != ShareType.All)
{
cb.Append("[Type] = @Type");
query.CreateParameter<ShareType>("@Type", shareType.Value, SqlDbType.TinyInt);
}
if (dataAccessLevel == DataAccessLevel.Normal)
{
cb.Append("[PrivacyType] IN (0, 3)");
}
else if (dataAccessLevel == DataAccessLevel.Friend)
{
cb.Append("[PrivacyType] IN (0, 1, 3)");
}
else if(dataAccessLevel == DataAccessLevel.DataOwner)
{
cb.Append("[PrivacyType] != 2");
}
query.Pager.Condition = cb.ToString();
ShareCollection shares;
using (XSqlDataReader reader = query.ExecuteReader())
{
shares = new ShareCollection(reader);
if (reader.NextResult())
{
if (totalCount == null && reader.Read())
{
totalCount = reader.Get<int>(0);
}
shares.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillComments(shares, query);
return shares;
}
}