· 9 years ago · Oct 21, 2016, 04:02 PM
1USE [ACCOUNT_DBF]
2GO
3
4/****** Object: Table [dbo].[MAC_BAN_TBL] Script Date: 10/21/2016 11:59:03 PM ******/
5DROP TABLE [dbo].[MAC_BAN_TBL]
6GO
7
8/****** Object: Table [dbo].[MAC_BAN_TBL] Script Date: 10/21/2016 11:59:03 PM ******/
9SET ANSI_NULLS ON
10GO
11
12SET QUOTED_IDENTIFIER ON
13GO
14
15SET ANSI_PADDING ON
16GO
17
18CREATE TABLE [dbo].[MAC_BAN_TBL](
19 [id] [int] IDENTITY(1,1) NOT NULL,
20 [macaddr] [varchar](40) NULL
21) ON [PRIMARY]
22
23GO
24
25SET ANSI_PADDING OFF
26GO
27
28
29USE [ACCOUNT_DBF]
30GO
31
32/****** Object: StoredProcedure [dbo].[ACCOUNT_STR] Script Date: 10/22/2016 12:00:13 AM ******/
33DROP PROCEDURE [dbo].[ACCOUNT_STR]
34GO
35
36/****** Object: StoredProcedure [dbo].[ACCOUNT_STR] Script Date: 10/22/2016 12:00:13 AM ******/
37SET ANSI_NULLS ON
38GO
39
40SET QUOTED_IDENTIFIER ON
41GO
42
43CREATE PROC [dbo].[ACCOUNT_STR]
44@iaccount VARCHAR(32),
45@ipassword VARCHAR(16)
46/***********************************************************************************
47***********************************************************************************
48***********************************************************************************
49***********************************************************************************
50
51
52
53 ACCOUNT_STR ????
54 ??? : ???
55 ??? : 2004.01.18
56
57 ex) ACCOUNT_STR 'beat','1234'
58
59***********************************************************************************
60***********************************************************************************
61***********************************************************************************
62***********************************************************************************/
63AS
64set nocount on
65IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b
66 WHERE a.account = b.account AND a.account = @iaccount AND gamecode = 'A000')
67 BEGIN
68 IF EXISTS(SELECT account FROM ACCOUNT_TBL
69 WHERE account = @iaccount AND password = @ipassword )
70-- BEGIN
71--
72-- DECLARE @birthyear CHAR(4),@currdate CHAR(8)
73-- SELECT @birthyear = CASE WHEN SUBSTRING(id_no2,1,1) IN ('9','0') THEN '18' + SUBSTRING(id_no1,1,2)
74-- WHEN SUBSTRING(id_no2,1,1) IN ('1','2') THEN '19' + SUBSTRING(id_no1,1,2)
75-- WHEN SUBSTRING(id_no2,1,1) IN ('3','4') THEN '20' + SUBSTRING(id_no1,1,2)
76-- WHEN SUBSTRING(id_no2,1,1) IN ('5','6') THEN '21' + SUBSTRING(id_no1,1,2)
77-- WHEN SUBSTRING(id_no2,1,1) IN ('7','8') THEN '22' + SUBSTRING(id_no1,1,2)
78-- END
79-- FROM ACCOUNT_TBL
80-- WHERE account = @iaccount
81--
82-- SELECT @currdate = CONVERT(CHAR(8),GETDATE(),112)
83--
84-- SELECT fError = CASE WHEN a.id_no1 = 'a00000'
85-- THEN '0'
86-- WHEN b.BlockTime >= @currdate AND b.EndTime < @currdate
87-- THEN '3'
88-- WHEN a.realname = 'F'
89-- THEN '4'
90-- WHEN @birthyear > DATEADD(Year,-13,@currdate)
91-- THEN '5'
92-- WHEN @birthyear >= DATEADD(Year,-11,@currdate) AND b.tester = '0'
93-- THEN '6'
94-- ELSE '0' END,
95--
96-- fText = CASE WHEN a.id_no1 = 'a00000'
97-- THEN '?? ? ??'
98-- WHEN b.BlockTime >= @currdate AND b.EndTime < @currdate
99-- THEN '??????? ??? ??'
100-- WHEN a.realname = 'F'
101-- THEN '????? ???'
102-- WHEN @birthyear > DATEADD(Year,-13,@currdate)
103-- THEN '???? 12? ?? ??? ??? ????? ?? ????.'
104-- WHEN @birthyear >= DATEADD(Year,-11,@currdate) AND b.tester = '0'
105-- THEN '14? ?? ??? ??? ??? ???? ????? ?? ??? ?????'
106-- ELSE '????? 14? ??' END
107-- FROM ACCOUNT_TBL a, ACCOUNT_TBL_DETAIL b
108-- WHERE a.account = b.account AND b.account = @iaccount AND a.[password] = @ipassword
109-- END
110 BEGIN
111 SELECT fError = '0', fText = 'OK'
112 END
113 ELSE
114 BEGIN
115 SELECT fError = '1', fText = 'Wrong Password !!'
116 END
117 END
118ELSE
119 BEGIN
120 SELECT fError = '2', fText = 'Account Not Exists !!'
121 END
122RETURN
123
124-- ?? ?? Rule
125-- 1. (fError=2 ??) ????? ??. ????? "gamecode = A000" ? ??? ??.
126-- 2. (fError=1 ??) ????.
127-- 3. (fError=0 ??) ?? ? ?? ??( id_no1? a0000 )?? ??.??.??? ??
128-- 4. (fError=3 ??) ?????, ???? ??.
129-- 5. (fError=4 ??) ????.
130-- 6. (fError=5 ??) 12? ???? ??. ???? "???? 12? ?? ??? ??? ]
131-- ????? ?? ????." ?? ??.
132-- 7. (fError=6 ??) ?????? ?? ?? ?? tester = 0 ?? "14? ?? ??? ???
133-- ?????? ????? ?? ??? ?????"?? ??.
134-- 8. (fError=0 ??) ?? ???? ?? ???? ??.
135set nocount off
136
137GO
138
139
140USE [ACCOUNT_DBF]
141GO
142
143/****** Object: StoredProcedure [dbo].[LOGIN_STR] Script Date: 10/22/2016 12:00:37 AM ******/
144SET ANSI_NULLS ON
145GO
146
147SET QUOTED_IDENTIFIER ON
148GO
149
150CREATE PROC [dbo].[LOGIN_STR]
151 @iaccount VARCHAR(32),
152 @ipassword char(32)
153 -- Ver. 14 PCZoneIP
154 ,@i_IPAddress varchar(15) = '0.0.0.0'
155 ,@i_MacAddress varchar(38) = ''
156/***********************************************************************************
157***********************************************************************************
158***********************************************************************************
159***********************************************************************************
160
161
162
163 ACCOUNT_STR ????
164 ??? : ???
165 ??? : 2004.01.18
166
167 ex) ACCOUNT_STR 'beat','1234'
168SELECT * FROM ACCOUNT_TBL_DETAIL WHERE account='aeonsoft'
169
170***********************************************************************************
171***********************************************************************************
172***********************************************************************************
173***********************************************************************************/
174AS
175set nocount on
176
177-- Ver 14. PCZoneIP_Check
178declare @o_Grade tinyint
179exec dbo.USP_PCZoneIP_Check @i_IPAddress, @o_Grade output
180
181IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b
182 WHERE a.account = b.account AND a.account = @iaccount ) -- AND gamecode = 'A000')
183BEGIN
184
185 IF EXISTS(SELECT id FROM MAC_BAN_TBL WHERE macaddr = @i_MacAddress)
186 BEGIN
187 SELECT fError = '10', fText = 'Mac address banned !!',fCheck ='',f18='1', fPCZone = '0'
188 END
189 ELSE
190 BEGIN
191
192 DECLARE @curDate char(8)
193
194 IF EXISTS(SELECT account FROM ACCOUNT_TBL
195 WHERE account = @iaccount AND password = @ipassword) BEGIN
196
197 SELECT @curDate=CONVERT(CHAR(8), getdate(), 112)
198
199 UPDATE ACCOUNT_TBL_DETAIL SET lastmac = @i_MacAddress WHERE account = @iaccount
200
201 SELECT fError=CASE
202 -- WHEN session<>@isession OR sessionExpireDt<getdate() THEN '91'
203 WHEN BlockTime>=@curDate THEN '9'
204 ELSE '0' END,
205 fText= CASE
206 -- WHEN session<>@isession OR sessionExpireDt<getdate() THEN 'Session Expired'
207 WHEN BlockTime>=@curDate THEN 'Block' ELSE 'OK' END,
208 fCheck=tester,
209 f18='1'
210 -- Ver14. PCZoneIP
211 ,fPCZone = @o_Grade
212 FROM ACCOUNT_TBL a INNER JOIN ACCOUNT_TBL_DETAIL b ON (a.account=b.account)
213 WHERE a.account=@iaccount
214 END
215 ELSE BEGIN
216 SELECT fError = '1', fText = 'Wrong Password !!',fCheck ='',f18='1', fPCZone = '0' -- PCZoneIP (, fPCZone = '0' ??)
217 END
218 END
219END
220ELSE BEGIN
221 SELECT fError = '2', fText = 'Account Not Exists !!',fCheck ='',f18='1', fPCZone = '0' -- PCZoneIP (, fPCZone = '0' ??)
222END
223
224RETURN
225
226-- ?? ?? Rule
227-- 1. (fError=2 ??) ????? ??. ????? "gamecode = A000" ? ??? ??.
228-- 2. (fError=1 ??) ????.
229-- 3. (fError=0 ??) ?? ? ?? ??( id_no1? a0000 )?? ??.??.??? ??
230-- 4. (fError=3 ??) ?????, ???? ??.
231-- 5. (fError=4 ??) ????.
232-- 6. (fError=5 ??) 12? ???? ??. ???? "???? 12? ?? ??? ??? ]
233-- ????? ?? ????." ?? ??.
234-- 7. (fError=6 ??) ?????? ?? ?? ?? tester = 0 ?? "14? ?? ??? ???
235-- ?????? ????? ?? ??? ?????"?? ??.
236-- 8. (fError=0 ??) ?? ???? ?? ???? ??.
237set nocount off
238
239GO
240
241
242USE [ACCOUNT_DBF]
243GO
244
245/****** Object: StoredProcedure [dbo].[MAC_BAN_ACC] Script Date: 10/22/2016 12:00:59 AM ******/
246SET ANSI_NULLS ON
247GO
248
249SET QUOTED_IDENTIFIER ON
250GO
251
252-- =============================================
253-- Author: <Author,,Name>
254-- Create date: <Create Date,,>
255-- Description: <Description,,>
256-- =============================================
257CREATE PROCEDURE [dbo].[MAC_BAN_ACC]
258 -- Add the parameters for the stored procedure here
259 @iAccount varchar(64)
260AS
261BEGIN
262 -- SET NOCOUNT ON added to prevent extra result sets from
263 -- interfering with SELECT statements.
264 SET NOCOUNT ON;
265
266 -- Insert statements for procedure here
267 declare @lastmac as varchar(38)
268 select @lastmac = (SELECT lastmac FROM ACCOUNT_TBL_DETAIL WHERE account = @iAccount)
269 INSERT INTO MAC_BAN_TBL (macaddr)
270 VALUES (@lastmac)
271END
272
273GO