public override DeleteResult DeleteAlbumsBySearch(AdminAlbumFilter filter, int operatorID, IEnumerable<Guid> excludeRoleIDs, int topCount, out int deletedCount, out int[] deletedPhotoIDs)
{
deletedCount = 0;
using (SqlQuery query = new SqlQuery())
{
string conditions = BuildConditionsByFilter(query, filter, operatorID, excludeRoleIDs, true);
StringBuffer sql = new StringBuffer();
sql += @"
DECLARE @DeleteData table (UserID int, AlbumID int, PhotoCount int);
INSERT INTO @DeleteData SELECT TOP (@TopCount) [UserID],[A].[AlbumID],(SELECT COUNT(*) FROM [bx_Photos] AS [B] WHERE [B].[AlbumID]=[A].[AlbumID]) FROM [bx_Albums] AS [A] " + conditions + @";
DECLARE @DeletePhotos table (PhotoID int);
INSERT INTO @DeletePhotos SELECT PhotoID from bx_Photos WHERE AlbumID IN (SELECT [AlbumID] FROM @DeleteData);
DELETE [bx_Albums] WHERE [AlbumID] IN (SELECT [AlbumID] FROM @DeleteData);
SELECT @@ROWCOUNT;
SELECT [UserID],SUM([PhotoCount]) AS [Count] FROM @DeleteData GROUP BY [UserID];
SELECT [PhotoID] FROM @DeletePhotos;";
query.CreateTopParameter("@TopCount", topCount);
query.CommandText = sql.ToString();
using (XSqlDataReader reader = query.ExecuteReader())
{
DeleteResult deleteResult = new DeleteResult();
if (reader.Read())
deletedCount = reader.Get<int>(0);
reader.NextResult();
while (reader.Read())
{
deleteResult.Add(reader.Get<int>("UserID"), reader.Get<int>("Count"));
}
reader.NextResult();
List<int> deletePhotos = new List<int>();
while (reader.Read())
{
deletePhotos.Add(reader.Get<int>(0));
}
deletedPhotoIDs = deletePhotos.ToArray();
return deleteResult;
}
}
}
private string BuildConditionsByFilter(SqlQuery query, AdminAlbumFilter filter, int operatorUserID, IEnumerable<Guid> excludeRoleIds, bool startWithWhere)
{
StringBuffer sqlConditions = new StringBuffer();
if (filter.AlbumID != null)
{
sqlConditions += " AND [AlbumID] = @AlbumID";
query.CreateParameter<int?>("@AlbumID", filter.AlbumID, SqlDbType.Int);
}
if (filter.AuthorID != null)
{
sqlConditions += " AND [UserID] = @UserID";
query.CreateParameter<int?>("@UserID", filter.AuthorID, SqlDbType.Int);
}
if (filter.PrivacyType != null)
{
sqlConditions += " AND [PrivacyType] = @PrivacyType";
query.CreateParameter<PrivacyType?>("@PrivacyType", filter.PrivacyType, SqlDbType.TinyInt);
}
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);
}
if (string.IsNullOrEmpty(filter.Username) == false)
{
sqlConditions += " AND [UserID] = (SELECT [UserID] FROM [bx_Users] WHERE [Username] = @Username)";
query.CreateParameter<string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
if (string.IsNullOrEmpty(filter.Name) == false)
{
sqlConditions += " AND ([Name] LIKE '%' + @Key + '%')";
query.CreateParameter<string>("@Key", filter.Name, SqlDbType.NVarChar, 50);
}
string excludeRolesSql = DaoUtil.GetExcludeRoleSQL("[UserID]", "[LastEditUserID]", operatorUserID, excludeRoleIds, query);
if (string.IsNullOrEmpty(excludeRolesSql) == false)
sqlConditions += " AND " + excludeRolesSql;
if (sqlConditions.Length > 0)
{
sqlConditions.Remove(0, 5);
if (startWithWhere)
sqlConditions.InnerBuilder.Insert(0, " WHERE ");
}
return sqlConditions.ToString();
}
public override AlbumCollection GetAlbumsBySearch(int operatorID, IEnumerable<Guid> excludeRoleIDs, AdminAlbumFilter filter, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
string sqlCondition = BuildConditionsByFilter(query, filter, operatorID, excludeRoleIDs, false);
query.Pager.TableName = "bx_Albums";
query.Pager.SortField = filter.Order.ToString();
if (filter.Order != AdminAlbumFilter.OrderBy.AlbumID)
{
query.Pager.PrimaryKey = "AlbumID";
}
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.SelectCount = true;
query.Pager.IsDesc = filter.IsDesc;
query.Pager.Condition = sqlCondition;
using (XSqlDataReader reader = query.ExecuteReader())
{
AlbumCollection albums = new AlbumCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
albums.TotalRecords = reader.Get<int>(0);
}
}
return albums;
}
}
}
