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