[Release] AutoCashPoint new version with All Character fix get Regen Cash

[ROSLAW]

AutoCashPoint by TimePlay new version
All Character in one account will get regen cash even that character have Timeplay lower than other character.

I release it because I am not use this way again, I am already put this features to my Module protection.
so I release this way to public.
This version for fix issue about some character cannot have cashpoint regeneration because have lower timeplay than other character in one account.

Following This for Installation

1. Create new column on your tbl_base

USE [RF_World]

-- ================================================
-- Pembuat 			: <Hanry Roslaw Saputra>
-- Dibuat Tanggal	: <13 April 2014>
-- Keterangan		: <Trigger AutoCash Version 1.2>
-- ================================================

ALTER TABLE [dbo].[tbl_base]
ADD LogPlay INT NOT NULL DEFAULT ((0))

2. Create new procedure on your RF_World database

USE [RF_World]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Pembuat 			: <Hanry Roslaw Saputra>
-- Dibuat Tanggal	: <13 April 2014>
-- Keterangan		: <Prosedur AutoCash Version 1.2>
-- ================================================

CREATE PROCEDURE [dbo].[pUpdate_RoslawBase]
 @serial INT,
 @logPlay INT
AS
BEGIN

	SET NOCOUNT ON
	
	UPDATE [dbo].[tbl_base]
	SET LogPlay = @logPlay
	WHERE Serial = @serial
	
END
GO

3. Create new procedure on your BILLING Database

USE [Billing]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Pembuat 			: <Hanry Roslaw Saputra>
-- Dibuat Tanggal	: <13 April 2014>
-- Keterangan		: <Prosedur AutoCash Version 1.2>
-- ================================================

CREATE PROCEDURE [dbo].[RF_AutocashRoslaw]
 @id VARCHAR(17),
 @cash INT
AS
BEGIN

	SET NOCOUNT ON
	
	UPDATE [dbo].[tbl_UserStatus]
	SET Cash = (Cash + @cash)
	WHERE id = @id
	
END
GO

4. Create new Triger on your tbl_general

USE [RF_World]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Pembuat 			: <Hanry Roslaw Saputra>
-- Dibuat Tanggal	: <13 April 2014>
-- Keterangan		: <Trigger AutoCash Version 1.2>
-- ================================================

CREATE TRIGGER [dbo].[AutocashRoslaw]
	ON [dbo].[tbl_general]
	FOR UPDATE
AS 
BEGIN

	SET NOCOUNT ON
	
	DECLARE @GSerial INT
	DECLARE @GDCK BIT
	DECLARE @GPlay INT
	
	DECLARE @BSerial INT
	DECLARE @BDCK BIT
	DECLARE @BAccount VARCHAR(17)
	DECLARE @BPlay INT
	
	DECLARE @regencash INT
	
	IF UPDATE(TotalPlayMin)
	BEGIN
		SELECT @GSerial = Serial, @GDCK = DCK, @GPlay = TotalPlayMin FROM INSERTED
		IF (@GSerial > 0 AND @GDCK = 0)
		BEGIN
			SELECT @BSerial = Serial, @BDCK = DCK, @BAccount = Account, @BPlay = LogPlay FROM [dbo].[tbl_base] WHERE Serial = @GSerial
			IF (@BSerial > 0 AND @BDCK = 0)
			BEGIN
				SELECT @regencash = ((@GPlay-@BPlay)*60)
				IF  @regencash > 0)
				BEGIN
					EXEC [Billing].[dbo].[RF_AutocashRoslaw] @id = @BAccount, @cash = @regencash
					EXEC [RF_World].[dbo].[pUpdate_RoslawBase] @serial = @GSerial, @logPlay = @GPlay
				END	
			END
		END	
	END
	
END
GO

to set value regen per minute, just edit value "60" on triger query.
Example :

SELECT       @regencash = ((@GPlay-@BPlay)*60)

"60" is mean players will get 60 cashpoint every minute.

Author @ROSLAW

[MentaL]

[likertuban]

AutoCashPoint by TimePlay new version
All Character in one account will get regen cash even that character have Timeplay lower than other character.

hmmm,
i'm not quite understand about SQL, and trying to learn how that work,

what if i create my own trigger, based on you trigger,
and it look like this :

USE [RF_World]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[Autocash]
    ON [dbo].[tbl_general]
    FOR UPDATE
