Working Item Mall Gift Box

[Fordmondeo20]

Hallo Leute,
ich verfolge eure Posts und Releases schon seit geraumer Zeit und dank eurer Hilfe konnte ich schon an einem Shaiya Server arbeiten, bei dem auch vieles funktioniert und besser aussieht
Ich bin jedoch an einer Hürde stecken geblieben - die Geschenkbox.
Mein Ziel ist es, ein Auto PvP Reward System einzufügen. Das ist mir bisher auch gelungen: Wenn Kill Nummer XXX erreicht wurde, bekomme ich Item Nummer XXX. Das wird entweder zu dem Bank Teller oder zu der Geschenkbox gesendet.
Allerdings aktualisieren sich beide Elemente ja erst nach einem Relog! Angenommen, ich schicke einem Spieler ein Item aus der Item Mall, schaut dieser ja auch in seine Geschenkbox, ohne zu reloggen und es wird sofort, also in Echtzeit, angezeigt.

Wie bekomme ich dies hin? Helfen mir Trigger? Wenn ja, wie?

Ein normales

UPDATE table ...

reicht wohl nicht aus.

Ich hoffe, jemand von euch Dev's hat eine Lösung.

Grüße.


[ENGLISH VERSION]
Heya Guys,
I follow your posts and releases already for a few months and cuz of your help I could configure a pretty well server where some is working and look better then "normal" pserver I think
However, I'm stuck on one thing - the gift box. My goal is to configure an auto pvp reward system what I also succeeded so far: If u have got kill number X you'll get Item N° X and it'll be sent either to the bank teller (my alternative) or the personal gift box.
Unfortunately both can't update in real time. For example you buy an item of the Item Mall and you send it to another player via gift box. The other player can see and reach the item instantly and he needn't to relog. But now in my situation on my server I still need to.

How can we fix the problem? Is a trigger helpful? If yes, how?

A normal

UPDATE table ...

is probably not enough.

I hope somebody of the dev's have or had got a solution.
Please tell me how to fix it. That would be really nice!

Best regards.

If you didn't really understand what I want from you please try to translate my mother language (german) into yours. Thank you.

[[Alcatraz]]

the problem as you said is that the database updates arent in real time your trying to add rewards by kills when you add your kill number for the rewards it Is sometimes jumped over when the data is updated in the database the best way I found is to add them by the KillLevel this is the level for when you goto the vet manager and get your rewards there the numbers are smaller and easier to manage you can use a trigger that would work but the better way is to code it into your procedures.

but even using the killlevel you can still run into the same problem with the updates jumping over a set number but using that is easier to manage then the k1

 Spoiler

