[Problem] Honor Rank

[Overlimit Developer]

check the proc that handly this system in shard database

[witchymoo]

Originally Posted by B13.

IS there is a way to change this

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TrainingCampRanking]    Script Date: 8/7/2015 5:35:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[_TrainingCampRanking]
AS


SELECT Ranking, Tcm.CharName, [Rank]
FROM _TrainingCampHonorRank Tchr 
JOIN _TrainingCampMember Tcm
ON Tchr.CampID = Tcm.CampID
WHERE Tcm.MemberClass = 0
order by HonorPoint desc 


and make it order them by graduates
*Note the guy with 162 graduates has more honor point,
just asking ..
thx for your help

There you go:

SELECT Tchr.Ranking, Tcm.CharName, Tchr.[Rank] FROM [_TrainingCampHonorRank] AS Tchr
	JOIN [_TrainingCampMember] AS Tcm ON Tchr.CampID = Tcm.CampID
	JOIN [_TrainingCamp] AS Tcamp ON Tchr.CampID = Tcamp.ID
	WHERE Tcm.MemberClass = 0
	ORDER BY Tcamp.GraduateCount DESC

[Aaron*]

Originally Posted by witchymoo

There you go:

SELECT Tchr.Ranking, Tcm.CharName, Tchr.[Rank] FROM [_TrainingCampHonorRank] AS Tchr
    JOIN [_TrainingCampMember] AS Tcm ON Tchr.CampID = Tcm.CampID
    JOIN [_TrainingCamp] AS Tcamp ON Tchr.CampID = Tcamp.ID
    WHERE Tcm.MemberClass = 0
    ORDER BY Tcamp.GraduateCount DESC

This is not what he needs He wants it to be visually shown in the client.
His problem is _TRAINING_CAMP_UPDATE_HONORRANK procedure is f*cked up, he is probably using a released buggy db.

[B13.]

_TRAINING_CAMP_ADDMEMBER

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_ADDMEMBER]    Script Date: 8/8/2015 8:47:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[_TRAINING_CAMP_ADDMEMBER]
    @CampID    INT,
    @MemberID    INT
AS

    SET NOCOUNT ON

    IF NOT EXISTS (SELECT [ID] FROM _TrainingCamp WITH (NOLOCK) WHERE [ID] = @CampID)
    BEGIN
        RETURN -1000
    END

    IF EXISTS (SELECT CharID FROM _TrainingCampMember WHERE CharID = @MemberID)
    BEGIN
        RETURN -1001
    END

    DECLARE @Ret     INT

BEGIN TRANSACTION

    DECLARE @HonorPoint INT
    SET @HonorPoint = 0
    SELECT @HonorPoint = HonorPoint FROM _TrainingCampSubMentorHonorPoint WHERE CharID = @MemberID    
    
    IF @HonorPoint <> 0
    BEGIN
        DELETE FROM _TrainingCampSubMentorHonorPoint WHERE  CharID = @MemberID    
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            RETURN -6
        END
    END

    EXEC @Ret =  _TrainingCamp_FnAddMember @CampID, @MemberID, @HonorPoint
    IF (@@ERROR <> 0 OR @Ret <= 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @Ret
    END

COMMIT TRANSACTION

    SET NOCOUNT OFF

    RETURN 1 


_TRAINING_CAMP_CREATE

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_CREATE]    Script Date: 8/8/2015 8:47:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[_TRAINING_CAMP_CREATE]
    @CreaterID    INT
AS
    SET NOCOUNT ON

    IF EXISTS (SELECT CharID FROM _TrainingCampMember WHERE CharID = @CreaterID)
    BEGIN
        RETURN -1001
    END

    DECLARE @CampID        INT
    DECLARE @FoundationDate    SMALLDATETIME
    SET @FoundationDate = GETDATE()

