[SQL] auto free Silk/Hour based on the Online time (System)

[Caipi]

Introduction:

Stick to the steps stated underneath to achieve the result I were aiming at


What does it do?
Basically it rewards the Players for their online time automatically. It'll log all online chars into another table, with several other information. Furthermore it'll add 1 Silk/Hour on default to the chars which is 60 minutes+ online.
The amount per Hour which they gain is also stated in the _OnlineOffline table on the right hand side - when a char exceeds the duration of his online time of 1 Week, he/she'll automatically get 2 Silk/Hour (1 Month+ = 3 Silk/Hour).
More importantly is that it'll generate a random time at which a random player will gain for a specific amount of time 5 Silk/Hour - each day another one.

It works completely automated since it's based on the procedure _AddLogChar. I tested it manually on SQL'08, but actually it should also be working well on SQL '05 I already bypassed the use of DATE which SQL'05 doesn't support.

So here are two screens of the new tables..

_OnlineOffline


_RandomPlayer&Date


tbl_Silk/Hour-Config

(Requirements = Minutes Playtime)


How to..

Order (execute the first one then the other one and so on..):

tbl_OnlineOffline
->
tbl_RandomPlayer&Date
->
sp_GetRandomTime
->
sp_RandomPlayerID
->
sp_extraSilk
->
sp_GetSilkperHour
->
sp_AddLogChar


tbl = Table
sp = Stored Procedure

SRO_VT_SHARD -> Shard DB
SRO_VT_ACCOUNT -> Account DB
SRO_VT_SHARDLOG -> Log DB

Queries to be executed..

USE SRO_VT_SHARDLOG

BEGIN TRY
    DROP TABLE [_Silk/Hour-Config]
END TRY
BEGIN CATCH END CATCH;

CREATE TABLE [_Silk/Hour-Config] (
    [Desc] varchar(max) NULL,
    [DefaultSilk] int NOT NULL,
    [Step1Silk] int NOT NULL,
    [Step2Silk] int NOT NULL,
    [Step3Silk] int NOT NULL,
    [RewardSilk] int NOT NULL,
    [WEEKDAYS] varchar(15) NULL
    
    CHECK (
            [WEEKDAYS] like 'Monday' OR
            [WEEKDAYS] like 'Tuesday' OR
            [WEEKDAYS] like 'Wednesday' OR
            [WEEKDAYS] like 'Thursday' OR
            [WEEKDAYS] like 'Friday' OR
            [WEEKDAYS] like 'Saturday' OR
            [WEEKDAYS] like 'Sunday'
            )
);

INSERT INTO [_Silk/Hour-Config] SELECT 'Rates', 1, 2, 3, 4, 5, NULL
INSERT INTO [_Silk/Hour-Config] SELECT 'Requirements', 0, (60*24*7), (60*24*30), (60*24*365), 0, NULL
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #1', 0, 0, 0, 0, 0, 'Monday'
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #2', 0, 0, 0, 0, 0, 'Tuesday'
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #3', 0, 0, 0, 0, 0, 'Wednesday'
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #4', 0, 0, 0, 0, 0, 'Thursday'
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #5', 0, 0, 0, 0, 0, 'Friday'
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #6', 0, 0, 0, 0, 0, 'Saturday'
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #7', 0, 0, 0, 0, 0, 'Sunday' 

USE SRO_VT_SHARDLOG

BEGIN TRY
    DROP TABLE _OnlineOffline
END TRY
BEGIN CATCH END CATCH;

CREATE TABLE _OnlineOffline
(
[No.] int PRIMARY KEY IDENTITY (1,1),
[CharID] int NOT NULL,
[Charname] varchar(64) NOT NULL,
[Status] varchar(20) NOT NULL,
[Date] datetime NOT NULL,
[Minutes] bigint,
[tMinutes] bigint,
[eSilk] int,
[mOnline] varchar(max) NULL,
[Silk/Hour] int NOT NULL,
[stillOnline@] datetime NULL
);

