[Release] Auto Enchanting Anywhere

[[Alcatraz]]

I seen someone asking for this script It was a script I built for a friend all it does is basically is it enchants all weapons and gears on a persons account each time they relog no matter where the items are located at in the players inventory or in their warehouse.

Please Note these are rough scripts I haven't fully tested them and I been working with my friend as he finds problems with them to get them fix but so far they have been working with very little problems and I have updated them to remove the problems found. BUT you will need to check the types of the items to the types your client is using not all clients have the same types.

I also modified the scripts so they should work for all versions of sql instead of just 2012 and up

part 1 for player warehouse

USE [PS_GameData]
GO
/****** Object:  Trigger [dbo].[Auto_Enchant]    Script Date: 6/16/2015 1:11:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Auto_Enchant]
ON [dbo].[UserStoredItems]
AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @UserUID INT
DECLARE @Slot INT 
DECLARE @Types TINYINT 
DECLARE @CraftName VARCHAR(20)
DECLARE @Enchant NVARCHAR(2)
DECLARE @Rolls VARCHAR(18)

SET @UserUID = (SELECT UserUID FROM inserted)
SET @Slot = (SELECT Slot FROM inserted)
SET @Types = (SELECT [Type] FROM inserted)
SET @CraftName = (SELECT Craftname FROM inserted)
SET @Enchant = (SELECT SUBSTRING(Craftname, 19, 2) FROM inserted)
SET @Rolls = (SELECT CAST(SUBSTRING(Craftname, 1, 18) AS NVARCHAR)FROM inserted)

IF @Types IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,19,31,34,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,69,84) and @CraftName = ''
BEGIN
UPDATE UserStoredItems
SET CRAFTNAME = 00000000000000000000
WHERE UserUID = @UserUID AND Slot = @Slot
END

IF @Types IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,31,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,69,84)
AND @Enchant < 20
BEGIN
SET @Enchant = 20
UPDATE UserStoredItems
SET CRAFTNAME = @Rolls + @Enchant
WHERE UserUID = @UserUID AND Slot = @Slot
END
ELSE IF @Types IN (16,17,18,19,20,21,67,68,70,71,31,32,33,34,35,36,82,83,85,86,24,39) 
AND @Enchant < 70
BEGIN
SET @Enchant = 70
UPDATE UserStoredItems
SET CRAFTNAME = @Rolls + @Enchant
WHERE UserUID = @UserUID AND Slot = @Slot
END

	END

part 2 player inventory

USE [PS_GameData]
GO
/****** Object:  StoredProcedure [dbo].[usp_Save_Char_Item_Add_E]    Script Date: 6/16/2015 2:15:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


/****** 개체: 저장 프로시저 dbo.usp_Save_Char_Item_Add_E ******/


ALTER Proc [dbo].[usp_Save_Char_Item_Add_E]

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

AS
DECLARE @Maketime as datetime
SELECT @Maketime = CONVERT(datetime, @MaketimeZ, 120)
--SET NOCOUNT ON

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

-- AUTO ENCHANT BY: ALCATRAZ-------------------------------------------------------------------------------------------------------------------------------
DECLARE @Enchant NVARCHAR(2)
DECLARE @Rolls VARCHAR(18) 

SET @Enchant = (SELECT SUBSTRING(@Craftname, 19, 2))
SET @Rolls = (SELECT CAST(SUBSTRING(@Craftname, 1, 18) AS NVARCHAR))

IF @Type IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,19,31,34,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,69,84) and @CraftName = ''
BEGIN
SET @Craftname = 00000000000000000000
END

IF @Type IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,31,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,69,84)
AND @Enchant < 20
BEGIN
SET @Enchant = 20
SET @CRAFTNAME = @Rolls + @Enchant
END
ELSE IF @Type IN (16,17,18,19,20,21,67,68,70,71,31,32,33,34,35,36,82,83,85,86,24,39) 
AND @Enchant < 70
BEGIN
SET @Enchant = 70
SET @CRAFTNAME = @Rolls + @Enchant
END
-- AUTO ENCHANT END----------------------------------------------------------------------------------------------------

INSERT INTO CharItems
(CharID, bag, slot, ItemID, Type, TypeID, ItemUID, quality, gem1, gem2, gem3, gem4,
gem5, gem6, craftname, [count], maketime, maketype)
VALUES(@CharID, @Bag, @Slot, @ItemID, @Type, @TypeID, @ItemUID, @Quality, @Gem1, @Gem2, @Gem3, @Gem4,
@Gem5, @Gem6, @Craftname, @Count, @Maketime, @Maketype)