BEGIN TRANSACTION

    DECLARE @HonorPoint INT
    SET @HonorPoint = 0
    SELECT @HonorPoint = HonorPoint FROM _TrainingCampSubMentorHonorPoint WHERE CharID = @CreaterID    
    
    IF @HonorPoint <> 0
    BEGIN
        DELETE FROM _TrainingCampSubMentorHonorPoint WHERE  CharID = @CreaterID    
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            RETURN -6
        END
    END

    INSERT INTO _TrainingCamp (CreationDate, Rank, GraduateCount, EvaluationPoint,  LatestEvaluationDate, CommentTitle, Comment )
    VALUES ( @FoundationDate, 5, 0, 0,  @FoundationDate, '', '')

    SET @CampID = @@IDENTITY

    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0 OR @CampID = 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -7
    END

    DECLARE @Ret INT
    EXEC @Ret = _TrainingCamp_FnAddMember  @CampID, @CreaterID, @HonorPoint 
    IF (@@ERROR <> 0 OR @Ret <= 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @Ret
    END

COMMIT TRANSACTION

    SET NOCOUNT OFF

    RETURN @CampID 


_TRAINING_CAMP_DELCAMP

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_DELCAMP]    Script Date: 8/8/2015 8:48:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[_TRAINING_CAMP_DELCAMP]
    @CampID    INT,
    @CreaterID    INT
AS
    SET NOCOUNT ON

    IF NOT EXISTS (SELECT [ID] FROM _TrainingCamp WITH (NOLOCK) WHERE [ID] = @CampID)
    BEGIN
        RETURN -1000
    END

    DECLARE @MemberClass TINYINT
    SELECT @MemberClass = MemberClass FROM _TrainingCampMember WITH (NOLOCK) WHERE CampID = @CampID AND CharID = @CreaterID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0 OR @MemberClass <> 0)
    BEGIN
        RETURN -2000
    END

BEGIN TRANSACTION

    -- º¸Á¶ ÈÄ°ßÀÎÀÇ HonorPoint¸¦ ÀúÀåÇصдÙ.
    INSERT _TrainingCampSubMentorHonorPoint SELECT CharID, HonorPoint FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 1 AND HonorPoint > 0    
    IF (@@ERROR <> 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -2
    END


    DELETE FROM _TrainingCampMember WHERE CampID = @CampID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -1
    END

    DELETE FROM _TrainingCamp WHERE [ID] = @CampID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -2
    END

    -- °æÇèÄ¡ ¹öÇÁ ·¹Äڵ堻èÁ¦
    DELETE FROM _TrainingCampBuffStatus WHERE CampID = @CampID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -2005
    END

    COMMIT TRANSACTION

    SET NOCOUNT OFF

    RETURN 1 


_TRAINING_CAMP_DELMEMBER

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_DELMEMBER]    Script Date: 8/8/2015 8:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[_TRAINING_CAMP_DELMEMBER]
    @CampID    INT,
    @MemberID    INT,
    @IsBan        TINYINT
AS
    SET NOCOUNT ON

    IF NOT EXISTS (SELECT [ID] FROM _TrainingCamp WITH (NOLOCK) WHERE [ID] = @CampID)
    BEGIN
        RETURN -2000
    END

    DECLARE @MemberClass TINYINT
    SELECT @MemberClass = MemberClass FROM _TrainingCampMember WHERE CampID = @CampID AND CharID = @MemberID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0 OR @MemberClass = 0)
    BEGIN
        RETURN -2001
    END

BEGIN TRANSACTION

    -- º¸Á¶ ÈÄ°ßÀÎÀÌ Å»Åð, È¤Àº °*ÅðµÇ´Â °æ¿ì¿£ º¸Á¶ ÈÄ°ßÀÎÀÇ HonorPoint¸¦ ÀúÀåÇؾߠÇÑ´Ù.
    IF @MemberClass = 1 
    BEGIN
        DECLARE @HonorPoint INT
        SELECT @HonorPoint = HonorPoint FROM _TrainingCampMember WHERE CampID = @CampID AND CharID = @MemberID
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            RETURN -2002
        END

        -- °*ÅðµÈ °ÍÀÌ ¾Æ´Ï°í, ¸í¿¹Æ÷ÀÎÆ®°¡ 10Á¡ ÀÌ»óÀ̶ó¸é 10Á¡À» ±îÀÚ.
        IF (@IsBan <> 1)
        BEGIN
            IF (@HonorPoint >= 10)
            BEGIN
                SET @HonorPoint = @HonorPoint - 10    
            END
            ELSE
            BEGIN
                SET @HonorPoint = 0
            END        
        END

        -- Å×ÀÌºí¿¡ ¸í¿¹ Æ÷ÀÎÆ®¸¦ ÀúÀå.
        IF @HonorPoint > 0
        BEGIN
            INSERT INTO _TrainingCampSubMentorHonorPoint VALUES (@MemberID, @HonorPoint)        
            IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
            BEGIN
                ROLLBACK TRANSACTION
                RETURN -2
            END
        END
    END
    ELSE
    BEGIN
        -- °æÇèÄ¡ ¹öÇÁ ·¹Äڵ堻èÁ¦
        DELETE FROM _TrainingCampBuffStatus WHERE CampID = @CampID AND RecipientCharID = @MemberID
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            RETURN -2005
        END
    END

    DELETE FROM _TrainingCampMember WHERE CampID = @CampID AND CharID = @MemberID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -1
    END

    COMMIT TRANSACTION

    SET NOCOUNT OFF

    RETURN 1 


