[Release] Procedure for events

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

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

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