IF(@@ERROR = 0)
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN -1
END

--SET NOCOUNT OFF

after running these scripts any weapon armor or shield a player picks up will be auto enchanted to max once they log out to char screen no matter what slot they are in for they players inventory or the players warehouse.

enjoy and if you if like it hit the thanks button

also a big thanks to for doing the testing of these scripts

this script was first built to bypass the need for a speed enchanting so that server wouldn't get any of the random bugs that come along with the release version of the enchanting items staying stacked that was released here

[nubness]

You tell us the first code snippet is for inventory, but it's for warehouse, and that the second one is for warehouse, but it's for inventory.

You use a trigger for warehouse, and you apply changes directly in the procedure for the inventory, and I'm not sure why. The stored procedure that adds items to the UserStoredItems (warehouse) table is usp_Save_User_StoredItem_Add_E.

Also, what's wrong with doing it all with only 4 lines of code ? Is all of that SQL diarrhea really necessary ?

IF @Type <= 15 OR @Type IN (60, 61) -- Weapons
	SET @Craftname = SUBSTRING(@Craftname, 1, 18) + '20'
ELSE IF @Type IN (16, 17, 18, 19, 20, 21, 24, 31, 32, 33, 34, 35, 36, 39) -- Gears and capes
	SET @Craftname = SUBSTRING(@Craftname, 1, 18) + '70'

[[Alcatraz]]

Originally Posted by nubness

You tell us the first code snippet is for inventory, but it's for warehouse, and that the second one is for warehouse, but it's for inventory.

You use a trigger for warehouse, and you apply changes directly in the procedure for the inventory, and I'm not sure why. The stored procedure that adds items to the UserStoredItems (warehouse) table is usp_Save_User_StoredItem_Add_E.

yeah I had the scripts backwards I fixed the info on them and the trigger was used when I was creating the scripts its much easier to modify the trigger then a procedure because I can make changes to it while the server is up and running unlike procedure some you have to stop all services and restart the server in order for them to take affect

Originally Posted by nubness

Also, what's wrong with doing it all with only 4 lines of code ? Is all of that SQL diarrhea really necessary ?

im sure I can do It less lines of code yes but the script was a modified version of another script so the coding was left and no I didn't remove it because I really wasn't planning on releasing this script I just released it because someone was asking for it.

but no one is perfect I was able to take your perfect oj script which is 343 lines and reduce it to 136 line and im sure I can make it even smaller if I remove all the description text from my version of the rewrite that would make it only 108 lines of code

[nubness]

You don't need to restart any services for stored procedures to take effect. Also, making shorter code is fine only as long as you don't mess with the functionality.

I wasn't bashing or anything. I'm just saying it all based on what I see here, as well as other releases of yours. You have A LOT of trash code that simply shouldn't be there, and don't get me wrong, but some people might even think you have no idea of what you're doing.

[[Alcatraz]]

with all things I do I know everything isn't always perfect. I didnt goto school for anything of it. I learn thru trial and error most of the time and I don't take any offense from anyone all replys are welcome if anything they will help me improve my skills more. and no offense but I just used you as a example to prove my point as to not everyone scripts are perfect because you was the one who posted about unneeded coding figure it would make a good example. I wasn't trying to offend you by doing so.

and you right about the functionality that why I didn't post anything about your oj script on the release I figure as long as it works and does what it meant to there isn't a point in posting a smaller version of.

but here is my rewrite of the oj script of yours im sure it can be improved more but this is what I came up with if you like to take a look at it here it is this is the 136 line version I traded some of the if statements for case to improve the speed of the scripts and did a little other work to it. but its just a rewrite of your code.

GO

CREATE TRIGGER [dbo].[GM_Reroll_System]
   ON  [dbo].[CharQuests]
   AFTER INSERT
AS

	SET NOCOUNT ON;

