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

-- Versions und Datenbankprüfung
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 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


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 wird nicht mehr unterstützt!!!'	
																																															Print '		Bitte migrieren Sie auf mindestens SQL Server 2005 (empfohlen 2012)'
																																															Print ''
																																															Print '=============================================='
																																															Print ''
																																															Print ''
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
																																															Print ''
																																															Print ''
																																															RAISERROR('Nicht unterstützte SQL-Server-Version!!!', 20, -1) WITH LOG
																																															SET NOEXEC ON
END



GO
--Datenbanksversionsprüfung
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 146 OR (Minor=146 AND Build < 100)))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Der Versionsunterschied ist zu groß!!!'	
																																															Print '		Bitte führen Sie DbScript_2014.sql 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
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major > 4 OR (Major = 4 AND (Minor >= 161))))
BEGIN
																																															Print ''
																																															Print ''
																																															Print '=============================================='		
																																															Print ''
																																															Print '		Veraltete Skriptversion!!!'	
																																															Print '		Bitte führen Sie DbScript_2016.sql bzw. DbScript.sql aus.'
																																															Print ''
																																															Print '=============================================='
																																															Print ''
																																															Print ''																																															Print''
																																															Print''
																																															RAISERROR(N'Skriptversion veraltet!!!', 20, -1) WITH LOG
																																															SET NOEXEC ON
END
GO

---Ende Versionsprüfung

---------------------HilfsProzeduren
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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
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
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
		--if @cmd is null print 'No default constraint to drop'
		exec (@cmd)
		declare @cmd2 varchar(100)
		select @cmd2 = @tablename + '.' + @columnname
		--EXECUTE sp_unbindefault  @cmd2

		select @cmd='alter table '+@tablename+ ' drop COLUMN ' + @columnname
			exec (@cmd)
	END
END
GO
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

--#region Stored Procedure zum Löschen von Rollen, die vorher allerdings alle Member aus de Rolle entfernt
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

-------Ende HilfsProzeduren -----------------------------

---------Begin Codeblock---------------------------------
---Scripterstelldatum : 29.01.2015
----Datenbankänderungen ab hier--------------------------
--TK2015-01-16
-- Nicht mehr benötigte Felder löschen
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur3]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur3]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur1]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur1]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur2]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur2]
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
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur3]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur3]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur1]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur1]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersonalDetails_Agentur2]') AND type = 'F')
	ALTER TABLE [dbo].[PersonalDetails] DROP CONSTRAINT [FK_PersonalDetails_Agentur2]
GO
DropColumn 'PersonalDetails','Theater'
GO
DropColumn 'PersonalDetails','Hotel'
GO
DropColumn 'PersonalDetails','EMail'
GO
DropColumn 'PersonalDetails','Handy'
GO
DropColumn 'PersonalDetails','HausRuf'
GO
DropColumn 'PersonalDetails','NVDat'
GO
DropColumn 'PersonalDetails','AgenturNr2'
GO
DropColumn 'PersonalDetails','Bild'
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].[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].[Agentur]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE Agentur
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AgFilter]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE AgFilter
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 NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'AttachmentDocumentType' and o.id = OBJECT_ID(N'dbo.PersContractValidationStatus'))
BEGIN
	ALTER TABLE dbo.PersContractValidationStatus ADD AttachmentDocumentType INT NULL;
END


--BM 2015-01-20 Berechtigung, um besondere Pressetexte zu lesen und zu bearbeiten
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AbPressetext'))
BEGIN
	DROP VIEW [dbo].[AbPressetext]
END
GO
CREATE VIEW [dbo].[AbPressetext]
AS
	SELECT p.* FROM PresseText AS p
		INNER JOIN AufführgTabelle AS act ON p.AuffNr = act.AuffNr
		INNER JOIN Stücke AS st ON act.SNr = st.SNr
		INNER JOIN Gruppe AS gr ON st.ModuleGroupId = gr.GruppeID
		INNER JOIN UserGruppe AS ug ON gr.GruppeID = ug.GruppeID   
		INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
	WHERE ug.BerechtigungsbereichID = 2 AND UserPW.Username = CURRENT_USER

GO

-- berechtigungen alle auf public
GRANT SELECT ON AbPressetext TO public
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AbPressetext_Update'))
BEGIN
DROP VIEW [dbo].[AbPressetext_Update]
END
GO
CREATE VIEW [dbo].[AbPressetext_Update]
AS
	SELECT p.* FROM PresseText AS p
	WHERE p.AuffNr IS NULL OR p.AuffNr IN (
		SELECT act.AuffNr FROM AufführgTabelle AS act
			INNER JOIN Stücke AS st ON act.SNr = st.SNr
			INNER JOIN Gruppe AS gr ON st.ModuleGroupId = gr.GruppeID
			INNER JOIN UserGruppe AS ug ON gr.GruppeID = ug.GruppeID   
			INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
		WHERE ug.BerechtigungsbereichID = 2 AND ug.OptionsFlag & 2 = 2 AND UserPW.Username = CURRENT_USER
	)

	WITH CHECK OPTION

GO

-- berechtigungen alle auf public
GRANT SELECT, UPDATE, INSERT, DELETE ON AbPressetext_Update TO public
GO

--DH20150122 Neue Spalte Buchhaltungsdruckdatum für Bestellungen
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'AccountancyPrintDate' and o.id = OBJECT_ID(N'[dbo].[Orders]'))
BEGIN
	ALTER TABLE dbo.Orders ADD AccountancyPrintDate datetime NULL
END
go
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'CallOfTender' AND o.id = OBJECT_ID(N'[dbo].Orders') AND c.prec < 50)
BEGIN
	ALTER TABLE Orders ALTER COLUMN CallOfTender VARCHAR(50)
	--rolle 0 bereinigen, nur einmal, daher innerhalb von dem if hier
	UPDATE BesetzungStd SET Anzahl = 1 , Pos = 100000 WHERE ID = 0
	DELETE FROM BesetzungStdSoliTabelle WHERE RolleID = 0
END
GO

--DH 29.01.2015 Dynamische Verteiler nach Zusatzmerkmal

IF NOT EXISTS 
	(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('DistributionLists_Traits') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.DistributionLists_Traits ( 
				DistributionListTraitId INT IDENTITY(1,1) NOT NULL,--ID
				DistributionListId INT NOT NULL,
				TraitId INT NOT NULL,
				EntryType INT NOT NULL,				
				LC DATETIME NOT NULL,
				UC INT NOT NULL,
				LM DATETIME NOT NULL,
				UM INT NOT NULL
				CONSTRAINT PK_DistributionLists_Traits PRIMARY KEY CLUSTERED (DistributionListTraitId ASC) ON [PRIMARY],				
				CONSTRAINT FK_DistributionLists_Traits_DistributionList FOREIGN KEY (DistributionListId) REFERENCES dbo.Verteiler (VerteilerId) on delete Cascade,
				CONSTRAINT FK_DistributionLists_Traits_Traits FOREIGN KEY (TraitId) REFERENCES dbo.PersZusatzdatenTyp (PersZDTypID) on delete Cascade
		) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IX_DistributionLists_Traits_DistributionListId] ON [dbo].[DistributionLists_Traits] ([DistributionListId])
	CREATE NONCLUSTERED INDEX [IX_DistributionLists_Traits_TraitId] ON [dbo].[DistributionLists_Traits] ([TraitId])
	CREATE UNIQUE NONCLUSTERED INDEX [IX_DistributionLists_Traits_DistributionListIdTraitId] on [dbo].[DistributionLists_Traits] ([DistributionListId],[TraitId])
END
GO	
	GRANT SELECT ON dbo.DistributionLists_Traits TO [Public] 
	GRANT DELETE, INSERT, UPDATE ON dbo.DistributionLists_Traits TO [Stammdaten ändern]
	GRANT DELETE, INSERT, UPDATE ON dbo.DistributionLists_Traits TO [PersoAdmin]
	GRANT DELETE, INSERT, UPDATE ON dbo.DistributionLists_Traits TO [Konfigurieren]	
GO 

--2015-02-09 sh
--zustand für ressourcenobjekte
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('ResourceObjectConditions') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.ResourceObjectConditions ( 
				ResourceObjectConditionId INT IDENTITY(1,1) NOT NULL,--ID
				Name varchar(20) NOT NULL,
				Module INT NOT NULL,
				LC DATETIME NOT NULL,
				UC INT NOT NULL,
				LM DATETIME NOT NULL,
				UM INT NOT NULL
				CONSTRAINT PK_ResourceObjectConditions PRIMARY KEY CLUSTERED (ResourceObjectConditionId ASC) ON [PRIMARY],				
	) ON [PRIMARY]
END
GO	
GRANT SELECT ON dbo.ResourceObjectConditions TO [Public] 
GRANT DELETE, INSERT, UPDATE ON dbo.ResourceObjectConditions TO [Stammdaten ändern]
GRANT DELETE, INSERT, UPDATE ON dbo.ResourceObjectConditions TO [TheaOrchester]
GRANT DELETE, INSERT, UPDATE ON dbo.ResourceObjectConditions TO [WorksWrite]
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ResourceObjectConditionId' AND o.id = OBJECT_ID(N'[dbo].ResourceObjects'))
BEGIN
	ALTER TABLE dbo.ResourceObjects ADD ResourceObjectConditionId INT NULL
	CONSTRAINT FK_ResourceObjects_ResourceObjectConditions FOREIGN KEY (ResourceObjectConditionId)
	REFERENCES dbo.ResourceObjectConditions(ResourceObjectConditionId)

	ALTER TABLE dbo.ResourceObjects ADD IsInactive BIT CONSTRAINT DF_ResourceObjects_IsInactive  DEFAULT (0) NOT NULL
END
GO

--attachments für ressourcenobjekte
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.ResourceObjectAttachments') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.ResourceObjectAttachments (
			ResourceObjectAttachmentId INT IDENTITY(1,1) NOT NULL,--ID
			ResourceObjectId 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_ResourceObjectAttachments PRIMARY KEY CLUSTERED (ResourceObjectAttachmentId ASC) ON [PRIMARY]
			CONSTRAINT FK_ResourceObjectAttachments_ResourceObjects FOREIGN KEY (ResourceObjectId) REFERENCES dbo.ResourceObjects (ResourceObjectId) ON DELETE CASCADE, --wenn löschen im programm sauber implementiert kann das cascade weg
			CONSTRAINT FK_ResourceObjectAttachments_Attachments FOREIGN KEY (AttachmentId) REFERENCES dbo.[Attachments] (AttachmentId),
	) ON [PRIMARY]
	GRANT SELECT ON dbo.ResourceObjectAttachments TO [Public]
	GRANT DELETE ON dbo.ResourceObjectAttachments TO [TheaOrchester], [WorksWrite]
	GRANT INSERT ON dbo.ResourceObjectAttachments TO [TheaOrchester], [WorksWrite]
	GRANT UPDATE ON dbo.ResourceObjectAttachments TO [TheaOrchester], [WorksWrite]
END
GO

--xml daten für ressourcen
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ResourceData' AND o.id = OBJECT_ID(N'[dbo].Resources'))
BEGIN
	ALTER TABLE Resources ADD ResourceData VARCHAR(MAX)
END
GO

--tabelle für die notentypen
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.ScoreTypes') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.ScoreTypes (
			ScoreTypeId INT IDENTITY(1,1) NOT NULL,--ID
			Name VARCHAR(30) NOT NULL, 
			ShortName VARCHAR(10) NULL,
			Sort INT NOT NULL CONSTRAINT DF_ScoreTypes_Sort DEFAULT(0),
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL,
			CONSTRAINT PK_ScoreTypes PRIMARY KEY CLUSTERED (ScoreTypeId ASC) ON [PRIMARY]
	) ON [PRIMARY]
	GRANT SELECT ON dbo.ScoreTypes TO [Public]
	GRANT DELETE ON dbo.ScoreTypes TO [TheaOrchester], [WorksWrite]
	GRANT INSERT ON dbo.ScoreTypes TO [TheaOrchester], [WorksWrite]
	GRANT UPDATE ON dbo.ScoreTypes TO [TheaOrchester], [WorksWrite]
END
GO

--zuordnungstabelle zw. notentypen und ressourcentypen
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.ScoreTypes_ResourceTypes') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.ScoreTypes_ResourceTypes (
			ScoreTypeResourceTypeId INT IDENTITY(1,1) NOT NULL,--ID
			ScoreTypeId INT NOT NULL,
			ResourceTypeId INT NOT NULL,
			Sort INT NOT NULL CONSTRAINT DF_ScoreTypes_ResourceTypes_Sort DEFAULT(0),
			IsOptional BIT NOT NULL CONSTRAINT DF_ScoreTypes_ResourceTypes_IsOptional DEFAULT(0),
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL,
			CONSTRAINT PK_ScoreTypes_ResourceTypes PRIMARY KEY CLUSTERED (ScoreTypeResourceTypeId ASC) ON [PRIMARY],
			CONSTRAINT FK_ScoreTypes_ResourceTypes_ScoreTypes FOREIGN KEY (ScoreTypeId) REFERENCES dbo.ScoreTypes (ScoreTypeId) ON DELETE CASCADE,
			CONSTRAINT FK_ScoreTypes_ResourceTypes_ResourceTypes FOREIGN KEY (ResourceTypeId) REFERENCES dbo.ResourceTypes (ResourceTypeId) ON DELETE CASCADE,
	) ON [PRIMARY]
	GRANT SELECT ON dbo.ScoreTypes_ResourceTypes TO [Public]
	GRANT DELETE ON dbo.ScoreTypes_ResourceTypes TO [TheaOrchester], [WorksWrite]
	GRANT INSERT ON dbo.ScoreTypes_ResourceTypes TO [TheaOrchester], [WorksWrite]
	GRANT UPDATE ON dbo.ScoreTypes_ResourceTypes TO [TheaOrchester], [WorksWrite]
	CREATE NONCLUSTERED INDEX IX_ScoreTypes_ResourceTypes_ScoreTypeId ON dbo.ScoreTypes_ResourceTypes (ScoreTypeId)  ON [PRIMARY]
	CREATE NONCLUSTERED INDEX IX_ScoreTypes_ResourceTypes_ResourceTypeId ON dbo.ScoreTypes_ResourceTypes (ResourceTypeId)  ON [PRIMARY]
END
GO

--einstellungen der notenkategorien von xml in tabellenstruktur umwandeln
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 151 OR (Minor = 151 AND Build < 9))))
	AND EXISTS(SELECT s.WertText FROM Settings s WHERE s.Form = 'WorkDetailsView' AND s.Control = 'NotesPageSettings' AND s.WertText IS NOT NULL AND s.WertText <> ''))
BEGIN

	DECLARE @XML XML
	SELECT @XML = werttext FROM Settings s  WHERE s.Form = 'WorkDetailsView' AND s.Control = 'NotesPageSettings'

	DECLARE @ResourceTypeDataTable TABLE(
		CategoryIndex INT,
		CategoryName VARCHAR(40),
		CategorySort INT,
		ResourceTypeId INT,
		ResourceTypeSort INT
	)

	INSERT INTO @ResourceTypeDataTable
	SELECT
		NoteResources.rt.value('for $i in . return count($i/../../../*[. << $i])', 'int') AS CategoryIndex, --position der notecategory node
		NoteResources.rt.value('../../@Name', 'varchar(40)') AS CategoryName,
		NoteResources.rt.value('../../@OrderIndex', 'int') AS CategorySort,
		NoteResources.rt.value('@ResourceTypeId', 'int') AS ResourceTypeId,
		NoteResources.rt.value('@OrderIndex', 'int') AS ResourceTypeSort	
	FROM @XML.nodes('//WorkVersionNotesPageSettings/NoteCategories/NoteCategory/AvailableResourceTypes/ResourceType') AS NoteResources(rt)

	DECLARE @now AS DATETIME
	SET @now = GETDATE()

	SET IDENTITY_INSERT [dbo].[ScoreTypes] ON;
	INSERT INTO [dbo].[ScoreTypes] (ScoreTypeId, Name, Sort, LC, UC, LM, UM)
	SELECT DISTINCT rtd.CategoryIndex, rtd.CategoryName, rtd.CategorySort, @now, 0, @now,0 FROM @ResourceTypeDataTable rtd
	SET IDENTITY_INSERT [dbo].[ScoreTypes] OFF;

	INSERT INTO [dbo].[ScoreTypes_ResourceTypes] (ScoreTypeId, ResourceTypeId, Sort, LC, UC, LM, UM)
	SELECT 	rtd.CategoryIndex, rtd.ResourceTypeId, rtd.ResourceTypeSort, @now, 0, @now, 0 FROM @ResourceTypeDataTable rtd
END
GO

--2015-02-13 sh
--tabelle für ressourcenobjektverleih
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.ResourceObjectBorrows') AND OBJECTPROPERTY(id,'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.ResourceObjectBorrows (
			ResourceObjectBorrowId INT IDENTITY(1,1) NOT NULL,--ID
			ResourceObjectId INT NOT NULL,
			PersonId INT NOT NULL,
			StartDate DATETIME NOT NULL,
			EndDate DATETIME NULL,
			Note VARCHAR(100) NULL,
			LC DATETIME NOT NULL,
			UC INT NOT NULL,
			LM DATETIME NOT NULL,
			UM INT NOT NULL,
			CONSTRAINT PK_ResourceObjectBorrows PRIMARY KEY CLUSTERED (ResourceObjectBorrowId ASC) ON [PRIMARY],
			CONSTRAINT FK_ResourceObjectBorrows_ResourceObjects FOREIGN KEY (ResourceObjectId) REFERENCES dbo.ResourceObjects (ResourceObjectId),
			CONSTRAINT FK_ResourceObjectBorrows_Persons FOREIGN KEY (PersonId) REFERENCES dbo.PersH (PersNr)
	) ON [PRIMARY]
	GRANT SELECT ON dbo.ResourceObjectBorrows TO [Public]
	GRANT DELETE ON dbo.ResourceObjectBorrows TO [TheaOrchester], [WorksWrite]
	GRANT INSERT ON dbo.ResourceObjectBorrows TO [TheaOrchester], [WorksWrite]
	GRANT UPDATE ON dbo.ResourceObjectBorrows TO [TheaOrchester], [WorksWrite]
	CREATE NONCLUSTERED INDEX IX_ResourceObjectBorrows_ResourceObjectId ON dbo.ResourceObjectBorrows (ResourceObjectId)  ON [PRIMARY]
	CREATE NONCLUSTERED INDEX IX_ResourceObjectBorrows_PersonId ON dbo.ResourceObjectBorrows (PersonId)  ON [PRIMARY]
	CREATE NONCLUSTERED INDEX IX_ResourceObjectBorrows_StartDate ON dbo.ResourceObjectBorrows (StartDate)  ON [PRIMARY]
	CREATE NONCLUSTERED INDEX IX_ResourceObjectBorrows_EndDate ON dbo.ResourceObjectBorrows (EndDate)  ON [PRIMARY]
END
GO

GO
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 151 OR (Minor = 151 AND Build < 3)))))
BEGIN
	UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 128 WHERE BerechtigungsbereichID = 0 AND UserID IN (
	(SELECT p.UserID FROM [dbo].[sysusers] AS u
	INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
	INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
	INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
	WHERE g.name = 'Honorar-Daten ändern' OR g.name = 'db_owner'))
END
BEGIN
	UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 64 WHERE BerechtigungsbereichID = 0 AND UserID IN (
	(SELECT p.UserID FROM [dbo].[sysusers] AS u
	INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
	INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
	INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
	WHERE g.name = 'Honorar-Daten lesen' OR g.name = 'db_owner'))
END
GO

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsCostUnitAsignable' AND o.id = OBJECT_ID(N'[dbo].Zulagen') AND c.xtype = 104)
BEGIN
	exec DeleteConstraint 'Zulagen','IsCostUnitAsignable'
	UPDATE Zulagen  SET  IsCostUnitAsignable = 0 WHERE  IsCostUnitAsignable IS NULL
	ALTER TABLE [dbo].Zulagen ALTER COLUMN IsCostUnitAsignable INT NOT NULL
	ALTER TABLE [dbo].Zulagen ADD CONSTRAINT DF_Bonus_IsCostUnitAssignable DEFAULT (0) FOR IsCostUnitAsignable
END

GO

-- 2015.02.11 dh
-- Personenhonorardaten: Berechtigung umgestellt auf View und Optionsflag
IF  NOT EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'FK_PersonalHonorar_PersH'))
	BEGIN
ALTER TABLE [dbo].[PersonalHonorar]  WITH CHECK ADD  CONSTRAINT [FK_PersonalHonorar_PersH] FOREIGN KEY([Persnr])
REFERENCES [dbo].[PersH] ([PersNr]) ON DELETE CASCADE
ALTER TABLE [dbo].[PersonalHonorar] CHECK CONSTRAINT [FK_PersonalHonorar_PersH]
	END

GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbPersonalHonorar'))
DROP VIEW [dbo].[AbPersonalHonorar]
GO
CREATE VIEW dbo.AbPersonalHonorar
AS 
SELECT ph.* FROM dbo.PersonalHonorar ph 
	INNER JOIN dbo.PersH p		ON p.PersNr = ph.Persnr 
	INNER JOIN Gruppe g			ON p.Besitzer = g.GruppeID
	INNER JOIN UserGruppe AS ug ON g.GruppeID = ug.GruppeID 
	INNER JOIN UserPW AS up		ON ug.Userid = up.Userid 
	WHERE up.Username = CURRENT_USER AND ug.BerechtigungsbereichID = 0 AND ((OptionsFlag & 64) = 64 OR  (OptionsFlag & 128) = 128)

GO
GRANT SELECT ON AbPersonalHonorar TO public
GO

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

GO
CREATE VIEW [dbo].[AbPersonalHonorar_Update]
AS 
SELECT  ph.* FROM dbo.PersonalHonorar ph 
		INNER JOIN dbo.PersH p ON p.PersNr=ph.Persnr 
		INNER JOIN	(SELECT DISTINCT GruppeID, Schreiben 
						FROM			UserGruppe 
						INNER JOIN		UserPW		ON UserGruppe.Userid = UserPW.Userid 
						WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 0 AND (OptionsFlag & 128) = 128) AS UG
				ON p.Besitzer = UG.GruppeID
GO

