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

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