Simple SQL query to check Max Plus of items (Real time or login/logout)

[Witchy Moo]

Hello,

Here I'll share simple SQL query to check and update "Max Plus" (with and without Adv. Elixir) of items in 2 different methods: (doesn't matter which, both has same effect)

Method 1: Real time after fusing
Method 2: On login/logout session

[STRIKE]Why login/logout session? well as far as I tried, changing item plus from database trigger or [_AddLogItem] query without refreshing the client will cause misinformation between client and server. And might cause the client dc or crash (no idea which), because of wrong information send to/received from the server. (CMIIW)

For example, someone did +13 and you force real time query back to +12. The database already have "+12", but the client still reads "+13"... then the player fused again and succeeded the 2nd time (this time client reads +14 while server reads +12), and they start hunting/hitting/killing whatever without teleporting... not sure what will happen :P
[/STRIKE]
(Update: as someone confirmed, nothing will happen, so I added a "real time" max plus check during equipment fusing, even though the client has to teleport to refresh the visual side)

Update notes:

• This will work and might be useful if you run your server without additional 3rd party filter. But you won't need this if using filter such as K-Guard, SUPERMAN, Galullians or others, since those already have a feature for plus limit. [IMG]http://www.***********.com/forum/images/smilies/smile.gif[/IMG]
• If you use Method 1, you won't need to use Method 2 because Method 1 already update the "ItemID" itself. So it won't matter where the player put the item after fusing :)

Now the boring part: (Feel free to develop the query as you see fit)

Method 1. "Real time" check and update right after fusing. (Client needs to teleport to refresh visual side)

Procedure: [SRO_VT_LOG].[dbo].[_AddLogItem]

Spoiler:

/* Item upgrading */
IF  @Operation = 90 OR @Operation = 160)
BEGIN
    /* Start: Max Plus */
    DECLARE @strDescStrip VARCHAR(128)
    DECLARE @ItemID INT

    DECLARE @MaxOptLevel TINYINT
    SET @MaxOptLevel = 12

    /* Get current fused plus */
    SET @Len_desc1 = CHARINDEX (']', @strDesc)
    IF  @Len_desc1 = 9)
    BEGIN
        SET @strDesc = SUBSTRING(@strDesc, 0, 9)
        SET @strDescStrip = SUBSTRING(@strDesc, 8, 1)
    END
    ELSE IF  @Len_desc1 = 10)
    BEGIN
        SET @strDesc = SUBSTRING(@strDesc, 0, 10)
        SET @strDescStrip = SUBSTRING(@strDesc, 8, 2)
    END
    /* Check if OptLevel above @MaxOptLevel */
    IF (@strDescStrip >= @MaxOptLevel)
    BEGIN
        /* Get ItemID */
        SELECT @ItemID = ID64 from [SRO_VT_SHARD].[dbo].[_Items] WHERE Serial64 = @ItemSerial
        /* Update items if has more than @MaxOptLevel without Adv elixir*/
        UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND Serial64 = @ItemSerial
        /* Delete Adv elixir effect for item if has more than @MaxOptLevel with Adv elixir */
        DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
            SELECT a.ID64 FROM [SRO_VT_SHARD].[dbo].[_Items] a WITH (NOLOCK)
            INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] b ON a.ID64 = b.nItemDBID
            WHERE a.ID64 = @ItemID AND (a.OptLevel + b.nOptValue) > @MaxOptLevel
            AND b.bOptType = 2
        )
    END
    RETURN -1
    /* End: Max Plus */
END

Method 2. Check and update during login/logout session for Inventory, Storage, and Guild Storage

Procedure: [SRO_VT_LOG].[dbo].[_AddLogChar]

Spoiler:

