[RELEASE] Guild Leader change - SQL version

USE PS_GAMEDATA
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tomasz Wiacek
-- Create date: 10.IV.2010
-- Description:	Change guild leader
/* 
Provide the procedure with character name of new
and old guild leader - @OldGuildLeader is old GL
and @NewGuildLeader is new GL respectively.
The rest will be done by the procedure.
*/
-- =============================================
CREATE PROCEDURE dbo.ausp_ChangeGuildLeader 
	
	@OldGuildLeader varchar(30),
	@NewGuildLeader varchar(30)

AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @GuildID int				-- GuildID where change of leadership will take place	
	DECLARE @MasterCharID int			-- stores new GL CharID
	DECLARE @MasterUserID varchar(30)	-- stores new GL UserID
	DECLARE @OldGLCharID int			-- used to store CharID of current GL
	DECLARE @MasterName varchar(40)		-- stores new GL CharName

	-- select GuildID and old GuildLeader CharID
	SELECT @GuildID = GuildID, @OldGLCharID = MasterCharID
	FROM PS_GameData.dbo.Guilds
	WHERE MasterName = @OldGuildLeader;
	
	-- check if any results were returned
	IF @@ROWCOUNT = 0
	begin
		PRINT 'Guild leader - ' + @OldGuildLeader + ' does not exist.';
	end
	ELSE
	begin
		-- get CharID, UserID and CharName of new GuildLeader
		SELECT @MasterCharID = CharID, @MasterUserID = UserID, @MasterName = CharName
		FROM PS_GameData.dbo.Chars
		WHERE CharName = @NewGuildLeader;
		
		-- if new GuildLeader CharName is correct and it exists (result was returned by above statement)
		-- and new GuildLeader is part of the guild execute leadership change
		IF @@ROWCOUNT <> 0 AND @MasterCharID IN (SELECT CharID FROM PS_GameData.dbo.GuildChars WHERE GuildID=@GuildID AND Del=0 AND (LeaveDate IS NULL OR LeaveDate=0))
		begin
			-- update information about who's the current GuildLeader
			UPDATE PS_GameData.dbo.Guilds 
			SET MasterCharID = @MasterCharID, MasterUserID = @MasterUserID, MasterName = @MasterName
			WHERE GuildID = @GuildID;
			
			-- make new GL leader of the guild
			UPDATE PS_GameData.dbo.GuildChars
			SET GuildLevel = 1 WHERE CharID = @MasterCharID AND GuildID = @GuildID;
			
			-- give old GL GuildLevel=3 to be on the safe side if there are too many Lv2's in guild
			UPDATE PS_GameData.dbo.GuildChars
			SET GuildLevel = 3 WHERE CharID = @OldGLCharID AND GuildID = @GuildID;
			
			PRINT 'Guild leadership between - ' + @OldGuildLeader + ' - and - ' + @NewGuildLeader + ' was changed.'
			
		end
		ELSE
		begin
			PRINT 'No such character: ' + @NewGuildLeader + ' or character is not part of the guild.';
		end
	end



END

EXEC PS_GameData..ausp_ChangeGuildLeader 'OldGLName','NewGLName'

--Get the Guild ID
	SELECT @GuildID = GuildID FROM PS_GameData.dbo.GuildChars WHERE [CharID]=@NewGuildLeaderID AND Del=0 AND (LeaveDate IS NULL OR LeaveDate=0)

	-- Get old GuildLeader CharID
	SELECT @OldGLCharID = MasterCharID
	FROM PS_GameData.dbo.Guilds
	WHERE GuildID = @GuildID

--Get the Guild ID and old GuildLeader CharID
	SELECT @GuildID = gc.GuildID, @OldGLCharID = g.MasterCharID
	FROM PS_GameData.dbo.GuildChars AS gc
	INNER JOIN PS_GameData.dbo.Guilds AS g ON gc.GuildID = g.GuildID
	WHERE gc.[CharID]=@NewGuildLeaderID
	AND gc.Del=0
	AND (gc.LeaveDate IS NULL OR gc.LeaveDate=0)

/remob(1) - Takes 1 parameter namely mobid. Resets a Mob
/remobitem(1)  - Takes 1 parameter namely MobID. Resets the items of a mob
/reitem(1)  - Takes 1 parameter amely ItemID. Resets an Item
/reskill(2) - Takes 2 parameters hopegully SkillID and Level. Resets a skill
/remobskill(1) - Takes 1 paramater namely MobID. Resets the skills of a mob
/reseqitem(0)
/reitemenh(0) 
/reitemcre(0)