GRANT SELECT ON AbPersonalHonorar_Update TO public
GRANT DELETE ON AbPersonalHonorar_Update TO public
GRANT INSERT ON AbPersonalHonorar_Update TO public
GRANT UPDATE ON AbPersonalHonorar_Update TO public

GO
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 151 OR (Minor = 151 AND Build < 3)))))
BEGIN
	UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 128 WHERE BerechtigungsbereichID = 0 AND UserID IN (
	(SELECT p.UserID FROM [dbo].[sysusers] AS u
	INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
	INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
	INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
	WHERE g.name = 'Honorar-Daten ändern' OR g.name = 'db_owner'))
END
BEGIN
	UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 64 WHERE BerechtigungsbereichID = 0 AND UserID IN (
	(SELECT p.UserID FROM [dbo].[sysusers] AS u
	INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
	INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
	INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
	WHERE g.name = 'Honorar-Daten lesen' OR g.name = 'db_owner'))
END
GO

-- 2015.02.13 BM
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'WebLockDatum' and o.id = OBJECT_ID(N'dbo.Gruppe'))
BEGIN
	ALTER TABLE dbo.Gruppe ADD WebLockDatum DATETIME NULL;
END
GO
--TK 2015-02-18
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'QuoteNo' AND o.id = OBJECT_ID(N'dbo.Orders') AND c.prec < 40)
BEGIN
	ALTER TABLE dbo.Orders ALTER COLUMN QuoteNo VARCHAR(40)
END
GO

-- 2015.02.18 BM
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetActvityTypeNumbersPerformance'))	DROP FUNCTION dbo.GetActvityTypeNumbersPerformance

GO
CREATE FUNCTION dbo.GetActvityTypeNumbersPerformance(@start DATETIME, @end DATETIME, @variantFilter varchar(400))
		RETURNS TABLE
		AS
			RETURN (SELECT Row_Number() OVER (PARTITION BY Snr ORDER BY Datum, CONVERT(CHAR(5), UhrBeg, 108)) as RowNum, AuffNr, Datum, SNr, ZRNr
			FROM AufführgTabelle AS A
				INNER JOIN AuffTypen AS AT ON AT.AuffTnr = A.Art
			WHERE KatNr = 0 AND Datum BETWEEN @start AND @end AND ([status] BETWEEN 0 AND 99 OR [status] IN (SELECT OrderID FROM SplitIds(@variantFilter))))
GO
	GRANT SELECT ON GetActvityTypeNumbersPerformance TO Public
GO


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetActvityTypeNumbersRehearsal')) 	DROP FUNCTION dbo.GetActvityTypeNumbersRehearsal
GO
CREATE FUNCTION dbo.GetActvityTypeNumbersRehearsal(@start DATETIME, @end DATETIME, @variantFilter varchar(400))
		RETURNS TABLE
		AS
			RETURN (SELECT Row_Number() OVER (PARTITION BY Art, Snr ORDER BY Datum, CONVERT(CHAR(5), UhrBeg, 108)) as RowNum, AuffNr, Datum, SNr, ZRNr
			FROM AufführgTabelle AS A
			INNER JOIN AuffTypen AS AT ON AT.AuffTnr = A.Art 
		WHERE AT.LfdNr = 1 AND Datum BETWEEN @start AND @end AND ([status] BETWEEN 0 AND 99 OR [status] IN (SELECT OrderID FROM SplitIds(@variantFilter))))

GO
GRANT SELECT ON GetActvityTypeNumbersRehearsal TO PUBLIC
GO

--TK 20150223 ContractId hinzugefügt
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.CastActiveties')) DROP VIEW dbo.CastActiveties
GO
CREATE VIEW dbo.CastActiveties
AS
SELECT	ac.Datum, ac.UhrBeg, ac.UhrEnde, ac.SNr, ac.ZrNr, ac.Art, ac.Bem, at.KatNr, ac.Status,
		ca.*

FROM	AufführgTabelle ac
		INNER JOIN AuffTypen		AS at	ON ac.art = at.AuffTNr
		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
		LEFT 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
		LEFT  JOIN BesetzungStd		AS ro	ON ca.RolleID = ro.ID
		INNER  JOIN Gruppe			AS g	ON g.GruppeID = ro.Besitzer	
		INNER  JOIN UserGruppe		AS ugc	ON G.GruppeID = 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)) OR ca.RolleId IS NULL)
GO
GRANT SELECT ON CastActiveties TO PUBLIC
GO

--BM20150219
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].Settings'))
BEGIN
	IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 146 OR (Minor = 146 AND Build < 103)))))
	BEGIN
		DELETE FROM Settings  WHERE Form = N'Benutzereinstellungen' AND Control = N'ActivityContextMenuUserSettings' AND Pref IS NULL
		DELETE FROM Settings  WHERE Form = N'System' AND Control = N'ActivityContextMenuGlobalSettings' AND Pref IS NULL
		DELETE FROM Settings  WHERE Form = N'SettingsView' AND Control = N'ActivityContextMenuSettingsControl' AND Pref IS NULL
		IF EXISTS (SELECT * FROM Fahrplan)
		BEGIN
			INSERT INTO [Settings] (
				Form,
				[control],
				WertText,
				IsGlobalSetting
			) VALUES (
				'SettingsView',
				'ActivityContextMenuSettingsControl',
				'<ActivityContextMenuSettings><SelectedMenuItems><string>TimetableActivityControl</string><string>ActivityCast</string><string>ActivityHistory</string></SelectedMenuItems></ActivityContextMenuSettings>',
				1
			)
		END
		ELSE
			INSERT INTO [Settings] (
				Form,
				[control],
				WertText,
				IsGlobalSetting
			) VALUES (
				'SettingsView',
				'ActivityContextMenuSettingsControl',
				'<ActivityContextMenuSettings><SelectedMenuItems><string>ActivityCast</string><string>ActivityHistory</string></SelectedMenuItems></ActivityContextMenuSettings>',
				1
			)
	END
END
GO

--am 26.02.2015 aus release gemerged, da bugfixes
--BM 20150224
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_SubscriptionActivityCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_SubscriptionActivityCount
GO
CREATE PROCEDURE [dbo].sp_SubscriptionActivityCount
	@subscriptionId int,
	@date  Datetime, 
	@seasonStart  Datetime
As
	SELECT COUNT(*)
	FROM AufR as AR
		INNER JOIN Aufführgtabelle AS A ON AR.AuffNr = A.AuffNr 
	WHERE AboNr = @subscriptionId AND Datum <= @date AND Datum >= @seasonStart AND [Status] BETWEEN 0 AND 99
GO

GRANT EXECUTE ON sp_SubscriptionActivityCount TO Public
GO

--2015-02-24 sh
--bugfix im View ActivitiesUpdate, der letzte JOIN auf UserPW mit einem LEFT JOIN statt INNER JOIN, da der Benutzer vlt. schon nicht mehr existiert
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.[ActivitiesUpdate]'))
BEGIN
	DROP VIEW dbo.[ActivitiesUpdate]
END
GO
CREATE VIEW [dbo].[ActivitiesUpdate]
AS
SELECT * 
FROM AufführgTabelle AS Auff 
WHERE Auff.Datum <= dbo.AccessDatum() 
AND AuffNr IN(
			SELECT DISTINCT A.AuffNr FROM AufführgTabelle AS A
			INNER JOIN AuffTypen AS AT ON AT.AuffTNr = A.Art
			LEFT JOIN ActivityTypeGroups AS ATG ON AT.AuffTNr = ATG.ActivityTypeId
			LEFT JOIN Stücke AS S ON S.Snr = A.SNr
			LEFT JOIN UserGruppe AS UGBB2 ON UGBB2.BerechtigungsbereichID = 2 AND UGBB2.schreiben <> 0 AND UGBB2.GruppeID = S.ModuleGroupId
			LEFT JOIN UserPW AS UPBB2 ON UGBB2.Userid  = UPBB2.Userid AND UPBB2.Username = CURRENT_USER
			LEFT JOIN UserGruppe AS UGBB9 ON UGBB9.BerechtigungsbereichID = 9 AND UGBB9.schreiben <> 0 AND UGBB9.GruppeID = ATG.GroupId
			LEFT JOIN UserPW AS UPBB9 ON UGBB9.Userid  = UPBB9.Userid AND UPBB9.Username = CURRENT_USER
			LEFT JOIN UserPW AS UPC ON UPC.UserId = A.UC
			WHERE (ATG.ActivityTypeId IS NULL OR UPBB9.UserID IS NOT NULL) AND UPBB2.UserID IS NOT NULL OR 
			(1 = IS_MEMBER('db_owner') OR (UPC.UserName = CURRENT_USER))
		)
WITH CHECK OPTION
GO

GRANT SELECT ON [ActivitiesUpdate] TO Public
GRANT INSERT ON [ActivitiesUpdate] TO Public
GRANT DELETE ON [ActivitiesUpdate] TO Public
GRANT UPDATE ON [ActivitiesUpdate] TO Public
GO
--TK20150223 wird nicht mehr verwendet
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.CastActivetiesPublic')) DROP VIEW dbo.CastActivetiesPublic
GO


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

GO
CREATE VIEW dbo.AbPersonalDetails
AS
	SELECT  PersonalDetails.*  FROM  PersonalDetails 
		INNER JOIN PersH ON PersonalDetails.PNr = Persh.PersNr
		INNER JOIN UserGruppe AS UG ON PersH.Besitzer =  UG.GruppeID 
		INNER JOIN UserPW ON Ug.Userid = UserPW.Userid 
	WHERE  ug.BerechtigungsbereichID = 0 AND (ug.OptionsFlag & 4 = 4 OR ug.OptionsFlag & 8 = 8) AND Username = CURRENT_USER
GO

GRANT SELECT ON AbPersonalDetails TO public
GO

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

GO
CREATE VIEW dbo.AbPersonalDetails_Update
AS
	SELECT  PersonalDetails.*  FROM  PersonalDetails WHERE Pnr 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.Schreiben=1 AND  ug.BerechtigungsbereichID = 0 AND ug.OptionsFlag & 8 = 8 AND Username = CURRENT_USER GROUP BY pp.PersNr)

	WITH CHECK OPTION
GO

GRANT SELECT ON AbPersonalDetails_Update TO public
GRANT DELETE ON AbPersonalDetails_Update TO public
GRANT INSERT ON AbPersonalDetails_Update TO public
GRANT UPDATE ON AbPersonalDetails_Update TO public
GO


IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbBankAccount'))
	DROP VIEW dbo.AbBankAccount
GO
CREATE VIEW dbo.AbBankAccount
AS
	SELECT  BankAccount.*  FROM  BankAccount 
		INNER JOIN PersH ON BankAccount.PersID = Persh.PersNr
		INNER JOIN UserGruppe AS UG ON PersH.Besitzer =  UG.GruppeID 
		INNER JOIN UserPW ON Ug.Userid = UserPW.Userid 
	WHERE  ug.BerechtigungsbereichID = 0 AND (ug.OptionsFlag & 16 = 16 OR ug.OptionsFlag & 32 = 32) AND Username = CURRENT_USER
GO
GRANT SELECT ON AbBankAccount TO public
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbBankAccount_Update'))
	DROP VIEW dbo.AbBankAccount_Update
GO
CREATE VIEW dbo.AbBankAccount_Update
AS
	SELECT  BankAccount.*  FROM  BankAccount WHERE PersID 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 & 32 = 32 AND Username = CURRENT_USER GROUP BY pp.PersNr)

	WITH CHECK OPTION
GO

GRANT SELECT ON AbBankAccount_Update TO public
GRANT DELETE ON AbBankAccount_Update TO public
GRANT INSERT ON AbBankAccount_Update TO public
GRANT UPDATE ON AbBankAccount_Update TO public
GO

IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 151 OR (Minor = 151 AND Build < 101)))))
BEGIN
UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 40 WHERE IsInherited = 0 AND BerechtigungsbereichID = 0 AND Schreiben = 1 AND UserID IN (
		(SELECT p.UserID FROM [dbo].[sysusers] AS u
			INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
			INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
			INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
		WHERE (g.name = 'Soli-Daten ändern' OR g.name = 'db_owner') AND g.issqlrole = 1))

exec InheritPermissions

UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 40, IsInherited = 0 WHERE IsInherited = 1 AND OptionsFlag & 40 <> 40 AND BerechtigungsbereichID = 0 AND Schreiben = 1 AND UserID IN (
		(SELECT p.UserID FROM [dbo].[sysusers] AS u
			INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
			INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
			INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
		WHERE (g.name = 'Soli-Daten ändern' OR g.name = 'db_owner') AND g.issqlrole = 1))

UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 20 WHERE IsInherited = 0 AND BerechtigungsbereichID = 0 AND UserID IN (
		(SELECT p.UserID FROM [dbo].[sysusers] AS u
			INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
			INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
			INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
		WHERE (g.name = 'Soli-Daten lesen' OR g.name = 'Soli-Daten ändern' OR g.name = 'db_owner') AND g.issqlrole = 1))

exec InheritPermissions

UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 20, IsInherited = 0 WHERE IsInherited = 1 AND OptionsFlag & 20 <> 20 AND BerechtigungsbereichID = 0 AND UserID IN (
		(SELECT p.UserID FROM [dbo].[sysusers] AS u
			INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
			INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
			INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
		WHERE (g.name = 'Soli-Daten lesen' OR g.name = 'Soli-Daten ändern' OR g.name = 'db_owner') AND g.issqlrole = 1))
END
GO

GRANT DELETE, INSERT, UPDATE ON WageType TO PersoAdmin
GO

--2015-02-10 dh
--Benutzergruppen auf aktiv setzen, da Inaktiv keine Auswirkung hat.
update dbo.userpw set isinactive=0 where isgroup=1 and isinactive=1;
GO

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

GO
CREATE VIEW dbo.AbPersDataCategoryData
AS
	SELECT  PersDataCategoryData.*  FROM  PersDataCategoryData 
		INNER JOIN PersH ON PersDataCategoryData.PersonId = Persh.PersNr
		INNER JOIN UserGruppe AS UG ON PersH.Besitzer =  UG.GruppeID 
		INNER JOIN UserPW ON Ug.Userid = UserPW.Userid 
	WHERE  ug.BerechtigungsbereichID = 0 AND (ug.OptionsFlag & 4 = 4 OR ug.OptionsFlag & 8 = 8) AND Username = CURRENT_USER 
GO
GRANT SELECT ON AbPersDataCategoryData TO public
GO

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

GO
CREATE VIEW dbo.AbPersDataCategoryData_Update
AS
	SELECT  PersDataCategoryData.*  FROM  PersDataCategoryData WHERE 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 & 8 = 8 AND Username = CURRENT_USER GROUP BY pp.PersNr)

	WITH CHECK OPTION
GO

GRANT SELECT ON AbPersDataCategoryData_Update TO public
GRANT DELETE ON AbPersDataCategoryData_Update TO public
GRANT INSERT ON AbPersDataCategoryData_Update TO public
GRANT UPDATE ON AbPersDataCategoryData_Update TO public
GO
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 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



-- DH 20150223 -- Umstellen der Planpermissionsettings auf View und OptionsFlag
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbPlanPermissionSettings]'))
BEGIN
DROP VIEW [dbo].[AbPlanPermissionSettings]
END
GO
CREATE VIEW [dbo].[AbPlanPermissionSettings]
AS
	SELECT pps.* FROM [dbo].[PlanPermissionSettings] AS PPS
		INNER JOIN dbo.[Plan] AS P ON pps.PlanId=p.PlanID		
		INNER JOIN Gruppe AS gr ON p.OwnerId = gr.GruppeID
		INNER JOIN UserGruppe AS ug ON gr.GruppeID = ug.GruppeID   
		INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
	WHERE ug.BerechtigungsbereichID = 11 AND ug.OptionsFlag & 1 = 1 AND UserPW.Username = CURRENT_USER

GO
print 'ABPlanPermissionSettings'
-- berechtigungen alle auf public
GRANT SELECT ON AbPlanPermissionSettings TO public
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AbPlanPermissionSettings_Update'))
BEGIN
DROP VIEW [dbo].[AbPlanPermissionSettings_Update]
END
GO
CREATE VIEW [dbo].[AbPlanPermissionSettings_Update]
AS
	SELECT pps.* FROM [dbo].[PlanPermissionSettings] AS PPS WHERE PPS.PlanId IN (
		SELECT P.PlanId FROM dbo.[Plan] AS P		
		INNER JOIN Gruppe AS gr ON p.OwnerId = gr.GruppeID
		INNER JOIN UserGruppe AS ug ON gr.GruppeID = ug.GruppeID   
		INNER JOIN UserPW ON Ug.Userid  = UserPW.Userid 
		WHERE ug.BerechtigungsbereichID = 11 AND ug.OptionsFlag & 2 = 2 AND UserPW.Username = CURRENT_USER)
	

	WITH CHECK OPTION

GO
print 'ABPlanPermissionSettings_Update'
-- berechtigungen alle auf public
GRANT SELECT, UPDATE, INSERT, DELETE ON [AbPlanPermissionSettings_Update] TO public
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbPlanConfiguration]'))
BEGIN
/****** Object:  View [dbo].[AbPlanConfiguration]    Script Date: 23.02.2015 12:43:46 ******/
	DROP VIEW [dbo].[AbPlanConfiguration]
END
GO

/****** Object:  View [dbo].[AbPlanConfiguration]    Script Date: 23.02.2015 12:43:46 ******/

CREATE VIEW [dbo].[AbPlanConfiguration]
AS 
SELECT  p.* FROM [Plan] AS p
WHERE p.OwnerId IN (
		SELECT UserGruppe.GruppeID 
		FROM UserGruppe INNER JOIN UserPW ON UserGruppe.Userid = UserPW.Userid 
		WHERE UserPW.Username = CURRENT_USER AND BerechtigungsbereichID = 11
	)
	OR p.PlanID IN (
		SELECT pps.PlanId FROM PlanPermissionSettings pps
		INNER JOIN UserPw g ON pps.UserId = g.UserID
		INNER JOIN UsersInGroups uig ON g.UserId = uig.GroupId  
		INNER JOIN UserPw u on uig.UserId = u.UserID AND u.UserName = CURRENT_USER
	)
WITH CHECK OPTION
GO

GRANT SELECT ON [dbo].[AbPlanConfiguration] TO public
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbPlanConfiguration_Update]'))
/****** Object:  View [dbo].[AbPlanConfiguration_Update]    Script Date: 23.02.2015 12:44:14 ******/
BEGIN
	DROP VIEW [dbo].[AbPlanConfiguration_Update]
END
GO

CREATE VIEW [dbo].[AbPlanConfiguration_Update]
AS 
SELECT  p.*  FROM  [Plan]  AS p WHERE p.OwnerId IN
	   (SELECT UserGruppe.GruppeID 
		FROM UserGruppe INNER JOIN UserPW ON UserGruppe.Userid = UserPW.Userid 
		WHERE UserPW.Username = CURRENT_USER AND BerechtigungsbereichID = 11 AND UserGruppe.Schreiben = 1)
WITH CHECK OPTION
GO

GRANT SELECT, UPDATE, DELETE, INSERT ON [dbo].[AbPlanConfiguration_Update] TO public
GO
-- Usergruppen mit Schreibrechten auf Planfreigaben versehen , wenn sie schon Schreibrechte haben 
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 151 OR (Minor = 151 AND Build < 10)))))
BEGIN
	update UserGruppe
	set OptionsFlag= OptionsFlag | 3
	WHERE BerechtigungsbereichID = 11
			AND UserGruppe.Schreiben = 1
END

GO

--aus release merge vom 2015-03-09
--2015-03-06 sh
-- converterfeld umbenannt, da name irreführend und nicht passend zum rest
UPDATE PlanSort SET ContentPath = 'workingTimeHoursSum' WHERE ContentPath = 'setupTimeHoursSum'
GO

--2015-03-12 sh
--index auf planart da oft danach gefiltert abgefragt wird
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[Plan]')  AND name = 'IX_Plan_PlanArt')
CREATE NONCLUSTERED INDEX IX_Plan_PlanArt ON dbo.[Plan]
	(PlanArt) ON [PRIMARY] 
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o ON c.id = o.id
	WHERE c.name = 'CostUnitId' AND o.id = OBJECT_ID('dbo.VertragRolle'))
BEGIN
	ALTER TABLE VertragRolle ADD CostUnitId INT NULL
	ALTER TABLE [dbo].VertragRolle  WITH CHECK ADD  CONSTRAINT [FK_ContractRole_CostUnit] FOREIGN KEY(CostUnitId)
		REFERENCES [dbo].CostUnit (CostUnitId) ON DELETE SET NULL
END
GO
-- Feiertagsformeln zusammenfassen

-- Wochen Feiertag mit Ausgleich
print 'WoFtMitAusgleich'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>Wochenfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra>false</SundayExtra>
	</HolidayTimeSetting>
	<HolidayTimeSetting>
	  <HolidayType>Sonntagsfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra xsi:nil="true" />
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut>false</PaidOut>
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1270 ;
-- Wochen Feiertag ohne Ausgleich
print 'WoFtOhneAusgleich'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>Wochenfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra>false</SundayExtra>
	</HolidayTimeSetting>
	<HolidayTimeSetting>
	  <HolidayType>Sonntagsfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra xsi:nil="true" />
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut>true</PaidOut>
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1271; 

-- Wochen Feiertag am Sonntag mit Ausgleich
print 'WoFtAmSoMitAusgleich'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>Wochenfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra>true</SundayExtra>
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut>false</PaidOut>
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1272 ;
-- Wochen Feiertag am Sonntag mit Ausgleich
print 'WoFtAmSoOhneAusgleich'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>Wochenfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra>true</SundayExtra>
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut>true</PaidOut>
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1273 ;

