vSRO / Find & Destroy Item Query

[ßyMesMes]

HERAKLES Otomatik Avlı kalıcı sunucu. 19 Haziran'da açılıyor. Atius & Wizard güvencesiyle hemen kayıt ol, ön kayıt ödülleri aktif. HEMEN TIKLA!

Hepinizin bildiği gibi oyuncu üzerinde itemleri rahatlıkla gerek smc olsun gerek query olsun rahatlıkla görebiliyoruz. Araştırmalarım sonucunda bu konuda eksiklik olduğunu farkettim. Mesela diyelim ki bir durum yaşadınız ve sadece bir oyuncudan item silmeniz gerekiyor. Yalnız oyuncu bu item'i pet'e koyup pet'ide guild bankasına koyduğu zaman bunu bulmak o kadarda kolay değil. İşte yazdığım query ile oyuncu item'i nereye koyarsa koysun hem kontrol edip hemde itemin seri kodundan o itemi direk silebiliyorsunuz ve oyuncunun oyundan çıkış yapması gibi bir durum gerekmiyor.
​

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

 

[XxZeMaHSeRxX-Turkmmo]

Teşekkürler