[RELEASE] Guild Leader change - SQL version

[ProfNerwosol]

Here's a Guild Leader change script wrote in SQL. It is a stored procedure so you can call it from php or whatever you are using.

There are many comments so you shouldn't be lost when reading through it. I haven't tested it. What I did was rewrite old procedure I had lying around. If you get any errors, post them here.

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

To those new to SQL, paste the above code as new query in Server Management Studio and execute it. To run it do this:

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

replacing the words in-between quotes with real character names.

NOTE:
The changes won't take effect until server restart.

[·Tyler·]

Haha, I probably could have written one, I have been doing it manually.
Thanks for the release.

Tyler

[Alladrios]

Thank you prof, I will test it and report any error

Thanks also for your global skill/stat reset, this query has been a life saver in last maint.

Envotion from Rebirth.

[lilprohacker]

The problem with this is that it wont take effect until a server restart. Cause the Guild Info is stored in memory while the server is running.

[ProfNerwosol]

Originally Posted by lilprohacker

The problem with this is that it wont take effect until a server restart. Cause the Guild Info is stored in memory while the server is running.

I know. I remember you posted how to reload any particular data into server memory from database, but I don't remember where it was.

I added the info to first post.

[RebeccaBlack]

I used a test guild to test it, and it looks like it made us both rank 1. Would that be correct?

*Edit* After rerunning it, it seemed to work, so nevermind. ._.

[Danco1990]

I will check my SQL quiry i got lying around, mine seems to work without a restart as long as the person relogs, i think it has a readressing function or something, will check out, either way, nice script, bit more advanced then mine.

[[GM]Father]

Was there something wrong with using GuildName and NewLeaderName as inputs?

I thought it was odd you got the Guild ID from the guild leader rather than using the guild name as an input.

I felt using the Guild Name rather than old guild leader name has less room for error, since the guild leader can change names and such.

[ProfNerwosol]

You can see I wrote it in April 2010, when I didn't know as much. I don't remember why I chose CharName, I guess I wanted to know who's taking part in the process or I thought CharNames is a good idea to do this. GuildName would work better but I believe any name change should be reflected in other tables so the current version should still work.

[Bаne]

# added to my collection of gudies.

[lilprohacker]

removed

[abrasive]

If you want to optimize a little more you can save a query in Lilpro's version if you change this:

--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

To this:

--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)

Also it looks like you fat-fingered a comma in place of your DECLARE statement for @GuildID int.

[zargon05]

Also it looks like you fat-fingered a comma in place of your DECLARE statement for @GuildID int.

Nah it works, it's same as
DECLARE a int, b int. The comments are ignored during execution

[lilprohacker]

Originally Posted by abrasive

If you want to optimize a little more you can save a query in Lilpro's version...

Thanks for this. I Just quickly modified the current one above to it current state, I didn't think about going though and optimizing it, But since you already have, Thanks! it makes it easier on me.

[zargon05]

Go into the command of the Game service and type /help.

/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)