-- Vor Feiertag ohne Ausgleich
print 'VorFtOhneAusgleich'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>VorFeiertagSteuerfrei</HolidayType>
	  <TimeRange>
		<Start>'+SUBSTRING(Settings,CHARINDEX('<Start>',Settings)+7,CHARINDEX('</start>',Settings) - CHARINDEX('<start>',Settings)-7)+'</Start>
		<End>'+SUBSTRING(Settings,CHARINDEX('<End>',Settings)+5,CHARINDEX('</End>',Settings) - CHARINDEX('<End>',Settings)-5) +'</End>
	  </TimeRange>
	  <SundayExtra xsi:nil="true" />
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut>false</PaidOut>
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1274 and settings is not null;
-- Vor Feiertag mit Ausgleich
print 'VorFtMitAusgleich'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>VorFeiertagSteuerfrei</HolidayType>
	  <TimeRange>
		<Start>'+SUBSTRING(Settings,CHARINDEX('<Start>',Settings)+7,CHARINDEX('</start>',Settings) - CHARINDEX('<start>',Settings)-7)+'</Start>
		<End>'+SUBSTRING(Settings,CHARINDEX('<End>',Settings)+5,CHARINDEX('</End>',Settings) - CHARINDEX('<End>',Settings)-5) +'</End>
	  </TimeRange>
	  <SundayExtra xsi:nil="true" />
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut>false</PaidOut>
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1275 and settings is not null;

-- Arbeit an Feiertag(Zeit)
print 'WorkingOnHolidayMinutes'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>Wochenfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra xsi:nil="true" />
	</HolidayTimeSetting>
	<HolidayTimeSetting>
	  <HolidayType>Sonntagsfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra xsi:nil="true" />
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut xsi:nil="true" />
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1276 ;

-- Wochenfeiertagsstunden
print 'WeekHoliday'
update Zeitkonten
set formel = 1278,
	Settings='<?xml version="1.0" encoding="utf-8"?>
<HolidaySettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/
Schema">
  <HolidayTimesettings>
	<HolidayTimeSetting>
	  <HolidayType>Wochenfeiertag</HolidayType>
	  <TimeRange>
		<Start>0</Start>
		<End>1440</End>
	  </TimeRange>
	  <SundayExtra xsi:nil="true" />
	</HolidayTimeSetting>
  </HolidayTimesettings>
  <PaidOut>false</PaidOut>
  <HolidayEndsOnMidnight>true</HolidayEndsOnMidnight>
  <HolidayStartsOnMidnight>false</HolidayStartsOnMidnight>
</HolidaySettings>'
where formel = 1277 ;



--Globale settings als solche kennzeichen Settings 
UPDATE Settings SET IsGlobalSetting = 1 Where IsGlobalSetting = 0 AND (
	Form IN ('frmExport', 'MailingConfig','budget', 'CashCommands', 'DBSetting','Adresse', 'frmWerk','Application','CategoryColors','Files', 'Path', 'BasicCast', 'UpdateLog','Contracts', 'Import', 'ContractExportDefinition', 'PrintCastMatrixReportSettings','AddCashCommand','PrintSubscriptionPrices','PrintActivityCast','BasicCastPrint','WebCalendarProfiles','Export','LohnExport.BSO')
	OR (Form = 'DispoSettings' AND Control <> 'UseDailyScheduleInterface')
	OR (Form = 'DutyRoster' AND Control <> 'HighlightDutiesWithWarnings' AND Control <> 'MailParameter' AND Control <> 'SendMailAccount')
	OR ([Form] = 'General' AND [Control] = 'DatabaseIdentifier') 
	OR ([Form] = 'AttachmentService' AND [Control] = 'BasicSettings')
)

--Doppelte Globale löschen
DELETE FROM Settings 
	WHERE IsGlobalSetting = 1 AND 
	SID NOT IN (
		SELECT MIN(SID) FROM Settings 
		WHERE IsGlobalSetting = 1
		GROUP BY Form, Control, Pref )

GO

--2015-03-26 sh
--alte sichten entfernen
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].Sicht1'))
BEGIN
	DROP VIEW [dbo].Sicht1
END
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].Sicht2'))
BEGIN
	DROP VIEW [dbo].Sicht2
END
GO
	
--fehlende beziehung zw. person und perskat (aufgetreten in bonn)
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_PersH_PersKat]') AND type = 'F')
BEGIN
	ALTER TABLE [dbo].[PersH]  WITH CHECK ADD  CONSTRAINT [FK_PersH_PersKat] FOREIGN KEY([PersKatNr])
	REFERENCES [dbo].[PersKat] ([PersKatNr])
END
GO

--2015-04-27 sh
--bugfix der views AbContractBonus (mehrfach denselben bonus ausgegeben) und AbContractBonus_Update (löschen ncht möglich)
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AbContractBonus'))
BEGIN
	DROP VIEW [dbo].AbContractBonus
END
GO

CREATE VIEW [dbo].AbContractBonus
AS 
SELECT v.* FROM  ContractBonus AS v WHERE v.ContractId IN (  
	SELECT Vertrag.VertragsNr FROM Vertrag
	INNER JOIN  UserGruppe ON Vertrag.ModulGruppeID = UserGruppe.GruppeID
	INNER JOIN	UserPW ON UserGruppe.Userid = UserPW.Userid 
	WHERE Username = CURRENT_USER AND BerechtigungsbereichID IN (1, 4, 5) --Vertrag, Besetzung, Dienstplan
)
GO
GRANT SELECT ON AbContractBonus TO public
GO

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

CREATE VIEW [dbo].AbContractBonus_Update
AS 
SELECT v.* FROM  ContractBonus AS v WHERE v.ContractId IN (
	SELECT Vertrag.VertragsNr FROM Vertrag
	INNER JOIN  UserGruppe ON Vertrag.ModulGruppeID = UserGruppe.GruppeID
	INNER JOIN	UserPW ON UserGruppe.Userid = UserPW.Userid 
	WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 1 AND UserGruppe.Schreiben = 1
)
GO

GRANT SELECT ON AbContractBonus_Update TO public
GRANT DELETE ON AbContractBonus_Update TO public
GRANT INSERT ON AbContractBonus_Update TO public
GRANT UPDATE ON AbContractBonus_Update TO public
GO

--2015-04-09 DH
--Verknüpfung Dienstplaneintrag, Activity über CostunitWorkingtime
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'ShiftEnumValue' and o.id = OBJECT_ID(N'[dbo].[CostUnitWorkingTime]'))
BEGIN
	ALTER TABLE [dbo].[CostUnitWorkingTime] ADD ShiftEnumValue int null;
END
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'ActivityID' and o.id = OBJECT_ID(N'[dbo].[CostUnitWorkingTime]'))
BEGIN
	 ALTER TABLE [dbo].[CostUnitWorkingTime] ADD ActivityID int null;
END
-- Verknüpfung mit Activity Tabelle
IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CostUnitWorkingTime_AufführgTabelle]'))
BEGIN
	ALTER TABLE [dbo].[costunitworkingtime] WITH CHECK ADD  CONSTRAINT [FK_CostUnitWorkingTime_AufführgTabelle] FOREIGN KEY ([activityID]) 
	REFERENCES [dbo].[aufführgTabelle]([AuffNr]) ON DELETE CASCADE

END
GO

--2015-04-14 BM
--Eindeutiger Index Kostenträger/Dienstplaneintrag entfernt, da Dienstplaneinträgen sonst keine Aktivitäten zugewiesen werden können.
IF  EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[CostUnitWorkingTime]')  AND name = 'IX_CostUnitShiftDay' )
BEGIN
	
		DROP INDEX CostUnitWorkingTime.IX_CostUnitShiftDay
END
GO

--2015-04-16 BM
--Prozedur zum zählen von Besetzungungen zu einem Vertrag. Berechtigungen werden umgangen
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[GetCastCountByContract]'))
BEGIN
	DROP PROCEDURE [dbo].[GetCastCountByContract]
END
GO

CREATE PROCEDURE [dbo].[GetCastCountByContract]
		@ContractId INT
AS
BEGIN
	SELECT COUNT(B.ID) FROM BesetzungTabelle AS B
	INNER JOIN AufführgTabelle AS A ON A.AuffNr = B.AuffNr
	AND B.IsCanceled = 0
	AND A.[Status] >= 0
	AND B.ContractId = @ContractId
END
GO

GRANT EXECUTE ON [dbo].[GetCastCountByContract] TO Public
GO

--2015-04-24 BM
--Vertragsvalidierungsstatus -> Neues Feld "NotifyCreator"
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'NotifyContractCreator' and o.id = OBJECT_ID(N'dbo.PersContractValidationStatus'))
BEGIN
	ALTER TABLE dbo.PersContractValidationStatus ADD NotifyContractCreator BIT DEFAULT(0) NOT NULL
	EXEC ('UPDATE PersContractValidationStatus SET NotifyContractCreator = 1')
END
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AbVertragRolle'))
	DROP VIEW [dbo].[AbVertragRolle]

GO
CREATE VIEW [dbo].[AbVertragRolle]
AS 
SELECT
	vr.VertragsNr,
	vr.RolleId,
	vr.VRId,
	vr.ProbUnbez,
	vr.AbrechnungProben,
	vr.ProbBezahltAnz,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.ProbBezahltWert ELSE -1 END AS ProbBezahltWert,
	vr.ProbVorstAnteilAnz,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.Honorar ELSE -1 END AS Honorar,
	vr.VorstAnz,
	vr.AbrechnungVs,
	vr.VorstAnzMin,
	vr.VorstAnzVortrag,
	vr.LM,
	vr.UM,
	vr.LC,
	vr.UC,
	vr.vrVon,
	vr.vrBis,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.ExtraFee ELSE -1 END AS ExtraFee,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.SalaryLevelId ELSE -1 END AS SalaryLevelId,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.CoverFee ELSE -1 END AS CoverFee,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.FeeComment ELSE NULL END AS FeeComment,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.RehearsalFeeComment ELSE NULL END AS RehearsalFeeComment,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.DayFee ELSE -1 END AS DayFee,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.TravelExpensesFlat ELSE -1 END AS TravelExpensesFlat,
	vr.ProductionId,
	vr.CostUnitId,
	CASE WHEN UG.HasFeesPermission = 1 THEN vr.OvernightExpensesFlat ELSE -1 END AS OvernightExpensesFlat,
	vr.RehearsalsCompensatedByPerformance
FROM  
	VertragRolle AS vr 
		INNER JOIN Vertrag AS v ON VR.VertragsNr = v.VertragsNr  
		INNER JOIN	(SELECT GruppeID, Schreiben, (CASE WHEN OptionsFlag & 1 <> 1 THEN 0 ELSE 1 END) AS HasFeesPermission
						FROM			UserGruppe 
						INNER JOIN		UserPW		ON UserGruppe.Userid = UserPW.Userid 
						WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 1) AS UG
				ON v.ModulGruppeID = UG.GruppeID
GO
-- berechtigungen alle auf public
GRANT SELECT ON AbVertragRolle TO public
GO
--TK 2015-04-29
IF NOT EXISTS 
	(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('SeasonOwnerGroupSettings') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.SeasonOwnerGroupSettings ( 
				SeasonOwnerGroupSettingId INT IDENTITY(1,1) NOT NULL,--ID
				SeasonId INT NOT NULL,
				OwnerGroupId INT NOT NULL,
				RehearsalFlatFee MONEY NOT NULL CONSTRAINT DF_SeasonOwnerGroupSettings_RehearsalFlatFee DEFAULT(0),
				TravelCosts MONEY NOT NULL CONSTRAINT DF_SeasonOwnerGroupSettings_TravelCosts DEFAULT(0),
				AgencyPercent FLOAT NOT NULL CONSTRAINT DF_SeasonOwnerGroupSettings_AgencyPercent DEFAULT(0),
				SocialCostsPercent FLOAT NOT NULL CONSTRAINT DF_SeasonOwnerGroupSettings_SocialCostsPercent DEFAULT(0),
				LC DATETIME NOT NULL,
				UC INT NOT NULL,
				LM DATETIME NOT NULL,
				UM INT NOT NULL
				CONSTRAINT PK_SeasonOwnerGroupSettingId PRIMARY KEY CLUSTERED (SeasonOwnerGroupSettingId ASC) ON [PRIMARY],
				CONSTRAINT FK_SeasonOwnerGroupSettings_Seasons FOREIGN KEY (SeasonId) REFERENCES dbo.Spielzeittabelle (SpzNr) on delete Cascade,
				CONSTRAINT FK_SeasonOwnerGroupSettings_OwnerGroups FOREIGN KEY (OwnerGroupId) REFERENCES dbo.Gruppe (GruppeId) on delete Cascade
		) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX IX_SeasonOwnerGroupSettings_SeasonId ON dbo.SeasonOwnerGroupSettings (SeasonId)
	CREATE NONCLUSTERED INDEX IX_SeasonOwnerGroupSettings_OwnerGroupId ON dbo.SeasonOwnerGroupSettings (OwnerGroupId)
	CREATE UNIQUE NONCLUSTERED INDEX IX_SeasonOwnerGroupSettings_SeasonId_OwnerGroupId on dbo.SeasonOwnerGroupSettings (SeasonId, OwnerGroupId)
END
GO	
	GRANT SELECT ON dbo.SeasonOwnerGroupSettings TO [Public] 
	GRANT DELETE, INSERT, UPDATE ON dbo.SeasonOwnerGroupSettings TO CostsWrite

GO 
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'ShortName' and o.id = OBJECT_ID(N'dbo.ResourceObjectConditions'))
BEGIN
	 ALTER TABLE dbo.ResourceObjectConditions ADD ShortName VARCHAR(20) NULL
	 EXEC ('UPDATE ResourceObjectConditions SET ShortName = LEFT(Name, 15)')
	 ALTER TABLE dbo.ResourceObjectConditions ALTER COLUMN ShortName VARCHAR(20) NOT NULL
	 ALTER TABLE dbo.ResourceObjectConditions ALTER COLUMN Name VARCHAR(40) NOT NULL
END
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Format' AND o.id = OBJECT_ID(N'dbo.DayNotes') AND c.prec < 1024)
		ALTER TABLE DayNotes ALTER COLUMN [Format] VARCHAR(1024)
GO

--2015-04-23 BM
--Setzen des vDatum und WebLockDatum einer Gruppe über jeweilige Prozedur. 
--Umgeht Berechtigungen auf Gruppe und prüft stattdessen auf Berechtigungen für Dispodaten
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[SetModulGroupWebLockDate]'))
BEGIN
	DROP PROCEDURE [dbo].[SetModulGroupWebLockDate]
END
GO
CREATE PROCEDURE [dbo].[SetModulGroupWebLockDate]
	@ModuleGroupId int,
	@Date Datetime 
AS
BEGIN   
		UPDATE dbo.Gruppe
		SET webLockDatum = @Date
		WHERE GruppeID = @ModuleGroupId
		AND EXISTS (
			SELECT u.UserID FROM UserPw AS u
			INNER JOIN UserGruppe AS ug ON ug.UserID = u.UserID
			WHERE u.UserName = CURRENT_USER AND ug.GruppeID = @ModuleGroupId AND ug.BerechtigungsbereichID = 2 AND ug.Schreiben = 1
		)

		SELECT @@ROWCOUNT
END
GO

GRANT EXECUTE ON [dbo].[SetModulGroupWebLockDate] TO Public
GO

IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[SetModulGroupVDatum]'))
BEGIN
	DROP PROCEDURE [dbo].[SetModulGroupVDatum]
END
GO
CREATE PROCEDURE [dbo].[SetModulGroupVDatum]
	@ModuleGroupId int,
	@Date Datetime 
AS
BEGIN   
		UPDATE dbo.Gruppe
		SET vDatum = @Date
		WHERE GruppeID = @ModuleGroupId
		AND EXISTS (
			SELECT u.UserID FROM UserPw AS u
			INNER JOIN UserGruppe AS ug ON ug.UserID = u.UserID
			WHERE u.UserName = CURRENT_USER AND ug.GruppeID = @ModuleGroupId AND ug.BerechtigungsbereichID = 2 AND ug.OptionsFlag & 4 = 4
		)

		SELECT @@ROWCOUNT
END
GO

GRANT EXECUTE ON [dbo].[SetModulGroupVDatum] TO Public
GO

--20150518 Bugfix Abwesenheiten
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[PublicStatus]'))
	BEGIN
		DROP VIEW [dbo].[PublicStatus]
	END
GO

--Kollektive, Firmen und Anwesende
CREATE VIEW [dbo].[PublicStatus]
AS
	SELECT			st.*,
					pk.AdressArt,
					P.Nachname, P.Suchname
	FROM			StatusZeitR st
		INNER JOIN  PersH			AS P	ON st.PersNr = P.PersNr
		INNER JOIN  PersKat			AS pk	ON P.PersKatNr = pk.PersKatNr
		INNER JOIN  [Status]		AS s	ON st.StatusID = s.StatusID
	WHERE    
		pk.AdressArt BETWEEN 10 AND 200 OR s.StatusArt>= 20

GO

GRANT SELECT ON [dbo].[PublicStatus] TO public
GO

--20150512 DH - Geburtstagsspalte DispoBeta
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].Birthdays'))
	DROP VIEW [dbo].[Birthdays]
GO
CREATE VIEW [dbo].[Birthdays] AS
	SELECT pd.PNr AS PersonId, Month(pd.GebDatum) AS BirthMonth, Day(pd.GebDatum) AS Birthday, p.Suchname AS Searchname, p.Besitzer AS OwnerGroupId, p.alt AS Inactive, pd.PNr AS PersNr --PersNr noch aus Kompatibilitätsgründen
	FROM PersonalDetails pd INNER JOIN persh p ON p.PersNr = pd.PNr 
	WHERE NOT GebDatum IS NULL 
go
GRANT SELECT ON [dbo].[Birthdays] TO public
GO

--2015-05-13 sh
--tabelle für exportprofile
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('ExportProfiles') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.ExportProfiles ( 
				ExportProfileId INT IDENTITY(1,1) NOT NULL,--ID
				Name VARCHAR(50) NOT NULL,
				[Description] VARCHAR(255) NULL,
				ExportArea INT NOT NULL, --enum
				ExportProvider VARCHAR(80) NOT NULL,
				DefaultExportPath VARCHAR(255) NULL,
				ExportAreaProviderSettings VARCHAR(MAX) NULL,
				ExportProviderSettings VARCHAR(MAX) NULL,
				ZipSettings VARCHAR(MAX) NULL,								
				TransferSettings VARCHAR(MAX) NULL,
				MailingSettings VARCHAR(MAX) NULL,
				OwnerGroupId INT NOT NULL, --fk
				LC DATETIME NOT NULL,
				UC INT NOT NULL,
				LM DATETIME NOT NULL,
				UM INT NOT NULL
				CONSTRAINT PK_ExportProfileId PRIMARY KEY CLUSTERED (ExportProfileId ASC) ON [PRIMARY],
				CONSTRAINT FK_ExportProfiles_OwnerGroups FOREIGN KEY (OwnerGroupId) REFERENCES dbo.Gruppe,
		) ON [PRIMARY]
END
GO

--view für exportprofiles
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AbExportProfiles'))
	DROP VIEW [dbo].[AbExportProfiles]
GO

CREATE VIEW [dbo].[AbExportProfiles]
AS 
SELECT
	ep.*
FROM
	ExportProfiles ep
	INNER JOIN	(
		SELECT GruppeID, Schreiben, OptionsFlag FROM UserGruppe 
		INNER JOIN		UserPW		ON UserGruppe.Userid = UserPW.Userid 
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 14
	) AS UG ON ep.OwnerGroupId = UG.GruppeID AND (ep.ExportArea & UG.OptionsFlag) <> 0
GO
-- berechtigungen alle auf public
GRANT SELECT ON [AbExportProfiles] TO public
GO 

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

CREATE VIEW [dbo].[AbExportProfiles_Update]
AS 
SELECT
	ep.*
FROM
	ExportProfiles ep
	INNER JOIN	(
		SELECT GruppeID, Schreiben, OptionsFlag FROM UserGruppe 
		INNER JOIN		UserPW		ON UserGruppe.Userid = UserPW.Userid 
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 14
	) AS UG ON ep.OwnerGroupId = UG.GruppeID AND Schreiben = 1 AND (ep.ExportArea & UG.OptionsFlag) <> 0
GO
-- berechtigungen alle auf public
GRANT SELECT, UPDATE, INSERT, DELETE ON [AbExportProfiles_Update] TO public
GO 

--tk 2015-05-19

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.BesetzungTabelle')  AND name = 'IX_Cast_ContractId')
	CREATE INDEX IX_Cast_ContractId ON dbo.BesetzungTabelle (ContractId) ON [PRIMARY]
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.CountCastShiftByContract'))
	DROP PROCEDURE dbo.CountCastShiftByContract

GO
CREATE PROCEDURE dbo.CountCastShiftByContract
	@countType INT,
	@start Datetime, 
	@end Datetime,
	@contractIds NVARCHAR(4000),
	@departmentId INT,
	@activitytypIds NVARCHAR(4000)
AS
BEGIN
	SET NOCOUNT ON;
/*
@countType
0 = Anzahl
1 = Anrechenbare
2 = Zeit
*/

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

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

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

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

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

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