AS 
BEGIN


    SET NOCOUNT ON
    
    DECLARE @GSerial INT
    DECLARE @GDCK BIT
    
    DECLARE @BSerial INT
    DECLARE @BDCK BIT
    DECLARE @BAccount VARCHAR(17)
    
    IF UPDATE(TotalPlayMin)
    BEGIN
        SELECT @GSerial = Serial, @GDCK = DCK FROM INSERTED
        IF (@GSerial > 0 AND @GDCK = 0)
        BEGIN
            SELECT @BSerial = Serial, @BDCK = DCK, @BAccount = Account FROM [dbo].[tbl_base] WHERE Serial = @GSerial
            IF (@BSerial > 0 AND @BDCK = 0)
            BEGIN
                UPDATE [dbo].[tbl_UserStatus]
                SET Cash = Cash+60
                WHERE id = @BAccount
            END
        END    
    END
    
END
GO

do you think this trigger would work?
it give 60 cashpoint everytime TotalPlayMin updated, TotalPlayMin column is updated every 1 minutes right? o.O

SET Cash = Cash+60

and it give cash for everyminutes regardless of how much the totalplaymin, and doesn't matter if it's new character or old, am i wrong about this part?
or this query would have a bug in it?
cause i can't tested it yet
just want to hear your opinion, since i'm still learning myself...

[ROSLAW]

hmmm,
i'm not quite understand about SQL, and trying to learn how that work,

what if i create my own trigger, based on you trigger,
and it look like this :

USE [RF_World]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[Autocash]
    ON [dbo].[tbl_general]
    FOR UPDATE
AS 
BEGIN


    SET NOCOUNT ON
    
    DECLARE @GSerial INT
    DECLARE @GDCK BIT
    
    DECLARE @BSerial INT
    DECLARE @BDCK BIT
    DECLARE @BAccount VARCHAR(17)
    
    IF UPDATE(TotalPlayMin)
    BEGIN
        SELECT @GSerial = Serial, @GDCK = DCK FROM INSERTED
        IF (@GSerial > 0 AND @GDCK = 0)
        BEGIN
            SELECT @BSerial = Serial, @BDCK = DCK, @BAccount = Account FROM [dbo].[tbl_base] WHERE Serial = @GSerial
            IF (@BSerial > 0 AND @BDCK = 0)
            BEGIN
                UPDATE [dbo].[tbl_UserStatus]
                SET Cash = Cash+60
                WHERE id = @BAccount
            END
        END    
    END
    
END
GO

do you think this trigger would work?
it give 60 cashpoint everytime TotalPlayMin updated, TotalPlayMin column is updated every 1 minutes right? o.O

SET Cash = Cash+60

and it give cash for everyminutes regardless of how much the totalplaymin, and doesn't matter if it's new character or old, am i wrong about this part?
or this query would have a bug in it?
cause i can't tested it yet
just want to hear your opinion, since i'm still learning myself...

your query will work for each character. but not accurate with timeplay character. you just update they cash if SQL update timeplay database on tbl general.
Server will update all data from zoneserver to database SQL every 3 - 7 minutes it's depend of spec from PC or Zoneserver Mainthread.

so your query triggers just update 60 cashpoint every random minutes.

[Didaros]

help with this error I followed the orden post

Msg 102, Level 15, State 1, Procedure AutocashRoslaw, Line 36
Incorrect syntax near ')'.

[likertuban]

your query will work for each character. but not accurate with timeplay character. you just update they cash if SQL update timeplay database on tbl general.
Server will update all data from zoneserver to database SQL every 3 - 7 minutes it's depend of spec from PC or Zoneserver Mainthread.

so your query triggers just update 60 cashpoint every random minutes.

i think this simple trigger would solved it, check this out ^^7
this query check the old value of timeplay, and new value of timeplay, and set it as regentcash (and multiple it if they want ^^7) then set cash from that account to cash+regentcash
doesn't need to create new column for log, and doesn't need procedure either

USE [RF_World]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




    CREATE TRIGGER [dbo].[Autocash]
    ON [dbo].[tbl_general]
    FOR UPDATE
    AS 
    BEGIN


    SET NOCOUNT ON
    
    DECLARE @GSerial INT
    DECLARE @GDCK BIT
    DECLARE @GPlay INT
    
    DECLARE @BAccount VARCHAR(17)
    DECLARE @BPlay INT
    
    DECLARE @regencash INT
    
    IF UPDATE(TotalPlayMin)
    BEGIN
        SELECT @GDCK = a.DCK, @BAccount = b.Account, @GPlay = a.TotalPlayMin, @BPlay=g.TotalPlayMin  FROM INSERTED as a inner join tbl_base as b on b.Serial=a.Serial inner join DELETED as g on a.serial=g.serial
        IF (@GDCK = 0 and @GPlay > @BPlay)
        BEGIN
            set @regencash=(@GPlay-@BPlay)*60
            update Billing2232.dbo.tbl_UserStatus
            set cash = cash @regencash
            where id=@BAccount
            
        END
    END    
