/*
Open the Facilitator Update.xlsx file and update the table as required.  
Then copy the SQL column (excluding the SQL header) and paste the values into the block below (line 39)
*/
USE ClickDW
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
CREATE TABLE #Data
(
	RowNumber int not null identity(2,1),
	CurrentSchool varchar(100) not null,
	CurrentFirstName varchar(50) not null,
	CurrentLastName varchar(50) not null,
	CurrentEmail varchar(100) not null,
	CurrentCell varchar(20) not null,
	UpdateFirstName varchar(50) not null,
	UpdateLastName varchar(50) not null,
	UpdateEmail varchar(100) not null,
	UpdateCell varchar(20) not null,
	MoveSchool varchar(100) not null,
	AddSchool varchar(100) not null,
	RemoveSchool varchar(100) not null,
	--
	CurrentSchoolID int null,
	CurrentSchoolEntityID int null,
	MoveSchoolID int null,
	MoveSchoolEntityID int null,
	AddSchoolID int null,
	AddSchoolEntityID int null,
	RemoveSchoolID int null,
	RemoveSchoolEntityID int null,
	PersonID int null,
	NewPersonID int null,
)

--==============================================================================================================================
--PASTE BETWEEN THESE TWO LINES ================================================================================================
INSERT #Data(CurrentSchool,CurrentFirstName,CurrentLastName,CurrentEmail,CurrentCell,UpdateFirstName,UpdateLastName,UpdateEmail,UpdateCell,MoveSchool,AddSchool,RemoveSchool) SELECT '109327409','Saeed','Cornelson','S.Cornelson@lab.clicklearning.org','','','','','','109320609','',''
INSERT #Data(CurrentSchool,CurrentFirstName,CurrentLastName,CurrentEmail,CurrentCell,UpdateFirstName,UpdateLastName,UpdateEmail,UpdateCell,MoveSchool,AddSchool,RemoveSchool) SELECT '108470392','Devinice Jaundre','Fortuin','d.fortuin@lab.clicklearning.org','','','','','','108470732','',''


--PASTE BETWEEN THESE TWO LINES ================================================================================================
--==============================================================================================================================

DECLARE @DefaultFacilitatorPersonRoles varchar(1000) = 'Facilitator';
DECLARE @DefaultFacilitatorEntityRoles varchar(1000) = 'Get Reports';
DECLARE @DefaultIdentityUserPassword nvarchar(max) = null; --'AQAAAAEAACcQAAAAEGo+sF/b7/wLqSVp3sqaeUGAA8fsoUK1tIdIFM6JfOlM2i7OBao4pu/5VKJNQXT5nQ==';

DECLARE 
	@TotalRowCount int,
	@RowCount int;

