/*
	DIESE SKRIPT MUSS VOR DEM ERSTEN START VON THEASOFT V4 AUSGEFÜHRT WERDEN.
    Das Script aktualisiert die Datenstruktur der theasoft Datenbank.
	
	- Bitte achten Sie darauf das die richtige (theasoft) Datenbank ausgewählt ist. -

	VOR DEM AUSFÜHEN DIESE SKRIPTES SOLLTEN ALLE ANWENDER THEASOFT V4 GESCHLOSSEN HABEN!
	Andernfalls kann es bei dem betreffenden Anwender zu einem Fehler kommen.

	Um zu prüfen ob Anwender theasoft V4 geöffnet haben, markieren Sie die acht folgenden Zeilen
	(von "SELECT" bis "'Theasoft%'") und klicken auf "Ausführen" (oder Taste "F5")
	Zur anschließenden Ausführung des gesamten Skriptes die Markierung entfernen.

	SELECT KeyName AS NAME, LOGINAME, HOSTNAME, LAST_BATCH, LastLoginDate, PROGRAM_NAME, 
	(SELECT TOP 1 DataValue FROM PersonContactData AS pcd INNER JOIN CommunicationTypes AS ct ON pcd.CommunicationTypeId = ct.CommunicationTypeId WHERE pcd.PersonId = users.PersonId AND Medium = 0  AND IsSecret = 0 AND NOT DataValue IS NULL AND DataValue <> '' ORDER BY ct.IsCommercial DESC, ct.Sort, pcd.Sort) AS Fon,
	(SELECT TOP 1 DataValue FROM PersonContactData AS pcd INNER JOIN CommunicationTypes AS ct ON pcd.CommunicationTypeId = ct.CommunicationTypeId WHERE pcd.PersonId = users.PersonId AND Medium = 10 AND IsSecret = 0 AND NOT DataValue IS NULL AND DataValue <> '' ORDER BY ct.IsCommercial DESC, ct.Sort, pcd.Sort) AS Mobil,
	(SELECT TOP 1 DataValue FROM PersonContactData AS pcd INNER JOIN CommunicationTypes AS ct ON pcd.CommunicationTypeId = ct.CommunicationTypeId WHERE pcd.PersonId = users.PersonId AND Medium = 50 AND IsSecret = 0 AND NOT DataValue IS NULL AND DataValue <> '' ORDER BY ct.IsCommercial DESC, ct.Sort, pcd.Sort) AS Email
	FROM sys.sysprocesses INNER	JOIN master.dbo.sysdatabases ON sysprocesses.DBID = sysdatabases.DBID
		LEFT JOIN Users ON (LOGINAME collate database_default = Users.UserName) 
		LEFT JOIN persons ON users.PersonId = persons.PersonId
	WHERE sysdatabases.name IN (SELECT db_name()) AND PROGRAM_NAME LIKE 'Theasoft%'

*/

--#region --------------------- Versions und Datenbankprüfungen ---------

--#region Prüfung auf Theasoft Datenbank
IF NOT EXISTS 
	(SELECT * 
	 FROM sys.objects 
	 WHERE  object_id = OBJECT_ID('dbo.NVData') AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='
		
																																															Print ''
																																															Print '		Keine theasoft V4 Datenbank!!!'	
																																															Print '		Bitte wechseln Sie die Datenbank'
																																															Print ''
																																															Print '=============================================='
																																															Print ''
																																															Print ''
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
																																															Print ''
																																															Print ''
																																															RAISERROR('Keine Theasoft Datenbank!!!', 20, -1) WITH LOG
																																															SET NOEXEC ON
END
GO
--#endregion

--#region Berechtigungsprüfung
 if ((SELECT IS_SRVROLEMEMBER('sysadmin'))+(SELECT IS_ROLEMEMBER('db_Owner'))= 0)
 BEGIN
																																															Print ''
																																															Print ''
																																															Print '=================================================================='
		
																																															Print ''
																																															Print '		Keine ausreichenden Berechtigungen auf der Datenbank!!!'	
																																															Print '		Bitte führen Sie das Skript als SysAdmin oder db_owner aus'
																																															Print ''
																																															Print '=================================================================='
																																															Print ''
																																															Print ''	
																																															Print ''
																																															Print ''
																																															RAISERROR('Nicht genügend Berechtigungen!!!', 20, -1) WITH LOG
																																															SET NOEXEC ON
END
GO
--#endregion Berechtigungsprüfung

--#region SQL-Server Versionsprüfung
DECLARE @serverVersion INT
SELECT @serverVersion = CONVERT(INTEGER, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))
	IF @serverVersion < 10
	
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='
																																															Print ''
																																															Print '		Die SQL-Server-Version 2005 u. älter wird nicht mehr unterstützt!!!'	
																																															Print '		Bitte migrieren Sie auf eine aktuelle Version'
																																															Print ''
																																															Print '=============================================='
																																															Print ''
																																															Print ''
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
																																															Print ''
																																															Print ''
																																															RAISERROR('Nicht unterstützte SQL-Server-Version!!!', 20, -1) WITH LOG
																																															SET NOEXEC ON
END
GO
--#endregion

--#region Datenbankmodel Versionsprüfung 216 (letzte Version 2021)
IF EXISTS (SELECT * FROM NvData WHERE  ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 216 OR (Minor = 216 AND Build < 100)))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Der Versionsunterschied ist zu groß!!!'	
																																															Print '		Bitte führen Sie DbScript_2021.sql aus dem Unterordner OptionalScripts aus '
																																															Print '		bevor Sie DBScript.sql ausführen      '
																																															Print ''
																																															Print '=============================================='
																																															Print ''
																																															Print ''																																															Print''
																																															Print''
																																															RAISERROR(N'Datenbankversion zu alt!!!', 20, -1) WITH LOG
																																															SET NOEXEC ON
END
GO
--#endregion

--#region Datenbankmodel Versionsprüfung 221
--#region Datenbankmodel Versionsprüfung 231
IF EXISTS (SELECT * FROM NvData WHERE  ByModulScript = 1 AND (Major > 4 OR (Major = 4 AND (Minor >= 231))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Veraltete Skriptversion!!!'	
																																															Print '		Bitte führen Sie DbScript_2023.sql bzw. DbScript.sql aus.'
																																															Print ''
																																															Print '=============================================='
																																															Print ''
																																															Print ''																																															Print''
																																															Print''
																																															RAISERROR(N'Skriptversion veraltet!!!', 20, -1) WITH LOG
																																															SET NOEXEC ON
END
GO
--#endregion

--#endregion --------------------- Ende Versions und Datenbankprüfungen ----

--#region --------------------- HilfsProzeduren -------------------------

--#region Standards setzen
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--#endregion



--#region PrepareUniqueColumnIndex
IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].PrepareUniqueColumnIndex'))
BEGIN
	DROP PROCEDURE dbo.PrepareUniqueColumnIndex 
END
Go
CREATE PROCEDURE dbo.PrepareUniqueColumnIndex 
	(@tablename varchar(100),
	@columnname varchar(100),
	@primarykey varchar(100),
	@type varchar(100))
AS
BEGIN
	DECLARE @cmd NVARCHAR(MAX);
	SELECT @cmd='UPDATE '+@tablename+' SET '+@columnname+' = b.Neu 
		FROM '+ 
		@tablename+' as a inner join (
				SELECT 
					CAST
					(
					(CAST(ROW_NUMBER() OVER 
						(PARTITION BY '+ 
		@columnname+' ORDER BY LM) 
					AS NVARCHAR(2)) +COALESCE('+ 
		@columnname+',''#NAME#'')) AS '+@type+') AS neu
					,ROW_NUMBER() OVER (PARTITION BY 
		'+@columnname+' ORDER BY lm) AS nummer,
		'+@primarykey+'
				FROM 
		'+@tablename+'
				) AS b 
	ON 
		a.'+@primarykey+'=b.'+@primarykey+'
	WHERE b.nummer>1';
	EXECUTE SP_EXECUTESQL @cmd
END
GO
--#endregion



--#region DropColumn


IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].DropConstraint'))
BEGIN
	DROP PROCEDURE dbo.DropConstraint 
END
GO
CREATE PROCEDURE dbo.DropConstraint 
	@tablename VARCHAR(100),
	@columnname VARCHAR(100)
AS
BEGIN
		DECLARE @defname VARCHAR(100)
		SELECT @defname = name 
		FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid 
			WHERE object_name(so.parent_obj) =  @tablename
				AND so.xtype = 'D'
				AND sc.colid = 	(SELECT colid FROM syscolumns 
								WHERE id = OBJECT_ID(N'[dbo].[' + @tablename + ']') AND name = @columnname)

		IF (NOT @defname IS NULL)
		BEGIN
			DECLARE @cmd VARCHAR(100)
			SELECT @cmd='ALTER TABLE [' + @tablename + '] DROP CONSTRAINT ' + @defname
			EXEC (@cmd)
			PRINT('Constraint '+ @defname + ' dropped!')
		END
END
GO


IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].DropColumn'))
BEGIN
	DROP PROCEDURE dbo.DropColumn 
END
GO
CREATE PROCEDURE dbo.DropColumn 
	@tablename varchar(100),
	@columnname varchar(100)
AS
BEGIN

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = @columnname AND o.id = OBJECT_ID(N'[dbo].[' + @tablename + ']'))	

	BEGIN
		EXEC DropConstraint @tablename, @columnname
		----TODO: Ggf. Index entf.
		--declare @defname varchar(100)
		--declare @cmd varchar(100)
		--SELECT @defname = name 

		--FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid 
		--	WHERE object_name(so.parent_obj) =  @tablename
		--		AND so.xtype = 'D'
		--		AND sc.colid = 	(SELECT colid FROM syscolumns 
		--						WHERE id = OBJECT_ID(N'[dbo].[' + @tablename + ']') AND name = @columnname)

		--SELECT @cmd='alter table ['+ @tablename + '] drop constraint ' + @defname
		--EXEC (@cmd)

		DECLARE @cmd VARCHAR(100)

		IF EXISTS (SELECT TOP 1 S.NAME FROM SYS.SYSOBJECTS S JOIN SYS.SYSCOLUMNS C ON S.ID = C.CDEFAULT
			WHERE C.ID = OBJECT_ID(N'[dbo].[' + @tablename + ']') AND C.NAME =  @columnname)
		BEGIN

			SELECT @cmd ='sp_unbindefault ''[' +  @tablename + '].' + @columnname + ''''
			EXEC (@cmd)
		END
		
		SELECT @cmd='alter table ['+@tablename+ '] drop COLUMN [' + @columnname + ']'
		EXEC (@cmd)
		print('Column '+@columnname+' dropped!')
	END
END
GO

--#endregion

--Stored Procedure zum Löschen von Rollen, die vorher allerdings alle Member aus de Rolle entfernt
--#region DropRoleWithDropRoleMember

IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].DropRoleWithDropRoleMember'))
BEGIN
	DROP PROCEDURE dbo.DropRoleWithDropRoleMember 
END
GO
CREATE PROCEDURE dbo.DropRoleWithDropRoleMember 
	@role varchar(100)
AS 

BEGIN
	DECLARE @RoleName sysname
	set @RoleName = @role
	IF  EXISTS (SELECT * FROM dbo.sysusers WHERE  name = @RoleName AND issqlrole = 1)
	Begin
 
      DECLARE @RoleMemberName sysname
      /* Cursor to Loop in for Each Member have the Role Privilege AND Drop RoleMember */
      DECLARE Member_Cursor CURSOR FOR
      select [name]
      from dbo.sysusers
      WHERE  uid in (
            select memberuid
            from dbo.sysmembers
            WHERE  groupuid in (
                  select uid
                  FROM dbo.sysusers WHERE  [name] = @RoleName AND issqlrole = 1))
 
      OPEN Member_Cursor;
 
      FETCH NEXT FROM Member_Cursor
      into @RoleMemberName
 
      WHILE @@FETCH_STATUS = 0
      BEGIN
 
            exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName
 
            FETCH NEXT FROM Member_Cursor
            into @RoleMemberName
      END;
 
      CLOSE Member_Cursor;
      DEALLOCATE Member_Cursor;
      /* End Of Cursor */
 
	END
	
	/* Checking If Role Name Exists In Database */
	IF  EXISTS (SELECT * FROM dbo.sysusers WHERE  name = @RoleName)
		EXEC sp_droprole @rolename = @RoleName
	
END
go
--#endregion

--#region ConvertColumnToXML
IF  EXISTS (SELECT * FROM  sys.objects o WHERE o.object_id = OBJECT_ID(N'[dbo].ConvertColumnToXml'))
BEGIN
	DROP PROCEDURE dbo.ConvertColumnToXml 
END
GO
CREATE PROCEDURE dbo.ConvertColumnToXml 
	@tablename VARCHAR(100),
	@columnname VARCHAR(100),
	@sparse BIT = 0,
	@endTag VARCHAR(100) = ''
AS 

BEGIN
	DECLARE @collation VARCHAR(100)
	DECLARE @codePage VARCHAR(4)
	DECLARE @sql nvarchar(4000)
	DECLARE @systemTypeId TINYINT
	DECLARE @maxLength SMALLINT
	DECLARE @isSparse BIT
	DECLARE @sqlSparse VARCHAR (10)
	DECLARE @debug BIT

	SET @debug = 0

	IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@tablename) AND [name] = @columnname AND system_type_id IN (35, 99, 167, 231)) BEGIN
		PRINT('Changing ' + @tablename + '.' + @columnname + ' to XML...')

		SELECT @systemTypeId = s.system_type_id, @maxLength = s.max_length, @isSparse = s.is_sparse
		FROM sys.columns s WHERE s.object_id = OBJECT_ID(@tablename) AND s.[name] = @columnname

		IF @isSparse = 1 OR @sparse = 1
        	SET @sqlSparse = 'SPARSE'
        ELSE
			SET @sqlSparse = ''
        
		IF @debug = 1
			PRINT ('	isSparse = ' + CONVERT(VARCHAR, @isSparse))

		--varchar oder nvarchar die nicht max sind von kaputten einträgen bereinigen
		IF(@systemTypeId IN (167, 231) AND @maxLength <> -1) BEGIN
			PRINT('	Increasing size of ' + @tablename + '.' + @columnname + ' to MAX...')
			
			-- konvertieren in den passenden max, damit das replace platz hat
			IF @systemTypeId = 167 BEGIN
				SET @sql = 'ALTER TABLE [' + @tablename + '] ALTER COLUMN ['+@columnname + '] VARCHAR(MAX) ' + @sqlSparse + ' NULL'
			END ELSE BEGIN
				SET @sql = 'ALTER TABLE [' + @tablename + '] ALTER COLUMN ['+@columnname + '] NVARCHAR(MAX) ' + @sqlSparse + ' NULL'
			END

			IF @debug = 1
				PRINT @sql

			EXECUTE sp_executesql @sql

			IF @endTag = '' BEGIN
				SET @endTag = '>'
			END
		END

		IF (@endTag <> '') BEGIN
			PRINT('	Removing broken XML content from ' + @tablename + '.' + @columnname + '...')
			SET @sql = ('UPDATE ['+@tablename +'] SET ['+@columnname+'] = NULL WHERE ['+@columnname+'] IS NOT NULL AND NOT ['+@columnname+'] LIKE ''%'+ @endTag +'%''')
			
			IF @debug = 1
				PRINT @sql

			EXECUTE sp_executesql @sql
		END

		-- bei text und varchar das encoding der xml daten anpassen
		IF @systemTypeId IN (35, 167) BEGIN
			SELECT @collation = collation_name FROM sys.columns c WHERE c.name = @columnname AND c.object_id = OBJECT_ID(@tablename)
			SELECT @codePage = CONVERT(VARCHAR(4),COLLATIONPROPERTY( [name], 'CodePage')) FROM sys.fn_helpcollations() WHERE [name] = @collation
			PRINT('	Collation is ' + @collation)
			PRINT('	CodePage is ' + @codePage)
			PRINT('	Update ' + @tablename + '.' + @columnname + ' XML content to use different encoding...')

			SET @sql = 'UPDATE [' + @tablename + '] SET [' + @columnname + '] =  REPLACE(REPLACE(CONVERT(VARCHAR(MAX),['+@columnname +']),
			 ''<?xml version="1.0" encoding="utf-8"?>'',''<?xml version="1.0" encoding="windows-'+ @codepage +'"?>''),
			  ''<?xml version="1.0" encoding="utf-16"?>'',''<?xml version="1.0" encoding="windows-'+ @codepage +'"?>'')
			  WHERE NOT [' + @columnname+'] IS NULL'
		
			IF @debug = 1
				PRINT @sql
			
			EXECUTE sp_executesql @sql
		END ELSE BEGIN
			
			PRINT('	Update ' + @tablename + '.' + @columnname + ' XML content to use utf-16 encoding...')

			SET @sql = 'UPDATE [' + @tablename + '] SET [' + @columnname + '] =  REPLACE(CONVERT(NVARCHAR(MAX),['+@columnname +']),
			 ''<?xml version="1.0" encoding="utf-8"?>'',''<?xml version="1.0" encoding="utf-16"?>'')
			  WHERE NOT [' + @columnname+'] IS NULL'
		
			IF @debug = 1
				PRINT @sql
			
			EXECUTE sp_executesql @sql
		END
		
		PRINT('	Converting column to XML...')
		SET @sql = 'ALTER TABLE ['+@tablename +'] ALTER COLUMN ['+@columnname+'] XML ' + @sqlSparse + ' NULL'
		
		IF @debug = 1
			PRINT @sql
		
		EXECUTE sp_executesql @sql
	END
END
GO
--#endregion ConvertColumnToXML
--#endregion --------------------- Ende HilfsProzeduren --------------------

--------------------- Begin Änderungsblock --------------------
--------------------- Scripterstelldatum : 30.12.2021 ---------
--------------------- Datenbankänderungen ab hier -------------

--#region ----------- 4.216.x Release Übertrag aus CreateDB_2021.sql ----------------

IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID(N'dbo.GetDutyRosterByContractsInternal')) DROP PROCEDURE dbo.GetDutyRosterByContractsInternal
GO