IF @countType = 0
	--Anzahl
	BEGIN
		IF @activitytypIds IS NULL
			--ohne Aufftypfilter
			BEGIN 
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ca.AuffNr) AS float) AS Sum  
				FROM			AufführgTabelle ac
					INNER JOIN Besetzungtabelle	AS ca	ON ac.Auffnr = ca.auffnr
					INNER JOIN BesetzungStd		AS r	ON ca.RolleID = r.id
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
				WHERE Datum BETWEEN @start AND @end 
					AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
					AND ac.Status BETWEEN 0 AND 99 
				GROUP BY ca.ContractId
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ca.AuffNr) AS float) AS Sum  
				FROM		AufführgTabelle ac
					INNER JOIN Besetzungtabelle	AS ca	ON ac.Auffnr =ca.auffnr
					INNER JOIN BesetzungStd		AS r	ON ca.RolleID =r.id
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.art = #activitytypIds.id
				WHERE Datum BETWEEN @start AND @end 
					AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
					AND ac.Status BETWEEN 0 AND 99 
				GROUP BY ca.ContractId
			END
		END
	ELSE IF @countType = 1
		--Anrechenbare
		BEGIN
		IF @activitytypIds IS NULL
		--ohne Aufftypfilter
			BEGIN 
				SELECT ContractId, SUM(Sum1) FROM (SELECT ca.ContractId, ca.AuffNr, 
					MAX(ISNULL(ca.Creditable,
						ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		AufführgTabelle			AS ac
					INNER JOIN AuffTypen			AS at		ON at.AuffTNr = ac.Art
					INNER JOIN Besetzungtabelle		AS ca		ON ac.Auffnr =ca.auffnr
					INNER JOIN BesetzungStd			AS r		ON ca.RolleID =r.id
					LEFT JOIN AuffDienstplanArten	AS ActDep	ON ac.AuffNr = ActDep.AuffNr AND ActDep.DienstplanArtenID = @departmentId
					LEFT JOIN StückeDienstplan		AS ProdDep	ON ac.SNr = ProdDep.Snr AND ProdDep.DienstplanArtenID = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds						ON ca.ContractId = #contractIds.id
					WHERE Datum BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, ca.AuffNr) AS SubSQL GROUP BY ContractId 
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ContractId, SUM(Sum1) FROM (SELECT ContractId, ca.AuffNr, MAX(ISNULL(ca.Creditable,
						ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		AufführgTabelle			AS ac
					INNER JOIN AuffTypen			AS at		ON at.AuffTNr = ac.Art
					INNER JOIN Besetzungtabelle		AS ca		ON ac.Auffnr = ca.auffnr
					INNER JOIN BesetzungStd			AS r		ON ca.RolleID = r.id
					LEFT JOIN AuffDienstplanArten	AS ActDep	ON ac.AuffNr = ActDep.AuffNr AND ActDep.DienstplanArtenID = @departmentId
					LEFT JOIN StückeDienstplan		AS ProdDep	ON ac.SNr = ProdDep.Snr AND ProdDep.DienstplanArtenID = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds					ON ac.art = #activitytypIds.id
					WHERE Datum BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, ca.AuffNr) AS SubSQL GROUP BY ContractId 
			END	
		END
ELSE IF @countType = 2
	BEGIN
	--Zählen pro Tag max 2 Dienst  (morgens < 14:00 und abends)
			IF @activitytypIds IS NULL
		--ohne Aufftypfilter
			BEGIN 
				SELECT ContractId, SUM(Sum1) FROM (SELECT ContractId, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, 
					MAX(ISNULL(ca.Creditable, ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		AufführgTabelle			AS ac
					INNER JOIN AuffTypen			AS at	ON at.AuffTNr = ac.Art
					INNER JOIN Besetzungtabelle		AS ca	ON ac.Auffnr = ca.auffnr
					INNER JOIN BesetzungStd			AS r	ON ca.RolleID = r.id
					LEFT JOIN AuffDienstplanArten	AS ActDep ON ac.AuffNr = ActDep.AuffNr AND ActDep.DienstplanArtenID = @departmentId
					LEFT JOIN StückeDienstplan		AS ProdDep ON ac.SNr = ProdDep.Snr AND ProdDep.DienstplanArtenID = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					WHERE Datum BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL GROUP BY ContractId 
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ContractId, SUM(Sum1) FROM (SELECT ContractId, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, MAX(ISNULL(ca.Creditable,
						ISNULL(ActDep.ShiftCount, CASE WHEN at.KatNr = 0 THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
				FROM		AufführgTabelle			AS ac
					INNER JOIN AuffTypen			AS at	ON at.AuffTNr = ac.Art
					INNER JOIN Besetzungtabelle		AS ca	ON ac.Auffnr = ca.auffnr
					INNER JOIN BesetzungStd			AS r	ON ca.RolleID = r.id
					LEFT JOIN AuffDienstplanArten	AS ActDep ON ac.AuffNr = ActDep.AuffNr AND ActDep.DienstplanArtenID = @departmentId
					LEFT JOIN StückeDienstplan		AS ProdDep ON ac.SNr = ProdDep.Snr AND ProdDep.DienstplanArtenID = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.art = #activitytypIds.id
					WHERE Datum BETWEEN @start AND @end 
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL GROUP BY ContractId 
			END		
	END
ELSE IF @countType = 3
	BEGIN
	--Zählen pro Tag max 2 Dienst  (morgens < 14:00 und abends)
			IF @activitytypIds IS NULL
		--ohne Aufftypfilter
			BEGIN 
				SELECT ContractIdDay, SUM(SumDay) FROM(
				SELECT ContractId AS ContractIdDay, datum, SUM(Sum1)-1  AS [SumDay]
				FROM (
					SELECT ContractId, ac.datum, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, 
						MAX(ISNULL(ca.Creditable, ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 
							THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
					FROM		AufführgTabelle			AS ac
					INNER JOIN AuffTypen			AS at	ON at.AuffTNr = ac.Art
					INNER JOIN Besetzungtabelle		AS ca	ON ac.Auffnr = ca.auffnr
					INNER JOIN BesetzungStd			AS r	ON ca.RolleID = r.id
					LEFT JOIN AuffDienstplanArten	AS ActDep ON ac.AuffNr = ActDep.AuffNr AND ActDep.DienstplanArtenID =@departmentId
					LEFT JOIN StückeDienstplan		AS ProdDep ON ac.SNr = ProdDep.Snr AND ProdDep.DienstplanArtenID = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					WHERE Datum BETWEEN @start AND @end
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, ac.datum, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL 
				GROUP BY ContractId, datum)  AS subDay Group By ContractIdDay
END		
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ContractIdDay, SUM(SumDay) FROM(
				SELECT ContractId AS ContractIdDay, datum, SUM(Sum1)-1  AS [SumDay]
				FROM (
					SELECT ContractId, ac.datum, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END AS Shift, 
						MAX(ISNULL(ca.Creditable, ISNULL(ActDep.ShiftCount, 
						CASE WHEN at.KatNr = 0 
							THEN ISNULL(ProdDep.ShiftCount, 1) ELSE 1 END))) AS Sum1 
					FROM		AufführgTabelle			AS ac
					INNER JOIN AuffTypen			AS at	ON at.AuffTNr = ac.Art
					INNER JOIN Besetzungtabelle		AS ca	ON ac.Auffnr = ca.auffnr
					INNER JOIN BesetzungStd			AS r	ON ca.RolleID = r.id
					LEFT JOIN AuffDienstplanArten	AS ActDep ON ac.AuffNr = ActDep.AuffNr AND ActDep.DienstplanArtenID =@departmentId
					LEFT JOIN StückeDienstplan		AS ProdDep ON ac.SNr = ProdDep.Snr AND ProdDep.DienstplanArtenID = @departmentId AND ProdDep.SeasonId IS NULL
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.art = #activitytypIds.id
					WHERE Datum BETWEEN @start AND @end
						AND (ca.IsCanceled = 0 OR ca.Creditable IS NULL OR ca.Creditable > 0)
						AND ac.Status BETWEEN 0 AND 99
					GROUP BY ContractId, ac.datum, CONVERT(VARCHAR(10),ac.datum,126) + '_' + CASE WHEN CONVERT(VARCHAR(5),ac.UhrBeg, 108) > '14:00'  THEN '2' ELSE '1' END) AS SubSQL 
				GROUP BY ContractId, datum)  AS subDay Group By ContractIdDay
			END		
	END
ELSE IF @countType = 4
	BEGIN
		IF @activitytypIds IS NULL
			--ohne Aufftypfilter
			BEGIN 
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ac.Datum) AS float) AS Sum  
				FROM		AufführgTabelle ac
					INNER JOIN Besetzungtabelle	AS ca	ON ac.Auffnr =ca.auffnr
					INNER JOIN BesetzungStd		AS r	ON ca.RolleID =r.id
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
				WHERE Datum BETWEEN @start AND @end 
					AND ca.IsCanceled = 0 
					AND ac.Status BETWEEN 0 AND 99 	
				GROUP BY ContractId
			END
		ELSE
		--mit Aufftypfilter
			BEGIN
				SELECT ca.ContractId, CAST(COUNT (DISTINCT ac.Datum) AS float) AS Sum  
				FROM		AufführgTabelle ac
					INNER JOIN Besetzungtabelle	AS ca	ON ac.Auffnr =ca.auffnr
					INNER JOIN BesetzungStd		AS r	ON ca.RolleID =r.id
					INNER JOIN #contractIds				ON ca.ContractId = #contractIds.id
					INNER JOIN #activitytypIds			ON ac.art = #activitytypIds.id
				WHERE Datum BETWEEN @start AND @end 
					AND ca.IsCanceled = 0 
					AND ac.Status BETWEEN 0 AND 99 
				GROUP BY ContractId
			END
		END

END
	DROP TABLE  #contractIds
	IF @activitytypIds IS NOT NULL
	BEGIN
		DROP TABLE #activitytypIds
	END


GO
GRANT EXECUTE ON CountCastShiftByContract TO Public
GO
--TK 2015-05-21
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
		WHERE c.name = 'FixBonusValue' and o.id = OBJECT_ID(N'dbo.ProductionBonus'))
	ALTER TABLE dbo.ProductionBonus ADD FixBonusValue MONEY NULL
GO 

IF NOT EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'FK_RoleBonus_ContractType'))
BEGIN
	DELETE FROM RoleBonus WHERE ContractTypeId NOT IN  (SELECT VorlagenID FROM VertragsVorlagen)
	ALTER TABLE dbo.RoleBonus  WITH CHECK ADD  CONSTRAINT FK_RoleBonus_ContractType FOREIGN KEY(ContractTypeId)
	REFERENCES dbo.VertragsVorlagen (VorlagenID) ON DELETE CASCADE
	ALTER TABLE dbo.RoleBonus CHECK CONSTRAINT FK_RoleBonus_ContractType
END
GO

IF NOT EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'FK_ProductionBonus_ContractType'))
BEGIN
	DELETE FROM ProductionBonus WHERE ContractTypeId NOT IN  (SELECT VorlagenID FROM VertragsVorlagen)
	ALTER TABLE dbo.ProductionBonus  WITH CHECK ADD  CONSTRAINT FK_ProductionBonus_ContractType FOREIGN KEY(ContractTypeId)
	REFERENCES dbo.VertragsVorlagen (VorlagenID) ON DELETE CASCADE
	ALTER TABLE dbo.ProductionBonus CHECK CONSTRAINT FK_ProductionBonus_ContractType
END
GO
--Vertragsart 0 "legalisieren"
IF EXISTS(SELECT * FROM VertragsVorlagen WHERE VorlagenID = 0)
BEGIN
	IF EXISTS(SELECT * FROM Vertrag WHERE VorlagenID = 0)
	BEGIN
		INSERT INTO VertragsVorlagen ( [VorlgenBez] ,[VArt],[Abgaben] ,[LM] ,[UM],[LC],[UC],[persoParameterID],[Module]) SELECT '['+ [VorlgenBez] + ']',[VArt],[Abgaben] ,[LM] ,[UM],[LC],[UC],[persoParameterID],[Module] FROM [dbo].[VertragsVorlagen] WHERE VorlagenID = 0
		UPDATE Vertrag SET VorlagenID = (SELECT MAX(VorlagenID) FROM VertragsVorlagen) WHERE VorlagenID = 0
		UPDATE ContractType_Department SET ContractTypeId = (SELECT MAX(VorlagenID) FROM VertragsVorlagen) WHERE ContractTypeId = 0
	END
	DELETE FROM VertragsVorlagen WHERE VorlagenID = 0
END


-- DH 20150224 -- Umstellen der Planpermissionsettings auf View und OptionsFlag
-- updated 20150608

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[WarningLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[WarningLog] (
	[WarningLogId] [int] IDENTITY(1,1) NOT NULL,
	[WarningTypeId] [int] NOT NULL,
	[ErrorCount] [int] NOT NULL,
	[ErrorDetails] [nvarchar](max) NULL,
	[Closed] [bit] NOT NULL,
	[LM] [datetime] NOT NULL,
	[UM] [int] NOT NULL,
	[LC] [datetime] NOT NULL,
	[UC] [int] NOT NULL
	CONSTRAINT PK_Warninglog_LogID PRIMARY KEY CLUSTERED (WarningLogID ASC)
	
)
END
GO
GRANT SELECT ON [dbo].[WarningLog] TO [Public] 
GRANT DELETE, INSERT, UPDATE ON [dbo].[WarningLog] TO SecurityAdmin
--TK 2015-05-27
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
		WHERE c.name = 'DepartmentId' and o.id = OBJECT_ID(N'dbo.ProductionBonus'))
BEGIN
	ALTER TABLE dbo.ProductionBonus ADD DepartmentId INT NULL
	EXEC  ('UPDATE ProductionBonus SET DepartmentId = (SELECT MIN(da.DienstplanArtenID) FROM DienstplanArtenTabelle AS da INNER JOIN Gruppe ON da.ModulGruppeID = Gruppe.GruppeID WHERE Gruppe.Modul = 16)')
	ALTER TABLE dbo.ProductionBonus ALTER COLUMN DepartmentId INT NOT NULL

	ALTER TABLE dbo.ProductionBonus  WITH CHECK ADD  CONSTRAINT FK_ProductionBonus_Department FOREIGN KEY(DepartmentId)
	REFERENCES dbo.DienstplanArtenTabelle (DienstplanArtenID) ON DELETE CASCADE
	ALTER TABLE dbo.ProductionBonus CHECK CONSTRAINT FK_ProductionBonus_Department
END
GO


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

CREATE VIEW dbo.AbProductionBonus_Update
AS 
SELECT
	pb.*
FROM
	ProductionBonus AS pb WHERE pb.DepartmentId IN (
		SELECT Dp.DienstplanArtenID FROM UserGruppe 
		INNER JOIN          UserPW                            ON UserGruppe.Userid   = UserPW.Userid 
		INNER JOIN          DienstplanArtenTabelle AS DP      ON UserGruppe.GruppeID = DP.ModulGruppeID 
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 4 AND Schreiben = 1
	)
GO

-- berechtigungen alle auf public
GRANT SELECT, UPDATE, INSERT, DELETE ON AbProductionBonus_Update TO public
GO 

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

CREATE VIEW dbo.AbRoleBonus_Update
AS 
SELECT
	rb.*
FROM
	RoleBonus AS rb WHERE rb.RoleId IN (
		SELECT R.Id FROM UserGruppe 
		INNER JOIN		UserPW            ON UserGruppe.Userid   = UserPW.Userid 
		INNER JOIN		BesetzungStd AS R ON UserGruppe.GruppeID = r.Besitzer
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 4 AND Schreiben = 1 
	) 
GO
-- berechtigungen alle auf public
GRANT SELECT, UPDATE, INSERT, DELETE ON AbRoleBonus_Update TO public
GO

--2015-05-29 sh
--sort spalte für sparten
IF NOT  EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Sort' and o.id = OBJECT_ID(N'[dbo].Sparte'))
BEGIN
	ALTER TABLE Sparte ADD Sort int NOT NULL CONSTRAINT [DF_Sparte_Sort] DEFAULT 0
END
GO

--name von rollenltg erhöhen (geicher wert wie bei besetzungstd)
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Rolle' AND o.id = OBJECT_ID(N'[dbo].RollenLtg') AND c.prec < 150)
BEGIN
	ALTER TABLE RollenLtg ALTER COLUMN Rolle VARCHAR(150) NULL
END
GO

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Inhalt' AND o.id = OBJECT_ID(N'dbo.PersonalNotizen') AND c.prec < 500)
		ALTER TABLE PersonalNotizen ALTER COLUMN Inhalt VARCHAR(500)
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Bem' AND o.id = OBJECT_ID(N'dbo.ZeitkontenMonat') AND c.prec < 500)
		ALTER TABLE ZeitkontenMonat ALTER COLUMN Bem VARCHAR(500)
GO
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.SpielzeitTabelle')  AND name = 'IX_SpielzeitTabelle_SpzBez' )
			DROP INDEX [IX_SpielzeitTabelle_SpzBez] ON [dbo].[SpielzeitTabelle]
GO
IF  EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.SpielzeitTabelle')  AND name = 'IX_SpielzeitTabelle_SpzStart' )
			DROP INDEX [IX_SpielzeitTabelle_SpzStart] ON [dbo].[SpielzeitTabelle]
	
--20150612 DH Eindeutiger Index auf Spielzeitbezeichnung,Start
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.SpielzeitTabelle')  AND name = 'IX_Season_Name' )
	BEGIN
		ALTER TABLE SpielzeitTabelle ALTER COLUMN SpzBez VARCHAR(15) NOT NULL
		CREATE UNIQUE NONCLUSTERED INDEX IX_Season_Name ON dbo.SpielzeitTabelle (SpzBez ASC) ON [PRIMARY]
	END
GO
IF  EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'dbo.SpielzeitTabelle')  AND name = 'IX_Season_Start' )
	BEGIN
		ALTER TABLE SpielzeitTabelle ALTER COLUMN SpzStart DATETIME NOT NULL
		ALTER TABLE SpielzeitTabelle ALTER COLUMN SpzEnde DATETIME NOT NULL
		CREATE UNIQUE NONCLUSTERED INDEX IX_Season_Start ON dbo.SpielzeitTabelle (SpzStart ASC)ON [PRIMARY]
	END

--2015-06-16 BM
--Sternchen-Stück auf nicht abgespielt setzen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 152 OR (Minor = 152 AND Build < 105)))))
BEGIN
	UPDATE Stücke SET Alt = 0 WHERE Titel = '*'
END
GO
--2015-06-24 DH AktivitiesUpdate war langsam, Berechtigungsprüfung beschleuningt.
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Username' AND o.id = OBJECT_ID(N'dbo.[UserPw]') AND c.prec < 128)
BEGIN
	DROP INDEX [IX_UserName] ON [dbo].[UserPw]
	ALTER TABLE UserPW ALTER COLUMN Username NVARCHAR(128)
	CREATE UNIQUE NONCLUSTERED INDEX [IX_UserName] ON [dbo].[UserPw] ([UserName] ASC) ON [PRIMARY]
END
--2015-07-02 DH
IF EXISTS (SELECT FahrplanStdID FROM FahrplanStd WHERE Gruppe IS NULL )
BEGIN
	DELETE from FahrplanStd where Gruppe is null 
END
go
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
		WHERE c.name = 'Gruppe' AND o.id = OBJECT_ID(N'[dbo].Fahrplanstd') AND c.isnullable=1)
BEGIN
	IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[FahrplanStd]') AND NAME ='_1CA61188_BD03_11D2_BA7D_00400')
    BEGIN
		DROP INDEX [_1CA61188_BD03_11D2_BA7D_00400] ON [dbo].[FahrplanStd];
	END
	IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[FahrplanStd]') AND NAME ='Gruppe')
    BEGIN
		DROP INDEX [Gruppe] ON [dbo].[FahrplanStd]
	END
	ALTER TABLE [dbo].[Fahrplanstd] ALTER COLUMN Gruppe Int not null;
	CREATE NONCLUSTERED INDEX [IX_JourneyTimetableDefault_JourneyTimetableGroup] ON [dbo].[FahrplanStd]
	(
		[Gruppe] ASC
	) ON [PRIMARY]
	IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'FK_JourneyTimetableDefault_JourneyTimetableGroup'))
	BEGIN
		ALTER TABLE [dbo].[Fahrplanstd] DROP CONSTRAINT [FK_JourneyTimetableDefault_JourneyTimetableGroup]
	END
	ALTER TABLE [dbo].[Fahrplanstd]   WITH CHECK ADD  CONSTRAINT [FK_JourneyTimetableDefault_JourneyTimetableGroup] FOREIGN KEY([Gruppe])
	REFERENCES [dbo].[FahrplanGruppen] ([GruppeID]); 
	END
GO

--DH 20150601

--Filter für Repertoire nach Produktion-Abgespielt
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CastActiveties]'))
BEGIN
DROP VIEW [dbo].[CastActiveties]
END 
GO
CREATE VIEW [dbo].[CastActiveties] 
AS
SELECT	ac.Datum, ac.UhrBeg, ac.UhrEnde, ac.SNr, ac.ZrNr, ac.Art, ac.Bem, at.KatNr, ac.Status,
		ca.*,st.alt

FROM	AufführgTabelle ac
		INNER JOIN AuffTypen		AS at	ON ac.art = at.AuffTNr
		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
		LEFT 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
		LEFT  JOIN BesetzungStd		AS ro	ON ca.RolleID = ro.ID
		INNER  JOIN Gruppe			AS g	ON g.GruppeID = ro.Besitzer	
		INNER  JOIN UserGruppe		AS ugc	ON G.GruppeID = ugc.GruppeID AND ugc.UserID = ua.UserID AND ugc.BerechtigungsbereichID = 4
		LEFT JOIN Stücke			AS st	ON ac.SNr=st.SNr 
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)) OR ca.RolleId IS NULL)

GO
GRANT SELECT ON CastActiveties TO Public
GO

IF NOT  EXISTS (SELECT * FROM Rapport)	INSERT INTO Rapport (Bez, Module) VALUES ('Rapport', 0)

--TK 20150611 Rapport an Vertragsart
IF NOT  EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'RapportId' and o.id = OBJECT_ID(N'[dbo].VertragsVorlagen'))
BEGIN
	ALTER TABLE VertragsVorlagen ADD RapportId INT NULL
	EXEC('UPDATE VertragsVorlagen SET RapportId = PersoParameter.rid FROM VertragsVorlagen INNER JOIN PersoParameter ON VertragsVorlagen.persoParameterID = PersoParameter.PersoParameterID')
	EXEC('UPDATE VertragsVorlagen SET RapportId = (SELECT MAX(rid) FROM Rapport ) FROM VertragsVorlagen WHERE  RapportId IS NULL')
	EXEC('ALTER TABLE VertragsVorlagen ALTER COLUMN RapportId INT NOT NULL')

	EXEC('ALTER TABLE dbo.VertragsVorlagen  WITH CHECK ADD  CONSTRAINT FK_ContractType_Rapport FOREIGN KEY(RapportId) 	REFERENCES dbo.Rapport (Rid)')
	EXEC('ALTER TABLE dbo.VertragsVorlagen CHECK CONSTRAINT FK_ContractType_Rapport')

	ALTER TABLE dbo.PersoParameter 	DROP CONSTRAINT FK_persoParameter_Rapport
	ALTER TABLE dbo.PersoParameter	DROP COLUMN RID
 END
 GO
