public void CaptureTransactionsForLastFullDay(GameMonitoringConfig game)
        {
            DateTime end = DateTime.Now.Date;
            DateTime start = end.AddDays(-1);
            List<PlayversePurchaseAggregation> purchaseDays = GetPurchaseTransactions(game.Id, start, end);

            RecordPurchaseTransactionAggregation(purchaseDays);
        }
        public void CheckActiveUserDelta(GameMonitoringConfig game)
        {
            //MoniverseNotification notification = UserNotification.ActiveUserDeltaThreshold(game);
            MoniverseNotification notification = UserNotification.ActiveUserDeltaThreshold(game);

            List<INotifier> NotifierList;

            if (!UserNotification.RunningNotifications.TryGetValue(game.ShortTitle, out NotifierList))
            {
                NotifierList = new List<INotifier>();
                UserNotification.RunningNotifications.TryAdd(game.ShortTitle, NotifierList);
            }

            if (notification.ShouldSend)
            {
                if (UserNotification.RunningNotifications.TryGetValue(game.ShortTitle, out NotifierList))
                {
                    if (NotifierList.Exists(x => {
                        Notifier notifier = (Notifier)x;
                        return notifier.NotificationId == notification.Id;
                    }))
                    {
                        return;
                    }
                    else
                    {
                        INotifier OnlineUserCount = new Notifier(game.Id, MessageTopic.Users, notification);
                        NotifierList.Add(OnlineUserCount);

                        int counter = 0;
                        int interval = 1000 * 60 * 5;
                        int notifyCount = 3;

                        OnlineUserCount.SendTick(() =>
                         {

                             MoniverseNotification ThresholdCheck = UserNotification.CheckOnlineUserCount(game, notifyCount - counter - 1, interval);
                             OnlineUserCount.setNotification(ThresholdCheck);

                             if (counter >= notifyCount)
                                 ThresholdCheck.ShouldSend = false;

                             if (!ThresholdCheck.ShouldSend) {
                                 UserNotification.RunningNotifications.TryGetValue(game.ShortTitle, out NotifierList);
                                 NotifierList.Remove(OnlineUserCount);
                             }
                             counter++;
                             return ThresholdCheck.ShouldSend;

                         }, interval);
                    }

                }
            }
        }
        public List<PlayversePurchaseAggregation> getTransactionsForGameFromRange(GameMonitoringConfig Game, DateTime start, DateTime end)
        {
            //GameShortTitle = GameShortTitle.ToUpper();

            //GameMonitoringConfig game = Games.Instance.GetMonitoredGames().Where(x => x.ShortTitle == GameShortTitle).FirstOrDefault();
            //if (game == null) {
            //    throw new Exception("No Game by that name");
            //}

            return GetPurchaseTransactions(Game.Id, start, end);
        }
        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 void CaptureLatest(GameMonitoringConfig game)
        {
            List<PlayversePurchaseAggregation> purchaseDays = GetLatestTransactions(game.Id);

            RecordLatestTransactions(purchaseDays);
        }
        public static MoniverseNotification ActiveUserDeltaThreshold(GameMonitoringConfig game)
        {
            MoniverseNotification notification = new MoniverseNotification()
            {
                Id = "UserDelta",
                ShouldSend = false
            };

            string subject = "{0} Users Online Dropped {1}";
            string message = "[{0} Alert {1}] | Game: {2} - Number of Users Online Dropped by {3} over past {4} minutes which is over the threshold of {5}";
            Logger.Instance.Info(String.Format("Check User Delta for {0}", game.Id));

            // Get last three user count snapshots
            string query = String.Format(
                @"SELECT RecordTimestamp,
                        SUM(GameSessionUsers) AS GameSessionUsers
                FROM (
                SELECT	DATE_SUB(RecordTimestamp, INTERVAL (IFNULL(HOUR(RecordTimestamp) % FLOOR(3 / 60), 0) * 60 * 60) + ((MINUTE(RecordTimestamp) % 3) * 60) + SECOND(RecordTimestamp) SECOND) AS RecordTimestamp,
                        ROUND(AVG(GameSessionUsers)) AS GameSessionUsers,
                        RegionName
                FROM GameUserActivity
                WHERE GameId = '{0}'
                AND DATE_SUB(RecordTimestamp, INTERVAL (IFNULL(HOUR(RecordTimestamp) % FLOOR(3 / 60), 0) * 60 * 60) + ((MINUTE(RecordTimestamp) % 3) * 60) + SECOND(RecordTimestamp) SECOND) > UTC_TIMESTAMP() - INTERVAL 10 MINUTE
                GROUP BY DATE_SUB(RecordTimestamp, INTERVAL (IFNULL(HOUR(RecordTimestamp) % FLOOR(3 / 60), 0) * 60 * 60) + ((MINUTE(RecordTimestamp) % 3) * 60) + SECOND(RecordTimestamp) SECOND),
                        RegionName
                ) USERS
                GROUP BY RecordTimestamp
                ORDER BY RecordTimestamp DESC
                LIMIT 0, 3;",
                game.Id);

            DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query);
            if (!queryResults.HasRows() || queryResults.Rows.Count != 3)
            {
                if (game.IsNotificationSettingEnabled(NotificationLevel.PVSupport))
                {

                    new Notifier(Games.EMPTYGAMEID, MessageTopic.Error, new MoniverseNotification()
                    {
                        Id = "DBERROR",
                        Message = "Not Enough User Rows returned in Online User Check",
                        Subject = "Online User Check Failed"
                    }).SendOnce();

                    Logger.Instance.Info(String.Format("Not Enough User Rows Returned", game.Id));
                }
            }

            int currentUsers = (int)queryResults.Rows[0].Field<decimal>("GameSessionUsers");
            int users_3MinsAgo = (int)queryResults.Rows[1].Field<decimal>("GameSessionUsers");
            int users_6MinsAgo = (int)queryResults.Rows[2].Field<decimal>("GameSessionUsers");
            string recordTimestamp = queryResults.Rows[0].Field<DateTime>("RecordTimestamp").ToString();

            // Check notification thresholds and send notification of delta warning or error is necessary
            float sixMinDropPercent = 1.0f - ((float)currentUsers / (float)users_6MinsAgo);
            float threeMinDropPercent = 1.0f - ((float)currentUsers / (float)users_3MinsAgo);

            string sixMinuteDropF = Math.Abs((decimal)(sixMinDropPercent * 100)).ToString("#.##\\%");

            //
            if (sixMinDropPercent > game.ActiveUserDeltaThresholdPct_6Min)
            {
                notification.ShouldSend = true;
                notification.Message = String.Format(message, NotificationLevel.Error.ToString(), recordTimestamp, game.Title, sixMinuteDropF, 6, game.ActiveUserDeltaThresholdPct_6Min);
                notification.Subject = String.Format(subject, game.ShortTitle, sixMinuteDropF);
            }
            else if (threeMinDropPercent > game.ActiveUserDeltaThresholdPct_6Min)
            {
                notification.ShouldSend = true;
                notification.Message = String.Format(message, NotificationLevel.Error.ToString(), recordTimestamp, game.Title, sixMinuteDropF, 6, game.ActiveUserDeltaThresholdPct_6Min);
                notification.Subject = String.Format(subject, game.ShortTitle, sixMinuteDropF);
            }
            else if (threeMinDropPercent > game.ActiveUserDeltaThresholdPct_3Min)
            {
                notification.ShouldSend = true;
                notification.Message = String.Format(message, NotificationLevel.Error.ToString(), recordTimestamp, game.Title, sixMinuteDropF, 6, game.ActiveUserDeltaThresholdPct_6Min);
                notification.Subject = String.Format(subject, game.ShortTitle, sixMinuteDropF);
            }

            return notification;
        }
        public void CaptureGameCreditTransactions(GameMonitoringConfig game)
        {
            List<PlayverseGameCreditTransaction> data = GetGameCreditTransactions(game.Id);

            RecordGameCreditTransactions(data);
        }
        public void RecordActiveUsers(GameMonitoringConfig game)
        {
            try
            {
                if (game == null)
                {
                    throw new Exception("GameInfo cannot be null or empty");
                }

                // Init the session type specific collections
                string[] sessionTypes = game.ActiveUserSessionTypes.ToArray();
                string[] sessionTypeNames = new string[8];
                for (int i = 0; i < 8; i++)
                {
                    if (i < sessionTypes.Count())
                    {
                        sessionTypeNames[i] = sessionTypes[i];
                    }
                    else
                    {
                        sessionTypeNames[i] = String.Empty;
                    }
                }

                // Grab regional counts
                DataTable queryResults = DBManager.Instance.Query(Datastore.General, GetSessionTypeUserCountByRegionQueryStr(game.Id, sessionTypes.ToList()));
                if (queryResults.HasRows())
                {
                    List<RegionSessionTypeCounts> gameSessionUserResults = new List<RegionSessionTypeCounts>();
                    foreach (DataRow row in queryResults.Rows)
                    {
                        gameSessionUserResults.Add(new RegionSessionTypeCounts()
                        {
                            RegionName = row.Field<string>("RegionName"),
                            SessionType = row.Field<string>("SessionType"),
                            Count = (int)row.Field<long>("Count")
                        });
                    }

                    // Grab global playverse game counts
                    int eventListeners = DBManager.Instance.QueryForCount(Datastore.General, GetEventListenersCountQueryStr(game.Id));
                    int titleScreenUsers = eventListeners - gameSessionUserResults.Sum(x => x.Count);
                    int otherSessionTypeUsers = 0;
                    DateTime timestamp = DateTime.UtcNow;

                    // Grab the region counts and insert the region specific metric
                    foreach (IGrouping<string, RegionSessionTypeCounts> region in gameSessionUserResults.GroupBy(x => x.RegionName))
                    {
                        int gameSessionUsers = region.Sum(x => x.Count);
                        int[] sessionTypeUserCounts = new int[8];
                        for (int i = 0; i < 8; i++)
                        {
                            if (sessionTypeNames[i] != String.Empty)
                            {
                                RegionSessionTypeCounts regionSessionType = region.Where(x => x.SessionType == sessionTypeNames[i]).FirstOrDefault();
                                if (regionSessionType != null)
                                {
                                    sessionTypeUserCounts[i] = regionSessionType.Count;
                                }
                            }
                        }

                        // Grab the other session type region count
                        RegionSessionTypeCounts otherRegionSessionType = region.Where(x => x.SessionType == "Other").FirstOrDefault();
                        if (otherRegionSessionType != null)
                        {
                            otherSessionTypeUsers = otherRegionSessionType.Count;
                        }

                        // Store new game user activity record
                        string insertQuery = String.Format(
                            @"INSERT INTO GameUserActivity
                            (GameId, RegionName, RecordTimestamp, GameSessionUsers, EventListeners, TitleScreenUsers, SessionTypeName_0, SessionTypeUsers_0,
                            SessionTypeName_1, SessionTypeUsers_1, SessionTypeName_2, SessionTypeUsers_2, SessionTypeName_3, SessionTypeUsers_3,
                            SessionTypeName_4, SessionTypeUsers_4, SessionTypeName_5, SessionTypeUsers_5, SessionTypeName_6, SessionTypeUsers_6,
                            SessionTypeName_7, SessionTypeUsers_7, SessionTypeUsers_Other)
                            VALUES('{0}','{1}','{2}',{3},{4},{5},'{6}',{7},'{8}',{9},'{10}',{11},'{12}',{13},'{14}',{15},'{16}',{17},'{18}',{19},'{20}',{21}, {22})",
                                    game.Id,
                                    region.Key,
                            //truncated seconds
                                    timestamp.ToString("yyyy-MM-dd HH:mm"),
                                    gameSessionUsers,
                                    eventListeners,
                                    titleScreenUsers,
                                    sessionTypeNames[0],
                                    sessionTypeUserCounts[0],
                                    sessionTypeNames[1],
                                    sessionTypeUserCounts[1],
                                    sessionTypeNames[2],
                                    sessionTypeUserCounts[2],
                                    sessionTypeNames[3],
                                    sessionTypeUserCounts[3],
                                    sessionTypeNames[4],
                                    sessionTypeUserCounts[4],
                                    sessionTypeNames[5],
                                    sessionTypeUserCounts[5],
                                    sessionTypeNames[6],
                                    sessionTypeUserCounts[6],
                                    sessionTypeNames[7],
                                    sessionTypeUserCounts[7],
                                    otherSessionTypeUsers);
                        try
                        {
                            MoniverseResponse response = new MoniverseResponse()
                            {
                                Status = "unsent",
                                TimeStamp = DateTime.UtcNow
                            };

                            lock (MoniverseBase.ConsoleWriterLock)
                            {
                                Console.ForegroundColor = ConsoleColor.Cyan;
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("Beginning Insert of Active Users");
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("");
                            }
                            Users.Service(Service =>
                            {
                                response = Service.Insert(new MoniverseRequest()
                                {
                                    TaskName = "Insert RecordActiveUsers",
                                    Task = insertQuery,
                                    TimeStamp = DateTime.UtcNow
                                });
                            });
                            //int result = DBManager.Instance.Insert(Datastore.Monitoring, insertQuery);

                            lock (MoniverseBase.ConsoleWriterLock)
                            {
                                Console.ForegroundColor = ConsoleColor.Green;
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("Active User Success");
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("");
                                Logger.Instance.Info("Done inserting Retention Row Data.");
                                Logger.Instance.Info(String.Format("success!! {0}", response.Status));
                                Logger.Instance.Info("");
                                Console.ResetColor();
                            }

                        }
                        catch (Exception e)
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Logger.Instance.Info(e.Message);
                            Console.ResetColor();
                        }

                    }
                }

            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(String.Format("Game: {0} - {1} | Message: {2}", game.Id, game.Title, ex.Message), ex.StackTrace);
            }
        }
        public List<PVTimeSeries<double>> GetDollarCostAveragePerDAU(GameMonitoringConfig gameMonitoringConfig, AWSRegion awsRegion, DateTime start, DateTime end)
        {
            int daysDifference = (end.Date - start.Date).Days + 1;

            DateTime minDate = start.Date;

            var summary = (new AWSModel()).FetchNetflixIceData(start, end);
            var retention = this.GetDailyActiveUsersByGame(gameMonitoringConfig, TimeInterval.Day, AWSRegion.All, start, end);

            List<PVTimeSeries<double>> allSeries = new List<PVTimeSeries<double>>();

            var pvDAU = new PVTimeSeries<double>()
            {
                data = new List<double>(),
                name = "Daily Active Users",
                pointInterval = 24 * 60 * 60 * 1000,
                pointStart = minDate.ToUnixTimestamp() * 1000,
                type = "column"
            };
            for (int i = 0; i < daysDifference; i++)
            {
                int dailyActiveUsers = retention[0].data[i];

                pvDAU.data.Add(dailyActiveUsers);
            }
            allSeries.Add(pvDAU);

            var pvTimeSeries = new PVTimeSeries<double>()
            {
                data = new List<double>(),
                name = "Dollar Cost per DAU",
                pointInterval = 24 * 60 * 60 * 1000,
                pointStart = minDate.ToUnixTimestamp() * 1000,
                type = "line",
                yAxis = 1
            };
            for (int i = 0; i < daysDifference; i++)
            {
                double cost = summary.data.aggregated[i];
                int dailyActiveUsers = retention[0].data[i];

                double dollarCostPerDAU = dailyActiveUsers == 0 ? 0 : cost / dailyActiveUsers;

                pvTimeSeries.data.Add(dollarCostPerDAU);
            }
            allSeries.Add(pvTimeSeries);

            return allSeries;
        }
        public FlowDataView GetCoinFlowByCat(GameMonitoringConfig game, AWSRegion region, DateTime start, DateTime end)
        {
            FlowDataView chart = new FlowDataView()
            {
                Inflows = GetFlowDataByCategory(game.Id, TransactionType.AddCredits, start, end),
                Outflows = GetFlowDataByCategory(game.Id, TransactionType.Purchase, start, end),
                StartDate = start.ToUnixTimestamp() * 1000
            };

            return chart;
        }
        public List<Dictionary<string, object>> ReturnerDataTable(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate)
        {
            List<PVTimeSeries> SeriesList = new List<PVTimeSeries>();
            string query = String.Format(@"SELECT
            `Retention`.`Date`,
            `Retention`.`WAU`,
            `Retention`.`NewUserCohort`,
            `Retention`.`ContinuingUsersCohort`,
            `Retention`.`ReactivatedUsersCohort`,
            `Retention`.`NUR`,
            `Retention`.`CUR`,
            `Retention`.`RUR`,
            `Retention`.`NURR`,
            `Retention`.`CURR`,
            `Retention`.`RURR`
            FROM `Moniverse`.`Retention` order by Date desc;", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"));

            DataTable dt = DBManager.Instance.Query(Datastore.Monitoring, query);

            return JSONFriendifyDataTable(dt);
        }
        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 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 int GetPartiesCount(GameMonitoringConfig game)
        {
            int result = 0;

            string query = String.Format(
                @"SELECT COUNT(*)
                FROM Party
                WHERE GameId = '{0}';",
                game.Id);

            try
            {
                result = DBManager.Instance.QueryForCount(Datastore.General, query);
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(ex.Message, ex.StackTrace);
            }

            return result;
        }
        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 GeoJsonData GetLoginDenistyMap(TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate, GameMonitoringConfig game)
        {
            GeoJsonData returnValue = new GeoJsonData()
            {
                type = "FeatureCollection",
                features = new List<GeoJsonFeature>()
            };
            string query = string.Format(@"select Country,
               Region,
               City,
               Latitude,
               Longitude,
               DATE_SUB(LoginTimestamp, INTERVAL (IFNULL(HOUR(LoginTimestamp) % FLOOR(60 / 60), 0) * 60 * 60) + ((MINUTE(LoginTimestamp) % 60) * 60) + SECOND(LoginTimestamp) SECOND) AS Date,
               COUNT(UserId) as c
                from UserSessionMeta
                where DATE(LoginTimestamp) > '2015-07-12'
                AND LoginTimestamp BETWEEN '{0}' and '{1}'
                AND Latitude IS NOT NULL
                AND Longitude IS NOT NULL
                group by Date, Latitude, Longitude
                order by c desc, LoginTimestamp desc;", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"));

            DataTable dt = DBManager.Instance.Query(Datastore.Monitoring, query);

            foreach (DataRow data in dt.Rows)
            {
                List<float> floatList = new List<float>();

                //cooridinates
                floatList.Add((data["Longitude"].ToString() == null || data["Longitude"].ToString() == "") ? 0 : float.Parse(data["Longitude"].ToString()));
                floatList.Add((data["Latitude"].ToString() == null || data["Latitude"].ToString() == "") ? 0 : float.Parse(data["Latitude"].ToString()));

                //description
                string desc = string.Format(@" {0}, {1}, {2} - {3}",
                    (data["City"].ToString() == null || data["City"].ToString() == "") ? "" : data["City"].ToString(),
                    (data["Region"].ToString() == null || data["Region"].ToString() == "") ? "" : data["Region"].ToString(),
                    (data["Country"].ToString() == null || data["Country"].ToString() == "") ? "" : data["Country"].ToString(),
                    (data["c"].ToString() == null || data["c"].ToString() == "") ? 0 : Convert.ToInt32(data["c"].ToString()));
                returnValue.features.Add(new GeoJsonFeature()
                {
                    type = "Feature",
                    geometry = new GeoJsonGeometry()
                    {
                        type = "Point",
                        coordinates = floatList.ToArray()
                    },
                    properties = new GeoJsonProperties()
                    {
                        count = (data["c"].ToString() == null || data["c"].ToString() == "") ? 0 : Convert.ToInt32(data["c"].ToString()),
                        timestamp = (data["Country"].ToString() == null || data["Country"].ToString() == "") ? 0 : DateTime.Parse(data["Date"].ToString()).ToUnixTimestamp() * 1000
                    }
                });
            }
            return returnValue;
        }
        public List<GameUserActivity> GetGameUserActivity(GameMonitoringConfig game, int days)
        {
            List<GameUserActivity> result = new List<GameUserActivity>();

            string query = String.Format(
                @"SELECT *
                FROM GameUserActivity
                WHERE GameId = '{0}'
                AND RecordTimestamp BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL {1} DAY) AND UTC_TIMESTAMP()
                ORDER BY RecordTimestamp ASC;",
                game.Id,
                days);

            try
            {
                DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query);

                if (queryResult.HasRows())
                {
                    foreach (DataRow row in queryResult.Rows)
                    {
                        result.Add(new GameUserActivity()
                        {
                            GameId = row.Field<string>("GameId"),
                            RegionName = row.Field<string>("RegionName"),
                            RecordTimestamp = row.Field<DateTime>("RecordTimestamp"),
                            GameSessionUsers = row.Field<int>("GameSessionUsers"),
                            EventListeners = row.Field<int>("EventListeners"),
                            TitleScreenUsers = row.Field<int>("TitleScreenUsers"),
                            SessionTypeName_0 = row.Field<string>("SessionTypeName_0"),
                            SessionTypeUsers_0 = row.Field<int>("SessionTypeUsers_0"),
                            SessionTypeName_1 = row.Field<string>("SessionTypeName_1"),
                            SessionTypeUsers_1 = row.Field<int>("SessionTypeUsers_1"),
                            SessionTypeName_2 = row.Field<string>("SessionTypeName_2"),
                            SessionTypeUsers_2 = row.Field<int>("SessionTypeUsers_2"),
                            SessionTypeName_3 = row.Field<string>("SessionTypeName_3"),
                            SessionTypeUsers_3 = row.Field<int>("SessionTypeUsers_3"),
                            SessionTypeName_4 = row.Field<string>("SessionTypeName_4"),
                            SessionTypeUsers_4 = row.Field<int>("SessionTypeUsers_4"),
                            SessionTypeName_5 = row.Field<string>("SessionTypeName_5"),
                            SessionTypeUsers_5 = row.Field<int>("SessionTypeUsers_5"),
                            SessionTypeName_6 = row.Field<string>("SessionTypeName_6"),
                            SessionTypeUsers_6 = row.Field<int>("SessionTypeUsers_6"),
                            SessionTypeName_7 = row.Field<string>("SessionTypeName_7"),
                            SessionTypeUsers_7 = row.Field<int>("SessionTypeUsers_7"),
                            SessionTypeUsers_Other = row.Field<int>("SessionTypeUsers_Other")
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(ex.Message, ex.StackTrace);
            }

            return result;
        }
        public List<GameSessionUserStats> GetGameSessionUserStats(GameMonitoringConfig game, int days)
        {
            List<GameSessionUserStats> result = new List<GameSessionUserStats>();

            string query = String.Format(
                @"SELECT *
                FROM GameSessionUserStats
                WHERE GameId = '{0}'
                AND RecordTimestamp BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL {1} DAY) AND UTC_TIMESTAMP()
                ORDER BY RecordTimestamp ASC;",
                game.Id,
                days);

            try
            {
                DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query);

                if (queryResult.HasRows())
                {
                    foreach (DataRow row in queryResult.Rows)
                    {
                        result.Add(new GameSessionUserStats()
                        {
                            GameId = row.Field<string>("GameId"),
                            SessionType = row.Field<string>("SessionType"),
                            MaxNumPlayers = row.Field<int>("MaxNumPlayers"),
                            AvgPlayers = row.Field<decimal>("AvgPlayers"),
                            Sessions = row.Field<int>("Sessions"),
                            PrivateAvgPlayers = row.Field<decimal>("PrivateAvgPlayers"),
                            PrivateSessions = row.Field<int>("PrivateSessions"),
                            TotalAvgPlayers = row.Field<decimal>("TotalAvgPlayers"),
                            TotalSessions = row.Field<int>("TotalSessions"),
                            RecordTimestamp = row.Field<DateTime>("RecordTimestamp")
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(ex.Message, ex.StackTrace);
            }

            return result;
        }
        public List<PVTimeSeries> GetDailyActiveUsersByGame(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startdate, DateTime enddate)
        {
            DateTime minDate = startdate.Date;
            int daysDifference = (enddate.Date - startdate.Date).Days + 1;

            List<PVTimeSeries> result = new List<PVTimeSeries>();

            string query = String.Format(
                @"SELECT RecordTimestamp
                        ,Count
                FROM MonitoringStats
                WHERE Type = 'DailyActiveUsers'
                AND GameId = '{0}'
                AND DATE(RecordTimestamp) BETWEEN DATE('{1}') and DATE('{2}')
                ORDER BY RecordTimestamp;",
                game.Id,
                startdate.ToString("yyyy-MM-dd HH:mm:ss"),
                enddate.ToString("yyyy-MM-dd HH:mm:ss"));

            try
            {
                DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query);

                if (queryResult.HasRows())
                {
                    PVTimeSeries series = result.FirstOrDefault(x => x.name == "DailyActiveUsers");
                    if (series == default(PVTimeSeries))
                    {
                        series = new PVTimeSeries();
                        series.name = "Users";
                        series.data = new List<int>();
                        series.pointStart = queryResult.Rows[0].Field<DateTime>("RecordTimestamp").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds
                        series.pointInterval = 1440 * 60 * 1000; //JS unix timestamp is in milliseconds
                        series.type = "column";

                        result.Add(series);

                        for (int i = 0; i < daysDifference; i++)
                        {
                            series.data.Add(0);
                        }
                    }
                    foreach (DataRow row in queryResult.Rows)
                    {
                        int index = (row.Field<DateTime>("RecordTimestamp").Date - minDate).Days;
                        series.data[index] = Convert.ToInt32(row["Count"].ToString());
                    }

                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(ex.Message, ex.StackTrace);
            }

            return result;
        }
        public List<string> GetPrivateSessionCompareTypes(GameMonitoringConfig game)
        {
            List<string> result = new List<string>();

            string query = String.Format(
                @"SELECT SessionType
                FROM GameSessionUserStats
                WHERE GameId = '{0}'
                GROUP BY SessionType
                HAVING MAX(Sessions) > 1
                AND MAX(PrivateSessions) > 1;"
                , game.Id);

            try
            {
                DataTable dt = DBManager.Instance.Query(Datastore.Monitoring, query);
                if (dt.HasRows())
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        string sessionType = row.Field<string>("SessionType");
                        if (sessionType != null && !String.IsNullOrEmpty(sessionType))
                        {
                            result.Add(sessionType);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(ex.Message, ex.StackTrace);
            }

            return result;
        }
        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;
        }
        public void RecordGameSessionUserStats(GameMonitoringConfig game)
        {
            try
            {
                if (game == null)
                {
                    throw new Exception("GameInfo cannot be null or empty");
                }

                foreach (PrivacyCompareSessionTypes privacyCompare in game.PrivacyCompareSessionTypes)
                {
                    DataTable result = DBManager.Instance.Query(Datastore.General, GetGameSessionUserStatsQueryStr(game.Id, privacyCompare.PublicTypes, privacyCompare.PrivateTypes));

                    if (result.HasRows())
                    {
                        string query = String.Format(
                            @"INSERT INTO GameSessionUserStats
                            (GameId, SessionType, MaxNumPlayers, AvgPlayers, Sessions, PrivateAvgPlayers
                            ,PrivateSessions, TotalAvgPlayers, TotalSessions, RecordTimestamp)
                            VALUES ('{0}', '{1}', {2}, {3}, {4}, {5}, {6}, {7}, {8}, '{9}');",
                            result.Rows[0].Field<string>("GameId"),
                            result.Rows[0].Field<string>("SessionType"),
                            result.Rows[0].Field<int>("MaxNumPlayers"),
                            result.Rows[0].Field<decimal>("AvgPlayers"),
                            result.Rows[0].Field<long>("Sessions"),
                            result.Rows[0].Field<decimal>("PrivateAvgPlayers"),
                            result.Rows[0].Field<long>("PrivateSessions"),
                            result.Rows[0].Field<decimal>("TotalAvgPlayers"),
                            result.Rows[0].Field<long>("TotalSessions"),
                            DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
                        try
                        {
                            MoniverseResponse response = new MoniverseResponse()
                            {
                                Status = "unsent",
                                TimeStamp = DateTime.UtcNow
                            };

                            lock (MoniverseBase.ConsoleWriterLock)
                            {
                                Console.ForegroundColor = ConsoleColor.Cyan;
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("Beginning Insert of Game Session User Stats");
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("");
                            }
                            Users.Service(Service =>
                            {
                                response = Service.Insert(new MoniverseRequest()
                                {
                                    TaskName = "Insert",
                                    Task = query,
                                    TimeStamp = DateTime.UtcNow
                                });
                            });
                            //int res = DBManager.Instance.Insert(Datastore.Monitoring, query);

                            lock (MoniverseBase.ConsoleWriterLock)
                            {
                                Console.ForegroundColor = ConsoleColor.Green;
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("Game Session User Stats success");
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("");
                                Logger.Instance.Info(String.Format("success!! {0}", response.Status));
                                Console.ResetColor();
                            }

                        }
                        catch (Exception e)
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Logger.Instance.Info(e.Message);
                            Console.ResetColor();
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(String.Format("Game: {0} - {1} | Message: {2}", game.Id, game.Title, ex.Message), ex.StackTrace);
            }
        }
        public PVPPieChart GetBuyWhaleReport(GameMonitoringConfig game, AWSRegion region, DateTime start, DateTime end, int cohort)
        {
            string gameId = game.Id;

            string thresholdQuery = String.Format(
            @"
            # Get threshold for top x% spenders in dollars
            SET @startDate = '{0}';
            SET @endDate = '{1}';
            SET @rownum = 0, @prev_val = NULL, @top_percent= {2};

            # Calculate threshold for top x% of spenders
            SELECT IFNULL(min(score),0.0) as threshold, IFNULL(max(row),0) as numUsers FROM
            (
            SELECT @rownum := @rownum + 1 AS row,
            @prev_val := score AS score,
            UserId
            FROM
            (
            select sum(CostAmount) as score, UserId from Moniverse.Economy_GameCreditTransactions
                where GameId = '{3}'
                and TransactionType = 0   # addCredits
                and CostAmount > 0          # money was actually spent
                and Status = 1 			    # finalized transaction
                and CreatedOn between @startDate and @endDate
                group by UserId
                order by score desc
            ) as spending
            ORDER BY score DESC
            ) as rankedSpending
            WHERE row <= ceil((@top_percent/100 * @rownum)); # ceil helps return at least one row for small datasets

            ", start.ToString("yyyy-MM-dd HH:mm:ss"), end.ToString("yyyy-MM-dd HH:mm:ss"), cohort, gameId);

            DataTable thresholdDatable = DBManager.Instance.Query(Datastore.Monitoring, thresholdQuery);
            decimal threshold = thresholdDatable.Rows[0].Field<decimal>("threshold");
            long numUsers = thresholdDatable.Rows[0].Field<long>("numUsers");

            string query = String.Format(
            @"
            # Get people that spent more than threshold for a given date range
            SET @startDate = '{0}';
            SET @endDate = '{1}';

            # Grab users that have spent >= threshold
            DROP TEMPORARY TABLE IF EXISTS topSpenders;
            CREATE TEMPORARY TABLE IF NOT EXISTS topSpenders (PRIMARY KEY (UserId))
            (
            select sum(CostAmount) as score, UserId from Moniverse.Economy_GameCreditTransactions
            where GameId = '{2}'
            and TransactionType = 0     # addCredits
            and CostAmount > 0          # money was actually spent
            and Status = 1 			    # finalized transaction
            and CreatedOn between @startDate and @endDate
            group by UserId
            having score >= {3}
            order by score desc
            );

            # Get items bought by the top spenders
            SELECT UserData as category, Sum(CostAmount) as total, count(*) as count
            FROM Economy_GameCreditTransactions AS egct
            INNER JOIN topSpenders AS topSpenders
            ON egct.UserId = topSpenders.UserId
            where GameId = '{2}'
            and TransactionType = 0     # addCredits
            and CostAmount > 0          # money was actually spent
            and Status = 1 			    # finalized transaction
            and CreatedOn between @startDate and @endDate
            GROUP BY UserData
            order by total desc;
            ", start.ToString("yyyy-MM-dd HH:mm:ss"), end.ToString("yyyy-MM-dd HH:mm:ss"), gameId, threshold);

            DataTable result = DBManager.Instance.Query(Datastore.Monitoring, query);

            var categoryData = new List<PVPieChartCategories>();
            foreach (DataRow row in result.Rows)
            {
                var category = new PVPieChartCategories()
                {
                    name = row.Field<String>("category"),
                    value = row.Field<Decimal>("total")
                };
                category.metadata.Add("count", row["count"].ToString());
                categoryData.Add(category);
            }

            var pieChart = new PVPPieChart()
            {
                title = cohort + "%",
                subtitle = (categoryData.Any() ? string.Format("Users: ~{0}<br/>Threshold: >= ${1}", numUsers, threshold) : "No data"),
                categoryData = categoryData
            };

            return pieChart;
        }
        public List<GameMonitoringConfig> GetMonitoredGames()
        {
            List<GameMonitoringConfig> games = new List<GameMonitoringConfig>();

            // Get game info configuration
            string query =
                @"SELECT *
                FROM GameMonitoringConfig;";
            try {
                DataTable queryResults = DBManager.Instance.Query(Datastore.Monitoring, query);

                // Collect game info configuration from result
                if (queryResults.HasRows())
                {

                    foreach (DataRow row in queryResults.Rows)
                    {
                        GameMonitoringConfig game = new GameMonitoringConfig();
                        game.Id = row.Field<string>("GameId");
                        game.Title = row.Field<string>("Title");
                        game.ShortTitle = row.Field<string>("ShortTitle");
                        game.LaunchTime = row.Field<DateTime>("LaunchTime");
                        game.ActiveUserSessionTypes = row.Field<string>("ActiveUserSessionTypes").Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
                        game.ActiveUserDeltaThresholdPct_3Min = (float)row.Field<decimal>("ActiveUserDeltaThresholdPct_3Min");
                        game.ActiveUserDeltaThresholdPct_6Min = (float)row.Field<decimal>("ActiveUserDeltaThresholdPct_6Min");
                        List<PrivacyCompareSessionTypes> privacyCompares = new List<PrivacyCompareSessionTypes>();
                        foreach (string privacyCompare in row.Field<string>("PrivacyCompareSessionTypes").Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries).ToList())
                        {
                            string[] compares = privacyCompare.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
                            if (compares.Count() == 2)
                            {
                                List<string> publicTypes = compares[0].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
                                List<string> privateTypes = compares[1].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
                                if (publicTypes.Any() && privateTypes.Any())
                                {
                                    privacyCompares.Add(new PrivacyCompareSessionTypes()
                                    {
                                        PublicTypes = publicTypes,
                                        PrivateTypes = privateTypes
                                    });
                                }
                            }
                        }
                        game.PrivacyCompareSessionTypes = privacyCompares;
                        game.MaxRunningHostingInstances = row.Field<int>("MaxRunningHostingInstances");
                        game.NotificationSettings = new NotificationSettings()
                        {
                            Info = Convert.ToBoolean(row.Field<ulong>("Notifications_Info")),
                            Warning = Convert.ToBoolean(row.Field<ulong>("Notifications_Warning")),
                            Error = Convert.ToBoolean(row.Field<ulong>("Notifications_Error")),
                            PVSupport = Convert.ToBoolean(row.Field<ulong>("Notifications_PVSupport"))
                        };
                        games.Add(game);
                    }
                }
            }
            catch (Exception e) {
                Logger.Instance.Exception(e.Message, e.StackTrace);
            }

            return games;
        }
        public List<PVTimeSeries> ReturnerTimeSeries(GameMonitoringConfig game, TimeInterval interval, AWSRegion region, DateTime startDate, DateTime endDate)
        {
            List<PVTimeSeries> SeriesList = new List<PVTimeSeries>();
            string query = String.Format("SELECT Date, NURR, CURR, RURR from Retention as view WHERE view.Date BETWEEN '{0}' AND '{1}' order by view.Date desc", startDate.ToString("yyyy-MM-dd HH:mm:ss"), endDate.ToString("yyyy-MM-dd HH:mm:ss"));

            int daysDifference = 0;

            DataTable result = DBManager.Instance.Query(Datastore.Monitoring, query);

            if (result.HasRows())
            {
                DateTime minDate = startDate.Date;
                daysDifference = (endDate.Date - startDate.Date).Days;
                foreach (DataRow row in result.Rows)
                {
                    DateTime currentDay = DateTime.Parse(row["Date"].ToString());
                    int daysBetween = (currentDay - minDate).Days;

                    PVTimeSeries NewReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "New Returns");
                    PVTimeSeries ContinuingReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "Continuing Returns");
                    PVTimeSeries ReactivatedReturnsSeries = SeriesList.FirstOrDefault(x => x.name == "Reactivated Returns");
                    if (NewReturnsSeries == default(PVTimeSeries))
                    {
                        NewReturnsSeries = new PVTimeSeries();
                        NewReturnsSeries.name = "New Returns";
                        NewReturnsSeries.data = new List<int>();
                        NewReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds
                        NewReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day);
                        NewReturnsSeries.type = "line";
                        for (int z = 0; z < daysDifference; z++)
                        {
                            NewReturnsSeries.data.Add(0);
                        }

                        SeriesList.Add(NewReturnsSeries);
                    }
                    if (ContinuingReturnsSeries == default(PVTimeSeries))
                    {
                        ContinuingReturnsSeries = new PVTimeSeries();
                        ContinuingReturnsSeries.name = "Continuing Returns";
                        ContinuingReturnsSeries.data = new List<int>();
                        ContinuingReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds
                        ContinuingReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day);
                        ContinuingReturnsSeries.type = "line";
                        for (int z = 0; z < daysDifference; z++)
                        {
                            ContinuingReturnsSeries.data.Add(0);
                        }

                        SeriesList.Add(ContinuingReturnsSeries);
                    }
                    if (ReactivatedReturnsSeries == default(PVTimeSeries))
                    {
                        ReactivatedReturnsSeries = new PVTimeSeries();
                        ReactivatedReturnsSeries.name = "Reactivated Returns";
                        ReactivatedReturnsSeries.data = new List<int>();
                        ReactivatedReturnsSeries.pointStart = result.Rows[0].Field<DateTime>("Date").ToUnixTimestamp() * 1000; //JS unix timestamp is in milliseconds
                        ReactivatedReturnsSeries.pointInterval = Convert.ToInt32(TimeSeriesPointInterval.day);
                        ReactivatedReturnsSeries.type = "line";
                        for (int z = 0; z < daysDifference; z++)
                        {
                            ReactivatedReturnsSeries.data.Add(0);
                        }
                        SeriesList.Add(ReactivatedReturnsSeries);
                    }

                    int index = daysBetween;

                    ReactivatedReturnsSeries.data[index] = (int)row.Field<decimal?>("RURR").GetValueOrDefault(-1);
                    ContinuingReturnsSeries.data[index] = (int)row.Field<decimal?>("CURR").GetValueOrDefault(-1);
                    NewReturnsSeries.data[index] = (int)row.Field<decimal?>("NURR").GetValueOrDefault(-1);
                }
            }
            return SeriesList;
        }
        public static MoniverseNotification TestNotification(GameMonitoringConfig game)
        {
            MoniverseNotification notification = new MoniverseNotification()
            {
                Id = "TestNotification",
                Message = "TESTMESSAGE",
                Subject = "TEST TEST TEST"
            };

            return notification;
        }
        public void RecordDailyActiveUsersByGame(GameMonitoringConfig game)
        {
            try
            {
                if (game == null)
                {
                    throw new Exception("GameInfo cannot be null or empty");
                }

                if (!dau_nextGameRecordTime.ContainsKey(game.Id))
                {
                    dau_nextGameRecordTime.TryAdd(game.Id, DateTime.UtcNow.Date);
                }

                DateTime nextRecordTime = dau_nextGameRecordTime[game.Id];

                if (nextRecordTime <= DateTime.UtcNow)
                {
                    // Check if game DAU record exists for yesterday
                    string query = CheckStatsRecordExistsQueryStr(game.Id, TYPE_DAU, nextRecordTime.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss"));
                    int count = DBManager.Instance.QueryForCount(Datastore.Monitoring, query);

                    if (count == 0)
                    {
                        // Get count of game DAU from RecentPlayers
                        query = GetRecentPlayerCountQueryStr(game.Id, nextRecordTime.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss"), nextRecordTime.AddMinutes(-1).ToString("yyyy-MM-dd HH:mm:ss"));
                        count = DBManager.Instance.QueryForCount(Datastore.General, query);

                        // Insert game DAU record into Monitoring datastore
                        query = InsertStatsRecordQueryStr(game.Id, TYPE_DAU, count, nextRecordTime.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss"));
                        try
                        {
                            MoniverseResponse response = new MoniverseResponse()
                            {
                                Status = "unsent",
                                TimeStamp = DateTime.UtcNow
                            };

                            lock (MoniverseBase.ConsoleWriterLock)
                            {
                                Console.ForegroundColor = ConsoleColor.Cyan;
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("Beginning Insert of DAU By Game");
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("");
                            }
                            Users.Service(Service =>
                            {
                                response = Service.Insert(new MoniverseRequest()
                                {
                                    TaskName = game.ShortTitle + "DAU Insert",
                                    Task = query,
                                    TimeStamp = DateTime.UtcNow
                                });
                            });
                            //int result = DBManager.Instance.Insert(Datastore.Monitoring, query);

                            lock (MoniverseBase.ConsoleWriterLock)
                            {
                                Console.ForegroundColor = ConsoleColor.Green;
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("DAU By Game Success");
                                Logger.Instance.Info("--------------------------------------");
                                Logger.Instance.Info("");
                                Logger.Instance.Info(String.Format("success!! {0}", response.Status));
                                Console.ResetColor();
                            }
                        }
                        catch (Exception e)
                        {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Logger.Instance.Info(e.Message);
                            Console.ResetColor();
                        }
                    }

                    // Increment by a day so the record is only collected once a day after midnight
                    dau_nextGameRecordTime[game.Id] = nextRecordTime.AddDays(1);
                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(String.Format("Game: {0} - {1} | Message: {2}", game.Id, game.Title, ex.Message), ex.StackTrace);
            }
        }
        public List<DailyActiveUserSummary> GetDailyActiveUserSummary(GameMonitoringConfig game)
        {
            List<DailyActiveUserSummary> result = new List<DailyActiveUserSummary>();

            string query = String.Format(
                @"SELECT RecordTimestamp
                        ,Count
                FROM MonitoringStats
                WHERE Type = 'DailyActiveUsers'
                AND GameId = '{0}'
                ORDER BY RecordTimestamp;",
                game.Id);

            try
            {
                DataTable queryResult = DBManager.Instance.Query(Datastore.Monitoring, query);

                if (queryResult.HasRows())
                {
                    foreach (DataRow row in queryResult.Rows)
                    {
                        result.Add(new DailyActiveUserSummary()
                        {
                            RecordTimestamp = row.Field<DateTime>("RecordTimestamp"),
                            Count = row.Field<int>("Count")
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(ex.Message, ex.StackTrace);
            }

            return result;
        }
        public static MoniverseNotification CheckOnlineUserCount(GameMonitoringConfig game, int counter, int milliseconds)
        {
            int count = DBManager.Instance.QueryForCount(Datastore.General, Users.Instance.GetGameSessionUserCountQueryStr(game.Id));
            string formatString = "";

            if (counter == 0)
            {
                formatString = "{1} > !ALERT! currently online: {0} notification interval set at {2}";
            }
            else
            {
                formatString = "{1} > !ALERT! currently online: {0} notification interval set at {2} ({3} Notifications pending)";
            }

            string alert = String.Format(formatString, count, game.ShortTitle, TimeSpan.FromMilliseconds(milliseconds).TotalMinutes, counter);

            return new MoniverseNotification()
            {
                Message = alert,
                Subject = alert,
                Id = "CheckOnlineUserCount",
                ShouldSend = true
            };
        }
        public void CheckComputeHostingInstances(GameMonitoringConfig game)
        {
            DataTable Result = new DataTable();
            string query = String.Format(@"SELECT 0 as 'ID'
                ,'{0}'  AS RecordTimestamp
                ,HI.Id AS 'HostingInstance_Id'
                ,HI.MachineId AS 'HostingInstance_MachineId'
                ,HI.IP AS 'HostingInstance_IP'
                ,HI.CreationTime AS 'HostingInstance_CreationTime'
                ,HI.StartTime AS 'HostingInstance_StartTime'
                ,HI.LastUpdateTime AS 'HostingInstance_LastUpdateTime'
                ,HI.GameId AS 'HostingInstance_GameId'
                ,RHC.Id AS 'RegionHostingConfiguration_Id'
                ,RHC.MinimumNumInstances AS 'RegionHostingConfiguration_MinimumNumInstances'
                ,RHC.MaximumFreeInstances AS 'RegionHostingConfiguration_MaximumFreeInstances'
                ,HC.Name AS 'HostingConfiguration_Name'
                ,HR.Name AS 'HostingRegion_Name'
                ,HI.GameVersionId 'GameVersion_Id'
                ,GV.Major AS 'GameVersion_Major'
                ,GV.Minor AS 'GameVersion_Minor'
                ,GV.Status AS 'GameVersion_Status'
                ,GV.Label 'GameVersion_Label'
                ,HI.Status AS 'HostingInstance_Status'
                ,HI.Health AS 'HostingInstance_Health'
                ,HI.MaximumComputeUnits AS 'HostingInstance_MaximumComputeUnits'
                ,HI.TotalComputeUnits AS 'HostingInstance_TotalComputeUnits'
                ,IFNULL(CALC.TotalComputeUnits, 0) AS 'HostingInstance_CalcTotalComputeUnits'
                ,HI.ServersCount AS 'HostingInstance_ServersCount'
                ,IFNULL(CALC.ServersCount, 0) AS 'HostingInstance_CalcServersCount'
                ,IFNULL(ROUND(CALC.TotalComputeUnits/CALC.ServersCount, 2), 0) AS 'HostingInstance_AvgComputeUnitsAcrossServers'
                ,IFNULL(CALC.MaxUserCount, 0) AS 'HostingInstance_MaxUserCount'
                ,IFNULL(CALC.UserCount, 0) AS 'HostingInstance_UserCount'
                FROM HostingInstance HI
                INNER JOIN RegionHostingConfiguration RHC
                    ON HI.RegionConfigurationId = RHC.Id
                INNER JOIN HostingConfiguration  HC
                    ON RHC.HostingConfigurationId = HC.Id
                INNER JOIN HostingRegion HR
                    ON RHC.RegionId = HR.Id
                LEFT JOIN GameVersion GV
                    ON HI.GameVersionId = GV.Id
                LEFT JOIN (
                    SELECT GSr.InstanceId, SUM(GSr.ComputeUnits) AS 'TotalComputeUnits', COUNT(*) AS 'ServersCount', SUM(GS.MaxNumPlayers) AS 'MaxUserCount', SUM(GS.UserCount) AS 'UserCount'
                    FROM GameServer GSr
                    INNER JOIN
                    (
                        SELECT GSn.Id, COUNT(*) AS 'UserCount', GST.MaxNumPlayers
                        FROM GameSession GSn
                        INNER JOIN GameSessionType GST
                            ON GSn.SessionTypeId = GST.Id
                        LEFT JOIN GameSessionUser GSU
                            ON GSn.Id = GSU.GameSessionId
                        WHERE GSU.Status = 2
                        GROUP BY GSn.Id
                    ) GS
                    ON GSr.GameSessionId = GS.Id
                    GROUP BY GSr.InstanceId
                ) CALC
                    ON HI.Id = CALC.InstanceId
                WHERE HI.IsLocallyEmulated = 0
                AND HI.IsPhysicallyHosted = 0
                AND HI.GameID = '{1}'
                ORDER BY HI.GameId, HR.Name, HI.Status, HI.Id;",
                DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"), game.Id);

            try
            {

                Logger.Instance.Info("--------------------------------------");
                Logger.Instance.Info("Beginning Hosting Instance Query");
                Logger.Instance.Info("--------------------------------------");
                Logger.Instance.Info("");
                Result = DBManager.Instance.Query(Datastore.General, query);
                Logger.Instance.Info(String.Format("Succesfully retrieved Hosting Instances  : {0}", Result.Rows.Count));
                if (Result.Rows.Count > 0)
                {
                    List<string> InsertStatements = new List<string>();
                    foreach (DataRow row in Result.Rows)
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append(@"INSERT INTO `HostingInstance_ComputeRaw`
                    (`ID`,
                    `RecordTimestamp`,
                    `HostingInstance_Id`,
                    `HostingInstance_MachineId`,
                    `HostingInstance_IP`,
                    `HostingInstance_CreationTime`,
                    `HostingInstance_StartTime`,
                    `HostingInstance_LastUpdateTime`,
                    `HostingInstance_GameId`,
                    `RegionHostingConfiguration_Id`,
                    `RegionHostingConfiguration_MinimumNumInstances`,
                    `RegionHostingConfiguration_MaximumFreeInstances`,
                    `HostingConfiguration_Name`,
                    `HostingRegion_Name`,
                    `GameVersion_Id`,
                    `GameVersion_Major`,
                    `GameVersion_Minor`,
                    `GameVersion_Status`,
                    `GameVersion_Label`,
                    `HostingInstance_Status`,
                    `HostingInstance_Health`,
                    `HostingInstance_MaximumComputeUnits`,
                    `HostingInstance_TotalComputeUnits`,
                    `HostingInstance_CalcTotalComputeUnits`,
                    `HostingInstance_ServersCount`,
                    `HostingInstance_CalcServersCount`,
                    `HostingInstance_AvgComputeUnitsAcrossServers`,
                    `HostingInstance_MaxUserCount`,
                    `HostingInstance_UserCount`) VALUES (");
                        foreach (var item in row.ItemArray)
                        {
                            if (item is DateTime)
                            {
                                DateTime itm = (DateTime)item;
                                sb.Append("'" + itm.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                                sb.Append(",");
                            }
                            else
                            {
                                sb.Append("'" + item.ToString() + "'");
                                sb.Append(",");
                            }

                        }
                        sb.Length--;
                        sb.Append(");");
                        string Insert = sb.ToString();
                        InsertStatements.Add(Insert);

                    }
                    try
                    {
                        MoniverseResponse response = new MoniverseResponse()
                        {
                            Status = "unsent",
                            TimeStamp = DateTime.UtcNow
                        };

                        string shittyWay = string.Join("", InsertStatements);
                        lock (MoniverseBase.ConsoleWriterLock)
                        {
                            Console.ForegroundColor = ConsoleColor.Cyan;
                            Logger.Instance.Info("--------------------------------------");
                            Logger.Instance.Info("Beginning Insert of Retention Row Batch");
                            Logger.Instance.Info("--------------------------------------");
                            Logger.Instance.Info("");
                        }
                        HostingInstances.Service(Service => {
                            Service.Insert(new MoniverseRequest()
                            {
                                TaskName = "Insert",
                                Task = shittyWay,
                                TimeStamp = DateTime.UtcNow
                            });
                        });
                        //int result = DBManager.Instance.Insert(Datastore.Monitoring, shittyWay);
                        lock (MoniverseBase.ConsoleWriterLock)
                        {
                            Console.ForegroundColor = ConsoleColor.Green;
                            Logger.Instance.Info("--------------------------------------");
                            Logger.Instance.Info("Beginning Insert of Retention Row Batch");
                            Logger.Instance.Info("--------------------------------------");
                            Logger.Instance.Info("");
                            Logger.Instance.Info(String.Format("success!! {0} inserted", InsertStatements.Count));
                            Console.ResetColor();
                        }

                    }
                    catch (Exception e)
                    {
                        Console.ForegroundColor = ConsoleColor.Red;
                        Logger.Instance.Info(e.Message);
                        Console.ResetColor();
                    }
                }

            }
            catch (Exception ex)
            {
                Logger.Instance.Exception(ex.Message, ex.StackTrace);
            }
        }