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'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]
		  ,[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'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'Dropping foreign keys from [dbo].[EmalSubscriptionDateRanges]'
GO
ALTER TABLE [dbo].[EmalSubscriptionDateRanges] DROP CONSTRAINT[FK_EmalSubscriptionDateRanges_DateRanges]
ALTER TABLE [dbo].[EmalSubscriptionDateRanges] DROP CONSTRAINT[FK_EmalSubscriptionDateRanges_EmailSubscriptions]
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'Dropping foreign keys from [dbo].[SubscriptionDeliveryMethods]'
GO
ALTER TABLE [dbo].[SubscriptionDeliveryMethods] DROP CONSTRAINT[FK_SubscriptionDeliveryMethods_DeliveryMethods]
ALTER TABLE [dbo].[SubscriptionDeliveryMethods] DROP CONSTRAINT[FK_SubscriptionDeliveryMethods_EmailSubscriptions]
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'Dropping constraints from [dbo].[DateRanges]'
GO
ALTER TABLE [dbo].[DateRanges] DROP CONSTRAINT [PK_SubscriptionDateRange]
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'Dropping constraints from [dbo].[DeliveryMethods]'
GO
ALTER TABLE [dbo].[DeliveryMethods] DROP CONSTRAINT [PK_DeliveryMethods]
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'Dropping constraints from [dbo].[EmalSubscriptionDateRanges]'
GO
ALTER TABLE [dbo].[EmalSubscriptionDateRanges] DROP CONSTRAINT [PK_EmalSubscriptionDateRanges]
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'Dropping constraints from [dbo].[SubscriptionDeliveryMethods]'
GO
ALTER TABLE [dbo].[SubscriptionDeliveryMethods] DROP CONSTRAINT [PK_SubscriptionDeliveryMethods]
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'Dropping [dbo].[SubscriptionDeliveryMethods]'
GO
DROP TABLE [dbo].[SubscriptionDeliveryMethods]
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'Dropping [dbo].[DeliveryMethods]'
GO
DROP TABLE [dbo].[DeliveryMethods]
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'Dropping [dbo].[EmalSubscriptionDateRanges]'
GO
DROP TABLE [dbo].[EmalSubscriptionDateRanges]
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'Dropping [dbo].[DateRanges]'
GO
DROP TABLE [dbo].[DateRanges]
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
[ServiceLastSeenAlive] [datetime] NULL,
[LEE] [nvarchar] (255) NULL,
[LicenseValidTo] [datetime] 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].[v_SPDocKitAdmins]'
GO
CREATE VIEW [EventCollection].[v_SPDocKitAdmins]
WITH SCHEMABINDING
AS

WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      )

SELECT i.LoginName, i.PrincipalTypeId FROM(
	SELECT DISTINCT u.LoginName, u.PrincipalTypeID, s.RBSecurityEnabled AS 'ResFlag'
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY SecurityEnabled	AS s
			WHERE r.Name ='Admins'
	UNION 
	 SELECT NULL AS LoginName, NULL AS PrincipalTypeID, ~s.RBSecurityEnabled AS 'ResFlag'
	FROM SecurityEnabled AS s) AS i
WHERE ResFlag = 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 [EventCollection].[v_SPDocKitUserFarms]'
GO
CREATE VIEW [EventCollection].[v_SPDocKitUserFarms]
WITH SCHEMABINDING
AS	
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT CAST(case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END AS BIT)
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
RoleFarms AS (select  u.LoginName, u.PrincipalTypeID,  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM [dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			INNER JOIN [dbo].[SPDocKitRoleFarms] rf ON rf.RoleID = r.ID
			INNER JOIN [dbo].[Farms] f ON rf.FarmID = f.ID
			UNION 
			SELECT u.LoginName, u.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
			FROM[dbo].[SPDocKitUsers] u
			INNER JOIN [dbo].[SPDocKitUserRoles] ur ON u.ID = ur.UserID   
			INNER JOIN [dbo].[SPDocKitRoles] r ON ur.RoleID = r.ID   
			CROSS APPLY [dbo].[Farms] AS f
			WHERE r.Name ='Admins'),
AllFarms AS (
 SELECT NULL AS 'LoginName', NULL AS 'PrincipalTypeID',  f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
		FROM [dbo].[Farms] AS f)

SELECT  f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
FROM(
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , s.RBSecurityEnabled AS 'ResFlag' FROM RoleFarms AS f CROSS APPLY SecurityEnabled as s
	UNION 
	SELECT f.LoginName, f.PrincipalTypeID, f.[ID]
					  ,f.[FarmGuid]
					  ,f.[Name]
					  ,f.[LSE]
					  ,f.[Hash]
					  ,f.[CentralAdministrationUrl]
					  ,f.[Version]
					  ,f.[UsageDatabaseConnectionString]
					  ,f.[DiagnosticsLogLocation]
					  , ~s.RBSecurityEnabled AS 'ResFlag' FROM AllFarms AS f CROSS APPLY SecurityEnabled as s
	)AS f 
WHERE ResFlag = 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'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]
		  ,[LEE]
		  ,[LicenseValidTo]
		  ,[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].[GetSPGroupsForSiteIDs]'
GO
CREATE PROCEDURE [dbo].[GetSPGroupsForSiteIDs]
	@SiteIDs FilterIDsType READONLY,
	@FarmID int,
	@LoadingInfoID int
AS

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_SiteIDs FilterIDsType
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_LoadingInfoID int

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

	INSERT INTO @Loc_SiteIDs
	SELECT * FROM @SiteIDs

	SELECT	                                                    
		p.PrincipalID,
		p.Name,
		p.DisplayName,
		p.Email,
		p.SiteID
    FROM Principals p
	INNER JOIN @Loc_SiteIDs filter ON p.SiteID = filter.ID AND p.FarmID = @FarmID
	INNER JOIN LoadedPrincipals lp on lp.PrincipalID = p.PrincipalID AND lp.LoadingInfoID = @LoadingInfoID 
	WHERE p.PrincipalTypeID = 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'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 permissions on [dbo].[GetSPGroupsForSiteIDs]'
GO
GRANT EXECUTE ON  [dbo].[GetSPGroupsForSiteIDs] TO [spdockit_report_viewer]
GO
UPDATE Options SET OptionValue = '6.2.3' 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