--2015-06-04 BM
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'DefaultRehearsalDurationMorning' and o.id = OBJECT_ID(N'dbo.AuffTypen'))
BEGIN
	ALTER TABLE dbo.AuffTypen ADD DefaultRehearsalDurationMorning INT NULL;
	EXEC ('UPDATE dbo.AuffTypen SET DefaultRehearsalDurationMorning = 
			CASE WHEN Probendauer > 8 
				THEN ROUND(Probendauer, 0)  
				ELSE ROUND(Probendauer * 60, 0)
			END')  
	ALTER TABLE dbo.AuffTypen DROP COLUMN Probendauer
	ALTER TABLE dbo.AuffTypen ADD Probendauer AS CONVERT(REAL, DefaultRehearsalDurationMorning) / 60
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'DefaultRehearsalDurationEvening' and o.id = OBJECT_ID(N'dbo.AuffTypen'))
BEGIN
	ALTER TABLE dbo.AuffTypen ADD DefaultRehearsalDurationEvening INT NULL;
	EXEC ('UPDATE dbo.AuffTypen SET DefaultRehearsalDurationEvening = DefaultRehearsalDurationMorning')
END
GO

--2015-06-30 BM
--Sperren-/Freigeben-Datum setzen jetzt über Kombination aus Schreibrecht für "Aktivitäten (über Produktionen)" 
--und Option "Zugriff auf gesperrte Aktivitäten" (Details: WI#7386)
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[SetModulGroupVDatum]'))
BEGIN
	DROP PROCEDURE [dbo].[SetModulGroupVDatum]
END
GO
CREATE PROCEDURE [dbo].[SetModulGroupVDatum]
	@ModuleGroupId int,
	@Date Datetime 
AS
BEGIN   
		UPDATE dbo.Gruppe
		SET vDatum = @Date
		WHERE GruppeID = @ModuleGroupId
		AND EXISTS (
			SELECT u.UserID FROM UserPw AS u
			INNER JOIN UserGruppe AS ug ON ug.UserID = u.UserID
			WHERE u.UserName = CURRENT_USER AND ug.GruppeID = @ModuleGroupId AND ug.BerechtigungsbereichID = 2 AND ug.Schreiben = 1 AND ug.OptionsFlag & 1 = 1
		)

		SELECT @@ROWCOUNT
END
GO

GRANT EXECUTE ON [dbo].[SetModulGroupVDatum] TO Public
GO

IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 153 OR (Minor = 153 AND Build < 6)))))
BEGIN
	UPDATE UserGruppe SET OptionsFlag = OptionsFlag | 1
	WHERE BerechtigungsbereichID = 2 AND OptionsFlag & 4 = 4

	UPDATE UserGruppe SET OptionsFlag = OptionsFlag - 4
	WHERE BerechtigungsbereichID = 2 AND OptionsFlag & 4 = 4
END
GO

--2015-07-01 BM
--Aktivitätstypen können inaktiv sein
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'IsInactive' and o.id = OBJECT_ID(N'dbo.AuffTypen'))
BEGIN
	ALTER TABLE dbo.AuffTypen ADD IsInactive BIT DEFAULT(0) NOT NULL ;
END

--2015-07-09
--Abendbesetzung einer gerade gelöschten Teilproduktion mitlöschen
IF EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[DeleteCastFromProductionPart]'))
BEGIN
	DROP PROCEDURE [dbo].[DeleteCastFromProductionPart]
END
GO
	CREATE PROCEDURE [dbo].[DeleteCastFromProductionPart]
		@ParentProductionId int,
		@ProductionPartId int 
	AS
		BEGIN   
			DELETE c FROM BesetzungTabelle AS c
			INNER JOIN AufführgTabelle AS a ON c.AuffNr = a.AuffNr
			INNER JOIN BesetzungStd AS s ON c.RolleID = s.ID
			INNER JOIN Stücke AS p ON @ParentProductionId = p.SNr
			INNER JOIN UserGruppe AS g ON p.ModuleGroupId = g.GruppeID
			INNER JOIN UserPw AS u ON g.UserID = u.UserID
			WHERE a.SNr = @ParentProductionId
			AND s.SNr = @ProductionPartId
			AND u.UserName = CURRENT_USER 
			AND g.BerechtigungsbereichID = 3 
			AND g.Schreiben = 1
		END
GO

GRANT EXECUTE ON [dbo].[DeleteCastFromProductionPart] TO Public
GO

--2015-07-16 BM
--PersoCloseWrite entfernen
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ZeitkontenMonat_Update]'))
BEGIN
	DROP VIEW [dbo].[ZeitkontenMonat_Update]
END 
GO

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

CREATE VIEW [dbo].[AbZeitkontenMonat]
AS
SELECT * FROM ZeitkontenMonat WHERE zmID IN (
	SELECT ZM.zmId
	FROM ZeitkontenMonat AS ZM
		INNER JOIN Vertrag AS V ON V.VertragsNr = ZM.VertragsNr
		INNER JOIN VertragPerso AS VP ON VP.VertragsNr = V.VertragsNr
		INNER JOIN AB ON AB.ABId = VP.ABId
		INNER JOIN DienstplanArtenTabelle AS DP ON DP.DienstplanArtenID = AB.DienstplanArtenID
		INNER JOIN UserGruppe AS UG ON 
			   (UG.GruppeID = V.ModulGruppeID AND UG.BerechtigungsbereichID = 1) 
			OR (UG.GruppeID = DP.ModulGruppeID AND UG.BerechtigungsbereichID = 5)
		INNER JOIN UserPw AS UP ON UP.UserID = UG.UserID
	WHERE UP.UserName = CURRENT_USER)
GO

GRANT SELECT ON [AbZeitkontenMonat] TO Public
GO

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

CREATE VIEW [dbo].[AbZeitkontenMonat_Update]
AS
SELECT * FROM ZeitkontenMonat WHERE zmID IN (
	SELECT ZM.zmId
	FROM ZeitkontenMonat AS ZM
		INNER JOIN Vertrag AS V ON V.VertragsNr = ZM.VertragsNr
		INNER JOIN VertragPerso AS VP ON VP.VertragsNr = V.VertragsNr
		INNER JOIN AB ON AB.ABId = VP.ABId
		INNER JOIN DienstplanArtenTabelle AS DP ON DP.DienstplanArtenID = AB.DienstplanArtenID
		INNER JOIN UserGruppe AS UG ON 
			   (UG.GruppeID = V.ModulGruppeID AND UG.BerechtigungsbereichID = 1) 
			OR (UG.GruppeID = DP.ModulGruppeID AND UG.BerechtigungsbereichID = 5 AND OptionsFlag & 2 = 2)
		INNER JOIN UserPw AS UP ON UP.UserID = UG.UserID
	WHERE UP.UserName = CURRENT_USER)
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON [AbZeitkontenMonat_Update] TO public
GO

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

CREATE VIEW [dbo].[AbJahresabschluss_Update]
AS
	SELECT j.* FROM Jahresabschluss AS j WHERE j.DienstplanArtenID IN (
		SELECT dpa.DienstplanArtenID FROM DienstplanArtenTabelle AS dpa
	INNER JOIN UserGruppe AS ug ON dpa.ModulGruppeID = ug.GruppeID
	INNER JOIN UserPw AS u ON ug.UserID = u.UserID
	WHERE u.UserName = CURRENT_USER AND ug.BerechtigungsbereichID = 5 AND ug.OptionsFlag & 2 = 2
	)
	WITH CHECK OPTION
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON [AbJahresabschluss_Update] TO public
GO


--2015-07-20 sh
--neue spalter in userpw
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'IsAuthorizedByWindowsGroup' and o.id = OBJECT_ID(N'dbo.UserPw'))
BEGIN
	ALTER TABLE dbo.UserPw ADD IsAuthorizedByWindowsGroup BIT CONSTRAINT DF_UserPw_IsAuthorizedByWindowsGroup DEFAULT 0  NOT NULL
END
GO

--InheritPermissions ansgepaßt, UsersInGroups wird nun über eine temp Tabelle verwendet, um die abfragen zu beschleunigen
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[InherhitPermissions]'))
BEGIN
	DROP PROCEDURE dbo.[InherhitPermissions] 
END
GO

-- =============================================
-- Author:			Torsten Klier
-- Create date:		2010 11 01
-- Modified date:	2011 01 24
--                  2015-03-26 sh temp table beim löschen entfernt, stattdessen usersingroups über temptabelle um alle abfragen zu beschleunigen
--                  2015-07-16 sh temp tabellen durch variablen ersetzt, ist für kleine daten besser
--                                neu anlegen von userpw eintrag hinzugefügt und auswertung bzgl windows gruppen
-- (Abteilungs-)Gruppenberechtigungen aus Datenbankrollen übernehmen
-- Wenn ein Recht aus mehreren Datenbankrollen geerbt wird, wird das "größte" übernommen 
-- =============================================
CREATE PROCEDURE [dbo].[InherhitPermissions]
AS
BEGIN   
	DECLARE @dbo BIT
	DECLARE @currentUserId INT
	DECLARE @authorizedByGroup BIT
	DECLARE @forAllUsers BIT
	
	SET @currentUserId = 0
	SET @authorizedByGroup = 0
	SET @forAllUsers = 0

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

	SELECT @dbo = IS_MEMBER('db_owner')
	IF @dbo = 0
		BEGIN
			Print 'User' +  cast(@currentUserId as Varchar(10))
		END  
	ELSE
		BEGIN
			SET @forAllUsers = 1
			Print 'All User'
		END

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

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

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

	--Berechtigungen entfernen
	DELETE FROM UserGruppe 
	WHERE IsInherited = 1 
		AND (UserGruppe.UserId = @currentUserId OR @forAllUsers = 1)
		AND  UserGruppeID NOT IN 
				(
					SELECT  ist.UserGruppeID
					FROM			@usersInGroups AS ugs 
						INNER JOIN	UserGruppe AS soll		ON ugs.GroupId = soll.UserID
						INNER JOIN	UserGruppe as ist	ON ugs.UserID = ist.UserID AND soll.GruppeID  = ist.GruppeID AND soll.BerechtigungsbereichID = ist.BerechtigungsbereichID
				)
		AND (UserGruppe.UserID NOT IN (SELECT * FROM @usersAuthorizedByGroup))
	
	UPDATE UserGruppe SET UserGruppe.Schreiben = SollSchreiben, UserGruppe.OptionsFlag=SollOp
	FROM 
	(SELECT ugs.UserId, g.GruppeID, BerechtigungsbereichID, MAX(CAST(g.Schreiben AS INT)) AS SollSchreiben,
			 MAX(g.OptionsFlag&1)+MAX(g.OptionsFlag&2)+MAX(g.OptionsFlag&4)+MAX(g.OptionsFlag&8)+MAX(g.OptionsFlag&16)
			 +MAX(g.OptionsFlag&32)+MAX(g.OptionsFlag&64)+MAX(g.OptionsFlag&128)+MAX(g.OptionsFlag&256)
			 +MAX(g.OptionsFlag&512)+MAX(g.OptionsFlag&1024) AS SollOp 
		FROM @usersInGroups AS ugs 
			INNER JOIN UserGruppe AS G ON ugs.GroupId = G.UserID
		GROUP BY ugs.UserId , g.GruppeID, BerechtigungsbereichID) AS soll 
	INNER JOIN UserGruppe  ON 
			soll.UserId = UserGruppe.UserID AND 
			soll.GruppeID = UserGruppe.GruppeID AND 
			soll.BerechtigungsbereichID = UserGruppe.BerechtigungsbereichID
	WHERE
		(SollSchreiben <> UserGruppe.Schreiben OR SollOp <> UserGruppe.OptionsFlag)
		AND UserGruppe.IsInherited = 1
		AND (UserGruppe.UserId = @currentUserId OR @forAllUsers = 1)
		AND (UserGruppe.UserID NOT IN (SELECT * FROM @usersAuthorizedByGroup))	
	
	--Jeden User in Gruppe Kontaktdaten Jeder
	INSERT INTO UserGruppe(GruppeID ,UserID ,Schreiben, LM, UM, LC, UC, BerechtigungsbereichID, OptionsFlag)
	SELECT -1, UserPw.UserID, 0, GETDATE(), 0 ,GETDATE(), 0, 0, 0  
	FROM UserPw LEFT JOIN (SELECT * FROM UserGruppe WHERE BerechtigungsbereichID = 0 AND GruppeID = -1) AS UG on UserPW.UserID=UG.UserID WHERE UG.UserGruppeID IS NULL

	DELETE FROM UserGruppe WHERE BerechtigungsbereichID <> 0 AND GruppeID = -1
	
	INSERT INTO UserForAccess (SpzNr,UserID,IsInherited,LM,UM,LC,UC)
	SELECT DISTINCT soll.SpzNr, ugs.UserID, 1, GETDATE(), -1, GETDATE(), -1
	FROM	@usersInGroups AS ugs 
		INNER JOIN	UserForAccess AS soll	ON ugs.Groupid = soll.UserID
		LEFT JOIN	UserForAccess AS ist	ON ugs.UserID = ist.UserID AND soll.SpzNr=ist.SpzNr
	WHERE  ist.UserForAccessID IS NULL AND (ugs.UserId = @currentUserId OR @forAllUsers = 1)

	DELETE FROM UserForAccess 
	WHERE IsInherited = 1 AND  UserForAccessID NOT IN (
		SELECT      ist.UserForAccessID
		FROM        @usersInGroups          AS ugs
		INNER JOIN	UserForAccess soll      ON ugs.GroupId = soll.UserID
		INNER JOIN	UserForAccess ist       ON ugs.UserId = ist.UserID  AND soll.SpzNr=ist.SpzNr
	) AND (UserId = @currentUserId OR @forAllUsers = 1)
	AND (UserID NOT IN (SELECT * FROM @usersAuthorizedByGroup))
END	
GO
GRANT EXECUTE ON InherhitPermissions TO Public
GO

--anpassungen an AddCurrentUser
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AddCurrentUser'))
BEGIN
	DROP PROCEDURE dbo.AddCurrentUser
END
go
CREATE PROCEDURE dbo.AddCurrentUser
AS
BEGIN
	DECLARE @id INT
	IF  NOT EXISTS (SELECT * FROM  UserPW WHERE UserName = Current_User)
		BEGIN
			DECLARE @authorizedByGroup BIT
			SET @authorizedByGroup = 0

			SELECT @authorizedByGroup =
				CASE 
					WHEN EXISTS (SELECT * FROM [sys].[database_principals] dp WHERE dp.[type] IN ('S', 'U') AND IS_MEMBER(dp.name) = 1)
					THEN 0
					ELSE 1
				END

			INSERT INTO UserPW( UserName, lm, lc, Um, uc, IsAuthorizedByWindowsGroup) VALUES (Current_User, GetDate(), GetDate(), 0, 0, @authorizedByGroup)
			SELECT @id = ISNULL(scope_identity(), 0)
			UPDATE UserPW SET um = @id, uc = @id WHERE UserId =  @id
		END
	SET NOCOUNT ON;
END
GO

GRANT EXECUTE ON AddCurrentUser TO Public
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'Settings' and o.id = OBJECT_ID(N'dbo.HDMVorlagen'))
BEGIN
	ALTER TABLE dbo.HDMVorlagen ADD Settings VARCHAR(MAX)
	ALTER TABLE dbo.HDMVorlagen ALTER COLUMN HDMBez VARCHAR(30) NOT NULL
END
GO

--2015-08-12 BM Anzahl der möglichen Zeichen einer Ressourcenbemerkung erhöht.
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Note' AND o.id = OBJECT_ID(N'[dbo].Resources') AND c.prec < 1000)
BEGIN
	ALTER TABLE Resources ALTER COLUMN Note VARCHAR(1000) NULL
END
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'Edition' and o.id = OBJECT_ID(N'dbo.WorkVersions'))
BEGIN
	ALTER TABLE dbo.WorkVersions ADD Edition VARCHAR(32) NULL
END
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'Keywords' and o.id = OBJECT_ID(N'dbo.Works'))
BEGIN
	ALTER TABLE dbo.Works ADD Keywords VARCHAR(150) NULL
END
GO


IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'RolesNote' and o.id = OBJECT_ID(N'dbo.WorkVersions'))
BEGIN
	ALTER TABLE dbo.WorkVersions ADD RolesNote VARCHAR(MAX) NULL
	ALTER TABLE dbo.WorkVersions ADD MaterialIsOnlyRentable BIT NOT NULL CONSTRAINT [DF_WorkVersions_MaterialIsOnlyRentable] DEFAULT 0
END
GO

--2015-07-15 DH
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Travels]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[Travels](
		TravelId INT IDENTITY(1,1) NOT NULL,
		ContractId INT NULL,
		[Description] VARCHAR(250) NULL,
		Note VARCHAR(4000) NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		OwnerGroupId INT NOT NULL,
	 CONSTRAINT [PK_Travel] PRIMARY KEY CLUSTERED 
	([TravelId] ASC) ON [PRIMARY]) ON [PRIMARY]
END
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.SetActivityHistory'))	DROP PROCEDURE dbo.SetActivityHistory
go
CREATE PROCEDURE dbo.SetActivityHistory
	@ActivetyHistoryId INT,
	@note VARCHAR(MAX),
	@relevant BIT
AS
	UPDATE XHDelAufführg 
	SET		XHBem = @note,
			XHRelevant = @relevant
	WHERE XHID	= @ActivetyHistoryId
go
GRANT EXECUTE ON [dbo].SetActivityHistory TO public
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.SetCastHistory'))	DROP PROCEDURE dbo.SetCastHistory
go
CREATE PROCEDURE dbo.SetCastHistory
	@CastHistoryId INT,
	@note VARCHAR(MAX),
	@relevant BIT
AS
	UPDATE XHDelBesetzung
	SET		XHBem = @note,
			XHRelevant = @relevant
	WHERE XHID	= @CastHistoryId
go
GRANT EXECUTE ON [dbo].SetCastHistory TO public


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TravelStepTypes]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[TravelStepTypes](
		TravelStepTypeId INT IDENTITY(1,1) NOT NULL,
		Name VARCHAR (50) NULL,
		TravelStep INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL
	 CONSTRAINT [PK_TravelStepTypes] PRIMARY KEY CLUSTERED 
	([TravelStepTypeId] ASC) ON [PRIMARY]) ON [PRIMARY]
END
GO
GRANT SELECT ON [TravelStepTypes] TO public
GRANT UPDATE, INSERT, DELETE ON [TravelStepTypes] TO [Stammdaten ändern]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TravelStepTemplates]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[TravelStepTemplates](
		TravelStepTemplateId INT IDENTITY(1,1) NOT NULL,
		Name VARCHAR(50) NOT NULL,
		Note VARCHAR(500) NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL
	CONSTRAINT [PK_TravelStepTemplates] PRIMARY KEY CLUSTERED 
	([TravelStepTemplateId] ASC) ON [PRIMARY]) ON [PRIMARY]
END
GRANT SELECT ON [TravelStepTemplates] TO public
GRANT UPDATE, INSERT, DELETE ON [TravelStepTemplates] TO [Stammdaten ändern]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TravelSteps]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[TravelSteps](
		TravelStepId INT IDENTITY(1,1) NOT NULL,
		TravelId INT NOT NULL,
		StepTypeId INT NOT NULL,
		Start DATETIME NULL,
		[End] DATETIME NULL,
		[From] VARCHAR(50) NULL,
		Destination VARCHAR(50) NULL,
		OrganisationId INT NULL,
		BookingStatus INT NOT NULL,
		BookingNumber VARCHAR(50) NULL,
		TicketNumber VARCHAR(50) NULL,
		BookingName VARCHAR(100) NULL,
		Costs MONEY NOT NULL,
		PayedByTravler BIT NULL,
		Notes VARCHAR(500) NULL,
		Settings VARCHAR(max) NULL,
		Sort INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		ParentStepId INT NULL,
	CONSTRAINT [PK_TravelSteps] PRIMARY KEY CLUSTERED ([TravelStepId] ASC) ON [PRIMARY],
	CONSTRAINT [FK_TravelSteps_Travels] FOREIGN KEY([TravelId]) REFERENCES [dbo].[Travels] ([TravelId]),
	CONSTRAINT [FK_TravelSteps_TravelSteps] FOREIGN KEY([StepTypeId]) REFERENCES [dbo].[TravelStepTypes] ([TravelStepTypeId])
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TravelStepsTemplateSteps]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[TravelStepsTemplateSteps](
		TravelStepTemplateStepId INT IDENTITY(1,1) NOT NULL,
		TravelStepTypeId INT NOT NULL,
		PayedByTravler BIT NULL,
		Notes VARCHAR(500) NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		Sort INT NOT NULL,
		TravelStepTemplateId INT NOT NULL,
	 CONSTRAINT [PK_TravelStepsTemplateSteps] PRIMARY KEY CLUSTERED	([TravelStepTemplateStepId] ASC) ON [PRIMARY],
	 CONSTRAINT [FK_TravelStepsTemplateSteps_TravelStepTemplates] FOREIGN KEY (TravelStepTemplateID) REFERENCES [dbo].[TravelStepTemplates](TravelStepTemplateId),
	 CONSTRAINT [FK_TravelStepsTemplateSteps_TravelStepTypes] FOREIGN KEY (TravelStepTypeId) REFERENCES [dbo].[TravelStepTypes](TravelStepTypeId)
	 ) ON [PRIMARY]
END
GRANT SELECT ON [TravelStepsTemplateSteps] TO public
GRANT UPDATE, INSERT, DELETE ON [TravelStepsTemplateSteps] TO [Stammdaten ändern]
--view für Travels
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbTravels]'))
	DROP VIEW [dbo].[AbTravels]
GO

CREATE VIEW [dbo].[AbTravels]
AS 
SELECT 	T.*
FROM	Travels AS T
			INNER JOIN	(
			SELECT GruppeID, Schreiben FROM UserGruppe 
			INNER JOIN		UserPW		ON UserGruppe.Userid = UserPW.Userid 
			WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 15
		) AS UG ON T.OwnerGroupId = UG.GruppeID 
GO
-- berechtigungen alle auf public
GRANT SELECT ON [AbTravels] TO public
GO 
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbTravels_Update]'))
	DROP VIEW [dbo].[AbTravels_Update]
GO
CREATE VIEW [dbo].[AbTravels_Update]
AS 
SELECT * FROM Travels WHERE TravelId IN (
	SELECT ts.TravelId FROM Travels AS ts
	INNER  JOIN	(SELECT DISTINCT GruppeID, Schreiben 
						FROM			UserGruppe 
						INNER JOIN		UserPW		ON UserGruppe.Userid = UserPW.Userid 
						WHERE Username = CURRENT_USER  AND BerechtigungsbereichID = 15) AS UG
				ON ts.OwnerGroupId = UG.GruppeID
	WHERE UG.Schreiben = 1)

	WITH CHECK OPTION
