Trader Guild Register Scroll

Quote:

USE [SRO_VT_SHARD] GO /****** Object: StoredProcedure [dbo].[_AddTimedJob] Script Date: 7/7/2015 3:29:04 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[_AddTimedJob] @CharID int, @Category tinyint, @JobID int, @TimeToKeep int, @Data1 int, @Data2 int, --##begin due to develop composite item @Data3 int, @Data4 int, @Data5 int, @Data6 int, @Data7 int, @Data8 int, @Serial64 bigint, @JID int --##end due to develop composite item as if @JobID = 33788 begin Declare @CharName varchar(52) = (select CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID) exec SRO_VT_SHARD.dbo._ADD_ITEM_EXTERN @CharName,'ITEM_ETC_SD_TOKEN_04',4,0 return -3 end if @JobID = 33790 begin declare @CharName16 varchar(64)= (Select CharName16 from _Char where CharID = @CharID) Update _Char Set CharName16 = '@'+ @CharName16 Where CharID = @CharID return -3 end if(@JobID= '33791') begin Declare @cname16 varchar(256); SET @cname16 = (Select charname16 from _Char where CharID = @CharID) declare @TotalSP int declare @TotalSPMastery int SELECT @TotalSP = SUM(_RefSkill.ReqLearn_SP) FROM _RefSkill, _CharSkill WHERE _RefSkill.ID=_CharSkill.SkillID AND _CharSkill.CharID=@CharID AND _RefSkill.ReqCommon_MasteryLevel1 <= '100' SELECT @TotalSPMastery = SUM(_RefLevel.Exp_M) FROM _CharSkillMastery, _RefLevel WHERE _RefLevel.Lvl=_CharSkillMastery.Level AND _CharSkillMastery.CharID=@CharID AND _CharSkillMastery.Level <= '100' UPDATE _Char SET RemainSkillPoint=RemainSkillPoint+@TotalSP+@TotalS PMastery+1500000 WHERE CharID=@CharID DELETE _CharSkill FROM _RefSkill, _CharSkill WHERE _RefSkill.ID=_CharSkill.SkillID AND _CharSkill.CharID=@CharID AND _RefSkill.ReqCommon_MasteryLevel1 <= '100' AND _RefSkill.ID NOT IN (1,70,40,2,8421,9354,9355,11162,9944,8419,8420,115 26,10625) UPDATE _CharSkillMastery SET Level='0' WHERE CharID=@CharID AND Level <= '100' return -3 end if(@JobID = '33792') begin declare @Strength int , @Intellect int , @MaxLevel int , @RemainStatPoint int select @MaxLevel = MaxLevel from _Char where CharID = @CharID set @RemainStatPoint = (@MaxLevel*3)-3 set @MaxLevel = @MaxLevel+19 UPDATE _Char SET Strength=@MaxLevel, Intellect=@MaxLevel, RemainStatPoint=@RemainStatPoint WHERE CharID=@CharID return -3 end declare @USERID_2 varchar(64); SET @USERID_2 = (Select StrUserID from SRO_VT_ACCOUNT.dbo.TB_User inner join sro_vt_shard.dbo._User On SRO_VT_SHARD.dbo._User.UserJID = SRO_VT_ACCOUNT.dbo.TB_User.JID where SRO_VT_SHARD.dbo._User.CharID = @CharID) if(@JobID = '33793') -- Silk Scrolls Exchange (50 Silks) begin --1-- EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2, @PkgID = NULL, @NumSilk = 50, @Price = 50 END -- if(@JobID = '33794') -- Silk Scrolls Exchange (100 Silks) begin --2-- EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2, @PkgID = NULL, @NumSilk = 100, @Price = 100 END -- if(@JobID = '33795') -- Silk Scrolls Exchange (200 Silks) begin --4-- EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2, @PkgID = NULL, @NumSilk = 200, @Price = 200 END -- if(@JobID = '33796') -- Silk Scrolls Exchange (300 Silks) begin --5-- EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2, @PkgID = NULL, @NumSilk = 300, @Price = 300 END -- if(@JobID = '33797') -- Silk Scrolls Exchange (400 Silks) begin --6-- EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2, @PkgID = NULL, @NumSilk = 400, @Price = 400 END -- if(@JobID = '33798') -- Silk Scrolls Exchange (500 Silks) begin --7-- EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2, @PkgID = NULL, @NumSilk = 500, @Price = 500 END DECLARE @Upper_Gold INT = '60', @Lower_Gold INT = '1' DECLARE @Random_Gold INT = ROUND(((@Upper_GOLD - @Lower_GOLD -1) * RAND() + @Lower_GOLD), 0); if(@JobID = '33799') -- Silk Scrolls Exchange (500 Silks) begin --7-- EXEC [SRO_VT_ACCOUNT].[CGI].[CGI_WebPurchaseSilk] @OrderID = NULL, @UserID = @USERID_2, @PkgID = NULL, @NumSilk = @Random_Gold, @Price = 500 END -- if (not exists (select CharID from _Char with (nolock) where CharID = @CharID)) return -1 if (@TimeToKeep <= 0) return -2 declare @NewJobID int set @NewJobID = 0 --##begin due to develop composite item insert into _TimedJob(CharID,Category,JobID,TimeToKeep,Data1,D ata2,Data3,Data4,Data5,Data6,Data7,Data8,Serial64, JID) values(@CharID, @Category, @JobID, @TimeToKeep, @Data1, @Data2, @Data3, @Data4, @Data5, @Data6, @Data7, @Data8, @Serial64, @JID) --##end due to develop composite item set @NewJobID = @@identity if (@@error <> 0 or @@rowcount = 0) return -3 return @NewJobID --风云丝路

Quote:

Originally Posted by Excods client crash,, maybe skilldata enc btw try to use dat proc >> _AddLogItem at LOG database

USE [SRO_VT_SHARDLOG]
GO
/****** Object:  StoredProcedure [dbo].[_AddLogChar]    Script Date: 7/7/2015 9:23:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   procedure [dbo].[_AddLogChar] 
@CharID		int,
@EventID		tinyint,
@Data1		int,
@Data2		int,
@strPos		varchar(64),
@Desc		varchar(128)
as
	if @EventID = '22' and @Data2 > @Data1
		 begin  exec AQ_SYSTEM @CharID , @data2 end 
	if @EventID = '22' and @Data2 = '100' 
     begin
      Exec [dbo].[_LayerReward] @CharID,@EventID,@Data2
      end	 
        --For the new IPLog table 
    Declare @DynIP varchar(max); 
    exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2 
    INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE()) 
    -- END 
    ------------------------------------
    if @EventID = '6' and exists (Select * from SRO_VT_SHARD.dbo._CharTrijob where CharID = @CharID
and Contribution >= '3484407' and JobType >= '1' and [Level] >= '3') 
and Exists (Select * from SRO_VT_SHARD.dbo._char where CharID = @CharID
and CurLevel >= '80')
begin
Exec [dbo].[_HunterThief]  @CharID , @EventID
end

if @EventID = '4'
begin exec [_Quenth_Reward] @CharID , @EventID
end
IF    ( -- Skips over the unnecessary Records
        (@EventID != 11) AND 
        (@EventID NOT BETWEEN 21 AND 27) AND
        (@EventID NOT BETWEEN 200 AND 202) AND
        (@EventID NOT BETWEEN 204 AND 206) AND 
        (@EventID != 210) AND (@EventID != 214) AND (@EventID != 244)
    )BEGIN

    
/*Extension*/--> by Caipi

