SQL queries: Castle Siege Winner

[Bolo]

Use a scheduled SQL job to distribute WCoins to all characters belonging to the Castle Siege winning guild.
Configure the reward amount, then execute the job immediately after the Castle Siege event concludes.

Example: If Castle Siege ends at 22:00, configure the job to run at 22:01.

• OWNER GUILD: ALL MEMBERS +10 WCoinC
• ALLIED GUILDS: ALL MEMBERS +10 WCoinC

Code Select Expand

/* ============================================
  Castle Siege Rewards - Optimized
  ============================================ */

DECLARE @OwnerGuild VARCHAR(8)

-- Get castle owner guild
SELECT @OwnerGuild = OWNER_GUILD
FROM MuCastle_DATA

/* ============================================
  Reward OWNER Guild Members
  ============================================ */

UPDATE C
SET C.WCoinC = C.WCoinC + 10
FROM CashShopData C
INNER JOIN Character CH ON C.AccountID = CH.AccountID
INNER JOIN GuildMember GM ON CH.Name = GM.Name
WHERE GM.G_Name = @OwnerGuild


/* ============================================
  Reward ALLIED Guild Members
  ============================================ */

UPDATE C
SET C.WCoinC = C.WCoinC + 10
FROM CashShopData C
INNER JOIN Character CH ON C.AccountID = CH.AccountID
INNER JOIN GuildMember GM ON CH.Name = GM.Name
INNER JOIN Guild G ON GM.G_Name = G.G_Name
WHERE G.G_Union =
(
    SELECT G_Union
    FROM Guild
    WHERE G_Name = @OwnerGuild
)
AND GM.G_Name <> @OwnerGuild

• Guild Master Extra Reward

Code Select Expand

UPDATE C
SET C.WCoinC = C.WCoinC + 500
FROM CashShopData C
INNER JOIN MEMB_INFO M ON C.AccountID = M.memb___id
INNER JOIN Guild G ON M.memb_name = G.G_Master
WHERE G.G_Name = @OwnerGuild

Before applying these queries, please test them on the beta/test version of MuOnline. Once verified, and then you can applied to the official version.