private string BuildConditionsByFilter(SqlQuery query, AdminDoingFilter filter, IEnumerable <Guid> excludeRoleIDs, bool startWithWhere) { StringBuffer sqlConditions = new StringBuffer(); if (filter.UserID != null && filter.UserID > 0) { sqlConditions += " AND UserID = @UserID"; query.CreateParameter <int?>("@UserID", filter.UserID, SqlDbType.Int); } if (!string.IsNullOrEmpty(filter.Username)) { sqlConditions += " AND UserID IN (SELECT UserID FROM bx_Users WHERE Username = @Username)"; query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } if (!string.IsNullOrEmpty(filter.Content)) { sqlConditions += " AND Content LIKE '%'+@Content+'%'"; query.CreateParameter <string>("@Content", filter.Content, SqlDbType.NVarChar, 200); } if (!string.IsNullOrEmpty(filter.IP)) { sqlConditions += " AND CreateIP LIKE '%'+@IP+'%'"; query.CreateParameter <string>("@IP", filter.IP, SqlDbType.VarChar, 50); } if (filter.BeginDate != null) { sqlConditions += " AND CreateDate >= @BeginDate"; query.CreateParameter <DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime); } if (filter.EndDate != null) { sqlConditions += " AND CreateDate <= @EndDate"; query.CreateParameter <DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime); } string excludeRoleSQL = DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query); if (string.IsNullOrEmpty(excludeRoleSQL) == false) { sqlConditions += " AND " + excludeRoleSQL; } if (sqlConditions.Length > 0) { sqlConditions.Remove(0, 5); if (startWithWhere) { sqlConditions.InnerBuilder.Insert(0, " WHERE "); } } return(sqlConditions.ToString()); }
/// <summary> /// 删除符合指定条件的通知 /// </summary> public override bool DeleteNotifiesBySearch(AdminNotifyFilter notifyFilter, Guid[] excludeRoleIds) { using (SqlQuery query = new SqlQuery()) { #region 组合SQL语句 StringBuilder builder = FilterToCondition(query, notifyFilter); string excludeRoleSql = DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIds, query); if (string.IsNullOrEmpty(excludeRoleSql) == false) { builder.Append(" AND " + excludeRoleSql); } #endregion query.CommandText = "DELETE FROM bx_Notify WHERE " + builder.ToString(); query.ExecuteNonQuery(); } return(true); }
/// <summary> /// 高级搜索 /// </summary> public override NotifyCollection AdminGetNotifiesBySearch(AdminNotifyFilter notifyFilter, int pageNumber, IEnumerable <Guid> excludeRoleIds) { using (SqlQuery query = new SqlQuery()) { StringBuilder condition = FilterToCondition(query, notifyFilter); string exlcludeUserIDs = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIds, query); if (!string.IsNullOrEmpty(exlcludeUserIDs)) { condition.Append(" AND " + exlcludeUserIDs); } query.Pager.IsDesc = true; query.Pager.TableName = "[bx_Notify]"; query.Pager.SortField = "[NotifyID]"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = notifyFilter.PageSize; query.Pager.SelectCount = true; query.Pager.Condition = condition.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { NotifyCollection notifies = new NotifyCollection(reader); if (reader.NextResult()) { if (reader.Read()) { notifies.TotalRecords = reader.Get <int>(0); } } return(notifies); } } }
public override UserEmoticonInfoCollection AdminGetUserEmoticonInfos(EmoticonFilter filter, int pageIndex, IEnumerable <Guid> excludeRoleIDs) { using (SqlQuery query = new SqlQuery()) { query.Pager.TableName = "bx_UserEmoticonInfo"; query.Pager.PageSize = filter.Pagesize; query.Pager.PageNumber = pageIndex; query.Pager.SortField = "[UserID]"; if (filter.UserName == null) { filter.UserName = string.Empty; } if (filter.Order != null) { switch (filter.Order.Value) { case EmoticonFilter.OrderBy.SpaceSize: query.Pager.SortField = "[TotalSizes]"; break; case EmoticonFilter.OrderBy.EmoticonCount: query.Pager.SortField = "[TotalEmoticons]"; break; } } query.Pager.Condition = " Username LIKE '%'+@Username+'%'"; string excludeRoleUserIds = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIDs, query); if (!string.IsNullOrEmpty(excludeRoleUserIds)) { query.Pager.Condition += " AND " + excludeRoleUserIds; } query.CreateParameter <string>("@Username", filter.UserName, SqlDbType.NVarChar, 50); query.Pager.SelectCount = true; query.Pager.PrimaryKey = "[UserID]"; query.Pager.IsDesc = filter.IsDesc; using (XSqlDataReader reader = query.ExecuteReader()) { UserEmoticonInfoCollection groups = new UserEmoticonInfoCollection(reader); if (reader.NextResult()) { if (reader.Read()) { groups.TotalRecords = reader.Get <int>(0); } } return(groups); } } }
public override void AdminDeleteMessage(IEnumerable <int> messageIDs, IEnumerable <Guid> excludeRoles) { using (SqlQuery query = new SqlQuery()) { string excludeRoleUserIds = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoles, query); query.CommandText = "DELETE FROM bx_ChatMessages WHERE MessageID IN( @IDs)"; if (!string.IsNullOrEmpty(excludeRoleUserIds)) { query.CommandText += " AND " + excludeRoleUserIds; } query.CreateInParameter <int>("@IDs", messageIDs); query.ExecuteNonQuery(); } }
public override void AdminDeleteSessions(IEnumerable <int> sessionIds, IEnumerable <Guid> exludeRoles) { using (SqlQuery query = new SqlQuery()) { string excludeRoleCondition = DaoUtil.GetExcludeRoleSQL("UserID", exludeRoles, query); query.CommandText = "DELETE FROM bx_ChatSessions WHERE ChatSessionID IN( @IDs) "; if (!string.IsNullOrEmpty(excludeRoleCondition)) { query.CommandText += " AND " + excludeRoleCondition; } query.CreateInParameter <int>("@IDs", sessionIds); query.ExecuteNonQuery(); } }
public override void AdminDeleteDiskFiles(IEnumerable <int> ids, IEnumerable <Guid> excludeRoleIds) { using (SqlQuery query = new SqlQuery()) { string sql = "DELETE FROM bx_DiskFiles WHERE DiskFileID IN (@FileIds)"; query.CreateInParameter <int>("@FileIds", ids); string excludeRoleUsers = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIds, query); if (!string.IsNullOrEmpty(excludeRoleUsers)) { sql += " AND " + excludeRoleUsers; } query.CommandText = sql; query.ExecuteNonQuery(); } }
public override DeleteResult DeleteDoings(int operatorID, IEnumerable <int> doingIDs, IEnumerable <Guid> excludeRoleIDs) { using (SqlQuery query = new SqlQuery()) { string excludeRolesSql = DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query); if (string.IsNullOrEmpty(excludeRolesSql) == false) { excludeRolesSql = " AND ([UserID] = @UserID OR " + excludeRolesSql + ")"; } string sql = @" DECLARE @DeleteData table (UserID int, DoingID int); INSERT INTO @DeleteData SELECT [UserID],[DoingID] FROM [bx_Doings] WHERE [DoingID] IN (@DoingIDs)" + excludeRolesSql + @"; DELETE [bx_Doings] WHERE DoingID IN (SELECT [DoingID] FROM @DeleteData); SELECT [UserID],COUNT(*) AS [Count] FROM @DeleteData GROUP BY [UserID];"; query.CommandText = sql; query.CreateInParameter <int>("@DoingIDs", doingIDs); query.CreateParameter <int>("@UserID", operatorID, SqlDbType.Int); 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 DiskFileCollection AdminSearchFiles(DiskFileFilter filter, IEnumerable <Guid> exculdeRoles, int pageIndex) { using (SqlQuery query = new SqlQuery()) { StringBuilder builder = new StringBuilder(); if (filter.UserID != null) { builder.Append(" AND UserID = @UserID"); query.CreateParameter <int>("@UserID", filter.UserID.Value, SqlDbType.Int); } if (filter.Username != null) { builder.Append(" AND UserID IN (SELECT UserID FROM bx_Users WHERE Username LIKE '%' + @Username + '%')"); query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } if (filter.DirectoryName != null) { builder.Append(" AND DirectoryID IN (SELECT DirectoryID FROM bx_DiskDirectories WHERE [Name] LIKE '%' + @DirectoryName + '%')"); query.CreateParameter <string>("@DirectoryName", filter.DirectoryName, SqlDbType.NVarChar, 256); } if (filter.Filename != null) { builder.Append(" AND FileName LIKE '%' + @FileName + '%'"); query.CreateParameter <string>("@FileName", filter.Filename, SqlDbType.NVarChar, 256); } if (filter.Size_1 != null) { builder.Append(" AND FileSize >= @FileSize1"); long size = filter.Size_1.Value; switch (filter.SizeUnit_1) { case FileSizeUnit.K: size *= 1024; break; case FileSizeUnit.M: size *= 1024 * 1024; break; case FileSizeUnit.G: size *= 1024 * 1024 * 1024; break; } query.CreateParameter <long>("@FileSize1", size, SqlDbType.BigInt); } if (filter.Size_2 != null) { builder.Append(" AND FileSize <= @FileSize2"); long size = filter.Size_2.Value; switch (filter.SizeUnit_2) { case FileSizeUnit.K: size *= 1024; break; case FileSizeUnit.M: size *= 1024 * 1024; break; case FileSizeUnit.G: size *= 1024 * 1024 * 1024; break; } query.CreateParameter <long>("@FileSize2", size, SqlDbType.BigInt); } if (filter.CreateDate_1 != null) { builder.Append(" AND CreateDate >= @CreateDate1"); query.CreateParameter <DateTime>("@CreateDate1", filter.CreateDate_1.Value, SqlDbType.DateTime); } if (filter.CreateDate_2 != null) { builder.Append(" AND CreateDate <= @CreateDate2"); query.CreateParameter <DateTime>("@CreateDate2", filter.CreateDate_2.Value, SqlDbType.DateTime); } string noSelectRoles = DaoUtil.GetExcludeRoleSQL("[UserID]", exculdeRoles, query); if (!string.IsNullOrEmpty(noSelectRoles)) { builder.Append(" AND "); builder.Append(noSelectRoles); } if (builder.Length >= 5) { builder.Remove(0, 5); } query.Pager.TableName = "bx_DiskFiles"; query.Pager.PageSize = filter.PageSize; query.Pager.PageNumber = pageIndex; query.Pager.Condition = builder.ToString(); query.Pager.IsDesc = filter.IsDesc == null ? true : filter.IsDesc.Value; query.Pager.PrimaryKey = "DiskFileID"; query.Pager.SortField = "DiskFileID"; query.Pager.SelectCount = true; if (filter.Order != null) { switch (filter.Order.Value) { case FileOrderBy.CreateDate: query.Pager.SortField = "CreateDate"; break; case FileOrderBy.Name: query.Pager.SortField = "FileName"; break; case FileOrderBy.Size: query.Pager.SortField = "FileSize"; break; case FileOrderBy.Type: query.Pager.SortField = "Extension"; break; } } using (XSqlDataReader reader = query.ExecuteReader()) { DiskFileCollection files = new DiskFileCollection(reader); if (reader.NextResult()) { if (reader.Read()) { files.TotalRecords = reader.Get <int>(0); } } return(files); } } }
public override ChatSessionCollection AdminGetSessions(ChatSessionFilter filter, int pageNumber, IEnumerable <Guid> excludeRoleIds) { ChatSessionCollection sessions; using (SqlQuery query = new SqlQuery()) { string excludeRoleCondition = DaoUtil.GetExcludeRoleSQL("UserID", excludeRoleIds, query); StringBuffer buffer = new StringBuffer(); if (filter.UserID != null) { buffer += " AND UserID = @UserID"; query.CreateParameter <int>("@UserID", filter.UserID.Value, SqlDbType.Int); } if (!string.IsNullOrEmpty(filter.Username)) { buffer += " AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%'+@Username+'%' ) OR TargetUserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%'+@Username+'%' )"; query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } //if (!string.IsNullOrEmpty(filter.TargetUsername)) //{ // buffer += " AND TargetUserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%'+@TargetUser+'%' )"; // query.CreateParameter<string>("@TargetUser", filter.TargetUsername, SqlDbType.NVarChar, 50); //} //if (!string.IsNullOrEmpty(filter.Contains)) //{ // buffer +=" AND ChatSessionID IN( SELECT ) " //} if (filter.BeginDate != null) { buffer += " AND CreateDate >= @BeginDate"; query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime); } if (filter.EndDate != null) { buffer += " AND UpdateDate <= @EndDate"; query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime); } if (!string.IsNullOrEmpty(excludeRoleCondition)) { buffer += " AND " + excludeRoleCondition; excludeRoleCondition = DaoUtil.GetExcludeRoleSQL("TargetUserID", excludeRoleIds, query); buffer += " AND " + excludeRoleCondition; } if (buffer.Length > 0) { buffer.Remove(0, 5); } //query.CommandText = "SELECT * FROM bx_ChatSessions"; query.Pager.TableName = "bx_ChatSessions"; query.Pager.PrimaryKey = "ChatSessionID"; query.Pager.PageNumber = pageNumber; query.Pager.PageSize = filter.PageSize; query.Pager.SortField = "ChatSessionID"; query.Pager.IsDesc = true; query.Pager.SelectCount = true; query.Pager.Condition = buffer.ToString(); using (XSqlDataReader reader = query.ExecuteReader()) { sessions = new ChatSessionCollection(reader); while (reader.NextResult()) { if (reader.Read()) { sessions.TotalRecords = reader.GetInt32(0); } } } } return(sessions); }
private string BuildConditionsByFilter(SqlQuery query, AdminCommentFilter filter, bool startWithWhere, int operatorUserID, IEnumerable <Guid> excludeRoleIds) { StringBuffer sqlCondition = new StringBuffer(); if (filter.Type != CommentType.All) { sqlCondition += " AND Type = @Type"; query.CreateParameter <int>("@Type", (int)filter.Type, SqlDbType.Int); } if (filter.IsApproved != null) { sqlCondition += " AND IsApproved = @IsApproved"; query.CreateParameter <bool?>("@IsApproved", filter.IsApproved, SqlDbType.Bit); } if (string.IsNullOrEmpty(filter.Username) == false) { sqlCondition += " AND UserID = (SELECT UserID FROM bx_Users WHERE Username = @Username)"; query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50); } if (string.IsNullOrEmpty(filter.TargetUsername) == false) { sqlCondition += " AND TargetUserID IN (SELECT UserID FROM bx_Users WHERE Username = @TargetUsername)"; query.CreateParameter <string>("@TargetUsername", filter.TargetUsername, SqlDbType.NVarChar, 50); } if (filter.BeginDate != null) { sqlCondition += " AND CreateDate >= @BeginDate"; query.CreateParameter <DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime); } if (filter.EndDate != null) { sqlCondition += " AND CreateDate <= @EndDate"; query.CreateParameter <DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime); } if (!string.IsNullOrEmpty(filter.Content)) { sqlCondition += " AND Content LIKE '%'+@Content+'%'"; query.CreateParameter <string>("@Content", filter.Content, SqlDbType.NVarChar, 50); } if (!string.IsNullOrEmpty(filter.IP)) { sqlCondition += " AND CreateIP = @CreateIP"; query.CreateParameter <string>("@CreateIP", filter.IP, SqlDbType.VarChar, 50); } string excludeRoleSQL = DaoUtil.GetExcludeRoleSQL("[UserID]", "[LastEditUserID]", operatorUserID, excludeRoleIds, query); if (string.IsNullOrEmpty(excludeRoleSQL) == false) { sqlCondition += " AND " + excludeRoleSQL; } if (sqlCondition.Length != 0) { sqlCondition.Remove(0, 5); } if (startWithWhere && sqlCondition.Length > 0) { sqlCondition.InnerBuilder.Insert(0, " WHERE "); } return(sqlCondition.ToString()); }