عايز كيورى لمعرفة الايتمز موجوده مع مين عن طريق سيريال الايتمز

[McDiesel]

اتفضل

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