public static UserUsageSummary GetUserUsage(string alias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT SUM(UsageMinutes) AS UsageMinutes FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)";
command.Parameters.AddWithValue("@Alias", alias);
DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MinValue;
GetPeriodDates(period, ref startDate, ref endDate);
command.Parameters.AddWithValue("@StartDate", startDate.ToString());
command.Parameters.AddWithValue("@EndDate", endDate.ToString());
reader = command.ExecuteReader();
UserUsageSummary summary = new UserUsageSummary();
summary.Alias = alias;
summary.Period = period;
if (reader.Read())
{
if (!((reader["UsageMinutes"]) is DBNull))
{
summary.TotalHours = Convert.ToInt32(reader["UsageMinutes"]) / 60;
}
}
TimeSpan span = endDate - startDate;
if (span.Days == 0)
{
span = new TimeSpan(1, 0, 0, 0);
}
summary.AverageHours = (float)summary.TotalHours / (float)span.Days;
return summary;
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}
public static ReadOnlyCollection<UserUsageSummary> GetUserSummaries(UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
string cacheKey = "GetUserSummaries(" + period.ToString() + ")";
if (HttpContext.Current.Cache[cacheKey] != null)
{
return new ReadOnlyCollection<UserUsageSummary>((List<UserUsageSummary>)HttpContext.Current.Cache[cacheKey]);
}
else
{
List<UserUsageSummary> userList = new List<UserUsageSummary>();
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT TOP 10 Alias, SUM(UsageMinutes) AS UsageTotal FROM Usage WHERE TickTime >= @StartDate AND TickTime <= @EndDate GROUP BY Alias ORDER BY UsageTotal DESC";
DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MinValue;
GetPeriodDates(period, ref startDate, ref endDate);
command.Parameters.AddWithValue("@StartDate", startDate.ToString());
command.Parameters.AddWithValue("@EndDate", endDate.ToString());
reader = command.ExecuteReader();
while (reader.Read())
{
UserUsageSummary summary = new UserUsageSummary();
summary.Alias = Convert.ToString(reader["Alias"]);
summary.Period = period;
summary.TotalHours = Convert.ToInt32(reader["UsageTotal"]) / 60;
userList.Add(summary);
}
HttpContext.Current.Cache.Add(cacheKey, userList, null, DateTime.Now.AddMinutes(59), TimeSpan.Zero, CacheItemPriority.Normal, null);
return new ReadOnlyCollection<UserUsageSummary>(userList);
}
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}
public static ReadOnlyCollection<UsageData> GetUserDetails(string alias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)";
command.Parameters.AddWithValue("@Alias", alias);
DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MinValue;
GetPeriodDates(period, ref startDate, ref endDate);
command.Parameters.AddWithValue("@StartDate", startDate.ToString());
command.Parameters.AddWithValue("@EndDate", endDate.ToString());
reader = command.ExecuteReader();
UserUsageSummary summary = new UserUsageSummary();
summary.Alias = alias;
summary.Period = period;
List<UsageData> usageList = new List<UsageData>();
while (reader.Read())
{
UsageData data = new UsageData();
data.Alias = Convert.ToString(reader["Alias"]);
data.Domain = Convert.ToString(reader["Domain"]);
data.TickTime = Convert.ToDateTime(reader["TickTime"]);
data.UsageMinutes = Convert.ToInt32(reader["UsageMinutes"]);
data.IPAddress = Convert.ToString(reader["IPAddress"]);
data.GameVersion = Convert.ToString(reader["GameVersion"]);
data.PeerChannel = Convert.ToString(reader["PeerChannel"]);
data.PeerCount = Convert.ToInt32(reader["PeerCount"]);
data.AnimalCount = Convert.ToInt32(reader["AnimalCount"]);
data.MaxAnimalCount = Convert.ToInt32(reader["MaxAnimalCount"]);
data.WorldHeight = Convert.ToInt32(reader["WorldHeight"]);
data.WorldWidth = Convert.ToInt32(reader["WorldWidth"]);
data.MachineName = Convert.ToString(reader["MachineName"]);
data.OSVersion = Convert.ToString(reader["OSVersion"]);
data.ProcessorCount = Convert.ToInt32(reader["ProcessorCount"]);
data.ClrVersion = Convert.ToString(reader["ClrVersion"]);
data.WorkingSet = Convert.ToInt32(reader["WorkingSet"]);
data.MaxWorkingSet = Convert.ToInt32(reader["MaxWorkingSet"]);
data.MinWorkingSet = Convert.ToInt32(reader["MinWorkingSet"]);
data.ProcessorTimeInSeconds = Convert.ToInt32(reader["ProcessorTime"]);
data.ProcessStartTime = Convert.ToDateTime(reader["ProcessStartTime"]);
usageList.Add(data);
}
return new ReadOnlyCollection<UsageData>(usageList);
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}
public static ReadOnlyCollection <UserUsageSummary> GetUserSummaries(UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
string cacheKey = "GetUserSummaries(" + period + ")";
if (HttpContext.Current.Cache[cacheKey] != null)
{
return
(new ReadOnlyCollection <UserUsageSummary>(
(List <UserUsageSummary>)HttpContext.Current.Cache[cacheKey]));
}
else
{
List <UserUsageSummary> userList = new List <UserUsageSummary>();
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
"SELECT TOP 10 Alias, SUM(UsageMinutes) AS UsageTotal FROM Usage WHERE TickTime >= @StartDate AND TickTime <= @EndDate GROUP BY Alias ORDER BY UsageTotal DESC";
DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MinValue;
GetPeriodDates(period, ref startDate, ref endDate);
command.Parameters.AddWithValue("@StartDate", startDate.ToString());
command.Parameters.AddWithValue("@EndDate", endDate.ToString());
reader = command.ExecuteReader();
while (reader.Read())
{
UserUsageSummary summary = new UserUsageSummary();
summary.Alias = Convert.ToString(reader["Alias"]);
summary.Period = period;
summary.TotalHours = Convert.ToInt32(reader["UsageTotal"]) / 60;
userList.Add(summary);
}
HttpContext.Current.Cache.Add(cacheKey, userList, null, DateTime.Now.AddMinutes(59), TimeSpan.Zero,
CacheItemPriority.Normal, null);
return(new ReadOnlyCollection <UserUsageSummary>(userList));
}
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}
public static ReadOnlyCollection <UsageData> GetUserDetails(string alias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
"SELECT * FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)";
command.Parameters.AddWithValue("@Alias", alias);
DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MinValue;
GetPeriodDates(period, ref startDate, ref endDate);
command.Parameters.AddWithValue("@StartDate", startDate.ToString());
command.Parameters.AddWithValue("@EndDate", endDate.ToString());
reader = command.ExecuteReader();
UserUsageSummary summary = new UserUsageSummary();
summary.Alias = alias;
summary.Period = period;
List <UsageData> usageList = new List <UsageData>();
while (reader.Read())
{
UsageData data = new UsageData();
data.Alias = Convert.ToString(reader["Alias"]);
data.Domain = Convert.ToString(reader["Domain"]);
data.TickTime = Convert.ToDateTime(reader["TickTime"]);
data.UsageMinutes = Convert.ToInt32(reader["UsageMinutes"]);
data.IPAddress = Convert.ToString(reader["IPAddress"]);
data.GameVersion = Convert.ToString(reader["GameVersion"]);
data.PeerChannel = Convert.ToString(reader["PeerChannel"]);
data.PeerCount = Convert.ToInt32(reader["PeerCount"]);
data.AnimalCount = Convert.ToInt32(reader["AnimalCount"]);
data.MaxAnimalCount = Convert.ToInt32(reader["MaxAnimalCount"]);
data.WorldHeight = Convert.ToInt32(reader["WorldHeight"]);
data.WorldWidth = Convert.ToInt32(reader["WorldWidth"]);
data.MachineName = Convert.ToString(reader["MachineName"]);
data.OSVersion = Convert.ToString(reader["OSVersion"]);
data.ProcessorCount = Convert.ToInt32(reader["ProcessorCount"]);
data.ClrVersion = Convert.ToString(reader["ClrVersion"]);
data.WorkingSet = Convert.ToInt32(reader["WorkingSet"]);
data.MaxWorkingSet = Convert.ToInt32(reader["MaxWorkingSet"]);
data.MinWorkingSet = Convert.ToInt32(reader["MinWorkingSet"]);
data.ProcessorTimeInSeconds = Convert.ToInt32(reader["ProcessorTime"]);
data.ProcessStartTime = Convert.ToDateTime(reader["ProcessStartTime"]);
usageList.Add(data);
}
return(new ReadOnlyCollection <UsageData>(usageList));
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}
public static UserUsageSummary GetUserUsage(string alias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
"SELECT SUM(UsageMinutes) AS UsageMinutes FROM Usage WHERE Alias = @Alias AND (TickTime >= @StartDate AND TickTime <= @EndDate)";
command.Parameters.AddWithValue("@Alias", alias);
DateTime startDate = DateTime.MinValue;
DateTime endDate = DateTime.MinValue;
GetPeriodDates(period, ref startDate, ref endDate);
command.Parameters.AddWithValue("@StartDate", startDate.ToString());
command.Parameters.AddWithValue("@EndDate", endDate.ToString());
reader = command.ExecuteReader();
UserUsageSummary summary = new UserUsageSummary();
summary.Alias = alias;
summary.Period = period;
if (reader.Read())
{
if (!((reader["UsageMinutes"]) is DBNull))
{
summary.TotalHours = Convert.ToInt32(reader["UsageMinutes"]) / 60;
}
}
TimeSpan span = endDate - startDate;
if (span.Days == 0)
{
span = new TimeSpan(1, 0, 0, 0);
}
summary.AverageHours = summary.TotalHours / (float)span.Days;
return(summary);
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}