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