[Euphoria Dev Team Release] Perfect Orange Stats Game Service

[st.jimmy998]

Originally Posted by lool9329

I grabbed another look Procedure and relogiar NOR Mission Meeting Point Inserts GM RR

Reemplazalo por esto, ya que segun lo que dijiste, estas intentando recrear un Arma, pero no hay Armas con Type 20, se entiende? El Type 20 es de las botas para los personajes de la Faccion Luz

INSERT INTO @GearTypes VALUES (16),(17),(18),(19),(20),(21),(24),(31),(32),(33), (34),(35),(36),(39)
-- Types of gears, capes. Here you can include anything you want to be rerollable with both stat and vital stat recreation runes.

INSERT INTO @WeaponTypes VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ,(13),(14),(15),(22),(23),(40)
-- Types of weapons, accessories. Here you can include anything you want to be rerollable only with stat recreation runes.

[lool9329]

Creo que no logro amigo poder insertar almenos una GM RR en las armaduras, armas y joyerias.

USE [PS_GameData]
GO
/****** Object: Trigger [dbo].[Perfect_Orange_Stats] Script Date: 05/05/2016 22:56:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ==================================================
-- Developed by Euphoria Dev Team
-- ==================================================

ALTER TRIGGER [dbo].[Perfect_Orange_Stats]
ON [dbo].[CharQuests]
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @QuestID INT = (SELECT QuestID FROM inserted)

IF @QuestID = 1410 -- The Perfect Orange Stats QuestID
BEGIN

DECLARE @CharID INT = (SELECT CharID FROM inserted)
DECLARE @UserUID INT = (SELECT UserUID FROM Chars WHERE CharID = @CharID)

DECLARE @ItemType TINYINT,
@RecRuneItemID INT

DECLARE @StatRecRuneItemID TABLE (StatRecRuneItemID INT) -- Str, Dex, Rec, Int, Wis, Luc
DECLARE @VitalRecRuneItemID TABLE (VitalRecRuneItemID INT) -- HP, MP, SP
DECLARE @GearTypes TABLE (GearTypes TINYINT) -- Gears, Capes and Shields
DECLARE @WeaponTypes TABLE (WeaponTypes TINYINT) -- Weapons and Accessories

-- The above tables were created in order to ensure a fast access to the types
-- of items that are to be recreated and the rune used to recreate them.


SET @ItemType = (SELECT Type FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
SET @RecRuneItemID = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 1)

INSERT INTO @StatRecRuneItemID VALUES (100202),(100203),(100204),(100205),(100206),(1002 07)
-- ItemIDs of stat recreation runes (Str, Dex, Rec, Int, Wis, Luc, HP, MP, SP)

INSERT INTO @VitalRecRuneItemID VALUES (100208),(100209),(100210)
-- ItemIDs of vital stat recreation runes (HP, MP, SP)

INSERT INTO @GearTypes VALUES (16),(17),(18),(19),(20),(21),(24),(31),(32),(33), (34),(35),(36),(39)
-- Types of gears, capes. Here you can include anything you want to be rerollable with both stat and vital stat recreation runes.

INSERT INTO @WeaponTypes VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ,(13),(14),(15),(22),(23),(40)
-- Types of weapons, accessories. Here you can include anything you want to be rerollable only with stat recreation runes.

IF ((@ItemType IN (SELECT GearTypes FROM @GearTypes)) AND (@RecRuneItemID IN (SELECT StatRecRuneItemID FROM @StatRecRuneItemID) OR @RecRuneItemID IN (SELECT VitalRecRuneItemID FROM @VitalRecRuneItemID))) OR (@ItemType IN (SELECT WeaponTypes FROM @WeaponTypes) AND (@RecRuneItemID IN (SELECT StatRecRuneItemID FROM @StatRecRuneItemID)))
-- The above IF statement checks if the item to be recreated and the recreation rune are compatible with each other.
-- As you can see, it works only if it's an item with the Type from @GearTypes(gears, capes) and all recreation runes,
-- or if it's an item with the Type from @WeaponTypes(weapons, accessories) and stat recreation runes only.
BEGIN

DECLARE @Craftname VARCHAR(20),
@Str TINYINT,
@Dex TINYINT,
@Rec TINYINT,
@Int TINYINT,
@Wis TINYINT,
@Luc TINYINT,
@HP TINYINT,
@MP TINYINT,
@SP TINYINT,
@Enchant TINYINT,

@ItemID INT,
@ReqWis TINYINT,
@Server TINYINT,
@OJs TINYINT,
@NewOJs TINYINT,
@MaxedOJs TINYINT,
@LowestOJType VARCHAR(3),
@LowestOJ TINYINT

SET @Craftname = (SELECT Craftname FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
SET @Str = CAST(SUBSTRING(@Craftname,1,2) AS TINYINT)
SET @Dex = CAST(SUBSTRING(@Craftname,3,2) AS TINYINT)
SET @Rec = CAST(SUBSTRING(@Craftname,5,2) AS TINYINT)
SET @Int = CAST(SUBSTRING(@Craftname,7,2) AS TINYINT)
SET @Wis = CAST(SUBSTRING(@Craftname,9,2) AS TINYINT)
SET @Luc = CAST(SUBSTRING(@Craftname,11,2) AS TINYINT)
SET @HP = CAST(SUBSTRING(@Craftname,13,2) AS TINYINT)
SET @MP = CAST(SUBSTRING(@Craftname,15,2) AS TINYINT)
SET @SP = CAST(SUBSTRING(@Craftname,17,2) AS TINYINT)
SET @Enchant = CAST(SUBSTRING(@Craftname,19,2) AS TINYINT)

SET @ItemID = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
SET @ReqWis = (SELECT ReqWis FROM PS_GameDefs.dbo.Items WHERE ItemID = @ItemID)
-- Get the maximum possible OJ on the item
SET @Server = (SELECT Server FROM PS_GameDefs.dbo.Items WHERE ItemID = @ItemID)
-- Get the maximum amount of OJs on the item

DECLARE @AllOJs TABLE (StatName VARCHAR(3), Stats TINYINT)
INSERT INTO @AllOJs VALUES ('Str',@Str),('Dex',@Dex),('Rec',@Rec),('Int',@Int ),('Wis',@Wis),('Luc',@Luc),('HP',@HP),('MP',@MP), ('SP',@SP)
-- Declaring the @AllOJs table and storing the OJs of the item before the recreation process.

SET @OJs = (SELECT COUNT(*) FROM @AllOJs WHERE Stats > 0) -- Getting the amount of OJs
SET @MaxedOJs = (SELECT COUNT(*) FROM @AllOJs WHERE Stats = @ReqWis) -- Getting the amount of MaxedOJs

IF @OJs <= @Server AND @MaxedOJs < @Server
-- Checking if there's still room for another perfect OJ.
BEGIN

DECLARE @NewStr TINYINT,
@NewDex TINYINT,
@NewRec TINYINT,
@NewInt TINYINT,
@NewWis TINYINT,
@NewLuc TINYINT,
@NewHP TINYINT,
@NewMP TINYINT,
@NewSP TINYINT,
@NewEnchant TINYINT

-- Declaring the new variables, where the new orange stats will be stored

SET @NewStr = @Str
SET @NewDex = @Dex
SET @NewRec = @Rec
SET @NewInt = @Int
SET @NewWis = @Wis
SET @NewLuc = @Luc
SET @NewHP = @HP
SET @NewMP = @MP
SET @NewSP = @SP
SET @NewEnchant = @Enchant

-- Assigning the old OJs to the new OJ variables

IF @RecRuneItemID = 100202 -- STR Rec Rune
BEGIN
SET @NewStr = @ReqWis
END

ELSE IF @RecRuneItemID = 100203 -- DEX Rec Rune
BEGIN
SET @NewDex = @ReqWis
END

ELSE IF @RecRuneItemID = 100204 -- REC Rec Rune
BEGIN
SET @NewRec = @ReqWis
END

ELSE IF @RecRuneItemID = 100205 -- INT Rec Rune
BEGIN
SET @NewInt = @ReqWis
END

ELSE IF @RecRuneItemID = 100206 -- WIS Rec Rune
BEGIN
SET @NewWis = @ReqWis
END

ELSE IF @RecRuneItemID = 100207 -- LUC Rec Rune
BEGIN
SET @NewLuc = @ReqWis
END

ELSE IF @RecRuneItemID = 100208 -- HP Rec Rune
BEGIN
SET @NewHP = @ReqWis
END

ELSE IF @RecRuneItemID = 100209 -- MP Rec Rune
BEGIN
SET @NewMP = @ReqWis
END

ELSE IF @RecRuneItemID = 100210 -- SP Rec Rune
BEGIN
SET @NewSP = @ReqWis
END

END

ELSE GOTO FAIL

END -- End of the case where both items are good and compatible with each other

ELSE GOTO FAIL
-- In the case where the item and the rec rune aren't compatible with each other,
-- the process will end. No modifications to the item will occur.

CHECKOJAMOUNT:
-- This label checks if after the reroll there aren't more
-- types of orange stats than the item can normally have.

DECLARE @NewAllOJs TABLE (StatName VARCHAR(3), Stats TINYINT)
INSERT INTO @NewAllOJs VALUES ('Str',@NewStr),('Dex',@NewDex),('Rec',@NewRec),(' Int',@NewInt),('Wis',@NewWis),('Luc',@NewLuc),('HP ',@NewHP),('MP',@NewMP),('SP',@NewSP)
-- Declaring the @NewAllOJs table and assigning the new OJs to it.

SET @NewOJs = (SELECT COUNT(*) FROM @NewAllOJs WHERE Stats > 0)
-- Getting the amount of OJs on the item after the reroll.

IF @NewOJs > @Server
-- If the amount is bigger than what the item can normally have,
-- the lowest stat, that is bigger than zero of course, is going to be removed.
-- e.g. If the item allows max 4 orange stats, max stat being 40,
-- it has 40 STR, 40 DEX, 40 REC, 39 LUC, and we want to use a HP Rec Rune on it.
-- After the reroll process it will have 4000 HP, 40 STR, 40 DEX, 40 REC and 0 LUC.
BEGIN
SET @LowestOJType = (SELECT StatName FROM @NewAllOJs WHERE Stats = (SELECT MIN(Stats) FROM @NewAllOJs WHERE Stats > 0))
-- Getting the lowest stat.

IF @LowestOJType = 'Str'
SET @NewStr = 0
ELSE IF @LowestOJType = 'Dex'
SET @NewDex = 0
ELSE IF @LowestOJType = 'Rec'
SET @NewRec = 0
ELSE IF @LowestOJType = 'Int'
SET @NewInt = 0
ELSE IF @LowestOJType = 'Wis'
SET @NewWis = 0
ELSE IF @LowestOJType = 'Luc'
SET @NewLuc = 0
ELSE IF @LowestOJType = 'HP'
SET @NewHP = 0
ELSE IF @LowestOJType = 'MP'
SET @NewMP = 0
ELSE IF @LowestOJType = 'SP'
SET @NewSP = 0
-- Whichever the lowest stat is, it is changed to 0.
END

CRAFTNAMEUPDATE:
-- This label is responsible for applying the new orange stat to the item.

DECLARE @FinalStr VARCHAR(2),
@FinalDex VARCHAR(2),
@FinalRec VARCHAR(2),
@FinalInt VARCHAR(2),
@FinalWis VARCHAR(2),
@FinalLuc VARCHAR(2),
@FinalHP VARCHAR(2),
@FinalMP VARCHAR(2),
@FinalSP VARCHAR(2),
@FinalEnchant VARCHAR(2)
-- As Craftname is a varchar(20), we need to create it out of varchars.

SET @FinalStr = CAST(@NewStr AS VARCHAR(2))
SET @FinalDex = CAST(@NewDex AS VARCHAR(2))
SET @FinalRec = CAST(@NewRec AS VARCHAR(2))
SET @FinalInt = CAST(@NewInt AS VARCHAR(2))
SET @FinalWis = CAST(@NewWis AS VARCHAR(2))
SET @FinalLuc = CAST(@NewLuc AS VARCHAR(2))
SET @FinalHP = CAST(@NewHP AS VARCHAR(2))
SET @FinalMP = CAST(@NewMP AS VARCHAR(2))
SET @FinalSP = CAST(@NewSP AS VARCHAR(2))
SET @FinalEnchant = CAST(@NewEnchant AS VARCHAR(2))
-- Assigning the new OJs to the respective varchar(2) variables.

IF LEN(@FinalStr) < 2
BEGIN
SET @FinalStr = '0' + @FinalStr
END

IF LEN(@FinalDex) < 2
BEGIN
SET @FinalDex = '0' + @FinalDex
END

IF LEN(@FinalRec) < 2
BEGIN
SET @FinalRec = '0' + @FinalRec
END

IF LEN(@FinalInt) < 2
BEGIN
SET @FinalInt = '0' + @FinalInt
END

IF LEN(@FinalWis) < 2
BEGIN
SET @FinalWis = '0' + @FinalWis
END

IF LEN(@FinalLuc) < 2
BEGIN
SET @FinalLuc = '0' + @FinalLuc
END

IF LEN(@FinalHP) < 2
BEGIN
SET @FinalHP = '0' + @FinalHP
END

IF LEN(@FinalMP) < 2
BEGIN
SET @FinalMP = '0' + @FinalMP
END

IF LEN(@FinalSP) < 2
BEGIN
SET @FinalSP = '0' + @FinalSP
END

IF LEN(@FinalEnchant) < 2
BEGIN
SET @FinalEnchant = '0' + @FinalEnchant
END
-- The above 10 blocks are here to ensure that each orange stat varchar(2) is actually 2 digit long.

UPDATE UserStoredItems
SET Craftname = @FinalStr + @FinalDex + @FinalRec + @FinalInt + @FinalWis + @FinalLuc + @FinalHP + @FinalMP + @FinalSP + @FinalEnchant
WHERE UserUID = @UserUID AND Slot = 0
-- The above block updates the Craftname of the item.

DELETERUNE:
-- This label removes the recreation rune.
-- As you can see, it removes just one recreation rune, not the whole stack.

UPDATE UserStoredItems
SET Count -= 1
WHERE UserUID = @UserUID AND Slot = 1
-- Decrementing the amount of recreation runes

DELETE FROM UserStoredItems
WHERE UserUID = @UserUID AND Slot = 1 AND Count = 0
-- If after decrementing the amount of recreation runes the amount is 0, delete the row from the database.

END

FAIL:
-- This label is called from above a couple of times,
-- in case the recreation requirements aren't met.
DELETEQUEST:
-- This label deletes the recreation quest.
DELETE FROM CharQuests
WHERE CharID = @CharID AND QuestID = @QuestID

END

[egelim]

Hello Friends
GM GM stock reserves retained earnings I have a problem in putting it in storage, I relog and the item I'm throwing GM, but don't get the item retained earnings are ineffective why do you think?
Thanks for your help in advance.

[dtnn]

Originally Posted by Mithrandir.

Can anyone please upload a screenshot of his NPC?

I created a npc in NpcQuest.Sdata,
created the recration runes in Item.SData,
Changed the ItemID's in the query at the start and at the end,
executed the query,
Server Restart

Don't work :/

I didn't changed any itemtypes or anything else..

Originally Posted by Mithrandir.

Problem is solved, nubness helped me.

Cna you explain how to fix this problem?

[RapSandy]

i become no quest and i change the npcquest.sdata can u me help? on Teamviewer

[GMCronus]

for all problems. look here.

you need sql 2008 r2 or highter NOT 2005. that's soooooooo 2015 :-P

then read the script.

You need to make quest number 2222 or make a quest and change that number.

In that quest you need to have a NPC number put the right npc number you have in AH ( or where ever you want to do this ).

Then you need to make the items 100230 - 100238. if you can't you have to change this numbers in the script to a item you can make.

Then all the items you want to be abel to use this have to be setup to use orange stats. this is done in sh tool ( for one ) here stats is how many diffrent you can have, and max is how much they can have on them at max. like they talk about here 40..

Now run the script, update the items and change the npcquest so the game can read the quest you made to this..

Update both client and server with this. and it should work. if not. well idk LOL

[dorado1234]

enter the items to my wherehouse > i get the mission and finish it > relog (and before of change character) > disconnect
someone can help me?

[AnimuNazi]

check your logs , client side and sql probably doesn’t match.

[dorado1234]

Anyone fix error that the item don't get new recreation? i used this script and change all items id and quest id and don't work. If anyone need my script edited for see and search the error or what i need edit, tell me. ty

[dorado1234]

Sorry about the spam but anyone can help me?
Anyone fix error that the item don't get new recreation? i used this script and change all items id and quest id and don't work. If anyone need my script edited for see and search the error or what i need edit, tell me. ty

[jefersongpx88]

I do my everything right but it does not work I quest tbm relogo and it does not work I can not explain better?

I do this more when I put item I of the procedure of the quest relogo and does not recreat can help explaining please

I do this more when I put item I of the procedure of the quest relogo and does not recreat can help explaining please

Originally Posted by nubness

Hello elitepvpers,

After multiple requests, I decided to release the Perfect Orange Stats Service that we used on Shaiya Euphoria. Before releasing it, we decided to improve it a little bit, to make it even more powerful and flexible.
Some of you might prefer the web based recreation service, we chose to create something different, it's purely game based, you don't have to go on any website to do apply Perfect Orange Stats to your items. The former players of Shaiya Euphoria loved it, it is indeed very simple to use.

So, let's get started.
First of all, in order to avoid accidental rerolls, a quest is necessary. All you have to do is create a quest that doesn't require anything, which basically means it's completed right after starting it. Make the quest start at the AH Warehouse Keeper NPC(6 29), it doesn't really matter where it ends.
Second of all, you need to change the name, description and icon of 9 items:

• STR Recreation Rune
• DEX Recreation Rune
• REC Recreation Rune
• INT Recreation Rune
• WIS Recreation Rune
• LUC Recreation Rune
• HP Recreation Rune
• MP Recreation Rune
• SP Recreation Rune

The main, and the most important part of this is the database trigger that does the whole thing:

 Spoiler

USE [PS_GameData]
GO
/****** Object:  Trigger [dbo].[Perfect_Orange_Stats]    Script Date: 07/31/2013 15:21:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ==================================================
--	    Developed by Euphoria Dev Team
-- ==================================================

CREATE TRIGGER [dbo].[Perfect_Orange_Stats]
   ON  [dbo].[CharQuests]
   AFTER INSERT
AS 
BEGIN

	SET NOCOUNT ON;

    DECLARE @QuestID INT = (SELECT QuestID FROM inserted)
    
    IF @QuestID = 2222 -- The Perfect Orange Stats QuestID
    BEGIN
		
		DECLARE @CharID INT = (SELECT CharID FROM inserted)
		DECLARE	@UserUID INT = (SELECT UserUID FROM Chars WHERE CharID = @CharID)
		
		DECLARE	@ItemType TINYINT,
				@RecRuneItemID INT
		
		DECLARE @StatRecRuneItemID TABLE (StatRecRuneItemID INT) -- Str, Dex, Rec, Int, Wis, Luc
		DECLARE @VitalRecRuneItemID TABLE (VitalRecRuneItemID INT) -- HP, MP, SP
		DECLARE @GearTypes TABLE (GearTypes TINYINT) -- Gears, Capes and Shields
		DECLARE @WeaponTypes TABLE (WeaponTypes TINYINT) -- Weapons and Accessories
		
		-- The above tables were created in order to ensure a fast access to the types
		-- of items that are to be recreated and the rune used to recreate them.
		
		
		SET @ItemType = (SELECT Type FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
		SET @RecRuneItemID = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 1)
		
		INSERT INTO @StatRecRuneItemID VALUES (100230),(100231),(100232),(100233),(100234),(100235)
		-- ItemIDs of stat recreation runes (Str, Dex, Rec, Int, Wis, Luc, HP, MP, SP)
		
		INSERT INTO @VitalRecRuneItemID VALUES (100236),(100237),(100238)
		-- ItemIDs of vital stat recreation runes (HP, MP, SP)
		
		INSERT INTO @GearTypes VALUES (16),(17),(18),(19),(20),(21),(24),(31),(32),(33),(34),(35),(36),(39)
		-- Types of gears, capes. Here you can include anything you want to be rerollable with both stat and vital stat recreation runes.
		
		INSERT INTO @WeaponTypes VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(22),(23),(40)
		-- Types of weapons, accessories. Here you can include anything you want to be rerollable only with stat recreation runes.
		
		IF ((@ItemType IN (SELECT GearTypes FROM @GearTypes)) AND (@RecRuneItemID IN (SELECT StatRecRuneItemID FROM @StatRecRuneItemID) OR @RecRuneItemID IN (SELECT VitalRecRuneItemID FROM @VitalRecRuneItemID))) OR (@ItemType IN (SELECT WeaponTypes FROM @WeaponTypes) AND (@RecRuneItemID IN (SELECT StatRecRuneItemID FROM @StatRecRuneItemID)))
		-- The above IF statement checks if the item to be recreated and the recreation rune are compatible with each other.
		-- As you can see, it works only if it's an item with the Type from @GearTypes(gears, capes) and all recreation runes,
		-- or if it's an item with the Type from @WeaponTypes(weapons, accessories) and stat recreation runes only.
		BEGIN
			
			DECLARE @Craftname VARCHAR(20),
					@Str TINYINT,
					@Dex TINYINT,
					@Rec TINYINT,
					@Int TINYINT,
					@Wis TINYINT,
					@Luc TINYINT,
					@HP TINYINT,
					@MP TINYINT,
					@SP TINYINT,
					@Enchant TINYINT,
								
					@ItemID INT,
					@ReqWis TINYINT,
					@Server TINYINT,
					@OJs TINYINT,
					@NewOJs TINYINT,
					@MaxedOJs TINYINT,
					@LowestOJType VARCHAR(3),
					@LowestOJ TINYINT
			
			SET @Craftname = (SELECT Craftname FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
			SET @Str = CAST(SUBSTRING(@Craftname,1,2) AS TINYINT)
			SET @Dex = CAST(SUBSTRING(@Craftname,3,2) AS TINYINT)
			SET @Rec = CAST(SUBSTRING(@Craftname,5,2) AS TINYINT)
			SET @Int = CAST(SUBSTRING(@Craftname,7,2) AS TINYINT)
			SET @Wis = CAST(SUBSTRING(@Craftname,9,2) AS TINYINT)
			SET @Luc = CAST(SUBSTRING(@Craftname,11,2) AS TINYINT)
			SET @HP = CAST(SUBSTRING(@Craftname,13,2) AS TINYINT)
			SET @MP = CAST(SUBSTRING(@Craftname,15,2) AS TINYINT)
			SET @SP = CAST(SUBSTRING(@Craftname,17,2) AS TINYINT)
			SET @Enchant = CAST(SUBSTRING(@Craftname,19,2) AS TINYINT)
						
			SET @ItemID = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
			SET @ReqWis = (SELECT ReqWis FROM PS_GameDefs.dbo.Items WHERE ItemID = @ItemID)
			-- Get the maximum possible OJ on the item
			SET @Server = (SELECT Server FROM PS_GameDefs.dbo.Items WHERE ItemID = @ItemID)
			-- Get the maximum amount of OJs on the item
			
			DECLARE @AllOJs TABLE (StatName VARCHAR(3), Stats TINYINT)
			INSERT INTO @AllOJs VALUES ('Str',@Str),('Dex',@Dex),('Rec',@Rec),('Int',@Int),('Wis',@Wis),('Luc',@Luc),('HP',@HP),('MP',@MP),('SP',@SP)
			-- Declaring the @AllOJs table and storing the OJs of the item before the recreation process.
			
			SET @OJs = (SELECT COUNT(*) FROM @AllOJs WHERE Stats > 0) -- Getting the amount of OJs
			SET @MaxedOJs = (SELECT COUNT(*) FROM @AllOJs WHERE Stats = @ReqWis) -- Getting the amount of MaxedOJs
			
			IF @OJs <= @Server AND @MaxedOJs < @Server
			-- Checking if there's still room for another perfect OJ.
			BEGIN
			
			DECLARE @NewStr TINYINT,
					@NewDex TINYINT,
					@NewRec TINYINT,
					@NewInt TINYINT,
					@NewWis TINYINT,
					@NewLuc TINYINT,
					@NewHP TINYINT,
					@NewMP TINYINT,
					@NewSP TINYINT,
					@NewEnchant TINYINT
					
					-- Declaring the new variables, where the new orange stats will be stored
					
			SET @NewStr = @Str
			SET @NewDex = @Dex
			SET @NewRec = @Rec
			SET @NewInt = @Int
			SET @NewWis = @Wis
			SET @NewLuc = @Luc
			SET @NewHP = @HP
			SET @NewMP = @MP
			SET @NewSP = @SP
			SET @NewEnchant = @Enchant
				
				-- Assigning the old OJs to the new OJ variables
				
				IF @RecRuneItemID = 100230 -- STR Rec Rune
				BEGIN				
					SET @NewStr = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100231 -- DEX Rec Rune
				BEGIN				
					SET @NewDex = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100232 -- REC Rec Rune
				BEGIN				
					SET @NewRec = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100233 -- INT Rec Rune
				BEGIN				
					SET @NewInt = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100234 -- WIS Rec Rune
				BEGIN				
					SET @NewWis = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100235 -- LUC Rec Rune
				BEGIN				
					SET @NewLuc = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100236 -- HP Rec Rune
				BEGIN				
					SET @NewHP = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100237 -- MP Rec Rune
				BEGIN				
					SET @NewMP = @ReqWis
				END
				
				ELSE IF @RecRuneItemID = 100238 -- SP Rec Rune
				BEGIN				
					SET @NewSP = @ReqWis
				END
								
			END 
			
			ELSE GOTO FAIL
						
		END -- End of the case where both items are good and compatible with each other
		
		ELSE GOTO FAIL
		-- In the case where the item and the rec rune aren't compatible with each other,
		-- the process will end. No modifications to the item will occur.
		
		CHECKOJAMOUNT:
		-- This label checks if after the reroll there aren't more
		-- types of orange stats than the item can normally have.
		
			DECLARE @NewAllOJs TABLE (StatName VARCHAR(3), Stats TINYINT)
			INSERT INTO @NewAllOJs VALUES ('Str',@NewStr),('Dex',@NewDex),('Rec',@NewRec),('Int',@NewInt),('Wis',@NewWis),('Luc',@NewLuc),('HP',@NewHP),('MP',@NewMP),('SP',@NewSP)
			-- Declaring the @NewAllOJs table and assigning the new OJs to it.
			
			SET @NewOJs = (SELECT COUNT(*) FROM @NewAllOJs WHERE Stats > 0)
			-- Getting the amount of OJs on the item after the reroll.		
		
			IF @NewOJs > @Server
			-- If the amount is bigger than what the item can normally have,
			-- the lowest stat, that is bigger than zero of course, is going to be removed.
			-- e.g. If the item allows max 4 orange stats, max stat being 40,
			-- it has 40 STR, 40 DEX, 40 REC, 39 LUC, and we want to use a HP Rec Rune on it.
			-- After the reroll process it will have 4000 HP, 40 STR, 40 DEX, 40 REC and 0 LUC.
			BEGIN				
				SET @LowestOJType = (SELECT StatName FROM @NewAllOJs WHERE Stats = (SELECT MIN(Stats) FROM @NewAllOJs WHERE Stats > 0))
				-- Getting the lowest stat.
								
				IF @LowestOJType = 'Str'
					SET @NewStr = 0
				ELSE IF @LowestOJType = 'Dex'
					SET @NewDex = 0
				ELSE IF @LowestOJType = 'Rec'
					SET @NewRec = 0
				ELSE IF @LowestOJType = 'Int'
					SET @NewInt = 0
				ELSE IF @LowestOJType = 'Wis'
					SET @NewWis = 0
				ELSE IF @LowestOJType = 'Luc'
					SET @NewLuc = 0
				ELSE IF @LowestOJType = 'HP'
					SET @NewHP = 0
				ELSE IF @LowestOJType = 'MP'
					SET @NewMP = 0
				ELSE IF @LowestOJType = 'SP'
					SET @NewSP = 0
				-- Whichever the lowest stat is, it is changed to 0.
			END
		
		CRAFTNAMEUPDATE:
		-- This label is responsible for applying the new orange stat to the item.
		
			DECLARE @FinalStr VARCHAR(2),
					@FinalDex VARCHAR(2),
					@FinalRec VARCHAR(2),
					@FinalInt VARCHAR(2),
					@FinalWis VARCHAR(2),
					@FinalLuc VARCHAR(2),
					@FinalHP VARCHAR(2),
					@FinalMP VARCHAR(2),
					@FinalSP VARCHAR(2),
					@FinalEnchant VARCHAR(2)
					-- As Craftname is a varchar(20), we need to create it out of varchars.
			
			SET @FinalStr = CAST(@NewStr AS VARCHAR(2))
			SET @FinalDex = CAST(@NewDex AS VARCHAR(2))
			SET @FinalRec = CAST(@NewRec AS VARCHAR(2))
			SET @FinalInt = CAST(@NewInt AS VARCHAR(2))
			SET @FinalWis = CAST(@NewWis AS VARCHAR(2))
			SET @FinalLuc = CAST(@NewLuc AS VARCHAR(2))
			SET @FinalHP = CAST(@NewHP AS VARCHAR(2))
			SET @FinalMP = CAST(@NewMP AS VARCHAR(2))
			SET @FinalSP = CAST(@NewSP AS VARCHAR(2))
			SET @FinalEnchant = CAST(@NewEnchant AS VARCHAR(2))
			-- Assigning the new OJs to the respective varchar(2) variables.
			
			IF LEN(@FinalStr) < 2
			BEGIN
				SET @FinalStr = '0' + @FinalStr
			END
			
			IF LEN(@FinalDex) < 2
			BEGIN
				SET @FinalDex = '0' + @FinalDex
			END
			
			IF LEN(@FinalRec) < 2
			BEGIN
				SET @FinalRec = '0' + @FinalRec
			END
			
			IF LEN(@FinalInt) < 2
			BEGIN
				SET @FinalInt = '0' + @FinalInt
			END
			
			IF LEN(@FinalWis) < 2
			BEGIN
				SET @FinalWis = '0' + @FinalWis
			END
			
			IF LEN(@FinalLuc) < 2
			BEGIN
				SET @FinalLuc = '0' + @FinalLuc
			END
			
			IF LEN(@FinalHP) < 2
			BEGIN
				SET @FinalHP = '0' + @FinalHP
			END
			
			IF LEN(@FinalMP) < 2
			BEGIN
				SET @FinalMP = '0' + @FinalMP
			END
			
			IF LEN(@FinalSP) < 2
			BEGIN
				SET @FinalSP = '0' + @FinalSP
			END
			
			IF LEN(@FinalEnchant) < 2
			BEGIN
				SET @FinalEnchant = '0' + @FinalEnchant
			END
			-- The above 10 blocks are here to ensure that each orange stat varchar(2) is actually 2 digit long.
		
			UPDATE UserStoredItems
			SET Craftname = @FinalStr + @FinalDex + @FinalRec + @FinalInt + @FinalWis + @FinalLuc + @FinalHP + @FinalMP + @FinalSP + @FinalEnchant
			WHERE UserUID = @UserUID AND Slot = 0
			-- The above block updates the Craftname of the item.
			
		DELETERUNE:
		-- This label removes the recreation rune.
		-- As you can see, it removes just one recreation rune, not the whole stack.
		
			UPDATE UserStoredItems
			SET Count -= 1
			WHERE UserUID = @UserUID AND Slot = 1
			-- Decrementing the amount of recreation runes
			
			DELETE FROM UserStoredItems
			WHERE UserUID = @UserUID AND Slot = 1 AND Count = 0
			-- If after decrementing the amount of recreation runes the amount is 0, delete the row from the database. 
		
    END
    
    FAIL:
    -- This label is called from above a couple of times,
    -- in case the recreation requirements aren't met.		
	DELETEQUEST:
	-- This label deletes the recreation quest.
		DELETE FROM CharQuests
		WHERE CharID = @CharID AND QuestID = @QuestID

END

All you have to do is run it. I've attached the .sql file at the bottom of this post.

How does it work ? Simple, the player that wants a stat to be maxed on an item they own will simply go to the AH Warehouse(Watson Orpheris), place the item to be recreated in the 1st slot and the special recreation rune in the second slot. Then they have to take the quest, this will help prevent accidental rerolls. The recreation won't happen if the quest isn't taken, or also if the item already has all the possible stats maxed.

It wouldn't be bad to explain the player how this recreation service works inside the quest text.

The trigger/.sql file is full of comments, which I hope will help whoever implements this understand it better.

Enjoy !

P.S. There shouldn't be any questions, but if there are, feel free to post them in this thread, we'll do our best to answer them.

I do this more when I put item I of the procedure of the quest relogo and does not recreat can help explaining please

?

[[DEV]Wan]

Originally Posted by jefersongpx88

I do my everything right but it does not work I quest tbm relogo and it does not work I can not explain better?

I do this more when I put item I of the procedure of the quest relogo and does not recreat can help explaining please

I do this more when I put item I of the procedure of the quest relogo and does not recreat can help explaining please



I do this more when I put item I of the procedure of the quest relogo and does not recreat can help explaining please

?

qual seu problema mano

[pardilias]

Hi
I click on take
nothing happens

thank you

[[ADM]Minxii]

Firstly Really sorry to necro an old post. I am having an issue I have seen here from others but do not see a solution. I have tried googling and seeing if i could find the issue there and doing an SQL Profiler trace (but that just said the same as the logs not exactly where my issue was)

Ok so the error I get in logs is this

2019-03-13 14:30:01 err=-1, [Microsoft][ODBC SQL Server Driver][SQL 
Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression., SQL STATE: 21000, NATIVE ERROR: 512 (0x200)

2019-03-13 14:30:01 ::SaveCharacter 1144 Lexii ret=1, qerr=-1, {?=call Usp_Save_Char_Quest_Add_E(1144,2222,0,0,0,0)}

I take the quest Out items in WH Log to char screen - That's where I DC with a 10053 error and my Char rolls back. The weird thing tho is it doesn't happen to everyone just some people Any ideas?

I use SQL Server 2016 if anyone can help I would very much appreciate it

[beetols]

Originally Posted by [ADM]Minxii

Ok so the error I get in logs is this

2019-03-13 14:30:01 err=-1, [Microsoft][ODBC SQL Server Driver][SQL 
Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression., SQL STATE: 21000, NATIVE ERROR: 512 (0x200)

2019-03-13 14:30:01 ::SaveCharacter 1144 Lexii ret=1, qerr=-1, {?=call Usp_Save_Char_Quest_Add_E(1144,2222,0,0,0,0)}

I take the quest Out items in WH Log to char screen - That's where I DC with a 10053 error and my Char rolls back. The weird thing tho is it doesn't happen to everyone just some people Any ideas?

I use SQL Server 2016 if anyone can help I would very much appreciate it

It's hard to answer to your error. The Script is a Trigger on dbo.CharQuests and your error is given on the procedure usp_Save_Char_Quest_Add_E that return error -1.

I suppose that the trigger have some commands not correctly executed from your sql version, and you have to review the code and see if execute correctly all parts of it.

Unfortunatly without see your database there is not much more to say.. goodluck