public List<PVTableRow> GetAverageSessionLengthTable(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { #region Validation if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) { throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); } if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate)) { throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); } if (String.IsNullOrEmpty(game.Id)) { throw new Exception("GameID cannot be empty or null"); } #endregion List<PVTableRow> dataTableData = new List<PVTableRow>(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"select DATE(RecordCreated) as RecordTimeStamp, SessionTypeFriendly as SeriesName, round(avg(minute(timediff(RecordLastUpdateTime, RecordCreated)))) as AverageSessionLength from {0} WHERE GameId = '{1}' AND DATE(RecordCreated) BETWEEN '{2}' and '{3}' AND minute(timediff(RecordLastUpdateTime, RecordCreated)) > 1 group by DATE(RecordCreated), SessionTypeFriendly order by RecordCreated asc;", "GameSessionMeta", game.Id, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); try { queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); dataTableData = Charts.Instance.ProcessedSessionLengthData(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return dataTableData; }
public List<PVTableRow> GetAverageSessionLengthTable(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { #region Validation if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) { throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); } if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate)) { throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); } if (String.IsNullOrEmpty(game.Id)) { throw new Exception("GameID cannot be empty or null"); } #endregion List<PVTableRow> dataTableData = new List<PVTableRow>(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"SELECT RecordTimestamp, SessionTypeName_0, AverageSessionTypeLength_0, SessionTypeName_1, AverageSessionTypeLength_1, SessionTypeName_2, AverageSessionTypeLength_2, SessionTypeName_3, AverageSessionTypeLength_3, SessionTypeName_4, AverageSessionTypeLength_4, SessionTypeName_5, AverageSessionTypeLength_5, SessionTypeName_6, AverageSessionTypeLength_6, SessionTypeName_7, AverageSessionTypeLength_7 FROM GameAverageSessionLength WHERE GameId = '{1}' GROUP BY RecordTimestamp, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7 ORDER BY RecordTimestamp DESC;", "GameAverageSessionLength", game.Id); try { queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); dataTableData = Charts.Instance.ProcessedDataTable(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return dataTableData; }
public TimeSeriesDataNew GetConcurrentUsersSessionType(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game) { #region Validation if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) { throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); } //if (region != 0) { // throw new Exception("write check for valid region"); //} if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate)) { throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); } if (String.IsNullOrEmpty(game.Id)) { throw new Exception("GameID cannot be empty or null"); } #endregion TimeSeriesDataNew timeSeriesData = new TimeSeriesDataNew(); DataTable queryResults = new DataTable(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); string query = String.Format( @"SELECT RecordTimestamp, SessionTypeName_0, SUM(SessionTypeUsers_0) AS SessionTypeUsers_0, SessionTypeName_1, SUM(SessionTypeUsers_1) AS SessionTypeUsers_1, SessionTypeName_2, SUM(SessionTypeUsers_2) AS SessionTypeUsers_2, SessionTypeName_3, SUM(SessionTypeUsers_3) AS SessionTypeUsers_3, SessionTypeName_4, SUM(SessionTypeUsers_4) AS SessionTypeUsers_4, SessionTypeName_5, SUM(SessionTypeUsers_5) AS SessionTypeUsers_5, SessionTypeName_6, SUM(SessionTypeUsers_6) AS SessionTypeUsers_6, SessionTypeName_7, SUM(SessionTypeUsers_7) AS SessionTypeUsers_7, Other, SUM(SessionTypeUsers_Other) AS SessionTypeUsers_Other FROM ( SELECT RecordTimestamp, RegionName, SessionTypeName_0, ROUND(AVG(SessionTypeUsers_0)) AS SessionTypeUsers_0, SessionTypeName_1, ROUND(AVG(SessionTypeUsers_1)) AS SessionTypeUsers_1, SessionTypeName_2, ROUND(AVG(SessionTypeUsers_2)) AS SessionTypeUsers_2, SessionTypeName_3, ROUND(AVG(SessionTypeUsers_3)) AS SessionTypeUsers_3, SessionTypeName_4, ROUND(AVG(SessionTypeUsers_4)) AS SessionTypeUsers_4, SessionTypeName_5, ROUND(AVG(SessionTypeUsers_5)) AS SessionTypeUsers_5, SessionTypeName_6, ROUND(AVG(SessionTypeUsers_6)) AS SessionTypeUsers_6, SessionTypeName_7, ROUND(AVG(SessionTypeUsers_7)) AS SessionTypeUsers_7, 'Other', ROUND(AVG(SessionTypeUsers_Other)) AS SessionTypeUsers_Other FROM {0} WHERE GameId = '{1}' AND RecordTimestamp BETWEEN '{2}' AND '{3}' AND RegionName like '{4}' GROUP BY RecordTimestamp, RegionName, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7, 'Other' ) AGGSESSIONS GROUP BY RecordTimestamp, SessionTypeName_0, SessionTypeName_1, SessionTypeName_2, SessionTypeName_3, SessionTypeName_4, SessionTypeName_5, SessionTypeName_6, SessionTypeName_7, Other ORDER BY RecordTimestamp ASC;", String.Format("GameUserActivity{0}", interval.ToDbTableString()), game.Id, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"), region.GetDatabaseString()); try { // Get time series data queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); timeSeriesData = Charts.Instance.GetTimeSeriesNewData(queryResults, interval, startDate, endDate, "RecordTimestamp"); } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public List<PVTimeSeries> GetCurrentOnline(TimeInterval interval, DateTime start, DateTime end, GameMonitoringConfig game) { #region Validation if (!interval.IsSupportedInterval(TimeInterval.Minute, TimeInterval.Year)) { throw new Exception(String.Format("Chart data only supports an interval between {0} and {1}", TimeInterval.Day, TimeInterval.Year)); } if (start == DateTime.MinValue || end == DateTime.MinValue || (start >= end)) { throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate"); } start = start.RoundDown(interval).ToUniversalTime(); end = end.RoundDown(interval).ToUniversalTime(); #endregion string query = String.Format( @"SELECT RecordTimestamp, Title, sum(GameSessionUsers) as GameSessionUsers FROM ( SELECT RecordTimestamp, GMC.Title, GUA.RegionName, round(avg(GameSessionUsers)) as GameSessionUsers FROM {0} GUA INNER JOIN GameMonitoringConfig GMC ON GUA.GameId = GMC.GameId WHERE RecordTimestamp BETWEEN '{1}' AND '{2}' AND GMC.GameId = '{3}' GROUP BY RecordTimestamp, GMC.Title, GUA.RegionName ) USERS GROUP BY RecordTimestamp, Title ORDER BY RecordTimestamp ASC, Title ASC;", String.Format("GameUserActivity{0}", interval.ToDbTableString()), start.ToString("yyyy-MM-dd HH:mm:ss"), end.ToString("yyyy-MM-dd HH:mm:ss"), game.Id); List<PVTimeSeries> SeriesList = new List<PVTimeSeries>(); try { // Get time series data DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); foreach (DataRow row in queryResults.Rows) { PVTimeSeries series = SeriesList.FirstOrDefault(x => x.name == row["Title"].ToString()); if (series == default(PVTimeSeries)) { series = new PVTimeSeries(); series.name = row["Title"].ToString(); series.data = new List<int>(); series.pointStart = queryResults.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds series.pointInterval = (int)interval * 60 * 1000; //JS unix timestamp is in milliseconds series.type = "area"; int zerosCount = ((int)(end - start).TotalMinutes / (int)interval) + 1; for (int i = 0; i < zerosCount; i++) { series.data.Add(0); } SeriesList.Add(series); } DateTime timeStamp = row.Field<DateTime>("RecordTimestamp"); int index = (int)(timeStamp - start).TotalMinutes / (int)interval; series.data[index] = Convert.ToInt32(row["GameSessionUsers"].ToString()); } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return SeriesList; }