xusNGJ19

1USE [KN_Online]
2GO
3/****** Object:  StoredProcedure [dbo].[UPDATE_PERSONAL_RANK]    Script Date: 04/11/2011 15:54:48 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [dbo].[UPDATE_PERSONAL_RANK]
9
10AS
11
12IF exists (select * from sysobjects where id = object_id(N'[dbo].[KPERSONAL_RANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
13DROP TABLE [dbo].[KPERSONAL_RANK]
14
15IF exists (select * from sysobjects where id = object_id(N'[dbo].[EPERSONAL_RANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
16DROP TABLE [dbo].[EPERSONAL_RANK]
17
18CREATE TABLE [KPERSONAL_RANK]
19(
20    [nRank] [int] IDENTITY (1, 1) NOT NULL ,
21    [strUserID] char(21) NOT NULL,
22    [LoyaltyMonthly] int NOT NULL,
23    [Loyalty] int NOT NULL,
24    [nRankUP] int NOT NULL
25) ON [PRIMARY]
26
27CREATE TABLE [EPERSONAL_RANK]
28(
29    [nRank] [int] IDENTITY (1, 1) NOT NULL ,
30    [strUserID] char(21) NOT NULL,
31    [LoyaltyMonthly] int NOT NULL,
32    [Loyalty] int NOT NULL,
33    [nRankUP] int NOT NULL
34) ON [PRIMARY]
35
36INSERT  KPERSONAL_RANK
37    SELECT TOP 100 strUserID, LoyaltyMonthly, Loyalty, 101
38    FROM USERDATA
39    WHERE Nation = 1 AND Authority = 1
40    ORDER BY LoyaltyMonthly desc, Loyalty desc
41
42INSERT  EPERSONAL_RANK
43    SELECT TOP 100 strUserID, LoyaltyMonthly, Loyalty, 101
44    FROM USERDATA
45    WHERE Nation = 2 AND Authority = 1
46    ORDER BY LoyaltyMonthly desc, Loyalty desc
47
48UPDATE KPERSONAL_RANK
49    SET nRankUP = R.nRank - KPERSONAL_RANK.nRank
50    FROM USER_PERSONAL_RANK R
51    WHERE R.strKarusUserID = KPERSONAL_RANK.strUserID
52
53UPDATE EPERSONAL_RANK
54    SET nRankUP = R.nRank - EPERSONAL_RANK.nRank
55    FROM USER_PERSONAL_RANK R
56    WHERE R.strElmoUserID = EPERSONAL_RANK.strUserID
57
58DECLARE @tUpdateDate smalldatetime
59SET @tUpdateDate =GETDATE()
60
61TRUNCATE TABLE USER_PERSONAL_RANK
62
63INSERT USER_PERSONAL_RANK
64    SELECT E.nRank, 1, E.nRankUP, E.strUserID, E.LoyaltyMonthly, 1, K.nRankUP, K.strUserID, K.LoyaltyMonthly, 1, 1, @tUpdateDate
65    FROM EPERSONAL_RANK E, KPERSONAL_RANK K
66    WHERE E.nRank = K.nRank
67    ORDER BY E.nRank
68
69UPDATE USER_PERSONAL_RANK SET strPosition = 'Gold Knight', nElmoCheck = 1000000, nKarusCheck = 1000000, nSalary = 100000000  WHERE nRank = 1
70UPDATE USER_PERSONAL_RANK SET strPosition = 'Silver Knight', nElmoCheck = 800000, nKarusCheck = 800000, nSalary = 70000000  WHERE nRank>= 2 AND nRank<= 4
71UPDATE USER_PERSONAL_RANK SET strPosition = 'Mirage Knight', nElmoCheck = 600000, nKarusCheck = 600000, nSalary = 50000000  WHERE nRank>= 5 AND nRank<= 9 
72UPDATE USER_PERSONAL_RANK SET strPosition = 'Shadow Knight', nElmoCheck = 400000, nKarusCheck = 400000, nSalary = 30000000  WHERE nRank>= 10 AND nRank<= 25
73UPDATE USER_PERSONAL_RANK SET strPosition = 'Mist Knight', nElmoCheck = 200000, nKarusCheck = 200000, nSalary = 20000000  WHERE nRank>= 26 AND nRank<= 50
74UPDATE USER_PERSONAL_RANK SET strPosition = 'Training Knight', nElmoCheck = 50000, nKarusCheck = 50000, nSalary = 5000000  WHERE nRank>= 51
75
76BEGIN TRAN
77DELETE FROM USER_PERSONAL_RANK WHERE UpdateDate < @tUpdateDate
78COMMIT TRAN
79
80IF exists (select * from sysobjects where id = object_id(N'[dbo].[KPERSONAL_RANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
81DROP TABLE [dbo].[KPERSONAL_RANK]
82
83IF exists (select * from sysobjects where id = object_id(N'[dbo].[EPERSONAL_RANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
84DROP TABLE [dbo].[EPERSONAL_RANK]