SET @TotalRowCount = (SELECT COUNT(*) FROM #Data);
RAISERROR('Source rows: %d', 0, 0, @TotalRowCount) WITH NOWAIT;

CREATE UNIQUE INDEX IX ON #Data(RowNumber);

DECLARE @DefaultFacilitatorPersonRoleID dbo.tIntTable;
DECLARE @DefaultFacilitatorEntityRoleID dbo.tIntTable;

INSERT @DefaultFacilitatorPersonRoleID
SELECT DISTINCT R.PersonRoleID
FROM string_split(@DefaultFacilitatorPersonRoles,',') X
JOIN PersonRole R ON X.value = R.Code OR X.value = R.Desription

INSERT @DefaultFacilitatorEntityRoleID
SELECT DISTINCT R.EntityRoleID
FROM string_split(@DefaultFacilitatorEntityRoles,',') X
JOIN EntityRole R ON X.value = R.Name OR X.value = R.Description

IF EXISTS(
	SELECT *
	FROM #Data D
	WHERE D.CurrentSchool = '' AND D.CurrentFirstName = '' AND D.CurrentLastName = '' AND D.CurrentEmail = '' AND D.CurrentCell = ''
		AND NOT (D.UpdateFirstName <> '' AND D.UpdateLastName <> '' AND D.UpdateEmail <> '' AND D.AddSchool <> '')
)
BEGIN
	SELECT 'Missing data to add new person', D.*
	FROM #Data D
	WHERE D.CurrentSchool = '' AND D.CurrentFirstName = '' AND D.CurrentLastName = '' AND D.CurrentEmail = '' AND D.CurrentCell = ''
		AND NOT (D.UpdateFirstName <> '' AND D.UpdateLastName <> '' AND D.UpdateEmail <> '' AND D.AddSchool <> '')
	ORDER BY RowNumber
	GOTO ScriptEnd
END

UPDATE D SET
	CurrentSchoolID = C.SchoolID,
	CurrentSchoolEntityID = C.EntityID,
	MoveSchoolID = M.SchoolID,
	MoveSchoolEntityID = M.EntityID,
	AddSchoolID = A.SchoolID,
	AddSchoolEntityID = A.EntityID,
	RemoveSchoolID = R.SchoolID,
	RemoveSchoolEntityID = R.EntityID
FROM #Data D
LEFT JOIN School C ON (D.CurrentSchool = C.EMISCode AND C.EMISCode <> '') OR D.CurrentSchool = C.Name
LEFT JOIN School M ON (D.MoveSchool    = M.EMISCode AND M.EMISCode <> '') OR D.MoveSchool    = M.Name
LEFT JOIN School A ON (D.AddSchool     = A.EMISCode AND A.EMISCode <> '') OR D.AddSchool     = A.Name
LEFT JOIN School R ON (D.RemoveSchool  = R.EMISCode AND R.EMISCode <> '') OR D.RemoveSchool  = R.Name

IF EXISTS(SELECT * FROM #Data WHERE CurrentSchool <> '' AND CurrentSchoolID IS NULL)
BEGIN
	SELECT 'Unknown Current School', * FROM #Data WHERE CurrentSchool <> '' AND CurrentSchoolID IS NULL ORDER BY RowNumber;
	GOTO ScriptEnd
END

IF EXISTS(SELECT * FROM #Data WHERE MoveSchool <> '' AND MoveSchoolID IS NULL)
BEGIN
	SELECT 'Unknown Move School', * FROM #Data WHERE MoveSchool <> '' AND MoveSchoolID IS NULL ORDER BY RowNumber;
	GOTO ScriptEnd
END

IF EXISTS(SELECT * FROM #Data WHERE AddSchool <> '' AND AddSchoolID IS NULL)
BEGIN
	SELECT 'Unknown Add School', * FROM #Data WHERE AddSchool <> '' AND AddSchoolID IS NULL ORDER BY RowNumber;
	GOTO ScriptEnd
END

IF EXISTS(SELECT * FROM #Data WHERE RemoveSchool <> '' AND RemoveSchoolID IS NULL)
BEGIN
	SELECT 'Unknown Remove School', * FROM #Data WHERE RemoveSchool <> '' AND RemoveSchoolID IS NULL ORDER BY RowNumber
	GOTO ScriptEnd
END

UPDATE #Data SET 
	CurrentCell = CASE WHEN CurrentCell = '' THEN '' ELSE ClickCOMMON.dbo.fFormatCellNumber(CurrentCell) END,
	UpdateCell  = CASE WHEN UpdateCell  = '' THEN '' ELSE ClickCOMMON.dbo.fFormatCellNumber(UpdateCell)  END

UPDATE #Data SET PersonID = NULL;

;WITH personIDs(RowNumber, PersonID) AS
(
	SELECT D.RowNumber, P.PersonID
	FROM #Data D
	JOIN Person P ON ((D.CurrentFirstName = '') OR (D.CurrentFirstName = P.FirstName)) AND ((D.CurrentLastName = '') OR (D.CurrentLastName = P.LastName))
	LEFT JOIN PersonHasContact PC ON P.PersonID = PC.PersonID AND PC.ContactTypeID = 1 AND PC.[Order] = 1 AND ((D.CurrentCell = '') OR (D.CurrentCell = PC.Value))
	LEFT JOIN PersonHasContact PE ON P.PersonID = PE.PersonID AND PE.ContactTypeID = 4 AND PE.[Order] = 1 AND ((D.CurrentEmail = '') OR (D.CurrentEmail = PE.Value))
	WHERE (D.CurrentSchool <> '' OR D.CurrentFirstName <> '' OR D.CurrentLastName <> '' OR D.CurrentEmail <> '' OR D.CurrentCell <> '')
		AND ((D.CurrentCell = '') OR (D.CurrentCell <> '' AND PC.PersonID IS NOT NULL))
		AND ((D.CurrentEmail = '') OR (D.CurrentEmail <> '' AND PE.PersonID IS NOT NULL))
)
UPDATE D SET 
	PersonID = ID.PersonID, 
	CurrentSchoolID = S.SchoolID,
	CurrentSchoolEntityID = S.EntityID
	--CurrentSchool = S.EMISCode
FROM #Data D
JOIN personIDs ID ON D.RowNumber = ID.RowNumber
OUTER APPLY
(
	SELECT TOP 1 EP.*
	FROM EntityPerson EP
	JOIN PersonRoleHasEntityPerson PREP ON EP.EntityID = PREP.EntityID AND EP.PersonID = PREP.PersonID AND PREP.PersonRoleID IN (3)
	WHERE EP.PersonID = ID.PersonID
		AND ((D.CurrentSchool = '') OR (D.CurrentSchool <> '' AND EP.EntityID = D.CurrentSchoolEntityID))
) EP
LEFT JOIN School S ON EP.EntityID = S.EntityID
WHERE EP.PersonID IS NOT NULL

UPDATE D SET 
	PersonID = P.PersonID
FROM #Data D
JOIN Person P ON ((D.CurrentFirstName = '') OR (D.CurrentFirstName = P.FirstName)) AND ((D.CurrentLastName = '') OR (D.CurrentLastName = P.LastName))
JOIN vwPersonRoleHasPerson PRP ON P.PersonID = PRP.PersonID AND PRP.PersonRoleID IN (3)
WHERE (D.CurrentSchool <> '' OR D.CurrentFirstName <> '' OR D.CurrentLastName <> '' OR D.CurrentEmail <> '' OR D.CurrentCell <> '')
	AND D.PersonID IS NULL

IF EXISTS(
	SELECT *
	FROM #Data D
	WHERE (D.CurrentSchool <> '' OR D.CurrentFirstName <> '' OR D.CurrentLastName <> '' OR D.CurrentEmail <> '' OR D.CurrentCell <> '')
		AND D.PersonID IS NULL
)
BEGIN
	SELECT 'Can''t find Facilitator', D.*
	FROM #Data D
	WHERE (D.CurrentSchool <> '' OR D.CurrentFirstName <> '' OR D.CurrentLastName <> '' OR D.CurrentEmail <> '' OR D.CurrentCell <> '')
		AND D.PersonID IS NULL
	ORDER BY D.RowNumber
	GOTO ScriptEnd;
END

SET @RowCount = (SELECT COUNT(*) FROM #DATA WHERE PersonID IS NOT NULL);
RAISERROR('Matched persons: %d', 0, 0, @RowCount) WITH NOWAIT;

EXEC ClickCOMMON.dbo.pResetSequence 'ClickDW', 'dbo', 'Person', @Silent=1

IF EXISTS(
	SELECT *
	FROM #Data D
	JOIN Person P ON D.UpdateFirstName = P.FirstName AND D.UpdateLastName = P.LastName
	JOIN PersonHasContact PE ON P.PersonID = PE.PersonID AND PE.ContactTypeID = 4 AND PE.[Order] = 1 AND PE.[Value] = D.UpdateEmail
	LEFT JOIN PersonRoleHasPerson PRP ON P.PersonID = PRP.PersonID AND PRP.PersonRoleID = 3
	OUTER APPLY (SELECT COUNT(*) Cnt FROM PersonRoleHasEntityPerson PREP WHERE PREP.PersonID = P.PersonID AND PREP.PersonRoleID = 3) PREP
	WHERE D.PersonID IS NULL
		AND (PRP.PersonID IS NOT NULL OR ISNULL(PREP.Cnt,0) > 0)
)
BEGIN
	SELECT 'Facilitator exists already', D.*
	FROM #Data D
	JOIN Person P ON D.UpdateFirstName = P.FirstName AND D.UpdateLastName = P.LastName
	JOIN PersonHasContact PE ON P.PersonID = PE.PersonID AND PE.ContactTypeID = 4 AND PE.[Order] = 1 AND PE.[Value] = D.UpdateEmail
	LEFT JOIN PersonRoleHasPerson PRP ON P.PersonID = PRP.PersonID AND PRP.PersonRoleID = 3
	OUTER APPLY (SELECT COUNT(*) Cnt FROM PersonRoleHasEntityPerson PREP WHERE PREP.PersonID = P.PersonID AND PREP.PersonRoleID = 3) PREP
	WHERE D.PersonID IS NULL
		AND (PRP.PersonID IS NOT NULL OR ISNULL(PREP.Cnt,0) > 0)
	ORDER BY D.RowNumber
	GOTO ScriptEnd
END

UPDATE D SET NewPersonID = NEXT VALUE FOR dbo.PersonSequence
FROM #Data D
WHERE D.PersonID IS NULL

RAISERROR('New persons: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;


UPDATE D SET MoveSchoolID = NULL, MoveSchoolEntityID = NULL
FROM #Data D
WHERE D.MoveSchoolID = D.CurrentSchoolID

UPDATE D SET AddSchoolID = NULL, AddSchoolEntityID = NULL
FROM #Data D
WHERE D.AddSchoolID = D.CurrentSchoolID

--SELECT * FROM #Data

BEGIN TRY
	BEGIN TRAN

-- Update FirstName / LastName
	UPDATE P SET
		P.FirstName = CASE WHEN D.UpdateFirstName <> '' THEN D.UpdateFirstName ELSE P.FirstName END,
		P.LastName  = CASE WHEN D.UpdateLastName  <> '' THEN D.UpdateLastName  ELSE P.LastName  END
	FROM #Data D
	JOIN Person P ON D.PersonID = P.PersonID
	WHERE D.PersonID IS NOT NULL
		AND ((D.UpdateFirstName <> '' AND D.UpdateFirstName <> P.FirstName)
			OR (D.UpdateLastName <> '' AND D.UpdateLastName  <> P.LastName));

	RAISERROR('Person updated: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;

-- Add Person
	INSERT Person(PersonID,FirstName,LastName)
	SELECT D.NewPersonID, D.UpdateFirstName, D.UpdateLastName
	FROM #Data D
	WHERE D.NewPersonID IS NOT NULL

	RAISERROR('Person inserted: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;

-- Update Email/Cell
	IF OBJECT_ID('tempdb..#PersonHasContact') IS NOT NULL DROP TABLE #PersonHasContact;

	SELECT ISNULL(D.PersonID,D.NewPersonID) PersonID, 1 ContactTypeID, 1 [Order], D.UpdateCell [Value]
	INTO #PersonHasContact
	FROM #Data D
	WHERE D.UpdateCell <> '' AND D.UpdateCell <> D.CurrentCell
	UNION
	SELECT ISNULL(D.PersonID,D.NewPersonID) PersonID, 4 ContactTypeID, 1 [Order], D.UpdateEmail [Value]
	FROM #Data D
	WHERE D.UpdateEmail <> '' AND D.UpdateEmail <> D.CurrentEmail

	MERGE PersonHasContact AS target
	USING (SELECT * FROM #PersonHasContact) AS source
	ON target.PersonID = source.PersonID AND target.ContactTypeID = source.ContactTypeID AND target.[Order] = source.[Order]
	WHEN MATCHED AND target.[Value] <> source.[Value] THEN
	UPDATE SET [Value] = source.[Value];

	RAISERROR('PersonHasContact updated: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;

	MERGE PersonHasContact AS target
	USING (SELECT * FROM #PersonHasContact) AS source
	ON target.PersonID = source.PersonID AND target.ContactTypeID = source.ContactTypeID AND target.[Order] = source.[Order]
	WHEN NOT MATCHED THEN
	INSERT (PersonID, ContactTypeID, [Order], [Value]) VALUES (PersonID, ContactTypeID, [Order], [Value]);

	RAISERROR('PersonHasContact inserted: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;

	IF OBJECT_ID('tempdb..#PersonHasContact') IS NOT NULL DROP TABLE #PersonHasContact;

-- IdentityUser
	UPDATE I SET
		UserName = lower(D.UpdateEmail),
		Email = lower(D.UpdateEmail)
	FROM IdentityUser I
	JOIN #Data D ON D.PersonID = I.PersonID
	WHERE I.Email <> D.UpdateEmail AND D.UpdateEmail <> ''

	RAISERROR('IdentityUser updated: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;

	INSERT IdentityUser
	(IdentityUserID,UserName,Email,EmailConfirmed,PasswordHash,TwoFactorEnabled,LockoutEnabled,AccessFailedCount,PersonID)
	SELECT 
		UPPER(NEWID()),
		LOWER(D.CurrentEmail),
		LOWER(D.CurrentEmail),
		1,
		@DefaultIdentityUserPassword,
		0,
		0,
		0,
		ISNULL(D.PersonID,D.NewPersonID)
	FROM #Data D
	LEFT JOIN IdentityUser I ON D.PersonID = I.PersonID
	WHERE I.PersonID IS NULL 
		AND (D.CurrentEmail <> '' AND D.UpdateEmail = '')
	UNION
	SELECT 
		UPPER(NEWID()),
		LOWER(D.UpdateEmail),
		LOWER(D.UpdateEmail),
		1,
		@DefaultIdentityUserPassword,
		0,
		0,
		0,
		ISNULL(D.PersonID,D.NewPersonID)
	FROM #Data D
	LEFT JOIN IdentityUser I ON D.PersonID = I.PersonID
	WHERE I.PersonID IS NULL 
		AND (D.UpdateEmail <> '')
		AND D.RemoveSchoolID IS NOT NULL;

	RAISERROR('IdentityUser inserted: %d', 0, 0, @@ROWCOUNT) WITH NOWAIT;

	IF EXISTS(SELECT * FROM #Data WHERE MoveSchoolID IS NOT NULL)
	BEGIN
		INSERT EntityPerson(EntityID,PersonID)
		SELECT D.MoveSchoolENtityID, D.PersonID
		FROM #Data D
		WHERE D.MoveSchoolID IS NOT NULL

		RAISERROR('MoveSchool - EntityPerson inserted: %d', 0, 0, @@ROWCOUNT);

		INSERT PersonRoleHasEntityPerson(PersonRoleID,EntityID,PersonID)
		SELECT R.PersonRoleID, D.MoveSchoolEntityID, D.PersonID
		FROM #Data D
		JOIN PersonRoleHasEntityPerson R ON D.CurrentSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.MoveSchoolID IS NOT NULL
		UNION
		SELECT X.value, D.MoveSchoolEntityID, D.PersonID
		FROM #Data D
		CROSS JOIN @DefaultFacilitatorPersonRoleID X 
		LEFT JOIN PersonRoleHasEntityPerson R ON D.CurrentSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.MoveSchoolID IS NOT NULL AND R.PersonRoleID IS NULL

		RAISERROR('MoveSchool - PersonRoleHasEntityPerson inserted: %d', 0, 0, @@ROWCOUNT);

		INSERT EntityRoleHasEntityPerson(EntityRoleID,EntityID,PersonID)
		SELECT R.EntityRoleID, D.MoveSchoolEntityID, D.PersonID
		FROM #Data D
		JOIN EntityRoleHasEntityPerson R ON D.CurrentSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.MoveSchoolID IS NOT NULL
		UNION
		SELECT X.value, D.MoveSchoolEntityID, D.PersonID
		FROM #Data D
		CROSS JOIN @DefaultFacilitatorEntityRoleID X
		LEFT JOIN EntityRoleHasEntityPerson R ON D.CurrentSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.MoveSchoolID IS NOT NULL AND R.EntityRoleID IS NULL

		RAISERROR('MoveSchool - EntityRoleHasEntityPerson inserted: %d', 0, 0, @@ROWCOUNT);

		DELETE R
		FROM #Data D
		JOIN EntityRoleHasEntityPerson R ON D.CurrentSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.MoveSchoolID IS NOT NULL

		RAISERROR('MoveSchool - EntityRoleHasEntityPerson deleted: %d', 0, 0, @@ROWCOUNT);

		DELETE R
		FROM #Data D
		JOIN PersonRoleHasEntityPerson R ON D.CurrentSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.MoveSchoolID IS NOT NULL

		RAISERROR('MoveSchool - PersonRoleHasEntityPerson deleted: %d', 0, 0, @@ROWCOUNT);

		DELETE R
		FROM #Data D
		JOIN EntityPerson R ON D.CurrentSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.MoveSchoolID IS NOT NULL

		RAISERROR('MoveSchool - EntityPerson deleted: %d', 0, 0, @@ROWCOUNT);
	END

	IF EXISTS(SELECT * FROM #Data WHERE AddSchoolID IS NOT NULL)
	BEGIN
		INSERT EntityPerson(EntityID,PersonID)
		SELECT D.AddSchoolEntityID, ISNULL(D.PersonID,D.NewPersonID)
		FROM #Data D
		WHERE D.AddSchoolID IS NOT NULL

		RAISERROR('AddSchool - EntityPerson inserted: %d', 0, 0, @@ROWCOUNT);

		INSERT PersonRoleHasEntityPerson(PersonRoleID,EntityID,PersonID)
		SELECT R.value, D.AddSchoolEntityID, ISNULL(D.PersonID,D.NewPersonID)
		FROM #Data D
		CROSS JOIN @DefaultFacilitatorPersonRoleID R
		WHERE D.AddSchoolEntityID IS NOT NULL

		RAISERROR('AddSchool - PersonRoleHasEntityPerson inserted: %d', 0, 0, @@ROWCOUNT);

		INSERT EntityRoleHasEntityPerson(EntityRoleID,EntityID,PersonID)
		SELECT R.value, D.AddSchoolEntityID, ISNULL(D.PersonID,D.NewPersonID)
		FROM #Data D
		CROSS JOIN @DefaultFacilitatorEntityRoleID R
		WHERE D.AddSchoolID IS NOT NULL

		RAISERROR('AddSchool - EntityRoleHasEntityPerson inserted: %d', 0, 0, @@ROWCOUNT);
	END

	IF EXISTS(SELECT * FROM #Data WHERE RemoveSchoolID IS NOT NULL)
	BEGIN
		DELETE R
		FROM #Data D
		JOIN EntityRoleHasEntityPerson R ON D.RemoveSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.RemoveSchoolID IS NOT NULL

		RAISERROR('RemoveSchool - EntityRoleHasEntityPerson deleted: %d', 0, 0, @@ROWCOUNT);

		DELETE R
		FROM #Data D
		JOIN PersonRoleHasEntityPerson R ON D.RemoveSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.RemoveSchoolID IS NOT NULL

		RAISERROR('RemoveSchool - PersonRoleHasEntityPerson deleted: %d', 0, 0, @@ROWCOUNT);

		DELETE R
		FROM #Data D
		JOIN EntityPerson R ON D.RemoveSchoolEntityID = R.EntityID AND D.PersonID = R.PersonID
		WHERE D.RemoveSchoolID IS NOT NULL

		RAISERROR('RemoveSchool - EntityPerson deleted: %d', 0, 0, @@ROWCOUNT);	

		DELETE U
		FROM #Data D
		JOIN IdentityUser U ON D.PersonID = U.PersonID
		CROSS APPLY (SELECT COUNT(*) Cnt FROM EntityPerson EP WHERE D.PersonID = EP.PersonID) EP
		WHERE EP.Cnt = 0

		RAISERROR('RemoveSchool - IdentityUser deleted: %d', 0, 0, @@ROWCOUNT);	
	END

	RAISERROR('COMMITting', 0, 0) WITH NOWAIT;
	COMMIT TRAN
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
	THROW;
END CATCH

ScriptEnd:
