SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating schemata'
GO
CREATE SCHEMA [EventCollection]
AUTHORIZATION [dbo]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1
		  OR f.ID IN (SELECT a.FarmID FROM MyFarms a)
		  OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Removing schema binding from [dbo].[v_FarmDescriptions]'
GO
ALTER VIEW [dbo].[v_FarmDescriptions]
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	 IsAdminTable (IsAdmin) 
	 AS
	 (
			SELECT TOP 1 v.IsAdmin 
			FROM [dbo].[v_IsUserAdmin] v
	 ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
  SELECT fd.[ID]
      ,fd.[Name]
      ,fd.[FarmData]
      ,fd.[IsSnapshot]
      ,fd.[IsManual]
      ,fd.[IsBackup]
      ,fd.[DifferencesDetected]
      ,fd.[CreatedBy]
      ,fd.[LoadingInfoID]
      ,fd.[IsGoodConfiguration]
	  ,fd.[SnapshotId]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating types'
GO
CREATE TYPE [dbo].[BooleanFilterType] AS TABLE
(
[BitValue] [bit] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Farms]'
GO
ALTER TABLE [dbo].[Farms] ADD
[DiagnosticsLogLocation] [nvarchar] (1024) NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[Agents]'
GO
CREATE TABLE [EventCollection].[Agents]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[AgentID] [nvarchar] (255) NOT NULL,
[LastSeenOnline] [datetime] NOT NULL,
[HostName] [nvarchar] (255) NOT NULL,
[Port] [int] NOT NULL,
[Configuration] [xml] NULL,
[CollectionEnabled] [bit] NOT NULL CONSTRAINT [DF_Agents_CollectionEnabled] DEFAULT ((1))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Agents] on [EventCollection].[Agents]'
GO
ALTER TABLE [EventCollection].[Agents] ADD CONSTRAINT [PK_Agents] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[AgentFarmAssignments]'
GO
CREATE TABLE [EventCollection].[AgentFarmAssignments]
(
[FarmID] [int] NOT NULL,
[AgentID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AgentFarmAssignments] on [EventCollection].[AgentFarmAssignments]'
GO
ALTER TABLE [EventCollection].[AgentFarmAssignments] ADD CONSTRAINT [PK_AgentFarmAssignments] PRIMARY KEY CLUSTERED  ([FarmID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[DatabaseSizes]'
GO
CREATE TABLE [dbo].[DatabaseSizes]
(
[LoadingInfoID] [int] NOT NULL,
[Size] [bigint] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_DatabaseSizes] on [dbo].[DatabaseSizes]'
GO
ALTER TABLE [dbo].[DatabaseSizes] ADD CONSTRAINT [PK_DatabaseSizes] PRIMARY KEY CLUSTERED  ([LoadingInfoID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[EventSourceConfigurations]'
GO
CREATE TABLE [EventCollection].[EventSourceConfigurations]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[EventSourceID] [nvarchar] (255) NOT NULL,
[FarmID] [int] NOT NULL,
[Configuration] [xml] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_EventSourceConfigurations] on [EventCollection].[EventSourceConfigurations]'
GO
ALTER TABLE [EventCollection].[EventSourceConfigurations] ADD CONSTRAINT [PK_EventSourceConfigurations] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[ServerEventSourceCollectionStates]'
GO
CREATE TABLE [EventCollection].[ServerEventSourceCollectionStates]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[EventSourceID] [nvarchar] (255) NOT NULL,
[ServerID] [int] NOT NULL,
[CollectedUpTo] [datetime] NOT NULL,
[State] [xml] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ServerEventSourceCollectionStates] on [EventCollection].[ServerEventSourceCollectionStates]'
GO
ALTER TABLE [EventCollection].[ServerEventSourceCollectionStates] ADD CONSTRAINT [PK_ServerEventSourceCollectionStates] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_Farms]'
GO
ALTER VIEW [dbo].[v_Farms]
WITH SCHEMABINDING
AS 
  WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	  MyFarms
	  AS
	  (
	        SELECT DISTINCT a.FarmID
			FROM [dbo].[v_SPObjectsWithAccess] a
	  ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID 
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
	SELECT [ID]
		  ,[FarmGuid]
		  ,[Name]
		  ,[LSE]
		  ,[Hash]
		  ,[CentralAdministrationUrl]
		  ,[Version]
		  ,[UsageDatabaseConnectionString]
		  ,[DiagnosticsLogLocation]
	  FROM SecurityEnabled sec,  [dbo].[v_HasAccessToAllSPObjects] acc, [dbo].[Farms] f
	  WHERE sec.RBSecurityEnabled = 0 OR acc.HasAccess = 1
		  OR f.ID IN (SELECT a.FarmID FROM MyFarms a)
		  OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetFullControlPrincipalsForWebApplication]'
GO
CREATE PROCEDURE dbo.GetFullControlPrincipalsForWebApplication
	@Principals [dbo].[FilterIDsType] READONLY,
	@LoadingInfoID int,
	@WebApplicationID int,
	@EnabledState [dbo].[BooleanFilterType] READONLY
	AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_Principals FilterIDsType
	DECLARE @Loc_WebApplicationID int
	DECLARE @Loc_EnabledState [dbo].[BooleanFilterType]

	SELECT @Loc_LoadingInfoID = @LoadingInfoID,
		   @Loc_WebApplicationID = @WebApplicationID

	INSERT INTO @Loc_Principals
	SELECT * FROM @Principals

	INSERT INTO @Loc_EnabledState
	SELECT * FROM @EnabledState

	DECLARE	@FilterPrincipals bit
	SELECT @FilterPrincipals = CASE WHEN EXISTS(SELECT 1 FROM @Loc_Principals)THEN 1
									ELSE 0
									END


SELECT 
	[Principals].[PrincipalID] AS [PrincipalID], 
	[Principals].[Name] AS [Name], 
	[Principals].[DisplayName] AS [DisplayName], 
	[LoadedPrincipals].[AccountEnabled] AS [AccountEnabled], 
	[Principals].[PrincipalTypeID] AS [PrincipalTypeID], 
	[Principals].[Email] AS [Email], 	
	cast(0 as bit) AS [C1]
		FROM  [dbo].[v_Principals] AS [Principals]
		INNER JOIN [dbo].[LoadedPrincipals] AS [LoadedPrincipals] ON [Principals].[PrincipalID] = [LoadedPrincipals].[PrincipalID]
		LEFT OUTER JOIN @Loc_Principals principalFilter ON principalFilter.ID = [Principals].PrincipalID 
		WHERE (@FilterPrincipals = 0 OR principalFilter.ID IS NOT NULL)
			  AND ((NOT EXISTS(SELECT 1 FROM @Loc_EnabledState)) OR (EXISTS(SELECT 1 FROM @Loc_EnabledState as [Enabled] WHERE [LoadedPrincipals].[AccountEnabled] = [Enabled].[BitValue])))
			  AND (EXISTS (
			        SELECT 1 AS [C1]
						FROM  [dbo].[WebApplicationPermissions] AS [WebApplicationPermissions]
						INNER JOIN [dbo].[PolicyLevels] AS [PolicyLevels] ON [WebApplicationPermissions].[PolicyLevelID] = [PolicyLevels].[PolicyLevelID]
						WHERE ([Principals].[PrincipalID] = [WebApplicationPermissions].[PrincipalID]) 
							AND ([WebApplicationPermissions].[LoadingInfoID] = @Loc_LoadingInfoID) 
							AND (N'Full Control' = [PolicyLevels].[Name]) 
							AND [WebApplicationPermissions].[WebApplicationID] = @Loc_WebApplicationID) 
							AND ([LoadedPrincipals].[LoadingInfoID] = @Loc_LoadingInfoID))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[MonitoringServers]'
GO
CREATE TABLE [EventCollection].[MonitoringServers]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (255) NOT NULL,
[FarmID] [int] NOT NULL,
[UlsMonitoringEnabled] [bit] NOT NULL CONSTRAINT [DF_MonitoringServers_UlsMonitoringEnabled] DEFAULT ((1)),
[WindowsEventLogMonitoringEnabled] [bit] NOT NULL CONSTRAINT [DF_MonitoringServers_WindowsEventLogMonitoringEnabled] DEFAULT ((1))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_MonitoringServers] on [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] ADD CONSTRAINT [PK_MonitoringServers] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[EmailSubscriptions]'
GO
ALTER TABLE [dbo].[EmailSubscriptions] ADD
[DontSendIfNoReportData] [bit] NOT NULL CONSTRAINT [DF_EmailSubscriptions_DontSendIfNoReportData] DEFAULT ((0)),
[IsAlert] [bit] NOT NULL CONSTRAINT [DF_EmailSubscriptions_IsAlert] DEFAULT ((0))
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_FarmDescriptions]'
GO
ALTER VIEW [dbo].[v_FarmDescriptions]
WITH SCHEMABINDING
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	 IsAdminTable (IsAdmin) 
	 AS
	 (
			SELECT TOP 1 v.IsAdmin 
			FROM [dbo].[v_IsUserAdmin] v
	 ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
  SELECT fd.[ID]
      ,fd.[Name]
      ,fd.[FarmData]
      ,fd.[IsSnapshot]
      ,fd.[IsManual]
      ,fd.[IsBackup]
      ,fd.[DifferencesDetected]
      ,fd.[CreatedBy]
      ,fd.[LoadingInfoID]
      ,fd.[IsGoodConfiguration]
	  ,fd.[SnapshotId]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetFeatureActivations]'