INSERT INTO _OnlineOffline 
    SELECT CharID, CharName16, 'OnHold', GETDATE(), 0, 0, 0, '0 minute(s)', (SELECT DefaultSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates'), NULL
    FROM SRO_VT_SHARD.dbo._Char ORDER BY CharID 

USE SRO_VT_SHARDLOG

BEGIN TRY
    DROP TABLE [_RandomPlayer&Date]
END TRY
BEGIN CATCH END CATCH;

CREATE TABLE [_RandomPlayer&Date] (
    [NextDate] datetime DEFAULT NULL,
    [RefreshedDate] datetime,
    [CharID] int,
    [Charname] varchar(64),
    [Desc] varchar(20) NULL
);

INSERT INTO [_RandomPlayer&Date] SELECT NULL,NULL,NULL,NULL,'Current'
INSERT INTO [_RandomPlayer&Date] SELECT NULL,NULL,NULL,NULL,'Next' 

USE SRO_VT_SHARD
GO

CREATE PROCEDURE _GetRandomTime /*by Caipi*/

@TodaysDate datetime,
@EndDate datetime OUTPUT

AS BEGIN

SET @EndDate = GETDATE();

WHILE (@EndDate <= GETDATE())
    BEGIN
        DECLARE 
                @BasicDate    varchar(12) = CONVERT(VARCHAR(10), @TodaysDate, 120),
                @Hour        varchar(2) = CONVERT(varchar(2),(ROUND(RAND()*23,0))),
                @Minutes    varchar(2) = CONVERT(varchar(2),(ROUND(RAND()*59,0))),
                @Seconds    varchar(2) = CONVERT(varchar(2),(ROUND(RAND()*59,0)))

        SET @EndDate = CONVERT(DATETIME,@BasicDate + ' ' + @Hour + ':' + @Minutes + ':' + @Seconds)
    END

END 

USE [SRO_VT_SHARD]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_RandomPlayerID] /*by Caipi*/

as

Declare @Random int = 1, @Bool bit = 0;

WHILE (@Bool = 0)
    BEGIN
        SET @Random = ROUND(RAND()*(SELECT MAX(CharID) FROM SRO_VT_SHARD.dbo._Char),0)
        IF exists (SELECT CharID FROM SRO_VT_SHARD.dbo._Char WHERE CharID = @Random)
            BEGIN
                SET @Bool = 1 /*true*/
            END
    END
    
return @Random 

USE [SRO_VT_ACCOUNT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_extraSilk] /*by Caipi*/

    @CharID int,
    @Silk int

as

Declare @JID int, @Multiplier int = (SELECT [Silk/Hour] FROM SRO_VT_SHARDLOG.dbo._OnlineOffline WHERE CharID = @CharID);

SET @JID = (
            SELECT usert.UserJID FROM SRO_VT_SHARD.dbo._User as usert
            JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
            WHERE chart.CharID = @CharID
            );
            
    IF not exists (SELECT JID FROM SK_Silk WHERE JID = @JID)
        BEGIN
            INSERT INTO SK_Silk SELECT @JID, 0, 0, 0
        END
            
    UPDATE SK_Silk
    SET silk_own = silk_own + (@Silk*@Multiplier)
    WHERE JID = @JID 

USE [USE SRO_VT_SHARDLOG]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_GetSilkperHour] /*by Caipi*/

@CharID int

as

Declare @totalMinutes bigint = (SELECT tMinutes FROM _OnlineOffline WHERE CharID = @CharID), @Silkoutput int;

