Anyone else get reports that this is broken on vsro files? Can we make this work?
Printable View
Anyone else get reports that this is broken on vsro files? Can we make this work?
You mean reward job weekly?
Its not broken and of course you can make it work.
You just need to make scheduler job to execute storeprocedure.
For me its work using sql agent to scheduling this procedure.
Create sqlagent job to execute _RewardJobWeekly.
Sorry im mistake sp name
_UpdateTriangleJobWeekly
Quote:
ALTER PROCEDURE [dbo].[_UpdateTriangleJobWeekly]
AS
SET NOCOUNT ON
-- #define OBJ_JOBSTATE_TRADER (BYTE)0x01
-- #define OBJ_JOBSTATE_ROBBER (BYTE)0x02
-- #define OBJ_JOBSTATE_HUNTER (BYTE)0x03
-- Çæ.. ¸¸¾à¿¡ ¾Æ¹«µµ Á÷¾÷Ȱµ¿À» ÇÏÁö ¾ÊÀ¸¸é ·©Å· °»½ÅÀÌ ¾ø´Â ¹®Á¦°¡ ÀÖ³×?
-- ÀÌ°É ¾î¿±î? °¡´É¼ºÀº ¸Å¿ì ³·Áö¸¸.. ©e..
-- INT MAX º¯¼ö ¼±¾ð
DECLARE @INT_MAX BIGINT
SET @INT_MAX = 2147483647
-- ÀÏ´Ü Contribution¿¡ µû¶ó ÇåÅÍ¿Í µµÀûÀÇ ¼º°ú±ÝÀ» ºÐ¹èÇØ º¸ÀÚ.
DECLARE @TotalContribution BIGINT
DECLARE @TotalReward BIGINT
DECLARE @Contribution INT
DECLARE @CharID INT
DECLARE @Reward INT
-- @Reward INT MAX Overflow¸¦ ÇØ°áÇϱâ À§ÇÑ º¯¼ö
DECLARE @Reward_Bigint BIGINT
-- µµÀû ¼º°ú±Ý
SELECT @TotalContribution = SUM( CAST(Contribution as bigint) ) FROM _CharTrijob WITH (NOLOCK) WHERE JobType = 2
SELECT @TotalReward = ISNULL( MIN(Reward), 0 ) FROM _TrijobRewards WITH (NOLOCK) WHERE JobType = 2 -- NULL ¸®ÅÏ ¼öÁ¤ by zone0000
-- ¼öÁ¤ºÎºÐ
if(@TotalContribution <> 0 or @TotalReward <> 0)
begin
SET @Reward_Bigint = 0
DECLARE RewRobberCursor CURSOR FAST_FORWARD FOR
SELECT CharID, Contribution FROM _CharTrijob WITH (NOLOCK) WHERE JobType = 2
OPEN RewRobberCursor
FETCH NEXT FROM RewRobberCursor INTO @CharID, @Contribution
WHILE (@@fetch_status = 0)
BEGIN
-- @Reward INT Overflow °ËÁõ ÄÚµå Ãß°¡
SET @Reward_Bigint = @TotalReward * @Contribution / @TotalContribution
SELECT @Reward_Bigint = @Reward_Bigint + ISNULL( MIN(Reward), 0 ) from _CharTrijob WHERE CharID = @CharID
IF( @Reward_Bigint > @INT_MAX )
BEGIN
SET @Reward = @INT_MAX
END
ELSE
BEGIN
SET @Reward = @Reward_Bigint
END
UPDATE _CharTrijob SET Reward = @Reward WHERE CharID = @CharID
--select @Reward
FETCH NEXT FROM RewRobberCursor INTO @CharID, @Contribution
END
CLOSE RewRobberCursor
DEALLOCATE RewRobberCursor
-- Reward¸¦ 0À¸·Î~!
UPDATE _TrijobRewards SET Reward = 0 where JobType = 2
end
-- ÇåÅÍ ¼º°ú±Ý
SELECT @TotalContribution = SUM( CAST(Contribution as bigint) ) FROM _CharTrijob WITH (NOLOCK) WHERE JobType = 3
SELECT @TotalReward = ISNULL( MIN(Reward), 0 ) FROM _TrijobRewards WITH (NOLOCK) WHERE JobType = 3 -- NULL ¸®ÅÏ ¼öÁ¤ by zone0000
-- ¼öÁ¤ºÎºÐ
if(@TotalContribution <> 0 or @TotalReward <> 0)
begin
SET @Reward_Bigint = 0
DECLARE RewHunterCursor CURSOR FAST_FORWARD FOR
SELECT CharID, Contribution FROM _CharTrijob WITH (NOLOCK) WHERE JobType = 3
OPEN RewHunterCursor
FETCH NEXT FROM RewHunterCursor INTO @CharID, @Contribution
WHILE (@@fetch_status = 0)
BEGIN
SET @Reward_Bigint = @TotalReward * @Contribution / @TotalContribution
SELECT @Reward_Bigint = @Reward_Bigint + ISNULL( MIN(Reward), 0 ) from _CharTrijob WHERE CharID = @CharID
IF( @Reward_Bigint > @INT_MAX )
BEGIN
SET @Reward = @INT_MAX
END
ELSE
BEGIN
SET @Reward = @Reward_Bigint
END
UPDATE _CharTrijob SET Reward = @Reward WHERE CharID = @CharID
--select @Reward
FETCH NEXT FROM RewHunterCursor INTO @CharID, @Contribution
END
CLOSE RewHunterCursor
DEALLOCATE RewHunterCursor
-- Reward¸¦ 0À¸·Î~!
UPDATE _TrijobRewards SET Reward = 0 where JobType = 3
end
DECLARE @Rank TINYINT
DECLARE @NickName VARCHAR(17)
-- ¸ÕÀú Activity WeeklyRankingÀ» »Ì¾Æº¸ÀÚ.
DELETE FROM Tab_RefRanking_TraderActivity
DELETE FROM Tab_RefRanking_HunterActivity
DELETE FROM Tab_RefRanking_RobberActivity
DECLARE @JobLevel TINYINT
DECLARE @JobExp INT
DECLARE @Country SMALLINT
-- For Trader!!
SET @Rank = 1
DECLARE ActTraderCursor CURSOR FAST_FORWARD FOR
SELECT TOP 50 CharID, [Level], [Exp]
FROM _CharTrijob WITH (NOLOCK)
WHERE JobType = 1
ORDER BY [Level] DESC, [Exp] DESC
OPEN ActTraderCursor
FETCH NEXT FROM ActTraderCursor INTO @CharID, @JobLevel, @JobExp
WHILE (@@fetch_status = 0)
BEGIN
SELECT @Country = _RefObjCommon.Country FROM _Char
join _RefObjCommon on _Char.RefObjID = _RefObjCommon.ID
where CharID = @CharID
SELECT @NickName = NickName16 FROM _Char WITH (NOLOCK) WHERE CharID = @CharID
INSERT INTO Tab_RefRanking_TraderActivity VALUES (@Rank, @NickName, @JobLevel, @JobExp, @Country)
SET @Rank = @Rank + 1
FETCH NEXT FROM ActTraderCursor INTO @CharID, @JobLevel, @JobExp
END
CLOSE ActTraderCursor
DEALLOCATE ActTraderCursor
-- For Robber!!
SET @Rank = 1
DECLARE ActRobberCursor CURSOR FAST_FORWARD FOR
SELECT TOP 50 CharID, [Level], [Exp]
FROM _CharTrijob WITH (NOLOCK)
WHERE JobType = 2
ORDER BY [Level] DESC, [Exp] DESC
OPEN ActRobberCursor
FETCH NEXT FROM ActRobberCursor INTO @CharID, @JobLevel, @JobExp
WHILE (@@fetch_status = 0)
BEGIN
SELECT @Country = _RefObjCommon.Country FROM _Char
join _RefObjCommon on _Char.RefObjID = _RefObjCommon.ID
where CharID = @CharID
SELECT @NickName = NickName16 FROM _Char WITH (NOLOCK) WHERE CharID = @CharID
INSERT INTO Tab_RefRanking_RobberActivity VALUES (@Rank, @NickName, @JobLevel, @JobExp, @Country)
SET @Rank = @Rank + 1
FETCH NEXT FROM ActRobberCursor INTO @CharID, @JobLevel, @JobExp
END
CLOSE ActRobberCursor
DEALLOCATE ActRobberCursor
-- For Hunter!!
SET @Rank = 1
DECLARE ActHunterCursor CURSOR FAST_FORWARD FOR
SELECT TOP 50 CharID, [Level], [Exp]
FROM _CharTrijob WITH (NOLOCK)
WHERE JobType = 3
ORDER BY [Level] DESC, [Exp] DESC
OPEN ActHunterCursor
FETCH NEXT FROM ActHunterCursor INTO @CharID, @JobLevel, @JobExp
WHILE (@@fetch_status = 0)
BEGIN
SELECT @Country = _RefObjCommon.Country FROM _Char
join _RefObjCommon on _Char.RefObjID = _RefObjCommon.ID
where CharID = @CharID
SELECT @NickName = NickName16 FROM _Char WITH (NOLOCK) WHERE CharID = @CharID
INSERT INTO Tab_RefRanking_HunterActivity VALUES (@Rank, @NickName, @JobLevel, @JobExp, @Country)
SET @Rank = @Rank + 1
FETCH NEXT FROM ActHunterCursor INTO @CharID, @JobLevel, @JobExp
END
CLOSE ActHunterCursor
DEALLOCATE ActHunterCursor
-- For Trader!!
-- ¼öÁ¤ºÎºÐ
if(exists(select '' from _CharTrijob where JobType = 1 and Contribution > 0))
begin
DELETE FROM Tab_RefRanking_TraderContribution
SET @Rank = 1
DECLARE ContTraderCursor CURSOR FAST_FORWARD FOR
SELECT TOP 50 CharID, [Level], Contribution
FROM _CharTrijob WITH (NOLOCK)
WHERE JobType = 1
ORDER BY Contribution DESC
OPEN ContTraderCursor
FETCH NEXT FROM ContTraderCursor INTO @CharID, @JobLevel, @Contribution
WHILE (@@fetch_status = 0)
BEGIN
SELECT @NickName = NickName16 FROM _Char WITH (NOLOCK) WHERE CharID = @CharID
INSERT INTO Tab_RefRanking_TraderContribution VALUES (@Rank, @NickName, @JobLevel, @Contribution)
SET @Rank = @Rank + 1
FETCH NEXT FROM ContTraderCursor INTO @CharID, @JobLevel, @Contribution
END
CLOSE ContTraderCursor
DEALLOCATE ContTraderCursor
UPDATE _CharTrijob SET Contribution = 0 where JobType = 1
end
-- For Robber!!
-- ¼öÁ¤ºÎºÐ
if(exists(select '' from _CharTrijob where JobType = 2 and Contribution > 0))
begin
DELETE FROM Tab_RefRanking_RobberContribution
SET @Rank = 1
DECLARE ContRobberCursor CURSOR FAST_FORWARD FOR
SELECT TOP 50 CharID, [Level], Contribution
FROM _CharTrijob WITH (NOLOCK)
WHERE JobType = 2
ORDER BY Contribution DESC
OPEN ContRobberCursor
FETCH NEXT FROM ContRobberCursor INTO @CharID, @JobLevel, @Contribution
WHILE (@@fetch_status = 0)
BEGIN
SELECT @NickName = NickName16 FROM _Char WITH (NOLOCK) WHERE CharID = @CharID
INSERT INTO Tab_RefRanking_RobberContribution VALUES (@Rank, @NickName, @JobLevel, @Contribution)
SET @Rank = @Rank + 1
FETCH NEXT FROM ContRobberCursor INTO @CharID, @JobLevel, @Contribution
END
CLOSE ContRobberCursor
DEALLOCATE ContRobberCursor
UPDATE _CharTrijob SET Contribution = 0 where JobType = 2
end
-- For Hunter!!
-- ¼öÁ¤ºÎºÐ
if(exists(select '' from _CharTrijob where JobType = 3 and Contribution > 0))
begin
DELETE FROM Tab_RefRanking_HunterContribution
SET @Rank = 1
DECLARE ContHunterCursor CURSOR FAST_FORWARD FOR
SELECT TOP 50 CharID, [Level], Contribution
FROM _CharTrijob WITH (NOLOCK)
WHERE JobType = 3
ORDER BY Contribution DESC
OPEN ContHunterCursor
FETCH NEXT FROM ContHunterCursor INTO @CharID, @JobLevel, @Contribution
WHILE (@@fetch_status = 0)
BEGIN
SELECT @NickName = NickName16 FROM _Char WITH (NOLOCK) WHERE CharID = @CharID
INSERT INTO Tab_RefRanking_HunterContribution VALUES (@Rank, @NickName, @JobLevel, @Contribution)
SET @Rank = @Rank + 1
FETCH NEXT FROM ContHunterCursor INTO @CharID, @JobLevel, @Contribution
END
CLOSE ContHunterCursor
DEALLOCATE ContHunterCursor
UPDATE _CharTrijob SET Contribution = 0 where JobType = 3
end
SET NOCOUNT OFF
Ok so just create an SQL agent job to automatically execute this script?
Is it a way to modify the rewards ?