[McDiesel]

بسم الله الرحمن الرحيم

╙~µ┌╥ Honor Rank By Job Level

ندخل ف الموضوع علطول اكيد الموضوع مفهوم من العنوان وهيفيد السيرفرات ال Based On Job وهيبقا كويس لما تفتح Job Lv ل 120 مثلا مش هطول عليكوو الموضوع كلة عبارة عن 2 Query هتعملهم ومبروك عليك

_TRAINING_CAMP_UPDATEHONORRANK

USE SRO_VT_SHARD
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
AS

Declare @CampID int
Declare @CharID int
declare @index int
declare @Rank int
declare @ID int
SET @index =0
SET @ID =0
update _TrainingCampHonorRank set Rank =null,CampID =null
/* --------------------Trader------------------*/
DECLARE RankCursor CURSOR FAST_FORWARD FOR
	SELECT TOP 30 CharID FROM _CharTrijob WITH (NOLOCK)
	WHERE JobType =1 and Level >=5
	ORDER BY Exp DESC, Level desc
	OPEN RankCursor
	FETCH NEXT FROM RankCursor INTO @CharID
	WHILE @@FETCH_STATUS = 0
	BEGIN
	select @CampID = CampID from _TrainingCampMember where CharID =@CharID
				IF (@@ROWCOUNT > 0)
			 BEGIN
     set @ID = @ID +1
	set @index = @index +1
	IF (@index between 1 and 5)
		BEGIN
			SET @Rank = 1
		END
		ELSE IF(@index between 6 and 10)
		BEGIN
			SET @Rank = 2
		END
		ELSE IF(@index between 11 and 20)
		BEGIN
			SET @Rank = 3
		END
		ELSE IF(@index between 21 and 30)
		BEGIN
			SET @Rank = 4
		END
		 update _TrainingCamp set Rank= @Rank where ID =@CampID
		 update _TrainingCampHonorRank set Rank =@Rank,CampID =@CampID where Ranking = @ID
		
		 END
	 FETCH NEXT FROM RankCursor INTO @CharID
    END
	CLOSE RankCursor
	DEALLOCATE RankCursor


SET @index =0
set @CharID =0
set @CampID =0
/* --------------------Thief------------------*/
DECLARE RankCursor CURSOR FAST_FORWARD FOR
	SELECT TOP 30 CharID FROM _CharTrijob WITH (NOLOCK)
	WHERE JobType =2 and Level >=5
	ORDER BY Exp DESC, Level desc
	OPEN RankCursor
	FETCH NEXT FROM RankCursor INTO @CharID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		select @CampID = CampID from _TrainingCampMember where CharID =@CharID
		IF (@@ROWCOUNT > 0)
			 BEGIN
			 
	set @ID = @ID +1
	set @index = @index +1
	IF (@index between 1 and 5)
		BEGIN
			SET @Rank = 1
		END
		ELSE IF(@index between 6 and 10)
		BEGIN
			SET @Rank = 2
		END
		ELSE IF(@index between 11 and 20)
		BEGIN
			SET @Rank = 3
		END
		ELSE IF(@index between 21 and 30)
		BEGIN
			SET @Rank = 4
		END
          update _TrainingCamp set Rank= @Rank where ID =@CampID
	   update _TrainingCampHonorRank set Rank =@Rank,CampID =@CampID where Ranking = @ID
	   
	    END
	 FETCH NEXT FROM RankCursor INTO @CharID
    END
	CLOSE RankCursor
	DEALLOCATE RankCursor

SET @index =0
set @CharID =0
set @CampID =0
/* --------------------Hunter------------------*/
DECLARE RankCursor CURSOR FAST_FORWARD FOR
	SELECT TOP 30 CharID FROM _CharTrijob WITH (NOLOCK)
	WHERE JobType =3 and Level >=5
	ORDER BY Exp DESC, Level desc
	OPEN RankCursor
	FETCH NEXT FROM RankCursor INTO @CharID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		select @CampID = CampID from _TrainingCampMember where CharID =@CharID
		IF (@@ROWCOUNT > 0)
			 BEGIN
			 
	set @ID = @ID +1
	set @index = @index +1
	IF (@index between 1 and 5)
		BEGIN
			SET @Rank = 1
		END
		ELSE IF(@index between 6 and 10)
		BEGIN
			SET @Rank = 2
		END
		ELSE IF(@index between 11 and 20)
		BEGIN
			SET @Rank = 3
		END
		ELSE IF(@index between 21 and 30)
		BEGIN
			SET @Rank = 4
		END
		   update _TrainingCamp set Rank= @Rank where ID =@CampID
	   update _TrainingCampHonorRank set Rank =@Rank,CampID =@CampID where Ranking = @ID
	 
	    END
	 FETCH NEXT FROM RankCursor INTO @CharID
    END
	CLOSE RankCursor
	DEALLOCATE RankCursor
	

	RETURN 1

_TrainingCamp_FnAddMember

USE SRO_VT_SHARD
GO
/****** Object:  StoredProcedure [dbo].[_TrainingCamp_FnAddMember]    Script Date: 21.02.2016 01:07:51 ******/
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
	DECLARE @Jobtype TINYINT
	Declare @String NVARCHAR(50)
  
	select @Jobtype = JobType from _CharTrijob where CharID=@MemberID
    if(@Jobtype =1)
    begin
    set @String ='*(Trader)'
    end
    else if (@Jobtype =2)
    begin
       set @String ='*(Thief)'
    end
    else if(@Jobtype =3)
    begin
       set @String ='*(Hunter)'
    end
	SELECT @MemberName = CharName16+@String, @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
   

	----------------------------------------------------------------------------------------------------------
	-- 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

**********************


السيستم بيبداء من Level 5 تقدر تخلية من Level 1 او 10 زي متحب كمان تقدرو تعدلو علية يبقا عن طريق ال Job Kills

بالتوفيق