CREATE PROCEDURE [dbo].[GetDutyRosterByContractsInternal]
	@start DATE,
	@end DATE,
	@contractids IdFilterTable READONLY,
	@where NVARCHAR(MAX),
	@include INT,
	@topFromEnd INT,
	@userId INT,
	@personId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @e NVARCHAR(MAX)
	SET NOCOUNT ON
	
	CREATE TABLE #Permissions (ContractId INT NOT NULL, StartDate DATE NOT NULL, EndDate DATE NOT NULL)

	IF (NOT @personId IS NULL)
		BEGIN
			CREATE TABLE #OwnContracts (ContractId INT NOT NULL)
			INSERT INTO #OwnContracts SELECT ci.Id 
				FROM @contractids AS ci
					INNER JOIN Contracts AS c ON ci.Id = c.ContractId
				WHERE c.PersonId = @personId

			IF ((SELECT COUNT(*) FROM #OwnContracts) > 0)
				BEGIN
					INSERT INTO #Permissions (ContractId, StartDate, EndDate) SELECT ContractId, @start, @end FROM #OwnContracts
					DECLARE @otherContractids IdFilterTable
					INSERT INTO @otherContractids SELECT c.Id FROM @contractids AS c WHERE NOT EXISTS (SELECT * FROM #OwnContracts AS oc WHERE oc.ContractId = c.Id)
					INSERT INTO #Permissions SELECT * FROM dbo.DutyRosterPeriodPermissionByContracts (@start, @end, @otherContractids, @userId)
				END
			ELSE
				BEGIN
					INSERT INTO #Permissions SELECT * FROM dbo.DutyRosterPeriodPermissionByContracts (@start, @end, @contractids, @userId)
				END
		END
	ELSE
		BEGIN
			INSERT INTO #Permissions SELECT * FROM dbo.DutyRosterPeriodPermissionByContracts (@start, @end, @contractids, @userId)
		END
	
	SET @SQLQuery =	N'SELECT '
	IF (@topFromEnd > 0)  SET @SQLQuery = @SQLQuery + N' TOP ' + CAST(@topFromEnd AS VARCHAR(10))
	SET @SQLQuery = @SQLQuery + N' dp.* '

	SET @SQLQuery =@SQLQuery + N' FROM DutyRoster AS dp
						INNER JOIN #Permissions AS p ON dp.ContractId = p.ContractId AND dp.[Date] BETWEEN p.StartDate AND p.EndDate '
	
	 IF (ISNULL(@where, '') <> '' )  SET @SQLQuery = @SQLQuery + ' AND ' + @where
	 IF (@topFromEnd > 0)  SET @SQLQuery = @SQLQuery + ' ORDER BY dp.Date DESC'

	 EXECUTE sp_Executesql @SQLQuery
	--Weiteres Where wird aus Performancegründen ignoriert 	
	IF (@include&1 = 1) 
		SELECT NULL AS [#1], * FROM BonusDutyRosterData AS b
			INNER JOIN #Permissions AS p ON b.ContractId = p.ContractId AND b.[Date] BETWEEN p.StartDate AND p.EndDate 

	IF (@include&2 = 2) 
		SELECT NULL AS [#2], * FROM CostUnitWorkingTimes AS cuwt 
			INNER JOIN DutyRoster AS dp ON cuwt.ShiftDayId = dp.DutyRosterId
			INNER JOIN #Permissions AS p ON dp.ContractId = p.ContractId AND dp.[Date] BETWEEN p.StartDate AND p.EndDate 
END
GO

IF EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.GetDutyRosterByDepartmentInternal')) DROP PROCEDURE dbo.GetDutyRosterByDepartmentInternal
GO
CREATE PROCEDURE dbo.GetDutyRosterByDepartmentInternal
	@start DATE,
	@end DATE,
	@Departmentids IdFilterTable READONLY,
	@where NVARCHAR(MAX),
	@include INT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	DECLARE @SQLQuery AS NVARCHAR(MAX)

	SET NOCOUNT ON
	
	CREATE TABLE #Permissions (DepartmentId INT NOT NULL,  StartDate DATE NOT NULL, EndDate DATE NOT NULL)
	INSERT INTO #Permissions SELECT * FROM dbo.DutyRosterPeriodPermissionByDepartments (@start,  @end, @Departmentids, @userId)

	SET @SQLQuery = 'SELECT dp.* 
					FROM DutyRoster AS dp 
						INNER JOIN ContractDepartments AS cd ON dp.ContractId = cd.ContractId 
						INNER JOIN WorkAreas AS w ON cd.WorkAreaId = w.WorkAreaId
						INNER JOIN #Permissions AS p ON w.DepartmentId = p.DepartmentId
					WHERE dp.[Date] BETWEEN p.StartDate AND p.EndDate'

	  IF (ISNULL(@where, '') <> '' )  SET @SQLQuery = @SQLQuery + ' AND ' + @where
	 EXECUTE sp_Executesql @SQLQuery

	IF (@include&1 = 1) 
		SELECT NULL AS [#1], * FROM BonusDutyRosterData AS b
			INNER JOIN #Permissions AS p ON b.ContractId = p.ContractId AND b.[Date] BETWEEN p.StartDate AND p.EndDate 

	IF (@include&2 = 2) 
		SELECT NULL AS [#2], * FROM CostUnitWorkingTimes AS cuwt
			INNER JOIN DutyRoster AS dp ON cuwt.ShiftDayId = dp.DutyRosterId
			INNER JOIN  #Permissions AS p ON dp.ContractId = p.ContractId AND dp.[Date] BETWEEN p.StartDate AND p.EndDate 
END
GO

IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.AbContractMeasureGroups_Update') AND type <> 'U') DROP VIEW dbo.[AbContractMeasureGroups_Update]
GO

IF EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.GetDutyRosterByContractsCore')) DROP PROCEDURE dbo.GetDutyRosterByContractsCore
GO
CREATE PROCEDURE dbo.GetDutyRosterByContractsCore
	@start DATE,
	@end DATE,
	@contractids IdFilterTable READONLY,
	@include INT
AS
BEGIN
	
	SET NOCOUNT ON
	DECLARE @userId INT
	DECLARE @personId INT
	SELECT @userId = UserId, @personId = PersonId FROM Users WHERE UserName  = CURRENT_USER

	IF (NOT @personId IS NULL) 
	BEGIN
		IF (IS_MEMBER('PersoWriteOwnRoster') = 0 AND IS_MEMBER('PersoWriteOwnRegularRoster') = 0)
		SET @personId = NULL
	END
	
	CREATE TABLE #Permissions (ContractId INT NOT NULL, StartDate DATE NOT NULL, EndDate DATE NOT NULL)
	
	IF (NOT @personId IS NULL)
		BEGIN
			CREATE TABLE #OwnContracts (ContractId INT NOT NULL)
			INSERT INTO #OwnContracts SELECT ci.Id FROM @contractids AS ci INNER JOIN Contracts AS c ON ci.Id = c.ContractId WHERE c.PersonId = @personId

			IF ((SELECT COUNT(*) FROM #OwnContracts) > 0)
				BEGIN
					INSERT INTO #Permissions (ContractId, StartDate, EndDate) SELECT ContractId, @start, @end FROM #OwnContracts
					DECLARE @otherContractids IdFilterTable
					INSERT INTO @otherContractids SELECT c.Id FROM @contractids AS c WHERE NOT EXISTS (SELECT * FROM #OwnContracts AS oc WHERE oc.ContractId = c.Id)
					INSERT INTO #Permissions SELECT * FROM dbo.DutyRosterPeriodPermissionByContracts (@start, @end, @otherContractids, @userId)
				END
			ELSE
				BEGIN
					INSERT INTO #Permissions SELECT * FROM dbo.DutyRosterPeriodPermissionByContracts (@start, @end, @contractids, @userId)
				END
		END
	ELSE
		BEGIN
			INSERT INTO #Permissions SELECT * FROM dbo.DutyRosterPeriodPermissionByContracts (@start, @end, @contractids, @userId)
		END
		
	SELECT dp.* FROM DutyRoster AS dp
		INNER JOIN #Permissions AS p ON dp.ContractId = p.ContractId AND [Date] BETWEEN p.StartDate AND p.EndDate 
	WHERE dp.DutyRosterTypeId = 0

	IF (@include&1 = 1) 
		SELECT NULL AS [#1], * FROM BonusDutyRosterData AS b
			INNER JOIN #Permissions AS p ON b.ContractId = p.ContractId AND b.[Date] BETWEEN p.StartDate AND p.EndDate

	IF (@include&2 = 2) 
		SELECT NULL AS [#2], * FROM CostUnitWorkingTimes AS cuwt
			INNER JOIN DutyRoster AS dp ON cuwt.ShiftDayId = dp.DutyRosterId
			INNER JOIN  #Permissions AS p ON dp.ContractId = p.ContractId AND dp.[Date] BETWEEN p.StartDate AND p.EndDate 

END
GO

ALTER PROCEDURE [dbo].[GetPersonsInternal]
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@ShowPersonsWithoutRights BIT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLQuery AS NVARCHAR(MAX)

	-- *** Wenn weitere Daten mitgeladen werden Temp Tabelle mit  PersonIds erstellen ***
	IF (@include <> 0) 
	BEGIN
	 CREATE TABLE #PersonsNP (PersonId INT NOT NULL)
	End
	-- *** Berechtigungen des Users für Personen (0) in Temp Tabelle ***
	CREATE TABLE #UserGroupPermissions (OwnerGroupId INT NOT NULL, PermissionArea INT NOT NULL, OptionsFlag INT NOT NULL)
	INSERT INTO #UserGroupPermissions SELECT OwnerGroupId, PermissionArea, OptionsFlag FROM Permissions WHERE  UserId = @userId AND PermissionArea IN (0,1,6)
		
	-- *** Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben, ansonsten PersonIds in Temp
	
	if (@include <> 0 OR @ShowPersonsWithoutRights = 0) 
		BEGIN
			SET @SQLQuery = 'INSERT INTO #PersonsNP SELECT Persons.PersonId FROM Persons '
			IF (@ShowPersonsWithoutRights = 0) 
			BEGIN
				SET @SQLQuery =  @SQLQuery + ' INNER JOIN #UserGroupPermissions AS UG ON UG.OwnerGroupId = Persons.OwnerGroupId AND ug.PermissionArea = 0'
			END
		END								
	ELSE		
		BEGIN
			SET @SQLQuery = 'SELECT persons.* FROM Persons  ' 
		END			
	-- *** Weitere Joins ***	 
		IF (@joins&1 = 1)
			SET @SQLQuery = @SQLQuery + ' INNER JOIN PersonCategories ON Persons.PersonCategoryId = PersonCategories.PersonCategoryId '
		
		IF (@joins&2 = 2) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN PersonDetails ON Persons.PersonId = PersonDetails.PersonId '

		 IF (@condition IS NOT NULL AND @condition <> '')
			 SET @SQLQuery = @SQLQuery + ' WHERE ' + @condition 

		if (@include = 0 AND @order IS NOT NULL) 
			SET @SQLQuery = @SQLQuery + ' ORDER BY ' + @order 

		print @sqlquery
		EXECUTE sp_Executesql @SQLQuery 		
	
	IF (@include <> 0) --Wenn keine weiteren Daten mitgeladen werden wurde das Ergebnis bereits zurückgeben
		BEGIN
			SET @SQLQuery = 'SELECT persons.* 
								FROM Persons INNER JOIN  #PersonsNP ON Persons.PersonId = #PersonsNP.PersonId'
				
			If (@order IS NOT NULL)
			BEGIN
			IF (@joins&1 = 1)
				SET @SQLQuery = @SQLQuery + ' INNER JOIN PersonCategories ON Persons.PersonCategoryId = PersonCategories.PersonCategoryId '
				SET @SQLQuery = @SQLQuery  + ' ORDER BY ' + @order 
			END
			print @sqlquery
			EXECUTE sp_Executesql  @SQLQuery
		END

	IF(@include<>0)
	BEGIN
		WITH CTE AS(
			SELECT PP.PersonId
					,MIN(p.OwnerGroupId) AS BaseOwner
                    ,MAX(CONVERT(TINYINT, PP.HasWritePermission)) AS Write
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&1) AS ReadSecret 
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&2) AS WriteQualifications
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&4) AS ReadPersonDetails
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&8) AS WritePersonDetails 
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&16) AS ReadPersonBankData
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&32) AS WritePersonBankData
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&64) AS ReadPersonFeeNotes
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&128) AS WritePersonFeeNotes
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&256) AS WritePersonEmployee
                    ,MAX(P.OptionsFlag&PP.OptionsFlag&512) AS ShowLinkedContacts
					,MAX(P.OptionsFlag&PP.OptionsFlag&2048)+MAX(P.OptionsFlag&PP.OptionsFlag&4096) AS ReadAddressData
					,MAX(P.OptionsFlag&PP.OptionsFlag&8192)+MAX(P.OptionsFlag&PP.OptionsFlag&16384) AS ReadContactData
		
                    FROM PersonPermissions PP  
                           INNER JOIN #UserGroupPermissions P ON PP.OwnerGroupId = P.OwnerGroupId AND P.PermissionArea = 0                            
                    WHERE pp.PersonId IN (SELECT PersonId FROM #PersonsNP)
					GROUP BY pp.PersonId
					
		) SELECT * INTO #includePermissions FROM cte 
	END 

	if (@include&1024 = 1024)   --An den Anfang verschoben um preloading zu optimieren
			SELECT NULL as [#1024],PP.* 
			FROM PersonPermissions PP INNER JOIN #PersonsNP PNP on PNP.PersonId = pp.PersonId

	IF (@include&1 = 1)
			SELECT NULL AS [#1], 
				pk.PersonContactDataId, 
				pk.CommunicationTypeId, 
				pk.PersonId, 
				pk.Sort, 
				pk.IsSecret,
				CASE WHEN IsSecret = 1 AND ReadSecret = 0 THEN '**********' ELSE  pk.DataValue END AS DataValue,
				CASE WHEN IsSecret = 1 AND ReadSecret = 0 THEN '**********' ELSE  pk.Note END AS Note,
				pk.ContactPersonConnectionID,
				pk.LM, pk.UM, pk.LC, pk.UC
			FROM PersonContactData AS pk
				INNER JOIN (
				SELECT pp.PersonId, pp.ReadSecret
				FROM #includePermissions AS PP WHERE PP.ReadContactData > 0 
					) AS sub ON pk.PersonId = sub.PersonId

	IF (@include&2 = 2)
		SELECT NULL AS [#2], PersonAddress.*
		FROM PersonAddress
			INNER JOIN #includePermissions AS PP ON PersonAddress.PersonId = PP.PersonId
		WHERE (PersonAddress.IsSecret = 0 OR ReadSecret > 0) AND PP.ReadAddressData > 0 
					
	IF (@include&4 = 4)
		SELECT NULL AS [#4], PersonDetails.*
		FROM PersonDetails 			
			INNER JOIN #includePermissions ON PersonDetails.PersonId = #includePermissions.PersonId WHERE ReadPersonDetails > 0 OR WRITEPERSONDETAILS>0
		
	IF (@include&8 = 8)
		SELECT NULL AS [#8],PersonEmployee.*
		FROM PersonEmployee
			INNER JOIN #includePermissions ON PersonEmployee.PersonId = #includePermissions.PersonId
			
	IF (@include&16 = 16) 
		SELECT NULL AS [#16], zd.*
		FROM PersonAdditionalData zd 
		WHERE zd.PersonAdditionalDataId IN 
		(SELECT PersonAdditionalDataId FROM PersonAdditionalData
			INNER JOIN	PersonAdditionalDataTypes	AS zdt	ON zd.PersonAdditionalDataTypeId = zdt.PersonAdditionalDataTypeId
			INNER JOIN	#includePermissions			AS gp	ON zd.PersonId = gp.PersonId
			INNER JOIN  #UserGroupPermissions		AS ug	ON zdt.OwnerGroupId = ug.OwnerGroupId			 
		WHERE 
			zdt.IsSecret = 0 OR ug.PermissionArea = 0)
	
	IF (@include&32 = 32) 
		SELECT NULL AS [#32], PersonRelations.*
		FROM PersonRelations
			INNER JOIN #includePermissions ON PersonRelations.PersonId = #includePermissions.PersonId AND ShowLinkedContacts > 0

	IF (@include&64 = 64) 
		SELECT NULL AS [#64], PersonRelations.*
		FROM PersonRelations 
			INNER JOIN #includePermissions ON PersonRelations.ChildPersonId = #includePermissions.PersonId

	IF (@include&128 = 128) 
		SELECT NULL AS [#128], Qualification_Person.*
		FROM Qualification_Person 
			INNER JOIN #includePermissions ON Qualification_Person.PersonId = #includePermissions.PersonId
	
	IF (@include&256 = 256) 
		SELECT NULL AS [#256], BodyMeasurements.*
		FROM BodyMeasurements 
			INNER JOIN #includePermissions ON BodyMeasurements.PersonId = #includePermissions.PersonId
			INNER JOIN #UserGroupPermissions prp ON prp.OwnerGroupId = #includePermissions.baseOwner AND prp.PermissionArea=6
	
	IF (@include&512 = 512) 
		SELECT NULL AS [#512], BankAccounts.*
		FROM BankAccounts 
			INNER JOIN #includePermissions ON BankAccounts.PersonId = #includePermissions.PersonId 
				AND (#includePermissions.ReadPersonBankdata>0 OR #includePermissions.WritePersonBankdata > 0)

	IF (@include&2048 = 2048) 
		SELECT NULL AS [#2048], PersonAttachments.PersonId, Count(*) AS CountAttachments
		FROM PersonAttachments 
			INNER JOIN #includePermissions ON PersonAttachments.PersonId = #includePermissions.PersonId
		GROUP BY PersonAttachments.PersonId

	IF (@include&4096 = 4096) 
		SELECT NULL AS [#4096], Birthdays.*
		FROM Birthdays 
			INNER JOIN #includePermissions ON Birthdays.PersonId = #includePermissions.PersonId		
	IF (@include&8192 = 8192 OR @include&16384 = 16384) 
		SELECT NULL AS [#8192], Contracts.*
		FROM Contracts 
			INNER JOIN #includePermissions ON Contracts.PersonId = #includePermissions.PersonId	AND contracts.OwnerGroupId IN 
			(SELECT OwnerGroupId FROM #UserGroupPermissions WHERE PermissionArea = 1)

	IF (@include&16384 = 16384) 
		SELECT NULL AS [#16384], ContractDepartments.*
		FROM ContractDepartments 
			INNER JOIN Contracts ON ContractDepartments.ContractId=Contracts.ContractId
			INNER JOIN #includePermissions ON Contracts.PersonId = #includePermissions.PersonId	AND contracts.OwnerGroupId IN 
			(SELECT OwnerGroupId FROM #UserGroupPermissions WHERE PermissionArea IN (1, 5))		

	IF (@include&32768 = 32768) 
		SELECT NULL AS [32768], PersonInCategories.*
		FROM PersonInCategories 
			INNER JOIN #PersonsNP ON PersonInCategories.PersonId = #PersonsNP.PersonId	

	IF (@include&65536 = 65536) 
		SELECT NULL AS [65536],pfd.*
		FROM PersonFieldData pfd 
				INNER JOIN PersonFields AS pf ON pfd.PersonFieldId = pf.PersonFieldId
				INNER JOIN #includePermissions AS PP ON pfd.PersonId = PP.PersonId
		WHERE ReadPersonDetails > 0 OR WritePersonDetails > 0 OR pf.ShowOnPage = 1 -- Pressetextseite(1) ist öffentlich

	IF (@include<>0) 
	BEGIN	
		DROP TABLE #includePermissions
		DROP TABLE #PersonsNP
	END
	DROP TABLE #UserGroupPermissions
END
GO
CREATE VIEW [dbo].[AbContractMeasureGroups_Update]
AS 
SELECT * FROM ContractMeasureGroups WHERE
	ContractId IN 
	(
		SELECT Contracts.ContractId 
		FROM [Permissions] AS CoPerMi
			INNER JOIN	Users		ON CoPerMi.Userid = Users.Userid 
			INNER JOIN	Contracts	ON Contracts.OwnerGroupId = CoPerMi.OwnerGroupId 
		WHERE Username = CURRENT_USER AND PermissionArea = 1 AND CoPerMi.HasWritePermission = 1
	UNION 
		SELECT Contracts.ContractId 
		FROM [Permissions] AS PersonPerMi
			INNER JOIN	Users		ON PersonPerMi.Userid = Users.Userid 
			INNER JOIN	Persons		ON PersonPerMi.OwnerGroupId = Persons.OwnerGroupId 
			INNER JOIN	Contracts	ON Contracts.PersonId = Persons.PersonId 
		WHERE Username = CURRENT_USER AND PermissionArea = 0 AND PersonPerMi.OptionsFlag&131072 = 131072
	 )

WITH CHECK OPTION
GO
GRANT SELECT, UPDATE, INSERT, DELETE ON [AbContractMeasureGroups_Update] TO PUBLIC
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('MeasureGroups') AND [name] = 'LevelNumber') ALTER TABLE MeasureGroups ADD LevelNumber VARCHAR(8) NULL
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('MeasureTypePriorities') AND [name] = 'BackColor')	ALTER TABLE MeasureTypePriorities ADD BackColor INT NULL
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('MeasureTypePriorities') AND [name] = 'ForeColor')	ALTER TABLE MeasureTypePriorities ADD ForeColor INT NULL
GO
DropColumn 'EntitlementRules', 'TimeAccountId'
GO
IF EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.DeleteDutyRosterByContractId')) DROP PROCEDURE dbo.DeleteDutyRosterByContractId
GO
--Löschte Dienstplaneinträge und Zulagen pro Vertrag
CREATE PROCEDURE dbo.DeleteDutyRosterByContractId
	@contractIds INT
AS
BEGIN
	
	SET NOCOUNT ON
	
	IF EXISTS (	SELECT *
				FROM [Permissions]  AS P 
				INNER JOIN Users AS u ON p.UserID = u.UserID 
				INNER JOIN Departments AS D ON p.OwnerGroupId = d.OwnerGroupId 
				INNER JOIN WorkAreas AS w ON d.DepartmentId = w.DepartmentId
				INNER JOIN ContractDepartments AS cd ON cd.WorkAreaId = w.WorkAreaId 
				WHERE u.UserName = CURRENT_USER AND 
					PermissionArea IN (1, 5) AND 
					p.HasWritePermission = 1 AND
					cd.ContractId = @contractIds)
	BEGIN
		DELETE FROM DutyRoster WHERE ContractId = @contractIds
		DELETE FROM BonusDutyRosterData WHERE ContractId = @contractIds
	END
END
GO

GRANT EXECUTE ON DeleteDutyRosterByContractId TO Public
GO

--#region nvarchar für personfielddata textvalue
ALTER TABLE PersonFieldData ALTER COLUMN TextValue NVarChar(Max) NULL 
GO
--#endregion nvarchar für personfielddata textvalue

--#region Berechtigungen

GRANT INSERT, UPDATE, DELETE ON RuleCriteriaSelections TO MasterAdmin
REVOKE INSERT, UPDATE, DELETE ON RuleCriteriaSelections TO DispoAdmin
REVOKE INSERT, UPDATE, DELETE ON RuleCriteriaSelections TO PersoAdmin
REVOKE INSERT, UPDATE, DELETE ON RuleCriteriaSelections TO OrchesterAdmin
REVOKE INSERT, UPDATE, DELETE ON RuleCriteriaSelections TO BallettAdmin
REVOKE INSERT, UPDATE, DELETE ON RuleCriteriaSelections TO StatisterieAdmin

--Plandetails: Lesen nicht über View
GRANT SELECT ON PlanColumns TO Public
GRANT SELECT ON PlanColumnContent TO Public

--#endregion

--#region Berechtigungen 2022-01-25  Veraltete Bereche entfernen
UPDATE PlanColumnContent
SET ContentSettings = CAST(REPLACE(CAST(ContentSettings AS NVARCHAR(MAX)), '&lt;Categories&gt;DailyTaskActivity&lt;/Categories&gt;','') AS XML) 
FROM PlanColumnContent WHERE NOT ContentSettings IS NULL AND CAST(ContentSettings AS VARCHAR(MAX)) LIKE '%&lt;Categories&gt;DailyTaskActivity&lt;/Categories&gt;%'
GO
--#endregion

--#region 2022-01-25 InheritPermissions / Berechtigungen für "jeder"

ALTER PROCEDURE [dbo].[InheritPermissions]
AS
BEGIN   
	DECLARE @dbo BIT
	DECLARE @secAdm BIT
	DECLARE @currentUserId INT
	DECLARE @authorizedByGroup BIT
	DECLARE @forAllUsers BIT
	
	SET @currentUserId = 0
	SET @authorizedByGroup = 0
	SET @forAllUsers = 0

	--daten für aktuellen benutzer abfragen, notfalls neu anlegen
	SELECT @currentUserId = UserId, @authorizedByGroup = IsAuthorizedByWindowsGroup FROM Users WHERE UserName = CURRENT_USER
	IF @currentUserId = 0 
	BEGIN
		EXEC AddCurrentUser
		SELECT @currentUserId = UserId, @authorizedByGroup = IsAuthorizedByWindowsGroup FROM Users WHERE UserName = CURRENT_USER
	END

	SELECT @dbo = IS_MEMBER('db_owner') 
	SELECT @secAdm = IS_MEMBER('SecurityAdmin') 
	
	IF (@dbo = 1 OR @secAdm = 1) SET @forAllUsers = 1	

	--UsersInGroups in variable speichern, da im weiteren Verlauf viel damit gearbeitet wird und die abfragen ansonsten sehr langsam sind
	DECLARE @usersInGroups TABLE (GroupId INT, UserId INT)
	INSERT INTO @usersInGroups (GroupId, UserId)
	SELECT ugs.GroupId, ugs.UserId
	FROM UsersInGroups AS ugs

	--wenn über gruppe authorisiert, manuell benutzergruppenzugehörigkeit einfügen
	IF @authorizedByGroup = 1 
	BEGIN
		INSERT INTO @usersInGroups (GroupId, UserId)
		SELECT grp.UserID, @currentUserId
		FROM sys.database_principals dp 
		INNER JOIN Users grp ON dp.name = grp.UserName
		WHERE dp.type = 'R' AND IS_MEMBER(dp.name) = 1 AND dp.name LIKE '[_]%' 
		AND NOT EXISTS(SELECT * FROM @usersInGroups WHERE GroupId = grp.UserID AND UserId = @currentUserId)
	END

	--abfragedaten für alle benutzer die über windowsgruppen authorisiert wurden und ignoriert werden (aktueller benutzer ausgenommen)
	DECLARE @usersAuthorizedByGroup TABLE (userId INT)
	--füllen nur, wenn für alle benutzer berechtigungen erstellt werden
	IF @forAllUsers = 1
	BEGIN 
	   	INSERT INTO @usersAuthorizedByGroup
		SELECT up.UserID FROM Users up WHERE up.IsAuthorizedByWindowsGroup = 1 AND up.UserID <> @currentUserId
    END
	
	--neue Berechtigungen einfügen
	INSERT INTO Permissions (OwnerGroupId, UserID, PermissionArea, HasWritePermission, OptionsFlag, IsInherited, LM, UM, LC, UC)
	SELECT  soll.OwnerGroupId, ugs.UserID, soll.PermissionArea, CAST(COUNT(soll.HasWritePermission) AS BIT),
	  		  MAX(soll.OptionsFlag&1)
			 +MAX(soll.OptionsFlag&2)
			 +MAX(soll.OptionsFlag&4)
			 +MAX(soll.OptionsFlag&8)
			 +MAX(soll.OptionsFlag&16)
			 +MAX(soll.OptionsFlag&32)
			 +MAX(soll.OptionsFlag&64)
			 +MAX(soll.OptionsFlag&128)
			 +MAX(soll.OptionsFlag&256)
			 +MAX(soll.OptionsFlag&512)
			 +MAX(soll.OptionsFlag&1024)
			 +MAX(soll.OptionsFlag&2048)
			 +MAX(soll.OptionsFlag&4096)
			 +MAX(soll.OptionsFlag&8192)
			 +MAX(soll.OptionsFlag&16384)
			 +MAX(soll.OptionsFlag&32768)
			 +MAX(soll.OptionsFlag&65536)
			 +Max(soll.OptionsFlag&131072),
			 1, MAX(soll.LM), MAX(soll.UM), MAX(soll.LC), MAX(soll.UC)
	FROM	@usersInGroups AS ugs 
		INNER JOIN	[Permissions] AS soll	ON ugs.GroupID = soll.UserID
		LEFT JOIN	[Permissions] AS ist	ON ugs.UserID = ist.UserID AND soll.OwnerGroupId  = ist.OwnerGroupId AND soll.PermissionArea = ist.PermissionArea
	WHERE  ist.PermissionId IS NULL AND (ugs.UserId = @currentUserId OR @forAllUsers = 1)
	GROUP BY soll.OwnerGroupId, ugs.UserID, soll.PermissionArea

	--Berechtigungen entfernen
	DELETE FROM [Permissions] 
	WHERE IsInherited = 1 
		AND ([Permissions].UserId = @currentUserId OR @forAllUsers = 1)
		AND  PermissionId NOT IN 
				(
					SELECT  ist.PermissionId
					FROM			@usersInGroups AS ugs 
						INNER JOIN [Permissions] AS soll		ON ugs.GroupId = soll.UserID
						INNER JOIN [Permissions] as ist	ON ugs.UserID = ist.UserID AND soll.OwnerGroupId  = ist.OwnerGroupId AND soll.PermissionArea = ist.PermissionArea
				)
		AND ([Permissions].UserID NOT IN (SELECT * FROM @usersAuthorizedByGroup))
	
	UPDATE [Permissions] SET [Permissions].HasWritePermission = SollSchreiben, Permissions.OptionsFlag=SollOp
	FROM 
	(SELECT ugs.UserId, g.OwnerGroupId, PermissionArea, MAX(CAST(g.HasWritePermission AS INT)) AS SollSchreiben,
			  MAX(g.OptionsFlag&1)
			 +MAX(g.OptionsFlag&2)
			 +MAX(g.OptionsFlag&4)
			 +MAX(g.OptionsFlag&8)
			 +MAX(g.OptionsFlag&16)
			 +MAX(g.OptionsFlag&32)
			 +MAX(g.OptionsFlag&64)
			 +MAX(g.OptionsFlag&128)
			 +MAX(g.OptionsFlag&256)
			 +MAX(g.OptionsFlag&512)
			 +MAX(g.OptionsFlag&1024)
			 +MAX(g.OptionsFlag&2048)
			 +MAX(g.OptionsFlag&4096)
			 +MAX(g.OptionsFlag&8192)
			 +MAX(g.OptionsFlag&16384)
			 +MAX(g.OptionsFlag&32768)
			 +MAX(g.OptionsFlag&65536)
			 +Max(g.OptionsFlag&131072) AS SollOp  
		FROM @usersInGroups AS ugs 
			INNER JOIN [Permissions] AS G ON ugs.GroupId = G.UserId
		GROUP BY ugs.UserId, g.OwnerGroupId, PermissionArea) AS soll 
	INNER JOIN [Permissions]  ON 
			soll.UserId = [Permissions].UserId AND 
			soll.OwnerGroupId = [Permissions].OwnerGroupId AND 
			soll.PermissionArea = [Permissions].PermissionArea
	WHERE
		(SollSchreiben <> [Permissions].HasWritePermission OR SollOp <> [Permissions].OptionsFlag)
		AND [Permissions].IsInherited = 1
		AND ([Permissions].UserId = @currentUserId OR @forAllUsers = 1)
		AND ([Permissions].UserId NOT IN (SELECT * FROM @usersAuthorizedByGroup))	
	
	--Jeden User in Gruppe Kontaktdaten Jeder
	INSERT INTO [Permissions] (OwnerGroupId, UserId, HasWritePermission, LM, UM, LC, UC, PermissionArea, OptionsFlag)
	SELECT -1, Users.UserId, -1, GETDATE(), -1, GETDATE(), 0, 0, 11796
	FROM Users LEFT JOIN (SELECT * FROM [Permissions] WHERE PermissionArea = 0 AND OwnerGroupId = -1) AS UG ON Users.UserId = UG.UserId WHERE UG.PermissionId IS NULL

	--Berechtigung für Jeder/Kontakte
	UPDATE [Permissions] SET OptionsFlag = 11796 WHERE PermissionArea = 0 AND OwnerGroupId = -1 AND OptionsFlag <> 11796

	DELETE FROM [Permissions] WHERE PermissionArea <> 0 AND OwnerGroupId = -1
	
	INSERT INTO SeasonPermissions (SeasonId, UserId, IsInherited, LM, UM, LC, UC)
	SELECT DISTINCT soll.SeasonId, ugs.UserId, 1, GETDATE(), -1, GETDATE(), -1
	FROM	@usersInGroups AS ugs 
		INNER JOIN	SeasonPermissions AS soll ON ugs.Groupid = soll.UserId
		LEFT JOIN	SeasonPermissions AS ist  ON ugs.UserId = ist.UserId AND soll.SeasonId=ist.SeasonId
	WHERE  ist.SeasonPermissionId IS NULL AND (ugs.UserId = @currentUserId OR @forAllUsers = 1)

	DELETE FROM SeasonPermissions 
	WHERE IsInherited = 1 AND  SeasonPermissionId NOT IN (
		SELECT      ist.SeasonPermissionId
		FROM        @usersInGroups          AS ugs
		INNER JOIN	SeasonPermissions soll      ON ugs.GroupId = soll.UserId
		INNER JOIN	SeasonPermissions ist       ON ugs.UserId = ist.UserId  AND soll.SeasonId=ist.SeasonId
	) AND (UserId = @currentUserId OR @forAllUsers = 1)
	AND (UserId NOT IN (SELECT * FROM @usersAuthorizedByGroup))

	--UserFeatures
	--Neue UserFeatures einfügen
	INSERT INTO UserFeatures (UserId, FeatureId, IsAvailable, IsInherited, LM, UM, LC, UC)
	SELECT ugs.UserId, soll.FeatureId, MAX(CAST(soll.IsAvailable AS INT)), 1, MAX(soll.LM), MAX(soll.UM), MAX(soll.LC), MAX(soll.UC)
	FROM @usersInGroups AS ugs 
		INNER JOIN UserFeatures AS soll ON ugs.GroupID = soll.UserId
		LEFT  JOIN UserFeatures AS ist ON ugs.UserId = ist.UserId AND soll.FeatureId  = ist.FeatureId
	WHERE  ist.UserId IS NULL AND (ugs.UserId = @currentUserId OR @forAllUsers = 1)
	GROUP BY ugs.UserId, soll.FeatureId

	--Überflüssige UserFeatures entfernen
	DELETE FROM UserFeatures 
	WHERE IsInherited = 1 
		AND (UserFeatures.UserId = @currentUserId OR @forAllUsers = 1)
		AND UserFeatureId NOT IN 
				(
					SELECT  ist.UserFeatureId
					FROM			@usersInGroups AS ugs 
						INNER JOIN	UserFeatures AS soll ON ugs.GroupId = soll.UserId
						INNER JOIN	UserFeatures AS ist ON ugs.UserId = ist.UserId AND soll.FeatureId  = ist.FeatureId
				)
		AND (UserFeatures.UserId NOT IN (SELECT * FROM @usersAuthorizedByGroup))
	
	--UserFeatures aktualisieren
	UPDATE UserFeatures SET UserFeatures.IsAvailable = SollIsAvailable
	FROM 
	(
		SELECT ugs.UserId, FeatureId, MAX(CAST(uf.IsAvailable AS INT)) AS SollIsAvailable
		FROM @usersInGroups AS ugs 
			INNER JOIN UserFeatures AS uf ON ugs.GroupId = uf.UserId
		GROUP BY ugs.UserId, uf.FeatureId
	) AS soll 
	INNER JOIN UserFeatures  ON 
			soll.UserId = UserFeatures.UserId AND 
			soll.FeatureId = UserFeatures.FeatureId
	WHERE
		SollIsAvailable <> UserFeatures.IsAvailable
		AND UserFeatures.IsInherited = 1
		AND (UserFeatures.UserId = @currentUserId OR @forAllUsers = 1)
		AND (UserFeatures.UserId NOT IN (SELECT * FROM @usersAuthorizedByGroup))

	--UserFeatureDefaults
	--Neue UserFeatureDefaults einfügen
	INSERT INTO UserFeatureDefaults (UserId, IsAvailable, IsInherited, LM, UM, LC, UC)
	SELECT ugs.UserId, MAX(CAST(soll.IsAvailable AS INT)), 1, MAX(soll.LM), MAX(soll.UM), MAX(soll.LC), MAX(soll.UC)
	FROM @usersInGroups AS ugs 
		INNER JOIN UserFeatureDefaults AS soll ON ugs.GroupID = soll.UserId
		LEFT JOIN UserFeatureDefaults AS ist ON ugs.UserId = ist.UserId
	WHERE  ist.UserId IS NULL AND (ugs.UserId = @currentUserId OR @forAllUsers = 1)
	GROUP BY ugs.UserId

	--Überflüssige UserFeatureDefaults entfernen
	DELETE FROM UserFeatureDefaults 
	WHERE IsInherited = 1 
		AND (UserFeatureDefaults.UserId = @currentUserId OR @forAllUsers = 1)
		AND UserFeatureDefaultId NOT IN 
				(
					SELECT  ist.UserFeatureDefaultId
					FROM			@usersInGroups AS ugs 
						INNER JOIN	UserFeatureDefaults AS soll ON ugs.GroupId = soll.UserId
						INNER JOIN	UserFeatureDefaults AS ist ON ugs.UserID = ist.UserId
				)
		AND (UserFeatureDefaults.UserId NOT IN (SELECT * FROM @usersAuthorizedByGroup))
	
	--UserFeatureDefaults aktualisieren
	UPDATE UserFeatureDefaults SET UserFeatureDefaults.IsAvailable = SollIsAvailable
	FROM 
	(
		SELECT ugs.UserId, MAX(CAST(uf.IsAvailable AS INT)) AS SollIsAvailable
		FROM @usersInGroups AS ugs 
			INNER JOIN UserFeatureDefaults AS uf ON ugs.GroupId = uf.UserId
		GROUP BY ugs.UserId
	) AS soll 
	INNER JOIN UserFeatureDefaults  ON 
			soll.UserId = UserFeatureDefaults.UserId
	WHERE
		SollIsAvailable <> UserFeatureDefaults.IsAvailable
		AND UserFeatureDefaults.IsInherited = 1
		AND (UserFeatureDefaults.UserId = @currentUserId OR @forAllUsers = 1)
		AND (UserFeatureDefaults.UserId NOT IN (SELECT * FROM @usersAuthorizedByGroup))
END	
GO

--#endregion

--#region 2022-01-31 mit Reschreibrechten auch lesen
ALTER VIEW [dbo].[AbMeasureParticipations]
AS 
SELECT	mp.*,(
				SELECT COUNT(*) AS FollowerCount
					FROM  MeasureParticipations mps 
					WHERE mps.PersonId = mp.PersonId 
						AND mps.ParticipationDate > mp.ParticipationDate 
						AND mps.MeasureTypeId = mp.MeasureTypeId
			 ) AS FollowerCount 
FROM
	MeasureParticipations mp
WHERE 
	mp.PersonId IN (
		SELECT  pp.PersonId
		FROM PersonPermissions AS pp
			INNER JOIN [Permissions] AS p ON pp.OwnerGroupId =  p.OwnerGroupId 
			INNER JOIN Users u ON p.Userid = u.Userid 
		WHERE 
			(p.OptionsFlag&pp.OptionsFlag&65536 = 65536 OR p.OptionsFlag&pp.OptionsFlag&131072 = 131072)
			AND PermissionArea = 0
			AND Username = CURRENT_USER
		GROUP BY pp.PersonId
	)
GO
--#endregion 

--#regionauf 2022-02 "ON DELETE SET NULL" ändern
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Auditions_Cast') AND type = 'F') ALTER TABLE dbo.Auditions DROP CONSTRAINT FK_Auditions_Cast
ALTER TABLE dbo.Auditions ADD CONSTRAINT FK_Auditions_Cast FOREIGN KEY (CastId) REFERENCES [Cast] (CastId) ON DELETE SET NULL
GO
--#endregion

--Als Bugfix angelegte View entfernen
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.PlanPermissionSettings') AND type <> 'U') DROP VIEW dbo.PlanPermissionSettings
GO

IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='PlanPermissionSettings') EXEC sp_rename 'PlanLayoutPermissions', 'PlanPermissionSettings'

--#region 2022-02-17 in eingen DBs Fehler in PlanLayoutPermissions reperieren

--Unter dem Tabellennemaen 'PlanPermissionSettings' wurde in4.216 als temporär eien View angelegt
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID(N'dbo.PlanPermissionSettings') AND [type] <> 'U') DROP VIEW dbo.PlanPermissionSettings 
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('PlanPermissionSettings') AND [name] = 'PublishInWeb') 
BEGIN
	PRINT 'Rename PlanPermissionSettings.PublicateInWeb'
	EXEC sp_rename 'PlanPermissionSettings.PublicateInWeb', 'PublishInWeb', 'COLUMN';
END 
IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_PlanPermissionSettings_PublishInWeb') ALTER TABLE PlanPermissionSettings DROP CONSTRAINT DF_PlanPermissionSettings_PublishInWeb
IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_PlanLayoutPermissions_PublishInWeb') ALTER TABLE PlanPermissionSettings DROP CONSTRAINT DF_PlanLayoutPermissions_PublishInWeb
ALTER TABLE PlanPermissionSettings ALTER COLUMN PublishInWeb TINYINT NOT NULL
ALTER TABLE PlanPermissionSettings ADD CONSTRAINT DF_PlanPermissionSettings_PublishInWeb DEFAULT 0 FOR PublishInWeb
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_PlanLayoutPermissions_OwnerGroups_UserId') AND type = 'F') ALTER TABLE dbo.PlanPermissionSettings DROP CONSTRAINT FK_PlanLayoutPermissions_OwnerGroups_UserId

DELETE FROM PlanPermissionSettings WHERE NOT EXISTS(SELECT * FROM [Users] AS U  WHERE U.UserID = PlanPermissionSettings.UserId)

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_PlanPermissionSettings_Users') AND type = 'F') ALTER TABLE dbo.PlanPermissionSettings ADD CONSTRAINT FK_PlanPermissionSettings_Users FOREIGN KEY (UserId) REFERENCES [Users] (UserId) 

GRANT SELECT ON PlanPermissionSettings TO Public
GO
--#endregion

--#endregion--------- (4.216.x Release Übertrag) --------------

--#region ----------- 4.221.x ---------------------------------
--#region  Alte TAbellen und Felder entfernen
IF EXISTS (SELECT * FROM  sysobjects o WHERE o.id = OBJECT_ID(N'dbo.PlanZR')) DROP TABLE dbo.PlanZR
IF EXISTS (SELECT * FROM  sysobjects o WHERE o.id = OBJECT_ID(N'dbo.FormulaConfigurations_Departments')) DROP TABLE dbo.FormulaConfigurations_Departments
IF EXISTS (SELECT * FROM  sysobjects o WHERE o.id = OBJECT_ID(N'dbo.X_BesetzungStdSoliTabelle')) DROP TABLE dbo.X_BesetzungStdSoliTabelle
IF EXISTS (SELECT * FROM  sysobjects o WHERE o.id = OBJECT_ID(N'dbo.X_CastRepertoire')) DROP TABLE dbo.X_CastRepertoire
IF EXISTS (SELECT * FROM  sysobjects o WHERE o.id = OBJECT_ID(N'dbo.X_ProductionChilds')) DROP TABLE dbo.X_ProductionChilds
IF EXISTS (SELECT * FROM  sysobjects o WHERE o.id = OBJECT_ID(N'dbo.X_ProductionsWorkVersions')) DROP TABLE dbo.X_ProductionsWorkVersions
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID(N'dbo.AbPlanSort') AND type <> 'U') DROP VIEW dbo.AbPlanSort
IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID(N'dbo.AbPlanColumn') AND type <> 'U') DROP VIEW dbo.AbPlanColumn
IF EXISTS (SELECT * FROM  sysindexes o WHERE o.id = OBJECT_ID(N'Persons') AND o.Name = 'IX_PersKatNr') DROP INDEX IX_PersKatNr ON Persons
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.RenameColumn')) DROP PROCEDURE dbo.RenameColumn
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.RenameColumnInTable')) DROP PROCEDURE dbo.RenameColumnInTable
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.RenameTable')) DROP PROCEDURE dbo.RenameTable
IF EXISts (SELECT * FROM  sysindexes o WHERE o.id = OBJECT_ID(N'WorkAreaGroupMembers') AND o.name = (N'IX_WorkAreaGroupMembers_WorkArea_RoleType')) DROP INDEX [IX_WorkAreaGroupMembers_WorkArea_RoleType] ON [dbo].[WorkAreaGroupMembers]
GO
Exec DropColumn 'Departments', 'VisibleDaysNumber'
Exec DropColumn 'Departments', 'TimePartitionMinutes'
Exec DropColumn 'Departments', 'VisibleDaySums'
Exec DropColumn 'Departments', 'VisibleColumns'
Exec DropColumn 'Departments', 'ColWidth'
Exec DropColumn 'Departments', 'VisibleAreas'
Exec DropColumn 'Departments', 'ActivitiesTimePartitions'
Exec DropColumn 'ResourceObjects', 'ResourceData'
Exec DropColumn 'ContractDepartments', 'DefaultWorkAreaGroupId'
Exec DropColumn 'WorkAreaGroupMembers', 'WorkAreaGroupId'
Exec DropColumn 'TechnicalJobs', 'WorkAreaGroupId'
Exec DropColumn 'RoleTypes', 'RoleTypeCategory'
Exec DropColumn 'RoleTypes', 'PreparationDuration'
Exec DropColumn 'RoleTypes', 'x_PreparationDuration'
Exec DropColumn 'ContractSalaryRules', 'IsDefaultValue'
Exec DropColumn 'Persons', 'X_PersonCategoryId'
Exec DropColumn 'BonusDutyRosterData', 'x_DutyRosterId'
--#endregion
GO
--#region 2022-01-11

--Maßnameh ohne Maße
DELETE FROM BodyMeasurements WHERE NOT EXISTS (SELECT * FROM BodyMeasurementData WHERE BodyMeasurementData.BodyMeasurementId = BodyMeasurements.BodyMeasurementId)

GO
--#endregion
--#region 20220105 AP #17566 - Warnungen im Startfenster anzeigen

IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='CastConflictsExclude')
BEGIN
	CREATE TABLE dbo.CastConflictsExclude(
		CastConflictExcludeId INT IDENTITY(1, 1) NOT NULL,
		ConflictType TINYINT NOT NULL,
		ActivityId1	INT NOT NULL,		--Alle
		ActivityId2	INT NULL,			--Für Cast Überschneidung
		PersonId	INT NULL,			--Für Cast Überschneidung
		AbsencePersonStatusId INT NULL, --Für Abwesenheitsüberschenidung
		RoleId INT NULL,				--Nicht benötigte zwingend benötigte unbesetzte Rollen
		Note VARCHAR(1024),
		[LM] SMALLDATETIME NOT NULL CONSTRAINT DF_CastConflictExclude_LM DEFAULT GETDATE(),
		[UM] INT NULL,
		[LC] SMALLDATETIME NOT NULL CONSTRAINT DF_CastConflictsExclude_LC DEFAULT GETDATE(),
		[UC] INT NULL,
		CONSTRAINT PK_CastConflictsExclude PRIMARY KEY  NONCLUSTERED (CastConflictExcludeId) ON [PRIMARY]
    ) ON [PRIMARY]
	GRANT SELECT ON CastConflictsExclude TO public
	GRANT INSERT, UPDATE, DELETE ON CastConflictsExclude TO DispoAdmin

	CREATE CLUSTERED INDEX [IX_CastConflictExclude_ActivityId1] ON CastConflictsExclude (
	ActivityId1 ASC
	) 
END
GO

IF EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].[GetNoCastWarnings]'))DROP PROCEDURE [dbo].[GetNoCastWarnings]
GO
CREATE PROCEDURE [dbo].[GetNoCastWarnings]
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLQuery AS NVARCHAR(MAX)

	DECLARE @start DATE
	DECLARE @end DATE
	SET @start = GETDATE()
	SET @end = '30000101'

	DECLARE @permissionPeriodStart DATE
	DECLARE @permissionPeriodEnd DATE

	DECLARE @userId INT
	SELECT @userId = UserId FROM Users WHERE UserName  = CURRENT_USER

	-- *** Spielzeitberechtigungen prüfen ***
	SELECT @permissionPeriodStart = MIN(s.StartDate), @permissionPeriodEnd = MAX(s.EndDate)
		FROM Seasons AS s
			INNER JOIN SeasonPermissions AS us	ON s.SeasonId = us.SeasonId
		WHERE us.UserId = @userId 

	if (@permissionPeriodStart IS NULL)
		BEGIN 
			SET @start = '30000101'
			SET @end = '30000101'
		END
	ELSE
		BEGIN
			IF (@start < @permissionPeriodStart)	BEGIN SET @start = @permissionPeriodStart END
			IF (@end > @permissionPeriodEnd)		BEGIN SET @end = @permissionPeriodEnd END
		END

	SELECT a.ActivityId, r.RoleId, r.[Name] AS RoleName
	FROM [Activities] AS a 
		INNER JOIN Productions AS p ON a.ProductionId = p.ProductionId 
		INNER JOIN Roles AS r ON a.ProductionId = r.ProductionId 
		INNER JOIN OwnerGroups AS g ON r.OwnerGroupId = g.OwnerGroupId 
		INNER JOIN CastRules AS cr ON a.ActivityTypeId = cr.ActivityTypeId AND r.RoleTypeId = cr.RoleTypeId
		LEFT JOIN [Cast] AS c ON a.ActivityId = c.ActivityId AND c.RoleId = r.RoleId
		INNER JOIN (
					SELECT OwnerGroupId, OptionsFlag 
					FROM [Permissions] AS pm
					WHERE UserId = @userId AND PermissionArea = 2
					) AS UGActivity ON UGActivity.OwnerGroupId = P.OwnerGroupId
	
	WHERE Date BETWEEN  @start AND @end AND 
		g.Modul = 1 AND
		cr.IsMandatory = 1 AND 
		NOT cr.MarkVacantCastDaysBefore IS NULL AND
		DATEADD(DAY, cr.MarkVacantCastDaysBefore * -1, Date) <= GETDATE() AND
		c.CastId IS NULL AND 
		VariantId = 0 AND
		(a.IsSecret = 0 OR UGActivity.OptionsFlag&1 = 1)
	ORDER BY a.Date, a.StartTime
END

GO
GRANT EXECUTE ON [GetNoCastWarnings] TO Public
GO
--#endregion

--#region
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ProductionCosts') AND [name] = 'EndDate')
BEGIN
PRINT('ProductionCosts EndDate..')
	ALTER TABLE ProductionCosts ADD EndDate DATE NULL
	--Enddatum auf Spielzeitende setzen
	EXEC ('UPDATE ProductionCosts 
				SET EndDate = Seasons.endDate
		  FROM  ProductionCosts 
			INNER JOIN ProductionCostTypes ON ProductionCosts.ProductionCostTypeId = ProductionCostTypes.ProductionCostTypeId
			INNER JOIN Seasons ON  ProductionCosts.CalculationDate >= Seasons.StartDate AND ProductionCosts.CalculationDate <= Seasons.EndDate
		WHERE IsProPerformance = 1')

		--Beim letzten satz das EndDatum entfernen
		EXEC ('UPDATE ProductionCosts SET EndDate = null FROM ProductionCosts INNER JOIN 
		(SELECT  ProductionId , ProductionCosts.ProductionCostTypeId, Max(CalculationDate) as d  FROM  ProductionCosts 
			INNER JOIN ProductionCostTypes ON ProductionCosts.ProductionCostTypeId = ProductionCostTypes.ProductionCostTypeId
		WHERE IsProPerformance = 1
		GROUP BY ProductionId , ProductionCosts.ProductionCostTypeId) AS SUB ON ProductionCosts.ProductionId = SUB.ProductionId AND ProductionCosts.ProductionCostTypeId = SUB.ProductionCostTypeId AND ProductionCosts.CalculationDate = SUB.d')
END
GO
--#endregion

--#region
ALTER PROCEDURE dbo.TruncateUpdateLog
	@fromDate DATE
AS
BEGIN	
	BEGIN TRY
		
		DECLARE @totalTreuncatedRows INT;
		DECLARE @totalRows INT;
		DECLARE @message NVARCHAR(200);

		SELECT @totalTreuncatedRows = COUNT(*) FROM UpdateLog WHERE LC <= @fromDate
		IF (@totalTreuncatedRows > 0) 
		BEGIN
			
			SELECT @totalRows = COUNT(*) FROM UpdateLog
						
			SET @message = 'TRUNCATE UPDATE LOG from: ' + CAST(@fromDate AS VARCHAR(10)) + ' Total: ' + CAST(@totalRows AS VARCHAR(100)) + ' Delete: ' + CAST(@totalTreuncatedRows AS VARCHAR(100))
			RAISERROR(@message, 0, 1) WITH NOWAIT

			DECLARE @Deleted_Rows INT;
			SET @Deleted_Rows = 1;

			DECLARE @All_Rows INT;
			SELECT @All_Rows = COUNT(*) FROM Updatelog WHERE lc < @fromDate
  
			DECLARE @All_Deleted_Rows INT;
			SET @All_Deleted_Rows = 0;
			BEGIN TRANSACTION
			WHILE (@Deleted_Rows > 0)
				BEGIN
  
				DELETE TOP (100000)  Updatelog WHERE lc < @fromDate

				SET @Deleted_Rows = @@ROWCOUNT;
				SET @All_Deleted_Rows = @All_Deleted_Rows + @Deleted_Rows;

				SET @message = 'DELETED: ' + CAST(@All_Deleted_Rows  AS VARCHAR)  + '/ FROM: ' +   CAST(@All_Rows  AS VARCHAR) + '/ REMAINING: ' +   CAST(@All_Rows-@All_Deleted_Rows  AS VARCHAR)
				RAISERROR(@message, 0, 1) WITH NOWAIT
			END
		
		COMMIT TRANSACTION

		SET @message =  CAST(@totalTreuncatedRows AS VARCHAR(100)) + ' rows deleted'
		RAISERROR(@message, 0, 1) WITH NOWAIT
		END
		END TRY

	BEGIN CATCH 
		ROLLBACK TRANSACTION
		RAISERROR('Error', 0, 1) WITH NOWAIT
	END CATCH
END
GO
--#endregion

--#region
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('PersonAdditionalDataTypes') AND [name] = 'GroupName') ALTER TABLE PersonAdditionalDataTypes ADD GroupName VARCHAR(30)
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ContractDepartments') AND [name] = 'IsOnlyVisibeInDepartment') ALTER TABLE ContractDepartments ADD IsOnlyVisibeInDepartment BIT CONSTRAINT DF_ContractDepartments_IsOnlyVisibeInDepartment DEFAULT 0
GO
--#endregion
--#region
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Persons') AND [name] = 'KeyWords') 
BEGIN
	ALTER TABLE Persons ALTER COLUMN CompanyName NVARCHAR(150)
	ALTER TABLE Persons ADD KeyWords VARCHAR(64) NULL
	EXEC ('UPDATE Persons SET KeyWords = KeyName WHERE NOT KeyName IS NULL AND Len(KeyName) > 20')
END

--Suchname NOT Null
UPDATE Persons SET KeyName = LEFT(ISNULL(LastName, ISNULL(FirstName, CAST(personid AS VARCHAR(10)))), 20) FROM Persons WHERE KeyName IS NULL OR KeyName = ''

IF EXISTS (SELECT * FROM  sysindexes o WHERE o.id = OBJECT_ID(N'Persons') AND o.Name = 'IX_Suchname') DROP INDEX IX_Suchname ON Persons

UPDATE Persons SET KeyName = LEFT(KeyName,20) WHERE LEN(KeyName) > 20
ALTER TABLE Persons ALTER COLUMN KeyName VARCHAR(20) NOT NULL

GO
--#endregion

--#region 17774, Two Letter Country Codes, ISO 3166-1

PRINT('Check PersonAddress - CountryId...')	
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('PersonAddress') AND [name] = 'CountryId')
BEGIN
	ALTER TABLE PersonAddress ADD CountryId INT NULL

	DECLARE @SQLQuery AS NVARCHAR(MAX)
	SET @SQLQuery =
	'UPDATE PersonAddress SET PersonAddress.CountryId = Countries.CountryId
	 FROM Countries
	 WHERE Countries.Code = PersonAddress.CountryCode'
	EXECUTE sp_Executesql @SQLQuery
END
GO

IF NOT EXISTS ( SELECT * FROM SYS.FOREIGN_KEYS WHERE [name] ='FK_PersonAdress_Countries' )
BEGIN
PRINT('Check PersonAddress CountryId foreign key...')
	ALTER TABLE PersonAddress ADD CONSTRAINT FK_PersonAdress_Countries FOREIGN KEY (CountryId)
    REFERENCES Countries (CountryId)
END
GO

IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Countries') AND [name] = 'TLCC')
BEGIN
	PRINT('Check column name TLCC...')
	EXECUTE sp_rename 'Countries.TLCC', 'TwoLetterCode', 'COLUMN';
END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Countries') AND [name] = 'TwoLetterCode')
BEGIN
	PRINT('Check Countries columns...')
	ALTER TABLE Countries ALTER COLUMN [Name] VARCHAR(60) NOT NULL
	ALTER TABLE Countries ADD [Name_en] VARCHAR(60) NULL
	ALTER TABLE Countries ADD TwoLetterCode VARCHAR(2) NULL
END
GO

PRINT('Update settings...')
UPDATE settings 
SET DataValue = Countries.CountryId 
FROM Countries INNER JOIN settings ON Countries.Code = settings.DataValue 
WHERE settings.Section = 'Adresse' AND settings.KeyName = 'Country'
GO

IF (COL_LENGTH('PersonAddress', 'PostCode') < 16)
BEGIN
	PRINT('Updating PersonAddress...')
    ALTER TABLE PersonAddress ALTER COLUMN PostCode VARCHAR(16)
	UPDATE PersonAddress SET PostCode = CountryCode + ISNULL('-' + PostCode, '')
	FROM Countries 
		RIGHT JOIN PersonAddress ON	Countries.Code = PersonAddress.CountryCode 
	WHERE Countries.CountryId IS NULL AND PersonAddress.CountryCode <> '' AND NOT PersonAddress.CountryCode IS NULL
END
GO
PRINT('Updating view ABPersonAddress...')
GO
ALTER VIEW [dbo].[ABPersonAddress]
AS 
	SELECT
		pa.AddressId, 
		pa.PersonId, 
		CASE WHEN pa.IsSecret = 1 AND SecretPermission = 0 THEN '**********' ELSE pa.Street END AS Street,
		CASE WHEN pa.IsSecret = 1 AND SecretPermission = 0 THEN '*****' ELSE pa.PostCode END AS PostCode,
		CASE WHEN pa.IsSecret = 1 AND SecretPermission = 0 THEN '**********' ELSE pa.City END AS City,
		CASE WHEN pa.IsSecret = 1 AND SecretPermission = 0 THEN NULL ELSE pa.CountryId END AS CountryId,
		CASE WHEN pa.IsSecret = 1 AND SecretPermission = 0 THEN NULL ELSE pa.co END AS Co,
		CASE WHEN pa.IsSecret = 1 AND SecretPermission = 0 THEN NULL ELSE pa.Note END AS Note,
		pa.Sort,
		pa.[Name],
		pa.IsSecret,
		pa.IsTaxDomicile,
		pa.IsHomeAddress,
		pa.LM, pa.UM, pa.LC, pa.UC
		FROM PersonAddress AS pa
		INNER JOIN (SELECT  pp.PersonId, MAX(CASE WHEN ug.OptionsFlag&pp.OptionsFlag&1 <> 1 THEN 0 ELSE 1 END) AS SecretPermission 
					FROM PersonPermissions AS PP
						INNER JOIN [Permissions] AS UG ON pp.OwnerGroupId =  UG.OwnerGroupId 
						INNER JOIN Users ON Ug.Userid = Users.Userid 
					WHERE  PermissionArea = 0 
						AND (ug.OptionsFlag&pp.OptionsFlag&2048=2048 OR ug.OptionsFlag&pp.OptionsFlag&4096=4096)
						AND  Username = CURRENT_USER 
					GROUP BY pp.PersonId
					) AS sub ON pa.PersonId = sub.PersonId
GO
GRANT SELECT ON [ABPersonAddress] TO Public

--#endregion 17774
GO
ALTER VIEW [dbo].[AbPlanConfiguration]
AS 
SELECT  p.* FROM PlanLayouts AS p
WHERE (p.PlanLayoutType <> 150 AND p.OwnerGroupId IN (
		SELECT [Permissions].OwnerGroupId 
		FROM [Permissions] INNER JOIN Users ON [Permissions].Userid = Users.Userid 
		WHERE Users.Username = CURRENT_USER AND PermissionArea = 11
	))
	OR (p.PlanLayoutType = 150 AND p.OwnerGroupId IN (
		SELECT [Permissions].OwnerGroupId 
		FROM [Permissions] INNER JOIN Users ON [Permissions].Userid = Users.Userid 
		WHERE Users.Username = CURRENT_USER AND PermissionArea = 7
	))
GO

ALTER VIEW [dbo].[AbPlanPermissionSettings]
AS
	SELECT pps.* FROM PlanPermissionSettings AS PPS
		INNER JOIN PlanLayouts AS P ON pps.PlanLayoutId=p.PlanLayoutId		
		INNER JOIN OwnerGroups AS gr ON p.OwnerGroupId = gr.OwnerGroupId
		INNER JOIN [Permissions] AS ug ON gr.OwnerGroupId = ug.OwnerGroupId   
		INNER JOIN Users ON Ug.Userid  = Users.Userid 
	WHERE ug.PermissionArea = 11 AND ug.OptionsFlag & 1 = 1 AND Users.Username = CURRENT_USER
GO
ALTER VIEW [dbo].[AbPlanPermissionSettings_Update]
AS
	SELECT pps.* FROM PlanPermissionSettings AS PPS WHERE PPS.PlanLayoutId IN (
		SELECT P.PlanLayoutId FROM PlanLayouts AS P		
		INNER JOIN OwnerGroups AS gr ON p.OwnerGroupId = gr.OwnerGroupId
		INNER JOIN [Permissions] AS ug ON gr.OwnerGroupId = ug.OwnerGroupId   
		INNER JOIN Users ON Ug.Userid  = Users.Userid 
		WHERE ug.PermissionArea = 11 AND ug.OptionsFlag & 2 = 2 AND Users.Username = CURRENT_USER)
	WITH CHECK OPTION
GO

--#endregion

--#region 2022-02-18
IF EXISTS (SELECT * FROM  sysindexes o WHERE o.id = OBJECT_ID(N'CountDowns') AND o.Name = '[IX_CountDowns_ProductionId]')
	CREATE  INDEX [IX_CountDowns_ProductionId] ON CountDowns (ProductionId)

DELETE Settings WHERE Settings.KeyName = 'Log' AND Settings.Section = 'UpdateLog'
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('CashCommands') AND [name] = 'Email') 
BEGIN
	ALTER TABLE CashCommands ADD Email VARCHAR(128) NULL
END
--#endregion
--#endregion ------- (4.221.x) --------------------------------

--#region ------------4.222.x ---------------------------------
--#region DH - NighSleepSetting per Timeaccount and tarif
GO
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('TimeAccountTarifSettings') AND [name] = 'XMLSettings' AND system_type_id = 35)
BEGIN
	DECLARE @night REAL;
	SELECT @night = NARUHEGK FROM PersoParameter GROUP BY NARUHEGK ORDER BY COUNT(NARUHEGK) ASC
	SELECT @night 

	DECLARE @comand nvarchar(max);
	SET @comand = N'UPDATE TimeAccountTarifSettings '+ 
		N'SET XMLSettings=REPLACE(XMLSettings,''</SleepTimeSettings>'', ''<SleepTimeDuration>''+CONVERT(VARCHAR(5),P.NARUHEGK) +''</SleepTimeDuration></SleepTimeSettings>'' '+
		N') FROM TimeAccountTarifSettings T INNER JOIN PersoParameter P ON T.TarifId = P.PersoParameterID WHERE TimeAccountId IN (SELECT TimeAccountId FROM TimeAccounts WHERE Formel=1220) ;'
	EXEC (@comand);

	INSERT INTO TimeAccountTarifSettings(TimeAccountId,TarifId, LM, LC, UC, UM, XMLSettings)
		SELECT T.TimeAccountId, P.PersoParameterID, GETDATE(), GETDATE(), -1 ,-1 ,REPLACE(CAST(T.Settings AS VARCHAR(MAX)),'</SLEEPTIMESettings>', '<SleepTimeDuration>' + CONVERT(VARCHAR(5),P.NARUHEGK) + '</SleepTimeDuration></SLEEPTIMESettings>')
		FROM TimeAccounts T CROSS JOIN PersoParameter P 
		WHERE T.Formel = 1220 AND P.NARUHEGK <> @night 
			AND NOT EXISTS(SELECT T2.TimeAccountTarifSettingId FROM TimeAccountTarifSettings T2 WHERE T2.TimeAccountId = T.TimeAccountId AND T2.TarifId = P.PersoParameterID)
	
	UPDATE TimeAccounts
	SET Settings = CAST(REPLACE(CAST(Settings AS VARCHAR(MAX)),'</SleepTimeSettings>', '<SleepTimeDuration>'+ CONVERT(VARCHAR(3), @night) + '</SleepTimeDuration></SleepTimeSettings>') AS XML)
	FROM TimeAccounts WHERE Formel = 1220 AND NOT Settings IS NULL;

	UPDATE TimeAccounts
	SET Settings = CAST('<SleepTimeSettings><SleepTimeDuration>'+ CONVERT(VARCHAR(3), @night) + '</SleepTimeDuration></SleepTimeSettings>' AS XML)
	FROM TimeAccounts WHERE Formel = 1220 AND Settings IS NULL;
END
GO
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('TimeAccountTarifSettings') AND [name] = 'XMLSettings' AND system_type_id = 35)
BEGIN
	EXEC ConvertColumnToXml @tablename = 'TimeAccountTarifSettings', @columnname = 'XMLSettings'
END
GO
--#endregion DH - NighSleepSetting per Timeaccount and tarif

--#region 17883, Verwaltung Planungsstatus

PRINT('Check TravelStepStatus...')	
IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='TravelStepStatus')
BEGIN
	CREATE TABLE dbo.TravelStepStatus(
		TravelStepStatusId INT IDENTITY(1, 1) NOT NULL,
		[Name] VARCHAR(100) NOT NULL,		
		[Status] SMALLINT NOT NULL,
		Settings VARCHAR(MAX) NULL,
		[LM] SMALLDATETIME NOT NULL DEFAULT GETDATE(),
		[UM] INT NULL,
		[LC] SMALLDATETIME NOT NULL DEFAULT GETDATE(),
		[UC] INT NULL,
		CONSTRAINT PK_TravelStepStatusId PRIMARY KEY  NONCLUSTERED (TravelStepStatusId) ON [PRIMARY]
    ) ON [PRIMARY]

	GRANT SELECT ON TravelStepStatus TO public
	GRANT INSERT, UPDATE, DELETE ON TravelStepStatus TO DispoAdmin
	
	INSERT INTO TravelStepStatus ([Name], [Status], Settings, LM, UM, LC, UC)
	VALUES
	('Storniert', -10, NULL, GETDATE(), -1, GETDATE(), -1),
	('Offen', 0, NULL, GETDATE(), -1, GETDATE(), -1),
	('Geplant', 5, NULL, GETDATE(), -1, GETDATE(), -1),
	('Gebucht', 10, NULL, GETDATE(), -1, GETDATE(), -1),
	('Verrechnet', 20, NULL, GETDATE(), -1, GETDATE(), -1),
	('Fremdgeplant', 100, NULL, GETDATE(), -1, GETDATE(), -1)

	EXEC sp_rename 'TravelSteps.BookingStatus', 'TravelStepStatusId', 'COLUMN';

	--Status gegen Id der TravelStepStatus austauschen
	EXEC('UPDATE TravelSteps SET TravelSteps.TravelStepStatusId = TravelStepStatus.TravelStepStatusId 
	FROM TravelStepStatus 
	WHERE TravelSteps.TravelStepStatusId = TravelStepStatus.Status');
END
GO

PRINT('Check foreign key TravelSteps...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_TravelSteps_TravelStepStatus') AND type = 'F') 
BEGIN
	ALTER TABLE dbo.TravelSteps ADD CONSTRAINT FK_TravelSteps_TravelStepStatus FOREIGN KEY (TravelStepStatusId) REFERENCES [TravelStepStatus] (TravelStepStatusId) 
END
GO

--#endregion 17883



--#region AP - WI #17891 - Wohnungsverwaltung Felder für Ein- Auscheck Standardzeiten

PRINT('Check TravelStepTypes - DefaultStartTime...')	
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('TravelStepTypes') AND [name] = 'DefaultStartTime')
BEGIN
	ALTER TABLE TravelStepTypes ADD DefaultStartTime Time NULL
	ALTER TABLE TravelStepTypes ADD DefaultEndTime Time NULL
END
GO

PRINT('Update TravelStepTypes - DefaultTimes...')	
UPDATE TravelStepTypes SET DefaultStartTime = '14:00:00', DefaultEndTime = '10:00:00'
WHERE TravelStep = 100
GO

PRINT('Check TravelSteps - CheckIn/Out...')	
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('TravelSteps') AND [name] = 'isCheckedin')
BEGIN
	ALTER TABLE TravelSteps ADD IsCheckedin BIT NOT NULL  CONSTRAINT DF_TravelSteps_IsCheckin DEFAULT 0
	ALTER TABLE TravelSteps ADD IsCheckedout BIT NOT NULL CONSTRAINT DF_TravelSteps_IsCheckout DEFAULT 0
END
GO

--#endregion AP - WI #17891 - Wohnungsverwaltung Felder für Ein- Auscheck Standardzeiten

--#region AP WI #17879 - Wohnungsverwaltung, es können Rechnungen erstellt werden

------------------------------------------------------------
--Apartment Ivoice
------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='Invoices')
BEGIN
	CREATE TABLE dbo.Invoices (
	InvoiceId		INT IDENTITY(1, 1) NOT NULL, 
	PersonId		INT NOT NULL,		--Rechnungsempfänger
	Invoicenumber	INT NULL,			--Rechnungsnummer
	InvoiceDate		DATE NOT NULL,		--Rechnungsdatum
	Note			VARCHAR(255) NULL,	--Bezeichnung
	LM				SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	UM				INT NULL,
	LC				SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	UC				INT NULL,
	OwnerGroupId	INT NOT NULL,
	IsCreated		BIT NOT NULL CONSTRAINT DF_Invoices_IsCreated DEFAULT 0 	--Kennzeichen, ob Rechnung erstellt wurde
	CONSTRAINT PK_InvoiceId PRIMARY KEY  NONCLUSTERED (InvoiceId) ON [PRIMARY]
	) ON [PRIMARY]

	CREATE CLUSTERED INDEX IX_Invoices_PersonId ON Invoices (PersonId ASC)
	CREATE UNIQUE INDEX IX_Invoices_Invoicenumber ON Invoices (Invoicenumber)
	CREATE INDEX IX_Invoices_InvoiceDate ON Invoices (InvoiceDate ASC)

	CREATE TABLE dbo.InvoicePositions (
	InvoicePositionId	INT IDENTITY(1, 1) NOT NULL,
	InvoiceId			INT NOT NULL,
	Performancedate		DATE NULL,							--Datum d. erbrachten Leistung
	Position			SMALLINT NOT NULL DEFAULT 0,		--Rechnungsposition
	Quantity			DECIMAL(9, 2) NOT NULL DEFAULT 0,		--Anzahl
	[Description]		VARCHAR(1000) NULL,					--Bezeichnung
	NetPrice			DECIMAL(9, 2) NULL,					--Einzelpreis, Netto
	Tax					DECIMAL(4, 2) NULL,					--Aktuellen Satz aus den Einstellungen kopieren
	LM					SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	UM					INT NULL,
	LC					SMALLDATETIME NOT NULL DEFAULT GETDATE(),
	UC					INT NULL
	CONSTRAINT PK_InvoicePositionId PRIMARY KEY  NONCLUSTERED (InvoicePositionId) ON [PRIMARY]
	) ON [PRIMARY]
END

GO

IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetMaxInvoiceNumber'))
BEGIN
	DROP PROCEDURE dbo.GetMaxInvoiceNumber 
END
GO

CREATE PROCEDURE GetMaxInvoiceNumber
AS
BEGIN
    SELECT ISNULL(MAX(Invoicenumber),1) FROM Invoices
END
GO
GRANT EXECUTE ON GetMaxInvoiceNumber TO Public
GO

PRINT('Check foreign key TravelSteps-Invoices...')
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('TravelSteps') AND [name] = 'InvoiceId') 
BEGIN
	ALTER TABLE travelSteps ADD InvoiceId INT NULL
	ALTER TABLE travelSteps ADD CONSTRAINT FK_travelSteps_Invoices FOREIGN KEY (InvoiceId) REFERENCES Invoices (InvoiceId) ON DELETE SET NULL 
END
GO

PRINT('Check foreign key InvoicePositions...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_InvoicePositions_Invoices') AND type = 'F') 
BEGIN
	ALTER TABLE InvoicePositions ADD CONSTRAINT FK_InvoicePositions_Invoices FOREIGN KEY (InvoiceId) REFERENCES Invoices (InvoiceId) 
END
GO

PRINT('Check foreign key Invoices-Persons...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Invoices_Persons') AND type = 'F')
BEGIN
	ALTER TABLE Invoices  WITH CHECK ADD  CONSTRAINT FK_Invoices_Persons FOREIGN KEY(PersonId)
	REFERENCES Persons (PersonId)
	ALTER TABLE Invoices CHECK CONSTRAINT FK_Invoices_Persons
END
GO

PRINT('Check foreign key Invoices-OwnerGroups...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Invoices_OwnerGroups') AND type = 'F')
BEGIN
	ALTER TABLE Invoices  WITH CHECK ADD  CONSTRAINT FK_Invoices_OwnerGroups FOREIGN KEY(OwnerGroupId)
	REFERENCES OwnerGroups (OwnerGroupId)
	ALTER TABLE Invoices CHECK CONSTRAINT FK_Invoices_OwnerGroups
END
GO

--Invoice Views erstellen
PRINT('Creating Invoice views...') 
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbInvoices') )
	DROP VIEW AbInvoices
GO
	CREATE VIEW dbo.AbInvoices
	AS 
	SELECT 	I.*
	FROM	Invoices AS I
				INNER JOIN	(
				SELECT OwnerGroupId, HasWritePermission FROM Permissions 
				INNER JOIN		Users		ON Permissions.Userid = Users.Userid 
				WHERE Username = CURRENT_USER AND PermissionArea = 23
			) AS UG ON I.OwnerGroupId = UG.OwnerGroupId 
GO
GRANT SELECT ON AbInvoices TO public
REVOKE INSERT, UPDATE, DELETE ON AbInvoices TO DispoAdmin
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbInvoices_Update') )
	DROP VIEW AbInvoices_Update
GO
CREATE VIEW dbo.AbInvoices_Update
AS 
SELECT * FROM Invoices WHERE InvoiceId IN (
	SELECT IU.InvoiceId FROM Invoices AS IU
	INNER  JOIN	(SELECT DISTINCT OwnerGroupId, HasWritePermission 
						FROM			Permissions 
						INNER JOIN		Users		ON Permissions.Userid = Users.Userid 
						WHERE Username = CURRENT_USER  AND PermissionArea = 23) AS UG
				ON IU.OwnerGroupId = UG.OwnerGroupId
				WHERE UG.HasWritePermission = 1)
WITH CHECK OPTION
GO
GRANT SELECT ON AbInvoices_Update TO public
REVOKE INSERT, UPDATE, DELETE ON AbInvoices_Update TO DispoAdmin
GRANT INSERT, UPDATE, DELETE ON AbInvoices_Update TO Public
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbInvoicePositions') )
	DROP VIEW AbInvoicePositions
GO
CREATE VIEW dbo.AbInvoicePositions
AS 
SELECT IP.* FROM InvoicePositions AS IP INNER JOIN Invoices AS I on I.InvoiceId = IP.InvoiceId INNER JOIN (
		SELECT OwnerGroupId, HasWritePermission FROM Permissions 
		INNER JOIN		Users 		ON Permissions.Userid = Users.Userid 
		WHERE Username = CURRENT_USER AND PermissionArea = 23		
	) AS UG ON I.OwnerGroupId = UG.OwnerGroupId 
GO
GRANT SELECT ON AbInvoicePositions TO public
REVOKE INSERT, UPDATE, DELETE ON AbInvoicePositions TO DispoAdmin
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbInvoicePositions_Update') )
	DROP VIEW AbInvoicePositions_Update
GO
CREATE VIEW dbo.AbInvoicePositions_Update
AS 
SELECT IP.* FROM InvoicePositions AS IP 
	WHERE IP.InvoiceId IN (SELECT InvoiceId
							FROM  Invoices AS I
								INNER JOIN  Permissions AS ug ON I.OwnerGroupId = ug.OwnerGroupId
								INNER JOIN		Users 		 ON ug.Userid = Users.Userid 
							WHERE Username = CURRENT_USER AND PermissionArea = 23 AND ug.HasWritePermission = 1) 
WITH CHECK OPTION
GO
GRANT SELECT ON AbInvoicePositions_Update TO public
REVOKE INSERT, UPDATE, DELETE ON AbInvoicePositions_Update TO DispoAdmin
GRANT INSERT, UPDATE, DELETE ON AbInvoicePositions_Update TO Public
GO

--endregion AP WI #17879 - Wohnungsverwaltung, es können Rechnungen erstellt werden


--#region DH - PaymentWageTypeSettings 28.03.2022 
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('RapportColumns') AND [name] = 'PaymentWageTypesSetting') 
BEGIN
Print 'Adding PaymentWageTimeSettings'
	ALTER TABLE RapportColumns ADD PaymentWageTypesSetting XML NULL ;
