private static async Task ParseNewDiscordIds()
{
DiscordRestClient discordClient = new DiscordRestClient();
discordClient.Log += new Func <LogMessage, Task>(l => { Console.WriteLine(l); return(Task.CompletedTask); });
await discordClient.LoginAsync(TokenType.Bot, config.DiscordApiToken);
List <string> DiscordIdsToParse = FetchDiscordIds(true);
using (BatchQueryBuilder batchQuerybuilder = new BatchQueryBuilder(config.ConnString, 4000))
{
foreach (string i in DiscordIdsToParse)
{
if (i == "0")
{
continue;
}
try
{
Console.WriteLine($"Parsing Discord ID {i}");
var discordUser = await discordClient.GetUserAsync(UInt64.Parse(i));
batchQuerybuilder.AppendSqlCommand($"UPDATE FamilyRpServerAccess.ParsedDiscordAccounts SET DiscordCreated = '{discordUser.CreatedAt.UtcDateTime}', DiscordUsername = '******', DiscordDiscriminator = {MySqlHelper.EscapeString(discordUser.Discriminator)}, IsParsed = 1 WHERE DiscordId = {i};");
AuditLog(null, "Discord", "Parsed", "", i, batchQuerybuilder);
}
catch (Exception ex)
{
Console.WriteLine($"Could not parse Discord ID {i}; {ex}");
}
}
}
}
private static async Task ParseNewTwitchIds()
{
List <string> TwitchIdsToParse = FetchTwitchIds(true);
TwitchAPI.Settings.ClientId = config.TwitchApiClientId;
TwitchAPI.Settings.AccessToken = config.TwitchApiAccessToken;
using (BatchQueryBuilder batchQuerybuilder = new BatchQueryBuilder(config.ConnString))
{
foreach (string i in TwitchIdsToParse)
{
if (i == "0")
{
continue;
}
try
{
Console.WriteLine($"Parsing Twitch ID {i}");
var twitchUser = await TwitchAPI.Users.v5.GetUserByIDAsync(i);
var twitchUserCreated = twitchUser.CreatedAt;
var twitchUserFollowerCount = (await TwitchAPI.Channels.v5.GetChannelFollowersAsync(twitchUser.Id)).Total;
batchQuerybuilder.AppendSqlCommand($"UPDATE FamilyRpServerAccess.ParsedTwitchAccounts SET TwitchCreated = '{twitchUserCreated}', TwitchName = {(twitchUser.Name == null ? "null" : $"'{MySqlHelper.EscapeString(UTF8toASCII(twitchUser.Name))}'")}, TwitchFollowerCount = {twitchUserFollowerCount}, IsParsed = 1 WHERE TwitchId = {i};");
AuditLog(null, "Twitch", "Parsed", "", i, batchQuerybuilder);
}
catch (Exception ex)
{
Console.WriteLine($"Failed to parse Twitch ID {i}; {ex}");
}
}
}
}
private static async Task ParseNewSteamIds()
{
List <string> SteamIdsToParse = FetchSteamIds(true);
using (BatchQueryBuilder batchQuerybuilder = new BatchQueryBuilder(config.ConnString))
{
var steamInterface = new SteamUser(config.SteamWebApiKey, null);
foreach (string i in SteamIdsToParse)
{
if (i == "0")
{
continue;
}
try
{
Console.WriteLine($"Parsing Steam ID {i}");
var playerSummaryResponseData = (await steamInterface.GetPlayerSummaryAsync(UInt64.Parse(i))).Data;
var playerBansResponseData = (await steamInterface.GetPlayerBansAsync(UInt64.Parse(i))).Data;
IReadOnlyCollection <FriendModel> playerFriendsResponseData = null;
if (playerSummaryResponseData.ProfileVisibility == Steam.Models.SteamCommunity.ProfileVisibility.Public)
{
playerFriendsResponseData = (await steamInterface.GetFriendsListAsync(UInt64.Parse(i))).Data;
}
batchQuerybuilder.AppendSqlCommand(new MySqlCommand($"UPDATE FamilyRpServerAccess.ParsedSteamAccounts SET SteamCreated = '{playerSummaryResponseData.AccountCreatedDate}',RowUpdated = NOW()," +
$"SteamName = '{MySqlHelper.EscapeString(UTF8toASCII(playerSummaryResponseData.Nickname))}'," +
$"SteamVisibility = '{playerSummaryResponseData.ProfileVisibility}'," +
$"NumSteamFriends = {(playerFriendsResponseData != null ? playerFriendsResponseData.Count : 0)}," +
$"SteamBans = {playerBansResponseData.First().NumberOfGameBans + (playerBansResponseData.First().CommunityBanned ? 1 : 0) + playerBansResponseData.First().NumberOfVACBans}," +
$"isParsed = 1 " +
$"WHERE SteamId = {i};"));
AuditLog(null, "Steam", "Parsed", "", i, batchQuerybuilder);
}
catch (Exception ex)
{
Console.WriteLine($"Could not fetch Steam ID {i}: {ex}");
}
}
}
}
private static void PropagateChangedUsers()
{
DbDataReader reader;
using (var conn = new MySqlConnection(config.ConnString))
{
conn.Open();
var command = new MySqlCommand()
{
Connection = conn
};
command.CommandText = $@"
SELECT member_id,
name,
fm.email,
ip_address,
fm.steamid,
From_unixtime(joined) AS joined,
From_unixtime(last_visit) AS last_visit,
From_unixtime(last_activity) AS last_activity,
discord_id,
discord_token,
tw_id,
tw_name,
tw_dname,
tw_token,
fsa.DiscordId,
fsa.TwitchId,
fsa.SteamId AS OldSteamId,
fsa.CurrentIp AS OldIp,
temp_ban,
member_posts,
msg_count_total,
member_group_id,
mgroup_others,
Md5(CONCAT(COALESCE(fm.steamid, ''), COALESCE(last_visit, ''),
COALESCE(last_activity, ''), COALESCE(discord_id, ''),
COALESCE(tw_id, ''),
COALESCE(temp_ban, ''), COALESCE(member_posts, ''),
COALESCE(msg_count_total, ''), COALESCE(member_group_id, ''),
COALESCE(mgroup_others, ''))) AS checksum
FROM familyrp.frpcore_members AS fm
LEFT JOIN FamilyRpServerAccess.Users AS fsa ON fm.member_id = fsa.ForumId
WHERE member_id IN (SELECT member_id FROM FamilyRpServerAccess.Users AS fsa
INNER JOIN familyrp.frpcore_members AS fm ON fm.member_id = fsa.ForumId
AND fsa.ForumDbRowChecksum <> MD5(CONCAT(COALESCE(fm.steamid, ''), COALESCE(fm.last_visit, ''),
COALESCE(fm.last_activity, ''), COALESCE(fm.discord_id, ''),
COALESCE(fm.tw_id, ''),
COALESCE(fm.temp_ban, ''), COALESCE(fm.member_posts, ''),
COALESCE(fm.msg_count_total, ''), COALESCE(fm.member_group_id, ''),
COALESCE(fm.mgroup_others, '')))
ORDER BY member_id DESC);";
reader = command.ExecuteReader();
List <UserDbModel> users = new List <UserDbModel>();
int i = 0;
using (var bqBuilder = new BatchQueryBuilder(config.ConnString))
{
while (reader.Read())
{
var user = UserDbModel.Create(reader);
if (Convert.ToString(reader["steamid"]) != "" && Convert.ToString(reader["steamid"]) != Convert.ToString(reader["OldSteamId"]))
{
AuditLog(Convert.ToString(reader["member_id"]), "Steam", "Changed", Convert.ToString(reader["OldSteamId"]), Convert.ToString(reader["steamid"]), bqBuilder);
}
if (reader["steamid"] != DBNull.Value && !SteamIds.Contains(Convert.ToString(reader["steamid"])))
{
CreateNonParsedSteamId(Convert.ToString(reader["steamid"]), bqBuilder);
}
if (Convert.ToString(reader["discord_id"]) != "" && Convert.ToString(reader["discord_id"]) != Convert.ToString(reader["DiscordId"]))
{
AuditLog(Convert.ToString(reader["member_id"]), "Discord", "Changed", Convert.ToString(reader["DiscordId"]), Convert.ToString(reader["discord_id"]), bqBuilder);
}
if (reader["discord_id"] != DBNull.Value && Convert.ToString(reader["discord_id"]) != null && !DiscordIds.Contains(Convert.ToString(reader["discord_id"])))
{
CreateNonParsedDiscordId(Convert.ToString(reader["discord_id"]), bqBuilder);
}
if (Convert.ToString(reader["tw_id"]) != "" && Convert.ToString(reader["tw_id"]) != Convert.ToString(reader["TwitchId"]))
{
AuditLog(Convert.ToString(reader["member_id"]), "Twitch", "Changed", Convert.ToString(reader["TwitchId"]), Convert.ToString(reader["tw_id"]), bqBuilder);
}
if (reader["tw_id"] != DBNull.Value && Convert.ToString(reader["tw_id"]) != null && !TwitchIds.Contains(Convert.ToString(reader["tw_id"])))
{
CreateNonParsedTwitchId(Convert.ToString(reader["tw_id"]), bqBuilder);
}
if (Convert.ToString(reader["ip_address"]) != "" && Convert.ToString(reader["ip_address"]) != Convert.ToString(reader["OldIp"]))
{
AuditLog(Convert.ToString(reader["member_id"]), "IP", "Changed", Convert.ToString(reader["OldIp"]), Convert.ToString(reader["ip_address"]), bqBuilder);
}
if (reader["ip_address"] != DBNull.Value && Convert.ToString(reader["ip_address"]) != null && !IpAddresses.Contains(Convert.ToString(reader["ip_address"])))
{
CreateIpAddress(Convert.ToInt32(reader["member_id"]), Convert.ToString(reader["ip_address"]), bqBuilder);
}
command = new MySqlCommand();
command.CommandText = $@"UPDATE FamilyRpServerAccess.Users SET SteamId = @SteamId, DiscordId = @DiscordId, TwitchId = @TwitchId, GtaLicense = @GtaLicense, ForumBanned = @ForumBanned, ForumPostCount = @ForumPostCount, ForumPmCount = @ForumPmCount, ForumGroups = @ForumGroups, IsAdmin = @IsAdmin, IsDev = @IsDev, IsPolice = @IsPolice, IsEMS = @IsEMS, IsFireDept = @IsFireDept, LastLoggedInForum = @LastLoggedInForum, ForumDbRowChecksum = @ForumDbRowChecksum, CurrentIP = @CurrentIP, RowUpdated = NOW() WHERE ForumId = @ForumId;";
command.Parameters.AddWithValue("ForumId", user.Forum.Id);
command.Parameters.AddWithValue("SteamId", user.Steam.Id);
command.Parameters.AddWithValue("DiscordId", user.Discord.Id);
command.Parameters.AddWithValue("TwitchId", user.Twitch.Id);
command.Parameters.AddWithValue("GtaLicense", DBNull.Value);
command.Parameters.AddWithValue("ForumBanned", user.Forum.BanTime != 0 ? 1 : 0);
command.Parameters.AddWithValue("ForumPostCount", user.Forum.PostCount);
command.Parameters.AddWithValue("ForumPmCount", user.Forum.MessageCount);
command.Parameters.AddWithValue("ForumGroups", String.Join(",", user.Forum.Groups));
command.Parameters.AddWithValue("IsAdmin", user.Forum.Groups.Contains(10) ? 1 : 0);
command.Parameters.AddWithValue("IsDev", user.Forum.Groups.Contains(11) ? 1 : 0);
command.Parameters.AddWithValue("IsPolice", user.Forum.Groups.Contains(9) ? 1 : 0);
command.Parameters.AddWithValue("IsEMS", user.Forum.Groups.Contains(20) ? 1 : 0);
command.Parameters.AddWithValue("IsFireDept", 0);
command.Parameters.AddWithValue("LastLoggedInForum", user.Forum.LastVisit);
command.Parameters.AddWithValue("LastLoggedInGame", new DateTime(1900, 1, 1));
command.Parameters.AddWithValue("ForumDbRowChecksum", user.Forum.RowChecksum);
command.Parameters.AddWithValue("CurrentIP", user.Forum.CurrentIP);
bqBuilder.AppendSqlCommand(command);
Console.WriteLine($"Updating row #{i} with forum id #{user.Forum.Id}");
UpdateGroups(user.Forum.Id.ToString(), user.Forum.Groups, bqBuilder);
i++;
}
}
reader.Dispose();
}
}
private static void CreateNonParsedSteamId(string steamId, BatchQueryBuilder batchQueryBuilder)
{
SteamIds.Add(steamId);
batchQueryBuilder.AppendSqlCommand(new MySqlCommand($"INSERT INTO FamilyRpServerAccess.ParsedSteamAccounts (SteamId, IsParsed, RowCreated, RowUpdated) VALUES ({MySqlHelper.EscapeString(steamId)}, 0, NOW(), NOW());"));
}
private static void CreateNonParsedDiscordId(string discordId, BatchQueryBuilder batchQueryBuilder)
{
DiscordIds.Add(discordId);
batchQueryBuilder.AppendSqlCommand(new MySqlCommand($"INSERT INTO FamilyRpServerAccess.ParsedDiscordAccounts (DiscordId, IsParsed, RowCreated, RowUpdated) VALUES ({Convert.ToUInt64(discordId)}, 0, NOW(), NOW());"));
}
private static void CreateGtaId(string gtaId, BatchQueryBuilder batchQueryBuilder)
{
batchQueryBuilder.AppendSqlCommand(new MySqlCommand($"INSERT INTO FamilyRpServerAccess.GtaAccounts (GtaLicense, RowCreated, RowUpdated) VALUES ('{MySqlHelper.EscapeString(gtaId)}', NOW(), NOW());"));
}
private static void CreateIpAddress(int forumId, string Ip, BatchQueryBuilder batchQueryBuilder)
{
IpAddresses.Add(Ip);
batchQueryBuilder.AppendSqlCommand(new MySqlCommand($"INSERT INTO FamilyRpServerAccess.IpAddresses (ForumId, IP, RowCreated, RowUpdated) VALUES ({forumId}, '{MySqlHelper.EscapeString(Ip)}', NOW(), NOW());"));
}
private static Task PropagateNewUsers()
{
DbDataReader reader;
using (var conn = new MySqlConnection(config.ConnString))
{
conn.Open();
var command = new MySqlCommand()
{
Connection = conn
};
command.CommandText = $@"
SELECT member_id,
name,
fm.email,
ip_address,
steamid,
From_unixtime(joined) AS joined,
From_unixtime(last_visit) AS last_visit,
From_unixtime(last_activity) AS last_activity,
discord_id,
discord_token,
tw_id,
tw_name,
tw_dname,
tw_token,
temp_ban,
member_posts,
msg_count_total,
member_group_id,
mgroup_others,
Md5(CONCAT(COALESCE(steamid, ''), COALESCE(last_visit, ''),
COALESCE(last_activity, ''), COALESCE(discord_id, ''),
COALESCE(tw_id, ''),
COALESCE(temp_ban, ''), COALESCE(member_posts, ''),
COALESCE(msg_count_total, ''), COALESCE(member_group_id, ''),
COALESCE(
mgroup_others, ''))) AS checksum
FROM familyrp.frpcore_members AS fm
WHERE member_id IN (SELECT member_id FROM FamilyRpServerAccess.Users AS fsa
RIGHT JOIN familyrp.frpcore_members AS fm ON fm.member_id = fsa.ForumId
WHERE fsa.ForumId IS NULL
ORDER BY fm.member_id DESC);";
reader = command.ExecuteReader();
List <UserDbModel> users = new List <UserDbModel>();
int i = 1;
using (var bqBuilder = new BatchQueryBuilder(config.ConnString))
{
while (reader.Read())
{
var user = UserDbModel.Create(reader);
if (Convert.ToString(reader["steamid"]) != "")
{
AuditLog(Convert.ToString(reader["member_id"]), "Steam", "Added", null, Convert.ToString(reader["steamid"]), bqBuilder);
}
if (reader["steamid"] != DBNull.Value && !SteamIds.Contains(Convert.ToString(reader["steamid"])))
{
CreateNonParsedSteamId(Convert.ToString(reader["steamid"]), bqBuilder);
}
if (Convert.ToString(reader["discord_id"]) != "")
{
AuditLog(Convert.ToString(reader["member_id"]), "Discord", "Added", null, Convert.ToString(reader["discord_id"]), bqBuilder);
}
if (reader["discord_id"] != DBNull.Value && Convert.ToString(reader["discord_id"]) != null && !DiscordIds.Contains(Convert.ToString(reader["discord_id"])))
{
CreateNonParsedDiscordId(Convert.ToString(reader["discord_id"]), bqBuilder);
}
if (Convert.ToString(reader["tw_id"]) != "")
{
AuditLog(Convert.ToString(reader["member_id"]), "Twitch", "Added", null, Convert.ToString(reader["tw_id"]), bqBuilder);
}
if (reader["tw_id"] != DBNull.Value && Convert.ToString(reader["tw_id"]) != null && !TwitchIds.Contains(Convert.ToString(reader["tw_id"])))
{
CreateNonParsedTwitchId(Convert.ToString(reader["tw_id"]), bqBuilder);
}
if (Convert.ToString(reader["ip_address"]) != "")
{
AuditLog(Convert.ToString(reader["member_id"]), "IP", "Added", null, Convert.ToString(reader["ip_address"]), bqBuilder);
}
if (reader["ip_address"] != DBNull.Value && Convert.ToString(reader["ip_address"]) != null && !IpAddresses.Contains(Convert.ToString(reader["ip_address"])))
{
CreateIpAddress(Convert.ToInt32(reader["member_id"]), Convert.ToString(reader["ip_address"]), bqBuilder);
}
command = new MySqlCommand();
command.CommandText = $@"INSERT INTO FamilyRpServerAccess.Users (ForumId, Email, SteamId, DiscordId, TwitchId, ForumBanned, ForumPostCount, ServerBanned, ForumPmCount, ForumGroups, IsAdmin, IsDev, IsPolice, IsEMS, IsFireDept, LastLoggedInForum, ForumDbRowChecksum, CurrentIP) VALUES (@ForumId, @Email, @SteamId, @DiscordId, @TwitchId, @ForumBanned, @ForumPostCount, @ServerBanned, @ForumPmCount, @ForumGroups, @IsAdmin, @IsDev, @IsPolice, @IsEMS, @IsFireDept, @LastLoggedInForum, @ForumDbRowChecksum, @CurrentIP);";
command.Parameters.AddWithValue("ForumId", user.Forum.Id);
command.Parameters.AddWithValue("SteamId", user.Steam.Id);
command.Parameters.AddWithValue("DiscordId", user.Discord.Id);
command.Parameters.AddWithValue("TwitchId", user.Twitch.Id);
command.Parameters.AddWithValue("ForumBanned", user.Forum.BanTime != 0 ? 1 : 0);
command.Parameters.AddWithValue("Email", user.Forum.Email);
command.Parameters.AddWithValue("ForumPostCount", user.Forum.PostCount);
command.Parameters.AddWithValue("ServerBanned", 0);
command.Parameters.AddWithValue("ForumPmCount", user.Forum.MessageCount);
command.Parameters.AddWithValue("ForumGroups", String.Join(",", user.Forum.Groups));
command.Parameters.AddWithValue("IsAdmin", user.Forum.Groups.Contains(10) ? 1 : 0);
command.Parameters.AddWithValue("IsDev", user.Forum.Groups.Contains(11) ? 1 : 0);
command.Parameters.AddWithValue("IsPolice", user.Forum.Groups.Contains(9) ? 1 : 0);
command.Parameters.AddWithValue("IsEMS", user.Forum.Groups.Contains(20) ? 1 : 0);
command.Parameters.AddWithValue("IsFireDept", 0);
command.Parameters.AddWithValue("LastLoggedInForum", user.Forum.LastVisit);
command.Parameters.AddWithValue("ForumDbRowChecksum", user.Forum.RowChecksum);
command.Parameters.AddWithValue("CurrentIP", user.Forum.CurrentIP);
bqBuilder.AppendSqlCommand(command);
Console.WriteLine($"Populating row #{i} with forum id #{user.Forum.Id}");
AuditLog(user.Forum.Id.ToString(), "Forum", "Create", "", "", bqBuilder);
UpdateGroups(user.Forum.Id.ToString(), user.Forum.Groups, bqBuilder);
i++;
}
}
reader.Dispose();
}
return(Task.CompletedTask);
}
private static void UpdateGroups(string forumId, List <int> groups, BatchQueryBuilder batchQueryBuilder)
{
batchQueryBuilder.AppendSqlCommand($"DELETE FROM FamilyRpServerAccess.GroupMembership WHERE ForumId = {forumId};");
batchQueryBuilder.AppendSqlCommand($"INSERT INTO FamilyRpServerAccess.GroupMembership (ForumId, GroupMembership.Group) VALUES {String.Join(", ", groups.Select(g => $"({forumId}, {g})"))};");
}
private static void AuditLog(string ForumId, string AccountType = "", string ChangeType = "", string OldValue = "", string NewValue = "", BatchQueryBuilder batchQueryBuilder = null)
{
if (batchQueryBuilder != null)
{
batchQueryBuilder.AppendSqlCommand(new MySqlCommand($@"INSERT INTO FamilyRpServerAccess.Log (Date, AccountType, ChangeType, OldValue, NewValue, ForumId) VALUES
(NOW(),
{(AccountType == null ? "null" : $"'{MySqlHelper.EscapeString(AccountType)}'")},
{(ChangeType == null ? "null" : $"'{MySqlHelper.EscapeString(ChangeType)}'")},
{(OldValue == null ? "null" : $"'{MySqlHelper.EscapeString(OldValue)}'")},
{(NewValue == null ? "null" : $"'{MySqlHelper.EscapeString(NewValue)}'")},
{(ForumId == null ? "null" : ForumId)});"));
}
}