USE [PS_GameData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create TRIGGER [dbo].[Rewards_Example]
ON [PS_GameData].[dbo].[Chars]
AFTER UPDATE
AS

IF (UPDATE (KillLevel))
BEGIN
DECLARE @OldKillLevel INT = (SELECT KillLevel FROM deleted)
DECLARE @NewKillLevel INT = (SELECT KillLevel FROM inserted)
DECLARE @CharID INT = (SELECT CharID FROM inserted)
DECLARE @UserUID INT = (SELECT UserUID FROM inserted)
DECLARE @Reward AS INT -- this will be the itemid of the reward
DECLARE @Slot TINYINT = 0
DECLARE @Empty TINYINT = -1

SET @Reward = CASE
WHEN @OldKillLevel = 0 AND @NewKillLevel = 1 THEN 100001 -- 100001 is the itemid of etain potion
WHEN @OldKillLevel = 1 AND @NewKillLevel = 2 THEN 100001
WHEN @OldKillLevel = 2 AND @NewKillLevel = 3 THEN 100001
END

WHILE (@Slot <= 239)
BEGIN

SET @empty = (SELECT COUNT(Slot) FROM PS_Billing.dbo.Users_Product WHERE UserUID = @UserUID AND Slot = @Slot)

IF (@empty <= 0) BREAK
ELSE
SET @Slot = @Slot+1
END

INSERT INTO PS_Billing.dbo.Users_Product
(UserUID, Slot, ItemID, ItemCount, ProductCode, OrderNumber, BuyDate)
VALUES (@UserUID, @Slot, @Reward, 1, '',1, GETDATE())
END

but for this to work you need to make sure the killlevel column is real time updates I cant remember if it is or not. if its not then there is another way of doing this where it will always work but requires another table being added with some extra coding

but using the killlevel column instead of the kills allows for a range grouping of kills instead of a specific kill amount its less likely to be jumped over for better results using this try not giving rewards for the first 3 or 4 ranks

[_Diavolino_]

i think there is a problem the fact to use Level of kill, because if you dont make a table that make sure that you receive the reward ALREADY, a player will just need to reset stat in Cycle to touche Non Stop the reward...

i think to make a table using this process rest good solution...
if the player reach the levelkill... so Check the table if Toon id Reward = 0 Send Gift and if Reward = 1 No Gift or an Apple or Banana !

Or if not using the kill but will need to relog to get the reward and same after reset Stat the reward Cant be again effective but it is not in real time.

kind Regards

[[Alcatraz]]

yes I think that is right I haven't used a pvp rewards system but I created a leveling rewards system for a friend but yes I do believe your right there will need to be a new table created that will store some info so people cant use a restat stone to get the rewards over and over. good catch now its really got my interest lets see what I can come up with

[Fordmondeo20]

Thank you for your replies!

Originally Posted by Alcatraz

I created a leveling rewards system for a friend

Anywhere I read that the leveling procedure is in real time or I think there is the real time solution possible. If yes can I check the K1 or the kill level while I'm "leveling" although I'm not? Do you know what I mean?

EDIT: Or can I manipulate the procedures which read the entries in the slots of bank teller or the gift box again when I open them?

EDIT2: The Server "Shai**t*pia" has the working real time script about the gift box. Players can send other players gifts of the mall there. Hm.

[[Alcatraz]]

Originally Posted by Fordmondeo20

Thank you for your replies!

Anywhere I read that the leveling procedure is in real time or I think there is the real time solution possible. If yes can I check the K1 or the kill level while I'm "leveling" although I'm not? Do you know what I mean?

Most of all info of the game is stored in memory and it only added to the database when a player logout or the ps_game.exe does its 15 min updates for the database leveling is one of those although there is a way I found to track the leveling In real time all you have to do is write some codes to look for @ActionType = 146 that's the leveling actiontype if im not mistaken and thinking of that you might be able to do the kills the same way if im not mistaken anytime a player makes a kill its shown in real time in the actionlogs I think

and im not quite sure what you mean about the gift boxes and bank teller slots

[Fordmondeo20]

yeah I meant with the slots that if a special ActionType is executed which send items to bank teller or gift box and when I open one of these boxes where the item was sent to should it be there without logout you know?

[[Alcatraz]]

well no matter what youll have to logout for any gift item sent using any means other then the gift button on the ingame item mall because all other actions are coding written outside the game the only reason the gift button works automatically is because it programmed into the game.exe and ps_game.exe

the only other way I can think would be to find someone with some really good Cheat engine skills to do it but my best guess it would be a lot of working and it wouldn't come free.

by the way what type of items are you trying to send out for pvp rewards ?

[Fordmondeo20]

Originally Posted by [Alcatraz]

by the way what type of items are you trying to send out for pvp rewards ?

You can goal better gear items if you got enough kills for each item.
With almost every pvp rank up you'll get for instance a better helmet or better gaunlets and so on till Rank Metatron (purple star).

The reason why I want to solve the problem is that the new Item will get the stats of the old item. If you got some new and better gaunlets you would have the same stats (Craftname) on the new Item. However, the game update in the database the new stats when you take it off the gift box but not in the game. So you need to relog 2 times to get the new Items with the old stats and that sucks I think

1st relog to update the bank teller or the gift box
2nd relog to update the stats

[[Alcatraz]]

you know you can bypass the bank teller all together by just having the added coding change the type, typeid, and itemid of the item your wanting to upgrade the players wouldn't get a extra item but it would take the current item and upgrade it

just a thought for you

and all links, enchants, and ojs would be the same as the old item from before

[Fordmondeo20]

Okay and how I get it?

Shell I just add new columns because u have only this.

PHP Code:

INSERT INTO PS_Billing.dbo.Users_Product (UserUID, Slot, ItemID, ItemCount, ProductCode, OrderNumber, BuyDate) 


So and when u save the item while u put it in ur inventory the DB get the info..

 Spoiler

PHP Code:

USE [PS_GameData]
GO
/****** Object:  StoredProcedure [dbo].[usp_Save_Char_Product_Item_E]    Script Date: 18.08.2015 13:13:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- ************************************************************** --
--
-- Bank FIX by [Dev]HoaX 2011
-- ************************************************************** --

ALTER Proc [dbo].[usp_Save_Char_Product_Item_E]

@ServerID tinyint,
@UserUID int,
@BankSlot tinyint,

@CharID int,
@ItemUID bigint,
@Bag tinyint,
@Slot tinyint,
@ItemID int,
@Type tinyint,
@TypeID tinyint,
@Quality smallint,    --int
@Gem1 tinyint,
@Gem2 tinyint,
@Gem3 tinyint,
@Gem4 tinyint,
@Gem5 tinyint,
@Gem6 tinyint,
@Craftname varchar(20) = '', 
@Count tinyint,
@Maketime datetime,
@Maketype varchar(1)

AS

SET XACT_ABORT ON

DECLARE @ProductCode varchar(20)
DECLARE @OrderNumber int
DECLARE @VerifyCode bigint
DECLARE @BuyDate datetime

IF(@Quality >= 5000)
BEGIN
    SET @Quality=0
END

BEGIN DISTRIBUTED TRANSACTION

-- and so on... 


[[Alcatraz]]

im assuming you have all the same in the table for the bank im not sure I only have the normal columns that was needed and I didn't use hoax bank fix on mine

[Fordmondeo20]

okay i just try a bit and then I'll come back to tell you

Thanks a lot!

Okay I'm quite done.

Now I added the columns named down below in the PS_Billing.dbo.Users_Product. The PvP Reward Procedure write the current stats like the Gem's and OJ's in this table but after a relog in the bank teller there is the item without the current stats. So I have to take it in my inventory, relog again and then they are. I had updated all procedures which handle with the table but I think in this one is the mistake.

 Spoiler

PHP Code:

USE [PS_GameData]
GO
/****** Object:  StoredProcedure [dbo].[usp_Read_Char_Product_Item_E]    Script Date: 18.08.2015 17:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Proc [dbo].[usp_Read_Char_Product_Item_E]

@UserUID int

AS

SELECT Slot,ItemID,ItemCount,[Type],TypeID,Quality,Gem1,Gem2,Gem3,Gem4, Gem5,Gem6,CraftName FROM PS_Billing.dbo.Users_Product WHERE UserUID=@UserUID 


I guess the game don't read the bank teller correctly enough. I just don't know how to continue...

[[Alcatraz]]

ok im not sure what your trying to do here but ill see if I can point you in the right directions I know you want to do pvp rewards first things first you need to figure out what type of rewards you plan to give out if its only going to be gear and weapons like you said then good next do you want to give a new item as a reward or upgrade the old items the player has already. if you plan to give out new items then youll need to use the bank teller it would be the easier way if you only want to upgrade the old items and turn them into the new items like I suggested then you don't need the bank teller at all

using the bank: the bank a log will be required to see the items in the bank teller after completing the rewards requirements

upgrading the items: no bank teller is need a simple code would be made to change the type typeid and itemid of a item in a specific slot and bag doing this would change the basic stats of the item if they was different from the item before but all enchants, links, and ojs would stay the same as it was on the first item it was.

figure out how you want to do it and ill try to help build the codes for it so it can be done.

[Fordmondeo20]

ahm okay..

I made new Items youll get then. I have a kind of way I can solve this. If I need help again then I ask you

Dear Alcatraz, thank you so much anyway that you tried to help me!