· 6 years ago · Aug 02, 2019, 07:20 PM
1CREATE DEFINER=`root`@`localhost` PROCEDURE `ProcGetMessageMemberInfo_One`(IN @PlayerUID int, IN friend_uid int)
2BEGIN
3 DECLARE u_id,
4 unknown_1,
5 unknown_2,
6 unknown_3,
7 unknown_4,
8 unknown_5,
9 unknown_6,
10 flag_1,
11 flag_state,
12 player_flag INT(10) DEFAULT 0;
13 DECLARE nickname VARCHAR(23);
14 DECLARE apelido_in VARCHAR(12);
15 DECLARE FINISHED INT DEFAULT 0;
16
17 DECLARE MSN_CURSOR CURSOR FOR
18 ( SELECT a.nick,
19 b.uid_friend,
20 b.apelido,
21 b.unknown1,
22 b.unknown2,
23 b.unknown3,
24 b.unknown4,
25 b.unknown5,
26 b.unknown6,
27 b.flag1,
28 b.state_flag,
29 @Flag := 1 AS FLAG_PLAYER
30 FROM account a, pangya_friend_list b
31 WHERE b.uid = @PlayerUID AND a.uid = b.uid_friend AND b.uid_friend = friend_uid
32 )
33 UNION ALL
34 ( SELECT a.nick,
35 @uid_fr := c.member_uid as uid_friend,
36 @apel := "" as apelido,
37 @unk1 := -1 as unknwon1,
38 @unk2 := 0 as unknwon2,
39 @unk3 := -1 as unknwon3,
40 @unk4 := 0 as unknwon4,
41 @unk5 := 0 as unknwon5,
42 @unk6 := 0 as unknown6,
43 @flag1 := -1 as flag1,
44 @flag4 := 0 as state_flag,
45 @Flag := 2 AS FLAG_PLAYER
46 FROM account a, pangya_guild_member c
47 WHERE c.GUILD_UID = (SELECT i.GUILDINDEX FROM Pangya_Member i, pangya_guild_member j WHERE i.UID = @PlayerUID AND i.GUILD_ID = j.GUILD_ID AND j.GUILD_MEMBER_UID = @PlayerUID AND j.GUILD_MEMBER_STATUS < 9)
48 AND c.member_uid = a.uid AND c.member_uid = friend_uid AND c.MEMBER_STATE_FLAG != 9
49 );
50
51 DECLARE CONTINUE HANDLER
52 FOR NOT FOUND SET FINISHED = 1;
53
54 DROP TEMPORARY TABLE IF EXISTS `temp_msn_one`;
55 CREATE TEMPORARY TABLE `temp_msn_one`(nick VARCHAR(23) NOT NULL,
56 uid INT(10) NOT NULL,
57 apelido VARCHAR(12),
58 unknown1 INT(10),
59 unknown2 INT(10),
60 unknown3 INT(10),
61 unknown4 INT(10),
62 unknown5 INT(10),
63 unknown6 INT(10),
64 flag1 INT(10),
65 state_flag INT(10),
66 FLAG_PLAYER INT(10));
67 OPEN MSN_CURSOR;
68
69 MSN_LOOP : LOOP
70 FETCH MSN_CURSOR INTO nickname, u_id, apelido_in, unknown_1, unknown_2, unknown_3,
71 unknown_4, unknown_5, unknown_6, flag_1, flag_state, player_flag;
72
73 IF FINISHED = 1 THEN
74 LEAVE MSN_LOOP;
75 END IF;
76
77 IF (SELECT uid FROM temp_msn_one WHERE UID = u_id) IS NOT NULL THEN
78 UPDATE temp_msn_one SET FLAG_PLAYER = FLAG_PLAYER + player_flag WHERE UID = u_id;
79 ELSE
80 INSERT INTO temp_msn_one VALUES(nickname, u_id, apelido_in, unknown_1, unknown_2, unknown_3,
81 unknown_4, unknown_5, unknown_6, flag_1, flag_state, player_flag);
82 END IF;
83 END LOOP MSN_LOOP;
84
85 CLOSE MSN_CURSOR;
86
87 SELECT * FROM temp_msn_one;
88END
89
90USE [DB_Pangya]
91GO
92/****** Object: StoredProcedure [dbo].[ProcGetMessageMemberInfo_One] Script Date: 02/08/2019 13:04:42 ******/
93SET ANSI_NULLS ON
94GO
95SET QUOTED_IDENTIFIER ON
96GO
97
98CREATE PROCEDURE [dbo].[ProcGetMessageMemberInfo_One]
99
100@PLAYER_UID INT,
101 @friend_uid int
102AS
103BEGIN
104 SET NOCOUNT ON;
105 DECLARE @u_id INT =0;
106 DECLARE @unknown_1 INT =0;
107 DECLARE @unknown_2 INT =0;
108 DECLARE @unknown_3 INT =0;
109 DECLARE @unknown_4 INT =0;
110 DECLARE @unknown_5 INT =0;
111 DECLARE @unknown_6 INT =0;
112 DECLARE @flag_1 INT =0;
113 DECLARE @flag_state INT =0;
114 DECLARE @player_flag INT = 0;
115 DECLARE @nickname VARCHAR(23);
116 DECLARE @apelido_in VARCHAR(12);
117 DECLARE @FINISHED INT = 0;
118 DECLARE @uid_fr INT =0;
119 DECLARE @apel VARCHAR(23) = '';
120 DECLARE @unk1 INT = -1;
121 DECLARE @unk2 INT = 0;
122 DECLARE @unk3 INT = -1;
123 DECLARE @unk4 INT = 0;
124 DECLARE @unk5 INT = 0;
125 DECLARE @unk6 INT = 0;
126 DECLARE @flag1 INT = -1;
127 DECLARE @flag4 INT = 0;
128 DECLARE @Flag INT = 2;
129
130 DECLARE MSN_CURSOR CURSOR FOR
131 ( SELECT a.Nickname,
132 b.Friend_UID,
133 b.Friend_Nick,
134 b.UN,
135 b.UN2,
136 b.UN3,
137 b.UN4,
138 b.UN5,
139 b.UN6,
140 b.flag1,
141 b.StatusFlag,
142 1 AS FLAG_PLAYER
143 FROM Pangya_Member a, pangya_friend_list b
144 WHERE b.uid = @PLAYER_UID AND a.uid = b.Friend_UID AND b.Friend_UID = @friend_uid
145 )
146
147 UNION ALL
148 ( SELECT a.Nickname,
149 c.GUILD_MEMBER_UID as uid_friend,
150 @apel as apelido,
151 @unk1 as unknwon1,
152 @unk2 as unknwon2,
153 @unk3 as unknwon3,
154 @unk4 as unknwon4,
155 @unk5 as unknwon5,
156 @unk6 as unknown6,
157 @flag1 as flag1,
158 @flag4 as state_flag,
159 @Flag AS FLAG_PLAYER
160 FROM pangya_member a, pangya_guild_member c
161 WHERE c.GUILD_ID = (SELECT i.GUILDINDEX FROM Pangya_Member i, pangya_guild_member j WHERE i.UID = @PLAYER_UID AND i.GUILDINDEX = j.GUILD_ID AND j.GUILD_MEMBER_UID = @PLAYER_UID AND j.GUILD_MEMBER_STATUS < 9)
162 and c.GUILD_MEMBER_UID = a.uid AND c.GUILD_MEMBER_UID = @friend_uid AND c.GUILD_MEMBER_STATUS != 9
163 );
164
165 --DECLARE CONTINUE HANDLER FOR NOT FOUND SET @FINISHED = 1;
166
167
168 IF (OBJECT_ID('TEMPDB..#temp_msn_one') IS NOT NULL)
169 DROP TABLE #temp_msn_one
170 create table #temp_msn_one(nick VARCHAR(23) NOT NULL,
171 uid INT NOT NULL,
172 apelido VARCHAR(12),
173 unknown1 INT,
174 unknown2 INT,
175 unknown3 INT,
176 unknown4 INT,
177 unknown5 INT,
178 unknown6 INT,
179 flag1 INT,
180 state_flag INT,
181 FLAG_PLAYER INT);
182
183--OPEN MSN_CURSOR;
184
185 -- MSN_LOOP : LOOP
186 --FETCH MSN_CURSOR INTO @nickname, @u_id, @apelido_in, @unknown_1, @unknown_2, @unknown_3,
187 -- @unknown_4, @unknown_5, @unknown_6, @flag_1, @flag_state, @player_flag;
188
189 -- IF @FINISHED = 1 begin
190 -- LEAVE MSN_LOOP;
191 --END
192
193 -- IF (SELECT uid FROM #temp_msn_one WHERE UID = @u_id) IS NOT NULL begin
194 -- UPDATE #temp_msn_one SET FLAG_PLAYER = FLAG_PLAYER + @player_flag WHERE UID = @u_id;
195 --ELSE
196 -- INSERT INTO #temp_msn_one VALUES(@nickname, @u_id, @apelido_in, @unknown_1, @unknown_2, @unknown_3,
197 -- @unknown_4, @unknown_5, @unknown_6, @flag_1, @flag_state, @player_flag);
198 --END
199 -- END LOOP MSN_LOOP;
200
201 --CLOSE MSN_CURSOR;
202
203 SELECT * FROM #temp_msn_one;
204
205END