-
[SQL] auto free Silk/Hour based on the Online time (System)
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
http://s14.directupload.net/images/120517/a35689sq.jpg
_RandomPlayer&Date
http://s14.directupload.net/images/120517/6zl68ssl.jpg
tbl_Silk/Hour-Config
http://s14.directupload.net/images/120518/tyx9ovwv.jpg
(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!
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.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
omg,again,you're awesome!
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
great like all time ( like ur work ) :thumbup:
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Caipirinha Drink's :D
Thx
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
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 ?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Extremly awesome work - as always.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
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 ?
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 ^^
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
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 ?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
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)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
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
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
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
Sure it is, simply wrap the "Extension" (besides the main if clause to skip over the unimportant records) into another if clause like..
PHP Code:
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
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Caipi
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
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
yeah my fking bad to tell you this idea -.-
but fine atleast i did something to everyone here ..
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
GloryAtomix
yeah my fking bad to tell you this idea -.-
but fine atleast i did something to everyone here ..
nope, you didnt
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
sladlejrhfpq
nope, you didnt
look at the end of topic
you will see this
"""It's the bad of GlorySRO that I've created this one ;o"""
i dont lie
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
#Realised the request, you can state your own schedule now + own rates at the new table
In case you are using the older version DROP everything and recreate it through the stated queries, I changed several parts
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
god.. that took forever to understand what you were trying to do with the random time + character thing... arrg!! and i still don't get it? how the hell does that work exactly..
also.. very nice :O
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Jangan
alter sro vt account -> table = _Insertnewchar? -> insert into the log char the information of the new character...
Hum? Dun get that, it adds everything automatically - you'd only need to create all those new tables and SP's and set your own rates at the config table -> that's it ^^
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Caipi
Hum? Dun get that, it adds everything automatically - you'd only need to create all those new tables and SP's and set your own rates at the config table -> that's it ^^
yeah i saw... btw you should use the CGI_WebPurchaseSilk instead of just insert into sk_silk :P since it will log in game for people so they have a reference or something xD
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Jangan
yeah i saw... btw you should use the CGI_WebPurchaseSilk instead of just insert into sk_silk :P since it will log in game for people so they have a reference or something xD
As soon as the Online time exceeds 59 minutes the silks will be added -> would turn out into a mess if you'd have a log of those ^^ they'll recognize it anyway
But feel free to edit it on the way you'd like to have it working
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
really love this lol... i will use this ^.^
Perfection Network
if anyone wants my ranking script pm me...
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Jangan
really love this lol... i will use this ^.^
Perfection Network
if anyone wants my ranking script pm me...
Glad to see that it's useful ^^
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
alot of error in sql 2005 :(
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Thank You Caipi For that But i cant Set silk hour and random player silk .. so can u Give us more Example Pls !!
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
guys this thing is dced u right ??
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
safty0202
guys this thing is dced u right ??
DC's doesn't make sense since it's based on a long procedure and doesn't return any specific value which could cause into dc'd
@ st0o0ry, what's the matter exactly? ^^ There's only one table where you can edit those rates, do it before creating the other tables/procedures
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
seriously?? you people run private servers and have 0 knowledge of SQL? thats sad and depressing...
for people saying they get errors...
replace:
Quote:
USE [USE SRO_VT_SHARDLOG]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_GetSilkperHour] /*by Caipi*/
@CharID int
with
Quote:
USE [SRO_VT_SHARDLOG]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_GetSilkperHour] /*by Caipi*/
@CharID int
..... if you dont know what im saying.. then dont host a private server thanks...
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
its make's High load iam right cuz my Pc restarting while the server is on
Btw my ram is 16 GP !!
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
When i start do this Query i got error !!
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
this error
Code:
Msg 156, Level 15, State 1, Procedure _GetRandomTime, Line 14
Incorrect syntax near the keyword 'CONVERT'.
Msg 137, Level 15, State 2, Procedure _GetRandomTime, Line 19
Must declare the scalar variable "@BasicDate".
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
modog1545
When i start do this Query i got error !!
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
this error
Code:
Msg 156, Level 15, State 1, Procedure _GetRandomTime, Line 14
Incorrect syntax near the keyword 'CONVERT'.
Msg 137, Level 15, State 2, Procedure _GetRandomTime, Line 19
Must declare the scalar variable "@BasicDate".
Same here :) Can you guys help? :) Thanks
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
You're using SQL'05,
you can't declare+set a variable at the same time there so simply..
PHP Code:
DECLARE @BasicDate varchar(12), @Hour varchar(2), @Minutes varchar(2), @Seconds varchar(2);
SET @BasicDate = CONVERT(VARCHAR(10), @TodaysDate, 120),
SET @Hour = CONVERT(varchar(2),(ROUND(RAND()*23,0))),
SET @Minutes = CONVERT(varchar(2),(ROUND(RAND()*59,0))),
SET @Seconds = CONVERT(varchar(2),(ROUND(RAND()*59,0)))
instead of..
PHP Code:
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)))
There cud be more incompatibilities!
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
nice m8:D keep it up :) i have it already running hahahah nice
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
How To Change Time Of Give Silk And Change Number silk fast Please :*:
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Can u pls share query's for SQL'05 ?
Please :(
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
If you'll keep on doing this I'll prolly regret releasing this one ;x
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
7 hours of reading but it works now perfectly! Thanks Caipi :)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Not Work With SQL 2005 :(
when i user SRO_VT_ACCOUNT
PHP Code:
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
Give me That
PHP Code:
Msg 102, Level 15, State 1, Procedure _extraSilk, Line 9
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure _extraSilk, Line 23
Must declare the scalar variable "@Multiplier".
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Caipi
You're using SQL'05,
you can't declare+set a variable at the same time
there it is
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
After using this cleanup script this auto silk stoped working.
It still count the minuntes and eSilk in the _onlineoffline table, but it does not load them to the sk_silk table.
Any idea what could have been changed so it stoped working?
Code:
USE [SRO_VT_SHARD]
GO
TRUNCATE TABLE [dbo].[_Chest]
TRUNCATE TABLE [dbo].[_ChestInfo]
TRUNCATE TABLE [dbo].[_User]
TRUNCATE TABLE [dbo].[_OpenMarket]
DELETE FROM [dbo].[_AccountJID]
TRUNCATE TABLE [dbo].[_GuildWar]
TRUNCATE TABLE [dbo].[_GuildMember]
TRUNCATE TABLE [dbo].[_GuildChest]
TRUNCATE TABLE [dbo].[_SiegeFortressStruct]
TRUNCATE TABLE [dbo].[_SiegeFortressObject]
TRUNCATE TABLE [dbo].[_SiegeFortressItemForge]
DELETE FROM [dbo].[_SiegeFortress]
INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (1)
INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (3)
INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (6)
UPDATE [dbo].[_AlliedClans] SET Ally1 = '0', Ally2 = '0', Ally3 = '0', Ally4 = '0', Ally5 = '0', Ally6 = '0', Ally7 = '0', Ally8 = '0'
DELETE FROM [dbo].[_Guild] WHERE ID > 0
DELETE FROM [dbo].[_AlliedClans] WHERE ID > 0
TRUNCATE TABLE [dbo].[_InventoryForAvatar]
TRUNCATE TABLE [dbo].[_TrainingCampSubMentorHonorPoint]
TRUNCATE TABLE [dbo].[_BlockedWhisperers]
TRUNCATE TABLE [dbo].[_TrainingCampMember]
TRUNCATE TABLE [dbo].[_CharTrijobSafeTrade]
DELETE FROM [dbo].[_CharTrijob] WHERE CharID > 0
TRUNCATE TABLE [dbo].[_TimedJob]
TRUNCATE TABLE [dbo].[_StaticAvatar]
TRUNCATE TABLE [dbo].[_Inventory]
TRUNCATE TABLE [dbo].[_Memo]
TRUNCATE TABLE [dbo].[_FleaMarketNetwork]
TRUNCATE TABLE [dbo].[_Friend]
TRUNCATE TABLE [dbo].[_CharSkillMastery]
TRUNCATE TABLE [dbo].[_CharSkill]
TRUNCATE TABLE [dbo].[_InvCOS]
DELETE FROM [dbo].[_CharCOS] WHERE ID > 0
DELETE FROM [dbo].[_Char] WHERE CharID > 0
TRUNCATE TABLE [dbo].[_CharCollectionBook]
TRUNCATE TABLE [dbo].[_CharInstanceWorldData]
TRUNCATE TABLE [dbo].[_CharNameList]
TRUNCATE TABLE [dbo].[_CharNickNameList]
TRUNCATE TABLE [dbo].[_CharQuest]
TRUNCATE TABLE [dbo].[_ClientConfig]
TRUNCATE TABLE [dbo].[_DeletedChar]
TRUNCATE TABLE [dbo].[_GPHistory]
TRUNCATE TABLE [dbo].[_InventoryForLinkedStorage]
TRUNCATE TABLE [dbo].[_ItemPool]
DELETE FROM [dbo].[_Items] WHERE ID64 > 0
TRUNCATE TABLE [dbo].[_Skill_BaoHiem_TNET]
TRUNCATE TABLE [dbo].[_TimedJobForPet]
TRUNCATE TABLE [dbo].[_TrainingCampBuffStatus]
TRUNCATE TABLE [dbo].[_TrainingCampHonorRank]
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (1)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (2)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (3)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (4)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (5)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (6)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (7)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (8)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (9)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (10)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (11)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (12)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (13)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (14)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (15)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (16)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (17)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (18)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (19)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (20)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (21)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (22)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (23)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (24)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (25)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (26)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (27)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (28)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (29)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (30)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (31)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (32)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (33)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (34)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (35)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (36)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (37)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (38)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (39)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (40)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (41)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (42)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (43)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (44)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (45)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (46)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (47)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (48)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (49)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (50)
EXEC [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
DELETE FROM [dbo].[_TrainingCamp]
TRUNCATE TABLE [dbo].[Tab_RefRanking_HunterContribution]
TRUNCATE TABLE [dbo].[Tab_RefRanking_RobberContribution]
TRUNCATE TABLE [dbo].[Tab_RefRanking_TraderContribution]
USE [Log_DB]
GO
TRUNCATE TABLE [dbo].[_LogCashItem]
TRUNCATE TABLE [dbo].[_LogEventChar]
TRUNCATE TABLE [dbo].[_LogEventItem]
TRUNCATE TABLE [dbo].[_LogEventSiegeFortress]
TRUNCATE TABLE [dbo].[_LogSchedule]
TRUNCATE TABLE [dbo].[_LogServerEvent]
USE [SRO_VT_ACCOUNT]
GO
TRUNCATE TABLE [dbo].[__SiegeFortressStatus__]
TRUNCATE TABLE [dbo].[_BlockedUser]
TRUNCATE TABLE [dbo].[_CasGMChatLog]
TRUNCATE TABLE [dbo].[_Notice]
TRUNCATE TABLE [dbo].[_Punishment]
TRUNCATE TABLE [dbo].[_ServiceManagerLog]
TRUNCATE TABLE [dbo].[_ShardCurrentUser]
TRUNCATE TABLE [dbo].[_SMCLog]
TRUNCATE TABLE [dbo].[QuaySoEpoint]
TRUNCATE TABLE [dbo].[SK_CharRenameLog]
TRUNCATE TABLE [dbo].[SK_DownLevelLog]
TRUNCATE TABLE [dbo].[SK_ITEM_GuardLog]
TRUNCATE TABLE [dbo].[SK_ItemSaleLog]
TRUNCATE TABLE [dbo].[SK_PackageItemSaleLog]
TRUNCATE TABLE [dbo].[SK_PK_UpdateLog]
TRUNCATE TABLE [dbo].[SK_ResetSkillLog]
TRUNCATE TABLE [dbo].[SK_Silk]
TRUNCATE TABLE [dbo].[SK_SilkBuyList]
TRUNCATE TABLE [dbo].[SK_SubtractSilk_VAS]
TRUNCATE TABLE [dbo].[SR_ShardCharNames]
TRUNCATE TABLE [dbo].[TB_Net2e]
TRUNCATE TABLE [dbo].[TB_Net2e_Bak]
TRUNCATE TABLE [dbo].[tb_partnerInfo]
TRUNCATE TABLE [dbo].[tb_paygate_trans]
TRUNCATE TABLE [dbo].[TB_User]
TRUNCATE TABLE [dbo].[TB_User_Bak]
TRUNCATE TABLE [dbo].[Test_HN]
UPDATE [dbo].[_ShardService] SET ShardID = '64'
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Thx Caipi
But How i can Change The Silk Value ?
i don't Understand your Reply about it i don't Found The Silk Value To Change>
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
VanSoR
Thx Caipi
But How i can Change The Silk Value ?
i don't Understand your Reply about it i don't Found The Silk Value To Change>
u can set it up on
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 <<<<<<<<<<<<<<<<<<<<<<<<<<<<< Setup your own lik 5,10 blblalba
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' also u can changes this crap to
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'
i have it here already working :D nice job anyway :D
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'
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Dear god the amount of stupidity in this thread is marvellous! Why do you people run private servers if you cant even read simple things >.>
Btw Caipi, i am using this on my server if you are interested in heavy loads of data and testing, i found few problems with the system, if you are interested i will let you on my server to play around for performance etc...
talk to you on skype ;)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Isn't everyone actually aiming on high loads, especially those who buy 96gig for 1 server :) leave me a message there
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Jangan
ok i want it can you upload it and tell me link or just send me link at private msg? thanks
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
ok my previous problem was solved.
iv got a question about this settings part:
if we are initiatning our rates here
Quote:
INSERT INTO [_Silk/Hour-Config] SELECT 'Rates', 1, 2, 3, 4, 5, NULL
why zeros are below ?(0,0,0,0,0)
Quote:
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'
is this for over-writing our rates and we can for example:
Quote:
INSERT INTO [_Silk/Hour-Config] SELECT 'allowed Day #7', 2, 4, 6, 8, 10, 'Sunday'
double our rates in sunday? is it working like this?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
hym1988
ok my previous problem was solved.
iv got a question about this settings part:
if we are initiatning our rates here
why zeros are below ?(0,0,0,0,0)
is this for over-writing our rates and we can for example:
double our rates in sunday? is it working like this?
Well as u can see
INSERT INTO [_Silk/Hour-Config] SELECT 'Rates', 1, 2, 3, 4, 5, NULL u can change that also
the 0,0,0,0,0 u can change to :) do that and execute in the sql :) its works fine :)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
but iam asking about this " 0,0,0,0,0"
what will happend if I will change it from zeros for example in sunday?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
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]
What should i put if i want 1silk gain per hours ??
thanks
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
if you want 1 silk per hour you need to set up like this
INSERT INTO [_Silk/Hour-Config] SELECT 'Rates', 1, 1, 1, 1, 1, NULL
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
djfisring
After using this cleanup script this auto silk stoped working.
It still count the minuntes and eSilk in the _onlineoffline table, but it does not load them to the sk_silk table.
Any idea what could have been changed so it stoped working?
Code:
USE [SRO_VT_SHARD]
GO
TRUNCATE TABLE [dbo].[_Chest]
TRUNCATE TABLE [dbo].[_ChestInfo]
TRUNCATE TABLE [dbo].[_User]
TRUNCATE TABLE [dbo].[_OpenMarket]
DELETE FROM [dbo].[_AccountJID]
TRUNCATE TABLE [dbo].[_GuildWar]
TRUNCATE TABLE [dbo].[_GuildMember]
TRUNCATE TABLE [dbo].[_GuildChest]
TRUNCATE TABLE [dbo].[_SiegeFortressStruct]
TRUNCATE TABLE [dbo].[_SiegeFortressObject]
TRUNCATE TABLE [dbo].[_SiegeFortressItemForge]
DELETE FROM [dbo].[_SiegeFortress]
INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (1)
INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (3)
INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (6)
UPDATE [dbo].[_AlliedClans] SET Ally1 = '0', Ally2 = '0', Ally3 = '0', Ally4 = '0', Ally5 = '0', Ally6 = '0', Ally7 = '0', Ally8 = '0'
DELETE FROM [dbo].[_Guild] WHERE ID > 0
DELETE FROM [dbo].[_AlliedClans] WHERE ID > 0
TRUNCATE TABLE [dbo].[_InventoryForAvatar]
TRUNCATE TABLE [dbo].[_TrainingCampSubMentorHonorPoint]
TRUNCATE TABLE [dbo].[_BlockedWhisperers]
TRUNCATE TABLE [dbo].[_TrainingCampMember]
TRUNCATE TABLE [dbo].[_CharTrijobSafeTrade]
DELETE FROM [dbo].[_CharTrijob] WHERE CharID > 0
TRUNCATE TABLE [dbo].[_TimedJob]
TRUNCATE TABLE [dbo].[_StaticAvatar]
TRUNCATE TABLE [dbo].[_Inventory]
TRUNCATE TABLE [dbo].[_Memo]
TRUNCATE TABLE [dbo].[_FleaMarketNetwork]
TRUNCATE TABLE [dbo].[_Friend]
TRUNCATE TABLE [dbo].[_CharSkillMastery]
TRUNCATE TABLE [dbo].[_CharSkill]
TRUNCATE TABLE [dbo].[_InvCOS]
DELETE FROM [dbo].[_CharCOS] WHERE ID > 0
DELETE FROM [dbo].[_Char] WHERE CharID > 0
TRUNCATE TABLE [dbo].[_CharCollectionBook]
TRUNCATE TABLE [dbo].[_CharInstanceWorldData]
TRUNCATE TABLE [dbo].[_CharNameList]
TRUNCATE TABLE [dbo].[_CharNickNameList]
TRUNCATE TABLE [dbo].[_CharQuest]
TRUNCATE TABLE [dbo].[_ClientConfig]
TRUNCATE TABLE [dbo].[_DeletedChar]
TRUNCATE TABLE [dbo].[_GPHistory]
TRUNCATE TABLE [dbo].[_InventoryForLinkedStorage]
TRUNCATE TABLE [dbo].[_ItemPool]
DELETE FROM [dbo].[_Items] WHERE ID64 > 0
TRUNCATE TABLE [dbo].[_Skill_BaoHiem_TNET]
TRUNCATE TABLE [dbo].[_TimedJobForPet]
TRUNCATE TABLE [dbo].[_TrainingCampBuffStatus]
TRUNCATE TABLE [dbo].[_TrainingCampHonorRank]
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (1)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (2)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (3)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (4)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (5)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (6)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (7)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (8)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (9)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (10)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (11)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (12)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (13)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (14)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (15)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (16)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (17)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (18)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (19)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (20)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (21)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (22)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (23)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (24)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (25)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (26)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (27)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (28)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (29)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (30)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (31)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (32)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (33)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (34)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (35)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (36)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (37)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (38)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (39)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (40)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (41)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (42)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (43)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (44)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (45)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (46)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (47)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (48)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (49)
INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (50)
EXEC [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
DELETE FROM [dbo].[_TrainingCamp]
TRUNCATE TABLE [dbo].[Tab_RefRanking_HunterContribution]
TRUNCATE TABLE [dbo].[Tab_RefRanking_RobberContribution]
TRUNCATE TABLE [dbo].[Tab_RefRanking_TraderContribution]
USE [Log_DB]
GO
TRUNCATE TABLE [dbo].[_LogCashItem]
TRUNCATE TABLE [dbo].[_LogEventChar]
TRUNCATE TABLE [dbo].[_LogEventItem]
TRUNCATE TABLE [dbo].[_LogEventSiegeFortress]
TRUNCATE TABLE [dbo].[_LogSchedule]
TRUNCATE TABLE [dbo].[_LogServerEvent]
USE [SRO_VT_ACCOUNT]
GO
TRUNCATE TABLE [dbo].[__SiegeFortressStatus__]
TRUNCATE TABLE [dbo].[_BlockedUser]
TRUNCATE TABLE [dbo].[_CasGMChatLog]
TRUNCATE TABLE [dbo].[_Notice]
TRUNCATE TABLE [dbo].[_Punishment]
TRUNCATE TABLE [dbo].[_ServiceManagerLog]
TRUNCATE TABLE [dbo].[_ShardCurrentUser]
TRUNCATE TABLE [dbo].[_SMCLog]
TRUNCATE TABLE [dbo].[QuaySoEpoint]
TRUNCATE TABLE [dbo].[SK_CharRenameLog]
TRUNCATE TABLE [dbo].[SK_DownLevelLog]
TRUNCATE TABLE [dbo].[SK_ITEM_GuardLog]
TRUNCATE TABLE [dbo].[SK_ItemSaleLog]
TRUNCATE TABLE [dbo].[SK_PackageItemSaleLog]
TRUNCATE TABLE [dbo].[SK_PK_UpdateLog]
TRUNCATE TABLE [dbo].[SK_ResetSkillLog]
TRUNCATE TABLE [dbo].[SK_Silk]
TRUNCATE TABLE [dbo].[SK_SilkBuyList]
TRUNCATE TABLE [dbo].[SK_SubtractSilk_VAS]
TRUNCATE TABLE [dbo].[SR_ShardCharNames]
TRUNCATE TABLE [dbo].[TB_Net2e]
TRUNCATE TABLE [dbo].[TB_Net2e_Bak]
TRUNCATE TABLE [dbo].[tb_partnerInfo]
TRUNCATE TABLE [dbo].[tb_paygate_trans]
TRUNCATE TABLE [dbo].[TB_User]
TRUNCATE TABLE [dbo].[TB_User_Bak]
TRUNCATE TABLE [dbo].[Test_HN]
UPDATE [dbo].[_ShardService] SET ShardID = '64'
Anyone knows what the problem is?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Thanks UseFul But Make a Guide how to change max silks giving
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Please please tel us how make it withe SQL 2005 :((((
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
i need create my own schedule ? or just exec the querys?
@edit nvm solved... ppl need relog for receive the silk.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
hello guys. i am really new with this sro system, but at my new job i need to do this, except, i should give 1silk/4hours.
and i do not need give silk for random players.
i've did set up the tables, and procedures, except randomplayer&date, and getrandomplayer, and modified the _AddLogChar procedure, and removed this:
Quote:
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
I have several questions:
- i doesn't get any documentation like a develepores guide, where can i download one? I tried to search without success.
- how can i change the time of extra silk from 1 hour to 4 hours?
- i'd like to test is, so i would like to change the time to some minutes, to test it on my test server. is it possible?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
can anyone make video turtial that's will help us very mush :)
Just Want to know
i need create my own schedule ? or just exec the querys?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
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
It's should exec ? or leave it ?
when i try to exec it i found proplems
what should i do in it
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Hello,
First thanks you very much for this release again :)
i have a little question!
I have do some try to make it works and he works properly for win 1 silk every hours,
now i wanted to change a little bit this and make 10 silks every 12h, 30 every 24h
so for this i have try to to add into tbl _Silk/hours-config
defaultsilk colunm
Rates 10 as
Requirements 720
but this is apparently don't works cause nothing happend after 12h
I also tryed to add it into
RewardSilk colunm
Rate 10
Requirements 720
Nothing happend here too
What wrong i do ?
Allowed day was all on 0 tryed whit 1 too
If someone can help me into this issue :)
Thanks you very much if ou read and help
regard
fabien
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
bah i installed this but never seem to get silk added
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Awesome :) works perfect om Ezma DB :)
Thanks bro :P
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Done everything according to the guide, but its not working. Am online with non gm and gm acc too since 10 hours, but 0 silk given for them, neither after relog. No idea why.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Hi
Pls help me, this query give 1silk/1hour, I would like to settings it, that 1 silk/3 hours, and the random player is not needed silk. How to need setting this? Ahead thank you for your help.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
isnt working for me , all querys added but noone get silk per hour.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
working fine now , but its only update the silks evry 24 h ;) but dosnt matter thanks for the script ^, ^
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
_Extrasilk isnt working with me gets an error with defination thingy
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
What i've to do with these?
Quote:
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
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
its a clue of whats means in the query
hmm guys
did any one knows the way to make a level requirement to start this system
example : creddy Loki making from 60+ this system work
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
All working fine =) Gained silk after relogging your char and it's easy to change the Silk per hour or the time when it gives more then 1 silk :)
I've change it to "Start => 3 Silk"
After 24H => 4Silk/h
after 7Days => 5 Silk/h
it's realy easy and working great :) thanks for all :)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Dragonzee
its a clue of whats means in the query
hmm guys
did any one knows the way to make a level requirement to start this system
example : creddy Loki making from 60+ this system work
@ _AddLogChar :)
from
PHP Code:
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
to »
PHP Code:
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 >= 60)
BEGIN
UPDATE _OnlineOffline
SET [eSilk] = ISNULL([eSilk],0) + (@Silk*[Silk/Hour])
WHERE CharID = @CharID
exec SRO_VT_ACCOUNT.dbo._extraSilk @CharID, @Silk
END
END
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
well thanks for help me caipi
but there a problem
if i executed the query as u wrote it will says
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 191
Incorrect syntax near 'END'.
and by tracing the problem if i deleted
PHP Code:
IF (@CurLevel >= 60)
BEGIN
UPDATE _OnlineOffline
if i deleted BEGIN it will success
if i put it its get that problem
well iam not good in SQL
but do u know any solution ?
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
errors :(
Msg 102, Level 15, State 1, Procedure _GetSilkperHour, Line 8
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure _GetSilkperHour, Line 8
Incorrect syntax near ','.
Msg 137, Level 15, State 2, Procedure _GetSilkperHour, Line 13
Must declare the scalar variable "@totalMinutes".
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 14
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 15
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 16
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 17
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 18
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 19
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 22
Incorrect syntax near the keyword 'RETURN'.
Msg 137, Level 15, State 2, Procedure _GetSilkperHour, Line 22
Must declare the scalar variable "@Silkoutput".
--------------------
Msg 156, Level 15, State 1, Procedure _GetRandomTime, Line 14
Incorrect syntax near the keyword 'CONVERT'.
Msg 137, Level 15, State 2, Procedure _GetRandomTime, Line 19
Must declare the scalar variable "@BasicDate".
only first 3 queries worked
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
carmenjason
errors :(
Msg 102, Level 15, State 1, Procedure _GetSilkperHour, Line 8
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure _GetSilkperHour, Line 8
Incorrect syntax near ','.
Msg 137, Level 15, State 2, Procedure _GetSilkperHour, Line 13
Must declare the scalar variable "@totalMinutes".
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 14
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 15
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 16
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 17
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 18
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 19
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure _GetSilkperHour, Line 22
Incorrect syntax near the keyword 'RETURN'.
Msg 137, Level 15, State 2, Procedure _GetSilkperHour, Line 22
Must declare the scalar variable "@Silkoutput".
--------------------
Msg 156, Level 15, State 1, Procedure _GetRandomTime, Line 14
Incorrect syntax near the keyword 'CONVERT'.
Msg 137, Level 15, State 2, Procedure _GetRandomTime, Line 19
Must declare the scalar variable "@BasicDate".
only first 3 queries worked
check those querys again and edit it with ur correct db names
and make sure u dont edited anything else
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Hey guys,
can anyone explain me how i can change the time where the silks added to char ?
it working well for me but the silks only added all 24h to the accounts. May someone
can help me thanks
best regards
anikan1985
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
I didn't change anything .. I'm using SQL Server 2005 btw!
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
This query
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
This Error
PHP Code:
Mens 208, Nivel 16, Estado 6, Procedimiento _AddLogChar, Línea 179
Invalid object name 'dbo._AddLogChar'.
help please :S
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
well you forgot to execute this part i guess
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
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
meowlicious
well you forgot to execute this part i guess
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
I run but I get the error
PHP Code:
Mens 208, Nivel 16, Estado 6, Procedimiento _AddLogChar, Línea 179
Invalid object name 'dbo._AddLogChar'.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
SHARDLOG = LOG DB ^^ Either you tried to execute it on the wrong database or you lack of an _AddLogChar-Procedure in your LOG DB, which would be quite uncommon
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
I change the SHARD LOG for LOG DB, and that error comes, I think it's because there AddLogChar, as you would?
Thanks you :thumbup1:
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Caipi you say i got this error becz i use sql05 .. now i use sql08 and i have same error ?!
PHP Code:
Msg 156, Level 15, State 1, Procedure _GetRandomTime, Line 14
Incorrect syntax near the keyword 'CONVERT'.
Msg 137, Level 15, State 2, Procedure _GetRandomTime, Line 19
Must declare the scalar variable "@BasicDate".
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
If You wanna really Shared your Experience, WHY DONT ADD QUERYS FOR SQL 2005 ?
So, u cant see much Friends Use SQL 2005 and Wanna Do ur work too...
Make sure do ur best , or do nothing !!!
Its my Opinion !!
Then, if possible, ADD QUERYS TO 2005 TOO !!
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
hellangel2
If You wanna really Shared your Experience, WHY DONT ADD QUERYS FOR SQL 2005 ?
So, u cant see much Friends Use SQL 2005 and Wanna Do ur work too...
Make sure do ur best , or do nothing !!!
Its my Opinion !!
Then, if possible, ADD QUERYS TO 2005 TOO !!
Cut the crap, it's supposed to work in MSSQL '08, I didn't check its compatibility for '05.
If you're not even able to change the declaration part of variables (You can't declare and set variables in '05 at once) then you better stop to work with these files.
-
1 Attachment(s)
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Caipi
Cut the crap, it's supposed to work in MSSQL '08, I didn't check its compatibility for '05.
If you're not even able to change the declaration part of variables (You can't declare and set variables in '05 at once) then you better stop to work with these files.
I know that Very Well my friend ..
SO IF U WAS ASK ABOUT TAKE
FOR SQL 2005:
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), @Hour varchar(2), @Minutes varchar(2), @Seconds varchar(2);
SET @EndDate = CONVERT(DATETIME,@BasicDate + ' ' + @Hour + ':' + @Minutes + ':' + @Seconds)
END
END
SO On..
Attachment 118244
THE QUEST IS:
This site is for ONE TEACH OTHERS, and no for ASK IF can't work with filles, to stop ...
IF YOU DONT HAVE PACIENCE TO TEACH.. BETTER DONT POST ...
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
hellangel2
I know that Very Well my friend ..
SO IF U WAS ASK ABOUT TAKE
FOR SQL 2005:
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), @Hour varchar(2), @Minutes varchar(2), @Seconds varchar(2);
SET @EndDate = CONVERT(DATETIME,@BasicDate + ' ' + @Hour + ':' + @Minutes + ':' + @Seconds)
END
END
SO On..
Attachment 118244
THE QUEST IS:
This site is for ONE TEACH OTHERS, and no for ASK IF can't work with filles, to stop ...
IF YOU DONT HAVE PACIENCE TO TEACH.. BETTER DONT POST ...
Stop flaming on Caipi MORON !
And forum rules rule 1
Quote:
- Always show respect towards each other. Flames and personal attacks are not welcome at RaGEZONE. This includes but not limited to; racism, xenophobia, etc.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Stop flaming on Caipi MORON !
And forum rules rule 1
Iam Don't Flaming, only do my opinion... ITS IREGULAR ? IF U ARE SO GOOD, THEN LOOK AHEAD ON TOPIC AND SEE MUCH REALLY FLAMING!!!
So on .. Iam Stop See TOPIC Right Now ....
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
hellangel2
Iam Don't Flaming, only do my opinion... ITS IREGULAR ? IF U ARE SO GOOD, THEN LOOK AHEAD ON TOPIC AND SEE MUCH REALLY FLAMING!!!
So on .. Iam Stop See TOPIC Right Now ....
Yo you're here in the Release section, it ain't a Guide one - such brainless people like you make me regret that I've even posted it. Get yo an english book and check what "release" essentially means. Dun complain just because you're a friggin lazy bum, you could've also simply downloaded mssql 2008 instead of bitching about the incompatibility of the queries in the '05 version.
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Quote:
Originally Posted by
Caipi
Yo you're here in the Release section, it ain't a Guide one - such brainless people like you make me regret that I've even posted it. Get yo an english book and check what "release" essentially means. Dun complain just because you're a friggin lazy bum, you could've also simply downloaded mssql 2008 instead of bitching about the incompatibility of the queries in the '05 version.
THIS IS A FLAMING :)
Alhead Use SQL 2008 R2 and Soon Update to 2012 ... But iam ask for others, no me !!!!
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
is not a silkroad vital procedure, is an add-on.. say thx caipi even release it
how to convert from ms sql 2008 to other version, please google about ms sql sites/guides.
in other hand, if i release a proc and i have sql 2008r2, i must install 2005, 2000, 2012, 1e25 version of sql to make the same proc over and over again?
dont like the proc? dont use it
the proc dont work? fix it
you tried to fix it and you cant? then ask for help... and so far i dont even see you trying
-
Re: [SQL] auto free Silk/Hour based on the Online time (System)
Thats right, why for me WORK, but i ask for others..
Iam Alhead THX CAIPI -- If he come in my house, i will do for he a CAIPIrinha.... From Brazil :)