END 
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('RapportColumns') AND [name] = 'xxPaymentWageTypeId') 
BEGIN
	Print 'Updating PaymentWageTimeSettings'
	declare @command nvarchar(1000);
	set @command=N' UPDATE RapportColumns SET PaymentWageTypesSetting = CONVERT(xml,N''<PaymentWagetypesSetting xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'+
		N'<Settings>    <WageTypePaymentSetting>      <WageTypeID>'' +convert(nvarchar, PaymentWageTypeId)+ N''</WageTypeID>      <PayoutValuesFrom>0</PayoutValuesFrom>'+
		N'<PayoutValuesTo xsi:nil="true" />   </WageTypePaymentSetting>  </Settings></PaymentWagetypesSetting>'')'+ 
	  N' WHERE PAYMENTWAGETYPEID IS NOT NULL;';
	  exec (@command);
	Print 'renaming PaymentWageTypeId'
	EXEC sp_rename 'dbo.Rapportcolumns.PaymentWageTypeId', 'xxPaymentWageTypeId', 'COLUMN';
END 
GO	
--#endregion DH - PaymentWageTypeSettings 28.03.2022 
--#region DH - Variable Workingdays
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ContractPartTimePeriods') AND [name] = 'VariableWorkingdays')	
BEGIN
ALTER TABLE ContractPartTimePeriods ADD VariableWorkingdays BIT NOT NULL  CONSTRAINT DF_ContractPartTimePeriods_VariableWorkingdays DEFAULT 0 
END
GO
--#endregion DH - Variable Workingdays

