CREATE PROCEDURE UPDATE_PERSONAL_RANK
AS
/*
Author : AKUMA
E-Mail : [email protected]
*/
SET NOCOUNT ON
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING_MONTHLY]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING_MONTHLY]
CREATE TABLE [dbo].[KUSER_RATING_MONTHLY] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[strKnightsName] [char] (21) NULL ,
[nLoyaltyMonthly] [int] NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[EUSER_RATING_MONTHLY] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[strKnightsName] [char] (21) NULL ,
[nLoyaltyMonthly] [int] NULL ,
) ON [PRIMARY]
INSERT INTO KUSER_RATING_MONTHLY
SELECT TOP 100 USERDATA.strUserId, KNIGHTS.IDName, USERDATA.LoyaltyMonthly FROM USERDATA INNER JOIN KNIGHTS ON USERDATA.Knights = KNIGHTS.IDNum
WHERE (USERDATA.Nation = 1) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.LoyaltyMonthly DESC
INSERT INTO EUSER_RATING_MONTHLY
SELECT TOP 100 USERDATA.strUserId, KNIGHTS.IDName, USERDATA.LoyaltyMonthly FROM USERDATA INNER JOIN KNIGHTS ON USERDATA.Knights = KNIGHTS.IDNum
WHERE (USERDATA.Nation = 2) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.LoyaltyMonthly DESC
CREATE INDEX [IX_RATING_RANK] ON [dbo].[KUSER_RATING_MONTHLY]([nRank]) ON [PRIMARY]
CREATE INDEX [IX_RATING_RANK] ON [dbo].[EUSER_RATING_MONTHLY]([nRank]) ON [PRIMARY]
DECLARE @CheckUserPersonalRankTable tinyint
SELECT @CheckUserPersonalRankTable = Count(*) FROM USER_PERSONAL_RANK
IF @CheckUserPersonalRankTable < 100
BEGIN
DECLARE @RankName varchar(30)
DECLARE @I int
SET @I = 1
WHILE @I < 101
BEGIN
IF @I = 1
BEGIN
SET @RankName = 'Gold Knight'
END
IF @I > 1 AND @I <= 4
BEGIN
SET @RankName = 'Silver Knight'
END
IF @I > 4 AND @I <= 9
BEGIN
SET @RankName = 'Mirage Knight'
END
IF @I > 9 AND @I <= 10
BEGIN
SET @RankName = 'Shadow Knight'
END
IF @I > 25 AND @I <= 50
BEGIN
SET @RankName = 'Mist Knight'
END
IF @I > 50 AND @I <= 100
BEGIN
SET @RankName = 'Training Knight'
END
INSERT INTO USER_PERSONAL_RANK VALUES (@I,@RankName,0,NULL,0,1000000,0,NULL,NULL,1000000,1000000,GETDATE())
SET @I = @I + 1
END
END
DECLARE @strCharID char(21)
DECLARE @strKnightsID char(21)
DECLARE @nIndex smallint
DECLARE @nLoyaltyMonthly int
SET @strCharID = NULL
SET @strKnightsID = NULL
SET @nIndex = 0
SET @nLoyaltyMonthly = 0
DECLARE CRS_KARUS CURSOR FOR
SELECT nRank, strUserID, strKnightsName, nLoyaltyMonthly FROM KUSER_RATING_MONTHLY
OPEN CRS_KARUS
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_PERSONAL_RANK SET strKarusUserID = @strCharID, nKarusLoyaltyMonthly = @nLoyaltyMonthly WHERE nRank = @nIndex
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly
END
CLOSE CRS_KARUS
DEALLOCATE CRS_KARUS
DECLARE CRS_HUMAN CURSOR FOR
SELECT nRank, strUserID, strKnightsName, nLoyaltyMonthly FROM EUSER_RATING_MONTHLY
OPEN CRS_HUMAN
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_PERSONAL_RANK SET strElmoUserID = @strCharID, nElmoLoyaltyMonthly = @nLoyaltyMonthly WHERE nRank = @nIndex
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @strKnightsID, @nLoyaltyMonthly
END
CLOSE CRS_HUMAN
DEALLOCATE CRS_HUMAN
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING_MONTHLY]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING_MONTHLY]
