Im looking for enable weekly job system, but i realize its too hard to find. Anyway i found this query for enable something about similar system for job ranking. Would you guys explain how these querys works, what they do exactly? And if they have errors?
Code:
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 [MENTION=955363]rank[/MENTION] 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 [MENTION=955363]rank[/MENTION] = 1
END
ELSE IF(@index between 6 and 10)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 2
END
ELSE IF(@index between 11 and 20)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 3
END
ELSE IF(@index between 21 and 30)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 4
END
update _TrainingCamp set Rank= [MENTION=955363]rank[/MENTION] where ID =@CampID
update _TrainingCampHonorRank set Rank [MENTION=955363]rank[/MENTION],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 [MENTION=955363]rank[/MENTION] = 1
END
ELSE IF(@index between 6 and 10)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 2
END
ELSE IF(@index between 11 and 20)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 3
END
ELSE IF(@index between 21 and 30)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 4
END
update _TrainingCamp set Rank= [MENTION=955363]rank[/MENTION] where ID =@CampID
update _TrainingCampHonorRank set Rank [MENTION=955363]rank[/MENTION],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 [MENTION=955363]rank[/MENTION] = 1
END
ELSE IF(@index between 6 and 10)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 2
END
ELSE IF(@index between 11 and 20)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 3
END
ELSE IF(@index between 21 and 30)
BEGIN
SET [MENTION=955363]rank[/MENTION] = 4
END
update _TrainingCamp set Rank= [MENTION=955363]rank[/MENTION] where ID =@CampID
update _TrainingCampHonorRank set Rank [MENTION=955363]rank[/MENTION],CampID =@CampID where Ranking = @ID
END
FETCH NEXT FROM RankCursor INTO @CharID
END
CLOSE RankCursor
DEALLOCATE RankCursor
RETURN 1
Code:
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,
[MENTION=283932]member[/MENTION]ID INT,
@HonorPoint INT
AS
SET NOCOUNT ON
IF EXISTS (SELECT CharID FROM _TrainingCampMember WHERE CharID = [MENTION=283932]member[/MENTION]ID)
BEGIN
RETURN -1001
END
DECLARE [MENTION=283932]member[/MENTION]Name VARCHAR(64)
DECLARE [MENTION=283932]member[/MENTION]MaxLvl TINYINT
DECLARE [MENTION=283932]member[/MENTION]CurLvl TINYINT
DECLARE @Jobtype TINYINT
Declare [MENTION=622889]string[/MENTION] NVARCHAR(50)
select @Jobtype = JobType from _CharTrijob where CharID [MENTION=283932]member[/MENTION]ID
if(@Jobtype =1)
begin
set [MENTION=622889]string[/MENTION] ='*(Trader)'
end
else if (@Jobtype =2)
begin
set [MENTION=622889]string[/MENTION] ='*(Thief)'
end
else if(@Jobtype =3)
begin
set [MENTION=622889]string[/MENTION] ='*(Hunter)'
end
SELECT [MENTION=283932]member[/MENTION]Name = CharName16 [MENTION=622889]string[/MENTION], [MENTION=283932]member[/MENTION]MaxLvl = MaxLevel, [MENTION=283932]member[/MENTION]CurLvl = CurLevel FROM _Char WITH (NOLOCK) WHERE CharID = [MENTION=283932]member[/MENTION]ID
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
RETURN -1
END
IF EXISTS (SELECT CharID FROM _TrainingCampMember WITH (NOLOCK) WHERE CampID = @CampID AND CharID = [MENTION=283932]member[/MENTION]ID)
BEGIN
RETURN -2
END
DECLARE [MENTION=975074]Refo[/MENTION]bjID INT
SELECT [MENTION=975074]Refo[/MENTION]bjID = RefObjID FROM _Char WITH (NOLOCK) WHERE CharID = [MENTION=283932]member[/MENTION]ID
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
RETURN -3
END
/* commented by novice. ¾Æ·¡·Î ¿Å±ä´Ù!! 2007. 07. 16.
----------------------------------------------------------------------------------------------------------
-- by novice
DECLARE @ApprenticeCount INT
DECLARE [MENTION=2619528]submen[/MENTION]torCount INT
-- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
SELECT [MENTION=2619528]submen[/MENTION]torCount = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 1
IF ( [MENTION=2619528]submen[/MENTION]torCount >= 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 [MENTION=283932]member[/MENTION]Class TINYINT
SET [MENTION=283932]member[/MENTION]Class = 0
----------------------------------------------------------------------------------------------------------
-- by novice 2007. 07. 16.
-- °¡ÀÔ½ÃÅ°´Â »ç¶÷ÀÇ ·¹º§¿¡ µû¶ó¼* Ŭ·¡½º¸¦ °áÁ¤Çϴµ¥..
-- ±×³É ¹«ÀÛÁ¤ °¢ Ŭ·¡½º¿¡ ´ëÇØ ÀοøÀ» °Ë»çÇÏ¸é ´ç±Ù ¹®Á¦°¡ ÀÖ´Ù
-- °¡ÀÔÇÏ´Â »ç¶÷ÀÇ Å¬·¡½º¿¡ ¸Â´Â Àοø °Ë»ç¸¦ ½ÃµµÇØ¾ß ÇÑ´Ù!!
-- ±Ùµ¥.. ³»°¡ Àü¿¡´Â ¿Ö Àú·¸°Ô Çß¾úÀ»±î.......... ¤Ð_¤Ð
----------------------------------------------------------------------------------------------------------
IF [MENTION=283932]member[/MENTION]Class = 0
BEGIN
DECLARE [MENTION=387241]Mentor[/MENTION]Count INT
-- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
SELECT [MENTION=387241]Mentor[/MENTION]Count = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 0
IF ( [MENTION=387241]Mentor[/MENTION]Count >= 1 )
BEGIN
RETURN -14 -- ADD DBÀÛ¾÷ ½ÇÆжó´Â ·Î±×·Î ¾´´Ù..
END
END
ELSE IF [MENTION=283932]member[/MENTION]Class = 1
BEGIN
DECLARE [MENTION=2619528]submen[/MENTION]torCount INT
-- Àý´ë nolockÀ¸·Î ÇÏÁö ¸»ÀÚ!!
SELECT [MENTION=2619528]submen[/MENTION]torCount = COUNT(*) FROM _TrainingCampMember WHERE CampID = @CampID AND MemberClass = 1
IF ( [MENTION=2619528]submen[/MENTION]torCount >= 2 )
BEGIN
RETURN -11
END
END
ELSE IF [MENTION=283932]member[/MENTION]Class = 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, [MENTION=283932]member[/MENTION]ID, [MENTION=975074]Refo[/MENTION]bjID, [MENTION=283932]member[/MENTION]Name, GETDATE(), [MENTION=283932]member[/MENTION]Class, [MENTION=283932]member[/MENTION]MaxLvl, [MENTION=283932]member[/MENTION]CurLvl, [MENTION=283932]member[/MENTION]MaxLvl, @HonorPoint)
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
RETURN -5
END
IF ( 0 < (SELECT COUNT(*) FROM _TrainingCampBuffStatus
WHERE CampID = @CampID and RecipientCharID = [MENTION=283932]member[/MENTION]ID) )
BEGIN
RETURN -2006
END
IF [MENTION=283932]member[/MENTION]Class = 0 OR [MENTION=283932]member[/MENTION]Class = 2
BEGIN
-- °æÇèÄ¡ ´©Àû ¹öÇÁ¿¡ ´ëÇÑ ³»¿ë
INSERT _TrainingCampBuffStatus
VALUES (@CampID, [MENTION=283932]member[/MENTION]ID, 0, 0, default, 0, 0)
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
RETURN -2005
END
END
-- °æÇèÄ¡ Àû¸³ ¹öÇÁ¿¡ ´ëÇÑ ³»¿ë
IF [MENTION=283932]member[/MENTION]Class = 0
BEGIN
INSERT _TrainingCampBuffStatus
SELECT @CampID, [MENTION=283932]member[/MENTION]ID, 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 [MENTION=283932]member[/MENTION]Class = 2
BEGIN
INSERT _TrainingCampBuffStatus
SELECT @CampID, [MENTION=283932]member[/MENTION]ID, 1, 0, '2000-01-01', 0, 3
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
RETURN -2005
END
END
SET NOCOUNT OFF
RETURN 1