--#region StaffAdminPermissions 
GRANT INSERT, UPDATE, DELETE ON QualificationCategory TO StaffAdmin
GRANT INSERT, UPDATE, DELETE ON Qualification TO StaffAdmin
GO
--#endregion StaffAdminPermissions 

GRANT EXECUTE ON DeleteUsagesOfUserModified TO SecurityAdmin
GO


--#region 2022-03-03 Reperatur von Besetzungen mit falschen Verträgen
UPDATE [cast] 
SET contractid = NULL 
FROM [cast]
	INNER JOIN contracts ON [cast].contractid = contracts.contractid 
WHERE [cast].personid <> contracts.personid
GO
--#endregion

--#region
ALTER VIEW [dbo].[AbPersDataCategoryData]
AS
	SELECT
		pfd.*
	FROM
		PersonFieldData pfd
	WHERE
		pfd.PersDataCategoryDataId IN (
				SELECT pfd.PersDataCategoryDataId
				FROM PersonFieldData AS pfd 
					INNER JOIN PersonFields AS pf ON pfd.PersonFieldId = pf.PersonFieldId
					INNER JOIN PersonPermissions AS pp ON pfd.personId = pp.personId
					INNER JOIN [Permissions] AS p ON pp.OwnerGroupId =  p.OwnerGroupId 
					INNER JOIN Users AS u ON p.Userid = u.Userid 
				WHERE
					p.PermissionArea = 0
					AND (p.OptionsFlag&pp.OptionsFlag&4 = 4 OR p.OptionsFlag&pp.OptionsFlag&8 = 8 OR pf.ShowOnPage = 1) -- Pressetextseite(1) ist öffentlich
					AND Username = CURRENT_USER		
		)
