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;
                }
            }
        }
Example #4
0
        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;
                }
            }
        }
Example #5
0
        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;
                }
            }
        }
Example #6
0
        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;
                }
            }
        }