-- version 2.0.0 / 2020-01-08
---------------------------------------------------------------------------------
-- history
--
-- # 2.0.0
-- # Angepasst an neue Datenbankstruktur DB 2.0
--
-- # 1.0.1
-- # WHERE Bedingung beim Ergänzen der Brechtigungen korrigiert / WHERE conditions of update user permissions corrected
-- # Bemerkung bzgl. dbo ergänzt / added note about dbo user
-- # Rollenberechtigungen nicht mehr bei dbo setzen / role permissions are not set anymore for dbo user
--
-- # 1.0.0
-- # Erste Veröffentlichung / Initial Release
---------------------------------------------------------------------------------
DECLARE @userId INT
DECLARE @userName NVARCHAR(128)

-- Benutzernamen hier angeben. Bei Windowsbenutzern mit Domäne.
-- Falls der Benutzer die Serverrolle sysadmin hat, dbo als Namen verwenden.

--Specify user name here. Include domain for windows users.
--If the user has the server role sysadmin use dbo as name.
SET @userName = N'theasoftwebuser'

---------------------------------------------------------------------------------
-- Bitte ab hier nichts mehr ändern. / Please do not change anything below that line.

IF @userName IS NULL OR @userName = ''
BEGIN
	PRINT 'Bitte geben Sie einen Benutzernamen an. / Please specify a user name.'
