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;
}
