![[SQL] Silk per hour based on Online time(System)](https://www.elitepvpers.com/forum/iconimages/sro-pserver-guides-releases/sql-silk-per-hour-based-online-time-system_ltr.gif){kind=small}
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.
Screenshots of the new tables
_OnlineOffline
_RandomPlayer&Date
tbl_silk/hour-config
(Requirements - minutes play time)
How to?
Code:
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
Code:
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'
Code:
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
Code:
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'
Code:
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
Code:
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
Code:
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
Code:
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
Code:
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
And you're done, but remember, if you're not using the same DB names, make sure you change it!
Text files
