public override BanUserOperationCollection GetBanUserLogsByUserID(int userid)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = @"
SELECT * FROM bx_BanUserLogs WHERE UserID = @userid ORDER BY OperationTime DESC;
SELECT * FROM bx_BanUserLogForumInfos WHERE LogID IN(SELECT LogID FROM bx_BanUserLogs WHERE UserID = @userid) ORDER BY LogID DESC;";
query.CreateParameter <int>("@userid", userid, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
BanUserOperationCollection collection = new BanUserOperationCollection(reader);
BanUserOperation operation = null;
if (reader.NextResult())
{
while (reader.Next)
{
BanForumInfo foruminfo = new BanForumInfo(reader);
if (operation != null && foruminfo.ID == operation.ID)
{
operation.ForumInfoList.Add(foruminfo);
}
else
{
operation = collection.GetValue(foruminfo.ID);
operation.ForumInfoList.Add(foruminfo);
}
}
}
return(collection);
}
}
}
public override BanUserOperationCollection GetBanUserLogsBySearch(BanUserLogFilter filter, int pagenumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = new StringBuilder();
string SortField = "OperationTime";
if (filter.OrderBy != null)
{
switch (filter.OrderBy)
{
case BanUserLogSortOrder.LogID:
SortField = "LogID";
break;
case BanUserLogSortOrder.OperationType:
SortField = "OperationType";
break;
case BanUserLogSortOrder.UserID:
SortField = "UserID";
break;
case BanUserLogSortOrder.UserName:
SortField = "UserName";
break;
default:
break;
}
}
if (!string.IsNullOrEmpty(filter.Username))
{
condition.Append(" AND Username=@Username");
query.CreateParameter<string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
if (filter.UserID != null)
{
condition.Append(" AND UserID=@UserID");
query.CreateParameter<int>("@UserID", filter.UserID.Value, SqlDbType.Int);
}
if (filter.ForumID != null)
{
condition.Append(" AND LogID IN( SELECT LogID FROM bx_BanUserLogForumInfos WHERE ForumID=@ForumID)");
query.CreateParameter<int>("@ForumID", filter.ForumID.Value, SqlDbType.Int);
}
if (filter.OperationType != null)
{
condition.Append(" AND OperationType=@OperationType");
query.CreateParameter<int>("@OperationType", (int)filter.OperationType.Value, SqlDbType.Int);
}
if (filter.BeginDate != null)
{
condition.Append(" AND OperationTime>=@BeginDate");
query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND OperationTime<=@EndDate");
query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
query.Pager.SortField = SortField;
query.Pager.PageNumber = pagenumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_BanUserLogs";
query.Pager.PrimaryKey = "LogID";
query.Pager.IsDesc = filter != null ? filter.IsDesc : true;
query.Pager.SelectCount = true;
if (condition.Length > 0)
{
query.Pager.Condition = condition.ToString().Substring(4);
}
using (XSqlDataReader reader = query.ExecuteReader())
{
BanUserOperationCollection collection = new BanUserOperationCollection(reader);
if (reader.NextResult())
{
while(reader.Next)
collection.TotalRecords = reader.Get<int>(0);
}
return collection;
}
}
}
public override BanUserOperationCollection GetBanUserLogsByUserID(int userid)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = @"
SELECT * FROM bx_BanUserLogs WHERE UserID = @userid ORDER BY OperationTime DESC;
SELECT * FROM bx_BanUserLogForumInfos WHERE LogID IN(SELECT LogID FROM bx_BanUserLogs WHERE UserID = @userid) ORDER BY LogID DESC;";
query.CreateParameter<int>("@userid", userid, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
BanUserOperationCollection collection = new BanUserOperationCollection(reader);
BanUserOperation operation = null;
if(reader.NextResult())
{
while (reader.Next)
{
BanForumInfo foruminfo = new BanForumInfo(reader);
if (operation != null && foruminfo.ID == operation.ID)
operation.ForumInfoList.Add(foruminfo);
else
{
operation = collection.GetValue(foruminfo.ID);
operation.ForumInfoList.Add(foruminfo);
}
}
}
return collection;
}
}
}
public override BanUserOperationCollection GetBanUserLogsBySearch(BanUserLogFilter filter, int pagenumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = new StringBuilder();
string SortField = "OperationTime";
if (filter.OrderBy != null)
{
switch (filter.OrderBy)
{
case BanUserLogSortOrder.LogID:
SortField = "LogID";
break;
case BanUserLogSortOrder.OperationType:
SortField = "OperationType";
break;
case BanUserLogSortOrder.UserID:
SortField = "UserID";
break;
case BanUserLogSortOrder.UserName:
SortField = "UserName";
break;
default:
break;
}
}
if (!string.IsNullOrEmpty(filter.Username))
{
condition.Append(" AND Username=@Username");
query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
if (filter.UserID != null)
{
condition.Append(" AND UserID=@UserID");
query.CreateParameter <int>("@UserID", filter.UserID.Value, SqlDbType.Int);
}
if (filter.ForumID != null)
{
condition.Append(" AND LogID IN( SELECT LogID FROM bx_BanUserLogForumInfos WHERE ForumID=@ForumID)");
query.CreateParameter <int>("@ForumID", filter.ForumID.Value, SqlDbType.Int);
}
if (filter.OperationType != null)
{
condition.Append(" AND OperationType=@OperationType");
query.CreateParameter <int>("@OperationType", (int)filter.OperationType.Value, SqlDbType.Int);
}
if (filter.BeginDate != null)
{
condition.Append(" AND OperationTime>=@BeginDate");
query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND OperationTime<=@EndDate");
query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
query.Pager.SortField = SortField;
query.Pager.PageNumber = pagenumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_BanUserLogs";
query.Pager.PrimaryKey = "LogID";
query.Pager.IsDesc = filter != null ? filter.IsDesc : true;
query.Pager.SelectCount = true;
if (condition.Length > 0)
{
query.Pager.Condition = condition.ToString().Substring(4);
}
using (XSqlDataReader reader = query.ExecuteReader())
{
BanUserOperationCollection collection = new BanUserOperationCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
collection.TotalRecords = reader.Get <int>(0);
}
}
return(collection);
}
}
}