a8RqHTMe

1USE [KN_Online]
2GO
3/****** Object:  StoredProcedure [dbo].[UPDATE_PERSONAL_RANK]    Script Date: 04/08/2011 18:30:13 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8
9ALTER   PROCEDURE  [dbo].[UPDATE_PERSONAL_RANK]
10
11AS
12
13DECLARE @tUpdateDate SMALLDATETIME
14
15--SET @tUpdateDate =CONVERT(VARCHAR(15) , GETDATE(),102)
16--SET @tUpdateDate = DATEADD(HH,DATEPART(HH,dateadd(mi, 10, GETDATE())),@tUpdateDate) 
17
18
19set @tupdatedate = getdate()
20
21/* --enable etmek icin (tablo) olusturmak ıcın
22create table [KARUS_RANK]
23(
24  [nRank] [int] IDENTITY (1, 1) NOT NULL ,
25  [strUserID] char(21)NULL,
26  [LoyaltyMonthly] int NOT NULL,
27  [Loyalty] int NOT NULL,
28  [nRankUP] int NOT NULL
29) ON [PRIMARY]
30
31create table [ELMO_RANK]
32(
33  [nRank] [int] IDENTITY (1, 1) NOT NULL ,
34  [strUserID] char(21) NULL,
35  [LoyaltyMonthly] int NOT NULL,
36  [Loyalty] int NOT NULL,
37  [nRankUP] int NOT NULL
38) ON [PRIMARY]
39
40*/
41truncate table karus_rank -- yenilemek icin gerekli..
42insert  KARUS_RANK
43  select top 100 strUserID, LoyaltyMonthly, Loyalty, 101
44  from USERDATA
45  where Nation = 1
46  order by LoyaltyMonthly desc, Loyalty desc
47
48truncate table elmo_rank -- yenilemek icin gerekli
49insert  ELMO_RANK
50  select top 100 strUserID, LoyaltyMonthly, Loyalty, 101
51  from USERDATA
52  where Nation = 2
53  order by LoyaltyMonthly desc, Loyalty desc
54
55
56UPDATE KARUS_RANK
57  Set nRankUP = R.nRank - KARUS_RANK.nRank
58  from USER_PERSONAL_RANK R
59  where R.strKarusUserID = KARUS_RANK.strUserID
60
61UPDATE ELMO_RANK
62  Set nRankUP = R.nRank - ELMO_RANK.nRank
63  from USER_PERSONAL_RANK R
64  where R.strElmoUserID = ELMO_RANK.strUserID
65
66
67
68IF EXISTS (SELECT COUNT(*) FROM USER_PERSONAL_RANK WHERE nRank = 1)
69UPDATE USER_PERSONAL_RANK SET strPosition = 'Gold Knight', nElmoCheck = 1000000, nKarusCheck = 1000000, nSalary = 100000000  WHERE nRank = 1
70
71IF EXISTS (SELECT COUNT(*) FROM USER_PERSONAL_RANK WHERE nRank>= 2 AND nRank<= 4)
72UPDATE USER_PERSONAL_RANK SET strPosition = 'Silver Knight', nElmoCheck = 800000, nKarusCheck = 800000, nSalary = 70000000  WHERE nRank>= 2 AND nRank<= 4
73
74IF EXISTS (SELECT COUNT(*) FROM USER_PERSONAL_RANK WHERE nRank>= 5 AND nRank<= 9 )
75UPDATE USER_PERSONAL_RANK SET strPosition = 'Mirage Knight', nElmoCheck = 600000, nKarusCheck = 600000, nSalary = 50000000  WHERE nRank>= 5 AND nRank<= 9 
76
77IF EXISTS (SELECT COUNT(*) FROM USER_PERSONAL_RANK WHERE nRank>= 10 AND nRank<= 25)
78UPDATE USER_PERSONAL_RANK SET strPosition = 'Shadow Knight', nElmoCheck = 400000, nKarusCheck = 400000, nSalary = 30000000  WHERE nRank>= 10 AND nRank<= 25
79
80IF EXISTS (SELECT COUNT(*) FROM USER_PERSONAL_RANK WHERE nRank>= 26 AND nRank<= 50)
81UPDATE USER_PERSONAL_RANK SET strPosition = 'Mist Knight', nElmoCheck = 200000, nKarusCheck = 200000, nSalary = 20000000  WHERE nRank>= 26 AND nRank<= 50
82
83IF EXISTS (SELECT COUNT(*) FROM USER_PERSONAL_RANK WHERE nRank>= 51)
84UPDATE USER_PERSONAL_RANK SET strPosition = 'Training Knight', nElmoCheck = 50000, nKarusCheck = 50000, nSalary = 5000000  WHERE nRank>= 51
85
86
87
88BEGIN TRAN
89DELETE FROM USER_PERSONAL_RANK WHERE UpdateDate < @tUpdateDate
90COMMIT TRAN
91
92INSERT INTO USER_PERSONAL_RANK
93  select E.nRank, R.strPosition, E.nRankUP, E.strUserID, E.LoyaltyMonthly, R.nSalary, K.nRankUP, K.strUserID, K.LoyaltyMonthly, R.nSalary, R.nSalary, @tUpdateDate
94  from ELMO_RANK E, KARUS_RANK K, USER_PERSONAL_RANK R
95  WHERE (E.nRank = K.nRank AND E.nRank = R.nRank and R.UpdateDate < @tUpdateDate)
96  ORDER BY E.nRank
97
98DELETE FROM USER_PERSONAL_RANK
99WHERE UpdateDate < @tUpdateDate