[Euphoria Dev Team Release] Perfect Orange Stats Game Service

[dorrigro]

@nubness I can add you on skype? give me your address, I need your help..

[rempieremco153]

Well done and thanks for the release, though i think GM Reroll's are total bullshit.
People should be able to win without them.

[nubness]

Originally Posted by rempieremco153

Well done and thanks for the release, though i think GM Reroll's are total bullshit.
People should be able to win without them.

Just like most updates coming after ep3, they're here for the profit, not for the game itself

Even though, I do believe enchants and orange stats aren't that bad, probably because I love farming

[lolercheto]

guys i followed the instructions but when i execute it in SQL it give me these errors.
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 14
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 16
Must declare the scalar variable "@QuestID".
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 19
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 20
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 20
Must declare the scalar variable "@CharID".
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 34
Must declare the scalar variable "@UserUID".
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 35
Must declare the scalar variable "@UserUID".
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 37
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 40
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 43
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 46
Incorrect syntax near ','.
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 76
Must declare the scalar variable "@UserUID".
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 88
Must declare the scalar variable "@UserUID".
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 95
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 191
Incorrect syntax near ','.
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 308
Must declare the scalar variable "@UserUID".
Msg 102, Level 15, State 1, Procedure Perfect_Orange_Stats, Line 316
Incorrect syntax near '-'.
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 321
Must declare the scalar variable "@UserUID".
Msg 137, Level 15, State 2, Procedure Perfect_Orange_Stats, Line 332
Must declare the scalar variable "@CharID".

[nubness]

This was answered a few times in this thread, please look carefully.

[accuface35]

hm i have the same mistakes how dorrigro, how is the correct script for sql 2005?

[nubness]

Originally Posted by accuface35

hm i have the same mistakes how dorrigro, how is the correct script for sql 2005?

What's the matter with you people ? Do I really have to rage on everyone here ? I checked this thread just now and I've seen me explaining what you're asking for not once, BUT TWICE. I EXPLAINED IT TWO TIMES, WHY IS IT SO HARD TO READ A FEW POSTS ?!?

[accuface35]

calm down but nubness times, it was only ne question, everything is good, there are still plenty of people who have never done this, but would like to learn that you can not do that bad or learn? then please show us some games looks like the complete script for 2005.

[Stureplan]

#Added to Index.

[nubness]

Originally Posted by accuface35

calm down but nubness times, it was only ne question, everything is good, there are still plenty of people who have never done this, but would like to learn that you can not do that bad or learn? then please show us some games looks like the complete script for 2005.

Did you read my rage post ? Look through this thread, I've answered your question in page 2 and in page 3.

[_Diavolino_]

i think could be good to make one compilation of all your advice Nub xD i think more and more to make one "Quote" way to make understand...
i am telling often i am one such of nub in sql but i could follow the instruction ! Philips and Nubness are using respectively Mssql 2012 and Mssql 2008... wich could be a good start to make working all the script that they offer to the community.
to make one new installation there is tons of tutorial on google... in advice you could all ready just in "services" by administration menu in windows to close mssql service and to process to the unistall by classic way or using one usefull programs to uninstall as Iobit Uninstall and to make scan of the "rest" of programs BEFORE MAKING ANY NEW installation of mssql... and i am agree it take less than 1h to do it... same for me xD

So take care more xD

regards !

[sominus]

I don't understand why would ppl stick to SQL2005 instead of upgrading to 2008 R2?
It's just make a copy of your DBs, unninstall 2005, install 2008, attach the DBs again and thats all.

I know the 2008 installer is a mess (not something that MS should be proud off) but it's not that hard if you installed 2005 before.
Besides you just have to install it once, and forget about it.
If you don't like Management Studio you could just use Navicat for administrative tasks, wich is 1000 times faster than Management Studio.

[accuface35]

you have write this In SQL Server 2005 such a thing as SET @VARIABLE += 1 doesn't work, while SET @VARIABLE = @VARIABLE + 1 does work.


But i didnt find it in the script to change, where is it in the script?

[Philipp_]

Compound operators are used once only, in the DELETERUNE label.

Originally Posted by nubness

UPDATE UserStoredItems
SET Count -= 1
WHERE UserUID = @UserUID AND Slot = 1

[Mithrandir.]

Hey i have a little problem, i created a new quest. quest id = 2244



And this is the query:

 Spoiler

USE [PS_GameData]
GO
/****** Object: Trigger [dbo].[Perfect_Orange_Stats] Script Date: 11/04/2013 20:07:31 ******/
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 = 2244 -- 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

Thanks for any help.