Ecoin on DG

[RGaming]

anyone can help me how to add ecoins automatically add to thier account after finishing the dungeon

 

[Ocid Borlan]

cabal_sp_DungeonRanking
Add
IF (@DungeonIdx = ?) -- If you want to add only one dungeon
BEGIN
Update DB..dbo.CashAccount set Cash = Cash + ? where UserNum = charidx/8
END

good luck

 

[RGaming]

ill check it thank you

cabal_sp_DungeonRanking
Add
IF (@DungeonIdx = ?) -- If you want to add only one dungeon
BEGIN
Update DB..dbo.CashAccount set Cash = Cash + ? where UserNum = charidx/8
END

good luck

how about if i want to add it in all dungeon

 

[Ocid Borlan]

ill check it thank you





how about if i want to add it in all dungeon

IF (@DungeonIdx = ?) < DELETE
BEGIN < DELETE
Update DB..dbo.CashAccount set Cash = Cash + ? where UserNum = charidx/8
END < DELETE

 

[RGaming]

is this the right sP ? cabal_sp_DungeonRankingSingle_Get?

 

[RGaming]

up any can tell where i can find the store procedure for this

 

[Ocid Borlan]

up any can tell where i can find the store procedure for this

cabal_sp_DungeonRanking_Single_Clear_Set

 

[RGaming]

cabal_sp_DungeonRanking_Single_Clear_Set

thank you ill check it now



hello ive tried to add it show this error

Invalid column name 'charidx'.

 

[HariSeldon]

For cabal_sp_DungeonRanking_Single_Clear_Set

Update CabalCash.dbo.CashAccount set Cash = Cash + ? where UserNum = @[I][B][URL="http://forum.ragezone.com/members/551894.html"]Char[/URL][/B][/I]Idx/8

There will be something like this for the party:
cabal_sp_DungeonRanking_Party_Clear_Set

        DECLARE @CNT int, @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id int
        SET @CNT = 1

        WHILE @CNT < DATALENGTH ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData )
        BEGIN
                SET @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id = dbo.BinToInt(SUBSTRING ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData,@CNT,4 ))
                Update CabalCash.dbo.CashAccount set Cash = Cash + ? where UserNum = @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id/8
                SET @CNT= @CNT+27
        END

 

[RGaming]

hello sir already tried your code and it work but only one time after you finish the dungeon you cannot get ecoin anymore

 

[Tryout]

For cabal_sp_DungeonRanking_Single_Clear_Set

Update CabalCash.dbo.CashAccount set Cash = Cash + ? where UserNum = @[I][B][URL="http://forum.ragezone.com/members/551894.html"]Char[/URL][/B][/I]Idx/8

There will be something like this for the party:
cabal_sp_DungeonRanking_Party_Clear_Set

        DECLARE @CNT int, @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id int
        SET @CNT = 1

        WHILE @CNT < DATALENGTH ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData )
        BEGIN
                SET @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id = dbo.BinToInt(SUBSTRING ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData,@CNT,4 ))
                Update CabalCash.dbo.CashAccount set Cash = Cash + ? where UserNum = @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id/8
                SET @CNT= @CNT+27
        END

How about query for the party with specific dungeon (If I only want to do it with one dungeon)?



Is this correct?

DECLARE @CNT int, [USER=2000256504]mem[/USER]Id int
        SET @CNT = 1

        WHILE @CNT < DATALENGTH ( [USER=2000256504]mem[/USER]berData )
        IF (@DungeonIdx = ?)
BEGIN
             SET [USER=2000256504]mem[/USER]Id = dbo.BinToInt(SUBSTRING ( [USER=2000256504]mem[/USER]berData,@CNT,4 ))
             Update CabalCash.dbo.CashAccount set Cash = Cash + ? where UserNum = [USER=2000256504]mem[/USER]Id/8
             SET @CNT= @CNT+27
        END

 

[HariSeldon]

How about query for the party with specific dungeon (If I only want to do it with one dungeon)?



Is this correct?

DECLARE @CNT int, @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id int
        SET @CNT = 1

        WHILE @CNT < DATALENGTH ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData )
        IF  @[I][B][URL="http://forum.ragezone.com/members/2000255614.html"]dung[/URL][/B][/I]eonIdx = ?)
BEGIN
             SET @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id = dbo.BinToInt(SUBSTRING ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData,@CNT,4 ))
             Update CabalCash.dbo.CashAccount set Cash = Cash + ? where UserNum = @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id/8
             SET @CNT= @CNT+27
        END

DECLARE @CNT int, @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id int, @reward int
SET @reward = 
    CASE
        WHEN @[I][B][URL="http://forum.ragezone.com/members/2000255614.html"]dung[/URL][/B][/I]eonIdx = 4252 THEN 100 --100 eCoins for DungeonIdx=4252
        WHEN @[I][B][URL="http://forum.ragezone.com/members/2000255614.html"]dung[/URL][/B][/I]eonIdx = 4253 THEN 200 --200 eCoins for DungeonIdx=4253
        WHEN @[I][B][URL="http://forum.ragezone.com/members/2000255614.html"]dung[/URL][/B][/I]eonIdx = 4254 THEN 300 --300 eCoins for DungeonIdx=4254
        ELSE 0 --Default - 0 eCoins
    END
