elitepvpers - [Release] Quest Upgrade System

elitepvpers (https://www.elitepvpers.com/forum/)

- SRO PServer Guides & Releases (https://www.elitepvpers.com/forum/sro-pserver-guides-releases/)

- - [Release] Quest Upgrade System (https://www.elitepvpers.com/forum/sro-pserver-guides-releases/3605453-release-quest-upgrade-system.html)

Aaron*

02/15/2015 01:06

[Release] Quest Upgrade System

I had some free time and i was thinking to write a procedure that upgrades your items by quests instead of using scrolls, and i have made it and tested it. it worked so i decided to release it since i don't need it.

FAQ
How does this system work?
You have to collect a certain number of a weapon, and once gained you can upgrade that count of that weapon to a newer seal of that weapon from a quest.

But i don't have upgrade quest,how to make it?
MaDenGo released it before , it is not too hard to make .

I don't like that system
gtfo without posting a stupid post.

Here is the procedure

Quote:

CREATE PROC [_AeronQuestUpgrade]

@CharID int,
@QuestID int
as
DECLARE @CharName Varchar(MAX) = (select CharName16 from SHARD.dbo._Char Where CharID = @CharID)
if(@QuestID='Your quest id')
begin
Declare @OldItemCode varchar(128)
Declare @NewItemCode varchar(128)
Declare @ItemID INT
Select @ItemID = (Select ID From SHARD.dbo._RefObjCommon Where CodeName128=@OldItemCode)
select @OldItemCode = (select codename128 from SHARD.._refobjcommon where id = @itemID)
If @OldItemCode = 'ITEM_EU_TSTAFF_10_B_RARE'
begin
set @NewItemCode = 'ITEM_EU_TSTAFF_10_C_RARE'
end
else if @OldItemCode = 'ITEM_EU_TSWORD_10_B_RARE'
begin
set @NewItemCode = 'ITEM_EU_TSWORD_10_C_RARE'
end

Declare @OldItemCount int
Select @OldItemCount = (select COUNT(Item.refitemid) from SHARD.dbo._Items as Item inner Join SHARD.dbo._Inventory as inv on item.ID64 = inv.ItemID where inv.CharID = @CharID and item.RefItemID = @ItemID and item.Serial64 >0)
if(@OldItemCount >= 5)
begin
update SHARD.dbo._Inventory set ItemID = 0
where ItemID in (SELECT TOP 5 Item.ID64 from SHARD.dbo._Items as Item
inner Join SHARD.dbo._Inventory as inv on item.ID64 = inv.ItemID
where inv.CharID = @CharID and item.RefItemID = @ItemID and item.Serial64 >0)
update SHARD.dbo._Items set Serial64 = 0 where ID64 in (SELECT TOP 5 Item.ID64 from SHARD.dbo._Items as Item
inner Join SHARD.dbo._Inventory as inv
on item.ID64 = inv.ItemID
where inv.CharID = @CharID and item.RefItemID = @ItemID and item.Serial64 >0)
exec SHARD.dbo._ADD_ITEM_EXTERN @Charname,@NewItemCode,1,0
end
end

add this to your shard.dbo._quest_manage procedure

Quote:

If @QuestID = 'Your quest id'
begin
exec _AeronQuestUpgrade @CharID , @QuestID
end

Aaron*

02/17/2015 01:46

push^^
@PortalDark you should write #Approved next time :p

KingDollar

02/17/2015 06:59

the idea of release is good

but the code is not good :)

as i see you are using a very basic way to code
you have a many mistakes in your code

well you have to train more and study as hard to reach a good level

but for a start - not bad

bestprem

02/17/2015 07:46

How many cheaters will cheat,,, :eek:

Storxy_

02/17/2015 17:04

Quote:

Originally Posted by Alexiuns* (Post 31060268)

first bug with this code
(the weapons must be with the same status)

Exo	02/17/2015 17:35

Well it won't even work since you never assigned a value to @OldItemCode xD

Aaron*

02/17/2015 18:40

whoever is going to use it, he should fix some mistakes in it. of course im not going to release my full work that took some of my time^

Crue*

02/18/2015 02:14

Quote:

Originally Posted by Aeron* (Post 31065398)

whoever is going to use it, he should fix some mistakes in it. of course im not going to release my full work that took some of my time^

so why you release something not even work ?

Aaron*

02/18/2015 15:45

Okay i have fixed the procedure now, you are able to use it without fixing it.
haters should stop hating now.

bestprem

02/18/2015 16:27

Quote:

Originally Posted by Aeron* (Post 31073196)

Okay i have fixed the procedure now, you are able to use it without fixing it.
haters should stop hating now.

so put this sh!t at ur new server, if u did i promise i will play sro again specially for you even if

Exo	02/18/2015 17:34

PHP Code:


		
			
If @OldItemCode = 'ITEM_EU_TSTAFF_10_B_RARE'
begin
set @NewItemCode = 'ITEM_EU_TSTAFF_10_C_RARE'
end
else if @OldItemCode = 'ITEM_EU_TSWORD_10_B_RARE'
begin
set @NewItemCode = 'ITEM_EU_TSWORD_10_C_RARE'
end

TIP: This check is useless since @OldItemCode was not assigned.

And since the two variables Old/New has no values then the whole thing won't work.

TheRunner

02/18/2015 21:15

Thanks for Share it

Royalblade*

02/18/2015 21:17

Utter bullshit in its' purest form. Welcome to stupidity.

May I ask where you are from Aeron?

Oh and you can do this in one line. Well two lines if you put an IF statement.

ILowe

02/18/2015 21:32

item slot ?

Bocc1337

02/19/2015 01:47

Well i tried it and it works perfect for me...

ersers2

02/19/2015 08:24

Thank you for this release! But let me just mention:

this statement will not work because @OldItemCode has not been initialized

PHP Code:


		
			
Select @ItemID = (Select ID From SHARD.dbo._RefObjCommon Where CodeName128=@OldItemCode)

this 2nd statement will not work because @NewItemCode has not been initialized

PHP Code:


		
			
exec SHARD.dbo._ADD_ITEM_EXTERN @Charname,@NewItemCode,1,0

Exo	02/19/2015 10:25

^The whole thing won't work*

Writing the whole thing in 3 lines is easier than reading and fixing it.

Syloxx

02/19/2015 18:26

Its a really nice idea but a bad implementation...

i was bored for like 10 mins so i started to rewrite the system but i become busy / lazy so i stopped here but maybe you can make something useful with it

[Only registered and activated users can see links. Click Here To Register...]

-Syloxx

Aaron*

02/20/2015 01:57

Quote:

Originally Posted by Syloxx (Post 31084857)

If you read the difference between mine and yours you will find out that mine depends on quest while yours depends on a scroll.
btw the one who depends on a scroll is old and many ppl have it now since it is in one of the shard databases.
but here is a copy of it written by me->

Quote:

If @operation = 41 and @ItemRefID = Scroll id from objcom
begin
Declare @ReqOptLvl int = '9'

Declare @OldIcode varchar(MAX)
declare @NewIcode varchar(MAX)
Select @NewIid int = (select id from shard.._Refobjcommon where codename128 like @NewIcode)
declare @OldIid int = (select id from shard.._refobjcommon where codename128 like @OldIcode)
declare @ItemsID int = (select id64 from shard.._items it join shard.._inventory inv on it.id64 = inv.itemid where inv.charid = @charid and inv.slot = 13 and it.serial64 >0 and it.optlevel >= @ReqOptLevel and it.refitemid = @oldiid)
select @OldIcode = codename128 from shard.._refobjcommon
select @NewIcode = codename128 from shard.._refobjcommon
If @OldIcode = 'ITEM_EU_TSTAFF_10_B_RARE'
begin
set @NewIcode = 'ITEM_EU_TSTAFF_10_C_RARE'
end
declare @ItemReplacer int = (select refitemid from shard.._items it join _inventory inv on it.id64 = inv.itemid where it.optlevel >= @ReqOptLevel and inv.slot = 13 and inv.charid = @charid and it.serial64 > 0 and it.refitemid = @OldIid)
if @ItemReplacer = @OldIid
begin
update sro_vt_shard.._items set RefItemID = @NewIid where id64 like @ItemsID and Refitemid like @OldIid
end

these lines should be added in _AddLogItem in log db
I never tested it but i think it will work

Exo	02/20/2015 02:31

Ques....WHAT???

Syloxx

02/20/2015 06:05

@Aeron* just some nice tips:

-If you do it related to quests then its abusable becuase a quest can't instant teleport you.
-Your scroll thing is related on equipment only and optlevel while your "quest" thing is for item count (equip only aswell)
-Even your scroll upgrade system is abusable...

anyway... i continue my query a bit... but for the add item part i'm 2 lazy because there are so many options like:

reqitem = equip | newitem != equip
reqitem = equip | newitem = equip
reqitem != equip | newitem != equip
reqitem != equip | newitem = equip

and yes it matters for adding the "newitemcount"

Code:

CREATE PROCEDURE _UpgradeSystem

  @intScrollID int

, @intCharID int

AS

SET NOCOUNT ON

SET XACT_ABORT ON



DECLARE          @intReturnValue int

                , @intReqItemID int

                , @intReqItemCount int

                , @intNewItemID int

                , @intNewItemCount int

                , @bitIsEquip bit



IF XACT_STATE() = -1

BEGIN

        SET @intReturnValue = 1

        GOTO ErrorHandler

END



BEGIN TRY

        SELECT          @intReqItemID = IT.RefItemID

        FROM          _Inventory INV WITH (NOLOCK)

        JOIN          _Items IT WITH (NOLOCK)

        ON                  INV.ItemID = IT.ID64

        WHERE          INV.Slot = 13

        AND                  INV.CharID = @intCharID



        IF @intReqItemID EXISTS IN (SELECT ReqItemID FROM _UpgradeItemPool WITH (NOLOCK))

        BEGIN

                SELECT          @intReqItemCount = ReqItemCount

                                , @intNewItemID = NewItemID

                                , @intNewItemCount = NewItemCount

                                , @bitIsEquip = IsEquip

                FROM          _UpgradeItemPool

                WHERE          ReqItemID = @intReqItemID

        END

        ELSE

        BEGIN

                SET @intReturnValue = 100

                GOTO ErrorHandler

        END



        BEGIN TRANSACTION



        IF @bitIsEquip = 1

        BEGIN

                SELECT          *

                FROM          _Inventory INV WITH (NOLOCK)

                JOIN          _Items IT WITH (NOLOCK)

                ON                  INV.ItemID = IT.ID64

                WHERE          INV.Slot BETWEEN 13 AND 13 + @intReqItemCount

                AND                  IT.RefItemID = @intReqItemCount

                AND                  INV.CharID = @intCharID



                IF @@ROWCOUNT = @intReqItemCount

                BEGIN

                        UPDATE          ITP

                        SET                  InUse = 0

                        FROM          _ItemPool ITP WITH (NOLOCK)

                        JOIN          _Inventory INV WITH (NOLOCK)

                        ON                  ITP.ItemID = INV.ItemID

                        WHERE          INV.Slot BETWEEN 13 AND 13 + @intReqItemCount

                        AND                  INV.CharID = @intCharID



                        UPDATE          _Inventory

                        SET                  ItemID = 0

                        WHERE          Slot BETWEEN 13 AND 13 + @intReqItemCount

                        AND                  CharID = @intCharID

                END



                ELSE

                BEGIN

                        SET @intReturnValue = 101

                        GOTO ErrorHandler

                END

        END



        ELSE

        BEGIN

                DECLARE @intCurItemCount int



                SELECT          @intCurItemCount = IT.Data

                FROM          _Items IT WITH (NOLOCK)

                JOIN          _Inventory INV WITH (NOLOCK)

                ON                  IT.ID64 = INV.ItemID

                WHERE          INV.Slot = 13

                AND                  INV.CharID = @intCharID



                IF @intCurItemCount = @intReqItemCount

                BEGIN

                        UPDATE          ITP

                        SET                  InUse = 0

                        FROM          _ItemPool ITP WITH (NOLOCK)

                        JOIN          _Inventory INV WITH (NOLOCK)

                        ON                  ITP.ItemID = INV.ItemID

                        WHERE          INV.Slot = 13

                        AND                  INV.CharID = @intCharID



                        UPDATE          _Inventory

                        SET                  ItemID = 0

                        WHERE          Slot = 13

                        AND                  CharID = @intCharID

                END



                ELSE IF @intCurItemCount > @intReqItemCount

                BEGIN

                        UPDATE          IT

                        SET                  Data -= @intReqItemCount

                        FROM          _Items IT WITH (NOLOCK)

                        JOIN          _Inventory INV WITH (NOLOCK)

                        ON                  IT.ID64 = INV.ItemID

                        WHERE          INV.Slot = 13

                        AND                  INV.CharID = @intCharID

                END



                ELSE

                BEGIN

                        SET @intReturnValue = 102

                        GOTO ErrorHandler

                END

        END

END TRY

ersers2

02/21/2015 07:08

Wow, this actually get's complicated the deeper you get into it. I've noticed all the additional logic you've added. Syloxx, are the lastthief and others right that this should be done in 2 or 3 extremely long lines of SQL code? Or does this way offer more benefits. Maybe because this concept is quest based or scroll based, and the classic _AddLogItem reacts to the teleport eventID.

Royalblade*

02/21/2015 12:47

It still doesn't get complicated.

I don't know what you guys are all smoking, but those things can still be done in a single update.

Syloxx made the first post better but killed performance again, entirely.

It's rather funny how people still don't understand shit about actual database management.

You all go n learn SQL; but it really shows that essential skills are lacking when ya'll write this simple item update sp.

Quote:

Originally Posted by ersers2 (Post 31099276)

Syloxx, are the lastthief and others right that this should be done in 2 or 3 extremely long lines of SQL code? Or does this way offer more benefits

Shorter, same function, faster, less "bugs", since its literally a line, less to write. More brainwork though.

VS

Longer, same function, more bugs, slow as shit, lot more work to write. Less brainwork.

Decide for yourself^^.
And all these people are still failling at it haha

Muhab*

02/21/2015 17:07

That's how i wrote it fastly... it's good enough , and it can be optimized more cuz i'm still not good with sql at all.

Spoiler:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [_Upgrade]
@CharID int
AS

declare @ReqItemID int = (SELECT RefItemID FROM SRO_VT_SHARD.._Items JOIN SRO_VT_SHARD.._Inventory on ID64 = ItemID and CharID = @CharID and Slot = 13)
IF((SELECT COUNT(RefItemID) FROM SRO_VT_SHARD.._Items JOIN SRO_VT_SHARD.._Inventory on ID64 = ItemID and CharID = @CharID and RefItemID = @ReqItemID) >= 5)
UPDATE _Items SET
_Items.RefItemID = b.ID
--You can reset item stats/blue/plus here..
FROM SRO_VT_SHARD.._Items _Items
JOIN SRO_VT_SHARD.._Inventory _Inventory on _Items.ID64 = _Inventory.ItemID and _Inventory.Slot = 13 and CharID = @CharID
JOIN SRO_VT_SHARD.._RefObjCommon a on a.Service = 1 and a.ID = _Items.RefItemID and a.CodeName128 like '%[_]12[_]%%B[_]RARE%'
JOIN SRO_VT_SHARD.._RefObjCommon b on b.Service = 1 and a.TypeID1 = b.TypeID1 and a.TypeID2 = b.TypeID2 and a.TypeID3 = b.TypeID3 and a.TypeID4 = b.TypeID4
and b.CodeName128 like '%[_]12[_]%%C[_]RARE%'
UPDATE _Inventory SET
_Inventory.ItemID = 0
FROM SRO_VT_SHARD.._Inventory _Inventory
JOIN (SELECT TOP 4 ID64 FROM SRO_VT_SHARD.._Items a JOIN SRO_VT_SHARD.._Inventory b on a.RefItemID = @ReqItemID and a.ID64 = b.ItemID and b.CharID = @CharID) asd
on _Inventory.ItemID = asd.ID64

I prefer to assign ItemClass from _RefObjItem than using CodeName128 that's in case you haven't created any similar items.

OR
I do prefer this way.

Spoiler:

USE [SRO_VT_HOBA]
GO

/****** Object: Table [dbo].[_Upgarde] Script Date: 02/21/2015 17:51:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_Upgarde](
[ReqItem] [int] NOT NULL,
[NewItem] [int] NOT NULL
) ON [PRIMARY]

GO

Spoiler:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [_Upgrade]
@CharID int
AS

declare @ReqItemID int,@NewItemID int
SELECT @ReqItemID = ReqItem, @NewItemID = NewItem FROM SRO_VT_HOBA.._Upgarde
declare @CharName16 varchar(16) = (SELECT CharName16 FROM SRO_VT_SHARD.._Char WHERE CharID = @CharID),
@CodeName128 varchar(129) = (SELECT CodeName128 FROM SRO_VT_SHARD.._RefObjCommon WHERE ID = @NewItemID)
IF((SELECT COUNT(RefItemID) FROM SRO_VT_SHARD.._Items JOIN SRO_VT_SHARD.._Inventory on ID64 = ItemID and CharID = @CharID and RefItemID = @ReqItemID) >= 5)
UPDATE _Inventory SET
_Inventory.ItemID = 0
FROM SRO_VT_SHARD.._Inventory _Inventory
JOIN (SELECT TOP 5 ID64 FROM SRO_VT_SHARD.._Items a JOIN SRO_VT_SHARD.._Inventory b on a.RefItemID = @ReqItemID and a.ID64 = b.ItemID and b.CharID = @CharID) asd
on _Inventory.ItemID = asd.ID64
exec SRO_VT_SHARD.._ADD_ITEM_EXTERN @CharName16,@CodeName128,1,0

P.S. I haven't tested them but they should work. ;)

ILowe

03/02/2015 21:06

Quote:

Originally Posted by Muhab Ashraf (Post 31103424)

That's how i wrote it fastly... it's good enough , and it can be optimized more cuz i'm still not good with sql at all.

Spoiler:

I prefer to assign ItemClass from _RefObjItem than using CodeName128 that's in case you haven't created any similar items.

OR
I do prefer this way.

Spoiler:

P.S. I haven't tested them but they should work. ;)

log ?

norulez13

03/03/2015 15:28

usefull

Dewsbury*

06/14/2016 15:23

there's new way to upgrade weapon --_-- try to create upgrade scroll with producer can choose weapons stats from table lel..

All times are GMT +2. The time now is 19:38.