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