GO
-- berechtigungen alle auf public
GRANT SELECT, UPDATE, INSERT, DELETE ON [AbTravels_Update] TO public
GO

--view für Travelsteps
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbTravelsteps]'))
	DROP VIEW [dbo].[AbTravelsteps]
GO

CREATE VIEW [dbo].[AbTravelSteps]
AS 
SELECT ts.* FROM TravelSteps AS ts INNER JOIN Travels AS t on t.TravelId =ts.TravelId INNER JOIN (
		SELECT GruppeID, Schreiben FROM UserGruppe 
		INNER JOIN		UserPW 		ON UserGruppe.Userid = UserPW.Userid 
		WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 15		
	) AS UG ON T.OwnerGroupId = UG.GruppeID 
GO
-- berechtigungen alle auf public
GRANT SELECT ON [AbTravelsteps] TO public
GO 
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbTravelsteps_Update]'))
	DROP VIEW [dbo].[AbTravelsteps_Update]
GO
CREATE VIEW [dbo].[AbTravelsteps_Update]
AS 
SELECT ts.* FROM TravelSteps AS ts 
	WHERE ts.TravelId IN (SELECT TravelId
							FROM  Travels AS t
								INNER JOIN  UserGruppe AS ug ON t.OwnerGroupId = ug.GruppeID
								INNER JOIN		UserPW 		 ON ug.Userid = UserPW.Userid 
							WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 15 AND ug.Schreiben = 1) 
	WITH CHECK OPTION
GO
-- berechtigungen alle auf public
GRANT SELECT, UPDATE, INSERT, DELETE ON [AbTravelsteps_Update] TO public
GO

--2015-07-20 BM
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'JourneyIsOrganizedByUs' AND o.id = OBJECT_ID(N'[dbo].[VertragDetails]'))
BEGIN
	ALTER TABLE VertragDetails ADD JourneyIsOrganizedByUs BIT NULL
END
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'AccommodationIsOrganizedByUs' AND o.id = OBJECT_ID(N'[dbo].[VertragDetails]'))
BEGIN
	ALTER TABLE VertragDetails ADD AccommodationIsOrganizedByUs BIT NULL
END
GO

IF  NOT EXISTS (SELECT * FROM  TravelStepTypes WHERE TravelStepTypeId = 0)
BEGIN
	SET IDENTITY_INSERT TravelStepTypes ON
	INSERT INTO [dbo].[TravelStepTypes] ([TravelStepTypeId],[Name],[TravelStep],[LM],[UM],[LC],[UC])
		VALUES (0, 'Undefiniert' ,0 ,GetDate() ,-1 ,GetDate() ,-1)
	SET IDENTITY_INSERT TravelStepTypes OFF
END
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'LocationTypeId' AND o.id = OBJECT_ID(N'[dbo].[ZR]'))
BEGIN
	ALTER TABLE ZR ADD LocationTypeId INT NOT NULL CONSTRAINT [DF_Location_LocationTypeId] DEFAULT 1 
	EXEC('UPDATE ZR SET LocationTypeId = 2 WHERE IsRehearsalStage = 1')
	EXEC('UPDATE ZR SET LocationTypeId = 0 WHERE ZRNr = 0')
	ALTER TABLE ZR DROP CONSTRAINT DF_Zr_IsRehearsalStage
	ALTER TABLE Zr DROP COLUMN  IsRehearsalStage
END
GO
IF  EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsRehearsalStage' AND o.id = OBJECT_ID(N'dbo.ZR'))
BEGIN
	ALTER TABLE Zr DROP COLUMN  IsRehearsalStage
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsInactive' AND o.id = OBJECT_ID(N'dbo.ZR'))
BEGIN
	ALTER TABLE ZR ADD IsInactive BIT NOT NULL CONSTRAINT [DF_Location_IsInactive] DEFAULT 0 
	EXEC('UPDATE ZR SET IsInactive = 1 WHERE Alt = 1')

	EXEC('UPDATE ZR SET AdressPersNr = NULL WHERE NOT AdressPersNr IS NULL AND ( NOT EXISTS (SELECT * FROM PersH WHERE PersNr = AdressPersNr) OR AdressPersNr IN (0,260))')
	ALTER TABLE dbo.zr  WITH CHECK ADD  CONSTRAINT FK_Location_Contact FOREIGN KEY(AdressPersNr)
	REFERENCES dbo.Persh (Persnr)
	ALTER TABLE dbo.zr CHECK CONSTRAINT FK_Location_Contact
END

GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'LocationId' AND o.id = OBJECT_ID(N'[dbo].[Resources]'))
BEGIN
	ALTER TABLE Resources ADD LocationId INT NULL
END
GO

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 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

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Bemkurz' AND o.id = OBJECT_ID(N'[dbo].Aufführgtabelle') AND c.prec < 150)
BEGIN
	ALTER TABLE Aufführgtabelle ALTER COLUMN Bemkurz VARCHAR(150)
END
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'BemKurz2' AND o.id = OBJECT_ID(N'[dbo].Aufführgtabelle') AND c.prec < 150)
BEGIN
	ALTER TABLE Aufführgtabelle ALTER COLUMN BemKurz2 VARCHAR(150)
END
GO
--TK2015-09-10
GO
DropColumn 'Dienstplan','azx'
GO
DropColumn 'Dienstplan','az1x'
GO
DropColumn 'Dienstplan','az2x'
GO
DropColumn 'Dienstplan','ZyklusStatus'
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'RegularWorkingTimeIsManual' AND o.id = OBJECT_ID(N'dbo.Dienstplan'))
BEGIN
	ALTER TABLE Dienstplan ADD RegularWorkingTimeIsManual BIT NOT NULL CONSTRAINT [DF_DutyRoster_RegularWorkingTimeIsManual] DEFAULT 0
	EXEC ('UPDATE Dienstplan SET RegularWorkingTimeIsManual = 1 WHERE AzBerechnen = 0')
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'DynamicAssignRegularWorkingtime' AND o.id = OBJECT_ID(N'dbo.PersoParameter'))
BEGIN
	ALTER TABLE PersoParameter ADD DynamicAssignRegularWorkingtime BIT NOT NULL CONSTRAINT [DF_Tarif_DynamicAssignRegularWorkingtime] DEFAULT 0
END
GO
UPDATE UserGruppe SET OptionsFlag = 1 WHERE BerechtigungsbereichID = 4 AND Schreiben = 1 AND OptionsFlag = 0
UPDATE UserGruppe SET OptionsFlag = 1 WHERE BerechtigungsbereichID = 2 AND Schreiben = 1 AND OptionsFlag = 0
GO
GRANT DELETE, INSERT, UPDATE ON dbo.ResourceTypes TO WorksWrite
GO

-- 20150826 DH 
-- Neue Notizspalte für Freigabemodelle
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'Note' and o.id = OBJECT_ID(N'dbo.DutyRosterReleaseModels'))
BEGIN
ALTER TABLE dbo.DutyRosterReleaseModels ADD Note NVARCHAR(200) NULL;
END
GO

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ISMN' AND o.id = OBJECT_ID(N'dbo.WorkVersions') AND c.prec < 13)
BEGIN
	ALTER TABLE dbo.WorkVersions ALTER COLUMN ISMN VARCHAR(13) NULL
	ALTER TABLE dbo.WorkVersions ALTER COLUMN Edition VARCHAR(50) NULL
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.ResourceContainer') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE dbo.ResourceContainer(
		ResourceContainerId INT IDENTITY(1,1) NOT NULL,
		Name VARCHAR(64) NULL,
		Code VARCHAR(16) NULL,
		Note VARCHAR(512) NULL,
		WorkVersionId INT NOT NULL,
		OwnerId INT NOT NULL,
		LocationId INT NULL,
		ResourceContainerData VARCHAR(MAX),
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL
	CONSTRAINT [PK_ResourceContainer] PRIMARY KEY CLUSTERED (ResourceContainerId ASC) ON [PRIMARY],
	CONSTRAINT [FK_ResourceContainer_WorkVersion] FOREIGN KEY(WorkVersionId) REFERENCES dbo.WorkVersions (WorkVersionId),
	CONSTRAINT [FK_ResourceContainer_Owner] FOREIGN KEY(OwnerId) REFERENCES dbo.Gruppe (Gruppeid),
	CONSTRAINT [FK_ResourceContainer_Location] FOREIGN KEY(LocationId) REFERENCES dbo.zr (zrnr)
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
	UPDATE Gruppe SET Modul = 64 WHERE Modul = 1 AND GruppeBez = 'Bibliothek'

END
GO
GRANT SELECT, UPDATE, INSERT, DELETE ON ResourceContainer TO WorksWrite
GO
GRANT SELECT ON ResourceContainer TO Public
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'ResourceContainerId' and o.id = OBJECT_ID(N'dbo.Resources'))
BEGIN
ALTER TABLE dbo.Resources ADD ResourceContainerId INT NULL;
	ALTER TABLE dbo.Resources  WITH CHECK ADD CONSTRAINT FK_Resources_ResourceContainer FOREIGN KEY(ResourceContainerId)
		REFERENCES dbo.ResourceContainer (ResourceContainerId)
	ALTER TABLE dbo.Resources CHECK CONSTRAINT FK_Resources_ResourceContainer
END
GO

IF  NOT EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'FK_Resources_Location'))
BEGIN
	ALTER TABLE dbo.Resources  WITH CHECK ADD CONSTRAINT FK_Resources_Location FOREIGN KEY(LocationId) REFERENCES dbo.Zr (ZrNr)
	ALTER TABLE dbo.Resources CHECK CONSTRAINT FK_Resources_Location
END
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetActivityTypeNumbersPerformance'))	DROP FUNCTION dbo.GetActivityTypeNumbersPerformance
GO
CREATE FUNCTION dbo.GetActivityTypeNumbersPerformance(@start DATETIME, @end DATETIME)
		RETURNS TABLE
		AS
			RETURN (SELECT Row_Number() OVER (PARTITION BY Snr ORDER BY Datum, CONVERT(CHAR(5), UhrBeg, 108)) AS RowNum, AuffNr, Datum
					FROM AufführgTabelle AS A
						INNER JOIN AuffTypen AS AT ON AT.AuffTnr = A.Art
					WHERE KatNr = 0 AND Datum BETWEEN @start AND @end AND [Status] BETWEEN 0 AND 99 )
GO
GRANT SELECT ON GetActivityTypeNumbersPerformance TO Public
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetActivityTypeNumbersPerformanceByStatus'))	DROP FUNCTION dbo.GetActivityTypeNumbersPerformanceByStatus
GO
CREATE FUNCTION dbo.GetActivityTypeNumbersPerformanceByStatus(@start DATETIME, @end DATETIME, @variantFilter VARCHAR(512))
		RETURNS TABLE
		AS
			RETURN (SELECT Row_Number() OVER (PARTITION BY Snr ORDER BY Datum, CONVERT(CHAR(5), UhrBeg, 108)) AS RowNum, AuffNr, Datum
					FROM AufführgTabelle AS A
						INNER JOIN AuffTypen AS AT ON AT.AuffTnr = A.Art
					WHERE KatNr = 0 AND Datum BETWEEN @start AND @end AND [Status] IN (SELECT OrderID FROM SplitIds(@variantFilter)))
GO
GRANT SELECT ON GetActivityTypeNumbersPerformanceByStatus TO Public
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetActivityTypeNumbersRehearsal')) 	DROP FUNCTION dbo.GetActivityTypeNumbersRehearsal
GO
CREATE FUNCTION dbo.GetActivityTypeNumbersRehearsal(@start DATETIME, @end DATETIME)
		RETURNS TABLE
		AS
			RETURN (SELECT Row_Number() OVER (PARTITION BY Art, Snr ORDER BY Datum, CONVERT(CHAR(5), UhrBeg, 108)) AS RowNum, AuffNr, Datum
					FROM AufführgTabelle AS A
					INNER JOIN AuffTypen AS AT ON AT.AuffTnr = A.Art 
				WHERE AT.LfdNr = 1 AND Datum BETWEEN @start AND @end AND [Status] BETWEEN 0 AND 99)
GO
GRANT SELECT ON GetActivityTypeNumbersRehearsal TO PUBLIC
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetActivityTypeNumbersRehearsalByStatus')) 	DROP FUNCTION dbo.GetActivityTypeNumbersRehearsalByStatus
GO
CREATE FUNCTION dbo.GetActivityTypeNumbersRehearsalByStatus(@start DATETIME, @end DATETIME, @variantFilter VARCHAR(512))
		RETURNS TABLE
		AS
			RETURN (SELECT Row_Number() OVER (PARTITION BY Art, Snr ORDER BY Datum, CONVERT(CHAR(5), UhrBeg, 108)) AS RowNum, AuffNr, Datum
			FROM AufführgTabelle AS A
			INNER JOIN AuffTypen AS AT ON AT.AuffTnr = A.Art 
		WHERE AT.LfdNr = 1 AND Datum BETWEEN @start AND @end AND [Status] IN (SELECT OrderID FROM SplitIds(@variantFilter)))

GO
GRANT SELECT ON GetActivityTypeNumbersRehearsalByStatus TO PUBLIC
GO

--BM 20151023
--Zuordnung der Bühnengruppe bei den Bühnen brauch keinen Default-Wert
IF (SELECT c.cdefault FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id
       WHERE c.name = 'ZrAbschnitt' AND o.id = OBJECT_ID(N'dbo.ZR')) <> 0
BEGIN
       EXECUTE sp_unbindefault N'dbo.ZR.ZrAbschnitt'
END
GO

-- 20150828 DH 
-- Neue Spalte für Gastierurlaube
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'GuestHoliday' and o.id = OBJECT_ID(N'dbo.VertragDetails'))
BEGIN
	ALTER TABLE dbo.VertragDetails ADD GuestHoliday FLOAT NULL;
END
GO

-- 20150903 BM
-- Pläne: Neues Feld für Informationen unterhalb der eigentlichen Tabelle
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'B1' and o.id = OBJECT_ID(N'dbo.Plan'))
BEGIN
	ALTER TABLE [dbo].[Plan] ADD B1 NVARCHAR(MAX) NULL;
	ALTER TABLE [dbo].[Plan] ADD B1Fett BIT NULL;
	ALTER TABLE [dbo].[Plan] ADD B1Schrift TINYINT NULL;
	ALTER TABLE [dbo].[Plan] ADD B1Ausri TINYINT NULL;
END
GO

--2015-09-09 sh
-- neue Spalte AprtmentId für TravelSteps
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE c.name = 'ApartmentId' and o.id = OBJECT_ID(N'dbo.TravelSteps'))
BEGIN
	ALTER TABLE dbo.TravelSteps ADD ApartmentId INT NULL;
	ALTER TABLE dbo.TravelSteps  WITH CHECK ADD CONSTRAINT FK_TravelSteps_ZR FOREIGN KEY(ApartmentId)
	REFERENCES dbo.ZR (ZRNr)
	ALTER TABLE [dbo].TravelSteps CHECK CONSTRAINT FK_TravelSteps_ZR
END
GO

--2015-09-28 dh
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'StartTime' AND o.id = OBJECT_ID(N'[dbo].[TravelSteps]')) 
BEGIN
	ALTER TABLE TravelSteps ADD StartTime DATETIME NULL;
	ALTER TABLE Travelsteps ADD EndTime DATETIME NULL;
END
GO

--2015-10-01 sh
--unique constraint auf CostCenter_OwnerGroup für die FK Kombination
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[UQ_CostCenter_OwnerGroup_CostCenterId_OwnerGroupId]') AND xtype = 'UQ')
BEGIN
	DELETE FROM dbo.CostCenter_OwnerGroup WHERE CostCenterOwnerGroupId NOT IN (SELECT MIN(CostCenterOwnerGroupId) FROM dbo.CostCenter_OwnerGroup GROUP BY CostCenterId, OwnerGroupId)
	ALTER TABLE dbo.CostCenter_OwnerGroup ADD CONSTRAINT UQ_CostCenter_OwnerGroup_CostCenterId_OwnerGroupId UNIQUE NONCLUSTERED (CostCenterId, OwnerGroupId) ON [PRIMARY]
END
GO

--20151005 dh StornierStatus für Bestellungen

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'CancelOrderStatusId' AND o.id = OBJECT_ID(N'[dbo].[OrderValidationApprovalStatus]')) 
BEGIN
	ALTER TABLE [dbo].[OrderValidationApprovalStatus] ADD CancelOrderStatusId int null;
	ALTER TABLE [dbo].[OrderValidationApprovalStatus] ADD CancelBehavior int not null constraint DF_OrderValidationApprovalStatus_CancelBehaviour default 0;
	ALTER TABLE dbo.[OrderValidationApprovalStatus]  WITH CHECK ADD CONSTRAINT FK_OrderValidationApprovalStatus_OrderApprovalStatus FOREIGN KEY(CancelOrderStatusId)
	REFERENCES dbo.OrderApprovalStatus (OrderApprovalStatusId)
	ALTER TABLE [dbo].[OrderValidationApprovalStatus] CHECK CONSTRAINT FK_OrderValidationApprovalStatus_OrderApprovalStatus
END

--20151019 dh Korrektur/Cancelstatus für Bestellungen
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'CorrectionStatus' AND o.id = OBJECT_ID(N'[dbo].[Orders]')) 
BEGIN
	ALTER TABLE [dbo].[Orders] ADD CorrectionStatus INT NOT NULL constraint DF_Orders_CorrectionStatus DEFAULT(0)
END
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'CanEditCanceledMetaData' AND o.id = OBJECT_ID(N'[dbo].[OrderValidationApprovalStatus]')) 
BEGIN
	ALTER TABLE [dbo].[OrderValidationApprovalStatus] ADD CanEditCanceledMetaData bit not null constraint DF_OrderValidationApprovalStatus_CanEditCanceledMetaData default 0;
END


--TK20150930
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id WHERE c.name = 'FixCostUnitId' and o.id = OBJECT_ID(N'dbo.Zulagen'))
BEGIN
	ALTER TABLE dbo.Zulagen ADD FixCostUnitId INT NULL;
	ALTER TABLE dbo.Zulagen  WITH CHECK ADD CONSTRAINT FK_Bonus_CostUnit FOREIGN KEY(FixCostUnitId)
		REFERENCES dbo.CostUnit (CostUnitId) ON DELETE SET NULL
	ALTER TABLE dbo.Zulagen CHECK CONSTRAINT FK_Bonus_CostUnit
END

--2015-11-03
--Zuordnungen von Arbeitszeitmodellen und Perso-Verträgen
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('dbo.ContractPersoWorkingCycles') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.ContractPersoWorkingCycles ( 
		ContractPersoWorkingCycleId INT IDENTITY(1,1) NOT NULL,
		ContractPersoId INT NOT NULL,
		WorkingCycleId INT NOT NULL,
		Offset INT NOT NULL,
		StartDate DATETIME NULL,
		EndDate DATETIME NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		CONSTRAINT PK_ContractPersoWorkingCycleId PRIMARY KEY CLUSTERED (ContractPersoWorkingCycleId ASC) ON [PRIMARY],
		CONSTRAINT FK_ContractPersoWorkingCycles_VertragPerso FOREIGN KEY (ContractPersoId) REFERENCES dbo.VertragPerso (ContractPersoId),
		CONSTRAINT FK_ContractPersoWorkingCycles_Zyklen FOREIGN KEY (WorkingCycleId) REFERENCES dbo.Zyklen (ZyklusNr),
	) ON [PRIMARY]

	--Dieser Code ist verkehrt. Auf ZyklusWo müsste 1 raufaddiert werden, damit es dem neuen Offset entspricht. 
	--Der Fehler wird aus Kompatibilitätsgründe in der Release-Section gefixt.
	exec('INSERT INTO dbo.ContractPersoWorkingCycles (ContractPersoId, WorkingCycleId, Offset, LC, UC, LM, UM)
	SELECT ContractPersoId, Zyklus, ZyklusWo, COALESCE(LC, GETDATE()), COALESCE(UC, 1), COALESCE(LM, GETDATE()), COALESCE(UM, 1) 
	FROM VertragPerso WHERE Zyklus IS NOT NULL')

	exec DeleteConstraint 'VertragPerso','Zyklus'
	exec DeleteConstraint 'VertragPerso','ZyklusWo'
	ALTER TABLE VertragPerso DROP CONSTRAINT FK_VertragPerso_Zyklen
	ALTER TABLE VertragPerso DROP COLUMN Zyklus
	ALTER TABLE VertragPerso DROP COLUMN ZyklusWo

END
GO	

GRANT SELECT ON dbo.ContractPersoWorkingCycles TO [Public] 
GRANT SELECT, INSERT, DELETE, UPDATE ON dbo.ContractPersoWorkingCycles TO PersoAdmin
GRANT SELECT, INSERT, DELETE, UPDATE ON dbo.ContractPersoWorkingCycles TO PersoWorkingModelWrite
GO

--UpdateView
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].AbContractPersoWorkingCycles_Update'))
BEGIN
DROP VIEW [dbo].AbContractPersoWorkingCycles_Update
END
GO
CREATE VIEW [dbo].AbContractPersoWorkingCycles_Update
AS
	SELECT * FROM ContractPersoWorkingCycles WHERE ContractPersoId IN (
		SELECT VertragPerso.ContractPersoId FROM UserGruppe 
		INNER JOIN	UserPW		 ON UserGruppe.Userid = UserPW.Userid 
		INNER JOIN	Vertrag		 ON Vertrag.ModulGruppeID = UserGruppe.GruppeID 
		INNER JOIN  VertragPerso ON VertragPerso.VertragsNr = Vertrag.VertragsNr
		WHERE Username = CURRENT_USER  AND BerechtigungsbereichID IN (1,5) AND UserGruppe.Schreiben = 1) 
WITH CHECK OPTION
GO

GRANT SELECT, UPDATE, INSERT, DELETE ON dbo.AbContractPersoWorkingCycles_Update TO [Public] 
GO

