/*
	Dieses Script muss vor dem ersten Start von theasoft V4 ausgeführt werden
	Das Script aktualisiert die Datensstruktur der theasoft Datenbank.
	
    - Bitte achten Sie darauf das die richtige (theasoft) Datenbank ausgewählt ist. -
 
 */

--------------------- Versions und Datenbankprüfungen ---------------------

--#region Prüfung auf Theasoft Datenbank
IF NOT EXISTS 
	(SELECT * 
	 FROM dbo.sysobjects 
	 WHERE id = OBJECT_ID('dbo.NVData') AND OBJECTPROPERTY(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 SQL-Server Versionsprüfung
DECLARE @serverVersion INT
SELECT @serverVersion = CONVERT(INTEGER, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))
	IF @serverVersion < 9
	
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='
		
																																													Print ''
																																															Print '		Die SQL-Server-Version 2000 u. alter 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 156
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 156 OR (Minor=156 AND Build < 100)))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Der Versionsunterschied ist zu groß!!!'	
																																															Print '		Bitte führen Sie DbScript_2015.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 171
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major > 4 OR (Major = 4 AND (Minor >= 171))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Veraltete Skriptversion!!!'	
																																															Print '		Bitte führen Sie DbScript_2017.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
--------------------- Ende Versions und Datenbankprüfungen ---------------------

--------------------- HilfsProzeduren ---------------------

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

--#region DeleteConstraint
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DeleteConstraint'))
BEGIN
	DROP PROCEDURE dbo.DeleteConstraint 
END
go

CREATE PROCEDURE dbo.DeleteConstraint
	@tab varchar(100), 
	@field varchar(100)
AS
BEGIN
	DECLARE @defname varchar(100)
	SELECT @defname = name 	FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid 
			WHERE object_name(so.parent_obj) = @tab
			AND so.xtype = 'D'
			AND sc.colid = 	(SELECT colid FROM syscolumns WHERE id = OBJECT_ID(@tab) AND name = @field)
	
	IF @defname <> '' BEGIN
		EXEC ('ALTER TABLE '+@tab+' DROP CONSTRAINT '+@defname)
		Print 'Default Constraint '+@defname+' gelöscht.'
	END	
END
GO
--#endregion
--#region DropIndexByTable
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.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 @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 DropColumn
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.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(@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( @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)
	END
END
GO
--#endregion

--#region DropDefault
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DropDefault'))
BEGIN
	DROP PROCEDURE dbo.DropDefault 
END
GO
CREATE PROCEDURE dbo.DropDefault 
	@tablename varchar(100),
	@columnname varchar(100)
AS 

BEGIN
	declare @default sysname,	@exec    nvarchar(4000)

	select 	@default = o.name
	from syscolumns c inner join sysobjects o on c.cdefault = o.id
	where c.cdefault > 0 and c.id = OBJECT_ID(@tablename) and
	c.name = @columnname

	set @exec = 'alter table ' + @tablename + ' drop constraint ' + @default
	print @exec
	if @exec is not NULL exec sp_executesql @exec
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  sysobjects o WHERE  o.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
--------------------- Ende HilfsProzeduren ---------------------

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

--#region Übertragene Änderungen aus CreateDb_2015.sql für Release 156 

--Übertrag der RELEASESECTION aus 156
-- 20160121 - DH Bugfix Produktionen kopieren
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[CopyRole]'))
BEGIN
DROP PROCEDURE [dbo].[CopyRole]
END
GO
CREATE PROCEDURE [dbo].[CopyRole]
	@productionId int,
	@roleId  int
As
BEGIN
	DECLARE @userId INT

	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
INSERT INTO BesetzungStd_Update (
	[Rolle],[Pos],[Leporello],[PresseSort],[nameDrucken],[StdVs],[StdPro],[RollenLtgID],[GruppeID],[DruckSteuerung]
      ,[DruckStd],[Cover],[Besitzer],[ABGruppeID] ,[Anzahl],[Exklusiv],[RolleKurz],[StartOffset],[EndOffset]
      ,[CountDownId],[RollePresse],[StarRole],[IsInactive],[RoleCode],[Note],[RoleCategoryId],[WorkVersionRoleId]
	  ,[SNr] 
      ,[LM]
      ,[UM]
      ,[LC]
      ,[UC]) 
SELECT 
	[Rolle],[Pos],[Leporello],[PresseSort],[nameDrucken],[StdVs],[StdPro],[RollenLtgID],[GruppeID],[DruckSteuerung]
      ,[DruckStd],[Cover],[Besitzer],[ABGruppeID] ,[Anzahl],[Exklusiv],[RolleKurz],[StartOffset],[EndOffset]
      ,[CountDownId],[RollePresse],[StarRole],[IsInactive],[RoleCode],[Note],[RoleCategoryId],[WorkVersionRoleId]
	  ,@productionId, 
	  GETDATE(),
	  @userId,
	  GETDATE(),
	  @userId
FROM BesetzungStd AS RoleSource WHERE RoleSource.ID = @roleId
SELECT CAST(@@IDENTITY as INT)
END	

GO
GRANT EXECUTE ON dbo.CopyRole TO public

go
--20160129 -DH- Bugfix Castrepertoire
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbCastActiveties]'))	DROP VIEW [dbo].[AbCastActiveties]
GO
CREATE VIEW [dbo].[AbCastActiveties] 
AS
	SELECT	ac.Datum, ac.UhrBeg, ac.UhrEnde, ac.SNr, ac.ZrNr, ac.Art, ac.Bem, ac.[Status], ca.*,p.Alt,
	ro.id AS Role_RoleId, ro.RollenLtgId AS Role_TaskAreaId, ro.RoleCategoryId AS Role_RoleCategoryId, ro.Besitzer AS Role_OwnerId
	FROM	AufführgTabelle				AS ac
			INNER JOIN Spielzeittabelle AS s	ON ac.Datum <= s.SpzEnde AND ac.Datum >= s.SpzStart
			INNER JOIN UserForAccess	AS us	ON s.spznr = us.spznr
			INNER JOIN UserPW			AS ua	ON us.UserId = ua.UserID
			INNER JOIN Stücke			AS p	ON ac.snr = p.snr
			INNER JOIN UserGruppe		AS uga	ON p.ModuleGroupId = uga.GruppeID AND ua.UserID = uga.UserID AND uga.BerechtigungsbereichID = 2
			INNER JOIN Besetzungtabelle	AS ca	ON ac.Auffnr = ca.AuffNr
			INNER JOIN BesetzungStd		AS ro	ON ca.RolleID = ro.ID
			INNER JOIN UserGruppe		AS ugc	ON ro.Besitzer = ugc.GruppeID AND ugc.UserID = ua.UserID AND ugc.BerechtigungsbereichID = 4
	WHERE
			ua.UserName = CURRENT_USER
			AND  (ac.Gesperrt = 0 OR uga.OptionsFlag IS NULL OR uga.OptionsFlag & 1 = 1)
			AND ((ca.Gesperrt = 0 OR (Ugc.OptionsFlag & 1 = 1)))

GO
GRANT SELECT ON [AbCastActiveties] TO public
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetActivitiesInternal'))
	DROP PROCEDURE dbo.GetActivitiesInternal
