/*
	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 drei 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
	FROM sys.sysprocesses INNER	JOIN master.dbo.sysdatabases ON sysprocesses.DBID = sysdatabases.DBID
		LEFT JOIN Users on LOGINAME = 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 196 (letzte Version 2019)
IF EXISTS (SELECT * FROM NvData WHERE  ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 196 OR (Minor=196 AND Build < 105)))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Der Versionsunterschied ist zu groß!!!'	
																																															Print '		Bitte führen Sie DbScript_2019.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 211
IF EXISTS (SELECT * FROM NvData WHERE  ByModulScript = 1 AND (Major > 4 OR (Major = 4 AND (Minor >= 211))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Veraltete Skriptversion!!!'	
																																															Print '		Bitte führen Sie DbScript_2021.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 DropIndexByTable
IF  EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].DropIndexByTable'))
BEGIN
	DROP PROCEDURE dbo.DropIndexByTable
END
go
CREATE PROCEDURE dbo.DropIndexByTable 
	@tablename varchar(100)
AS
BEGIN

DECLARE @ConstraintName VARCHAR(100) 
DECLARE cview_cursor CURSOR FOR 
	SELECT so.name FROM sysobjects so JOIN sysconstraints sc
		ON so.id = sc.constid  
		WHERE object_name(so.parent_obj) = @tablename
		AND so.xtype = 'UQ'

OPEN cview_cursor 
	FETCH NEXT FROM cview_cursor 
	INTO @ConstraintName 
	WHILE @@FETCH_STATUS = 0 
	BEGIN  
		Print 'Dropping Constraint ' + @ConstraintName + '...'
		declare @ccmd varchar(100)
	
		select @ccmd='ALTER TABLE '+@tablename+' DROP CONSTRAINT '+@ConstraintName
		exec (@ccmd)

		FETCH NEXT FROM cview_cursor 
		INTO @ConstraintName 
	END 
CLOSE cview_cursor 
DEALLOCATE cview_cursor 


DECLARE @IndexName VARCHAR(100) 
DECLARE view_cursor CURSOR FOR
		SELECT  i.name AS IndexName 
		FROM sysindexes AS i 
		WHERE OBJECT_NAME( i.id ) = @tablename AND NOT ([Status]&0x800)=0x800 AND  not i.name LIKE '_WA_Sys%' AND impid = 0
	
OPEN view_cursor 
	FETCH NEXT FROM view_cursor 
	INTO @IndexName 
	WHILE @@FETCH_STATUS = 0 
	BEGIN  
		Print @IndexName
		declare @cmd varchar(100)
		select @cmd='DROP INDEX ' +@tablename+ '.'+ @IndexName
		exec (@cmd)

		FETCH NEXT FROM view_cursor 
		INTO @IndexName 
	END 
CLOSE view_cursor 
DEALLOCATE view_cursor 


END
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 DropDefaultConstraint
IF EXISTS (SELECT * FROM  sys.objects o WHERE  o.object_id = OBJECT_ID(N'DropDefaultConstraint'))
	DROP PROCEDURE dbo.DropDefaultConstraint 
GO
CREATE PROCEDURE dbo.DropDefaultConstraint
	@tablename varchar(100),
	@columnname varchar(100)
AS
BEGIN
	DECLARE @defname VARCHAR(100)
	DECLARE @cmd VARCHAR(200)

	SELECT @defname = Name
	FROM sys.default_constraints dc
	WHERE	OBJECT_NAME(parent_object_id) = @tablename
	AND		COL_NAME(parent_object_id, parent_column_id) = @columnname
	
	IF (NOT @defname IS NULL)
	BEGIN
			SELECT @cmd='ALTER TABLE '+ @tablename + ' DROP CONSTRAINT ' + @defname
			EXEC (@cmd)
			PRINT @defname + ' dropped'
	END

	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( @tablename) AND C.NAME =  @columnname)
	BEGIN
		SELECT @cmd ='sp_unbindefault ''' +  @tablename + '.' + @columnname + ''''
		EXEC (@cmd)
		PRINT @columnname + ' unbound'
	END
END
GO
--#endregion

--#region DropColumn
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
		--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)
			
		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


--#endregion --------------------- Ende HilfsProzeduren --------------------

--------------------- Begin Änderungsblock --------------------
--------------------- Scripterstelldatum : 19.10.2018 ---------
--------------------- Datenbankänderungen ab hier -------------
--------------------- möglichst mit regions umschließen -------

--#region ----------- 4.201.x ---------------------------------

--#region 2019-12-13 Index

GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'PK_PersonFeeNotes' AND object_id = OBJECT_ID(N'dbo.PersonFeeNotes') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create PK_PersonFeeNotes'
	EXEC DropIndexByTable 'PersonFeeNotes'
	ALTER TABLE dbo.PersonFeeNotes DROP CONSTRAINT PK_PersonFeeNotes
	ALTER TABLE dbo.PersonFeeNotes ADD CONSTRAINT PK_PersonFeeNotes PRIMARY KEY CLUSTERED (PersonId) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_PlanLayouts_PlanLayoutType' AND object_id = OBJECT_ID(N'dbo.PlanLayouts') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create IX_PlanLayouts_PlanLayoutType'
	IF EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.PlanLayouts') AND o.Name = 'IX_PlanLayouts_PlanLayoutType') DROP INDEX PlanLayouts.IX_PlanLayouts_PlanLayoutType
	CREATE CLUSTERED INDEX IX_PlanLayouts_PlanLayoutType ON dbo.PlanLayouts (PlanLayoutType)
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ProductionInSeasons_SeasonId_ProductionId' AND object_id = OBJECT_ID(N'dbo.ProductionInSeasons') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create IX_ProductionInSeasons_SeasonId_ProductionId'
	IF EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.ProductionInSeasons') AND o.Name = 'IX_StückeSPZID') DROP INDEX ProductionInSeasons.IX_StückeSPZID
	CREATE CLUSTERED INDEX IX_ProductionInSeasons_SeasonId_ProductionId ON dbo.ProductionInSeasons (SeasonId, ProductionId)
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_CashCommands_Locations_MainStageId') AND type = 'F') ALTER TABLE dbo.CashCommands DROP CONSTRAINT FK_CashCommands_Locations_MainStageId
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE  c.name = 'MainStageId' AND o.id = OBJECT_ID(N'dbo.CashCommands')) ALTER TABLE CashCommands DROP COLUMN MainStageId
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'PK_Locations' AND object_id = OBJECT_ID(N'dbo.Locations') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create PK_Locations'

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_CountDowns_Locations') AND type = 'F') ALTER TABLE dbo.CountDowns DROP CONSTRAINT FK_CountDowns_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_SeatingPlans_Locations') AND type = 'F') ALTER TABLE dbo.SeatingPlans DROP CONSTRAINT FK_SeatingPlans_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_LocationAttachments_Locations') AND type = 'F') ALTER TABLE dbo.LocationAttachments DROP CONSTRAINT FK_LocationAttachments_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Resources_Locations') AND type = 'F') ALTER TABLE dbo.Resources DROP CONSTRAINT FK_Resources_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ResourceContainer_Locations') AND type = 'F') ALTER TABLE dbo.ResourceContainer DROP CONSTRAINT FK_ResourceContainer_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_PlanZR_Locations') AND type = 'F') ALTER TABLE dbo.PlanZR DROP CONSTRAINT FK_PlanZR_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_CashCommands_Locations_MainStageId') AND type = 'F') ALTER TABLE dbo.CashCommands DROP CONSTRAINT FK_CashCommands_Locations_MainStageId

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_TravelSteps_Locations_ApartmentId') AND type = 'F') ALTER TABLE dbo.TravelSteps DROP CONSTRAINT FK_TravelSteps_Locations_ApartmentId
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Activities_Locations') AND type = 'F') ALTER TABLE dbo.Activities DROP CONSTRAINT FK_Activities_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_StageDetails_Locations') AND type = 'F') ALTER TABLE dbo.StageDetails DROP CONSTRAINT FK_StageDetails_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_LocationRates_Locations') AND type = 'F') ALTER TABLE dbo.LocationRates DROP CONSTRAINT FK_LocationRates_Locations
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_HDMZr_Locations') AND type = 'F') ALTER TABLE dbo.HDMZr DROP CONSTRAINT FK_HDMZr_Locations

	ALTER TABLE dbo.Locations DROP CONSTRAINT PK_Locations
	ALTER TABLE dbo.Locations ADD CONSTRAINT PK_Locations PRIMARY KEY CLUSTERED (LocationId) ON [PRIMARY]

	ALTER TABLE dbo.CountDowns ADD CONSTRAINT FK_CountDowns_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId) ON  DELETE SET NULL 
	ALTER TABLE dbo.SeatingPlans ADD CONSTRAINT FK_SeatingPlans_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId)
	ALTER TABLE dbo.LocationAttachments ADD CONSTRAINT FK_LocationAttachments_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId)
	ALTER TABLE dbo.Resources ADD CONSTRAINT FK_Resources_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId)
	ALTER TABLE dbo.ResourceContainer ADD CONSTRAINT FK_ResourceContainer_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId)
	ALTER TABLE dbo.TravelSteps ADD CONSTRAINT FK_TravelSteps_Locations_ApartmentId FOREIGN KEY (ApartmentId) REFERENCES dbo.Locations (LocationId)
	ALTER TABLE dbo.Activities ADD CONSTRAINT FK_Activities_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId)
	ALTER TABLE dbo.StageDetails ADD CONSTRAINT FK_StageDetails_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId) ON DELETE CASCADE
	ALTER TABLE dbo.LocationRates ADD CONSTRAINT FK_LocationRates_Locations FOREIGN KEY (LocationId) REFERENCES dbo.Locations (LocationId) ON DELETE CASCADE
END

GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'PK_Templates' AND object_id = OBJECT_ID(N'dbo.Templates') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create PK_Templates'

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ContractTypes_Templates_Templates') AND type = 'F') ALTER TABLE dbo.ContractTypes_Templates DROP CONSTRAINT FK_ContractTypes_Templates_Templates
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Contracts_Templates') AND type = 'F') ALTER TABLE dbo.Contracts DROP CONSTRAINT FK_Contracts_Templates
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_OrderValidationTypes_Templates') AND type = 'F') ALTER TABLE dbo.OrderValidationTypes DROP CONSTRAINT FK_OrderValidationTypes_Templates

	ALTER TABLE dbo.Templates DROP CONSTRAINT PK_Templates
	ALTER TABLE dbo.Templates ADD CONSTRAINT PK_Templates PRIMARY KEY CLUSTERED (TemplateId) ON [PRIMARY]
	ALTER TABLE dbo.ContractTypes_Templates ADD CONSTRAINT FK_ContractTypes_Templates_Templates FOREIGN KEY (TemplateId) REFERENCES dbo.Templates (TemplateId) ON DELETE CASCADE
	ALTER TABLE dbo.Contracts ADD CONSTRAINT FK_Contracts_Templates FOREIGN KEY (TemplateId) REFERENCES dbo.Templates (TemplateId) ON DELETE SET NULL
	ALTER TABLE dbo.OrderValidationTypes ADD CONSTRAINT FK_OrderValidationTypes_Templates FOREIGN KEY (TemplateId) REFERENCES dbo.Templates (TemplateId) ON DELETE CASCADE
	UPDATE Templates SET Name = CAST([TemplateId] AS VARCHAR(MAX)) WHERE  Name IS NULL
	ALTER TABLE Templates ALTER COLUMN Name VARCHAR(50) NOT NULL

	UPDATE Templates SET Filename = '--' WHERE  Filename IS NULL
	ALTER TABLE Templates ALTER COLUMN Filename VARCHAR(256) NOT NULL
END
GO
--SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
--ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
--indexstats.avg_fragmentation_in_percent 
--FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
--INNER JOIN sys.indexes ind  
--ON ind.object_id = indexstats.object_id 
--AND ind.index_id = indexstats.index_id 
--WHERE indexstats.avg_fragmentation_in_percent > 30 
--ORDER BY indexstats.avg_fragmentation_in_percent DESC

--#endregion index

--#region 2019-12-13 PeriodCheck
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_PersonStatus_PeriodCheck') 
BEGIN 
	ALTER TABLE PersonStatus WITH CHECK ADD CONSTRAINT CK_PersonStatus_PeriodCheck 
		CHECK ( CASE WHEN StartTime IS NULL THEN StartDate ELSE CAST(StartDate AS DATETIME) + CAST(StartTime AS DATETIME) END
			<=  CASE WHEN EndTime   IS NULL THEN DATEADD (DAY, 1, CAST(EndDate AS DATETIME)) ELSE CAST(EndDate AS DATETIME) + CAST(EndTime AS DATETIME) END)
	ALTER TABLE PersonStatus CHECK CONSTRAINT CK_PersonStatus_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_CostUnit_PeriodCheck') 
BEGIN 
	Print 'Add CK_CostUnit_PeriodCheck'
	ALTER TABLE CostUnits ALTER COLUMN BookableFrom DATE NULL
	ALTER TABLE CostUnits ALTER COLUMN BookableTo DATE NULL
	UPDATE CostUnits SET BookableTo = NULL  WHERE  BookableFrom > BookableTo
	ALTER TABLE CostUnits WITH CHECK ADD CONSTRAINT CK_CostUnit_PeriodCheck CHECK (BookableFrom <= BookableTo)
	ALTER TABLE CostUnits CHECK CONSTRAINT CK_CostUnit_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_CostCenters_PeriodCheck') 
BEGIN 
	Print 'Add CK_CostCenters_PeriodCheck'
	ALTER TABLE CostCenters ALTER COLUMN BookableFrom DATE NULL
	ALTER TABLE CostCenters ALTER COLUMN BookableTo DATE NULL
	UPDATE CostCenters SET BookableTo = NULL  WHERE  BookableFrom > BookableTo
	ALTER TABLE CostCenters WITH CHECK ADD CONSTRAINT CK_CostCenters_PeriodCheck CHECK (BookableFrom <= BookableTo)
	ALTER TABLE CostCenters CHECK CONSTRAINT CK_CostCenters_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_BudgetItems_PeriodCheck') 
BEGIN 
	Print 'Add CK_BudgetItems_PeriodCheck'
	ALTER TABLE BudgetItems ALTER COLUMN BookableFrom DATE NULL
	ALTER TABLE BudgetItems ALTER COLUMN BookableTo DATE NULL
	UPDATE BudgetItems SET BookableTo = NULL  WHERE  BookableFrom > BookableTo
	ALTER TABLE BudgetItems WITH CHECK ADD CONSTRAINT CK_BudgetItems_PeriodCheck CHECK (BookableFrom <= BookableTo)
	ALTER TABLE BudgetItems CHECK CONSTRAINT CK_BudgetItems_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_CostTypes_PeriodCheck') 
BEGIN 
	Print 'Add CK_CostTypes_PeriodCheck'
	ALTER TABLE CostTypes ALTER COLUMN BookableFrom DATE NULL
	ALTER TABLE CostTypes ALTER COLUMN BookableTo DATE NULL
	UPDATE CostTypes SET BookableTo = NULL  WHERE  BookableFrom > BookableTo
	ALTER TABLE CostTypes WITH CHECK ADD CONSTRAINT CK_CostTypes_PeriodCheck CHECK (BookableFrom <= BookableTo)
	ALTER TABLE CostTypes CHECK CONSTRAINT CK_CostTypes_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_Taxes_PeriodCheck') 
BEGIN 
	Print 'Add CK_Taxes_PeriodCheck'
	ALTER TABLE Taxes ALTER COLUMN ValidFrom DATE NULL
	ALTER TABLE Taxes ALTER COLUMN ValidTo DATE NULL
	UPDATE Taxes SET ValidTo = NULL  WHERE  ValidFrom > ValidTo
	ALTER TABLE Taxes WITH CHECK ADD CONSTRAINT CK_Taxes_PeriodCheck CHECK (ValidFrom <= ValidTo)
	ALTER TABLE Taxes CHECK CONSTRAINT CK_Taxes_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_Tours_PeriodCheck') 
BEGIN 
	Print 'Add CK_Tours_PeriodCheck'
	ALTER TABLE Tours ALTER COLUMN StartTime TIME NULL
	ALTER TABLE Tours ALTER COLUMN EndTime TIME NULL

	UPDATE tours SET [end] = Start WHERE  Start > [end] 
	UPDATE tours set EndTime = NULL  WHERE  Start  = [end] AND NOT StartTime  IS NULL AND NOT EndTime IS NULL AND CAST(Start AS DATETIME) + CAST(StartTime AS DATETIME) > CAST([End] AS DATETIME) + CAST(EndTime AS DATETIME) 

	ALTER TABLE Tours WITH CHECK ADD CONSTRAINT CK_Tours_PeriodCheck 
	CHECK ( CASE WHEN StartTime IS NULL THEN Start ELSE CAST(Start AS DATETIME) + CAST(StartTime AS DATETIME) END
			<=  CASE WHEN EndTime IS NULL THEN DATEADD (DAY, 1, CAST([End] AS DATETIME)) ELSE CAST([End] AS DATETIME) + CAST(EndTime AS DATETIME) END)
	ALTER TABLE Tours CHECK CONSTRAINT CK_Tours_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_Seasons_PeriodCheck') 
BEGIN 
	Print 'Add CK_Seasons_PeriodCheck'
	ALTER TABLE Seasons WITH CHECK ADD CONSTRAINT CK_Seasons_PeriodCheck CHECK (StartDate <= EndDate)
	ALTER TABLE Seasons CHECK CONSTRAINT CK_Seasons_PeriodCheck
END 
GO
IF EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_BudgetPeriods_Period') ALTER TABLE BudgetPeriods DROP CONSTRAINT CK_BudgetPeriods_Period
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_BudgetPeriods_PeriodCheck') 
BEGIN 
	Print 'Add CK_BudgetPeriods_PeriodCheck'
	ALTER TABLE BudgetPeriods ALTER COLUMN StartDate DATE NOT NULL
	ALTER TABLE BudgetPeriods ALTER COLUMN EndDate DATE NOT NULL
	ALTER TABLE BudgetPeriods WITH CHECK ADD CONSTRAINT CK_BudgetPeriods_PeriodCheck CHECK (StartDate <= EndDate)
	ALTER TABLE BudgetPeriods CHECK CONSTRAINT CK_BudgetPeriods_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_Contracts_PeriodCheck') 
BEGIN 
	Print 'Add CK_Contracts_PeriodCheck'
	UPDATE Contracts SET EndDate = NULL  WHERE  StartDate > EndDate
	ALTER TABLE Contracts WITH CHECK ADD CONSTRAINT CK_Contracts_PeriodCheck CHECK (StartDate <= EndDate)
	ALTER TABLE Contracts CHECK CONSTRAINT CK_Contracts_PeriodCheck
END 
UPDATE Contracts SET StartDate = NULL WHERE  StartDate = '19000101'
UPDATE Contracts SET EndDate = NULL WHERE  EndDate = '30000101'
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_ContractEngagements_PeriodCheck') 
BEGIN 
	Print 'Add CK_ContractEngagements_PeriodCheck'
	UPDATE ContractEngagements SET EndDate = NULL  WHERE  StartDate > EndDate
	ALTER TABLE ContractEngagements WITH CHECK ADD CONSTRAINT CK_ContractEngagements_PeriodCheck CHECK (StartDate <= EndDate)
	ALTER TABLE ContractEngagements CHECK CONSTRAINT CK_ContractEngagements_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_ContractSalaryRules_PeriodCheck') 
BEGIN 
	Print 'Add CK_ContractSalaryRules_PeriodCheck'
	UPDATE ContractSalaryRules SET EndDate = NULL  WHERE  StartDate > EndDate
	ALTER TABLE ContractSalaryRules WITH CHECK ADD CONSTRAINT CK_ContractSalaryRules_PeriodCheck CHECK (StartDate <= EndDate)
	ALTER TABLE ContractSalaryRules CHECK CONSTRAINT CK_ContractSalaryRules_PeriodCheck
END 
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_SalaryLevelData_PeriodCheck') 
BEGIN 
	Print 'Add CK_SalaryLevelData_PeriodCheck'
	ALTER TABLE SalaryLevelData ALTER COLUMN ValidFrom DATE NULL
	ALTER TABLE SalaryLevelData ALTER COLUMN ValidTo DATE NULL
	UPDATE SalaryLevelData SET ValidTo = NULL  WHERE  ValidFrom > ValidTo
	ALTER TABLE SalaryLevelData WITH CHECK ADD CONSTRAINT CK_SalaryLevelData_PeriodCheck CHECK (ValidFrom <= ValidTo)
	ALTER TABLE SalaryLevelData CHECK CONSTRAINT CK_SalaryLevelData_PeriodCheck
END 
--#endregion

--#region 2019-12-13 Löschen alter Felder

DELETE FROM ContractEngagements WHERE  ContractEngagementid IN 
			(	SELECT  vr.ContractEngagementid 
				FROM ContractEngagements AS VR 
					INNER JOIN contracts AS v ON vr.contractid = v.contractid 
					INNER JOIN ownerGroups AS G ON v.ownerGroupid = g.ownerGroupid 
				WHERE g.Modul = 4 AND NOT v.CostUnitId IS NULL)
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.AbTechnicalPersonalRequirements') AND type <> 'U') DROP VIEW dbo.AbTechnicalPersonalRequirements
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.PresseText') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE PresseText
GO

ALTER PROCEDURE [dbo].[DeleteProduction]
	@ProductionId INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @UserGroup int
SELECT @UserGroup = OwnerGroupId FROM Productions WHERE  ProductionId = @ProductionId

--Berechtigungsprüfung
IF (dbo.GetCurrentPermission(@UserGroup, 3) < 2 ) 
	BEGIN
		SELECT 1 AS Attribut, 'No Permissions' AS MSG
	END
ELSE
	BEGIN 
		IF (@ProductionId = 0)	
		BEGIN
			SELECT 0 AS Attribut,'Keine gültige Produktion übergeben (Id 0)' AS MSG
		END
		ELSE
		BEGIN
			DELETE FROM BasicCast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM ContractEngagements WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM CastRepertoire WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM ContractDateConfirmations WHERE  ContractDateId IN (SELECT ContractDateId FROM ContractDates 
				WHERE CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)))
			DELETE FROM ContractDates WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastBonus WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CashCommandPos_Cast WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastCredits WHERE  ServiceCastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastCredits WHERE  ReplacementCastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			UPDATE ContractDates SET CastId = NULL WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM TechnicalJobs WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM RoleBonus WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Role_Season WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Roles_Scenes WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Qualification_Role WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Roles WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)	
			DELETE FROM ProductionChilds WHERE  ProductionId = @ProductionId
			DELETE FROM ProductionChilds WHERE  ChildProductionId = @ProductionId
			DELETE FROM ActivityScenes WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDepartmentData WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM TechnicalJobs WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityCosts WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDetails WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM JourneyTimetables WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM CostUnitWorkingTimes WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDetails WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivitySubscriptions WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM Activities WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			UPDATE Activities SET LinkedActivityId = NULL WHERE  LinkedActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
		END
	END
END
GO
ALTER PROCEDURE [dbo].[DeleteActivityRelations]
	@ActivityId INT
AS
BEGIN
SET NOCOUNT ON
	IF NOT EXISTS (SELECT * FROM ActivitiesUpdate WHERE  ActivityId= @ActivityId)
		BEGIN
			SELECT 1 AS Attribut, 'No Permission on Activity '+ CAST(@ActivityId AS VARCHAR(100)) AS MSG
		END
		ELSE
		BEGIN	
			DELETE FROM CastCredits WHERE  ServiceCastId IN (SELECT CastId FROM Cast WHERE  ActivityId = @activityId)
			DELETE FROM CastCredits WHERE  ReplacementCastId IN (SELECT CastId FROM Cast WHERE  ActivityId = @activityId)
			DELETE FROM CastBonus WHERE  CastBonus.CastId IN (SELECT CastId FROM Cast WHERE  ActivityId = @activityId)
			DELETE FROM CashCommandPos_Cast WHERE  CastId IN (SELECT CastId FROM Cast WHERE  ActivityId = @activityId)
			UPDATE ContractDates SET CastId = NULL WHERE  CastId IN (SELECT CastId FROM Cast WHERE  ActivityId = @activityId)
			DELETE FROM Cast WHERE  ActivityId = @activityId
			DELETE FROM ActivityScenes WHERE  ActivityId = @activityId
			DELETE FROM ActivityDepartmentData WHERE  ActivityId = @activityId
			DELETE FROM TechnicalJobs WHERE  ActivityId = @activityId
			DELETE FROM ActivityCosts WHERE  ActivityId = @activityId
			DELETE FROM ActivityDetails WHERE  ActivityId = @activityId
			DELETE FROM JourneyTimetables WHERE  ActivityId = @ActivityId
			DELETE FROM CostUnitWorkingTimes WHERE  ActivityId = @activityId
			DELETE FROM ActivityScenes WHERE  ActivityId = @activityId
			UPDATE Activities SET LinkedActivityId = NULL WHERE  LinkedActivityId = @ActivityId
			DELETE FROM Activities_ResourceObjects WHERE  ActivityId = @ActivityId
		END	
END	
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE  c.name = 'T10' AND o.id = OBJECT_ID(N'dbo.ProductionDetails'))
BEGIN
	Print 'Drop  ProductionDetails Filds'
	ALTER TABLE ProductionDetails DROP COLUMN T10
	ALTER TABLE ProductionDetails DROP COLUMN T20
	ALTER TABLE ProductionDetails DROP COLUMN T30
	ALTER TABLE ProductionDetails DROP COLUMN T40
	ALTER TABLE ProductionDetails DROP COLUMN F10
	ALTER TABLE ProductionDetails DROP COLUMN F20
	ALTER TABLE ProductionDetails DROP COLUMN F30
	ALTER TABLE ProductionDetails DROP COLUMN F40
	ALTER TABLE ProductionDetails DROP COLUMN L10
	ALTER TABLE ProductionDetails DROP COLUMN L20
	ALTER TABLE ProductionDetails DROP COLUMN L30
	ALTER TABLE ProductionDetails DROP COLUMN L40
	ALTER TABLE ProductionDetails DROP COLUMN L50
	ALTER TABLE ProductionDetails DROP COLUMN L60
	ALTER TABLE ProductionDetails DROP COLUMN LRechts
	ALTER TABLE ProductionDetails DROP COLUMN KRechts
	ALTER TABLE ProductionDetails DROP COLUMN K10
	ALTER TABLE ProductionDetails DROP COLUMN K20
	ALTER TABLE ProductionDetails DROP COLUMN K30
	ALTER TABLE ProductionDetails DROP COLUMN K40
	ALTER TABLE ProductionDetails DROP COLUMN InternetLink
	ALTER TABLE ProductionDetails DROP COLUMN Memo
	ALTER TABLE ProductionDetails DROP COLUMN Memo_en
END
GO
EXEC DropColumn 'ContractDetails' ,'HasPriority'
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE  c.name = 'Memo1' AND o.id = OBJECT_ID(N'dbo.ContractDetails'))
BEGIN
	Print 'Drop  ContractDetails Filds'
	ALTER TABLE ContractDetails DROP COLUMN Memo1
	ALTER TABLE ContractDetails DROP COLUMN Memo2
	ALTER TABLE ContractDetails DROP COLUMN Memo3
	ALTER TABLE ContractDetails DROP COLUMN Memo4
	ALTER TABLE ContractDetails DROP COLUMN Memo5
	ALTER TABLE ContractDetails DROP COLUMN Memo6
	ALTER TABLE ContractDetails DROP COLUMN Optionen
	ALTER TABLE ContractDetails DROP COLUMN UnterkunftVs
	ALTER TABLE ContractDetails DROP COLUMN Reisekosten
END
GO
--#endregion löschen 

--#region 2019-12-16 Role_Season
PRINT('Alter AbRole_Season_Update')
GO
ALTER VIEW dbo.AbRole_Season_Update
	AS
	SELECT      S.*
	FROM        Role_Season AS S 
	WHERE S.RoleId IN (
		SELECT RoleId FROM			Roles AS R
					INNER JOIN  Permissions AS Ug	ON r.OwnerGroupId = Ug.OwnerGroupId   
					INNER JOIN  Users				ON Ug.Userid  = Users.Userid 
					WHERE (ug.PermissionArea = 4 OR (ug.PermissionArea = 1 AND ug.OptionsFlag & 1 = 1)) AND ug.HasWritePermission = 1 AND Users.Username = CURRENT_USER)
	WITH CHECK OPTION

GO
--#endregion

--#region 2019-12-18
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('WorkAreaGroups') AND [name] = 'FontFormat')
BEGIN
	PRINT('Adding column WorkAreaGroups.FontFormat ...')
	ALTER TABLE dbo.WorkAreaGroups ADD FontFormat XML NULL
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ContractDates_ContractEngagementId' AND object_id = OBJECT_ID(N'dbo.ContractDates')) 
BEGIN
	Print 'Create IX_ContractDates_ContractEngagementId'
	CREATE INDEX IX_ContractDates_ContractEngagementId ON dbo.ContractDates (ContractEngagementId)
END
GO
--#endregion

--#region 2020-01-03 ContractTrackingStatus für Perso
PRINT('Alter ABContractTrackingStatus_Helper')
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.ABContractTrackingStatus_Helper'))  DROP VIEW dbo.ABContractTrackingStatus_Helper
 GO
CREATE VIEW ABContractTrackingStatus_Helper
AS
SELECT cc.ContractId,
cc.documenttypeid,
ROW_NUMBER() OVER (PARTITION BY cc.contractid,cc.[DocumentTypeID] ORDER BY ct.trackingdate DESC,ccs.sort DESC ) AS TrackingRank,
ct.ContractTrackingStatusId
,ct.trackingdate FROM ContractConfirmations cc 
INNER JOIN ContractTracking ct ON cc.ContractConfirmationId=ct.ContractConfirmationId 
INNER JOIN ContractDateStatus ccs ON ct.ContractTrackingStatusId=ccs.CDStatusId 

GO
GRANT SELECT ON ABContractTrackingStatus_Helper to public
GO
--#endregion 2020-01-03 ContractTrackingStatus für Perso

--#region 2020-01-08
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('RoleCategories') AND [name] = 'FeeFactor')
BEGIN
	PRINT('Adding column RoleCategories.FeeFactor ...')
	ALTER TABLE dbo.RoleCategories ADD FeeFactor FLOAT NULL
	--#region 2020-01-07 Pausen / bugfix (Kopie für devdb aus 2019)
	UPDATE persoparameter SET BreakRules = REPLACE(CAST(BreakRules AS VARCHAR(MAX)),'<NotForDoubleShifts>false</NotForDoubleShifts>','') WHERE  NOT BreakRules IS NULL
	UPDATE persoparameter SET BreakRules = REPLACE(CAST(BreakRules AS VARCHAR(MAX)),'<NotForDoubleShifts>true</NotForDoubleShifts>','') WHERE  NOT BreakRules IS NULL

	UPDATE FormulaConfigurations SET 
		FormulaId = 'BalancePeriodCastFormula',
		SerializedParameter = REPLACE(CAST(SerializedParameter AS VARCHAR(MAX)),'BalancePeroidCastFormula','BalancePeriodCastFormula')
	WHERE FormulaId = 'BalancePeroidCastFormula'

--#endregion

END
GO
DropColumn 'ContractDetails','ForeignerTax'
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Contracts') AND [name] = 'AllocationGroupId')
BEGIN
	PRINT('Adding column Contracts.AllocationGroupId ...')
	ALTER TABLE dbo.Contracts ADD AllocationGroupId INT NULL
	ALTER TABLE dbo.Contracts ADD CONSTRAINT FK_Contracts_AllocationGroups FOREIGN KEY (AllocationGroupId) REFERENCES dbo.AllocationGroup (AllocationGroupId)
END
--#endregion
	
--#region 2020-01-08 StatisterieAdmin
if NOT EXISTS (SELECT * FROM dbo.sysusers WHERE  name = 'StatisterieAdmin')
BEGIN
	PRINT('Add StatisterieAdmin...')
	EXEC sp_addrole @rolename = N'StatisterieAdmin'
	GRANT INSERT, UPDATE, DELETE ON Bonus TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON BonusDefaultAmounts TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON BonusInDepartments TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractType_Department	TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_ContractFieldTypes TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_Templates TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON DepartmentCollectives TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON FormulaConfigurations TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON FormulaConfigurations_Departments TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON MailingList TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON PersoParameter TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON ProductionBonus TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON Qualification TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON QualificationCategory TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON Rapport TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON RapportColumns TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON RoleTypes TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevel TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevelData TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON SeasonOwnerGroupSettings TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON Templates TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON TimeAccounts TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON WorkAreaGroupMembers TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON WorkAreaGroups TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON WorkAreas TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON DienstplanArten_Update TO StatisterieAdmin
	GRANT INSERT, UPDATE, DELETE ON RoleCategories TO StatisterieAdmin

	GRANT EXECUTE ON DeleteTimeAccount TO StatisterieAdmin
END
GO
GRANT INSERT, UPDATE, DELETE ON Templates TO StatisterieAdmin
GO
--#endregion

--#region === ProductionTasks ###
IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='ProductionTemplates')
BEGIN

	ALTER TABLE dbo.ProductionInSeasons DROP CONSTRAINT PK_ProductionInSeasons
	ALTER TABLE dbo.ProductionInSeasons ADD CONSTRAINT PK_ProductionInSeasons PRIMARY KEY  (ProductionInSeasonID) ON [PRIMARY]

	PRINT 'Create ProductionTemplates'
       CREATE TABLE ProductionTemplates(
             ProductionTemplateId INT IDENTITY(1,1) NOT NULL,
			 Name VARCHAR(50) NOT NULL,
			 Description VARCHAR(MAX) NULL,
			 ProductionTemplateCategory TINYINT NOT NULL CONSTRAINT DF_ProductionTemplates_ProductionTemplateCategory DEFAULT 1,
			 OwnerGroupId INT NOT NULL,
             LM SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTemplates_LM DEFAULT GETDATE(),
             UM INT NULL,
             LC SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTemplates_LC DEFAULT GETDATE(),
             UC INT NULL,
             CONSTRAINT PK_ProductionTemplates PRIMARY KEY CLUSTERED (ProductionTemplateId) ON [PRIMARY]
       ) ON [PRIMARY]
	  
	   PRINT 'Create ProjectTaskTypes'
	   CREATE TABLE ProjectTaskTypes(
             ProjectTaskTypeId INT IDENTITY(1,1) NOT NULL,
			 ProjectTaskCategory TINYINT NOT NULL CONSTRAINT DF_ProjectTaskTypes_ProjectTaskCategory DEFAULT 1,
			 Name VARCHAR(50) NOT NULL,
			 Description VARCHAR(MAX) NULL,
			 OwnerGroupId INT NOT NULL,
			 IsMileStone BIT NOT NULL CONSTRAINT DF_ProjectTaskTypes_IsMileStone DEFAULT 0,	
             LM SMALLDATETIME NOT NULL CONSTRAINT DF_ProjectTaskTypes_LM DEFAULT GETDATE(),
             UM INT NULL,
             LC SMALLDATETIME NOT NULL CONSTRAINT DF_ProjectTaskTypess_LC DEFAULT GETDATE(),
             UC INT NULL,
             CONSTRAINT PK_ProjectTaskTypes PRIMARY KEY CLUSTERED (ProjectTaskTypeId) ON [PRIMARY]
       ) ON [PRIMARY]


       PRINT 'Create ProductionTaskTemplates'
       CREATE TABLE ProductionTaskTemplates(
             ProductionTaskTemplateId INT IDENTITY(1,1) NOT NULL,
			 ProductionTemplateId INT NOT NULL,
			 SortInParent INT NOT NULL CONSTRAINT DF_ProductionTaskTemplates_SortInParent DEFAULT 0,
			 Name VARCHAR(50) NOT NULL,
			 ProjectTaskTypeId INT NOT NULL,
			 PersonInChargeId INT NULL,
			 LocationId INT NULL,
			 Days SMALLINT NOT NULL CONSTRAINT DF_ProductionTaskTemplates_Days DEFAULT 0,
			 ParentProductionTemplateTaskId INT NULL,
			 WorkAreaId INT NULL,
			 RequiredWorkingMinutes INT NULL,
             LM SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTaskTemplates_LM DEFAULT GETDATE(),
             UM INT NULL,
             LC SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTaskTemplates_LC DEFAULT GETDATE(),
             UC INT NULL,
             CONSTRAINT PK_ProductionTaskTemplates PRIMARY KEY CLUSTERED (ProductionTaskTemplateId) ON [PRIMARY],
             CONSTRAINT FK_ProductionTaskTemplates_ProjectTaskTypes FOREIGN KEY(ProjectTaskTypeId) REFERENCES ProjectTaskTypes (ProjectTaskTypeId),
			 CONSTRAINT FK_ProductionTaskTemplates_Locations FOREIGN KEY(LocationId) REFERENCES Locations (LocationId),
			 CONSTRAINT FK_ProductionTaskTemplates_PersonInCharge FOREIGN KEY(PersonInChargeId) REFERENCES Persons (PersonId)
       ) ON [PRIMARY]

	   PRINT 'Create ProductionTaskDependencyTemplates'
       CREATE TABLE ProductionTaskDependencyTemplates(
             ProductionTaskDependencyTemplateId INT IDENTITY(1,1) NOT NULL,
             PredecessorProductionTaskTemplateId INT NOT NULL,
			 FollowerProductionTaskTemplateId INT NOT NULL,
             LM SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTaskDependencyTemplates_LM DEFAULT GETDATE(),
             UM INT NULL,
             LC SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTaskDependencyTemplates_LC DEFAULT GETDATE(),
             UC INT NULL,
             CONSTRAINT PK_ProductionTaskDependencyTemplates PRIMARY KEY CLUSTERED (ProductionTaskDependencyTemplateId) ON [PRIMARY],
             CONSTRAINT FK_ProductionTaskDependencyTemplates_ProductionTaskPredecessor FOREIGN KEY(PredecessorProductionTaskTemplateId) REFERENCES ProductionTaskTemplates (ProductionTaskTemplateId),
			 CONSTRAINT FK_ProductionTaskDependencyTemplates_ProductionTaskFollower FOREIGN KEY(FollowerProductionTaskTemplateId) REFERENCES ProductionTaskTemplates (ProductionTaskTemplateId)
       ) ON [PRIMARY]
		CREATE UNIQUE INDEX IX_ProductionTaskDependencyTemplates_FollowerProductionTaskId_PredecessorProductionTaskId ON dbo.ProductionTaskDependencyTemplates (FollowerProductionTaskTemplateId, PredecessorProductionTaskTemplateId )
		
       PRINT 'Create ProductionTasks'
       CREATE TABLE ProjectTasks(
             ProjectTaskId INT IDENTITY(1,1) NOT NULL,
			 [Name] VARCHAR(50) NULL,
			 ProjectTaskTypeId INT NOT NULL,
			 ProductionInSeasonId INT NULL,
			 ProductionTaskTemplateId INT NULL,
			 StartDate Date NOT NULL,
			 EndDate Date NOT NULL,
			 BaselineStartDate Date NULL,
			 BaselineEndDate Date NULL,
			 ParentProjectTaskId INT NULL,
			 PersonInChargeId INT NULL,
			 LocationId INT NULL,
			 WorkAreaId INT NULL,
			 RequiredWorkingMinutes INT NULL,
			 ExtraWorkingMinutes INT NULL,
			 Progress TINYINT NULL,
			 IsCanceled BIT NOT NULL CONSTRAINT DF_ProjectTasks_IsCanceled DEFAULT 0,
			 IsSimulation BIT NOT NULL CONSTRAINT DF_ProjectTaskss_IsSimulation DEFAULT 0, --ggf mit Id
             LM SMALLDATETIME NOT NULL CONSTRAINT DF_ProjectTasks_LM DEFAULT GETDATE(),
             UM INT NULL,
             LC SMALLDATETIME NOT NULL CONSTRAINT DF_ProjectTasks_LC DEFAULT GETDATE(),
             UC INT NULL,
             CONSTRAINT PK_ProjectTasks PRIMARY KEY CLUSTERED (ProjectTaskId) ON [PRIMARY],
             CONSTRAINT FK_ProjectTasks_ProjectTaskTypes FOREIGN KEY(ProjectTaskTypeId) REFERENCES ProjectTaskTypes (ProjectTaskTypeId),
			 CONSTRAINT FK_ProjectTasks_ProductionInSeasons FOREIGN KEY(ProductionInSeasonId) REFERENCES ProductionInSeasons (ProductionInSeasonId),
			 CONSTRAINT FK_ProjectTasks_WorkAreas FOREIGN KEY(WorkAreaId) REFERENCES WorkAreas (WorkAreaId),
			 CONSTRAINT FK_ProjectTasks_Persons FOREIGN KEY(PersonInChargeId) REFERENCES Persons (PersonId),
			 CONSTRAINT FK_ProjectTasks_Locations FOREIGN KEY(LocationId) REFERENCES Locations (LocationId),
       ) ON [PRIMARY]

	   PRINT 'Create ProjectTaskDependencies'
       CREATE TABLE ProjectTaskDependencies(
             ProjectTaskDependencyId INT IDENTITY(1,1) NOT NULL,
             PredecessorProductionTaskId INT NOT NULL,
			 FollowerProductionTaskId INT NOT NULL,
             LM SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTasksDependencies_LM DEFAULT GETDATE(),
             UM INT NULL,
             LC SMALLDATETIME NOT NULL CONSTRAINT DF_ProductionTasksDependencies_LC DEFAULT GETDATE(),
             UC INT NULL,
             CONSTRAINT PK_ProjectTaskDependencies PRIMARY KEY CLUSTERED (ProjectTaskDependencyId) ON [PRIMARY],
             CONSTRAINT FK_ProjectTaskDependencies_ProductionTasksPredecessor FOREIGN KEY(PredecessorProductionTaskId) REFERENCES ProjectTasks (ProjectTaskId),
			 CONSTRAINT FK_ProjectTaskDependencies_ProductionTasksFollower FOREIGN KEY(FollowerProductionTaskId) REFERENCES ProjectTasks (ProjectTaskId),
       ) ON [PRIMARY]
		CREATE UNIQUE INDEX IX_ProjectTaskDependencies_FollowerProductionTaskId_PredecessorProductionTaskId ON dbo.ProjectTaskDependencies(FollowerProductionTaskId, PredecessorProductionTaskId )
	
		GRANT SELECT ON ProductionTaskDependencyTemplates to public
		GRANT SELECT ON ProductionTaskTemplates to public
		GRANT SELECT ON ProductionTemplates to public
		GRANT SELECT ON ProjectTaskTypes to public

		GRANT UPDATE, INSERT, DELETE ON ProductionTaskDependencyTemplates to MasterAdmin
		GRANT UPDATE, INSERT, DELETE ON ProductionTaskTemplates to MasterAdmin
		GRANT UPDATE, INSERT, DELETE ON ProductionTemplates to MasterAdmin
		GRANT UPDATE, INSERT, DELETE ON ProjectTaskTypes to MasterAdmin
		
		SET IDENTITY_INSERT ProductionTemplates ON
		INSERT INTO ProductionTemplates (ProductionTemplateId, OwnerGroupId, Name ) VALUES (1, 1, 'Produktion')
		SET IDENTITY_INSERT ProductionTemplates OFF	

		--SET IDENTITY_INSERT ProjectTaskTypes ON
		--INSERT INTO ProjectTaskTypes (ProjectTaskTypeId, ProjectTaskCategory, OwnerGroupId, IsMileSone, Name) VALUES 
		--(2, 1, 1, 0, 'Vorbereitunge Bauprobe'),
		--(3 , 1, 1, 0, 'Material zusammenstellen'),
		--(4 , 1, 1, 0, 'Möbel'),
		--(5 , 1, 1, 0, 'Vorkallulation erstellen'),
		--(6 , 1, 1, 1, 'Bauprobe'),
		--(7 , 1, 1, 0, 'Planabgabe vorbereiten'),
		--(8 , 1, 1, 0, 'Bauprobenprotokoll'),
		--(9 , 1, 1, 0, 'Planbearbeitung'),
		--(10 , 1, 1, 0, 'Konstrtruktionsklärung'),
		--(11 , 1, 1, 0, 'Materialauswahl / Test'),
		--(12 , 1, 1, 0, 'Standartmaterial zusammenstellen'),
		--(13 , 1, 1, 0, 'Planabgabe'),
		--(14 , 1, 1, 0, 'WÜ vorbereiten'),
		--(15 , 1, 1, 0, 'Konstruktion'),
		--(16 , 1, 1, 0, 'Herstellungspläne'),
		--(17 , 1, 1, 0, 'Abteilungskalkulationen'),
		--(18 , 1, 1, 1, 'WÜ'),
		--(19 , 1, 1, 0, 'Herstellung vorbereiten'),
		--(20 , 1, 1, 0, 'Materialbestellung'),
		--(21 , 1, 1, 0, 'Plankorrekturen'),
		--(22 , 1, 1, 0, 'Lieferfrist Arbeitsmaterial'),
		--(23 , 1, 1, 0, 'erf. Fremdvergaben'),
		--(24 , 1, 1, 0, 'Werkstattzeit'),
		--(25 , 1, 1, 0, 'Wände'),
		--(26 , 1, 1, 0, 'Gestelle'),
		--(27 , 1, 1, 0, 'Plastiken'),
		--(28 , 1, 1, 0, 'Prospekte'),
		--(29 , 1, 1, 0, 'Puffer'),
		--(30 , 1, 1, 1, 'TE'),
		--(31 , 1, 1, 0, 'Finale'),
		--(32 , 1, 1, 0, 'Proben'),
		--(33 , 1, 1, 0, 'Korrekturen'),
		--(34 , 1, 1, 0, 'Ergänzungen'),
		--(35 , 10, 1, 0, 'Probenzeitraum')
		--SET IDENTITY_INSERT ProjectTaskTypes OFF 

		--SET IDENTITY_INSERT ProductionTaskTemplates ON
		--INSERT INTO ProductionTaskTemplates (ProductionTaskTemplateId, ProjectTaskTypeId, ProductionTemplateId, Name, Days, ParentProductionTemplateTaskId, SortInParent) VALUES 
		--(2, 2, 1, 'Vorbereitunge Bauprobe', 10, NULL, 1),
		--(3, 3, 1, 'Material zusammenstellen', 5, 2, 1),
		--(4, 4, 1, 'Möbel', 4, 2, 1),
		--(5, 5, 1, 'Vorkallulation erstellen', 5, 2, 1),
		--(6, 6, 1, 'Bauprobe',  0, 2, 1),
		--(7, 7, 1, 'Planabgabe vorbereiten', 30 , NULL, 2),
		--(8, 8, 1, 'Bauprobenprotokoll', 5, 7, 1),
		--(9, 9, 1, 'Planbearbeitung',  25, 7, 2),
		--(10, 11, 1, 'Konstrtruktionsklärung', 15 , 7, 3),
		--(11, 11, 1, 'Materialauswahl / Test', 10, 7, 4),
		--(12, 12, 1, 'Standartmaterial zusammenstellen', 10, 7, 5),
		--(13, 13, 1, 'Planabgabe', 0, 7, 6),
		--(14, 14, 1, 'WÜ vorbereiten', 20, NULL,3),
		--(15, 15, 1, 'Konstruktion', 10, 14, 1),
		--(16, 16, 1, 'Herstellungspläne', 10, 14,2),
		--(17, 17, 1, 'Abteilungskalkulationen', 20, 14,3),
		--(18, 18, 1, 'WÜ',0, 14, 4),
		--(19, 19, 1, 'Herstellung vorbereiten', 20, NULL, 4),
		--(20, 20, 1, 'Materialbestellung', 5, 19, 1),
		--(21, 21, 1, 'Plankorrekturen', 10, 19, 2),
		--(22, 22, 1, 'Lieferfrist Arbeitsmaterial', 20, 19,3),
		--(23, 23, 1, 'erf. Fremdvergaben', 1,  19, 4),
		--(24, 24, 1, 'Werkstattzeit', 30, NULL, 5),
		--(25, 25, 1, 'Wände', 30, 24, 1),
		--(26, 26, 1, 'Gestelle', 10, 24, 2),
		--(27, 27, 1, 'Plastiken', 5, 24, 3),
		--(28, 28, 1, 'Prospekte', 30, 24, 4),
		--(29, 29, 1, 'Puffer', 5, 24, 5),
		--(30, 30, 1, 'TE', 0, 24, 6),
		--(31, 31, 1, 'Finale', 10, NULL, 5),
		--(32, 31, 1, 'Proben', 10, 31, 1),
		--(33, 33, 1, 'Korrekturen', 2, 31, 2),
		--(34, 34, 1, 'Ergänzungen', 1,  31, 3)
		--SET IDENTITY_INSERT ProductionTaskTemplates OFF 
				
		--INSERT INTO ProductionTaskDependencyTemplates (PredecessorProductionTaskTemplateId, FollowerProductionTaskTemplateId ) VALUES 
		--(3, 5),
		--(2, 6),
		--(6, 7),
		--(8, 9),
		--(10, 12),
		--(7, 13),
		--(13, 14),
		--(15, 16),
		--(14, 18),
		--(18, 19),
		--(19, 24),
		--(24, 29),
		--(29, 30),
		--(30, 31)
	
END
GO

--#endregion

--#region
EXEC DropColumn 'PersonContactData' ,'locked'
EXEC DropColumn 'PersonPermissions' ,'HasAccessToSecretContactData'
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.DienstplanIst') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE DienstplanIst

GO
--#endregion

--#region 2020-01-28
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Departments') AND [name] = 'IsInactive')
BEGIN
	PRINT('Adding column Departments.IsInactive ...')
	ALTER TABLE dbo.Departments ADD IsInactive BIT NOT NULL CONSTRAINT DF_Departments_IsInactive DEFAULT 0
END
GO
--#endregion

--#region 2020-02-05
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Roles') AND [name] = 'Name_en')
BEGIN
	PRINT('Adding column Roles.Name_en ...')
	ALTER TABLE dbo.Roles ADD Name_en NVARCHAR(150) SPARSE NULL 
	ALTER TABLE dbo.Roles ALTER COLUMN PublishedName NVARCHAR(150) SPARSE NULL 
	ALTER TABLE dbo.Roles ALTER COLUMN RoleCode VARCHAR(3) SPARSE NULL 
	ALTER TABLE dbo.Roles ALTER COLUMN Note VARCHAR(250) SPARSE NULL 
END
GO

--#endregion

--#region
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.MergeRoles')) DROP PROCEDURE dbo.MergeRoles
GO
CREATE PROCEDURE dbo.MergeRoles
	@OldActivityType INT,
	@NewActivityType INT
	AS
BEGIN
	UPDATE Roles SET RoleTypeId = @NewActivityType WHERE  RoleTypeId = @OldActivityType
END
GO
GRANT EXECUTE ON MergeRoles TO DispoAdmin
GRANT EXECUTE ON MergeRoles TO DataCleaningAdmin
GO
PRINT('Alter MergeActivityTypes')
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.MergeActivityTypes')) DROP PROCEDURE dbo.MergeActivityTypes
GO
CREATE PROCEDURE dbo.MergeActivityTypes
	@OldActivityTypeId INT,
	@NewActivityTypeId INT
	AS
BEGIN
	UPDATE Activities SET ActivityTypeId = @NewActivityTypeId WHERE  ActivityTypeId = @OldActivityTypeId
END
GO
GRANT EXECUTE ON MergeRoles TO DispoAdmin
GRANT EXECUTE ON MergeRoles TO DataCleaningAdmin
GO
PRINT('Alter MergeShiftTypes')
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.MergeShiftTypes')) DROP PROCEDURE dbo.MergeShiftTypes
GO
CREATE PROCEDURE dbo.MergeShiftTypes
	@OldShiftTypId INT,
	@NewActivityTypeId INT
	AS
BEGIN
	UPDATE DutyRoster SET ShiftTypeId1 = @NewActivityTypeId WHERE  ShiftTypeId1 = @OldShiftTypId 
	UPDATE DutyRoster SET ShiftTypeId2 = @NewActivityTypeId WHERE  ShiftTypeId2 = @OldShiftTypId 
	UPDATE WorkingTimeModelDays SET ShiftTypeId = @NewActivityTypeId WHERE  ShiftTypeId = @OldShiftTypId 
END
GO
GRANT EXECUTE ON MergeShiftTypes TO PersoAdmin
GRANT EXECUTE ON MergeShiftTypes TO DataCleaningAdmin
GO

--#endregion

--#endregion -------- (4.201.x) -------------------------------

--#region ----------- 4.201.x Release Übertrag ----------------

--#region sh 2020-02-18 DF_Cast_GroupPosition von 0 auf 1 ändern und fehlerhafte Cast Einträge korrigieren

IF EXISTS (SELECT * FROM NVDATA WHERE  ByModulScript = 1 AND (Major * 100000000) + (Minor * 1000) + Build  < 400202000) 
BEGIN
	PRINT('Edit DF_Cast_GroupPosition...')
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_Cast_GroupPosition') ALTER TABLE [Cast] DROP CONSTRAINT [DF_Cast_GroupPosition]
	ALTER TABLE [Cast] ADD  CONSTRAINT [DF_Cast_GroupPosition]  DEFAULT 1 FOR [GroupPosition]
	UPDATE [Cast] SET GroupPosition = 1 WHERE  GroupPosition < 1
END
go
--#endregion sh 2020-02-18 DF_Cast_GroupPosition von 0 auf 1 ändern und fehlerhafte Cast Einträge korrigieren

--#region dh 2020-02-21 AbZeitkontenMonat_Update notwendige Berechtigungen angepasst.
Print 'Alter AbZeitkontenMonat_Update'
GO
ALTER VIEW [dbo].[AbZeitkontenMonat_Update]
AS
SELECT * FROM TimeAccountMonthValues WHERE  TimeAccountMonthValueId IN (
	SELECT ZM.TimeAccountMonthValueId
	FROM TimeAccountMonthValues AS ZM
		INNER JOIN Contracts AS V ON V.ContractId = ZM.ContractId
		INNER JOIN ContractDepartments AS VP ON VP.ContractId = V.ContractId
		INNER JOIN WorkAreas ON WorkAreas.WorkAreaId = VP.WorkAreaId
		INNER JOIN Departments AS DP ON DP.DepartmentId = WorkAreas.DepartmentId
		INNER JOIN Permissions AS UG ON 
			   (UG.OwnerGroupId = V.OwnerGroupId AND UG.PermissionArea = 1 AND ug.HasWritePermission=1) 
			OR (UG.OwnerGroupId = DP.OwnerGroupId AND UG.PermissionArea = 5 AND OptionsFlag & 2 = 2)
			OR (UG.OwnerGroupId = DP.OwnerGroupId AND UG.PermissionArea = 5 AND OptionsFlag & 8 = 8)
		INNER JOIN Users AS UP ON UP.UserID = UG.UserID
	WHERE UP.UserName = CURRENT_USER)
GO
--#endregion dh 2020-02-21 AbZeitkontenMonat_Update notwendige Berechtigungen angepasst.



--#region SH 2020-03-13 Bei ausgefallenen Aktivitäten die Dienste bei DepartmentInformation einmalig auf null setzen.

IF EXISTS (SELECT * FROM NvData WHERE  ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 201 OR (Minor=201 AND Build < 105)))))
BEGIN
	PRINT('Setting ShiftCount for canceled activities to null...')
	 UPDATE dd SET dd.ShiftCount = NULL FROM ActivityDepartmentData dd INNER JOIN Activities a ON dd.ActivityId = a.ActivityId WHERE  a.Status < 0 AND dd.ShiftCount IS NOT NULL
END
GO
--#endregion

--#region SH 2020-03-13 Period Constraint für LocationRates

IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_LocationRates_PeriodCheck') 
BEGIN 
	Print 'Add CK_LocationRates_PeriodCheck'
	UPDATE LocationRates SET EndDate = NULL  WHERE  StartDate > EndDate
	ALTER TABLE LocationRates WITH CHECK ADD CONSTRAINT CK_LocationRates_PeriodCheck CHECK (StartDate <= EndDate)
	ALTER TABLE LocationRates CHECK CONSTRAINT CK_LocationRates_PeriodCheck
END 
GO

--#endregion
--#region DH 2020-03-26 Prozedur für Dienste in Zeitraum angepasst
--#region CountCastShiftByContract
Print 'Alter CountCastShiftByContract'
GO
ALTER PROCEDURE [dbo].[CountCastShiftByContract]
	@countType INT,
	@start Datetime, 
	@end Datetime,
	@contractIds NVARCHAR(4000),
	@departmentId INT,
	@activitytypIds NVARCHAR(4000)
AS
BEGIN
	SET NOCOUNT ON;
/*
@countType
0 = Anzahl
1 = Anrechenbare
2 = Zeit
*/

