[RELEASE] UM Resurrection script - SQL version

USE [PS_GameData]
GO

/****** Object:  Table [dbo].[Slots]    Script Date: 01/11/2011 22:23:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [PS_GameData].[dbo].[Slots](
	[Slot] [tinyint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Slot] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [PS_GameData].[dbo].[Slots] ([Slot]) VALUES (0)
INSERT INTO [PS_GameData].[dbo].[Slots] ([Slot]) VALUES (1)
INSERT INTO [PS_GameData].[dbo].[Slots] ([Slot]) VALUES (2)
INSERT INTO [PS_GameData].[dbo].[Slots] ([Slot]) VALUES (3)
INSERT INTO [PS_GameData].[dbo].[Slots] ([Slot]) VALUES (4)
GO

USE [PS_GameData]
GO
/****** Object:  StoredProcedure [dbo].[ausp_Restore_UM2]    Script Date: 10/11/2010 16:59:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Tomasz Wiącek
-- Create date: 18.VIII.2010
-- Description:	Restores dead UM characters,
--				which player did not delete by him/herself
-- =============================================
ALTER PROCEDURE [dbo].[ausp_Restore_UM2]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SET ANSI_NULLS ON;

DECLARE dead_um CURSOR FOR 
	SELECT UserUID,UserID,CharID,Slot,CharName,Family FROM PS_GameData.dbo.Chars WHERE Del=1 AND RemainTime=0;

/* Table to store results of running the procedure, used in PHP to list resurrected characters */
DECLARE @ResultTable TABLE (
	UserID varchar(40), CharID int NOT NULL PRIMARY KEY CLUSTERED, CharName varchar(50) NOT NULL, 
	Resurrected varchar(3) NOT NULL, Slot tinyint NULL, NoFreeSlots varchar(3) NULL, FactionChange varchar(3) NULL, 
	InGuild varchar(3) NULL, GuildReturnedTo varchar(30) NULL);		

DECLARE @UserUID int
DECLARE @UserID varchar(40)
DECLARE @CharID int
DECLARE @Slot tinyint
DECLARE @Free_slot tinyint		-- variable to store players first available slot
DECLARE @CharName varchar(50)
DECLARE @GuildID smallint
DECLARE @GuildName varchar(40)
DECLARE @Family tinyint			-- used to test for race to avoid having AoL characters at UoF side - glitch in the script if player had only one UM char
DECLARE @Country tinyint

	-- commonly used SQL queries through the script
	DECLARE @sql_update_chars1 nvarchar(200)	-- player had only one character which was UM
	DECLARE @sql_update_chars2 nvarchar(200)	-- used when player has more than 1 character
	DECLARE @sql_delete nvarchar(200)			-- remove information about characters death
	DECLARE @sql_update_guild nvarchar(200)
	DECLARE @sql_update_count nvarchar(200)
	DECLARE @sql_vars1 nvarchar(50)
	DECLARE @sql_vars2 nvarchar(50)
	DECLARE @sql_vars3 nvarchar(50)
	
	
	SET @sql_update_chars1	= N'UPDATE PS_GameData.dbo.Chars SET Del=0, DeleteDate=NULL, Slot=0 WHERE CharID=@CharID'
	SET @sql_update_chars2	= N'UPDATE PS_GameData.dbo.Chars SET Del=0, DeleteDate=NULL, Slot=@Free_slot WHERE CharID=@CharID'
	SET @sql_delete			= N'DELETE FROM PS_GameData.dbo._DeletedChars WHERE CharID=@CharID'
	SET @sql_vars1			= N'@CharID int'
	SET @sql_vars3			= N'@Free_slot tinyint, @CharID int'
	
	SET @sql_update_guild	= N'UPDATE PS_GameData.dbo.GuildChars SET Del=0,LeaveDate=NULL WHERE CharID=@CharID'
	SET @sql_update_count	= N'UPDATE PS_GameData.dbo.Guilds SET TotalCount=TotalCount+1 WHERE GuildID=@GuildID'
	SET @sql_vars2			= N'@GuildID smallint'
	

/*********
- Enough of declaring, let's do something with the data.

- Let's open the cursor and start restoring UM's
**********/

OPEN dead_um
FETCH NEXT FROM dead_um INTO
	@UserUID,@UserID,@CharID,@Slot,@CharName,@Family

IF @@CURSOR_ROWS = 0
	begin
		PRINT 'No one died so far.'
		CLOSE dead_um
		DEALLOCATE dead_um		
		return
	end
	