_TRAINING_CAMP_MEMBER_GRADUATE

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_MEMBER_GRADUATE]    Script Date: 8/8/2015 8:49:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[_TRAINING_CAMP_MEMBER_GRADUATE]
    @CampID    INT,
    @MemberID    INT,
    @Evaluation    INT
AS
    SET NOCOUNT ON

    IF NOT EXISTS (SELECT [ID] FROM _TrainingCamp WITH (NOLOCK) WHERE [ID] = @CampID)
    BEGIN
        RETURN -2000
    END

    DECLARE @MemberClass TINYINT
    SELECT @MemberClass = MemberClass FROM _TrainingCampMember WHERE CampID = @CampID AND CharID = @MemberID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0 OR @MemberClass = 0)
    BEGIN
        RETURN -2001
    END

BEGIN TRANSACTION

    -- º¸Á¶ ÈÄ°ßÀÎÀÇ Á¹¾÷
    IF @MemberClass = 1
    BEGIN
        -- À̳ÑÀº Æò°¡ Á¡¼ö°¡ ¾ø´Ù. ´Ù¸¸, ÀÌ ³ÑÀÇ HonorPoint¸¦ ÀúÀ常 ÇØÁָ頵ȴÙ.
        DECLARE @HonorPoint INT
        SELECT @HonorPoint = HonorPoint FROM _TrainingCampMember WHERE CampID = @CampID AND CharID = @MemberID
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            RETURN -2002
        END

        -- Å×ÀÌºí¿¡ ¸í¿¹ Æ÷ÀÎÆ®¸¦ ÀúÀå.
        IF @HonorPoint > 0
        BEGIN
            INSERT INTO _TrainingCampSubMentorHonorPoint VALUES (@MemberID, @HonorPoint)        
            IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
            BEGIN
                ROLLBACK TRANSACTION
                RETURN -2
            END
        END
    END
    ELSE
    -- ¼ö·Ã»ýÀÇ Á¹¾÷
    BEGIN

        -- ¸â¹öµé¿¡°Ô ¸í¿¹ Æ÷ÀÎÆ®¸¦ ¹Ý¿µÇغ¸ÀÚ.
        DECLARE @JoinLvl         INT

        SELECT @JoinLvl = CharJoinedLevel FROM _TrainingCampMember WHERE CampID = @CampID AND CharID = @MemberID
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            RETURN -2003
        END

        -- °¡ÀÔ·¹º§ÀÌ 30 ÀÌÇÏ¿©¾ß ¿©·¯°¡Áö ºÎ°¡ ÇýÅÃÀÌ Á¸ÀçÇÑ´Ù.
        IF (@JoinLvl <= 30)
        BEGIN    
            DECLARE @MentorPoint        INT
            DECLARE @SubMentorPoint    INT

            SET @MentorPoint = (40 - @JoinLvl)
            SET @SubMentorPoint = (@MentorPoint / 10)
    
            UPDATE _TrainingCampMember SET HonorPoint = (HonorPoint + @MentorPoint) WHERE CampID = @CampID AND MemberClass = 0
            IF (@@ERROR <> 0)
            BEGIN
                ROLLBACK TRANSACTION
                RETURN -4
            END
            
            UPDATE _TrainingCampMember SET HonorPoint = (HonorPoint + @SubMentorPoint) WHERE CampID = @CampID AND MemberClass = 1
            IF (@@ERROR <> 0)
            BEGIN
                ROLLBACK TRANSACTION
                RETURN -5
            END
    
            -- Æò°¡Á¡¼ö¸¦ ¹Ý¿µÇغ¸ÀÚ.
            UPDATE _TrainingCamp SET     GraduateCount = (GraduateCount + 1), 
                            EvaluationPoint = (EvaluationPoint + @Evaluation), 
                            LatestEvaluationDate = GETDATE()
            WHERE [ID] = @CampID
            IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
            BEGIN
                ROLLBACK TRANSACTION
                RETURN -3
            END
        END
        ELSE
        BEGIN
            UPDATE _TrainingCamp SET GraduateCount = (GraduateCount + 1) WHERE [ID] = @CampID
            IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
            BEGIN
                ROLLBACK TRANSACTION
                RETURN -3
            END    
        END


        DELETE FROM _TrainingCampBuffStatus WHERE CampID = @CampID AND RecipientCharID = @MemberID
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            RETURN -2005
        END
    END

    -- ÇöÀç Ä·ÇÁ ¸â¹ö¿¡¼* »èÁ¦
    DELETE FROM _TrainingCampMember WHERE CampID = @CampID AND CharID = @MemberID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION
        RETURN -1
    END

    COMMIT TRANSACTION

    SET NOCOUNT OFF

    RETURN 1 