/* Login/Logout sequence (4 = Login, 6 = Logout) */
IF  @EventID = 4 OR @EventID = 6)
BEGIN
    /*
     * Begin: Item Plus check
     */
    DECLARE @userJID INT, @GuiLdID INT, @MaxOptLevel TINYINT
    SET @MaxOptLevel = 12    -- Set Max Plus value (change this)
    /*
     * Get JID and GuildID info
     */
    SELECT @userJID = a.UserJID, @GuiLdID = b.GuildID FROM [SRO_VT_SHARD].[dbo].[_User] a WITH (NOLOCK)
        INNER JOIN [SRO_VT_SHARD].[dbo].[_Char] b ON a.CharID = b.CharID WHERE a.CharID = @CharID
    /*
     * 1. INVENTORY: Update items in inventory if has more than +12 without Adv elixir
     */
    UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND ID64 IN (
        SELECT ItemID FROM [SRO_VT_SHARD].[dbo].[_Inventory] WITH (NOLOCK)
        WHERE CharID = @CharID AND ItemID > 0
    )
    /*
     * 2. INVENTORY: Remove Adv elixir effect for items in inventory if has more than +12 with Adv elixir
     */
    DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
        SELECT a.ItemID FROM [SRO_VT_SHARD].[dbo].[_Inventory] a WITH (NOLOCK) 
        INNER JOIN [SRO_VT_SHARD].[dbo].[_Items] b ON a.ItemID = b.ID64 
        INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] c ON a.ItemID = c.nItemDBID
        WHERE a.CharID = @CharID AND a.ItemID > 0 AND (b.OptLevel + c.nOptValue) > @MaxOptLevel
        AND c.bOptType = 2
    )
    /*
     * 3. PERSONAL STORAGE: Update items in personal storage if has more than +12 without Adv elixir
     */
    UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND ID64 IN (
        SELECT ItemID FROM [SRO_VT_SHARD].[dbo].[_Chest] WITH (NOLOCK)
        WHERE UserJID = @userJID AND ItemID > 0
    )
    /*
     * 4. PERSONAL STORAGE: Remove Adv elixir effect for items in personal storage if has more than +12 with Adv elixir
     */
    DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
        SELECT a.ItemID FROM [SRO_VT_SHARD].[dbo].[_Chest] a WITH (NOLOCK) 
        INNER JOIN [SRO_VT_SHARD].[dbo].[_Items] b ON a.ItemID = b.ID64 
        INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] c ON a.ItemID = c.nItemDBID
        WHERE a.UserJID = @userJID AND a.ItemID > 0 AND (b.OptLevel + c.nOptValue) > @MaxOptLevel
        AND c.bOptType = 2
    )
    /*
     * 5. GUILD: If character is guild member, check Item Plus in Guild Inventory
     */
    IF  @GuiLdID <> 0)
    BEGIN
        /*
         * 5.1. Update items in guild storage if has more than +12 without Adv elixir
         */
        UPDATE [SRO_VT_SHARD].[dbo].[_Items] SET OptLevel = @MaxOptLevel WHERE OptLevel > @MaxOptLevel AND ID64 IN (
            SELECT ItemID FROM [SRO_VT_SHARD].[dbo].[_GuildChest] WITH (NOLOCK)
            WHERE GuildID = @GuiLdID AND ItemID > 0
        )
        /*
         * 5.2. Remove Adv elixir effect for items in guild storage if has more than +12 with Adv elixir
         */
        DELETE FROM [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] WHERE nItemDBID IN (
            SELECT a.ItemID FROM [SRO_VT_SHARD].[dbo].[_GuildChest] a WITH (NOLOCK) 
            INNER JOIN [SRO_VT_SHARD].[dbo].[_Items] b ON a.ItemID = b.ID64 
            INNER JOIN [SRO_VT_SHARD].[dbo].[_BindingOptionWithItem] c ON a.ItemID = c.nItemDBID
            WHERE a.GuildID = @GuiLdID AND a.ItemID > 0 AND (b.OptLevel + c.nOptValue) > @MaxOptLevel
            AND c.bOptType = 2
        )
    END
    /*
     * END: Item Plus check
     */
END

To test, fuse some items with or without Adv Elixir to more than your Max Plus value, then teleport (to test method 1), or put those items in Inventory, Storage, and Guild Storage, then logout/login again (to test method 2). see if it works.

good luck and have fun ;)

[MentaL]

[pr0xy1337]

Could be faster if you instead of looking for the number like that you use dwData, hint:

[hellangel2]

In Real Time .. IF Item Take +13 whats Query Do??

Instant Disconect ou Teleport or ???