GO
--#endregion

GRANT SELECT ON ActivityAttachments TO DataService
GRANT SELECT ON PersonAttachments TO DataService
GRANT SELECT ON measuretypeattachments TO DataService
GRANT SELECT ON measureParticipationAttachments TO DataService
GRANT SELECT  ON BasicCast TO DataService
GRANT EXECUTE ON GetDutyRosterByContractsInternal TO DataService
REVOKE EXECUtE ON GetDutyRosterByContractsInternal FROM public
REVOKE EXECUtE ON GetGroupedDutyRosterByContractsInternal FROM public
GO
--#region
--#region
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ProjectTasks') AND [name] = 'ProductionId')
BEGIN
	PRINT('Add  ProductionId column...')
	EXEC ('DELETE FROM ProjectTasks WHERE ProductionInSeasonId IS NULL')
	ALTER TABLE ProjectTasks ADD ProductionId INT NULL
	EXEC ('UPDATE ProjectTasks SET ProductionId = ProductionInSeasons.ProductionId FROM ProjectTasks INNER JOIN ProductionInSeasons ON ProjectTasks.ProductionInSeasonId = ProductionInSeasons.ProductionInSeasonID')
	ALTER TABLE ProjectTasks ALTER COLUMN ProductionId INT NOT NULL
	ALTER TABLE ProjectTasks ADD CONSTRAINT  FK_ProjectTasks_Productions FOREIGN KEY (ProductionId) REFERENCES Productions (ProductionId)
	ALTER TABLE ProjectTasks DROP CONSTRAINT FK_ProjectTasks_ProductionInSeasons
	EXECUTE sp_rename 'ProjectTasks.ProductionInSeasonId', 'x_ProductionInSeasonId', 'COLUMN'
END
GO
--#endregion
--#region 2022-03-29
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ContractTypes_Templates') AND [name] = 'StartDate')
BEGIN
	PRINT('Check ContractTypes_Templates columns...')
	IF EXISTS (SELECT * FROM sysindexes o WHERE o.id = OBJECT_ID(N'ProjectTasks') AND o.Name = 'IX_ProjectTasks_ProductionId') 
		CREATE INDEX IX_ProjectTasks_ProductionId ON ProjectTasks (ProductionId) 
	ALTER TABLE ContractTypes_Templates ADD StartDate DATE NULL
	ALTER TABLE ContractTypes_Templates ADD EndDate DATE NULL

	EXEC('ALTER TABLE ContractTypes_Templates WITH CHECK ADD  CONSTRAINT [CK_ContractTypes_Templates_PeriodCheck] CHECK  (StartDate <= EndDate OR StartDate IS NULL OR EndDate IS NULL)')
	EXEC('ALTER TABLE ContractTypes_Templates CHECK CONSTRAINT [CK_ContractTypes_Templates_PeriodCheck]')
END
GO
--#endregion

--#region  2022-03-30
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('YearClosePeriods') AND [name] = 'CloseState')
BEGIN
	PRINT('Check ContractTypes_Templates columns...')

	ALTER TABLE YearClosePeriods ADD CloseState TINYINT NOT NULL CONSTRAINT DF_YearClosePeriods_CloseState DEFAULT 0
	EXEC('UPDATE YearClosePeriods SET CloseState = 1 WHERE IsClosed = 1')
	EXEC sp_rename 'YearClosePeriods.IsClosed', 'x_IsClosed', 'COLUMN';
END
GO
--#endregion

--#endregion ---------4.222.x ---------------------------------

--#region ---------4.223.x ------------------------------------
--#region AP - WI #17891 - Wohnungsverwaltung Felder für Ein- Auscheck Standardzeiten

PRINT('Check TravelStepTypes - DefaultStartTime...')	
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('TravelStepTypes') AND [name] = 'DefaultStartTime')
BEGIN
	ALTER TABLE TravelStepTypes ADD DefaultStartTime TIME(0) NULL
	ALTER TABLE TravelStepTypes ADD DefaultEndTime TIME(0) NULL
	PRINT('Update TravelStepTypes - DefaultTimes...')	
	EXEC('UPDATE TravelStepTypes SET DefaultStartTime = ''14:00:00'', DefaultEndTime = ''10:00:00''
	WHERE TravelStep = 100')
END
GO

--#endregion AP - WI #17891 - Wohnungsverwaltung Felder für Ein- Auscheck Standardzeiten

--#region 2022-03-18
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Auditions') AND [name] = 'ExternalId') 
BEGIN
	ALTER TABLE Auditions ADD ExternalId INT NULL
END
GO
IF  EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Auditions') AND [name] = 'x_Start') 
BEGIN
	ALTER TABLE Auditions DROP COLUMN x_Start
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Countries') AND [name] = 'Nationality ') 
BEGIN
	ALTER TABLE Countries ADD Nationality VARCHAR(60)
END
GO
--#endregion
GRANT SELECT ON Seasons TO public
GO
--#region 2022-05-09 FK
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_RapportColumns_TimeAccountInOther') AND type = 'F')
BEGIN
	Print 'Adding FK_RapportColumns_TimeAccountInOther'
	UPDATE RapportColumns SET InOtherTimeAccountId = NULL WHERE NOT InOtherTimeAccountId IS NULL AND NOT EXISTS(SELECT * FROM TimeAccounts WHERE InOtherTimeAccountId = TimeAccounts.TimeAccountId) 
	ALTER TABLE RapportColumns  WITH CHECK ADD  CONSTRAINT FK_RapportColumns_TimeAccountInOther FOREIGN KEY(InOtherTimeAccountId)
	REFERENCES TimeAccounts (TimeAccountId)
	ALTER TABLE RapportColumns CHECK CONSTRAINT FK_RapportColumns_TimeAccountInOther
END
GO
--#endregion
--#region 2022-05-09 Erweiterungen zu Invoices
IF NOT EXISTS (SELECT * FROM sysindexes o WHERE o.id = OBJECT_ID(N'InvoicePositions') AND o.Name = 'IX_InvoicePositions_InvoiceId') 
	CREATE CLUSTERED INDEX IX_InvoicePositions_InvoiceId ON InvoicePositions (InvoiceId ASC)
GO 
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Invoices') AND [name] = 'IsCreated') 
	ALTER TABLE Invoices ADD IsCreated BIT NOT NULL
GO
IF NOT EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_Invoices_IsCreated')
	ALTER TABLE Invoices ADD CONSTRAINT DF_Invoices_IsCreated DEFAULT 0 FOR IsCreated
GO
--#endregion


--#region AP - WI #17999 - Dienstplan->Wochenfreigabe: Beim Wechsel zu einigen Freigabestatus wird zur Kommentareingabe aufgefordert. Der Kommentar wird angezeigt

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRosterReleaseWorkflows') AND [name] = 'CommentRequest')
BEGIN
	PRINT('Add DutyRosterReleaseWorkflows - CommentRequest...')	
	ALTER TABLE DutyRosterReleaseWorkflows ADD CommentRequest BIT NOT NULL CONSTRAINT DF_DutyRosterReleaseWorkflows_CommentRequest DEFAULT 0
END
GO


IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRosterPeriods') AND [name] = 'Comment')
BEGIN
	PRINT('Add DutyRosterPeriods - Comment...')	
	ALTER TABLE DutyRosterPeriods ADD Comment VARCHAR(MAX) NULL
END
GO

--#endregion AP - WI #17999
--#region 20220610
IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='ProductionSets')
BEGIN
	PRINT('Add ProductionSets')	
	CREATE TABLE dbo.ProductionSets (
		ProductionSetId	INT IDENTITY(1, 1) NOT NULL,
		[Name]			VARCHAR(50) NOT NULL,
		StartDate		DATE NULL,
		EndDate			DATE NULL,	
		LM				SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionSets_LM DEFAULT GETDATE(),
		UM				INT NULL,
		LC				SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionSet_LC DEFAULT GETDATE(),
		UC				INT NULL
	CONSTRAINT PK_ProductionSet PRIMARY KEY CLUSTERED (ProductionSetId) ON [PRIMARY]
	) ON [PRIMARY]

	CREATE UNIQUE INDEX IX_ProductionSets_Name ON ProductionSets ([Name])

	CREATE TABLE dbo.ProductionSetItems (
		ProductionSetItemId	INT IDENTITY(1, 1) NOT NULL,
		ProductionSetId		INT NOT NULL,
		ProductionId		INT NOT NULL,
		LM					SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionSetItems_LM DEFAULT GETDATE(),
		UM					INT NULL,
		LC					SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionSetItems_LC DEFAULT GETDATE(),
		UC					INT NULL
	CONSTRAINT PK_InvoicePosition PRIMARY KEY NONCLUSTERED (ProductionSetItemId) ON [PRIMARY]
	) ON [PRIMARY]
		
	CREATE UNIQUE CLUSTERED INDEX IX_ProductionSetItems_ProductionSetId_ProductionId ON ProductionSetItems (ProductionSetId, ProductionId)
	CREATE UNIQUE INDEX IX_ProductionSetItems_ProductionId_ProductionSetId ON ProductionSetItems (ProductionId, ProductionSetId)
	
	ALTER TABLE ProductionSets WITH CHECK ADD  CONSTRAINT CK_ProductionSets_PeriodCheck CHECK ((ISNULL(StartDate, '00010101') <= ISNULL(EndDate, '99991231')))
	ALTER TABLE ProductionSets CHECK CONSTRAINT CK_ProductionSets_PeriodCheck

	ALTER TABLE ProductionSetItems  WITH CHECK ADD  CONSTRAINT FK_ProductionSetItems_ProductionSets FOREIGN KEY(ProductionSetId)
		REFERENCES ProductionSets (ProductionSetId)
	ALTER TABLE ProductionSetItems CHECK CONSTRAINT FK_ProductionSetItems_ProductionSets

	ALTER TABLE ProductionSetItems  WITH CHECK ADD  CONSTRAINT FK_ProductionSetItems_Productions FOREIGN KEY(ProductionId)
		REFERENCES Productions (ProductionId)
	ALTER TABLE ProductionSetItems CHECK CONSTRAINT FK_ProductionSetItems_Productions

	GRANT SELECT ON ProductionSets TO Public
	GRANT INSERT, UPDATE, DELETE ON ProductionSets TO DispoAdmin
	GRANT SELECT ON ProductionSetItems TO Public
	GRANT INSERT, UPDATE, DELETE ON ProductionSetItems TO DispoAdmin
