[Release] Quest Upgrade System

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

-Syloxx

Aaron* · 02/20/2015, 01:57

Quote:

Originally Posted by Syloxx

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

-Syloxx

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 **** 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

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 ****, 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

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.

ILowe · 03/02/2015, 21:06

Quote:

Originally Posted by Muhab Ashraf

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.

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..