public override UserIPLogCollection GetUserIPLogsByIP(string IP)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "SELECT * FROM [bx_IPLogs] LEFT JOIN bx_BannedUsers ON bx_BannedUsers.UserID=bx_IPLogs.UserID WHERE NewIP=@IP";
query.CreateParameter <string>("@IP", IP, SqlDbType.VarChar, 50);
using (XSqlDataReader reader = query.ExecuteReader())
{
UserIPLogCollection collection = new UserIPLogCollection(reader);
return(collection);
}
}
}
public override UserIPLogCollection GetUserIPLogsByIP(string IP, int pageNumber, int pageSize, out int total)
{
total = 0;
using (SqlQuery query = new SqlQuery())
{
query.Pager.IsDesc = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.TableName = "[bx_IPLogs]";
query.Pager.SortField = "[LogID]";
query.Pager.Condition = " LogID in(SELECT Max(LogID) FROM [bx_IPLogs] WHERE NewIP = @IP GROUP BY UserID) ";
query.Pager.AfterExecute = "SELECT * FROM bx_BannedUsers WHERE UserID in(SELECT UserID FROM [bx_IPLogs] WHERE NewIP = @IP GROUP BY UserID)";
//query.CommandText = "SELECT * FROM [bx_IPLogs] LEFT JOIN bx_BannedUsers ON bx_BannedUsers.UserID=bx_IPLogs.UserID WHERE NewIP=@IP";
query.CreateParameter <string>("@IP", IP, SqlDbType.VarChar, 50);
using (XSqlDataReader reader = query.ExecuteReader())
{
UserIPLogCollection collection = new UserIPLogCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
total = reader.GetInt32(0);
}
}
if (reader.NextResult())
{
while (reader.Read())
{
int userID = reader.Get <int>("UserID");
int forumID = reader.Get <int>("ForumID");
foreach (UserIPLog log in collection)
{
if (log.UserID == userID)
{
log.BannedForumID = forumID;
}
}
}
}
return(collection);
}
}
}
public override UserIPLogCollection GetUserIPLogsByIP(string IP, int pageNumber, int pageSize, out int total)
{
total = 0;
using (SqlQuery query = new SqlQuery())
{
query.Pager.IsDesc = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.TableName = "[bx_IPLogs]";
query.Pager.SortField = "[LogID]";
query.Pager.Condition = " LogID in(SELECT Max(LogID) FROM [bx_IPLogs] WHERE NewIP = @IP GROUP BY UserID) ";
query.Pager.AfterExecute = "SELECT * FROM bx_BannedUsers WHERE UserID in(SELECT UserID FROM [bx_IPLogs] WHERE NewIP = @IP GROUP BY UserID)";
//query.CommandText = "SELECT * FROM [bx_IPLogs] LEFT JOIN bx_BannedUsers ON bx_BannedUsers.UserID=bx_IPLogs.UserID WHERE NewIP=@IP";
query.CreateParameter<string>("@IP", IP, SqlDbType.VarChar, 50);
using (XSqlDataReader reader = query.ExecuteReader())
{
UserIPLogCollection collection = new UserIPLogCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
total = reader.GetInt32(0);
}
}
if (reader.NextResult())
{
while (reader.Read())
{
int userID = reader.Get<int>("UserID");
int forumID = reader.Get<int>("ForumID");
foreach (UserIPLog log in collection)
{
if (log.UserID == userID)
{
log.BannedForumID = forumID;
}
}
}
}
return collection;
}
}
}
public override UserIPLogCollection GetUserIPLogsByIP(string IP)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "SELECT * FROM [bx_IPLogs] LEFT JOIN bx_BannedUsers ON bx_BannedUsers.UserID=bx_IPLogs.UserID WHERE NewIP=@IP";
query.CreateParameter<string>("@IP", IP, SqlDbType.VarChar, 50);
using (XSqlDataReader reader = query.ExecuteReader())
{
UserIPLogCollection collection = new UserIPLogCollection(reader);
return collection;
}
}
}
public override UserIPLogCollection GetUserIPLogsBySearch(UserIPSearchFilter filter, int pagenumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = new StringBuilder();
string SortField = "CreateDate";
if (filter != null)
{
switch (filter.OrderBy)
{
case UserIPLogSortOrder.Username:
SortField = "Username";
break;
case UserIPLogSortOrder.NewIP:
SortField = "NewIP";
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 (!string.IsNullOrEmpty(filter.NewIP))
{
condition.Append(" AND NewIP=@NewIP");
query.CreateParameter<string>("@NewIP", filter.NewIP, SqlDbType.VarChar, 50);
}
if (filter.BeginDate != null)
{
condition.Append(" AND CreateDate>=@BeginDate");
query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND CreateDate<=@EndDate");
query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
}
query.Pager.SortField = SortField;
query.Pager.TableName = "bx_IPLogs";
query.Pager.PageSize = filter.PageSize;
query.Pager.PageNumber = pagenumber;
query.Pager.PrimaryKey = "LogID";
query.Pager.IsDesc = filter != null ? filter.IsDesc : true;
if (condition.Length > 0)
query.Pager.Condition = condition.ToString().Substring(4);
query.Pager.SelectCount = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserIPLogCollection iploglist = new UserIPLogCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
iploglist.TotalRecords = reader.Get<int>(0);
}
return iploglist;
}
}
}
public override UserIPLogCollection GetUserIPLogsBySearch(UserIPSearchFilter filter, int pagenumber)
{
using (SqlQuery query = new SqlQuery())
{
StringBuilder condition = new StringBuilder();
string SortField = "CreateDate";
if (filter != null)
{
switch (filter.OrderBy)
{
case UserIPLogSortOrder.Username:
SortField = "Username";
break;
case UserIPLogSortOrder.NewIP:
SortField = "NewIP";
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 (!string.IsNullOrEmpty(filter.NewIP))
{
condition.Append(" AND NewIP=@NewIP");
query.CreateParameter <string>("@NewIP", filter.NewIP, SqlDbType.VarChar, 50);
}
if (filter.BeginDate != null)
{
condition.Append(" AND CreateDate>=@BeginDate");
query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND CreateDate<=@EndDate");
query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
}
query.Pager.SortField = SortField;
query.Pager.TableName = "bx_IPLogs";
query.Pager.PageSize = filter.PageSize;
query.Pager.PageNumber = pagenumber;
query.Pager.PrimaryKey = "LogID";
query.Pager.IsDesc = filter != null ? filter.IsDesc : true;
if (condition.Length > 0)
{
query.Pager.Condition = condition.ToString().Substring(4);
}
query.Pager.SelectCount = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserIPLogCollection iploglist = new UserIPLogCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
iploglist.TotalRecords = reader.Get <int>(0);
}
}
return(iploglist);
}
}
}