Vsro Job Point Haftalık Limitli

Konuyu başlatan #1
Katılım
12 Nis 2017
Mesajlar
42
Beğeniler
0
Konum
KAYSERİ
Job Point Limiti 500 Olarak Ayarlanmıştır

USE [SRO_VT_ACCOUNT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[_JobPoints]

@CharID int,
@Point int

as

Declare @JID int;

SET @JID = (
SELECT usert.UserJID FROM SRO_VT_SHARD.dbo._User as usert
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE chart.CharID = @CharID
);

IF not exists (SELECT JID FROM SK_Silk WHERE JID = @JID)
BEGIN
INSERT INTO SK_Silk SELECT @JID, 0, 0, 0
END

UPDATE SK_Silk
SET silk_gift = silk_gift + (@Point)
WHERE JID = @JID

USE [SRO_VT_LOG]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[JobPoints](
[CharName] [varchar](50) NOT NULL,
[Amount] [int] NULL,
[Limited] [int] NULL,
[Date] [datetime] NULL,
[TotalAmount] [int] NULL,
CONSTRAINT [PK_JobPoints] PRIMARY KEY CLUSTERED
(
[CharName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Point Limitini Ayarlamak İçin Prosedürdeki Yerleri Düzenlemeniz Lazım
if ((select Amount from JobPoints where CharName = @charname16) >= 500)
declare @contribution int;
declare @coinWinfortra int;
declare @coinWinforthi int;
declare @coinWinforhun int;
declare @dividerfortra int;
declare @dividerforthi int;
declare @dividerforhun int;
declare @charname16 varchar(16);
declare @JobType int;
declare @total int;

set @dividerfortra = 2700000;
set @dividerforthi = 9000000;
set @dividerforhun = 3000000;

if(@EventID = 9 or @EventID = 4 or @EventID = 6 or @EventID = 11) --Teleport or log out/in
begin
set @charname16 = (SELECT CharName16 FROM [SRO_VT_SHARD].[dbo].[_Char] where CharID = @CharID);
set @contribution = (SELECT Contribution FROM [SRO_VT_SHARD].[dbo].[_CharTriJob] WHERE CharID = @CharID);
set @coinWinfortra = ROUND( @contribution / @dividerfortra,0);
set @coinWinforthi = ROUND (@contribution / @dividerforthi,0);
set @coinWinforhun = ROUND (@contribution / @dividerforhun,0);
set @JobType = (SELECT JobType FROM [SRO_VT_SHARD].[dbo].[_CharTrijob] Where CharID = @CharID);
set @total = (select TotalAmount from JobPoints where CharName = @charname16) + (select Amount from JobPoints where CharName = @charname16);

if(@coinWinfortra != 0 and @JobType = 1)
begin
if (@coinWinfortra > 5)
begin
set @coinWinfortra = 5
end

if exists (select @charname16 from [SRO_VT_LOG].[dbo].[JobpPoints])
begin
if ((select Amount from JobPoints where CharName = @charname16) >= 500)
begin
UPDATE JobPoints SET Limited = 1 WHERE CharName = @charname16
end
else
begin
UPDATE [SRO_VT_LOG].[dbo].[JobPoints] SET Amount = Amount + @coinWinfortra, Date = GETDATE(), TotalAmount = @total where CharName = @charname16
exec [SRO_VT_ACCOUNT].[dbo].[_JobPoints] @CharID, @coinWinfortra;
end
end
else
begin
INSERT INTO [SRO_VT_LOG].[dbo].[JobPoints] VALUES (@charname16, @coinWinfortra, 0, GETDATE(), @total)
exec [SRO_VT_ACCOUNT].[dbo].[_JobPoints] @CharID, @coinWinfortra;
end
end --end trader coin

if(@coinWinforthi != 0 and @JobType = 2)
begin
if (@coinWinforthi > 5)
begin
set @coinWinforthi = 5
end

if exists (select @charname16 from [SRO_VT_LOG].[dbo].[JobPoints])
begin
if ((select Amount from JobPoints where CharName = @charname16) >= 500)
begin
UPDATE JobPoints SET Limited = 1 WHERE CharName = @charname16
end
else
begin
UPDATE [SRO_VT_LOG].[dbo].[JobPoints] SET Amount = Amount + @coinWinforthi, Date = GETDATE(), TotalAmount = @total where CharName = @charname16
exec [SRO_VT_ACCOUNT].[dbo].[_JobPoints] @CharID, @coinWinforthi;
end
end
else
begin
INSERT INTO [SRO_VT_LOG].[dbo].[JobPoints] VALUES (@charname16, @coinWinforthi, 0, GETDATE(), @total)
exec [SRO_VT_ACCOUNT].[dbo].[_JobPoints] @CharID, @coinWinforthi;
end
end --end thief coin

if(@coinWinforhun != 0 and @JobType = 3)
begin
if (@coinWinforhun > 5)
begin
set @coinWinforhun = 5
end

if exists (select @charname16 from [SRO_VT_LOG].[dbo].[JobPoints])
begin
if ((select Amount from JobPoints where CharName = @charname16) >= 500)
begin
UPDATE JobPoints SET Limited = 1 WHERE CharName = @charname16
end
else
begin
UPDATE [SRO_VT_LOG].[dbo].[JobPoints] SET Amount = Amount + @coinWinforhun, Date = GETDATE(), TotalAmount = @total where CharName = @charname16
exec [SRO_VT_ACCOUNT].[dbo].[_JobPoints] @CharID, @coinWinforhun;
end
end
else
begin
INSERT INTO [SRO_VT_LOG].[dbo].[JobPoints] VALUES (@charname16, @coinWinforhun, 0, GETDATE(), @total)
exec [SRO_VT_ACCOUNT].[dbo].[_JobPoints] @CharID, @coinWinforhun;
end
end --end hunter coin
if (@JobType != 0)
begin
UPDATE [SRO_VT_SHARD].[dbo].[_CharTriJob] set Contribution = 0 WHERE CharID = @CharID;
end
end --end coin reward

if (@EventID = 4)
BEGIN
if not exists (select CharName from JobPoints where CharName = @charname16)
BEGIN
INSERT INTO JobPoints VALUES(@charname16, 0, 0, GETDATE(), 0)
END
ELSE
BEGIN
UPDATE JobPoints SET Date = GETDATE() where CharName = @charname16
END
END

Aşşağıdaki Trigger Prosedör JobPoint Limit 1 Haftalık Günü Gelince Tabloyu Komple Silip 1 Haftalık Limiti Tekrar Aktif Ediyor
Günleri Ayarlamak İçin Monday Ve Sundayı Kendiniz İstediğiniz Gün Olarak Ayarlıyabilirsiniz

if ((SELECT DATENAME(dw,GETDATE())) = 'Sunday' and @cleared = NULL or @cleared = 0)
begin
TRUNCATE TABLE JobPoints
set @cleared = 1
end

if ((SELECT DATENAME(dw,GETDATE())) = 'Monday' AND @cleared = 1)
begin
USE [SRO_VT_LOG]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[CodeXPointDelete] ON [dbo].[JobPoints] AFTER UPDATE
AS
BEGIN

declare @cleared int

if ((SELECT DATENAME(dw,GETDATE())) = 'Sunday' and @cleared = NULL or @cleared = 0)
begin
TRUNCATE TABLE JobPoints
set @cleared = 1
end

if ((SELECT DATENAME(dw,GETDATE())) = 'Monday' AND @cleared = 1)
begin
set @cleared = 0
end
end
 
Üst