public JsonResult ReturnersReport()
{
//generating mock data for the graph
//get the date
DateTime today = DateTime.UtcNow;
//init the table
//init the lists of HighCharts configured points for
//New Users Returns
//Continuing User returns
//Returning user returns (lol)
List<PlaytricsPoint> NURRList = new List<PlaytricsPoint>();
List<PlaytricsPoint> CURRList = new List<PlaytricsPoint>();
List<PlaytricsPoint> RURRList = new List<PlaytricsPoint>();
//this object has a contract with the Series javascript object.
//they friends
TimeSeriesDataNew tsd = new TimeSeriesDataNew();
// for each row returned fill out the return
List<ReturnerRow> ReturnerTable = RetentionModel.Instance.GetReturnerRetention(today);
tsd.EndDate = ReturnerTable.OrderByDescending(x => x.Date).First().Date;
tsd.StartDate = ReturnerTable.OrderByDescending(x => x.Date).Last().Date;
foreach (ReturnerRow row in ReturnerTable.ToList())
{
//CreateActionInvoker high Charts configured points
// [x,y] (s)datetime, (Count)]
PlaytricsPoint CURR = new PlaytricsPoint();
PlaytricsPoint NURR = new PlaytricsPoint();
PlaytricsPoint RURR = new PlaytricsPoint();
//need to make the date format in the correct timezone encoded JSON friendly format
// .toJSON() in javascriptland
CURR.RecordTimestamp = row.Date;
CURR.Count = row.CURR;
CURRList.Add(CURR);
NURR.RecordTimestamp = row.Date;
NURR.Count = row.NURR;
NURRList.Add(NURR);
RURR.RecordTimestamp = row.Date;
RURR.Count = row.RURR;
RURRList.Add(RURR);
}
Dictionary<string, List<PlaytricsPoint>> serieses = new Dictionary<string, List<PlaytricsPoint>>() {
{"NURR", NURRList},
{"CURR", CURRList},
{"RURR", RURRList}
};
tsd.SeriesData = serieses;
ReturningRetentionResponse RRresponse = new ReturningRetentionResponse();
RRresponse.Chart = tsd;
RRresponse.Table = ReturnerTable;
return JsonResult( RRresponse );
}
public TimeSeriesDataNew GetTimeSeriesNewData(DataTable data, TimeInterval interval, DateTime startDate, DateTime endDate, string timestampColumnName)
{
// This method expects the dates to be rounded to their respected intervals
#region Validation
if (String.IsNullOrEmpty(timestampColumnName))
{
throw new Exception("Timestamp column name supplied cannot be null or empty");
}
if (!(data.Columns[0].ColumnName == timestampColumnName))
{
throw new Exception("Invalid data set. First column must be of type DateTime with same name as the passed in column name");
}
if (data.Columns.Count <= 1 || ((data.Columns.Count - 1) % 2) != 0)
{
throw new Exception("Invalid column sequence. Sequence of columns after first (record timestamp) should be String, Object, String, Object, etc.");
}
if (startDate == DateTime.MinValue || endDate == DateTime.MinValue || (startDate >= endDate))
{
throw new Exception("StartDate and EndDate cannot be null, and StartDate must come before EndDate");
}
#endregion Validation
// Init return object
TimeSeriesDataNew result = new TimeSeriesDataNew()
{
CategoryEntries = new List<string>(),
SeriesData = new Dictionary<string, List<PlaytricsPoint>>(),
StartDate = startDate.ToString("yyyy-MM-ddTHH:mm:ssZ"),
EndDate = endDate.ToString("yyyy-MM-ddTHH:mm:ssZ")
};
// Capture the record timestamp category entries
switch (interval)
{
case TimeInterval.Minute:
case TimeInterval.ThreeMinutes:
case TimeInterval.FiveMinutes:
case TimeInterval.TenMinutes:
case TimeInterval.FifteenMinutes:
case TimeInterval.ThirtyMinutes:
case TimeInterval.Hour:
case TimeInterval.ThreeHours:
case TimeInterval.SixHours:
case TimeInterval.TwelveHours:
case TimeInterval.Day:
TimeSpan timeInterval = TimeSpan.FromMinutes((int)interval);
for (long i = 0; startDate.AddTicks(i).Ticks <= endDate.Ticks; i += timeInterval.Ticks)
{
result.CategoryEntries.Add(startDate.AddTicks(i).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
case TimeInterval.Week:
for (int i = 0; startDate.AddDays(i) <= endDate; i += 7)
{
result.CategoryEntries.Add(startDate.AddDays(i).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
case TimeInterval.Month:
for (int i = 0; startDate.AddMonths(i) <= endDate; i++)
{
result.CategoryEntries.Add(startDate.AddMonths(i).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
case TimeInterval.QuarterYear:
for (int i = 0; startDate.AddMonths(i) <= endDate; i += 3)
{
result.CategoryEntries.Add(startDate.AddMonths(i).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
case TimeInterval.Biannual:
for (int i = 0; startDate.AddMonths(i) <= endDate; i += 6)
{
result.CategoryEntries.Add(startDate.AddMonths(i).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
case TimeInterval.Year:
for (int i = 0; startDate.AddYears(i) <= endDate; i++)
{
result.CategoryEntries.Add(startDate.AddYears(i).ToString("yyyy-MM-dd HH:mm:ss"));
}
break;
}
// Capture the series data that matches the record timestamp
if (data.HasRows())
{
for (int i = 0; i < result.CategoryEntries.Count; i++)
{
foreach (DataRow row in data.Select(String.Format("{0} = '{1}'", timestampColumnName, result.CategoryEntries[i])))
{
// Iterate through the series keypairs
for (int x = 1; x < data.Columns.Count; x += 2)
{
//if (row[x] == null || String.IsNullOrEmpty(row[x].ToString()))
//{
// break;
//}
string seriesName = row[x].ToString();
string seriesValue = row[x + 1].ToString();
if (!result.SeriesData.ContainsKey(seriesName))
{
// If the series was not in the data set, add it
List<PlaytricsPoint> PointList = new List<PlaytricsPoint>();
for (int y = 0; y < result.CategoryEntries.Count; y++)
{
PlaytricsPoint point = new PlaytricsPoint()
{
RecordTimestamp = result.CategoryEntries[y],
Count = 0
};
PointList.Add(point);
}
PointList[i].Count = seriesValue;
if (seriesName != "")
{
result.SeriesData.Add(seriesName, PointList);
}
//if (!PointList.All(p => p.Count.Equals(0)))
//{
// result.SeriesData.Add(seriesName, PointList);
//}
}
else
{
// Add to the existing series data set
result.SeriesData[seriesName][i].Count = seriesValue;
}
}
}
}
}
return result;
}
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;
}