--Spielzeitberechtigungen prüfen und ggf Zeitraum anpassen
DECLARE @permissionPeriodStart DATETIME
DECLARE @permissionPeriodEnd DATETIME

SELECT @permissionPeriodStart = MIN(s.SeasonId) , @permissionPeriodEnd = MAX(s.EndDate)
	FROM Seasons AS s	
	INNER JOIN SeasonPermissions	AS us	ON s.SeasonId = us.SeasonId
	INNER JOIN Users			AS U	ON us.UserId =u.UserId
WHERE u.UserName  = CURRENT_USER

IF (@start < @permissionPeriodStart)  SELECT @start = @permissionPeriodStart
IF (@end > @permissionPeriodEnd)  SELECT @end = @permissionPeriodEnd

--Unterscheidung ob die Anzahl oder die anrechenbare Zahl der Dienste gezählt wird(
--z.B. Creditable = 2 als zwei Dienste)

CREATE TABLE #contractIds (id INT  PRIMARY KEY)
INSERT INTO #contractIds SELECT * FROM [dbo].[SplitIDs] ( @contractIds)

IF @activitytypIds IS NOT NULL
BEGIN
	CREATE TABLE #activitytypIds (id int  PRIMARY KEY)
	INSERT INTO #activitytypIds SELECT * FROM [dbo].[SplitIDs] ( @activitytypIds)
END

