Exemplo n.º 1
0
        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;
        }
Exemplo n.º 2
0
        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;
        }
Exemplo n.º 3
0
        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;
        }
Exemplo n.º 4
0
        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;
        }