Hello,
I coded this for Thera, my server is running with it and it's working perfectly fine.
[SRO_VT_SHARD].[dbo].[_ADD_ITEM_EXTERN_CHEST_FAST]
[SRO_VT_LOG].[dbo].[_AddLogChar]
New table [_Rewards]
New table [_Reward_log]
_Rewards table example
Hint:
If you want to reward avatars make sure to add them as women, my script will change them depending on char gender.
Enjoy :rolleyes:
I coded this for Thera, my server is running with it and it's working perfectly fine.
[SRO_VT_SHARD].[dbo].[_ADD_ITEM_EXTERN_CHEST_FAST]
PHP Code:
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST] Script Date: 2015-11-16 21:53:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN_CHEST_FAST]
@JID int,
@ItemToAdd int,
@data int,
@opt_level int
as
if (not exists(select * from sysobjects where name = '_TEMP_ADDITEMEXTERN_CHEST_LOG'))
begin
create table _TEMP_ADDITEMEXTERN_CHEST_LOG
(
ID int identity(0, 1) not null,
LogString varchar(256),
LogDate datetime default getdate() not null
)
end
declare @LogString varchar(256)
declare @link_to_item int
if (not exists (select UserJID from _User with (nolock) where UserJID = @JID))
begin
set @LogString = 'not existing account ID: ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -2
end
--//////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- 2009.05.12 ¿øº´Ã¶ â°í »çÀÌÁî·Î â°í¿¡ ¾ÆÀÌÅÛÀ» Áö±ÞÇÒ¼ö ÀÖÀ»Áö ¿©ºÎ·Î ¹Ù²Ù¾ú´Ù.
-- ÀÌÀü üũ¹æ½Ä
-- if (not exists (select * from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null)))
-- begin
-- set @LogString = 'chest Full! JID is ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15))
-- insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
-- raiserror('%s', 1, 16, @LogString)
-- return -3
-- end
-- »õ·Î¿î üũ
declare @IsAdd_Able int
-- iSRO fuckup
--set @IsAdd_able = 0
--- New select here
select @IsAdd_Able = ( case when count(CHEST.slot) >= CHESTINFO.ChestSize then 0 else 1 end )
from _chest as CHEST with (nolock) INNER JOIN _chestinfo as CHESTINFO with (nolock) ON CHESTINFO.JID = CHEST.UserJID
where CHEST.UserJID = @JID and (CHEST.itemid != 0 and CHEST.itemid is not null)
group by CHESTINFO.ChestSize
if ( @IsAdd_Able = 0 )
begin
set @LogString = 'chest Full! JID is ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -3
end
--//////////////////////////////////////////////////////////////////////////////////////////////////////////////
declare @empty_slot int
select top 1 @empty_slot = slot from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null) order by slot
set @link_to_item = 0
select @link_to_item = link from _RefObjCommon where ID = @ItemToAdd
if (@link_to_item = 0 or @link_to_item is null)
begin
set @LogString = 'RefItem Link == NULL JID is ' + cast(@JID as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -5
end
declare @tid1 int
declare @tid2 int
declare @tid3 int
declare @tid4 int
select @tid1 = TypeID1, @tid2 = TypeID2, @tid3 = TypeID3, @tid4 = TypeID4 from _RefObjCommon where ID = @ItemToAdd
declare @IS_EQUIP int
declare @IS_PET int
set @IS_EQUIP = 0
set @IS_PET = 0
if (@tid1 <> 3)
begin
set @LogString = 'not item! JID is ' + cast(@JID as char(15)) + 'item is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -6 -- ¿ÀÀ×! ¾ÆÀÌÅÛÀÌ ¾Æ´Ï¾ß?
end
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 1
else if (@tid1 = 3 and @tid2 = 2 and @tid3 = 1 and (@tid4 = 1 or @tid4 = 2))
set @IS_PET = 1
if (@IS_EQUIP = 1)
begin
select @data = Dur_L from _RefObjItem where ID = @link_to_item
if (@opt_level < 0)
set @opt_level = 0
else if (@opt_level > 12)
set @opt_level = 12
end
else
begin
if( @IS_PET = 1 )
set @data = 0
else
begin
declare @max_count int
select @max_count = MaxStack from _RefObjItem where ID = @link_to_item
if (@data <= 0 or @data > @max_count)
set @data = @max_count
end
set @opt_level = 0
end
-------------------------------------------------------------------------------- let's generate item !!!
set xact_abort on
begin transaction
declare @dummy_serial_number bigint
set @dummy_serial_number = 0
declare @NewItemID bigint
set @NewItemID = 0
EXEC @NewItemID = _STRG_ALLOC_ITEM_NoTX @dummy_serial_number OUTPUT
IF (@NewItemID = 0)
BEGIN
rollback transaction
set @LogString = 'failed to allocate new item id! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -7
END
if( @IS_PET = 1 )
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data, Variance = 0 WHERE ID64 = @NewItemID
else
UPDATE _Items SET RefItemID = @ItemToAdd, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to create new item! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
set @LogString = 'failed to insert item to chest! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
set @LogString = 'item generation failed for unknown reason! JID is ' + cast(@JID as char(15)) + ', Item Is: ' + cast(@ItemToAdd as char(15))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
raiserror('%s', 1, 16, @LogString)
return -10
end
commit transaction
set @LogString = 'success ' + 'jid: ' + cast(@JID as char(15)) + ', item: ' + cast(@ItemToAdd as char(15)) + cast(@data as varchar(10)) + ', slot: ' + cast(@empty_slot as varchar(10))
insert into _TEMP_ADDITEMEXTERN_CHEST_LOG(LogString) values(@LogString)
return 1
[SRO_VT_LOG].[dbo].[_AddLogChar]
PHP Code:
-- Level up? wat
if(@Data1=@Data2-1 AND @Data2 = @Data1+1 AND @EventID = 22 AND @Data2 > 23)
BEGIN
-- Get JID
SELECT DISTINCT TOP(1) @JID = a.UserJID, @StrUserID = b.StrUserID
FROM [SRO_VT_SHARD].[dbo].[_User] a WITH (NOLOCK)
JOIN [SRO_VT_ACCOUNT].[dbo].[TB_User] b on a.UserJID = b.JID
where CharID = @CharID
--- MAKE SURE PEOPLE GET REWARDED
DECLARE @Something int = 0;
-- Fix for first people
IF NOT EXISTS(SELECT TOP(1) CurLevel FROM SRO_VT_LOG.dbo._Reward_log where CharName16 = @CharName16 order by CurLevel desc)
BEGIN
--- Will fix bug for noobs.
SET @Something = 23;
END
ELSE
BEGIN
--- Will fix bug for noobs.
SET @Something = (SELECT TOP(1) CurLevel FROM SRO_VT_LOG.dbo._Reward_log where CharName16 = @CharName16 order by CurLevel desc);
END
-- REWARD LEVEL, me fyll SQL PROMANZ
DECLARE @RewardLevel int = (SELECT TOP(1) CharLevel from SRO_VT_LOG.dbo._Rewards where CharLevel <= @Data2 and CharLevel > @Something);
--- Check if they dont exist
IF NOT EXISTS(SELECT CharName16 from SRO_VT_LOG.dbo._Reward_log where CharName16 = @CharName16 and CurLevel = @RewardLevel)
BEGIN
--- Select everything needed
SELECT DISTINCT TOP(1) @CodeName128 = a.CodeName128, @Amount = a.Amount, @Data = a.Data, @Reward_s = a.Reward_s, @Reward_c = a.Reward_c, @RefObjID = b.RefObjID
FROM [SRO_VT_LOG].[dbo].[_Rewards] a WITH (NOLOCK)
JOIN [SRO_VT_SHARD].[dbo].[_Char] b ON @CharID = b.CharID
WHERE CharLevel = @RewardLevel
--- Check if event is still going on.
if(@Reward_s > @Reward_c)
BEGIN
-- INSERT SHIT
INSERT INTO [SRO_VT_LOG].[dbo].[_Reward_log](CharName16, CurLevel, StrUserID, JID) VALUES(@CharName16, @RewardLevel, @StrUserID, @JID);
-- UPDATE SHIT
UPDATE [SRO_VT_LOG].[dbo].[_Rewards] set Reward_c = (Reward_c + 1) where CharLevel = @RewardLevel;
--- Switch method
IF(@CodeName128 = 'SILK' or @CodeName128 = 'GIFT' or @CodeName128 = 'POINT')
BEGIN
--- Quick shit
IF NOT EXISTS(SELECT JID FROM SK_Silk where JID = @JID)
BEGIN
INSERT INTO SK_Silk (JID, silk_own, silk_gift, silk_point) VALUES(@JID, 0, 0, 0);
END
--- NORMAL SILK
IF(@CodeName128 = 'SILK')
BEGIN
--- GIVE SHIT
UPDATE [SRO_VT_ACCOUNT].[dbo].[SK_Silk] set silk_own = (silk_own + @Amount) where JID = @JID;
-- SEND NOTICE
--INSERT INTO [SUPERMAN].[dbo].[_Notice] ([message], [added]) VALUES(@CharName16 + ' was top ' + CAST(@Reward_c+1 as varchar)+ ' of ' + CAST(@Reward_s as varchar) + ' to get the level(' + CAST(@RewardLevel as varchar) +') reward!', GETDATE());
END
--- SILK GIFT
ELSE IF(@CodeName128 = 'GIFT')
BEGIN
--- GIVE SHIT
UPDATE [SRO_VT_ACCOUNT].[dbo].[SK_Silk] set silk_gift = (silk_gift + @Amount) where JID = @JID;
-- SEND NOTICE
--INSERT INTO [SUPERMAN].[dbo].[_Notice] ([message], [added]) VALUES(@CharName16 + ' was top ' + CAST(@Reward_c+1 as varchar)+ ' of ' + CAST(@Reward_s as varchar) + ' to get the level(' + CAST(@RewardLevel as varchar) +') reward!', GETDATE());
END
--- SILK POINT
ELSE IF(@CodeName128 = 'POINT')
BEGIN
--- GIVE SHIT
UPDATE [SRO_VT_ACCOUNT].[dbo].[SK_Silk] set silk_point = (silk_point + @Amount) where JID = @JID;
-- SEND NOTICE
--INSERT INTO [SUPERMAN].[dbo].[_Notice] ([message], [added]) VALUES(@CharName16 + ' was top ' + CAST(@Reward_c+1 as varchar)+ ' of ' + CAST(@Reward_s as varchar) + ' to get the level(' + CAST(@RewardLevel as varchar) +') reward!', GETDATE());
END
END
ELSE
-- Item reward here
BEGIN
if(@CodeName128 LIKE '%_F_%' OR @CodeName128 LIKE '%_W_%' OR @CodeName128 LIKE '%SILSAMO%')
BEGIN
--- Male
IF (@RefObjID BETWEEN 1900 AND 1919)
BEGIN
SET @Gender=1 -- male
End
--- Male
ELSE IF (@RefObjID BETWEEN 14875 AND 14887)
BEGIN
SET @Gender=1 -- male
END
-- Gender switch
IF(@Gender = 1)
BEGIN
IF(@CodeName128 like '%SILSAMO%')
BEGIN
SET @CodeName128 = REPLACE(@CodeName128, '_F', '_M');
END
ELSE
BEGIN
SET @CodeName128 = REPLACE(@CodeName128, '_W_', '_M_');
SET @CodeName128 = REPLACE(@CodeName128, '_F_', '_M_');
END
END
END
-- Give reward
EXEC [SRO_VT_SHARD].[dbo].[_ADD_ITEM_EXTERN_CHEST] @StrUserID, @CodeName128, @Data, @Amount;
-- SEND NOTICE
--IF(@RewardLevel >= 52)
--BEGIN
--INSERT INTO [SUPERMAN].[dbo].[_Notice] ([message], [added]) VALUES(@CharName16 + ' was top ' + CAST(@Reward_c+1 as varchar)+ ' of ' + CAST(@Reward_s as varchar) + ' to get the level(' + CAST(@RewardLevel as varchar) +') reward!', GETDATE());
--END
END
END
END
END
New table [_Rewards]
PHP Code:
USE [SRO_VT_LOG]
GO
/****** Object: Table [dbo].[_Rewards] Script Date: 2015-11-16 21:55:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_Rewards](
[CharLevel] [bigint] NOT NULL,
[CodeName128] [varchar](255) NOT NULL,
[Amount] [int] NOT NULL,
[Data] [int] NOT NULL,
[Reward_s] [bigint] NOT NULL,
[Reward_c] [bigint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[_Rewards] ADD CONSTRAINT [DF__Rewards_CharLevel] DEFAULT ((24)) FOR [CharLevel]
GO
ALTER TABLE [dbo].[_Rewards] ADD CONSTRAINT [DF__Rewards_Amount] DEFAULT ((0)) FOR [Amount]
GO
ALTER TABLE [dbo].[_Rewards] ADD CONSTRAINT [DF__Rewards_Data] DEFAULT ((0)) FOR [Data]
GO
ALTER TABLE [dbo].[_Rewards] ADD CONSTRAINT [DF__Rewards_Reward_c] DEFAULT ((0)) FOR [Reward_c]
GO
New table [_Reward_log]
PHP Code:
USE [SRO_VT_LOG]
GO
/****** Object: Table [dbo].[_Reward_log] Script Date: 2015-11-16 21:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_Reward_log](
[CharName16] [varchar](64) NOT NULL,
[CurLevel] [bigint] NOT NULL,
[StrUserID] [varchar](64) NOT NULL,
[JID] [bigint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
_Rewards table example
Code:
CharLevel CodeName128/GIFT/POINT/SILK Amount Durability Reward_number 0
If you want to reward avatars make sure to add them as women, my script will change them depending on char gender.
Enjoy :rolleyes:
