Loading...   

  • Created By Uleat On: 08:01 PM September 11, 2016
  • Link

DROP VIEW IF EXISTS `vwbotcharactermobs`;
DROP VIEW IF EXISTS `vwbotgroups`;
DROP VIEW IF EXISTS `vwgroups`;
DROP VIEW IF EXISTS `vwguildmembers`;

DROP VIEW IF EXISTS `vwBotCharacterMobs`;
DROP VIEW IF EXISTS `vwBotGroups`;
DROP VIEW IF EXISTS `vwGroups`;
DROP VIEW IF EXISTS `vwGuildMembers`;

DROP VIEW IF EXISTS `vw_bot_character_mobs`;
DROP VIEW IF EXISTS `vw_bot_groups`;
DROP VIEW IF EXISTS `vw_groups`;
DROP VIEW IF EXISTS `vw_guild_members`;

DROP FUNCTION IF EXISTS `GetMobType`;
DROP FUNCTION IF EXISTS `GetMobTypeByName`;
DROP FUNCTION IF EXISTS `GetMobTypeByID`;


DELIMITER $$

CREATE FUNCTION `GetMobType` (mob_name VARCHAR(64)) RETURNS CHAR(1)
BEGIN
	DECLARE Result CHAR(1);
	
	SET Result = NULL;
	
	IF ((SELECT COUNT(*) FROM `character_data` WHERE `name` = mob_name) > 0) THEN
		SET Result = 'C';
	ELSEIF ((SELECT COUNT(*) FROM `bot_data` WHERE `name` = mob_name) > 0) THEN
		SET Result = 'B';
	END IF;
	
	RETURN Result;
END$$

CREATE FUNCTION `GetMobTypeById` (mob_id INTEGER UNSIGNED) RETURNS CHAR(1)
BEGIN
	DECLARE Result CHAR(1);
	
	SET Result = NULL;
	
	IF ((select `id` from `character_data` where `id` = mob_id) > 0) THEN
		SET Result = 'C';
	ELSEIF ((select `bot_id` from `bot_data` where `bot_id` = mob_id) > 0) THEN
		SET Result = 'B';
	END IF;
	
	RETURN Result;
END$$

DELIMITER ;


CREATE VIEW `vw_bot_character_mobs` AS
SELECT
_utf8'C' AS mob_type,
c.`id`,
c.`name`,
c.`class`,
c.`level`,
c.`last_login`,
c.`zone_id`
FROM `character_data` AS c
UNION ALL
SELECT _utf8'B' AS mob_type,
b.`bot_id` AS id,
b.`name`,
b.`class`,
b.`level`,
b.`last_spawn` AS last_login,
b.`zone_id`
FROM `bot_data` AS b;

CREATE VIEW `vw_bot_groups` AS
SELECT
g.`groups_index`,
g.`group_name`,
g.`group_leader_id`,
b.`name` AS group_leader_name,
b.`owner_id`,
c.`name` AS owner_name
FROM `bot_groups` AS g
JOIN `bot_data` AS b ON g.`group_leader_id` = b.`bot_id`
JOIN `character_data` AS c ON b.`owner_id` = c.`id`
ORDER BY b.`owner_id`, g.`group_name`;

CREATE VIEW `vw_groups` AS
SELECT
g.`groupid` AS group_id,
GetMobType(g.`name`) AS mob_type,
g.`name` AS name,
g.`charid` AS mob_id,
IFNULL(c.`level`, b.`level`) AS level
FROM `group_id` AS g
LEFT JOIN `character_data` AS c ON g.`name` = c.`name`
LEFT JOIN `bot_data` AS b ON g.`name` = b.`name`;

CREATE VIEW `vw_guild_members` AS
SELECT
'C' AS mob_type,
cm.`char_id`,
cm.`guild_id`,
cm.`rank`,
cm.`tribute_enable`,
cm.`total_tribute`,
cm.`last_tribute`,
cm.`banker`,
cm.`public_note`,
cm.`alt`
FROM `guild_members` AS cm
UNION ALL
SELECT
'B' AS mob_type,
bm.`bot_id` AS char_id,
bm.`guild_id`,
bm.`rank`,
bm.`tribute_enable`,
bm.`total_tribute`,
bm.`last_tribute`,
bm.`banker`,
bm.`public_note`,
bm.`alt`
FROM `bot_guild_members` AS bm;

Raw Paste Data