--SELECT * FROM dbo.func_extract_words('test satu dua')

--Select *  From Login
--Select *  From Profile_Login
--Select *  From Profile_Pelamar

--SELECT e.* FROM [Login] AS l CROSS APPLY dbo.func_extract_words(l.NAMA) AS e WHERE ID = 894 AND LOG_ID = '435CEEC4-4E4F-4FD9-90FB-1C70A7EBEE9E'

DECLARE 
	@ID INT,
	@LOG_ID UNIQUEIDENTIFIER,
	@PRF_ID VARCHAR(50)

DECLARE list_cursor CURSOR FAST_FORWARD FOR  
SELECT
	l.ID,
	l.LOG_ID,
	pl.PRF_ID
FROM
	[Login] AS l INNER JOIN
	Profile_Login AS pl ON l.LOG_ID = pl.LOG_ID INNER JOIN
	Profile_Pelamar AS pp ON pl.LOG_ID = pp.LOG_ID
WHERE
	pp.PRF_ID <> '000'

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @ID, @LOG_ID, @PRF_ID  

WHILE @@FETCH_STATUS = 0  
BEGIN
	DECLARE @nama_acak VARCHAR(200) = ''
	--SELECT @nama = NAMA FROM [Login] WHERE ID = @ID AND LOG_ID = @LOG_ID
	
	--WHILE ( @Counter <= 10)
	--BEGIN
	--    PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter)
	--    SET @Counter  = @Counter  + 1
	--END

	DECLARE
		@nama_part VARCHAR(50),
		@length_part TINYINT,
		@position_part TINYINT
	DECLARE nama_cursor CURSOR FAST_FORWARD FOR 
	SELECT e.Word, e.[Length], e. Position FROM [Login] AS l CROSS APPLY dbo.func_extract_words(l.NAMA) AS e WHERE ID = @ID AND LOG_ID = @LOG_ID
	OPEN nama_cursor  
	FETCH NEXT FROM nama_cursor INTO @nama_part, @length_part, @position_part  
	WHILE @@FETCH_STATUS = 0  
	BEGIN
		DECLARE
			@return_value int,
			@po_text varchar(50)

		EXEC	@return_value = [dbo].[proc_generate_random_string]
				@pi_min = @length_part,
				@pi_max = @length_part,
				@po_text = @po_text OUTPUT
		--SELECT	@po_text as N'@po_text'
		--SELECT	'Return Value' = @return_value
		SET @nama_acak = @nama_acak + @po_text + ' '

		FETCH NEXT FROM nama_cursor INTO @nama_part, @length_part, @position_part
	END
	CLOSE nama_cursor  
	DEALLOCATE nama_cursor 

	--PRINT @nama_acak
	UPDATE
		[Login]
	SET
		NAMA = RTRIM(@nama_acak),
		EMAIL = dbo.func_mask_string(EMAIL,'x')
	WHERE
		ID = @ID 
		AND LOG_ID = @LOG_ID

	UPDATE
		Profile_Login
	SET
		NM_PEG = RTRIM(@nama_acak),
		EMAIL = dbo.func_mask_string(EMAIL,'x')
	WHERE
		ID = @ID 
		AND LOG_ID = @LOG_ID
		AND PRF_ID = @PRF_ID

	UPDATE
		Profile_Pelamar
	SET
		NM_PEG = RTRIM(@nama_acak)
	WHERE
		LOG_ID = @LOG_ID
		AND PRF_ID = @PRF_ID

	FETCH NEXT FROM list_cursor INTO @ID, @LOG_ID, @PRF_ID    
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 