END
GO
GO
DropColumn 'ProductionDetails', 'x_MemoIntern'
GO
DropColumn 'ProductionDetails', 'Creator'
GO
IF EXISTS (SELECT * FROM  sysindexes o WHERE o.id = OBJECT_ID(N'PersoParameter') AND o.Name = 'UQ_PersoParameter_AZNameModule') DROP INDEX UQ_PersoParameter_AZNameModule ON PersoParameter
GO
UPDATE ContractTypes SET [Name] = '--' WHERE [Name] IS NULL
ALTER TABLE ContractTypes ALTER COLUMN [NAME] VARCHAR(50) NOT NULL
ALTER TABLE PersoParameter ALTER COLUMN AzName VARCHAR(50) NOT NULL
ALTER TABLE TimeAccounts ALTER COLUMN [Name] VARCHAR(50) NOT NULL
GO
IF NOT EXISTS (SELECT * FROM  sysindexes o WHERE o.id = OBJECT_ID(N'PersoParameter') AND o.Name = 'UQ_PersoParameter_AZNameModule')  
	CREATE UNIQUE NONCLUSTERED INDEX UQ_PersoParameter_AZNameModule ON PersoParameter (AzName , Module) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.ContractType_Department')) DROP TABLE dbo.ContractType_Department

GO
--#endregion 

--#region MaskAdmin
if NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = 'MakeUpAdmin')
BEGIN
	CREATE ROLE MakeUpAdmin
END
GO
GRANT INSERT, UPDATE, DELETE ON ResourceTypes TO MakeUpAdmin
GRANT INSERT, UPDATE, DELETE ON ResourceObjectConditions TO MakeUpAdmin
GRANT INSERT, UPDATE, DELETE ON ResourceFieldTypes TO MakeUpAdmin
GRANT INSERT, UPDATE, DELETE ON Templates TO MakeUpAdmin
GRANT INSERT, UPDATE, DELETE ON ResourceContainerAttachmentsCategories TO MakeUpAdmin
GRANT INSERT, UPDATE, DELETE ON RoleJobStepCategories TO MakeUpAdmin
REVOKE INSERT, UPDATE, DELETE ON ResourceInContainer TO MakeUpAdmin 
REVOKE INSERT, UPDATE, DELETE ON ResourceCreationPartList TO MakeUpAdmin
--#endregion MakeUpAdmin
--#region FundAdmin
if NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = 'FundAdmin')
BEGIN
	CREATE ROLE FundAdmin
END
GO
GRANT INSERT, UPDATE, DELETE ON ResourceTypes TO FundAdmin
GRANT INSERT, UPDATE, DELETE ON ResourceObjectConditions TO FundAdmin
GRANT INSERT, UPDATE, DELETE ON ResourceFieldTypes TO FundAdmin
GRANT INSERT, UPDATE, DELETE ON Templates TO FundAdmin
GRANT INSERT, UPDATE, DELETE ON ResourceContainerAttachmentsCategories TO FundAdmin
--#endregion FundAdmin
GRANT SELECT ON ResourceContainer TO public

IF EXISTS (SELECT * FROM  sysindexes o WHERE o.id = OBJECT_ID(N'ResourceObjects') AND o.Name = 'IX_ResourceObjects_IdentificationCode')
	CREATE INDEX IX_ResourceObjects_IdentificationCode ON ResourceObjects (IdentificationCode)


--#region Fehlerbehebung zu #18012


IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'DeleteUsagesOfUserModified'))
BEGIN
	DROP PROCEDURE DeleteUsagesOfUserModified 
END
GO

CREATE PROCEDURE DeleteUsagesOfUserModified
 @Userid   INT
 WITH EXECUTE AS 'dbo'
  AS 
BEGIN

	SET NOCOUNT ON
	DELETE FROM HistoryConfirmations WHERE UM = @Userid

	DECLARE @table VARCHAR(250), @field VARCHAR(2)
	DECLARE fields CURSOR FOR 
		SELECT o.[name], c.[name] FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id
		WHERE (c.[name] = 'UC' OR c.[name] = 'UM' ) AND o.xtype = 'U' AND  c.xtype = 56

	OPEN fields

	FETCH NEXT FROM fields

	INTO  @table, @field
	WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC ('UPDATE [' + @table  + '] SET [' + @field + ']= -1 WHERE [' + @field + '] = '+ @Userid ) 
			FETCH NEXT FROM fields INTO  @table, @field
		END
		
	CLOSE fields
	DEALLOCATE fields
END
GO
GRANT EXECUtE ON UpdateCastAsDbo TO DataCleaningAdmin
GRANT EXECUtE ON GetContractsASDBO TO DataCleaningAdmin
GRANT SELECT, DELETE, UPDATE, INSERT  ON DutyRoster TO DataCleaningAdmin
GRANT SELECT, DELETE, UPDATE, INSERT  ON resourcefieldtypes TO OrchesterAdmin
GO

--#endregion

--#endregion 
--#endregion ---------4.223.x ---------------------------------

--#region ---------4.224.x ---------------------------------

--#region AP #18126 Fundus: Es können Liefer-, Ausgabescheine verwaltet werden

PRINT('Check ResourceObjectBorrowSets...')
IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='ResourceObjectBorrowSets')
BEGIN
	CREATE TABLE dbo.ResourceObjectBorrowSets (
	ResourceObjectBorrowSetId			INT IDENTITY(1, 1) NOT NULL, 
	PersonId							INT NOT NULL,		--Kunde
	InvoiceId							INT NULL,							--RechnungsId
	[Type]							    TINYINT NOT NULL CONSTRAINT DF_ResourceObjectBorrowSets_BorrowType DEFAULT 0,			--Enum, 0 = Lieferschein
	SetNumber							INT NULL,			--Liefer-, Ausgabescheinnummer
	StartDate							DATE NOT NULL,		--Ausleih Start
	EndDate								DATE NULL,			--Ausleih Ende
	LumpSum								DECIMAL(9, 2) NULL,	--Zusätzlicher Pauschalbetrag
	Lender								VARCHAR(100) NULL,	--Wer leiht aus
	Note								VARCHAR(MAX) NULL,	--Bemerkung
	LM									SMALLDATETIME NOT NULL CONSTRAINT DF_ResourceObjectBorrowSets_LM DEFAULT GETDATE(),
	UM									INT NULL,
	LC									SMALLDATETIME NOT NULL CONSTRAINT DF_ResourceObjectBorrowSets_LC DEFAULT GETDATE(),
	UC									INT NULL,
	OwnerGroupId						INT NOT NULL,
	IsCreated		BIT NOT NULL CONSTRAINT DF_ResourceObjectBorrowSets_IsCreated DEFAULT 0 	--Kennzeichen, ob Liefer-, Ausgabeschein erstellt wurde
	CONSTRAINT PK_ResourceObjectBorrowSetId PRIMARY KEY CLUSTERED (ResourceObjectBorrowSetId) ON [PRIMARY]
	) ON [PRIMARY]

	CREATE INDEX IX_ResourceObjectBorrowSets_PersonId ON ResourceObjectBorrowSets (PersonId ASC)
	CREATE INDEX IX_ResourceObjectBorrowSets_InvoiceId ON ResourceObjectBorrowSets (InvoiceId ASC)
	CREATE UNIQUE INDEX ResourceObjectBorrowSets_SetNumber ON ResourceObjectBorrowSets (SetNumber)
	CREATE INDEX IX_ResourceObjectBorrowSets_StartDate ON ResourceObjectBorrowSets (StartDate ASC)

END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectBorrowSets') AND [name] = 'InvoiceId')
BEGIN
	PRINT('Add ResourceObjectBorrowSets - InvoiceId...')	
	ALTER TABLE ResourceObjectBorrowSets ADD InvoiceId INT NULL
END
GO

PRINT('Check foreign key ResourceObjectBorrowSets-Persons...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceObjectBorrowSets_Persons') AND type = 'F')
BEGIN
	ALTER TABLE ResourceObjectBorrowSets WITH CHECK ADD  CONSTRAINT FK_ResourceObjectBorrowSets_Persons FOREIGN KEY(PersonId)
	REFERENCES Persons (PersonId)
	ALTER TABLE ResourceObjectBorrowSets CHECK CONSTRAINT FK_ResourceObjectBorrowSets_Persons
END
GO

PRINT('Check foreign key ResourceObjectBorrowSets-OwnerGroups...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceObjectBorrowSets_OwnerGroups') AND type = 'F')
BEGIN
	ALTER TABLE ResourceObjectBorrowSets  WITH CHECK ADD  CONSTRAINT FK_ResourceObjectBorrowSets_OwnerGroups FOREIGN KEY(OwnerGroupId)
	REFERENCES OwnerGroups (OwnerGroupId)
	ALTER TABLE ResourceObjectBorrowSets CHECK CONSTRAINT FK_ResourceObjectBorrowSets_OwnerGroups
END
GO

--#endregion AP #18126 Fundus: Es können Liefer-, Ausgabescheine verwaltet werden
--#region AP - 18126, Fundus: Es können Ausgabe/Lieferscheine verwaltet werden

PRINT('Check ResourceObjectBorrowSets...')

--Tabelle ResourceObjectBorrows erweitern
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectBorrows') AND [name] = 'ResourceObjectBorrowSetId') 
	ALTER TABLE ResourceObjectBorrows ADD ResourceObjectBorrowSetId INT NULL

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectBorrows') AND [name] = 'Quantity') 
	ALTER TABLE ResourceObjectBorrows ADD Quantity SMALLINT NULL CONSTRAINT FK_ResourceObjectBorrows_Quantity DEFAULT 1

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectBorrows') AND [name] = 'RentalFee') 
	ALTER TABLE ResourceObjectBorrows ADD RentalFee	DECIMAL(9, 2) NULL
	
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectBorrows') AND [name] = 'Position') 
	ALTER TABLE ResourceObjectBorrows ADD Position SMALLINT NOT NULL CONSTRAINT FK_ResourceObjectBorrows_Position DEFAULT 0
GO

PRINT('Check foreign key ResourceObjectBorrows - ResourceObjectBorrowSetId...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceObjectBorrows_ResourceObjectBorrowSets') AND type = 'F') 
BEGIN
	ALTER TABLE ResourceObjectBorrows ADD CONSTRAINT FK_ResourceObjectBorrows_ResourceObjectBorrowSets 
	FOREIGN KEY (ResourceObjectBorrowSetId) 
	REFERENCES ResourceObjectBorrowSets (ResourceObjectBorrowSetId) 
END
GO

PRINT('Check foreign key ResourceObjectBorrowSets-Invoice...')
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceObjectBorrowSets_Invoice') AND type = 'F')
BEGIN
	ALTER TABLE ResourceObjectBorrowSets WITH CHECK ADD  CONSTRAINT FK_ResourceObjectBorrowSets_Invoice FOREIGN KEY(InvoiceId)
	REFERENCES Invoices (InvoiceId) ON DELETE SET NULL
	ALTER TABLE ResourceObjectBorrowSets CHECK CONSTRAINT FK_ResourceObjectBorrowSets_Invoice
END
GO

PRINT('Creating ResourceObjectBorrowSets views...') 
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbResourceObjectBorrowSets') )
	DROP VIEW AbResourceObjectBorrowSets
GO
	CREATE VIEW dbo.AbResourceObjectBorrowSets
	AS 
	SELECT 	I.*
	FROM	ResourceObjectBorrowSets AS I
				INNER JOIN	(
				SELECT OwnerGroupId, HasWritePermission 
					FROM [Permissions] AS ug 
						INNER JOIN Users ON ug.Userid = Users.Userid 
				WHERE Username = CURRENT_USER  AND PermissionArea = 16 AND ug.OptionsFlag&2 = 2
			) AS UG ON I.OwnerGroupId = UG.OwnerGroupId 
GO
GRANT SELECT ON AbResourceObjectBorrowSets TO public
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbResourceObjectBorrowSets_Update') )
	DROP VIEW AbResourceObjectBorrowSets_Update
GO
CREATE VIEW dbo.AbResourceObjectBorrowSets_Update
AS 
SELECT * FROM ResourceObjectBorrowSets WHERE ResourceObjectBorrowSetId IN (
	SELECT RU.ResourceObjectBorrowSetId FROM ResourceObjectBorrowSets AS RU
	INNER  JOIN	(SELECT DISTINCT OwnerGroupId, HasWritePermission 
						FROM	[Permissions] AS ug
							INNER JOIN	Users ON ug.Userid = Users.Userid 
						WHERE Username = CURRENT_USER AND PermissionArea = 16 AND ug.OptionsFlag&2 = 2) AS UG
				ON RU.OwnerGroupId = UG.OwnerGroupId
				WHERE UG.HasWritePermission = 1)
WITH CHECK OPTION
GO
GRANT SELECT ON AbResourceObjectBorrowSets_Update TO public
GRANT INSERT, UPDATE, DELETE ON AbResourceObjectBorrowSets_Update TO Public
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbResourceObjectBorrows') )
	DROP VIEW AbResourceObjectBorrows
GO
CREATE VIEW dbo.AbResourceObjectBorrows
AS 
SELECT RP.* FROM ResourceObjectBorrows AS RP
GO
GRANT SELECT ON AbResourceObjectBorrows TO public
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbResourceObjectBorrows_Update') )
	DROP VIEW AbResourceObjectBorrows_Update
GO
CREATE VIEW AbResourceObjectBorrows_Update
AS 
SELECT RP.* FROM ResourceObjectBorrows AS RP 
	WHERE ResourceObjectId IN 
							(
					SELECT  ro.ResourceObjectId FROM dbo.ResourceObjects AS ro
						INNER JOIN Resources AS r ON ro.ResourceId = r.ResourceId
						INNER JOIN [Permissions] AS ug ON r.OwnerGroupId = ug.OwnerGroupId
						INNER JOIN Users AS pw ON ug.UserId = pw.UserId
					WHERE Username = CURRENT_USER AND PermissionArea = 16 AND ug.OptionsFlag&2 = 2
							)

WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbResourceObjectBorrows_Update TO Public

GO

IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetMaxBorrowNumber'))
BEGIN
	DROP PROCEDURE dbo.GetMaxBorrowNumber 
END
GO

CREATE PROCEDURE dbo.GetMaxBorrowNumber
AS
BEGIN
    SELECT ISNULL(MAX(SetNumber),1) FROM ResourceObjectBorrowSets
END
GO
GRANT EXECUTE ON GetMaxBorrowNumber TO Public
GO

--#endregion 18126, Fundus: Es können Ausgabe/Lieferscheine verwaltet werden



--#region 2022 06 28
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ResourceObjectMonetaryValues_ResourceObjectId_EstimateDate' AND object_id = OBJECT_ID(N'dbo.ResourceObjectMonetaryValues')) 
BEGIN

Print 'Create ResourceObjectMonetaryValues IX_ResourceObjectMonetaryValues_ResourceObjectId_EstimateDate'

	EXEC('UPDATE ResourceObjectMonetaryValues SET Start = ''19000101'' WHERE Start IS NULL')
	EXEC('DELETE FROM ResourceObjectMonetaryValues WHERE ResourceObjectMonetaryValueId IN (SELECT MIN(ResourceObjectMonetaryValueId) FROM ResourceObjectMonetaryValues GROUP BY ResourceObjectId, [Start] HAVING COUNT(*) > 1)')
	EXEC('DELETE FROM ResourceObjectMonetaryValues WHERE ResourceObjectMonetaryValueId IN (SELECT MIN(ResourceObjectMonetaryValueId) FROM ResourceObjectMonetaryValues GROUP BY ResourceObjectId, [Start] HAVING COUNT(*) > 1)')
	EXEC('DELETE FROM ResourceObjectMonetaryValues WHERE ResourceObjectMonetaryValueId IN (SELECT MIN(ResourceObjectMonetaryValueId) FROM ResourceObjectMonetaryValues GROUP BY ResourceObjectId, [Start] HAVING COUNT(*) > 1)')

	EXEC sp_rename 'ResourceObjectMonetaryValues.Start', 'EstimateDate', 'COLUMN';
--	EXEC('UPDATE ResourceObjectMonetaryValues SET EstimateDate = ''19000101'' WHERE EstimateDate IS NULL')
	ALTER TABLE ResourceObjectMonetaryValues ALTER COLUMN EstimateDate DATE NOT NULL

	ALTER TABLE dbo.ResourceObjectMonetaryValues  DROP COLUMN [End]
	ALTER TABLE dbo.ResourceObjectMonetaryValues DROP CONSTRAINT PK_ResourceObjectMonetaryValues
	   
	CREATE UNIQUE CLUSTERED INDEX IX_ResourceObjectMonetaryValues_ResourceObjectId_EstimateDate ON dbo.ResourceObjectMonetaryValues (ResourceObjectId, EstimateDate DESC)
	
	ALTER TABLE dbo.ResourceObjectMonetaryValues ADD CONSTRAINT PK_ResourceObjectMonetaryValues PRIMARY KEY NONCLUSTERED (ResourceObjectMonetaryValueId) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectInsurances') AND [name] = 'StartDate')
BEGIN
	EXEC sp_rename 'ResourceObjectInsurances.Start', 'StartDate', 'COLUMN';
	EXEC sp_rename 'ResourceObjectInsurances.End', 'EndDate', 'COLUMN';
END
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ResourceObjectInsurances_ResourceObjectId_StartDate' AND object_id = OBJECT_ID(N'dbo.ResourceObjectInsurances')) 
BEGIN
	--IX_ResourceObjectInsurances_ResourceObjectId_StartDate wurd mal ohne IsPrivateInsurance angelegt
	DROP INDEX IX_ResourceObjectInsurances_ResourceObjectId_StartDate ON ResourceObjectInsurances
	CREATE UNIQUE CLUSTERED INDEX IX_ResourceObjectInsurances_ResourceObjectId_StartDate_IsPrivateInsurance ON dbo.ResourceObjectInsurances (ResourceObjectId, StartDate DESC, IsPrivateInsurance)
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ResourceObjectInsurances_ResourceObjectId_StartDate_IsPrivateInsurance' AND object_id = OBJECT_ID(N'dbo.ResourceObjectInsurances')) 
BEGIN

	EXEC('UPDATE ResourceObjectInsurances SET StartDate = ''19000101'' WHERE StartDate IS NULL')
	EXEC('UPDATE ResourceObjectInsurances SET [EndDate] = [StartDate] WHERE NOT [StartDate] IS NULL AND NOT [EndDate] IS NULL AND [StartDate] > [EndDate]')
	   	
	ALTER TABLE ResourceObjectInsurances ALTER COLUMN StartDate DATE NOT NULL
	IF EXISTS (SELECT * FROM sysobjects WHERE  id = OBJECT_ID(N'dbo.PK_ResourceObjectInsurances')) 
	BEGIN
		ALTER TABLE dbo.ResourceObjectInsurances DROP CONSTRAINT PK_ResourceObjectInsurances
	 END
	CREATE UNIQUE CLUSTERED INDEX IX_ResourceObjectInsurances_ResourceObjectId_StartDate_IsPrivateInsurance ON dbo.ResourceObjectInsurances (ResourceObjectId, StartDate DESC, IsPrivateInsurance)
	
	ALTER TABLE ResourceObjectInsurances ADD CONSTRAINT PK_ResourceObjectInsurances PRIMARY KEY NONCLUSTERED (ResourceObjectInsuranceId) ON [PRIMARY]

	EXEC('ALTER TABLE ResourceObjectInsurances  WITH CHECK ADD  CONSTRAINT CK_ResourceObjectInsurances_PeriodCheck CHECK (StartDate<=EndDate OR EndDate IS NULL)')
	ALTER TABLE ResourceObjectInsurances CHECK CONSTRAINT CK_ResourceObjectInsurances_PeriodCheck
END

GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'AbResourceObjectsWithTodaysInsurancesAndMonetaryValue') )
	DROP VIEW AbResourceObjectsWithTodaysInsurancesAndMonetaryValue

 GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectMonetaryValues') AND [name] = 'EstimatedByPersonId')
BEGIN
	PRINT('Add ResourceObjectMonetaryValues - EstimatedByPersonId...')	
	ALTER TABLE ResourceObjectMonetaryValues ADD EstimatedByPersonId INT NULL

	ALTER TABLE ResourceObjectMonetaryValues WITH CHECK ADD  CONSTRAINT FK_ResourceObjectMonetaryValues_EstimatedByPerson FOREIGN KEY(EstimatedByPersonId)
	REFERENCES Persons (PersonId)
	ALTER TABLE ResourceObjectMonetaryValues CHECK CONSTRAINT FK_ResourceObjectMonetaryValues_EstimatedByPerson
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_Productions_ProductionType' AND object_id = OBJECT_ID(N'dbo.Productions')) 
	CREATE NONCLUSTERED INDEX IX_Productions_ProductionType ON Productions (ProductionType) WHERE (productiontype > (0))

IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRoster') AND [name] = 'X_TimeAccountId1')	ALTER TABLE dbo.DutyRoster  DROP COLUMN X_TimeAccountId1
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRoster') AND [name] = 'X_TimeAccountId2')	ALTER TABLE dbo.DutyRoster  DROP COLUMN X_TimeAccountId2
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRoster') AND [name] = 'X_TimeAccountId3')	ALTER TABLE dbo.DutyRoster  DROP COLUMN X_TimeAccountId3
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Cast') AND [name] = 'AgencyId')	ALTER TABLE dbo.Cast  DROP COLUMN AgencyId
GO

--#endregion

--#region 2022
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('HolidayCompensationRuleSets') AND [name] = 'CompensationOnVariableWorkingDay')
BEGIN
	PRINT('Add HolidayCompensationRuleSets - CompensationOnVariableWorkingDay...')	
	ALTER TABLE HolidayCompensationRuleSets ADD CompensationOnVariableWorkingDay BIT NOT NULL CONSTRAINT DF_CompensationOnVariableWorkingDay_VariableWorkingDays DEFAULT 0
END


IF EXISTS (SELECT * FROM sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Roles_WorkAreaGroups') AND type = 'F') ALTER TABLE dbo.Roles DROP CONSTRAINT FK_Roles_WorkAreaGroups
IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.WorkAreaGroups')) DROP TABLE dbo.WorkAreaGroups
GO
DropColumn 'Roles', 'x_StartMasktime'
GO
DropColumn 'Roles', 'x_EndMasktime'
GO
DropColumn 'Roles', 'WorkAreaGroupId'
GO
IF EXISTS (SELECT * FROM sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_BasicCast_Seasons') AND type = 'F') ALTER TABLE dbo.BasicCast DROP CONSTRAINT FK_BasicCast_Seasons
GO
DropColumn 'BasicCast', 'x_SeasonId'
GO
DropColumn 'BasicCast', 'X_BasicCastCategoryId'
GO

--#endregio
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_WorkVersions_WorkId' AND object_id = OBJECT_ID(N'dbo.WorkVersions')) 
	CREATE NONCLUSTERED INDEX IX_WorkVersions_WorkId ON WorkVersions (WorkId )

--#endregion

--#region 2022-07-12
IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'WarningContactWithoutOwnerPermission')) DROP PROCEDURE dbo.WarningContactWithoutOwnerPermission
GO
CREATE PROCEDURE dbo.WarningContactWithoutOwnerPermission AS
BEGIN
	DECLARE @maxPermission INT
	--Der max falg-Wert sollten die maximalen Berechtigugnen sein, zu Zeitpunkt der Scripterstellen ist der Wert 262143
	--Diser wert wid auch verwendet wenn kein Berechtigungen enthalten sind
	SELECT @maxPermission = ISNULL(MAX(OptionsFlag), 262143) FROM PersonPermissions
	IF (@maxPermission IS NULL OR  @maxPermission < 262143) SET @maxPermission = 262143

	INSERT INTO PersonPermissions (PersonId, OwnerGroupId, HasWritePermission, OptionsFlag, LM, UM, LC, UC)
	SELECT PersonId, OwnerGroupId, 1, @maxPermission, ISNULL(LM, '19000101'), ISNULL(UM, -1), ISNULL(LC, '19000101'), ISNULL(UC, -1) FROM Persons 
		WHERE NOT EXISTS (SELECT * FROM PersonPermissions AS PP WHERE Persons.PersonId = PP.PersonId AND Persons.OwnerGroupId = PP.OwnerGroupId)
END
GO
GRANT EXECUTE ON WarningContactWithoutOwnerPermission TO Public
--#endregio
IF NOT EXISTS (SELECT * FROM sysobjects WHERE  id = OBJECT_ID(N'dbo.CK_CashCommands_PeriodCheck'))
BEGIN
	PRINT 'Add CK_CashCommands_PeriodCheck'
	UPDATE CashCommands SET BillingPeriodEnd = BillingPeriodStart WHERE BillingPeriodStart > BillingPeriodEnd
	ALTER TABLE CashCommands WITH CHECK ADD CONSTRAINT CK_CashCommands_PeriodCheck CHECK (BillingPeriodStart <= BillingPeriodEnd)
	ALTER TABLE Cashcommands CHECK CONSTRAINT CK_CashCommands_PeriodCheck
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_CashCommands_OwnerGroups') AND type = 'F')
BEGIN
	PRINT 'Add FK_CashCommands_OwnerGroups'
	UPDATE CashCommands SET OwnerGroupId = 1 WHERE NOT EXISTS(SELECT * FROM OwnerGroups WHERE OwnerGroups.OwnerGroupId = CashCommands.OwnerGroupId )
	ALTER TABLE dbo.CashCommands ADD CONSTRAINT FK_CashCommands_OwnerGroups FOREIGN KEY (OwnerGroupId) REFERENCES OwnerGroups (OwnerGroupId) 
END
--#endregion

--#region DH - 2022-07-07 ContractPartTimePeriods Use Working Minutes
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ContractPartTimePeriods') AND [name] = 'UseWorkingMinutes')
BEGIN
Print('ADD ContractPartTimePeriods - UseWorkingMinutes ')
	ALTER TABLE ContractPartTimePeriods ADD UseWorkingMinutes BIT NOT NULL CONSTRAINT DF_ContractPartTimePeriods_UseWorkingMinutes DEFAULT 1
Print('ADDED ContractPartTimePeriods - UseWorkingMinutes ')
END
GO
--#endregion DH - 2022-07-07 ContractPartTimePeriods Use Working Minutes
--#region DH - 220801 PersonrequiredMeasures
--#region 2022-01-31 mit Reschreibrechten auch lesen
ALTER VIEW [dbo].[AbMeasureParticipations]
AS 
SELECT	mp.*,(
				SELECT COUNT(*) AS FollowerCount
					FROM  MeasureParticipations mps 
					WHERE mps.PersonId = mp.PersonId 
						AND mps.ParticipationDate > mp.ParticipationDate 
						AND mps.MeasureTypeId = mp.MeasureTypeId
			 ) AS FollowerCount,
			 (Select dateofBirth from PersonDetails where PersonDetails.PersonId=mp.PersonId) as Birthday
FROM
	MeasureParticipations mp
WHERE 
	mp.PersonId IN (
		SELECT  pp.PersonId
		FROM PersonPermissions AS pp
			INNER JOIN [Permissions] AS p ON pp.OwnerGroupId =  p.OwnerGroupId 
			INNER JOIN Users u ON p.Userid = u.Userid 
		WHERE 
			(p.OptionsFlag&pp.OptionsFlag&65536 = 65536 OR p.OptionsFlag&pp.OptionsFlag&131072 = 131072)
			AND PermissionArea = 0
			AND Username = CURRENT_USER
		GROUP BY pp.PersonId
	)
GO
--#endregion DH 220801 PersonrequieredMeasures
--#region dh 220803 ShowAllDepartmentMember
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER PROCEDURE [dbo].[GetContractsInternal]
	@condition VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT,
	@orContact INT
	
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @SQLQuery AS NVARCHAR(MAX)

	IF (@include <> 0)
	BEGIN 
		CREATE TABLE #Contract (ContractId INT NOT NULL PRIMARY KEY , OwnerGroupId INT NOT NULL)
	END

	--Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben
	if (@include <> 0) 
		BEGIN
			SET @SQLQuery = 'INSERT INTO #Contract SELECT v.ContractId, v.OwnerGroupId   '
		END	
	ELSE
		BEGIN
			SET @SQLQuery = 'SELECT *'
		END
--print 'set sqlQuery'
	SET @SQLQuery = @SQLQuery + ' FROM Contracts AS v '

	IF (@joins&1 = 1)
		SET @SQLQuery = @SQLQuery + 'INNER JOIN Persons AS P ON v.PersonId = p.PersonId '

	IF (@joins&2 = 2)
		SET @SQLQuery = @SQLQuery + 'INNER JOIN OwnerGroups AS g ON v.OwnerGroupId = g.OwnerGroupId '

	IF (@joins&4 = 4)
		SET @SQLQuery = @SQLQuery + 'INNER JOIN ContractTypes AS vv ON vv.ContractTypeId = v.ContractTypeId '

	IF (@joins&8 = 8)
		SET @SQLQuery = @SQLQuery + 'INNER JOIN ContractDetails vd ON vd.ContractId = v.ContractId '

	IF (@joins&16 = 16)
		SET @SQLQuery = @SQLQuery + 'INNER JOIN PersonDetails pd ON v.PersonId = pd.PersonId '
--print @sqlquery
	DECLARE @securetyWhere AS NVARCHAR(MAX)
	SET @securetyWhere = ' EXISTS (SELECT * FROM Permissions p1 WHERE UserId = ' + CAST(@userId AS VARCHAR(MAX)) +' AND v.OwnerGroupId = p1.OwnerGroupId AND p1.PermissionArea IN (1, 5))'
	IF (@orContact <> 0)	SET @securetyWhere = '(' + @securetyWhere + ' OR v.PersonId = ' + CAST(@orContact AS VARCHAR(MAX)) + ')'
	IF (@include&1 = 1)  SET @securetyWhere = '(' + @securetyWhere + ' OR v.contractid in (select contractid from ContractDepartments c inner join WorkAreas w on c.WorkAreaId = w.WorkAreaId inner join departments d on w.departmentid=d.departmentid'
						+'  where EXISTS (SELECT * FROM Permissions p2 WHERE UserId = ' + CAST(@userId AS VARCHAR(MAX)) +' AND d.OwnerGroupId = p2.OwnerGroupId AND p2.PermissionArea = 5) ))'
	SET @SQLQuery = @SQLQuery+ ' WHERE ' + @securetyWhere

	IF (@condition IS NOT NULL  AND @condition <> '')
	SET @SQLQuery = @SQLQuery + ' AND ' + @condition 
