USE SRO_VT_SHARD
DECLARE @Charname VARCHAR(64)
DECLARE @Type TINYINT
DECLARE @DeepType TINYINT
DECLARE @Destroy TINYINT
DECLARE @ItemSerialCode BIGINT
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
SET @Charname='Senua'
SET @Destroy=0 /*** #Destroy=0 means do not destroy, #Destroy=1 means destroy ***/
SET @ItemSerialCode=72339069016478389 /*** Destroyed ItemSerialCode ***/
SET @Type=4 /*** #Inventory-1 #Stroage-2 #GuildStroage-3 #GrapPet-4 ***/
SET @DeepType=1 /*** For @Type=4 #Inventory-1 #Stroage-2 #GuildStroage-3 ***/
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF(@Destroy=1)
BEGIN
UPDATE V1 SET V1.ItemID=0
FROM _Char AS C1
INNER JOIN _Inventory AS V1 ON V1.CharID=C1.CharID AND C1.CharName16=@Charname
INNER JOIN _Items AS I1 ON V1.ItemID=I1.ID64 AND I1.Serial64=@ItemSerialCode
UPDATE CH1 SET CH1.ItemID=0
FROM _Char AS C1
INNER JOIN _User AS U1 ON U1.CharID=C1.CharID AND C1.CharName16=@Charname
INNER JOIN _Chest AS CH1 ON U1.CharID=C1.CharID AND CH1.UserJID=U1.UserJID
INNER JOIN _Items AS I1 ON CH1.ItemID=I1.ID64 AND I1.Serial64=@ItemSerialCode
UPDATE GC1 SET GC1.ItemID=0
FROM _Char AS C1
INNER JOIN _GuildChest AS GC1 ON C1.GuildID=GC1.GuildID AND C1.CharName16=@Charname
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _Items AS I1 ON GC1.ItemID=I1.ID64 AND I1.Serial64=@ItemSerialCode
UPDATE IC1 SET IC1.ItemID=0
FROM _Inventory AS V1
INNER JOIN _Char AS C1 ON V1.CharID=C1.CharID AND C1.CharName16=@Charname
INNER JOIN _Items AS I1 ON V1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M2 ON I1.RefItemID=M2.ID AND M2.TypeID1=3 AND M2.TypeID2=2 AND M2.TypeID3=1 AND M2.TypeID4=2 AND M2.ID!=0
LEFT JOIN _CharCOS AS CC1 ON CC1.ID=I1.Data
INNER JOIN _InvCOS AS IC1 ON IC1.COSID = CC1.ID
INNER JOIN _Items AS I2 ON IC1.ItemID=I2.ID64 AND I2.Serial64=@ItemSerialCode
UPDATE IC1 SET IC1.ItemID=0
FROM _Char AS C1
INNER JOIN _User AS U1 ON U1.CharID=C1.CharID AND C1.CharName16=@Charname
INNER JOIN _Chest AS CH1 ON U1.CharID=C1.CharID AND CH1.UserJID=U1.UserJID
INNER JOIN _Items AS I1 ON CH1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M2 ON I1.RefItemID=M2.ID AND M2.TypeID1=3 AND M2.TypeID2=2 AND M2.TypeID3=1 AND M2.TypeID4=2 AND M2.ID!=0
LEFT JOIN _CharCOS AS CC1 ON CC1.ID=I1.Data
INNER JOIN _InvCOS AS IC1 ON IC1.COSID = CC1.ID
INNER JOIN _Items AS I2 ON IC1.ItemID=I2.ID64 AND I2.Serial64=@ItemSerialCode
UPDATE IC1 SET IC1.ItemID=0
FROM _Char AS C1
INNER JOIN _GuildChest AS GC1 ON C1.GuildID=GC1.GuildID AND C1.CharName16=@Charname
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _Items AS I1 ON GC1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M2 ON I1.RefItemID=M2.ID AND M2.TypeID1=3 AND M2.TypeID2=2 AND M2.TypeID3=1 AND M2.TypeID4=2 AND M2.ID!=0
LEFT JOIN _CharCOS AS CC1 ON CC1.ID=I1.Data
INNER JOIN _InvCOS AS IC1 ON IC1.COSID = CC1.ID
INNER JOIN _Items AS I2 ON IC1.ItemID=I2.ID64 AND I2.Serial64=@ItemSerialCode
UPDATE _ItemPool SET InUse = 0 WHERE ItemID=(SELECT ID64 FROM _Items WHERE Serial64=@ItemSerialCode)
UPDATE _Items SET Serial64 = 0 WHERE Serial64=@ItemSerialCode
END
IF(@Type=1)
BEGIN
SELECT
C1.CharID,
C1.CharName16 AS CharName,
G1.Name AS Guild,
V1.Slot,
I1.RefItemID,
M1.CodeName128,
I1.OptLevel,
V1.ItemID,
I1.Serial64 AS ItemSerialCode
FROM _Inventory AS V1
INNER JOIN _Char AS C1 ON V1.CharID=C1.CharID
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _Items AS I1 ON V1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M1 ON I1.RefItemID=M1.ID AND M1.ID!=0
WHERE C1.CharName16=@Charname
ORDER BY C1.CharName16 ASC,V1.Slot ASC;
END
IF(@Type=2)
BEGIN
SELECT
C1.CharID,
C1.CharName16 AS CharName,
G1.Name AS Guild,
CH1.Slot,
I1.RefItemID,
M1.CodeName128,
I1.OptLevel,
CH1.ItemID,
I1.Serial64 AS ItemSerialCode
FROM _Char AS C1
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _User AS U1 ON U1.CharID=C1.CharID
INNER JOIN _Chest AS CH1 ON U1.CharID=C1.CharID AND CH1.UserJID=U1.UserJID
INNER JOIN _Items AS I1 ON CH1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M1 ON I1.RefItemID=M1.ID AND M1.ID!=0
WHERE C1.CharName16=@Charname
ORDER BY C1.CharName16 ASC,CH1.Slot ASC;
END
IF(@Type=3)
BEGIN
SELECT
C1.CharID,
C1.CharName16 AS CharName,
G1.Name AS Guild,
GC1.Slot,
I1.RefItemID,
M1.CodeName128,
I1.OptLevel,
GC1.ItemID,
I1.Serial64 AS ItemSerialCode
FROM _Char AS C1
INNER JOIN _GuildChest AS GC1 ON C1.GuildID=GC1.GuildID
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _Items AS I1 ON GC1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M1 ON I1.RefItemID=M1.ID AND M1.ID!=0
WHERE C1.CharName16=@Charname
ORDER BY C1.CharName16 ASC,GC1.Slot ASC;
END
IF(@Type=4)
BEGIN
IF(@DeepType=1)
BEGIN
SELECT
C1.CharID,
C1.CharName16 AS CharName,
G1.Name AS Guild,
CC1.ID AS CosID,
IC1.Slot,
I2.RefItemID,
M1.CodeName128,
I2.OptLevel,
IC1.ItemID,
I2.Serial64 AS ItemSerialCode
FROM _Inventory AS V1
INNER JOIN _Char AS C1 ON V1.CharID=C1.CharID
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _Items AS I1 ON V1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M2 ON I1.RefItemID=M2.ID AND M2.TypeID1=3 AND M2.TypeID2=2 AND M2.TypeID3=1 AND M2.TypeID4=2 AND M2.ID!=0
LEFT JOIN _CharCOS AS CC1 ON CC1.ID=I1.Data
INNER JOIN _InvCOS AS IC1 ON IC1.COSID = CC1.ID
INNER JOIN _Items AS I2 ON IC1.ItemID=I2.ID64
INNER JOIN _RefObjCommon AS M1 ON M1.ID=I2.RefItemID AND M1.ID!=0
WHERE C1.CharName16=@Charname
ORDER BY C1.CharName16 ASC,CC1.ID ASC, IC1.Slot ASC;
END
IF(@DeepType=2)
BEGIN
SELECT
C1.CharID,
C1.CharName16 AS CharName,
G1.Name AS Guild,
CC1.ID AS CosID,
IC1.Slot,
I2.RefItemID,
M1.CodeName128,
I2.OptLevel,
IC1.ItemID,
I2.Serial64 AS ItemSerialCode
FROM _Char AS C1
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _User AS U1 ON U1.CharID=C1.CharID
INNER JOIN _Chest AS CH1 ON U1.CharID=C1.CharID AND CH1.UserJID=U1.UserJID
INNER JOIN _Items AS I1 ON CH1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M2 ON I1.RefItemID=M2.ID AND M2.TypeID1=3 AND M2.TypeID2=2 AND M2.TypeID3=1 AND M2.TypeID4=2 AND M2.ID!=0
LEFT JOIN _CharCOS AS CC1 ON CC1.ID=I1.Data
INNER JOIN _InvCOS AS IC1 ON IC1.COSID = CC1.ID
INNER JOIN _Items AS I2 ON IC1.ItemID=I2.ID64
INNER JOIN _RefObjCommon AS M1 ON M1.ID=I2.RefItemID AND M1.ID!=0
WHERE C1.CharName16=@Charname
ORDER BY C1.CharName16 ASC,CC1.ID ASC, IC1.Slot ASC;
END
IF(@DeepType=3)
BEGIN
SELECT
C1.CharID,
C1.CharName16 AS CharName,
G1.Name AS Guild,
CC1.ID AS CosID,
IC1.Slot,
I2.RefItemID,
M1.CodeName128,
I2.OptLevel,
IC1.ItemID,
I2.Serial64 AS ItemSerialCode
FROM _Char AS C1
INNER JOIN _GuildChest AS GC1 ON C1.GuildID=GC1.GuildID
INNER JOIN _Guild AS G1 ON G1.ID=C1.GuildID
INNER JOIN _Items AS I1 ON GC1.ItemID=I1.ID64
INNER JOIN _RefObjCommon AS M2 ON I1.RefItemID=M2.ID AND M2.TypeID1=3 AND M2.TypeID2=2 AND M2.TypeID3=1 AND M2.TypeID4=2 AND M2.ID!=0
LEFT JOIN _CharCOS AS CC1 ON CC1.ID=I1.Data
INNER JOIN _InvCOS AS IC1 ON IC1.COSID = CC1.ID
INNER JOIN _Items AS I2 ON IC1.ItemID=I2.ID64
INNER JOIN _RefObjCommon AS M1 ON M1.ID=I2.RefItemID AND M1.ID!=0
WHERE C1.CharName16=@Charname
ORDER BY C1.CharName16 ASC,CC1.ID ASC, IC1.Slot ASC;
END
END