--Reference auf Schicht und Arbeitszeitmodell-Eintrag
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'AztNr' AND o.id = OBJECT_ID(N'dbo.ZyklusWochen') AND c.isnullable = 0)
BEGIN
	ALTER TABLE dbo.ZyklusWochen ALTER COLUMN AztNr INT NULL
	EXEC DeleteConstraint 'ZyklusWochen','AztNr'
	UPDATE ZyklusWochen SET AztNr = null WHERE AztNr IS NOT NULL AND NOT EXISTS (
		SELECT AztNr FROM AZTypen AS azt WHERE ZyklusWochen.AztNr = azt.AztNr AND azt.AztNr <> 0
	)
	ALTER TABLE ZyklusWochen ADD CONSTRAINT FK_ZyklusWochen_AZTypen FOREIGN KEY (AztNr) REFERENCES dbo.AZTypen (AZTNr)
END
GO

--20151106 BM
--Neue Einstellungen für TraveStepTemplateSteps
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ParentSort' AND o.id = OBJECT_ID(N'dbo.TravelStepsTemplateSteps'))
BEGIN
	ALTER TABLE TravelStepsTemplateSteps ADD ParentSort INT NULL
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id
	WHERE c.name = 'From' AND o.id = OBJECT_ID(N'dbo.TravelStepsTemplateSteps'))
BEGIN
	ALTER TABLE TravelStepsTemplateSteps ADD [From] NVARCHAR(50) NULL
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id
	WHERE c.name = 'Destination' AND o.id = OBJECT_ID(N'dbo.TravelStepsTemplateSteps'))
BEGIN
	ALTER TABLE TravelStepsTemplateSteps ADD Destination NVARCHAR(50) NULL
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'OrganisationId' AND o.id = OBJECT_ID(N'dbo.TravelStepsTemplateSteps'))
BEGIN
	ALTER TABLE TravelStepsTemplateSteps ADD OrganisationId INT NULL
END
GO
IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Sort' AND o.id = OBJECT_ID(N'dbo.TravelStepsTemplateSteps') AND c.isnullable = 1)
BEGIN
	UPDATE TravelStepsTemplateSteps SET [Sort] = 0 WHERE [Sort] IS NULL
	ALTER TABLE TravelStepsTemplateSteps ALTER COLUMN [Sort] INT NOT NULL
	ALTER TABLE TravelStepsTemplateSteps ADD CONSTRAINT DF_TravelStepsTemplateSteps_Sort DEFAULT (0) FOR [Sort]
END
GO

UPDATE PersH SET Geschlecht = CASE WHEN PersKat.AdressArt <= 6 THEN 0 ELSE -2 END 
		FROM PersH INNER JOIN PersKat ON persh.persKatNr = PersKat.PersKatNr WHERE Geschlecht IS NULL
ALTER TABLE [dbo].[PersH] ALTER COLUMN Geschlecht INT NOT NULL
GO

UPDATE PersH SET Gesperrt = 0 WHERE Gesperrt IS NULL
ALTER TABLE [dbo].[PersH] ALTER COLUMN Gesperrt BIT NOT NULL
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PersH_Gesperrt_Default]') AND type = 'D')
	ALTER TABLE [dbo].[PersH] DROP CONSTRAINT [PersH_Gesperrt_Default]
GO

IF NOT EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'DF_PersH_Gesperrt'))
BEGIN
	ALTER TABLE dbo.PersH ADD CONSTRAINT DF_PersH_Gesperrt DEFAULT 0 FOR gesperrt
END
GO


IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[PersH_Geschlecht_Default]') AND type = 'D')
	ALTER TABLE [dbo].[PersH] DROP CONSTRAINT [PersH_Geschlecht_Default]
GO

IF NOT EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'DF_PersH_Geschlecht'))
BEGIN
	ALTER TABLE dbo.PersH ADD CONSTRAINT DF_PersH_Geschlecht DEFAULT -2 FOR Geschlecht
END
GO

ALTER PROCEDURE [dbo].[DeleteGroup] 
	@Id INT
AS

BEGIN
	SET NOCOUNT ON

	CREATE TABLE #Msg(cnt int, tbl varchar(100))

	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Contacts'		FROM PersH			WHERE  Besitzer = @id
	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Productions'	FROM Stücke			WHERE  ModuleGroupId = @id
	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Contracts'	FROM Vertrag		WHERE  ModulGruppeID = @id
	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Roles'		FROM Besetzungstd	WHERE  Besitzer = @id
	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Departments'	FROM DienstplanArtenTabelle WHERE  ModulGruppeID = @id
	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Tours'		FROM Tours WHERE OwnerId = @id
	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'Exports'		FROM ExportProfiles WHERE OwnerGroupId = @id
	INSERT INTO #Msg (cnt, tbl) SELECT COUNT(*), 'ResourceContainer' FROM ResourceContainer  WHERE OwnerId = @id

	IF NOT EXISTS (SELECT * FROM  #Msg WHERE cnt > 0) 
		BEGIN
			DELETE FROM UserGruppe WHERE GruppeID = @id
			DELETE FROM PersonModulGroupPermission WHERE ModulGroupId = @id
			DELETE FROM CostCenter_OwnerGroup WHERE OwnerGroupId = @id
			DELETE FROM ActivityTypeGroups WHERE GroupId = @id
			DELETE FROM Gruppe WHERE GruppeID = @id
		END
	ELSE 
		BEGIN 
			SELECT * FROM #Msg  WHERE cnt > 0
		END
END

GRANT EXECUTE ON DeleteGroup TO  SecurityAdmin 
GO

----2015-10-21 sh
----spaltentyp von decimal auf float ändern

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'DiscountPercent' AND o.id = OBJECT_ID(N'[dbo].OrderPos') AND c.xtype = 106)
BEGIN
	ALTER TABLE OrderPos DROP CONSTRAINT DF_OrderPos_DiscountPercent
	ALTER TABLE OrderPos ALTER COLUMN DiscountPercent FLOAT NOT NULL
	ALTER TABLE OrderPos ADD CONSTRAINT DF_OrderPos_DiscountPercent DEFAULT 0 FOR DiscountPercent
END
GO

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ShiftCount' AND o.id = OBJECT_ID(N'[dbo].AuffDienstplanArten') AND c.xtype = 106)
BEGIN
	ALTER TABLE AuffDienstplanArten ALTER COLUMN ShiftCount FLOAT NULL
END
GO

IF EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ShiftCount' AND o.id = OBJECT_ID(N'[dbo].StückeDienstplan') AND c.xtype = 106)
BEGIN
	ALTER TABLE StückeDienstplan DROP CONSTRAINT DF_StückeDienstplan_ShiftCount
	ALTER TABLE StückeDienstplan ALTER COLUMN ShiftCount FLOAT NOT NULL
	ALTER TABLE StückeDienstplan ADD CONSTRAINT DF_StückeDienstplan_ShiftCount DEFAULT 1 FOR ShiftCount
END
GO
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'OwnerId' and o.id = OBJECT_ID(N'dbo.Resources'))
BEGIN

--Wenn für die Bibliothek eine Gruppe erstellt wurde bei dieser das Modul umschreiben


--Ansonsten neue Gruppe Erstellen
	IF EXISTS (SELECT *  FROM Resources INNER JOIN ResourceTypes AS rs ON Resources.ResourceTypeId = rs.ResourceTypeId
	WHERE rs.Module = 64) AND NOT EXISTS (SELECT *  FROM Gruppe WHERE Modul = 64)
	BEGIN
		INSERT INTO Gruppe (GruppeBez, Modul) VALUES ('Library', 64)
	END
	ALTER TABLE dbo.Resources ADD OwnerId INT NULL
	EXEC ('UPDATE Resources SET OwnerId =  (SELECT MAX(GruppeId) FROM Gruppe WHERE rs.Module &  Gruppe.Modul = Gruppe.Modul)
		FROM Resources INNER JOIN ResourceTypes AS rs ON Resources.ResourceTypeId = rs.ResourceTypeId')
	ALTER TABLE dbo.Resources ALTER COLUMN OwnerId INT NOT NULL
	
	ALTER TABLE dbo.Resources  WITH CHECK ADD CONSTRAINT FK_Resources_Owner FOREIGN KEY(OwnerId)
		REFERENCES dbo.Gruppe (GruppeId)
	ALTER TABLE dbo.Resources CHECK CONSTRAINT FK_Resources_Owner

	
	
	INSERT INTO UserGruppe (Userid, GruppeId, BerechtigungsbereichID, Schreiben)
		SELECT p.Userid, grp.GruppeId, 16, 1 FROM [dbo].[sysusers] AS u 
						INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
						INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
						INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
						CROSS  JOIN (SELECT * FROM Gruppe WHERE Modul = 64) AS grp 
					WHERE g.name = 'WorksWrite'
	
	INSERT INTO UserGruppe (Userid, GruppeId, BerechtigungsbereichID, Schreiben)
		SELECT p.Userid, grp.GruppeId, 16, 1 FROM [dbo].[sysusers] AS u 
						INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
						INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
						INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
						CROSS  JOIN (SELECT * FROM Gruppe WHERE Modul = 2) AS grp 
					WHERE g.name = 'TheaOrchester'

	INSERT INTO UserGruppe (Userid, GruppeId, BerechtigungsbereichID, Schreiben)
		SELECT p.Userid, grp.GruppeId, 16, 1 FROM [dbo].[sysusers] AS u 
						INNER JOIN [dbo].[sysmembers]   AS m ON  u.uid=m.memberuid
						INNER JOIN [dbo].[sysusers]     AS g ON  m.groupuid=g.uid
						INNER JOIN [dbo].[UserPW]		AS p ON  u.name=p.Username
						CROSS  JOIN (SELECT * FROM Gruppe WHERE Modul = 1 AND GruppeId > 0) AS grp 
					WHERE g.name = 'Spielplan ändern'

	END


GO

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

GO
CREATE VIEW [dbo].AbResources
AS 
SELECT  r.* FROM dbo.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 
GO
GRANT SELECT ON AbResources TO public
GO
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)
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResources_Update TO public
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbResourceObjects'))
DROP VIEW [dbo].AbResourceObjects

GO
CREATE VIEW [dbo].AbResourceObjects
AS 
SELECT  ro.* 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 AbResourceObjects TO public
GO
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)
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResourceObjects_Update TO public
GO

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

GO
CREATE VIEW [dbo].AbResourceBlockPeriods
AS 
SELECT  rb.* FROM ResourceBlockPeriods AS rb INNER JOIN dbo.Resources AS r ON rb.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 AbResourceBlockPeriods TO public
GO
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)
GO

GRANT SELECT, DELETE, INSERT, UPDATE ON AbResourceBlockPeriods_Update TO public
GO
--TO DO Berechtigungen entfernen

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'FeeNote' and o.id = OBJECT_ID(N'dbo.BesetzungStdSoliTabelle'))
BEGIN
	ALTER TABLE BesetzungStdSoliTabelle ADD FeeNote VARCHAR(1024) NULL
	ALTER TABLE BesetzungStdSoliTabelle ADD OvernightFee FLOAT NULL
END
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'CreditMinWorkingMinutes' and o.id = OBJECT_ID(N'dbo.PersoParameter'))
BEGIN
	ALTER TABLE PersoParameter ADD CreditMinWorkingMinutes INT NOT NULL CONSTRAINT DF_PersoCreditMinWorkingMinutes DEFAULT (0)

	UPDATE Zeitkonten SET settings = REPLACE(CONVERT(VARCHAR(MAX),settings),'EveryDayCountingTimeAccountSettings','SickAccountSettings') from Zeitkonten WHERE Formel = 3011 AND NOT Settings IS NULL
END
GRANT EXECUTE ON SetWorkVersionReferenceToProduction TO  WorksWrite 
GO

--2015-11-25 sh
--bugfix AbPersD : statt immer auf sort 0 bei adressen zu gehen, die erste nehmen
ALTER VIEW [dbo].[AbPersD]
AS
SELECT Ph.PersNr as pnr, AD.str, AD.ort,AD.plz,AD.land,AD.co,
	(SELECT TOP 1 Wert FROM PersKom WHERE  IsSecret=0 AND Sort = 0 AND PersKTypID =5 AND PersKom.PersNR=Ph.PersNr) AS Homepage,
	(SELECT TOP 1 Wert FROM PersKom WHERE  IsSecret=0 AND Sort = 0 AND PersKTypID =4 AND PersKom.PersNR=Ph.PersNr) AS eMail,
	(SELECT TOP 1 Wert FROM PersKom WHERE  IsSecret=0 AND Sort = 0 AND PersKTypID =3 AND PersKom.PersNR=Ph.PersNr) AS Fax,
	(SELECT TOP 1 Wert FROM PersKom WHERE  IsSecret=0 AND Sort = 0 AND PersKTypID =2 AND PersKom.PersNR=Ph.PersNr) AS Handy,
	(SELECT TOP 1 Wert FROM PersKom WHERE  IsSecret=0 AND Sort = 0 AND PersKTypID =1 AND PersKom.PersNR=Ph.PersNr) AS HausRuf,
	(SELECT TOP 1 Wert FROM PersKom WHERE  IsSecret=0 AND Sort = 0 AND PersKTypID =0 AND PersKom.PersNR=Ph.PersNr) AS Tel, 
	sub.schreiben,NULL AS InternetLink, NULL AS PersNrVorh, GebDatum, NULL AS KstAbt, RepetiSoliProSort,  [Memo], [National], GebOrt, MemoExtern, 
	Adressplan,0 as Plan_Person, NULL AS BildVerweis, MemoExtern_en, 0 AS InTrs, InWeb, VersicherungsNr, SteuerNr, Finanzamt, UstID, PassNr, 
	PassAusAmt, PassAusOrt, PassAusDatum, PassAblDatum
 FROM PersH  as Ph 
	LEFT JOIN PersonalDetails on ph.persnr=PersonalDetails.pnr 
	 LEFT JOIN (
		SELECT apa1.* FROM ABPersAdresse apa1
		INNER JOIN (
			SELECT a.PersNr, MIN(a.Sort) AS Sort
			FROM AbPersAdresse a
			GROUP BY a.PersNr
		) AS apa2 ON apa1.PersNr = apa2.PersNr AND apa1.Sort = apa2.Sort
	 ) as AD on ph.persnr=ad.persNr 
	 INNER  JOIN (	SELECT  pp.PersonId, MAX(CASE WHEN (OptionsFlag & 1 <> 1 OR PP.HasAccessToSecretContactData = 0) THEN 0 ELSE 1 END) AS SecretPermission , MAX(CAST(ug.schreiben as int)) as schreiben 
			FROM PersonModulGroupPermission AS PP
				INNER JOIN UserGruppe AS UG ON pp.ModulGroupId =  UG.GruppeID 
				INNER JOIN UserPW ON Ug.Userid = UserPW.Userid 
		WHERE BerechtigungsbereichID = 0 AND pp.PermissionType=0  AND Username = CURRENT_USER GROUP BY pp.PersonId) AS sub ON ph.PersNr = sub.PersonId 
	
	GO
	
--20151125 BM
--Mehr neue Einstellungen für TraveStepTemplateSteps
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Duration' AND o.id = OBJECT_ID(N'dbo.TravelStepsTemplateSteps'))
BEGIN
	ALTER TABLE TravelStepsTemplateSteps ADD Duration SMALLINT NOT NULL CONSTRAINT DF_TravelStepsTemplateSteps_Duration DEFAULT (0)
END
GO


--20151123 - DH - Neue Spalte Abgegoltene Arbeitszeit
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'UnpaidOvertimeMinutes' and o.id = OBJECT_ID(N'dbo.VertragsVorlagen'))
BEGIN
	ALTER TABLE VertragsVorlagen ADD UnpaidOvertimeMinutes INT CONSTRAINT DF_VertragsVorlagen_UnpaidOvertimeMinutes  DEFAULT (0) NOT NULL
END
GO

UPDATE PersoParameter SET FtPlusMaxAZ = 0 WHERE FtPlusMaxAZ = 1
GO
--20151208 Besetzungen in CostUnitWorkingTime

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'ActivityJobId' and o.id = OBJECT_ID(N'dbo.CostUnitWorkingTime'))
BEGIN

	UPDATE [CostUnitWorkingTime] SET [ShiftEnumValue] = 1  WHERE [ShiftEnumValue] IS NULL
	ALTER TABLE CostUnitWorkingTime ALTER COLUMN ShiftEnumValue TINYINT NOT NULL

	ALTER TABLE CostUnitWorkingTime ADD ActivityJobId INT NULL
	ALTER TABLE dbo.CostUnitWorkingTime  WITH CHECK ADD CONSTRAINT FK_CostUnitWorkingTime_ActivityJob FOREIGN KEY(ActivityJobId)
		REFERENCES dbo.AuffTechAufgabe (AuffTechAufgabeID)  on delete Cascade
	ALTER TABLE dbo.CostUnitWorkingTime CHECK CONSTRAINT FK_CostUnitWorkingTime_ActivityJob

	ALTER TABLE CostUnitWorkingTime ALTER COLUMN  CostUnitId INT NULL

	ALTER TABLE CostUnitWorkingTime ADD ActivityStartOffsetMinutes INT NULL CONSTRAINT [DF_CostUnitWorkingTime_ActivityStartOffsetMinutes] DEFAULT 0

	ALTER TABLE AuffTechAufgabe ALTER COLUMN AuffNr INT NOT NULL

	CREATE INDEX IX_CostUnitWorkingTime_Activity ON CostUnitWorkingTime (ActivityId) ON [PRIMARY]
	CREATE INDEX IX_CostUnitWorkingTime_ActivityJob ON CostUnitWorkingTime (ActivityJobId) ON [PRIMARY]
	CREATE INDEX IX_AuffTechAufgabe_Activity ON AuffTechAufgabe (Auffnr) ON [PRIMARY]
	EXEC ('INSERT INTO [dbo].[CostUnitWorkingTime]
		(ActivityJobId, ShiftDayId, [Minutes]
		,LM ,UM ,LC ,UC
		,ShiftEnumValue, ActivityID)
SELECT
		c.AuffTechAufgabeID ,d.DienstplanID ,0
		,c.lm,c.um,c.lc,c.uc
		,1, c.AuffNr
  FROM BesetzungTabelle AS c 
		INNER JOIN AufführgTabelle	AS a ON c.AuffNr = a.AuffNr
		INNER JOIN Vertrag			AS vr ON  c.persNr = vr.persnr  	AND (a.Datum >= vr.vvon OR vr.vvon IS NULL) AND (a.Datum <= vr.vbis OR vr.vbis IS NULL)
		INNER JOIN Dienstplan		AS d ON a.Datum = d.Datum and d.VertragsNr = vr.VertragsNr AND d.DienstplanEintragArtId = 0
  WHERE NOT AuffTechAufgabeID IS NULL')

  EXEC ('DELETE FROM BesetzungTabelle WHERE NOT AuffTechAufgabeID IS NULL')

  
	UPDATE AuffTechAufgabe SET AbGruppeID  = r.ABGruppeID FROM  AuffTechAufgabe AS AT inner join BesetzungStd as r on at.RolleID = r.id WHERE at.AbGruppeID IS NULL AND NOT r.ABGruppeID IS NULL
	DELETE FROM AuffTechAufgabe WHERE AbGruppeID IS NULL
	ALTER TABLE AuffTechAufgabe ALTER COLUMN  AbGruppeID INT NOT NULL

	

END
GO
IF  EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'AbGruppe' AND o.id = OBJECT_ID(N'dbo.AuffTechAufgabe'))
BEGIN
	ALTER TABLE AuffTechAufgabe DROP COLUMN  AbGruppe
END
GO

ALTER VIEW [dbo].[CastActiveties] 
AS
	SELECT	ac.Datum, ac.UhrBeg, ac.UhrEnde, ac.SNr, ac.ZrNr, ac.Art, ac.Bem, at.KatNr, ac.Status, ca.*,st.alt
	FROM	AufführgTabelle				AS ac
			INNER JOIN AuffTypen		AS at	ON ac.art = at.AuffTNr
			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
			LEFT 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
			LEFT  JOIN BesetzungStd		AS ro	ON ca.RolleID = ro.ID
			INNER  JOIN Gruppe			AS g	ON g.GruppeID = ro.Besitzer	
			INNER  JOIN UserGruppe		AS ugc	ON G.GruppeID = ugc.GruppeID AND ugc.UserID = ua.UserID AND ugc.BerechtigungsbereichID = 4
			LEFT JOIN Stücke			AS st	ON ac.SNr=st.SNr 
	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

ALTER VIEW [dbo].[AbCast_Update]
AS
	SELECT C.*
	FROM  BesetzungTabelle	AS C 
	WHERE RolleId IN (
		SELECT ID FROM BesetzungStd		AS Ro
			INNER JOIN Gruppe			AS g  ON g.GruppeID = ro.Besitzer	
			INNER JOIN UserGruppe		AS Ug ON G.GruppeID = Ug.GruppeID   
			INNER JOIN UserPW				  ON Ug.Userid  = UserPW.Userid 
		WHERE (BerechtigungsbereichID = 4 OR BerechtigungsbereichID = 2) 
				AND  schreiben <> 0 AND UserPW.Username = CURRENT_USER)

GO

ALTER VIEW [dbo].[AbCast]
AS
	SELECT ca.*
	FROM    BesetzungTabelle		AS ca 
		INNER JOIN BesetzungStd		AS ro ON ca.RolleID = ro.ID
		INNER JOIN Gruppe			AS g  ON g.GruppeID = ro.Besitzer	
		INNER JOIN UserGruppe		AS Ug ON G.GruppeID = Ug.GruppeID   
		INNER JOIN UserPW				  ON Ug.Userid  = UserPW.Userid 
	WHERE (BerechtigungsbereichID = 4 AND  (ca.Gesperrt = 0 OR (Ug.OptionsFlag &1 = 1)) AND UserPW.Username = CURRENT_USER) 

GO

ALTER VIEW [dbo].[AbTechnicalPersonalRequirements]
AS 

	SELECT auff.Datum, auff.UhrBeg, auff.UhrEnde, aufgabe.ArbeiterAnzahl AS SollZahl, aufgabe.ABGruppeId, Count(cuwt.ActivityJobId) as IstZahl, aufgabe.Pos

		FROM AuffTechAufgabe as aufgabe
		INNER JOIN AufführgTabelle AS auff ON auff.AuffNr = aufgabe.AuffNr
		LEFT JOIN CostUnitWorkingTime AS cuwt ON cuwt.ActivityJobId = aufgabe.AuffTechAufgabeID and cuwt.ActivityID = auff.AuffNr

	WHERE auff.Status BETWEEN 0 AND 99
	GROUP BY auff.Auffnr, aufgabe.AuffTechAufgabeID,  auff.Datum, auff.UhrBeg, auff.UhrEnde, aufgabe.ArbeiterAnzahl, aufgabe.ABGruppeId, aufgabe.Pos