IF (@reward>0)
BEGIN
    SET @CNT = 1
    WHILE @CNT < DATALENGTH ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData )
    BEGIN
        SET @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id = dbo.BinToInt(SUBSTRING ( @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]berData,@CNT,4 ))
        Update CabalCash.dbo.CashAccount set Cash = Cash + @reward where UserNum = @[I][B][URL="http://forum.ragezone.com/members/2000256504.html"]mem[/URL][/B][/I]Id/8
        SET @CNT= @CNT+27
    END
END

 

[Tryout]

ELSE 0 --Default - 0 eCoins <<<< what is this for? if dungeon is not finished doesn't give ecoin or do I need to remove this so the function above will work?

 

[HariSeldon]

ELSE 0 --Default - 0 eCoins <<<< what is this for? if dungeon is not finished doesn't give ecoin or do I need to remove this so the function above will work?

If a dungeon is not on your list, no reward will be issued. For example, you want to reward for finishing B3F, but do not want to reward for finishing SoD. If the SoD ID is not in the list, the default setting (0 eCoins) will work.

 

[ronsamrn]

Other method would be creating a table for Dungeon with corresponding ecoin reward and use select statement. its easier to update table values when you decided to change ecoin rewards for each dungeon.

USE [Server01]
GO

/****** Object:  Table [dbo].[cabal_DungeonEcoin_Table]    Script Date: 10/11/2020 8:13:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[cabal_DungeonEcoin_Table](
	[DungeonIdx] [int] NOT NULL,
	[ecoin] [int] NOT NULL,
 CONSTRAINT [PK_cabal_DungeonEcoin_Table] PRIMARY KEY CLUSTERED 
(
	[DungeonIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

DECLARE [B]@ecoin[/B] int    
        
    SELECT [B]@ecoin[/B] = ecoin
    FROM cabal_DungeonEcoin_Table
    WHERE DungeonIdx = [USER=2000255614]dung[/USER]eonIdx
        
    SET @CNT = 1
    WHILE @CNT < DATALENGTH ( [USER=2000256504]mem[/USER]berData )
        BEGIN
            SET [USER=2000256504]mem[/USER]Id = dbo.BinToInt(SUBSTRING ( [USER=2000256504]mem[/USER]berData,@CNT,4 ))
            UPDATE CabalCash.dbo.CashAccount set Cash = Cash + [B]@ecoin[/B] where UserNum = [USER=2000256504]mem[/USER]Id/8
            SET @CNT= @CNT+27
        END

 

[Tryout]

Other method would be creating a table for Dungeon with corresponding ecoin reward and use select statement. its easier to update table values when you decided to change ecoin rewards for each dungeon.

DECLARE @ecoin int

SELECT @ecoin = ecoin
FROM cabal_DungeonEcoin_Table
WHERE DungeonIdx = 194<<< is correct?

SET @CNT = 1
WHILE @CNT < DATALENGTH ( memberData )
BEGIN
SET memId = dbo.BinToInt(SUBSTRING ( memberData,@CNT,4 ))
UPDATE CabalCash.dbo.CashAccount set Cash = Cash + @ecoin where UserNum = memId/8
SET @CNT= @CNT+27
END

 

[ronsamrn]

DECLARE @ecoin int

SELECT @ecoin = ecoin
FROM cabal_DungeonEcoin_Table
WHERE DungeonIdx = 194<<< is correct?

SET @CNT = 1
WHILE @CNT < DATALENGTH ( @memberData )
BEGIN
SET @memId = dbo.BinToInt(SUBSTRING ( @memberData,@CNT,4 ))
UPDATE CabalCash.dbo.CashAccount set Cash = Cash + @ecoin where UserNum = @memId/8
SET @CNT= @CNT+27
END

Create a new table with dungeonid and ecoin value for that code to work.

USE [Server01]
GO

/****** Object:  Table [dbo].[cabal_DungeonEcoin_Table]    Script Date: 10/11/2020 8:13:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[cabal_DungeonEcoin_Table](
    [DungeonIdx] [int] NOT NULL,
    [ecoin] [int] NOT NULL,
 CONSTRAINT [PK_cabal_DungeonEcoin_Table] PRIMARY KEY CLUSTERED 
(
    [DungeonIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

sample table content

DungeonIdx ecoin 293 50 4353 150

293 is for Volcanic Citadel
4353 id for Ancient Tomb

I hope you get the idea.

 

[Tryout]

Create a new table with dungeonid and ecoin value for that code to work.

USE [Server01]
GO

/****** Object:  Table [dbo].[cabal_DungeonEcoin_Table]    Script Date: 10/11/2020 8:13:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[cabal_DungeonEcoin_Table](
    [DungeonIdx] [int] NOT NULL,
    [ecoin] [int] NOT NULL,
 CONSTRAINT [PK_cabal_DungeonEcoin_Table] PRIMARY KEY CLUSTERED 
(
    [DungeonIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

sample table content

DungeonIdx ecoin 293 50 4353 150

293 is for Volcanic Citadel
4353 id for Ancient Tomb

I hope you get the idea.

I get it, thanks. Is this working both Party and Solo DG?

 

[ronsamrn]

I get it, thanks. Is this working both Party and Solo DG?

yes. just add select query after variable declaration.

 

[Tryout]

I got this error after executing first query, I'm using sql2008 r2

Msg 155, Level 15, State 1, Line 8'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option.