USE [EventData]
GO
/****** Object: StoredProcedure [dbo].[cabal_sp_ems_get_eventdata] Script Date: 04/28/2013 22:39:02 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cabal_sp_ems_get_eventdata]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cabal_sp_ems_get_eventdata]
GO
USE [EventData]
GO
/****** Object: StoredProcedure [dbo].[cabal_sp_ems_get_eventdata] Script Date: 04/28/2013 22:39:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[cabal_sp_ems_get_eventdata]
(
@EventID INT,
@EventType INT,
@Option TINYINT = 0
)
AS
/*
EMST_INVALID = 0,
EMST_EVENT_MONSTER_SPAWN = 1,
EMST_EVENT_ITEM_COLLECTING = 2,
// EMST_EVENT_LOTTERY_ITEM = 3,
EMST_EVENT_GOAL = 4,
EMST_EVENT_MULTIPLE = 5,
EMST_EVENT_MULTIPLE_15 = 6,
EMST_DROPPOOL = 0xA0,
EMST_NPCITEMSHOP = 0xA1,
EMST_NCPSCRIPT = 0xA2,
EMST_ITEMDESC = 0xA3,
// EMST_LOTTERY_ITEMDESC = 0xA4,
EMST_LOTTERY_ITEMPOOL = 0xA5,
EMST_GOAL_REWARD_ITEMS = 0xA6,
EMST_GOAL_REWARD_BONUSES = 0xA7,
EMST_GOAL_GOALUNITS = 0xA8,
EMST_NPCITEMSHOP_ITEMPRICE = 0xA9,
EMST_MULTIPLE_VALUES = 0xAA,
EMST_CRAFT_FORMULACARD = 0xAB,
EMST_CRAFT_RECIPEPOOL = 0xAC,
EMST_MULTIPLE_VALUES_15TH = 0xAD,
------
EDO_DEFAULT = 0,
EDO_FOR_NPCITEMSHOP = 1,
*/
BEGIN
SET NOCOUNT ON
DECLARE @TimeZone INT
SET @TimeZone = DATEDIFF(hour, GETUTCDATE(), GETDATE())
IF (@EventType = 1)
BEGIN
-- EMST_EVENT_MONSTER_SPAWN
SELECT EventMonsterID, SpawnInterval, AuthorityType, PerfectDrop, MultiDropType, MultiDropMin,
MultiDropMax, XPos, YPos, Width, Height, SpawnWorldIdx, DropPoolID, SpawnServers, SpawnChannels
FROM cabal_ems_event_monster_spawn_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = 2)
BEGIN
-- EMST_EVENT_ITEM_COLLECTING
SELECT ItemKindIdx, ItemOption, DropRate, DropServers, DropRegions
FROM cabal_ems_event_item_collecting_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = 4)
BEGIN
-- EMST_EVENT_GOAL
SELECT MaxGoalCompletingCount
FROM cabal_ems_goalevent_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = 5)
BEGIN
-- EMST_EVENT_MULTIPLE
SELECT
DATEADD(hour, -@TimeZone, BeginDateTime),
DATEADD(hour, -@TimeZone, EndDateTime)
FROM cabal_ems_multipleevent_datetime_table
WHERE
EventID = @EventID
AND
GETDATE() <= EndDateTime
ORDER BY BeginDateTime ASC
END
ELSE IF (@EventType = 6)
BEGIN
SELECT TOP 1 type, [case], timespan, allowedtimesec
FROM cabal_ems_event_timer_condition_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = CAST(0xA0 AS INT)) --160
BEGIN
-- EMST_DROPPOOL
SELECT DropPoolID, ItemKindIdx, ItemOption, ItemDurationIdx, DropRate
FROM cabal_ems_droppool_table
WHERE DropPoolID IN
(
SELECT DISTINCT (DropPoolID)
FROM cabal_ems_event_monster_spawn_table
WHERE EventID = @EventID
)
ORDER BY DropPoolID ASC, [Order] ASC
END
ELSE IF (@EventType = CAST(0xA1 AS INT)) --161
BEGIN
-- EMST_NPCITEMSHOP
SELECT ItemKindIdx, ItemOption, ItemDurationIdx, PriceAlz, PricePoint, SlotIdx, ItemPriceID
FROM cabal_ems_event_npcitemshop_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = CAST(0xA2 AS INT)) --162
BEGIN
-- EMST_NCPSCRIPT
SELECT Script1, Script2, Script3, Script4, Script5
FROM cabal_ems_event_npcscript_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = CAST(0xA3 AS INT)) --163
BEGIN
-- EMST_ITEMDESC
SELECT ItemIdx, ItemOption, ExternalID, TextureItemIdx, ItemName, ItemDesc
FROM cabal_ems_event_item_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = CAST(0xA5 AS INT)) --165
BEGIN
-- EMST_LOTTERY_ITEMPOOL
SELECT LotteryID, ItemKindIdx, ItemOption, DurationIdx, Ratio
FROM cabal_ems_lotterydroppool_table
WHERE LotteryID IN
(
SELECT ExternalID
FROM cabal_ems_event_item_table
WHERE EventID = @EventID
)
END
ELSE IF (@EventType = CAST(0xA6 AS INT)) --166
BEGIN
-- EMST_GOAL_REWARD_ITEMS
SELECT A.GoalID, B.ItemKindIdx, B.ItemOption, B.ItemDurationIdx, B.ItemCount, B.RewardableFlag
FROM cabal_ems_goalevent_goal_table as A, cabal_ems_goalevent_reward_itempool_table as B
WHERE A.EventID = @EventID
AND A.RewardItemPoolID = B.ItemPoolID
ORDER BY A.GoalID
END
ELSE IF (@EventType = CAST(0xA7 AS INT)) --167
BEGIN
-- EMST_GOAL_REWARD_BONUSES
SELECT A.GoalID, B.Type, B.Value
FROM cabal_ems_goalevent_goal_table as A, cabal_ems_goalevent_reward_bonuspool_table as B
WHERE A.EventID = @EventID
AND A.RewardBonusPoolID = B.BonusPoolID
ORDER BY A.GoalID
END
ELSE IF (@EventType = CAST(0xA8 AS INT)) --168
BEGIN
-- EMST_GOAL_GOALUNITS
SELECT GoalID, Type, MinParticipationLevel, MaxParticipationLevel, TargetLevel,
MaxRetryCount, TargetQuests, RewardItemCount, RewardBonusCount
FROM cabal_ems_goalevent_goal_table
WHERE EventID = @EventID
ORDER BY GoalID
SELECT * FROM cabal_ems_goalevent_goal_table
END
ELSE IF (@EventType = CAST(0xA9 AS INT)) --169
BEGIN
-- EMST_NPCITEMSHOP_ITEMPRICE
SELECT ItemPriceID, ItemKindIdx, ItemOption, ItemCount
FROM cabal_ems_event_npcitemshop_itemprice_table
WHERE ItemPriceID IN
(
SELECT DISTINCT (ItemPriceID)
FROM cabal_ems_event_npcitemshop_table
WHERE EventID = @EventID
)
END
ELSE IF (@EventType = CAST(0xAA AS INT)) --176
BEGIN
SELECT
MultiplePoolID,
DATEADD(hour, -@TimeZone, BeginDateTime),
DATEADD(hour, -@TimeZone, EndDateTime),
ApplyNation,
ServerList,
ChannelList,
WorldList
FROM cabal_ems_multipleevent_datetime_table
WHERE EventID = @EventID
AND
GETDATE() <= EndDateTime
ORDER BY BeginDateTime ASC
END
ELSE IF (@EventType = CAST(0xAB AS INT)) --171
BEGIN
-- EMST_CRAFT_FORMULACARD
SELECT RecipeID, Rate, ItemKindIdx, ItemOption, ItemDurationIdx
FROM cabal_ems_craftsevent_formulacard_table
WHERE RecipeID IN
(
SELECT ExternalID
FROM cabal_ems_event_item_table
WHERE EventID = @EventID
)
END
ELSE IF (@EventType = CAST(0xAC AS INT)) --172
BEGIN
-- EMST_CRAFT_RECIPEPOOL
SELECT RecipeID, ItemKindIdx, ItemOption, ItemCount
FROM cabal_ems_craftsevent_recipepool_table
WHERE RecipeID IN
(
SELECT RecipeID
FROM cabal_ems_craftsevent_formulacard_table
WHERE RecipeID IN
(
SELECT ExternalID
FROM cabal_ems_event_item_table
WHERE EventID = @EventID
)
)
END
ELSE IF (@EventType = CAST(0xAD AS INT)) --173
BEGIN
SELECT uiStyle
FROM cabal_ems_event_npcitemshop_uistyle_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = 0xB0) --176
BEGIN
SELECT
MultiplePoolID,
DATEADD(hour, -@TimeZone, BeginDateTime),
DATEADD(hour, -@TimeZone, EndDateTime),
ApplyNation,
ServerList,
ChannelList,
WorldList
FROM cabal_ems_multipleevent_datetime_table
WHERE EventID = @EventID
AND
GETDATE() <= EndDateTime
ORDER BY BeginDateTime ASC
END
ELSE IF (@EventType = 0xB1) --177
BEGIN
SELECT timerid, rewardid, timespan
from cabal_ems_event_timer_event_table
WHERE EventID = @EventID
ORDER BY timerid
END
ELSE IF (@EventType = 0xB2) -- 178
BEGIN
SELECT rewardid, itemkindIndex, itemoption, itemDurationIndex
FROM cabal_ems_event_timer_rewarditem_table
WHERE EventID = @EventID
END
ELSE IF (@EventType = 3) --170
BEGIN
-- EMST_MULTIPLE_VALUES
SELECT A.MultiplePoolID, DurationServiceType, ServiceIndex, ColumnType,
MultpleValue, AppliedBeginLevel, AppliedEndLevel
FROM cabal_ems_multipleevent_multiple_table AS B
INNER JOIN
(
SELECT MultiplePoolID
FROM cabal_ems_multipleevent_datetime_table
GROUP BY MultiplePoolID
) AS A ON A.MultiplePoolID = B.MultiplePoolID
WHERE B.EventID = @EventID
END
--SELECT [Option]
-- FROM cabal_ems_event_table
-- WHERE EventID = @EventID
END
GO