END
    


GO

i've been tested with dummy database, and seem it's works fine ^^7

[ROSLAW]

i think this simple trigger would solved it, check this out ^^7
this query check the old value of timeplay, and new value of timeplay, and set it as regentcash (and multiple it if they want ^^7) then set cash from that account to cash+regentcash
doesn't need to create new column for log, and doesn't need procedure either

USE [RF_World]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




    CREATE TRIGGER [dbo].[Autocash]
    ON [dbo].[tbl_general]
    FOR UPDATE
    AS 
    BEGIN


    SET NOCOUNT ON
    
    DECLARE @GSerial INT
    DECLARE @GDCK BIT
    DECLARE @GPlay INT
    
    DECLARE @BAccount VARCHAR(17)
    DECLARE @BPlay INT
    
    DECLARE @regencash INT
    
    IF UPDATE(TotalPlayMin)
    BEGIN
        SELECT @GDCK = a.DCK, @BAccount = b.Account, @GPlay = a.TotalPlayMin, @BPlay=g.TotalPlayMin  FROM INSERTED as a inner join tbl_base as b on b.Serial=a.Serial inner join DELETED as g on a.serial=g.serial
        IF (@GDCK = 0 and @GPlay > @BPlay)
        BEGIN
            set @regencash=(@GPlay-@BPlay)*60
            update Billing2232.dbo.tbl_UserStatus
            set cash = cash @regencash
            where id=@BAccount
            
        END
    END    
END
    


GO

i've been tested with dummy database, and seem it's works fine ^^7

yes that code will run for single process.
that query save inserted value and save deleted value on temporary and select it.
I know that bro ^^7, imagine if the process is multiple access and huge access for each minute on every update timeplay for all players, do you think that query can handle it ^^7, especially deleted value on temporary process. ^^7

I am making query/program is for everyone not just for me ^^7, the reason why I am not make a simple code like you is secret ^^7. you will know after you have your own server. and manage all players you have and get some result. ^^7.
try your code on there, and after a few weeks maybe you will know something ^^7.

[FeralXan]

AutoCashPoint by TimePlay new version
All Character in one account will get regen cash even that character have Timeplay lower than other character.




Following This for Installation

1. Create new column on your tbl_base

USE [RF_World]

-- ================================================
-- Pembuat             : <Hanry Roslaw Saputra>
-- Dibuat Tanggal    : <13 April 2014>
-- Keterangan        : <Trigger AutoCash Version 1.2>
-- ================================================

ALTER TABLE [dbo].[tbl_base]
ADD LogPlay INT NOT NULL DEFAULT ((0))

2. Create new procedure on your RF_World database

USE [RF_World]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Pembuat             : <Hanry Roslaw Saputra>
-- Dibuat Tanggal    : <13 April 2014>
-- Keterangan        : <Prosedur AutoCash Version 1.2>
-- ================================================

CREATE PROCEDURE [dbo].[pUpdate_RoslawBase]
 @serial INT,
 @logPlay INT
AS
BEGIN

    SET NOCOUNT ON
    
    UPDATE [dbo].[tbl_base]
    SET LogPlay = @logPlay
    WHERE Serial = @serial
    
END
GO

3. Create new procedure on your BILLING Database

USE [Billing]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Pembuat             : <Hanry Roslaw Saputra>
-- Dibuat Tanggal    : <13 April 2014>
-- Keterangan        : <Prosedur AutoCash Version 1.2>
-- ================================================

CREATE PROCEDURE [dbo].[RF_AutocashRoslaw]
 @id VARCHAR(17),
 @cash INT
AS
BEGIN

    SET NOCOUNT ON
    
    UPDATE [dbo].[tbl_UserStatus]
    SET Cash = (Cash + @cash)
    WHERE id = @id
    
END
GO

4. Create new Triger on your tbl_general

USE [RF_World]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Pembuat             : <Hanry Roslaw Saputra>
-- Dibuat Tanggal    : <13 April 2014>
-- Keterangan        : <Trigger AutoCash Version 1.2>
-- ================================================

CREATE TRIGGER [dbo].[AutocashRoslaw]
    ON [dbo].[tbl_general]
    FOR UPDATE
