HEfJhYhm

1DROP TABLE IF EXISTS guild_duplicates;
2CREATE TABLE guild_duplicates (
3    guild_id INT(10) DEFAULT 0,
4    copied_guild_id INT(10) NOT NULL,
5    PRIMARY KEY (`copied_guild_id`)
6);
7
8INSERT IGNORE INTO guild_duplicates
9SELECT n1.guildid, n2.guildid FROM guild n1, guild n2 WHERE n2.guildid > n1.guildid AND n1.name = n2.name;
10
11DROP PROCEDURE IF EXISTS UpdateGuildMembers;
12DELIMITER //
13CREATE PROCEDURE UpdateGuildMembers()
14BEGIN
15	DECLARE guild_id_ INT;
16	DECLARE copied_guild_id_ INT;
17	DECLARE currentCount INT;
18	DECLARE entryCount INT;
19	
20	DECLARE cur1 CURSOR FOR SELECT guild_id, copied_guild_id FROM guild_duplicates ORDER BY copied_guild_id_;
21	
22	SET currentCount = 1;
23
24	SELECT COUNT(*) INTO entryCount FROM guild_duplicates;
25	
26	OPEN cur1;
27	WHILE currentCount <= entryCount DO
28		FETCH cur1 INTO guild_id_, copied_guild_id_;
29		UPDATE `guild_member` SET `guildid`=`guild_id_` WHERE `guildid`=`copied_guild_id_`;
30		SET currentCount = currentCount + 1;
31	END WHILE;
32END //
33DELIMITER ;
34
35CALL UpdateGuildMembers();
36
37DELETE FROM `guild` WHERE `guildid` IN (SELECT copied_guild_id FROM guild_duplicates);
38
39DROP TABLE guild_duplicates;
40
41-- // Guild Ranks
42DELETE FROM `guild_rank` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
43
44DROP PROCEDURE IF EXISTS AddMissingRank;
45DELIMITER //
46CREATE PROCEDURE AddMissingRank()
47BEGIN
48	DECLARE guild_id_ INT;
49	DECLARE currentCount INT;
50	DECLARE entryCount INT;
51	
52	DECLARE cur1 CURSOR FOR SELECT guildid FROM guild_rank WHERE guildid IN (SELECT guildid FROM guild) AND guildid IN (SELECT guildid FROM guild_rank GROUP BY guildid HAVING COUNT(rid) = 1) ORDER BY guildid;
53	
54	SET currentCount = 1;
55
56	SELECT COUNT(*) INTO entryCount FROM guild_rank WHERE guildid IN (SELECT guildid FROM guild) AND guildid IN (SELECT guildid FROM guild_rank GROUP BY guildid HAVING COUNT(rid) = 1) ORDER BY guildid;
57	
58	OPEN cur1;
59	WHILE currentCount <= entryCount DO
60		FETCH cur1 INTO guild_id_;
61		DELETE FROM `guild_rank` WHERE rid > 1;
62		DELETE FROM `guild_rank` WHERE rid < 1;
63		INSERT IGNORE INTO `guild_rank` VALUES (guild_id_,2,'Member',67,0);
64		SET currentCount = currentCount + 1;
65	END WHILE;
66END //
67DELIMITER ;
68
69CALL AddMissingRank();
70
71DROP PROCEDURE IF EXISTS CreateGuildRanks;
72DELIMITER //
73CREATE PROCEDURE CreateGuildRanks()
74BEGIN
75	DECLARE guild_id_ INT;
76	DECLARE currentCount INT;
77	DECLARE entryCount INT;
78	
79	DECLARE cur1 CURSOR FOR SELECT guildid FROM guild WHERE guildid NOT IN (SELECT guildid FROM guild_rank) ORDER BY guildid;
80	
81	SET currentCount = 1;
82
83	SELECT COUNT(*) INTO entryCount FROM guild WHERE guildid NOT IN (SELECT guildid FROM guild_rank) ORDER BY guildid;
84	
85	OPEN cur1;
86	WHILE currentCount <= entryCount DO
87		FETCH cur1 INTO guild_id_;
88		INSERT IGNORE INTO `guild_rank` VALUES (guild_id_,1,'Guild Master',913919,0);
89		INSERT IGNORE INTO `guild_rank` VALUES (guild_id_,2,'Member',67,0);
90		SET currentCount = currentCount + 1;
91	END WHILE;
92END //
93DELIMITER ;
94
95CALL CreateGuildRanks();
96
97-- Guild Members
98
99DROP PROCEDURE IF EXISTS UpdateGuildMemberRanks;
100DELIMITER //
101CREATE PROCEDURE UpdateGuildMemberRanks()
102BEGIN
103	DECLARE guild_id_ INT;
104	DECLARE guild_leader_ INT;
105	DECLARE currentCount INT;
106	DECLARE entryCount INT;
107	
108	DECLARE cur1 CURSOR FOR SELECT guildid, leaderguid FROM guild ORDER BY guildid;
109	
110	SET currentCount = 1;
111
112	SELECT COUNT(*) INTO entryCount FROM guild ORDER BY guildid;
113	
114	OPEN cur1;
115	WHILE currentCount <= entryCount DO
116		FETCH cur1 INTO guild_id_, guild_leader_;
117		UPDATE `guild_member` SET `rank`=0 WHERE `guildid`=guild_id_ AND `guid`=guild_leader_;
118		UPDATE `guild_member` SET `rank`=1 WHERE `guildid`=guild_id_ AND `guid` != guild_leader_ AND `rank`=0;
119		SET currentCount = currentCount + 1;
120	END WHILE;
121END //
122DELIMITER ;
123
124CALL UpdateGuildMemberRanks();
125
126DELETE FROM `guild_announce_cooldown` WHERE `guild_id` NOT IN (SELECT `guildid` FROM `guild`);
127DELETE FROM `guild_bank_eventlog` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
128DELETE FROM `guild_bank_item` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
129DELETE FROM `guild_bank_right` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
130DELETE FROM `guild_bank_tab` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
131DELETE FROM `guild_eventlog` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);