_TRAINING_CAMP_REFRESHMEMBERSTATUS

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_REFRESHMEMBERSTATUS]    Script Date: 8/8/2015 8:50:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


----------------------------------------------------------------------
-- 2. ´©Àû¹ö±× 2Â÷ - ·¹º§´Ù¿î ¹®Á¦ ÇØ°á 
----------------------------------------------------------------------


ALTER    PROCEDURE [dbo].[_TRAINING_CAMP_REFRESHMEMBERSTATUS]
    @CampID    INT,
    @MemberID    INT,
    @RefreshTarget    TINYINT,
    @Data        INT
AS
    SET NOCOUNT ON
/*
    #define TRAINING_CAMP_MEMBER_REFRESH_TARGET_PLAYSTATE    (BYTE)0x01
    #define TRAINING_CAMP_MEMBER_REFRESH_TARGET_LEVEL    (BYTE)0x02
    #define TRAINING_CAMP_MEMBER_REFRESH_TARGET_HONORPOINT    (BYTE)0x03
    #define TRAINING_CAMP_MEMBER_REFRESH_TARGET_POSITION    (BYTE)0x04
    #define TRAINING_CAMP_MEMBER_REFRESH_TARGET_CHARNAME    (BYTE)0x05
    #define TRAINING_CAMP_MEMBER_REFRESH_TARGET_CUR_LEVEL    (BYTE)0x06
*/
    IF @RefreshTarget = 2    -- MaxLevel
    BEGIN
        UPDATE _TrainingCampMember SET CharMaxLevel = @Data WHERE CampID = @CampID AND CharID = @MemberID
    END

    IF @RefreshTarget = 6    -- CurLevel
    BEGIN
        UPDATE _TrainingCampMember SET CharCurLevel = @Data WHERE CampID = @CampID AND CharID = @MemberID
    END

    SET NOCOUNT OFF 


_TRAINING_CAMP_STARTUP_HONOR_UPDATE_NEEDED

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_STARTUP_HONOR_UPDATE_NEEDED]    Script Date: 8/8/2015 8:50:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[_TRAINING_CAMP_STARTUP_HONOR_UPDATE_NEEDED]
AS
    IF NOT EXISTS (SELECT LastUpdateDate FROM _TrainingCampHonorRankUpdateDate )
    BEGIN
        RETURN 1
    END

    DECLARE @UpdateDate DATETIME

    SELECT @UpdateDate = LastUpdateDate FROM _TrainingCampHonorRankUpdateDate
    
    IF DATEDIFF(DAY, @UpdateDate, GETDATE()) = 0
    BEGIN
        RETURN 0
    END
    ELSE
    BEGIN
        RETURN 1
    END 


_TRAINING_CAMP_UPDATEHONORRANK

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]    Script Date: 8/8/2015 8:51:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
AS
SET NOCOUNT ON


-- ÀÏ´Ü ·©Å· Å×À̺íÀ» ¼¼ÆÃÇÑ´Ù.
DECLARE @Ranking INT
SET @Ranking = 1


DECLARE @CampID INT
DECLARE @Rank TINYINT


BEGIN TRANSACTION


DECLARE RankCursor CURSOR FAST_FORWARD FOR
SELECT TOP 50 [ID] FROM _TrainingCamp WITH (NOLOCK)
WHERE EvaluationPoint >= 10
ORDER BY EvaluationPoint DESC, LatestEvaluationDate ASC


-- ÀÏ´Ü Àüü ·©Å·À» ¾øÀ½À¸·Î Ç¥½ÃÇÏÀÚ.
UPDATE _TrainingCamp SET Rank = 5 WHERE Rank <> 5
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION


