[RELEASE] Find Item by Serial
I want to release something useful for all Silkroad administrators, so here is my "Find Item by Serial" Query / Stored Procedure.
Query:
Code:
USE SRO_VT_SHARD
GO
--//////////FindItemBySerial V1.3 By Syloxx\\\\\\\\\
DECLARE @serial BIGINT = YOUR_SERIAL_HERE,
@ItemID INT,
@ItemCodeName VARCHAR(128)
SELECT @ItemCodeName = C.CodeName128 ,
@ItemID = I.ID64
FROM _RefObjCommon C
INNER JOIN _Items I
ON C.ID = I.RefItemID
WHERE @serial = I.Serial64
--###############################################
--##### CHECK IF SERIAL IS VALID #####
--###############################################
IF @ItemID IS NULL OR @ItemID = 0
BEGIN
PRINT 'No item with the specified Serial found!'
END
--###############################################
--##### CHECK IF ITEM IS IN INVENTORY #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID from _Inventory))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Inventory' AS 'Storage',
INV.Slot
FROM _Items IT
INNER JOIN _Inventory INV
ON INV.ItemID = IT.ID64
INNER JOIN _Char C
ON C.CharID = INV.CharID
INNER JOIN _User U
ON U.CharID = C.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @serial
END
--###############################################
--##### CHECK IF ITEM IS IN STORAGE #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _Chest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Storage' AS 'Storage',
SINV.Slot
FROM _Items IT
INNER JOIN _Chest SINV
ON SINV.ItemID = IT.ID64
INNER JOIN _User U
ON U.UserJID = SINV.UserJID
INNER JOIN _Char C
ON C.CharID = U.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @serial
END
--###############################################
--##### CHECK IF ITEM IS IN PET #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
BEGIN
DECLARE @cosserial BIGINT,
@CoSItemID INT,
@CoSCodeName VARCHAR(128)
SELECT CCOS.ID AS 'COSID',
ROC.CodeName128 AS 'COS CodeName',
CCOS.CharName AS 'COS Name',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'COS Inventory' AS 'Storage',
CINV.Slot
FROM _Items IT
INNER JOIN _InvCOS CINV
ON CINV.ItemID = IT.ID64
INNER JOIN _CharCOS CCOS
ON CCOS.ID = CINV.COSID
INNER JOIN _Items COSIT
ON COSIT.Data = CCOS.ID
INNER JOIN _RefObjCommon ROC
ON ROC.ID = COSIT.RefItemID
WHERE IT.Serial64 = @serial
AND ROC.TypeID2=2
AND ROC.TypeID3=1
AND ROC.TypeID4=2
SELECT @cosserial = COSIT.Serial64,
@CoSItemID = COSIT.ID64,
@CoSCodeName = ROC.CodeName128
FROM _Items IT
INNER JOIN _InvCOS CINV
ON CINV.ItemID = IT.ID64
INNER JOIN _CharCOS CCOS
ON CCOS.ID = CINV.COSID
INNER JOIN _Items COSIT
ON COSIT.Data = CCOS.ID
INNER JOIN _RefObjCommon ROC
ON ROC.ID = COSIT.RefItemID
WHERE IT.Serial64 = @serial
AND ROC.TypeID2=2
AND ROC.TypeID3=1
AND ROC.TypeID4=2
--##############################################
--##### CHECK IF PET IS IN INVENTORY #####
--##############################################
IF @CoSItemID IN (SELECT ItemID from _Inventory))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@CoSCodeName AS 'COS CodeName',
IT.OptLevel,
'Inventory' AS 'Storage',
INV.Slot
FROM _Items IT
INNER JOIN _Inventory INV
ON INV.ItemID = IT.ID64
INNER JOIN _Char C
ON C.CharID = INV.CharID
INNER JOIN _User U
ON U.CharID = C.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @cosserial
END
--###############################################
--##### CHECK IF ITEM IS IN STORAGE #####
--###############################################
ELSE IF @CoSItemID IN (SELECT ItemID FROM _Chest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@CoSCodeName AS 'COS CodeName',
IT.OptLevel,
'Storage' AS 'Storage',
SINV.Slot
FROM _Items IT
INNER JOIN _Chest SINV
ON SINV.ItemID = IT.ID64
INNER JOIN _User U
ON U.UserJID = SINV.UserJID
INNER JOIN _Char C
ON C.CharID = U.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @cosserial
END
--##############################################
--##### CHECK IF PET IS IN GUILD STORAGE #####
--##############################################
ELSE IF @CoSItemID IN (SELECT ItemID FROM _GuildChest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
GM.CharID,
GM.CharName AS 'GuildMaster',
G.ID AS 'GuildID',
G.Name AS 'GuildName',
@CoSCodeName AS 'COS CodeName',
IT.OptLevel,
'Guild Storage' AS 'Storage',
GINV.Slot
FROM _Items IT
INNER JOIN _GuildChest GINV
ON GINV.ItemID = IT.ID64
INNER JOIN _Guild G
ON G.ID = GINV.GuildID
INNER JOIN _GuildMember GM
ON GM.GuildID = G.ID
INNER JOIN _User U
ON U.CharID = GM.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @cosserial
AND GM.MemberClass = 0
END
END
--###############################################
--##### CHECK IF ITEM IS IN GUILD STORAGE #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _GuildChest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
GM.CharID,
GM.CharName AS 'GuildMaster',
G.ID AS 'GuildID',
G.Name AS 'GuildName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Guild Storage' AS 'Storage',
GINV.Slot
FROM _Items IT
INNER JOIN _GuildChest GINV
ON GINV.ItemID = IT.ID64
INNER JOIN _Guild G
ON G.ID = GINV.GuildID
INNER JOIN _GuildMember GM
ON GM.GuildID = G.ID
INNER JOIN _User U
ON U.CharID = GM.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @serial
AND GM.MemberClass = 0
END
Procedure:
Code:
USE SRO_VT_SHARD
GO
CREATE PROCEDURE _FindItemBySerial
@serial BIGINT
AS
--//////////FindItemBySerial V1.3 By Syloxx\\\\\\\\\
DECLARE @ItemID INT,
@ItemCodeName VARCHAR(128)
SELECT @ItemCodeName = C.CodeName128 ,
@ItemID = I.ID64
FROM _RefObjCommon C
INNER JOIN _Items I
ON C.ID = I.RefItemID
WHERE @serial = I.Serial64
--###############################################
--##### CHECK IF SERIAL IS VALID #####
--###############################################
IF @ItemID IS NULL OR @ItemID = 0
BEGIN
PRINT 'No item with the specified Serial found!'
END
--###############################################
--##### CHECK IF ITEM IS IN INVENTORY #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID from _Inventory))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Inventory' AS 'Storage',
INV.Slot
FROM _Items IT
INNER JOIN _Inventory INV
ON INV.ItemID = IT.ID64
INNER JOIN _Char C
ON C.CharID = INV.CharID
INNER JOIN _User U
ON U.CharID = C.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @serial
END
--###############################################
--##### CHECK IF ITEM IS IN STORAGE #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _Chest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Storage' AS 'Storage',
SINV.Slot
FROM _Items IT
INNER JOIN _Chest SINV
ON SINV.ItemID = IT.ID64
INNER JOIN _User U
ON U.UserJID = SINV.UserJID
INNER JOIN _Char C
ON C.CharID = U.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @serial
END
--###############################################
--##### CHECK IF ITEM IS IN PET #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _InvCOS))
BEGIN
DECLARE @cosserial BIGINT,
@CoSItemID INT,
@CoSCodeName VARCHAR(128)
SELECT CCOS.ID AS 'COSID',
ROC.CodeName128 AS 'COS CodeName',
CCOS.CharName AS 'COS Name',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'COS Inventory' AS 'Storage',
CINV.Slot
FROM _Items IT
INNER JOIN _InvCOS CINV
ON CINV.ItemID = IT.ID64
INNER JOIN _CharCOS CCOS
ON CCOS.ID = CINV.COSID
INNER JOIN _Items COSIT
ON COSIT.Data = CCOS.ID
INNER JOIN _RefObjCommon ROC
ON ROC.ID = COSIT.RefItemID
WHERE IT.Serial64 = @serial
AND ROC.TypeID2=2
AND ROC.TypeID3=1
AND ROC.TypeID4=2
SELECT @cosserial = COSIT.Serial64,
@CoSItemID = COSIT.ID64,
@CoSCodeName = ROC.CodeName128
FROM _Items IT
INNER JOIN _InvCOS CINV
ON CINV.ItemID = IT.ID64
INNER JOIN _CharCOS CCOS
ON CCOS.ID = CINV.COSID
INNER JOIN _Items COSIT
ON COSIT.Data = CCOS.ID
INNER JOIN _RefObjCommon ROC
ON ROC.ID = COSIT.RefItemID
WHERE IT.Serial64 = @serial
AND ROC.TypeID2=2
AND ROC.TypeID3=1
AND ROC.TypeID4=2
--##############################################
--##### CHECK IF PET IS IN INVENTORY #####
--##############################################
IF @CoSItemID IN (SELECT ItemID from _Inventory))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@CoSCodeName AS 'COS CodeName',
IT.OptLevel,
'Inventory' AS 'Storage',
INV.Slot
FROM _Items IT
INNER JOIN _Inventory INV
ON INV.ItemID = IT.ID64
INNER JOIN _Char C
ON C.CharID = INV.CharID
INNER JOIN _User U
ON U.CharID = C.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @cosserial
END
--###############################################
--##### CHECK IF ITEM IS IN STORAGE #####
--###############################################
ELSE IF @CoSItemID IN (SELECT ItemID FROM _Chest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
C.CharID,
C.CharName16 AS 'CharName',
@CoSCodeName AS 'COS CodeName',
IT.OptLevel,
'Storage' AS 'Storage',
SINV.Slot
FROM _Items IT
INNER JOIN _Chest SINV
ON SINV.ItemID = IT.ID64
INNER JOIN _User U
ON U.UserJID = SINV.UserJID
INNER JOIN _Char C
ON C.CharID = U.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @cosserial
END
--##############################################
--##### CHECK IF PET IS IN GUILD STORAGE #####
--##############################################
ELSE IF @CoSItemID IN (SELECT ItemID FROM _GuildChest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
GM.CharID,
GM.CharName AS 'GuildMaster',
G.ID AS 'GuildID',
G.Name AS 'GuildName',
@CoSCodeName AS 'COS CodeName',
IT.OptLevel,
'Guild Storage' AS 'Storage',
GINV.Slot
FROM _Items IT
INNER JOIN _GuildChest GINV
ON GINV.ItemID = IT.ID64
INNER JOIN _Guild G
ON G.ID = GINV.GuildID
INNER JOIN _GuildMember GM
ON GM.GuildID = G.ID
INNER JOIN _User U
ON U.CharID = GM.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @cosserial
AND GM.MemberClass = 0
END
END
--###############################################
--##### CHECK IF ITEM IS IN GUILD STORAGE #####
--###############################################
ELSE IF (@ItemID IN (SELECT ItemID FROM _GuildChest))
BEGIN
SELECT TBU.JID AS 'UserJID',
TBU.StrUserID AS 'UserName',
GM.CharID,
GM.CharName AS 'GuildMaster',
G.ID AS 'GuildID',
G.Name AS 'GuildName',
@ItemCodeName AS 'Item CodeName',
IT.OptLevel,
'Guild Storage' AS 'Storage',
GINV.Slot
FROM _Items IT
INNER JOIN _GuildChest GINV
ON GINV.ItemID = IT.ID64
INNER JOIN _Guild G
ON G.ID = GINV.GuildID
INNER JOIN _GuildMember GM
ON GM.GuildID = G.ID
INNER JOIN _User U
ON U.CharID = GM.CharID
INNER JOIN SRO_VT_ACCOUNT..TB_User TBU
ON TBU.JID = U.UserJID
WHERE IT.Serial64 = @serial
AND GM.MemberClass = 0
END
Execute the procedure:
Code:
USE SRO_VT_SHARD
GO
exec _FindItemBySerial YOUR_SERIAL_HERE
Inventory:
http://puu.sh/b2s1P/0b9cce49d2.png
Storage:
http://puu.sh/b2s5P/5863923e9a.png
COS Inventory:
http://puu.sh/b2YLD/290d69e74f.png
Guild Storage:
http://puu.sh/b2sed/7f9aa156d8.png
Re: [RELEASE] Find Item by Serial
Re: [RELEASE] Find Item by Serial
really thanks
and i hope the items in pet works fine :):
Re: [RELEASE] Find Item by Serial
Updated Pet!
Pet works now even if the pet is in Storage or Guild Storage
Re: [RELEASE] Find Item by Serial
its for twsro files
so i found it usless since the files arent public
and who got them can only do it
so next them you post such usefull staff make sure its for files everyone using
otherwise dont post it
Re: [RELEASE] Find Item by Serial
Quote:
Originally Posted by
CrystalCoder
its for twsro files
so i found it usless since the files arent public
and who got them can only do it
so next them you post such usefull staff make sure its for files everyone using
otherwise dont post it
whats about testing be4 u write something like that
this query is working for ANY files
Re: [RELEASE] Find Item by Serial
maybe works for any files, but ofc you need to edit it for that
not all dbs are named SRO_TW_SHARD, and maybe another small modifications
Re: [RELEASE] Find Item by Serial
I think I can assume that everyone knows how to change a db name in a query ;)
€:
V1.2 Changelog
-added missing "END"
-changed SHARD DB from sro_tw_shard to SRO_VT_SHARD
-changed ACCOUNTDB from sro_tw_accountdb to SRO_VT_ACCOUNT
V1.3 Changelog
-optimized query
Re: [RELEASE] Find Item by Serial
Quote:
Originally Posted by
Syloxx
I think I can assume that everyone knows how to change a db name in a query ;)
believe me.. no .. they dont.... never... not in this life.
many.. many .. MANY are simply copy & paste "devs"...
Re: [RELEASE] Find Item by Serial
Quote:
Originally Posted by
CrystalCoder
its for twsro files
so i found it usless since the files arent public
and who got them can only do it
so next them you post such usefull staff make sure its for files everyone using
otherwise dont post it
CrystalCoder?
are you sure?