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<PVTableRow> ProcessedSessionLengthData(DataTable data, TimeInterval interval, DateTime startDate, DateTime endDate, string timestampColumnName) { // This method expects the dates to be rounded to their respected intervals //init return list List<PVTableRow> response = new List<PVTableRow>() { }; // Capture the series data that matches the record timestamp if (data.HasRows()) { List<object> SeriesNames = data.AsEnumerable().Select(r => r["SeriesName"]).Distinct().ToList(); foreach (DataRow row in data.Rows) { PVTableRow tablerow = new PVTableRow() { data = new List<PlaytricsPair>(), index = row["RecordTimestamp"].ToString() }; foreach (string sessionName in SeriesNames) { PlaytricsPair sessionLength = new PlaytricsPair() { name = sessionName, value = 0 }; tablerow.data.Add(sessionLength); } // Iterate through the series keypairs response.Add(tablerow); } } return response; }
public TimeSeriesData GetTimeSeriesData(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 TimeSeriesData result = new TimeSeriesData() { CategoryEntries = new List<string>(), SeriesData = new Dictionary<string, List<object>>(), StartDate = startDate, EndDate = endDate }; // 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()); } break; case TimeInterval.Week: for (int i = 0; startDate.AddDays(i) <= endDate; i += 7) { result.CategoryEntries.Add(startDate.AddDays(i).ToString()); } break; case TimeInterval.Month: for (int i = 0; startDate.AddMonths(i) <= endDate; i++) { result.CategoryEntries.Add(startDate.AddMonths(i).ToString()); } break; case TimeInterval.QuarterYear: for (int i = 0; startDate.AddMonths(i) <= endDate; i += 3) { result.CategoryEntries.Add(startDate.AddMonths(i).ToString()); } break; case TimeInterval.Biannual: for (int i = 0; startDate.AddMonths(i) <= endDate; i += 6) { result.CategoryEntries.Add(startDate.AddMonths(i).ToString()); } break; case TimeInterval.Year: for (int i = 0; startDate.AddYears(i) <= endDate; i++) { result.CategoryEntries.Add(startDate.AddYears(i).ToString()); } 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<object> objectList = new List<object>(); for (int y = 0; y < result.CategoryEntries.Count; y++) { objectList.Add(0); } objectList[i] = seriesValue; result.SeriesData.Add(seriesName, objectList); } else { // Add to the existing series data set result.SeriesData[seriesName][i] = seriesValue; } } } } } return result; }
public List<PVTableRow> ProcessedDataTable(DataTable data, TimeInterval interval, DateTime startDate, DateTime endDate, string timestampColumnName) { // This method expects the dates to be rounded to their respected intervals //init return list List<PVTableRow> response = new List<PVTableRow>(); // Capture the series data that matches the record timestamp if (data.HasRows()) { foreach (DataRow row in data.Rows) { List<PlaytricsPair> rowdata = new List<PlaytricsPair>(); // 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(); PlaytricsPair datapoint = new PlaytricsPair() { name = seriesName, value = (String.Format("{0} seconds", Int32.Parse(seriesValue) / 1000)) }; rowdata.Add(datapoint); } PVTableRow tablerow = new PVTableRow() { data = rowdata, index = row["RecordTimestamp"].ToString() }; response.Add(tablerow); } } 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; }