--print @sqlquery
	EXECUTE sp_Executesql @SQLQuery

	--Wenn keine weiteren Daten mitgeladen werden wurde das Ergebnis bereits zurückgeben
	IF (@include <> 0) 
	BEGIN
		SELECT * FROM Contracts AS V INNER JOIN #Contract ON v.ContractId = #Contract.ContractId 
	END
	ELSE
	BEGIN
		RETURN 
	END

	IF (@include&1 = 1) --Alle zugeordneten Perso Verträge
		SELECT NULL AS [#1], ContractDepartments.*
		FROM ContractDepartments
			INNER JOIN #Contract ON ContractDepartments.ContractId = #Contract.ContractId

	IF (@include&4 = 4)
	SELECT NULL AS [#4],
		vr.ContractId, vr.RoleId, vr.ContractEngagementId, vr.ProductionId, vr.StartDate, vr.EndDate, vr.RehearsalsCompensatedByPerformance, vr.CompensatedRehearselDays, vr.ProjectedPerformanceCount, vr.LM, vr.UM, vr.LC, vr.UC 
	FROM  
		ContractEngagements AS vr 
				INNER JOIN #Contract ON vr.ContractId = #Contract.ContractId
				INNER JOIN Permissions AS u ON u.UserId = @userId AND #Contract.OwnerGroupId = u.OwnerGroupId AND u.PermissionArea = 1

	IF (@include&8 = 8) 
		SELECT NULL AS [#8], ContractDetails.*
		FROM ContractDetails
			INNER JOIN #Contract ON ContractDetails.ContractId = #Contract.ContractId

	IF (@include&16 = 16)
		SELECT NULL AS [#16], ContractBonus.* 
		FROM ContractBonus 
			INNER JOIN #Contract ON ContractBonus.ContractId = #Contract.ContractId

	IF (@include&32 = 32)
		SELECT NULL AS [#32], Contract_Qualification_Person.*
		FROM Contract_Qualification_Person
			INNER JOIN #Contract ON Contract_Qualification_Person.ContractId = #Contract.ContractId

	IF (@include&64 = 64)
		SELECT NULL AS [#64], ContractFields.*
		FROM ContractFields
			INNER JOIN #Contract ON ContractFields.ContractId = #Contract.ContractId

	IF (@include&128 = 128)
		SELECT NULL AS [#128], TimeAccountUsage.*
		FROM TimeAccountUsage
			INNER JOIN #Contract ON TimeAccountUsage.ContractId = #Contract.ContractId

	IF (@include&256 = 256)
		SELECT NULL AS [#256], ContractPartTimePeriods.*
		FROM ContractPartTimePeriods
			INNER JOIN #Contract ON ContractPartTimePeriods.ContractId = #Contract.ContractId

	IF (@include&512 = 512)
		SELECT NULL AS [#512], ContractWorkingTimeModels.*
		FROM ContractWorkingTimeModels
			INNER JOIN #Contract ON ContractWorkingTimeModels.ContractId = #Contract.ContractId

	IF (@include&1024 = 1024)
		SELECT NULL AS [#1024], ContractSalaryRules.*
		FROM ContractSalaryRules
			INNER JOIN #Contract ON ContractSalaryRules.ContractId = #Contract.ContractId
			INNER JOIN Permissions AS u ON u.UserId = @userId AND #Contract.OwnerGroupId = u.OwnerGroupId 
		WHERE u.PermissionArea = 1 AND u.OptionsFlag&1 = 1

	IF (@include&2048 = 2048)
		SELECT NULL AS [#2048], ContractMeasureGroups.*
		FROM ContractMeasureGroups
			INNER JOIN #Contract ON ContractMeasureGroups.ContractId = #Contract.ContractId
	DROP TABLE #Contract
END
GO
--#endregion dh 220803 ShowAllDepartmentMember
--#region dh 220805 WorkTimeOverLimit
IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetWeeklyMinutesOverLimit'))
BEGIN
	DROP PROCEDURE dbo.GetWeeklyMinutesOverLimit 
END
GO
CREATE PROCEDURE GetWeeklyMinutesOverLimit 
	@start DATE,
	@end DATE,
	@limit INT,
	@contractids IdFilterTable READONLY
AS
BEGIN
	SELECT contractId, SUM(val) 
	FROM (SELECT contractId,
				SUM(DutyRoster.WorkingMinutes1+DutyRoster.WorkingMinutes2+DutyRoster.WorkingMinutes3+DutyRoster.MiscWorkingMinutes) -
				ISNULL((SELECT TOP 1 (WorkPercent) 
					FROM ContractPartTimePeriods as p 
					WHERE p.ContractId = DutyRoster.ContractId AND 
						p.[UseWorkingMinutes] = 1 AND
						((p.StartDate >=DATEADD(DAY, CASE WHEN DATEPART(DW, [Date]) = 1 THEN 6 ELSE DATEPART(DW, [Date]) -2 END * -1, [Date]) OR p.StartDate IS NULL ) 											AND
						(p.EndDate>=DATEADD(DAY, CASE WHEN DATEPART(DW, [Date]) = 1 THEN 6 ELSE DATEPART(DW, [Date]) -2 END * -1, [Date]) OR p.EndDate IS NULL)
			)), 100) * @limit / 100  AS val
			FROM DutyRoster
			WHERE [Date] BETWEEN @start AND @end AND 
				 contractid IN (SELECT Id FROM @contractids)
			GROUP BY contractId, DATEADD(DAY, CASE WHEN DATEPART(DW, [Date]) = 1 THEN 6 ELSE DATEPART(DW, [Date]) -2 END * -1, [Date])
		) AS sub 
	WHERE val > 0 GROUP BY contractId
END
GO
GRANT EXECUTE ON GetWeeklyMinutesOverLimit TO Public

GO
--#endregion WorktimeOverLimit
--#region GroupSettingsGlobal
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('OwnerGroupSettings') AND [name] = 'Description') 
	ALTER TABLE OwnerGroupSettings ADD [Description] VARCHAR(255) NULL

GO
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 224 OR (Minor = 224 AND Build < 7)))))
BEGIN

	ALTER TABLE OwnerGroupSettings ALTER COLUMN OwnerGroupId INT NULL
	ALTER TABLE OwnerGroupSettings ALTER COLUMN SettingsXML XML NULL
	ALTER TABLE OwnerGroupSettings ALTER COLUMN SettingsKey VARCHAR(50) NOT NULL

	SET  IDENTITY_INSERT OwnerGroupSettings ON
	INSERT INTO OwnerGroupSettings (GroupSettingId, SettingSection, SettingsKey, [Description], PermissionAreaId, SettingsXML, LM, lC, UC, UM)
	VALUES
	(100, 'ActivityFilter','Alles Öffentliche', 'Vorstellungen, Einführungen etc.', 2 ,'<CriteriaOperator xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:type="BinaryOperator" OperatorType="Equal">
	  <LeftOperand xsi:type="OperandProperty" PropertyName="ActivityTypeFilter" />
	  <RightOperand xsi:type="OperandValue">
		<string>public</string>
	  </RightOperand>
	</CriteriaOperator>', GETDATE(), GETDATE(), -1, -1),
	(101, 'ActivityFilter','Alles Öffentliche und Proben', 'Vorstellungen, Einführungen etc. und Proben', 2 ,'<CriteriaOperator xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="BinaryOperator" OperatorType="Equal">
	  <LeftOperand xsi:type="OperandProperty" PropertyName="ActivityTypeFilter" />
	  <RightOperand xsi:type="OperandValue">
		<string>public,privRehearsal</string>
	  </RightOperand>
	</CriteriaOperator>', GETDATE(), GETDATE(), -1, -1),
	(102, 'ActivityFilter','Vorstellungen und Endproben', 'Vorstellungen und Endproben', 2 ,'<CriteriaOperator xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="BinaryOperator" OperatorType="Equal">
	  <LeftOperand xsi:type="OperandProperty" PropertyName="ActivityTypeFilter" />
	  <RightOperand xsi:type="OperandValue">
		<string>pubPerformance,privRehearsal_EndRehearsal</string>
	  </RightOperand>
	</CriteriaOperator>', GETDATE(), GETDATE(), -1, -1),
	(103, 'ActivityFilter','Öffentliches, Proben und technische Aufgaben','Alles außer Soloproben, Auditions etc.', 2 ,'<CriteriaOperator xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="BinaryOperator" OperatorType="Equal">
	  <LeftOperand xsi:type="OperandProperty" PropertyName="ActivityTypeFilter" />
	  <RightOperand xsi:type="OperandValue">
		<string>public,privRehearsal,privTechnicalJob</string>
	  </RightOperand>
	</CriteriaOperator>', GETDATE(), GETDATE(), -1, -1),
	(105, 'ActivityFilter','Alles','Alle Aktivitäten', 2 ,null, GETDATE(), GETDATE(), -1, -1)
	SET  IDENTITY_INSERT OwnerGroupSettings OFF

	UPDATE DutyRosterPrintLayouts 
	SET LayoutSettings = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(LayoutSettings AS VARCHAR(MAX)),
		'<ActivityLayer>ActivityLayer_0</ActivityLayer>','<OwnerGroupFilterSettingId>100</OwnerGroupFilterSettingId>'),
		'<ActivityLayer>ActivityLayer_1</ActivityLayer>','<OwnerGroupFilterSettingId>101</OwnerGroupFilterSettingId>'),
		'<ActivityLayer>ActivityLayer_2</ActivityLayer>','<OwnerGroupFilterSettingId>101</OwnerGroupFilterSettingId>'),
		'<ActivityLayer>ActivityLayer_3</ActivityLayer>','<OwnerGroupFilterSettingId>103</OwnerGroupFilterSettingId>'),
		'<ActivityLayer>ActivityLayer_4</ActivityLayer>','<OwnerGroupFilterSettingId>103</OwnerGroupFilterSettingId>'),
		'<ActivityLayer>ActivityLayer_5</ActivityLayer>','<OwnerGroupFilterSettingId>105</OwnerGroupFilterSettingId>')
	FROM DutyRosterPrintLayouts WHERE NOT LayoutSettings IS NULL 
END

GO
ALTER VIEW [dbo].[ABGroupSettings]
		AS
	SELECT	OwnerGroupSettings.*
	FROM	OwnerGroupSettings
	WHERE EXISTS (
			SELECT R.* 
			FROM OwnerGroupSettings AS R
			INNER JOIN [Permissions] AS Ug ON r.OwnerGroupId = Ug.OwnerGroupId AND Ug.PermissionArea = R.PermissionAreaId
			INNER JOIN Users ON Ug.Userid  = Users.Userid
			WHERE Users.Username = CURRENT_USER) OR OwnerGroupId IS NULL
GO
GRANT SELECT ON OwnerGroupSettings TO Public
GO
ALTER VIEW [dbo].[ABGroupSettings_Update]
		AS
	SELECT	OwnerGroupSettings.*
	FROM	OwnerGroupSettings
	WHERE EXISTS (
			SELECT * FROM OwnerGroupSettings AS ogs
					INNER JOIN [Permissions] AS Ug ON ogs.OwnerGroupId = Ug.OwnerGroupId AND Ug.PermissionArea = ogs.PermissionAreaId  
					INNER JOIN Users ON Ug.Userid = Users.Userid 
					WHERE ug.HasWritePermission = 1 AND 
						  Users.Username = CURRENT_USER AND 
						  ogs.GroupSettingId = OwnerGroupSettings.GroupSettingId
					)
					OR (OwnerGroupSettings.OwnerGroupId IS NULL AND (IS_ROLEMEMBER('DispoAdmin') = 1 OR IS_ROLEMEMBER('dbo') = 1 ))
WITH CHECK OPTION
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('SubscriptionTypes') AND [name] = 'NumberInSchoolHoliday') 
BEGIN
	ALTER TABLE SubscriptionTypes ADD NumberInSchoolHoliday TINYINT NOT NULL CONSTRAINT DF_SubscriptionTypes_NumberInSchoolHoliday DEFAULT 0
	EXEC ('UPDATE SubscriptionTypes SET NumberInSchoolHoliday = 255 WHERE NotInSchoolHoliday = 1')
	EXEC sp_rename 'SubscriptionTypes.NotInSchoolHoliday', 'X_NotInSchoolHoliday', 'COLUMN';
END
GO
--#endregion GroupSettingsGlobal DH 20220829


--#regio 2022-10-27
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('PersoParameter') AND [name] = 'WorkingTimeInterruptionIsBreakUntil') 
BEGIN
	ALTER TABLE PersoParameter ADD WorkingTimeInterruptionIsBreakUntil SMALLINT NOT NULL CONSTRAINT DF_PersoParameter_WorkingTimeInterruptionIsBreakUntil DEFAULT 0
END
GO

IF EXISTS (SELECT * FROM sys.tables WHERE  [name]='ResourceObjectContainerParts') 
BEGIN
	Print 'ResourceObjectContainerParts->ResourceObjectPersons'

	EXEC sp_rename 'ResourceObjectContainerParts', 'ResourceObjectPersons';
	EXEC sp_rename 'ResourceObjectPersons.ResourceObjectContainerPartId', 'ResourceObjectPersonId', 'COLUMN';
	ALTER TABLE ResourceObjectPersons ADD PersonId INT NULL
	ALTER TABLE ResourceObjectPersons ADD ResourceContainerId INT NULL
	exec ('UPDATE ResourceObjectPersons SET PersonId = ResourceObjectContainers.PersonId FROM ResourceObjectPersons INNER JOIN ResourceObjectContainers ON ResourceObjectPersons.ResourceObjectContainerId = ResourceObjectContainers.ResourceObjectContainerId')
	exec ('UPDATE ResourceObjectPersons SET ResourceContainerId = ResourceObjectContainers.ResourceContainerId FROM ResourceObjectPersons INNER JOIN ResourceObjectContainers ON ResourceObjectPersons.ResourceObjectContainerId = ResourceObjectContainers.ResourceObjectContainerId')
	
	ALTER TABLE ResourceObjectPersons ALTER COLUMN PersonId INT NULL
	ALTER TABLE ResourceObjectPersons ALTER COLUMN ResourceContainerId INT NOT NULL

	ALTER TABLE dbo.ResourceObjectPersons DROP CONSTRAINT FK_ResourceObjectContainerParts_ResourceObjectContainers
	
	ALTER TABLE dbo.ResourceObjectPersons ADD CONSTRAINT FK_ResourceObjectPersons_Persons FOREIGN KEY (PersonId) REFERENCES Persons (PersonId)
	ALTER TABLE dbo.ResourceObjectPersons ADD CONSTRAINT FK_ResourceObjectPersons_ResourceContainer FOREIGN KEY (ResourceContainerId) REFERENCES ResourceContainer (ResourceContainerId)
	
	DROP INDEX IX_ResourceObjectContainerParts_ResourceObjectContainerId_ResourceObjectId ON ResourceObjectPersons
	ALTER TABLE ResourceObjectPersons DROP COLUMN ResourceObjectContainerId
	CREATE UNIQUE CLUSTERED INDEX IX_ResourceObjectPersons_ResourceContainerId_ResourceObjectId_PersonId ON ResourceObjectPersons (ResourceContainerId, ResourceObjectId, PersonId)
	DROP TABLE ResourceObjectContainers
	DROP VIEW [dbo].[AbResourceObjectContainers_Update]
	DROP VIEW [dbo].[AbResourceObjectContainerParts_Update]
END
GO
IF  EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceObjectPersons') AND [name] = 'ResourceObjectContainerId') 
BEGIN
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceObjectInResourceCostumes_ResourceCostumes') AND type = 'F') ALTER TABLE dbo.ResourceObjectPersons DROP CONSTRAINT FK_ResourceObjectInResourceCostumes_ResourceCostumes
	IF  EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ResourceObjectContainerParts_ResourceObjectContainerId_ResourceObjectId' AND object_id = OBJECT_ID(N'dbo.ResourceObjectPersons')) 
		DROP INDEX IX_ResourceObjectContainerParts_ResourceObjectContainerId_ResourceObjectId ON ResourceObjectPersons
	ALTER TABLE ResourceObjectPersons DROP COLUMN ResourceObjectContainerId

END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceObjectPersons_Persons') AND type = 'F')
BEGIN
	ALTER TABLE ResourceObjectPersons WITH CHECK ADD  CONSTRAINT FK_ResourceObjectPersons_Persons FOREIGN KEY(PersonId)
	REFERENCES Persons (PersonId) ON DELETE SET NULL
	ALTER TABLE ResourceObjectPersons CHECK CONSTRAINT FK_ResourceObjectPersons_Persons
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceObjectPersons_ResourceContainerId') AND type = 'F')
BEGIN
	ALTER TABLE ResourceObjectPersons WITH CHECK ADD  CONSTRAINT FK_ResourceObjectPersons_ResourceContainerId FOREIGN KEY(ResourceContainerId)
	REFERENCES ResourceContainer (ResourceContainerId)
	ALTER TABLE ResourceObjectPersons CHECK CONSTRAINT FK_ResourceObjectPersons_ResourceContainerId
END
GO
ALTER TABLE ResourceObjectPersons ALTER COLUMN PersonId INT NULL
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[AbResourceObjectPersons_Update]') )
	DROP VIEW [AbResourceObjectPersons_Update]
GO

CREATE VIEW [dbo].[AbResourceObjectPersons_Update]
AS 
SELECT  r.* FROM dbo.ResourceObjectPersons AS r
		 WHERE r.ResourceContainerId IN 
		 (	
			SELECT ResourceContainer.ResourceContainerId
			FROM ResourceContainer 
				INNER JOIN [Permissions] AS ug ON ResourceContainer.OwnerGroupId = ug.OwnerGroupId
				INNER JOIN Users AS pw ON ug.UserId = pw.UserId
			WHERE Username = CURRENT_USER
				AND PermissionArea = 16 
				AND HasWritePermission = 1
		)
WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON [AbResourceObjectPersons_Update] TO PUBLIC

GO
--#endregio
--#endregion ---------4.224.x ---------------------------------
--------------------- Ende Änderungsblock --------------------- 
---------------------------------------------------------------
--------------------- RELEASE SECTION -------------------------

--#regio
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Users') AND [name] = 'PasswordHash') 
BEGIN
	ALTER TABLE Users ADD IsTheasoftAuthorizationUser BIT NOT NULL  CONSTRAINT DF_Users_IsTheasoftAuthorizationUser DEFAULT 0
	ALTER TABLE Users ADD PasswordHash CHAR(70)
END
GO

IF  EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].SetPassword')) DROP PROCEDURE dbo.SetPassword 
GO
CREATE PROCEDURE SetPassword
	@userName VARCHAR,
	@newPasswordHash CHAR(70)
AS
BEGIN

	 UPDATE Users 
	 SET PasswordHash = @newPasswordHash 
	 WHERE Users.UserName = @userName AND IsTheasoftAuthorizationUser = 1
   
END
GO
GRANT EXECUTE ON SetPassword TO SecurityAdmin
--#endregio

--#region -------- alter Release Übertrag 4.223.x --------------
--Da es abhängige Views gibt, muss dieser zuerst neue erstellt werden
EXEC sp_refreshview 'AbResourceObjects'

GRANT EXECUtE ON UpdateCastAsDbo TO DataCleaningAdmin
GRANT EXECUtE ON GetContractsASDBO TO DataCleaningAdmin
GRANT SELECT, DELETE, UPDATE, INSERT  ON DutyRoster TO DataCleaningAdmin
GRANT SELECT, DELETE, UPDATE, INSERT  ON resourcefieldtypes TO OrchesterAdmin
GO

ALTER PROCEDURE [dbo].[CloseMonthInternal] 
	@dienstplanartID INT, 
	@month INT,
	@userID int
WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE Departments
	SET LastClosedYearMonth=@month
	WHERE 
		DepartmentId=@dienstplanartID 
		AND DepartmentId IN (
			SELECT da.DepartmentId
			FROM Departments  AS da		
				LEFT JOIN	(
					SELECT * FROM Permissions WHERE PermissionArea = 5 AND OptionsFlag&2 = 2) AS UG ON da.OwnerGroupId = UG.OwnerGroupId
				LEFT JOIN	Users AS up	ON up.UserID= UG.UserID 
			WHERE up.UserID = @userID);
	DELETE
	FROM TimeAccountMonthValues
	WHERE TimeAccountMonthValueId in (
		SELECT TimeAccountMonthValueId FROM TimeAccountMonthValues tamv
		INNER JOIN ContractDepartments cd ON cd.ContractId=tamv.ContractId
		INNER JOIN WorkAreas wd ON cd.WorkAreaId=wd.WorkAreaId
		INNER JOIN Departments d ON d.DepartmentId=wd.DepartmentId
		WHERE 	
			ZmType=1			
			AND d.DepartmentId=@dienstplanartID
			AND d.DepartmentId IN (
			SELECT da.DepartmentId
			FROM Departments  AS da		
				LEFT JOIN	(
					SELECT * FROM Permissions WHERE PermissionArea = 5 AND OptionsFlag&2 = 2) AS UG ON da.OwnerGroupId = UG.OwnerGroupId
				LEFT JOIN	Users AS up	ON up.UserID= UG.UserID 
			WHERE up.UserID = @userID)
			AND tamv.YearMonth>@month)	
END
GO

Update PlanExport Set Exportclass = 'Theasoft XML v11.0RC' where Exportclass like 'Theasoft XML v11.0b'

IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetDutyRosterSickReturns')) DROP PROCEDURE dbo.GetDutyRosterSickReturns 
GO
ALTER PROCEDURE [dbo].[DeleteGroup] 
	@Id INT
AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #Msg(cnt int, tbl varchar(100))

	--ID 0 zu 1 umplanen
	IF (@Id = 0) 
	BEGIN
		UPDATE Persons SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE Productions SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE Contracts SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE Roles SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE Departments SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE Tours SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE ExportProfiles SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE ResourceContainer SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE PlanLayouts SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE AuditionTypes SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE CheckListItem SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE ExportProfiles SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE PersonAdditionalDataTypes SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE ResourceContainer SET OwnerGroupId = 1 WHERE OwnerGroupId = 0
		UPDATE Travels SET OwnerGroupId = 1 WHERE OwnerGroupId = 0

	END
	ELSE
		BEGIN
			
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Contacts'		FROM Persons WHERE  OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Productions'	FROM Productions WHERE  OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Contracts'	FROM Contracts WHERE  OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Roles'		FROM Roles	WHERE  OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Departments'	FROM Departments WHERE  OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Tours'		FROM Tours WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Exports'		FROM ExportProfiles WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'ResourceContainer' FROM ResourceContainer  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'PlanLayouts' FROM PlanLayouts  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'AuditionTypes' FROM AuditionTypes  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'CheckListItem' FROM CheckListItem  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'ExportProfiles' FROM ExportProfiles  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'PersonAdditionalDataTypes' FROM PersonAdditionalDataTypes  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'ProductionTemplates' FROM ProductionTemplates  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'ResourceContainer' FROM ResourceContainer  WHERE OwnerGroupId = @id
		INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Travels' FROM Travels  WHERE OwnerGroupId = @id

	END
	
		IF NOT EXISTS (SELECT * FROM  #Msg WHERE cnt > 0) 
			BEGIN
				UPDATE RoleTypes SET TemplateOwnerGroupId = NULL WHERE TemplateOwnerGroupId = @id
				DELETE FROM [Permissions] WHERE OwnerGroupId = @id
				DELETE FROM OwnerGroupSettings WHERE OwnerGroupId = @id
				DELETE FROM CostCenter_OwnerGroup WHERE OwnerGroupId = @id
				DELETE FROM ActivityTypeGroups WHERE OwnerGroupId = @id
				DELETE FROM OwnerGroups WHERE OwnerGroupId = @id
				DELETE FROM PersonMemo WHERE OwnerGroupId = @id
				DELETE FROM SeasonOwnerGroupSettings WHERE OwnerGroupId = @id
			END
		ELSE 
			BEGIN 
				SELECT * FROM #Msg  WHERE cnt > 0
			END

END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('RoleBonus') AND [name] = 'CalculationOrSalary') 
BEGIN
	ALTER TABLE RoleBonus ADD CalculationOrSalary TINYINT NOT NULL CONSTRAINT DF_RoleBonus_CalculationOrSalarye DEFAULT 0
	ALTER TABLE RoleBonus ADD TimeAccountId INT NULL
	EXEC ('UPDATE RoleBonus SET TimeAccountId = bonus.TimeAccountId FROM RoleBonus INNER JOIN Bonus ON RoleBonus.BonusId = Bonus.BonusId')
	ALTER TABLE RoleBonus ALTER COLUMN TimeAccountId INT NOT NULL
	ALTER TABLE RoleBonus ADD SalaryLevelId INT NULL
	ALTER TABLE RoleBonus ALTER COLUMN BonusId INT NULL
	ALTER TABLE RoleBonus ADD CONSTRAINT FK_RoleBonus_TimeAccounts FOREIGN KEY (TimeAccountId) REFERENCES TimeAccounts (TimeAccountId)
	ALTER TABLE RoleBonus ADD CONSTRAINT FK_RoleBonus_SalaryLevel FOREIGN KEY (SalaryLevelId) REFERENCES SalaryLevel (SalaryLevelId)
	DELETE FROM [Permissions] WHERE PermissionArea = 24
	UPDATE [Permissions] SET OptionsFlag = OptionsFlag + 2 WHERE PermissionArea = 16 AND OptionsFlag < 2 
END

GO

ALTER TABLE ActivityDetails ALTER COLUMN PriceNote VARCHAR(150)
ALTER TABLE ActivityDetails ALTER COLUMN PriceNote_EN VARCHAR(150)
ALTER TABLE PriceCategories ALTER COLUMN [Name] VARCHAR(150)
GO
--#region Permissions Groupsettings
GRANT SELECT ON ABGroupSettings to Public
GRANT SELECT, INSERT, UPDATE, DELETE ON ABGroupSettings_Update to Public
GO
GRANT UPDATE, INSERT, DELETE ON Jobs TO DataService
GO
IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].CopyRole'))	DROP PROCEDURE dbo.CopyRole 

GO
--#endregion Permissions GroupSettings
--#region Permission DeleteUsagesOfUserModified
grant execute on dbo.DeleteUsagesOfUserModified to SecurityAdmin
GO
--#endregion Permission DeleteUsagesOfUserModified

--#endregion ---------4.225.x ---------------------------------

--#region ----------- 4.226.x ---------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.FK_travelSteps_Invoices') AND type = 'F') ALTER TABLE [dbo].[TravelSteps] DROP CONSTRAINT [FK_travelSteps_Invoices]
GO

ALTER TABLE [dbo].[TravelSteps]  WITH CHECK ADD  CONSTRAINT [FK_travelSteps_Invoices] FOREIGN KEY([InvoiceId])
REFERENCES [dbo].[Invoices] ([InvoiceId]) on delete set null
GO

ALTER TABLE [dbo].[TravelSteps] CHECK CONSTRAINT [FK_travelSteps_Invoices]
GO
--#endregion ----------- 4.226.x ------------------------------

--------------------- Ende Änderungsblock --------------------- 


--------------------- RELEASE SECTION -------------------------

--#region ----------- 4.225.x Release Übertrag ----------------
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.AbQualification_Person') )
	DROP VIEW dbo.AbQualification_Person
GO
CREATE VIEW dbo.AbQualification_Person
AS 
SELECT
	qp.*
FROM
	Qualification_Person qp
WHERE 
	qp.PersonId IN
	(
		SELECT  pp.PersonId
		FROM PersonPermissions AS pp
			INNER JOIN [Permissions] AS p ON pp.OwnerGroupId =  p.OwnerGroupId 
			INNER JOIN Users u ON p.Userid = u.Userid 
		WHERE 
			 PermissionArea = 0	AND 
			 Username = CURRENT_USER
	)
GO

--TODO 
GRANT SELECT ON dbo.AbQualification_Person TO PUBLIC
--REVOKE SELECT ON dbo.Qualification_Person TO PUBLIC
GRANT SELECT ON dbo.Qualification_Person TO DataService

--#endregion -------- (4.225.x Release Übertrag) --------------


---------------------------------------------------------------
--#region ----------- Views aktualisieren ---------------------
Print ('Updating Views...')

--Da es abhängige Views gibt, muss dieser zuerst neue erstellt werden
EXEC sp_refreshview 'AbResourceObjects'

DECLARE @SchemaView VARCHAR(100)
DECLARE view_cursor CURSOR FOR
SELECT Name
	FROM 	sysobjects
	WHERE	xtype = 'V'
	ORDER BY Name

OPEN view_cursor
FETCH NEXT FROM view_cursor INTO @SchemaView
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT '	' + @SchemaView
		BEGIN TRY
			EXEC sp_refreshview @SchemaView;
		END TRY
		BEGIN CATCH
			declare @error int, @line INT, @severity INT, @state INT, @procedure NVARCHAR(128), @message nvarchar(4000), @xstate int;
			select @error = ERROR_NUMBER(), @line = ERROR_LINE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE(),
			       @procedure = ERROR_PROCEDURE(), @message = ERROR_MESSAGE(),@xstate = XACT_STATE();
			PRINT('		Refreshing view failed...')
			PRINT('		ERROR #' + CONVERT(VARCHAR, @error) +' Severity '+ CONVERT(VARCHAR, @severity) + ' State ' + CONVERT(VARCHAR, @state))
			PRINT('		XACT_STATE ' + CONVERT(VARCHAR, @xstate))
			PRINT('		Procedure ' + @procedure + ' Line ' + CONVERT(VARCHAR, @line))
			PRINT('		Message ' + @message)

			-- wenn es noch offene transaktionen aus sp_refreshview gibt,
			-- diese rückgängig machen,
			-- da sonst folgefehler bei der nächsten view kommen
			-- tritt bei tests auf sql 2008 auf
			IF @xstate = -1 BEGIN
				PRINT('		Rolling back open transaction...')
				ROLLBACK;
			END

		END CATCH
		FETCH NEXT FROM view_cursor INTO @SchemaView
	END
CLOSE view_cursor
DEALLOCATE view_cursor
GO

--#endregion ----------- Views aktualisieren ---------------------
--#region ----------- UpdateLog verkleineren ------------------

GO
PRINT ('Execute TruncateUpdateLog...')
GO
DECLARE @from DATE
SELECT @from = GETDATE() - 1095
EXECUTE TruncateUpdateLog @from

GO
--#endregion 

--------------------------------------------------------------
--#region ----------- Wegräumen der Hilfsprozeduren -----------
Print ('Clean up...')
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].DropColumn'))					DROP PROCEDURE dbo.DropColumn 
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].DropRoleWithDropRoleMember'))	DROP PROCEDURE dbo.DropRoleWithDropRoleMember 
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].PrepareUniqueColumnIndex'))	DROP PROCEDURE dbo.PrepareUniqueColumnIndex
IF  EXISTS (SELECT * FROM  sys.objects o WHERE o.object_id = OBJECT_ID(N'[dbo].ConvertColumnToXml'))		DROP PROCEDURE dbo.ConvertColumnToXml 
--#endregion ----------- Wegräumen der Hilfsprozeduren -----------
--#region ----------- Zum Aufräumen vorgemerkt -----------
--IF EXISTS (SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID(N'dbo.AbPlanPermissionSettings') AND type <> 'U') DROP VIEW dbo.AbPlanPermissionSettings
--#endregion ----------- Zum Aufräumen vorgemerktn -----------
---------------------------------------------------------------
--#region ----------- VERSIONIERUNG ---------------------------
--#region ----------- Version aus ts_evt in TS_DataClass ------
DECLARE @major INT
DECLARE @minor INT
DECLARE @build INT
DECLARE @Datum DATE
----------
SET @major = 4
SET @minor = 225
SET @build = 3
SET @Datum = '20221208'
--#endregion ----------- Version aus ts_evt in TS_DataClass ------
--#region ----------- Version setzen ---------------------------
PRINT('Updating NvData...')
IF NOT EXISTS (SELECT * FROM  NvData WHERE ByModulScript = 1)
BEGIN
	INSERT INTO NvData (VersionDate, ByModulScript)
	VALUES				('19000101', 1)
END

--Ausführungen des Datenbankskripts werden geloggt.
--Diese haben für ByModulScript den Wert 2
INSERT INTO NVData
		  (VersionDate, ByModulScript, Major, Minor, LastDatabaseUpdateDate, Build) 
	SELECT VersionDate, 2,             Major, Minor, LastDataBaseUpdateDate, Build 
	FROM NVData 
	WHERE ByModulScript = 1

--Version setzen.

--Diese hat für ByModulScript den Wert 1.
UPDATE NvData
SET Major = @major, Minor = @minor, Build = @build, VersionDate = @Datum, LastDatabaseUpdateDate = GetDate()
WHERE ByModulScript = 1

PRINT	('		Script version ' + CAST(@major AS VARCHAR(4)) + '.' + CAST(@minor AS VARCHAR(4)) + '.' + CAST(@build AS VARCHAR(4)) + ' finished. (' + CAST(@Datum as  VARCHAR(100))  +')')
GO

--#endregion  ----------- Version setzen ---------------------------
--#endregion ----------- VERSIONIERUNG ---------------------------
--#region ----------- NOEXEC OFF ------------------------------
SET NOEXEC OFF
--#endregion ----------- NOEXEC OFF ------------------------------
