USE PatientFirstIntranet ;
GO
ALTER PROCEDURE dbo.MergeUMRAAccountUpdates AS
SET NOCOUNT ON ;
IF EXISTS
(
	SELECT * FROM tempdb.dbo.sysobjects o
	WHERE o.xtype IN ('U')
	AND o.id = object_id(N'tempdb..#MergeChangeTable')
)
DROP TABLE #MergeChangeTable ;
CREATE TABLE #MergeChangeTable
(
	ActionTaken VARCHAR(20)
	,NewUserName VARCHAR(64)
	,OldUserName VARCHAR(64)
)
;

BEGIN TRANSACTION

	MERGE PatientFirstIntranet.dbo.Users AS T
	USING PatientFirstIntranet.dbo.EmployeeNameChangeLog AS S
		ON T.UserName = S.OrigADUsername
	WHEN MATCHED AND S.IsProcessed = 'N' THEN
		UPDATE SET
		T.UserName = S.NewUserName
		,T.Email = S.NewEmailAddress
		,T.LastName = S.NewADLastName
		,T.DisplayName = S.NewDisplayName
	OUTPUT
		$action
		,INSERTED.UserName
		,DELETED.UserName
	INTO #MergeChangeTable
	;

	--SELECT * FROM #MergeChangeTable ;
	DECLARE @LogTime SMALLDATETIME ;
	SELECT @LogTime = GETDATE() ;

	UPDATE PatientFirstIntranet.dbo.EmployeeNameChangeLog SET
	IsProcessed =
		CASE
			WHEN mct.OldUserName IS NOT NULL THEN 'Y'
			ELSE 'E'
		END
	,DateMerged = @LogTime
	FROM
	PatientFirstIntranet.dbo.EmployeeNameChangeLog encl
	LEFT JOIN
	#MergeChangeTable mct
		ON encl.OrigADUsername = mct.OldUserName
	WHERE
	IsProcessed = 'N'
	;

	--SELECT * FROM PatientFirstIntranet.dbo.EmployeeNameChangeLog ;

	INSERT INTO MIS.MIS.Logging
	(
		MachineName
		,LogSource
		,LogLevel
		,LogMessage
		,LogDetailLocation
		,LogTime
	)
	SELECT
	MachineName = @@SERVERNAME
	,LogSource = 'UMRA - User Name Merge'
	,LogLevel =
		CASE IsProcessed
			WHEN 'Y' THEN 'INFO'
			ELSE 'WARN'
		END
	,LogMessage =
		CASE IsProcessed
			WHEN 'Y' THEN 'SUCCESS merging "' + z.NewUserName + '". The old username: "' + z.OrigADUserName + '" was updated in the PatientFirstIntranet.dbo.Users table.'
			ELSE 'FAILURE merging "' + z.NewUsername + '". The old username: "' + z.OrigADUsername + '" was NOT FOUND in the PatientFirstIntranet.dbo.Users table.'
		END
	,LogDetailLocation = 'Ulysses History Test'
	,LogTime = GETDATE()
	FROM
	PatientFirstIntranet.dbo.EmployeeNameChangeLog z
	WHERE
	DateMerged = @LogTime
	;

	DROP TABLE #MergeChangeTable ;
--ROLLBACK TRAN
COMMIT TRAN