DEALLOCATE RankCursor
RETURN -1
END


-- ·©Å· Å×ÀÌºíµµ ÃʱâÈ*ÇÏÀÚ.
UPDATE _TrainingCampHonorRank SET CampID = null, Rank = null
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION


DEALLOCATE RankCursor
RETURN -1
END


OPEN RankCursor
FETCH NEXT FROM RankCursor INTO @CampID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Ranking <= 5
BEGIN
SET @Rank = 1
END
ELSE IF @Ranking <= 15
BEGIN
SET @Rank = 2
END
ELSE IF @Ranking <= 30
BEGIN
SET @Rank = 3
END
ELSE
BEGIN
SET @Rank = 4
END


UPDATE _TrainingCamp SET Rank = @Rank WHERE [ID] = @CampID 
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
CLOSE RankCursor
DEALLOCATE RankCursor


RETURN -2
END
UPDATE _TrainingCampHonorRank SET CampID = @CampID, Rank = @Rank WHERE Ranking = @Ranking 
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
CLOSE RankCursor
DEALLOCATE RankCursor


RETURN -3
END 

SET @Ranking = @Ranking + 1


FETCH NEXT FROM RankCursor INTO @CampID
END


CLOSE RankCursor
DEALLOCATE RankCursor


-- ¸¶Áö¸·À¸·Î °»½ÅÇÑ ³¯Â¥¸¦ Àû¾îº¸ÀÚ!!!
IF EXISTS (SELECT LastUpdateDate FROM _TrainingCampHonorRankUpdateDate)
BEGIN
UPDATE _TrainingCampHonorRankUpdateDate SET LastUpdateDate = GETDATE()
END
ELSE
BEGIN
INSERT INTO _TrainingCampHonorRankUpdateDate VALUES (GETDATE())
END


IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -4
END


COMMIT TRANSACTION


SET NOCOUNT OFF


RETURN 1 


_TrainingCamp_FnAddMember

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TrainingCamp_FnAddMember]    Script Date: 8/8/2015 8:51:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[_TrainingCamp_FnAddMember]
    @CampID    INT,
    @MemberID    INT,
    @HonorPoint    INT
AS
    SET NOCOUNT ON

    IF EXISTS (SELECT CharID FROM _TrainingCampMember WHERE CharID = @MemberID)
    BEGIN
        RETURN -1001
    END

    DECLARE @MemberName    VARCHAR(64)
    DECLARE @MemberMaxLvl        TINYINT
    DECLARE @MemberCurLvl        TINYINT
    
    SELECT @MemberName = CharName16, @MemberMaxLvl = MaxLevel, @MemberCurLvl = CurLevel FROM _Char WITH (NOLOCK) WHERE CharID = @MemberID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        RETURN -1
    END

    IF EXISTS (SELECT CharID FROM _TrainingCampMember WITH (NOLOCK) WHERE CampID = @CampID AND CharID = @MemberID)
    BEGIN
        RETURN -2
    END

    DECLARE @RefObjID INT
    SELECT @RefObjID = RefObjID FROM _Char WITH (NOLOCK) WHERE CharID = @MemberID
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        RETURN -3
    END

