[Guide] Update_WebGuildRank

[Twilight]

---ONLY FOR DEVELOPERS-----------------

use PS_UserData;

CREATE TABLE [dbo].[GuildWeekRank] (
    [Index] [int] NULL ,
    [ServerID] [tinyint] NULL ,
    [GuildID] [int] NULL ,
    [Country] [int] NULL ,
    [GuildName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [MasterCharID] [int] NULL ,
    [MasterName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Rank] [int] NULL ,
    [GuildPoint] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Change] [int] NULL ,
    [UpdateDate] [datetime] NULL ,
    [TotalRank] [int] NULL ,
    [TotalChange] [int] NULL 
) ON [PRIMARY]

GO
CREATE PROCEDURE uxp_Backup_GuildWeekRankLog @ServerID     tinyint 
AS

GO

use PS_GameData;
drop procedure [dbo].[uxp_Update_WebGuildRank]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE    Proc uxp_Update_WebGuildRank
@CheckDate int = 1
AS

DECLARE
@ServerID     tinyint,
@RankTime    datetime,
@OldRankTime    datetime,
@WebRankTime    datetime,
@Index         int,

@GuildID        int,
@Rank        int,
@TotalRank    int,
@OldRank    int,
@OldTotalRank    int,
@Change    int,
@TotalChange    int

SET NOCOUNT ON

----------------------------------------------------------------
--RETURN 1
----------------------------------------------------------------

----------------------------------------------------------------
--Init
SET @ServerID = 1
SET @RankTime = GETDATE()


SELECT Top 1 @Index=[Index], @WebRankTime=UpdateDate FROM PS_UserData.dbo.GuildWeekRank 
WHERE ServerID=@ServerID ORDER BY UpdateDate DESC
SET @Index = ISNULL(@Index,0) + 1

--Check Web Update
IF @CheckDate = 1
BEGIN
    IF ( @WebRankTime IS NOT NULL )
    BEGIN
        IF ( DATEDIFF( day, @WebRankTime, @RankTime ) = 0 )
        BEGIN
            RETURN -1
        END
    END
END



----------------------------------------------------------------
--Check Update
IF @CheckDate = 1
BEGIN
    SET @OldRankTime = ( SELECT TOP 1 RankTime FROM GuildRankLog ORDER BY RankTime DESC )
    IF ( @OldRankTime IS NOT NULL )
    BEGIN
        IF ( DATEDIFF( day, @OldRankTime, @RankTime ) <> 0 )
        BEGIN
            RETURN -2
        END
    END
    ELSE
    BEGIN
        RETURN -3
    END
END

----------------------------------------------------------------
--Check Rank
DELETE FROM GuildRankLog2
INSERT INTO GuildRankLog2 SELECT * FROM GuildRankLog
DELETE FROM GuildRankLog


CREATE TABLE [#GuildRankLog] (
    [TotalRank] int IDENTITY (1, 1) NOT NULL ,
    [GuildID] [int] NOT NULL ,
    [GuildValue] [int] NOT NULL ,
    [Rank] [int] NOT NULL ) 

INSERT INTO #GuildRankLog( GuildID, GuildValue, Rank)
SELECT G.GuildID, G.GuildPoint, D.Rank  FROM Guilds G
INNER JOIN GuildDetails D ON G.GuildID = D.GuildID AND D.Rank < 31
WHERE G.Del = 0
ORDER BY G.GuildPoint DESC


INSERT INTO [GuildRankLog]([GuildID], [GuildValue], [Rank], [TotalRank])
SELECT GuildID, GuildValue, Rank, TotalRank FROM #GuildRankLog


DROP TABLE #GuildRankLog



EXEC PS_UserData.dbo.uxp_Backup_GuildWeekRankLog @ServerID
----------------------------------------------------------------
--Update Change
DECLARE Cur_Guild CURSOR 
FOR

SELECT GuildID, Rank, TotalRank FROM GuildRankLog WHERE Rank < 31

OPEN Cur_Guild

FETCH NEXT FROM Cur_Guild INTO @GuildID, @Rank, @TotalRank

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @OldRank=Rank, @OldTotalRank=TotalRank FROM GuildRankLog2 WHERE GuildID = @GuildID
    
    IF ( @@ROWCOUNT = 0 )
    BEGIN
        SET @Change =NULL
        SET @TotalChange = NULL
        UPDATE GuildRankLog SET Change=@Change, TotalChange=@TotalChange WHERE GuildID = @GuildID
    END
    
    ELSE
    BEGIN
        SET @Change = @OldRank-@Rank
        SET @TotalChange = @OldTotalRank-@TotalRank 
        UPDATE GuildRankLog SET Change=@Change, TotalChange=@TotalChange WHERE GuildID = @GuildID    
    END
    
    INSERT INTO [PS_UserData].[dbo].[GuildWeekRank] 
    ([Index], [ServerID], [Country], [GuildID], [GuildName], [MasterCharID], [MasterName], [Rank], [GuildPoint], [Change], [UpdateDate], [TotalRank], [TotalChange])
    SELECT @Index, @ServerID, G.Country, L.GuildID, G.GuildName, G.MasterCharID, G.MasterName, L.Rank, L.GuildValue, L.Change, L.RankTime, L.TotalRank, L.TotalChange FROM GuildRankLog L
    INNER JOIN Guilds G ON G.GuildID = L.GuildID
    WHERE L.GuildID = @GuildID


FETCH NEXT FROM Cur_Guild INTO @GuildID, @Rank, @TotalRank
END

CLOSE Cur_Guild
DEALLOCATE Cur_Guild
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Use:

use PS_GameData;
exec uxp_Update_WebGuildRank;

-- best query for web? -------

SELECT     TOP 31 Rank, GuildName, MasterName, GuildPoint, Change, TotalRank, TotalChange, Country FROM  GuildWeekRank ORDER BY Rank

or

SELECT     TOP 31 Rank, GuildName, MasterName, GuildPoint, Change, TotalRank, TotalChange, Country FROM  GuildWeekRank ORDER BY TotalRank

?

 

[KILLSTEAL]

Re: [Dev] Update_WebGuildRank

Ok this day i have test it but can´t run ?


the tables are all clean ?

 

[akademik]

Re: [Dev] Update_WebGuildRank

под какую версию сервера?


----
Translates to :
under which the server version?