IF @countType = 0
	--Anzahl
	BEGIN
		IF @activitytypIds IS NULL
			--ohne Aufftypfilter
			BEGIN 
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ca.ActivityId) AS float) AS Sum  
				FROM			Activities ac
					INNER JOIN Cast	AS ca	ON ac.ActivityId = ca.ActivityId
					INNER JOIN Roles		AS r	ON ca.RoleId = r.RoleId
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
				WHERE Date BETWEEN @start AND @end 
					AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
					AND ac.Status BETWEEN 0 AND 99 
				GROUP BY ca.ContractId
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ca.ActivityId) AS float) AS Sum  
				FROM		Activities ac
					INNER JOIN Cast	AS ca	ON ac.ActivityId =ca.ActivityId
					INNER JOIN Roles		AS r	ON ca.RoleId =r.RoleId
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.ActivityTypeId = #activitytypIds.id
				WHERE Date BETWEEN @start AND @end 
					AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
					AND ac.Status BETWEEN 0 AND 99 
				GROUP BY ca.ContractId
			END
		END
	ELSE IF @countType = 1
		--Anrechenbare
		BEGIN
		IF @activitytypIds IS NULL
		--ohne Aufftypfilter
			BEGIN 
				SELECT ContractId, SUM(Sum1) FROM (SELECT ca.ContractId, ca.ActivityId, 
					MAX(ISNULL(ca.Creditable,
						ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		Activities			AS ac
					INNER JOIN ActivityTypes			AS at		ON at.ActivityTypeId = ac.ActivityTypeId
					INNER JOIN Cast		AS ca		ON ac.ActivityId =ca.ActivityId
					INNER JOIN Roles			AS r		ON ca.RoleId =r.RoleId
					LEFT JOIN ActivityDepartmentData	AS ActDep	ON ac.ActivityId = ActDep.ActivityId AND ActDep.DepartmentId = @departmentId
					LEFT JOIN ProductionDeparmentData		AS ProdDep	ON ac.ProductionId = ProdDep.ProductionId AND ProdDep.DepartmentId = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds						ON ca.ContractId = #contractIds.id
					WHERE Date BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						
					GROUP BY ContractId, ca.ActivityId) AS SubSQL GROUP BY ContractId 
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ContractId, SUM(Sum1) FROM (SELECT ContractId, ca.ActivityId, MAX(ISNULL(ca.Creditable,
						ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		Activities			AS ac
					INNER JOIN ActivityTypes			AS at		ON at.ActivityTypeId = ac.ActivityTypeId
					INNER JOIN Cast		AS ca		ON ac.ActivityId = ca.ActivityId
					INNER JOIN Roles			AS r		ON ca.RoleId = r.RoleId
					LEFT JOIN ActivityDepartmentData	AS ActDep	ON ac.ActivityId = ActDep.ActivityId AND ActDep.DepartmentId = @departmentId
					LEFT JOIN ProductionDeparmentData		AS ProdDep	ON ac.ProductionId = ProdDep.ProductionId AND ProdDep.DepartmentId = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds					ON ac.ActivityTypeId = #activitytypIds.id
					WHERE Date BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						
					GROUP BY ContractId, ca.ActivityId) AS SubSQL GROUP BY ContractId 
			END	
		END
ELSE IF @countType = 2
	BEGIN
	--Zählen pro Tag max 2 Dienst  (morgens < 14:00 und abends)
			IF @activitytypIds IS NULL
		--ohne Aufftypfilter
			BEGIN 
				SELECT ContractId, SUM(Sum1) FROM (SELECT ContractId, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, 
					MAX(ISNULL(ca.Creditable, ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		Activities			AS ac
					INNER JOIN ActivityTypes			AS at	ON at.ActivityTypeId = ac.ActivityTypeId
					INNER JOIN Cast		AS ca	ON ac.ActivityId = ca.ActivityId
					INNER JOIN Roles			AS r	ON ca.RoleId = r.RoleId
					LEFT JOIN ActivityDepartmentData	AS ActDep ON ac.ActivityId = ActDep.ActivityId AND ActDep.DepartmentId = @departmentId
					LEFT JOIN ProductionDeparmentData		AS ProdDep ON ac.ProductionId = ProdDep.ProductionId AND ProdDep.DepartmentId = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					WHERE Date BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL GROUP BY ContractId 
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ContractId, SUM(Sum1) FROM (SELECT ContractId, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, MAX(ISNULL(ca.Creditable,
						ISNULL(ActDep.ShiftCount, CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		Activities			AS ac
					INNER JOIN ActivityTypes			AS at	ON at.ActivityTypeId = ac.ActivityTypeId
					INNER JOIN Cast		AS ca	ON ac.ActivityId = ca.ActivityId
					INNER JOIN Roles			AS r	ON ca.RoleId = r.RoleId
					LEFT JOIN ActivityDepartmentData	AS ActDep ON ac.ActivityId = ActDep.ActivityId AND ActDep.DepartmentId = @departmentId
					LEFT JOIN ProductionDeparmentData		AS ProdDep ON ac.ProductionId = ProdDep.ProductionId AND ProdDep.DepartmentId = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.ActivityTypeId = #activitytypIds.id
					WHERE Date BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL GROUP BY ContractId 
			END		
	END
ELSE IF @countType = 3
	BEGIN
	--Zählen pro Tag max 2 Dienst  (morgens < 14:00 und abends)
			IF @activitytypIds IS NULL
		--ohne Aufftypfilter
			BEGIN 
				SELECT ContractIdDay, SUM(SumDay) FROM(
				SELECT ContractId AS ContractIdDay, Date, SUM(Sum1)-1  AS [SumDay]
				FROM (
					SELECT ContractId, ac.Date, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, 
						MAX(ISNULL(ca.Creditable, ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 
							THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
					FROM		Activities			AS ac
					INNER JOIN ActivityTypes			AS at	ON at.ActivityTypeId = ac.ActivityTypeId
					INNER JOIN Cast		AS ca	ON ac.ActivityId = ca.ActivityId
					INNER JOIN Roles			AS r	ON ca.RoleId = r.RoleId
					LEFT JOIN ActivityDepartmentData	AS ActDep ON ac.ActivityId = ActDep.ActivityId AND ActDep.DepartmentId =@departmentId
					LEFT JOIN ProductionDeparmentData		AS ProdDep ON ac.ProductionId = ProdDep.ProductionId AND ProdDep.DepartmentId = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					WHERE Date BETWEEN @start AND @end
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, ac.Date, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL 
				GROUP BY ContractId, Date)  AS subDay Group By ContractIdDay
END		
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ContractIdDay, SUM(SumDay) FROM(
				SELECT ContractId AS ContractIdDay, Date, SUM(Sum1)-1  AS [SumDay]
				FROM (
					SELECT ContractId, ac.Date, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, 
						MAX(ISNULL(ca.Creditable, ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 
							THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
					FROM		Activities			AS ac
					INNER JOIN ActivityTypes			AS at	ON at.ActivityTypeId = ac.ActivityTypeId
					INNER JOIN Cast		AS ca	ON ac.ActivityId = ca.ActivityId
					INNER JOIN Roles			AS r	ON ca.RoleId = r.RoleId
					LEFT JOIN ActivityDepartmentData	AS ActDep ON ac.ActivityId = ActDep.ActivityId AND ActDep.DepartmentId =@departmentId
					LEFT JOIN ProductionDeparmentData		AS ProdDep ON ac.ProductionId = ProdDep.ProductionId AND ProdDep.DepartmentId = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.ActivityTypeId = #activitytypIds.id
					WHERE Date BETWEEN @start AND @end
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, ac.Date, CONVERT(VARCHAR(10),ac.Date,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.StartTime, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL 
				GROUP BY ContractId, Date)  AS subDay Group By ContractIdDay
			END		
	END
ELSE IF @countType = 4
	BEGIN
		IF @activitytypIds IS NULL
			--ohne Aufftypfilter
			BEGIN 
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ac.Date) AS float) AS Sum  
				FROM		Activities ac
					INNER JOIN Cast	AS ca	ON ac.ActivityId =ca.ActivityId
					INNER JOIN Roles		AS r	ON ca.RoleId =r.RoleId
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
				WHERE Date BETWEEN @start AND @end 
					AND ca.IsCanceled = 0 
					AND ac.Status BETWEEN 0 AND 99 	
				GROUP BY ContractId
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ac.Date) AS float) AS Sum  
				FROM		Activities ac
					INNER JOIN Cast	AS ca	ON ac.ActivityId =ca.ActivityId
					INNER JOIN Roles		AS r	ON ca.RoleId =r.RoleId
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.ActivityTypeId = #activitytypIds.id
				WHERE Date BETWEEN @start AND @end 
					AND ca.IsCanceled = 0 
					AND ac.Status BETWEEN 0 AND 99 
				GROUP BY ContractId
			END
		END

END
	DROP TABLE  #contractIds
	IF @activitytypIds IS NOT NULL
	BEGIN
		DROP TABLE #activitytypIds
	END
GO
--#endregion CountCastShift
--#endregion DH 2020-03-26 Prozedur für Dienste in Zeitraum angepasst
--#region DH 2020-04-02 Bugfix Seasonspermission
GRANT SELECT,INSERT,UPDATE,DELETE ON Seasons TO SecurityAdmin
GO
--#endregion DH 2020-04-02 Bugfix Seasonspermission

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

--#region ----------- 4.202.x ---------------------------------

--#region 2020-02-07 Rename CommunicationTypes.Medium
IF EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('CommunicationTypes') AND [name] = 'ComumunicationMedium')
BEGIN
	PRINT('Rename CommunicationTypes.Medium ...')
	EXEC sp_rename  'CommunicationTypes.ComumunicationMedium', 'Medium', 'COLUMN'
	EXEC sp_rename  'PersonMemoTypes.ComumunicationMedium', 'CommunicationMedium', 'COLUMN'
END
GO
--#endregion

--#region SubscriptionGroups
IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='SubscriptionGroups')
BEGIN
	PRINT 'Create SubscriptionGroups'
	CREATE TABLE SubscriptionGroups(
			SubscriptionGroupId INT IDENTITY(1,1) NOT NULL,
			Name VARCHAR(50) NOT NULL,
			LM SMALLDATETIME NOT NULL CONSTRAINT DF_SubscriptionGroups_LM DEFAULT GETDATE(),
			UM INT NULL,
			LC SMALLDATETIME NOT NULL CONSTRAINT DF_SubscriptionGroups_LC DEFAULT GETDATE(),
			UC INT NULL,
			CONSTRAINT PK_SubscriptionGroups PRIMARY KEY CLUSTERED (SubscriptionGroupId) ON [PRIMARY]
	) ON [PRIMARY]

	GRANT INSERT, UPDATE, DELETE ON SubscriptionGroups TO DispoAdmin
	GRANT SELECT ON SubscriptionGroups TO Public  
	ALTER TABLE dbo.SubscriptionTypes ADD SubscriptionGroupId INT NULL
	ALTER TABLE dbo.SubscriptionTypes ADD IsProductionsChoiceSubscription BIT NOT NULL CONSTRAINT DF_SubscriptionTypes_IsProductionsChoiceSubscription DEFAULT 0
	ALTER TABLE dbo.SubscriptionTypes ADD CONSTRAINT FK_SubscriptionTypes_SubscriptionGroups FOREIGN KEY (SubscriptionGroupId) REFERENCES dbo.SubscriptionGroups (SubscriptionGroupId)

	CREATE TABLE SubscriptionProductions(
			SubscriptionProductionId INT IDENTITY(1,1) NOT NULL,
			SubscriptionId INT NOT NULL,
			ProductionId INT NULL,
			IsAlternate Bit NOT NULL CONSTRAINT DF_SubscriptionProductions_IsAlternate DEFAULT 0,
			YearMonth INT NULL,
			Note VARCHAR(1024) NULL,
			LM SMALLDATETIME NOT NULL CONSTRAINT DF_SubscriptionProductions_LM DEFAULT GETDATE(),
			UM INT NULL,
			LC SMALLDATETIME NOT NULL CONSTRAINT DF_SubscriptionProductions_LC DEFAULT GETDATE(),
			UC INT NULL
			CONSTRAINT PK_SubscriptionProductions PRIMARY KEY NONCLUSTERED (SubscriptionProductionId) ON [PRIMARY]
	) ON [PRIMARY]


	CREATE CLUSTERED INDEX IX_SubscriptionProductions_SubscriptionId ON dbo.SubscriptionProductions (SubscriptionId)

	GRANT INSERT, UPDATE, DELETE ON SubscriptionProductions TO DispoAdmin
	GRANT SELECT ON SubscriptionProductions TO Public  

	ALTER TABLE dbo.SubscriptionProductions ADD CONSTRAINT FK_SubscriptionProductions_Subscription FOREIGN KEY (SubscriptionId) REFERENCES dbo.Subscriptions (SubscriptionId)	

	ALTER TABLE dbo.SubscriptionTypes ADD RegionCodes VARCHAR(35)

	ALTER TABLE dbo.Locations ADD MaxSubscribersPercent TINYINT NULL

	ALTER TABLE dbo.Subscriptions ADD LockoutPeriods XML NULL

	ALTER TABLE dbo.Subscriptions ADD ContactPersonId INT NULL
	ALTER TABLE dbo.Subscriptions ADD CONSTRAINT FK_Subscriptions_ContactPerson FOREIGN KEY (ContactPersonId) REFERENCES dbo.Persons (PersonId)	ON DELETE SET NULL

	ALTER TABLE dbo.SubscriptionTypes ADD NumberOfPerformancesRules XML NULL
	ALTER TABLE dbo.SubscriptionTypes ADD WeekdayCheckText VARCHAR(512)

	UPDATE SubscriptionTypes SET NotOnBankHolidays = 0 WHERE  NotOnBankHolidays IS NULL
	UPDATE SubscriptionTypes SET NotOnBankHolidays = 1 WHERE  NotOnBankHolidays = 2

	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_SubscriptionTypes_NotOnBankHolidays') ALTER TABLE SubscriptionTypes DROP CONSTRAINT DF_SubscriptionTypes_NotOnBankHolidays
	ALTER TABLE SubscriptionTypes ALTER COLUMN NotOnBankHolidays BIT NOT NULL
	ALTER TABLE SubscriptionTypes ADD CONSTRAINT DF_SubscriptionTypes_NotOnBankHolidays DEFAULT 0 FOR NotOnBankHolidays

	UPDATE SubscriptionTypes SET PremiereRule = 0 WHERE  PremiereRule IS NULL
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_SubscriptionTypes_PremiereRule') ALTER TABLE SubscriptionTypes DROP CONSTRAINT DF_SubscriptionTypes_PremiereRule
	ALTER TABLE SubscriptionTypes ALTER COLUMN PremiereRule TINYINT NOT NULL
	ALTER TABLE SubscriptionTypes ADD CONSTRAINT DF_SubscriptionTypes_PremiereRule DEFAULT 0 FOR PremiereRule

	UPDATE SubscriptionTypes SET TimeOfDayRule = 0 WHERE  TimeOfDayRule IS NULL
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_SubscriptionTypes_TimeOfDayRule') ALTER TABLE SubscriptionTypes DROP CONSTRAINT DF_SubscriptionTypes_TimeOfDayRule
	ALTER TABLE SubscriptionTypes ALTER COLUMN TimeOfDayRule TINYINT NOT NULL
	ALTER TABLE SubscriptionTypes ADD CONSTRAINT DF_SubscriptionTypes_TimeOfDayRule DEFAULT 0 FOR TimeOfDayRule
	  
	IF EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.SubscriptionTypes') AND o.Name = 'Sort') DROP INDEX SubscriptionTypes.Sort 
	UPDATE SubscriptionTypes SET Sort = 0 WHERE  Sort IS NULL
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_SubscriptionTypes_Sort') ALTER TABLE SubscriptionTypes DROP CONSTRAINT DF_SubscriptionTypes_Sort
	ALTER TABLE SubscriptionTypes ALTER COLUMN Sort SMALLINT NOT NULL
	ALTER TABLE SubscriptionTypes ADD CONSTRAINT DF_SubscriptionTypes_Sort DEFAULT 0 FOR Sort
	 
	 UPDATE SubscriptionTypes SET DaysBetweenRule = 0 WHERE  DaysBetweenRule IS NULL OR DaysBetweenRule> 255
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_SubscriptionTypes_DaysBetweenRule') ALTER TABLE SubscriptionTypes DROP CONSTRAINT DF_SubscriptionTypes_DaysBetweenRule
	ALTER TABLE SubscriptionTypes ALTER COLUMN DaysBetweenRule TINYINT NOT NULL
	ALTER TABLE SubscriptionTypes ADD CONSTRAINT DF_SubscriptionTypes_DaysBetweenRule DEFAULT 0 FOR DaysBetweenRule
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Productions') AND [name] = 'PerformanceStageId')
BEGIN
	PRINT('Adding column Productions.PerformanceStageId ...')
	ALTER TABLE dbo.Productions ADD PerformanceStageId INT NULL 
	ALTER TABLE dbo.Productions ADD CONSTRAINT FK_Productions_Locations FOREIGN KEY (PerformanceStageId) REFERENCES dbo.Locations (LocationId)

	EXEC ('UPDATE Productions SET PerformanceStageId = (SELECT TOP 1 a.LocationId 
					 FROM Activities a 
						INNER JOIN 	ActivityTypes att ON a.ActivityTypeId = att.ActivityTypeId
						INNER JOIN Locations l ON a.LocationId = l.LocationId
					WHERE l.Locality = 0 AND LocationTypeId = 1 AND att.Category>=2000 AND a.ProductionId = Productions.ProductionId)
					WHERE (SELECT COUNT(DISTINCT a.LocationId) 
					 FROM Activities a
						INNER JOIN 	ActivityTypes att ON a.ActivityTypeId = att.ActivityTypeId
						INNER JOIN Locations l ON a.LocationId = l.LocationId
					WHERE  l.Locality = 0 AND LocationTypeId = 1  AND att.Category>=2000 AND a.ProductionId = Productions.ProductionId) = 1')	
END
GO
--#endregion

--#region Adding column RoleCategories.RehearsalFeeFactor, Tours.BudgetItem, ActivityDetails.CostCenterId, CastBonus.BudgetItemId
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('RoleCategories') AND [name] = 'RehearsalFeeFactor')
BEGIN
	PRINT('Adding column RoleCategories.RehearsalFeeFactor ...')
	ALTER TABLE dbo.RoleCategories ADD RehearsalFeeFactor FLOAT NULL
	EXEC sp_rename  'RoleCategories.FeeFactor', 'PerformanceFeeFactor', 'COLUMN'
	UPDATE Users SET IsGroup = 0 WHERE  IsGroup IS NULL
	ALTER TABLE Users ALTER COLUMN IsGroup BIT NOT NULL
	UPDATE [Users] SET PersonId = NULL WHERE  PersonId NOT IN (SELECT PersonId FROM Persons)
	ALTER TABLE dbo.Users ADD CONSTRAINT FK_Users_Persons FOREIGN KEY (PersonId) REFERENCES dbo.Persons (PersonId) ON DELETE SET NULL
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Tours') AND [name] = 'BudgetItemId')
BEGIN
	PRINT('Adding column Tours.BudgetItem ...')
	ALTER TABLE dbo.Tours ADD BudgetItemId INT NULL
	ALTER TABLE dbo.Tours ADD CONSTRAINT FK_Tours_BudgetItems FOREIGN KEY (BudgetItemId) REFERENCES dbo.BudgetItems (BudgetItemId)
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityDetails') AND [name] = 'CostUnitId')
BEGIN
	PRINT('Adding column ActivityDetails.CostCenterId ...')
	ALTER TABLE dbo.ActivityDetails ADD BudgetItemId INT NULL
	ALTER TABLE dbo.ActivityDetails ADD CONSTRAINT FK_ActivityDetails_BudgetItems FOREIGN KEY (BudgetItemId) REFERENCES dbo.BudgetItems (BudgetItemId)
	ALTER TABLE dbo.ActivityDetails ADD CostUnitId INT NULL
	ALTER TABLE dbo.ActivityDetails ADD CONSTRAINT FK_ActivityDetails_CostUnits FOREIGN KEY (CostUnitId) REFERENCES dbo.CostUnits (CostUnitId)
	EXEC sp_rename  'ActivityDetails.BesucherIst', 'ActualVisitors', 'COLUMN'
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('CastBonus') AND [name] = 'BudgetItemId')
BEGIN
	PRINT('Adding column CastBonus.BudgetItemId ...')
	ALTER TABLE dbo.CastBonus ADD BudgetItemId INT NULL
	ALTER TABLE dbo.CastBonus ADD CONSTRAINT FK_CastBonus_BudgetItems FOREIGN KEY (BudgetItemId) REFERENCES dbo.BudgetItems (BudgetItemId)
END
GO
--#endregion

--#region 2020-02-25 DH - Ruhezeit für Warnung bei Frei einstellbar unabhängig von Nachtruhe
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'RuhezeitFrei' AND o.id = OBJECT_ID(N'[dbo].[PersoParameter]') AND c.length=1)	
BEGIN
	ALTER TABLE [dbo].[PersoParameter] DROP CONSTRAINT [DF_PersoParameter_RuhezeitFrei];

	ALTER TABLE PersoParameter ALTER COLUMN RuhezeitFrei INT NOT NULL;
	UPDATE PersoParameter
		SET RuhezeitFrei=RuhezeitFrei* 24*60 + NARUHE*60
		where RuhezeitFrei=1;
	ALTER TABLE [dbo].[PersoParameter] ADD  CONSTRAINT [DF_PersoParameter_RuhezeitFrei]  DEFAULT (0) FOR [RuhezeitFrei];
END
GO
--#endregion 2020-02-25 DH - Ruhezeit für Warnung bei Frei einstellbar unabhängig von Nachtruhe

--#region Adding column SchoolHolidays.TheatreIsClosed, check constraint CK_ProjectTasks_PeriodCheck
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('SchoolHolidays') AND [name] = 'TheatreIsClosed')
BEGIN
	PRINT('Adding column SchoolHolidays.TheatreIsClosed ...')
	ALTER TABLE dbo.SchoolHolidays ADD TheatreIsClosed BIT NOT NULL CONSTRAINT DF_SchoolHolidays_TheatreIsClosed DEFAULT 0
	ALTER TABLE dbo.ProjectTaskTypes ADD DayExceptionRules XML NULL
	ALTER TABLE dbo.ProductionTemplates ADD BackColor INT NULL
	ALTER TABLE dbo.ProjectTasks ADD BackColor INT NULL
	ALTER TABLE dbo.ProjectTaskTypes ADD BackColor INT NULL
	
	  ALTER TABLE ProjectTasks ALTER COLUMN Name VARCHAR(50) NULL
	  ALTER TABLE ProjectTasks ADD Note VARCHAR(1024) NULL
	  ALTER TABLE ProjectTaskTypes ADD ActivityTypeId INT NULL
	  ALTER TABLE dbo.ProjectTaskTypes ADD CONSTRAINT FK_ProjectTaskTypes_ActivityTypes FOREIGN KEY (ActivityTypeId) REFERENCES dbo.ActivityTypes (ActivityTypeId) ON DELETE SET NULL

	 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ProjectTasks_Persons') AND type = 'F') ALTER TABLE dbo.ProjectTasks DROP CONSTRAINT FK_ProjectTasks_Persons
	 IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE  c.name = 'PersonInChargeId' AND o.id = OBJECT_ID(N'dbo.ProjectTasks')) ALTER TABLE ProjectTasks DROP COLUMN PersonInChargeId
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables WHERE  [name]='ProjectTaskMembers')
BEGIN
	PRINT 'Creating table ProjectTaskMembers ...'
	CREATE TABLE ProjectTaskMembers(
				 ProjectTaskMemberId INT IDENTITY(1,1) NOT NULL,
				 ProjectTaskId INT NOT NULL,
				 PersonId INT NOT NULL,
				 ContractId INT NULL,
				 IsInCharge BIT NOT NULL CONSTRAINT DF_ProjectTaskMembers_IsInCharge DEFAULT 0,
				 Note VARCHAR(512) NULL,
				 LM SMALLDATETIME NOT NULL CONSTRAINT DF_ProjectTaskMembers_LM DEFAULT GETDATE(),
				 UM INT NULL,
				 LC SMALLDATETIME NOT NULL CONSTRAINT DF_ProjectTaskMembers_LC DEFAULT GETDATE(),
				 UC INT NULL,
				 CONSTRAINT PK_ProjectTaskMembers PRIMARY KEY NONCLUSTERED (ProjectTaskMemberId) ON [PRIMARY],
				 CONSTRAINT FK_ProjectTaskMembers_ProjectTasks FOREIGN KEY(ProjectTaskId) REFERENCES ProjectTasks (ProjectTaskId),
				 CONSTRAINT FK_ProjectTaskMembers_Persons FOREIGN KEY(PersonId) REFERENCES Persons (PersonId),
				 CONSTRAINT FK_ProjectTaskMembers_Contracts FOREIGN KEY(ContractId) REFERENCES Contracts (ContractId),
		   ) ON [PRIMARY]
	  
		 CREATE CLUSTERED INDEX IX_ProjectTaskMembers_ProjectTaskId ON dbo.ProjectTaskMembers (ProjectTaskId)
		 CREATE UNIQUE INDEX IX_ProjectTaskMembers_ProjectTaskId_PersonId ON dbo.ProjectTaskMembers (ProjectTaskId, PersonId)

		 GRANT UPDATE, INSERT, DELETE ON ProjectTaskMembers to public 
END
GO
IF EXISTS (SELECT * FROM sys.tables WHERE  [name]='ProjectTaskPersons')
BEGIN
	PRINT 'Dropping table ProjectTaskPersons...'
	DROP TABLE ProjectTaskPersons
END
GO


IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_ProjectTasks_PeriodCheck') 
BEGIN 
	Print 'Add CK_ProjectTasks_PeriodCheck'

	ALTER TABLE ProjectTasks WITH CHECK ADD CONSTRAINT CK_ProjectTasks_PeriodCheck CHECK (StartDate <= EndDate)
	ALTER TABLE ProjectTasks CHECK CONSTRAINT CK_ProjectTasks_PeriodCheck

	IF EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.ActivityDepartmentData') AND o.Name = 'IX_ActivityDepartment_ActivityId_DepartmentId') DROP INDEX ActivityDepartmentData.IX_ActivityDepartment_ActivityId_DepartmentId
	IF EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.ActivityDepartmentData') AND o.Name = 'PK_ActivityDepartmentData') 	ALTER TABLE dbo.ActivityDepartmentData	DROP CONSTRAINT PK_ActivityDepartmentData 
	CREATE UNIQUE CLUSTERED INDEX IX_ActivityDepartment_ActivityId_DepartmentId ON ActivityDepartmentData (ActivityId, DepartmentId ) ON [PRIMARY]

	ALTER TABLE ActivityDepartmentData ADD CONSTRAINT PK_ActivityDepartmentData PRIMARY KEY (ActivityDepartmentDataId) ON [PRIMARY]

END 
GO

--#endregion

--#region SH 2020-03-24 Datentyp von EntitlementRules.Days auf float
IF EXISTS (SELECT * FROM sys.columns c INNER JOIN sys.types s ON c.system_type_id = s.system_type_id WHERE  c.object_id = OBJECT_ID('EntitlementRules') AND c.[name] = 'Days' AND s.Name <> 'float')
BEGIN
	PRINT('Changing EntitlementRules.Days to FLOAT ...')
	EXEC DropDefaultConstraint @tablename = 'EntitlementRules', @columnname = 'Days'
	ALTER TABLE [EntitlementRules] ALTER COLUMN [Days] FLOAT NOT NULL
	PRINT('Creating DF_EntitlementRules_Days ...')
	ALTER TABLE [EntitlementRules] ADD  CONSTRAINT [DF_EntitlementRules_Days]  DEFAULT (0) FOR [Days]
END
GO

--#endregion

--#endregion -------- (4.202.x) -------------------------------

--#region ----------- 4.202.x Release Übertrag ----------------

--#region 2020-05-06
DELETE FROM TimeAccountMonthValues WHERE  DataValue = 0 AND IsManualEntry = 0 AND Note IS NULL
UPDATE TimeAccountMonthValues SET DataValue = Round(DataValue, 2)
--#endregion

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

--#region ----------- 4.203.x ---------------------------------

--#region DH 2020-04-03 Module für Vertragsfeldtypen
IF NOT EXISTS (SELECT [name] FROM sys.columns WHERE  object_id = OBJECT_ID('ContractFieldTypes') AND [name] = 'Module')
	BEGIN
		PRINT 'Adding column ContractFieldTypes.Module...'
		ALTER TABLE dbo.ContractFieldTypes ADD
		Module int NOT NULL CONSTRAINT DF_ContractFieldTypes_Module DEFAULT 0;		
	END
GO
IF EXISTS (SELECT * FROM NvData WHERE  ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 203))))
BEGIN
	print 'Updating Moduls';
	DECLARE @tempTable TABLE (   
		module int,
		contractfieldtypeID int
	);
	WITH Bits
			AS ( SELECT   1 AS BitMask
				UNION ALL
				SELECT   2
				UNION ALL
				SELECT   4
				UNION ALL
				SELECT   8
				UNION ALL
				SELECT   16
			    UNION ALL
				SELECT   32
			    UNION ALL
				SELECT   64
			    UNION ALL
				SELECT   128
			    UNION ALL
				SELECT   256
            )
			INSERT INTO @tempTable
			SELECT
				SUM(DISTINCT BitMask), ContractFieldTypeId
			FROM (
				SELECT ct.Module n, cft.ContractFieldTypeId
				FROM ContractTypes ct
				INNER JOIN ContractTypes_ContractFieldTypes ctctf ON ct.ContractTypeId=ctctf.ContractTypeId
				INNER JOIN ContractFieldTypes cft ON cft.ContractFieldTypeId=ctctf.ContractFieldTypeId
            ) AS t 
            JOIN Bits ON t.n & Bits.BitMask > 0 
			GROUP BY ContractFieldTypeId;

			UPDATE cft SET Module= t.Module
			FROM @tempTable t
			INNER JOIN ContractFieldTypes cft ON cft.ContractFieldTypeId = t.contractfieldtypeID
END
GO
--#endregion DH 2020-04-03 Module für Vertragsfeldtypen

--#region TK 2020-04-15
IF NOT EXISTS (SELECT [name] FROM sys.columns WHERE  object_id = OBJECT_ID('ProjectTaskTypes') AND [name] = 'IsMileStone')
BEGIN
	PRINT 'Adding column ProjectTasks.ProjectTaskTypes ...'
	ALTER TABLE ProjectTaskTypes ADD IsMileStone BIT NOT NULL CONSTRAINT DF_ProjectTaskTypes_IsMileStone DEFAULT 0	
END


IF NOT EXISTS (SELECT [name] FROM sys.columns WHERE  object_id = OBJECT_ID('ProductionInSeasons') AND [name] = 'ProductionTemplateId')
BEGIN
	PRINT 'Adding column ProductionInSeasons.ProductionTemplateId ...'
	ALTER TABLE ProductionInSeasons Add ProductionTemplateId INT NULL 
	ALTER TABLE ProductionInSeasons ADD CONSTRAINT FK_ProductionInSeason_ProductionTemplatess FOREIGN KEY (ProductionTemplateId) REFERENCES dbo.ProductionTemplates (ProductionTemplateId) ON DELETE SET NULL
	
	ALTER TABLE ProjectTasks ADD IsOutsourced INT NOT NULL CONSTRAINT DF_ProjectTasks_IsOutsourced DEFAULT 0	
	  
	ALTER TABLE ProjectTaskDependencies DROP CONSTRAINT FK_ProjectTaskDependencies_ProductionTasksPredecessor
	ALTER TABLE ProjectTaskDependencies  WITH CHECK ADD CONSTRAINT FK_ProjectTaskDependencies_ProductionTasksPredecessor 
		FOREIGN KEY(PredecessorProductionTaskId) REFERENCES ProjectTasks (ProjectTaskId) ON DELETE CASCADE

END
GO
EXEC DropColumn 'ProjectTasks' ,'IsSimulation'
GO

IF NOT EXISTS (SELECT [name] FROM sys.columns WHERE  object_id = OBJECT_ID('ProjectTaskDependencies') AND [name] = 'LagDays')
BEGIN
	PRINT 'Adding column ProjectTaskDependencies.LagDays ...'
	
	EXEC sp_rename 'ProjectTaskDependencies.PredecessorProductionTaskId', 'PredecessorProjectTaskId', 'COLUMN'
	EXEC sp_rename 'ProjectTaskDependencies.FollowerProductionTaskId', 'SuccessorProjectTaskId', 'COLUMN'
	EXEC sp_rename 'FK_ProjectTaskDependencies_ProductionTasksPredecessor', 'FK_ProjectTaskDependencies_ProjectTasksPredecessor'
	EXEC sp_rename 'FK_ProjectTaskDependencies_ProductionTasksFollower', 'FK_ProjectTaskDependencies_ProjectTasksSuccessor'

	ALTER TABLE ProductionTaskDependencyTemplates ADD DependencyType TINYINT NOT NULL CONSTRAINT DF_ProductionTaskDependencyTemplates_DependencyType DEFAULT 0	
	ALTER TABLE ProductionTaskDependencyTemplates ADD LagDays INT NOT NULL CONSTRAINT DF_ProductionTaskDependencyTemplates_LagDays DEFAULT 0	
	ALTER TABLE ProjectTaskDependencies ADD DependencyType TINYINT NOT NULL CONSTRAINT DF_ProjectTaskDependencies_DependencyType DEFAULT 0	
	ALTER TABLE ProjectTaskDependencies ADD LagDays INT NOT NULL CONSTRAINT DF_ProjectTaskDependencies_LagLagDays DEFAULT 0
	
	ALTER TABLE ProjectTasks WITH CHECK ADD  CONSTRAINT FK_ProjectTasks_ProjectTasks FOREIGN KEY(ParentProjectTaskId) REFERENCES ProjectTasks (ProjectTaskId)
END
GO
--#endregion 

--#region
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityTypes') AND [name] = 'IsMileStone')
BEGIN
	ALTER TABLE ActivityTypes ADD IsMileStone BIT NOT NULL CONSTRAINT DF_ActivityTypes_IsMileStone DEFAULT 0
	EXEC ('UPDATE ActivityTypes SET IsMileStone = 1 WHERE  Category IN (410, 420, 810, 2010)')
END
--#endregion

--#region DH 2020-05-07 Berechtigungen auf Aktivitätstypen
GRANT INSERT, UPDATE, DELETE ON dbo.Activitytypes TO Masteradmin,StatisterieAdmin, ChorAdmin, OrchesterAdmin
GO
--#endregion DH 2020-05-07 Aktivitätstypen auch im Allgemeinen Bereich und Statisterie

--#region 2020-05-14
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Qualification_Person') AND [name] = 'ValidUntil')
BEGIN
	Print 'Adding Qualification_Person.ValidUntil ...'
	ALTER TABLE Qualification ALTER COLUMN  NAME VARCHAR(50) NOT NULL
	ALTER TABLE Qualification_Person ADD ValidUntil DATE NULL
	ALTER TABLE Qualification_Person ALTER COLUMN DateOfQualification DATE NULL
	EXEC ('ALTER TABLE Qualification_Person WITH CHECK ADD CONSTRAINT CK_Qualification_Person_PeriodCheck CHECK (DateOfQualification <= ValidUntil)')
	EXEC ('ALTER TABLE Qualification_Person CHECK CONSTRAINT CK_Qualification_Person_PeriodCheck')
END
IF NOT EXISTS (SELECT * FROM sys.columns c INNER JOIN sys.types s ON c.system_type_id = s.system_type_id WHERE  c.object_id = OBJECT_ID('PersonAddress') AND c.[name] = 'IsTaxDomicile')
BEGIN
	PRINT('Create IsTaxDomicile')

	UPDATE PlanColumnContent SET ContentSettings = Convert(XML, Replace(Convert(VARCHAR(max),  ContentSettings),'&lt;eStatusStatus&gt;approved&lt;/eStatusStatus&gt;',
		 '&lt;eStatusStatus&gt;approved&lt;/eStatusStatus&gt;
		  &lt;eStatusStatus&gt;conditionally&lt;/eStatusStatus&gt;')) 
		FROM PlanColumnContent ps
			INNER JOIN PlanColumns sp ON ps.PlanColumnId = sp.PlanColumnId
			INNER JOIN PlanLayouts p ON sp.PlanLayoutId = p.PlanLayoutId
		WHERE PlanLayoutType IN (30, 120, 130, 160)
			AND NOT ContentSettings IS NULL
			AND CONVERT(VARCHAR(max), ContentSettings) LIKE '%PersStatusListConverterFieldSettings%'
			AND CONVERT(VARCHAR(max), ContentSettings) LIKE '%&lt;eStatusStatus&gt;approved&lt;/eStatusStatus&gt;%'
			AND NOT CONVERT(VARCHAR(max), ContentSettings) LIKE '%&lt;eStatusStatus&gt;conditionally&lt;/eStatusStatus&gt;%'

	ALTER TABLE PersonAddress ALTER COLUMN Note VARCHAR(1024)
	ALTER TABLE PersonAddress ADD IsTaxDomicile BIT  NOT NULL CONSTRAINT DF_PersonAddress_IsTaxDomicile DEFAULT 0
END
GO
PRINT('Alter ABPersAdresse')
GO
ALTER VIEW ABPersAdresse
	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 '***' ELSE pa.CountryCode END AS CountryCode,
			  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.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 Username = CURRENT_USER 
						 GROUP BY pp.PersonId) AS sub ON pa.PersonId = sub.PersonId
GO
PRINT('Alter ABPersAdresse_Update')
GO
ALTER VIEW ABPersAdresse_Update
AS
	SELECT Pa.*
	FROM PersonAddress as Pa
	WHERE PersonId IN ( SELECT  pp.PersonId
						FROM PersonPermissions AS PP
							INNER JOIN Permissions AS UG ON pp.OwnerGroupId =  UG.OwnerGroupId 
							INNER JOIN Users ON Ug.Userid = Users.Userid 
						WHERE ug.HasWritePermission = 1
							  AND (IsSecret = 0 OR ug.OptionsFlag&pp.OptionsFlag&1 = 1)
							  AND PermissionArea = 0
							  AND pp.HasWritePermission = 1
							  AND Username = CURRENT_USER )
WITH CHECK OPTION
GO
--#endregion
--#region 2020-06-18 Doppelte global Settings entfernen
DELETE FROM Settings WHERE  SettingId IN 
			(
			SELECT SettingId FROM Settings INNER JOIN 
				(SELECT Section, keyName, ISNULL(Settings.Context,'') AS c, MIN(SettingId) AS I FROM Settings WHERE  IsGlobalSetting = 1 GROUP BY Section, keyName, ISNULL(Settings.Context,'') HAVING COUNT(*) > 1) AS a 
				ON Settings.Section = a.Section AND Settings.keyName = a.keyName AND ISNULL(Settings.Context,'') = a.c AND Settings.SettingId <> a.i

			)
GO
IF NOT EXISTS (SELECT * FROM  sysindexes o WHERE  o.id = OBJECT_ID(N'dbo.Settings') AND o.Name = 'UQ_Settings_Section_KeyName_Context') 
	CREATE UNIQUE NONCLUSTERED INDEX UQ_Settings_Section_KeyName_Context ON Settings (Section, KeyName, Context) WHERE  (IsGlobalSetting=(1)) ON [PRIMARY]
GO

--#endregion
--#region 2020-05-14 BasicCast Period
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'StartDate' AND o.id = OBJECT_ID('BasicCast'))	
BEGIN
	Print 'create IX_ProductionInSeasons_SeasonId_ProductionId'

	DROP INDEX ProductionInSeasons.IX_ProductionInSeasons_SeasonId_ProductionId
	DROP INDEX ProductionInSeasons.IX_SPZNR
	CREATE UNIQUE CLUSTERED INDEX IX_ProductionInSeasons_SeasonId_ProductionId ON ProductionInSeasons (SeasonId, ProductionId) 


	Print 'drop BasicCast.IX_BasicCast_Seasons_Roles'
	DROP INDEX BasicCast.IX_BasicCast_Seasons_Roles

	--#region Doubletten (gleiche Person in Rolle/Spielzeit) aus BasicCast entfenen
	DECLARE @tempTable TABLE (
		minid INT,
		RoleId INT,
		SeasonId INT,
		PersonId INT,
		GroupPosition INT,
		UnderstudyPosition INT
	)

	INSERT INTO @tempTable (minid, RoleId, SeasonId, PersonId, GroupPosition, UnderstudyPosition) 
		SELECT MIN(BasicCastId), RoleId, ISNULL(SeasonId,0) as si, PersonId, GroupPosition, UnderstudyPosition 
		FROM BasicCast 
		GROUP BY BasicCast.RoleId, ISNULL(SeasonId,0), PersonId, GroupPosition, UnderstudyPosition
		HAVING COUNT(*) > 1

	DELETE FROM  BasicCast WHERE  EXISTS (SELECT * FROM  @tempTable AS b 
		WHERE b.RoleId = BasicCast.RoleId 
			AND b.SeasonId = ISNULL(BasicCast.SeasonId, 0) 
			AND b.PersonId = BasicCAst.PersonId 
			AND b.GroupPosition = BasicCast.GroupPosition 
			AND b.UnderstudyPosition = BasicCast.UnderstudyPosition) 
			AND BasicCast.BasicCastId NOT IN (SELECT MinId FROM @tempTable)
		
	--#endregion Doubletten (gleiche Person in Rolle/Spielzeit) aus BasicCast entfenen

	--#region Start&Ende
	Print 'add BasicCast Start&Ende'
	ALTER TABLE BasicCast ADD StartDate DATE
	ALTER TABLE BasicCast ADD EndDate DATE
		
	EXEC ('ALTER TABLE BasicCast WITH CHECK ADD CONSTRAINT CK_BasicCast_PeriodCheck CHECK (StartDate <= EndDate)')
	EXEC ('ALTER TABLE BasicCast CHECK CONSTRAINT CK_BasicCast_PeriodCheck')
	
	EXEC ('UPDATE BasicCast SET BasicCast.StartDate = Seasons.StartDate, BasicCast.EndDate = Seasons.EndDate FROM Basiccast INNER JOIN Seasons ON Basiccast.SeasonId = Seasons.SeasonId ')
	
	--#endregion Start&Ende

	--#region Besetzungen entfernen die nicht zu den Einstelunungen "In Spielzeit passen"
	DECLARE @para VARCHAR(1)
	SET @para = '0'
	SELECT @para = Settings.DataValue  FROM Settings WHERE  Settings.Section = 'BasicCast' AND keyName = 'Season'

	IF (@para = '0')
	BEGIN 
		Print 'AllInInvariantSeason'
		DELETE FROM BasicCast WHERE  BasicCastID IN (SELECT BasicCastID FROM BasicCast INNER JOIN Roles ON  BasicCast.RoleId = Roles.RoleId WHERE  NOT BasicCast.SeasonId IS NULL)
	END

	IF (@para = '1')
	BEGIN 
		Print 'AllInSeason'
		DELETE FROM BasicCast WHERE  BasicCastID IN (SELECT BasicCastID FROM BasicCast INNER JOIN Roles ON  BasicCast.RoleId = Roles.RoleId WHERE  BasicCast.SeasonId IS NULL)
	END

	IF (@para = '2')
	BEGIN 
		Print 'InSeasonProductionTeamInInvariantSeason'
		DELETE FROM BasicCast WHERE  BasicCastID IN (SELECT BasicCastID FROM BasicCast INNER JOIN Roles ON  BasicCast.RoleId = Roles.RoleId WHERE  (Roles.IsProductionTeam = 1 AND NOT BasicCast.SeasonId IS NULL) OR  (Roles.IsProductionTeam = 0 AND BasicCast.SeasonId IS NULL))
	END
	--#endregion Besetzungen entfernen die nicht zu den Einstelunungen "In Spielzeit passen"

END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'StartDate' AND o.id = OBJECT_ID('BasicCastHistory'))	
BEGIN
	Print 'create IX_ProductionInSeasons_SeasonId_ProductionId'
	--#region BasicCast mit Merkmal IsProductionTeam zusammenfassen
	Print 'merge BasicCast'
	DECLARE @id INT, @roleId INT, @personId INT, @groupPosition INT,@understudyPosition INT,  @start DATE, @end DATE, @oId INT, @oRoleId INT, @oPersonId INT, @oGroupPosition INT, @oUnderstudyPosition INT, @oEnd DATE
	DECLARE cview_cursor CURSOR FOR 

	SELECT BasiccastId, Basiccast.RoleId, PersonId, GroupPosition, UnderstudyPosition, Seasons.StartDate, Seasons.EndDate
	FROM Basiccast 
		INNER JOIN Seasons ON Basiccast.SeasonId = Seasons.SeasonId
		INNER JOIN Roles ON Basiccast.RoleId = Roles.RoleId
	WHERE Roles.IsProductionTeam = 1
	ORDER BY Basiccast.RoleId, PersonId, GroupPosition, UnderstudyPosition, Seasons.StartDate
	
	OPEN cview_cursor 
	FETCH NEXT FROM cview_cursor 
	INTO @id, @roleId, @personId, @groupPosition, @understudyPosition, @start, @end
	WHILE @@FETCH_STATUS = 0 
	BEGIN  
		IF (@oRoleId = @roleId AND @oGroupPosition = @groupPosition AND @oUnderstudyPosition = @understudyPosition AND @oPersonId = @personId AND DATEADD(d,1,@oEnd) = @start)
		BEGIN
			UPDATE Basiccast SET EndDate = @end WHERE  BasiccastId = @oId
			Print 'Delete'
			DELETE FROM Basiccast WHERE  BasiccastId = @id
		END
		ELSE
		BEGIN
			SET @oRoleId = @roleId 
			SET @oPersonId = @personId
			SET @oGroupPosition = @groupPosition
			SET @oUnderstudyPosition = @understudyPosition
			SET @oId = @id
		END
		
		FETCH NEXT FROM cview_cursor 
		INTO @id, @roleId, @personId, @groupPosition, @understudyPosition, @start, @end
	END 
	CLOSE cview_cursor 
	DEALLOCATE cview_cursor 

	--#endregion BasicCast mit Merkmal IsProductionTeam zusammenfassen

	--#region 
	Print 'add IX_BasicCast_Role_StartDate_GroupPosition_UnderstudyPosition_PersonId'
	CREATE UNIQUE CLUSTERED  INDEX IX_BasicCast_Role_StartDate_GroupPosition_UnderstudyPosition_PersonId ON dbo.BasicCast (RoleId, StartDate, GroupPosition, UnderstudyPosition, PersonId)
	
	Print 'add BasicCastHistory StartDate&EndDate'
	ALTER TABLE BasicCastHistory ADD StartDate DATE
	ALTER TABLE BasicCastHistory ADD EndDate DATE

	EXEC ('ALTER TABLE BasicCastHistory WITH CHECK ADD CONSTRAINT CK_BasicCastHistory_PeriodCheck CHECK (StartDate <= EndDate)')
	EXEC ('ALTER TABLE BasicCastHistory CHECK CONSTRAINT CK_BasicCastHistory_PeriodCheck')
	EXEC ('UPDATE BasicCastHistory SET BasicCastHistory.StartDate = Seasons.StartDate, BasicCastHistory.EndDate = Seasons.StartDate FROM BasicCastHistory INNER JOIN Seasons ON BasicCastHistory.SeasonId = Seasons.SeasonId ')

	--#endregion
END
GO

--#endregion 2020-05-14 BasicCast Period

--#region ABBasicCast
PRINT('Alter ABBasicCast')
GO
ALTER VIEW dbo.ABBasicCast
AS
SELECT BasicCastId
      ,bc.RoleId
      ,CASE WHEN bc.IsSecret = 1 AND Ug.OptionsFlag&1 = 0 THEN -1 ELSE bc.PersonId END AS PersonId 
	  ,bc.StartDate
	  ,bc.EndDate
      ,bc.UnderstudyPosition
      ,bc.GroupPosition
      ,bc.IsSecret
      ,bc.Note
      ,bc.LM
      ,bc.UM
      ,bc.LC
      ,bc.UC
FROM  BasicCast AS bc
	INNER JOIN Roles AS R ON bc.RoleId = R.RoleId
	INNER JOIN OwnerGroups AS G ON R.OwnerGroupId = G.OwnerGroupId
	INNER JOIN Permissions AS Ug ON  G.OwnerGroupId = Ug.OwnerGroupId
	INNER JOIN Users ON Ug.Userid = Users.Userid 
WHERE Users.Username = CURRENT_USER AND PermissionArea = 4
GO
PRINT('Alter GetPossibleCastByPeriodInternal')
GO
ALTER PROCEDURE [dbo].[GetPossibleCastByPeriodInternal]
	@start DATE,
	@end DATE,
	@personIds IdFilterTable READONLY,
	@activityTypeIds IdFilterTable READONLY,
	@variantIds IdFilterTable READONLY,
	@understudyPosition TINYINT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @permissionPeriodStart DATETIME
		DECLARE @permissionPeriodEnd DATETIME

		-- *** 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 
			SELECT @start = '30000101'
			SELECT @end = '30000101'
		END
		ELSE
		BEGIN
			IF (@start < @permissionPeriodStart)	BEGIN SELECT @start = @permissionPeriodStart END
			IF (@end > @permissionPeriodEnd)		BEGIN SELECT @end = @permissionPeriodEnd END
		END

	IF  EXISTS(SELECT * FROM @activityTypeIds)
	BEGIN
	--Mit Activitytype Filter
	SELECT DISTINCT d.PersonId, d.ActivityId, c.CastId, d.RoleId, IsMandatory
		FROM (
				SELECT a.ActivityId, bs.RoleId, need.IsMandatory, bs.PersonId
				FROM Activities AS a 
					INNER JOIN ProductionChilds AS st ON a.ProductionId = st.ProductionId
					INNER JOIN Productions AS p ON st.ProductionId = p.ProductionId
					INNER JOIN Roles AS r ON st.ChildProductionId = r.ProductionId
					INNER JOIN BasicCast AS bs ON r.RoleId = bs.RoleId 
												AND bs.UnderstudyPosition < @understudyPosition 
												AND (bs.StartDate <= a.Date OR bs.StartDate IS NULL) AND (bs.EndDate >= a.Date OR bs.EndDate IS NULL)
					INNER JOIN CastRules AS need ON r.RoleTypeId = need.RoleTypeId AND a.ActivityTypeId = need.ActivityTypeId
				WHERE Date BETWEEN @start AND @end AND VariantId IN (SELECT id FROM @variantIds) AND a.Status >= 0
					AND bs.PersonId IN (SELECT Id FROM @personIds)
					AND a.ActivityTypeId IN (SELECT Id FROM @activityTypeIds)
					AND p.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE  UserId = @userId AND PermissionArea = 2)
					AND r.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE  UserId = @userId AND PermissionArea = 4)
			) AS d
			LEFT JOIN ( SELECT * 
						FROM Cast
						WHERE IsCanceled = 0 AND IsCover = 0 ) AS c 
				ON d.ActivityId = c.ActivityId AND d.RoleId = c.RoleId
			WHERE c.CastId IS NULL OR c.PersonId <> d.PersonId
			ORDER BY d.PersonId, d.ActivityId
	END 
	ELSE
	BEGIN
	--Ohne Activitytype Filter
	SELECT DISTINCT d.PersonId, d.ActivityId, c.CastId, d.RoleId, IsMandatory
		FROM (
				SELECT a.ActivityId, a.Date, StartTime, EndTime, bs.RoleId, need.IsMandatory, bs.PersonId
				FROM Activities AS a 
					INNER JOIN ProductionChilds AS st ON a.ProductionId = st.ProductionId
					INNER JOIN Productions AS p ON st.ProductionId = p.ProductionId
					INNER JOIN Roles AS r ON st.ChildProductionId = r.ProductionId
					INNER JOIN BasicCast AS bs ON r.RoleId = bs.RoleId 
												AND bs.UnderstudyPosition < @understudyPosition 
												AND (bs.StartDate <= a.Date OR bs.StartDate IS NULL) AND (bs.EndDate >= a.Date OR bs.EndDate IS NULL)
					INNER JOIN CastRules AS need ON r.RoleTypeId = need.RoleTypeId AND a.ActivityTypeId = need.ActivityTypeId
				WHERE Date BETWEEN @start AND @end AND VariantId IN (SELECT id FROM @variantIds) AND a.Status >= 0
					AND bs.PersonId IN (SELECT Id FROM @personIds)
					AND p.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE  UserId = @userId AND PermissionArea = 2)
					AND r.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE  UserId = @userId AND PermissionArea = 4)
			) AS d
			LEFT JOIN ( SELECT * 
						FROM Cast 
						WHERE IsCanceled = 0 AND IsCover = 0 ) AS c 
				ON d.ActivityId = c.ActivityId AND d.RoleId = c.RoleId  
			WHERE c.CastId IS NULL OR c.PersonId <> d.PersonId
			ORDER BY d.PersonId, d.ActivityId
	END