GO
CREATE PROCEDURE dbo.GetActivitiesInternal
	@start DATETIME,
	@end DATETIME,
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @permissionPeriodStart DATETIME
	DECLARE @permissionPeriodEnd DATETIME


	SELECT @permissionPeriodStart = MIN(s.SpzStart), @permissionPeriodEnd = MAX(s.SpzEnde)
	FROM Spielzeittabelle AS s
		INNER JOIN UserForAccess AS us	ON s.spznr = us.spznr
	WHERE us.UserId = @userId AND s.SpzNr <> 0

	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 (@include <> 0)  CREATE TABLE #Activities (ActivityId INT NOT NULL)

	CREATE TABLE #Permissions (UserGruppeId INT NOT NULL, BerechtigungsbereichId INT NOT NULL, OptionsFlag INT NOT NULL)
	INSERT INTO #Permissions SELECT UserGruppe.GruppeId, UserGruppe.BerechtigungsbereichID, OptionsFlag  FROM UserGruppe WHERE UserId = @userId AND BerechtigungsbereichID IN (2,4)


	DECLARE @fields VARCHAR(MAX)
	SET @fields  = 'Aufführg.AuffNr, Aufführg.SNr, Aufführg.Datum, Aufführg.UhrBeg, Aufführg.UhrEnde, Aufführg.ZRNr, Aufführg.Pl, Aufführg.Art, Aufführg.Bem, Aufführg.BemKurz, Aufführg.BemKurz2, Aufführg.DiplaDru, Aufführg.VonZeit, 
					Aufführg.Status, Aufführg.PreisNr, Aufführg.ZuAuffNr, Aufführg.LM ,Aufführg.UM, Aufführg.Gesperrt, Aufführg.LC, Aufführg.UC, Aufführg.CreateCountDownId, 
					Aufführg.IsTimeSpan, Aufführg.IsExtraPayLiable '

	--Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben
	if (@include <> 0) 
		SET @SQLQuery = 'INSERT INTO #Activities SELECT  Aufführg.AuffNr'
	ELSE
		SET @SQLQuery = 'SELECT ' + @fields
					
	 SET @SQLQuery =  @SQLQuery + '	 FROM  AufführgTabelle AS Aufführg
		INNER JOIN  Stücke AS p ON Aufführg.snr = p.snr
		INNER JOIN #Permissions AS UG ON UG.UserGruppeId  = P.ModuleGroupId AND ug.BerechtigungsbereichID = 2' 

		IF (@joins&1 = 1) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN AufführgDetails ON Aufführg.Auffnr = AufführgDetails.AuffNr '

		IF (@joins&2 = 2) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN PresseText AS PT ON PT.AuffNr = Aufführg.AuffNr '
			--Wenn Abfrage direkt ausgeführt wird Felder für oder Joinen hinzufügen. Ggf auch bei Abfrge unten prüfen
		IF (@joins&4 = 4 OR (@include = 0 AND CHARINDEX('zr.',@order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN Zr ON Aufführg.ZrNr = Zr.ZrNr '

		IF (@joins&8 = 8 OR (@include = 0 AND CHARINDEX('AuffTypen.',@order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN AuffTypen ON Aufführg.Art = AuffTypen.AuffTNr '

		IF (@joins&16 = 16) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN StückeDetails ON Aufführg.Snr = StückeDetails.Snr '

		IF (@joins&32 = 32) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN Gruppe ON p.ModuleGroupId = Gruppe.GruppeID '
			
		SET @SQLQuery = @SQLQuery + ' WHERE Aufführg.Datum BETWEEN ''' + CAST(@start AS VARCHAR(MAX)) + '''  AND ''' + CAST(@end AS VARCHAR(MAX)) + '''AND (Aufführg.Gesperrt = 0 OR ug.OptionsFlag&1 = 1)'
		
		 IF (@condition IS NOT NULL)
			 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 ' + @fields +' FROM Aufführgtabelle AS Aufführg 
														INNER JOIN  #Activities ON Aufführg.auffnr = #Activities.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('zr.',@order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN Zr ON Aufführg.ZrNr = Zr.ZrNr '
				END
				IF (CHARINDEX('Titel',@order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN  Stücke AS p ON Aufführg.snr = p.snr '
				END
				IF (CHARINDEX('AuffTypen',@order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN AuffTypen ON Aufführg.Art = AuffTypen.AuffTNr '
				END
				SET @SQLQuery = @SQLQuery + @order 

			END

			EXECUTE sp_Executesql  @SQLQuery

		END

	IF (@include&1 = 1) 
		SELECT NULL AS [#1], AufführgDetails.* 
		FROM AufführgDetails 
			INNER JOIN #Activities On AufführgDetails.AuffNr = #Activities.ActivityId;

	IF (@include&2 = 2) 
		SELECT NULL AS [#2], PresseText.* 
		FROM PresseText 
			INNER JOIN #Activities ON PresseText.AuffNr = #Activities.ActivityId;

	IF (@include&4 = 4) 
		SELECT NULL AS [#4], Aufr.*, Ring.ZuAbo 
		FROM Aufr 
			INNER JOIN Ring ON AufR.AboNr = Ring.AboNr
			INNER JOIN #Activities ON Aufr.AuffNr = #Activities.ActivityId;

	IF (@include&8 = 8) 
		SELECT NULL AS [#8], BesetzungTabelle.* 
		FROM BesetzungTabelle 
			INNER JOIN #Activities ON BesetzungTabelle.AuffNr = #Activities.ActivityId
			INNER JOIN BesetzungStd ON BesetzungStd.Id = BesetzungTabelle.RolleID
			INNER JOIN #Permissions AS UG ON UG.UserGruppeId  = BesetzungStd.Besitzer AND  ug.BerechtigungsbereichID = 4 AND (BesetzungTabelle.Gesperrt = 0 OR ug.OptionsFlag&1 = 1)
			
	IF (@include&16 = 16) 
	BEGIN
		 DECLARE @changeSince  DATETIME
		 SET @changeSince = GETDATE() - 21 
		SELECT NULL AS [#16],ax.*
		FROM  XHDelAufführg AS ax
			INNER JOIN #Activities ON ax.AuffNr = #Activities.ActivityId
			LEFT JOIN (SELECT xhid FROM KenntnisDel WHERE tabl = 'Aufführg' 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], AuffDienstplanArten.* 
		FROM AuffDienstplanArten 
			INNER JOIN #Activities ON AuffDienstplanArten.AuffNr = #Activities.ActivityId;
						
	IF (@include&256 = 256) 
		SELECT NULL AS [#256], ActivityScenes.* 
		FROM ActivityScenes 
			INNER JOIN #Activities ON ActivityScenes.ActivityId = #Activities.ActivityId;
			
	IF (@include&512 = 512) 
		SELECT NULL AS [#512], AuffTechAufgabe.* 
		FROM AuffTechAufgabe 
			INNER JOIN #Activities ON AuffTechAufgabe.AuffNr = #Activities.ActivityId;

END

GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetActivities'))
	DROP PROCEDURE dbo.GetActivities
GO
CREATE PROCEDURE dbo.GetActivities
	@start DATETIME,
	@end DATETIME,
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @userId INT
	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
	EXECUTE GetActivitiesInternal @start, @end, @condition, @order, @joins, @include, @userId

END
GO
GRANT EXECUTE ON GetActivities TO public
GO
------------------- Besitzer der DB auf 'sa' stellen wenn 'sa' vorhanden --------------
IF NOT EXISTS (SELECT suser_sname(sid) 
				FROM  sys.database_principals 
				WHERE name = 'dbo' AND suser_sname(sid) = 'sa')
					AND NOT suser_sname(0x01) IS NULL
BEGIN
	EXECUTE sp_changedbowner @loginame = 'sa'
	Print 'sp_changedbowner'
END
GO

--Da die V3 Dispo noch BesSort mit 0 schreibt diese korrigieren
UPDATE BesetzungStdSoli SET BesSort = 1 WHERE BesSort = 0
UPDATE BesetzungTabelle SET BesSort = 1 WHERE BesSort = 0
GO

-- AbContractDates_Update
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbContractDates_Update'))
	DROP VIEW dbo.AbContractDates_Update

go
CREATE VIEW dbo.AbContractDates_Update
AS 
	SELECT
		cd.*
	FROM  
		ContractDates cd
	WHERE 
		cd.ContractRoleId IN (
		SELECT vr.VRID FROM VertragRolle AS vr
			INNER JOIN Vertrag v ON vr.VertragsNr = v.VertragsNr
			INNER JOIN	(SELECT DISTINCT GruppeID, Schreiben 
							FROM			UserGruppe 
								INNER JOIN		UserPW		ON UserGruppe.Userid = UserPW.Userid 
							WHERE	Username = CURRENT_USER AND 
									BerechtigungsbereichID = 1 AND 
									((Schreiben = 1 AND (OptionsFlag&1) = 1) OR (OptionsFlag&4) = 4)) AS UG
					ON v.ModulGruppeID = UG.GruppeID)
					--Schreibrecht + Honorara lesen oder Contracttracking schreiben
GO
GRANT SELECT, DELETE, INSERT, UPDATE ON AbContractDates_Update TO public
GO

--2016-02-19 sh
--neue spalte für az3 einfügen, da az zu sonstige arbeitszeit wird
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Az3' and o.id = OBJECT_ID(N'[dbo].[Dienstplan]'))
BEGIN
	ALTER TABLE dbo.Dienstplan ADD Az3 INT NOT NULL CONSTRAINT DF_Dienstplan_WorkingMinutes3 DEFAULT 0
END
GO
--einmalig daten umkopieren von az nach az3 sofern v3 und b3 gesetzt sind
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 156 OR (Minor = 156 AND Build < 102)))))
BEGIN
	UPDATE Dienstplan SET Az3 = Az, Az = 0 WHERE NOT (V3 IS NULL OR B3 IS NULL) AND Az3 = 0 AND Az <> 0
END
GO
--#region standardwert von bessort auf 1 ändern
--default constraint mit wert 0 löschen
 IF EXISTS (
	SELECT * FROM dbo.sysobjects so
	LEFT JOIN dbo.syscomments sm ON sm.id = so.id
	WHERE so.id = OBJECT_ID(N'[dbo].[DF_BesetzungStdSoliTabelle_Bessort]') AND so.type = 'D' AND (sm.text = '((0))' OR sm.text = '(0)')
 )
 BEGIN
	ALTER TABLE [dbo].[BesetzungStdSoliTabelle] DROP CONSTRAINT [DF_BesetzungStdSoliTabelle_Bessort]
 END
 GO
 --default constraint mit wert 1 anlegen
 IF NOT EXISTS (SELECT * FROM dbo.sysobjects so WHERE so.id = OBJECT_ID(N'[dbo].[DF_BesetzungStdSoliTabelle_Bessort]'))
 BEGIN
	ALTER TABLE dbo.BesetzungStdSoliTabelle ADD CONSTRAINT DF_BesetzungStdSoliTabelle_Bessort DEFAULT 1 FOR BesSort
 END
 GO

--#endregion


--2016-02-24 sh
--#region bugfix: pressesort fehlt in abcast und AbCastActiveties
ALTER VIEW [dbo].[AbCast]
AS
	SELECT	c.*, r.id AS Role_RoleId, r.RollenLtgId AS Role_TaskAreaId, r.RoleCategoryId AS Role_RoleCategoryId,
	        r.Besitzer AS Role_OwnerId, r.PresseSort AS Role_PressSort
	FROM    BesetzungTabelle		AS c 
		INNER JOIN BesetzungStd		AS r ON c.RolleId = r.ID
		INNER JOIN UserGruppe AS Ug ON r.Besitzer = Ug.GruppeID   
		INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
	WHERE (BerechtigungsbereichID = 4 AND  (c.Gesperrt = 0 OR (Ug.OptionsFlag&1 = 1)) AND UserPW.Username = CURRENT_USER) 

GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbCastActiveties]'))	DROP VIEW [dbo].[AbCastActiveties]
GO
CREATE VIEW [dbo].[AbCastActiveties] 
AS
	SELECT	ac.Datum, ac.UhrBeg, ac.UhrEnde, ac.SNr, ac.ZrNr, ac.Art, ac.Bem, ac.[Status], ca.*,
	        ro.id AS Role_RoleId, ro.RollenLtgId AS Role_TaskAreaId,
	        ro.RoleCategoryId AS Role_RoleCategoryId, ro.Besitzer AS Role_OwnerId, ro.PresseSort AS Role_PressSort
	FROM	AufführgTabelle				AS ac
			INNER JOIN Spielzeittabelle AS s	ON ac.Datum <= s.SpzEnde AND ac.Datum >= s.SpzStart
			INNER JOIN UserForAccess	AS us	ON s.spznr = us.spznr
			INNER JOIN UserPW			AS ua	ON us.UserId = ua.UserID
			INNER JOIN Stücke			AS p	ON ac.snr = p.snr
			INNER JOIN UserGruppe		AS uga	ON p.ModuleGroupId = uga.GruppeID AND ua.UserID = uga.UserID AND uga.BerechtigungsbereichID = 2
			INNER JOIN Besetzungtabelle	AS ca	ON ac.Auffnr = ca.AuffNr
			INNER JOIN BesetzungStd		AS ro	ON ca.RolleID = ro.ID
			INNER JOIN UserGruppe		AS ugc	ON ro.Besitzer = ugc.GruppeID AND ugc.UserID = ua.UserID AND ugc.BerechtigungsbereichID = 4
	WHERE
			ua.UserName = CURRENT_USER
			AND  (ac.Gesperrt = 0 OR uga.OptionsFlag IS NULL OR uga.OptionsFlag & 1 = 1)
			AND ((ca.Gesperrt = 0 OR (Ugc.OptionsFlag & 1 = 1)))

GO
GRANT SELECT ON [AbCastActiveties] TO public
GO
--#endregion

--2016-02-25 sh
--bugfix von bm vom 24.02.2016 wiede rückgängig machen, da es doch kein bug war sondern nur schrott daten
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 156 OR (Minor = 156 AND Build < 105)))))
BEGIN
	--daten sind markiert mit UC und UM mit Wert -100
	--offset wird um 1 verringert 
	UPDATE ContractPersoWorkingCycles SET Offset = Offset -1, LM = GETDATE(), UM = -200 WHERE UC = -100 AND UM = -100
END
GO

--tk 2016-03-07 FK_DutyRosterPeriods_Departments mit DELETE CASCADE erstellen
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.FK_DutyRosterPeriods_Departments') AND type = 'F')
	ALTER TABLE dbo.DutyRosterPeriods DROP CONSTRAINT FK_DutyRosterPeriods_Departments
GO

IF  NOT EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'FK_DutyRosterPeriods_Departments_withDelete'))
BEGIN
	ALTER TABLE dbo.DutyRosterPeriods  WITH CHECK ADD  CONSTRAINT FK_DutyRosterPeriods_Departments_withDelete FOREIGN KEY(DepartmentId)
	REFERENCES dbo.DienstplanArtenTabelle (DienstplanArtenId) ON DELETE CASCADE
	ALTER TABLE dbo.DutyRosterPeriods CHECK CONSTRAINT FK_DutyRosterPeriods_Departments_withDelete
END
GO

--#endregion (RELEASE Übertrag)


--20160115 bm	
--#region Attachments für TravelSteps
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.TravelStepAttachments') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.TravelStepAttachments (
			TravelStepAttachmentsId INT IDENTITY(1,1) NOT NULL,--ID
			TravelStepId INT NOT NULL,
			AttachmentId INT NOT NULL, 
			Note VARCHAR (255) NULL,
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL
			CONSTRAINT PK_TravelStepAttachments PRIMARY KEY CLUSTERED (TravelStepAttachmentsId ASC) ON [PRIMARY]
			CONSTRAINT FK_TravelStepAttachments_Person FOREIGN KEY (TravelStepId) REFERENCES dbo.TravelSteps (TravelStepId),
			CONSTRAINT FK_TravelStepAttachments_Attachment FOREIGN KEY (AttachmentId) REFERENCES dbo.[Attachments] (AttachmentId),
	) ON [PRIMARY]
END
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbTravelStepAttachments'))
	DROP VIEW [dbo].[AbTravelStepAttachments]
GO

CREATE VIEW [dbo].[AbTravelStepAttachments]
AS 
	SELECT * FROM TravelStepAttachments WHERE TravelStepId IN (SELECT TravelStepId FROM AbTravelSteps)
GO

GRANT SELECT ON dbo.AbTravelStepAttachments TO PUBLIC
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbTravelStepAttachments_Update'))
	DROP VIEW [dbo].[AbTravelStepAttachments_Update]
GO

CREATE VIEW [dbo].[AbTravelStepAttachments_Update]
AS 
	SELECT * FROM TravelStepAttachments WHERE TravelStepId IN (SELECT TravelStepId FROM AbTravelSteps_Update)
	WITH CHECK OPTION
GO

GRANT SELECT, INSERT, DELETE, UPDATE ON dbo.AbTravelStepAttachments_Update TO PUBLIC
GO
--#endregion

--#region Admin Rollen erstellen und alte Rollen entfernen

if NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = 'MasterAdmin')
BEGIN
	ALTER ROLE TheaOrchester WITH NAME = OrchesterAdmin;
	ALTER ROLE [Stammdaten ändern] WITH NAME = DispoAdmin;
	EXEC sp_addrole @rolename = N'MasterAdmin'
	EXEC sp_addrole @rolename = N'BallettAdmin'
	EXEC sp_addrole @rolename = N'LibraryAdmin'
	EXEC sp_addrole @rolename = N'OrderAdmin'
	EXEC sp_addrole @rolename = N'KontaktAdmin'
	

		--Alle mit WorksBerechtigungen in Library	
      DECLARE @RoleMemberName sysname
      DECLARE Member_Cursor CURSOR FOR
    SELECT u.name FROM  dbo.sysusers u WHERE  u.uid IN (
			SELECT memberuid FROM dbo.sysmembers WHERE groupuid IN (
				SELECT uid FROM dbo.sysusers WHERE name = 'WorksWrite' AND issqlrole = 1))
 
      OPEN Member_Cursor;
 
      FETCH NEXT FROM Member_Cursor
      INTO @RoleMemberName
 
      WHILE @@FETCH_STATUS = 0
      BEGIN
			EXEC sp_addrolemember 'LibraryAdmin', @RoleMemberName
            FETCH NEXT FROM Member_Cursor
            INTO @RoleMemberName
      END;
      CLOSE Member_Cursor;
      DEALLOCATE Member_Cursor;
	  -----------------------------------------

	  -- prüfen ob nur eine Lizenz, dann reicht eine Gruppe für Master Admin
	 DECLARE  @OnlyOneModule BIT
	IF NOT EXISTS  (SELECT  * FROM settings WHERE Form = 'Application' AND [Control] = 'License'  AND (CASE WHEN CHARINDEX('theaBallett',Werttext)>1 THEN 1 ELSE 0 END) + (CASE WHEN CHARINDEX('theaDispo',Werttext)>1 THEN 1 ELSE 0 END) + (CASE WHEN CHARINDEX('theaLibrary',Werttext)>1 THEN 1 ELSE 0 END) + (CASE WHEN CHARINDEX('theaOrchester',Werttext)>1 THEN 1 ELSE 0 END) +   (CASE WHEN CHARINDEX('theaChor',Werttext)>1 THEN 1 ELSE 0 END) + (CASE WHEN CHARINDEX('theaPerso',Werttext)>1 THEN 1 ELSE 0 END)  > 1)
	BEGIN 
		SET @OnlyOneModule = 1
	END 

		--Wer in mehreren Modulen Admin ist wird neuer MasterAdmin	
      DECLARE Member_Cursor CURSOR FOR
   			SELECT u.name FROM dbo.sysmembers as s INNER JOIN dbo.sysusers u ON  u.uid = s.memberuid  WHERE groupuid IN (
				SELECT uid FROM dbo.sysusers WHERE (name IN  ('PersoAdmin','ChorAdmin', 'OrchesterAdmin', 'DispoAdmin' ) OR Name =  'DispoAdmin' AND @OnlyOneModule = 1) AND issqlrole = 1) GROUP by   u.name  HAVING COUNT(*) > 1
 
      OPEN Member_Cursor;
 
      FETCH NEXT FROM Member_Cursor
      INTO @RoleMemberName
 
      WHILE @@FETCH_STATUS = 0
      BEGIN
			EXEC sp_addrolemember 'MasterAdmin', @RoleMemberName
            FETCH NEXT FROM Member_Cursor
            INTO @RoleMemberName
      END;
      CLOSE Member_Cursor;
      DEALLOCATE Member_Cursor;
	  -----------------------------------------
	   	
	GRANT INSERT, UPDATE, DELETE ON ABGruppe TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON ABGruppe_AB TO ChorAdmin

	GRANT INSERT, UPDATE, DELETE ON AllocationGroup TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON Anrede TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON AnredeInternational TO MasterAdmin
	REVOKE INSERT, UPDATE, DELETE ON BankAccount TO OrchesterAdmin
	REVOKE INSERT, UPDATE, DELETE ON BesRepertoire TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON BonusDefaultAmounts TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON BonusDefaultAmounts TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON CheckListItemTemplate TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON CheckListTemplate TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON CashFundPositionValidationStatus TO OrderAdmin
	
	GRANT INSERT, UPDATE, DELETE ON ContractTrackingWorkflow TO MasterAdmin
	
	GRANT INSERT, UPDATE, DELETE ON ContractType_Department TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractType_Department TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractType_Department TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_ContractFieldTypes TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_ContractFieldTypes TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_ContractFieldTypes TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_ContractFieldTypes TO OrchesterAdmin

	GRANT INSERT, UPDATE, DELETE ON ContractTypes_Templates TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_Templates TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON ContractTypes_Templates TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON CostCenter TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON CostCenter_OwnerGroup TO MasterAdmin
	REVOKE INSERT, UPDATE, DELETE ON CostCenter TO  DispoAdmin
	REVOKE INSERT, UPDATE, DELETE ON CostCenter_OwnerGroup TO  DispoAdmin

	GRANT INSERT, UPDATE, DELETE ON CostTypes TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON CostUnit TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON CostUnitProduction TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON Currency TO MasterAdmin

	GRANT INSERT, UPDATE, DELETE ON DepartmentCollectives TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON DepartmentCollectives TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON DepartmentCollectives TO OrchesterAdmin

	REVOKE INSERT, UPDATE, DELETE ON Dienstplan TO PersoAdmin
	REVOKE INSERT, UPDATE, DELETE ON DutyRosterPeriods TO PersoAdmin

	GRANT INSERT, UPDATE, DELETE ON FormulaConfigurations TO BallettAdmin
	GRANT INSERT, UPDATE, DELETE ON FormulaConfigurations_Departments TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON Kosten TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON Land TO MasterAdmin


	GRANT INSERT, UPDATE, DELETE ON OrderApprovalStatus TO OrderAdmin
	GRANT INSERT, UPDATE, DELETE ON OrderValidationApprovalStatus TO OrderAdmin
	GRANT INSERT, UPDATE, DELETE ON OrderValidationTypes TO OrderAdmin


	GRANT INSERT, UPDATE, DELETE ON PersContractValidationStatus TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON PersContractValidationType TO MasterAdmin


	GRANT INSERT, UPDATE, DELETE ON PersDataCategories TO KontaktAdmin
	GRANT INSERT, UPDATE, DELETE ON PersKomTyp TO MasterAdmin

	GRANT INSERT, UPDATE, DELETE ON PersonalNotizenTyp TO MasterAdmin

	GRANT INSERT, UPDATE, DELETE ON PersoParameter TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON PersoParameter TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON PersoParameter TO OrchesterAdmin
	REVOKE INSERT, UPDATE, DELETE ON PersH TO  OrchesterAdmin
	REVOKE INSERT, UPDATE, DELETE ON PersonalDetails TO  OrchesterAdmin

	GRANT INSERT, UPDATE, DELETE ON Qualification TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON Qualification TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON Qualification TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON Qualification TO BallettAdmin
	GRANT INSERT, UPDATE, DELETE ON Qualification TO KontaktAdmin

	GRANT INSERT, UPDATE, DELETE ON QualificationCategory TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON QualificationCategory TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON QualificationCategory TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON QualificationCategory TO BallettAdmin
	GRANT INSERT, UPDATE, DELETE ON QualificationCategory TO KontaktAdmin

	GRANT INSERT, UPDATE, DELETE ON Rapport TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON Rapport TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON Rapport TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON Rapport TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON RapportSpalte TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON RapportSpalte TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON RapportSpalte TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON RapportSpalte TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON ResourceTypes TO  LibraryAdmin
	GRANT INSERT, UPDATE, DELETE ON ResourceObjectConditions TO  LibraryAdmin
	GRANT INSERT, UPDATE, DELETE ON ResourceObjectBorrows TO  LibraryAdmin


	REVOKE INSERT, UPDATE, DELETE ON ResourceContainer TO  OrchesterAdmin
	REVOKE INSERT, UPDATE, DELETE ON ResourceTypes TO  WorksWrite
	REVOKE INSERT, UPDATE, DELETE ON ResourceObjectBorrows TO  WorksWrite
	REVOKE INSERT, UPDATE, DELETE ON ResourceObjectConditions TO  WorksWrite
	REVOKE INSERT, UPDATE, DELETE ON ResourceObjectConditions TO  orchesterAdmin
	REVOKE INSERT, UPDATE, DELETE ON ResourceObjects TO  OrchesterAdmin	
	REVOKE INSERT, UPDATE, DELETE ON ResourceObjects TO  WorksWrite

	REVOKE INSERT, UPDATE, DELETE ON ScoreTypes TO  WorksWrite
	REVOKE INSERT, UPDATE, DELETE ON ScoreTypes_ResourceTypes TO  WorksWrite

	GRANT INSERT, UPDATE, DELETE ON ScoreTypes TO  LibraryAdmin
	GRANT INSERT, UPDATE, DELETE ON ScoreTypes_ResourceTypes TO  LibraryAdmin

	GRANT INSERT, UPDATE, DELETE ON RollenLtg TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON RollenLtg TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON RollenLtg TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON SalaryLevel TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevel TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevel TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevel TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevel TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON SalaryLevelData TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevelData TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevelData TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevelData TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON SalaryLevelData TO BallettAdmin

	REVOKE INSERT, UPDATE, DELETE ON StatusZeitR TO  OrchesterAdmin

	GRANT INSERT, UPDATE, DELETE ON Tarif TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON Tarif TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON Tarif TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON Tarif TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON Taxes TO MasterAdmin

	GRANT INSERT, UPDATE, DELETE ON Typ TO LibraryAdmin

	REVOKE INSERT, UPDATE, DELETE ON TimeAccountUsage TO  PersoAdmin

	GRANT INSERT, UPDATE, DELETE ON TourStatus TO MasterAdmin
	GRANT INSERT, UPDATE, DELETE ON TourValidationStatus TO MasterAdmin

	REVOKE INSERT, UPDATE, DELETE ON VertragPerso TO  ChorAdmin
	REVOKE INSERT, UPDATE, DELETE ON VertragPerso TO  PersoAdmin

	GRANT INSERT, UPDATE, DELETE ON Verteiler TO  KontaktAdmin
	GRANT INSERT, UPDATE, DELETE ON Verteiler TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON VerteilerFeld TO  KontaktAdmin
	GRANT INSERT, UPDATE, DELETE ON VerteilerFeld TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON VerteilerFeld TO PersoAdmin

	GRANT INSERT, UPDATE, DELETE ON VertragsStatus TO MasterAdmin

	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON VertragsVorlagen TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON Vorlagen TO PersoAdmin
	GRANT INSERT, UPDATE, DELETE ON Vorlagen TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON Vorlagen TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON Vorlagen TO BallettAdmin
	GRANT INSERT, UPDATE, DELETE ON Vorlagen TO KontaktAdmin
	GRANT INSERT, UPDATE, DELETE ON Vorlagen TO LibraryAdmin
	GRANT INSERT, UPDATE, DELETE ON Vorlagen TO OrderAdmin

	GRANT INSERT, UPDATE, DELETE ON WageType TO MasterAdmin
	REVOKE INSERT, UPDATE, DELETE ON WageType TO  DispoAdmin

	GRANT INSERT, UPDATE, DELETE ON Zeitkonten TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON Zeitkonten TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON Zeitkonten TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON Zeitkonten TO BallettAdmin
	
	REVOKE INSERT, UPDATE, DELETE ON ZeitkontenMonat TO  PersoAdmin

	GRANT INSERT, UPDATE, DELETE ON Zulagen TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON Zulagen TO ChorAdmin
	GRANT INSERT, UPDATE, DELETE ON Zulagen TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON ZulagenDienstplanArt TO DispoAdmin
	GRANT INSERT, UPDATE, DELETE ON ZulagenDienstplanArt TO OrchesterAdmin
	GRANT INSERT, UPDATE, DELETE ON ZulagenDienstplanArt TO BallettAdmin

	GRANT INSERT, UPDATE, DELETE ON ZR TO LibraryAdmin

	GRANT INSERT, UPDATE, DELETE ON  Serienbrief TO  KontaktAdmin
	
	GRANT INSERT, UPDATE, DELETE ON Feiertage TO SpielzeitenVerwalten
	GRANT INSERT, UPDATE, DELETE ON Kalender TO SpielzeitenVerwalten

END
GO
DropRoleWithDropRoleMember 'ContractTrackingRead'
GO
DropRoleWithDropRoleMember 'ContractTrackingWrite'
GO
DropRoleWithDropRoleMember 'Kostüm-Daten lesen'
GO
DropRoleWithDropRoleMember 'KostümDatenSchreiben'
GO
DropRoleWithDropRoleMember 'PersoBasicDataWrite'
GO
DropRoleWithDropRoleMember 'PersoContractWrite'
GO
DropRoleWithDropRoleMember 'TheaOrchester lesen'
GO

--#endregion

--#region AbRole_Season_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbRole_Season_Update')) DROP VIEW dbo.AbRole_Season_Update
GO

CREATE VIEW dbo.AbRole_Season_Update
	AS
	SELECT      S.*
	FROM        Role_Season AS S 
	WHERE S.RoleId IN(
	SELECT Id FROM  BesetzungStd AS R
					INNER JOIN UserGruppe AS Ug ON r.Besitzer = Ug.GruppeID   
					INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
					WHERE ug.BerechtigungsbereichID = 4 AND ug.Schreiben = 1  AND UserPW.Username = CURRENT_USER)
	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON Role_Season TO PUBLIC
GO
--#endregion

--#region IsActivityWritable
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.IsActivityWritable')) DROP FUNCTION dbo.IsActivityWritable
GO
CREATE FUNCTION [dbo].IsActivityWritable(@Date DATETIME, @ProductionId INT, @ActivityTypeId INT, @ActivityStatus INT, @ActivityCreatorId INT)
 RETURNS BIT
 AS
 BEGIN
 	DECLARE @Permission BIT	
	SET @Permission  = 1
	DECLARE @userId INT
	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER

	--Berechtigung für Spielzeit prüfen
	IF @Permission = 1 AND NOT EXISTS (SELECT * FROM Spielzeittabelle AS s
		INNER JOIN UserForAccess AS us	ON s.spznr = us.spznr
				WHERE us.UserId = @userId AND s.SpzNr <> 0 AND s.SpzStart <= @Date AND s.SpzEnde >= @Date) 
	BEGIN
		SET @Permission = 0
	END
	
	--Bei Berechtigung auf Anfragen brauchen keine weiteren Prüfungen stattfinden
	IF (@ActivityCreatorId <> @userId OR NOT @ActivityStatus IN (2,-1) OR  IS_MEMBER('EditActivityRequests') = 0) 
	BEGIN
		--Berechtigung auf Produktion	
		IF @Permission = 1 AND NOT EXISTS (SELECT * FROM Stücke AS s
				INNER JOIN UserGruppe AS ug	ON s.ModuleGroupId = ug.GruppeID
						WHERE ug.BerechtigungsbereichID = 2 AND ug.Schreiben = 1)
			BEGIN
				SET @Permission = 0
			END

		--Berechtigung auf Aktivitätstyp, sind zu einem Typ keine Besitzergruppen wird dies wie "alle" gewertet	
		IF @Permission = 1 AND 
			 EXISTS (SELECT * FROM ActivityTypeGroups AS atg WHERE ActivityTypeId = @ActivityTypeId) AND
			 NOT EXISTS (SELECT * FROM ActivityTypeGroups AS atg INNER JOIN UserGruppe AS ug ON atg.GroupId = ug.GruppeID WHERE ug.BerechtigungsbereichID = 2 AND ug.Schreiben = 1 AND ActivityTypeId = @ActivityTypeId) 
			BEGIN
				SET @Permission = 0
			END
	END

	RETURN  @Permission

	END

GO
--#endregion

--#region AbActivities_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbActivities_Update')) DROP VIEW dbo.AbActivities_Update
GO
CREATE VIEW dbo.AbActivities_Update
	AS
	SELECT      a.*
	FROM        AufführgTabelle AS a
	WHERE dbo.IsActivityWritable(a.Datum, a.SNr, a.Art, a.Status, a.UC) = 1
	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbActivities_Update TO PUBLIC
--#endregion

--#region AbActivitiesSubscriptions_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbActivitiesSubscriptions_Update')) DROP VIEW dbo.AbActivitiesSubscriptions_Update
GO
CREATE VIEW dbo.AbActivitiesSubscriptions_Update
	AS
	SELECT      *
	FROM       Aufr
	WHERE AufR.AuffNR IN (SELECT AuffNr FROM AufführgTabelle AS a WHERE  dbo.IsActivityWritable(a.Datum, a.SNr, a.Art, a.Status, a.UC) = 1 OR IS_MEMBER('Vorverkauf ändern') = 1)
	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbActivitiesSubscriptions_Update TO PUBLIC
GO
--#endregion

--#region AbActivityScenes_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbActivityScenes_Update')) DROP VIEW dbo.AbActivityScenes_Update
GO
CREATE VIEW dbo.AbActivityScenes_Update
	AS
	SELECT     *
	FROM       ActivityScenes
	WHERE ActivityScenes.ActivityId IN (SELECT AuffNr FROM AufführgTabelle AS a WHERE  dbo.IsActivityWritable(a.Datum, a.SNr, a.Art, a.Status, a.UC) = 1)
	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbActivityScenes_Update TO PUBLIC
GO
--#endregion

--#region AbActivityDetails_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbActivityDetails_Update')) DROP VIEW dbo.AbActivityDetails_Update
GO
CREATE VIEW dbo.AbActivityDetails_Update
	AS
	SELECT      *
	FROM       AufführgDetails AS AD 
	WHERE ad.AuffNR IN (SELECT AuffNr FROM AufführgTabelle AS a WHERE  dbo.IsActivityWritable(a.Datum, a.SNr, a.Art, a.Status, a.UC) = 1 OR IS_MEMBER('Vorverkauf ändern') = 1 OR IS_MEMBER('CostsWrite') = 1)

	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbActivityDetails_Update TO PUBLIC
GO
--#endregion

--#region AbJourneyTimetabl_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbJourneyTimetabl_Update')) DROP VIEW dbo.AbJourneyTimetabl_Update
GO
CREATE VIEW dbo.AbJourneyTimetabl_Update
	AS
	SELECT      *
	FROM       Fahrplan AS AD 
	WHERE ad.AuffNR IN (SELECT AuffNr FROM AufführgTabelle AS a WHERE  dbo.IsActivityWritable(a.Datum, a.SNr, a.Art, a.Status, a.UC) = 1)

	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbJourneyTimetabl_Update TO PUBLIC
GO
--#endregion

--#region AbPersEmployee_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbPersEmployee_Update')) DROP VIEW dbo.AbPersEmployee_Update
GO
CREATE VIEW dbo.AbPersEmployee_Update
	
AS
	SELECT  *  
	FROM  PersEmployee 
	WHERE PersEmployee.PersonId IN (SELECT pp.PersNr 
	FROM PersH AS PP
		INNER JOIN UserGruppe AS UG ON pp.Besitzer =  UG.GruppeID 
		INNER JOIN UserPW ON Ug.Userid = UserPW.Userid 
	WHERE  ug.BerechtigungsbereichID = 0 AND ug.Schreiben = 1  AND ug.OptionsFlag & 256 = 256 AND Username = CURRENT_USER GROUP BY pp.PersNr)

	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbPersEmployee_Update TO PUBLIC
GO
--#endregion

--#region AbProductionDepartment_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbProductionDepartment_Update')) DROP VIEW dbo.AbProductionDepartment_Update
GO
CREATE VIEW dbo.AbProductionDepartment_Update
AS
	SELECT  *  
	FROM  StückeDienstplan AS pd
	WHERE pd.DienstplanArtenID IN (SELECT d.DienstplanArtenID
	FROM DienstplanArtenTabelle AS d
		INNER JOIN UserGruppe AS UG ON d.ModulGruppeID =  UG.GruppeID 
		INNER JOIN UserPW ON Ug.Userid = UserPW.Userid 
	WHERE ug.BerechtigungsbereichID = 5 AND ug.Schreiben = 1 AND Username = CURRENT_USER )

	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON AbProductionDepartment_Update TO PUBLIC
GO
--#endregion

--2016-01-13 sh
--tabelle und view für resourceobjectwerte und -versicherungen

--#region ResourceObjectMonetaryValues
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('ResourceObjectMonetaryValues') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.ResourceObjectMonetaryValues ( 
		ResourceObjectMonetaryValueId INT IDENTITY(1,1) NOT NULL,
		ResourceObjectId INT NOT NULL,
		Start DATETIME NULL,
		[End] DATETIME NULL,
		MonetaryValue MONEY NOT NULL CONSTRAINT DF_ResourceObjectMonetaryValues_MonetaryValue DEFAULT 0,
		Valuation VARCHAR(20) NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		CONSTRAINT PK_ResourceObjectMonetaryValues PRIMARY KEY (ResourceObjectMonetaryValueId ASC) ON [PRIMARY],
		CONSTRAINT FK_ResourceObjectMonetaryValues_ResourceObjects FOREIGN KEY (ResourceObjectId) REFERENCES dbo.ResourceObjects(ResourceObjectId)
	) ON [PRIMARY]
END
GO
--#endregion
--#region Bugfix Spaltengröße zu klein 
--DH 20160415
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Valuation' and c.length=20   and o.id = OBJECT_ID(N'[dbo].[ResourceObjectMonetaryValues]')) 
BEGIN
	ALTER TABLE [dbo].[ResourceObjectMonetaryValues] ALTER COLUMN Valuation  NVARCHAR(60) NULL 
END
GO
--#endregion
--#region AbResourceObjectMonetaryValues
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceObjectMonetaryValues'))
DROP VIEW [dbo].AbResourceObjectMonetaryValues

GO
CREATE VIEW [dbo].AbResourceObjectMonetaryValues
AS 
SELECT romv.* FROM ResourceObjectMonetaryValues romv WHERE romv.ResourceObjectId IN (
		SELECT  ro.ResourceObjectId FROM ResourceObjects AS ro
		INNER JOIN dbo.Resources AS r ON ro.ResourceId = r.ResourceId
		INNER JOIN UserGruppe AS ug ON r.OwnerId = ug.GruppeId
		INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 16 
	)
GO
GRANT SELECT ON AbResourceObjectMonetaryValues TO public
GO
--#endregion

--#region AbResourceObjectMonetaryValues_Update
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceObjectMonetaryValues_Update'))
DROP VIEW [dbo].AbResourceObjectMonetaryValues_Update

GO
CREATE VIEW [dbo].AbResourceObjectMonetaryValues_Update
AS 
SELECT romv.* FROM ResourceObjectMonetaryValues romv WHERE romv.ResourceObjectId IN (
		SELECT ro.ResourceObjectId FROM dbo.ResourceObjects AS ro
		INNER JOIN Resources r ON r.ResourceId = ro.ResourceId
		INNER JOIN UserGruppe AS ug ON r.OwnerId = ug.GruppeId
		INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 16 AND Schreiben = 1
	)
WITH CHECK OPTION
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResourceObjectMonetaryValues_Update TO public
GO
--#endregion

--#region ResourceObjectInsurances
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('ResourceObjectInsurances') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.ResourceObjectInsurances ( 
		ResourceObjectInsuranceId INT IDENTITY(1,1) NOT NULL,
		ResourceObjectId INT NOT NULL,
		InsuredPersonId INT NULL,
		Start DATETIME NULL,
		[End] DATETIME NULL,
		IsPrivateInsurance BIT NOT NULL CONSTRAINT DF_ResourceObjectInsurances_IsPrivateInsurance DEFAULT 0,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		CONSTRAINT PK_ResourceObjectInsurances PRIMARY KEY (ResourceObjectInsuranceId ASC) ON [PRIMARY],
		CONSTRAINT FK_ResourceObjectInsurances_ResourceObjects FOREIGN KEY (ResourceObjectId) REFERENCES dbo.ResourceObjects(ResourceObjectId),
		CONSTRAINT FK_ResourceObjectInsurances_InsuredPersonId FOREIGN KEY (InsuredPersonId) REFERENCES dbo.PersH(PersNr),
	) ON [PRIMARY]
END
GO
--#endregion

--#region AbResourceObjectInsurances
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceObjectInsurances'))
DROP VIEW [dbo].AbResourceObjectInsurances

GO
CREATE VIEW [dbo].AbResourceObjectInsurances
AS 
SELECT roi.* FROM ResourceObjectInsurances roi WHERE roi.ResourceObjectId IN (
		SELECT  ro.ResourceObjectId FROM ResourceObjects AS ro
		INNER JOIN dbo.Resources AS r ON ro.ResourceId = r.ResourceId
		INNER JOIN UserGruppe AS ug ON r.OwnerId = ug.GruppeId
		INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 16 
	)
GO
GRANT SELECT ON AbResourceObjectInsurances TO public
GO
--#endregion

--#region AbResourceObjectInsurances_Update
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceObjectInsurances_Update'))
DROP VIEW [dbo].AbResourceObjectInsurances_Update

GO
CREATE VIEW [dbo].AbResourceObjectInsurances_Update
AS 
SELECT roi.* FROM ResourceObjectInsurances roi WHERE roi.ResourceObjectId IN (
		SELECT ro.ResourceObjectId FROM dbo.ResourceObjects AS ro
		INNER JOIN Resources r ON r.ResourceId = ro.ResourceId
		INNER JOIN UserGruppe AS ug ON r.OwnerId = ug.GruppeId
		INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 16 AND Schreiben = 1
	)
WITH CHECK OPTION
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResourceObjectInsurances_Update TO public
GO
--#endregion

--2016-01-15 sh
--#region Typ von verwendeten Vorlagen für Chor-/Orchesterabrechnung ändern in neuen ReportType
UPDATE Vorlagen SET ReportType = 1301 WHERE VorlagenID IN (
	SELECT p.TemplateId FROM [Plan] p WHERE p.PlanArt = 140 AND p.TemplateId IS NOT NULL
)
GO
--#endregion

--2016-04-12 sh
--#region kopieren der qualifikationen von den aufgabenbereichen zu den rollen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND Minor < 161) OR (Major = 4 AND Minor = 161 AND Build < 102)))
BEGIN 
	INSERT INTO Qualification_Role (QualificationId, RoleId, LM, UM, LC ,UC) 
	SELECT l.QualificationId, r.Id, ISNULL(l.LM,'19000101'), ISNULL(l.UM,-1), ISNULL(l.UC,'19000101'), ISNULL(l.UC,-1) 
	FROM BesetzungStd AS r
		INNER JOIN RollenLtg AS L ON r.RollenLtgID = l.RollenLtgID 
	WHERE NOT l.QualificationId IS NULL AND NOT EXISTS(SELECT * FROM Qualification_Role AS qr WHERE qr.RoleId = r.id AND qr.QualificationId = l.QualificationId)
END
GO
--#endregion

--20160203 bm
--#region Resourcentyp für Cases
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND Minor < 161) OR (Major = 4 AND Minor = 161 AND Build < 3)))
BEGIN	
	INSERT INTO ResourceTypes (Name, Module, LC, UC, LM, UM, CategoryId) VALUES ('Cases', 2, GETDATE(), 0, GETDATE(), 0, 3)																																	
END
GO
--#endregion

--20160204 bm	
--#region Feiertagsausgleichsets ohne dbo-Rechte anlegen und löschen
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ShiftType' AND o.id = OBJECT_ID(N'dbo.FeiertageAusg'))
BEGIN
	ALTER TABLE FeiertageAusg ADD ShiftType INT NOT NULL CONSTRAINT DF_FeiertageAusg_ShiftTimeType DEFAULT(0)
	EXEC('UPDATE FeiertageAusg SET ShiftType = ArbFrei')
	CREATE UNIQUE NONCLUSTERED INDEX IX_HolidayBalanceSetID_FtArt_Wt_ShiftType ON dbo.FeiertageAusg (HolidayBalanceSetID, FTArt, Wt, ShiftType) ON [PRIMARY]
	DROP INDEX IX_HolidayBalanceSetID_FtArt_Wt ON FeiertageAusg
	ALTER TABLE FeiertageAusg DROP COLUMN ArbFrei
	GRANT SELECT, INSERT, UPDATE, DELETE ON FeiertageAusg TO PersoAdmin
	GRANT SELECT, INSERT, UPDATE, DELETE ON HolidayBalanceSet TO PersoAdmin --Löschen des Std.-Eintrags (Id 0) wird quellcodeseitig unterbunden
END
GO
--#endregion

--20160210 bm
--#region "Ist exportiert"-Flag für Verträge.
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsExported' AND o.id = OBJECT_ID(N'dbo.Vertrag'))
BEGIN
	ALTER TABLE Vertrag ADD IsExported BIT NOT NULL CONSTRAINT DF_Vertrag_IsExported DEFAULT(0)
END
GO
--#endregion

--2016-02-25 sh
--neuer view für AbResourceObjects mit join von werten und versicherungen
--#region AbResourceObjectsWithTodaysInsurancesAndMonetaryValue
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceObjectsWithTodaysInsurancesAndMonetaryValue'))
	DROP VIEW [dbo].AbResourceObjectsWithTodaysInsurancesAndMonetaryValue
GO
CREATE VIEW [dbo].AbResourceObjectsWithTodaysInsurancesAndMonetaryValue
AS 	
	SELECT  ro.*,
			roip.ResourceObjectInsuranceId AS CurrentPrivateResourceObjectInsuranceId,
			roip.InsuredPersonId AS CurrentPrivateInsuredPersonId,
	        rois.ResourceObjectInsuranceId AS CurrentDutyResourceObjectInsuranceId,
			rois.InsuredPersonId AS CurrentDutyInsuredPersonId,
			romv.ResourceObjectMonetaryValueId AS CurrentResourceObjectMonetaryValueId
	FROM ResourceObjects AS ro 
	INNER JOIN dbo.Resources AS r ON ro.ResourceId = r.ResourceId
	INNER JOIN UserGruppe AS ug ON r.OwnerId = ug.GruppeID
	INNER JOIN UserPw AS pw ON ug.UserId = pw.UserID
	LEFT JOIN ResourceObjectInsurances roip ON roip.ResourceObjectId = ro.ResourceObjectId
		 AND roip.IsPrivateInsurance = 1 AND ([roip].[Start] IS NULL OR [roip].[Start] <= GETDATE()) AND ([roip].[End] IS NULL OR [roip].[End] >= GETDATE())
	LEFT JOIN ResourceObjectInsurances rois ON rois.ResourceObjectId = ro.ResourceObjectId
		 AND rois.IsPrivateInsurance = 0 AND ([rois].[Start] IS NULL OR [rois].[Start] <= GETDATE()) AND ([rois].[End] IS NULL OR [rois].[End] >= GETDATE())
	LEFT JOIN ResourceObjectMonetaryValues romv ON romv.ResourceObjectId = ro.ResourceObjectId 
		 AND ([romv].[Start] IS NULL OR [romv].[Start] <= GETDATE()) AND ([romv].[End] IS NULL OR [romv].[End] >= GETDATE())
	WHERE pw.Username = CURRENT_USER AND ug.BerechtigungsbereichID = 16 
GO
GRANT SELECT ON AbResourceObjectsWithTodaysInsurancesAndMonetaryValue TO public
GO
--#endregion
--2016-03-07 sh
--resource update views mit check options erweitert
--#region AbResources_Update
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResources_Update'))
	DROP VIEW [dbo].AbResources_Update
GO
CREATE VIEW [dbo].AbResources_Update
AS 
SELECT  r.* FROM dbo.Resources AS r
		 WHERE r.OwnerId IN ( SELECT GruppeId FROM UserGruppe AS ug
				INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
			WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 16 AND Schreiben = 1)
WITH CHECK OPTION
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResources_Update TO public
GO
--#endregion
--#region AbResourceObjects_Update
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceObjects_Update'))
	DROP VIEW [dbo].AbResourceObjects_Update
GO
CREATE VIEW [dbo].AbResourceObjects_Update
AS 
SELECT  ro.* FROM dbo.ResourceObjects AS ro
		 WHERE  ro.ResourceId IN (SELECT r.ResourceId FROM Resources AS r 
														INNER JOIN UserGruppe AS ug ON r.OwnerId = ug.GruppeId
														INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
									WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 16 AND Schreiben = 1)
WITH CHECK OPTION
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResourceObjects_Update TO public
GO
--#endregion
--#region AbResourceBlockPeriods_Update
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceBlockPeriods_Update'))
	DROP VIEW [dbo].AbResourceBlockPeriods_Update
GO

CREATE VIEW [dbo].AbResourceBlockPeriods_Update
AS 
SELECT  rb.* FROM dbo.ResourceBlockPeriods AS rb
		 WHERE  rb.ResourceId IN (SELECT r.ResourceId FROM Resources AS r 
														INNER JOIN UserGruppe AS ug ON r.OwnerId = ug.GruppeId
														INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
									WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 16 AND Schreiben = 1)
WITH CHECK	OPTION
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResourceBlockPeriods_Update TO public
GO
--#endregion

--2016-03-11 sh
--#region alte instrumentenwerte und versicherungen umschreiben
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 161 OR (Minor=161 AND Build < 5)))))
BEGIN
	--versicherungen hinzufügen
	INSERT INTO ResourceObjectInsurances (ResourceObjectId, InsuredPersonId, Start, [End], IsPrivateInsurance, LC, UC, LM, UM)
	SELECT ro.ResourceObjectId, ro.PersonId, NULL, NULL, 0, GETDATE(), -100, GETDATE(), -100
	FROM ResourceObjects ro 
	INNER JOIN Resources AS r ON ro.ResourceId = r.ResourceId 
	INNER JOIN ResourceTypes AS rt ON r.ResourceTypeId = rt.ResourceTypeId 
	WHERE rt.CategoryId = 1 AND  NOT ro.PersonId IS NULL AND NOT ro.ResourceData IS NULL AND ro.ResourceData LIKE '%IsAssured="true"%'
	PRINT 'Versicherungen für Instrumente umgeschrieben'

	--werte hinzufügen
	INSERT INTO ResourceObjectMonetaryValues (ResourceObjectId, Start, [End], MonetaryValue, Valuation, LC, UC, LM, UM)
	SELECT ro.ResourceObjectId, NULL, NULL, ro.Value, 
	CAST(CAST(CAST(ro.ResourceData AS NVARCHAR(MAX)) AS XML).query('data(/InstrumentResourceObjectData/@ValueEstimatedBy)') AS VARCHAR(60)), GETDATE(),-100,GETDATE(),-100
	FROM ResourceObjects ro
	INNER JOIN Resources AS r ON ro.ResourceId = r.ResourceId 
	INNER JOIN ResourceTypes AS rt ON r.ResourceTypeId = rt.ResourceTypeId 
	WHERE rt.CategoryId = 1 AND NOT ro.ResourceData IS NULL	AND NOT ro.Value IS NULL AND NOT ro.Value = 0
	PRINT 'Wert für Instrumente umgeschrieben'
END
GO
--#endregion

--20160224 DH
--#region CloseMonth mit Leseberechtigung
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.CloseMonthInternal'))
	DROP PROCEDURE dbo.CloseMonthInternal

GO
CREATE PROCEDURE CloseMonthInternal 
	@dienstplanartID INT, 
	@month INT,
	@userID int
WITH EXECUTE AS 'dbo'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	UPDATE DienstplanArtenTabelle
	SET MAbschluss=@month
	WHERE 
		DienstplanArtenID=@dienstplanartID 
		AND DienstplanArtenID IN (
			SELECT da.DienstplanArtenID
			FROM DienstplanArtenTabelle  AS da		
				LEFT JOIN	(
					SELECT * FROM UserGruppe WHERE BerechtigungsbereichID = 5 AND OptionsFlag&2 = 2) AS UG ON da.ModulGruppeID = UG.GruppeID
				LEFT JOIN	UserPW AS up	ON up.UserID= UG.UserID 
			WHERE up.UserID = @userID)
		
END
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.CloseMonth'))
	DROP PROCEDURE dbo.CloseMonth
GO
CREATE PROCEDURE dbo.CloseMonth
	@dienstplanartID INT, 
	@month INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE 
	@userId INT
	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
	EXECUTE CloseMonthInternal @dienstplanartID, @month, @userId
END
GO
GRANT EXECUTE ON CloseMonth TO public
GO
--#endregion

--Hinter die Umbennenung der Gruppe "OrchesterAdmin" verschoben
GRANT  INSERT, UPDATE, DELETE  ON dbo.CastCreditTypes TO OrchesterAdmin

--20160329 bm	
--#region Pro OrderType nur einen OrderValidationType
IF NOT EXISTS (SELECT * FROM  sysindexes o WHERE  o.id = OBJECT_ID(N'dbo.OrderValidationTypes') AND o.Name = 'IX_OrderType')
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX IX_OrderType ON dbo.OrderValidationTypes (OrderType) ON [PRIMARY]
END
GO
--#endregion

--20160421 bm
--#region Dauer von Werken auch in Sekunden
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Duration' AND c.xtype = 56 AND o.id = OBJECT_ID(N'[dbo].[WorkVersions]')) --xtype 56 entspricht INT
BEGIN
	EXEC sp_rename 'WorkVersions.Duration', 'DurationOld', 'COLUMN';
	ALTER TABLE WorkVersions ADD [Duration] DATETIME NULL
	EXEC('UPDATE WorkVersions SET Duration = DATEADD(MINUTE, DurationOld, ''19000101'')')
	EXEC('ALTER TABLE WorkVersions DROP COLUMN DurationOld')
END
GO
--#endregion


--20160224 bm
--#region Neue Tabelle, um einer Tournee Instrumente und Cases zuzuordnen.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.Tours_InventoryItems') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.Tours_InventoryItems (
			TourInventoryItemId INT IDENTITY(1,1) NOT NULL,--ID
			TourId INT NOT NULL,
			ResourceObjectId INT NOT NULL, 
			ContractId INT NULL, 
			CaseResourceObjectId INT NULL, 
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL
			CONSTRAINT PK_Tours_InventoryItems PRIMARY KEY CLUSTERED (TourInventoryItemId ASC) ON [PRIMARY],
			CONSTRAINT FK_Tours_InventoryItems_Tours FOREIGN KEY (TourId) REFERENCES dbo.Tours (TourId),
			CONSTRAINT FK_Tours_InventoryItems_ResourceObjects FOREIGN KEY (ResourceObjectId) REFERENCES dbo.ResourceObjects (ResourceObjectId),
			CONSTRAINT FK_Tours_InventoryItems_Vertrag FOREIGN KEY (ContractId) REFERENCES dbo.Vertrag (VertragsNr),
			CONSTRAINT FK_Tours_InventoryItems_CaseResourceObjects FOREIGN KEY (CaseResourceObjectId) REFERENCES dbo.ResourceObjects (ResourceObjectId)
	) ON [PRIMARY]
	CREATE UNIQUE NONCLUSTERED INDEX IX_Tour_ResourceObjectId ON dbo.Tours_InventoryItems (TourId, ResourceObjectId) ON [PRIMARY]
END
GO
--#endregion

--20160308 
--#region Dienstreise-Flag für Tourneen
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsDutyTour' AND o.id = OBJECT_ID(N'dbo.Tours'))
BEGIN
	ALTER TABLE Tours ADD IsDutyTour INT NOT NULL CONSTRAINT DF_Tours_IsDutyTour DEFAULT(0)
END
GO
--#endregion

--#region GetCastByPeriodInternal
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastByPeriodInternal'))
	DROP PROCEDURE dbo.GetCastbyPeriodInternal 
GO					 
CREATE PROCEDURE dbo.GetCastByPeriodInternal
	@start DATETIME,
	@end DATETIME,
	@condition VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @permissionPeriodStart DATETIME
	DECLARE @permissionPeriodEnd DATETIME


	SELECT @permissionPeriodStart = MIN(s.SpzStart), @permissionPeriodEnd = MAX(s.SpzEnde)
	FROM Spielzeittabelle AS s
		INNER JOIN UserForAccess AS us	ON s.spznr = us.spznr
	WHERE us.UserId = @userId AND s.SpzNr <> 0

	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 (@include <> 0)  CREATE TABLE #Cast (CastId INT NOT NULL)

	CREATE TABLE #Permissions (UserGruppeId INT NOT NULL, BerechtigungsbereichId INT NOT NULL, OptionsFlag INT NOT NULL)
	INSERT INTO #Permissions SELECT UserGruppe.GruppeId, UserGruppe.BerechtigungsbereichID, OptionsFlag  FROM UserGruppe WHERE UserId = @userId AND BerechtigungsbereichId IN (1,2,4)


	DECLARE @fields VARCHAR(MAX)
	SET @fields  = 'B.Id, B.RolleId, B.PersNr, B.AuffNr, B.DiffVon, B.DiffBis, B.BesBem, B.Druck, B.LM, B.UM, B.LC, B.UC, B.Gesperrt, B.BesSort, B.CastStatusId, 
					B.AgencyId, B.IsCover, B.IsCanceled, B.Creditable, B.BackColorArgb, B.IsReplacement, B.ContractId '

	--Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben
	if (@include <> 0) 
		SET @SQLQuery = 'INSERT INTO #Cast SELECT b.Id '
	ELSE
		SET @SQLQuery = 'SELECT ' + @fields
					
	 SET @SQLQuery =  @SQLQuery + 
	 'FROM  BesetzungTabelle		AS B
		INNER JOIN BesetzungStd		AS ro	ON b.RolleID = ro.ID
		INNER JOIN AufführgTabelle	AS A	ON B.AuffNr = A.Auffnr
		INNER JOIN Stücke			AS p	ON A.snr = p.snr
		INNER JOIN #Permissions		AS ugA	ON ugA.UserGruppeId  = P.ModuleGroupId AND ugA.BerechtigungsbereichID = 2
		INNER JOIN #Permissions		AS ugC	ON ugC.UserGruppeId  = ro.Besitzer AND ugC.BerechtigungsbereichID = 4'
				 
		IF (@joins&1 = 1) 
			SET @SQLQuery = @SQLQuery + 'INNER JOIN (SELECT * FROM ContractDates WHERE IsCanceled = 0) AS cd ON b.ID	= cd.CastID  '	
		IF (@joins&4 = 4) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN StückeDetails ON A.Snr = StückeDetails.Snr   '
		IF (@joins&8 = 8) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN AuffTypen AS AT ON A.art = AT.AufftNr  '
			
		SET @SQLQuery = @SQLQuery + ' WHERE A.Datum 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 gesperrt erforderlich
		IF (EXISTS(SELECT * FROM #Permissions WHERE BerechtigungsbereichId = 2 AND OptionsFlag&1 <> 1)) 
		BEGIN
			SET @SQLQuery = @SQLQuery  + ' AND (a.Gesperrt = 0 OR uga.OptionsFlag&1 = 1)'
		END

		--Wenn in allen Besitzergruppe gesperrte Besetzungen gelesen werden können ist keine Prüfung auf gesperrt erforderlich
		IF (EXISTS(SELECT * FROM #Permissions WHERE BerechtigungsbereichId = 4 AND OptionsFlag&1 <> 1)) 
		BEGIN
			SET @SQLQuery = @SQLQuery  + ' AND (b.Gesperrt = 0 OR ugc.OptionsFlag&1 = 1)'
		END

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


		EXECUTE sp_Executesql @SQLQuery 

	IF (@include <> 0) --Wenn keine weiteren Daten mitgeladen werden wurde das Ergebnis bereits zurückgeben
		BEGIN
			SET @SQLQuery = 'SELECT ' + @fields +' FROM BesetzungTabelle AS b 
														INNER JOIN  #Cast ON b.id = #cast.CastId'
		
			EXECUTE sp_Executesql  @SQLQuery
			
		END
		
	IF (@include&1 = 1) 
		SELECT NULL AS [#1], CastBonus.* 
		FROM CastBonus 
			INNER JOIN #Cast ON CastBonus.CastId = #Cast.CastId

	IF (@include&2 = 2) 
	BEGIN
    		-- contractdates mit berechtigung laden
		CREATE TABLE #ContractDates (ContractDateId INT NOT NULL, ReplacementContractDateId INT NULL)

		INSERT INTO #ContractDates (ContractDateId, ReplacementContractDateId)
		SELECT cd.ContractDateId, cd.ReplacementContractDateId FROM ContractDates cd
		INNER JOIN VertragRolle vr ON cd.ContractRoleId = vr.VRID
		INNER JOIN Vertrag v ON vr.VertragsNr = v.VertragsNr
		INNER JOIN #Permissions p ON p.BerechtigungsbereichId = 1 AND p.UserGruppeId = v.ModulGruppeID

		--abfrage über tabelle mit join auf die ids mit berechtigungen
		SELECT NULL AS [#2], cd.*, rcd_temp.ContractDateId AS ReplacedContractDateId 
		FROM ContractDates AS cd
		INNER JOIN #ContractDates cd_temp ON cd.ContractDateId = cd_temp.ContractDateId
		LEFT JOIN #ContractDates rcd_temp ON cd.ContractDateId = rcd_temp.ReplacementContractDateId
		INNER JOIN #Cast ON cd.CastId = #Cast.CastId
	END
END
GO

--#endregion

--#region GetCastByActivityIdInternal
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastByActivityIdInternal'))
	DROP PROCEDURE dbo.GetCastByActivityIdInternal 
GO
CREATE PROCEDURE dbo.GetCastByActivityIdInternal
	@ActivityId VARCHAR(MAX),
	@condition VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @SQLQuery AS NVARCHAR(MAX)

	if (@include <> 0)  CREATE TABLE #Cast (CastId INT NOT NULL)

	CREATE TABLE #Permissions (UserGruppeId INT NOT NULL, BerechtigungsbereichId INT NOT NULL, OptionsFlag INT NOT NULL)
	INSERT INTO #Permissions SELECT UserGruppe.GruppeId, UserGruppe.BerechtigungsbereichID, OptionsFlag  FROM UserGruppe WHERE UserId = @userId AND BerechtigungsbereichId IN (1,4)


	DECLARE @fields VARCHAR(MAX)
	SET @fields  = 'B.Id, B.RolleId, B.PersNr, B.AuffNr, B.DiffVon, B.DiffBis, B.BesBem, B.Druck, B.LM, B.UM, B.LC, B.UC, B.Gesperrt, B.BesSort, B.CastStatusId, 
					B.AgencyId, B.IsCover, B.IsCanceled, B.Creditable, B.BackColorArgb, B.IsReplacement, B.ContractId '

	--Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben
	if (@include <> 0) 
		SET @SQLQuery = 'INSERT INTO #Cast SELECT b.Id '
	ELSE
		SET @SQLQuery = 'SELECT ' + @fields
					
	 SET @SQLQuery =  @SQLQuery + '	 FROM  BesetzungTabelle AS B
		INNER JOIN BesetzungStd		AS ro	ON b.RolleID = ro.ID
		INNER JOIN #Permissions AS ugC ON ugC.UserGruppeId  = ro.Besitzer AND ugC.BerechtigungsbereichId = 4'
		
		IF (@joins&1 = 1) 
			SET @SQLQuery = @SQLQuery + 'INNER JOIN (SELECT * FROM ContractDates WHERE IsCanceled = 0) AS cd ON b.ID	= cd.CastID  '

		SET @SQLQuery = @SQLQuery + ' WHERE b.Auffnr IN (' + @ActivityId + ')'
		 
		--Wenn in allen Besitzergruppe gesperrte Besetzungen gelesen werden können ist keine Prüfung auf gesperrt erforderlich
		IF (EXISTS(SELECT * FROM #Permissions WHERE BerechtigungsbereichId = 4 AND OptionsFlag&1 <> 1)) 
		BEGIN
			SET @SQLQuery = @SQLQuery  + ' AND (b.Gesperrt = 0 OR ugc.OptionsFlag&1 = 1)'
		END

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

		EXECUTE sp_Executesql @SQLQuery 

	IF (@include <> 0) --Wenn keine weiteren Daten mitgeladen werden wurde das Ergebnis bereits zurückgeben
		BEGIN
			SET @SQLQuery = 'SELECT ' + @fields +' FROM BesetzungTabelle AS b 
														INNER JOIN  #Cast ON b.id = #cast.CastId'

			EXECUTE sp_Executesql  @SQLQuery

		END
	
	IF (@include&1 = 1) 
		SELECT NULL AS [#1], CastBonus.* 
		FROM CastBonus 
			INNER JOIN #Cast ON CastBonus.CastId = #Cast.CastId
	
	IF (@include&2 = 2) 
	BEGIN
    		-- contractdates mit berechtigung laden
		CREATE TABLE #ContractDates (ContractDateId INT NOT NULL, ReplacementContractDateId INT NULL)

		INSERT INTO #ContractDates (ContractDateId, ReplacementContractDateId)
		SELECT cd.ContractDateId, cd.ReplacementContractDateId FROM ContractDates cd
		INNER JOIN VertragRolle vr ON cd.ContractRoleId = vr.VRID
		INNER JOIN Vertrag v ON vr.VertragsNr = v.VertragsNr
		INNER JOIN #Permissions p ON p.BerechtigungsbereichId = 1 AND p.UserGruppeId = v.ModulGruppeID

		--abfrage über tabelle mit join auf die ids mit berechtigungen
		SELECT NULL AS [#2], cd.*, rcd_temp.ContractDateId AS ReplacedContractDateId 
		FROM ContractDates AS cd
		INNER JOIN #ContractDates cd_temp ON cd.ContractDateId = cd_temp.ContractDateId
		LEFT JOIN #ContractDates rcd_temp ON cd.ContractDateId = rcd_temp.ReplacementContractDateId
		INNER JOIN #Cast ON cd.CastId = #Cast.CastId
	END
END
GO
--#endregion

--#region GetCastByPeriod
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastByPeriod'))
	DROP PROCEDURE dbo.GetCastByPeriod 
GO
CREATE PROCEDURE dbo.GetCastByPeriod
	@start DATETIME,
	@end DATETIME,
	@condition VARCHAR(MAX),
	@joins INT,
	@include INT
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @userId INT
	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
	EXECUTE GetCastByPeriodInternal @start, @end, @condition, @joins, @include, @userId

END
GO
GRANT EXECUTE ON dbo.GetCastByPeriod TO public
GO
--#endregion

--#region GetCastByActivityId
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastByActivityId'))
	DROP PROCEDURE dbo.GetCastByActivityId 
GO
CREATE PROCEDURE dbo.GetCastByActivityId
	@ActivityId VARCHAR(MAX),
	@condition VARCHAR(MAX),
	@joins INT,
	@include INT
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @userId INT
	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
	EXECUTE GetCastByActivityIdInternal @ActivityId, @condition, @joins, @include, @userId
END
GO
GRANT EXECUTE ON dbo.GetCastByActivityId TO public
--#endregion

--#region GetCastByCastIdInternal
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastByCastIdInternal'))
	DROP PROCEDURE dbo.GetCastByCastIdInternal 
GO
CREATE PROCEDURE dbo.GetCastByCastIdInternal
	@CastId VARCHAR(MAX),
	@condition VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @SQLQuery AS NVARCHAR(MAX)

	if (@include <> 0)  CREATE TABLE #Cast (CastId INT NOT NULL)
	
	DECLARE @fields VARCHAR(MAX)
	SET @fields  = 'B.Id, B.RolleId, B.PersNr, B.AuffNr, B.DiffVon, B.DiffBis, B.BesBem, B.Druck, B.LM, B.UM, B.LC, B.UC, B.Gesperrt, B.BesSort, B.CastStatusId, 
					B.AgencyId, B.IsCover, B.IsCanceled, B.Creditable, B.BackColorArgb, B.IsReplacement, B.ContractId '

	--Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben
	if (@include <> 0) 
		SET @SQLQuery = 'INSERT INTO #Cast '
	ELSE
		SET @SQLQuery = 'SELECT ' + @fields
					
	 SET @SQLQuery =  @SQLQuery + '	 FROM  BesetzungTabelle AS B '
			
		IF (@joins&1 = 1) 
			SET @SQLQuery = @SQLQuery + 'INNER JOIN (SELECT * FROM ContractDates WHERE IsCanceled = 0) AS cd ON b.ID = cd.CastID  '

		SET @SQLQuery = @SQLQuery + ' WHERE b.Id IN (' + @CastId + ')'
			
				print @SQLQuery
		 IF (@condition IS NOT NULL AND @condition <> '')
			 SET @SQLQuery = @SQLQuery + ' AND ' + @condition 

		EXECUTE sp_Executesql @SQLQuery 

	IF (@include <> 0) --Wenn keine weiteren Daten mitgeladen werden wurde das Ergebnis bereits zurückgeben
		BEGIN
			SET @SQLQuery = 'SELECT ' + @fields +' FROM BesetzungTabelle AS b 
														INNER JOIN  #Cast ON b.id = #cast.CastId'

			EXECUTE sp_Executesql  @SQLQuery

		END

	IF (@include&1 = 1) 
		SELECT NULL AS [#1], CastBonus.* 
		FROM CastBonus 
			INNER JOIN #Cast ON CastBonus.CastId = #Cast.CastId
			
	IF (@include&2 = 2) 
	BEGIN
		-- contractdates mit berechtigung laden
		CREATE TABLE #ContractDates (ContractDateId INT NOT NULL, ReplacementContractDateId INT NULL)

		INSERT INTO #ContractDates (ContractDateId, ReplacementContractDateId)
		SELECT cd.ContractDateId, cd.ReplacementContractDateId FROM ContractDates cd
		INNER JOIN VertragRolle vr ON cd.ContractRoleId = vr.VRID
		INNER JOIN Vertrag v ON vr.VertragsNr = v.VertragsNr
		INNER JOIN UserGruppe ug ON ug.GruppeID = v.ModulGruppeID AND ug.BerechtigungsbereichID = 1 AND ug.UserID = @userId

		--abfrage über tabelle mit join auf die ids mit berechtigungen
		SELECT NULL AS [#2], cd.*, rcd_temp.ContractDateId AS ReplacedContractDateId 
		FROM ContractDates AS cd
		INNER JOIN #ContractDates cd_temp ON cd.ContractDateId = cd_temp.ContractDateId
		LEFT JOIN #ContractDates rcd_temp ON cd.ContractDateId = rcd_temp.ReplacementContractDateId
		INNER JOIN #Cast ON cd.CastId = #Cast.CastId
	END
END
GO
--#endregion

--#region GetCastByCastId
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastByCastId'))
	DROP PROCEDURE dbo.GetCastByCastId 
GO
CREATE PROCEDURE dbo.GetCastByCastId
	@CastId VARCHAR(MAX),
	@condition VARCHAR(MAX),
	@joins INT,
	@include INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @userId INT
	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
	EXECUTE GetCastByCastIdInternal @CastId, @condition, @joins, @include, @userId
END
GO
GRANT EXECUTE ON dbo.GetCastByCastId TO public
GO
--#endregion

--#region GetActivitiesInternal
ALTER PROCEDURE [dbo].[GetActivitiesInternal]
	@start DATETIME,
	@end DATETIME,
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT
	WITH EXECUTE AS 'dbo'
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @permissionPeriodStart DATETIME
	DECLARE @permissionPeriodEnd DATETIME


	SELECT @permissionPeriodStart = MIN(s.SpzStart), @permissionPeriodEnd = MAX(s.SpzEnde)
	FROM Spielzeittabelle AS s
		INNER JOIN UserForAccess AS us	ON s.spznr = us.spznr
	WHERE us.UserId = @userId AND s.SpzNr <> 0

	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 (@include <> 0)  CREATE TABLE #Activities (ActivityId INT NOT NULL)

	CREATE TABLE #Permissions (UserGruppeId INT NOT NULL, BerechtigungsbereichId INT NOT NULL, OptionsFlag INT NOT NULL)
	INSERT INTO #Permissions SELECT UserGruppe.GruppeId, UserGruppe.BerechtigungsbereichID, OptionsFlag  FROM UserGruppe WHERE UserId = @userId AND BerechtigungsbereichId IN (2,4)


	DECLARE @fields VARCHAR(MAX)
	SET @fields  = 'Aufführg.AuffNr, Aufführg.SNr, Aufführg.Datum, Aufführg.UhrBeg, Aufführg.UhrEnde, Aufführg.ZRNr, Aufführg.Pl, Aufführg.Art, Aufführg.Bem, Aufführg.BemKurz, Aufführg.BemKurz2, Aufführg.DiplaDru, Aufführg.VonZeit, 
					Aufführg.Status, Aufführg.PreisNr, Aufführg.ZuAuffNr, Aufführg.LM ,Aufführg.UM, Aufführg.Gesperrt, Aufführg.LC, Aufführg.UC, Aufführg.CreateCountDownId, 
					Aufführg.IsTimeSpan, Aufführg.IsExtraPayLiable '

	--Wenn keine weiteren Daten mitgeladen werden das Ergebnis direkt zurückgeben
	if (@include <> 0) 
		SET @SQLQuery = 'INSERT INTO #Activities SELECT  Aufführg.AuffNr'
	ELSE
		SET @SQLQuery = 'SELECT ' + @fields
					
	 SET @SQLQuery =  @SQLQuery + '	 FROM  AufführgTabelle AS Aufführg
		INNER JOIN  Stücke AS p ON Aufführg.snr = p.snr
		INNER JOIN #Permissions AS UG ON UG.UserGruppeId = P.ModuleGroupId AND ug.BerechtigungsbereichID = 2' 

		IF (@joins&1 = 1) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN AufführgDetails ON Aufführg.Auffnr = AufführgDetails.AuffNr '

		IF (@joins&2 = 2) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN PresseText AS PT ON PT.AuffNr = Aufführg.AuffNr '
			--Wenn Abfrage direkt ausgeführt wird Felder für oder Joinen hinzufügen. Ggf auch bei Abfrge unten prüfen
		IF (@joins&4 = 4 OR (@include = 0 AND CHARINDEX('zr.',@order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN Zr ON Aufführg.ZrNr = Zr.ZrNr '

		IF (@joins&8 = 8 OR (@include = 0 AND CHARINDEX('AuffTypen.',@order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN AuffTypen ON Aufführg.Art = AuffTypen.AuffTNr '

		IF (@joins&16 = 16) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN StückeDetails ON Aufführg.Snr = StückeDetails.Snr '

		IF (@joins&32 = 32) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN Gruppe ON p.ModuleGroupId = Gruppe.GruppeID '
			
		SET @SQLQuery = @SQLQuery + ' WHERE Aufführg.Datum 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 gesperrt erforderlich
		IF (EXISTS(SELECT * FROM #Permissions WHERE BerechtigungsbereichId = 2 AND OptionsFlag&1 <> 1)) 
		BEGIN
			SET @SQLQuery = @SQLQuery  + ' AND (Aufführg.Gesperrt = 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 ' + @fields +' FROM Aufführgtabelle AS Aufführg 
														INNER JOIN  #Activities ON Aufführg.auffnr = #Activities.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('zr.', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN Zr ON Aufführg.ZrNr = Zr.ZrNr '
				END
				IF (CHARINDEX('Titel', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN  Stücke AS p ON Aufführg.snr = p.snr '
				END
				IF (CHARINDEX('AuffTypen', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN AuffTypen ON Aufführg.Art = AuffTypen.AuffTNr '
				END
				SET @SQLQuery = @SQLQuery + @order 

			END

			EXECUTE sp_Executesql  @SQLQuery

		END

	IF (@include&1 = 1) 
		SELECT NULL AS [#1], AufführgDetails.* 
		FROM AufführgDetails 
			INNER JOIN #Activities On AufführgDetails.AuffNr = #Activities.ActivityId;

	IF (@include&2 = 2) 
		SELECT NULL AS [#2], PresseText.* 
		FROM PresseText 
			INNER JOIN #Activities ON PresseText.AuffNr = #Activities.ActivityId;

	IF (@include&4 = 4) 
		SELECT NULL AS [#4], Aufr.*, Ring.ZuAbo 
		FROM Aufr 
			INNER JOIN Ring ON AufR.AboNr = Ring.AboNr
			INNER JOIN #Activities ON Aufr.AuffNr = #Activities.ActivityId;

	IF (@include&8 = 8) 
		SELECT NULL AS [#8], BesetzungTabelle.* 
		FROM BesetzungTabelle 
			INNER JOIN #Activities ON BesetzungTabelle.AuffNr = #Activities.ActivityId
			INNER JOIN BesetzungStd ON BesetzungStd.Id = BesetzungTabelle.RolleID
			INNER JOIN #Permissions AS UG ON UG.UserGruppeId  = BesetzungStd.Besitzer AND  ug.BerechtigungsbereichID = 4 AND (BesetzungTabelle.Gesperrt = 0 OR ug.OptionsFlag&1 = 1)
			
	IF (@include&16 = 16) 
	BEGIN
		 DECLARE @changeSince  DATETIME
		 SET @changeSince = GETDATE() - 21 
		SELECT NULL AS [#16],ax.*
		FROM  XHDelAufführg AS ax
			INNER JOIN #Activities ON ax.AuffNr = #Activities.ActivityId
			LEFT JOIN (SELECT xhid FROM KenntnisDel WHERE tabl = 'Aufführg' 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], AuffDienstplanArten.* 
		FROM AuffDienstplanArten 
			INNER JOIN #Activities ON AuffDienstplanArten.AuffNr = #Activities.ActivityId;
						
	IF (@include&256 = 256) 
		SELECT NULL AS [#256], ActivityScenes.* 
		FROM ActivityScenes 
			INNER JOIN #Activities ON ActivityScenes.ActivityId = #Activities.ActivityId;
			
	IF (@include&512 = 512) 
		SELECT NULL AS [#512], AuffTechAufgabe.* 
		FROM AuffTechAufgabe 
			INNER JOIN #Activities ON AuffTechAufgabe.AuffNr = #Activities.ActivityId;
END




GO
--#endregion

--20160408 DH
--#region Bugfix Budget mit Canceled Orders
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'isCanceled' and c.length>1  and o.id = OBJECT_ID(N'[dbo].[Orders]'))
BEGIN
ALTER TABLE dbo.Orders DROP CONSTRAINT df_orders_isCanceled
ALTER TABLE dbo.Orders ALTER COLUMN IsCanceled INT NOT NULL 
ALTER TABLE dbo.Orders ADD CONSTRAINT DF_Orders_IsCanceled DEFAULT 0 FOR isCanceled
END
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'isCanceled' and c.length>1   and o.id = OBJECT_ID(N'[dbo].[Orders]')) 
AND EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND Minor < 161) OR (Major = 4 AND Minor = 161 AND Build < 101)))
BEGIN	
	UPDATE dbo.Orders 
		SET IsCanceled=2 
		WHERE OrderId IN (
			SELECT DISTINCT OrderId FROM Orders O
			INNER JOIN OrderValidationTypes AS ovt ON o.OrderType = ovt.OrderType
			INNER JOIN OrderValidationApprovalStatus OVAS ON O.OrderApprovalStatusId=OVAS.OrderApprovalStatusId 
			AND ovas.OrderValidationTypeId = ovt.OrderValidationTypeId
			WHERE OVAS.CancelBehavior = 3
			AND O.IsCanceled=1) 
END
GO
ALTER PROCEDURE [dbo].[DeleteActivityRelations] 
	@ActivityId INT
AS
BEGIN
SET NOCOUNT ON
	IF EXISTS (SELECT * FROM ActivitiesUpdate WHERE AuffNr= @ActivityId)
	BEGIN
		DELETE FROM CastBonus WHERE CastBonus.CastId IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		DELETE FROM CashCommandPos_Cast WHERE CastId IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		DELETE FROM BesetzungTabelle WHERE Auffnr = @activityId
		DELETE FROM ActivityScenes WHERE ActivityId = @activityId
		DELETE FROM AuffDienstplanArten WHERE Auffnr = @activityId
		DELETE FROM AuffTechAufgabe WHERE Auffnr = @activityId
		DELETE FROM Kosten_Aufführg WHERE Auffnr = @activityId
		DELETE FROM PresseText WHERE Auffnr = @activityId
		DELETE FROM AufführgDetails WHERE Auffnr = @activityId
		DELETE FROM Fahrplan WHERE AuffNr = @ActivityId
		DELETE FROM CostUnitWorkingTime WHERE ActivityId = @activityId
		UPDATE AufführgTabelle SET ZuAuffNr = NULL WHERE ZuAuffNr = @ActivityId
	END	
END	
GO
--#endregion

--2016-04-12 sh
--#region AbQualification_Role_Update -> berechtigungsbereich 4 (besetzung) darf auch schreiben
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbQualification_Role_Update'))
	DROP VIEW dbo.AbQualification_Role_Update

GO
CREATE VIEW dbo.AbQualification_Role_Update
AS
	SELECT  Qualification_Role.*  FROM  Qualification_Role WHERE Qualification_Role.RoleId IN (SELECT pp.Id 
	FROM BesetzungStd AS PP
		INNER JOIN UserGruppe AS UG ON pp.Besitzer =  UG.GruppeID 
		INNER JOIN UserPW ON Ug.Userid = UserPW.Userid 
	WHERE  (ug.BerechtigungsbereichID = 2 OR ug.BerechtigungsbereichID = 4) AND ug.Schreiben = 1 AND Username = CURRENT_USER GROUP BY pp.Id)

	WITH CHECK OPTION
GO

GRANT SELECT ON AbQualification_Role_Update TO public
GRANT DELETE ON AbQualification_Role_Update TO public
GRANT INSERT ON AbQualification_Role_Update TO public
GRANT UPDATE ON AbQualification_Role_Update TO public
GO
--#endregion

--20160420 bm
--#region Bezeichnungsfelder für Sparte und Werkeart größer
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'SpartenBez' and c.length = 25   and o.id = OBJECT_ID(N'[dbo].[Sparte]')) 
BEGIN
	ALTER TABLE [dbo].[Sparte] ALTER COLUMN SpartenBez  VARCHAR(32) NULL
END
GO

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'TypBez' and c.length = 25   and o.id = OBJECT_ID(N'[dbo].[Typ]')) 
BEGIN
	ALTER TABLE [dbo].[Typ] ALTER COLUMN TypBez  VARCHAR(32) NULL
END
GO
--#endregion

--20160426 bm
--#region Noten ausleihen über Berechtigung auf ResourceObjects

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

GO
CREATE VIEW dbo.AbResourceObjectBorrows
AS
	SELECT * FROM ResourceObjectBorrows WHERE ResourceObjectId IN (SELECT ResourceObjectId FROM AbResourceObjects)
GO
GRANT SELECT ON AbResourceObjectBorrows TO public
GO

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

GO
CREATE VIEW dbo.AbResourceObjectBorrows_Update
AS
	SELECT * FROM ResourceObjectBorrows WHERE ResourceObjectId IN (SELECT ResourceObjectId FROM AbResourceObjects_Update)
	WITH CHECK OPTION
GO
GRANT SELECT, DELETE, INSERT, UPDATE ON AbResourceObjectBorrows_Update TO public
GO

--#endregion

--#region DeletePersonReference
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DeletePersonReference'))
BEGIN
	DROP PROCEDURE dbo.DeletePersonReference 
END
GO
Create PROCEDURE [dbo].[DeletePersonReference] (@personId INT, @del INT) AS 
	DECLARE @conflictCounter INT
	SELECT @conflictCounter = 0
	DECLARE @conflictDescription VARCHAR(MAX)
	SELECT @conflictDescription = ''

	DECLARE @Temp INT
	SELECT  @Temp = 0
	DECLARE @CRLF char(2)
	SET @CRLF =  '|'
	SET NOCOUNT On
	/*
	Eingabeparameter: 
	@personId			=> Zu bearbeitendes PersId
	@del			= 0 Nur überprüfen, ob das Löschen der Detaildaten möglich ist / 1 löschen

	Rückgabewerte:

	1. @conflictCounter			=> Anzahl der Tabellen in denen eine Löschung nicht möglich wäre.
	2. @ConflictDescription		=>	Ausgabe der Löschkonflikte
	*/

	if (@del > 0)
	--Rekusiveraufruf beim "echtem löschen mit Check (0)"
	BEGIN
		DECLARE @return_value int
		EXEC @return_value = dbo.DeletePersonReference @personId, 0

		IF (@return_value > 0) 
		BEGIN
			RETURN @return_value
		END
	END

	SELECT @Temp = Besitzer FROM PersH WHERE PersNr = @personId

	IF (dbo.GetCurrentPermission(@Temp,0) <> 2) 
	BEGIN
		SELECT @conflictCounter = @conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'No Owner Permission' + @CRLF
		SELECT @conflictCounter AS ConflictCount,  @conflictDescription AS ConflictDescription
		RETURN @conflictCounter
	END

	/*  Tabelle BesetzungstdSoli */
	IF (SELECT COUNT(*) FROM BesetzungstdSoli WHERE PersNr1 = @personId OR PersNr2 = @personId OR PersNr3 = @personId OR PersNr4 = @personId) > 0
	BEGIN 
		SELECT @conflictCounter=@conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'BasicCast' + @CRLF
	END

	/*  Tabelle Besetzung */
	IF (SELECT COUNT(*) FROM BesetzungTabelle WHERE PersNr = @personId) > 0
	BEGIN 
		SELECT @conflictCounter=@conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'Cast' + @CRLF
	END

	/*  Tabelle Produktionsplan  */
	IF (@del > 0) DELETE Produktionsplan  FROM Produktionsplan  WHERE PNr = @personId
	
	/*  Bereich der Tabellen StatusZeitR  */
	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM StatusZeitR WHERE PersNr = @personId
	IF (SELECT COUNT(*) FROM AbPersStatus_Update WHERE PersNr = @personId) < @Temp 
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Status' + @CRLF
		END
	ELSE
		BEGIN
			IF(@Temp) > 0
			BEGIN 
				IF (@del > 0) DELETE FROM StatusZeitR WHERE PersNr = @personId
			END
		END

	/*  Tabelle Vertrag  */
	IF (SELECT COUNT(*) FROM Vertrag WHERE PersNr = @personId) > 0
	BEGIN 
		SELECT @conflictCounter = @conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'Contract' + @CRLF
	END
	/*  Tabelle Vertrag  */
	IF (SELECT COUNT(*) FROM VertragDetails WHERE VertragDetails.AgPersNr = @personId) > 0  
	BEGIN 
		SELECT @conflictCounter = @conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'Contract (Agency)' + @CRLF
	END

	/* Cashcommand*/
	IF (SELECT COUNT(*) FROM CashCommand WHERE PersonId = @personId) > 0  
	BEGIN 
		SELECT @conflictCounter = @conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'CashCommand' + @CRLF
	END

	IF (SELECT COUNT(*) FROM CashCommand WHERE AgencyId=@personId) > 0  
	BEGIN 
		SELECT @conflictCounter = @conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'CashCommand (Agency)' + @CRLF
	END

	/* PersonalDetails*/
	IF (SELECT COUNT(*) FROM PersonalDetails WHERE HealthInsuranceId=@personId) > 0
	BEGIN 
		SELECT @conflictCounter = @conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'Personal( HealthInsurance)' + @CRLF
	END

	IF (SELECT COUNT(*) FROM PersonalDetails WHERE TaxOfficeId = @personId) > 0  
	BEGIN 
		SELECT @conflictCounter = @conflictCounter + 1
		SELECT @conflictDescription = @conflictDescription + 'Personal (TaxOffice)' + @CRLF
	END

	/*  Tabelle PersMasse  */
	IF (@del > 0) DELETE FROM PersMasse WHERE PersNr = @personId
	
	/* ------------------------------------Dispositionsbereich ende -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

	IF (@del > 0) DELETE FROM PersonalDetails WHERE Pnr = @personId

	/*  Tabelle PersAgentur  */
	IF  (SELECT PERMISSIONS(OBJECT_ID('PersAgentur'))&2)<>2     /*  Keine Updateberechtingung    */
		BEGIN
			IF (SELECT COUNT(*) FROM PersAgentur WHERE PersNr = @personId) > 0  
			BEGIN 
				SELECT @conflictCounter=@conflictCounter+1
				SELECT @conflictDescription = @conflictDescription + 'Secondary Person' + @CRLF
			END
	
			IF (SELECT COUNT(*) FROM PersAgentur WHERE childPersNr=@personId) > 0  
			BEGIN 
				SELECT @conflictCounter = @conflictCounter + 1
				SELECT @conflictDescription = @conflictDescription + 'Secondary Person' + @CRLF
			END
		END
	ELSE
		BEGIN
			IF (@del > 0) 
			BEGIN
				DELETE FROM PersAgentur WHERE PersNr = @personId
				DELETE FROM PersAgentur WHERE childPersNr = @personId
			END
		END

	/*  Tabelle BesRepertoire  */
	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM BesRepertoire WHERE PersNr = @personId
	IF (SELECT COUNT(*) FROM RoleQualifications_Update WHERE PersNr = @personId) < @Temp 
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Repertoire' + @CRLF
		END
	ELSE
		BEGIN
			IF(@Temp) > 0
			BEGIN 
				IF (@del > 0) DELETE FROM BesRepertoire WHERE PersNr = @personId
			END
		END

	/*  Tabelle XHDelBasicCast */
	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM XHDelBasicCast WHERE PersonOldId = @personId OR PersonNewId = @personId
	IF (SELECT COUNT(*) FROM AbXHDelBasicCast_Update WHERE PersonOldId = @personId OR PersonNewId = @personId) < @Temp
		BEGIN 
			SELECT @conflictCounter=@conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'BasicCastHistory' + @CRLF
		END
	ELSE
		BEGIN
			IF(@Temp) > 0
			BEGIN 
				IF (@del > 0) DELETE FROM XHDelBasicCast WHERE PersonOldId = @personId OR PersonNewId = @personId
			END
		END

	/*  Tabelle PersonalHonorar  */
	IF  (SELECT PERMISSIONS(OBJECT_ID('PersonalHonorar'))&2)<>2     /*  Keine Updateberechtingung    */
		BEGIN
			IF (SELECT COUNT(*) FROM PersonalHonorar WHERE PersNr = @personId) > 0
			BEGIN 
				SELECT @conflictCounter = @conflictCounter + 1
				SELECT @conflictDescription = @conflictDescription + 'Fee Notes' + @CRLF
			END
		END
	ELSE
		BEGIN
			IF (@del > 0)DELETE FROM PersonalHonorar WHERE PersNr=@personId
		END

/*  Tabelle AbPersonalNotizen_Update  */
	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM PersonalNotizen WHERE PersNr = @personId
	IF (SELECT COUNT(*) FROM AbPersonalNotizen_Update WHERE PersNr=@personId) < @Temp 
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Notes' + @CRLF
		END
	ELSE
		BEGIN
			IF(@Temp) > 0
			BEGIN 
				IF (@del > 0) DELETE FROM PersonalNotizen WHERE PersNr = @personId
			END
		END


	/*  Tabelle ABPersKom_Update  */
	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM PersKom WHERE PersNr = @personId
	IF (SELECT COUNT(*) FROM ABPersKom_Update WHERE PersNr = @personId) < @Temp
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Contactdata' + @CRLF
		END
	ELSE
		BEGIN
			IF(@Temp)>0
			BEGIN 
				IF (@del > 0)DELETE FROM PersKom WHERE PersNr=@personId
			END
		END

	/*  Tabelle ABPersAdresse_Update  */
		SELECT  @Temp = 0
		SELECT @Temp = COUNT(*) FROM PersAdresse WHERE PersNr = @personId
		IF (SELECT COUNT(*) FROM ABPersAdresse_Update WHERE PersNr = @personId) < @Temp
			BEGIN
				SELECT @conflictCounter = @conflictCounter + 1
				SELECT @conflictDescription = @conflictDescription + 'Adressdata' + @CRLF
			END
		ELSE
			BEGIN
				IF(@Temp)>0
				BEGIN 
					IF (@del > 0)DELETE FROM PersAdresse WHERE PersNr=@personId
				END
			END

	/*  Tabelle AbPersZusatzDaten_Update  */
		SELECT  @Temp = 0
		SELECT @Temp = COUNT(*) FROM PersZusatzDaten WHERE PersNr = @personId
		IF (SELECT COUNT(*) FROM AbPersZusatzDaten_Update WHERE PersNr = @personId) < @Temp 
			BEGIN
				SELECT @conflictCounter = @conflictCounter + 1
				SELECT @conflictDescription = @conflictDescription + 'Personal attribute' + @CRLF
			END
		ELSE
			BEGIN
				IF(@Temp) > 0
				BEGIN 
					IF (@del > 0)DELETE FROM PersZusatzDaten WHERE PersNr = @personId
				END
			END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM ResourceObjectBorrows WHERE PersonId = @personId AND EndDate IS NULL
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Open Borrows' + @CRLF
		END

	IF (@del > 0) DELETE FROM ResourceObjectBorrows WHERE PersonId = @personId


	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM CashCommand WHERE PersonId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'CashCommand' + @CRLF
		END


	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM CashCommand WHERE AgencyId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'CashCommand (Agency)' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM DepartmentCollectives WHERE PersonId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'DepartmentCollectives' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM Orders WHERE SupplierId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Orders' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM Orders WHERE SupplierId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Orders (Supplier)' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM OrderTravelers WHERE TravelerId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'OrderTravelers' + @CRLF
		END


	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM DienstplanArtenTabelle WHERE HeadOfDepartmentId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'Department (HeadOfDepartment)' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM PersonAttachments WHERE PersonId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'PersonAttachments' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM WorkCreators WHERE PersonId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'WorkCreators' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM WorkVersionRoles_Repertoire WHERE PersonId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'WorkVersionRoles_Repertoire (Person)' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM WorkVersionRoles_Repertoire WHERE TheaterId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'WorkVersionRoles_Repertoire (TheaterId)' + @CRLF
		END

	SELECT  @Temp = 0
	SELECT @Temp = COUNT(*) FROM WorkVersions WHERE PublisherId = @personId
	IF(@Temp) > 0
		BEGIN
			SELECT @conflictCounter = @conflictCounter + 1
			SELECT @conflictDescription = @conflictDescription + 'WorkVersions (Publisher)' + @CRLF
		END


	SELECT @conflictCounter AS ConflictCount,  @conflictDescription AS ConflictDescription
	RETURN @conflictCounter
GO

GRANT EXECUTE ON DeletePersonReference TO Public
GO
--#endregion

--#region FlightTrainNumber in TravelSteps
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'FlightTrainNumber' AND o.id = OBJECT_ID(N'dbo.TravelSteps'))
BEGIN
	ALTER TABLE TravelSteps ADD FlightTrainNumber VARCHAR(50) NULL
END
GO
--#endregion

--TK 20160520 
--#region Repetitor Rollenenmaen setzen
UPDATE BesetzungStd SET Rolle = 'Repetitor' WHERE id = -1 AND (Rolle = '' OR Rolle IS NULL)
--#endregion

--#region ResourceObjectInsurances.InsuredPersonId auf NOT NULL setzen
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'InsuredPersonId' AND o.id = OBJECT_ID(N'dbo.ResourceObjectInsurances') AND c.isnullable = 1)
BEGIN
	EXEC('UPDATE ResourceObjectInsurances SET InsuredPersonId = 260 WHERE InsuredPersonId IS NULL')
	ALTER TABLE ResourceObjectInsurances ALTER COLUMN InsuredPersonId INT NOT NULL
END
GO
--#endregion

--20160526 bm
--#region BasicCastHistory-Season wie bei BasicCast NotNull
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'SeasonId' AND o.id = OBJECT_ID(N'dbo.XHDelBasicCast') AND c.isnullable = 1)
BEGIN
	EXEC('UPDATE XHDelBasicCast SET SeasonId = 0 WHERE SeasonId IS NULL')
	ALTER TABLE XHDelBasicCast ALTER COLUMN SeasonId INT NOT NULL
END
GO
--#endregion

--2016-06-08 TK
--#region PayOutMonthValue in RapportSpalte
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'PayOutMonthValue' AND o.id = OBJECT_ID(N'dbo.RapportSpalte'))
BEGIN
	ALTER TABLE RapportSpalte ADD PayOutMonthValue BIT NOT NULL DEFAULT 0
END
GO
--#endregion

--#region ParentTravelStepTemplateStepId in TravelStepsTemplateSteps
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ParentTravelStepTemplateStepId' AND o.id = OBJECT_ID(N'dbo.TravelStepsTemplateSteps'))
BEGIN
	ALTER TABLE TravelStepsTemplateSteps ADD ParentTravelStepTemplateStepId INT NULL
	EXEC('UPDATE TravelStepsTemplateSteps
		SET ParentTravelStepTemplateStepId = (SELECT top 1 TravelStepTemplateStepId  FROM TravelStepsTemplateSteps as a WHERE TravelStepsTemplateSteps.TravelStepTemplateId = a.TravelStepTemplateId AND TravelStepsTemplateSteps.ParentSort = a.sort ) 
	WHERE NOT ParentSort IS NULL')
END
GO
--#endregion


--2016-06-07 sh
--#region OptionsFlag für Berechtigungsbereich DutyRoster (5) umschreiben, da Alle entfernt wurde
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 163 OR (Minor=163 AND Build < 2)))))
BEGIN
	UPDATE UserGruppe SET OptionsFlag = 
	((OptionsFlag & 1) + (OptionsFlag & 2) + (OptionsFlag & 4) + (OptionsFlag & 8) + (OptionsFlag & 16) + (OptionsFlag & 256))
	WHERE BerechtigungsbereichID = 5
END
GO
--#endregion

--20160609 bm
--#region Spalteneinstellungen des Besetzungsfenster bei den Aktivitätsdetails einmalig zurücksetzen,
--damit die neue Spalte "Druckfilter" an der richtigen Position erscheint
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 163 OR (Minor = 163 AND Build < 2)))))
BEGIN
	DELETE FROM [Settings] WHERE form = 'ActivityCastControl'
END
GO
--#endregion

--2016-07-18 dh
--#region Zeitkonteneinstellungen pro Tarif
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.[TimeAccountsTarifsSettings]') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN

CREATE TABLE [dbo].[TimeAccountsTarifsSettings](
	[SettingsId] int IDENTITY(1,1) NOT NULL,
	[TimeAccountId] int not null,
	[TarifId] Int not null,
	[XMLSettings] varchar(max),
	LC DATETIME NOT NULL,
	UC INT NOT NULL,
	LM DATETIME NOT NULL,
	UM INT NOT NULL
 CONSTRAINT [PK_TimeAccountsTarifsSettings] PRIMARY KEY CLUSTERED 
(
	[SettingsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90),
CONSTRAINT FK_TimeAccountsTarifsSettings_TimeAccounts FOREIGN KEY ([TimeAccountId]) 
    REFERENCES dbo.Zeitkonten (ZeitKtoNr) 
    ON DELETE CASCADE
    ON UPDATE CASCADE
,
CONSTRAINT FK_TimeAccountsTarifsSettings_Tarif FOREIGN KEY ([TarifId]) 
    REFERENCES dbo.persoparameter (PersoParameterID) 
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ON [PRIMARY]
END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.[TimeAccountsTarifsSettings]')
		AND o.NAME = 'UQ_TimeAccountsTarifsSettings_TarifId'
	)
BEGIN
	CREATE NONCLUSTERED INDEX UQ_TimeAccountsTarifsSettings_TarifId ON dbo.TimeAccountsTarifsSettings (TarifId)
END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.[TimeAccountsTarifsSettings]')
		AND o.NAME = 'UQ_TimeAccountsTarifsSettings_TimeAccountId'
	)
BEGIN
	CREATE NONCLUSTERED INDEX UQ_TimeAccountsTarifsSettings_TimeAccountId ON dbo.TimeAccountsTarifsSettings (TimeAccountId)
END
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.[TimeAccountsTarifsSettings]')
		AND o.NAME = 'UQ_TimeAccountsTarifsSettings_TimeAccountIdTarifId'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_TimeAccountsTarifsSettings_TimeAccountIdTarifId ON dbo.TimeAccountsTarifsSettings (TimeAccountId,TarifId)
END

GRANT UPDATE,DELETE,SELECT ON Timeaccountstarifssettings TO Persoadmin
GRANT SELECT ON Timeaccountstarifssettings TO PUBLIC

Go


--#endregion

--#region 20160603 bm
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbCastActiveties')) 
	DROP VIEW dbo.AbCastActiveties
GO
CREATE VIEW dbo.AbCastActiveties
AS
	SELECT	ac.Datum, ac.UhrBeg, ac.UhrEnde, ac.SNr, ac.ZrNr, ac.Art, ac.Bem, ac.[Status], ca.*,
	        ro.id AS Role_RoleId, ro.RollenLtgId AS Role_TaskAreaId,
	        ro.RoleCategoryId AS Role_RoleCategoryId, ro.Besitzer AS Role_OwnerId, ro.PresseSort AS Role_PressSort, p.Alt AS alt
	FROM	AufführgTabelle				AS ac
			INNER JOIN Spielzeittabelle AS s	ON ac.Datum <= s.SpzEnde AND ac.Datum >= s.SpzStart
			INNER JOIN UserForAccess	AS us	ON s.spznr = us.spznr
			INNER JOIN UserPW			AS ua	ON us.UserId = ua.UserID
			INNER JOIN Stücke			AS p	ON ac.snr = p.snr
			INNER JOIN UserGruppe		AS uga	ON p.ModuleGroupId = uga.GruppeID AND ua.UserID = uga.UserID AND uga.BerechtigungsbereichID = 2
			INNER JOIN Besetzungtabelle	AS ca	ON ac.Auffnr = ca.AuffNr
			INNER JOIN BesetzungStd		AS ro	ON ca.RolleID = ro.ID
			INNER JOIN UserGruppe		AS ugc	ON ro.Besitzer = ugc.GruppeID AND ugc.UserID = ua.UserID AND ugc.BerechtigungsbereichID = 4
	WHERE
			ua.UserName = CURRENT_USER
			AND  (ac.Gesperrt = 0 OR uga.OptionsFlag IS NULL OR uga.OptionsFlag & 1 = 1)
			AND ((ca.Gesperrt = 0 OR (Ugc.OptionsFlag & 1 = 1)))
GO
--#endregion

GRANT SELECT ON AbCastActiveties TO PUBLIC

--20160606 bm
--#region Abteilungsbesitzer jetzt not null
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ModulGruppeID' AND o.id = OBJECT_ID(N'dbo.DienstplanArtenTabelle') AND c.isnullable = 1)
BEGIN
	BEGIN TRY
		 DELETE FROM DienstplanArtenTabelle WHERE ModulGruppeID IS NULL
		 ALTER TABLE DienstplanArtenTabelle ALTER COLUMN ModulGruppeID INT NOT NULL
	END TRY
	BEGIN CATCH
		  DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = 'Es gibt fehlerhafte Abteilungsdatensätze in Ihrer Theasoft-Datenbank. 
Bitte kontaktieren Sie den Theasoft-Support zur Lösung des Problems. 
Zur schnellen Bearbeitung nennen Sie dort bitte die Fehler-ID: 9596. 
Sie können in der Zwischenzeit, sollten keine weiteren Fehler aufgetreten sein, normal mit Ihrer Theasoft-Installation weiterarbeiten.',
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, 
               @ErrorSeverity,
               @ErrorState)
	END CATCH
END
GO
--#endregion

--2016-06-07 sh
--#region berechtigung auf DeleteGroup setzen
GRANT EXECUTE ON DeleteGroup TO SecurityAdmin 
GO
--tk 20160609
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'DepartmentWorkingTimeSettings' AND o.id = OBJECT_ID(N'dbo.DienstplanArtenTabelle'))
BEGIN
	ALTER TABLE DienstplanArtenTabelle ADD DepartmentWorkingTimeSettings VARCHAR(MAX) NULL
END

GO
--#endregion

--20160607 bm
--#region Manuell-Check für Freie Tage Gutschrift beim Dienstplaneintrag
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsDayOffCreditManual' AND o.id = OBJECT_ID(N'dbo.Dienstplan'))
BEGIN
	ALTER TABLE Dienstplan ADD IsDayOffCreditManual BIT NOT NULL CONSTRAINT DF_Dienstplan_IsDayOffCreditManual DEFAULT 0
END
GO
--#endregion

--20160620
--#region ContractTrackingFlowsheets
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.ContractTrackingFlowsheets') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.ContractTrackingFlowsheets (
			ContractTrackingFlowsheetId INT IDENTITY(1,1) NOT NULL,--ID
			Name VARCHAR(50) NOT NULL,
			Module INT NOT NULL,
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL
			CONSTRAINT PK_ContractTrackingFlowsheets PRIMARY KEY CLUSTERED (ContractTrackingFlowsheetId ASC) ON [PRIMARY],
	) ON [PRIMARY]
	CREATE UNIQUE NONCLUSTERED INDEX IX_ContractTrackingFlowsheets_Module_Name ON dbo.ContractTrackingFlowsheets (Module, Name) ON [PRIMARY]
END
GO
GRANT SELECT ON ContractTrackingFlowsheets TO public
GRANT INSERT, UPDATE, DELETE ON ContractTrackingFlowsheets TO BallettAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingFlowsheets TO ChorAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingFlowsheets TO DispoAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingFlowsheets TO OrchesterAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingFlowsheets TO PersoAdmin

GRANT INSERT, UPDATE, DELETE ON ContractTrackingWorkflow TO BallettAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingWorkflow TO ChorAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingWorkflow TO DispoAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingWorkflow TO OrchesterAdmin
GRANT INSERT, UPDATE, DELETE ON ContractTrackingWorkflow TO PersoAdmin
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ContractTrackingFlowsheetId' AND o.id = OBJECT_ID(N'dbo.VertragsVorlagen'))
BEGIN
	INSERT INTO ContractTrackingFlowsheets (Name,Module,LC,UC,LM,UM) VALUES ('Default',1,GETDATE(),-1,GETDATE(),-1)
	ALTER TABLE VertragsVorlagen ADD ContractTrackingFlowsheetId INT  NULL

	EXEC ('UPDATE VertragsVorlagen SET ContractTrackingFlowsheetId = 1 WHERE VertragsVorlagen.Module = 1 AND VArt <> 255')
	ALTER TABLE ContractTrackingWorkflow ADD ContractTrackingFlowsheetId INT  NULL
	EXEC ('UPDATE ContractTrackingWorkflow SET ContractTrackingFlowsheetId = 1')
	ALTER TABLE ContractTrackingWorkflow ALTER COLUMN ContractTrackingFlowsheetId INT NOT NULL
	
	ALTER TABLE dbo.ContractTrackingWorkflow
	WITH CHECK ADD CONSTRAINT FK_ContractTrackingWorkflow_ContractTrackingFlowsheets FOREIGN KEY (ContractTrackingFlowsheetId) REFERENCES dbo.ContractTrackingFlowsheets(ContractTrackingFlowsheetId) 

	ALTER TABLE dbo.VertragsVorlagen
	WITH CHECK ADD CONSTRAINT FK_ContractTypes_ContractTrackingFlowsheets FOREIGN KEY (ContractTrackingFlowsheetId) REFERENCES dbo.ContractTrackingFlowsheets(ContractTrackingFlowsheetId) 
END
GO
--#endregion

--20160622 bm
--#region Berechtigungen für TourInventoryItems
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbTours_InventoryItems'))
	DROP VIEW dbo.AbTours_InventoryItems
GO
CREATE VIEW dbo.AbTours_InventoryItems
AS
	SELECT * FROM Tours_InventoryItems WHERE TourId IN (SELECT TourId FROM AbTours)
GO
GRANT SELECT ON AbTours_InventoryItems TO PUBLIC
GO

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

GO
CREATE VIEW dbo.AbTours_InventoryItems_Update
AS
	SELECT * FROM Tours_InventoryItems WHERE TourId IN (SELECT TourId FROM AbTours_Update)
	WITH CHECK OPTION
GO
GRANT SELECT, INSERT, DELETE, UPDATE ON AbTours_InventoryItems_Update TO PUBLIC
GO
--#endregion

--2016-06-17 dh
-- Neue Indizes auf Namen und Bezeichnungen, die nicht doppelt vorkommen sollten
--#region PrepareUniqueColumnIndex
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.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 Unique Index für Namen und Bezeichnungen
IF EXISTS (SELECT zyklusBEZ,COUNT(ZyklusBez) FROM dbo.zyklen GROUP BY ZyklusBez HAVING COUNT(zyklusBez)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'Zyklen',N'ZyklusBez',N'ZyklusNr',N'nvarchar(10)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.Zyklen')
		AND o.NAME = 'UQ_Zyklen_ZyklusBez'
	)
BEGIN	
	CREATE UNIQUE NONCLUSTERED INDEX UQ_Zyklen_ZyklusBez ON dbo.Zyklen (ZyklusBez)
END
GO
IF EXISTS (SELECT NAME,COUNT(NAME) FROM dbo.YearCloseTypes GROUP BY NAME HAVING COUNT(NAME)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'YearCloseTypes',N'NAME',N'YearCloseTypeId',N'varchar(50)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.YearCloseTypes')
		AND o.NAME = 'UQ_YearCloseTypes_Name'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_YearCloseTypes_Name ON dbo.YearCloseTypes (NAME)
END
GO
IF EXISTS (SELECT Bezeichnung,COUNT(Bezeichnung) FROM dbo.Urlaubsberechnungsmodell GROUP BY Bezeichnung HAVING COUNT(Bezeichnung)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'Urlaubsberechnungsmodell',N'Bezeichnung',N'UrlaubsberechnungsModellID',N'varchar(20)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.Urlaubsberechnungsmodell')
		AND o.NAME = 'UQ_Urlaubsberechnungsmodell_Bezeichnung'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_Urlaubsberechnungsmodell_Bezeichnung ON dbo.Urlaubsberechnungsmodell (Bezeichnung)
END
GO

UPDATE TravelStepTypes
SET NAME = convert(VARCHAR(50), TravelStepTypeId) + '##NAME##'
WHERE NAME IS NULL
go
IF EXISTS (
	SELECT *
	FROM       sysColumns c
	INNER JOIN sysobjects o ON c.id = o.id
	WHERE c.NAME = 'NAME'
		AND o.id = OBJECT_ID(N'[dbo].[TravelStepTypes]')
		AND c.isnullable = 0
	)
BEGIN
	ALTER TABLE TravelStepTypes
	ALTER COLUMN NAME NVARCHAR(50) NOT NULL
END
GO
IF EXISTS (SELECT NAME,COUNT(NAME) FROM dbo.TravelStepTypes GROUP BY NAME HAVING COUNT(NAME)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'TravelStepTypes',N'NAME',N'TravelStepTypeId',N'varchar(50)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.travelsteptypes')
		AND o.NAME = 'UQ_TravelStepTypes_Name'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_TravelStepTypes_NAME ON dbo.TravelStepTypes (NAME)
END
GO
IF EXISTS (SELECT NAME,COUNT(NAME) FROM dbo.travelstepTemplates GROUP BY NAME HAVING COUNT(NAME)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'travelstepTemplates',N'NAME',N'TravelStepTypeId',N'varchar(50)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.travelstepTemplates')
		AND o.NAME = 'UQ_TravelstepTemplates_Name'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_TravelstepTemplates_Name ON dbo.travelstepTemplates (NAME)
END
GO
--# Index wird nicht mehr gebraucht.
IF EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.Tours')
		AND o.NAME = 'UQ_Tours_Title'
	)
BEGIN
	DROP INDEX UQ_Tours_Title ON dbo.Tours 
END
GO
IF EXISTS (SELECT SpartenBez,COUNT(SpartenBez) FROM dbo.Sparte GROUP BY SpartenBez HAVING COUNT(SpartenBez)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'Sparte',N'SpartenBez',N'SparteId',N'varchar(32)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.Sparte')
		AND o.NAME = 'UQ_Sparte_SpartenBez'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_Sparte_SpartenBez ON dbo.Sparte (SpartenBez)
END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.SeatGroups')
		AND o.NAME = 'UQ_SeatGroups_NameSeatingplan'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_SeatGroups_NameSeatingplan ON dbo.SeatGroups (NAME,SeatingplanId)
END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.Rapport')
		AND o.NAME = 'UQ_Rapport_BezModule'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_Rapport_BezModule ON dbo.Rapport (
	Bez
	,Module
	)
END
GO
IF EXISTS (SELECT Bezeichnung,COUNT(Bezeichnung) FROM dbo.PersZusatzDatenTyp GROUP BY Bezeichnung HAVING COUNT(Bezeichnung)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'PersZusatzDatenTyp',N'Bezeichnung',N'PersZDTypID',N'varchar(30)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.PersZusatzDatenTyp')
		AND o.NAME = 'UQ_PersZusatzDatenTyp_Bezeichnung'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_PersZusatzDatenTyp_Bezeichnung ON dbo.PersZusatzDatenTyp (Bezeichnung)
END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.PersoParameter')
		AND o.NAME = 'UQ_PersoParameter_AZNameModule'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_PersoParameter_AZNameModule ON dbo.PersoParameter (
	AZName
	,Module
	)
END
GO
IF EXISTS (SELECT Bezeichnung,COUNT(Bezeichnung) FROM dbo.PersKomTyp GROUP BY Bezeichnung HAVING COUNT(Bezeichnung)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'PersKomTyp',N'Bezeichnung',N'PersKTypID',N'varchar(50)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.PersKomTyp')
		AND o.NAME = 'UQ_PersKomTyp_Bezeichnung'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_PersKomTyp_Bezeichnung ON dbo.PersKomTyp (Bezeichnung)
END
GO
IF EXISTS (SELECT KostenBez,COUNT(KostenBez) FROM dbo.Kosten GROUP BY KostenBez HAVING COUNT(KostenBez)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'Kosten',N'KostenBez',N'KostenID',N'varchar(50)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.Kosten')
		AND o.NAME = 'UQ_Kosten_KostenBez'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_Kosten_KostenBez ON dbo.Kosten (KostenBez)
END
GO
IF EXISTS (SELECT Name,COUNT(Name) FROM dbo.HolidayBalanceSet GROUP BY Name HAVING COUNT(Name)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'HolidayBalanceSet',N'Name',N'HolidayBalanceSetID',N'varchar(16)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.HolidayBalanceSet')
		AND o.NAME = 'UQ_HolidayBalanceSet_Name'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_HolidayBalanceSet_Name ON dbo.HolidayBalanceSet (NAME)
END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.ABGruppe')
		AND o.NAME = 'UQ_ABGruppe_BezeichnungModule'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_ABGruppe_BezeichnungModule ON dbo.ABGruppe (
	Bezeichnung
	,Module
	)
END
GO
IF EXISTS (SELECT Name,COUNT(Name) FROM dbo.CastStatus GROUP BY Name HAVING COUNT(Name)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'CastStatus',N'Name',N'CastStatusId',N'varchar(25)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.CastStatus')
		AND o.NAME = 'UQ_CastStatus_Name'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_CastStatus_Name ON dbo.CastStatus (NAME)
END
GO
IF EXISTS (SELECT CategoryName,COUNT(CategoryName) FROM dbo.ContractFieldTypes GROUP BY CategoryName HAVING COUNT(CategoryName)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'ContractFieldTypes',N'CategoryName',N'ContractFieldTypeId',N'varchar(100)';
	END
GO
IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.ContractFieldTypes')
		AND o.NAME = 'UQ_ContractFieldTypes_CategoryName' 
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_ContractFieldTypes_CategoryName ON dbo.ContractFieldTypes (CategoryName)
END
GO

IF EXISTS (SELECT CategoryCode,COUNT(CategoryCode) FROM dbo.ContractFieldTypes GROUP BY CategoryCode HAVING COUNT(CategoryCode)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'ContractFieldTypes',N'CategoryCode',N'ContractFieldTypeId',N'varchar(20)';
	END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.ContractFieldTypes')
		AND o.NAME = 'UQ_ContractFieldTypes_CategoryCode'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_ContractFieldTypes_CategoryCode ON dbo.ContractFieldTypes (CategoryCode)
END

IF EXISTS (SELECT AllocationGroupDescription,COUNT(AllocationGroupDescription) FROM dbo.AllocationGroup GROUP BY AllocationGroupDescription HAVING COUNT(AllocationGroupDescription)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'AllocationGroup',N'AllocationGroupDescription',N'AllocationGroupId',N'varchar(30)';
	END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.AllocationGroup')
		AND o.NAME = 'UQ_AllocationGroup_AllocationGroupDescription'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_AllocationGroup_AllocationGroupDescription ON dbo.AllocationGroup (AllocationGroupDescription)
END

--Schichten
IF EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.AZTypen')
		AND o.NAME = 'UQ_AZTypen_AZTBez'
	)
BEGIN
	DROP INDEX UQ_AZTypen_AZTBez ON AZTypen;
END
GO
IF EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.AZTypen')
		AND o.NAME = 'UQ_AZTypen_AZTBezInDienstplan'
	)
BEGIN
	DROP INDEX  UQ_AZTypen_AZTBezInDienstplan ON dbo.AZTypen 
END

IF EXISTS (SELECT NAme,COUNT(NAme) FROM dbo.CastCreditTypes GROUP BY NAme HAVING COUNT(NAme)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'CastCreditTypes',N'NAme',N'CastCreditTypeId',N'varchar(50)';
	END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.CastCreditTypes')
		AND o.NAME = 'UQ_CastCreditTypes_NAme'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_CastCreditTypes_NAme ON dbo.CastCreditTypes (NAme)
END

IF EXISTS (SELECT Bezeichung,COUNT(Bezeichung) FROM dbo.CountDownArt GROUP BY Bezeichung HAVING COUNT(Bezeichung)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'CountDownArt',N'Bezeichung',N'CountDownArtId',N'varchar(100)';
	END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.CountDownArt')
		AND o.NAME = 'UQ_CountDownArt_Bezeichung'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_CountDownArt_Bezeichung ON dbo.CountDownArt (Bezeichung)
END


IF EXISTS (SELECT Bezeichnung,COUNT(Bezeichnung) FROM dbo.DienstplanEintragArt GROUP BY Bezeichnung HAVING COUNT(Bezeichnung)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'DienstplanEintragArt',N'Bezeichnung',N'DienstplanEintragArtId',N'varchar(20)';
	END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.DienstplanEintragArt')
		AND o.NAME = 'UQ_DienstplanEintragArt_Bezeichung'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_DienstplanEintragArt_Bezeichung ON dbo.DienstplanEintragArt (Bezeichnung)
END
IF EXISTS (SELECT DienstplanGruppenBez,COUNT(DienstplanGruppenBez) FROM dbo.DienstplanGruppen GROUP BY DienstplanGruppenBez HAVING COUNT(DienstplanGruppenBez)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'DienstplanGruppen',N'DienstplanGruppenBez',N'DienstplanGruppenID',N'varchar(20)';
	END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.DienstplanGruppen')
		AND o.NAME = 'UQ_DienstplanGruppen_DienstplanGruppenBez'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_DienstplanGruppen_DienstplanGruppenBez ON dbo.DienstplanGruppen (DienstplanGruppenBez)
END

IF EXISTS (SELECT Name,COUNT(Name) FROM dbo.ExportProfiles GROUP BY Name HAVING COUNT(Name)>1)
	BEGIN
	EXECUTE PrepareUniqueColumnIndex N'ExportProfiles',N'Name',N'ExportProfileId',N'varchar(50)';
	END
GO

IF NOT EXISTS (
	SELECT *
	FROM sysindexes o
	WHERE o.id = OBJECT_ID(N'dbo.ExportProfiles')
		AND o.NAME = 'UQ_ExportProfiles_Name'
	)
BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX UQ_ExportProfiles_Name ON dbo.ExportProfiles (Name)
END
--#endregion

--#region Persstatuscontract
--20160704 dh Zuordnung von Verträgen zu Abwesenheiten.
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ContractId' and o.id = OBJECT_ID(N'[dbo].[StatusZeitR]'))
BEGIN
	ALTER TABLE dbo.StatusZeitR ADD ContractId INT NULL 
END
GO

--#endregion

--20160706 bm	
--#region XML-Settings für Dienstplaneinträge
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'BreakCorridorSettings' AND o.id = OBJECT_ID(N'dbo.Dienstplan'))
BEGIN
	DECLARE @serverVersion INT
	SELECT @serverVersion = CONVERT(INTEGER, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))
		IF @serverVersion > 10 
	
		BEGIN
			EXEC ('ALTER TABLE Dienstplan ADD BreakCorridorSettings VARCHAR(MAX) SPARSE NULL')
		END
	ELSE 
		BEGIN
			ALTER TABLE Dienstplan ADD BreakCorridorSettings VARCHAR(MAX) NULL
		END
END
GO
--#endregion

--20160726 bm
--#region Perso-Berechtigung, um eigene Soll-Zeiten zu schreiben
if NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = 'PersoWriteOwnRegularRoster')
BEGIN
	EXEC sp_addrole @rolename = 'PersoWriteOwnRegularRoster'
END
GO
--#endregion

--TK20160812
--#region PersonId in Travels einige Spalten korrekt einstellen bzgl. NULL
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'PersonId' and o.id = OBJECT_ID(N'dbo.Travels'))
BEGIN
	ALTER TABLE dbo.Travels ADD PersonId INT NULL 
	EXEC ('UPDATE  Travels SET PersonId = Persnr FROM Travels INNER JOIN Vertrag ON Travels.ContractId = Vertrag.VertragsNr')
	ALTER TABLE dbo.Travels ALTER COLUMN PersonId INT NOT NULL 
	ALTER TABLE dbo.Travels ALTER COLUMN ContractId INT NULL 

	CREATE NONCLUSTERED INDEX IX_Travels_Contract ON dbo.Travels (ContractId) ON [PRIMARY]
	EXEC ('CREATE NONCLUSTERED INDEX IX_Travels_Person ON dbo.Travels (PersonId) ON [PRIMARY]')

	DELETE FROM dbo.TravelSteps WHERE [Start] IS NULL 
	ALTER TABLE dbo.TravelSteps ALTER COLUMN [Start] DATETIME NOT NULL
END
GO
--#endregion

--TK20160815
--#region Index auf Abteilungszulagen und Druckname für Dienstplanzeitraumstatus
IF NOT EXISTS (SELECT * FROM  sysindexes o WHERE  o.id = OBJECT_ID(N'dbo.ZulagenDienstplanArt') AND o.Name = 'IX_Department_Bonus')
BEGIN
--Doppelte Zuordnungen löschen
	DELETE FROM ZulagenDienstplanArt WHERE ZulagenDienstplanArtId IN (SELECT Max(ZulagenDienstplanArtId) FROM ZulagenDienstplanArt GROUP BY DienstplanArtenId,ZulagenNr HAVING Count(*) > 1)
	DELETE FROM ZulagenDienstplanArt WHERE ZulagenDienstplanArtId IN (SELECT Max(ZulagenDienstplanArtId) FROM ZulagenDienstplanArt GROUP BY DienstplanArtenId,ZulagenNr HAVING Count(*) > 1)
	DELETE FROM ZulagenDienstplanArt WHERE ZulagenDienstplanArtId IN (SELECT Max(ZulagenDienstplanArtId) FROM ZulagenDienstplanArt GROUP BY DienstplanArtenId,ZulagenNr HAVING Count(*) > 1)
	CREATE UNIQUE NONCLUSTERED INDEX IX_Department_Bonus ON dbo.ZulagenDienstplanArt (DienstplanArtenId,ZulagenNr ) ON [PRIMARY]
	
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'PrintCaption' and o.id = OBJECT_ID(N'dbo.DutyRosterPeriodStatus'))
BEGIN
	ALTER TABLE dbo.DutyRosterPeriodStatus ADD PrintCaption VARCHAR(50)
END
GO
--#endregion

--#region DeleteUsagesOfUserModified
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DeleteUsagesOfUserModified'))
	DROP PROCEDURE dbo.DeleteUsagesOfUserModified 
GO
CREATE PROCEDURE dbo.DeleteUsagesOfUserModified
 @Userid   INT AS 
BEGIN
	SET NOCOUNT ON
	DECLARE @table VARCHAR(250), @field VARCHAR(2)
	DECLARE fields CURSOR FOR 
		SELECT o.name, c.name FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
		WHERE (c.name = 'UC' OR c.name = 'UM' ) AND o.xtype = 'U' AND  c.xtype = 56  

	OPEN fields

	FETCH NEXT FROM fields

	INTO  @table, @field
	WHILE @@FETCH_STATUS = 0
		BEGIN

			EXEC ('UPDATE [' + @table  + '] SET [' + @field + ']=-1 WHERE [' + @field + ']='+ @Userid ) 

			FETCH NEXT FROM fields INTO  @table, @field
	
		END
		
	CLOSE fields
	DEALLOCATE fields
END
GO

GRANT EXECUTE ON dbo.DeleteUsagesOfUserModified TO SecurityAdmin
GO
--#endregion

--#region FindUsagesOfUserModified
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].FindUsagesOfUserModified'))
	DROP PROCEDURE dbo.FindUsagesOfUserModified 
GO
CREATE PROCEDURE dbo.FindUsagesOfUserModified
 @Userid INT 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 1

	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

GRANT EXECUTE ON dbo.FindUsagesOfUserModified TO SecurityAdmin
GO
--#endregion

--2016-08-30 SH 
--#region Einstellungen von Vorlagen auf NVARCHAR(MAX) umstellen
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'TemplateParameterData' AND o.id = OBJECT_ID(N'[dbo].Vorlagen') AND c.xtype = 167 and c.[length]<>-1)
BEGIN
	ALTER TABLE Vorlagen ALTER COLUMN TemplateParameterData nvarchar(MAX) NULL
END
GO
--#endregion

--2016-09-01 sh
--#region Buß- und Bettag korrigieren
IF EXISTS (SELECT * FROM Feiertage f WHERE f.FDatum = '20171115')
AND NOT EXISTS (SELECT * FROM Feiertage f WHERE f.FDatum = '20171122')
BEGIN
	UPDATE Feiertage SET FDatum = '20171122' WHERE FDatum = '20171115'
END
GO
IF EXISTS (SELECT * FROM Feiertage f WHERE f.FDatum = '20231115')
AND NOT EXISTS (SELECT * FROM Feiertage f WHERE f.FDatum = '20231122')
BEGIN
	UPDATE Feiertage SET FDatum = '20231122' WHERE FDatum = '20231115'
END
GO
--#endregion

--2016-09-01 sh
--#region Reformationstag für 2017 für alle deutschen Bundesländer anlegen
IF EXISTS (SELECT * FROM Settings s WHERE s.Form = 'Adresse' AND s.Control = 'Country' AND s.WertText = 'D')
AND NOT EXISTS (SELECT * FROM Feiertage f WHERE f.FDatum = '20171031')
BEGIN
	INSERT INTO Feiertage (FDatum, FtBez, LM, UM, LC, UC, Ftart)
	VALUES ('20171031', 'Reformationstag', GETDATE(), 0, GETDATE(), 0, 3);
END
GO
--#endregion

--2016-09-06 sh
--#region Pref mit Leerstring in Globalen Settings korrigieren
UPDATE Settings SET Pref = NULL WHERE IsGlobalSetting = 1 AND Pref = ''
--#endregion

--tk 2016-08-24
--#region PayOutRule in RapportSpalte hinzufügen
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'PayOutRule' AND o.id = OBJECT_ID(N'dbo.RapportSpalte'))
BEGIN
	ALTER TABLE RapportSpalte ADD PayOutRule VARCHAR(MAX) NULL
	EXEC ('UPDATE RapportSpalte SET PayOutRule = ''<?xml version="1.0" encoding="utf-8"?>
<PayOutRule xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PayOutValuesFrom>0</PayOutValuesFrom>
  <RuleBaseddOnAccountSum>false</RuleBaseddOnAccountSum>
</PayOutRule>'' WHERE PayOutMonthValue = 1')
END
GO
--#endregion

--TK 20160908
--#region UpdateLog um TransactionId erweitert
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'PKValue' AND o.id = OBJECT_ID(N'dbo.Updatelog'))
BEGIN

	--ALTER TABLE Updatelog ALTER COLUMN LogText VARCHAR(MAX) NOT NULL
	--ALTER TABLE Updatelog ALTER COLUMN WinUser VARCHAR(50) NOT NULL
	--ALTER TABLE Updatelog ALTER COLUMN LogTable VARCHAR(50) NOT NULL
	--ALTER TABLE Updatelog ALTER COLUMN LogArt TINYINT NOT NULL
	--ALTER TABLE Updatelog ALTER COLUMN UC INT NOT NULL
	--ALTER TABLE Updatelog ALTER COLUMN LC DATETIME NOT NULL

	ALTER TABLE Updatelog ADD PKValue INT
	ALTER TABLE Updatelog ADD PKField VARCHAR(50) NULL
	ALTER TABLE Updatelog ADD TransactionId INT NULL
	EXEC ('CREATE NONCLUSTERED INDEX IX_Updatelog_TransactionId ON dbo.Updatelog (TransactionId) ON [PRIMARY]')

END
GO
--#endregion

--Script zum umschreiben der Daten. 
--DELETE FROM Updatelog WHERE CHARINDEX(CHAR(13), LogText) = 0 
--UPDATE Updatelog SET 
--	PKValue =  CONVERT(INT, RIGHT(  LEFT(LogText, CHARINDEX(CHAR(13), LogText)-1)   , LEN(  LEFT(LogText, CHARINDEX(CHAR(13), LogText)-1)   )- CHARINDEX('=',   LEFT(LogText, CHARINDEX(CHAR(13), LogText)-1)   ))) , 
--	LogText =  RIGHT(LogText, LEN(LogText)- LEN(  LEFT(LogText, CHARINDEX(CHAR(13), LogText)-1)   ) - 2) ,
--	PKField = LEFT(  LEFT(LogText, CHARINDEX(CHAR(13), LogText)-1)   ,  CHARINDEX('=',   LEFT(LogText, CHARINDEX(CHAR(13), LogText)-1)   )-1) 
--	WHERE PKValue IS NULL

--#region AbUpdatelog
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbUpdatelog'))
	DROP VIEW dbo.AbUpdatelog

GO
CREATE VIEW dbo.AbUpdatelog
AS 
	SELECT  Updatelog.*
	FROM  	Updatelog INNER JOIN UserPW ON Updatelog.UC = UserPW.UserId
	WHERE	Username = CURRENT_USER 
GO

GRANT SELECT  ON AbUpdatelog TO public
--#endregion

--bm20160913
--#region BestMatchingContract-Stuff WI#9857
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastWithContractPermission'))
	DROP PROCEDURE dbo.GetCastWithContractPermission
GO
CREATE PROCEDURE GetCastWithContractPermission 
	@startDate DATE = NULL, 
	@endDate DATE = NULL,
	@persNrList VARCHAR(MAX) = NULL,
	@groupIdList VARCHAR(50) = NULL,
	@include INT = NULL
AS
BEGIN
	SELECT c.* INTO #cast FROM BesetzungTabelle AS c
	INNER JOIN BesetzungStd	AS r ON c.RolleId = r.ID
	INNER JOIN AufführgTabelle AS a ON c.AuffNr = a.AuffNr
	INNER JOIN UserGruppe AS Ug ON r.Besitzer IN (ISNULL(@groupIdList, r.Besitzer))   
	INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
	WHERE ug.BerechtigungsbereichID = 1 /*Es soll der Berechtigungsbereich 1 (Vertragsdaten) verwendet werden*/
	AND ug.Schreiben = 1
	AND UserPW.Username = CURRENT_USER
	AND c.PersNr IN (ISNULL(@persNrList, c.PersNr))
	AND a.Datum >= ISNULL(@startDate, a.Datum)
	AND a.Datum <= ISNULL(@endDate, a.Datum)

	SELECT * FROM #Cast

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

	IF (@include&1 = 1) 
		SELECT NULL AS [#1], cb.* 
		FROM CastBonus AS cb
		INNER JOIN #Cast ON cb.CastId = #Cast.ID
	IF (@include&2 = 2) 
		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.ID
	IF (@include&4 = 4)
		SELECT NULL AS [#4], a.*
		FROM AufführgTabelle AS a 
		WHERE EXISTS (SELECT AuffNr FROM #Cast AS c WHERE c.AuffNr = a.AuffNr)
	IF (@include&8 = 8)
		SELECT NULL AS [#8], v.*
		FROM Vertrag AS v
		WHERE EXISTS (SELECT ContractId FROM #Cast AS c WHERE c.ContractId = v.VertragsNr)
	IF (@include&16 = 16)
		SELECT NULL AS [#16], r.*
		FROM BesetzungStd AS r 
		WHERE EXISTS (SELECT RolleID FROM #Cast AS c WHERE c.RolleID = r.ID)
	IF (@include&32 = 32)
		SELECT NULL AS [#32], p.*
		FROM PersH AS p 
		WHERE EXISTS (SELECT PersNr FROM #Cast AS c WHERE c.PersNr = p.PersNr)
	IF (@include&96 = 96)
		SELECT NULL AS [#96], v.*
		FROM Vertrag AS v
		WHERE EXISTS (SELECT PersNr FROM #Cast AS c WHERE c.ContractId = v.VertragsNr)
	IF (@include&224 = 224)
		SELECT NULL AS [#224], vr.*
		FROM VertragRolle AS vr
		INNER JOIN Vertrag AS v ON vr.VertragsNr = v.VertragsNr
		INNER JOIN BesetzungStd AS r ON vr.RolleId = r.ID
		WHERE EXISTS (SELECT v.PersNr FROM #Cast AS c WHERE c.PersNr = v.PersNr)
END
GO

GRANT EXECUTE ON dbo.GetCastWithContractPermission TO PUBLIC
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetCastAsDbo'))
	DROP PROCEDURE dbo.GetCastAsDbo
GO
CREATE PROCEDURE GetCastAsDbo 
	@include INT = NULL
AS
BEGIN
	SELECT * FROM BesetzungTabelle

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

	IF (@include&1 = 1) 
		SELECT NULL AS [#1], cb.* 
		FROM CastBonus AS cb
		INNER JOIN BesetzungTabelle ON cb.CastId = BesetzungTabelle.ID
	IF (@include&2 = 2) 
		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 BesetzungTabelle ON cd.CastId = BesetzungTabelle.ID
	IF (@include&4 = 4)
		SELECT NULL AS [#4], a.*
		FROM AufführgTabelle AS a 
		WHERE EXISTS (SELECT AuffNr FROM BesetzungTabelle AS c WHERE c.AuffNr = a.AuffNr)
	IF (@include&8 = 8)
		SELECT NULL AS [#8], v.*
		FROM Vertrag AS v
		WHERE EXISTS (SELECT ContractId FROM BesetzungTabelle AS c WHERE c.ContractId = v.VertragsNr)
	IF (@include&16 = 16)
		SELECT NULL AS [#16], r.*
		FROM BesetzungStd AS r 
		WHERE EXISTS (SELECT RolleID FROM BesetzungTabelle AS c WHERE c.RolleID = r.ID)
	IF (@include&32 = 32)
		SELECT NULL AS [#32], p.*
		FROM PersH AS p 
		WHERE EXISTS (SELECT PersNr FROM BesetzungTabelle AS c WHERE c.PersNr = p.PersNr)
	IF (@include&96 = 96)
		SELECT NULL AS [#96], v.*
		FROM Vertrag AS v
		WHERE EXISTS (SELECT PersNr FROM BesetzungTabelle AS c WHERE c.ContractId = v.VertragsNr)
	IF (@include&224 = 224)
		SELECT NULL AS [#224], vr.*
		FROM VertragRolle AS vr
		INNER JOIN Vertrag AS v ON vr.VertragsNr = v.VertragsNr
		INNER JOIN BesetzungStd AS r ON vr.RolleId = r.ID
		WHERE EXISTS (SELECT v.PersNr FROM BesetzungTabelle AS c WHERE c.PersNr = v.PersNr)
END
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.UpdateCastWithContractPermission'))
	DROP PROCEDURE dbo.UpdateCastWithContractPermission
GO
CREATE PROCEDURE UpdateCastWithContractPermission 
	@castId INT,
	@contractId INT
AS
BEGIN
	UPDATE c SET c.ContractId = @contractId FROM BesetzungTabelle AS c
	INNER JOIN BesetzungStd	AS r ON c.RolleId = r.ID
	INNER JOIN UserGruppe AS Ug ON r.Besitzer = ug.GruppeID   
	INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
	WHERE @castId = c.ID 
	AND ug.BerechtigungsbereichID = 1 /*Es soll der Berechtigungsbereich 1 (Vertragsdaten) verwendet werden*/
	AND ug.Schreiben = 1 
	AND UserPW.Username = CURRENT_USER
END
GO

GRANT EXECUTE ON dbo.UpdateCastWithContractPermission TO PUBLIC
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.UpdateCastAsDbo'))
	DROP PROCEDURE dbo.UpdateCastAsDbo
GO
CREATE PROCEDURE UpdateCastAsDbo 
	@castId INT,
	@contractId INT
AS
BEGIN
	UPDATE c SET c.ContractId = @contractId FROM BesetzungTabelle AS c
	INNER JOIN BesetzungStd	AS r ON c.RolleId = r.ID
	INNER JOIN UserGruppe AS Ug ON r.Besitzer = ug.GruppeID   
	INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
	WHERE @castId = c.ID
END
GO
--#endregion


--TK20160921
--#region ProductionsWorkVersions
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.ProductionsWorkVersions') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.ProductionsWorkVersions (
			ProductionsWorkVersionId INT IDENTITY(1,1) NOT NULL,
			WorkVersionId INT NOT NULL,
			ProductionId INT NOT NULL,
			OrderNumber INT NULL DEFAULT(0),
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL
			CONSTRAINT PK_ProductionsWorkVersions PRIMARY KEY CLUSTERED (ProductionsWorkVersionId ASC) ON [PRIMARY],
	) ON [PRIMARY]
	CREATE UNIQUE NONCLUSTERED INDEX IX_WorkVersionId_ProductionId ON dbo.ProductionsWorkVersions (WorkVersionId, ProductionId) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX IX_WorkVersionIdd ON dbo.ProductionsWorkVersions (WorkVersionId) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX IX_ProductionId ON dbo.ProductionsWorkVersions (ProductionId) ON [PRIMARY]

	ALTER TABLE dbo.ProductionsWorkVersions  WITH CHECK ADD  CONSTRAINT FK_ProductionsWorkVersions_WorkVersions FOREIGN KEY(WorkVersionId)
	REFERENCES dbo.WorkVersions (WorkVersionId) ON DELETE CASCADE

	ALTER TABLE dbo.ProductionsWorkVersions  WITH CHECK ADD  CONSTRAINT FK_ProductionsWorkVersions_Productions FOREIGN KEY(ProductionId)
	REFERENCES dbo.Stücke (SNR) ON DELETE CASCADE
	EXEC('INSERT INTO ProductionsWorkVersions (WorkVersionId,ProductionId,LC,UC,LM,UM ) SELECT WorkVersionId, Snr, ISNULL(lc,GETDATE()),ISNULL(UC,-1),ISNULL(lc,GETDATE()),ISNULL(UC,-1)  FROM Stücke WHERE NOT WorkVersionId IS NULL')
	ALTER TABLE Stücke drop constraint FK_Productions_WorkVersions2
	ALTER TABLE Stücke DROP COLUMN WorkVersionId
	DROP PROCEDURE SetWorkVersionReferenceToProduction
	DROP PROCEDURE RemoveWorkVersionReference
END
GO
GRANT SELECT ON ProductionsWorkVersions TO public
GRANT INSERT, DELETE, UPDATE ON ProductionsWorkVersions TO WorksWrite
GO
--#endregion

--dh20160927
--#region NVData um Spalte erweitern und Primärkey neu setzen
--Neue Spalte und neuer Primärschlüssel für NVData
IF exists (SELECT name  
	FROM sys.key_constraints  
	WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'NVData' AND Name= N'PK_NVData') 
		BEGIN
			ALTER TABLE NVData ADD NVDataId INT IDENTITY(1,1) NOT NULL;
			ALTER TABLE NVData  
			DROP CONSTRAINT PK_NVData;
			ALTER TABLE NVData ADD Constraint PK_NVData_NVDataId PRIMARY KEY CLUSTERED(NVDataId);   
		END
--#endregion

--20160926 bm
--#region Kontaktkalender "nur Vst. anzeigen" durch Aktivitätstyp-Filter austauschen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 165))))
BEGIN
		UPDATE Settings SET WertText = NULL
		WHERE [Form] = 'PersonCalender'
		AND [Control] = 'Settings'
		AND WertText LIKE '%ActivityTypeNameList%'

		UPDATE Settings SET WertText = REPLACE([WertText], '<ShowOnlyPerformances>true</ShowOnlyPerformances>', '<ActivityTypeNameList><string>public</string></ActivityTypeNameList>')
		WHERE [Form] = 'PersonCalender'
		AND [Control] = 'Settings'
		AND WertText LIKE '%<ShowOnlyPerformances>true</ShowOnlyPerformances>%'

		UPDATE Settings SET WertText = REPLACE([WertText], '<ShowOnlyPerformances>false</ShowOnlyPerformances>', '')
		WHERE [Form] = 'PersonCalender'
		AND [Control] = 'Settings'
		AND WertText LIKE '%<ShowOnlyPerformances>false</ShowOnlyPerformances>%'
	END
GO
--#endregion

--dh20160927
--#region NVData um Spalte erweitern und Primärkey neu setzen
--Neue Spalte und neuer Primärschlüssel für NVData
IF exists (SELECT name  
	FROM sys.key_constraints  
	WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'NVData' AND Name= N'PK_NVData') 
		BEGIN
			ALTER TABLE NVData ADD NVDataId INT IDENTITY(1,1) NOT NULL;
			ALTER TABLE NVData  
			DROP CONSTRAINT PK_NVData;
			ALTER TABLE NVData ADD Constraint PK_NVData_NVDataId PRIMARY KEY CLUSTERED(NVDataId);   
		END
--#endregion

--20161005 bm
--#region AutoZoom-Einstellung des Personenkalenders in Einstellungsdialog ausgelagert.
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 165))))
BEGIN
		update Settings set WertText = null
		where Form = 'PersonCalender'
		and [Control] = 'Settings'
		AND WertText LIKE '%YearViewSettings%'

		update settings set 
		[WertText] = REPLACE([WertText], '<AutoZoom>false</AutoZoom>', '<YearViewSettings><AutoZoom>false</AutoZoom></YearViewSettings>')
		from Settings
		where Form = 'PersonCalender'
		and [Control] = 'Settings'
		AND WertText LIKE '%<AutoZoom>false</AutoZoom>%'

		update settings set 
		[WertText] = REPLACE([WertText], '<AutoZoom>true</AutoZoom>', '<YearViewSettings><AutoZoom>true</AutoZoom></YearViewSettings>')
		from Settings
		where Form = 'PersonCalender'
		and [Control] = 'Settings'
		AND WertText LIKE '%<AutoZoom>true</AutoZoom>%'

		update settings set 
		[WertText] = REPLACE([WertText], '<AutoZoom />', '')
		from Settings
		where Form = 'PersonCalender'
		and [Control] = 'Settings'
		AND WertText LIKE '%<AutoZoom />%'
	END
GO
--#endregion

--20161011 tk
--#region Regel für Vertragsbonus
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'RuleSettings' AND o.id = OBJECT_ID(N'dbo.ContractBonus'))
BEGIN
	ALTER TABLE ContractBonus ADD RuleSettings VARCHAR(MAX) NULL
END
IF  EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.ContractBonus')  AND name = 'UQ_ContractId_BonusId' )
BEGIN
	ALTER TABLE dbo.ContractBonus DROP CONSTRAINT UQ_ContractId_BonusId
END
GO
--#endregion

--TK 20160916
--#region  Berechtigung zum schreiben von Wochenabschlüssen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 164 OR (Minor = 164 AND Build < 101)))))
BEGIN
	UPDATE UserGruppe SET OptionsFlag =  OptionsFlag + 32
		WHERE BerechtigungsbereichID = 5 AND Schreiben = 1  AND (OptionsFlag&2 = 2)
END
GO
--#endregion

--2016-09-28 sh
--#region Leere Pausenkorridore aus Dienstplan entfernen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 164 OR (Minor = 164 AND Build < 103)))))
BEGIN
	UPDATE Dienstplan SET BreakCorridorSettings = NULL WHERE BreakCorridorSettings = '<?xml version="1.0" encoding="utf-8"?>' + CHAR(13) + CHAR(10) +
	'<DutyRosterEntryBreakCorridorSetting xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />'
END
GO
--#endregion

--2016-09-29 sh
--#region LoadAllCastForEnsembleContractsInReport für Josefstadt setzen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 164 OR (Minor = 164 AND Build < 104)))))
   AND EXISTS(SELECT * FROM settings WHERE [form] = 'Application' AND [control]='License' AND IsGlobalSetting = 1 AND WertText LIKE '%<CustomerId>10112</CustomerId>%')
BEGIN
	IF EXISTS (SELECT * FROM settings WHERE [form] = 'MiscSystemSettings' AND [control]='LoadAllCastForEnsembleContractsInReport' AND IsGlobalSetting = 1)
	BEGIN
    	UPDATE Settings SET WertText = 'True' WHERE [form] = 'MiscSystemSettings' AND [control]='LoadAllCastForEnsembleContractsInReport' AND IsGlobalSetting = 1
    END
	ELSE
	BEGIN
    	INSERT INTO Settings (Form,[Control],WertText,LM,UM,LC,UC,IsGlobalSetting) values('MiscSystemSettings','LoadAllCastForEnsembleContractsInReport','True',GetDate(), -1,GetDate(), -1, 1)
    END
END
--#endregion

--2016-10-07 tk
--#region Eindeutigen Sort index von hdm Spalten entfernen, gleichen Sortids werden nach pk sortiert
IF  EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.HDM')  AND name = 'HDMVorlageIDSort' )
BEGIN
	DROP INDEX HDMVorlageIDSort ON dbo.HDM
END
GO
--#endregion

--2016-10-04 sh
--#region Farben umschreiben auf global, wenn diese noch fehlen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 164 OR (Minor = 164 AND Build < 105)))))
BEGIN
	UPDATE x SET x.IsGlobalSetting = 1 FROM settings x WHERE x.Form = 'TheasoftSystemColors' AND x.IsGlobalSetting = 0 AND NOT EXISTS 
	(SELECT * FROM Settings s WHERE s.Form = 'TheasoftSystemColors' AND s.IsGlobalSetting = 1 AND s.[Control] = x.[Control]) 
END
GO
--#endregion

--2016-10-12 tk
--#region InsertWithIdentityInternal

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.InsertWithIdentityInternal'))	DROP PROCEDURE dbo.InsertWithIdentityInternal  
GO
CREATE PROCEDURE dbo.InsertWithIdentityInternal 
	@Tablename NVARCHAR(254),
	@Fields NVARCHAR(MAX),
	@Values NVARCHAR(MAX),
	@UserId INT
	WITH EXECUTE AS 'dbo'
AS
	BEGIN
		SET NOCOUNT ON
		DECLARE @date NVARCHAR(MAX)
		SELECT  @date = '''' + CONVERT(NVARCHAR(50), GetDate()) + ''''
		EXEC (N'SET IDENTITY_INSERT ['+ @Tablename + N'] ON')
		EXEC (N'INSERT INTO [' + @Tablename + N'] (' + @Fields + N', LC, UC, LM, UM) VALUES  (' + @Values + N', '+  @date + N', ' + @UserId + N', '+  @date + N', ' + @UserId + N')')
		EXEC (N'SET IDENTITY_INSERT ['+ @Tablename + N'] OFF')
	END
GO
IF  EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.InsertWithIdentity'))	DROP PROCEDURE dbo.InsertWithIdentity  
GO
--Procedure zum wirderherstellen von gelöschen Daten (undo). Muss aufgrund von Berechtigunge auf 'IDENTITY_INSERT' in der InsertWithIdentityInternal als dbo ausgefühert werden
CREATE PROCEDURE dbo.InsertWithIdentity
	@Tablename NVARCHAR(254),
	@Fields NVARCHAR(MAX),
	@Values NVARCHAR(MAX)
AS
	BEGIN
		IF EXISTS(SELECT * FROM fn_my_permissions(@Tablename, N'OBJECT')   WHERE subentity_name  = N''  AND Permission_Name = N'INSERT') 
		BEGIN
			SET NOCOUNT ON
			DECLARE @userId INT
			SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
			EXECUTE InsertWithIdentityInternal @Tablename, @Fields, @Values, @userId
		END
	END
	GO
GRANT EXECUTE ON dbo.InsertWithIdentity TO public

--#endregion
REVOKE INSERT, UPDATE, DELETE  ON Role_Season TO PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON ABRole_Season_UPdate TO PUBLIC
REVOKE INSERT, UPDATE, DELETE, SELECT  ON Role_Season TO DispoAdmin
GO

--2016-12-21 sh
--#region [AbPersonAttachments] korrigiert
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbPersonAttachments]'))
	DROP VIEW [dbo].[AbPersonAttachments]
GO

CREATE VIEW [dbo].[AbPersonAttachments]
AS 

SELECT pa.* FROM PersonAttachments pa 
WHERE pa.PublicPermission = 1
OR pa.PublicPermission = 2
OR (
	pa.PublicPermission = 0
	AND pa.OwnerId IN (
		SELECT GruppeId FROM UserGruppe AS UG 
		INNER JOIN UserPw AS U ON U.Userid = UG.UserID
		WHERE U.UserName = CURRENT_USER AND UG.BerechtigungsbereichID = 13
	)
) 
WITH CHECK OPTION
GO

GRANT SELECT ON dbo.AbPersonAttachments TO Public
GO

--#endregion

--#region Standardlänge
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'DefaultNumberOfMonth' and o.id = OBJECT_ID(N'dbo.YearCloseTypes'))
BEGIN
	ALTER TABLE dbo.YearCloseTypes ADD DefaultNumberOfMonth INT NOT NULL DEFAULT(12)
END
GO
--#endregion



--TK 2016-12-21 Unfall hat keine eingenen Einstellungen
UPDATE Zeitkonten SET Formel = 3011 WHERE Formel = 3012
UPDATE Zeitkonten SET Formel = 3000 WHERE Formel = 3015 OR Formel = 3017
GO

--2017-01-16 sh
--#region korrektur der berechtigungen für vertragsfelder
REVOKE INSERT, UPDATE, DELETE ON ContractFields FROM MasterAdmin
GRANT INSERT, UPDATE, DELETE ON ContractFieldTypes TO MasterAdmin
GO
--#endregion

--2017-01-26 tk
--#region In AbExportProfiles_Update konnten kein Deletes ausgeführt werden
ALTER VIEW [dbo].[AbExportProfiles_Update]
AS 
	SELECT 	ep.*
	FROM ExportProfiles AS ep  
	WHERE ep.OwnerGroupId IN 
		   (SELECT GruppeID 
			FROM UserGruppe INNER JOIN UserPW ON UserGruppe.Userid = UserPW.Userid 
			WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 14 AND Schreiben = 1 AND (ep.ExportArea & OptionsFlag) <> 0) 	

GO
--#endregion

--2017-01-26 tk
--#region Aktivitätstypen mit 0 und -1 entfernen
IF EXISTS (SELECT * FROM AuffTypen WHERE AuffTNr = -1) 
BEGIN
	IF EXISTS (SELECT * FROM AufführgTabelle WHERE Art = -1) 
	BEGIN
		INSERT INTO AuffTypen (MitChor,MitOrch,KatNr,AuffTKurz,AuffTBez,Sort,PremSort,WoBez,TagBez,Merkmal,mitBallett,VonzeitDrucken,BiszeitDrucken,ProbenZeitMo,ProbenZeitAb,LM,UM,LC,UC,BackColor,noOverlap,tsAuffTypBasisID,LfdNr,CountDownTyp,ShortName_en,Name_en,ForeColor,DefaultRehearsalDurationMorning,DefaultRehearsalDurationEvening,IsInactive)
		 SELECT MitChor,MitOrch,KatNr,ISNULL(AuffTKurz,'#'),'#'+ISNULL(AuffTBez,'') ,1,PremSort,WoBez,TagBez,Merkmal,mitBallett,VonzeitDrucken,BiszeitDrucken,ProbenZeitMo,ProbenZeitAb,LM,UM,LC,UC,BackColor,noOverlap,tsAuffTypBasisID,LfdNr,CountDownTyp,ShortName_en,Name_en,ForeColor,DefaultRehearsalDurationMorning,DefaultRehearsalDurationEvening,IsInactive FROM Aufftypen WHERE AufftNr = -1
		UPDATE AufführgTabelle SET Art = (SELECT MAX(AufftNR) FROM AuffTypen) WHERE Art = -1		
	END
	DELETE FROM AuffTypen WHERE AufftNr = -1
END
GO

IF EXISTS (SELECT * FROM AuffTypen WHERE AuffTNr = 0) 
BEGIN
	IF EXISTS (SELECT * FROM AufführgTabelle WHERE Art = 0) 
	BEGIN
		INSERT INTO AuffTypen (MitChor,MitOrch,KatNr,AuffTKurz,AuffTBez,Sort,PremSort,WoBez,TagBez,Merkmal,mitBallett,VonzeitDrucken,BiszeitDrucken,ProbenZeitMo,ProbenZeitAb,LM,UM,LC,UC,BackColor,noOverlap,tsAuffTypBasisID,LfdNr,CountDownTyp,ShortName_en,Name_en,ForeColor,DefaultRehearsalDurationMorning,DefaultRehearsalDurationEvening,IsInactive)
		 SELECT MitChor,MitOrch,KatNr,ISNULL(AuffTKurz,'*'),'*'+ISNULL(AuffTBez,'') ,1,PremSort,WoBez,TagBez,Merkmal,mitBallett,VonzeitDrucken,BiszeitDrucken,ProbenZeitMo,ProbenZeitAb,LM,UM,LC,UC,BackColor,noOverlap,tsAuffTypBasisID,LfdNr,CountDownTyp,ShortName_en,Name_en,ForeColor,DefaultRehearsalDurationMorning,DefaultRehearsalDurationEvening,IsInactive FROM Aufftypen WHERE AufftNr = 0
		UPDATE AufführgTabelle SET Art = (SELECT MAX(AufftNR) FROM AuffTypen) WHERE Art = 0	
	END
	DELETE FROM AuffTypen WHERE AufftNr = 0
END
GO
--#endregion

--2017-02-03
--#region Prozedur zum Abfragen von Schichten von Mitarbeitern
IF  EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.ShiftCountingByWorkingTime'))	
	DROP PROCEDURE dbo.ShiftCountingByWorkingTime  
GO
CREATE PROCEDURE dbo.ShiftCountingByWorkingTime
	@departmentId INT,
	@contractIds varchar(MAX),
	@startDate DATE,
	@endDate DATE
AS
	BEGIN
		--IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#xml%') BEGIN
		--   DROP TABLE #xml;
		--END;

		--IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#times%') BEGIN
		--   DROP TABLE #times;
		--END;

		--IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#times2%') BEGIN
		--   DROP TABLE #times2;
		--END;

		--IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#data%') BEGIN
		--   DROP TABLE #data;
		--END;

		SELECT cast([DepartmentWorkingTimeSettings] as xml) as workingTimes Into #xml FROM [DienstplanArtenTabelle] where DienstplanArtenID = @departmentId

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

		declare @latestTime datetime select top 1 @latestTime = zeit from #times order by zeit desc;
		declare @earliestTime datetime 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.VertragsNr, COUNT(1) as Count from #times2 as t
		CROSS JOIN Dienstplan as d
		where  d.DienstplanEintragArtId = 0 AND d.vertragsNr IN (SELECT * FROM SplitIds(@contractIds))
		AND d.datum BETWEEN @startDate AND  @endDate
		AND (d.V1 >= t.aktuell AND d.V1 < t.[next]
			OR d.V2 >= t.aktuell AND d.V2 < t.[next]
			OR d.V3 >= t.aktuell AND d.V3 < t.[next]
			OR t.[next] = @earliestTime AND (t.aktuell <= d.V1 OR t.aktuell <= d.V2 OR t.aktuell <= d.V3 OR t.[next] > d.V1 OR t.[next] > d.V2 OR t.[next] > d.V3))
		GROUP BY t.aktuell, d.VertragsNr) as data

	END
GO
GRANT EXECUTE ON ShiftCountingByWorkingTime TO public
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'SpecialWithFactor' AND o.id = OBJECT_ID(N'dbo.FormulaConfigurations'))
BEGIN
	ALTER TABLE FormulaConfigurations ADD SpecialWithFactor FLOAT NULL
END
GO
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 165 OR (Minor = 165 AND Build < 105)))))
BEGIN
    --Übernehhemn der Formeln	
	IF EXISTS(SELECT * FROM DienstplanArtenTabelle WHERE  SumColVisible&512 = 512 OR SumColVisible&1024 = 1024 OR SumColVisible&2048 = 2048 OR SumColVisible&0x400000 = 0x400000)
	BEGIN
		INSERT INTO FormulaConfigurations ([Name], Code,SerializedParameter,FormulaId,Modul,LC, UC, LM ,UM, SpecialWithFactor)
		VALUES ('Schichtlage','SL','<?xml version="1.0" encoding="utf-16"?>  <ShiftCountingByWorkingTimeParameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" PeriodType="YearCloseType" PeriodCount="1" EndIsCurrentWeek="true" StaticPeriodStart="0" StaticPeriodStartType="Week" DisplayFormat="Absolute" ValueSeparator=" / " NumberFormat="000" />',
				'ShiftCountingByWorkingTime',4,GETDATE(),-1,GETDATE(),-1,2)

		INSERT INTO FormulaConfigurations_Departments (FormulaConfigurationId, DepartmentId,LC, UC, LM ,UM)
		SELECT (SELECT MAX(FormulaConfigurationId) FROM FormulaConfigurations), DienstplanArtenID, GETDATE(), -1, GETDATE(), -1 
				FROM DienstplanArtenTabelle WHERE SumColVisible&512 = 512 OR SumColVisible&1024 = 1024 OR SumColVisible&2048 = 2048 OR SumColVisible&0x400000 = 0x400000
	END

	IF EXISTS(SELECT * FROM DienstplanArtenTabelle WHERE  SumColVisible&4096 = 4096 OR SumColVisible&8192 = 8192 OR SumColVisible&16384 = 16384 OR SumColVisible&0x800000 = 0x800000)
	BEGIN
		INSERT INTO FormulaConfigurations ([Name], Code,SerializedParameter,FormulaId,Modul,LC, UC, LM ,UM, SpecialWithFactor)
		VALUES ('Schichtlage %','SL%','<?xml version="1.0" encoding="utf-16"?>  <ShiftCountingByWorkingTimeParameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" PeriodType="YearCloseType" PeriodCount="1" EndIsCurrentWeek="true" StaticPeriodStart="0" StaticPeriodStartType="Week" DisplayFormat="Percentual" ValueSeparator=" / " NumberFormat="00" />',
				'ShiftCountingByWorkingTime',4,GETDATE(),-1,GETDATE(),-1,2)

		INSERT INTO FormulaConfigurations_Departments (FormulaConfigurationId, DepartmentId,LC, UC, LM ,UM)
		SELECT (SELECT MAX(FormulaConfigurationId) FROM FormulaConfigurations), DienstplanArtenID, GETDATE(), -1, GETDATE(), -1 
				FROM DienstplanArtenTabelle WHERE SumColVisible&4096 = 4096 OR SumColVisible&8192 = 8192 OR SumColVisible&16384 = 16384 OR SumColVisible&0x800000 = 0x800000
	END
END
GO
--#endregion

--#region NET Framework loggen
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'FrameworkInfo' AND o.id = OBJECT_ID(N'dbo.Computer'))
BEGIN
	ALTER TABLE Computer ADD FrameworkInfo VARCHAR(1000)
END
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.LogLogin' ))
BEGIN
	DROP PROCEDURE dbo.LogLogin
END
GO

CREATE PROCEDURE dbo.LogLogin 
		@ComputerName VARCHAR(64),
		@OSVersion VARCHAR(24),
		@TheasoftVersion VARCHAR(24),
		@IsAutoUpdateEnabled BIT,
		@AutoUpdatePath VARCHAR(255),
		@FrameworkInfo VARCHAR(MAX)
	AS
	BEGIN
		DECLARE  @ComputerId INT
		DECLARE  @UserId INT
		SET @UserId = 0
		SET @ComputerId = 0
		SELECT @ComputerId = ComputerId FROM Computer WHERE Name = @ComputerName
		SELECT @UserId = UserID FROM UserPW WHERE  UserName = CURRENT_USER

		IF @UserId = 0 
		BEGIN
			EXEC AddCurrentUser
			SELECT @UserId = UserID FROM UserPW WHERE  UserName = CURRENT_USER
		END

		IF @ComputerId > 0
			BEGIN
				UPDATE Computer SET theasoftVersion = @TheasoftVersion, OSVersion = @OSVersion, AutoUpdatePath = @AutoUpdatePath, FrameworkInfo = @FrameworkInfo, LM = GetDate(), UM = @UserId WHERE  ComputerId = @ComputerId
			END
		ELSE
			BEGIN
				INSERT INTO Computer (Name, theasoftVersion,  OSVersion,AutoUpdatePath, FrameworkInfo, LC, UC, LM, UM) 
					VALUES (@ComputerName, @TheasoftVersion, @OSVersion, @AutoUpdatePath, @FrameworkInfo, GetDate(), @UserId, GetDate(), @UserId) 

				SELECT @ComputerId = ISNULL(scope_identity(),0)
			END	

			UPDATE UserPW SET LastLoginDate = GetDate(), LastLoginComputerId = @ComputerId WHERE UserID  = @UserId
	END
GO
GRANT EXECUTE ON dbo.LogLogin TO public
GO
--#endregion


--------------------- 4.166.x ---------------------------------

--2016-11-03 sh
--#region neue Spalte ChangeIdentifiers in OrderHistory
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ChangeIdentifiers' AND o.id = OBJECT_ID(N'dbo.OrderHistory'))
BEGIN
	ALTER TABLE OrderHistory ADD ChangeIdentifiers BIGINT NOT NULL CONSTRAINT DF_OrderHistory_ChangeIdentifiers DEFAULT 0
END
GO
--#endregion

--20161104 DH
--#region Bestellung: Liefer und Zahlstatus in Tabelle ausgelagert.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.OrderPaymentStatusTypes') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE OrderPaymentStatusTypes (
		OrderPaymentStatusTypeId INT IDENTITY(1,1) NOT NULL,
		Name NVARCHAR(50) NOT NULL,
		ExportCode NVARCHAR(50) NOT NULL,
		Sort INT NOT NULL DEFAULT(0),
		IsReadOnly BIT NOT NULL DEFAULT(0) ,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL
		CONSTRAINT PK_OrderPaymentStatusTypes PRIMARY KEY CLUSTERED (OrderPaymentStatusTypeId ASC) ON [PRIMARY],
		CONSTRAINT UQ_OrderPaymentStatusTypes_Name UNIQUE (Name),
		CONSTRAINT UQ_OrderPaymentStatusTypes_ExportCode UNIQUE (ExportCode)
	) On [PRIMARY]
	SET Identity_Insert OrderPaymentStatusTypes ON
	INSERT INTO OrderPaymentStatusTypes (
			OrderPaymentStatusTypeId,Name,ExportCode,Sort,LC,LM,UM,UC,IsReadOnly)
		VALUES 
			(0,'NotPaid','NotPaid',0,GetDate(),GETDATE(),0,0,1),
			(1,'Paid','Paid',1,GetDate(),GETDATE(),0,0,1),
			(2,'PartPaid','PartPaid',2,GetDate(),GETDATE(),0,0,1),
			(3,'RequestedPayback','RequestedPayback',3,GetDate(),GETDATE(),0,0,0),
			(4,'DeclinedPayback','DeclinedPayback',4,GetDate(),GETDATE(),0,0,0);			

	SET Identity_Insert OrderPaymentStatusTypes OFF
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID ('FK_Orders_OrderPaymenstatusTypes'))
BEGIN
	ALTER TABLE Orders WITH CHECK 
		ADD CONSTRAINT FK_Orders_OrderPaymenstatusTypes
		FOREIGN KEY (PaymentStatus) REFERENCES OrderPaymentStatusTypes(OrderPaymentStatusTypeId)
END

GRANT SELECT ON OrderPaymentStatusTypes TO Public
GRANT INSERT,UPDATE,DELETE on OrderPaymentStatusTypes TO OrderAdmin

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.OrderDeliveryStatusTypes') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE OrderDeliveryStatusTypes (
		OrderDeliveryStatusTypeID INT IDENTITY(20,1) NOT NULL,
		Name NVARCHAR(50) NOT NULL,
		ExportCode NVARCHAR(50) NOT NULL,
		Sort INT NOT NULL DEFAULT(0),
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL
		CONSTRAINT PK_OrderDeliveryStatusTypes PRIMARY KEY CLUSTERED (OrderDeliveryStatusTypeID ASC) ON [PRIMARY],
		CONSTRAINT UQ_OrderDeliveryStatusTypes_Name UNIQUE (Name),
		CONSTRAINT UQ_OrderDeliveryStatusTypes_ExportCode UNIQUE (ExportCode)
	) On [PRIMARY]
	SET Identity_Insert OrderDeliveryStatusTypes ON
	INSERT INTO OrderDeliveryStatusTypes (
			OrderDeliveryStatusTypeId,Name,ExportCode,Sort,LC,LM,UM,UC)
		VALUES 
			(0,'Unknown','Unknown',0,GetDate(),GETDATE(),0,0),
			(1,'Ordered','Ordered',1,GetDate(),GETDATE(),0,0),
			(2,'PartDelivered','PartDelivered',2,GetDate(),GETDATE(),0,0),
			(4,'Delivered','Delivered',3,GetDate(),GETDATE(),0,0),
			(8,'PartReturned','PartReturned',4,GetDate(),GETDATE(),0,0),
			(16,'Returned','Returned',5,GetDate(),GETDATE(),0,0);				

	SET Identity_Insert OrderDeliveryStatusTypes OFF
	ALTER TABLE Orders WITH CHECK 
		ADD CONSTRAINT FK_Orders_OrderDeliveryStatusTypes
		FOREIGN KEY (DeliveryStatus) REFERENCES OrderDeliveryStatusTypes(OrderDeliveryStatusTypeId)
END
GRANT SELECT ON OrderDeliveryStatusTypes TO Public
GRANT INSERT,UPDATE,DELETE on OrderDeliveryStatusTypes TO OrderAdmin
--#endregion

--20161104 dh
--#region Neue Felder für Bestellungen
IF NOT EXISTS (select * from dbo.syscolumns where ID=OBJECT_ID(N'[dbo].[Orders]') and name= 'PaymentNote')
BEGIN
ALTER TABLE dbo.Orders Add PaymentNote Nvarchar(1000) NULL 
END

IF NOT EXISTS (select * from dbo.syscolumns where ID=OBJECT_ID(N'[dbo].[Orders]') and name= 'PayedAmount')
BEGIN
ALTER TABLE dbo.Orders Add PayedAmount Money Not Null Default(0)
END
--#endregion

--#region GetActivitiesInternal2
-- Da sich die Parameter geändert haben als GetActivitiesInternal2 anlegen
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetActivitiesInternal2'))
	DROP PROCEDURE dbo.GetActivitiesInternal2
GO

CREATE PROCEDURE dbo.GetActivitiesInternal2
	@start DATETIME,
	@end DATETIME,
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@userId INT,
	@ShowActivitiesWithoutRights BIT
	WITH EXECUTE AS 'dbo'
AS
BEGIN
	
	SET NOCOUNT ON
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @permissionPeriodStart DATETIME
	DECLARE @permissionPeriodEnd DATETIME

	-- *** Spielzeitberechtigungen prüfen ***
	SELECT @permissionPeriodStart = MIN(s.SpzStart), @permissionPeriodEnd = MAX(s.SpzEnde)
		FROM Spielzeittabelle AS s
			INNER JOIN UserForAccess AS us	ON s.spznr = us.spznr
		WHERE us.UserId = @userId AND s.SpzNr <> 0

	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 (UserGruppeId INT NOT NULL, BerechtigungsbereichId INT NOT NULL, OptionsFlag INT NOT NULL)
	INSERT INTO #Permissions SELECT UserGruppe.GruppeId, UserGruppe.BerechtigungsbereichID, OptionsFlag  FROM UserGruppe WHERE UserId = @userId AND BerechtigungsbereichId 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  =', Aufführg.AuffNr, Aufführg.ZRNr,
					Aufführg.Datum, Aufführg.UhrBeg, Aufführg.UhrEnde, Aufführg.DiplaDru, Aufführg.VonZeit,  Aufführg.Pl, 
					Aufführg.Status, Aufführg.PreisNr, Aufführg.ZuAuffNr, Aufführg.Gesperrt, Aufführg.CreateCountDownId, 
					Aufführg.IsTimeSpan, Aufführg.IsExtraPayLiable, 
					Aufführg.LM, Aufführg.UM, Aufführg.LC, Aufführg.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  Aufführg.AuffNr, CASE WHEN  Ug.UserGruppeId IS NULL OR (Aufführg.Gesperrt = 1 AND ug.OptionsFlag&1 <> 1)  THEN 1	ELSE 0 END'

			SET @secretfields  =       'CASE WHEN NoPermission = 1 THEN -100 ELSE Aufführg.SNr		END	AS SNr, 
										CASE WHEN NoPermission = 1 THEN 0	 ELSE Aufführg.Art		END	AS Art, 
										CASE WHEN NoPermission = 1 THEN NULL ELSE Aufführg.Bem		END	AS Bem,
										CASE WHEN NoPermission = 1 THEN NULL ELSE Aufführg.BemKurz	END AS BemKurz,
										CASE WHEN NoPermission = 1 THEN NULL ELSE Aufführg.BemKurz2 END AS BemKurz2'
		END
	ELSE
		BEGIN
			IF (@ShowActivitiesWithoutRights = 1) 
				
				SET @secretfields  =   'CASE WHEN Ug.UserGruppeId IS NULL OR (Aufführg.Gesperrt = 1 AND ug.OptionsFlag&1 <> 1) THEN -100 ELSE Aufführg.SNr		END	AS SNr, 
										CASE WHEN Ug.UserGruppeId IS NULL OR (Aufführg.Gesperrt = 1 AND ug.OptionsFlag&1 <> 1) THEN 0	 ELSE Aufführg.Art		END	AS Art, 
										CASE WHEN Ug.UserGruppeId IS NULL OR (Aufführg.Gesperrt = 1 AND ug.OptionsFlag&1 <> 1) THEN NULL ELSE Aufführg.Bem		END	AS Bem,
										CASE WHEN Ug.UserGruppeId IS NULL OR (Aufführg.Gesperrt = 1 AND ug.OptionsFlag&1 <> 1) THEN NULL ELSE Aufführg.BemKurz	END AS BemKurz,
										CASE WHEN Ug.UserGruppeId IS NULL OR (Aufführg.Gesperrt = 1 AND ug.OptionsFlag&1 <> 1) THEN NULL ELSE Aufführg.BemKurz2 END AS BemKurz2'
			ELSE 
				SET @secretfields  =   'Aufführg.SNr,
										Aufführg.Art, 
										Aufführg.Bem, 
										Aufführg.BemKurz, 
										Aufführg.BemKurz2'

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



	-- ***	Immer auf Produktionen unf Berechtigungen ***
		SET @SQLQuery =  @SQLQuery + '	 FROM  AufführgTabelle AS Aufführg
										INNER JOIN  Stücke AS p ON Aufführg.snr = p.snr '

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

	-- *** Weitere Joins ***	 
		IF (@joins&1 = 1) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN AufführgDetails ON Aufführg.Auffnr = AufführgDetails.AuffNr '

		IF (@joins&2 = 2) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN PresseText AS PT ON PT.AuffNr = Aufführg.AuffNr '
			--Wenn Abfrage direkt ausgeführt wird Felder für oder Joinen hinzufügen. Ggf auch bei Abfrge unten prüfen
		IF (@joins&4 = 4 OR (@include = 0 AND CHARINDEX('zr.', @order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN Zr ON Aufführg.ZrNr = Zr.ZrNr '

		IF (@joins&8 = 8 OR (@include = 0 AND CHARINDEX('AuffTypen.', @order) > 0 )) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN AuffTypen ON Aufführg.Art = AuffTypen.AuffTNr '

		IF (@joins&16 = 16) 
			SET @SQLQuery = @SQLQuery + ' LEFT JOIN StückeDetails ON Aufführg.Snr = StückeDetails.Snr '

		IF (@joins&32 = 32) 
			SET @SQLQuery = @SQLQuery + ' INNER JOIN Gruppe ON p.ModuleGroupId = Gruppe.GruppeID '
			
		SET @SQLQuery = @SQLQuery + ' WHERE Aufführg.Datum 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 gesperrt erforderlich
		IF (EXISTS(SELECT * FROM #Permissions WHERE BerechtigungsbereichId = 2 AND OptionsFlag&1 <> 1)) 
		BEGIN
			
			IF (@ShowActivitiesWithoutRights = 0) 
					SET @SQLQuery = @SQLQuery  + ' AND (Aufführg.Gesperrt = 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 Aufführgtabelle AS Aufführg 
																	INNER JOIN  #ActivitiesNP ON Aufführg.auffnr = #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('zr.', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN Zr ON Aufführg.ZrNr = Zr.ZrNr '
				END
				IF (CHARINDEX('Titel', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN  Stücke AS p ON Aufführg.snr = p.snr '
				END
				IF (CHARINDEX('AuffTypen', @order) > 0) BEGIN
					SET @SQLQuery = @SQLQuery + ' INNER JOIN AuffTypen ON Aufführg.Art = AuffTypen.AuffTNr '
				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], AufführgDetails.* 
		FROM AufführgDetails 
			INNER JOIN #ActivitiesNP On AufführgDetails.AuffNr = #ActivitiesNP.ActivityId

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

	IF (@include&4 = 4) 
		SELECT NULL AS [#4], Aufr.*, Ring.ZuAbo 
		FROM Aufr 
			INNER JOIN Ring ON AufR.AboNr = Ring.AboNr
			INNER JOIN #ActivitiesNP ON Aufr.AuffNr = #ActivitiesNP.ActivityId

	IF (@include&8 = 8) 
		SELECT NULL AS [#8], BesetzungTabelle.* 
		FROM BesetzungTabelle 
			INNER JOIN #ActivitiesNP ON BesetzungTabelle.AuffNr = #ActivitiesNP.ActivityId
			INNER JOIN BesetzungStd ON BesetzungStd.Id = BesetzungTabelle.RolleID
			INNER JOIN #Permissions AS UG ON UG.UserGruppeId  = BesetzungStd.Besitzer AND  ug.BerechtigungsbereichID = 4 AND (BesetzungTabelle.Gesperrt = 0 OR ug.OptionsFlag&1 = 1)
			
	IF (@include&16 = 16) 
	BEGIN
		DECLARE @changeSince  DATETIME
		SET @changeSince = GETDATE() - 21 
		SELECT NULL AS [#16],ax.*
		FROM  XHDelAufführg AS ax
			INNER JOIN #ActivitiesNP ON ax.AuffNr = #ActivitiesNP.ActivityId
			LEFT JOIN (SELECT xhid FROM KenntnisDel WHERE tabl = 'Aufführg' 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], AuffDienstplanArten.* 
		FROM AuffDienstplanArten 
			INNER JOIN #ActivitiesNP ON AuffDienstplanArten.AuffNr = #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], AuffTechAufgabe.* 
		FROM AuffTechAufgabe 
			INNER JOIN #ActivitiesNP ON AuffTechAufgabe.AuffNr = #ActivitiesNP.ActivityId


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



GO

GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.GetActivities2'))
	DROP PROCEDURE dbo.GetActivities2
GO


CREATE PROCEDURE dbo.GetActivities2
	@start DATETIME,
	@end DATETIME,
	@condition VARCHAR(MAX),
	@order VARCHAR(MAX),
	@joins INT,
	@include INT,
	@ShowActivitiesWithoutRights BIT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @userId INT
	SELECT @userId = UserId FROM UserPW WHERE UserName  = CURRENT_USER
	EXECUTE GetActivitiesInternal2 @start, @end, @condition, @order, @joins, @include, @userId, @ShowActivitiesWithoutRights

END

GO
GRANT EXECUTE ON GetActivities2 TO public
GO

--#endregion GetActivities2

--20161019 bm
--#region Besetzungszettel-Einstellungen in ts_dataPlan verschieben
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 165 OR Minor = 165 AND Build < 3))))
BEGIN
    INSERT INTO [Plan] (Bez, PlanArt, LC, UC, LM, UM, Settings, OwnerId)
		SELECT [Control], 190, LC, UC, LM, UM, REPLACE(Werttext, 'PrintActivityCastSettings', 'DispoActivityCastPlanSettings'), 1
		FROM Settings 
		WHERE Form = 'PrintActivityCast'

	 INSERT INTO [Plan] (Bez, PlanArt, LC, UC, LM, UM, TemplateId, OwnerId)
		 SELECT Bez, 195,LC, UC, LM, UM, VorlagenId , (SELECT MIN(GruppeID) FROM Gruppe WHERE Modul = 2)
		 FROM Vorlagen 
		 WHERE Vorlagen.ReportType = 1201
END
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.DeleteProduction'))
	DROP PROCEDURE dbo.DeleteProduction
GO

CREATE PROCEDURE dbo.DeleteProduction
	@ProductionId INT
AS
BEGIN
SET NOCOUNT ON

DECLARE @UserGroup int
SELECT @UserGroup = ModuleGroupId FROM Stücke WHERE Snr = @ProductionId

--Berechtigungsprüfung
IF (dbo.GetCurrentPermission(@UserGroup, 3) < 2 ) 
	BEGIN
		SELECT 1 AS Attribut, 'No Permission' 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 BesetzungStdSoliTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM VertragRolle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM BesRepertoire WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM VertragsBesetzungsHonorar WHERE BesID IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId))
				DELETE FROM ContractDateConfirmations WHERE ContractDateId IN (SELECT ContractDateId FROM ContractDates WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)))
				DELETE FROM ContractDates WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId))
				DELETE FROM CastBonus WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId))
				DELETE FROM CashCommandPos_Cast WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId))
				DELETE FROM CastCredits WHERE ServiceCastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId))
				DELETE FROM CastCredits WHERE ReplacementCastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId))
				UPDATE ContractDates SET  CastId = NULL WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId))
				DELETE FROM BesetzungTabelle WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM AuffTechAufgabe WHERE RolleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM RoleBonus WHERE RoleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM Role_Season WHERE RoleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM Roles_Scenes WHERE RoleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM Qualification_Role WHERE RoleId IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)
				DELETE FROM BesetzungStd WHERE ID IN (SELECT ID FROM BesetzungStd WHERE Snr = @ProductionId)	
				DELETE FROM StückeTeil WHERE Snr = @ProductionId
				DELETE FROM StückeTeil WHERE SnrTeil = @ProductionId
				DELETE FROM ActivityScenes WHERE ActivityId IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM AuffDienstplanArten WHERE Auffnr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM AuffTechAufgabe WHERE Auffnr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM Kosten_Aufführg WHERE Auffnr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM PresseText WHERE Auffnr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM AufführgDetails WHERE Auffnr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM Fahrplan WHERE AuffNr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM CostUnitWorkingTime WHERE ActivityId IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM AufführgDetails WHERE AuffNr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM AufR WHERE AuffNr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				DELETE FROM AufführgTabelle WHERE AuffNr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
				UPDATE AufführgTabelle SET ZuAuffNr = NULL WHERE ZuAuffNr IN (SELECT AuffNr FROM AufführgTabelle WHERE Snr = @ProductionId)
			END
		END
END
go
GRANT EXECUTE ON DeleteProduction TO public

GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.DeleteRole'))
	DROP PROCEDURE dbo.DeleteRole
GO
CREATE PROCEDURE dbo.DeleteRole
	@RoleId INT
AS
BEGIN
SET NOCOUNT ON

DECLARE @UserGroup int
SELECT @UserGroup = Besitzer FROM BesetzungStd WHERE ID=@RoleId

--Berechtigungsprüfung
IF (dbo.GetCurrentPermission(@UserGroup, 4) < 2 ) 
	BEGIN
		SELECT 1 AS Attribut, 'No Permission' AS MSG
	END
ELSE
	BEGIN 
		IF (@RoleId = 0)	
			BEGIN
				SELECT 0 AS Attribut,'Keine gültige Rolle übergeben (Id 0)' AS MSG
			END
		ELSE
			BEGIN
				DELETE FROM BesetzungStdSoliTabelle WHERE RolleID=@RoleId
				DELETE FROM VertragRolle WHERE RolleID=@RoleId
				DELETE FROM BesRepertoire WHERE RolleID=@RoleId
				DELETE FROM RoleBonus WHERE RoleId=@RoleId
				DELETE FROM Role_Season WHERE RoleId=@RoleId
				DELETE FROM Roles_Scenes WHERE RoleId=@RoleId
				DELETE FROM Qualification_Role WHERE RoleId=@RoleId
				DELETE FROM VertragsBesetzungsHonorar WHERE BesID IN (SELECT ID FROM BesetzungTabelle WHERE RolleID=@RoleId)
				DELETE FROM ContractDateConfirmations WHERE ContractDateId IN (SELECT ContractDateId FROM ContractDates WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleID=@RoleId))
				DELETE FROM ContractDates WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleID=@RoleId)
				DELETE FROM CastBonus WHERE CastId IN (SELECT ID FROM BesetzungTabelle WHERE RolleID=@RoleId)
				DELETE FROM BesetzungTabelle WHERE RolleId=@RoleId
				DELETE FROM AuffTechAufgabe WHERE RolleId=@RoleId
				DELETE FROM BesetzungStd WHERE ID=@RoleId	

			END
		END
END
GO
GRANT EXECUTE ON DeleteRole TO public
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.DeleteCast'))
	DROP PROCEDURE dbo.DeleteCast
GO
CREATE PROCEDURE dbo.DeleteCast 
	@CastId INT
AS
BEGIN
SET NOCOUNT ON
 
DECLARE @UserGroupId INT 
			
--BesitzerId der Rolle
SELECT @UserGroupId = Besitzer
	FROM BesetzungStd AS R INNER JOIN BesetzungTabelle AS C ON r.id = c.RolleID
	WHERE c.Id = @CastId

IF (dbo.GetCurrentPermission(@UserGroupId, 4) < 2) 
	BEGIN
		SELECT 1 AS Attribut, 'No Permission' AS MSG
	END
ELSE
	BEGIN 
		DELETE FROM CashCommandPos_Cast WHERE CastId = @CastId
		DELETE FROM CastCredits WHERE ServiceCastId = @CastId
		DELETE FROM CastCredits WHERE ReplacementCastId = @CastId
		DELETE FROM CastBonus WHERE CastId = @CastId
		DELETE FROM VertragsBesetzungsHonorar WHERE BesID = @CastId
		UPDATE ContractDates SET  CastId = NULL WHERE CastId = @CastId
		DELETE FROM BesetzungTabelle WHERE Id = @CastId
	END
END	
GO
GRANT EXECUTE ON DeleteCast TO public

GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.DeleteActivityRelations'))
	DROP PROCEDURE dbo.DeleteActivityRelations
go
--Löscht keine Abos u. Auff.-Details
CREATE PROCEDURE dbo.DeleteActivityRelations
	@ActivityId INT
AS
BEGIN
SET NOCOUNT ON
	IF NOT EXISTS (SELECT * FROM ActivitiesUpdate WHERE AuffNr= @ActivityId)
		BEGIN
				SELECT 1 AS Attribut, 'No Permission' AS MSG
			END
		ELSE
	BEGIN
			
		DELETE FROM CastCredits WHERE ServiceCastId IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		DELETE FROM CastCredits WHERE ReplacementCastId IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		DELETE FROM VertragsBesetzungsHonorar WHERE BesID IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		DELETE FROM CastBonus WHERE CastBonus.CastId IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		DELETE FROM CashCommandPos_Cast WHERE CastId IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		UPDATE ContractDates SET  CastId = NULL WHERE CastId IN (SELECT Id FROM BesetzungTabelle WHERE Auffnr = @activityId)
		DELETE FROM BesetzungTabelle WHERE Auffnr = @activityId
		DELETE FROM ActivityScenes WHERE ActivityId = @activityId
		DELETE FROM AuffDienstplanArten WHERE Auffnr = @activityId
		DELETE FROM AuffTechAufgabe WHERE Auffnr = @activityId
		DELETE FROM Kosten_Aufführg WHERE Auffnr = @activityId
		DELETE FROM PresseText WHERE Auffnr = @activityId
		DELETE FROM AufführgDetails WHERE Auffnr = @activityId
		DELETE FROM Fahrplan WHERE AuffNr = @ActivityId
		DELETE FROM CostUnitWorkingTime WHERE ActivityId = @activityId
		DELETE FROM ActivityScenes WHERE ActivityId = @activityId
		UPDATE AufführgTabelle SET ZuAuffNr = NULL WHERE ZuAuffNr = @ActivityId
	END	
END	
GO
GRANT EXECUTE ON DeleteActivityRelations TO public

		
IF NOT EXISTS (select * from dbo.syscolumns where ID=OBJECT_ID(N'dbo.Aztypen') and name= 'IsInactive')
BEGIN
	ALTER TABLE dbo.Aztypen Add IsInactive BIT Not Null Default(0)
END

IF NOT EXISTS (select * from dbo.syscolumns where ID=OBJECT_ID(N'dbo.OrderValidationApprovalStatus') and name= 'ChangeRulesSettingXML')
BEGIN
	ALTER TABLE dbo.OrderValidationApprovalStatus Add ChangeRulesSettingXML VARCHAR(MAX) NULL
END

--#endregion

--#region neue Spalte OrderRequiresPaymentNote in OrderDeliveryStatusTypes
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'OrderRequiresPaymentNote' AND o.id = OBJECT_ID(N'dbo.OrderDeliveryStatusTypes'))
BEGIN
	ALTER TABLE OrderDeliveryStatusTypes ADD OrderRequiresPaymentNote BIT NOT NULL CONSTRAINT DF_OrderDeliveryStatusTypes_OrderRequiresPaymentNote DEFAULT 0
END
GO
--#endregion

--#region  Nicht mehr verwendete Tabellen und Felder löschen
DropColumn 'ZR','IsRehearsalStage'
GO
IF  EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 	WHERE c.name = 'Alt' and o.id = OBJECT_ID(N'[dbo].[ZR]'))	EXECUTE sp_unbindefault N'dbo.ZR.alt'
GO
DropColumn 'ZR','Alt'
GO
DropColumn 'StückeDetails','MusiProbenBegin'
GO
DropColumn 'StückeDetails','ProbenBegin'
GO
DropColumn 'StückeDetails','StückeDetailsTS'
GO
DropColumn 'StückeDetails','MusiProbenBegin'
GO
DropColumn 'StückeDetails','MusiProbenBegin'
GO
DropColumn 'Stücke','StückeTS'
GO
DropColumn 'Stücke','Teil'
GO
DropColumn 'Stücke','Sonderveranstaltung'
GO
DropColumn 'Stücke','Doppel'
GO
DropColumn 'Stücke','Mandant'
GO
DropColumn 'Stücke','Exid'
GO
DropColumn 'ZeitkontenMonat','WertKorr'
GO
DropColumn 'ZeitkontenMonat','Vortrag'
GO
DropColumn 'ZeitkontenMonat','Anspruch'
GO
DropColumn 'AuffTechAufgabe','AbGruppe'
GO
DropColumn 'AuffTechAufgabe','PersKatNr'
GO
DropColumn 'Sparte','InternetLinkSymbol'
GO
DropColumn 'Sparte','InternetPfad'
GO
DropColumn 'PersonalDetails','Str'
GO
DropColumn 'PersonalDetails','PLZ'
GO
DropColumn 'PersonalDetails','Ort'
GO
DropColumn 'PersonalDetails','Tel'
GO
DropColumn 'PersonalDetails','Fax'
GO
DropColumn 'PersonalDetails','Land'
GO
DropColumn 'PersonalDetails','KuenstlerVN'
GO
DropColumn 'PersonalDetails','KuenstlerNN'
GO
DropColumn 'PersonalDetails','Titel'
GO
DropColumn 'PersonalDetails','Co'
GO
DropColumn 'PersonalDetails','EintritsDat'
GO
DropColumn 'PersonalDetails','AustritsDat'
GO
DropColumn 'PersonalDetails','Theater'
GO
DropColumn 'PersonalDetails','Hotel'
GO
DropColumn 'PersonalDetails','AnzUeberspiel'
GO
DropColumn 'PersonalDetails','EMail'
GO
DropColumn 'PersonalDetails','Handy'
GO
DropColumn 'PersonalDetails','HausRuf'
GO
DropColumn 'PersonalDetails','Kopf'
GO
DropColumn 'PersonalDetails','Kragen'
GO
DropColumn 'PersonalDetails','Brust'
GO
DropColumn 'PersonalDetails','Arm'
GO
DropColumn 'PersonalDetails','Taille'
GO
DropColumn 'PersonalDetails','Po'
GO
DropColumn 'PersonalDetails','Seite'
GO
DropColumn 'PersonalDetails','Schuh'
GO
DropColumn 'PersonalDetails','Gewicht'
GO
DropColumn 'PersonalDetails','Groesse'
GO
DropColumn 'PersonalDetails','NVDat'
GO
DropColumn 'PersonalDetails','PersonalDetailsTS'
GO
DropColumn 'PersonalDetails','AgenturNr2'
GO
DropColumn 'PersonalDetails','Homepage'
GO
DropColumn 'PersonalDetails','Rueckenbreite'
GO
DropColumn 'PersonalDetails','Rueckenlaenge'
GO
DropColumn 'PersonalDetails','Konfektion'
GO
DropColumn 'PersonalDetails','Schritt'
GO
DropColumn 'PersonalDetails','Bild'
GO
DropColumn 'Countdown','Exklusiv'
GO
DropColumn 'Vertrag','HonorarUeberspielx'
GO
DropColumn 'PersonalDetails','Bank'
GO
DropColumn 'PersonalDetails','KtoInhaber'
GO
DropColumn 'PersonalDetails','KtoNr'
GO
DropColumn 'PersonalDetails','BLZ'
GO
DropColumn 'PersonalDetails','AufgabeNr'
GO
DropColumn 'PersonalDetails','BJBausgabe'
GO
DropColumn 'PersonalDetails','BJBGenPos'
GO
DropColumn 'BesetzungStd','AuffTNr'
GO
DropColumn 'AufführgTabelle','Chor'
GO
DropColumn 'AufführgTabelle','Orchester'
GO
DropColumn 'AufführgTabelle','Ballett'
GO
DropColumn '[UserPw]','Inactive'
GO
DropColumn 'Zulagen','StdWertVst'
GO
DropColumn 'Zulagen','StdWertPro'
GO
DropColumn 'PersonalDetails','InternetLink'
GO
DropColumn 'PersonalDetails','PersNrVorh'
GO
DropColumn 'PersonalDetails','KstAbt'
GO
DropColumn 'PersonalDetails','BildVerweis'
GO
DropColumn 'VertragPerso','FtAuszahlen'
GO
DropColumn 'VertragPerso','AzAuszahlenAb'
GO
DropColumn 'VertragPerso','AzAuszahlenBis'
GO
DropColumn 'VertragPerso','AbrechnungZyklus'
GO
DropColumn 'Status','StatusTS'
GO
DropColumn 'Status','Verdichten'
GO
DropColumn 'StatusZeitR','VRID'
GO
DropColumn 'StatusZeitR','StatusZeitRTS'
GO
DropColumn 'AufführgDetails','Kosten'
GO
DropColumn 'AufführgDetails','FV_Voll'
GO
DropColumn 'AufführgDetails','FV_Erm1'
GO
DropColumn 'AufführgDetails','FV_Erm2'
GO
DropColumn 'AufführgDetails','FV_Erm3'
GO
DropColumn 'AufführgDetails','FV_Gruppen'
GO
DropColumn 'AufführgDetails','FV_Schueler'
GO
DropColumn 'AufführgDetails','FV_Kinder'
GO
DropColumn 'AufführgDetails','Reservierung'
GO
DropColumn 'AufführgDetails','Steuerkarten'
GO
DropColumn 'AufführgDetails','Dienstkarten'
GO
DropColumn 'AufführgDetails','Freikarten'
GO
DropColumn 'AufführgDetails','Ausgaben'
GO
DropColumn 'AufführgDetails','AusGutscheine'
GO
DropColumn 'AufführgDetails','EinGutscheine'
GO
DropColumn 'BesetzungTabelle','BesetzungTS'
GO
DropColumn 'RapportSpalte','Jahressumme'
GO
DropColumn 'RapportSpalte','BesetzungTS'
GO
DropColumn 'RapportSpalte','Y'
GO
DropColumn 'RapportSpalte','Y'
GO
DropColumn 'RapportSpalte','LohnartX'
GO
DropColumn 'RapportSpalte','LohnartY'
GO
DropColumn 'Rapport','NNX'
GO
DropColumn 'Rapport','NNY'
GO
DropColumn 'Rapport','VNY'
GO
DropColumn 'Rapport','VNX'
GO
DropColumn 'Rapport','PnrX'
GO
DropColumn 'Rapport','PnrY'
GO
DropColumn 'Rapport','JahrX'
GO
DropColumn 'Rapport','JahrY'
GO
DropColumn 'Rapport','MonatX'
GO
DropColumn 'Rapport','MonatY'
GO
DropColumn 'Rapport','ArbeitsbereichX'
GO
DropColumn 'Rapport','ArbeitsbereichY'
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[InstrumentenGruppe]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE InstrumentenGruppe
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Instrumente]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Instrumente
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FahrplanText]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE FahrplanText
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BesetzungStdBG]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE BesetzungStdBG
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AusrüstungsArten]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE AusrüstungsArten
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Ausrüstung]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Ausrüstung
Go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[WebAccess]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE WebAccess
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SettingsLst]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE SettingsLst
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Parameter]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Parameter
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OrchBesetzung]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE OrchBesetzung
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OrchBesetzungStd]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE OrchBesetzungStd
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PrintStatistik]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE PrintStatistik
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Komponisten]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Komponisten
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ResAuff]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE ResAuff
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Planstellen_Personal]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Planstellen_Personal
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Planstellen]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Planstellen
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PrintStatistik]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE PrintStatistik
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ProduktionsplanRep]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE ProduktionsplanRep
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SettingsLst]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE SettingsLst
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[confirm]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE confirm
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Ruhezeit]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Ruhezeit
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Abwesend]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Abwesend
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CountDownTechAufgabe]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE CountDownTechAufgabe
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DienstplanUser]') AND type in (N'U'))	DROP TABLE [dbo].[DienstplanUser]
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[oStückeUpdate]') AND type in (N'V'))	DROP VIEW [dbo].oStückeUpdate
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[oAufführgUpdate]') AND type in (N'V'))	DROP VIEW [dbo].oAufführgUpdate
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Kostentraegerzeiten]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Kostentraegerzeiten
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Kostentraeger]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Kostentraeger
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[preiskategoriePreise]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE preiskategoriePreise
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PreisKategorie]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE PreisKategorie
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Ressourcen]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Ressourcen
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RingDatBereich]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE RingDatBereich
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TagesplanAusgabe]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE TagesplanAusgabe
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TagesplanSpalten]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE TagesplanSpalten
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Tagesplan]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Tagesplan
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Tarif]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Tarif
go
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TechAufgabe]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE TechAufgabe
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AboTemp]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE AboTemp
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BallettDienste]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE BallettDienste
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BallettDispo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE BallettDispo
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ChorDienste]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE ChorDienste
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ChorDispo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE ChorDispo
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DelBesetzung]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE DelBesetzung
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[KollBallett]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE KollBallett
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[KollChor]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE KollChor
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[KollOrchester]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE KollOrchester
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MSysConf_ALT]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE MSysConf_ALT
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OrchDispo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE OrchDispo
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Statisten]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Statisten
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StatistenStd]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE StatistenStd
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StatistenStd]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE StatistenStd
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[ABWebAccess]'))	DROP VIEW [dbo].ABWebAccess
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Frei]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE Frei
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BG_Auff]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE BG_Auff
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BG_Bes]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE BG_Bes
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BG_BesStd]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)	DROP TABLE BG_BesStd
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[PersonalOrchesterDetails]'))	DROP VIEW [dbo].PersonalOrchesterDetails
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[PersKatTechnik]'))	DROP VIEW [dbo].PersKatTechnik
GO
--#endregion


--#region Inaktiv für Bankkonten 
IF NOT EXISTS (select * from dbo.syscolumns where ID=OBJECT_ID(N'dbo.BankAccount') and name= 'IsInactive')
BEGIN
	ALTER TABLE dbo.BankAccount Add IsInactive BIT NOT NULL CONSTRAINT DF_BankAccount_IsInactive DEFAULT 0
END
--#endregion

--#region neue Tabelle Jobs
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.Jobs') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.Jobs (
			JobId INT IDENTITY(1,1) NOT NULL,
			Name VARCHAR(30) NOT NULL,
			[Description] VARCHAR(200) NULL,
			JobTypeId VARCHAR(100) NOT NULL,
			IsActive BIT NOT NULL CONSTRAINT DF_Jobs_IsActive DEFAULT 0,
			JobSettings VARCHAR(MAX) NULL,
			TriggerSettings VARCHAR(MAX) NULL,
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL
			CONSTRAINT PK_Jobs PRIMARY KEY CLUSTERED (JobId ASC) ON [PRIMARY],
	) ON [PRIMARY]
END
GO

GRANT SELECT ON Jobs TO public
GRANT INSERT, DELETE, UPDATE ON Jobs TO MasterAdmin
GO
--#endregion

--#region neue Spalte für Primärqualifikation
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsMainQualificationOfCategory' AND o.id = OBJECT_ID(N'dbo.Qualification'))
BEGIN
	ALTER TABLE Qualification ADD IsMainQualificationOfCategory BIT NOT NULL CONSTRAINT DF_Qualification_IsMainQualificationOfCategory DEFAULT 0
	EXEC ('UPDATE Qualification SET IsMainQualificationOfCategory = 1 WHERE Sort = 1')
END
GO
--#endregion

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

--2017-02-20 sh
--#region Spalte InReport in Tabelle RapportSpalte (wurde versehentlich gelöscht)
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'InReport' and o.id = OBJECT_ID(N'[dbo].[RapportSpalte]'))
BEGIN
	ALTER TABLE dbo.RapportSpalte ADD InReport INT NOT NULL CONSTRAINT DF_RapportSpalte_InReport DEFAULT 0
END
GO
--#endregion

---------------------------------------------------------------
--#region ----------- Views aktualisieren ---------------------
DECLARE @SchemaView VARCHAR(100)
DECLARE view_cursor CURSOR FOR
SELECT Name
	FROM 	sysobjects
	WHERE	xtype = 'V' ORDER by 1

OPEN view_cursor
FETCH NEXT FROM view_cursor INTO @SchemaView
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @SchemaView
		EXEC sp_refreshview @SchemaView;
		FETCH NEXT FROM view_cursor INTO @SchemaView
	END
CLOSE view_cursor
DEALLOCATE view_cursor
GO
--#endregion

---------------------------------------------------------------
--#region ----------- Wegräumen der Hilfsprozeduren -----------
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DropIndexByTable'))				DROP PROCEDURE dbo.DropIndexByTable
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DropColumn'))					DROP PROCEDURE dbo.DropColumn 
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DeleteConstraint'))				DROP PROCEDURE dbo.DeleteConstraint 
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DropDefault'))					DROP PROCEDURE dbo.DropDefault 
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].DropRoleWithDropRoleMember'))	DROP PROCEDURE dbo.DropRoleWithDropRoleMember 
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].SumToFormula'))					DROP PROCEDURE dbo.SumToFormula 
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].PrepareUniqueColumnIndex'))		DROP PROCEDURE dbo.PrepareUniqueColumnIndex 

GO
--#endregion

---------------------------------------------------------------
--------------------- VERSIONIERUNG ---------------------------
-- Version aus ts_evt in TS_DataClass 
DECLARE @major INT
DECLARE @minor INT
DECLARE @build INT
DECLARE @Datum INT
----------
SET @major = 4
SET @minor = 166
SET @build = 101
SET @Datum = 20170220
--#region ----------- Version setzen ---------------------------
IF NOT EXISTS (SELECT *
				FROM  NvData
				WHERE ByModulScript = 1)
BEGIN
	INSERT INTO NvData (lfdVerNr, ByModulScript)
	VALUES				(1, 1)
END

--Ausführungen des Datenbankskripts werden geloggt.
INSERT INTO 
	NVData(
	   [lfdVerNr]
      ,[ByModulScript]
      ,[Major]
      ,[Minor]
      ,[LastDatabaseUpdateDate]
      ,[Build]) 
	SELECT lfdVerNr,2,Major,Minor,LastDataBaseUpdateDate,Build 
	FROM NVData 
	WHERE [ByModulScript]=1;
UPDATE NvData
SET
	Major = @major, Minor = @minor, Build = @build, lfdVerNr = @Datum, LastDatabaseUpdateDate = GetDate()
WHERE
	ByModulScript = 1
GO
--#endregion
--#region ----------- NOEXEC OFF ------------------------------
SET NOEXEC OFF
--#endregion