/*    commented by novice. ¾Æ·¡·Î ¿Å±ä´Ù!! 2007. 07. 16.
    ----------------------------------------------------------------------------------------------------------
    -- by novice
    DECLARE @ApprenticeCount    INT
    DECLARE @SubMentorCount    INT

    -- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
    SELECT @SubMentorCount = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 1
    IF ( @SubMentorCount >= 2 )
    BEGIN
        RETURN -11
    END

    -- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
    SELECT @ApprenticeCount = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 2
    IF ( @ApprenticeCount >= 5 )
    BEGIN
        RETURN -12
    END
    ----------------------------------------------------------------------------------------------------------
*/

    DECLARE @MemberClass TINYINT
    SET @MemberClass = 0

    IF @MemberMaxLvl < 40
    BEGIN
        SET @MemberClass = 2
    END
    ELSE IF @MemberMaxLvl < 60
    BEGIN
        SET @MemberClass = 1
    END

    ----------------------------------------------------------------------------------------------------------
    -- by novice 2007. 07. 16.
    -- °¡ÀÔ½ÃÅ°´Â »ç¶÷ÀÇ ·¹º§¿¡ µû¶ó¼* Å¬·¡½º¸¦ °áÁ¤Çϴµ¥..
    -- ±×³É ¹«ÀÛÁ¤ °¢ Å¬·¡½º¿¡ ´ëÇØ ÀοøÀ» °Ë»çÇϸ頴ç±Ù ¹®Á¦°¡ ÀÖ´Ù
    -- °¡ÀÔÇϴ »ç¶÷ÀǠŬ·¡½º¿¡ ¸Â´Â Àοø °Ë»ç¸¦ ½ÃµµÇؾߠÇÑ´Ù!!
    -- ±Ùµ¥.. ³»°¡ Àü¿¡´Â ¿Ö Àú·¸°Ô Çß¾úÀ»±î.......... ¤Ð_¤Ð
    ----------------------------------------------------------------------------------------------------------
    IF @MemberClass = 0
    BEGIN
        DECLARE @MentorCount    INT
    
        -- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
        SELECT @MentorCount = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 0
        IF ( @MentorCount >= 1 )
        BEGIN
            RETURN -14            -- ADD DBÀÛ¾÷ ½ÇÆжó´Â ·Î±×·Î ¾´´Ù..
        END
    END
    ELSE IF @MemberClass = 1
    BEGIN
        DECLARE @SubMentorCount    INT
    
        -- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
        SELECT @SubMentorCount = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 1
        IF ( @SubMentorCount >= 2 )
        BEGIN
            RETURN -11
        END
    END
    ELSE IF @MemberClass = 2
    BEGIN
        DECLARE @ApprenticeCount    INT
    
        -- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
        SELECT @ApprenticeCount = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 2
        IF ( @ApprenticeCount >= 5 )
        BEGIN
            RETURN -12
        END
    END
    ----------------------------------------------------------------------------------------------------------


    INSERT _TrainingCampMember VALUES
    (@CampID, @MemberID, @RefObjID, @MemberName, GETDATE(), @MemberClass, @MemberMaxLvl, @MemberCurLvl, @MemberMaxLvl, @HonorPoint)
    IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
    BEGIN
        RETURN -5
    END

    IF ( 0 < (SELECT COUNT(*) FROM _TrainingCampBuffStatus
            WHERE CampID = @CampID and RecipientCharID = @MemberID) )
    BEGIN
        RETURN -2006
    END

    IF @MemberClass = 0 OR @MemberClass = 2
    BEGIN
        -- °æÇèÄ¡ ´©Àû ¹öÇÁ¿¡ ´ëÇÑ ³»¿ë
        INSERT _TrainingCampBuffStatus 
            VALUES (@CampID, @MemberID, 0, 0, default, 0, 0)
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            RETURN -2005
        END
    END

    -- °æÇèÄ¡ Àû¸³ ¹öÇÁ¿¡ ´ëÇÑ ³»¿ë
    IF @MemberClass = 0
    BEGIN
        INSERT _TrainingCampBuffStatus
            SELECT @CampID, @MemberID, cnt + 1, 0, '2000-01-01', 0, 3
            FROM _RefDummySlot with( nolock )  
            WHERE cnt < 5
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            RETURN -2005
        END
    END
    ELSE IF @MemberClass = 2
    BEGIN
        INSERT _TrainingCampBuffStatus
            SELECT @CampID, @MemberID, 1, 0, '2000-01-01', 0, 3
        IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
        BEGIN
            RETURN -2005
        END
    END

    SET NOCOUNT OFF

    RETURN 1 


_TrainingCampRanking

 Spoiler

PHP Code:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_TrainingCampRanking]    Script Date: 8/8/2015 8:51:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[_TrainingCampRanking]
AS
SELECT Tchr.Ranking, Tcm.CharName, Tchr.[Rank] FROM [_TrainingCampHonorRank] AS Tchr
    JOIN [_TrainingCampMember] AS Tcm ON Tchr.CampID = Tcm.CampID
    JOIN [_TrainingCamp] AS Tcamp ON Tchr.CampID = Tcamp.ID
    WHERE Tcm.MemberClass = 0
    ORDER BY Tcamp.GraduateCount DESC 


Game Result

thx for help.

[B13.]

this guy -.-

..
what is wrong with this guy !!

[Beba Army]

FIXED?

[powerclass]

Originally Posted by Beba Army

FIXED?

7 years ago
I think he put everything aside and got married and had grandchildren.

[Devsome]

#closed - the best for the married man