public void RoundDown_Wit45Minutes_Succeeds()
{
var target = new DateTime(2016, 11, 1, 1, 33, 45);
var result = target.RoundDown(TimeSpan.FromMinutes(45));
(new DateTime(2016, 11, 1, 1,30, 0) - result).TotalMinutes.Should().Be(0);
}
public void GetStartTimeForTradeBarsRoundsDown()
{
// 2015.09.01 @ noon
var end = new DateTime(2015, 09, 01, 12, 0, 1);
var barSize = TimeSpan.FromMinutes(1);
var hours = SecurityExchangeHours.AlwaysOpen(TimeZones.NewYork);
var start = Time.GetStartTimeForTradeBars(hours, end, barSize, 1, false);
// round down and back up a single bar
Assert.AreEqual(end.RoundDown(barSize).Subtract(barSize), start);
}
public List<PVTimeSeries> AverageSessionLength(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, 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 ASC;",
"GameAverageSessionLength",
game.Id);
try
{
queryResults = DBManager.Instance.Query(Datastore.Monitoring, query);
timeSeriesData = Charts.Instance.ProcessedTimeSeries(queryResults, interval, startDate, endDate, "RecordTimestamp");
}
catch (Exception ex)
{
Logger.Instance.Exception(ex.Message, ex.StackTrace);
}
return timeSeriesData;
}
/// <summary>
/// Primary entry point.
/// </summary>
public void Run()
{
IsActive = true;
// we want to emit to the bridge minimally once a second since the data feed is
// the heartbeat of the application, so this value will contain a second after
// the last emit time, and if we pass this time, we'll emit even with no data
var nextEmit = DateTime.MinValue;
try
{
while (!_cancellationTokenSource.IsCancellationRequested)
{
// perform sleeps to wake up on the second?
_frontierUtc = _timeProvider.GetUtcNow();
_frontierTimeProvider.SetCurrentTime(_frontierUtc);
var data = new List<DataFeedPacket>();
foreach (var subscription in Subscriptions)
{
var packet = new DataFeedPacket(subscription.Security, subscription.Configuration);
// dequeue data that is time stamped at or before this frontier
while (subscription.MoveNext() && subscription.Current != null)
{
packet.Add(subscription.Current);
}
// if we have data, add it to be added to the bridge
if (packet.Count > 0) data.Add(packet);
// we have new universe data to select based on
if (subscription.IsUniverseSelectionSubscription && packet.Count > 0)
{
var universe = subscription.Universe;
// always wait for other thread to sync up
if (!_bridge.WaitHandle.WaitOne(Timeout.Infinite, _cancellationTokenSource.Token))
{
break;
}
// assume that if the first item is a base data collection then the enumerator handled the aggregation,
// otherwise, load all the the data into a new collection instance
var collection = packet.Data[0] as BaseDataCollection ?? new BaseDataCollection(_frontierUtc, subscription.Configuration.Symbol, packet.Data);
_changes += _universeSelection.ApplyUniverseSelection(universe, _frontierUtc, collection);
}
}
// check for cancellation
if (_cancellationTokenSource.IsCancellationRequested) return;
// emit on data or if we've elapsed a full second since last emit
if (data.Count != 0 || _frontierUtc >= nextEmit)
{
_bridge.Add(TimeSlice.Create(_frontierUtc, _algorithm.TimeZone, _algorithm.Portfolio.CashBook, data, _changes), _cancellationTokenSource.Token);
// force emitting every second
nextEmit = _frontierUtc.RoundDown(Time.OneSecond).Add(Time.OneSecond);
}
// reset our security changes
_changes = SecurityChanges.None;
// take a short nap
Thread.Sleep(1);
}
}
catch (Exception err)
{
Log.Error(err);
_algorithm.RunTimeError = err;
}
Log.Trace("LiveTradingDataFeed.Run(): Exited thread.");
IsActive = false;
}
/// <summary>
/// Determines the start time required to produce the requested number of bars and the given size
/// </summary>
/// <param name="exchange">The exchange used to test for market open hours</param>
/// <param name="end">The end time of the last bar over the requested period</param>
/// <param name="barSize">The length of each bar</param>
/// <param name="barCount">The number of bars requested</param>
/// <param name="extendedMarketHours">True to allow extended market hours bars, otherwise false for only normal market hours</param>
/// <returns>The start time that would provide the specified number of bars ending at the specified end time, rounded down by the requested bar size</returns>
public static DateTime GetStartTimeForTradeBars(SecurityExchangeHours exchange, DateTime end, TimeSpan barSize, int barCount, bool extendedMarketHours)
{
if (barSize <= TimeSpan.Zero)
{
throw new ArgumentException("barSize must be greater than TimeSpan.Zero", "barSize");
}
var current = end.RoundDown(barSize);
for (int i = 0; i < barCount;)
{
var previous = current;
current = current - barSize;
if (exchange.IsOpen(current, previous, extendedMarketHours))
{
i++;
}
}
return current;
}
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<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> 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 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> 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<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 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;
}
