public List<PVTimeSeries> ProcessedTimeSeries(DataTable data, TimeInterval interval, DateTime startDate, DateTime endDate, string timestampColumnName) { // This method expects the dates to be rounded to their respected intervals List<PVTimeSeries> response = new List<PVTimeSeries>(); // Capture the series data that matches the record timestamp if (data.HasRows()) { int daysDifference = (endDate.Date - startDate.Date).Days + 1; DateTime minDate = startDate.Date; foreach (DataRow row in data.Rows) { DateTime currentDay = DateTime.Parse(row["RecordTimestamp"].ToString()).Date; // Iterate through the series keypairs for (int x = 1; x < data.Columns.Count; x += 2) { if (row[x].ToString() == "") { continue; } string seriesName = row[x].ToString(); string seriesValue = row[x + 1].ToString(); PVTimeSeries series = response.FirstOrDefault(pvts => pvts.name == seriesName); int daysBetween = (currentDay - minDate).Days; if (series == default(PVTimeSeries)) { series = new PVTimeSeries() { name = seriesName, data = new List<int>(), pointStart = ToMillisecondsUnixTime(startDate), pointInterval = 86400000 }; for (int z = 0; z < daysDifference; z++) { series.data.Add(0); } series.data[0] = Int32.Parse(seriesValue); response.Add(series); } else if (series.name != "" && series.name == seriesName) { int value = Int32.Parse(seriesValue); int index = daysBetween; series.data[index] = value; } } } } return response; }
public List<PVTimeSeries> UsersOnlineBySessionType(string gameShort, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == gameShort).FirstOrDefault(); List<PVTimeSeries> timeSeriesData = new List<PVTimeSeries>(); 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); if (queryResults.HasRows()) { foreach (DataRow row in queryResults.Rows) { foreach (DataColumn col in queryResults.Columns) { if ((col.ColumnName.Contains("SessionTypeName") || col.ColumnName == "Other") && !String.IsNullOrEmpty(row[col.ColumnName].ToString())) { int count = Convert.ToInt32(row[col.Ordinal + 1].ToString()); PVTimeSeries series = timeSeriesData.FirstOrDefault(x => x.name == row[col.ColumnName].ToString()); if (series == default(PVTimeSeries)) { series = new PVTimeSeries { name = row[col.ColumnName].ToString(), data = new List<int>(), pointStart = row.Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000, //JS unix timestamp is in milliseconds pointInterval = (int)interval * 60 * 1000, //JS unix timestamp is in milliseconds type = "area" }; int zerosCount = ((int)(endDate - startDate).TotalMinutes / (int)interval) + 1; for (int i = 0; i < zerosCount; i++) { series.data.Add(0); } timeSeriesData.Add(series); } else { DateTime timeStamp = row.Field<DateTime>("RecordTimestamp"); int index = (int)(timeStamp - startDate).TotalMinutes / (int)interval; series.data[index] = count; } } } } } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public List<PVTimeSeries> ReturnerTimeSeries(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate) { List<PVTimeSeries> SeriesList = new List<PVTimeSeries>(); string query = String.Format("SELECT Date, NURR, CURR, RURR from Retention as view WHERE view.Date BETWEEN '{0}' AND '{1}' order by view.Date desc", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); int daysDifference = 0; DataTable result = DBManager.Instance.Query(Datastore.Monitoring, query); if (result.HasRows()) { DateTime minDate = startDate.Date; daysDifference = (endDate.Date - startDate.Date).Days; foreach (DataRow row in result.Rows) { DateTime currentDay = DateTime.Parse(row["Date"].ToString()); int daysBetween = (currentDay - minDate).Days; PVTimeSeries NewReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "New Returns"); PVTimeSeries ContinuingReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "Continuing Returns"); PVTimeSeries ReactivatedReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "Reactivated Returns"); if (NewReturnsSeries == default(PVTimeSeries)) { NewReturnsSeries = new PVTimeSeries(); NewReturnsSeries.name = "New Returns"; NewReturnsSeries.data = new List<int>(); NewReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds NewReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day); NewReturnsSeries.type = "line"; for (int z = 0; z < daysDifference; z++) { NewReturnsSeries.data.Add(0); } SeriesList.Add(NewReturnsSeries); } if (ContinuingReturnsSeries == default(PVTimeSeries)) { ContinuingReturnsSeries = new PVTimeSeries(); ContinuingReturnsSeries.name = "Continuing Returns"; ContinuingReturnsSeries.data = new List<int>(); ContinuingReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds ContinuingReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day); ContinuingReturnsSeries.type = "line"; for (int z = 0; z < daysDifference; z++) { ContinuingReturnsSeries.data.Add(0); } SeriesList.Add(ContinuingReturnsSeries); } if (ReactivatedReturnsSeries == default(PVTimeSeries)) { ReactivatedReturnsSeries = new PVTimeSeries(); ReactivatedReturnsSeries.name = "Reactivated Returns"; ReactivatedReturnsSeries.data = new List<int>(); ReactivatedReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds ReactivatedReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day); ReactivatedReturnsSeries.type = "line"; for (int z = 0; z < daysDifference; z++) { ReactivatedReturnsSeries.data.Add(0); } SeriesList.Add(ReactivatedReturnsSeries); } int index = daysBetween; ReactivatedReturnsSeries.data[index] = (int)row.Field<decimal?>("RURR").GetValueOrDefault(-1); ContinuingReturnsSeries.data[index] = (int)row.Field<decimal?>("CURR").GetValueOrDefault(-1); NewReturnsSeries.data[index] = (int)row.Field<decimal?>("NURR").GetValueOrDefault(-1); } } return SeriesList; }
public List<PVTimeSeries> GetPrivateSessionTimeSeries(string gameShort, TimeInterval interval, DateTime startDate, DateTime endDate) { #region Validation GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == gameShort).FirstOrDefault(); #endregion List<PVTimeSeries> timeSeriesData = new List<PVTimeSeries>(); startDate = startDate.RoundDown(interval); endDate = endDate.RoundDown(interval); // Create a chart for each privacy comparison session type foreach (string sessionType in GetPrivateSessionCompareTypes(game)) { string query = String.Format( @"SELECT RecordTimestamp, 'Private', PrivateSessions, 'Non-Private', Sessions FROM {0} WHERE GameId = '{1}' AND SessionType = '{2}' AND RecordTimestamp BETWEEN '{3}' AND '{4}' GROUP BY RecordTimestamp ORDER BY RecordTimestamp;", String.Format("GameSessionUserStats{0}", interval.GetTimeIntervalString()), game.Id, sessionType, startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss")); try { // Get time series data DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); if (queryResults.HasRows()) { foreach (DataRow row in queryResults.Rows) { PVTimeSeries series = timeSeriesData.FirstOrDefault(x => x.name == row["Private"].ToString()); if (series == default(PVTimeSeries)) { series = new PVTimeSeries(); series.name = row["Private"].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"; timeSeriesData.Add(series); } PVTimeSeries nonPrivateSeries = timeSeriesData.FirstOrDefault(x => x.name == row["Non-Private"].ToString()); if (nonPrivateSeries == default(PVTimeSeries)) { nonPrivateSeries = new PVTimeSeries(); nonPrivateSeries.name = row["Non-Private"].ToString(); nonPrivateSeries.data = new List<int>(); nonPrivateSeries.pointStart = queryResults.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds nonPrivateSeries.pointInterval = (int)interval * 60 * 1000; //JS unix timestamp is in milliseconds nonPrivateSeries.type = "area"; timeSeriesData.Add(nonPrivateSeries); } series.data.Add(Convert.ToInt32(row["PrivateSessions"].ToString())); nonPrivateSeries.data.Add(Convert.ToInt32(row["Sessions"].ToString())); } } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } } return timeSeriesData; }
public List<PVTimeSeries> GetUsersByRegion(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<PVTimeSeries> timeSeriesData = new List<PVTimeSeries>(); // Init dates startDate = startDate.RoundDown(interval).ToUniversalTime(); endDate = endDate.RoundDown(interval).ToUniversalTime(); string query = String.Format( @"SELECT RecordTimestamp, RegionName, GameSessionUsers FROM {0} WHERE GameId = '{1}' AND RecordTimestamp BETWEEN '{2}' AND '{3}' ORDER BY RecordTimestamp, RegionName 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")); try { // Get time series data DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query); foreach (DataRow row in queryResults.Rows) { PVTimeSeries series = timeSeriesData.FirstOrDefault(x => x.name == row["RegionName"].ToString()); if (series == default(PVTimeSeries)) { series = new PVTimeSeries { name = row["RegionName"].ToString(), data = new List<int>(), pointStart = queryResults.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000, //JS unix timestamp is in milliseconds pointInterval = (int)interval * 60 * 1000, type = "area" }; int zerosCount = ((int)(endDate - startDate).TotalMinutes / (int)interval) + 1; for (int i = 0; i < zerosCount; i++) { series.data.Add(0); } timeSeriesData.Add(series); } DateTime timeStamp = row.Field<DateTime>("RecordTimestamp"); int index = (int)(timeStamp - startDate).TotalMinutes / (int)interval; series.data[index] = Convert.ToInt32(row["GameSessionUsers"].ToString()); } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return timeSeriesData; }
public List<PVTimeSeries<double>> GetDollarCostAveragePerDAU(GameMonitoringConfig gameMonitoringConfig, AWSRegion awsRegion, DateTime start, DateTime end) { int daysDifference = (end.Date - start.Date).Days + 1; DateTime minDate = start.Date; var summary = (new AWSModel()).FetchNetflixIceData(start, end); var retention = this.GetDailyActiveUsersByGame(gameMonitoringConfig, TimeInterval.Day, AWSRegion.All, start, end); List<PVTimeSeries<double>> allSeries = new List<PVTimeSeries<double>>(); var pvDAU = new PVTimeSeries<double>() { data = new List<double>(), name = "Daily Active Users", pointInterval = 24 * 60 * 60 * 1000, pointStart = minDate.ToUnixTimestamp() * 1000, type = "column" }; for (int i = 0; i < daysDifference; i++) { int dailyActiveUsers = retention[0].data[i]; pvDAU.data.Add(dailyActiveUsers); } allSeries.Add(pvDAU); var pvTimeSeries = new PVTimeSeries<double>() { data = new List<double>(), name = "Dollar Cost per DAU", pointInterval = 24 * 60 * 60 * 1000, pointStart = minDate.ToUnixTimestamp() * 1000, type = "line", yAxis = 1 }; for (int i = 0; i < daysDifference; i++) { double cost = summary.data.aggregated[i]; int dailyActiveUsers = retention[0].data[i]; double dollarCostPerDAU = dailyActiveUsers == 0 ? 0 : cost / dailyActiveUsers; pvTimeSeries.data.Add(dollarCostPerDAU); } allSeries.Add(pvTimeSeries); return allSeries; }
public List<PVTimeSeries> GetDailyActiveUsersByGame(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startdate, DateTime enddate) { DateTime minDate = startdate.Date; int daysDifference = (enddate.Date - startdate.Date).Days + 1; List<PVTimeSeries> result = new List<PVTimeSeries>(); string query = String.Format( @"SELECT RecordTimestamp ,Count FROM MonitoringStats WHERE Type = 'DailyActiveUsers' AND GameId = '{0}' AND DATE(RecordTimestamp) BETWEEN DATE('{1}') and DATE('{2}') ORDER BY RecordTimestamp;", game.Id, startdate.ToString("yyyy-MM-dd HH:mm:ss"), enddate.ToString("yyyy-MM-dd HH:mm:ss")); try { DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query); if (queryResult.HasRows()) { PVTimeSeries series = result.FirstOrDefault(x => x.name == "DailyActiveUsers"); if (series == default(PVTimeSeries)) { series = new PVTimeSeries(); series.name = "Users"; series.data = new List<int>(); series.pointStart = queryResult.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds series.pointInterval = 1440 * 60 * 1000; //JS unix timestamp is in milliseconds series.type = "column"; result.Add(series); for (int i = 0; i < daysDifference; i++) { series.data.Add(0); } } foreach (DataRow row in queryResult.Rows) { int index = (row.Field<DateTime>("RecordTimestamp").Date - minDate).Days; series.data[index] = Convert.ToInt32(row["Count"].ToString()); } } } catch (Exception ex) { Logger.Instance.Exception(ex.Message, ex.StackTrace); } return result; }
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; }