AS 
BEGIN

    SET NOCOUNT ON
    
    DECLARE @GSerial INT
    DECLARE @GDCK BIT
    DECLARE @GPlay INT
    
    DECLARE @BSerial INT
    DECLARE @BDCK BIT
    DECLARE @BAccount VARCHAR(17)
    DECLARE @BPlay INT
    
    DECLARE @regencash INT
    
    IF UPDATE(TotalPlayMin)
    BEGIN
        SELECT @GSerial = Serial, @GDCK = DCK, @GPlay = TotalPlayMin FROM INSERTED
        IF (@GSerial > 0 AND @GDCK = 0)
        BEGIN
            SELECT @BSerial = Serial, @BDCK = DCK, @BAccount = Account, @BPlay = LogPlay FROM [dbo].[tbl_base] WHERE Serial = @GSerial
            IF (@BSerial > 0 AND @BDCK = 0)
            BEGIN
                SELECT @regencash = ((@GPlay-@BPlay)*60)
                IF  @regencash > 0)
                BEGIN
                    EXEC [Billing].[dbo].[RF_AutocashRoslaw] @id = @BAccount, @cash = @regencash
                    EXEC [RF_World].[dbo].[pUpdate_RoslawBase] @serial = @GSerial, @logPlay = @GPlay
                END    
            END
        END    
    END
    
END
GO

Author @ROSLAW

Msg 102, Level 15, State 1, Procedure AutocashRoslaw, Line 36
Incorrect syntax near ')'.

[ROSLAW]

Check all query spell if you copy from Code Column. sometime query character changed because code column auto format form ragezone

[rfzeroadm]

Check all query spell if you copy from Code Column. sometime query character changed because code column auto format form ragezone

Sir.. Good day,

I have a question about your AutoCashPoint.
My server is 2.2.3

After I applied and run your querry the 4 querry my character keeps on DC;ing every 5mins.
WHY?

Please. explain and help me.

[ROSLAW]

Sir.. Good day,

I have a question about your AutoCashPoint.
My server is 2.2.3

After I applied and run your querry the 4 querry my character keeps on DC;ing every 5mins.
WHY?

Please. explain and help me.

This version is for RF Server 2.2.3.2 Golden Age.
But if you have good knowledge with SQL Query, you can make new trigger basicly from my trigger.

[rfzeroadm]

This version is for RF Server 2.2.3.2 Golden Age.
But if you have good knowledge with SQL Query, you can make new trigger basicly from my trigger.

I'm not good in SQL but I can' understand how sql works.

I though this was for 2.2.3 sorry.
I hope there will be a 2.2.3.

Thanks ROSLAW <- d'best ^_^

I was one of your fan. ^_^

[adhityariva]

If I Want to change at any time regen figure , whether the error occurred ? And whether Requires Restart zone ?

[felipelago]

Sir Roslaw i use all query and run, i see that creat new column in tbl_base (LogPlay), i log my char and see that got "LogPlay" but my cash still 0, but if i go in tbl_userstats in BILLING and set any value in column "Cash" works.... I tried make some changes in your query but no sucess.... have some clue for me sir ?
Thx a lot.

--Update---
I did another test, i added cash with this query in my character and only then could receive cash for time. I already increase column "Bonus" in my tbl and still not work.

 /****** Bacuy`s Area ******/
 /****** Script add premium, tanpa harus mendelete ******/
 /****** jika sudah ada pada table ******/

DECLARE @id VARCHAR(13)
DECLARE @status int
DECLARE   @Lama int
DECLARE   @cash int

/****** Your Name ******/
SET @id = 'bacuy'

/****** Set 2 for turn on Premium or Set 1 for turn off Premium  ******/
SET @status = '2'

/****** How long players get premium service (in days) exemple : 1 for 1 days, 2 for 2 days, 3 for 3days. ******/
SET   @Lama = '1'

/****** Ini gak perlu di isi, ini query khusus add premium aja ******/
SET   @cash = '0'

/************************************************************************************************************************************************/

declare   @count numeric(18, 0)
select   @count = COUNT(*) from [BILLING].[dbo].[tbl_UserStatus] where id = @id

if    @count = 0)
begin
	INSERT INTO [BILLING].[dbo].[tbl_UserStatus] 
	(
	[id],
	[Status],
	[DTStartPrem],
	[DTEndPrem],
	[Cash]
	)
	VALUES
	(
 @id,
	@status,
	CONVERT(datetime,GETDATE()),
	CONVERT(datetime,GETDATE()   @Lama),
   @cash
	)
end
else
begin
	update [BILLING].[dbo].[tbl_UserStatus]
	set [Status] = @status,
	[DTStartPrem] = CONVERT(datetime,GETDATE()),
	[DTEndPrem] = CONVERT(datetime,GETDATE()   @Lama),
	[Cash] =   @cash
	where id = @id
end

/***********************************************************************************************************************************************/

[ROSLAW]

PM me your access server. or add me on skype : roslaw.it
I will fix it for you. don't worry, that is free, I just want to know, what actually the problem it is.

[felipelago]

I add you in skype (i think i added you, appear many guys with "roslaw" ehehehe) i cant send your private message your box is full xD