[Guide] Simple SQL query to check Max Plus of items during login/logout session

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

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

(Update: as its.soul 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. :)
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

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

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

Spoiler

Code:

/* 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 ;)