![[Release]Auto free Silk/Hour based on the Online time (System)](https://www.elitepvpers.com/forum/iconimages/sro-pserver-guides-releases/release-auto-free-silk-hour-based-online-time-system_ltr.gif){kind=small}
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..
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
Queries to be executed..
PHP 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'
PHP 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
PHP 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'
PHP 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
PHP 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
PHP 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
PHP 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
PHP 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
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!
The Work Made By Caipi
And The Idea Made By Glory Silkroad (rushcrush and kofta)
And The Idea Made By Glory Silkroad (rushcrush and kofta)
Enjoy..
Uploaded .txt files ->
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.