END
GO
PRINT('Alter GetPossibleCastByPeriod')
GO
ALTER PROCEDURE [dbo].[GetPossibleCastByPeriod]
	@start DATE,
	@end DATE,
	@personIds IdFilterTable READONLY,
	@activityTypeIds IdFilterTable READONLY,
	@variantIds IdFilterTable READONLY,
	@understudyPosition TINYINT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @userId INT
	SELECT @userId = UserId FROM Users WHERE  UserName  = CURRENT_USER
	EXECUTE GetPossibleCastByPeriodInternal @start, @end, @personIds, @activityTypeIds, @variantIds, @understudyPosition, @userId
END
GO

--#endregion ABBasicCast


--#region 2020-05-26
IF  EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'OwnerGroupId' AND o.id = OBJECT_ID('ProjectTaskTypes'))	
BEGIN
	ALTER TABLE WorkAreas ADD IsInactive BIT NOT NULL CONSTRAINT DF_WorkAreas_IsInactive DEFAULT 0

	PRINT('Adding column ProjectTasks.OwnerGroupId ...')
	ALTER TABLE ProjectTasks ADD OwnerGroupId INT NULL
	EXEC ('UPDATE ProjectTasks SET OwnerGroupId = 1')
	ALTER TABLE ProjectTasks ALTER COLUMN OwnerGroupId INT NOT NULL
	ALTER TABLE ProjectTasks ADD CONSTRAINT FK_ProjectTasks_OwnerGroups FOREIGN KEY (OwnerGroupId) REFERENCES dbo.OwnerGroups (OwnerGroupId)

	PRINT('Adding column ProjectTaskTypes.Module ...')
	ALTER TABLE ProjectTaskTypes ADD Module TINYINT NOT NULL CONSTRAINT DF_ProjectTaskTypes_Module DEFAULT 0
	EXEC ('UPDATE ProjectTaskTypes SET Module = 1')

	EXEC sp_rename  'ProjectTasks.ExtraWorkingMinutes', 'BaselineWorkingMinutes', 'COLUMN'
	
	PRINT('Adding columns in Scenes ...')
	ALTER TABLE Scenes ADD DurationSeconds SMALLINT NOT NULL CONSTRAINT DF_Scenes_DurationSeconds DEFAULT 0	
	ALTER TABLE Scenes ADD PageInPianoScore VARCHAR(10) NULL
	ALTER TABLE Scenes ADD PositionInAct SMALLINT NOT NULL CONSTRAINT DF_Scenes_PositionInAct DEFAULT 0		
	ALTER TABLE Scenes ADD Note VARCHAR(1024)	
	
	PRINT('Adding columns in Roles_Scenes ...')
	ALTER TABLE Roles_Scenes ADD Note VARCHAR(1024) NULL
	ALTER TABLE Roles_Scenes ADD IsSilent BIT NOT NULL CONSTRAINT DF_Roles_Scenes_IsSilent DEFAULT 0	

	GRANT UPDATE, INSERT, DELETE ON ProductionTaskDependencyTemplates TO DispoAdmin
	GRANT UPDATE, INSERT, DELETE ON ProductionTaskTemplates TO DispoAdmin
	GRANT UPDATE, INSERT, DELETE ON ProductionTemplates TO DispoAdmin
	GRANT UPDATE, INSERT, DELETE ON ProjectTaskTypes TO DispoAdmin
		
	GRANT UPDATE, INSERT, DELETE ON ProductionTaskDependencyTemplates TO PersoAdmin
	GRANT UPDATE, INSERT, DELETE ON ProductionTaskTemplates TO PersoAdmin
	GRANT UPDATE, INSERT, DELETE ON ProductionTemplates TO PersoAdmin
	GRANT UPDATE, INSERT, DELETE ON ProjectTaskTypes TO PersoAdmin
	
	GRANT SELECT ON ProjectTaskDependencies TO public
	GRANT SELECT ON ProjectTasks TO public
	GRANT SELECT ON ProjectTaskMembers TO public

	REVOKE UPDATE, INSERT, DELETE ON ProjectTaskDependencies TO public
	REVOKE UPDATE, INSERT, DELETE ON ProjectTasks TO public
	REVOKE UPDATE, INSERT, DELETE ON ProjectTaskMembers TO public
	
	ALTER TABLE ProjectTaskTypes DROP COLUMN OwnerGroupId

END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.HDMZr') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE HDMZr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.PlanZR') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE PlanZR
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.zzzvertragbackup') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE zzzvertragbackup
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.AbProjectTasks_Update')) DROP VIEW dbo.AbProjectTasks_Update
GO
Print 'Alter AbProjectTasks_Update'
GO
CREATE VIEW dbo.AbProjectTasks_Update
AS
	SELECT      pt.*
	FROM        ProjectTasks AS pt 
	WHERE pt.OwnerGroupId IN (  SELECT OwnerGroupId 
								FROM Permissions AS Ug   
									INNER JOIN  Users ON Ug.Userid  = Users.Userid 
								WHERE  ug.PermissionArea = 20 AND ug.HasWritePermission = 1 AND Users.Username = CURRENT_USER)
	WITH CHECK OPTION

GO
Print 'Alter AbProjectTaskMembers_Update'
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.AbProjectTaskMembers_Update')) DROP VIEW dbo.AbProjectTaskMembers_Update
GO
CREATE VIEW dbo.AbProjectTaskMembers_Update
AS
	SELECT      ptm.*
	FROM        ProjectTaskMembers AS ptm 
	WHERE ptm.ProjectTaskId IN (SELECT ProjectTaskId FROM ProjectTasks AS pt 
									INNER JOIN Permissions AS Ug ON pt.OwnerGroupId = ug.OwnerGroupId
									INNER JOIN Users ON Ug.Userid  = Users.Userid 
								WHERE  ug.PermissionArea = 20 AND ug.HasWritePermission = 1 AND Users.Username = CURRENT_USER)
	WITH CHECK OPTION
GO
Print 'Alter AbProjectTaskDependencies_Update'
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.AbProjectTaskDependencies_Update')) DROP VIEW dbo.AbProjectTaskDependencies_Update
GO
CREATE VIEW dbo.AbProjectTaskDependencies_Update
AS
	SELECT      ptd.*
	FROM        ProjectTaskDependencies AS ptd 
	WHERE ptd.PredecessorProjectTaskId IN (SELECT ProjectTaskId FROM ProjectTasks AS pt 
												INNER JOIN Permissions AS Ug ON pt.OwnerGroupId = ug.OwnerGroupId
												INNER JOIN Users ON Ug.Userid  = Users.Userid 
											WHERE  ug.PermissionArea = 20 AND ug.HasWritePermission = 1 AND Users.Username = CURRENT_USER)
		OR  ptd.SuccessorProjectTaskId IN (SELECT ProjectTaskId FROM ProjectTasks AS pt 
												INNER JOIN Permissions AS Ug ON pt.OwnerGroupId = ug.OwnerGroupId
												INNER JOIN Users ON Ug.Userid  = Users.Userid 
											WHERE  ug.PermissionArea = 20 AND ug.HasWritePermission = 1 AND Users.Username = CURRENT_USER)
	WITH CHECK OPTION
GO

GRANT UPDATE, INSERT, DELETE ON AbProjectTaskDependencies_Update TO public
GRANT UPDATE, INSERT, DELETE ON AbProjectTasks_Update TO public
GRANT UPDATE, INSERT, DELETE ON AbProjectTaskMembers_Update TO public
GRANT UPDATE, INSERT, DELETE ON BudgetItems TO MasterAdmin

--SELECT o.name,  c.*, o.*  FROM sys.columns c INNER JOIN sys.types s ON c.system_type_id = s.system_type_id INNER JOIN sys.sysobjects o ON  c.object_id = o.id   WHERE  s.name = 'DateTime' AND xtype = 'U'
ALTER TABLE PlanLayoutPermissions ALTER COLUMN [Start] DATE NOT NULL
ALTER TABLE PlanLayoutPermissions ALTER COLUMN [End] DATE NOT NULL
ALTER TABLE PlanLayoutPermissions ALTER COLUMN [ShowHistorySince] DATE NULL

ALTER TABLE CastRepertoire ALTER COLUMN LastPerformanceDate DATE NULL

ALTER TABLE ShiftTypes ALTER COLUMN StartTimeDefault1 SMALLDATETIME NULL
ALTER TABLE ShiftTypes ALTER COLUMN StartTimeDefault2 SMALLDATETIME NULL
ALTER TABLE ShiftTypes ALTER COLUMN StartTimeDefault3 SMALLDATETIME NULL
ALTER TABLE ShiftTypes ALTER COLUMN EndTimeDefault1 SMALLDATETIME NULL
ALTER TABLE ShiftTypes ALTER COLUMN EndTimeDefault2 SMALLDATETIME NULL
ALTER TABLE ShiftTypes ALTER COLUMN EndTimeDefault3 SMALLDATETIME NULL

ALTER TABLE WorkVersions ALTER COLUMN Duration SMALLDATETIME NULL

ALTER TABLE OwnerGroups ALTER COLUMN ActivityLockingDate DATE NULL
ALTER TABLE OwnerGroups ALTER COLUMN CountDownDate DATE NULL
ALTER TABLE OwnerGroups ALTER COLUMN WebPublishLockingDate DATE NULL

ALTER TABLE CashCommandPos ALTER COLUMN MaintenanceFrom DATE NULL
ALTER TABLE CashCommandPos ALTER COLUMN MaintenanceUntil DATE NULL

ALTER TABLE PlanExport ALTER COLUMN ExportPeriodStart DATE NULL
ALTER TABLE PlanExport ALTER COLUMN ExportPeriodEnd DATE NULL
ALTER TABLE PlanExport ALTER COLUMN CastExportPeriodStart DATE NULL
ALTER TABLE PlanExport ALTER COLUMN CastExportPeriodEnd DATE NULL


ALTER TABLE ContractWorkingTimeModels ALTER COLUMN StartDate DATE NULL
ALTER TABLE ContractWorkingTimeModels ALTER COLUMN EndDate DATE NULL

ALTER TABLE ResourceObjectMonetaryValues ALTER COLUMN [Start] DATE NULL
ALTER TABLE ResourceObjectMonetaryValues ALTER COLUMN [End] DATE NULL

ALTER TABLE ResourceObjectInsurances ALTER COLUMN [Start] DATE NULL
ALTER TABLE ResourceObjectInsurances ALTER COLUMN [End] DATE NULL

ALTER TABLE ContractTracking ALTER COLUMN TrackingDate DATE NULL
ALTER TABLE ContractTracking ALTER COLUMN DeadlineDate DATE NULL

ALTER TABLE CashFunds ALTER COLUMN OpenedOn DATE NULL
ALTER TABLE CashFunds ALTER COLUMN ClosedOn DATE NULL
ALTER TABLE CashFundPositions ALTER COLUMN PositionDate DATE NULL

ALTER TABLE ContractPartTimePeriods ALTER COLUMN StartDate DATE NULL
ALTER TABLE ContractPartTimePeriods ALTER COLUMN EndDate DATE NULL

ALTER TABLE Tours ALTER COLUMN LastExportDate DATE NULL

ALTER TABLE PlanColumns ALTER COLUMN TimeOfDayStartFilter SMALLDATETIME NULL
ALTER TABLE PlanColumns ALTER COLUMN TimeOfDayEndFilter SMALLDATETIME NULL
GO

IF NOT EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.Holidays') AND o.Name = 'IX_Holidays_Date') 
BEGIN
	PRINT('Add IX_Holidays_Date...')
	ALTER TABLE dbo.Holidays DROP CONSTRAINT PK_Holidays
	IF EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.Holidays') AND o.Name = 'FDatum')	DROP INDEX Holidays.FDatum
	ALTER TABLE Holidays ALTER COLUMN [Date] DATE NOT NULL
	CREATE UNIQUE CLUSTERED INDEX IX_Holidays_Date ON Holidays ([Date]) 
	ALTER TABLE Holidays ADD CONSTRAINT PK_Holidays PRIMARY KEY (HolidayId) ON [PRIMARY]
	--Testdaten entfernen
	DELETE FROM ProductionTaskDependencyTemplates
	DELETE FROM ProductionTaskTemplates
	DELETE FROM ProjectTaskTypes
END
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ProjectTaskTypes_Variants') AND type = 'F') ALTER TABLE dbo.ProjectTaskTypes DROP CONSTRAINT FK_ProjectTaskTypes_Variants
GO
DropColumn 'ProjectTaskTypes','VariantId'
GO
IF NOT EXISTS (SELECT [name] FROM sys.columns WHERE  object_id = OBJECT_ID('ProjectTasks') AND [name] = 'VariantId')
BEGIN
	PRINT 'Adding column ProjectTasks.VariantId ...'
	ALTER TABLE ProjectTasks ADD VariantId INT NOT NULL CONSTRAINT DF_ProjectTasks_VarianteId DEFAULT 0
	ALTER TABLE ProjectTasks ADD CONSTRAINT FK_ProjectTasks_Variants FOREIGN KEY (VariantId) REFERENCES dbo.Variants (VariantId)
END
GO

--#endregion 2020-05-26

--#region sh 2020-06-11 CalculateTimeAccountMonthValuesSums gibt auch Korrekturen bei @forwardsOnly = 1 zurück
PRINT 'ALTER CalculateTimeAccountMonthValuesSums'
GO

ALTER PROCEDURE [dbo].[CalculateTimeAccountMonthValuesSums] 
	@ContractIds IdFilterTable READONLY, --vertrags-ids
	@AccountIds IdFilterTable READONLY, --konten-ids
	@startMonth INT, --startmonat
	@endMonth INT, -- endemonat
	@monthValuesUntil INT, --monatswerte bis, null wenn keine monatswerte gewünscht sind
	@forwardsOnly BIT, --nur vorträge
	@addAnnualForwardsOfNextMonth BIT, --jahresvortrag folgeperiode hinzufügen
	@nextMonth INT, --monat in dem folgeperiode anfängt, folgemonat von endMonth
	@correctionsOnly BIT = 0 --nur korrekturen
AS
BEGIN
	DECLARE @permissions AS TABLE
	(OwnerGroupId INT, PermissionArea INT);

	INSERT INTO @permissions
	SELECT p.OwnerGroupId, p.PermissionArea
	FROM [permissions] p 
	INNER JOIN Users u ON u.UserID = p.UserID
	WHERE u.UserName = CURRENT_USER AND p.PermissionArea IN (1,5)


	SELECT
		zm.ContractId,
		zm.TimeAccountId, 
		CASE WHEN @correctionsOnly = 1 THEN CAST(0 AS FLOAT) ELSE SUM(CASE WHEN ZmType = 16 THEN zm.DataValue ELSE 0 END) END AS Annual, 
		CASE WHEN @correctionsOnly = 1 THEN CAST(0 AS FLOAT) ELSE SUM(CASE WHEN ZmType = 32 THEN zm.DataValue ELSE 0 END) END AS Forward, 
		CASE WHEN @forwardsOnly = 1 OR @correctionsOnly = 1 THEN CAST(0 AS FLOAT) ELSE SUM(CASE WHEN ZmType = 1 AND ((@monthValuesUntil IS NULL AND zm.YearMonth = -1) OR (@monthValuesUntil IS NOT NULL AND zm.YearMonth <= @monthValuesUntil)) AND FromDifferentTimeAccountId IS NULL THEN zm.DataValue ELSE 0 END) END AS MonthValue, 
		CASE WHEN @forwardsOnly = 1 OR @correctionsOnly = 1 THEN CAST(0 AS FLOAT) ELSE SUM(CASE WHEN ZmType = 1 AND NOT FromDifferentTimeAccountId IS NULL THEN zm.DataValue ELSE 0 END) END AS MonthValueFromOtherAccount,
		CASE WHEN @forwardsOnly = 1 AND @correctionsOnly = 0 THEN CAST(0 AS FLOAT) ELSE SUM(CASE WHEN ZmType <> 2 AND ZmType < 16 AND ZmType > 1 THEN zm.DataValue ELSE 0 END) END AS Correction,
		CASE WHEN @forwardsOnly = 1 OR @correctionsOnly = 1 THEN CAST(0 AS FLOAT) ELSE SUM(CASE WHEN ZmType = 64 THEN zm.DataValue ELSE 0 END) END AS TransferTo, 
		CASE WHEN @forwardsOnly = 1 OR @correctionsOnly = 1 THEN CAST(0 AS FLOAT) ELSE SUM(CASE WHEN ZmType = 128 THEN zm.DataValue ELSE 0 END) END AS TransferFrom 
	FROM [TimeAccountMonthValues] AS zm 
	WHERE 
		zm.ContractId IN (
			SELECT c.Id FROM @ContractIds c
			INNER JOIN Contracts cc ON c.Id = cc.ContractId
			INNER JOIN ContractDepartments cd ON cc.ContractId = cd.ContractId
			INNER JOIN WorkAreas wa ON wa.WorkAreaId = cd.WorkAreaId
			INNER JOIN Departments AS d ON d.DepartmentId = wa.DepartmentId
			INNER JOIN @permissions AS p ON 
				(p.OwnerGroupId = cc.OwnerGroupId AND p.PermissionArea = 1) 
				OR (p.OwnerGroupId = d.OwnerGroupId AND p.PermissionArea = 5)
		)
		AND zm.TimeAccountId IN (SELECT Id FROM @AccountIds)
		AND (
			(zm.YearMonth >= @startMonth AND zm.YearMonth <= @endMonth)
			OR (@addAnnualForwardsOfNextMonth = 1 AND zm.YearMonth = @nextMonth AND ZmType = 16)
		)
	GROUP BY zm.ContractId, zm.TimeAccountId
END
GO
--#endregion sh 2020-06-11 CalculateTimeAccountMonthValuesSums gibt auch Korrekturen bei @forwardsOnly = 1 zurück
--#region 2020-06-18 Doppelte global Settings entfernen
DELETE FROM Settings WHERE  SettingId IN 
			(
			SELECT SettingId FROM Settings INNER JOIN 
				(SELECT Section, keyName, ISNULL(Settings.Context,'') AS c, MIN(SettingId) AS I FROM Settings WHERE  IsGlobalSetting = 1 GROUP BY Section, keyName, ISNULL(Settings.Context,'') HAVING COUNT(*) > 1) AS a 
				ON Settings.Section = a.Section AND Settings.keyName = a.keyName AND ISNULL(Settings.Context,'') = a.c AND Settings.SettingId <> a.i

			)
GO
--#endregion

--#region
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityScenes') AND name = 'EndOffset')
BEGIN
	PRINT 'Adding column ActivityScenes.StartOffset ...'
	ALTER TABLE ActivityScenes ADD StartOffset SMALLINT NOT NULL CONSTRAINT DF_ActivityScenes_StartOffset DEFAULT 0
	ALTER TABLE ActivityScenes ADD EndOffset SMALLINT NOT NULL CONSTRAINT DF_ActivityScenes_EndOffsett DEFAULT 0

	ALTER TABLE dbo.ActivityScenes DROP CONSTRAINT PK_ActivityScenes
	CREATE CLUSTERED INDEX IX_ActivityScenes_ActivityId ON ActivityScenes (ActivityId, StartOffset, EndOffset DESC) 
	ALTER TABLE ActivityScenes ADD CONSTRAINT PK_ActivityScenes PRIMARY KEY (ActivityScenesId) ON [PRIMARY]
	ALTER TABLE ActivityScenes DROP CONSTRAINT UQ_ActivityScenes_ActivityId_SceneId_ScheduledBasicCast
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('PersonStatusTypes') AND name = 'CountOnlyFullDays')
BEGIN
	PRINT 'Adding column PersonStatusTypes.CountOnlyFullDays ...'
	ALTER TABLE PersonStatusTypes ADD CountOnlyFullDays BIT NOT NULL CONSTRAINT DF_PersonStatusTypes_CountOnlyFullDays DEFAULT 0
	EXEC ('UPDATE PersonStatusTypes SET CountOnlyFullDays = 1 WHERE  NOT TemplateStartTime IS NULL OR  NOT TemplateEndTime IS NULL')
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ActivityScenes') AND name = 'InAddition')
BEGIN
	PRINT 'Adding column ActivityScenes.InAddition ...'
	ALTER TABLE ActivityScenes ADD InAddition BIT NOT NULL CONSTRAINT DF_ActivityScenes_ActivityScenes DEFAULT 0
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ActivityScenes') AND name = 'InstructionNotes')
BEGIN
	PRINT 'Adding column ActivityScenes.InstructionNotes ...'
	ALTER TABLE ActivityScenes ADD InstructionNotes VARCHAR(MAX) NULL 