GO
ALTER PROCEDURE [dbo].[GetFeatureActivations]
	@FeatureIDs FilterIDsType READONLY,
	@LoadingInfoID int
AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_FeatureIDs FilterIDsType

	SELECT @Loc_LoadingInfoID = @LoadingInfoID

	INSERT INTO @Loc_FeatureIDs
	SELECT * FROM @FeatureIDs

	SELECT ScopeID,
        FeatureScopes.Name as 'Scope',
        Title + ' (' + CAST(Features.FeatureGuid as nvarchar(255))+ ')' AS 'Title',
        Features.FeatureGuid as FeatureID,
		Features.ID,
        CASE WHEN FeaturesActivations.Active = 0
		    THEN 'Not Activated'
		    ELSE 'Activated'
        END as 'ActivatedOn' 
    FROM Features 
    JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID=@Loc_LoadingInfoID                              
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE Features.ScopeID IN (0) AND EXISTS (SELECT 1 FROM @Loc_FeatureIDs f WHERE f.ID = Features.ID)

    UNION

    (SELECT ScopeID,
        FeatureScopes.Name as 'Scope',
        Title + ' (' + CAST(Features.FeatureGuid as nvarchar(255))+ ')' AS 'Title',
        Features.FeatureGuid as FeatureID,
		Features.ID,
        ISNULL (
		    (CASE WHEN spo.SPObjectTypeID=1
		        THEN ISNULL((SELECT TOP 1 s.Name FROM v_SharePointObjects s 
                JOIN SharePointObjectMemberships ON SharePointObjectMemberships.ChildID = s.ID AND SharePointObjectMemberships.ParentID = spo.ID AND SharePointObjectMemberships.LoadingInfoID=@Loc_LoadingInfoID
            ), spo.Name)
		        ELSE  spo.Name 
		        END + ' (' + spo.Url + ')'), 'Not Activated') as 'ActivatedOn' 
    FROM Features 
    JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID=@Loc_LoadingInfoID
    JOIN v_SharePointObjects spo ON (FeaturesActivations.SPObjectID IS NOT NULL AND FeaturesActivations.SPObjectID=spo.ID)
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE FeaturesActivations.Active = 1 AND Features.ScopeID IN (2,3) AND EXISTS (SELECT 1 FROM @Loc_FeatureIDs f WHERE f.ID = Features.ID))
                
    UNION 
                
    (SELECT ScopeID,
        FeatureScopes.Name as 'Scope',
        Title + ' (' + CAST(Features.FeatureGuid as nvarchar(255))+ ')' AS 'Title',
        Features.FeatureGuid as FeatureID,
		Features.ID,
        ISNULL (w.Name, 'Not Activated') as 'ActivatedOn'                	                    
    FROM Features 
    JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID= @Loc_LoadingInfoID                                   
    JOIN v_WebApplications w ON (FeaturesActivations.WebApplicationID IS NOT NULL AND FeaturesActivations.WebApplicationID=w.ID)
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE FeaturesActivations.Active = 1 AND Features.ScopeID = 1 AND EXISTS (SELECT 1 FROM @Loc_FeatureIDs f WHERE f.ID = Features.ID))
    ORDER BY FeatureScopes.Name ASC, Title ASC
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetSCAdministrators]'
GO
CREATE PROCEDURE [dbo].[GetSCAdministrators]
	@Principals [dbo].[FilterIDsType] READONLY,
	@LoadingInfoID int,
	@WebApplicationID int,
	@FarmID int,
	@EnabledState [dbo].[BooleanFilterType] READONLY	
	AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_Principals FilterIDsType
	DECLARE @Loc_WebApplicationID int
	DECLARE	@Loc_FarmID int
	DECLARE @Loc_EnabledState [dbo].[BooleanFilterType]

	SELECT @Loc_LoadingInfoID = @LoadingInfoID,
		   @Loc_WebApplicationID = @WebApplicationID,
		   @Loc_FarmID = @FarmID

	INSERT INTO @Loc_Principals
	SELECT * FROM @Principals

	INSERT INTO @Loc_EnabledState
	SELECT * FROM @EnabledState

	DECLARE	@FilterPrincipals bit
	SELECT @FilterPrincipals = CASE WHEN EXISTS(SELECT 1 FROM @Loc_Principals)THEN 1
									ELSE 0
									END

	SELECT 
			spo.ID,
			adminPrincipal.PrincipalID,
			adminPrincipal.PrincipalTypeID,
			adminPrincipal.Name,
			adminPrincipal.DisplayName,
			adminPrincipal.Email,
			lp.AccountEnabled,
			CASE WHEN scAdmin.IsPrimary = 1 THEN CAST(0 AS INT)
				 ELSE CAST(1 AS INT) 
		    END AS AdminType,
			-1 AS SiteAdminGroupID
	FROM v_SharePointObjects spo
	INNER JOIN LoadedSiteCollections lsc ON lsc.WebApplicationID = @Loc_WebApplicationID AND lsc.SiteCollectionID = spo.ID AND lsc.LoadingInfoID = @Loc_LoadingInfoID
	INNER JOIN SiteCollectionAdministrators scAdmin ON scAdmin.SharePointObjectID = spo.ID AND scAdmin.LoadingInfoID = @Loc_LoadingInfoID
	INNER JOIN v_Principals adminPrincipal ON adminPrincipal.PrincipalID = scAdmin.PrincipalID
	INNER JOIN LoadedPrincipals lp ON lp.PrincipalID = adminPrincipal.PrincipalID AND lp.LoadingInfoID = @Loc_LoadingInfoID
	LEFT OUTER JOIN @Loc_Principals principalFilter ON principalFilter.ID = adminPrincipal.PrincipalID 
	WHERE ((NOT EXISTS(SELECT 1 FROM @Loc_EnabledState)) OR (EXISTS(SELECT 1 FROM @Loc_EnabledState as [Enabled] WHERE lp.[AccountEnabled] = [Enabled].[BitValue])))
	      AND (@FilterPrincipals = 0 OR principalFilter.ID IS NOT NULL)

	UNION ALL
	
	SELECT 
			spo.ID,
			scAdminGroupMember.PrincipalID,
			scAdminGroupMember.PrincipalTypeID,
			scAdminGroupMember.Name,
			scAdminGroupMember.DisplayName,
			scAdminGroupMember.Email,
			lp.AccountEnabled,
			2 AS AdminType,
			scAdminGroup.PrincipalID AS SiteAdminGroupID
	FROM v_SharePointObjects spo
	INNER JOIN LoadedSiteCollections lsc ON lsc.WebApplicationID = @Loc_WebApplicationID AND lsc.SiteCollectionID = spo.ID AND lsc.LoadingInfoID = @Loc_LoadingInfoID
	INNER JOIN v_Principals scAdminGroup ON scAdminGroup.FarmID = @Loc_FarmID AND scAdminGroup.SiteID = spo.ID
	INNER JOIN PrincipalMemberships pm ON pm.GroupID = scAdminGroup.PrincipalID AND pm.LoadingInfoID = @Loc_LoadingInfoID
	INNER JOIN v_Principals scAdminGroupMember ON pm.UserID = scAdminGroupMember.PrincipalID AND pm.LoadingInfoID = @Loc_LoadingInfoID
	INNER JOIN LoadedPrincipals lp ON lp.PrincipalID = scAdminGroupMember.PrincipalID AND lp.LoadingInfoID = @Loc_LoadingInfoID
	LEFT OUTER JOIN @Loc_Principals principalFilter ON principalFilter.ID = scAdminGroupMember.PrincipalID 
	WHERE scAdminGroup.Name = 'SiteAdministrators'
		AND ((NOT EXISTS(SELECT 1 FROM @Loc_EnabledState)) OR (EXISTS(SELECT 1 FROM @Loc_EnabledState as [Enabled] WHERE lp.[AccountEnabled] = [Enabled].[BitValue])))
	    AND (@FilterPrincipals = 0 OR principalFilter.ID IS NOT NULL)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[DatabaseSizes]'
