En Düzgün Dupe Check Prosedürü - (%100 Sorunsuz)

    Paylaş
    avatar
    Flexy

    Favori Oyun Favori Oyun : WolfTeam
    Nick Nick : Ko-PVP
    Karakter Karakter : Asas
    Irk Irk : El Morad
    Cinsiyet Cinsiyet : Erkek
    Mesaj Sayısı Mesaj Sayısı : 73
    Rep Puanı Rep Puanı : 1
    Kayıt tarihi Kayıt tarihi : 26/12/09
    Lakap Lakap : Asker

    En Düzgün Dupe Check Prosedürü - (%100 Sorunsuz)

    Mesaj tarafından Flexy Bir C.tesi Ara. 26, 2009 5:08 am

    Selamlar,

    Tamamiyle Kendi Yapımım Sadece Inventory leri Taramak İçin Yaptım Sizlerlede Paylaşayım Dedim

    Eğer Girişte Tarama Yapacaksanız LOAD_USER_DATA ya Aşağıdaki Şekilde Ekleyiniz.


    Kod:
    EXEC DUPE_CHECK_USER @id

    Tabiki Tavsiyem Tüm Kullanıcıları Uyarıp Serverı Kapayıp Tüm Innleri Temizleyip Aşağıdaki Kodu Yazmanızdır.

    NOT : LOAD_USER_DATA Taramasını Tavsiye Etmem Neden Derseniz Kişinin Örnek CS si Var Yan Çarına Verdi Oda Dupe Görünür Server Tamamını Tarayan Bir Sistem Olduğundan En Mantıklısı Reset Zamanlarında veya Haftada Bir Kere 2. Prosedürü Çalıştırmanızdır.


    Kod:
    EXEC DUPE_CHECK_ALL_USERS

    Direk Prosedürleri Uygulayın Gerekli Tabloları Kendi Oluşturacaktır Merak Etmeyin.

    Silinen Dupe İtemleri DUPE_CHECK_REPORT Tablosundan Görebilirsiniz.

    1. Procedür

    Kod:
    CREATE PROCEDURE [dbo].[DUPE_CHECK_USER]
    (@strUserId char(21))
    AS
    /*
    Author : AKUMA
    Update : 11.03.2009 - 11:37
    */
    DECLARE @DupeDeletingSystemStatus tinyint
    /* 0 = Dupe Items Only Reporting Their Deletion / 1 = Dupe Items Deleted */
    SET @DupeDeletingSystemStatus = 1

    /* The required tables are created. */
    IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id(N'[dbo].[DUPE_CHECKED_USER_ITEM_LIST]') AND OBJECTPROPERTY(id,N'IsUserTable') = 1)
    CREATE TABLE [dbo].[DUPE_CHECKED_USER_ITEM_LIST](
    [strUserId] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Num] [int] NULL,
    [strSerial] [int] NULL,
    [IsDupe] [tinyint] NULL
    ) ON [PRIMARY]

    IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id(N'[dbo].[DUPE_ITEM_LIST]') AND OBJECTPROPERTY(id,N'IsUserTable') = 1)
    CREATE TABLE [dbo].[DUPE_ITEM_LIST](
    [strUserId] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Num] [int] NULL,
    [strSerial] [int] NULL
    ) ON [PRIMARY]

    IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id(N'[dbo].[DUPE_CHECK_USER_INVENTORY]') AND OBJECTPROPERTY(id,N'IsUserTable') = 1)
    CREATE TABLE [dbo].[DUPE_CHECK_USER_INVENTORY](
    [strUserId] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Num] [int] NULL,
    [StackSize] [smallint] NULL,
    [Durability] [smallint] NULL,
    [InventorySlot] [int] NULL,
    [strSerial] [int] NULL DEFAULT ((0))
    ) ON [PRIMARY]

    IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE id = object_id(N'[dbo].[DUPE_CHECK_REPORT]') AND OBJECTPROPERTY(id,N'IsUserTable') = 1)
    CREATE TABLE [dbo].[DUPE_CHECK_REPORT](
    [strUserId] [char](21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Num] [int] NULL,
    [strSerial] [int] NULL,
    [ItemName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    ) ON [PRIMARY]

    /* Variables */
    DECLARE @CheckUser int,@CheckDupeItemUser int,@While int,@I int,@Num int, @Durability int,@StackSize int,@InventorySlot int,@strSerial int,@RecordCount int, @CountX int,@CheckInn int, @Slot int
    DECLARE @strUserIDx char(21), @strName varchar(100), @DurabilityX varchar(2),@StackSizeX varchar(2),@WareHouseData varchar(1601),@StopX varchar(2)
    DECLARE @NumX varbinary(4),@strSerialX varbinary(4)
    DECLARE @DupedCount int

    /* Delete User Information are Old. */
    DELETE FROM DUPE_CHECKED_USER_ITEM_LIST WHERE strUserId = @strUserId

    DELETE FROM DUPE_CHECK_USER_INVENTORY WHERE strUserId = @strUserId

    SET @I = 14 * 0 + 1
    SET @While = 401

    SELECT @CheckUser = COUNT(*) FROM USERDATA WHERE strUserId = @strUserId

    IF @CheckUser > 0
    BEGIN

    -- Control Begins.

    SET @I = 14 * 0 + 1
    SET @While = 401
    SET @Num = 0
    SET @Durability = 0
    SET @StackSize = 0
    SET @strSerial = 0

    WHILE @I < @While
    BEGIN

    SET @Num = 0
    SET @Durability = 0
    SET @StackSize = 0
    SET @strSerial = 0

    SELECT
    @strUserId = strUserId,
    @Num = CAST(CAST(SUBSTRING(CAST(SUBSTRING(strItem, @I,4) AS VARBINARY(4)), 4, 1)+SUBSTRING(CAST(SUBSTRING(strItem, @I,4) AS VARBINARY(4)), 3, 1)+SUBSTRING(CAST(SUBSTRING(strItem, @I,4) AS VARBINARY(4)), 2, 1)+SUBSTRING(CAST(SUBSTRING(strItem, @I,4) AS VARBINARY(4)), 1, 1) AS VARBINARY(4)) AS INT),
    @Durability = CAST(CAST(CAST(SUBSTRING(strItem, @I+5, 1) AS VARBINARY(1))+CAST(SUBSTRING(strItem, @I+4, 1) AS VARBINARY(1)) AS VARBINARY(2)) AS INT),
    @StackSize = CAST(CAST(CAST(SUBSTRING(strItem, @I+7, 1) AS VARBINARY(1))+CAST(SUBSTRING(strItem, @I+6, 1) AS VARBINARY(1)) AS VARBINARY(2)) AS INT),
    @strSerial = CAST(CAST(SUBSTRING(CAST(SUBSTRING(strSerial, @I,4) AS VARBINARY(4)), 4, 1)+SUBSTRING(CAST(SUBSTRING(strSerial, @I,4) AS VARBINARY(4)), 3, 1)+SUBSTRING(CAST(SUBSTRING(strSerial, @I,4) AS VARBINARY(4)), 2, 1)+SUBSTRING(CAST(SUBSTRING(strSerial, @I,4) AS VARBINARY(4)), 1, 1) AS VARBINARY(4)) AS INT)
    FROM USERDATA
    WHERE strUserId = @strUserId

    IF @strSerial <> 0 AND @Num <> 0
    BEGIN
    INSERT INTO DUPE_CHECKED_USER_ITEM_LIST (strUserId,Num,strSerial) VALUES (@strUserId,@Num,@strSerial)
    END


    INSERT INTO DUPE_CHECK_USER_INVENTORY (strUserId,Num,StackSize,Durability,InventorySlot,strSerial) VALUES (@strUserId,@Num,@StackSize,@Durability,(@I-1) / 8,@strSerial)

    SET @I = @I + 8

    END

    -- Add items to list dupe.

    DECLARE DUPE_CURSOR CURSOR FAST_FORWARD FOR
    SELECT strSerial, COUNT(*) -1
    FROM DUPE_CHECKED_USER_ITEM_LIST WHERE strSerial <> 0 AND Num <> 0
    GROUP BY strSerial
    HAVING COUNT(strSerial) > 1

    OPEN DUPE_CURSOR

    FETCH NEXT FROM DUPE_CURSOR INTO @strSerial, @CountX

    WHILE @@FETCH_STATUS = 0
    BEGIN

    UPDATE DUPE_CHECKED_USER_ITEM_LIST SET IsDupe = 1 WHERE strSerial = @strSerial

    FETCH NEXT FROM DUPE_CURSOR INTO @strSerial, @CountX
    END

    CLOSE DUPE_CURSOR
    DEALLOCATE DUPE_CURSOR

    DECLARE DUPE_CURSOR CURSOR FAST_FORWARD FOR

    SELECT strUserId,Num,strSerial FROM DUPE_CHECKED_USER_ITEM_LIST WHERE IsDupe = 1

    OPEN DUPE_CURSOR

    FETCH NEXT FROM DUPE_CURSOR INTO @strUserIdX,@Num,@strSerial
    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @DupedCount = 0

    SELECT @DupedCount = COUNT(*) FROM DUPE_ITEM_LIST WHERE strUserId = @strUserIdX AND Num = @Num AND strSerial = @strSerial

    IF @DupedCount = 0
    BEGIN
    INSERT INTO DUPE_ITEM_LIST (strUserId,Num,strSerial) VALUES (@strUserIdX,@Num,@strSerial)
    END

    FETCH NEXT FROM DUPE_CURSOR INTO @strUserIdX,@Num,@strSerial
    END

    CLOSE DUPE_CURSOR
    DEALLOCATE DUPE_CURSOR

    -- We are looking to dupe the queue.

    IF @DupeDeletingSystemStatus = 1
    BEGIN
    SELECT @DupedCount = COUNT(*) FROM DUPE_ITEM_LIST WHERE strUserId = @strUserId

    IF @DupedCount <> 0
    BEGIN

    -- Start Deleting
    SET @Num = 0
    SET @strSerial = 0

    DECLARE DUPE_ITEM_LIST_CURSOR CURSOR FOR

    SELECT Num,strSerial FROM DUPE_ITEM_LIST WHERE strUserId = @strUserId

    OPEN DUPE_ITEM_LIST_CURSOR

    FETCH NEXT FROM DUPE_ITEM_LIST_CURSOR INTO @Num,@strSerial

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

    SET @strName = NULL

    SELECT @strName = strName FROM ITEM WHERE Num = @Num

    INSERT INTO DUPE_CHECK_REPORT (strUserId,Num,strSerial,ItemName) VALUES (@strUserId,@Num,@strSerial,@strName)

    UPDATE DUPE_CHECK_USER_INVENTORY SET Num = 0,StackSize = 0, Durability = 0, strSerial = 0 WHERE Num = @Num AND strSerial = @strSerial AND strUserId = @strUserId

    DELETE FROM DUPE_ITEM_LIST WHERE Num = @Num AND strSerial = @strSerial AND strUserId = @strUserId

    FETCH NEXT FROM DUPE_ITEM_LIST_CURSOR INTO @Num,@strSerial
    END

    CLOSE DUPE_ITEM_LIST_CURSOR

    DEALLOCATE DUPE_ITEM_LIST_CURSOR

    SET @strSerial = 0
    SET @InventorySlot = 0
    SET @I = 14 * 0
    SET @While = 401

    SELECT
    @strUserId = strUserId,
    @Num = Num,
    @Durability = Durability,
    @StackSize = stacksize,
    @strSerial = strSerial
    FROM DUPE_CHECK_USER_INVENTORY
    WHERE strUserId = @strUserId and InventorySlot = @InventorySlot

    SET @NumX = SUBSTRING(CAST(@Num AS VARBINARY(4)), 4, 1) + SUBSTRING(CAST(@Num AS VARBINARY(4)), 3, 1) + SUBSTRING(CAST(@Num AS VARBINARY(4)), 2, 1) + SUBSTRING(CAST(@Num AS VARBINARY(4)), 1, 1)
    SET @DurabilityX = CAST(SUBSTRING(CAST(@Durability AS VARBINARY(2)), 2, 1)+SUBSTRING(CAST(@Durability AS VARBINARY(2)), 1, 1) as varchar(2))
    SET @StackSizeX = CAST(SUBSTRING(CAST(@StackSize AS VARBINARY(2)), 2, 1)+SUBSTRING(CAST(@StackSize AS VARBINARY(2)), 1, 1) as varchar(2))
    SET @strSerialX = SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 4, 1) + SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 3, 1) + SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 2, 1) + SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 1, 1)

    UPDATE USERDATA SET
    strItem = CAST(SUBSTRING(strItem, 1, @I) + CAST(CAST(@NumX as varchar(4)) + @DurabilityX + @StackSizeX as varchar(8)) + SUBSTRING(strItem, @I+8, 401-@I) as binary(400)),
    strSerial = CAST(SUBSTRING(strSerial, 1, @I) + CAST(@strSerialX as varchar(4)) + SUBSTRING(strSerial, @I+8, 401-@I) as binary(400))
    WHERE strUserId = @strUserId

    SET @I = @I + 8
    SET @InventorySlot = @InventorySlot + 1

    WHILE @InventorySlot < 50
    BEGIN

    SELECT
    @strUserId = strUserId,
    @Num = Num,
    @Durability = Durability,
    @StackSize = StackSize,
    @strSerial = strSerial
    FROM DUPE_CHECK_USER_INVENTORY
    WHERE strUserId = @strUserId and InventorySlot = @InventorySlot

    SET @NumX = SUBSTRING(CAST(@Num AS VARBINARY(4)), 4, 1) + SUBSTRING(CAST(@Num AS VARBINARY(4)), 3, 1) + SUBSTRING(CAST(@Num AS VARBINARY(4)), 2, 1) + SUBSTRING(CAST(@Num AS VARBINARY(4)), 1, 1)
    SET @DurabilityX = CAST(SUBSTRING(CAST(@Durability AS VARBINARY(2)), 2, 1)+SUBSTRING(CAST(@Durability AS VARBINARY(2)), 1, 1) as varchar(2))
    SET @StackSizeX = CAST(SUBSTRING(CAST(@StackSize AS VARBINARY(2)), 2, 1)+SUBSTRING(CAST(@StackSize AS VARBINARY(2)), 1, 1) as varchar(2))
    SET @strSerialX = SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 4, 1) + SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 3, 1) + SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 2, 1) + SUBSTRING(CAST(@strSerial AS VARBINARY(4)), 1, 1)

    UPDATE USERDATA SET
    strItem = CAST(SUBSTRING(strItem, 1, @I) + CAST(CAST(@NumX as varchar(4)) + @DurabilityX + @StackSizeX as varchar(8)) + SUBSTRING(strItem, @I+8, 401-@I) as binary(400)),
    strSerial = CAST(SUBSTRING(strSerial, 1, @I) + CAST(@strSerialX as varchar(4)) + SUBSTRING(strSerial, @I+8, 401-@I) as binary(400))
    WHERE strUserId = @strUserId

    SET @I = @I + 8
    SET @InventorySlot = @InventorySlot + 1

    END

    DELETE FROM DUPE_CHECK_USER_INVENTORY WHERE strUserId = @strUserId

    END
    END

    -- Deletion process is complete.

    END

    2. Procedür

    Kod:
    CREATE PROCEDURE [dbo].[DUPE_CHECK_ALL_USERS]
    AS
    /*
    Author : AKUMA
    Update : 11.03.2009 - 11:37
    */
    -- Deleting Old Data
    TRUNCATE TABLE DUPE_CHECK_USER_INVENTORY
    TRUNCATE TABLE DUPE_CHECKED_USER_ITEM_LIST
    TRUNCATE TABLE DUPE_ITEM_LIST
    TRUNCATE TABLE DUPE_CHECK_REPORT

    DECLARE @strUserId char(21)

    -- First Scan
    DECLARE DW_CURSOR CURSOR FOR

    SELECT strUserId FROM USERDATA ORDER By Loyalty DESC

    OPEN DW_CURSOR

    FETCH NEXT FROM DW_CURSOR INTO @strUserId
    WHILE (@@FETCH_STATUS =0)
    BEGIN

    EXEC DUPE_CHECK_USER @strUserId

    FETCH NEXT FROM DW_CURSOR INTO @strUserId
    END

    CLOSE DW_CURSOR

    DEALLOCATE DW_CURSOR

    -- Second Scan
    DECLARE DW_CURSOR CURSOR FOR

    SELECT strUserId FROM USERDATA ORDER By Loyalty DESC

    OPEN DW_CURSOR

    FETCH NEXT FROM DW_CURSOR INTO @strUserId
    WHILE (@@FETCH_STATUS =0)
    BEGIN

    EXEC DUPE_CHECK_USER @strUserId

    FETCH NEXT FROM DW_CURSOR INTO @strUserId
    END

    CLOSE DW_CURSOR

    DEALLOCATE DW_CURSOR

    Kolay Gelsin...

      Forum Saati Paz Ara. 16, 2018 2:51 pm