[SQL-Script] Merge Consumables items

02/24/2019 14:14 beetols#1

This sql script is useful for those who use bags and wanna automatically stack the items in inventory. It works by relogging.

You need to be implement this code in:
PS_GameLog -> Programmability -> Store Procedures -> dbo.usp_Insert_Action_Log_E

Quote:

IF @ActionType = '108'
BEGIN
-- Merge Consumable items
DECLARE @MyCursor CURSOR, @RowID INT, @RowID2 INT, @Count INT, @Count2 INT, @MaxCount INT, @ItemID INT
SET @MyCursor = CURSOR FOR select RowID from PS_GameData.dbo.CharItems where CharID = @CharID AND (Type = 25 OR Type = 44 OR Type = 100) ORDER BY Bag DESC, Slot DESC
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @RowID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ItemID = ci.ItemID, @Count = ci.[Count], @MaxCount = i.[Count] FROM PS_GameData.dbo.CharItems ci JOIN PS_GameDefs.dbo.Items i ON ci.ItemID = i.ItemID WHERE ci.CharID = @CharID AND ci.RowID = @RowID
IF @ItemID IS NOT NULL
BEGIN
SELECT TOP 1 @RowID2 = RowID, @Count2 = [Count] FROM PS_GameData.dbo.CharItems WHERE CharID = @CharID AND ItemID = @ItemID AND RowID != @RowID ORDER BY Bag DESC, Slot DESC
IF @RowID2 IS NOT NULL
BEGIN
SET @Count = @Count + @Count2
IF @Count > @MaxCount
BEGIN
SET @Count2 = @Count - @MaxCount
SET @Count = @MaxCount
UPDATE PS_GameData.dbo.CharItems SET [Count] = @Count2 WHERE RowID = @RowID2
END
ELSE
BEGIN
DELETE FROM PS_GameData.dbo.CharItems WHERE RowID = @RowID2
END
UPDATE PS_GameData.dbo.CharItems SET [Count] = @Count WHERE RowID = @RowID
END
END
FETCH NEXT FROM @MyCursor INTO @RowID
CLOSE @MyCursor
DEALLOCATE @MyCursor
END
-- Merge Consumable items END
END

There is an assembly script for stack the items from normal bags, but doesn't work with items that in ItemCreate.ini use 1001+ function.

02/24/2019 15:59 XareL#2

Mention tags messed up this script.

02/24/2019 17:34 [GM]-RazoR-*#3

Quote:

Originally Posted by XareL

Mention tags messed up this script.

This will solve the problem.

Code:

-- LOG OUT ACTION
	IF ActionType = '108'
	BEGIN
	-- Merge Consumable items
	DECLARE  mycursor CURSOR,  RowiD INT,  RowiD2 INT, count INT,  count2 INT,  maxcount INT,  itemID INT
	BEGIN
		SET  mycursor = CURSOR FOR select RowID from PS_GameData.dbo.CharItems where CharID = ChariD AND (Type = 25 OR Type = 44 OR Type = 100)  ORDER BY Bag DESC, Slot DESC
		OPEN  mycursor 
		FETCH NEXT FROM  mycursor INTO  RowiD
		WHILE @@FETCH_STATUS = 0
		BEGIN
		  SELECT  itemID = ci.ItemID, count = ci.[Count],  maxcount = i.[Count] FROM PS_GameData.dbo.CharItems ci JOIN PS_GameDefs.dbo.Items i ON ci.ItemID = i.ItemID WHERE ci.CharID = ChariD AND ci.RowID =  RowiD
		  IF  itemID IS NOT NULL
		  BEGIN
			  SELECT TOP 1  RowiD2 = RowID,  count2 = [Count] FROM PS_GameData.dbo.CharItems WHERE CharID = ChariD AND ItemID =  itemID AND RowID !=  RowiD ORDER BY Bag DESC, Slot DESC
			  IF  RowiD2 IS NOT NULL
			  BEGIN
			        SET count = count +  count2
				IF count >  maxcount
				BEGIN
					SET  count2 = count -  maxcount
					SET count =  maxcount
					UPDATE PS_GameData.dbo.CharItems SET [Count] =  count2 WHERE RowID =  RowiD2
				END
				ELSE
				BEGIN
					DELETE FROM PS_GameData.dbo.CharItems WHERE RowID =  RowiD2
				 END
				UPDATE PS_GameData.dbo.CharItems SET [Count] = count WHERE RowID =  RowiD
			  END
		  END
		  FETCH NEXT FROM  mycursor INTO  RowiD 
		END
		CLOSE  mycursor
		DEALLOCATE  mycursor
	END
	-- Merge Consumable items END
END

02/24/2019 18:49 beetols#4

Quote:

Originally Posted by XareL

Mention tags messed up this script.

Now as quote tag seems working properly, sorry that I didn't notice it before!

Quote:

Originally Posted by [GM]-RazoR-*

This will solve the problem.

This wont work, please test before share something corrupt, thank you anyway for trying to help!