GO

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
	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
	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 sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'BesetzungTS' and o.id = OBJECT_ID(N'dbo.BesetzungTabelle'))
BEGIN

	DROP INDEX BesetzungTabelle.IX_RolleId
	ALTER TABLE BesetzungTabelle ALTER COLUMN RolleId INT NOT NULL
	CREATE INDEX IX_Cast_RoleId ON dbo.BesetzungTabelle (RolleId) ON [PRIMARY]

	DROP INDEX BesetzungTabelle.IX_PersNr
	ALTER TABLE BesetzungTabelle ALTER COLUMN PersNr INT NOT NULL
	CREATE INDEX IX_Cast_PersonId ON dbo.BesetzungTabelle (PersNr) ON [PRIMARY]

	DROP INDEX BesetzungTabelle.IX_AuffNr
	ALTER TABLE BesetzungTabelle ALTER COLUMN AuffNr INT NOT NULL
	CREATE INDEX IX_Cast_ActivityId ON dbo.BesetzungTabelle (AuffNr) ON [PRIMARY]
	ALTER TABLE BesetzungTabelle DROP COLUMN  BesetzungTS

	CREATE INDEX IX_Cast_ActivityId_RoleId ON dbo.BesetzungTabelle (AuffNr, RolleId) ON [PRIMARY]

	CREATE INDEX IX_Roles_RoleId_OwnerId ON dbo.BesetzungStd (Id, Besitzer) ON [PRIMARY]

	DROP INDEX BesetzungStd.IX_Snr
	ALTER TABLE BesetzungStd ALTER COLUMN SNR INT NOT NULL
	CREATE INDEX IX_Roles_ProductionId ON dbo.BesetzungStd (Snr) ON [PRIMARY]

	ALTER TABLE [dbo].[XHDelAufführg] DROP CONSTRAINT [PRIMARYKEY_XHDelAufführg]
	DROP INDEX [IX_Date] ON [dbo].[XHDelAufführg]
	CREATE CLUSTERED INDEX [IX_ActivityHistory_Date] ON [dbo].[XHDelAufführg] ([Datum] ASC)	 
	ALTER TABLE dbo.XHDelAufführg ADD CONSTRAINT PK_ActivityHistory_XHId PRIMARY KEY (XHID) ON [PRIMARY]
	UPDATE XHDelAufführg SET Gesperrt = AufführgTabelle.Gesperrt  FROM XHDelAufführg INNER JOIN AufführgTabelle ON XHDelAufführg.AuffNr = AufführgTabelle.AuffNr
	UPDATE XHDelAufführg SET Gesperrt = 0 WHERE Gesperrt IS NULL
	ALTER TABLE XHDelAufführg ALTER COLUMN Gesperrt BIT NOT NULL 
	ALTER TABLE XHDelAufführg ADD CONSTRAINT DF_XHDelAufführg_gesperrt DEFAULT 0 FOR Gesperrt
	
	DROP INDEX [IX_UC_Tabl] ON [dbo].[KenntnisDel]
	CREATE NONCLUSTERED INDEX [IX_UC_Tabl] ON [dbo].[KenntnisDel] (	[UC] ASC,	[Tabl] ASC) INCLUDE ( [XHID])  ON [PRIMARY]
	

	CREATE NONCLUSTERED INDEX IX_Production_OwnerId_WithProductionId ON [dbo].[Stücke] ([ModuleGroupId] ASC)INCLUDE ( [SNr])

	EXEC('sp_refreshview besetzung')
END
GO
--2015-11-20 sh
--konto spalten im dienstplan
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
WHERE c.name = 'TimeAccountId1' and o.id = OBJECT_ID(N'dbo.Dienstplan'))
BEGIN
	ALTER TABLE dbo.Dienstplan ADD TimeAccountId1 INT NULL
	CONSTRAINT FK_Dienstplan_TimeAccountId1 FOREIGN KEY (TimeAccountId1)
	REFERENCES dbo.Zeitkonten(ZeitKtoNr)

	ALTER TABLE dbo.Dienstplan ADD TimeAccountId2 INT NULL
	CONSTRAINT FK_Dienstplan_TimeAccountId2 FOREIGN KEY (TimeAccountId2)
	REFERENCES dbo.Zeitkonten(ZeitKtoNr)

	ALTER TABLE dbo.Dienstplan ADD TimeAccountId3 INT NULL
	CONSTRAINT FK_Dienstplan_TimeAccountId3 FOREIGN KEY (TimeAccountId3)
	REFERENCES dbo.Zeitkonten(ZeitKtoNr)
END
GO

--20151218 BM	
--Historie für Std.-Besetzung
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('XHDelBasicCast') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.XHDelBasicCast ( 
		XHDelBasicCastId INT IDENTITY(1,1) NOT NULL,
		RoleId INT NOT NULL,
		SeasonId INT NULL,
		BasicCastId INT NULL,
		PersonOldId INT NULL,
		PersonNewId INT NULL,
		ChangeMode TINYINT NOT NULL,
		ChangeText NVARCHAR(512) NOT NULL,
		UnderstudyPos TINYINT NOT NULL, --fängt bei 0 an?
		PosInRole SMALLINT NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		CONSTRAINT PK_XHDelBasicCast PRIMARY KEY (XHDelBasicCastId ASC) ON [PRIMARY],	
		CONSTRAINT FK_XHDelBasicCast_BesetzungStdSoliTabelle FOREIGN KEY (BasicCastId) REFERENCES dbo.BesetzungStdSoliTabelle(BesetzungStdSoliID) ON DELETE SET NULL,	
		CONSTRAINT FK_XHDelBasicCast_SpielzeitTabelle FOREIGN KEY (SeasonId) REFERENCES dbo.SpielzeitTabelle(SpzNr),	
		CONSTRAINT FK_XHDelBasicCast_BesetzungStd FOREIGN KEY (RoleId) REFERENCES dbo.BesetzungStd(Id) ON DELETE CASCADE,
		CONSTRAINT FK_XHDelBasicCast_PersH_PersonOldId FOREIGN KEY (PersonOldId) REFERENCES dbo.PersH(PersNr) ON DELETE SET NULL,
		CONSTRAINT FK_XHDelBasicCast_PersH_PersonNewId FOREIGN KEY (PersonNewId) REFERENCES dbo.PersH(PersNr) --ON DELETE SET NULL
	) ON [PRIMARY]
END
GO

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

CREATE VIEW [dbo].[AbXHDelBasicCast]
AS 
SELECT  h.*  FROM XHDelBasicCast h WHERE h.BasicCastId IN (SELECT c.BesetzungStdSoliID FROM AbBesetzungStdSoli c)
GO

GRANT SELECT ON [dbo].[AbXHDelBasicCast] TO Public
GO

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

CREATE VIEW [dbo].[AbXHDelBasicCast_Update]
AS 
SELECT  h.*  FROM XHDelBasicCast h WHERE h.BasicCastId IN (SELECT c.BesetzungStdSoliID FROM AbBesetzungStdSoli_Update c)

WITH CHECK OPTION
GO

GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[AbXHDelBasicCast_Update] TO Public
GO

--2015-12-22 sh
--status für dritte schicht im dienstplan
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ExemptionStatus3' and o.id = OBJECT_ID(N'[dbo].[Dienstplan]'))
BEGIN
	ALTER TABLE dbo.Dienstplan ADD ExemptionStatus3 INT NOT NULL CONSTRAINT DF_DutyRoster_ExemptionStatus3 DEFAULT(0)
END
GO
--gesundgeschrieben in dienstplan in neue spalte ausgelagert
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'IsPostedHealthy' and o.id = OBJECT_ID(N'[dbo].[Dienstplan]'))
BEGIN
	ALTER TABLE dbo.Dienstplan ADD IsPostedHealthy BIT NOT NULL CONSTRAINT DF_DutyRoster_IsPostedHealthy DEFAULT(0)
	--status 1 auswerten und daten umschreiben
	EXEC('UPDATE Dienstplan SET IsPostedHealthy = 1, ExemptionStatus1 = ExemptionStatus1 & ~1024 WHERE ExemptionStatus1&1024 <> 0')
	--status 2 auswerten und daten umschreiben
	EXEC('UPDATE Dienstplan SET IsPostedHealthy = 1, ExemptionStatus2 = ExemptionStatus2 & ~1024 WHERE ExemptionStatus2&1024 <> 0')
END
GO

--2015-12-23 sh
--arbeitszeit von dienstplänen umschreiben, da jetzt auch für anwesenheiten die stunden in dem jeweiligen az feld stehen
--az wird in die erste schicht verschoben die von und bis zeit hat und keine stunden
--wird nur einmalig ausgeführt
--sollten daten durch alte version nochmal umgespeichert sein, kann man die sql anweisung manuell ausführen.
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 156 OR (Minor = 156 AND Build < 7)))))
BEGIN
	UPDATE Dienstplan SET Az1 = Az, Az = 0 WHERE NOT (V1 IS NULL OR B1 IS NULL) AND Az1 = 0 AND Az <> 0
	UPDATE Dienstplan SET Az2 = Az, Az = 0 WHERE V1 IS NULL AND B1 IS NULL AND NOT (V2 IS NULL OR B2 IS NULL) AND Az2 = 0 AND Az <> 0
END
GO

--20160104 BM
-- "Code" für Feiertage
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'Code' and o.id = OBJECT_ID(N'[dbo].[Feiertage]'))
BEGIN
	ALTER TABLE dbo.Feiertage ADD Code VARCHAR(10) NULL
END
GO

--20160106 BM
-- PersAbsenceStatusTreeView -> Übernahme alter Einstellungen
IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 156 OR (Minor = 156 AND Build < 9)))))
BEGIN
	UPDATE [PLAN] SET settings = Replace(CAST(settings AS VARCHAR(MAX)),'<WithCanceledPersStatus>false</WithCanceledPersStatus>','<PersAbsenceStatusList>
		<string>requestRoot</string>
		<string>confirmed</string>
	  </PersAbsenceStatusList>') 
	  WHERE PlanArt = 170
	UPDATE [PLAN] SET settings = Replace(CAST(settings AS VARCHAR(MAX)),'<WithCanceledPersStatus>true</WithCanceledPersStatus>','')   WHERE PlanArt = 170
	UPDATE [PLAN] SET settings = Replace(CAST(settings AS VARCHAR(MAX)),'<FilterWithCanceldAbsend>false</FilterWithCanceldAbsend>','<PersAbsenceStatusList>
		<string>requestRoot</string>
		<string>confirmed</string>
	  </PersAbsenceStatusList>')   WHERE PlanArt = 170
	UPDATE [PLAN] SET settings = Replace(CAST(settings AS VARCHAR(MAX)),'<FilterWithCanceldAbsend>true</FilterWithCanceldAbsend>','')   WHERE PlanArt = 170
END
GO

--2016-01-08 sh
--neue spalte für kapitelnummer bei haushaltstiteln
IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'ChapterNumber' and o.id = OBJECT_ID(N'[dbo].[BudgetItem]'))
BEGIN
	ALTER TABLE dbo.BudgetItem ADD ChapterNumber VARCHAR(12) NULL
END
GO

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

CREATE VIEW [dbo].[AbPlanColumn_update]
AS 
SELECT  ps.*  FROM [PlanSpalte] ps WHERE ps.PlanID IN (
	SELECT p.PlanID FROM [Plan] p
	INNER JOIN UserGruppe ug ON p.OwnerId = ug.GruppeID
	INNER JOIN UserPw u ON ug.UserID = u.Userid 
	WHERE u.Username = CURRENT_USER AND ug.BerechtigungsbereichID = 11 and ug.Schreiben=1
)
WITH CHECK OPTION
GO

GRANT SELECT ON [dbo].[AbPlanColumn_update] TO Public
GRANT DELETE ON [dbo].[AbPlanColumn_update] TO Public
GRANT INSERT ON [dbo].[AbPlanColumn_update] TO Public
GRANT UPDATE ON [dbo].[AbPlanColumn_update] TO Public
GO

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

CREATE VIEW [dbo].[AbPlanSort_update]
AS 
SELECT  pso.*  FROM [PlanSort] pso WHERE pso.SpalteID IN (
	SELECT ps.SpalteID FROM PlanSpalte ps
	INNER JOIN [Plan] p on ps.PlanID=p.PlanID
	INNER JOIN UserGruppe ug on ug.GruppeID = p.OwnerId
	INNER JOIN UserPW u ON ug.Userid = u.Userid 
	WHERE u.Username = CURRENT_USER AND ug.BerechtigungsbereichID = 11 AND ug.Schreiben=1
)
WITH CHECK OPTION
GO

GRANT SELECT ON [dbo].[AbPlanSort_update] TO Public
GRANT DELETE ON [dbo].[AbPlanSort_update] TO Public
GRANT INSERT ON [dbo].[AbPlanSort_update] TO Public
GRANT UPDATE ON [dbo].[AbPlanSort_update] TO Public
GO

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

CREATE VIEW [dbo].[AbPlanStages_update]
AS 
SELECT  pzr.*  FROM [PlanZR] pzr WHERE pzr.SpalteID IN (
	SELECT ps.SpalteID FROM PlanSpalte ps
	INNER JOIN [Plan] p on ps.PlanID=p.PlanID
	INNER JOIN UserGruppe ug on ug.GruppeID = p.OwnerId
	INNER JOIN UserPW u ON ug.Userid = u.Userid 
	WHERE u.Username = CURRENT_USER AND ug.BerechtigungsbereichID = 11 AND ug.Schreiben=1
)
WITH CHECK OPTION
GO

GRANT SELECT ON [dbo].[AbPlanStages_update] TO Public
GRANT DELETE ON [dbo].[AbPlanStages_update] TO Public
GRANT INSERT ON [dbo].[AbPlanStages_update] TO Public
GRANT UPDATE ON [dbo].[AbPlanStages_update] TO Public
GO

--BM20151209
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'[dbo].[AbResourceContainer]'))
BEGIN
	DROP VIEW [dbo].[AbResourceContainer]
END
GO

CREATE VIEW [dbo].[AbResourceContainer]
AS 
SELECT  r.* FROM dbo.ResourceContainer 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 
GO

GRANT SELECT ON [dbo].[AbResourceContainer] TO Public
GO

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

CREATE VIEW [dbo].[AbResourceContainer_Update]
AS 
SELECT  r.* FROM dbo.ResourceContainer 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 [dbo].[AbResourceContainer_Update] TO Public
GO

IF NOT EXISTS (SELECT * FROM sysColumns c INNER JOIN sysobjects o on c.id = o.id 
	WHERE c.name = 'InfoChar' AND o.id = OBJECT_ID(N'dbo.Zeitkonten'))
BEGIN
	ALTER TABLE Zeitkonten ADD InfoChar VARCHAR(1)
	UPDATE BesetzungStdSoli SET BesSort = BesSort + 1
	UPDATE BesetzungTabelle SET BesSort = BesSort + 1
END

GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('CastCreditTypes') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.CastCreditTypes ( 
		CastCreditTypeId INT IDENTITY(1,1) NOT NULL,
		Name VARCHAR(50) NOT NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		CONSTRAINT PK_CastCreditTypes PRIMARY KEY (CastCreditTypeId ASC) ON [PRIMARY],	
	) ON [PRIMARY]
END
GO
GRANT SELECT ON dbo.CastCreditTypes TO Public
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('CastCredits') AND OBJECTPROPERTY(id,'IsUserTable') = 1) 
BEGIN
	CREATE TABLE dbo.CastCredits ( 
		CastCreditsId INT IDENTITY(1,1) NOT NULL,
		AcquiredOnDate SMALLDATETIME NOT NULL,
		CastCreditTypeId INT NOT NULL,
		ContractId INT NOT NULL,
		ServiceCastId INT NULL,
		ReplacementCastId INT NULL,	
		Note VARCHAR(512) NULL,
		LC DATETIME NOT NULL,
		UC INT NOT NULL,
		LM DATETIME NOT NULL,
		UM INT NOT NULL,
		CONSTRAINT PK_CastCredits PRIMARY KEY NONCLUSTERED (CastCreditsId ASC) ON [PRIMARY],	
		CONSTRAINT FK_CastCredits_Contract FOREIGN KEY (ContractId) REFERENCES dbo.Vertrag(VertragsNr),
		CONSTRAINT FK_CastCredits_CastCreditTypes FOREIGN KEY (CastCreditTypeId) REFERENCES dbo.CastCreditTypes(CastCreditTypeId),	
		CONSTRAINT FK_CastCredits_ServiceCast FOREIGN KEY (ServiceCastId) REFERENCES dbo.BesetzungTabelle(Id),
		CONSTRAINT FK_CastCredits_ReplacementCaste FOREIGN KEY (ReplacementCastId) REFERENCES dbo.BesetzungTabelle(Id) ON DELETE SET NULL

	) ON [PRIMARY]
		CREATE CLUSTERED INDEX IX_CastCredits_ContractId ON dbo.CastCredits (CastCreditsId)
		CREATE UNIQUE NONCLUSTERED INDEX IX_CastCredits_ContractIdCastCreditTypeIdAcquiredOnDate ON dbo.CastCredits (ContractId, CastCreditTypeId, AcquiredOnDate)
		CREATE UNIQUE NONCLUSTERED INDEX IX_CastCredits_ServiceCastId ON dbo.CastCredits (ServiceCastId) WHERE ServiceCastId IS NOT NULL
		CREATE UNIQUE NONCLUSTERED INDEX IX_CastCredits_ReplacementCastId ON dbo.CastCredits (ReplacementCastId) WHERE ReplacementCastId IS NOT NULL
END
GO
IF  EXISTS (SELECT * FROM  sysobjects o WHERE  o.id = OBJECT_ID(N'dbo.AbCastCredits'))
	DROP VIEW dbo.AbCastCredits

GO

CREATE VIEW dbo.AbCastCredits
AS 
	SELECT CC.*  
	FROM CastCredits AS CC 
		INNER JOIN Vertrag AS c ON cc.ContractId = c.VertragsNr
		INNER JOIN UserGruppe AS ug ON c.ModulGruppeID = ug.GruppeId
		INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
			WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 4 
GO

GRANT SELECT ON dbo.AbCastCredits TO Public
GO

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

GO

CREATE VIEW dbo.AbCastCredits_Update
AS 
	SELECT CC.*  FROM CastCredits AS CC  WHERE CC.ContractId IN 
	(
		SELECT c.Vertragsnr 
		FROM  Vertrag AS c 
			INNER JOIN UserGruppe AS ug ON c.ModulGruppeID = ug.GruppeId
			INNER JOIN UserPw AS pw ON ug.UserId = pw.UserId
				WHERE Username = CURRENT_USER AND BerechtigungsbereichID = 4 

	)

WITH CHECK OPTION
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.AbCastCredits_Update TO Public

GO
IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.AbBesetzungLaden')) DROP VIEW dbo.AbBesetzungLaden
IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.oAufführg')) DROP VIEW dbo.oAufführg
IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.oAufführgUpdate')) DROP VIEW dbo.oAufführgUpdate
IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.oStatusZeitR')) DROP VIEW dbo.oStatusZeitR
IF EXISTS (SELECT * FROM sysobjects o WHERE o.id = OBJECT_ID(N'dbo.oStückeUpdate')) DROP VIEW dbo.oStückeUpdate
GO
---------Ende Änderungsblock----------------------------- 
---------------------------------------------------------
--RELEASE SECTION

-- 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 (@start < @permissionPeriodStart)	BEGIN SELECT @start = @permissionPeriodStart END
	IF (@end > @permissionPeriodEnd)		BEGIN SELECT @end = @permissionPeriodEnd 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
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.AbCastCredits_Update TO Public
GO
GRANT  INSERT, UPDATE, DELETE  ON dbo.CastCreditTypes TO TheaOrchester

--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-25 sh
--bugfix doch nicht notwendig -> korrektur weiter unten
--20160224 bm
--#region bm Bugfix: Korrektur des falschen Offsets bei Arbeitszeitmodellen
--IF EXISTS (SELECT * FROM NvData WHERE ByModulScript = 1 AND (Major < 4 OR (Major = 4 AND (Minor < 156 OR (Minor = 156 AND Build < 103)))))
--BEGIN
--	declare @date DATETIME = GETDATE() --Neues Datum und User -100 wird gesetzt, damit ein Datensatz mit der nachfolgenden Codezeile garantiert nur einmal gefixt wird.
--	UPDATE ContractPersoWorkingCycles SET Offset = Offset + 1, LC = @date, UC = -100, LM = @date, UM = -100 WHERE LM < (SELECT DATEADD(MINUTE, 5, so.create_date) FROM sys.tables so WHERE so.name = 'ContractPersoWorkingCycles') AND UC <> -100
--	ALTER TABLE ContractPersoWorkingCycles ADD CONSTRAINT DF_Offset DEFAULT 1 FOR Offset
--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
UPDATE BesetzungTabelle SET BesSort = 1 WHERE BesSort = 0


GO
------------------- Views aktualisieren -----------------
DECLARE @SchemaView VARCHAR(100)
DECLARE view_cursor CURSOR FOR
SELECT Name
	FROM 	sysobjects
	WHERE	xtype = 'V'

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
--sp_refreshview Besetzung
--------------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 
GO

DECLARE @major INT
DECLARE @minor INT
DECLARE @build INT
DECLARE @Datum INT
/*----------------VERSION-ts_DataClass---*/
SET @major = 4
SET @minor = 156
SET @build = 105
SET @Datum = 20160225
/*----------------VERSION-----------------*/

IF NOT EXISTS (SELECT *
				FROM  NvData
				WHERE ByModulScript = 1)
BEGIN
	INSERT INTO NvData (lfdVerNr, ByModulScript)
	VALUES				(1, 1)
END
UPDATE NvData
SET
	Major = @major, Minor = @minor, Build = @build, lfdVerNr = @Datum, LastDatabaseUpdateDate = GetDate()
WHERE
	ByModulScript = 1
GO
--#region ----------- NOEXEC OFF ------------------------------
SET NOEXEC OFF
--#endregion