/*Calculating the Silk/Hour-Amount related to the Online Time*/
SET @Silkoutput = 
CASE
    WHEN @totalMinutes >= (SELECT [Step3Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Requirements') 
        THEN (SELECT [Step3Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates')
    WHEN @totalMinutes >= (SELECT [Step2Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Requirements') 
        THEN (SELECT [Step2Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates')
    WHEN @totalMinutes >= (SELECT [Step1Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Requirements') 
        THEN (SELECT [Step1Silk] FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates')
    ELSE (SELECT DefaultSilk FROM [_Silk/Hour-Config] WHERE [Desc] like 'Rates')
END

RETURN @Silkoutput 

USE [SRO_VT_SHARDLOG]
GO

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

    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    
        insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc)    
    end
    else if (@len_pos > 0 and @len_desc = 0)
    begin     
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos)
    end
    else if (@len_pos = 0 and @len_desc > 0)
    begin     
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc)
    end
    else
    begin
        insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2)
    end
    
/*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] like DATENAME(WEEKDAY, GETDATE()))
                BEGIN
                    UPDATE _OnlineOffline
                    SET [eSilk] = [eSilk] + (@Silk*[Silk/Hour])
                    WHERE CharID = @CharID
                    
                    exec SRO_VT_ACCOUNT.dbo._extraSilk @CharID, @Silk
                END
            END
    /*!Calc*/
    
/*!Extension*/

END -- !Skipping 

IF you're not using the DB names like stated above, look through ALL these queries, there are plenty of paths you've got to change!

It's the bad of GlorySRO that I've created this one ;o

Enjoy.. :)

Uploaded .txt files -> ->Click<-

It's up to you to generate your own schedule by using the [_Silk/Hour-Config]-Table, the WEEKDAYS column can only contain NULL values and weekdays, nothing else.

[MentaL]

[jsqdcdh]

thx boy ,i like it

[NoNStop]

omg,again,you're awesome!

[capovip]

great like all time ( like ur work )

[nukertube]

Caipirinha Drink's :D
Thx

[tamer153]

thanks ! ur da best :))

everything u release is query then 14D will be by query's xD

btw can we modify it to :

earn 5 silks peer hour ?

[Maggo]

Extremly awesome work - as always.

[Caipi]

thanks ! ur da best :))

everything u release is query then 14D will be by query's xD

btw can we modify it to :

earn 5 silks peer hour ?

To set your own Silk amounts:

@ _GetSilkperHour -> 3 possibilities (on Default) - loook at the comments
@ _OnlineOffline -> UPDATE TABLE _OnlineOffline SET [Silk/Hour] = (your default Silk value of your choice)
@ _AddLogChar -> @ the first INSERT statement where the 'NULL,1,NULL ' is written, 1=Default Silk/Hour value
@ _RandomPlayer&Date -> UPDATE [_RandomPlayer&Date] SET [Silk/Hour] = (Your highest silk/hour amount as the reward)
--> done


I could also rewrite that you can choose your own rates easily - but actually I thought the rates I've choosen are the most efficient ones ^^

[SnapPop]

thanks Caipi you da best
Caipi i'm sorry i know i'm spamming but can you tell me about this freaking files i mean vsro files well there 2 tables at AccountDB (PrivilegedIP,SK_Gamebang_IP) they doesn't have any stored procedures while Black Rogue have.
how the hell they work anyway ? and lol when i try to add those missing stored procedures from Black Rogue, they fuck up something because server run no problem but when run client it stuck at loading even doesn't give error or DC lol nothing happens for 15 min i tried several time but failed i felt it came from there and when i used original AccountDB it runs normally
any hint about that bro ?

[Caipi]

You can also insert stuff into a DB with an outside application (If there aren't any SP's it doesn't mean that there's something wrong)

[hectormousa]

nice release man but what if i want this for just some time like for just week what i need to do how to make it work for week and then stop it

[Caipi]

nice release man but what if i want this for just some time like for just week what i need to do how to make it work for week and then stop it

Sure it is, simply wrap the "Extension" (besides the main if clause to skip over the unimportant records) into another if clause like..

IF (DATENAME(WEEKDAY, GETDATE()) like 'Saturday' OR DATENAME(WEEKDAY, GETDATE()) like 'Sunday')
BEGIN
..
...
....
END 

But you'd better make them log out (restart of the server) before and after the weekend starts/ends - otherwise it'll turn out into a mess

EDIT: Ima rewrite it later on that it matches all requirements - when I've got some time to do it

[tamer153]

To set your own Silk amounts:

@ _GetSilkperHour -> 3 possibilities (on Default) - loook at the comments
@ _OnlineOffline -> UPDATE TABLE _OnlineOffline SET [Silk/Hour] = (your default Silk value of your choice)
@ _AddLogChar -> @ the first INSERT statement where the 'NULL,1,NULL ' is written, 1=Default Silk/Hour value
@ _RandomPlayer&Date -> UPDATE [_RandomPlayer&Date] SET [Silk/Hour] = (Your highest silk/hour amount as the reward)
--> done


I could also rewrite that you can choose your own rates easily - but actually I thought the rates I've choosen are the most efficient ones ^^

ha :) thx anyway

[GloryAtomix]

yeah my fking bad to tell you this idea -.-
but fine atleast i did something to everyone here ..

[sladlejrhfpq]

yeah my fking bad to tell you this idea -.-
but fine atleast i did something to everyone here ..

nope, you didnt