[Paylaşım] Dupeleri yakalamak ve listelemek

Lutfen yayinladiginiz DB lerde en azindan 1 kere adim gecsin ki tesvik edici olsun, gokhantasi yazan yeri silip kendi adinizi yazmak en kolayi

Aynı serialli 2 kişi yakaladınız ( yani bir kişi chitin shield i dupelemiş ve üzerinde bulunduruyor )

Bunları listeyelebilir ve afiyetle banlayabilirsiniz.

Yapmanız gereken aşadaki kodları SQL e atmak.

Kod:

USE [KN_online]
GO
/****** Object: Table [dbo].[DUPE_CHECK] script Date: 05/10/2008 23:17:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DUPE_CHECK](
[id] [int] IDENTITY(1,1) NOT NULL,
[strUserID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DUPE_CHECK_strUserID] DEFAULT ('-'),
[strAccountID] [varchar](21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DUPE_CHECK_strAccountID] DEFAULT ('-'),
[Name] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dwID] [int] NULL,
[strSerial] [int] NULL,
[Durability] [int] NULL,
[StackSize] [smallint] NULL,
)
GO
SET ANSI_PADDING OFF


Part 2

Kod:

CREATE TABLE [dbo].[Gokhan] (
[Userid] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[date] [smalldatetime] NULL ,
[dupeitem] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sayisi] [int] NULL
)


Part 3


Kod:

CREATE PROCEDURE [dbo].[RunDupeCheck]
@StrUserID varchar(40)
AS
-- Empire Quest de yayinlanmistir.
-- gokhantasci tarafindan tekrar yapilandirilmistir.
-- Tesekkur etmeden kullanan , Ben yaptim diye gösteren , Aha buyrun ben buyum diyen , degistirip adimi silen Toptur , Ajdardir , Delikli Nane Polo dur , Good&Year uzun ömürlü lastikleridir

DECLARE @row int, @i int, @count int, @dwid int, @pos int, @type varbinary(4), @ext int, @StackSize int, @strName varchar(255), @strSerial int, @name char(50)
SET @strName = ''
SET @i = 1
set @name = ''

DELETE FROM DUPE_CHECK WHERE strUserID = @StrUserId

WHILE @i < 401
BEGIN

SELECT @dwid = 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),
@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),
@type = cast(substring(strItem, @i+4, 2) as varbinary(2)),
@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 smallint)
FROM USERDATA
WHERE strUserID = @StrUserID

select @name = strname from item where num = @dwid

IF @dwid <> 0

INSERT INTO DUPE_CHECK (strUserID, Name, dwid, strSerial, Durability, StackSize) VALUES (@StrUserId, @Name, @dwid, @strSerial, @type, @StackSize)

SELECT @count = COUNT(*) FROM DUPE_CHECK WHERE strUserID = @strUserID AND strSerial = @strSerial
IF @count > 1 and @strSerial <> 0
BEGIN
declare @aga varchar(100), @agacount varchar(100), @duper int
select @aga = strname from item where num = @dwid
select @agacount = count(userid) from gokhan where dupeitem = @aga and userid = @strUserID
select @duper = sayisi from gokhan where dupeitem = @aga and userid = @strUserID
if @count >= @duper
begin
delete from gokhan where dupeitem = @aga and userid = @strUserID
end
PRINT 'Duper'
insert into gokhan (userid, dupeitem, sayisi) values (@StrUserId, @aga, @count)
truncate table dupe_check
-- end
END
SET @i = @i + 8
END
GO


Kod:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RunDupeCheckInn]
@StrAccountID varchar(40)
AS
DECLARE @row int, @i int, @count int, @dwid int, @pos int, @type varbinary(4), @ext int, @StackSize int, @strName varchar(255), @strSerial int, @name char(50)
SET @strName = ''
SET @i = 1
set @name = ''


WHILE @i < 401
BEGIN

SELECT @dwid = cast(cast(substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 4, 1)+substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 2, 1)+substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) 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),
@type = cast(substring(WarehouseData, @i+4, 2) as varbinary(2)),
@StackSize = cast(cast(cast( substring(WarehouseData, @i+7, 1) as varbinary(1))+cast(substring(WarehouseData, @i+6, 1) as varbinary(1)) as varbinary(2)) as smallint)
FROM WAREHOUSE
WHERE StrAccountID = @StrAccountID

select @name = strname from item where num = @dwid

IF @dwid <> 0
INSERT INTO DUPE_CHECK (StrAccountID, Name, dwid, strSerial, Durability, StackSize) VALUES (@StrAccountID, @name, @dwid, @strSerial, @type, @StackSize)

SELECT @count = COUNT(*) FROM DUPE_CHECK WHERE strAccountID = @StrAccountID AND strSerial = @strSerial
IF @count > 1 and @strSerial <> 0
BEGIN
PRINT 'Duper'
END
SET @i = @i + 8
END


exec rundupecheck 'nick'
exec rundupecheckinn 'kullaniciadi'

Şeklinde çalıştırabilirsiniz


Dilerseniz bunlari , Load_user_data ya yada account_login e eklersiniz.

Load_user_data ya ekleyeceginiz kisim ;


Kod:

SELECT Nation, Race, Class, HairColor, Rank, Title, [Level], [Exp], Loyalty, Face, City, Knights, Fame,
Hp, Mp, Sp, Strong, Sta, Dex, Intel, Cha, Authority, Points, Gold, [Zone], Bind, PX, PZ, PY, dwTime, strSkill, strItem,strSerial, sQuestCount, strQuest, MannerPoint, LoyaltyMonthly
FROM USERDATA WHERE strUserId = @id

kisminin altina ekleyeceksiniz , ekledikten sonra söyle gözükecek

Kod:

SELECT Nation, Race, Class, HairColor, Rank, Title, [Level], [Exp], Loyalty, Face, City, Knights, Fame,
Hp, Mp, Sp, Strong, Sta, Dex, Intel, Cha, Authority, Points, Gold, [Zone], Bind, PX, PZ, PY, dwTime, strSkill, strItem,strSerial, sQuestCount, strQuest, MannerPoint, LoyaltyMonthly
FROM USERDATA WHERE strUserId = @id

SET @nRet = @@RowCount

exec rundupecheck @id




Kod:

Select * from gokhan

Yazdiginizda kimde hangi itemler , kac kere dupelenmis gorebilirsiniz.


Tamamen Alıntıdır gokhantasci arkadasımıza teşekkürler.