GO
ALTER TABLE [dbo].[DatabaseSizes] ADD CONSTRAINT [FK_DatabaseSizes_LoadingInfos] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[AgentFarmAssignments]'
GO
ALTER TABLE [EventCollection].[AgentFarmAssignments] ADD CONSTRAINT [FK_AgentFarmAssignments_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([ID]) ON DELETE CASCADE
ALTER TABLE [EventCollection].[AgentFarmAssignments] ADD CONSTRAINT [FK_AgentFarmAssignments_Agents] FOREIGN KEY ([AgentID]) REFERENCES [EventCollection].[Agents] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[EventSourceConfigurations]'
GO
ALTER TABLE [EventCollection].[EventSourceConfigurations] ADD CONSTRAINT [FK_EventSourceConfigurations_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[ServerEventSourceCollectionStates]'
GO
ALTER TABLE [EventCollection].[ServerEventSourceCollectionStates] ADD CONSTRAINT [FK_ServerEventSourceCollectionStates_MonitoringServers] FOREIGN KEY ([ServerID]) REFERENCES [EventCollection].[MonitoringServers] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[MonitoringServers]'
GO
ALTER TABLE [EventCollection].[MonitoringServers] ADD CONSTRAINT [FK_MonitoringServers_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering permissions on [dbo].[BooleanFilterType]'
GO
GRANT EXECUTE ON TYPE:: [dbo].[BooleanFilterType] TO [spdockit_monitoring_viewer]
GRANT EXECUTE ON TYPE:: [dbo].[BooleanFilterType] TO [spdockit_report_viewer]
GRANT EXECUTE ON TYPE:: [dbo].[BooleanFilterType] TO [spdockit_snapshot_viewer]
GO
PRINT N'Altering permissions on [dbo].[DatabaseSizes]'
GO
GRANT SELECT ON  [dbo].[DatabaseSizes] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [EventCollection].[Agents]'
GO
GRANT SELECT ON  [EventCollection].[Agents] TO [spdockit_monitoring_viewer]
GO
UPDATE Options SET OptionValue = '6.2.1' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

