Example #1
0
        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();
            }
        }
Example #2
0
        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);
        }