WHILE @@FETCH_STATUS=0
	begin				
		
		IF (SELECT COUNT(*) FROM PS_GameData.dbo.Chars WHERE UserUID=@UserUID AND Del=0)=0
			begin
				
				PRINT '-------------------------------'
				PRINT 'Every character associated to - ' + @UserID + ' - account is dead.'
				PRINT '----------'
				
				-- let's get that new faction and see if it's the same as the last time
				SELECT @Country=Country FROM PS_GameData.dbo.UserMaxGrow WHERE UserUID=@UserUID

				IF @Country<>2
					begin
						PRINT 'Player chose faction after loosing all characters. Checking whether new characters exist.'
						
						IF EXISTS (SELECT * FROM PS_GameData.dbo.Chars WHERE UserUID=@UserUID AND Del=0)
							begin
								PRINT 'Player has new characters. Checking faction.'
								PRINT '----------'
								
								IF (@Family=0 OR @Family=1) AND @Country<>0
									begin
										PRINT 'Faction is not the same. Was Alliance of Light is Union of Fury. Character not resurrected.';
										
										INSERT INTO @ResultTable 
											(UserID, CharID, CharName, Resurrected, NoFreeSlots, FactionChange) 
										VALUES
											(@UserID, @CharID, @CharName, 'no', 'no', 'yes');
										
										FETCH NEXT FROM dead_um INTO
											@UserUID,@UserID,@CharID,@Slot,@CharName,@Family;
										
										CONTINUE;
									end
								ELSE IF (@Family=2 OR @Family=3) AND @Country<>1
									begin
										PRINT 'Faction is not the same. Was Union of Fury is Alliance of Light. Character not resurrected.';
										INSERT INTO @ResultTable
											(UserID, CharID, CharName, Resurrected, NoFreeSlots, FactionChange) 
										VALUES
											(@UserID, @CharID, @CharName, 'no', 'no', 'yes');
										
										FETCH NEXT FROM dead_um INTO
											@UserUID,@UserID,@CharID,@Slot,@CharName,@Family;
										
										CONTINUE;										
									end																
							end
						ELSE
							begin
								PRINT 'No new characters detected. Proceeding with standard routine.'
								PRINT '----------'
							end						
					end				
				
				exec sp_executesql @sql_update_chars1, @sql_vars1, @CharID	-- restore UM
				exec sp_executesql @sql_delete, @sql_vars1, @CharID			-- remove entry about deleted UM from _DeletedChars table
				
				
				-- Character restored to Slot 0.
				INSERT INTO @ResultTable 
					(UserID, CharID, CharName, Resurrected, Slot) 
				VALUES
					(@UserID, @CharID, @CharName, 'yes', 0);
				
					-- verify whether character belonged to guild
				SELECT @GuildID = GC.GuildID, @GuildName = G.GuildName 
				FROM PS_GameData.dbo.GuildChars AS GC INNER JOIN PS_GameData.dbo.Guilds AS G
				ON GC.GuildID = G.GuildID
				WHERE GC.CharID=@CharID AND GC.LeaveDate IS NULL AND GC.Del=1
				
				IF (@GuildID <> 0 AND @GuildID IS NOT NULL)
					begin
						exec sp_executesql @sql_update_guild, @sql_vars1, @CharID
						exec sp_executesql @sql_update_count, @sql_vars2, @GuildID																														
						-- Character restored to guild.
						UPDATE @ResultTable SET InGuild='yes',GuildReturnedTo=@GuildName WHERE CharID=@CharID
					end
				ELSE
					begin						
						-- Character was not in guild.
						UPDATE @ResultTable SET InGuild='no' WHERE CharID=@CharID
					end							
			end
			/* Do nothing when player has no available slots. */
		ELSE IF (SELECT COUNT(Slot) FROM PS_GameData.dbo.Chars WHERE UserUID=@UserUID AND Del=0)=5
			begin
				
				-- No free slots to restore character.
				INSERT INTO @ResultTable 
					(UserID, CharID, CharName, Resurrected, NoFreeSlots) 
				VALUES
					(@UserID, @CharID, @CharName, 'no', 'yes');
				
				FETCH NEXT FROM dead_um INTO
					@UserUID,@UserID,@CharID,@Slot,@CharName,@Family																										
				CONTINUE;
			end
			/* Run when player has free slots and at least one character remaining on the account */
		ELSE	
			begin				
				SET @Free_Slot = (SELECT MIN(Slots.Slot) FROM
						 (SELECT Slot FROM PS_GameData.dbo.Slots) AS Slots
						 LEFT JOIN
						 (SELECT C.Slot
						 FROM PS_userdata.dbo.Users_Master AS UM
						 INNER JOIN PS_GameData.dbo.Chars AS C ON C.UserUID = UM.UserUID
						 WHERE UM.UserUID = @UserUID
						 AND C.Del = 0) AS Chars ON Chars.Slot = Slots.Slot
						 WHERE Chars.Slot IS NULL);
				
				exec sp_executesql @sql_update_chars2, @sql_vars3, @Free_slot, @CharID	-- restore UM
				exec sp_executesql @sql_delete, @sql_vars1, @CharID						-- remove entry about deleted UM from _DeletedChars table
				
				-- Character restored.
				INSERT INTO @ResultTable 
					(UserID, CharID, CharName, Resurrected, Slot) 
				VALUES
					(@UserID, @CharID, @CharName, 'yes', @Free_Slot);				
				
					-- verify whether character belonged to guild 
					-- aquire guild ID the character belonged to
				SELECT @GuildID = GC.GuildID, @GuildName = G.GuildName 
				FROM PS_GameData.dbo.GuildChars AS GC INNER JOIN PS_GameData.dbo.Guilds AS G
				ON GC.GuildID = G.GuildID
				WHERE GC.CharID=@CharID AND GC.LeaveDate IS NULL AND GC.Del=1
				
				IF (@GuildID <> 0 AND @GuildID IS NOT NULL)
					begin
						exec sp_executesql @sql_update_guild, @sql_vars1, @CharID
						exec sp_executesql @sql_update_count, @sql_vars2, @GuildID																														
						-- Character restored to guild.
						UPDATE @ResultTable SET InGuild='yes',GuildReturnedTo=@GuildName WHERE CharID=@CharID
					end
				ELSE
					begin						
						-- Character was not in guild.
						UPDATE @ResultTable SET InGuild='no' WHERE CharID=@CharID
					end
			end
		
		FETCH NEXT FROM dead_um INTO
			@UserUID,@UserID,@CharID,@Slot,@CharName,@Family		
	end

CLOSE dead_um
DEALLOCATE dead_um

SELECT UserID, CharID, CharName, Resurrected, Slot, NoFreeSlots, FactionChange, InGuild, GuildReturnedTo FROM @ResultTable

END