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 types'
GO
CREATE TYPE [dbo].[FeatureActivationsType] AS TABLE
(
[FeatureID] [int] NOT NULL,
[Active] [bit] 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'Altering [dbo].[Features]'
GO
ALTER TABLE [dbo].[Features] ADD
[IsSandboxed] [bit] NOT NULL CONSTRAINT [DF_Features_IsSandboxed] 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'Creating [dbo].[GetFeaturesOnSPObject]'
GO
CREATE PROCEDURE [dbo].[GetFeaturesOnSPObject]
	@LoadingInfoID int,
	@SPObjectID int,
	@Active bit = NULL
AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_SPObjectID int
	DECLARE	@Loc_Active bit
	DECLARE @IsClientLoad bit
	SET @Loc_LoadingInfoID = @LoadingInfoID
	SET @Loc_SPObjectID = @SPObjectID
	SET @Loc_Active = @Active
	SET @IsClientLoad = 0

IF EXISTS (SELECT SharePointObjects.ID FROM SharePointObjects
		   JOIN WebApplications ON SharePointObjects.WebAppID = WebApplications.ID
		   WHERE SharePointObjects.ID = @Loc_SPObjectID AND WebApplications.FarmType != 0)
	SET @IsClientLoad = 1;--DONT_TOUCH_ME


WITH baseQuery AS
(
	SELECT Title,
        Features.ID AS ID,
        Features.FeatureGuid AS FeatureID,
        ISNULL(FeaturesActivations.Active, 0) AS Active,    
        Features.Version,
        Features.Hidden,
        Features.Custom,
        Features.SolutionName									
    FROM FeaturesLoaded
    JOIN Features ON Features.ID=FeaturesLoaded.FeatureID AND FeaturesLoaded.LoadingInfoID = @Loc_LoadingInfoID
	JOIN SharePointObjects ON SharePointObjects.ID = @Loc_SPObjectID
    LEFT JOIN FeaturesActivations  ON Features.ID = FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID = @Loc_LoadingInfoID AND FeaturesActivations.SPObjectID = @Loc_SPObjectID
    WHERE Features.ScopeID = SharePointObjects.SPObjectTypeID + 1 AND (FeaturesActivations.FeatureID IS NOT NULL OR (Features.IsSandboxed = 0 AND @IsClientLoad = 0) )    
)
SELECT Title,
	    ID,
	    FeatureID,
	    Active,    	                                
	    Version,
	    Hidden,
	    Custom,
	    SolutionName
FROM baseQuery 
WHERE @Loc_Active IS NULL OR Active = @Loc_Active
ORDER BY 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].[GetFeaturesOnWebApplication]'
GO
CREATE PROCEDURE [dbo].[GetFeaturesOnWebApplication]
	@LoadingInfoID int,
	@WebApplicationID int,
	@Active bit = NULL
AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_WebApplicationID int
	DECLARE	@Loc_Active bit
	SET @Loc_LoadingInfoID = @LoadingInfoID
	SET @Loc_WebApplicationID = @WebApplicationID
	SET @Loc_Active = @Active;--DONT_TOUCH_ME

WITH baseQuery AS
(
	SELECT
	    Title,
	    Features.ID AS ID,
	    Features.FeatureGuid AS FeatureID,
	    ISNULL(FeaturesActivations.Active, 0) AS Active,    	                                
	    Features.Version,
	    Features.Hidden,
	    Features.Custom,
	    Features.SolutionName
    FROM FeaturesLoaded
    CROSS APPLY (SELECT WebApplicationID from LoadedWebApplications WHERE LoadingInfoID = @Loc_LoadingInfoID AND WebApplicationID = @Loc_WebApplicationID) AS webApp
    JOIN Features ON Features.ID=FeaturesLoaded.FeatureID AND FeaturesLoaded.LoadingInfoID = @Loc_LoadingInfoID
    LEFT JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID = @Loc_LoadingInfoID  AND FeaturesActivations.WebApplicationID = @Loc_WebApplicationID
    WHERE Features.ScopeID = 1
)
SELECT Title,
	    ID,
	    FeatureID,
	    Active,    	                                
	    Version,
	    Hidden,
	    Custom,
	    SolutionName
FROM baseQuery 
WHERE @Loc_Active IS NULL OR Active = @Loc_Active
ORDER BY 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].[InsertFeatureActivations]'
GO
CREATE PROCEDURE [dbo].[InsertFeatureActivations]
	@LoadingInfoID int,	
	@WebApplicationID int,	
	@SPObjectID int,	
	@FeatureActivations FeatureActivationsType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [dbo].[FeaturesActivations]
           ([LoadingInfoID]
		  ,[FeatureID]
		  ,[SPObjectID]
		  ,[WebApplicationID]
		  ,[Active])
	SELECT @LoadingInfoID
		  ,[FeatureID]
		  ,@SPObjectID
		  ,@WebApplicationID
		  ,[Active]
	FROM @FeatureActivations fa
END
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,
        'Activated' AS 'ActivatedOn' 
    FROM Features 
    JOIN FeaturesActivations ON Features.ID=FeaturesActivations.FeatureID AND FeaturesActivations.LoadingInfoID=@Loc_LoadingInfoID                              
    JOIN FeatureScopes ON Features.ScopeID=FeatureScopes.ID
    WHERE FeaturesActivations.Active = 1 AND 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'Altering permissions on [dbo].[GetFeaturesOnSPObject]'
GO
GRANT EXECUTE ON  [dbo].[GetFeaturesOnSPObject] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetFeaturesOnWebApplication]'
GO
GRANT EXECUTE ON  [dbo].[GetFeaturesOnWebApplication] TO [spdockit_report_viewer]
GO
UPDATE Options SET OptionValue = '7.3.0' 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
UPDATE Options SET OptionValue = '[7.0,7.2)' WHERE OptionID = 1003
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