END
GO
--#endregion 

--#endregion ----------- 4.203.x ---------------------------------

--#region ----------- 4.203.x Release Übertrag ----------------

--#region Bugfix DeleteProduction 2020-06-24 DH

ALTER PROCEDURE [dbo].[DeleteProduction]
	@ProductionId INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @UserGroup int
SELECT @UserGroup = OwnerGroupId FROM Productions WHERE  ProductionId = @ProductionId

--Berechtigungsprüfung
IF (dbo.GetCurrentPermission(@UserGroup, 3) < 2 ) 
	BEGIN
		SELECT 1 AS Attribut, 'No Permissions' AS MSG
	END
ELSE
	BEGIN 
		IF (@ProductionId = 0)	
		BEGIN
			SELECT 0 AS Attribut,'Keine gültige Produktion übergeben (Id 0)' AS MSG
		END
		ELSE
		BEGIN
			DELETE FROM BasicCast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM ContractEngagements WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM CastRepertoire WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM ContractDateConfirmations WHERE  ContractDateId IN (SELECT ContractDateId FROM ContractDates 
				WHERE CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)))
			DELETE FROM ContractDates WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastBonus WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CashCommandPos_Cast WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastCredits WHERE  ServiceCastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastCredits WHERE  ReplacementCastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			UPDATE ContractDates SET CastId = NULL WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM TechnicalJobs WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM RoleBonus WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Role_Season WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Roles_Scenes WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Qualification_Role WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Roles WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)	
			DELETE FROM ProductionChilds WHERE  ProductionId = @ProductionId
			DELETE FROM ProductionChilds WHERE  ChildProductionId = @ProductionId
			DELETE FROM ActivityScenes WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDepartmentData WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM TechnicalJobs WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityCosts WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDetails WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM JourneyTimetables WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM CostUnitWorkingTimes WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDetails WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivitySubscriptions WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM Activities WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			UPDATE Activities SET LinkedActivityId = NULL WHERE  LinkedActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ProductionDetails WHERE  ProductionId = @ProductionId
			DELETE FROM ProductionInSeasons WHERE  ProductionId = @ProductionId
		END
	END
END
GO
--#endregion bugfix DeleteProduction 2020-06-24 DH

--#region 20200703 Export-View
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.Extern_Activities')) DROP VIEW dbo.Extern_Activities
GO

CREATE VIEW dbo.Extern_Activities
AS
	SELECT	
	ActivityId,
	Date,
	CAST(Date AS DATETIME) + CAST(StartTime AS DATETIME) AS [Start],
	PrintStartTime,
	CASE WHEN StartTime <= EndTime THEN CAST(Date AS DATETIME) + CAST(EndTime AS DATETIME) 
		ELSE DATEADD(DAY,1 , CAST(Date AS DATETIME)) + CAST(EndTime AS DATETIME) END AS [End],
	PrintEndTime, 
	act.ProductionId,
	CASE WHEN LEFT(ISNULL(p.Title,''),1) = '*' THEN ArtisticNote ELSE p.Title END AS Title,
	act.LocationId AS StageId,
	lo.Name AS StageName,
	lo.ShortName AS StageShortName,
	act.ActivityTypeId,
	att.Name AS ActivityTypeName,
	att.ShortName AS ActivityTypeShortName,
	CASE WHEN att.Category >= 2000 THEN 1 ELSE 0 END AS IsPerformance,
	CASE WHEN att.Category >= 1000 THEN 1 ELSE 0 END AS IsPublicEvent,
	CASE WHEN Status < 0 THEN 1 ELSE 0 END AS IsCanceled,
	CASE WHEN Status = 4 THEN 1 ELSE 0 END AS IsOptional,
	CASE WHEN Status IN (2, 6) THEN 1 ELSE 0 END AS IsUnconfirmed,
	CASE WHEN LEFT(ISNULL(p.Title,''),1) = '*' THEN NULL ELSE ArtisticNote END AS Note,
	TechnicalNote,
	LinkedActivityId,
	act.LM AS LastModified
	FROM			Activities act
		INNER JOIN	Seasons				AS s  ON act.Date <= s.EndDate AND act.Date >= s.StartDate
		INNER JOIN	SeasonPermissions   AS us ON s.SeasonId   = us.SeasonId
		INNER JOIN	Users				AS u  ON us.UserId = u.UserID
		INNER JOIN	Productions         AS p  ON act.ProductionId = p.ProductionId
		INNER JOIN	ActivityTypes       AS att ON act.ActivityTypeId = att.ActivityTypeId
		INNER JOIN	Locations           AS lo ON act.LocationId = lo.LocationId
	WHERE
		u.UserName  = CURRENT_USER AND act.IsSecret = 0 AND VariantId = 0
GO
if NOT EXISTS (SELECT * FROM dbo.sysusers WHERE  name = 'DispoExport')
BEGIN
	PRINT('Add DispoExport...')
	EXEC sp_addrole @rolename = N'DispoExport'
END
GRANT SELECT ON Extern_Activities TO DispoExport
GO
--#endregion

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

--#region ----------- 4.204.x ---------------------------------

--#region DH 2020-06-29 Varianten mit Abwesenheitsüberschneidung

IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Variants') AND [name] = 'CheckOverlap')
BEGIN
	PRINT('Adding column Departments.IsInactive ...')
ALTER TABLE Variants ADD CheckOverlap BIT NOT NULL CONSTRAINT DF_Variants_CheckOverlap DEFAULT 0  
END
GO
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_Variants_StartDateEndDate') 
BEGIN
ALTER TABLE dbo.[Variants]   
   ADD CONSTRAINT CK_Variants_StartDateEndDate  
   CHECK (StartDate is null or StartDate <= EndDate OR EndDate is Null );  
   ALTER TABLE Variants  
	WITH CHECK CHECK CONSTRAINT CK_Variants_StartDateEndDate; 
END
--#endregion DH 2020-06-29 

--#region SH 2020-07-03 Neue Spalte CostUnits.FixedBudgetItemId

IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('CostUnits') AND [name] = 'FixedBudgetItemId')
BEGIN
	PRINT('Adding CostUnits.FixedBudgetItemId ...')
	ALTER TABLE dbo.CostUnits ADD FixedBudgetItemId INT NULL
	CONSTRAINT FK_CostUnits_BudgetItems FOREIGN KEY (FixedBudgetItemId) REFERENCES BudgetItems (BudgetItemId)
END
GO


--#endregion SH 2020-07-03 Neue Spalte CostUnits.FixedBudgetItemId

--#region SH 2020-07-13 Neue Spalte WorkVersions.RoyaltyChoreographyPercentage

IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('WorkVersions') AND [name] = 'RoyaltyChoreographyPercentage')
BEGIN
	PRINT('Adding WorkVersions.RoyaltyChoreographyPercentage ...')
	ALTER TABLE dbo.WorkVersions ADD RoyaltyChoreographyPercentage FLOAT NULL
END
GO


--#endregion SH 2020-07-03 Neue Spalte WorkVersions.RoyaltyChoreographyPercentage

--#region SH 2020-07-29 Neue Spalte Contracts.IsPrimaryContract

IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('Contracts') AND [name] = 'IsPrimaryContract')
BEGIN
	PRINT('Adding Contracts.IsPrimaryContract ...')
	ALTER TABLE Contracts ADD IsPrimaryContract BIT NOT NULL CONSTRAINT DF_Contracts_IsPrimaryContract DEFAULT 0
	EXEC ('UPDATE Contracts SET IsPrimaryContract = 1 FROM  Contracts c INNER JOIN ContractTypes ct ON c.ContractTypeId = ct.ContractTypeId WHERE  ct.ContractTypesCategory = 255')
END
GO


--#endregion SH 2020-07-29 Neue Spalte Contracts.IsPrimaryContract

--#region  2020-07-31 Bankadresse
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('BankAccounts') AND [name] = 'Street')
BEGIN
	PRINT('Adding BankAccounts.Street ...')
	ALTER TABLE dbo.BankAccounts ADD PostCode VARCHAR(10)
	ALTER TABLE dbo.BankAccounts ALTER COLUMN City NVARCHAR(80)
	UPDATE BankAccounts SET CountryCode = LEFT(CountryCode, 3) WHERE  NOT CountryCode IS NULL AND LEN(CountryCode) > 3
	ALTER TABLE dbo.BankAccounts ALTER COLUMN CountryCode VARCHAR(3)
	ALTER TABLE dbo.BankAccounts ALTER COLUMN AccountHolder NVARCHAR(150)
	UPDATE BankAccounts SET BankName = NULL WHERE  BankName = ''
	UPDATE BankAccounts SET City = NULL WHERE  City = ''
	UPDATE BankAccounts SET CountryCode = NULL WHERE  CountryCode = ''
	UPDATE BankAccounts SET SWIFTNumber = NULL WHERE  SWIFTNumber = ''
	UPDATE BankAccounts SET Iban = NULL WHERE  Iban = ''
	UPDATE BankAccounts SET BankCode = NULL WHERE  BankCode = ''
	UPDATE BankAccounts SET AccountNumber = NULL WHERE  AccountNumber = ''
	UPDATE BankAccounts SET AccountHolder = NULL WHERE  AccountHolder = ''
	UPDATE BankAccounts SET Note = NULL WHERE  Note = ''
	ALTER TABLE dbo.BankAccounts ADD Street NVARCHAR(150)
END
GO
--#endregion

--#region  2020-08-18 Attachments
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_PersonAttachments_PersonId' AND object_id = OBJECT_ID(N'dbo.PersonAttachments') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create PersonAttachments IX_PersonId'
	ALTER TABLE dbo.PersonAttachments DROP CONSTRAINT PK_PersonAttachments
	CREATE CLUSTERED INDEX IX_PersonAttachments_PersonId ON dbo.PersonAttachments (PersonId)
	ALTER TABLE dbo.PersonAttachments ADD CONSTRAINT PK_PersonAttachments PRIMARY KEY  (PersonAttachmentsId) ON [PRIMARY]		
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_ProductionAttachments_ProductionId' AND object_id = OBJECT_ID(N'dbo.ProductionAttachments') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create ProductionAttachments IX_ProductionId'
	ALTER TABLE dbo.ProductionAttachments DROP CONSTRAINT PK_ProductionAttachments
	CREATE CLUSTERED INDEX IX_ProductionAttachments_ProductionId ON dbo.ProductionAttachments (ProductionId)
	ALTER TABLE dbo.ProductionAttachments ADD CONSTRAINT PK_ProductionAttachments PRIMARY KEY  (ProductionAttachmentsId) ON [PRIMARY]		
END
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_ContractDocumentAttachments_ContractDocumentId' AND object_id = OBJECT_ID(N'dbo.ContractDocumentAttachments') AND type_desc = 'CLUSTERED') 
BEGIN
	Print 'Create ContractDocumentAttachments IX_ContractDocumentId'
	ALTER TABLE dbo.ContractDocumentAttachments DROP CONSTRAINT PK_ContractDocumentAttachments
	CREATE CLUSTERED INDEX IX_ContractDocumentAttachments_ContractDocumentId ON dbo.ContractDocumentAttachments (ContractDocumentId)
	ALTER TABLE dbo.ContractDocumentAttachments ADD CONSTRAINT PK_ContractDocumentAttachments PRIMARY KEY  (ContractDocumentAttachmentId) ON [PRIMARY]		
END
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Templates') AND [name] = 'AttachmentId')
BEGIN
	PRINT('Adding Templates.AttachmentId ...')
	ALTER TABLE dbo.Templates ADD AttachmentId INT NULL
		CONSTRAINT FK_Templates_Attachments FOREIGN KEY (AttachmentId) REFERENCES Attachments (AttachmentId)

END
GO
DELETE settings WHERE section  IN ('FoExport','frmWerk','FoTagesplanStr','RepertoireExport','frmDispoB','frmHistory')
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ResourceTypes') AND [name] = 'ShortName')
BEGIN
	PRINT('Adding ResourceTypes.ShortName ...')
	ALTER TABLE ResourceTypes ADD ShortName VARCHAR(20)
END
GO

--#endregion

--#region  2020-08-27 NVData VersionDate
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('NVData') AND name = 'VersionDate')
BEGIN
	PRINT('Adding NVData.VersionDate ...')
	ALTER TABLE NVData ADD VersionDate DATE NULL
	EXEC ('UPDATE NVData SET  VersionDate =  CASE WHEN ISDATE(CAST(NVData.lfdVerNr AS VARCHAR(8))) = 1 THEN CAST(CAST(NVData.lfdVerNr AS VARCHAR(8)) AS DATE) ELSE ''19000102'' END ')
	ALTER TABLE NVData ALTER COLUMN VersionDate DATE NOT NULL
	ALTER TABLE NVData DROP COLUMN lfdVerNr
END
GO
--#endregion

--#region  2020-08-28 ActivityScenes

IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityScenes') AND name = 'ScheduledBasicCastFlag')
BEGIN

	PRINT 'Adding column ActivityScenes.ScheduledBasicCast ...'
	ALTER TABLE ActivityScenes ADD ScheduledBasicCastFlag TINYINT NULL 
	EXEC ('UPDATE ActivityScenes SET ScheduledBasicCastFlag = 1 WHERE ScheduledBasicCast = 0')
	EXEC ('UPDATE ActivityScenes SET ScheduledBasicCastFlag = 2 WHERE ScheduledBasicCast = 1')
	EXEC ('UPDATE ActivityScenes SET ScheduledBasicCastFlag = 4 WHERE ScheduledBasicCast = 2')
	EXEC ('UPDATE ActivityScenes SET ScheduledBasicCastFlag = 8 WHERE ScheduledBasicCast = 3')
END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ActivityScenes') AND name = 'EndFormatOption')
BEGIN
	PRINT 'Adding column ActivityScenes.PrintStart ...'
	ALTER TABLE ActivityScenes ADD StartFormatOption TINYINT NOT NULL CONSTRAINT DF_ActivityScenes_StartFormatOption DEFAULT 0
	ALTER TABLE ActivityScenes ADD EndFormatOption TINYINT NOT NULL CONSTRAINT DF_ActivityScenes_EndFormatOption DEFAULT 0
	EXEC ('UPDATE ActivityScenes SET StartFormatOption = 1 WHERE ActivityScenes.StartOffset <> 0')
