private void FetchUserList() { DebugWrite("fetchUserList starting!", 6); if (HandlePossibleDisconnect()) { return; } try { if (!SendQuery("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE ( TABLE_SCHEMA = '" + _mySqlDatabaseName + "' AND TABLE_NAME = 'adkats_users' AND COLUMN_NAME = 'user_expiration' )", false)) { SendNonQuery("Adding user expiration.", "ALTER TABLE `adkats_users` ADD COLUMN `user_expiration` DATETIME NOT NULL AFTER `user_role`", true); SendNonQuery("Adding initial user expiration values.", "UPDATE `adkats_users` SET `user_expiration` = DATE_ADD(UTC_TIMESTAMP(), INTERVAL 20 YEAR)", true); } if (!SendQuery("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE ( TABLE_SCHEMA = '" + _mySqlDatabaseName + "' AND TABLE_NAME = 'adkats_users' AND COLUMN_NAME = 'user_notes' )", false)) { SendNonQuery("Adding user notes.", "ALTER TABLE `adkats_users` ADD COLUMN `user_notes` VARCHAR(1000) NOT NULL DEFAULT 'No Notes' AFTER `user_expiration`", true); } using (MySqlConnection connection = GetDatabaseConnection()) { using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT `adkats_users`.`user_id`, `adkats_users`.`user_name`, `adkats_users`.`user_email`, `adkats_users`.`user_phone`, `adkats_users`.`user_role`, `adkats_users`.`user_expiration`, `adkats_users`.`user_notes` FROM `adkats_users`"; var validIDs = new List<Int64>(); using (MySqlDataReader reader = command.ExecuteReader()) { if (_isTestingAuthorized) PushThreadDebug(DateTime.Now.Ticks, (String.IsNullOrEmpty(Thread.CurrentThread.Name) ? ("mainthread") : (Thread.CurrentThread.Name)), Thread.CurrentThread.ManagedThreadId, new System.Diagnostics.StackTrace(true).GetFrame(0).GetFileLineNumber(), ""); lock (_userCache) { while (reader.Read()) { int userID = reader.GetInt32("user_id"); //0 validIDs.Add(userID); string userName = reader.GetString("user_name"); //1 String userEmail = null; if (!reader.IsDBNull(2)) userEmail = reader.GetString("user_email"); //2 String userPhone = null; if (!reader.IsDBNull(3)) userPhone = reader.GetString("user_phone"); //3 AdKatsRole userRole; if (!_RoleIDDictionary.TryGetValue(reader.GetInt32("user_role"), out userRole)) { ConsoleError("Unable to find user role for role ID " + reader.GetInt32("user_role")); continue; } DateTime expirationTime = reader.GetDateTime("user_expiration"); String userNotes = reader.GetString("user_notes"); AdKatsUser aUser; if (_userCache.TryGetValue(userID, out aUser)) { if (expirationTime < DateTime.UtcNow) { userRole = _RoleKeyDictionary["guest_default"]; expirationTime = DateTime.UtcNow.AddYears(20); QueueUserForUpload(aUser); } aUser.user_name = userName; aUser.user_email = userEmail; aUser.user_phone = userPhone; aUser.user_role = userRole; aUser.user_expiration = expirationTime; aUser.user_notes = userNotes; } else { aUser = new AdKatsUser { user_id = userID, user_name = userName, user_email = userEmail, user_phone = userPhone, user_role = userRole, user_expiration = expirationTime, user_notes = userNotes }; if (expirationTime < DateTime.UtcNow) { userRole = _RoleKeyDictionary["guest_default"]; expirationTime = DateTime.UtcNow.AddYears(20); QueueUserForUpload(aUser); } _userCache.Add(aUser.user_id, aUser); } } foreach (AdKatsUser remUser in _userCache.Values.Where(usr => validIDs.All(id => id != usr.user_id)).ToList()) { _userCache.Remove(remUser.user_id); ConsoleSuccess("User " + remUser.user_name + " removed."); } } } } using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = @" SELECT `adkats_users`.`user_id`, `adkats_usersoldiers`.`player_id`, `tbl_playerdata`.`GameID` AS `game_id`, `tbl_playerdata`.`ClanTag` AS `clan_tag`, `tbl_playerdata`.`SoldierName` AS `player_name`, `tbl_playerdata`.`EAGUID` AS `player_guid`, `tbl_playerdata`.`IP_Address` AS `player_ip` FROM `adkats_users` INNER JOIN `adkats_usersoldiers` ON `adkats_users`.`user_id` = `adkats_usersoldiers`.`user_id` INNER JOIN `tbl_playerdata` ON `adkats_usersoldiers`.`player_id` = `tbl_playerdata`.`PlayerID` ORDER BY `user_id` ASC"; using (MySqlDataReader reader = command.ExecuteReader()) { if (_isTestingAuthorized) PushThreadDebug(DateTime.Now.Ticks, (String.IsNullOrEmpty(Thread.CurrentThread.Name) ? ("mainthread") : (Thread.CurrentThread.Name)), Thread.CurrentThread.ManagedThreadId, new System.Diagnostics.StackTrace(true).GetFrame(0).GetFileLineNumber(), ""); lock (_userCache) { foreach (AdKatsPlayer aPlayer in _userCache.Values.SelectMany(aUser => aUser.soldierDictionary.Values)) { aPlayer.update_playerUpdated = false; } while (reader.Read()) { int userID = reader.GetInt32("user_id"); //0 int playerID = reader.GetInt32("player_id"); //1 int gameID = reader.GetInt32("game_id"); //2 String clanTag = null; if (!reader.IsDBNull(3)) clanTag = reader.GetString("clan_tag"); //3 String playerName = null; if (!reader.IsDBNull(4)) playerName = reader.GetString("player_name"); //4 String playerGUID = null; if (!reader.IsDBNull(5)) playerGUID = reader.GetString("player_guid"); //5 String playerIP = null; if (!reader.IsDBNull(6)) playerIP = reader.GetString("player_ip"); //6 AdKatsUser aUser; if (_userCache.TryGetValue(userID, out aUser)) { AdKatsPlayer aPlayer; if (aUser.soldierDictionary.TryGetValue(playerID, out aPlayer)) { aPlayer.game_id = gameID; aPlayer.clan_tag = clanTag; aPlayer.player_name = playerName; aPlayer.player_guid = playerGUID; aPlayer.player_ip = playerIP; } else { aPlayer = new AdKatsPlayer { player_id = playerID, game_id = gameID, clan_tag = clanTag, player_name = playerName, player_guid = playerGUID, player_ip = playerIP }; aUser.soldierDictionary.Add(playerID, aPlayer); } aPlayer.player_role = aUser.user_role; aPlayer.update_playerUpdated = true; } else { ConsoleError("Unable to add soldier " + playerID + " to user " + userID + " when fetching user list. User not found."); } } foreach (AdKatsUser aUser in _userCache.Values) { foreach (AdKatsPlayer aPlayer in aUser.soldierDictionary.Values.Where(aPlayer => !aPlayer.update_playerUpdated)) { aUser.soldierDictionary.Remove(aPlayer.player_id); } } if (!_firstPlayerListComplete) { OnlineAdminSayMessage("User fetch complete. Fetching player list."); } } } } var tempSpecialPlayerCache = new List<AdKatsSpecialPlayer>(); using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = @" SELECT `player_group`, `player_id`, `player_game`, `player_server`, `player_identifier`, `player_effective`, `player_expiration` FROM `adkats_specialplayers` WHERE `player_expiration` > UTC_TIMESTAMP() ORDER BY `player_group` DESC"; using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var asPlayer = new AdKatsSpecialPlayer(); asPlayer.player_group = reader.GetString("player_group"); //0 if (!reader.IsDBNull(1)) asPlayer.player_object = FetchPlayer(false, true, false, null, reader.GetInt32("player_id"), null, null, null); //1 if (!reader.IsDBNull(2)) asPlayer.player_game = reader.GetInt32("player_game"); //2 if (!reader.IsDBNull(3)) asPlayer.player_server = reader.GetInt32("player_server"); //3 if (!reader.IsDBNull(4)) asPlayer.player_identifier = reader.GetString("player_identifier"); //4 asPlayer.player_effective = reader.GetDateTime("player_effective"); asPlayer.player_expiration = reader.GetDateTime("player_expiration"); //Check if special player applies to this server Boolean allowed = true; if (asPlayer.player_object == null) { //If they didn't define a player, they must define an identifier if (String.IsNullOrEmpty(asPlayer.player_identifier)) { allowed = false; } //Did they define a game for the special player? if (asPlayer.player_game != null) { //They did, only use if the given game id is this server's game id if (asPlayer.player_game != _gameID) { allowed = false; } } } else if (asPlayer.player_object.game_id != _gameID) { allowed = false; } //Did they define a server for the special player? if (asPlayer.player_server != null) { //They did, only use if the given server id is this server's server id if (asPlayer.player_server != _serverID) { allowed = false; } } if (allowed) { //Add the user to temp list tempSpecialPlayerCache.Add(asPlayer); } } } } //Update the special player cache if (_isTestingAuthorized) PushThreadDebug(DateTime.Now.Ticks, (String.IsNullOrEmpty(Thread.CurrentThread.Name) ? ("mainthread") : (Thread.CurrentThread.Name)), Thread.CurrentThread.ManagedThreadId, new System.Diagnostics.StackTrace(true).GetFrame(0).GetFileLineNumber(), ""); lock (_specialPlayerGroupCache) { _specialPlayerGroupCache.Clear(); foreach (AdKatsSpecialPlayer asPlayer in tempSpecialPlayerCache) { List<AdKatsSpecialPlayer> currentList; if (_specialPlayerGroupCache.TryGetValue(asPlayer.player_group, out currentList)) { currentList.Add(asPlayer); } else { currentList = new List<AdKatsSpecialPlayer> { asPlayer }; _specialPlayerGroupCache.Add(asPlayer.player_group, currentList); } } //Debug logging purposes only if (_debugLevel > 4) { foreach (string key in _specialPlayerGroupCache.Keys) { List<AdKatsSpecialPlayer> asPlayerList; if (_specialPlayerGroupCache.TryGetValue(key, out asPlayerList)) { DebugWrite("SPECIAL: List: " + key, 4); foreach (AdKatsSpecialPlayer asPlayer in asPlayerList) { if (asPlayer.player_object != null) { DebugWrite("SPECIAL: Contents: " + asPlayer.player_object.player_name, 4); } else { DebugWrite("SPECIAL: Contents: " + asPlayer.player_identifier, 4); } } } } } } } } catch (Exception e) { HandleException(new AdKatsException("Error while fetching access list.", e)); } _PlayerRoleRefetch = true; _PlayerProcessingWaitHandle.Set(); UpdateMULTIBalancerWhitelist(); UpdateMULTIBalancerDisperseList(); UpdateReservedSlots(); UpdateSpectatorList(); _lastUserFetch = DateTime.UtcNow; if (_userCache.Count > 0) { DebugWrite("User List Fetched from Database. User Count: " + _userCache.Count, 1); } else { ConsoleWarn("No users in the user table. Add a new user with 'Add User'."); } UpdateSettingPage(); DebugWrite("fetchUserList finished!", 6); }
public List<AdKatsSpecialPlayer> FetchMatchingSpecialPlayers(String group, AdKatsPlayer aPlayer) { DebugWrite("Entering FetchMatchingSpecialPlayers", 6); try { var matchingPlayers = new List<AdKatsSpecialPlayer>(); using (MySqlConnection connection = GetDatabaseConnection()) { using (MySqlCommand command = connection.CreateCommand()) { //Attempt to execute the query command.CommandText = @" SELECT `specialplayer_id`, `player_group`, `player_id`, `player_game`, `player_server`, `player_identifier`, `player_effective`, `player_expiration` FROM `adkats_specialplayers` WHERE `player_expiration` > UTC_TIMESTAMP() AND ( `player_id` = @player_id OR `player_identifier` = @player_name OR `player_identifier` = @player_guid OR `player_identifier` = @player_ip )"; command.Parameters.AddWithValue("@player_id", aPlayer.player_id); command.Parameters.AddWithValue("@player_name", aPlayer.player_name); command.Parameters.AddWithValue("@player_guid", aPlayer.player_guid); command.Parameters.AddWithValue("@player_ip", aPlayer.player_ip); using (MySqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var asPlayer = new AdKatsSpecialPlayer(); asPlayer.player_group = reader.GetString("player_group"); if (asPlayer.player_group != group) { continue; } Int32? player_id = null; if (!reader.IsDBNull(2)) { player_id = reader.GetInt32("player_id"); } if (!reader.IsDBNull(3)) asPlayer.player_game = reader.GetInt32("player_game"); if (!reader.IsDBNull(4)) asPlayer.player_server = reader.GetInt32("player_server"); if (!reader.IsDBNull(5)) asPlayer.player_identifier = reader.GetString("player_identifier"); asPlayer.player_effective = reader.GetDateTime("player_effective"); asPlayer.player_expiration = reader.GetDateTime("player_expiration"); if (asPlayer.player_game != null && asPlayer.player_game != _gameID) { DebugWrite("Matching player rejected for mismatched game id", 4); continue; } if (asPlayer.player_server != null && asPlayer.player_server != _serverID) { DebugWrite("Matching player rejected for mismatched server id", 4); continue; } if (player_id != null) { if (player_id != aPlayer.player_id) { DebugWrite("Matching player rejected for mismatched player id", 4); continue; } asPlayer.player_object = aPlayer; } if (String.IsNullOrEmpty(asPlayer.player_identifier) && asPlayer.player_identifier != aPlayer.player_name && asPlayer.player_identifier != aPlayer.player_guid && asPlayer.player_identifier != aPlayer.player_ip) { DebugWrite("Matching player rejected for mismatched player identifier", 4); continue; } matchingPlayers.Add(asPlayer); } } } } return matchingPlayers; } catch (Exception e) { HandleException(new AdKatsException("Error while taking action for Disperse record.", e)); } DebugWrite("Exiting FetchMatchingSpecialPlayers", 6); return null; }