DECLARE @QuestID INT = (SELECT QuestID FROM Inserted) -- GETS THE QUESTID FROM THE INSERTED ROW --
DECLARE @CharID INT = (SELECT CharID FROM Inserted) -- GETS THE CHARID FROM THE INSERTED ROW --
DECLARE	@UserUID INT = (SELECT UserUID FROM Chars WHERE CharID = @CharID) -- GETS THE USERS USERUID NUMBER
DECLARE @Type INT = (SELECT [Type] FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0) -- GETS THE ITEM TYPE NUMBER --
DECLARE @OldCraftName NVARCHAR(20)
DECLARE @OldStr NVARCHAR(2) 
DECLARE @OldDex NVARCHAR(2) 
DECLARE @OldRec NVARCHAR(2)
DECLARE @OldInt NVARCHAR(2)
DECLARE @OldWis NVARCHAR(2)
DECLARE @OldLuc NVARCHAR(2)
DECLARE @OldHP  NVARCHAR(2)
DECLARE @OldMP  NVARCHAR(2)
DECLARE @OldSP  NVARCHAR(2)
DECLARE @OldEnchant  NVARCHAR(2)
DECLARE @ItemID INT
DECLARE @TypeID INT
DECLARE @MaxStats VARCHAR(20)
DECLARE @MaxTotalStats TINYINT
DECLARE @NewCraftName NVARCHAR(20)	
DECLARE @TotalOJs TINYINT
DECLARE @OldOJs TABLE (StatsType VARCHAR(3), OldStats NVARCHAR(2))
DECLARE @NewOJs TABLE (NewStatsType VARCHAR(3), NewStats NVARCHAR(2))
DECLARE @NewStr NVARCHAR(2) 
DECLARE @NewDex NVARCHAR(2) 
DECLARE @NewRec NVARCHAR(2)
DECLARE @NewInt NVARCHAR(2)
DECLARE @NewWis NVARCHAR(2)
DECLARE @NewLuc NVARCHAR(2)
DECLARE @NewHP NVARCHAR(2)
DECLARE @NewMP NVARCHAR(2)
DECLARE @NewSP NVARCHAR(2)

-- CHECKS THE QUESTID NUMBER AND MAKES SURE THERE IS A ITEM TO BE REROLLED --
IF @QuestID = 1722 
AND EXISTS (SELECT [Type] FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
AND EXISTS (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 1)
AND @Type IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,24,31,32,33,34,35,36,39,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,67,68,69,70,82,83,84,85,86)
	BEGIN
		SET @OldCraftName = (SELECT CraftName FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
		SET @OldStr = (SELECT SUBSTRING(@OldCraftName,1,2))
		SET @OldDex  = (SELECT SUBSTRING(@OldCraftName,3,2))
		SET @OldRec = (SELECT SUBSTRING(@OldCraftName,5,2))
		SET @OldInt = (SELECT SUBSTRING(@OldCraftName,7,2))
		SET @OldWis = (SELECT SUBSTRING(@OldCraftName,9,2))
		SET @OldLuc = (SELECT SUBSTRING(@OldCraftName,11,2))
		SET @OldHP = (SELECT SUBSTRING(@OldCraftName,13,2))
		SET @OldMP = (SELECT SUBSTRING(@OldCraftName,15,2))
		SET @OldSP = (SELECT SUBSTRING(@OldCraftName,17,2))
		SET @OldEnchant = (SELECT SUBSTRING(@OldCraftName,19,2))
		SET @ItemID = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 1)
		SET @TypeID = (SELECT TypeID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
		SET @MaxStats = (SELECT RIGHT ('0'+ CONVERT(NVARCHAR(2), ReqWis),2 ) FROM PS_GameDefs.dbo.Items WHERE [Type] = @Type and TypeID = @TypeID)
		SET @MaxTotalStats = (SELECT [Server] FROM PS_GameDefs.dbo.Items WHERE [Type] = @Type and TypeID = @TypeID)
		SET @TotalOJs = 0
		
		-- BUILDS A TABLE WITH ALL THE OLDSTATS AND A TABLE WITH ALL THE NEWSTATS
		INSERT INTO @NewOJs VALUES ('STR',@OldStr),('DEX',@OldDex),('REC',@OldRec),('INT',@OldInt),('WIS',@OldWis),('LUC',@OldLuc),('HP',@OldHP),('MP',@OldMP),('SP',@OldSP)
		
		-- ADDS THE NEW STAT TO THE NEWOJS TABLE --
		UPDATE @NewOJs 
		SET NewStats = @MaxStats 	
		WHERE NewStatsType = Case
		WHEN @ItemID = '???' THEN 'STR'
		WHEN @ItemID = '???' THEN 'DEX'
		WHEN @ItemID = '???' THEN 'REC'
		WHEN @ItemID = '???' THEN 'INT'
		WHEN @ItemID = '???' THEN 'WIS'
		WHEN @ItemID = '???' THEN 'LUC'
		WHEN @ItemID = '???' THEN 'HP'
		WHEN @ItemID = '???' THEN 'MP'
		WHEN @ItemID = '???' THEN 'SP'
		END

		-- COUNTS THE NUMBER OF OJS ON THE ITEM AFTER THE NEW OJ WAS ADDED TO THE ITEM --
		SET @TotalOJs = (SELECT COUNT(*) FROM NewOJs WHERE NewStats > 0)

		-- THIS COMPARES THE NEW TOTAL TO THE MAX ALLOWED NUMBER OF STATS ON THAT ITEM --
		IF @TotalOJs < @MaxTotalStats
			BEGIN -- THIS SETS THE LOWEST OJ TO ZERO AND IF THERE IS 2 OR MORE OJS AT THE SAME LOW NUMBER IT SELECTS THE TOP ONE --
				UPDATE TOP(1) @NewOJs
				SET NewStats = '00'
				WHERE NewStats = (SELECT MIN(NewStats) FROM @NewOJs WHERE NewStats > 0)
			END
	
	-- GETS ALL THE NEW STATS THAT WILL BE NEEDED FOR A NEW CRAFTNAME --
	SET @NewStr = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'STR')
	SET @NewDex = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'DEX')
	SET @NewRec = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'REC')
	SET @NewInt = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'INT')
	SET @NewWis = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'WIS')
	SET @NewLuc = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'LUC')
	SET @NewHP = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'HP')
	SET @NewMP = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'MP')
	SET @NewSP = (SELECT NewStats FROM @NewOJs WHERE NewStatsType = 'SP')

	-- RECREATES THE CRAFTNAME OF THE ITEM WITH ALL THE NEW STATS --
	SET @NewCraftName = (@NewStr + @NewDex + @NewRec + @NewInt + @NewWis + @NewLuc + @NewHP + @NewMP + @NewSP + @OldEnchant)

	-- THIS UPDATES THE ITEM WITH ALL THE NEW STATS --
	UPDATE UserStoredItems
	SET Craftname = @NewCraftName 	
	WHERE UserUID = @UserUID AND Slot = 0

	-- THIS UPDATES THE COUNT TOTAL OF THE RUNES USED TO - 1 OF ITS CURRENT COUNT --
	UPDATE UserStoredItems
	SET [Count] = [Count] - 1
	WHERE UserUID = @UserUID AND Slot = 1

	-- THIS DELETES THE RUNE IF THE COUNT OF IT IS CHANGED TO 0 --
	DELETE 
	FROM UserStoredItems
	WHERE UserUID = @UserUID AND Slot = 1 AND Count = 0
	
	-- THIS DELETES THE QUEST LISTED BECAUSE THE QUEST WAS COMPLETED --
	DELETE 
	FROM CharQuests
	WHERE CharID = @CharID AND QuestID = @QuestID
