QVjDic3x

1USE [sKN_online]
2GO
3/****** Object:  StoredProcedure [dbo].[UPDATE_PERSONAL_RANK]    Script Date: 06/03/2011 16:09:40 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE  [dbo].[UPDATE_PERSONAL_RANK]
9AS
10
11SET NOCOUNT ON 
12IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
13DROP TABLE [dbo].[KUSER_RATING_MONTHLY] 
14IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
15DROP TABLE [dbo].[EUSER_RATING_MONTHLY] 
16CREATE TABLE [dbo].[KUSER_RATING_MONTHLY] ( 
17    [nRank] [int] IDENTITY (1, 1) NOT NULL , 
18    [strUserID] [char] (21) NULL, 
19    [strKnightsName] [char] (21) NULL , 
20    [nLoyaltyMonthly] [int] NULL , 
21) ON [PRIMARY] 
22CREATE TABLE [dbo].[EUSER_RATING_MONTHLY] ( 
23    [nRank] [int] IDENTITY (1, 1) NOT NULL , 
24    [strUserID] [char] (21) NULL, 
25    [strKnightsName] [char] (21) NULL , 
26    [nLoyaltyMonthly] [int] NULL , 
27) ON [PRIMARY] 
28INSERT INTO KUSER_RATING_MONTHLY
29   select top 100 strUserID, Loyalty,  LoyaltyMonthly, 101
30	from USERDATA
31	where Nation = 1 and Authority = '1'
32	order by LoyaltyMonthly desc
33
34INSERT INTO EUSER_RATING_MONTHLY
35   select top 100 strUserID, Loyalty,  LoyaltyMonthly, 101
36	from USERDATA
37	where Nation = 2 and Authority = '1'
38	order by LoyaltyMonthly desc
39
40CREATE  INDEX [IX_RATING_RANK] ON [dbo].[KUSER_RATING_MONTHLY]([nRank]) ON [PRIMARY] 
41CREATE  INDEX [IX_RATING_RANK] ON [dbo].[EUSER_RATING_MONTHLY]([nRank]) ON [PRIMARY] 
42
43DECLARE @CheckUserPersonalRankTable tinyint
44SELECT @CheckUserPersonalRankTable = Count(*) FROM USER_PERSONAL_RANK
45IF @CheckUserPersonalRankTable < 100
46BEGIN
47 DECLARE @RankName varchar(30)
48 DECLARE @I int
49 
50 SET @I = 1
51 WHILE @I < 101
52 BEGIN
53  IF @I = 1
54  BEGIN
55   SET @RankName = 'Gold Knight'
56  END
57  IF @I > 1 AND @I <= 4
58  BEGIN
59   SET @RankName = 'Silver Knight'
60  END
61  IF @I > 4 AND @I <= 9
62  BEGIN
63   SET @RankName = 'Mirage Knight'
64  END
65  IF @I > 9 AND @I <= 10
66  BEGIN
67   SET @RankName = 'Shadow Knight'
68  END
69  IF @I > 25 AND @I <= 50
70  BEGIN
71   SET @RankName = 'Mist Knight'
72  END
73  IF @I > 50 AND @I <= 100
74  BEGIN
75   SET @RankName = 'Training Knight'
76  END
77  
78  INSERT INTO USER_PERSONAL_RANK VALUES (@I,@RankName,0,NULL,0,1000000,0,NULL,NULL,1000000,1000000,GETDATE())
79 
80  SET @I = @I + 1
81 END
82END
83
84DECLARE @strCharID char(21) 
85DECLARE @strKnightsID char(21) 
86DECLARE @nIndex smallint 
87DECLARE @nLoyaltyMonthly int 
88SET @strCharID = NULL 
89SET @strKnightsID = NULL 
90SET @nIndex = 0 
91SET @nLoyaltyMonthly = 0 
92DECLARE CRS_KARUS CURSOR FOR 
93SELECT nRank, strUserID, strKnightsName, nLoyaltyMonthly FROM KUSER_RATING_MONTHLY 
94OPEN CRS_KARUS 
95FETCH NEXT FROM CRS_KARUS 
96INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly 
97WHILE @@FETCH_STATUS = 0  
98BEGIN 
99       
100 UPDATE USER_PERSONAL_RANK SET strKarusUserID = @strCharID, nKarusLoyaltyMonthly = @nLoyaltyMonthly WHERE nRank = @nIndex 
101             
102    FETCH NEXT FROM CRS_KARUS 
103    INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly 
104END 
105CLOSE CRS_KARUS 
106DEALLOCATE CRS_KARUS 
107DECLARE CRS_HUMAN CURSOR FOR 
108SELECT nRank, strUserID, strKnightsName, nLoyaltyMonthly FROM EUSER_RATING_MONTHLY 
109OPEN CRS_HUMAN 
110FETCH NEXT FROM CRS_HUMAN 
111INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly 
112WHILE @@FETCH_STATUS = 0  
113BEGIN 
114    
115 UPDATE USER_PERSONAL_RANK SET strElmoUserID = @strCharID, nElmoLoyaltyMonthly = @nLoyaltyMonthly WHERE nRank = @nIndex 
116             
117    FETCH NEXT FROM CRS_HUMAN 
118    INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly 
119END 
120CLOSE CRS_HUMAN 
121DEALLOCATE CRS_HUMAN 
122IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
123DROP TABLE [dbo].[KUSER_RATING_MONTHLY] 
124IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
125DROP TABLE [dbo].[EUSER_RATING_MONTHLY]