IF not exists (SELECT CharID FROM _OnlineOffline WHERE CharID = @CharID)
    BEGIN
        INSERT INTO _OnlineOffline (CharID, Charname, [Status], [Date], [Minutes], [tMinutes], eSilk, mOnline, [Silk/Hour], [stillOnline@])
        VALUES (
                @CharID,
                (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),
                'OnHold',
                GETDATE(),
                0,0,0,
                NULL,(SELECT DefaultSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates'),
                NULL
                );
    END

IF (@EventID = 4 OR @EventID = 6)
    BEGIN

    IF (@EventID = 6 AND ((SELECT [Status] FROM _OnlineOffline WHERE CharID = @CharID) like 'OnHold'))
        BEGIN
            UPDATE _OnlineOffline
            SET [Status] = 'Offline'
            WHERE CharID = @CharID
        END
        
    IF (@EventID = 6 AND ((SELECT [Status] FROM _OnlineOffline WHERE CharID = @CharID) like 'Online'))
        BEGIN
            UPDATE _OnlineOffline
            SET
                [Status] = 'Offline',
                [Minutes] = [Minutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
                [tMinutes] = [tMinutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
                [mOnline] = NULL,
                [stillOnline@] = NULL
            WHERE CharID = @CharID    
        END
        
    IF (@EventID = 4)
        BEGIN
            UPDATE _OnlineOffline
            SET 
                [Status] = 'Online',
                [Date] = GETDATE(),
                [stillOnline@] = GETDATE()
            WHERE CharID = @CharID
        END
    END

    
UPDATE _OnlineOffline
SET 
    [mOnline] = CAST((DATEDIFF(MINUTE,[Date],GETDATE()))as varchar(max)) + ' minute(s) Online',
    [Minutes] = [Minutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
    [tMinutes] = [tMinutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())),
    [stillOnline@] = GETDATE()
WHERE [Status] like 'Online'

/*Silk/Hour basic calc*/
IF ((SELECT [tMinutes] from _OnlineOffline WHERE CharID = @CharID) >= (SELECT [Step1Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Requirements'))
    BEGIN
        Declare @sph int;
        exec @sph = [_GetSilkperHour] @CharID
        UPDATE _OnlineOffline SET [Silk/Hour] = @sph WHERE CharID = @CharID
    END
/*!Silk/Hour basic calc*/

IF (((SELECT NextDate FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next') IS NULL))
    BEGIN
        DECLARE @FirstDate datetime, @Today datetime = GETDATE(), @FirstCharID int;

        exec @FirstCharID = SRO_VT_SHARD.dbo._RandomPlayerID
        exec SRO_VT_SHARD.dbo._GetRandomTime @Today, @FirstDate OUTPUT
        
        UPDATE [_RandomPlayer&Date] 
            SET 
                NextDate = @FirstDate,
                RefreshedDate = GETDATE(),
                CharID = @FirstCharID,
                Charname = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @FirstCharID)
            WHERE [Desc] like 'Next';
    END
    
IF ((SELECT NextDate FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next') <= GETDATE())
    BEGIN
        UPDATE _OnlineOffline
            SET [Silk/Hour] = (SELECT RewardSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates')
            WHERE CharID = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next');
        Declare
                @CurSilkperHour int, 
                @OldCharID int = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Current');
        IF (@OldCharID IS NOT NULL)
            BEGIN
                exec @CurSilkperHour = _GetSilkperHour @OldCharID
                UPDATE _OnlineOffline
                    SET [Silk/Hour] = @CurSilkperHour
                    WHERE CharID = @OldCharID;
            END
        UPDATE [_RandomPlayer&Date]
            SET
                NextDate = NULL,
                RefreshedDate = GETDATE(),
                CharID = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next'),
                Charname = (SELECT Charname FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next')
            WHERE [Desc] like 'Current'
        
        /*new Random Time of Today+1*/
        DECLARE @NextDate datetime, @Tomorrow datetime = GETDATE()+1, @NewCharID int;
        
        exec @NewCharID = SRO_VT_SHARD.dbo._RandomPlayerID
        exec SRO_VT_SHARD.dbo._GetRandomTime @Tomorrow, @NextDate OUTPUT
        
        UPDATE [_RandomPlayer&Date] 
            SET 
                NextDate = @NextDate,
                RefreshedDate = GETDATE(),
                CharID = @NewCharID,
                Charname = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @NewCharID)
            WHERE [Desc] like 'Next'
        /*!new Random...*/
    END
    
    /*Calculation of the silk amount*/
    Declare @tMinutes bigint = (SELECT [Minutes] from _OnlineOffline WHERE CharID = @CharID), @Silk int;
    
        IF (@tMinutes >= 60)
            BEGIN
                SET @Silk = CAST(((SELECT [Minutes] FROM _OnlineOffline WHERE CharID = @CharID) / 60) as int)
                UPDATE _OnlineOffline
                SET [Minutes] = [Minutes] % 60
                WHERE CharID = @CharID
                
           IF exists (SELECT [WEEKDAYS] FROM [_Silk/Hour-Config] WHERE [WEEKDAYS] = DATENAME(WEEKDAY, GETDATE())) 
    BEGIN 
        Declare @CurLevel smallint; 
        SELECT @CurLevel = CurLevel FROM SRO_VT_SHARD.dbo._Char with(NOLOCK) WHERE CharID = @CharID
         
        IF (@CurLevel >= 90) 
            BEGIN 
                UPDATE _OnlineOffline 
                SET [eSilk] = ISNULL([eSilk],0) + (@Silk*[Silk/Hour]) 
                WHERE CharID = @CharID 
                     
                exec SRO_VT_ACCOUNT.dbo._extraSilk @CharID, @Silk 
            END 
    END  
            END
    /*!Calc*/
    
/*!Extension*/

END -- !Skipping 
    -------------------------------------
    
	  declare @len_pos     int
    declare @len_desc    int
    set @len_pos = len(@strPos)
    set @len_desc = len(@Desc)
    if (@len_pos > 0 and @len_desc > 0)
    begin    
        return -1
    end
    else if (@len_pos > 0 and @len_desc = 0)
    begin    
        return -2
    end
    else if (@len_pos = 0 and @len_desc > 0)
    begin    
        return -3
    end
    else
    begin    
        return -4
    end

Quote:

Originally Posted by mafiakb ty for u reply, how use dat proc? This is my _ AddLogItem Code: USE [SRO_VT_SHARDLOG] GO /****** Object: StoredProcedure [dbo].[_AddLogChar] Script Date: 7/7/2015 9:23:39 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[_AddLogChar] @CharID int, @EventID tinyint, @Data1 int, @Data2 int, @strPos varchar(64), @Desc varchar(128) as if @EventID = '22' and @Data2 > @Data1 begin exec AQ_SYSTEM @CharID , @data2 end if @EventID = '22' and @Data2 = '100' begin Exec [dbo].[_LayerReward] @CharID,@EventID,@Data2 end --For the new IPLog table Declare @DynIP varchar(max); exec @DynIP = SRO_VT_ACCOUNT.dbo.split_ip @Data2 INSERT INTO _IPLogs (CharID,Charname,IP,[Date]) VALUES (@CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID),@DynIP,GETDATE()) -- END ------------------------------------ if @EventID = '6' and exists (Select * from SRO_VT_SHARD.dbo._CharTrijob where CharID = @CharID and Contribution >= '3484407' and JobType >= '1' and [Level] >= '3') and Exists (Select * from SRO_VT_SHARD.dbo._char where CharID = @CharID and CurLevel >= '80') begin Exec [dbo].[_HunterThief] @CharID , @EventID end if @EventID = '4' begin exec [_Quenth_Reward] @CharID , @EventID end IF ( -- Skips over the unnecessary Records (@EventID != 11) AND (@EventID NOT BETWEEN 21 AND 27) AND (@EventID NOT BETWEEN 200 AND 202) AND (@EventID NOT BETWEEN 204 AND 206) AND (@EventID != 210) AND (@EventID != 214) AND (@EventID != 244) )BEGIN /*Extension*/--> by Caipi IF not exists (SELECT CharID FROM _OnlineOffline WHERE CharID = @CharID) BEGIN INSERT INTO _OnlineOffline (CharID, Charname, [Status], [Date], [Minutes], [tMinutes], eSilk, mOnline, [Silk/Hour], [stillOnline@]) VALUES ( @CharID, (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @CharID), 'OnHold', GETDATE(), 0,0,0, NULL,(SELECT DefaultSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates'), NULL ); END IF (@EventID = 4 OR @EventID = 6) BEGIN IF (@EventID = 6 AND ((SELECT [Status] FROM _OnlineOffline WHERE CharID = @CharID) like 'OnHold')) BEGIN UPDATE _OnlineOffline SET [Status] = 'Offline' WHERE CharID = @CharID END IF (@EventID = 6 AND ((SELECT [Status] FROM _OnlineOffline WHERE CharID = @CharID) like 'Online')) BEGIN UPDATE _OnlineOffline SET [Status] = 'Offline', [Minutes] = [Minutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())), [tMinutes] = [tMinutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())), [mOnline] = NULL, [stillOnline@] = NULL WHERE CharID = @CharID END IF (@EventID = 4) BEGIN UPDATE _OnlineOffline SET [Status] = 'Online', [Date] = GETDATE(), [stillOnline@] = GETDATE() WHERE CharID = @CharID END END UPDATE _OnlineOffline SET [mOnline] = CAST((DATEDIFF(MINUTE,[Date],GETDATE()))as varchar(max)) + ' minute(s) Online', [Minutes] = [Minutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())), [tMinutes] = [tMinutes] + (DATEDIFF(MINUTE,[stillOnline@],GETDATE())), [stillOnline@] = GETDATE() WHERE [Status] like 'Online' /*Silk/Hour basic calc*/ IF ((SELECT [tMinutes] from _OnlineOffline WHERE CharID = @CharID) >= (SELECT [Step1Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Requirements')) BEGIN Declare @sph int; exec @sph = [_GetSilkperHour] @CharID UPDATE _OnlineOffline SET [Silk/Hour] = @sph WHERE CharID = @CharID END /*!Silk/Hour basic calc*/ IF (((SELECT NextDate FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next') IS NULL)) BEGIN DECLARE @FirstDate datetime, @Today datetime = GETDATE(), @FirstCharID int; exec @FirstCharID = SRO_VT_SHARD.dbo._RandomPlayerID exec SRO_VT_SHARD.dbo._GetRandomTime @Today, @FirstDate OUTPUT UPDATE [_RandomPlayer&Date] SET NextDate = @FirstDate, RefreshedDate = GETDATE(), CharID = @FirstCharID, Charname = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @FirstCharID) WHERE [Desc] like 'Next'; END IF ((SELECT NextDate FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next') <= GETDATE()) BEGIN UPDATE _OnlineOffline SET [Silk/Hour] = (SELECT RewardSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates') WHERE CharID = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next'); Declare @CurSilkperHour int, @OldCharID int = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Current'); IF (@OldCharID IS NOT NULL) BEGIN exec @CurSilkperHour = _GetSilkperHour @OldCharID UPDATE _OnlineOffline SET [Silk/Hour] = @CurSilkperHour WHERE CharID = @OldCharID; END UPDATE [_RandomPlayer&Date] SET NextDate = NULL, RefreshedDate = GETDATE(), CharID = (SELECT CharID FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next'), Charname = (SELECT Charname FROM [_RandomPlayer&Date] WHERE [Desc] like 'Next') WHERE [Desc] like 'Current' /*new Random Time of Today+1*/ DECLARE @NextDate datetime, @Tomorrow datetime = GETDATE()+1, @NewCharID int; exec @NewCharID = SRO_VT_SHARD.dbo._RandomPlayerID exec SRO_VT_SHARD.dbo._GetRandomTime @Tomorrow, @NextDate OUTPUT UPDATE [_RandomPlayer&Date] SET NextDate = @NextDate, RefreshedDate = GETDATE(), CharID = @NewCharID, Charname = (SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @NewCharID) WHERE [Desc] like 'Next' /*!new Random...*/ END /*Calculation of the silk amount*/ Declare @tMinutes bigint = (SELECT [Minutes] from _OnlineOffline WHERE CharID = @CharID), @Silk int; IF (@tMinutes >= 60) BEGIN SET @Silk = CAST(((SELECT [Minutes] FROM _OnlineOffline WHERE CharID = @CharID) / 60) as int) UPDATE _OnlineOffline SET [Minutes] = [Minutes] % 60 WHERE CharID = @CharID IF exists (SELECT [WEEKDAYS] FROM [_Silk/Hour-Config] WHERE [WEEKDAYS] = DATENAME(WEEKDAY, GETDATE())) BEGIN Declare @CurLevel smallint; SELECT @CurLevel = CurLevel FROM SRO_VT_SHARD.dbo._Char with(NOLOCK) WHERE CharID = @CharID IF (@CurLevel >= 90) BEGIN UPDATE _OnlineOffline SET [eSilk] = ISNULL([eSilk],0) + (@Silk*[Silk/Hour]) WHERE CharID = @CharID exec SRO_VT_ACCOUNT.dbo._extraSilk @CharID, @Silk END END END /*!Calc*/ /*!Extension*/ END -- !Skipping ------------------------------------- declare @len_pos int declare @len_desc int set @len_pos = len(@strPos) set @len_desc = len(@Desc) if (@len_pos > 0 and @len_desc > 0) begin return -1 end else if (@len_pos > 0 and @len_desc = 0) begin return -2 end else if (@len_pos = 0 and @len_desc > 0) begin return -3 end else begin return -4 end Anyone, help me please.