END

I left the itemids for the runes as ???s

the only real problem I found with this is the quest function of it because the database will be updated about every 15 mins so if someone takes the quest but doesnt logout before the auto update happens then the quest gets stuck in the quest table other then that the code you wrote was great.

Originally Posted by nubness

You don't need to restart any services for stored procedures to take effect.

I have had problem with that before that was the reason I go into making everything as trigger to start with until I perfected the custom scripts after I get all the bugs worked out I usually added them to the stored procedures but I don't currently use this script It was a build for a friend so there wasn't a need to build it as such.

[nubness]

The quest based recreation has plenty of flaws, true I coded it way too long ago, nowadays I do these things in assembly. Anyway, best of luck to you.

[[Alcatraz]]

yeah I kinda figure it was a earlier script because I seen some of your newer scripts and they are pretty much perfectly coded I just got in the habit of rewriting all scripts I find it helps me improve and that was one of the ones I did rewrite. and same to you

[Willians Rivero]

Quisiera colocarle el Auto enchant a mi sv pero no se como es exactamente y no se si los scrips quue tengo estan buenos, cuando intento ejecutarlos me sale error AYUDA

[PaFypvpers]

Care ! Type 39 was Dark side Cape, if you don't want to Enchant it, remove it in the selection

[st.jimmy998]

Originally Posted by PaFypvpers

Care ! Type 39 was Dark side Cape, if you don't want to Enchant it, remove it in the selection

Type 24 for Light side

[kankacık]

İ run work enchant but Recreation not work
how edit recreation (orange stat) ? Ty Guys

[pardilias]

hello

not enchant 20
lapisiatage enchant = 9
view the photo



where is error please

fixed : item warehouse

[Apero]

On the tack you have to put 70

[perlisson]

This script you know if you use this Item dup ! sorry i dont speak English...

[pardilias]

Hello
I have a concern
that enchanted that the weapons not the stuff