END
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ActivityScenes') AND name = 'PartContent')
BEGIN
	PRINT('Adding ActivityScenes.PartContent ...')
	ALTER TABLE ActivityScenes ADD PartContent VARCHAR(MAX) NULL

	EXEC( 'UPDATE ActivityScenes SET PartContent = co, ScheduledBasicCastFlag = bcSum, InstructionNotes = notes
	FROM (SELECT
			Activityid,StartOffset, MIN(ActivityScenesId) AS firstActivityScenesId,
			(SELECT SUM(a) FROM  (SELECT DISTINCT ScheduledBasicCastFlag AS a FROM ActivityScenes as p1 WHERE (p1.Activityid = p2.Activityid AND p1.StartOffset = p2.StartOffset AND NOT ScheduledBasicCastFlag IS NULL)) AS BC) AS bcSum,
			STUFF((SELECT '', '' + p1.InstructionNotes FROM ActivityScenes AS p1 WHERE (p1.Activityid = p2.Activityid AND p1.StartOffset = p2.StartOffset ) FOR XML PATH (''''))	,1,2,'''') AS notes,
			''['' + STUFF((SELECT CASE WHEN NOT SceneId IS NULL 
									THEN '',{"Type":"scene","Id":'' + CAST(p1.SceneId AS VARCHAR(10)) + ''}'' 
								WHEN NOT ProductionId IS NULL 
									THEN  '',{"Type":"production","Id":'' + CAST(p1.ProductionId AS VARCHAR(10)) + ''}''
								ELSE 
									''''
								END
									FROM ActivityScenes as p1 WHERE (p1.Activityid = p2.Activityid) FOR XML PATH (''''), TYPE).value(''.'',''VARCHAR(max)'')	,1,1,'''') + '']'' AS co
			FROM ActivityScenes  p2
			GROUP BY Activityid, StartOffset) AS sub 	
		INNER JOIN ActivityScenes ON sub.firstActivityScenesId = ActivityScenes.ActivityScenesId')
		
		DELETE FROM ActivityScenes WHERE ActivityScenesId NOT IN (SELECT MIN(ActivityScenesId) FROM ActivityScenes GROUP BY  Activityid, StartOffset )
		DROP INDEX IX_ActivityScenes_ActivityId ON ActivityScenes 
		CREATE UNIQUE CLUSTERED INDEX IX_ActivityScenes_ActivityId_StartOffset ON ActivityScenes (ActivityId, StartOffset) 

END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ActivityScenes_ProductionTimeFlow') AND type = 'F') ALTER TABLE dbo.ActivityScenes DROP CONSTRAINT FK_ActivityScenes_ProductionTimeFlow
IF EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityScenes') AND name = 'ActId') ALTER TABLE ActivityScenes DROP COLUMN ActId
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ActivityScenes_Productions') AND type = 'F') ALTER TABLE dbo.ActivityScenes DROP CONSTRAINT FK_ActivityScenes_Productions
--IF EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityScenes') AND name = 'ProductionId') ALTER TABLE ActivityScenes DROP COLUMN ProductionId
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ActivityScenes_Scenes') AND type = 'F') ALTER TABLE dbo.ActivityScenes DROP CONSTRAINT FK_ActivityScenes_Scenes
--IF EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityScenes') AND name = 'SceneId') ALTER TABLE ActivityScenes DROP COLUMN SceneId
--IF EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('ActivityScenes') AND name = 'ScheduledBasicCast') ALTER TABLE ActivityScenes DROP COLUMN ScheduledBasicCast
GO


IF EXISTS (SELECT * FROM  sysindexes o WHERE  o.id = OBJECT_ID(N'dbo.LocationGroups') AND o.Name = 'Sort') DROP INDEX LocationGroups.Sort
GO
--#endregion

--#endregion -------- (4.204.x) -------------------------------

--#region ----------- 4.205.x ---------------------------------

--#region DH 2020-09-04
--#region PersonPermissionindex
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_PersonPermissions_OwnergroupID' AND object_id = OBJECT_ID(N'dbo.PersonPermissions')) 
BEGIN
	Print 'Create IX_PersonPermissions_OwnergroupID'
CREATE NONCLUSTERED INDEX [IX_PersonPermissions_OwnergroupID]
ON [dbo].[PersonPermissions] ([OwnerGroupId])
INCLUDE ([PersonPermissionId],[PersonId],[HasWritePermission],[OptionsFlag])
END
GO
--#endregion PersonPermissionindex

--#region GetPersonsInternal
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetPersonsInternal'))			DROP PROCEDURE dbo.[GetPersonsInternal]
GO
CREATE PROCEDURE [dbo].[GetPersonsInternal]
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT,
	@ShowPersonsWithoutRights BIT 
	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,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
                    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 
	
	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		
					) AS sub ON pk.PersonId = sub.PersonId

	IF (@include&2 = 2)
		SELECT NULL AS [#2], PersonAddress.*
		FROM PersonAddress
			INNER JOIN #includePermissions ON PersonAddress.PersonId = #includePermissions.PersonId WHERE  PersonAddress.IsSecret=0 OR readSecret > 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
	END 
		
	IF (@include<>0) 
	BEGIN	
		DROP TABLE #includePermissions
		DROP TABLE #PersonsNP
	END
	DROP TABLE #UserGroupPermissions
END
GO
--#endregion GetPersonsInternal

--#region GetPersons
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetPersons'))			DROP PROCEDURE dbo.[GetPersons]
GO
CREATE PROCEDURE [dbo].[GetPersons]
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@ShowPersonsWithoutRights BIT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @userId INT
	SELECT @userId = UserId FROM Users WHERE UserName  = CURRENT_USER
	EXECUTE GetPersonsInternal @condition, @order, @joins, @include, @userId, @ShowPersonsWithoutRights
END
GO

GRANT EXECUTE ON dbo.getpersons TO PUBLIC
GO
--#endregion GetPersons

--#endregion DH 2020-09-04

--#region 2020-09-07 NoteLong
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ProductionFieldTypes') AND name = 'ShowOnPage')
BEGIN
	PRINT 'Adding column ProductionFieldTypes.ShowOnPage ...'
	ALTER TABLE ProductionFieldTypes ADD ShowOnPage TINYINT NOT NULL CONSTRAINT DF_ProductionFieldTypes_ShowOnPage DEFAULT 0
	EXEC(' INSERT INTO ProductionFieldTypes (Name,Code,GroupName, SectionName, SortInSection, IsVisibleIn, KnownField, DataType, LM,LC,UM,UC, IsExportable, ShowOnPage,Settings)
	VALUES (''Bemerkung Lang'',''LAN'',NULL,NULL,1,2,7,1,GETDATE(), GETDATE(),-1,-1,0,1,
					''<MultiLineTextFieldSettings><ValidationAttributes /><LookupValues /><MinimumVisibleLines>7</MinimumVisibleLines></MultiLineTextFieldSettings>'')')
	EXEC('DECLARE @id INT
	SELECT @id = ProductionFieldTypeId  FROM ProductionFieldTypes WHERE KnownField = 7
	INSERT INTO ActivityProductionFieldData (ActivityId, ProductionFieldTypeId, TextValue,LM,LC,UM,UC) SELECT ActivityId, @id, LongNote, LM,LC,ISNULL(UM,-1),ISNULL(UC,-1) FROM Activities WHERE NOT LongNote IS NULL AND LongNote <> ''''
	')
	--ALTER TABLE Activities DROP COLUMN LongNote
END
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE  c.name = 'Pl' AND o.id = OBJECT_ID(N'dbo.Activities')) ALTER TABLE Activities DROP COLUMN Pl
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Activities_PriceCategories') AND type = 'F') ALTER TABLE dbo.Activities DROP CONSTRAINT FK_Activities_PriceCategories
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE  c.name = 'PriceCategoryId' AND o.id = OBJECT_ID(N'dbo.Activities')) ALTER TABLE Activities DROP COLUMN PriceCategoryId
GO
DBCC CLEANTABLE (0,'Activities') WITH NO_INFOMSGS;
GO
ALTER PROCEDURE [dbo].[GetActivitiesInternal]
	@start DATE,
	@end DATE,
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT,
	@ShowActivitiesWithoutRights BIT --AND Cast
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @permissionPeriodStart DATE
	DECLARE @permissionPeriodEnd DATE

	-- *** 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 
			SELECT @start = '30000101'
			SELECT @end = '30000101'
		END
	ELSE
		BEGIN
			IF (@start < @permissionPeriodStart)	BEGIN SELECT @start = @permissionPeriodStart END
			IF (@end > @permissionPeriodEnd)		BEGIN SELECT @end = @permissionPeriodEnd END
		END

	-- *** Wenn weitere Daten mitgeladen werden Temp Tabelle mit  ActivityIds erstellen ***
	IF (@include <> 0)  CREATE TABLE #ActivitiesNP (ActivityId INT NOT NULL, NoPermission BIT NOT NULL)

	-- *** Berechtigungen des Users für Aktivitäten (2) und Besetzungen (4) in Temp Tabelle ***
	CREATE TABLE #Permissions (PermissionId INT NOT NULL, PermissionArea INT NOT NULL, OptionsFlag INT NOT NULL)
	INSERT INTO #Permissions SELECT Permissions.OwnerGroupId, Permissions.PermissionArea, OptionsFlag  FROM Permissions WHERE UserId = @userId AND PermissionArea IN (2,4)

	-- *** Spalten für ausgabe, wenn auch Daten ohne Berechtigungen geladen werden die ProduktionsId als 0 und keine Bemerkungen zurückgeben ***
	
	DECLARE @secretfields VARCHAR(MAX)
	DECLARE @fields VARCHAR(MAX)
	SET @fields  =', Activities.ActivityId, Activities.LocationId,
					Activities.Date, Activities.StartTime, Activities.EndTime, Activities.PrintEndTime, Activities.PrintStartTime,  
					Activities.Status, Activities.LinkedActivityId, Activities.IsSecret, Activities.CreateCountDownId, 
					Activities.IsTimeSpan, Activities.IsExtraPayLiable, Activities.VariantId, 
					Activities.LM, Activities.UM, Activities.LC, Activities.UC'

	-- *** Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben, ansonsten ActivityIds in Temp
	-- *** Die "@secretfields" müssen dann "@ShowActivitiesWithoutRights" abfragen
	if (@include <> 0) 
		BEGIN
			SET @SQLQuery = 'INSERT INTO #ActivitiesNP SELECT  Activities.ActivityId, CASE WHEN  Ug.PermissionId IS NULL OR (Activities.IsSecret = 1 AND ug.OptionsFlag&1 <> 1)  THEN 1	ELSE 0 END'

			SET @secretfields  =       'CASE WHEN NoPermission = 1 THEN -100 ELSE Activities.ProductionId		END	AS ProductionId,
										CASE WHEN NoPermission = 1 THEN 0	 ELSE Activities.ActivityTypeId		END	AS ActivityTypeId,
										CASE WHEN NoPermission = 1 THEN NULL ELSE Activities.ArtisticNote	END AS ArtisticNote,
										CASE WHEN NoPermission = 1 THEN NULL ELSE Activities.TechnicalNote END AS TechnicalNote'
		END
	ELSE
		BEGIN
			IF (@ShowActivitiesWithoutRights = 1)
				
				SET @secretfields  =   'CASE WHEN Ug.PermissionId IS NULL OR (Activities.IsSecret = 1 AND ug.OptionsFlag&1 <> 1) THEN -100 ELSE Activities.ProductionId		END	AS ProductionId,
										CASE WHEN Ug.PermissionId IS NULL OR (Activities.IsSecret = 1 AND ug.OptionsFlag&1 <> 1) THEN 0	 ELSE Activities.ActivityTypeId		END	AS ActivityTypeId,
										CASE WHEN Ug.PermissionId IS NULL OR (Activities.IsSecret = 1 AND ug.OptionsFlag&1 <> 1) THEN NULL ELSE Activities.ArtisticNote	END AS ArtisticNote,
										CASE WHEN Ug.PermissionId IS NULL OR (Activities.IsSecret = 1 AND ug.OptionsFlag&1 <> 1) THEN NULL ELSE Activities.TechnicalNote END AS TechnicalNote'
			ELSE 
				SET @secretfields  =   'Activities.ProductionId,
										Activities.ActivityTypeId,
										Activities.ArtisticNote,
										Activities.TechnicalNote'

			SET @SQLQuery = 'SELECT ' + @secretfields + @fields
		END

	-- ***	Immer auf Produktionen unf Berechtigungen ***
		SET @SQLQuery =  @SQLQuery + '	 FROM  Activities 
										INNER JOIN  Productions AS p ON Activities.ProductionId = p.ProductionId '

		IF (@ShowActivitiesWithoutRights = 1) 
			SET @SQLQuery =  @SQLQuery + 'LEFT JOIN #Permissions AS UG ON UG.PermissionId = P.OwnerGroupId AND ug.PermissionArea = 2'
		ELSE 
			SET @SQLQuery =  @SQLQuery + 'INNER JOIN #Permissions AS UG ON UG.PermissionId = P.OwnerGroupId AND ug.PermissionArea = 2'

	-- *** Weitere Joins ***	 
		IF (@joins&1 = 1)
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN ActivityDetails ON Activities.ActivityId = ActivityDetails.ActivityId '

		IF (@joins&4 = 4 OR (@include = 0 AND CHARINDEX('Locations.', @order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN Locations ON Activities.LocationId = Locations.LocationId '

		IF (@joins&8 = 8 OR (@include = 0 AND CHARINDEX('ActivityTypes.', @order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN ActivityTypes ON Activities.ActivityTypeId = ActivityTypes.ActivityTypeId '

		IF (@joins&16 = 16)
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN ProductionDetails ON Activities.ProductionId = ProductionDetails.ProductionId '

		IF (@joins&32 = 32)
			SET @SQLQuery = @SQLQuery + ' INNER JOIN OwnerGroups ON p.OwnerGroupId = OwnerGroups.OwnerGroupId '
			
		SET @SQLQuery = @SQLQuery + ' WHERE Activities.Date BETWEEN ''' + CAST(@start AS VARCHAR(MAX)) + '''  AND ''' + CAST(@end AS VARCHAR(MAX)) + ''''
		
			--Wenn in allen Besitzergruppe gesperrte Aktivitäten gelesen werden können ist keine Prüfung auf IsSecret erforderlich
		IF (EXISTS(SELECT * FROM #Permissions WHERE PermissionArea = 2 AND OptionsFlag&1 <> 1)) 
		BEGIN
			IF (@ShowActivitiesWithoutRights = 0)
					SET @SQLQuery = @SQLQuery  + ' AND (Activities.IsSecret = 0 OR ug.OptionsFlag&1 = 1)'
		END

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

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

		EXECUTE sp_Executesql @SQLQuery 
	
	IF (@include <> 0) --Wenn keine weiteren Daten mitgeladen werden wurde das Ergebnis bereits zurückgeben
		BEGIN
			SET @SQLQuery = 'SELECT '+ @secretfields+ @fields +' FROM Activities AS Activities 
																	INNER JOIN  #ActivitiesNP ON Activities.ActivityId = #ActivitiesNP.ActivityId'

			If (@order IS NOT NULL)
			BEGIN
				--Prüfen ob Tabellen für Sortierungen gejoint werden müßen, ggf. auch bei Direktabfragen "@include =0" prüfen

				IF (CHARINDEX('Locations.', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN Locations ON Activities.LocationId = Locations.LocationId '
				END
				IF (CHARINDEX('Title', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN  Productions AS p ON Activities.ProductionId = p.ProductionId '
				END
				IF (CHARINDEX('ActivityTypes', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN ActivityTypes ON Activities.ActivityTypeId = ActivityTypes.ActivityTypeId '
				END
				SET @SQLQuery = @SQLQuery + @order 

			END
			EXECUTE sp_Executesql  @SQLQuery
		END

-- *** Wenn Aktivitäten ohne Berechtigungen mitgeladen wurden für diese keine Abhängigen Daten laden
	IF (@ShowActivitiesWithoutRights = 1 AND @include <> 0)
		DELETE FROM #ActivitiesNP WHERE NoPermission = 1

	IF (@include&1 = 1)
		SELECT NULL AS [#1], ActivityDetails.*
		FROM ActivityDetails 
			INNER JOIN #ActivitiesNP On ActivityDetails.ActivityId = #ActivitiesNP.ActivityId

	IF (@include&2 = 2)
		SELECT NULL AS [#2], ActivityProductionFieldData.*
		FROM ActivityProductionFieldData 
			INNER JOIN #ActivitiesNP ON ActivityProductionFieldData.ActivityId = #ActivitiesNP.ActivityId

	IF (@include&4 = 4)
		SELECT NULL AS [#4], ActivitySubscriptions.*, Subscriptions.SubscriptionTypeId
		FROM ActivitySubscriptions 
			INNER JOIN Subscriptions ON ActivitySubscriptions.SubscriptionId = Subscriptions.SubscriptionId
			INNER JOIN #ActivitiesNP ON ActivitySubscriptions.ActivityId = #ActivitiesNP.ActivityId

	IF (@include&8 = 8)
		SELECT NULL AS [#8], B.CastId, B.RoleId, CASE WHEN B.IsSecret = 1 AND ug.OptionsFlag&1 <> 1 THEN -1 ELSE b.PersonId END AS PersonId, B.ActivityId, B.StartOffset, B.EndOffset, B.Note, B.PrintMode, B.LM, B.UM, B.LC, B.UC, B.IsSecret, B.GroupPosition, B.CastStatusId,
					B.IsCover, B.IsCanceled, B.Creditable, B.BackColorArgb, B.IsReplacement, B.ContractId
		FROM Cast AS B 
			INNER JOIN #ActivitiesNP ON B.ActivityId = #ActivitiesNP.ActivityId
			INNER JOIN Roles ON Roles.RoleId = B.RoleId
			INNER JOIN #Permissions AS UG ON UG.PermissionId  = Roles.OwnerGroupId AND  ug.PermissionArea = 4 AND (@ShowActivitiesWithoutRights = 1 OR B.IsSecret = 0 OR ug.OptionsFlag&1 = 1)	

	IF (@include&16 = 16) 
	BEGIN
		DECLARE @changeSince DATETIME
		SET @changeSince = GETDATE() - 21 
		SELECT NULL AS [#16],ax.*
		FROM  ActivityHistory AS ax
			INNER JOIN #ActivitiesNP ON ax.ActivityId = #ActivitiesNP.ActivityId
			LEFT JOIN (SELECT xhid FROM HistoryConfirmations WHERE TableName = 'Activities' AND uc = @userId) AS d ON ax.xhID = d.xhid 
		WHERE d.xhId IS NULL AND ax.uc <> @userId AND ax.lm >= @changeSince AND ax.xhRelevant = 1 
		ORDER BY ax.lc DESC
	END

	IF (@include&32 = 32) 
		SELECT NULL AS [#32], ActivityDepartmentData.*
		FROM ActivityDepartmentData 
			INNER JOIN #ActivitiesNP ON ActivityDepartmentData.ActivityId = #ActivitiesNP.ActivityId

	IF (@include&256 = 256) 
		SELECT NULL AS [#256], ActivityScenes.*
		FROM ActivityScenes 
			INNER JOIN #ActivitiesNP ON ActivityScenes.ActivityId = #ActivitiesNP.ActivityId

	IF (@include&512 = 512) 
		SELECT NULL AS [#512], TechnicalJobs.*
		FROM TechnicalJobs 
			INNER JOIN #ActivitiesNP ON TechnicalJobs.ActivityId = #ActivitiesNP.ActivityId

		IF (@include <> 0) DROP TABLE #ActivitiesNP
END
Go
--#endregion

--#region 20200915 GetPersonStatusInternal und GetPersonStatus
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetPersonStatusInternal')) DROP PROCEDURE dbo.GetPersonStatusInternal
GO
CREATE PROCEDURE [dbo].[GetPersonStatusInternal]
	@condition VARCHAR(MAX),
	@collectives BIT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	IF (@collectives = 0)
		BEGIN
			 SET @SQLQuery ='SELECT PersonStatusId,
				CASE WHEN ug.HasWritePermission = 1 OR ug.OptionsFlag & 256 = 256 THEN ps.PersonStatusTypeId ELSE -1 END AS PersonStatusTypeId,
				CASE WHEN ug.HasWritePermission = 1 OR ug.OptionsFlag & 256 = 256 THEN ps.Note  ELSE NULL END AS Note,
				CASE WHEN ug.HasWritePermission = 1 OR ug.OptionsFlag & 256 = 256 THEN st.TimeAccountId  ELSE -1 END AS TimeAccountId,
				CASE WHEN TimeAccounts.Formel IN (1, 2) THEN 2  WHEN TimeAccounts.Formel = 4050 THEN 4 ELSE 1 END AS AvailableStatus,
				CASE WHEN NOT st.TemplateStartTime IS NULL OR NOT st.TemplateEndTime IS NULL THEN 1  ELSE 0 END AS IsHalfDayType,
				ps.ApprovalStatus, ps.PersonId, ContractId, StartDate, EndDate, StartTime, EndTime, ManualDayCount, DateOfRequest, ps.LM, ps.UM, ps.LC, ps.UC
			FROM PersonStatus AS ps
				INNER JOIN Persons AS p ON ps.PersonId = p.PersonId 
				INNER JOIN PersonStatusTypes AS st ON ps.PersonStatusTypeId = st.PersonStatusTypeId 
				INNER JOIN TimeAccounts  ON st.TimeAccountId = TimeAccounts.TimeAccountId
				INNER JOIN Permissions AS ug ON p.OwnerGroupId = ug.OwnerGroupId
			WHERE ug.Userid = '+ CAST(@userId AS VARCHAR(10))+ 'AND PermissionArea = 8'
		END
	ELSE
		BEGIN
			 SET @SQLQuery =' SELECT ps.*, p.OwnerGroupId, p.PersonCategoryId, st.TimeAccountId,
			1 AS AvailableStatus,
			CASE WHEN NOT st.TemplateStartTime IS NULL OR NOT st.TemplateEndTime IS NULL THEN 1  ELSE 0 END AS IsHalfDayType
			FROM			PersonStatus ps
				INNER JOIN  Persons			AS P	ON ps.PersonId = P.PersonId
				INNER JOIN  PersonCategories			AS pk	ON P.PersonCategoryId = pk.PersonCategoryId
				INNER JOIN  PersonStatusTypes		AS st	ON ps.PersonStatusTypeId = st.PersonStatusTypeId
				INNER JOIN TimeAccounts  ON st.TimeAccountId = TimeAccounts.TimeAccountId
			WHERE    
				pk.PersonCategoryType BETWEEN 10 AND 200'
		END

	SET @SQLQuery = @SQLQuery + ' AND ' + @condition
	EXECUTE sp_Executesql @SQLQuery
END
GO

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

CREATE PROCEDURE dbo.GetPersonStatus
	@condition VARCHAR(MAX),
	@collectives BIT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @userId INT
	SELECT @userId = UserId FROM Users WHERE UserName  = CURRENT_USER
	EXECUTE GetPersonStatusInternal @condition, @collectives, @UserId 
END
GO

GRANT EXECUTE ON dbo.GetPersonStatus TO PUBLIC
GO

--#endregion 

--#region Scenes.IX_Scenes_ActId_PositionInAct
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_Scenes_ActId_PositionInAct' AND object_id = OBJECT_ID(N'dbo.Scenes') AND type_desc = 'CLUSTERED') 
BEGIN
	PRINT('Adding Scenes.IX_Scenes_ActId_PositionInAct ...')
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Roles_Scenes_Scenes') AND type = 'F') ALTER TABLE dbo.Roles_Scenes DROP CONSTRAINT FK_Roles_Scenes_Scenes 	 
	ALTER TABLE dbo.Scenes DROP CONSTRAINT PK_Scenes
	CREATE CLUSTERED INDEX IX_Scenes_ActId_PositionInAct ON Scenes (ActId, PositionInAct) 
	ALTER TABLE dbo.Scenes ADD CONSTRAINT PK_Sceness PRIMARY KEY  (SceneId) ON [PRIMARY]
	ALTER TABLE dbo.Roles_Scenes ADD CONSTRAINT FK_Roles_Scenes_Scenes FOREIGN KEY (SceneId) REFERENCES dbo.Scenes (SceneId) ON  DELETE CASCADE
END
GO
--#endregion

--#region CK_DutyRoster_Period
IF NOT EXISTS (SELECT * FROM sys.sysobjects dc WHERE  dc.name = 'CK_DutyRoster_Period3') 
BEGIN 
	UPDATE DutyRoster SET StartTime1 = NULL WHERE EndTime1 IS NULL AND NOT StartTime1 IS NULL
	UPDATE DutyRoster SET EndTime1 = NULL WHERE StartTime1 IS NULL AND NOT EndTime1 IS NULL

	UPDATE DutyRoster SET StartTime2 = NULL WHERE EndTime2 IS NULL AND NOT StartTime2 IS NULL
	UPDATE DutyRoster SET EndTime2 = NULL WHERE StartTime2 IS NULL AND NOT EndTime2 IS NULL

	UPDATE DutyRoster SET StartTime3 = NULL WHERE EndTime3 IS NULL AND NOT StartTime3 IS NULL
	UPDATE DutyRoster SET EndTime3 = NULL WHERE StartTime3 IS NULL AND NOT EndTime3 IS NULL
	
	ALTER TABLE DutyRoster WITH CHECK ADD CONSTRAINT CK_DutyRoster_Period1 
		CHECK ((StartTime1 IS NULL AND EndTime1 IS NULL) OR (NOT StartTime1 IS NULL AND NOT EndTime1 IS NULL))
	ALTER TABLE DutyRoster CHECK CONSTRAINT CK_DutyRoster_Period1

	ALTER TABLE DutyRoster WITH CHECK ADD CONSTRAINT CK_DutyRoster_Period2 
		CHECK ((StartTime2 IS NULL AND EndTime2 IS NULL) OR (NOT StartTime2 IS NULL AND NOT EndTime2 IS NULL))
	ALTER TABLE DutyRoster CHECK CONSTRAINT CK_DutyRoster_Period2

	ALTER TABLE DutyRoster WITH CHECK ADD CONSTRAINT CK_DutyRoster_Period3 
		CHECK ((StartTime3 IS NULL AND EndTime3 IS NULL) OR (NOT StartTime3 IS NULL AND NOT EndTime3 IS NULL))
	ALTER TABLE DutyRoster CHECK CONSTRAINT CK_DutyRoster_Period3
END 
GO
--#endregion

--#region AbActivityProductionFieldData_Update
ALTER VIEW [dbo].[AbActivityProductionFieldData_Update]
AS
	SELECT p.* FROM ActivityProductionFieldData AS p
	WHERE p.ActivityId IN (
		SELECT act.ActivityId FROM Activities AS act
			INNER JOIN Productions AS st ON act.ProductionId = st.ProductionId
			INNER JOIN OwnerGroups AS gr ON st.OwnerGroupId = gr.OwnerGroupId
			INNER JOIN Permissions AS ug ON gr.OwnerGroupId = ug.OwnerGroupId   
			INNER JOIN Users ON Ug.Userid  = Users.Userid 
		WHERE ug.PermissionArea = 2 AND (ug.OptionsFlag & 2 = 2 OR ug.HasWritePermission = 1) AND Users.Username = CURRENT_USER
	)
WITH CHECK OPTION
GO
--#endregion

--#region

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'CalculationSalaryLevelId' AND o.id = OBJECT_ID('Roles'))	
BEGIN
	PRINT('Adding column Roles.CalculationSalaryLevelId ...')
	ALTER TABLE Roles ADD CalculationSalaryLevelId INT NULL
	ALTER TABLE Roles ADD CONSTRAINT FK_Role_SalaryLevel FOREIGN KEY (CalculationSalaryLevelId) REFERENCES dbo.SalaryLevel (SalaryLevelId)

	
UPDATE RoleBonus 
	SET RuleSettings = REPLACE(CAST( [RuleSettings] AS VARCHAR(MAX)),'</RoleBonusFeeRule>','<ContractFieldTypeNames>ContractType_' + CAST(ContractTypeId AS VARCHAR(MAX))+ '</ContractFieldTypeNames></RoleBonusFeeRule>')
	WHERE NOT RuleSettings IS NULL

UPDATE RoleBonus 
	SET RuleSettings = '<RoleBonusFeeRule><ContractFieldTypeNames>ContractType_' + CAST(ContractTypeId AS VARCHAR(MAX))+ '</ContractFieldTypeNames></RoleBonusFeeRule>'
	WHERE RuleSettings IS NULL


UPDATE ProductionBonus 
	SET RuleSettings = REPLACE(CAST( [RuleSettings] AS VARCHAR(MAX)),'</ProductionBonusFeeRule>','<ContractFieldTypeNames>ContractType_' + CAST(ContractTypeId AS VARCHAR(MAX))+ '</ContractFieldTypeNames></ProductionBonusFeeRule>')
	WHERE NOT RuleSettings IS NULL

UPDATE ProductionBonus 
	SET RuleSettings = '<ProductionBonusFeeRule><ContractFieldTypeNames>ContractType_' + CAST(ContractTypeId AS VARCHAR(MAX))+ '</ContractFieldTypeNames></ProductionBonusFeeRule>'
	WHERE RuleSettings IS NULL


	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_RoleBonus_ContractTypes') AND type = 'F') ALTER TABLE dbo.RoleBonus DROP CONSTRAINT FK_RoleBonus_ContractTypes
	ALTER TABLE RoleBonus ALTER COLUMN ContractTypeId INT NULL

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ProductionBonus_ContractTypes') AND type = 'F') ALTER TABLE dbo.ProductionBonus DROP CONSTRAINT FK_ProductionBonus_ContractTypes
	ALTER TABLE ProductionBonus ALTER COLUMN ContractTypeId INT NULL


	ALTER TABLE dbo.ProductionBonus DROP CONSTRAINT PK_ProductionBonus
	CREATE CLUSTERED INDEX IX_ProductionBonus_Production ON ProductionBonus (ProductionId) 
	ALTER TABLE ProductionBonus ADD CONSTRAINT PK_ProductionBonus PRIMARY KEY (ProductionBonusId) ON [PRIMARY]

	ALTER TABLE dbo.RoleBonus DROP CONSTRAINT PK_RoleBonus
	CREATE CLUSTERED INDEX IX_RoleBonus_Role ON RoleBonus (RoleId) 
	ALTER TABLE RoleBonus ADD CONSTRAINT PK_RoleBonus PRIMARY KEY (RoleBonusId) ON [PRIMARY]

END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_PersonContactData_PersonId' AND object_id = OBJECT_ID(N'dbo.PersonContactData') AND type_desc = 'CLUSTERED') 
BEGIN
	PRINT('Adding PersonContactData.IX_PersonContactData_PersonId ...') 
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_PersonContactData_Sort') ALTER TABLE PersonContactData DROP CONSTRAINT DF_PersonContactData_Sort
	UPDATE PersonContactData SET Sort = 0 WHERE Sort IS NULL OR Sort < 0
	UPDATE PersonContactData SET Sort = 255 WHERE Sort > 255
	ALTER TABLE dbo.PersonContactData ALTER COLUMN Sort TINYINT NOT NULL
	ALTER TABLE PersonContactData ADD  CONSTRAINT DF_PersonContactData_Sort  DEFAULT 0 FOR Sort
	ALTER TABLE PersonContactData DROP CONSTRAINT PK_PersonContactData
	CREATE CLUSTERED INDEX IX_PersonContactData_PersonId ON PersonContactData (PersonId) 
	ALTER TABLE PersonContactData ADD CONSTRAINT PK_PersonContactData PRIMARY KEY (PersonContactDataId) ON [PRIMARY]
	IF EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.PersonContactData') AND o.Name = 'IX_PersCom_PersonId') DROP INDEX PersonContactData.IX_PersCom_PersonId
END
GO
IF NOT EXISTS (SELECT * FROM  sysindexes o WHERE   o.id = OBJECT_ID(N'dbo.WorkAreas') AND o.Name = 'IX_WorkAreas_DepartmentId') CREATE NONCLUSTERED INDEX IX_WorkAreas_DepartmentId ON WorkAreas (DepartmentId)
GO
--#endregion

--#region 2020-10-13 Season
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.TR_Season_Update')) DROP TRIGGER dbo.TR_Season_Update
GO
CREATE TRIGGER dbo.TR_Season_Update ON Seasons AFTER UPDATE 
AS 
BEGIN

	DECLARE @oldStart DATE
	DECLARE @oldEnd DATE
	DECLARE @newStart DATE
	DECLARE @newEnd DATE

	SELECT @oldStart = StartDate, @oldEnd = EndDate FROM DELETED
	SELECT @newStart = StartDate, @newEnd = EndDate FROM INSERTED

	IF UPDATE(StartDate)
	BEGIN
		UPDATE BasicCast 
			SET StartDate = @newStart
			WHERE StartDate = @oldStart 

		UPDATE Contracts 
			SET StartDate = @newStart
			FROM Contracts 
				INNER JOIN ContractTypes ON Contracts.ContractTypeId = ContractTypes.ContractTypeId 
			WHERE Module <> 4 AND StartDate = @oldStart AND (EndDate IS NULL OR EndDate = @oldEnd OR EndDate IN (SELECT EndDate FROM Seasons))

		UPDATE ContractEngagements 
			SET StartDate = @newStart
			WHERE StartDate = @oldStart AND (EndDate IS NULL OR EndDate = @oldEnd OR EndDate IN (SELECT EndDate FROM Seasons))

		UPDATE ContractSalaryRules
			SET StartDate = @newStart
			FROM ContractSalaryRules 
				INNER JOIN Contracts ON ContractSalaryRules.ContractId = Contracts.ContractId 
				INNER JOIN ContractTypes ON Contracts.ContractTypeId = ContractTypes.ContractTypeId 
			WHERE Module <> 4 AND ContractSalaryRules.StartDate = @oldStart AND (ContractSalaryRules.EndDate IS NULL OR ContractSalaryRules.EndDate = @oldEnd OR .ContractSalaryRules.EndDate IN (SELECT EndDate FROM Seasons))
	END

	IF UPDATE(EndDate)
	BEGIN
		UPDATE BasicCast 
			SET EndDate = @newEnd
			WHERE EndDate = @oldEnd

		UPDATE Contracts 
			SET EndDate = @newEnd
			FROM Contracts INNER JOIN ContractTypes ON Contracts.ContractTypeId = ContractTypes.ContractTypeId 
			WHERE Module <> 4 AND EndDate = @oldEnd AND (StartDate IS NULL OR StartDate = @newStart OR StartDate IN (SELECT StartDate FROM Seasons))

		UPDATE ContractEngagements 
			SET EndDate = @newEnd
			WHERE EndDate = @oldEnd AND (StartDate IS NULL OR StartDate = @newStart OR StartDate IN (SELECT StartDate FROM Seasons))

		UPDATE ContractSalaryRules 
			SET EndDate = @newEnd 
			FROM ContractSalaryRules
				INNER JOIN Contracts ON ContractSalaryRules.ContractId = Contracts.ContractId 
				INNER JOIN ContractTypes ON Contracts.ContractTypeId = ContractTypes.ContractTypeId 
			WHERE Module <> 4 AND  ContractSalaryRules.EndDate = @oldEnd AND (ContractSalaryRules.StartDate IS NULL OR ContractSalaryRules.StartDate = @newStart OR ContractSalaryRules.StartDate IN (SELECT StartDate FROM Seasons))
	END
END
GO
--#endregion 

--#region GetCastWithContractPermission

ALTER PROCEDURE [dbo].[GetCastWithContractPermission] 
	@startDate DATE = NULL, 
	@endDate DATE = NULL,
	@personId INT = NULL,
	@groupIdList VARCHAR(MAX) = NULL,
	@include INT = NULL
AS
BEGIN
	SELECT DISTINCT c.* INTO #cast FROM Cast AS c
		INNER JOIN Roles	AS r ON c.RoleId = r.RoleId
		INNER JOIN Activities AS a ON c.ActivityId = a.ActivityId
		INNER JOIN Permissions AS Ug ON r.OwnerGroupId IN (ISNULL(@groupIdList, r.OwnerGroupId))   
		INNER JOIN Users ON Ug.Userid  = Users.Userid 
	WHERE ug.PermissionArea = 1 /*Es soll der PermissionArea 1 (Vertragsdaten) verwendet werden*/
		AND ug.HasWritePermission = 1
		AND Users.Username = CURRENT_USER
		AND c.PersonId = (ISNULL(@personId, c.PersonId))
		AND a.Date >= ISNULL(@startDate, a.Date)
		AND a.Date <= ISNULL(@endDate, a.Date)

	SELECT * FROM #Cast

	IF (@include IS NULL OR @include = 0)
		RETURN

	IF (@include&1 = 1) -- Bonus der Besetzungen
		SELECT NULL AS [#1], cb.* 
		FROM CastBonus AS cb
		INNER JOIN #Cast ON cb.CastId = #Cast.CastId
	IF (@include&2 = 2) -- Vertragstermine der Besetzungen
		SELECT NULL AS [#2], cd.*, rdc.ContractDateId AS ReplacedContractDateId 
		FROM ContractDates AS cd 
		LEFT JOIN ContractDates AS rdc ON cd.ContractDateId = rdc.ReplacementContractDateId
		INNER JOIN #Cast ON cd.CastId = #Cast.CastId
	IF (@include&4 = 4) -- Aktivitäten der Besetzungen
		SELECT NULL AS [#4], a.*
		FROM Activities AS a 
		WHERE EXISTS (SELECT * FROM #Cast AS c WHERE c.ActivityId = a.ActivityId)
	IF (@include&8 = 8) -- Verträge der Besetzungen
		SELECT NULL AS [#8], v.*
		FROM Contracts AS v
		WHERE EXISTS (SELECT * FROM #Cast AS c WHERE c.ContractId = v.ContractId)
	IF (@include&16 = 16) -- Rollen der Besetzungen
		SELECT NULL AS [#16], r.*
		FROM Roles AS r 
		WHERE EXISTS (SELECT * FROM #Cast AS c WHERE c.RoleId = r.RoleId)
	IF (@include&32 = 32)  -- Personen der Besetzungen
		SELECT NULL AS [#32], p.*
		FROM Persons AS p 
		WHERE EXISTS (SELECT * FROM #Cast AS c WHERE c.PersonId = p.PersonId)
	IF (@include&96 = 96) -- Alle Verträge der besetzen Personen (PreLoading von Person.Contracts)
		SELECT NULL AS [#96], v.*
		FROM Contracts AS v
		WHERE EXISTS (SELECT * FROM #Cast AS c WHERE c.PersonId = v.PersonId)
	IF (@include&224 = 224)  -- Alle Rollenverträge (auch Spielzeithonorare etc.) der besetzten Personen (PreLoading von Contract.ContractRolle)
		SELECT NULL AS [#224], vr.*
		FROM ContractEngagements AS vr
		INNER JOIN Contracts AS v ON vr.ContractId = v.ContractId
		WHERE EXISTS (SELECT * FROM #Cast AS c WHERE c.PersonId = v.PersonId)
END
GO
--#endregion

--#region UpdateCastWithContractPermission
ALTER PROCEDURE [dbo].[UpdateCastWithContractPermission] 
	@castId INT,
	@contractId INT
AS
BEGIN
	UPDATE c SET c.ContractId = @contractId 
	FROM Cast AS c
		INNER JOIN Roles	AS r ON c.RoleId = r.RoleId
		INNER JOIN Permissions AS Ug ON r.OwnerGroupId = ug.OwnerGroupId   
		INNER JOIN Users ON Ug.UserId  = Users.UserId 
	WHERE @castId = c.CastId 
		AND ug.PermissionArea IN (1, 4) 
		AND ug.HasWritePermission = 1 
		AND Users.Username = CURRENT_USER
END
GO
--#endregion 

--#region  2020-10-19
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('CostUnits') AND name = 'InfoCode')
BEGIN
	BEGIN TRY;
		--Trigger muss deaktiviert werden, da sonst bei falsch gespeicherten costunits der infocode nicht gespeichert werden kann
		DISABLE TRIGGER TR_Insert_CostUnitNo ON CostUnits;  
		PRINT('Adding CostUnits.InfoCode ...')
		ALTER TABLE CostUnits ADD InfoCode VARCHAR(20)
		EXEC ('UPDATE CostUnits SET InfoCode = (SELECT LEFT(Title, 20) FROM Productions p INNER JOIN CostUnitProductions cp ON p.ProductionId = cp.ProductionId WHERE cp.CostUnitId = CostUnits.CostUnitId ) FROM CostUnits WHERE (SELECT COUNT(*) FROM CostUnitProductions cp WHERE cp.CostUnitId = CostUnits.CostUnitId) = 1');
		ENABLE TRIGGER TR_Insert_CostUnitNo ON CostUnits;  
	END TRY
	BEGIN CATCH
		ENABLE TRIGGER TR_Insert_CostUnitNo ON CostUnits;
		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('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)
	END CATCH
END
GO
--#endregion

--#endregion -------- (4.205.x) -------------------------------

--#region ----------- 4.205.x Release Übertrag ----------------

--#region SH 2020-11-02 FindUsagesOfUserModified - Order By Ordinal ersetzt durch Spaltennamen

ALTER PROCEDURE [dbo].[FindUsagesOfUserModified]
 @Userid INT
 with EXECUTE AS 'dbo'
  AS
BEGIN
	SET NOCOUNT ON
	DECLARE @table NVARCHAR(250), @field VARCHAR(2)
	CREATE TABLE #Msg(tableName VARCHAR(250),fieldName VARCHAR(2), countUsages int)

	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 AND o.name <> 'Settings' AND o.name <> 'UpdateLog' ORDER BY o.name

	OPEN fields;

	FETCH NEXT FROM fields

	INTO  @table, @field
	WHILE @@FETCH_STATUS = 0
		BEGIN
	
			DECLARE @count INT
			DECLARE @sql NVARCHAR(max)
			SET @sql = N'SELECT @count = COUNT(*) FROM [' + @table  + '] WHERE [' + @field + ']=' +   CONVERT(VARCHAR(20), @Userid) 
	
			EXEC sp_executesql @sql,N'@count int out', @count out
			IF (@count>0) 
			BEGIN
				DECLARE @sql1 NVARCHAR(max)
				SET @sql1 = N'INSERT INTO #Msg (tableName, fieldName, countUsages) VALUES (''' +@table+ ''','''+ @field+''','+   CONVERT(VARCHAR(20), @count)  +')'
				EXEC (@sql1)
			END
			FETCH NEXT FROM fields INTO  @table, @field
		END
	CLOSE fields
	DEALLOCATE fields
	SELECT * FROM #Msg
END
GO

--#endregion SH 2020-11-02 FindUsagesOfUserModified - Order By Ordinal ersetzt durch Spaltennamen

--#region SH 2020-11-02 AccessDatum - Top 1 durch Max ersetzt

ALTER FUNCTION [dbo].[AccessDatum] ()
RETURNS DATE AS
BEGIN
	DECLARE @TmpDat DATE

	SET @TmpDat = '30000101'
    SELECT @TmpDat = MAX(EndDate)
		FROM Seasons AS spz  
			INNER JOIN SeasonPermissions AS us ON spz.SeasonId = us.SeasonId
			INNER JOIN Users AS pw ON us.userid=pw.userid
    WHERE  CURRENT_USER = UserName 

	RETURN @TmpDat
END
GO

--#endregion SH 2020-11-02 AccessDatum - Top 1 durch Max ersetzt

--#region SH 2020-11-09 DELETE SET NULL von FK_Bonus_TimeAccounts und FK_PersonStatusTypes_TimeAccounts entfernen
IF(EXISTS(SELECT * FROM sys.foreign_keys WHERE [name] = 'FK_Bonus_TimeAccounts' AND delete_referential_action = 2))
BEGIN
	PRINT('Modifying FK_Bonus_TimeAccounts...')

	ALTER TABLE [dbo].[Bonus] DROP CONSTRAINT FK_Bonus_TimeAccounts
	ALTER TABLE [dbo].[Bonus] ADD CONSTRAINT [FK_Bonus_TimeAccounts] FOREIGN KEY ([TimeAccountId]) REFERENCES [dbo].[TimeAccounts] ([TimeAccountId]);
END
GO

IF(EXISTS(SELECT * FROM sys.foreign_keys WHERE [name] = 'FK_PersonStatusTypes_TimeAccounts' AND delete_referential_action = 2))
BEGIN
	PRINT('Modifying FK_PersonStatusTypes_TimeAccounts...')

	ALTER TABLE [dbo].[PersonStatusTypes] DROP CONSTRAINT [FK_PersonStatusTypes_TimeAccounts]
	ALTER TABLE [dbo].[PersonStatusTypes] ADD CONSTRAINT [FK_PersonStatusTypes_TimeAccounts] FOREIGN KEY ([TimeAccountId]) REFERENCES [dbo].[TimeAccounts] ([TimeAccountId]);
END
GO

--#endregion SH 2020-11-09 DELTE SET NULL von FK_Bonus_TimeAccounts entfernen

--#region SH 2020-11-10 GetCastCountByContract erweitern um Parameter für Zeitraum

PRINT('Updating Stored Procedure GetCastCountByContract...')
GO

ALTER PROCEDURE [dbo].[GetCastCountByContract]
		@ContractId INT, @ActivityStart DATE = NULL, @ActivityEnd DATE = NULL
AS
BEGIN
	SELECT COUNT(B.RoleId) FROM Cast AS B
	INNER JOIN Activities AS A ON A.ActivityId = B.ActivityId
	AND B.IsCanceled = 0
	AND A.[Status] >= 0
	AND B.ContractId = @ContractId
	AND (@ActivityStart IS NULL OR a.Date >= @ActivityStart)
	AND (@ActivityEnd IS NULL OR a.Date <= @ActivityEnd)
END
GO

--#endregion SH 2020-11-10 GetCastCountByContract erweitern um Parameter für Zeitraum

--#region 
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsCanceledActivityPayLiable' AND o.id = OBJECT_ID('ActivityDepartmentData'))	
BEGIN
	PRINT('Adding ActivityDepartmentData Roles.IsCanceledActivityPayLiable ...')
	ALTER TABLE ActivityDepartmentData ADD IsCanceledActivityPayLiable BIT NOT NULL CONSTRAINT DF_ActivityDepartmentData_IsCanceledActivityPayLiable DEFAULT 0
	EXEC('UPDATE ActivityDepartmentData SET IsCanceledActivityPayLiable = 1 WHERE NOT ShiftCount IS NULL AND ShiftCount > 0 ')
END
GO

--#endregion

--#region DH 2020-12-17 MergeDoubletten Verknüpfungen mit der eigenen Doublette werden gelöscht
-- nochmal atkualisiert am 2021-01-18
--#endregion DH 2020-12-17

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

--#region ----------- 4.206.x ---------------------------------

-- Nur neue Sachen für 4.206 hier eintragen!!! (Features)

--#region TK 2020-10-21
IF EXISTS(SELECT * FROM RoleTypes WHERE RoleTypeId = -1 ) 
BEGIN
	PRINT('Adding Roles.Roles ...') 
	UPDATE Roles SET RoleTypeId = -1 WHERE RoleId = -1 AND  RoleTypeId != -1
	UPDATE RoleTypes SET RoleTypeCategory = 1, IsProductionTeam = 0 WHERE RoleTypeId = -1 AND (RoleTypeCategory = 0  OR IsProductionTeam = 1)
END
GO
--#endregion  TK 2020-10-21

--#region TK 2020-10-30 
IF NOT EXISTS (SELECT * FROM sys.columns WHERE  object_id = OBJECT_ID('DutyRoster') AND [name] = 'ExemptionStatus3' AND user_type_id = 52)
BEGIN

	Print 'Alter DutyRoster.Start/EndTime1...'

	ALTER TABLE DutyRoster DROP CONSTRAINT CK_DutyRoster_Period1
	ALTER TABLE DutyRoster DROP CONSTRAINT CK_DutyRoster_Period2
	ALTER TABLE DutyRoster DROP CONSTRAINT CK_DutyRoster_Period3

	ALTER TABLE DutyRoster ALTER COLUMN StartTime1 TIME(0)
	ALTER TABLE DutyRoster ALTER COLUMN EndTime1 TIME(0)

	ALTER TABLE DutyRoster ALTER COLUMN StartTime2 TIME(0)
	ALTER TABLE DutyRoster ALTER COLUMN EndTime2 TIME(0)

	ALTER TABLE DutyRoster ALTER COLUMN StartTime3 TIME(0)
	ALTER TABLE DutyRoster ALTER COLUMN EndTime3 TIME(0)

	ALTER TABLE DutyRoster WITH CHECK ADD CONSTRAINT CK_DutyRoster_Period1 
		CHECK ((StartTime1 IS NULL AND EndTime1 IS NULL) OR (NOT StartTime1 IS NULL AND NOT EndTime1 IS NULL))
	ALTER TABLE DutyRoster CHECK CONSTRAINT CK_DutyRoster_Period1

	ALTER TABLE DutyRoster WITH CHECK ADD CONSTRAINT CK_DutyRoster_Period2 
		CHECK ((StartTime2 IS NULL AND EndTime2 IS NULL) OR (NOT StartTime2 IS NULL AND NOT EndTime2 IS NULL))
	ALTER TABLE DutyRoster CHECK CONSTRAINT CK_DutyRoster_Period2

	ALTER TABLE DutyRoster WITH CHECK ADD CONSTRAINT CK_DutyRoster_Period3 
		CHECK ((StartTime3 IS NULL AND EndTime3 IS NULL) OR (NOT StartTime3 IS NULL AND NOT EndTime3 IS NULL))
	ALTER TABLE DutyRoster CHECK CONSTRAINT CK_DutyRoster_Period3

	Print 'Alter DutyRoster.DebitWorkingMinutes...'
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_DebitWorkingMinutes
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_MiscWorkingMinutes
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_PaidOffWorkingMinutes

	ALTER TABLE DutyRoster ALTER COLUMN DebitWorkingMinutes SMALLINT NOT NULL
	UPDATE DutyRoster SET note = ISNULL(Note + ' ' , '') + CAST(MiscWorkingMinutes AS VARCHAR(100)), MiscWorkingMinutes = 0  WHERE MiscWorkingMinutes > 32767
	ALTER TABLE DutyRoster ALTER COLUMN MiscWorkingMinutes SMALLINT NOT NULL
	ALTER TABLE DutyRoster ALTER COLUMN PaidOffWorkingMinutes SMALLINT NOT NULL

	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_DebitWorkingMinutes DEFAULT 0 FOR DebitWorkingMinutes
	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_MiscWorkingMinutes DEFAULT 0 FOR MiscWorkingMinutes
	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_PaidOffWorkingMinutes DEFAULT 0 FOR PaidOffWorkingMinutes

	Print 'Alter DutyRoster.WorkingMinutes...'
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_WorkingMinutes1
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_WorkingMinutes2
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_WorkingMinutes3

	ALTER TABLE DutyRoster ALTER COLUMN WorkingMinutes1 SMALLINT NOT NULL
	ALTER TABLE DutyRoster ALTER COLUMN WorkingMinutes2 SMALLINT NOT NULL
	ALTER TABLE DutyRoster ALTER COLUMN WorkingMinutes3 SMALLINT NOT NULL

	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_WorkingMinutes1 DEFAULT 0 FOR WorkingMinutes1
	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_WorkingMinutes2 DEFAULT 0 FOR WorkingMinutes2
	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_WorkingMinutes3 DEFAULT 0 FOR WorkingMinutes3

	Print 'Alter DutyRoster.ExemptionStatus...'
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_ExemptionStatus1
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_ExemptionStatus2
	ALTER TABLE DutyRoster DROP CONSTRAINT DF_DutyRoster_ExemptionStatus3

	UPDATE DutyRoster SET IsPostedHealthy = 1, ExemptionStatus1 = 0 WHERE ExemptionStatus1 = 1024
	UPDATE DutyRoster SET IsPostedHealthy = 1, ExemptionStatus2 = 0 WHERE ExemptionStatus2 = 1024
	UPDATE DutyRoster SET IsPostedHealthy = 1, ExemptionStatus3 = 0 WHERE ExemptionStatus3 = 1024

	Print 'Alter DutyRoster.ExemptionStatus...TINYINT'
	ALTER TABLE DutyRoster ALTER COLUMN ExemptionStatus1 TINYINT NOT NULL
	ALTER TABLE DutyRoster ALTER COLUMN ExemptionStatus2 TINYINT NOT NULL
	ALTER TABLE DutyRoster ALTER COLUMN ExemptionStatus3 TINYINT NOT NULL

	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_ExemptionStatus1 DEFAULT 0 FOR ExemptionStatus1
	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_ExemptionStatus2 DEFAULT 0 FOR ExemptionStatus2
	ALTER TABLE DutyRoster ADD CONSTRAINT DF_DutyRoster_ExemptionStatus3 DEFAULT 0 FOR ExemptionStatus3

END
GO
ALTER PROCEDURE [dbo].[ShiftCountingByWorkingTime]
	@departmentId INT,
	@contractIds VARCHAR(MAX),
	@startDate DATE,
	@endDate DATE
AS
	BEGIN
	
		SELECT CAST(DepartmentWorkingTimeSettings AS XML) AS WorkingTimes 
		INTO #xml 
			FROM Departments 
			WHERE DepartmentId = @departmentId

		SELECT CAST( DATEADD(MINUTE, CAST(CAST(c.query('data(Start)') AS VARCHAR(4)) AS INT), '00:00') AS TIME(0)) AS zeit 
		INTO #times
			FROM #xml CROSS APPLY workingTimes.nodes('DepartmentWorkingTimeSettings/DayTimePartitions/DayTimePartition') x(c)

		DECLARE @latestTime   TIME(0) SELECT TOP 1 @latestTime = zeit   FROM #times ORDER BY zeit DESC
		DECLARE @earliestTime TIME(0) SELECT TOP 1 @earliestTime = zeit FROM #times ORDER BY zeit ASC

		SELECT * into #times2 FROM (
			SELECT t1.zeit AS aktuell, t2.zeit as [next]
			FROM #times AS t1
					INNER JOIN #times AS t2 ON t1.zeit <> t2.zeit 
						AND (t2.zeit = (SELECT TOP 1 zeit FROM #times WHERE zeit > t1.zeit))
						OR t1.zeit = @latestTime AND t2.zeit = @earliestTime) a
				ORDER BY aktuell
		
		SELECT * FROM (
			SELECT t.aktuell AS aktuell, d.ContractId, COUNT(1) AS COUNT FROM #times2 AS t
				CROSS JOIN DutyRoster AS d
				WHERE  d.DutyRosterTypeId = 0 AND d.ContractId IN (SELECT * FROM SplitIds(@contractIds))
					AND d.Date BETWEEN @startDate AND  @endDate
					AND (d.StartTime1 >= t.aktuell AND d.StartTime1 < t.[next]
						OR d.StartTime2 >= t.aktuell AND d.StartTime2 < t.[next]
						OR d.StartTime3 >= t.aktuell AND d.StartTime3 < t.[next]
						OR t.[next] = @earliestTime AND (t.aktuell <= d.StartTime1 OR t.aktuell <= d.StartTime2 OR t.aktuell <= d.StartTime3 OR t.[next] > d.StartTime1 OR t.[next] > d.StartTime2 OR t.[next] > d.StartTime3))
				GROUP BY t.aktuell, d.ContractId) AS DATA
	END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRoster') AND name = 'ShiftTypeId3')
BEGIN

	ALTER TABLE Bonus ALTER COLUMN Code NVARCHAR(3) NULL
	PRINT('Adding DutyRoster.ShiftTypeId3 ...')
	ALTER TABLE DutyRoster ADD ShiftTypeId3 INT NOT NULL CONSTRAINT DutyRoster_ShiftTypeId3 DEFAULT 0

	INSERT INTO ShiftTypes ([Name], Sort, Code, ShortName, TimeAccountId, lc, lm, uc, um, Note) SELECT LEFT([Name], 15), TimeAccountId, InfoChar, InfoChar, TimeAccountId, lc, lm, uc, um, '#Script#'  FROM TimeAccounts WHERE Formel = 2

	IF NOT EXISTS (SELECT * FROM ShiftTypes INNER JOIN TimeAccounts ON ShiftTypes.TimeAccountId = TimeAccounts.TimeAccountId WHERE ShiftTypeId = 0 AND TimeAccounts.Formel = 1)
	BEGIN
		IF NOT EXISTS (SELECT * FROM TimeAccounts WHERE TimeAccounts.Formel = 1)
		BEGIN
			IF EXISTS (SELECT * FROM TimeAccounts WHERE TimeAccounts.Name = 'Arbeitszeit' AND Formel = 0) 
			BEGIN
				Print 'Konto korrigiert'
				UPDATE TimeAccounts SET Formel = 1 WHERE TimeAccounts.Name = 'Arbeitszeit' AND Formel = 0 
			END
		END

		IF EXISTS (SELECT * FROM TimeAccounts WHERE TimeAccounts.Formel = 1)
			BEGIN
				UPDATE ShiftTypes SET TimeAccountId = (SELECT TOP 1 TimeAccountId FROM TimeAccounts WHERE TimeAccounts.Formel = 1) WHERE ShiftTypeId = 0 
			END
		ELSE
			BEGIN
				IF EXISTS (SELECT * FROM UserLicenses WHERE LicenseType = 2)
					BEGIN
						RAISERROR('In thea.perso ist ein Konto mit der Formel Arbeitszeit erforderlich', 20, -1) WITH LOG
						SET NOEXEC ON
					END
			END
	END
	
	UPDATE ShiftTypes SET [Name] = LEFT(TimeAccounts.[Name], 15)  FROM ShiftTypes INNER JOIN TimeAccounts ON ShiftTypes.TimeAccountId = TimeAccounts.TimeAccountId WHERE ShiftTypeId = 0

	EXEC('UPDATE DutyRoster SET ShiftTypeId1 = (SELECT ShiftTypeId FROM ShiftTypes WHERE Note = ''#Script#'' AND Sort = DutyRoster.TimeAccountId1 AND ShiftTypes.TimeAccountId = DutyRoster.TimeAccountId1 AND ShiftTypes.[Name] = Left(att.[Name],15) AND ShiftTypes.LM = att.LM) 
			FROM DutyRoster INNER JOIN TimeAccounts att ON DutyRoster.TimeAccountId1 = att.TimeAccountId
			WHERE ShiftTypeId1 = 0 AND att.Formel = 2')

	EXEC('UPDATE DutyRoster SET ShiftTypeId2 = (SELECT ShiftTypeId FROM ShiftTypes WHERE Note = ''#Script#'' AND Sort = DutyRoster.TimeAccountId2 AND ShiftTypes.TimeAccountId = DutyRoster.TimeAccountId2 AND ShiftTypes.[Name] = Left(att.[Name],15) AND ShiftTypes.LM = att.LM) 
			FROM DutyRoster INNER JOIN TimeAccounts att ON DutyRoster.TimeAccountId2 = att.TimeAccountId
			WHERE ShiftTypeId2 = 0 AND att.Formel = 2')

	EXEC('UPDATE DutyRoster SET ShiftTypeId3 = (SELECT ShiftTypeId FROM ShiftTypes WHERE Note = ''#Script#'' AND Sort = DutyRoster.TimeAccountId3 AND ShiftTypes.TimeAccountId = DutyRoster.TimeAccountId3 AND ShiftTypes.[Name] = Left(att.[Name],15) AND ShiftTypes.LM = att.LM) 
			FROM DutyRoster INNER JOIN TimeAccounts att ON DutyRoster.TimeAccountId3 = att.TimeAccountId
			WHERE ShiftTypeId3 = 0 AND att.Formel = 2')

	ALTER TABLE dbo.DutyRoster ADD CONSTRAINT FK_DutyRoster_ShiftTypes_ShiftTypeId3 FOREIGN KEY (ShiftTypeId3) REFERENCES dbo.ShiftTypes (ShiftTypeId)

	--spalten erstmal umbennen, löschen folgt später
	EXEC sp_rename 'DutyRoster.TimeAccountId1', 'X_TimeAccountId1', 'COLUMN';
	EXEC sp_rename 'DutyRoster.TimeAccountId2', 'X_TimeAccountId2', 'COLUMN';
	EXEC sp_rename 'DutyRoster.TimeAccountId3', 'X_TimeAccountId3', 'COLUMN';

	--constraints schonmal entfernen, da die spalten ja nicht wirklich mehr genutzt werden
	ALTER TABLE dbo.DutyRoster DROP CONSTRAINT FK_DutyRoster_TimeAccounts_TimeAccountId1
	ALTER TABLE dbo.DutyRoster DROP CONSTRAINT FK_DutyRoster_TimeAccounts_TimeAccountId2
	ALTER TABLE dbo.DutyRoster DROP CONSTRAINT FK_DutyRoster_TimeAccounts_TimeAccountId3
	
	Print 'Clean DutyRoster...'
	DBCC CLEANTABLE (0,'DutyRoster', 0) WITH NO_INFOMSGS
	
	Print 'Rebuild Index DutyRoster...'
	ALTER INDEX ALL ON DutyRoster REBUILD

	ALTER TABLE WorkVersions ALTER COLUMN OpusNumber VARCHAR(255) NULL
	
END
GO
--#endregion

--#region DH 2020-10-27
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].GetPersonsInternal'))	DROP PROCEDURE dbo.[GetPersonsInternal]
GO
CREATE PROCEDURE [dbo].[GetPersonsInternal]
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT,
	@ShowPersonsWithoutRights BIT 
	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
                    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		
					) AS sub ON pk.PersonId = sub.PersonId

	IF (@include&2 = 2)
		SELECT NULL AS [#2], PersonAddress.*
		FROM PersonAddress
			INNER JOIN #includePermissions ON PersonAddress.PersonId = #includePermissions.PersonId WHERE  PersonAddress.IsSecret=0 OR readSecret > 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<>0) 
	BEGIN	
		DROP TABLE #includePermissions
		DROP TABLE #PersonsNP
	END
	DROP TABLE #UserGroupPermissions
END
GO
--#endregion DH 2020-10-27

--#region TK
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Departments') AND name = 'ScheduleRuleSettings')
BEGIN
	PRINT('Adding FK_Departments_CostCenters ...')

	UPDATE Departments SET DefaultCostCenterId = NULL WHERE  DefaultCostCenterId NOT IN (SELECT CostCenterId FROM CostCenters)
	ALTER TABLE dbo.Departments ADD CONSTRAINT FK_Departments_CostCenters FOREIGN KEY (DefaultCostCenterId) REFERENCES dbo.CostCenters (CostCenterId) ON DELETE SET NULL
	
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_Departments_ShowProductionColor') ALTER TABLE Departments DROP CONSTRAINT DF_Departments_ShowProductionColor
	ALTER TABLE dbo.Departments DROP COLUMN ShowProductionColor
	IF EXISTS (SELECT * FROM sys.default_constraints dc WHERE  dc.name = 'DF_Departments_VisibleLayer') ALTER TABLE Departments DROP CONSTRAINT DF_Departments_VisibleLayer
	ALTER TABLE dbo.Departments DROP COLUMN VisibleLayer

	PRINT('Adding Departments.ScheduleRuleSettings ...')
	ALTER TABLE Departments ADD ScheduleRuleSettings XML NULL
END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('RoleTypes') AND name = 'TemplateCastPersonId')
BEGIN
	UPDATE Departments 
		SET ScheduleRuleSettings = '<DutyRosterScheduleRuleSettings>'+
			CASE WHEN NOT TimePartitionMinutes IS NULL AND TimePartitionMinutes <> 0 THEN
				'<UseTimePartitionEditor>true</UseTimePartitionEditor><TimePartitionMinutes>' + CAST(TimePartitionMinutes AS VARCHAR(2)) + '</TimePartitionMinutes>' 
			ELSE 
				'' 
			END +
			CASE WHEN EXISTS (SELECT * FROM Settings WHERE Section = 'DutyRoster' AND KeyName = 'AutoScheduleTasks') THEN 
				'<AutoScheduleTasks>true</AutoScheduleTasks>'
			ELSE 
				'' 
			END + 
				'</DutyRosterScheduleRuleSettings>'
  
	DELETE FROM Settings WHERE Section  = 'DutyRoster' AND KeyName = 'AutoScheduleTasks'
	
	UPDATE Departments 
		SET HeaderSettings = REPLACE(CAST(HeaderSettings AS VARCHAR(MAX)),'</DutyRosterHeaderSettings>','<ActivitiesTimePartitions>' + ActivitiesTimePartitions + '</ActivitiesTimePartitions></DutyRosterHeaderSettings>')
		WHERE NOT HeaderSettings IS NULL  AND NOT  ActivitiesTimePartitions IS NULL AND ActivitiesTimePartitions <> '' 

	UPDATE RoleTypes SET WorkAreaId = NULL WHERE  WorkAreaId NOT IN (SELECT WorkAreaId FROM WorkAreas)
	ALTER TABLE dbo.RoleTypes ADD CONSTRAINT FK_RoleTypes_WorkAreas FOREIGN KEY (WorkAreaId) REFERENCES dbo.WorkAreas (WorkAreaId) ON DELETE SET NULL

	--EXEC sp_rename 'Departments.ActivitiesTimePartitions', 'X_ActivitiesTimePartitions', 'COLUMN'
	--EXEC sp_rename 'Departments.TimePartitionMinutes', 'X_TimePartitionMinutes', 'COLUMN'

	ALTER TABLE RoleTypes ADD TemplateCastPersonId INT NULL
	ALTER TABLE dbo.RoleTypes ADD CONSTRAINT FK_RoleTypes_Persons FOREIGN KEY (TemplateCastPersonId) REFERENCES dbo.Persons (PersonId) ON DELETE SET NULL
END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('CostUnitWorkingTimes') AND name = 'PeriodSource')
BEGIN
	PRINT('Adding CostUnitWorkingTimes.PeriodSource ...')
	ALTER TABLE CostUnitWorkingTimes ADD PeriodSource TINYINT NOT NULL CONSTRAINT DF_CostUnitWorkingTimes_PeriodSource DEFAULT 0
	ALTER TABLE CostUnitWorkingTimes ALTER COLUMN [Minutes] SMALLINT NOT NULL
	EXEC ('UPDATE CostUnitWorkingTimes SET PeriodSource = 1 WHERE [Minutes] = 0')

	EXEC ('ALTER TABLE CostUnitWorkingTimes WITH CHECK ADD CONSTRAINT CK_CostUnitWorkingTimes_MinutesOrPeriodSourc
		CHECK (([Minutes] = 0 AND PeriodSource > 0) OR ([Minutes] <> 0 AND PeriodSource = 0))
	ALTER TABLE DutyRoster CHECK CONSTRAINT CK_DutyRoster_Period1')

END
GO
--#endregion TK

--region SH 2020-11-12 SalaryLevelData.ServiceRate

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('SalaryLevelData') AND [name] = 'ServiceRate')
BEGIN
	PRINT('Adding SalaryLevelData.ServiceRate ...')
	ALTER TABLE dbo.SalaryLevelData ADD ServiceRate FLOAT NOT NULL  
	CONSTRAINT DF_SalaryLevelData_ServiceRate DEFAULT 0
END
GO

--endregion SH 2020-11-12 SalaryLevelData.ServiceRate

--region
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'MergeBasicCastSeasons')) DROP PROCEDURE MergeBasicCastSeasons
GO
CREATE PROCEDURE [dbo].[MergeBasicCastSeasons]
AS
BEGIN
	--Wenn eine Rollen (mit gleichen Position, UnderstudyPosition) nur mit einer Person besetzt ist, alle "doppelten Einträge entfernen"
	DELETE FROM Basiccast WHERE BasiccastId IN 
	(
		SELECT  BasiccastId FROM Basiccast 
			INNER JOIN (SELECT MIN(BasiccastId) AS m, b.RoleId, GroupPosition, UnderstudyPosition
							FROM Basiccast AS b
							GROUP BY b.RoleId, GroupPosition, UnderstudyPosition
							HAVING COUNT(DISTINCT Personid) = 1 AND COUNT(*) > 1
						) AS sub
					ON Basiccast.RoleId = sub.RoleId 
					AND BasicCast.GroupPosition = sub.GroupPosition 
					AND BasicCast.UnderstudyPosition = sub.UnderstudyPosition 
					AND BasicCast.BasiccastId <> sub.m 
	)

	--Bei allen Rollen (mit gleichen Position, UnderstudyPosition mit nur einer Besetzung den Zeitraum entfernen
	UPDATE BasicCast SET StartDate = NULL, EndDate = NULL 
					 WHERE BasicCastId IN (
											SELECT MIN(BasiccastId) AS m
											FROM Basiccast AS b
											GROUP BY b.RoleId, GroupPosition, UnderstudyPosition
											HAVING COUNT(*) = 1
											)
	
	--Bei allen Rollen (mit gleichen Position, UnderstudyPosition) und gleicher Persone, zeitlich direkt nacheinander liegende Besetzungen zusammenfassen 
	DECLARE @id  INT, @roleId  INT, @personId  INT, @groupPosition  SMALLINT, @understudyPosition  TINYINT, @start DATE, @end DATE
	DECLARE @oId INT, @oRoleId INT, @oPersonId INT, @oGroupPosition SMALLINT, @oUnderstudyPosition TINYINT, @oEnd DATE

	SET @oEnd = '19000101'
	DECLARE cview_cursor CURSOR FOR 	
		SELECT  BasiccastId, RoleId, PersonId, GroupPosition, UnderstudyPosition, ISNULL(StartDate,'19000101'), ISNULL(EndDate,'30000101')
		FROM Basiccast
		ORDER BY RoleId, PersonId, GroupPosition, UnderstudyPosition, StartDate

	OPEN cview_cursor 
	FETCH NEXT FROM cview_cursor 
	INTO @id, @roleId, @personId, @groupPosition, @understudyPosition, @start, @end
	WHILE @@FETCH_STATUS = 0 
	BEGIN  
		IF (@oRoleId = @roleId AND @oGroupPosition = @groupPosition AND @oUnderstudyPosition = @understudyPosition AND @oPersonId = @personId AND DATEADD(d, 1, @oEnd) = @start)
			BEGIN
				UPDATE BasicCast SET EndDate = @end WHERE  BasicCastId = @oId
				PRINT 'DELETE'
				DELETE FROM Basiccast WHERE BasiccastId = @id
				SET @oEnd = @end
			END
		ELSE
			BEGIN
			PRINT CAST(@oRoleId AS VARCHAR) 
				SET @oRoleId = @roleId 
				SET @oPersonId = @personId
				SET @oGroupPosition = @groupPosition
				SET @oUnderstudyPosition = @understudyPosition
				SET @oId = @id
				SET @oEnd = @end
			END
		
		FETCH NEXT FROM cview_cursor 
		INTO @id, @roleId, @personId, @groupPosition, @understudyPosition, @start, @end
	END 
	CLOSE cview_cursor 
	DEALLOCATE cview_cursor 
END
GO
--endregion 

--#region
IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('RoleTypes') AND name = 'CalculationSalaryLevelId')
BEGIN
	ALTER TABLE RoleTypes ADD CalculationSalaryLevelId INT NULL
	ALTER TABLE RoleTypes ADD CONSTRAINT FK_RoleTypes_SalaryLevel FOREIGN KEY (CalculationSalaryLevelId) REFERENCES dbo.SalaryLevel (SalaryLevelId) 
END
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRosterTypes') AND name = 'DutyRosterTypeCategory')
BEGIN
	IF EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_Updatelog_TransactionId' AND object_id = OBJECT_ID(N'dbo.updateLog')) DROP INDEX updateLog.IX_Updatelog_TransactionId
	PRINT 'Add IX_Updatelog_TransactionId'
	CREATE NONCLUSTERED INDEX IX_Updatelog_TransactionId ON updateLog (TransactionId ) WHERE (TransactionId IS NOT NULL)
	PRINT 'Add SET updateLog DATETIME2'
	ALTER TABLE updateLog ALTER COLUMN LC DATETIME2(3) NOT NULL

	PRINT 'Add SET PeriodNotes DATE'
	ALTER TABLE PeriodNotes ALTER COLUMN Start DATE NOT NULL
	ALTER TABLE PeriodNotes ALTER COLUMN [End] DATE NOT NULL

	PRINT 'Add SET ResourceBlockPeriods DATE'
	ALTER TABLE ResourceBlockPeriods ALTER COLUMN Start DATE NOT NULL
	ALTER TABLE ResourceBlockPeriods ALTER COLUMN [End] DATE NOT NULL

	PRINT 'Add SET ActivityDetails DATE'
	ALTER TABLE ActivityDetails ALTER COLUMN SaleStartDate DATE NULL
	ALTER TABLE ActivityDetails ALTER COLUMN PublishInWebFrom DATE NULL

	ALTER TABLE Orders ALTER COLUMN AccountancyPrintDate SMALLDATETIME NULL
	ALTER TABLE ContractDates ALTER COLUMN Start SMALLDATETIME  NULL
	ALTER TABLE ContractDates ALTER COLUMN [End] SMALLDATETIME NULL
	   
	PRINT('Adding DutyRosterTypes.DutyRosterTypeCategory ...')
	ALTER TABLE DutyRosterTypes ADD DutyRosterTypeCategory TINYINT NOT NULL CONSTRAINT DF_DutyRosterTypes_DutyRosterTypeCategory DEFAULT 2
	EXEC ('CREATE UNIQUE INDEX DutyRosterTypes_DutyRosterTypeCategory ON DutyRosterTypes (DutyRosterTypeCategory) WHERE DutyRosterTypeCategory <= 1')
	EXEC ('UPDATE DutyRosterTypes SET DutyRosterTypeCategory = DutyRosterTypeId WHERE DutyRosterTypeId <= 1')
	
	ALTER TABLE DutyRosterTypes ADD StartDate DATE NULL
	ALTER TABLE DutyRosterTypes ADD EndDate DATE NULL

	EXEC ('ALTER TABLE DutyRosterTypes WITH CHECK ADD CONSTRAINT CK_DutyRosterTypes_PeriodCheck CHECK (StartDate <= EndDate)')
	ALTER TABLE DutyRosterTypes CHECK CONSTRAINT CK_DutyRosterTypes_PeriodCheck

	IF ((SELECT COUNT(*) FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 100 ) = 1) 
	BEGIN
		UPDATE RoleTypes SET TemplateCastPersonId = (SELECT TOP 1 PersonId FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 100) 
		WHERE RoleTypes.RoleTypeCategory = 100
	END

	IF ((SELECT COUNT(*) FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 110 ) = 1) 
	BEGIN
		UPDATE RoleTypes SET TemplateCastPersonId = (SELECT TOP 1 PersonId FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 110) 
		WHERE RoleTypes.RoleTypeCategory = 110
	END

	IF ((SELECT COUNT(*) FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 120 ) = 1) 
	BEGIN
		UPDATE RoleTypes SET TemplateCastPersonId = (SELECT TOP 1 PersonId FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 120) 
		WHERE RoleTypes.RoleTypeCategory = 120
	END

	IF ((SELECT COUNT(*) FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 130 ) = 1) 
	BEGIN
		UPDATE RoleTypes SET TemplateCastPersonId = (SELECT TOP 1 PersonId FROM Persons p INNER JOIN PersonCategories pc ON p.PersonCategoryId = pc.PersonCategoryId WHERE pc.PersonCategoryType = 130) 
		WHERE RoleTypes.RoleTypeCategory = 130
	END
END
GO
--#endregion

--#region

IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('OwnerGroups') AND name = 'VacantCastCalculation')
BEGIN
	PRINT('Adding OwnerGroups.VacantCastCalculation ...')
	ALTER TABLE OwnerGroups ADD VacantCastCalculation TINYINT NOT NULL CONSTRAINT DF_OwnerGroups_VacantCastCalculation DEFAULT 0
	EXEC('UPDATE OwnerGroups SET VacantCastCalculation = 1 WHERE Modul = 1 AND OwnerGroupId > -1')
	EXEC('UPDATE OwnerGroups SET VacantCastCalculation = 2 WHERE Modul = 16')
	EXEC('UPDATE OwnerGroups SET VacantCastCalculation = 3 WHERE Modul = 256')
END
GO
--#endregion

--#region ProductionTimeFlow
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('ProductionTimeFlow') AND [name] = 'ChildProductionId')
BEGIN
	
	PRINT 'Create ProductionTimeFlow'

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_Scenes_ProductionTimeFlow_ActId') AND type = 'F') ALTER TABLE dbo.Scenes DROP CONSTRAINT FK_Scenes_ProductionTimeFlow_ActId
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.PK_ProductionTimeFlow')) ALTER TABLE dbo.ProductionTimeFlow DROP CONSTRAINT PK_ProductionTimeFlow
	ALTER TABLE dbo.ProductionTimeFlow ALTER COLUMN Position SMALLINT NOT NULL
	ALTER TABLE dbo.ProductionTimeFlow ALTER COLUMN Duration SMALLINT NOT NULL
	CREATE CLUSTERED INDEX IX_ProductionTimeFlow_ProductionId_Position ON dbo.ProductionTimeFlow (ProductionId, Position)

	ALTER TABLE dbo.ProductionTimeFlow ADD CONSTRAINT PK_ProductionTimeFlow PRIMARY KEY (ProductionTimeFlowId) ON [PRIMARY]		
	ALTER TABLE dbo.Scenes ADD CONSTRAINT FK_Scenes_ProductionTimeFlow FOREIGN KEY (ActId) REFERENCES dbo.ProductionTimeFlow (ProductionTimeFlowId) 
	IF EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ProductionId' AND object_id = OBJECT_ID(N'dbo.ProductionTimeFlow')) DROP INDEX ProductionTimeFlow.IX_ProductionId
		
	PRINT('Adding ProductionTimeFlow.ChildProductionId ...')
	ALTER TABLE dbo.ProductionTimeFlow ADD ChildProductionId INT NULL  
	ALTER TABLE dbo.ProductionTimeFlow ADD WorkVersionId INT NULL  
	ALTER TABLE dbo.Productions ADD WorkVersionId INT NULL  
	EXEC('CREATE UNIQUE INDEX IX_ProductionTimeFlow_ChildProduction ON dbo.ProductionTimeFlow (ChildProductionId, ProductionId)  WHERE (ChildProductionId IS NOT NULL)')
	EXEC('CREATE UNIQUE INDEX IX_ProductionTimeFlow_WorkVersionId ON dbo.ProductionTimeFlow (WorkVersionId, ProductionId)  WHERE (WorkVersionId IS NOT NULL)')
	ALTER TABLE dbo.ProductionTimeFlow ADD CONSTRAINT FK_ProductionTimeFlow_WorkVersions FOREIGN KEY (WorkVersionId) REFERENCES dbo.WorkVersions (WorkVersionId) 
	ALTER TABLE dbo.ProductionTimeFlow ADD CONSTRAINT FK_ProductionTimeFlow_ChildProduction FOREIGN KEY (ChildProductionId) REFERENCES dbo.Productions (ProductionId) 

	ALTER TABLE dbo.Productions ADD CONSTRAINT FK_Productions_WorkVersions FOREIGN KEY (WorkVersionId) REFERENCES dbo.WorkVersions (WorkVersionId) 
	
	EXEC('INSERT ProductionTimeFlow (ProductionId, ChildProductionId, Position, Duration, LM, UM, LC, UC) 
		SELECT ProductionId, ChildProductionId, Sort, 0, LM, ISNULL(UM,-1), LC, ISNULL(UC,-1) FROM ProductionChilds WHERE ProductionId <> ChildProductionId')
		
	EXEC('INSERT ProductionTimeFlow (ProductionId, WorkVersionId, Position, Duration, LM, UM, LC, UC) 
		SELECT ProductionId, WorkVersionId, OrderNumber, 0, LM, ISNULL(UM,-1), LC, ISNULL(UC,-1)  
			FROM ProductionsWorkVersions WHERE ProductionId IN (SELECT ProductionId FROM ProductionsWorkVersions GROUP BY ProductionId HAVING COUNT(*) > 1)')

	EXEC('UPDATE Productions SET WorkVersionId = pwv.wvId FROM Productions INNER JOIN (SELECT ProductionId, MIN(WorkVersionId) AS wvId FROM ProductionsWorkVersions GROUP BY ProductionId HAVING COUNT(*) = 1) AS pwv ON Productions.ProductionId = pwv.ProductionId')

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ProductionChilds_Productions') AND type = 'F') ALTER TABLE dbo.ProductionChilds DROP CONSTRAINT FK_ProductionChilds_Productions
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ProductionChilds_Productions_ChildProductionId') AND type = 'F') ALTER TABLE dbo.ProductionChilds DROP CONSTRAINT FK_ProductionChilds_Productions_ChildProductionId

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ProductionsWorkVersions_Productions') AND type = 'F') ALTER TABLE dbo.ProductionsWorkVersions DROP CONSTRAINT FK_ProductionsWorkVersions_Productions
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.FK_ProductionsWorkVersions_WorkVersions') AND type = 'F') ALTER TABLE dbo.ProductionsWorkVersions DROP CONSTRAINT FK_ProductionsWorkVersions_WorkVersions
	EXEC sp_rename 'ProductionChilds', 'X_ProductionChilds'
	EXEC sp_rename 'ProductionsWorkVersions', 'X_ProductionsWorkVersions'
	IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'dbo.AbProductionsWorkVersions_Update'))  DROP VIEW  AbProductionsWorkVersions_Update

END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.ProductionParts_Update')) DROP VIEW ProductionParts_Update
GO
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.SetWorkVersionReferenceToProduction')) DROP PROCEDURE dbo.SetWorkVersionReferenceToProduction
GO
CREATE PROCEDURE dbo.SetWorkVersionReferenceToProduction
	@productionId INT, @workVersionId INT
AS
BEGIN
	SET NOCOUNT ON
	UPDATE Productions SET WorkVersionId = @workVersionId WHERE ProductionId = @productionId
END	
GRANT EXECUTE ON SetWorkVersionReferenceToProduction TO WorksWrite 
GO

ALTER PROCEDURE dbo.DeleteProduction
	@ProductionId INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @UserGroup int
SELECT @UserGroup = OwnerGroupId FROM Productions WHERE  ProductionId = @ProductionId

--Berechtigungsprüfung
IF (dbo.GetCurrentPermission(@UserGroup, 3) < 2 ) 
	BEGIN
		SELECT 1 AS Attribut, 'No Permissions' AS MSG
	END
ELSE
	BEGIN 
		IF (@ProductionId = 0)	
		BEGIN
			SELECT 0 AS Attribut,'Keine gültige Produktion übergeben (Id 0)' AS MSG
		END
		ELSE
		BEGIN
			DELETE FROM BasicCast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM ContractEngagements WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM CastRepertoire WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM ContractDateConfirmations WHERE  ContractDateId IN (SELECT ContractDateId FROM ContractDates 
				WHERE CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)))
			DELETE FROM ContractDates WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastBonus WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CashCommandPos_Cast WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastCredits WHERE  ServiceCastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM CastCredits WHERE  ReplacementCastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			UPDATE ContractDates SET CastId = NULL WHERE  CastId IN (SELECT CastId FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId))
			DELETE FROM Cast WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM TechnicalJobs WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM RoleBonus WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Role_Season WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Roles_Scenes WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Qualification_Role WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)
			DELETE FROM Roles WHERE  RoleId IN (SELECT RoleId FROM Roles WHERE  ProductionId = @ProductionId)	
			DELETE FROM ProductionTimeFlow WHERE  ProductionId = @ProductionId
			DELETE FROM ProductionTimeFlow WHERE  ChildProductionId = @ProductionId
			DELETE FROM ActivityScenes WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDepartmentData WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM TechnicalJobs WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityCosts WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDetails WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM JourneyTimetables WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM CostUnitWorkingTimes WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivityDetails WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ActivitySubscriptions WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM Activities WHERE  ActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			UPDATE Activities SET LinkedActivityId = NULL WHERE  LinkedActivityId IN (SELECT ActivityId FROM Activities WHERE  ProductionId = @ProductionId)
			DELETE FROM ProductionDetails WHERE  ProductionId = @ProductionId
			DELETE FROM ProductionInSeasons WHERE  ProductionId = @ProductionId
		END
	END
END
GO

ALTER PROCEDURE dbo.GetPossibleCastByPeriodInternal
	@start DATE,
	@end DATE,
	@personIds IdFilterTable READONLY,
	@activityTypeIds IdFilterTable READONLY,
	@variantIds IdFilterTable READONLY,
	@understudyPosition TINYINT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @permissionPeriodStart DATETIME
		DECLARE @permissionPeriodEnd DATETIME

		-- *** 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 
			SELECT @start = '30000101'
			SELECT @end = '30000101'
		END
		ELSE
		BEGIN
			IF (@start < @permissionPeriodStart)	BEGIN SELECT @start = @permissionPeriodStart END
			IF (@end > @permissionPeriodEnd)		BEGIN SELECT @end = @permissionPeriodEnd END
		END

	IF  EXISTS(SELECT * FROM @activityTypeIds)
	BEGIN
	--Mit Activitytype Filter
	SELECT DISTINCT d.PersonId, d.ActivityId, c.CastId, d.RoleId, IsMandatory
		FROM (
				SELECT DISTINCT a.ActivityId, bs.RoleId, need.IsMandatory, bs.PersonId
				FROM Activities AS a 
					INNER JOIN ProductionTimeFlow AS st ON a.ProductionId = st.ProductionId
					INNER JOIN Productions AS p ON st.ProductionId = p.ProductionId
					INNER JOIN Roles AS r ON st.ChildProductionId = r.ProductionId OR st.ProductionId = r.ProductionId 
					INNER JOIN BasicCast AS bs ON r.RoleId = bs.RoleId 
												AND bs.UnderstudyPosition < @understudyPosition 
												AND (bs.StartDate <= a.Date OR bs.StartDate IS NULL) AND (bs.EndDate >= a.Date OR bs.EndDate IS NULL)
					INNER JOIN CastRules AS need ON r.RoleTypeId = need.RoleTypeId AND a.ActivityTypeId = need.ActivityTypeId
				WHERE Date BETWEEN @start AND @end AND VariantId IN (SELECT id FROM @variantIds) AND a.Status >= 0
					AND bs.PersonId IN (SELECT Id FROM @personIds)
					AND a.ActivityTypeId IN (SELECT Id FROM @activityTypeIds)
					AND p.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE UserId = @userId AND PermissionArea = 2)
					AND r.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE UserId = @userId AND PermissionArea = 4)
			) AS d
			LEFT JOIN ( SELECT * 
						FROM Cast
						WHERE IsCanceled = 0 AND IsCover = 0 ) AS c 
				ON d.ActivityId = c.ActivityId AND d.RoleId = c.RoleId
			WHERE c.CastId IS NULL OR c.PersonId <> d.PersonId
			ORDER BY d.PersonId, d.ActivityId
	END 
	ELSE
	BEGIN
	--Ohne Activitytype Filter
	SELECT DISTINCT d.PersonId, d.ActivityId, c.CastId, d.RoleId, IsMandatory
		FROM (
				SELECT DISTINCT a.ActivityId, a.Date, StartTime, EndTime, bs.RoleId, need.IsMandatory, bs.PersonId
				FROM Activities AS a 
					INNER JOIN ProductionTimeFlow AS st ON a.ProductionId = st.ProductionId
					INNER JOIN Productions AS p ON st.ProductionId = p.ProductionId
					INNER JOIN Roles AS r ON st.ChildProductionId = r.ProductionId OR st.ProductionId = r.ProductionId
					INNER JOIN BasicCast AS bs ON r.RoleId = bs.RoleId 
												AND bs.UnderstudyPosition < @understudyPosition 
												AND (bs.StartDate <= a.Date OR bs.StartDate IS NULL) AND (bs.EndDate >= a.Date OR bs.EndDate IS NULL)
					INNER JOIN CastRules AS need ON r.RoleTypeId = need.RoleTypeId AND a.ActivityTypeId = need.ActivityTypeId
				WHERE Date BETWEEN @start AND @end AND VariantId IN (SELECT id FROM @variantIds) AND a.Status >= 0
					AND bs.PersonId IN (SELECT Id FROM @personIds)
					AND p.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE UserId = @userId AND PermissionArea = 2)
					AND r.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE UserId = @userId AND PermissionArea = 4)
			) AS d
			LEFT JOIN ( SELECT * 
						FROM Cast 
						WHERE IsCanceled = 0 AND IsCover = 0 ) AS c 
				ON d.ActivityId = c.ActivityId AND d.RoleId = c.RoleId  
			WHERE c.CastId IS NULL OR c.PersonId <> d.PersonId
			ORDER BY d.PersonId, d.ActivityId
	END
END
GO
ALTER PROCEDURE dbo.RoleCastInWrongActivityWarning
		@infoOnly bit
		WITH EXECUTE AS 'dbo'
		AS
		BEGIN
		
		IF(@infoOnly=0)
		BEGIN
			DELETE FROM CastBonus 
				WHERE CastId IN (SELECT BID FROM (SELECT Cast.CastId as bid, Activities.ProductionId as asnr, Roles.ProductionId as bsnr, Activities.Date, Roles.Name
									FROM Activities 
										INNER JOIN Cast ON Activities.ActivityId = Cast.ActivityId
										INNER JOIN Roles ON Roles.RoleId = Cast.RoleId
									WHERE (Roles.ProductionId <> Activities.ProductionId AND Roles.RoleId <> 0 AND Roles.ProductionId <> 0)) AS a 
										LEFT JOIN ProductionTimeFlow ON a.ASNr = ProductionTimeFlow.ProductionId AND a.BSnr = ProductionTimeFlow.ChildProductionId WHERE ChildProductionId IS NULL);

			DELETE FROM Cast 
				WHERE CastId IN (SELECT BID FROM (SELECT Cast.CastId as bid, Activities.ProductionId as asnr, Roles.ProductionId as bsnr, Activities.Date, Roles.Name
									FROM Activities 
										INNER JOIN Cast ON Activities.ActivityId = Cast.ActivityId
										INNER JOIN Roles ON Roles.RoleId = Cast.RoleId
									WHERE (Roles.ProductionId <> Activities.ProductionId AND Roles.RoleId <> 0 AND Roles.ProductionId <> 0)) AS a 
										LEFT JOIN ProductionTimeFlow ON a.ASNr = ProductionTimeFlow.ProductionId AND a.BSnr = ProductionTimeFlow.ChildProductionId WHERE ChildProductionId IS NULL);
			END
		ELSE
		BEGIN
			SELECT a.Date, a.Name
			FROM (SELECT Cast.CastId as bid, Activities.ProductionId as asnr, Roles.ProductionId as bsnr, Activities.Date, Roles.Name
					FROM Activities 
						INNER JOIN Cast ON Activities.ActivityId = Cast.ActivityId
						INNER JOIN Roles ON Roles.RoleId = Cast.RoleId
						WHERE (Roles.ProductionId <> Activities.ProductionId AND Roles.RoleId <> 0 AND Roles.ProductionId <> 0)) AS a 
						LEFT JOIN ProductionTimeFlow ON a.ASNr = ProductionTimeFlow.ProductionId AND a.BSnr = ProductionTimeFlow.ChildProductionId WHERE ChildProductionId IS NULL;
		END
	END
GO
--#endregion  ProductionTimeFlow

--#region  2020-11-07 (2020-05-19)
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'SalaryLevelId' AND o.id = OBJECT_ID('Role_Season'))	
BEGIN
	UPDATE Roles SET CalculationSalaryLevelId = (SELECT TOP 1 CalculationSalaryLevelId FROM Role_Season WHERE Role_Season.RoleId = Roles.RoleId ) WHERE CalculationSalaryLevelId  IS NULL
	ALTER TABLE Role_Season DROP CONSTRAINT FK_Role_Season
	EXEC sp_rename  'Role_Season.SalaryLevelId', 'x_SalaryLevelId', 'COLUMN'
	IF EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_UserName' AND object_id = OBJECT_ID(N'dbo.Users')) DROP INDEX Users.IX_UserName
	UPDATE Users SET UserName = '_NN_'+ CAST(UserID AS SYSNAME) WHERE UserName IS NULL
	ALTER TABLE Users ALTER COLUMN UserName SYSNAME NOT NULL 
	CREATE UNIQUE INDEX IX_UserName ON dbo.Users (UserName)
	ALTER TABLE Users ALTER COLUMN LastLoginDate SMALLDATETIME NULL
END
GO
--#endregion 2020-11-07 (2020-05-19)

--#region 2020-11-08
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_ActivityCosts_ActivityId_ProductionCostTypeId' AND object_id = OBJECT_ID(N'dbo.ActivityCosts'))
BEGIN

	IF EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_Activities_ResourceObjects_ActivityId' AND object_id = OBJECT_ID(N'dbo.Activities_ResourceObjects')) DROP INDEX Activities_ResourceObjects.IX_Activities_ResourceObjects_ActivityId
	IF EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_Activities_ResourceObjects_ActivityId_ResourceObjectId' AND object_id = OBJECT_ID(N'dbo.Activities_ResourceObjects')) DROP INDEX Activities_ResourceObjects.IX_Activities_ResourceObjects_ActivityId_ResourceObjectId
	CREATE UNIQUE CLUSTERED INDEX IX_Activities_ResourceObjects_ActivityId_ResourceObjectId ON dbo.Activities_ResourceObjects (ActivityId, ResourceObjectId)

	IF EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_ActivityDepartment_ActivityId' AND object_id = OBJECT_ID(N'dbo.ActivityDepartmentData')) DROP INDEX ActivityDepartmentData.IX_ActivityDepartment_ActivityId
	IF EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_ActivityDepartment_ActivityId_DepartmentId' AND object_id = OBJECT_ID(N'dbo.ActivityDepartmentData')) DROP INDEX ActivityDepartmentData.IX_ActivityDepartment_ActivityId_DepartmentId
	ALTER TABLE ActivityDepartmentData DROP CONSTRAINT PK_ActivityDepartmentData
	CREATE UNIQUE CLUSTERED INDEX IX_ActivityDepartmentData_ActivityId_DepartmentId ON dbo.ActivityDepartmentData (ActivityId, DepartmentId)
	ALTER TABLE ActivityDepartmentData ADD CONSTRAINT PK_ActivityDepartmentData PRIMARY KEY (ActivityDepartmentDataId) ON [PRIMARY]	

	IF EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_ActivityProductionFieldData_ActivityIdProductionFieldTypeId' AND object_id = OBJECT_ID(N'dbo.ActivityProductionFieldData')) DROP INDEX ActivityProductionFieldData.IX_ActivityProductionFieldData_ActivityIdProductionFieldTypeId
	IF EXISTS (SELECT * FROM sys.indexes WHERE name  = 'IX_ActivityProductionFieldDataa_Activity' AND object_id = OBJECT_ID(N'dbo.ActivityProductionFieldData')) DROP INDEX ActivityProductionFieldData.IX_ActivityProductionFieldDataa_Activity
	CREATE UNIQUE CLUSTERED INDEX IX_ActivityProductionFieldData_ActivityId_ProductionFieldTypeId ON dbo.ActivityProductionFieldData (ActivityId, ProductionFieldTypeId)

	DELETE FROM ActivityCosts WHERE ActivityId IS NULL OR ProductionCostTypeId IS NULL
	DELETE FROM ActivityCosts WHERE ActivityCostId IN 	(SELECT MIN(ActivityCostId) ActivityCosts FROM ActivityCosts GROUP BY ActivityId, ProductionCostTypeId  HAVING COUNT(*) > 1)
	IF EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_AuffNr' AND object_id = OBJECT_ID(N'dbo.ActivityCosts')) DROP INDEX ActivityCosts.IX_AuffNr
	IF EXISTS (SELECT * FROM sys.indexes WHERE  name  = 'IX_KostenID' AND object_id = OBJECT_ID(N'dbo.ActivityCosts')) DROP INDEX ActivityCosts.IX_KostenID
	Print 'ActivityCosts'
	ALTER TABLE ActivityCosts DROP CONSTRAINT PK_ActivityCosts
	ALTER TABLE ActivityCosts ALTER COLUMN ActivityId INT NOT NULL
	ALTER TABLE ActivityCosts ALTER COLUMN ProductionCostTypeId INT NOT NULL
	CREATE UNIQUE CLUSTERED INDEX IX_ActivityCosts_ActivityId_ProductionCostTypeId ON dbo.ActivityCosts (ActivityId, ProductionCostTypeId)
	ALTER TABLE ActivityCosts ADD CONSTRAINT PK_ActivityCosts PRIMARY KEY (ActivityCostId) ON [PRIMARY]	
END
GO
--#endregion 2020-11-08
GO
--Falls Templates über Attachments verwaltet werden ist der Name leer
ALTER TABLE Templates ALTER COLUMN Filename VARCHAR(256) NULL
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE c.name = 'BudgetReferenceX' AND o.id = OBJECT_ID('Budgets'))	ALTER TABLE Budgets DROP COLUMN BudgetReferenceX
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE c.name = 'PrevisionX' AND o.id = OBJECT_ID('Budgets'))	ALTER TABLE Budgets DROP COLUMN PrevisionX
GO
--IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('OwnerGroups') AND name = 'BasicCastIsPerSeason')
--BEGIN
--	PRINT('Adding OwnerGroups.BasicCastIsPerSeason ...')
--	ALTER TABLE OwnerGroups ADD BasicCastIsPerSeason BIT NOT NULL CONSTRAINT DF_OwnerGroups_BasicCastIsPerSeason DEFAULT 0
--	EXEC('UPDATE OwnerGroups SET BasicCastIsPerSeason = (SELECT CASE WHEN DataValue = 0 THEN 0 ELSE 1 END FROM SETTINGS WHERE Section = ''BasicCast'' AND KeyName = ''Season'') WHERE  OwnerGroupId > -1')
--END
--ALTER TABLE ResourceObjectBorrows ALTER COLUMN StartDate SMALLDATETIME NOT NULL
--ALTER TABLE ResourceObjectBorrows ALTER COLUMN EndDate SMALLDATETIME NULL
--ALTER TABLE BodyMeasurements ALTER COLUMN MeasuredAt DATE NOT NULL
--SELECT  o.name, o.id, * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
--WHERE   c.xtype = 61 and o.xtype = 'U'
GO
--#endregion--------- 4.206.x ---------------------------------

--- 2020 ENDE, Fortsetzung in 2021 ---

--------------------- Ende Änderungsblock --------------------- 
---------------------------------------------------------------
--------------------- RELEASE SECTION -------------------------

--#region ----------- 4.206.x Release Übertrag ----------------

-- Nur Änderungen im Release hier eintragen!!! (Bugfixes)
--#region 2020-01-05
IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.fn_GetNeededBasicCastByProduction') AND xtype = 'IF') DROP Function dbo.fn_GetNeededBasicCastByProduction
GO
CREATE FUNCTION dbo.fn_GetNeededBasicCastByProduction (
		@productionId AS INT, 
		@date AS DATE,
		@activityTypeId AS INT,
		@understudyPosition AS INT, 
		@personIds IdFilterTable READONLY,
		@rolePermissions IdFilterTable READONLY)
RETURNS TABLE
AS
  RETURN
  (
  
  SELECT
    bc.RoleId, bc.PersonId, need.IsMandatory
  FROM Roles r
	 INNER JOIN (SELECT ChildProductionId FROM ProductionTimeFlow WHERE ProductionId = @productionId AND ChildProductionId IS NOT NULL UNION SELECT @productionId ) AS p
		 ON r.ProductionId = p.ChildProductionId
	INNER JOIN BasicCast bc ON r.RoleId = bc.RoleId
	INNER JOIN CastRules AS need ON r.RoleTypeId = need.RoleTypeId 
 WHERE 
	bc.UnderstudyPosition < @understudyPosition 
	AND (bc.StartDate <= @date OR bc.StartDate IS NULL) AND (bc.EndDate >= @date OR bc.EndDate IS NULL)
	AND bc.PersonId IN (SELECT Id FROM @personIds)
	AND need.ActivityTypeId = @activityTypeId
	AND r.OwnerGroupId IN (SELECT Id FROM @rolePermissions)
  )
GO

ALTER PROCEDURE [dbo].[GetPossibleCastByPeriodInternal]
	@start DATE,
	@end DATE,
	@personIds IdFilterTable READONLY,
	@activityTypeIds IdFilterTable READONLY,
	@variantIds IdFilterTable READONLY,
	@understudyPosition TINYINT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @permissionPeriodStart DATE
	DECLARE @permissionPeriodEnd DATE

	-- *** 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 
			SELECT @start = '30000101'
			SELECT @end = '30000101'
		END
	ELSE
		BEGIN
			IF (@start < @permissionPeriodStart)	BEGIN SELECT @start = @permissionPeriodStart END
			IF (@end > @permissionPeriodEnd)		BEGIN SELECT @end = @permissionPeriodEnd END
		END

	DECLARE @rolePermissions IdFilterTable 
	INSERT INTO @rolePermissions SELECT OwnerGroupId FROM Permissions WHERE UserId = @userId AND PermissionArea = 4 

	IF EXISTS(SELECT * FROM @activityTypeIds)
		BEGIN
		--Mit Activitytype Filter
		SELECT DISTINCT d.PersonId, d.ActivityId, c.CastId, d.RoleId, IsMandatory
			FROM (
					SELECT DISTINCT a.ActivityId, b.RoleId, b.IsMandatory, b.PersonId
					FROM Activities AS a 
						INNER JOIN Productions AS p ON a.ProductionId = p.ProductionId
						CROSS APPLY dbo.fn_GetNeededBasicCastByProduction(a.ProductionId, a.Date, a.ActivityTypeId, @understudyPosition, @personIds, @rolePermissions) AS b
					WHERE Date BETWEEN @start AND @end AND VariantId IN (SELECT id FROM @variantIds) AND a.Status >= 0
						AND a.ActivityTypeId IN (SELECT Id FROM @activityTypeIds)
						AND p.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE UserId = @userId AND PermissionArea = 2)
				) AS d
				LEFT JOIN ( SELECT * 
							FROM Cast
							WHERE IsCanceled = 0 AND IsCover = 0) AS c 
					ON d.ActivityId = c.ActivityId AND d.RoleId = c.RoleId
				WHERE c.CastId IS NULL OR c.PersonId <> d.PersonId
				ORDER BY d.PersonId, d.ActivityId
		END 
	ELSE
		BEGIN
		--Ohne Activitytype Filter
			SELECT DISTINCT d.PersonId, d.ActivityId, c.CastId, d.RoleId, IsMandatory
			FROM (
					SELECT DISTINCT a.ActivityId, b.RoleId, b.IsMandatory, b.PersonId
					FROM Activities AS a 
						INNER JOIN Productions AS p ON a.ProductionId = p.ProductionId
						CROSS APPLY dbo.fn_GetNeededBasicCastByProduction(a.ProductionId, a.Date, a.ActivityTypeId, @understudyPosition, @personIds, @rolePermissions) as b
					WHERE Date BETWEEN @start AND @end AND VariantId IN (SELECT id FROM @variantIds) AND a.Status >= 0
						AND p.OwnerGroupId IN (SELECT OwnerGroupId FROM Permissions WHERE UserId = @userId AND PermissionArea = 2)
				) AS d
				LEFT JOIN ( SELECT * 
							FROM Cast
							WHERE IsCanceled = 0 AND IsCover = 0) AS c 
					ON d.ActivityId = c.ActivityId AND d.RoleId = c.RoleId
				WHERE c.CastId IS NULL OR c.PersonId <> d.PersonId
				ORDER BY d.PersonId, d.ActivityId
		END
END
GO
IF NOT EXISTS (SELECT * FROM ProductionFieldTypes WHERE KnownField = 1)
	INSERT INTO ProductionFieldTypes (Name,Code,SectionName,SortInSection,DataType,IsVisibleIn,KnownField,uc,lc,um,lm) 
		VALUES ('OriginalTitle', 'originalTitle',NULL,1,0,2,1,-1,GetDate(),-1,GetDate())

IF NOT EXISTS (SELECT * FROM ProductionFieldTypes WHERE KnownField = 2)
	INSERT INTO ProductionFieldTypes (Name,Code,SectionName,SortInSection,DataType,IsVisibleIn,KnownField,uc,lc,um,lm) 
		VALUES ('Komponist', 'composer',NULL,1,0,2,2,-1,GetDate(),-1,GetDate())
	
IF NOT EXISTS (SELECT * FROM ProductionFieldTypes WHERE KnownField = 3)
	INSERT INTO ProductionFieldTypes ( Name,Code,SectionName,SortInSection,DataType,IsVisibleIn,KnownField,uc,lc,um,lm) 
		VALUES ('Not In Press', 'NoPress',NULL,1,4,2,3,-1,GetDate(),-1,GetDate())

IF NOT EXISTS (SELECT * FROM ProductionFieldTypes WHERE KnownField = 4)
	INSERT INTO ProductionFieldTypes ( Name,Code,SectionName,SortInSection,DataType,IsVisibleIn,KnownField,uc,lc,um,lm) 
		VALUES ('PrintStart', 'PrintStart',NULL,1,4,2,4,-1,GetDate(),-1,GetDate())

IF NOT EXISTS (SELECT * FROM ProductionFieldTypes WHERE KnownField = 5)	
	INSERT INTO ProductionFieldTypes ( Name,Code,SectionName,SortInSection,DataType,IsVisibleIn,KnownField,uc,lc,um,lm) 
		VALUES ('PrintEnd', 'PrintEnd',NULL,1,4,2,5,-1,GetDate(),-1,GetDate())
	
IF NOT EXISTS (SELECT * FROM ProductionFieldTypes WHERE KnownField = 6)		
	INSERT INTO ProductionFieldTypes ( Name,Code,SectionName,SortInSection,DataType,IsVisibleIn,KnownField,uc,lc,um,lm) 
		VALUES ('AlternativeEndText', 'AlternativeEndText',NULL,1,0,2,6,-1,GetDate(),-1,GetDate())
GO
--#endregion

--#region SH 2021-01-18 [MergeDoubletten] - Update PersonFieldData entfernt, da dies jetzt im Code gemacht wird

ALTER PROCEDURE [dbo].[MergeDoubletten]
	@PersonId int, 
	@DoubletteId int
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	Begin TRY
	BEGIN TRAN
	--UPDATE ABgruppe_Person SET PersNr=@PersonId WHERE  PersNr=@DoubletteId
	UPDATE Auditions			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId 
	UPDATE BankAccounts			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE BasicCast			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE Cast					SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	
	UPDATE CastRepertoire		SET PersonId = @PersonId WHERE  PersonId = @DoubletteId AND RoleId NOT IN (SELECT RoleId FROM CastRepertoire WHERE  PersonId = @PersonId)
	UPDATE BodyMeasurements		SET PersonId = @PersonId, MeasuredAt=DATEADD(minute,1,MeasuredAt)  WHERE  PersonId = @DoubletteId
	UPDATE CashCommands			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE CashCommands			SET AgencyId = @PersonId WHERE  AgencyId = @DoubletteId
	UPDATE CheckListItem		SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE CheckListItemTemplate SET PersonId= @PersonId WHERE  PersonId = @DoubletteId
	
	UPDATE D SET PersonId= @PersonId from departmentcollectives d 
		WHERE  PersonId = @DoubletteId and not exists ( select * from DepartmentCollectives d2 where d2.DepartmentId=d.DepartmentId and d2.PersonId=@PersonId);
	DELETE From DepartmentCollectives where PersonId=@DoubletteId;
	UPDATE Departments			SET HeadOfDepartmentId = @PersonId 
														 WHERE  HeadOfDepartmentId = @DoubletteId
	UPDATE MailingListEntries	SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE Orders				SET SupplierId=@PersonId WHERE  SupplierId = @DoubletteId
	UPDATE OrderTravelers		SET TravelerId=@PersonId WHERE  TravelerId = @DoubletteId
	--Verweise zwischen Dublette und Zielperson werden gelöscht
	DELETE FROM PersonRelations WHERE PersonId=@PersonId AND ChildPersonId=@DoubletteId
	DELETE FROM PersonRelations WHERE PersonId=@DoubletteId AND ChildPersonId=@PersonId
	
	UPDATE PersonRelations		SET PersonId = @PersonId WHERE  PersonId = @DoubletteId 
	UPDATE PersonRelations		SET ChildPersonId = @PersonId WHERE  ChildPersonId = @DoubletteId
	
	IF NOT EXISTS (SELECT * FROM PersonEmployee WHERE  PersonId=@PersonId)
	BEGIN
		UPDATE PersonEmployee SET PersonId=@PersonId WHERE  PersonId= @DoubletteId
	END
	ELSE
	BEGIN
		DELETE FROM PersonEmployee WHERE  PersonId = @DoubletteId
	END
	UPDATE PersonMemo			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE PersonAdditionalData	SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE Qualification_Person SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE ResourceObjectBorrows SET PersonId= @PersonId WHERE  PersonId = @DoubletteId
	UPDATE ResourceObjectInsurances SET InsuredPersonId = @PersonId 
														 WHERE  InsuredPersonId = @DoubletteId
	UPDATE ResourceObjects		SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE PersonStatus			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE Travels				SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE Contracts			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE WorkCreators			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE WorkVersionRoles_Repertoire  SET PersonId=@PersonId WHERE  PersonId = @DoubletteId
	UPDATE WorkVersions			SET PublisherId=@PersonId WHERE  PublisherId = @DoubletteId
	UPDATE Locations			SET PersonId = @PersonId WHERE  PersonId = @DoubletteId
	UPDATE BasicCastHistory		SET PersonNewId = @PersonId	WHERE PersonNewId = @DoubletteId

	DELETE FROM CastRepertoire WHERE  PersonId = @DoubletteId AND RoleId IN (SELECT RoleId FROM CastRepertoire WHERE  PersonId = @PersonId)
	DELETE FROM Qualification_Person WHERE  PersonId = @DoubletteId AND Qualification_Person.QualificationId IN (SELECT QualificationId FROM Qualification_Person WHERE  PersonId = @PersonId)
	DELETE FROM PersonPermissions	WHERE PersonId = @DoubletteId
	DELETE FROM PersonContactData	WHERE PersonId = @DoubletteId
	DELETE FROM PersonAddress		WHERE PersonId = @DoubletteId
	DELETE FROM PersonDetails		WHERE PersonId = @DoubletteId
	DELETE FROM PersonFeeNotes		WHERE PersonId = @DoubletteId
	DELETE FROM Persons				WHERE PersonId = @DoubletteId
	Commit Tran
	End Try
	Begin Catch
	if @@TRANCOUNT>0 Rollback tran
	    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
  
    SELECT   
        @ErrorMessage = ERROR_MESSAGE(),  
        @ErrorSeverity = ERROR_SEVERITY(),  
        @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );
	End Catch
END 
GO

--#endregion (SH 2021-01-18 [MergeDoubletten] - Update PersonFieldData entfernt, da dies jetzt im Code gemacht wird)
--#region 2021-02-10
GRANT SELECT, UPDATE, INSERT, DELETE ON AbProjectTaskDependencies_Update TO public
GRANT SELECT, UPDATE, INSERT, DELETE ON AbProjectTasks_Update TO public
GRANT SELECT, UPDATE, INSERT, DELETE ON AbProjectTaskMembers_Update TO public
--#endregion
--#endregion -------- (4.206.x Release Übertrag) --------------

---------------------------------------------------------------
--#region ----------- Views aktualisieren ---------------------
Print ('Updating Views...')
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 ------------------
Print ('Alter TruncateUpdateLog...')
GO
ALTER PROCEDURE dbo.TruncateUpdateLog
	@fromDate DATE
AS
BEGIN	
	BEGIN TRY
		BEGIN TRANSACTION
		DECLARE @treuncatedrows INT
		DECLARE @total INT
		SELECT @treuncatedrows = COUNT(*) FROM UpdateLog WHERE LC <= @fromDate
		SELECT @total = COUNT(*) FROM UpdateLog
		PRINT 'TruncateUpdateLog from: ' + CAST(@fromDate AS VARCHAR(10)) + ' Total: ' + CAST( @total AS VARCHAR(100)) + ' Delete: ' + CAST( @treuncatedrows AS VARCHAR(100))

		IF (@treuncatedrows < 10000)
			BEGIN
				PRINT  'Delete rows: '  + CAST( @treuncatedrows AS VARCHAR(100))
				DELETE FROM UpdateLog WHERE LC <= @fromDate
			END
		ELSE
			BEGIN
				CREATE TABLE #UpdateLogTEMP(
					UpdateLogId INT NOT NULL,
					WinUser VARCHAR(100) NOT NULL,
					LogText VARCHAR(MAX) NOT NULL,
					LogArt TINYINT NOT NULL,
					LC DATETIME2(3) NOT NULL,
					UC INT NULL,
					LogTable VARCHAR(100) NOT NULL,
					PKValue INT NULL,
					PKField VARCHAR(100) NULL,
					TransactionId INT NULL)
						
				PRINT  'In Temp. rows: '  + CAST((@total - @treuncatedrows) AS VARCHAR(100))
				INSERT INTO #UpdateLogTEMP WITH (TABLOCK) (UpdateLogId, WinUser, LogText, LogArt, LC, UC, LogTable, PKValue, PKField, TransactionId)
				SELECT									   UpdateLogId, WinUser, LogText, LogArt, LC, UC, LogTable, PKValue, PKField, TransactionId
				FROM UpdateLog WHERE LC > @fromDate

				PRINT  'Truncat UpdateLog' 
				TRUNCATE TABLE UpdateLog

				PRINT  'Back to Log. rows: '  + CAST((@total-@treuncatedrows) AS VARCHAR(100))
				SET IDENTITY_INSERT UpdateLog ON
					INSERT INTO UpdateLog WITH (TABLOCK) (UpdateLogId, WinUser, LogText, LogArt, LC, UC, LogTable, PKValue, PKField, TransactionId)
					SELECT								  UpdateLogId, WinUser, LogText, LogArt, LC, UC, LogTable, PKValue, PKField, TransactionId
					FROM dbo.#UpdateLogTEMP 
				SET IDENTITY_INSERT updateLog OFF

				PRINT  'Drop temp '
				DROP TABLE #UpdateLogTEMP
			END
			COMMIT TRANSACTION
		PRINT  'Removed rows: ' + Cast(@treuncatedrows AS VARCHAR(100))
		END TRY

	BEGIN CATCH 
		ROLLBACK TRANSACTION
		PRINT 'Error'
	END CATCH
END
GO
PRINT ('Execute TruncateUpdateLog...')
GO
DECLARE @from DATE
SELECT @from = GETDATE()-1095
EXECUTE TruncateUpdateLog @from


GO
--#endregion 
--#region ----------- zum Löschen in 2021 vormarkiert ---------
--PlanZR
--#endregion

--#region ----------- zum Löschen in 2022 vormarkiert ---------
	--IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRoster') AND name = 'X_TimeAccountId1')
	--BEGIN
	--	ALTER TABLE dbo.DutyRoster DROP COLUMN X_TimeAccountId1
	--END
	--GO
	--
	--IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRoster') AND name = 'X_TimeAccountId2')
	--BEGIN
	--	ALTER TABLE dbo.DutyRoster DROP COLUMN X_TimeAccountId2
	--END
	--GO
	--
	--IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('DutyRoster') AND name = 'X_TimeAccountId3')
	--BEGIN
	--	ALTER TABLE dbo.DutyRoster DROP COLUMN X_TimeAccountId3
	--END
	--GO
--#endregion -------- (zum Löschen in 2022 vormarkiert) -------

---------------------------------------------------------------
--#region ----------- Wegräumen der Hilfsprozeduren -----------
Print ('Clean up...')
GO
IF EXISTS (SELECT * FROM sys.objects o WHERE  o.object_id = OBJECT_ID(N'[dbo].DropIndexByTable'))			DROP PROCEDURE dbo.DropIndexByTable
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].DropDefaultConstraint'))		DROP PROCEDURE dbo.DropDefaultConstraint 
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
GO
--#endregion ----------- Wegräumen der Hilfsprozeduren -----------
---------------------------------------------------------------
--#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 = 206
SET @build = 101
SET @Datum = '20210118'
--#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.		')
GO

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