/// <summary>
/// Returns an array of UserUsageSummary items for a team
/// </summary>
/// <param name="pumAlias">The Pum for the team</param>
/// <param name="period">Which period we care about</param>
/// <returns></returns>
public static List<UserUsageSummary> GetTeamDetails(string pumAlias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
string cacheKey = "GetTeamDetails(" + pumAlias + "," + period.ToString() + ")";
if (HttpContext.Current.Cache[cacheKey] != null)
{
return (List<UserUsageSummary>)HttpContext.Current.Cache[cacheKey];
}
else
{
TeamUsageSummary teamSummary = new TeamUsageSummary();
teamSummary.PumAlias = pumAlias;
teamSummary.Period = period;
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT PT.* FROM PumTeam PT, Pum P WHERE PT.PumID = P.Id AND P.Alias = @Alias";
command.Parameters.AddWithValue("@Alias", pumAlias);
reader = command.ExecuteReader();
List<UserUsageSummary> userList = new List<UserUsageSummary>();
while (reader.Read())
{
string userAlias = Convert.ToString(reader["Alias"]);
userList.Add(GetUserUsage(userAlias, period));
}
HttpContext.Current.Cache.Add(cacheKey, userList, null, DateTime.Now.AddMinutes(59), TimeSpan.Zero, CacheItemPriority.Normal, null);
return 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 TeamUsageSummary GetTeamUsage(string pumAlias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
TeamUsageSummary teamSummary = new TeamUsageSummary();
teamSummary.PumAlias = pumAlias;
teamSummary.Period = period;
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT PT.Alias, (SELECT SUM(U.UsageMinutes) FROM Usage U WHERE U.Alias = PT.Alias AND U.TickTime >= @StartDate AND U.TickTime <= @EndDate) AS UsageMinutes FROM Pum P, PumTeam PT WHERE PT.PumId = P.Id AND P.Alias = @Alias ORDER BY UsageMinutes DESC";
command.Parameters.AddWithValue("@Alias", pumAlias);
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())
{
if (reader["UsageMinutes"] is DBNull)
{
teamSummary.OutCount++;
}
else
{
teamSummary.TotalHours += Convert.ToInt32(reader["UsageMinutes"]);
teamSummary.InCount++;
}
teamSummary.TeamCount++;
}
teamSummary.TotalHours /= 60;
teamSummary.AverageHours = (float)teamSummary.TotalHours / (float)teamSummary.TeamCount;
teamSummary.ParticipationRate = (int)(100.0f * ((float)teamSummary.InCount / (float)teamSummary.TeamCount));
return teamSummary;
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}
/// <summary>
/// Returns an array of UserUsageSummary items for a team
/// </summary>
/// <param name="pumAlias">The Pum for the team</param>
/// <param name="period">Which period we care about</param>
/// <returns></returns>
public static List <UserUsageSummary> GetTeamDetails(string pumAlias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
string cacheKey = "GetTeamDetails(" + pumAlias + "," + period + ")";
if (HttpContext.Current.Cache[cacheKey] != null)
{
return((List <UserUsageSummary>)HttpContext.Current.Cache[cacheKey]);
}
else
{
TeamUsageSummary teamSummary = new TeamUsageSummary();
teamSummary.PumAlias = pumAlias;
teamSummary.Period = period;
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
"SELECT PT.* FROM PumTeam PT, Pum P WHERE PT.PumID = P.Id AND P.Alias = @Alias";
command.Parameters.AddWithValue("@Alias", pumAlias);
reader = command.ExecuteReader();
List <UserUsageSummary> userList = new List <UserUsageSummary>();
while (reader.Read())
{
string userAlias = Convert.ToString(reader["Alias"]);
userList.Add(GetUserUsage(userAlias, period));
}
HttpContext.Current.Cache.Add(cacheKey, userList, null, DateTime.Now.AddMinutes(59), TimeSpan.Zero,
CacheItemPriority.Normal, null);
return(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 TeamUsageSummary GetTeamUsage(string pumAlias, UsagePeriod period)
{
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
TeamUsageSummary teamSummary = new TeamUsageSummary();
teamSummary.PumAlias = pumAlias;
teamSummary.Period = period;
connection = new SqlConnection(ServerSettings.SpeciesDsn);
connection.Open();
command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText =
"SELECT PT.Alias, (SELECT SUM(U.UsageMinutes) FROM Usage U WHERE U.Alias = PT.Alias AND U.TickTime >= @StartDate AND U.TickTime <= @EndDate) AS UsageMinutes FROM Pum P, PumTeam PT WHERE PT.PumId = P.Id AND P.Alias = @Alias ORDER BY UsageMinutes DESC";
command.Parameters.AddWithValue("@Alias", pumAlias);
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())
{
if (reader["UsageMinutes"] is DBNull)
{
teamSummary.OutCount++;
}
else
{
teamSummary.TotalHours += Convert.ToInt32(reader["UsageMinutes"]);
teamSummary.InCount++;
}
teamSummary.TeamCount++;
}
teamSummary.TotalHours /= 60;
teamSummary.AverageHours = teamSummary.TotalHours / (float)teamSummary.TeamCount;
teamSummary.ParticipationRate = (int)(100.0f * (teamSummary.InCount / (float)teamSummary.TeamCount));
return(teamSummary);
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
}
private int SortByTotalHours(TeamUsageSummary left, TeamUsageSummary right)
{
int value = left.TotalHours.CompareTo(right.TotalHours);
if (true == (bool)this.ViewState["SortAscending"])
{
return -1 * value;
}
else
{
return value;
}
}
private int SortByParticipationRate(TeamUsageSummary left, TeamUsageSummary right)
{
int value = left.ParticipationRate.CompareTo(right.ParticipationRate);
if (true == (bool)this.ViewState["SortAscending"])
{
return value;
}
else
{
return -1 * value;
}
}
private int SortByOutCount(TeamUsageSummary left, TeamUsageSummary right)
{
int value = left.OutCount.CompareTo(right.OutCount);
if (true == (bool)this.ViewState["SortAscending"])
{
return -1 * value;
}
else
{
return value;
}
}