END
ELSE
BEGIN
	PRINT 'Berechtigungen werden angepasst für ' + @userName + '. / Permissions are updated for ' + @userName + '.'

	SELECT @userId = u.UserId FROM Users u WHERE U.UserName = @userName
	IF @userId IS NULL
	BEGIN
		PRINT 'Keinen Benutzereintrag gefunden. / No user entry found.'
	END
	ELSE
	BEGIN
		PRINT 'Benutzereintrag gefunden. / User entry found. Id = ' + CONVERT(VARCHAR(20), @userId)

		IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Users') AND [name] = 'KnownUserType')
		BEGIN
			PRINT('Adding column Users.KnownUserType ...')
			ALTER TABLE dbo.Users ADD KnownUserType TINYINT NULL
		END

		IF NOT EXISTS (SELECT * FROM dbo.sysindexes o WHERE o.id = OBJECT_ID(N'[dbo].[Users]') AND o.Name = N'UX_KnownUserType')
		BEGIN
			PRINT('Adding unique index for Users.KnownUserType ...')
			EXEC('CREATE UNIQUE NONCLUSTERED INDEX UX_KnownUserType ON dbo.Users(KnownUserType) WHERE KnownUserType IS NOT NULL ON [PRIMARY]')
		END

		--alten user flag weg nehmen
		EXEC ('UPDATE Users SET KnownUserType = NULL WHERE KnownUserType = 1')
		
		--Rolle DataService
		IF(@userName <> 'dbo')
		BEGIN
			PRINT 'Füge Rolle "Dataservice" hinzu. / Adding role "Dataservice"'
			EXEC sp_addrolemember N'Dataservice', @userName
		END

		--neuen user flag setzen
		EXEC sp_executesql N'UPDATE Users SET KnownUserType = 1 WHERE UserID = @userId', N'@userId INT', @userId

		--bühnenanhänge
		IF(@userName <> 'dbo')
		BEGIN
			PRINT 'Füge Rolle "thea.dispo - Stammdaten HasWritePermission" hinzu. / Adding role "thea.dispo - Write master data"'
			EXEC sp_addrolemember N'DispoAdmin', @userName
		END

		--ressourcenanhänge
		IF EXISTS (SELECT * FROM Permissions ug WHERE ug.PermissionArea = 16)
		BEGIN
			IF(@userName <> 'dbo')
			BEGIN
				PRINT 'Füge Rolle "Werkeverwaltung HasWritePermission" hinzu. / Adding role "Write work management"'
				EXEC sp_addrolemember N'WorksWrite', @userName
			END

			PRINT 'Erteile Berechtigungen für Ressourcen. / Granting permissions on resources.'
			INSERT INTO Permissions (OwnerGroupId, UserID, HasWritePermission, LM, UM, LC, UC, PermissionArea)
			SELECT g.OwnerGroupId, @userId, 0, GETDATE(), -1, GETDATE(), -1, 16
			FROM OwnerGroups g WHERE NOT EXISTS(SELECT * FROM Permissions ug WHERE ug.OwnerGroupId = g.OwnerGroupId AND ug.UserId = @userId AND ug.PermissionArea = 16)
		END

		--reiseanhänge
		IF EXISTS (SELECT * FROM Permissions ug WHERE ug.PermissionArea = 15)
		BEGIN
			PRINT 'Erteile Berechtigungen für Reisen. / Granting permissions on trips.'
			INSERT INTO Permissions (OwnerGroupId, UserID, HasWritePermission, LM, UM, LC, UC, PermissionArea)
			SELECT g.OwnerGroupId, @userId, 0, GETDATE(), -1, GETDATE(), -1, 15
			FROM OwnerGroups g WHERE NOT EXISTS(SELECT * FROM Permissions ug WHERE ug.OwnerGroupId = g.OwnerGroupId AND ug.UserId = @userId AND ug.PermissionArea = 15)
		END

		--bestellanhänge
		IF EXISTS (SELECT * FROM Permissions ug WHERE ug.PermissionArea = 12)
		BEGIN
			PRINT 'Erteile Berechtigungen für Bestellungen. / Granting permissions on orders.'
			INSERT INTO Permissions (OwnerGroupId, UserID, HasWritePermission, LM, UM, LC, UC, PermissionArea)
			SELECT g.OwnerGroupId, @userId, 0, GETDATE(), -1, GETDATE(), -1, 12
			FROM OwnerGroups g WHERE NOT EXISTS(SELECT * FROM Permissions ug WHERE ug.OwnerGroupId = g.OwnerGroupId AND ug.UserId = @userId AND ug.PermissionArea = 12)
		END

		--vertragsdokumentanhänge
		IF EXISTS (SELECT * FROM Permissions ug WHERE ug.PermissionArea = 1)
		BEGIN
			PRINT 'Erteile Berechtigungen für Vertragsdaten. / Granting permissions on contract data.'
			UPDATE Permissions SET OptionsFlag = (OptionsFlag & 1) WHERE PermissionArea = 1 AND UserId = @userId

			INSERT INTO Permissions (OwnerGroupId, UserID, HasWritePermission, LM, UM, LC, UC, PermissionArea, OptionsFlag)
			SELECT g.OwnerGroupId, @userId, 0, GETDATE(), -1, GETDATE(), -1, 1, 1
			FROM OwnerGroups g WHERE NOT EXISTS(SELECT * FROM Permissions ug WHERE ug.OwnerGroupId = g.OwnerGroupId AND ug.UserId = @userId AND ug.PermissionArea = 1)
		END

		--kontaktanhänge
		IF EXISTS (SELECT * FROM Permissions ug WHERE ug.PermissionArea = 13)
		BEGIN
			PRINT 'Erteile Berechtigungen für Personen-Anhänge. / Granting permissions on person attachments.'
			UPDATE Permissions SET HasWritePermission = 1 WHERE PermissionArea = 13 AND UserId = @userId

			INSERT INTO Permissions (OwnerGroupId, UserID, HasWritePermission, LM, UM, LC, UC, PermissionArea)
			SELECT g.OwnerGroupId, @userId, 1, GETDATE(), -1, GETDATE(), -1, 13
			FROM OwnerGroups g WHERE NOT EXISTS(SELECT * FROM Permissions ug WHERE ug.OwnerGroupId = g.OwnerGroupId AND ug.UserId = @userId AND ug.PermissionArea = 13)
		END
	END
END