![[Release] Procedure for events](https://www.elitepvpers.com/forum/iconimages/sro-pserver-guides-releases/release-procedure-events_ltr.gif){kind=small}
With this procedure you can give event rewards to your players with an ease, you only have to code an small php page or just take the char names by hand and insert them into a table in your database, then modify and execute the procedure.
The rewards will appear on the storage of the players. This means that there is no need of relog or restart the server.
Whatever, lets start. First you must create the table with the event winners:
Code:
USE [YOUR_SHARD_DB_NAME] GO /****** Object: Table [dbo].[eventwin] Script Date: 05/16/2012 00:59:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[eventwin]( [id] [int] IDENTITY(1,1) NOT NULL, [charname] [varchar](50) NOT NULL, [sex] [varchar](1) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Code:
USE [YOUR_SHARD_DB_NAME]
GO
/****** Object: StoredProcedure [dbo].[_ADD_ITEM_EXTERN_CHEST] Script Date: 05/16/2012 01:00:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_ADD_ITEM_EXTERN_CHEST]
@account_namet varchar(128),
@codename varchar(128),
@data int,
@opt_level int
as
declare @JID int
declare @ref_item_id int
declare @return_value tinyint
declare @account_name varchar(128)
declare @tmp varchar(128)
SELECT @tmp = UserJID FROM [YOUR_ACCOUNT_DB_HERE].[dbo].[SR_ShardCharNames] WHERE CharName = @account_namet
SELECT @account_name = StrUserID FROM [YOUR_ACCOUNT_DB_HERE].[dbo].[TB_User] WHERE JID = @tmp
select @account_name = AccountID from _AccountJID with ( nolock ) where AccountID = @account_namet
select @ref_item_id = [ID] from _RefObjCommon with ( nolock ) where Codename128 = @codename
declare @link_to_item int
if (not exists (select * from _RefObjCommon where Codename128 = @codename))
begin
raiserror('unknown item: %s', 1, 16, @codename)
return -1
end
-- JID
if (not exists (select * from _accountjid with (nolock) where AccountID = @account_name))
begin
raiserror('not existing account name: %s', 1, 16, @account_name)
return -2
end
-- CHEST
select @JID = JID from _AccountJID with (nolock) where AccountID = @account_name
if (not exists (select * from _chest with (nolock) where UserJID = @JID and (itemid = 0 or itemid is null)))
begin
raiserror('chest Full: %s', 1, 16, @account_name)
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 @ref_item_id = 0
set @link_to_item = 0
select @ref_item_id = id, @link_to_item = link from _RefObjCommon where codename128 = @codename
if (@ref_item_id = 0 or @ref_item_id is null)
begin
raiserror('@RefItemID == NULL: %s', 1, 16, @account_name)
return -4
end
if (@link_to_item = 0 or @link_to_item is null)
begin
raiserror('RefItem Link == NULL: %s', 1, 16, @account_name)
return -5
end
declare @tid1 int
declare @tid2 int
select @tid1 = TypeID1, @tid2 = TypeID2 from _RefObjCommon where ID = @ref_item_id
declare @IS_EQUIP int
set @IS_EQUIP = 0
if (@tid1 <> 3)
begin
raiserror('not item: %s, %s', 1, 16, @account_name, @codename)
return -6 -- ¿ÀÀ×! ¾ÆÀÌÅÛÀÌ ¾Æ´Ï¾ß?
end
if (@tid1 = 3 and @tid2 = 1)
set @IS_EQUIP = 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
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
set @opt_level = 0
end
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
raiserror('failed to allocate new item id: %s, %s', 1, 16, @account_name, @codename)
return -7
END
UPDATE _Items SET RefItemID = @ref_item_id, OptLevel = @Opt_Level, Data = @Data WHERE ID64 = @NewItemID
IF (@@ERROR <> 0)
BEGIN
rollback transaction
raiserror('failed to create new item: %s, %s', 1, 16, @account_name, @codename)
return -8
END
UPDATE _chest SET ItemID = @NewItemID WHERE UserJID = @JID AND Slot = @empty_slot
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
rollback transaction
raiserror('failed to insert item to chest: %s, %s', 1, 16, @account_name, @codename)
return -9
END
--------------------------------------------------------------------------------
if (@NewItemID <= 0)
begin
rollback transaction
raiserror('item generation failed for unknown reason: %s, %s', 1, 16, @account_name, @codename)
return -10
end
commit transaction
return 1
Code:
USE [YOUR_SHARD_DB_NAME] GO /****** Object: StoredProcedure [dbo].[Myth_GiveEventRewards] Script Date: 05/16/2012 01:03:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ------------------------------------------ -- Myth Event Reward dealer procedure ---- -- Created by Synx. Use and modify it ---- -- But leave credits if you do so! ---- ------------------------------------------ CREATE PROCEDURE [dbo].[Myth_GiveEventRewards] AS BEGIN declare @index int = 1 declare @CharName varchar(50) declare @ChestGold bigint declare @JID int WHILE @index <= (SELECT COUNT(id) FROM dbo.eventwin) --Repeat until last ID BEGIN SELECT @CharName = CharName FROM dbo.eventwin WHERE id = @index IF ((SELECT sex FROM dbo.eventwin WHERE CharName = @CharName) = 'M')-- Reward in case of Male characters BEGIN exec _ADD_ITEM_EXTERN_CHEST @CharName,'ITEM_ETC_E060529_GOLDDRAGONFLAG_M',1,0 END ELSE -- Reward in case of Female characters BEGIN exec _ADD_ITEM_EXTERN_CHEST @CharName,'ITEM_ETC_E060529_GOLDDRAGONFLAG_F',1,0 END -- General rewards start from here exec _ADD_ITEM_EXTERN_CHEST @CharName,'ITEM_EVENT_REVERSE_RETURN_SCROLL',10,0 exec _ADD_ITEM_EXTERN_CHEST @CharName,'ITEM_ETC_ARCHEMY_UPPER_REINFORCE_RECIPE_WE_B_12',1,0 -- Adds gold to the storage (optional, comment if you dont want to give gold) SELECT @JID = UserJID FROM [YOUR_ACCOUNT_DB_HERE].[dbo].[SR_ShardCharNames] WHERE CharName = @CharName SELECT @ChestGold = Gold FROM _AccountJID WHERE JID = @JID UPDATE _AccountJID SET Gold = @ChestGold + 20000000 --Edit this value with the amount of gold you want to give WHERE JID = @JID -- Increases the count and does the loop again for the next character SET @index = @index+1 END END GO
1. Run the first query for add the table
2. Run the second query for modify the chest stored procedure
3. Run the 3rd query for create the stored procedure in your shard database
Once you did this:
1. Fill the eventwin table with the character name and the gender (gender must be "M" or "F")
2. Modify the 2nd stored procedure with your desired rewards.
3. Execute the stored procedure
I'm a beginner at SQL so if you are experienced your eyes will probably bleed due to the noobish code. For now test it on your local servers, I used it on my server and for now seems working fine, but I cannot guarantee it will work well for everyone. If anyone want to modify it for improve or fix something on it feel free to do it, just leave credits of the original creator.
Enjoy!
