-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DynScopeFilter.EnumBackupServers]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DynScopeFilter.EnumBackupServers]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DynScopeFilter.EnumBackupServers]
	@report_attrs  XML = '<root></root>'
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [Repl.Topology.BackupServers].[id],
		   [Repl.Topology.BackupServers].[display_name]
	FROM  [Repl.Topology.BackupServers]
    
END
GO							
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DynScopeFilter.EnumLabRequestsStates]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DynScopeFilter.EnumLabRequestsStates]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DynScopeFilter.EnumLabRequestsStates]
	@report_attrs  XML = '<root></root>'
AS
BEGIN
	SET NOCOUNT ON;


	declare @activeId uniqueidentifier
	set @activeId = 'F0C46DEA-14AA-4ba4-99C9-70274E811C77'

	declare @closedId uniqueidentifier
	set @closedId = '71609269-A5D4-48a4-8A34-5B1FD97DB6D4'

	declare @lr_states as table (id uniqueidentifier, display_name nvarchar(255))
	
	insert into @lr_states(id, display_name)
	select * from
	(
		select @activeId, 'Active'
		union
		select @closedId, 'Closed'	
	) o(id, display_name)

	select * from @lr_states

	
END
GO							
-----------------------------------------------------





-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumDynScopeFilterTypes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumDynScopeFilterTypes]	
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumDynScopeFilterTypes]	
AS
BEGIN

	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.FilterTypes].[id] as filter_type_id,
		   [dbo].[Report.GridReport.FilterTypes].[unique_name] as filter_unique_name,
		   [dbo].[Report.GridReport.FilterType.DynScopeFilterTypes].[scope_procedure_name]
	FROM 
		   [dbo].[Report.GridReport.FilterType.DynScopeFilterTypes] INNER JOIN [dbo].[Report.GridReport.FilterTypes]
		   on [dbo].[Report.GridReport.FilterType.DynScopeFilterTypes].[filter_type_id] = [dbo].[Report.GridReport.FilterTypes].[id]
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumStatScopeFilterTypes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumStatScopeFilterTypes]	
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumStatScopeFilterTypes]	
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.FilterTypes].[id] as filter_type_id,
		   [dbo].[Report.GridReport.FilterTypes].[unique_name] as filter_unique_name,
		   [dbo].[Report.GridReport.FilterType.StatScopeFilterTypes].[enumeration_id],
		   [dbo].[Report.GridReport.FilterType.StatScopeFilterTypes].[is_icon_required],
		   [dbo].[Report.GridReport.FilterType.StatScopeFilterTypes].[is_text_required]
	FROM
		[dbo].[Report.GridReport.FilterType.StatScopeFilterTypes] INNER JOIN [dbo].[Report.GridReport.FilterTypes]
		ON [dbo].[Report.GridReport.FilterType.StatScopeFilterTypes].[filter_type_id] = [dbo].[Report.GridReport.FilterTypes].[id]
END
GO
-----------------------------------------------------
		
		

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumTextFilterTypes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumTextFilterTypes]	
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumTextFilterTypes]	
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.FilterTypes].[id] as filter_type_id,
		   [dbo].[Report.GridReport.FilterTypes].[unique_name] as filter_unique_name
	FROM
		[dbo].[Report.GridReport.FilterType.TextFilterTypes] INNER JOIN [dbo].[Report.GridReport.FilterTypes] 
		ON [dbo].[Report.GridReport.FilterType.TextFilterTypes].[filter_type_id] = [dbo].[Report.GridReport.FilterTypes].[id]
END
GO
-----------------------------------------------------


			

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Report.Shared.EnumTaskTypes]') AND type in (N'P'))
  DROP PROCEDURE [dbo].[usp.Report.Shared.EnumTaskTypes]	
GO			   
CREATE PROCEDURE [dbo].[usp.Report.Shared.EnumTaskTypes]	
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [dbo].[Report.Shared.TaskTypes].[id],
		   [dbo].[Report.Shared.TaskTypes].[image_id],
		   [dbo].[Report.Shared.TaskTypes].[display_name],
		   [dbo].[Report.Shared.TaskTypes].[invoke_type],
		   [dbo].[Report.Shared.TaskTypes].[req_lines_selection]
	FROM [dbo].[Report.Shared.TaskTypes]
END
GO	   
-----------------------------------------------------



-----------------------------------------------------
--	
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumerateReports]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumerateReports]
GO		
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumerateReports]	
AS
BEGIN		
	SET NOCOUNT ON;
	
	SELECT 
		[Reports].[id] as report_id, 
		[Report.GridReports].[id] as grid_report_id,
		[Reports].[unique_name],
		[Reports].[display_name], 
		[Report.GridReports].[datasrc_proc],
		[Reports].[naviname_builder_id],
		[Report.GridReports].[updates_check_proc],
		[Report.GridReports].[view_type]
	FROM 
		[Reports] INNER JOIN [Report.GridReports] 
			ON [Reports].[id] = [Report.GridReports].[report_id]				
END
GO
-----------------------------------------------------
		
		
		
	
		
		
-----------------------------------------------------
--	
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumColumns]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumColumns]	
GO		
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumColumns]	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Columns].[grid_report_id],
		   [dbo].[Report.GridReport.Columns].[display_name],
		   [dbo].[Report.GridReport.Columns].[unique_name],
		   [dbo].[Report.GridReport.Columns].[relative_width],
		   [dbo].[Report.GridReport.Columns].[is_sortable],
		   [dbo].[Report.GridReport.Columns].[icon_binding_id],
		   [dbo].[Report.GridReport.Columns].[text_binding_id],
		   [dbo].[Report.GridReport.Columns].[href_binding_id],
		   [dbo].[Report.GridReport.Columns].[order_id]
	FROM   [dbo].[Report.GridReport.Columns]
	ORDER BY [dbo].[Report.GridReport.Columns].[grid_report_id],
			 [dbo].[Report.GridReport.Columns].[ordinal_number]
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumHiddenColumns]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumHiddenColumns]	
GO		
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumHiddenColumns]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.HiddenColumns].[grid_report_id],
		   [dbo].[Report.GridReport.HiddenColumns].[unique_name],
		   [dbo].[Report.GridReport.HiddenColumns].[binding_id]
	FROM   [dbo].[Report.GridReport.HiddenColumns]
	ORDER BY [dbo].[Report.GridReport.HiddenColumns].[grid_report_id]
END
GO
-----------------------------------------------------


			
			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumDetailsPanels]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumDetailsPanels]	
GO					
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumDetailsPanels]	
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.DetailsPanels].[id],
		   [dbo].[Report.GridReport.DetailsPanels].[panel_width],
		   [dbo].[Report.GridReport.DetailsPanels].[text_binding_id],
		   [dbo].[Report.GridReport.DetailsPanels].[title],
		   [dbo].[Report.GridReport.DetailsPanels].[default_text]						   
	FROM   [dbo].[Report.GridReport.DetailsPanels]
    
END
GO
-----------------------------------------------------

			
			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumDirectTextColBindings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumDirectTextColBindings]
GO									
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumDirectTextColBindings]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Binding.TextColBindings].[id],
		   [dbo].[Report.GridReport.Binding.TextColBindings].[datasrc_column],
		   [dbo].[Report.GridReport.Binding.DirectTextColBindings].[default_value_column],
		   [dbo].[Report.GridReport.Binding.DirectTextColBindings].[sqldb_type],
		   [dbo].[Report.GridReport.Binding.DirectTextColBindings].[format]
	FROM [dbo].[Report.GridReport.Binding.TextColBindings] INNER JOIN [dbo].[Report.GridReport.Binding.DirectTextColBindings] 
		  ON [dbo].[Report.GridReport.Binding.TextColBindings].[id] = [dbo].[Report.GridReport.Binding.DirectTextColBindings].[id]
	
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumDirectColBindings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumDirectColBindings]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumDirectColBindings]
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Binding.HiddenColBindings].[id],
		   [dbo].[Report.GridReport.Binding.HiddenColBindings].[datasrc_column],
		   [dbo].[Report.GridReport.Binding.DirectTextColBindings].[sqldb_type]
	FROM [dbo].[Report.GridReport.Binding.HiddenColBindings] INNER JOIN [dbo].[Report.GridReport.Binding.DirectTextColBindings] 
		  ON [dbo].[Report.GridReport.Binding.HiddenColBindings].[id] = [dbo].[Report.GridReport.Binding.DirectTextColBindings].[id]
END
GO		
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumEnumerationTextColBindings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumEnumerationTextColBindings]
GO													
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumEnumerationTextColBindings]	
AS
BEGIN
	SET NOCOUNT ON;


	SELECT  [dbo].[Report.GridReport.Binding.TextColBindings].[id],
			[dbo].[Report.GridReport.Binding.TextColBindings].[datasrc_column],							
			[dbo].[Report.GridReport.Binding.EnumTextColBindings].[enum_id]
	FROM	[dbo].[Report.GridReport.Binding.TextColBindings] INNER JOIN [dbo].[Report.GridReport.Binding.EnumTextColBindings]
			ON [dbo].[Report.GridReport.Binding.TextColBindings].[id] = [dbo].[Report.GridReport.Binding.EnumTextColBindings].[id]
	
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumIconColBindings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumIconColBindings]
GO										
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumIconColBindings]	
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Binding.IconColBindings].[id],
		   [dbo].[Report.GridReport.Binding.IconColBindings].[datasrc_column],
		   [dbo].[Report.GridReport.Binding.IconColBindings].[enumeration_id]
	FROM   [dbo].[Report.GridReport.Binding.IconColBindings]

END
GO	
-----------------------------------------------------
			
			
-----------------------------------------------------
--			
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumHrefColBindings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindings]
GO							
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindings]	
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT [dbo].[Report.GridReport.Binding.HrefColBindings].[id],
		   [dbo].[Reports].[unique_name] as trgrep_unique_name,
		   [dbo].[Report.GridReport.Binding.HrefColBindings].[hyperlink_id],
		   [dbo].[Reports].[naviname_builder_id] 
	FROM   [dbo].[Reports] INNER JOIN [dbo].[Report.GridReport.Binding.HrefColBindings]
		   ON [dbo].[Reports].[id] = [dbo].[Report.GridReport.Binding.HrefColBindings].[target_report_id]
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumDynHrefColBindings]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumDynHrefColBindings]
GO							
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumDynHrefColBindings]	
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT [dbo].[Report.GridReport.Binding.DynHrefColBindings].[id],
		   [dbo].[Report.GridReport.Binding.DynHrefColBindings].[href_binding1_id],
		   [dbo].[Report.GridReport.Binding.DynHrefColBindings].[href_binding2_id],
		   [dbo].[Report.GridReport.Binding.DynHrefColBindings].[resolve_proc]
	FROM   [dbo].[Report.GridReport.Binding.DynHrefColBindings]
END
GO		
-----------------------------------------------------
			

			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumHrefColBindingsEx]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindingsEx]
GO					
	CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindingsEx]
		@grid_report_id uniqueidentifier	
	AS
	BEGIN					
		SET NOCOUNT ON;

		SELECT [Report.GridReport.HrefColBindings].[id],					   					   
		   [Reports].[unique_name] as trgrep_unique_name,
		   [Report.GridReport.HrefColBindings].[hyperlink_id],					   
		   [Reports].[naviname_builder_id]
		FROM
		   ( [Report.GridReport.HrefColBindings] INNER JOIN [Reports] ON [Report.GridReport.HrefColBindings].[target_report_id] = [Reports].[id] )
		   INNER JOIN [Report.GridReport.Columns] ON [Report.GridReport.HrefColBindings].[grid_column_id] = [Report.GridReport.Columns].[id]
		WHERE		 
			  [Report.GridReport.Columns].[grid_report_id] = @grid_report_id    
	END
GO			
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumHrefColBindingDynAttrs]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindingDynAttrs]
GO						
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindingDynAttrs]					
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs].[href_binding_id],
		   [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs].[attr_name],
		   [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs].[datasrc_column],
		   [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs].[is_mandatory_attr]
	FROM 
		   [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]					
END							
GO
-----------------------------------------------------


			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumHrefColBindingStatAttrs]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindingStatAttrs]
GO										
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumHrefColBindingStatAttrs]					
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Binding.HrefColBindingStatAttrs].[href_binding_id],
		   [dbo].[Report.GridReport.Binding.HrefColBindingStatAttrs].[attr_name],
		   [dbo].[Report.GridReport.Binding.HrefColBindingStatAttrs].[attr_value]
	FROM 
		   [dbo].[Report.GridReport.Binding.HrefColBindingStatAttrs]						      
END
GO	
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumFilters]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumFilters]
GO													
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumFilters]					
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Filters].[grid_report_id],
		   [dbo].[Report.GridReport.Filters].[filter_type_id],
		   [dbo].[Report.GridReport.Filters].[display_name],
		   [dbo].[Report.GridReport.Filters].[unique_name],
		   [dbo].[Report.GridReport.Filters].[width],
		   [dbo].[Report.GridReport.Filters].[prefval_object_id]
	FROM [dbo].[Report.GridReport.Filters]					
	ORDER BY [dbo].[Report.GridReport.Filters].[grid_report_id], 
			 [dbo].[Report.GridReport.Filters].[ordinal_number] 
END
GO							
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumOrders]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumOrders]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.EnumOrders]
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Orders].[id] as order_id,
		   [dbo].[Report.GridReport.Orders].[grid_report_id],
		   [dbo].[Report.GridReport.Orders].[column_name],
		   [dbo].[Report.GridReport.Orders].[sort_direction]
	FROM [dbo].[Report.GridReport.Orders]					
	ORDER BY [dbo].[Report.GridReport.Orders].[grid_report_id]
END
GO				
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Report.Config.EnumHeaders]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Report.Config.EnumHeaders]
GO
CREATE PROCEDURE [dbo].[usp.Report.Config.EnumHeaders]
AS
BEGIN	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.Headers].[id] as header_id,
		   [dbo].[Report.Headers].[unique_name],
		   [dbo].[Report.Headers].[datasrc_proc],
		   [dbo].[Report.Headers].[report_id]
	FROM [dbo].[Report.Headers]					
	ORDER BY [dbo].[Report.Headers].[report_id]
END
GO
-----------------------------------------------------



-----------------------------------------------------
--						
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.EnumTasks]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.EnumTasks]
GO															
CREATE PROCEDURE [usp.GridReport.Config.EnumTasks]		
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT [dbo].[Report.GridReport.Tasks].[grid_id],
		   [dbo].[Report.GridReport.Tasks].[task_type_id]
	FROM [dbo].[Report.GridReport.Tasks]
	ORDER BY [ordinal_number]
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.GetAttributes]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.GetAttributes]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Util.GetAttributes]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="n" value="v"></attr></attributes>'
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @docAttributes INT
	EXEC sp_xml_preparedocument @docAttributes OUTPUT, @attributesXml
	
	DECLARE @atributes table( [id] nvarchar(50), [value] nvarchar(50) )
	INSERT INTO @atributes SELECT * FROM OPENXML( @docAttributes, '/attributes/attr', 1)
	WITH
	(
	   name nvarchar(50),
	   value nvarchar(100)
	)
	
	EXEC sp_xml_removedocument @docAttributes
	SELECT * FROM @atributes
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.FindUidAttribute]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.FindUidAttribute]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.Util.FindUidAttribute]	
	@attributesXml XML,
	@attr_name nvarchar( 50 ),
	@uid_attr_value uniqueidentifier	OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

	-- PARSE DOCUMENT
	DECLARE @docAttributes INT
	EXEC sp_xml_preparedocument @docAttributes OUTPUT, @attributesXml	
	DECLARE @attributes table( [attr_name] nvarchar(50), [attr_value] nvarchar(255) )
	INSERT INTO @attributes SELECT * FROM OPENXML( @docAttributes, '/attributes/attr', 1)
	WITH
	(
	   name nvarchar(50),
	   value nvarchar(255)
	)	
	EXEC sp_xml_removedocument @docAttributes

	
	-- EXTRACT TEXT PRESENTATION OF UID
	DECLARE @uid_attr_str nvarchar( 100 )
	SELECT TOP(1) @uid_attr_str = [attr_value]
	FROM @attributes
	WHERE [attr_name] = @attr_name

	IF @uid_attr_str IS	NULL
		RETURN

	
	SELECT @uid_attr_value = CAST( @uid_attr_str as uniqueidentifier )
	RETURN
END
GO
-----------------------------------------------------

---------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.FindStringAttribute]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.FindStringAttribute]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.Util.FindStringAttribute]	
	@attributesXml XML,
	@attr_name nvarchar( 50 ),
	@string_attr_value nvarchar(255)	OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

	-- PARSE DOCUMENT
	DECLARE @docAttributes INT
	EXEC sp_xml_preparedocument @docAttributes OUTPUT, @attributesXml	
	DECLARE @attributes table( [attr_name] nvarchar(50), [attr_value] nvarchar(50) )
	INSERT INTO @attributes SELECT * FROM OPENXML( @docAttributes, '/attributes/attr', 1)
	WITH
	(
	   name nvarchar(50),
	   value nvarchar(100)
	)	
	EXEC sp_xml_removedocument @docAttributes

	
	-- EXTRACT TEXT PRESENTATION OF UID	
	SELECT TOP(1) @string_attr_value = [attr_value]
	FROM @attributes
	WHERE [attr_name] = @attr_name
	
	RETURN
END
GO
-----------------------------------------------------

			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.FindIntAttribute]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.FindIntAttribute]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.Util.FindIntAttribute]
	@attributesXml XML,
	@attr_name nvarchar( 50 ),
	@int_attr_value int OUTPUT
AS
BEGIN	
	SET NOCOUNT ON;

	-- PARSE DOCUMENT
	DECLARE @docAttributes INT
	EXEC sp_xml_preparedocument @docAttributes OUTPUT, @attributesXml	
	DECLARE @attributes table( [attr_name] nvarchar(50), [attr_value] nvarchar(50) )
	INSERT INTO @attributes SELECT * FROM OPENXML( @docAttributes, '/attributes/attr', 1)
	WITH
	(
	   name nvarchar(50),
	   value nvarchar(100)
	)	
	EXEC sp_xml_removedocument @docAttributes

	
	-- EXTRACT TEXT PRESENTATION OF UID
	DECLARE @int_attr_str nvarchar( 100 )
	SELECT TOP(1) @int_attr_str = [attr_value]
	FROM @attributes
	WHERE [attr_name] = @attr_name

	IF @int_attr_str IS	NULL
		RETURN

	
	SELECT @int_attr_value = CAST( @int_attr_str as int )
	RETURN
END
GO
-----------------------------------------------------			
		

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.GetDynamicScopeFilter]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.GetDynamicScopeFilter]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Util.GetDynamicScopeFilter]
	@docFilters int,
	@filterId nvarchar(50),
	@lowValue uniqueidentifier OUT,
	@highValue uniqueidentifier OUT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @lowValue = lowValue, @highValue = highValue
	FROM OPENXML( @docFilters, '/filters/dyn_scope_filter', 2)
		WITH
		(
			filterID nvarchar(50) '@id',
			lowValue uniqueidentifier 'low',
			highValue uniqueidentifier 'high'
		)
	WHERE
		filterID = @filterId
		
	IF @lowValue is NULL
		SET @lowValue = '00000000-0000-0000-0000-000000000000'
	IF @highValue is NULL
		SET @highValue = 'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'
END		
GO
-----------------------------------------------------




			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.GetStaticScopeFilter]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.GetStaticScopeFilter]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Util.GetStaticScopeFilter]
	@docFilters int,
	@filterId nvarchar(50),
	@lowValue int OUT,
	@highValue int OUT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @lowValue = lowValue, @highValue = highValue
	FROM OPENXML( @docFilters, '/filters/static_scope_filter', 2)
		WITH
		(
			filterID nvarchar(50) '@id',
			lowValue int 'low',
			highValue int 'high'
		)
	WHERE
		filterID = @filterId

	IF @lowValue is NULL
		SET @lowValue = -10000;
	IF @highValue is NULL
		SET @highValue = 10000;
END		
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.GetTextFilter]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.GetTextFilter]
GO
	CREATE PROCEDURE [dbo].[usp.GridReport.Util.GetTextFilter]
		@docFilters int,
		@filterId nvarchar(50),
		@value nvarchar(100) OUT
	AS
	BEGIN
		SET NOCOUNT ON;

		SELECT @value = value
		FROM OPENXML( @docFilters, '/filters/text_filter', 2)
			 WITH
			(
				filterID nvarchar(50) '@id',
				value nvarchar(100) 'value'
			)
		WHERE
			filterID = @filterId			
			
		IF @value is NULL
			SET @value = ''
	END
GO
-----------------------------------------------------



-----------------------------------------------------
--				
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Util.GetOrder]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Util.GetOrder]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Util.GetOrder]
	@ordersXml XML = N'<orders><order id="order_name" sort="asc"></order><order id="order_desc" sort="desc"></order></orders>',
	@col_name nvarchar(50) OUTPUT,
	@asc int OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @docOrders INT
	EXEC sp_xml_preparedocument @docOrders OUTPUT, @ordersXml
	
	DECLARE @orders table( [id] nvarchar(50), [sort] nvarchar(50) )
	INSERT INTO @orders SELECT * FROM OPENXML( @docOrders, '/orders/order', 1)
	WITH
	(
	   id nvarchar(50),
	   sort nvarchar(50)
	)
	
	EXEC sp_xml_removedocument @docOrders
	SELECT TOP(1) 
		@col_name = id, 
		@asc = 
			(CASE
				WHEN sort = 'asc' THEN 1
				ELSE 0
			END)
	FROM @orders
	WHERE sort = 'asc' OR sort = 'desc'
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.DefaultCheckUpdatesProc]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.DefaultCheckUpdatesProc]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.DefaultCheckUpdatesProc]
	@report_usn  BigInt
AS
BEGIN	
	SET NOCOUNT ON;

	-- Check for replications happed after the report was generated
	DECLARE @isGridNeedsReload Bit
	IF EXISTS ( SELECT * FROM [dbo].[Repl.Topology.BackupDbInstances] WHERE [update_usn] > @report_usn )
		BEGIN
			SELECT @isGridNeedsReload = 1
		END
	ELSE
		BEGIN
			SELECT @isGridNeedsReload = 0
		END

	    
	SELECT 30 as 'rep_lifetime_sec',
		   @isGridNeedsReload as 'is_data_should_be_reloaded'
		   		   
END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.VmInBackups]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.VmInBackups]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.VmInBackups]
	@attributesXml XML =	N'<?xml version="1.0"?><attributes />',
	@filtersXml XML =		N'<?xml version="1.0"?>
								<filters>
									<text_filter id="vm_name"><value></value></text_filter>												
								</filters>',
	@ordersXml XML =		N'<orders></orders>'
AS
BEGIN
	SET NOCOUNT ON;
	--  Extract filters
	DECLARE @docFilters int								
	DECLARE @value_VmName nvarchar(100)
	EXEC sp_xml_preparedocument @docFilters OUTPUT, @filtersXml				
	EXEC [dbo].[usp.GridReport.Util.GetTextFilter] @docFilters, 'vm_name', @value_VmName OUT								
	EXEC sp_xml_removedocument @docFilters
	DECLARE @oibs_count TABLE (id UNIQUEIDENTIFIER, num int,  backup_id uniqueidentifier, last_creation_time datetime)
	insert  @oibs_count(id, num, backup_id, last_creation_time)
	select
			oibs.object_id
			,COUNT (oibs.id) as num_points
			,points.backup_id
			,MAX(oibs.creation_time) as last_creation_time
	from
		[dbo].[C.Backup.Model.OIBs] oibs
		JOIN [dbo].[C.Backup.Model.OIBs] oibs2 ON (oibs.id = oibs2.id)
		JOIN [dbo].[C.Backup.Model.Points] points on (oibs2.point_id = points.id)
		where
			oibs.is_corrupted = 0
	GROUP BY 
	oibs.object_id, points.backup_id
	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
	-- GET user sid
	DECLARE @userSid varbinary(85), @userSidBase64 varchar(200);
	EXEC [usp.GridReport.Util.FindStringAttribute]
		@attributesXml, 'userSid', @userSidBase64 OUTPUT;
	IF (@userSidBase64 IS NULL)
		RAISERROR (N'@userSid attribute not found.', 9, 1);
	SELECT @userSid = CAST(N'' as xml).value('xs:base64Binary(sql:variable("@userSidBase64"))', 'varbinary(85)');
	DECLARE @BObjects TABLE (
		id uniqueidentifier, 
		object_name varchar(255), 
		host_id uniqueidentifier,
		object_type int
	);
	INSERT @BObjects (id, object_name, host_id, object_type)
	SELECT 
		f.[id]
		,  CASE 
			WHEN (f.[display_name] = NULL OR f.[display_name] = '')
			THEN f.[object_name]
			ELSE f.[display_name]
			END as name
		, f.[host_id]
		, f.[type] 
		FROM [fn.Backup.GetAccessibleObjects](@userSid) f;
	--  Perform stored function
	SELECT * 
	FROM
	(
		SELECT DISTINCT
				  ob.id,
				  ob.display_name as vm_name, 
				  vapp_name = CASE 
					WHEN ob.parent_id IS NOT NULL 
					THEN (SELECT p.display_name FROM [dbo].[C.Backup.Model.OIBs] p WHERE p.id=ob.parent_id)
				  ELSE NULL
				  END,
				  CASE 
						WHEN b.job_target_type = 22 THEN b.job_name
						WHEN LEFT(stg.file_path, 1) = N'<' THEN CAST(stg.file_path as xml).value('/FullName[1]', 'nvarchar(1000)')
						ELSE stg.file_path
				  END as last_backup_file,
				  oibs_count.num as restore_points_count,
				  CAST(oibs_count.num as nvarchar) +
					  CASE oibs_count.num
							WHEN 0 THEN ''
							WHEN 1 THEN N' point'
							ELSE N' points'
					  END 
				  as restore_points_linkname,
				  [dbo].[Repl.Topology.BackupServers].[display_name] as backup_server_name,
				  ob.[creation_time] as latest_success_dt,
				  CASE b.job_target_type 
					WHEN 22 THEN 'SAN Snapshot'
					ELSE b.[job_name]  
				  END as job_name,
				  vm_id = 
				  CASE 
					WHEN b.job_target_type = 22 THEN NULL
					ELSE obj.[id]
				  END,		   
				  pnt.[id] as point_id, 
				  ob.[db_instance_id] as vb_db_id,
				  CASE COALESCE(h.type, 0)
					WHEN 3 THEN [dbo].[Repl.Topology.BackupServers].[display_name]
					WHEN 0 THEN h2.name
					ELSE h.name
				  END as tgt_server
		FROM @oibs_count oibs_count
		INNER JOIN @BObjects obj 
			ON (obj.id  = oibs_count.id 
			-- VCD vApp fix
			AND obj.object_type = 1)
		INNER JOIN [dbo].[C.Backup.Model.OIBs] ob ON (oibs_count.id = ob.object_id AND oibs_count.last_creation_time = ob.creation_time AND ob.is_corrupted = 0)
		INNER JOIN [dbo].[C.Backup.Model.Points] pnt ON (ob.point_id = pnt.id AND pnt.backup_id = oibs_count.backup_id) 
		LEFT JOIN [dbo].[C.Backup.Model.Storages] stg ON (ob.[storage_id] = stg.id)
		LEFT JOIN [dbo].[C.Backup.Model.Backups] b ON (pnt.backup_id = b.[id])
		LEFT JOIN [dbo].[C.Hosts] h ON (stg.[host_id] = h.id)
		LEFT JOIN [dbo].[C.Hosts] h2 ON (obj.[host_id] = h2.id)
		JOIN [dbo].[Repl.Topology.BackupServers] ON (ob.[db_instance_id] = [dbo].[Repl.Topology.BackupServers].[current_db_id])
		WHERE 
			  obj.[object_name] LIKE '%' + @value_VmName + '%'
	) as DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'vm_name'				THEN rank() OVER(ORDER BY DataSrc.vm_name ASC)
					WHEN 'last_backup_file'		THEN rank() OVER(ORDER BY DataSrc.last_backup_file ASC)
					WHEN 'restore_points_count'	THEN rank() OVER(ORDER BY DataSrc.restore_points_count ASC)
					WHEN 'backup_server_name'	THEN rank() OVER(ORDER BY DataSrc.backup_server_name ASC)
					WHEN 'job_name'				THEN rank() OVER(ORDER BY DataSrc.job_name ASC)
					WHEN 'latest_success_dt'	THEN rank() OVER(ORDER BY DataSrc.latest_success_dt ASC)
					WHEN 'tgt_server'			THEN rank() OVER(ORDER BY DataSrc.tgt_server ASC)
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'vm_name'				THEN rank() OVER(ORDER BY DataSrc.vm_name DESC)
					WHEN 'last_backup_file'		THEN rank() OVER(ORDER BY DataSrc.last_backup_file DESC)
					WHEN 'restore_points_count'	THEN rank() OVER(ORDER BY DataSrc.restore_points_count DESC)
					WHEN 'backup_server_name'	THEN rank() OVER(ORDER BY DataSrc.backup_server_name DESC)
					WHEN 'job_name'				THEN rank() OVER(ORDER BY DataSrc.job_name DESC)
					WHEN 'latest_success_dt'	THEN rank() OVER(ORDER BY DataSrc.latest_success_dt DESC)
					WHEN 'tgt_server'			THEN rank() OVER(ORDER BY DataSrc.tgt_server DESC)
				END
			ELSE rank() OVER(ORDER BY vm_name ASC)
		END																																													
END
GO	
-----------------------------------------------------

			

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.VmRestorePoints]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.VmRestorePoints]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.VmRestorePoints]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="point_id" value="c0671ba6-022c-4ff7-b976-8d8f36f4c478"></attr><attr name="vm_id" value="6a0ae1bd-d86b-49f4-8b88-fbe7c0cabbcf"></attr></attributes>',
	@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
	@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'	
AS
BEGIN

	SET NOCOUNT ON;

	-- GET VM_ID
	DECLARE @vm_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'vm_id', @vm_id OUTPUT				
	IF @vm_id is NULL
		RAISERROR(N'The unique identifier of the virtual machine was not specified for the VmRestorePoints report', 9, 1 )

	-- GET ROOT BACKUP ID
	DECLARE @point_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'point_id', @point_id OUTPUT				
	IF @point_id IS NULL
		RAISERROR(N'The unique identifier of the root point was not specified for the VmRestorePoints report', 9, 2 )


	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	CREATE TABLE #ChainDesc ([oib_id] UNIQUEIDENTIFIER, rn int)

	INSERT #ChainDesc(oib_id, rn)
	select ob.id, pnt.num from [dbo].[C.Backup.Model.OIBs] ob, [dbo].[C.Backup.Model.Points] pnt
	where 
	ob.point_id = pnt.id and
	ob.object_id = @vm_id and
	pnt.backup_id = (select backup_id from [dbo].[C.Backup.Model.Points] where id = @point_id)



	SELECT * from
	(
	SELECT ob.id,
		   ob.creation_time as restore_point, 
		   CASE ob.type
			WHEN 0 THEN N'Full'
			WHEN 1 THEN N'Rollback'
			WHEN 2 THEN N'Increment'
			WHEN 3 THEN N'Different'
			WHEN 4 THEN N'Snapshot' 
		   END as TYPE
	FROM
		[dbo].[C.Backup.Model.OIBs] ob, 
		#ChainDesc bc
	WHERE 
		ob.id = bc.oib_id AND
		ob.is_corrupted = 0			
	) o
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'restore_point'	THEN rank() OVER(ORDER BY restore_point ASC)
					WHEN 'type'				THEN rank() OVER(ORDER BY type ASC)
				END
			WHEN 0 THEN 
					CASE @col_name
					WHEN 'restore_point'	THEN rank() OVER(ORDER BY restore_point DESC)
					WHEN 'type'				THEN rank() OVER(ORDER BY type DESC)
				END
			ELSE rank() OVER(ORDER BY restore_point DESC)
		END
		

	DROP TABLE #ChainDesc		  



END
GO
-----------------------------------------------------








-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.HrefResolveProc.Jobs_CurrentState]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.HrefResolveProc.Jobs_CurrentState]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.HrefResolveProc.Jobs_CurrentState]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="session_id" value="c143323f-98ba-483b-bdec-111bf50c3d2d"></attr></attributes>'
AS
BEGIN

	DECLARE 
		@cur_jobs_2_last_session_hrefid uniqueidentifier,
		@cur_jobs_2_last_sb_session_hrefid uniqueidentifier
	SELECT 
		@cur_jobs_2_last_session_hrefid = '153B2A2B-6BBD-4080-B203-6B96A6605BFB',
		@cur_jobs_2_last_sb_session_hrefid = '087B258B-4174-470d-89FB-0DE8CBF45569'
		
	-- IDENTIFY CUR SESSION ID
	DECLARE @cur_session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'session_id', @cur_session_id OUTPUT
	IF @cur_session_id is NULL
		RAISERROR(N'The current session unique identifier was not specified', 9, 1 )
	SELECT 
		CASE
			WHEN [dbo].[C.BJobs].[type] IS NULL THEN @cur_jobs_2_last_session_hrefid
			WHEN [dbo].[C.BJobs].[type] = 3 THEN @cur_jobs_2_last_sb_session_hrefid
			ELSE @cur_jobs_2_last_session_hrefid
		END as href_id
	FROM [dbo].[C.BJobs] 
		INNER JOIN [dbo].[C.Backup.Model.JobSessions] ON [dbo].[C.BJobs].[id] = [dbo].[C.Backup.Model.JobSessions].[job_id]
	WHERE [dbo].[C.Backup.Model.JobSessions].[id] = @cur_session_id
	
END
GO
-----------------------------------------------------





-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.JobLog]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.JobLog]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.JobLog]
	@attributesXml XML =	N'<?xml version="1.0"?><attributes />',
	@filtersXml XML =		N'<?xml version="1.0"?>
								<filters>
								</filters>',
	@ordersXml XML =		N'<orders></orders>',
	@is_all_data bit = 0 OUT
AS
BEGIN
		SET NOCOUNT ON;
	
	-- IDENTIFY SESSION
	DECLARE @session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'session_id', @session_id OUTPUT
	IF @session_id is NULL
		RAISERROR(N'The session identifier was not supplied for the JobLog report', 9, 1 )
	
				
	DECLARE @log_xml xml;
	SET @log_xml = (
		SELECT
			CASE
				WHEN 
					[dbo].[C.Backup.Model.JobSessions].[state] = -1 AND  -- CBaseSessionInfo.EState.Stopped
					[dbo].[C.Backup.Model.JobSessions].[log_xml] IS NOT NULL AND
					([dbo].[C.Backup.Model.JobSessions].[log_xml]).value('count(/*[1]/*[1]/*)', 'int') = 0
				THEN 
					[dbo].[fn.Upgrade.CreateBackupJobSessionXmlLog] (
						[dbo].[C.Backup.Model.JobSessions].[creation_time],
						[dbo].[C.Backup.Model.JobSessions].[end_time],
						[dbo].[C.Backup.Model.JobSessions].[operation],
						[dbo].[C.Backup.Model.JobSessions].[description],
						[dbo].[C.Backup.Model.BackupJobSessions].[total_objects],
						[dbo].[C.Backup.Model.BackupJobSessions].[total_size],
						[dbo].[C.Backup.Model.BackupJobSessions].[processed_size],
						[dbo].[C.Backup.Model.BackupJobSessions].[avg_speed],
						[dbo].[C.Backup.Model.BackupJobSessions].[job_source_type],

						-- successes
						(SELECT
							COUNT([dbo].[C.Backup.Model.BackupTaskSessions].[id])
						FROM
							[dbo].[C.Backup.Model.BackupTaskSessions] 
						WHERE
							[dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = [dbo].[C.Backup.Model.BackupJobSessions].[id] AND [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 0),

						-- warnings
						(SELECT
							COUNT([dbo].[C.Backup.Model.BackupTaskSessions].[id])
						FROM
							[dbo].[C.Backup.Model.BackupTaskSessions] 
						WHERE
							[dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = [dbo].[C.Backup.Model.BackupJobSessions].[id] AND [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 3),

						-- errors
						(SELECT
							COUNT([dbo].[C.Backup.Model.BackupTaskSessions].[id])
						FROM
							[dbo].[C.Backup.Model.BackupTaskSessions] 
						WHERE
							[dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = [dbo].[C.Backup.Model.BackupJobSessions].[id] AND [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 2)
						)
				ELSE
					[log_xml]
			END
		FROM
			[dbo].[C.Backup.Model.BackupJobSessions] RIGHT JOIN [dbo].[C.Backup.Model.JobSessions] 
		ON
			[dbo].[C.Backup.Model.BackupJobSessions].[id] = [dbo].[C.Backup.Model.JobSessions].[id]
		WHERE
			[dbo].[C.Backup.Model.JobSessions].[id] = @session_id
	)
	
	IF @log_xml is NULL
		RAISERROR(N'The session log xml is empty for the JobLog report', 9, 1 )
		
		
	DECLARE @session_text TABLE
	(
		time nvarchar(50),
		severity int,
		text nvarchar(max),
		color int,
		ordinal int
	)
	DECLARE @color_msg int, @color_suc int, @color_wrn int, @color_err int
	SELECT @color_msg = -1, @color_suc = -8519811, @color_wrn = -131, @color_err = -33411
	
	--
	DECLARE @xmlTbl INT
	EXEC sp_xml_preparedocument @xmlTbl OUTPUT, @log_xml	
	
	INSERT INTO @session_text 
	SELECT 
		[Time] AS 'Time',	 
			CASE [Status]
				WHEN 'ESucceeded' THEN 1 -- SUCCESS
				WHEN 'EFailed' THEN 3 -- ERROR
				WHEN 'EWarning' THEN 2 -- WARNING
				ELSE 0 
			END AS severity,
		[Title] AS text,
		CASE [Status]
			WHEN 'ESucceeded' THEN @color_suc -- SUCCESS
			WHEN 'EFailed' THEN @color_err -- ERROR
			WHEN 'EWarning' THEN @color_wrn -- WARNING
			ELSE @color_msg
		END AS 'color',
		[Id] as ordinal
	FROM OPENXML( @xmlTbl, 'log_xml/Root/Log', 1)
	WITH (
		[Id] int '@Id',
		[Status] nvarchar(50) '@Status',
		[Time]  nvarchar(50) '@Time',
		[Title] nvarchar(255) 'Title'
	)
	ORDER BY [Id] ASC
	
	EXEC sp_xml_removedocument @xmlTbl
	
	SELECT 
		@session_id as 'id',
		[dbo].[fn.Upgrade.ConvertXmlDateTimeToSql]([Time]) as 'time',
		*	
	FROM
		@session_text 
	ORDER BY
		[Id]
	ASC
	
	END
GO

-----------------------------------------------------

--	
-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.CollectLog]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.CollectLog]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.CollectLog]
	@attributesXml XML =	N'<?xml version="1.0"?><attributes />',
	@filtersXml XML =		N'<?xml version="1.0"?>
								<filters>
								</filters>',
	@ordersXml XML =		N'<orders></orders>',
	@is_all_data bit = 0 OUT
AS
BEGIN
	SET NOCOUNT ON;
	
	-- IDENTIFY SESSION
	DECLARE @session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'session_id', @session_id OUTPUT
	IF @session_id IS NULL
		RAISERROR(N'The session identifier was not supplied for the CollectLog report', 9, 1 )

	DECLARE @job_type int
	EXEC [dbo].[usp.GridReport.Util.FindIntAttribute] @attributesXml, N'job_type', @job_type OUTPUT
	IF @job_type IS NULL
		RAISERROR(N'The job type was not supplied for the CollectLog report', 9, 1 )

	DECLARE @color_msg int, @color_suc int, @color_wrn int, @color_err int
	SELECT @color_msg = -1, @color_suc = -8519811, @color_wrn = -131, @color_err = -33411

	SELECT
		@session_id as id,
		[dbo].[Enterprise.SessionEvents].[event_time] as time,
		CASE [dbo].[Enterprise.SessionEvents].[severity]
			WHEN 4 THEN 1
			ELSE [dbo].[Enterprise.SessionEvents].[severity]
		END as severity,
		[dbo].[Enterprise.SessionEvents].[ordinal_num],
		[dbo].[Enterprise.SessionEvents].[text],
		CASE [dbo].[Enterprise.SessionEvents].[severity]
			WHEN 1 THEN @color_suc
			WHEN 2 THEN @color_wrn
			WHEN 3 THEN @color_err
			ELSE @color_msg
		END as color
	FROM [dbo].[Enterprise.SessionEvents]
	WHERE [dbo].[Enterprise.SessionEvents].[session_id] = @session_id
	ORDER BY [dbo].[Enterprise.SessionEvents].[event_time] ASC, [dbo].[Enterprise.SessionEvents].[ordinal_num] ASC
END
GO
-----------------------------------------------------

			
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.CurrentJobs]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.CurrentJobs]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.CurrentJobs]
	@attributesXml XML =	N'<?xml version="1.0"?><attributes />',
	@filtersXml XML =		N'<?xml version="1.0"?>
								<filters>
									<text_filter id="job_name"><value></value></text_filter>
									<dyn_scope_filter id="backup_srv">
										<low>00000000-0000-0000-0000-000000000000</low>
										<high>FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF</high>
									</dyn_scope_filter>
								</filters>',
	@ordersXml XML =		N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @lowValue_BackupServer uniqueidentifier, @highValue_BackupServer uniqueidentifier
	DECLARE @lowValue_LastResult int, @highValue_LastResult int
	DECLARE @lowValue_JobType int, @highValue_JobType int				

	DECLARE @value_JobName nvarchar(100)

	DECLARE @docFilters int
	EXEC sp_xml_preparedocument @docFilters OUTPUT, @filtersXml

	EXEC [dbo].[usp.GridReport.Util.GetTextFilter] @docFilters, 'job_name', @value_JobName OUT
	EXEC [dbo].[usp.GridReport.Util.GetDynamicScopeFilter] @docFilters, 'backup_srv', @lowValue_BackupServer OUT, @highValue_BackupServer OUT
	EXEC [dbo].[usp.GridReport.Util.GetStaticScopeFilter] @docFilters, 'last_res', @lowValue_LastResult OUT, @highValue_LastResult OUT
	EXEC [dbo].[usp.GridReport.Util.GetStaticScopeFilter] @docFilters, 'job_type', @lowValue_JobType OUT, @highValue_JobType OUT
				
	EXEC sp_xml_removedocument @docFilters

	-- ENUMERATE WHEN LATEST SESSIONS WERE LAUNCHED
	DECLARE @latest_sessions_times TABLE
	(
		job_id uniqueidentifier,
		creation_time datetime,
		db_instance_id uniqueidentifier
	)

	DECLARE @latest_sessions_stat TABLE
	(
		job_id uniqueidentifier,		
		session_id uniqueidentifier,
		creation_time datetime,
		job_status    int,
		db_instance_id uniqueidentifier
	);
			
	INSERT INTO @latest_sessions_times( [job_id], [creation_time], [db_instance_id] )
	(	
		SELECT [dbo].[view.AllJobsSessions].[job_id], MAX([dbo].[view.AllJobsSessions].[creation_time]), [dbo].[view.AllJobsSessions].[db_instance_id]
		FROM [dbo].[C.BJobs] jobs 
		INNER JOIN [dbo].[view.AllJobsSessions]	ON jobs.id = [dbo].[view.AllJobsSessions].job_id	
		GROUP BY [dbo].[view.AllJobsSessions].[job_id], [dbo].[view.AllJobsSessions].[db_instance_id]
	)

	-- ENUMERATE SESSIONS STATISTIC
	INSERT INTO @latest_sessions_stat( [job_id],[session_id], [creation_time], [job_status], [db_instance_id])
	(
		SELECT [dbo].[view.AllJobsSessions].[job_id],			   
				[dbo].[view.AllJobsSessions].[id] as [session_id],
			   [dbo].[view.AllJobsSessions].[creation_time],
				CASE [dbo].[view.AllJobsSessions].[state]
					WHEN 3 THEN 3
					WHEN 4 THEN 4
					WHEN 5 THEN 5
					WHEN 8 THEN 8
					WHEN 9 THEN 9
					ELSE [dbo].[view.AllJobsSessions].[result]
				END,
			   [dbo].[view.AllJobsSessions].[db_instance_id]	
		FROM 
		[dbo].[C.BJobs] jobs 
		INNER JOIN @latest_sessions_times sessions_times ON jobs.id = sessions_times.job_id
		INNER JOIN [dbo].[view.AllJobsSessions]
		ON sessions_times.[creation_time] = [dbo].[view.AllJobsSessions].[creation_time] AND
		   sessions_times.[job_id] = [dbo].[view.AllJobsSessions].[job_id] AND
		   sessions_times.[db_instance_id] = [dbo].[view.AllJobsSessions].[db_instance_id]
	)

	-- ENUMERATE LATEST COMPLETED SESSIONS
	DECLARE  @latest_completedsessions_times  TABLE
	(
		job_id uniqueidentifier,
		end_time datetime,
		db_instance_id uniqueidentifier
	)
	INSERT INTO @latest_completedsessions_times( [job_id], [end_time], [db_instance_id] )
	(
		SELECT [dbo].[view.AllJobsSessions].[job_id], MAX( [dbo].[view.AllJobsSessions].[end_time] ), [dbo].[view.AllJobsSessions].[db_instance_id]
		FROM [dbo].[C.BJobs] jobs 
		INNER JOIN [dbo].[view.AllJobsSessions] ON jobs.id = [dbo].[view.AllJobsSessions].job_id
		WHERE ( [dbo].[view.AllJobsSessions].[state] = -1 )
		GROUP BY [dbo].[view.AllJobsSessions].[job_id], [dbo].[view.AllJobsSessions].[db_instance_id]
	)

	DECLARE @latest_completedsessions_info TABLE
	(
		job_id uniqueidentifier,
		session_id uniqueidentifier,					
		result  int,
		result_text nvarchar( 50 ),
		db_instance_id uniqueidentifier,
		end_time datetime,
		creation_time datetime
	)
	INSERT INTO @latest_completedsessions_info( [job_id], [session_id], [result], [result_text], [db_instance_id], [end_time], [creation_time] )
	(
	SELECT [dbo].[view.AllJobsSessions].[job_id],
		   [dbo].[view.AllJobsSessions].[id],
		   'result' =
			CASE 
			   WHEN [dbo].[view.AllJobsSessions].[result] = 0 THEN 0
			   WHEN [dbo].[view.AllJobsSessions].[result] = 1 THEN 1
			   WHEN [dbo].[view.AllJobsSessions].[result] = 2 THEN 2
		    END,
			'result_text' = 
			CASE
			  WHEN 	[dbo].[view.AllJobsSessions].[result] = 0	THEN 'Success'
			  WHEN 	[dbo].[view.AllJobsSessions].[result] = 1	THEN 'Warning'
			  WHEN 	[dbo].[view.AllJobsSessions].[result] = 2	THEN 'Failed'
			  ELSE N''
		    END,
			[dbo].[view.AllJobsSessions].[db_instance_id],
			[dbo].[view.AllJobsSessions].[end_time],
			[dbo].[view.AllJobsSessions].[creation_time]
	FROM  
		[dbo].[C.BJobs] jobs 
		INNER JOIN @latest_completedsessions_times sessions_times ON jobs.id = sessions_times.job_id
		INNER JOIN [dbo].[view.AllJobsSessions] ON 
			sessions_times.[end_time] = [dbo].[view.AllJobsSessions].[end_time] AND
			sessions_times.[job_id] = [dbo].[view.AllJobsSessions].[job_id] AND
			sessions_times.[db_instance_id] = [dbo].[view.AllJobsSessions].[db_instance_id]
						
	)

	-- ENUMERATE LATEST SUCCESSES
	DECLARE @latest_succeededsessions_times TABLE
	(
		job_id  uniqueidentifier,
		success_time datetime,
		db_instance_id uniqueidentifier
	)
	INSERT INTO @latest_succeededsessions_times( [job_id], [success_time], [db_instance_id] )
	(
		SELECT [dbo].[view.AllJobsSessions].[job_id], MAX( [dbo].[view.AllJobsSessions].[end_time]), [dbo].[view.AllJobsSessions].[db_instance_id]
		FROM [dbo].[C.BJobs] jobs 
		INNER JOIN [dbo].[view.AllJobsSessions]	ON jobs.id = [dbo].[view.AllJobsSessions].job_id
		WHERE -- STATUS Success or warning
			  ( [dbo].[view.AllJobsSessions].[state] = -1 AND ( [dbo].[view.AllJobsSessions].[result] = 0 OR [dbo].[view.AllJobsSessions].[result] = 1  ) ) 
		GROUP BY [dbo].[view.AllJobsSessions].[job_id], [dbo].[view.AllJobsSessions].[db_instance_id]
	)

	--CURRENT SESSIONS
	DECLARE @current_sessions_info TABLE
	(
		job_id uniqueidentifier,
		session_id uniqueidentifier,
		creation_time datetime,
		is_retry bit
	)
	INSERT INTO @current_sessions_info( [job_id], [session_id], [creation_time], [is_retry])
	(
		SELECT
			[dbo].[view.AllJobsSessions].[job_id],
			[dbo].[view.AllJobsSessions].[id],
			[dbo].[view.AllJobsSessions].[creation_time],
			[dbo].[view.AllJobsSessions].[is_retry]
		FROM 
			[dbo].[C.BJobs] 
			INNER JOIN [dbo].[view.AllJobsSessions] 
				ON [dbo].[C.BJobs].[id] = [dbo].[view.AllJobsSessions].[job_id] AND
					[dbo].[view.AllJobsSessions].[creation_time] = 
					(
						SELECT MAX(s.[creation_time])
						FROM [dbo].[view.AllJobsSessions] s 
						WHERE s.[job_id] = [dbo].[C.BJobs].[id]
					)					
			WHERE ( [dbo].[view.AllJobsSessions].[state] <> -1 )
	)

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT


	-- FINALLY, SELECT DATA
	SELECT
		DataSrc.id,
		DataSrc.name,
		DataSrc.type,
		DataSrc.description,
		DataSrc.status,
		DataSrc.last_success,
		DataSrc.backup_server,
		DataSrc.session_id,
		DataSrc.session_creation_time,
		DataSrc.latest_run,
		DataSrc.retry_text,
		DataSrc.platform,
		next_run = 
			CASE 
				WHEN DataSrc.is_continuous = 1 AND DataSrc.next_run IS NOT NULL THEN NULL
				WHEN DataSrc.is_continuous = 1 AND DataSrc.type IN (24, 28, 51) AND DataSrc.schedule_enabled = 1 THEN NULL 
				WHEN DataSrc.is_scheduleAfterJob =1 AND DataSrc.parent_schedule_id IS NOT NULL THEN NULL
				ELSE DataSrc.next_run
			END,
		next_run_def_value =
			CASE 
				WHEN DataSrc.is_continuous = 1 AND DataSrc.next_run IS NOT NULL THEN 'Continuous'
				WHEN DataSrc.is_continuous = 1 AND DataSrc.type IN (24, 28, 51) AND DataSrc.schedule_enabled = 1 THEN 'Continuous' 
				WHEN DataSrc.is_scheduleAfterJob = 1 AND DataSrc.parent_schedule_id IS NOT NULL THEN 'After [' + (SELECT name FROM [dbo].[C.BJobs] WHERE [id] = DataSrc.parent_schedule_id) + ']'
				ELSE DataSrc.next_run_def_value
			END,
		DataSrc.bs_version

	FROM
	(
			SELECT [dbo].[C.BJobs].[id],
			   [dbo].[C.BJobs].[name] as name,
			   [dbo].[C.BJobs].[type] as type,
			   [dbo].[C.BJobs].[description] as description,					   
			   'status' = 
			   CASE
				  WHEN [dbo].[C.BJobs].[latest_result] = 3 THEN 3
				  WHEN [dbo].[C.BJobs].[latest_result] = 4 THEN 4
				  WHEN latest_sessions.[job_status] IS NULL THEN -1
				  ELSE latest_sessions.[job_status]
			   END,
			   latest_successes.[success_time] as last_success,
			   [dbo].[Repl.Topology.BackupServers].[display_name] as backup_server,
				CASE 
					-- for sb jobs use current session id
					WHEN [dbo].[C.BJobs].[type] = 3 THEN current_sessions.[session_id]
					-- for others jobs use completed session id
					ELSE latest_sessions.[session_id]					
				END as session_id,
				CASE 
					-- for sb jobs use current session id
					WHEN [dbo].[C.BJobs].[type] = 3 THEN current_sessions.[creation_time]
					-- for others jobs use completed session id
					ELSE latest_sessions.[creation_time]	
				END as session_creation_time,		   
			   latest_completed_sessions.[end_time] as latest_run,
			   CASE current_sessions.[is_retry]
					WHEN 1 THEN N' (retry)'
					ELSE N''
			   END as retry_text,
			   [C.BJobs].[schedule].value('(/ScheduleOptions/OptionsContinuous/Enabled)[1]', 'bit') AS is_continuous,
			   [C.BJobs].[schedule].value('(/ScheduleOptions/OptionsScheduleAfterJob/IsEnabled)[1]', 'bit') AS is_scheduleAfterJob,
			   [C.BJobs].[parent_schedule_id],
			   [dbo].[C.BJobs].[next_run_time] as next_run,
			   [C.BJobs].[schedule_enabled],
			   CASE 
					WHEN [dbo].[C.BJobs].[schedule_enabled] = 0 THEN 'Disabled'
					ELSE 'Not scheduled'
			   END as next_run_def_value,
			   'platform' = 
			   CASE 
					WHEN [dbo].[C.BJobs].[type] = 2 THEN -1
					ELSE [dbo].[C.BJobs].[platform]
			   END,
			   [Repl.Topology.BackupServers].[version] as bs_version
			FROM (((( [dbo].[C.BJobs] 
				LEFT JOIN @current_sessions_info current_sessions
				ON [dbo].[C.BJobs].[id] = current_sessions.[job_id])
				LEFT JOIN @latest_sessions_stat latest_sessions
				ON [dbo].[C.BJobs].[id] = latest_sessions.[job_id] AND  [dbo].[C.BJobs].[db_instance_id] = latest_sessions.[db_instance_id])
				LEFT JOIN @latest_completedsessions_info latest_completed_sessions
				ON [dbo].[C.BJobs].[id] = latest_completed_sessions.[job_id] AND [dbo].[C.BJobs].[db_instance_id] = latest_sessions.[db_instance_id])
				LEFT JOIN @latest_succeededsessions_times latest_successes
				ON [dbo].[C.BJobs].[id] = latest_successes.[job_id] AND [dbo].[C.BJobs].[db_instance_id] = latest_successes.[db_instance_id] )
				INNER JOIN [Repl.Topology.BackupServers]
				ON [dbo].[C.BJobs].[db_instance_id] = [dbo].[Repl.Topology.BackupServers].[current_db_id]							
			WHERE
				[dbo].[C.BJobs].name LIKE '%' + @value_JobName + '%' AND
				@lowValue_BackupServer <= [dbo].[Repl.Topology.BackupServers].[id] AND [Repl.Topology.BackupServers].[id] <= @highValue_BackupServer AND
				@lowValue_LastResult <= [dbo].[C.BJobs].[latest_result] AND [dbo].[C.BJobs].[latest_result] <= @highValue_LastResult AND
				@lowValue_JobType <= [dbo].[C.BJobs].[type] AND [dbo].[C.BJobs].[type] <= @highValue_JobType
	) DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'name'			THEN rank() OVER(ORDER BY DataSrc.name ASC)
					WHEN 'platform'		THEN rank() OVER(ORDER BY DataSrc.platform ASC)
					WHEN 'description'	THEN rank() OVER(ORDER BY DataSrc.description ASC)
					WHEN 'status'		THEN rank() OVER(ORDER BY DataSrc.status ASC)
					WHEN 'last_success'	THEN rank() OVER(ORDER BY DataSrc.last_success ASC)
					WHEN 'backup_server'THEN rank() OVER(ORDER BY DataSrc.backup_server ASC)
					WHEN 'type'			THEN rank() OVER(ORDER BY DataSrc.type ASC)					
					WHEN 'latest_run'	THEN rank() OVER(ORDER BY DataSrc.latest_run ASC)
					WHEN 'next_run'	    THEN rank() OVER(ORDER BY DataSrc.next_run ASC)					
				END
			WHEN 0 THEN
				CASE @col_name
					WHEN 'name'			THEN rank() OVER(ORDER BY DataSrc.name DESC)
					WHEN 'platform'		THEN rank() OVER(ORDER BY DataSrc.platform DESC)
					WHEN 'description'	THEN rank() OVER(ORDER BY DataSrc.description DESC)
					WHEN 'status'		THEN rank() OVER(ORDER BY DataSrc.status DESC)
					WHEN 'last_success'	THEN rank() OVER(ORDER BY DataSrc.last_success DESC)
					WHEN 'backup_server'THEN rank() OVER(ORDER BY DataSrc.backup_server DESC)
					WHEN 'type'			THEN rank() OVER(ORDER BY DataSrc.type DESC)
					WHEN 'latest_run'	THEN rank() OVER(ORDER BY DataSrc.latest_run DESC)
					WHEN 'next_run'	    THEN rank() OVER(ORDER BY DataSrc.next_run DESC)					
				END
			ELSE rank() OVER(ORDER BY DataSrc.latest_run ASC)
		END

END
GO	
-----------------------------------------------------



		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.BackupServers]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.BackupServers]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.BackupServers]
	@attributesXml XML = N'<?xml version="1.0"?><attributes />',
	@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
	@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @report_table TABLE
	(
		id uniqueidentifier,					
		display_name nvarchar(255),	
		description nvarchar(2000),
		backupsrv_status int,
		jobs_count  int,
		sb_jobs_count int,
		vms_count int,
	    vms_total_size BigInt,
	    server_version nvarchar(50) --bytes
	)
	--  ENUMERATE SERVERS THAT WERE NOT REPLICATED YET
	INSERT INTO @report_table([id], [display_name], [description],
							  [backupsrv_status], [jobs_count], [vms_count], [sb_jobs_count], [vms_total_size], [server_version])
	(
		SELECT 
			[dbo].[Repl.Topology.BackupServers].[id],
			[dbo].[Repl.Topology.BackupServers].[display_name], 
			[dbo].[Repl.Topology.BackupServers].[description], -1, 0, 0, 0, 0,
			[dbo].[Repl.Topology.BackupServers].[version]
		FROM [dbo].[Repl.Topology.BackupServers]
		WHERE [dbo].[Repl.Topology.BackupServers].[current_db_id] IS NULL
	)
	-- GET NUMBER OF JOBS PER SERVER INSTANCE
	DECLARE @jobs_stat TABLE
	(
		db_instance_id uniqueidentifier,
		jobs_count  int
	)
    INSERT INTO @jobs_stat( [db_instance_id], [jobs_count])
	(
		SELECT [dbo].[C.BJobs].[db_instance_id], COUNT([dbo].[C.BJobs].[id])
		FROM [dbo].[C.BJobs]
		WHERE [dbo].[C.BJobs].[type] IN (0, 1, 2, 24, 28, 51)
		GROUP BY [dbo].[C.BJobs].[db_instance_id]
	)
	DECLARE @sb_jobs_stat TABLE
	(
		db_instance_id uniqueidentifier,
		sb_jobs_count  int
	)
	INSERT INTO @sb_jobs_stat( [db_instance_id], [sb_jobs_count])
	(
		SELECT [dbo].[C.BJobs].[db_instance_id], COUNT([dbo].[C.BJobs].[id])
		FROM [dbo].[C.BJobs]
		WHERE [dbo].[C.BJobs].[type] IN (3)
		GROUP BY [dbo].[C.BJobs].[db_instance_id]
	)
	-- GET TABLE OF VMS PER SERVERS
	DECLARE @vms_per_servers TABLE
	(
		db_instance_id uniqueidentifier,
		vm_id  uniqueidentifier
	)
	INSERT INTO @vms_per_servers( [db_instance_id], [vm_id])
	(
		SELECT  [dbo].[ObjectsInBackups].[db_instance_id], [dbo].[ObjectsInBackups].[object_id]
		FROM [dbo].[ObjectsInBackups]
		GROUP BY [dbo].[ObjectsInBackups].[db_instance_id], [dbo].[ObjectsInBackups].[object_id]
	)
	-- GET STATISTIC OF VMS SIZES AND NUMBER PER SERVER
	DECLARE @vms_stat TABLE
	( 
		db_instance_id uniqueidentifier,
		vms_count int,
		vms_total_size BigInt
	)
	INSERT INTO @vms_stat( [db_instance_id], [vms_count], [vms_total_size])
	SELECT * FROM [dbo].[fn.Backup.GetVmStats]()
	-- ADD REPORT RECORDS FOR THE SERVERS THAT WERE REPLICATED
	
	DECLARE @last_collect_status TABLE
	( 
		server_id uniqueidentifier,
		status int
	)
	INSERT INTO @last_collect_status( [server_id], [status])
	SELECT	
		[dbo].[Repl.Topology.BackupServers].[id] AS [server_id],
		[dbo].[Repl.ServerSessions].[status]
    FROM [dbo].[Repl.ServerSessions]
    INNER JOIN [dbo].[Repl.SessionRelations] ON [dbo].[Repl.SessionRelations].[server_session_id] = [dbo].[Repl.ServerSessions].[id]	
    INNER JOIN (	
		SELECT TOP(1)
        [dbo].[Enterprise.Sessions].[id] as repl_session_id
        FROM
			[dbo].[Enterprise.Sessions]
			WHERE [dbo].[Enterprise.Sessions].[request_type] <> 2 AND [dbo].[Enterprise.Sessions].[status] <> 2 
        ORDER BY [dbo].[Enterprise.Sessions].[end_date] DESC
        ) q ON [dbo].[Repl.SessionRelations].[parent_session_id] = q.[repl_session_id]
    INNER JOIN [dbo].[Repl.Topology.BackupServers] ON [dbo].[Repl.ServerSessions].[backup_server_id] = [dbo].[Repl.Topology.BackupServers].[id]
	
	
	
	
	INSERT INTO @report_table([id], [display_name], [description],
							  [backupsrv_status], [jobs_count], [sb_jobs_count], [vms_count], [vms_total_size], [server_version])
	(
		SELECT [dbo].[Repl.Topology.BackupServers].[id],	
			   [dbo].[Repl.Topology.BackupServers].[display_name],
			   [dbo].[Repl.Topology.BackupServers].[description],
			   CASE last_collect_status.[status]
					WHEN NULL THEN -1
					WHEN 1 THEN 0
					WHEN 3 THEN 1
			   END
			   AS status,
			   jobs_statistic.[jobs_count],
			   sb_jobs_statistic.[sb_jobs_count],
			   vms_statistic.[vms_count],
			   vms_statistic.[vms_total_size],
			   (PARSENAME([dbo].[Repl.Topology.BackupServers].[version], 4) + '.' + PARSENAME([dbo].[Repl.Topology.BackupServers].[version], 3)) as server_version
		FROM [dbo].[Repl.Topology.BackupServers] 
			 LEFT JOIN 
			 (
				@jobs_stat jobs_statistic LEFT JOIN @vms_stat vms_statistic
				ON jobs_statistic.[db_instance_id] = vms_statistic.[db_instance_id]
			 ) ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = jobs_statistic.[db_instance_id]
			 LEFT JOIN @sb_jobs_stat sb_jobs_statistic ON [dbo].[Repl.Topology.BackupServers].[current_db_id] = sb_jobs_statistic.[db_instance_id]
			 LEFT JOIN @last_collect_status last_collect_status ON ([dbo].[Repl.Topology.BackupServers].[id] = last_collect_status.[server_id])
		WHERE [dbo].[Repl.Topology.BackupServers].[current_db_id] IS NOT NULL
	)
    --  CLEAR JOB SIZE WHERE IT IS UNKNOWN
	UPDATE @report_table
	SET vms_total_size = 0
	WHERE vms_total_size IS NULL
    UPDATE @report_table
	SET jobs_count = 0
	WHERE jobs_count IS NULL
    UPDATE @report_table
	SET vms_count = 0
	WHERE vms_count IS NULL
	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
	SELECT 
		*
	FROM
		@report_table												
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'display_name'		THEN rank() OVER(ORDER BY display_name ASC)
					WHEN 'description'		THEN rank() OVER(ORDER BY description ASC)
					WHEN 'backupsrv_status'	THEN rank() OVER(ORDER BY backupsrv_status ASC)
					WHEN 'jobs_count'		THEN rank() OVER(ORDER BY jobs_count ASC)
					WHEN 'sb_jobs_count'	THEN rank() OVER(ORDER BY sb_jobs_count ASC)
					WHEN 'vms_count'		THEN rank() OVER(ORDER BY vms_count ASC)
					WHEN 'vms_total_size'	THEN rank() OVER(ORDER BY vms_total_size ASC)
					WHEN 'server_version'   THEN rank() OVER(ORDER BY server_version ASC)
				END
			WHEN 0 THEN
				CASE @col_name
					WHEN 'display_name'		THEN rank() OVER(ORDER BY display_name DESC)
					WHEN 'description'		THEN rank() OVER(ORDER BY description DESC)
					WHEN 'backupsrv_status'	THEN rank() OVER(ORDER BY backupsrv_status DESC)
					WHEN 'jobs_count'		THEN rank() OVER(ORDER BY jobs_count DESC)
					WHEN 'sb_jobs_count'	THEN rank() OVER(ORDER BY sb_jobs_count DESC)
					WHEN 'vms_count'		THEN rank() OVER(ORDER BY vms_count DESC)
					WHEN 'vms_total_size'	THEN rank() OVER(ORDER BY vms_total_size DESC)
					WHEN 'server_version'   THEN rank() OVER(ORDER BY server_version DESC)
				END
			ELSE
				rank() OVER(ORDER BY display_name ASC)
		END
END	
GO 
-----------------------------------------------------
		
									
-----------------------------------------------------
--		
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.JobsPerServer]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.JobsPerServer]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.JobsPerServer]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="id" value="fb02189d-1a2a-4698-92eb-c88a2e68b466"></attr></attributes>',
	@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
	@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'
AS
BEGIN
	SET NOCOUNT ON;

	-- IDENTIFY BACKUP SERVER
	DECLARE @backupsrv_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @backupsrv_id OUTPUT				
	IF @backupsrv_id is NULL
		RAISERROR(N'The backup server unique identifier was not specified for the JobsPerServer report', 9, 1 )
								
	-- EXIT IF DATA WERE NOT COLLECTED
	DECLARE @vb_db_id uniqueidentifier
	SELECT @vb_db_id = [dbo].[fn.BackupSrv.FindCurrentDbId]( @backupsrv_id )
	IF @vb_db_id IS NULL
	BEGIN
		DECLARE @reports_table TABLE
		(
			id uniqueidentifier,
			job_name nvarchar(255),
			job_description nvarchar( 1024 ),
			vms_count int,
			total_size BigInt,
			job_status  int,
			job_lastres_text nvarchar( 100 ),
			job_last_session_id uniqueidentifier,
			last_success datetime
		)
		SELECT * FROM @reports_table
		RETURN
	END


	-- ENUMERATE SESSIONS STATISTIC
	DECLARE @latest_sessions_stat TABLE
	(
	id uniqueidentifier,
	job_id uniqueidentifier,
	vms_count int,
	total_size BigInt,
	sess_state int,
	job_status int
	)
	INSERT INTO @latest_sessions_stat([id], [job_id], [vms_count], [total_size], [sess_state], [job_status] )
	(
	SELECT
			bjs.[id],
			bjs.[job_id],						   
			bjs.[total_objects],
			bjs.[total_size],
			bjs.[state],
			bjs.[result]	
	FROM
		(
			SELECT bjss.[job_id], MAX(bjss.[creation_time])
			FROM [dbo].[view.Backup.BackupJobSessions] bjss
			WHERE bjss.[db_instance_id] = @vb_db_id 						  
			GROUP BY bjss.[job_id]					
		) sessions_times(job_id, creation_time),
		[dbo].[view.Backup.BackupJobSessions] bjs
	WHERE
		sessions_times.[creation_time] = bjs.[creation_time] AND
		sessions_times.[job_id] = bjs.[job_id] AND
		bjs.[db_instance_id] = @vb_db_id					
	)

	-- ENUMERATE LATEST COMPLETED SESSIONS
	DECLARE  @latest_completedsessions_times  TABLE
	(
	job_id uniqueidentifier,
	end_time datetime
	)
	INSERT INTO @latest_completedsessions_times( [job_id], [end_time] )
	(
		SELECT bjs.[job_id], MAX(bjs.[end_time] )
		FROM [dbo].[view.Backup.BackupJobSessions] bjs
		WHERE bjs.[db_instance_id] = @vb_db_id AND
			  (bjs.[result] >= 0 AND bjs.[result] <= 2 )
		GROUP BY bjs.[job_id]
	)
	DECLARE @latest_completedsessions_info TABLE
	(
	id uniqueidentifier,
	job_id uniqueidentifier,
	session_id uniqueidentifier,
	result  int,					
	result_text nvarchar( 50 ),
	creation_time datetime,
	is_retry bit
	)
	INSERT INTO @latest_completedsessions_info( [id], [job_id], [session_id], [result], [result_text], [creation_time], [is_retry] )
	(
	SELECT 
			bjs.[id],
			bjs.[job_id],
			bjs.[id],
		   'result' =
			CASE 
			   WHEN bjs.[result] = 0 THEN 0
			   WHEN bjs.[result] = 1 THEN 1
			   WHEN bjs.[result] = 2 THEN 2
			END,
			'result_text' = 
			CASE						   
			  WHEN 	bjs.[result] = 0	THEN 'Success'
			  WHEN 	bjs.[result] = 1	THEN 'Warning'
			  WHEN 	bjs.[result] = 2	THEN 'Failed'
			  ELSE N''
			END	,
			bjs.[creation_time],
			bjs.[is_retry]
	FROM
		@latest_completedsessions_times sessions_times
	INNER JOIN
		[dbo].[view.Backup.BackupJobSessions] bjs
	ON sessions_times.[end_time] = bjs.[end_time] AND
	   sessions_times.[job_id] = bjs.[job_id]
	WHERE bjs.[db_instance_id] = @vb_db_id					
	)

	-- ENUMERATE LATEST SUCCESSES
	DECLARE @latest_succeededsessions_times TABLE
	(
	job_id  uniqueidentifier,
	success_time datetime
	)
	INSERT INTO @latest_succeededsessions_times( [job_id], [success_time] )
	(
		SELECT
			bjs.job_id,
			MAX(bjs.end_time)
		FROM
			[dbo].[view.Backup.BackupJobSessions] bjs
		WHERE bjs.db_instance_id = @vb_db_id AND
			  (bjs.result = 0 OR bjs.result = 1) -- STATUS Success or warning 
		GROUP BY bjs.[job_id]
	)				

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	-- FINALLY, SELECT DATA
	SELECT * 
	FROM

		(SELECT [dbo].[C.BJobs].[id],
			   [dbo].[C.BJobs].[name] as job_name,
			   [dbo].[C.BJobs].[type] as job_type,
			   [dbo].[C.BJobs].[description] as job_description,
			   latest_sessions.[vms_count] as vms_count,
			   latest_sessions.[total_size] as total_size,
			   'job_status' = 
			   CASE
				  WHEN latest_sessions.[job_status] IS NULL THEN -1
				  WHEN latest_sessions.[id] != latest_completed_sessions.[id] THEN latest_sessions.[sess_state]
				  ELSE latest_sessions.[job_status]
			   END,
			   'last_result' = 
				CASE
					WHEN latest_completed_sessions.[result] IS NULL THEN -1
					ELSE latest_completed_sessions.[result]
				END,
			   latest_completed_sessions.[session_id] as job_last_session_id,
			   latest_successes.[success_time] as last_success,
			   latest_completed_sessions.[creation_time] as session_creation_time,
				CASE latest_completed_sessions.[is_retry]
					WHEN 1 THEN N' (retry)'
					ELSE N''
				END as retry_text
			   
		FROM (( [dbo].[C.BJobs] LEFT JOIN @latest_sessions_stat latest_sessions
				ON [dbo].[C.BJobs].[id] = latest_sessions.[job_id] )
				LEFT JOIN @latest_completedsessions_info latest_completed_sessions
				ON [dbo].[C.BJobs].[id] = latest_completed_sessions.[job_id] )
			 LEFT JOIN @latest_succeededsessions_times latest_successes
			 ON [dbo].[C.BJobs].[id] = latest_successes.[job_id]
		WHERE 
			[dbo].[C.BJobs].[db_instance_id] = @vb_db_id AND
			[dbo].[C.BJobs].[type] IN (0, 1, 2, 24, 28, 51) -- backup, replica, copy, vm tape backup, file tape backup,  backup copy
	) DataSrc			
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'job_name'			THEN rank() OVER(ORDER BY DataSrc.job_name ASC)
					WHEN 'vms_count'		THEN rank() OVER(ORDER BY DataSrc.vms_count ASC)
					WHEN 'total_size'		THEN rank() OVER(ORDER BY DataSrc.total_size ASC)
					WHEN 'job_status'		THEN rank() OVER(ORDER BY DataSrc.job_status ASC)
					WHEN 'last_result'		THEN rank() OVER(ORDER BY DataSrc.last_result ASC)
					WHEN 'last_success'		THEN rank() OVER(ORDER BY DataSrc.last_success ASC)
					WHEN 'job_type'			THEN rank() OVER(ORDER BY DataSrc.job_type ASC)
				END
			WHEN 0 THEN
				CASE @col_name
					WHEN 'job_name'			THEN rank() OVER(ORDER BY DataSrc.job_name DESC)
					WHEN 'vms_count'		THEN rank() OVER(ORDER BY DataSrc.vms_count DESC)
					WHEN 'total_size'		THEN rank() OVER(ORDER BY DataSrc.total_size DESC)
					WHEN 'job_status'		THEN rank() OVER(ORDER BY DataSrc.job_status DESC)
					WHEN 'last_result'		THEN rank() OVER(ORDER BY DataSrc.last_result DESC)
					WHEN 'last_success'		THEN rank() OVER(ORDER BY DataSrc.last_success DESC)
					WHEN 'job_type'			THEN rank() OVER(ORDER BY DataSrc.job_type DESC)
				END
			--ELSE							
		END							

END	
GO
-----------------------------------------------------

		
		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.SessionsPerJob]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.SessionsPerJob]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.SessionsPerJob]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="id" value="c23f7fda-8b54-4e92-a6e7-9d60660c9a37"></attr></attributes>',
	@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
	@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'
AS
BEGIN
	SET NOCOUNT ON;

	-- IDENTIFY BACKUP SERVER
	DECLARE @job_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @job_id OUTPUT				
	IF @job_id is NULL
		RAISERROR(N'The unique identifier of the backup job was not specified for the SessionsPerJob report', 9, 1 )
								
	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
		
	SELECT *
	FROM
	
			(SELECT [dbo].[view.Backup.BackupJobSessions].[id],
				[dbo].[view.Backup.BackupJobSessions].[creation_time] as job_start_time,
				[dbo].[view.Backup.BackupJobSessions].[end_time] as job_end_time,
				CASE 
					WHEN [dbo].[view.Backup.BackupJobSessions].[result] = -1 THEN 5 -- Running 	
					ELSE [dbo].[view.Backup.BackupJobSessions].[result]					
				END as job_status,								
				CASE
					WHEN [dbo].[view.Backup.BackupJobSessions].[result] = 0 THEN N'Success'
					WHEN [dbo].[view.Backup.BackupJobSessions].[result] = 1 THEN N'Warning'
					WHEN [dbo].[view.Backup.BackupJobSessions].[result] = 2 THEN N'Failed'
					WHEN [dbo].[view.Backup.BackupJobSessions].[result] = 3 THEN N'Starting'
					WHEN [dbo].[view.Backup.BackupJobSessions].[result] = 4 THEN N'Stopping'
					ELSE N'Running'
				END as job_status_text,
			   CASE
				 WHEN [dbo].[view.Backup.BackupJobSessions].[avg_speed] IS NULL THEN 0
				 ELSE [dbo].[view.Backup.BackupJobSessions].[avg_speed]
			   END as perf_rate,
				[dbo].[view.Backup.BackupJobSessions].[stored_size] as data_transferred,
				[dbo].[C.BJobs].[name] as owner_job_name,
				CASE [dbo].[view.Backup.BackupJobSessions].[is_retry]
					WHEN 1 THEN N' (retry)'
					ELSE N''
				END as retry_text
				   
			FROM [dbo].[C.BJobs] 
				INNER JOIN [dbo].[view.Backup.BackupJobSessions] 
				ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id]
			WHERE [dbo].[C.BJobs].[id] = @job_id /*AND							  
				  ([dbo].[view.Backup.BackupJobSessions].[result] >= 0 AND [dbo].[view.Backup.BackupJobSessions].[result] <= 2 ) */) DataSrc
	ORDER BY 
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'job_start_time'	THEN rank() OVER(ORDER BY DataSrc.job_start_time ASC)
					WHEN 'job_end_time'		THEN rank() OVER(ORDER BY DataSrc.job_end_time ASC)
					WHEN 'job_status'		THEN rank() OVER(ORDER BY DataSrc.job_status ASC)
					WHEN 'perf_rate'		THEN rank() OVER(ORDER BY DataSrc.perf_rate ASC)
					WHEN 'data_transferred'	THEN rank() OVER(ORDER BY DataSrc.data_transferred ASC)
				END
			WHEN 0 THEN
				CASE @col_name					
					WHEN 'job_start_time'	THEN rank() OVER(ORDER BY DataSrc.job_start_time DESC)
					WHEN 'job_end_time'		THEN rank() OVER(ORDER BY DataSrc.job_end_time DESC)
					WHEN 'job_status'		THEN rank() OVER(ORDER BY DataSrc.job_status DESC)
					WHEN 'perf_rate'		THEN rank() OVER(ORDER BY DataSrc.perf_rate DESC)
					WHEN 'data_transferred'	THEN rank() OVER(ORDER BY DataSrc.data_transferred DESC)
				END
			ELSE 
				rank() OVER(ORDER BY DataSrc.job_start_time DESC)
		END
END
GO
-----------------------------------------------------
	
	
	
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.SessionDetails]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.SessionDetails]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.SessionDetails]
		@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="id" value="dc3e2619-e04e-4ac8-82a9-24ca7d3ef844"></attr></attributes>',
		@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
		@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'
AS
BEGIN	
	SET NOCOUNT ON;
			
	-- IDENTIFY SESSION
	DECLARE @session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @session_id OUTPUT				
	IF @session_id is NULL
		RAISERROR(N'The session identifier was not supplied for the SessionDetails report', 9, 1 )

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	SELECT
		* 
	FROM
		(SELECT
			[dbo].[C.Backup.Model.BackupTaskSessions].[id],
			'vm_id' = 
			CASE 
				WHEN [dbo].[C.Backup.Model.JobSessions].[job_type] = 24 THEN NULL
				ELSE [dbo].[C.Backup.Model.BackupTaskSessions].[object_id]
			END,							
			[dbo].[C.Backup.Model.BackupTaskSessions].[object_name] as vm_name,
			[dbo].[C.Backup.Model.BackupTaskSessions].[creation_time] as vm_processing_start_time,
			[dbo].[C.Backup.Model.BackupTaskSessions].[end_time] as vm_processing_end_time,

			CASE 
				WHEN [dbo].[C.Backup.Model.BackupTaskSessions].[status] > 5 THEN 5
				ELSE [dbo].[C.Backup.Model.BackupTaskSessions].[status]
			END as vm_processing_status,

			[dbo].[C.Backup.Model.BackupTaskSessions].[stored_size] as vm_processed_size,

			[dbo].[fn.Backup.GetSessionLogHtml] (
				CASE
					WHEN
						[dbo].[C.Backup.Model.JobSessions].[state] = -1 AND  -- CBaseSessionInfo.EState.Stopped
						[dbo].[C.Backup.Model.BackupTaskSessions].[log_xml] IS NOT NULL AND
						([dbo].[C.Backup.Model.BackupTaskSessions].[log_xml]).value('count(/*[1]/*[1]/*)', 'int') = 0
					THEN
						[dbo].[fn.Upgrade.CreateBackupTaskSessionXmlLog] (
							[dbo].[C.Backup.Model.BackupTaskSessions].[creation_time],
							[dbo].[C.Backup.Model.BackupTaskSessions].[end_time],
							[dbo].[C.Backup.Model.BackupTaskSessions].[operation],
							[dbo].[C.Backup.Model.BackupTaskSessions].[reason],
							[dbo].[C.Backup.Model.BackupTaskSessions].[total_objects],
							[dbo].[C.Backup.Model.BackupTaskSessions].[total_size],
							[dbo].[C.Backup.Model.BackupTaskSessions].[processed_objects],
							[dbo].[C.Backup.Model.BackupTaskSessions].[processed_size],
							[dbo].[C.Backup.Model.BackupTaskSessions].[avg_speed],
							[dbo].[C.Backup.Model.BackupTaskSessions].[mode],
							[dbo].[C.Backup.Model.BackupTaskSessions].[change_tracking],
							[dbo].[C.Backup.Model.BackupJobSessions].[job_source_type]									
						)
					ELSE
						[dbo].[C.Backup.Model.BackupTaskSessions].[log_xml]
				END
			) as vm_processing_details,

			CASE
				WHEN [dbo].[C.Backup.Model.BackupTaskSessions].[avg_speed] IS NULL THEN 0
				ELSE [dbo].[C.Backup.Model.BackupTaskSessions].[avg_speed]
			END as vm_processing_rate
		FROM
			[dbo].[C.Backup.Model.BackupTaskSessions]
		RIGHT JOIN
			[dbo].[C.Backup.Model.JobSessions] 
		ON
			[dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = [dbo].[C.Backup.Model.JobSessions].[id]
		RIGHT JOIN
			[dbo].[C.Backup.Model.BackupJobSessions]
		ON
			[dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = [dbo].[C.Backup.Model.BackupJobSessions].[id]
		WHERE
			[dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = @session_id) DataSrc
	ORDER BY 
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'vm_name'					THEN rank() OVER(ORDER BY DataSrc.vm_name ASC)
					WHEN 'vm_processing_start_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_start_time ASC)
					WHEN 'vm_processing_end_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_end_time ASC)
					WHEN 'vm_processing_status'		THEN rank() OVER(ORDER BY DataSrc.vm_processing_status ASC)
					WHEN 'vm_processed_size'		THEN rank() OVER(ORDER BY DataSrc.vm_processed_size ASC)
					WHEN 'vm_processing_rate'		THEN rank() OVER(ORDER BY DataSrc.vm_processing_rate ASC)
				END
			WHEN 0 THEN
				CASE @col_name					
					WHEN 'vm_name'					THEN rank() OVER(ORDER BY DataSrc.vm_name DESC)
					WHEN 'vm_processing_start_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_start_time DESC)
					WHEN 'vm_processing_end_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_end_time DESC)
					WHEN 'vm_processing_status'		THEN rank() OVER(ORDER BY DataSrc.vm_processing_status DESC)
					WHEN 'vm_processed_size'		THEN rank() OVER(ORDER BY DataSrc.vm_processed_size DESC)
					WHEN 'vm_processing_rate'		THEN rank() OVER(ORDER BY DataSrc.vm_processing_rate DESC)
				END
			ELSE
				rank() OVER(ORDER BY DataSrc.vm_processing_start_time DESC)
		END

END		
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.JobSessionsHeader]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.JobSessionsHeader]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.JobSessionsHeader]		
		@attributesXml XML = N''
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE @utc_time_min datetime
	SET @utc_time_min = '09.09.1900 0:00:00'
	DECLARE @cur_session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @cur_session_id OUTPUT
	IF @cur_session_id is NULL
		RAISERROR(N'Session unique identifier was not specified', 9, 1 )
	DECLARE @failed_count int, @warning_count int, @job_type int;
	SELECT
		@failed_count = (SELECT COUNT(*) FROM [dbo].[C.Backup.Model.BackupTaskSessions] WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = sessions.[id] AND [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 2),
		@warning_count = (SELECT COUNT(*) FROM [dbo].[C.Backup.Model.BackupTaskSessions] WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = sessions.[id] AND [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 3),
		@job_type = [dbo].[C.BJobs].[type]
	FROM
		[dbo].[C.BJobs], [dbo].[view.Backup.BackupJobSessions] sessions
	WHERE
		[dbo].[C.BJobs].[id] = sessions.[job_id] AND
		sessions.[id] = @cur_session_id 
	
	SELECT 
		sessions.[job_name] as job_name,
		@job_type as job_type,
		sessions.[creation_time] as start_time,
		CASE
			WHEN sessions.[end_time] < @utc_time_min THEN null
			ELSE sessions.[end_time]
		END as end_time,
		sessions.[processed_objects] as processed_objects,
		sessions.[total_objects] as total_objects,
		ISNULL(@failed_count, 0) as failed_count,
		ISNULL(@warning_count, 0) as warning_count,
		sessions.[total_size] as total_size,
		sessions.[processed_size] as processed_size,
		sessions.[avg_speed] as avg_speed,
		sessions.[description] as description,
		sessions.[result] as result
		
	FROM
		[dbo].[view.Backup.BackupJobSessions] sessions
	WHERE
		sessions.[id] = @cur_session_id 
END
GO
-----------------------------------------------------

	
	
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.SessionDetails.Summary]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.SessionDetails.Summary]
GO					
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.SessionDetails.Summary]
	@session_id  uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	DECLARE @success_vm_num int
	DECLARE @failed_vm_num int
	DECLARE @warn_vm_num int

	-- SELECT SUCCEEDED VMs	
	SELECT @success_vm_num = COUNT(*)
	FROM [dbo].[C.Backup.Model.BackupTaskSessions]
	WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = @session_id AND
		  [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 0


	-- SELECT WARNING VMs
	SELECT @warn_vm_num = COUNT(*)
	FROM [dbo].[C.Backup.Model.BackupTaskSessions]
	WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = @session_id AND
		  [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 1


	-- SELECT FAILED VMs
	SELECT @failed_vm_num = COUNT(*)
	FROM [dbo].[C.Backup.Model.BackupTaskSessions]
	WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = @session_id AND
		  [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 2


	-- SELECT SESSION INFO
	SELECT  TOP(1) [dbo].[view.Backup.BackupJobSessions].[total_objects] as total_vm_count,
			@success_vm_num as succeeded_vm_count,
			@failed_vm_num as failed_vm_count,
			@warn_vm_num as warn_vm_count,
			[dbo].[view.Backup.BackupJobSessions].[creation_time] as start_time,
			[dbo].[view.Backup.BackupJobSessions].[end_time] as end_time,
			5000000 as avg_byte_s_speed,
			[dbo].[view.Backup.BackupJobSessions].[total_size] as total_vm_byte_size,
			[dbo].[view.Backup.BackupJobSessions].[processed_size] as processed_vm_byte_size,									
			[dbo].[view.Backup.BackupJobSessions].[description]
	FROM [dbo].[view.Backup.BackupJobSessions]
	WHERE [dbo].[view.Backup.BackupJobSessions].[id] = @session_id

END
GO
-----------------------------------------------------


		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.VmSessionsHistory]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.VmSessionsHistory]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.VmSessionsHistory]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="id" value="b2c755b8-f416-43a1-8d63-9c2b5030e25d"></attr></attributes>',
	@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
	@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'				
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- IDENTIFY BACKUP SERVER
	DECLARE @vm_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @vm_id OUTPUT				
	IF @vm_id is NULL
		RAISERROR(N'The VM unique identifier was not specified for the VmSessionsHistory report', 9, 1 )
		
	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	SELECT * 
	FROM
	(
			SELECT [dbo].[C.Backup.Model.BackupTaskSessions].[id],
				   [dbo].[C.Backup.Model.BackupTaskSessions].[creation_time] as vm_processing_start_time,
				   [dbo].[C.Backup.Model.BackupTaskSessions].[end_time] as vm_processing_end_time,
				   [dbo].[C.Backup.Model.BackupTaskSessions].[status] as vm_processing_status,
				   [dbo].[C.Backup.Model.BackupTaskSessions].[stored_size] as transferred_data,							   
				   CASE
						WHEN [dbo].[C.Backup.Model.BackupTaskSessions].[avg_speed] IS NULL THEN 0
						ELSE [dbo].[C.Backup.Model.BackupTaskSessions].[avg_speed]
				   END as vm_perf_rate,
				   [dbo].[view.Backup.BackupJobSessions].[job_name] as session_owner_name,
				   [dbo].[view.Backup.BackupJobSessions].[creation_time] as session_creation_time,
				   [dbo].[view.Backup.BackupJobSessions].[id] as session_owner_id,
				   [dbo].[fn.Backup.GetSessionLogHtml]( [dbo].[C.Backup.Model.BackupTaskSessions].[log_xml] ) as vm_processing_details,
					CASE [dbo].[view.Backup.BackupJobSessions].[is_retry]
						WHEN 1 THEN N' (retry)'
						ELSE N''
					END as retry_text
			FROM [dbo].[C.Backup.Model.BackupTaskSessions] INNER JOIN [dbo].[view.Backup.BackupJobSessions]
				  ON [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = [dbo].[view.Backup.BackupJobSessions].[id]
			WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[object_id] = @vm_id
			
	) as DataSrc

	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'vm_processing_start_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_start_time ASC)
					WHEN 'vm_processing_end_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_end_time ASC)
					WHEN 'vm_processing_status'		THEN rank() OVER(ORDER BY DataSrc.vm_processing_status ASC)
					WHEN 'transferred_data'			THEN rank() OVER(ORDER BY DataSrc.transferred_data ASC)
					WHEN 'vm_perf_rate'				THEN rank() OVER(ORDER BY DataSrc.vm_perf_rate ASC)
					WHEN 'session_owner_name'		THEN rank() OVER(ORDER BY DataSrc.session_owner_name ASC)
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'vm_processing_start_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_start_time DESC)
					WHEN 'vm_processing_end_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_end_time DESC)
					WHEN 'vm_processing_status'		THEN rank() OVER(ORDER BY DataSrc.vm_processing_status DESC)
					WHEN 'transferred_data'			THEN rank() OVER(ORDER BY DataSrc.transferred_data DESC)
					WHEN 'vm_perf_rate'				THEN rank() OVER(ORDER BY DataSrc.vm_perf_rate DESC)
					WHEN 'session_owner_name'		THEN rank() OVER(ORDER BY DataSrc.session_owner_name DESC)
				END
			ELSE rank() OVER(ORDER BY vm_processing_start_time DESC)
		END
END
GO				
-----------------------------------------------------
		
		
		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.BSessionsForPeriod]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.BSessionsForPeriod]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.BSessionsForPeriod]
	@attributesXml XML,
	@filtersXml XML,
	@ordersXml XML
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @docAttributes int
	EXEC sp_xml_preparedocument @docAttributes OUTPUT, @attributesXml

	DECLARE @atributes table( [name] nvarchar(50), [value] nvarchar(255) );
	INSERT INTO @atributes SELECT name, value
		FROM OPENXML( @docAttributes, '/attributes/attr', 1)
	WITH
	(
	   name nvarchar(50),
	   value nvarchar(255)
	)

	EXEC sp_xml_removedocument @docAttributes

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
		
	DECLARE @session_status int
	SET @session_status = (SELECT TOP(1) CAST(value as int) FROM @atributes WHERE name = 'session_status')

	SELECT *
	FROM
	
		(SELECT	[dbo].[C.BJobs].[id] as id,
				[dbo].[C.BJobs].[name] as job_name,
				[dbo].[Repl.Topology.BackupServers].[id] as backup_srv_id,
				[dbo].[Repl.Topology.BackupServers].[display_name] as backup_srv_name,
				[dbo].[view.Backup.BackupJobSessions].[creation_time] as bsession_start_time,
				[dbo].[view.Backup.BackupJobSessions].[end_time] as bsession_end_time,
				[dbo].[view.Backup.BackupJobSessions].[result] as bsession_status
		FROM [dbo].[Repl.Topology.BackupServers], [dbo].[C.BJobs], [dbo].[view.Backup.BackupJobSessions]
		WHERE [dbo].[Repl.Topology.BackupServers].[current_db_id] = [dbo].[C.BJobs].[db_instance_id] AND
			  [dbo].[C.BJobs].[id] = [dbo].[view.Backup.BackupJobSessions].[job_id] AND
			  [dbo].[view.Backup.BackupJobSessions].[end_time] > DATEADD(hour, -(SELECT TOP(1) CAST(value as int) FROM @atributes WHERE name = 'period_hours') , GETUTCDATE()) AND
				(
					(ISNUMERIC(@session_status) = 1 AND [dbo].[view.Backup.BackupJobSessions].[result] = @session_status ) OR
					(ISNUMERIC(@session_status) <> 1 AND [dbo].[view.Backup.BackupJobSessions].[result] IN (0, 1, 2) )
				)
		) DataSrc

	ORDER BY 
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'job_name'				THEN rank() OVER(ORDER BY DataSrc.job_name ASC)
					WHEN 'backup_srv_name'		THEN rank() OVER(ORDER BY DataSrc.backup_srv_name ASC)
					WHEN 'bsession_start_time'	THEN rank() OVER(ORDER BY DataSrc.bsession_start_time ASC)
					WHEN 'bsession_end_time'	THEN rank() OVER(ORDER BY DataSrc.bsession_end_time ASC)
					WHEN 'bsession_status'		THEN rank() OVER(ORDER BY DataSrc.bsession_status ASC)
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'job_name'				THEN rank() OVER(ORDER BY DataSrc.job_name DESC)
					WHEN 'backup_srv_name'		THEN rank() OVER(ORDER BY DataSrc.backup_srv_name DESC)
					WHEN 'bsession_start_time'	THEN rank() OVER(ORDER BY DataSrc.bsession_start_time DESC)
					WHEN 'bsession_end_time'	THEN rank() OVER(ORDER BY DataSrc.bsession_end_time DESC)
					WHEN 'bsession_status'		THEN rank() OVER(ORDER BY DataSrc.bsession_status DESC)
				END
			ELSE rank() OVER(ORDER BY bsession_start_time DESC)
		END							
END
GO
-----------------------------------------------------
		
		
		
		
		
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.BackupServers]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.BackupServers]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.BackupServers]
	@attributesXml XML,
	@filtersXml XML,
	@ordersXml XML
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	--  Perform stored function
	SELECT *
	FROM
	(
		SELECT [dbo].[Repl.Topology.BackupServers].[id],
			   [dbo].[Repl.Topology.BackupServers].[display_name] as cfg_bs_name,					   
			   CASE 
				WHEN [dbo].[Repl.Topology.BackupServerCreds].[login] IS NULL THEN SYSTEM_USER					   
				ELSE
					CASE 
						WHEN [dbo].[Repl.Topology.BackupServerCreds].[domain] IS NULL THEN N''
						WHEN [dbo].[Repl.Topology.BackupServerCreds].[domain] = N'' THEN N''
						ELSE [dbo].[Repl.Topology.BackupServerCreds].[domain] + N'\'
					END + [dbo].[Repl.Topology.BackupServerCreds].[login]
			   END as cfg_bs_user,
			   [dbo].[Repl.Topology.BackupServers].[description] as cfg_bs_desc,
			   (PARSENAME([dbo].[Repl.Topology.BackupServers].[version], 4) + '.' + PARSENAME([dbo].[Repl.Topology.BackupServers].[version], 3)) as cfg_bs_version
		FROM [dbo].[Repl.Topology.BackupServers] LEFT JOIN [dbo].[Repl.Topology.BackupServerCreds] 
			ON [dbo].[Repl.Topology.BackupServers].[id] = [dbo].[Repl.Topology.BackupServerCreds].[backup_server]
	) as DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'cfg_bs_name'		THEN rank() OVER(ORDER BY DataSrc.cfg_bs_name ASC)
					WHEN 'cfg_bs_user'		THEN rank() OVER(ORDER BY DataSrc.cfg_bs_user ASC)
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'cfg_bs_name'		THEN rank() OVER(ORDER BY DataSrc.cfg_bs_name DESC)
					WHEN 'cfg_bs_user'		THEN rank() OVER(ORDER BY DataSrc.cfg_bs_user DESC)
				END
			ELSE rank() OVER(ORDER BY cfg_bs_name ASC)
		END		
END
GO
-----------------------------------------------------


		
		
		
		
-----------------------------------------------------
--		
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.Roles]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.Roles]
GO			
	CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.Roles]
		@attributesXml XML,
		@filtersXml XML,
		@ordersXml XML
	AS
	BEGIN
		SET NOCOUNT ON;
		
		--ORDER INFORMATION
		DECLARE	@col_name nvarchar(50), @asc int
		EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
		
		SELECT 
			DataSrc.id,
			DataSrc.roles_acctype,
			DataSrc.roles_account,
			roles_number = 	CASE 
				WHEN DataSrc.roles_number = 4 THEN 3 -- Restore Operator
				ELSE DataSrc.roles_number
			END
			
		FROM
		(
		
			SELECT 
				ra.role_group_id as id,
				ro.number as  roles_number,
				ac.[type] as roles_acctype,
				ac.[name] as roles_account,
				ROW_NUMBER() OVER (partition by ra.role_group_id order by ro.number asc) as min_role 
			FROM 
					[dbo].[Security.RoleAccounts] ra 
					,[dbo].[Security.Accounts] ac 
					,[dbo].[Security.Roles] ro
			WHERE 
					ra.[account_id] = ac.[id] AND
					ra.[role_id] = ro.[id]
		) as DataSrc
		WHERE min_role = 1
		ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'roles_name'		THEN rank() OVER(ORDER BY DataSrc.roles_acctype ASC)
					WHEN 'roles_account'		THEN rank() OVER(ORDER BY DataSrc.roles_account ASC)
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'roles_name'		THEN rank() OVER(ORDER BY DataSrc.roles_acctype DESC)
					WHEN 'roles_account'		THEN rank() OVER(ORDER BY DataSrc.roles_account DESC)
				END
			ELSE rank() OVER(ORDER BY DataSrc.roles_account ASC)
		END		
	END
GO
-----------------------------------------------------
		
		

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.CollectSessions]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.CollectSessions]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.CollectSessions]
	@attributesXml XML,
	@filtersXml XML,
	@ordersXml XML
AS
BEGIN
	SET NOCOUNT ON;

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	DECLARE @sessions_stat TABLE
	(
		session_id uniqueidentifier,
		session_children_status int
	)
	INSERT INTO @sessions_stat
	SELECT
		s.[id],
		MAX (ss.[status])
	FROM
		[dbo].[Enterprise.Sessions] s
			LEFT JOIN [dbo].[Repl.SessionRelations] ON s.[id] = [dbo].[Repl.SessionRelations].[parent_session_id]
			LEFT JOIN [dbo].[Repl.ServerSessions] ss ON ss.[id] = [dbo].[Repl.SessionRelations].[server_session_id]
	GROUP BY
		s.[id],
		s.[status]

	
	SELECT
		id, 
		cfg_sessions_start_date,
		cfg_sessions_type,
		cfg_sessions_status,
		cfg_sessions_initiated_by,
		log,
		job_type
	FROM
	
			(SELECT [dbo].[Enterprise.Sessions].[id],
				[dbo].[Enterprise.Sessions].[start_date] as cfg_sessions_start_date,
				[dbo].[Enterprise.Sessions].[job_type] as cfg_sessions_type,
				CASE 
					WHEN stat.[session_children_status] IS NULL THEN [dbo].[Enterprise.Sessions].[status]
					WHEN stat.[session_children_status] < [dbo].[Enterprise.Sessions].[status] THEN [dbo].[Enterprise.Sessions].[status]
					ELSE stat.[session_children_status]
				END as cfg_sessions_status,
				CASE
				   WHEN [dbo].[Enterprise.Sessions].[request_type] = 0 THEN 'User'
				   WHEN [dbo].[Enterprise.Sessions].[request_type] = 1 THEN 'Scheduler'
				   WHEN [dbo].[Enterprise.Sessions].[request_type] = 2 THEN 'Job Activity'
				END as cfg_sessions_initiated_by,
				N'click here' as log,
				[dbo].[Enterprise.Sessions].[job_type]
			FROM [dbo].[Enterprise.Sessions], @sessions_stat stat
			WHERE [dbo].[Enterprise.Sessions].[id] = stat.[session_id]
			) as DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'cfg_sessions_start_date'	THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_start_date ASC)
					WHEN 'cfg_sessions_status'		THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_status ASC)
					WHEN 'cfg_sessions_initiated_by'THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_initiated_by ASC)
					WHEN 'cfg_sessions_type'		THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_type ASC)
				END
			WHEN 0 THEN
				CASE @col_name					
					WHEN 'cfg_sessions_start_date'	THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_start_date DESC)
					WHEN 'cfg_sessions_status'		THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_status DESC)
					WHEN 'cfg_sessions_initiated_by'THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_initiated_by DESC)
					WHEN 'cfg_sessions_type'		THEN rank() OVER(ORDER BY DataSrc.cfg_sessions_type DESC)
				END
			ELSE
				rank() OVER(ORDER BY DataSrc.cfg_sessions_start_date DESC)
		END
END
GO
-----------------------------------------------------



-----------------------------------------------------
--		
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.CollectServerSessions]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.CollectServerSessions]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.CollectServerSessions]
	@attributesXml XML,
	@filtersXml XML,
	@ordersXml XML
AS
BEGIN
	SET NOCOUNT ON;

	-- IDENTIFY COLLECT SESSION
	DECLARE @parent_session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @parent_session_id OUTPUT
	IF @parent_session_id is NULL
		RAISERROR(N'Main collect session unique identifier was not specified for the ''Server Sessions'' report', 9, 1 )

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 					
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
							
	SELECT *
	FROM
	
			(SELECT [dbo].[Repl.ServerSessions].[id],
				[dbo].[Repl.ServerSessions].[backup_server] as cfg_srv_sessions_backup_server,
				[dbo].[Repl.ServerSessions].[start_date] as cfg_srv_sessions_start_date,
				[dbo].[Repl.ServerSessions].[status] as cfg_srv_sessions_status
			FROM
				[dbo].[Repl.ServerSessions]
					INNER JOIN [dbo].[Repl.SessionRelations] ON [dbo].[Repl.ServerSessions].[id] = [dbo].[Repl.SessionRelations].[server_session_id]
			WHERE
				[dbo].[Repl.SessionRelations].[parent_session_id] = @parent_session_id) DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'cfg_srv_sessions_backup_server'	THEN rank() OVER(ORDER BY DataSrc.cfg_srv_sessions_backup_server ASC)
					WHEN 'cfg_srv_sessions_start_date'		THEN rank() OVER(ORDER BY DataSrc.cfg_srv_sessions_start_date ASC)
					WHEN 'cfg_srv_sessions_status'			THEN rank() OVER(ORDER BY DataSrc.cfg_srv_sessions_status ASC)
				END
			WHEN 0 THEN
				CASE @col_name					
					WHEN 'cfg_srv_sessions_backup_server'	THEN rank() OVER(ORDER BY DataSrc.cfg_srv_sessions_backup_server DESC)
					WHEN 'cfg_srv_sessions_start_date'		THEN rank() OVER(ORDER BY DataSrc.cfg_srv_sessions_start_date DESC)
					WHEN 'cfg_srv_sessions_status'			THEN rank() OVER(ORDER BY DataSrc.cfg_srv_sessions_status DESC)
				END
			ELSE
				rank() OVER(ORDER BY DataSrc.cfg_srv_sessions_start_date DESC)
		END
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Report.Shared.EnumEnumerations]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Report.Shared.EnumEnumerations]
GO						
CREATE PROCEDURE [dbo].[usp.Report.Shared.EnumEnumerations]	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT [dbo].[Report.Shared.Enumerations].[id],
		   [dbo].[Report.Shared.Enumerations].[unique_name]
	FROM [dbo].[Report.Shared.Enumerations]						 
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Report.Shared.EnumEnumerationValues]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.Report.Shared.EnumEnumerationValues]
END
GO										
CREATE PROCEDURE [dbo].[usp.Report.Shared.EnumEnumerationValues]
	
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	SELECT [dbo].[Report.Shared.EnumerationValues].[id],
		   [dbo].[Report.Shared.EnumerationValues].[enumeration_id],
		   [dbo].[Report.Shared.EnumerationValues].[text_value],
		   [dbo].[Report.Shared.EnumerationValues].[small_image_id],   
		   [dbo].[Report.Shared.EnumerationValues].[large_image_id],   
		   [dbo].[Report.Shared.EnumerationValues].[ordinal_number]
	FROM [dbo].[Report.Shared.EnumerationValues]
	
			
END
GO		
-----------------------------------------------------
			


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Report.Shared.RegisterQueryAttribute]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Report.Shared.RegisterQueryAttribute]
GO
CREATE PROCEDURE [dbo].[usp.Report.Shared.RegisterQueryAttribute]
	@builder_id as uniqueidentifier,						
	@name as nvarchar(100),
	@ordinal as int
AS
BEGIN	
	SET NOCOUNT ON;

	INSERT INTO [dbo].[Report.Shared.Builders.NaviName.QueryAttributes]( [id], [builder_id], [name], [ordinal] )
	VALUES( NEWID(), @builder_id, @name, @ordinal)
END
GO	
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Report.Shared.EnumNaviNameBuilders]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.Report.Shared.EnumNaviNameBuilders]
GO						
CREATE PROCEDURE [dbo].[usp.Report.Shared.EnumNaviNameBuilders]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT [dbo].[Report.Shared.Builders.NaviName].[id],
		   [dbo].[Report.Shared.Builders.NaviName].[format]
	FROM [dbo].[Report.Shared.Builders.NaviName]
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.Report.Shared.EnumQueryAttributes]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.Report.Shared.EnumQueryAttributes]
END
GO										
CREATE PROCEDURE [dbo].[usp.Report.Shared.EnumQueryAttributes]
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	SELECT [dbo].[Report.Shared.Builders.NaviName.QueryAttributes].[id],
		   [dbo].[Report.Shared.Builders.NaviName.QueryAttributes].[builder_id],
		   [dbo].[Report.Shared.Builders.NaviName.QueryAttributes].[name],
		   [dbo].[Report.Shared.Builders.NaviName.QueryAttributes].[ordinal]
	FROM 
		[dbo].[Report.Shared.Builders.NaviName.QueryAttributes]
	ORDER BY 
		[dbo].[Report.Shared.Builders.NaviName.QueryAttributes].[ordinal] ASC
			
END
GO				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.CreateNew]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.CreateNew]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.CreateNew]
	@session_id uniqueidentifier,
	@start_date datetime,
	@job_type int,
	@status int,
    @initiator_type int,
    @config nvarchar(max)                                                 	
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	INSERT INTO [dbo].[Enterprise.Sessions]( [id], [request_type], [start_date], [status], [details], [job_type], [job_spec])
	VALUES
	(
		@session_id,
	    @initiator_type,
	    @start_date, 
	    @status, 	     		
	    N'',
	    @job_type,
	    @config   
	)
			
END
GO				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.GetSessionConfig]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.GetSessionConfig]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.GetSessionConfig]
	@session_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	SELECT [job_spec] as 'config_value'
	FROM  [dbo].[Enterprise.Sessions]
	WHERE [id] = @session_id
			
END
GO				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.FireSessionCancelFlag]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.FireSessionCancelFlag]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.FireSessionCancelFlag]
	@session_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	UPDATE [dbo].[Enterprise.Sessions]
	SET [cancel_flag] = 1
	WHERE [id] = @session_id			
END
GO				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.EnterpriseSessions.IsSessionCanceled]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.IsSessionCanceled]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.IsSessionCanceled]
	@session_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	SELECT [cancel_flag] as N'cancel_flag'
	FROM [dbo].[Enterprise.Sessions]
	WHERE  [id] = @session_id
		
END
GO				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.EnterpriseSessions.IsSessionCompleted]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.IsSessionCompleted]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.IsSessionCompleted]
	@session_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	SELECT COUNT(*) as N'completed_flag'
	FROM [dbo].[Enterprise.Sessions]
	WHERE [id] = @session_id AND [status] <> 0	
		
END
GO				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.EnterpriseSessions.CompleteSession]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.CompleteSession]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.CompleteSession]
	@session_id uniqueidentifier,
	@session_status int
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	UPDATE [dbo].[Enterprise.Sessions]
	SET [status] = @session_status,
		[end_date] = GETUTCDATE()
	WHERE [id] = @session_id
	
END
GO				
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.EnterpriseSessions.AddSessionEvent]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.AddSessionEvent]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.AddSessionEvent]
	@session_id uniqueidentifier,
	@event_time datetime,
	@event_severity int,
	@event_text nvarchar(max),
	@ordinal_num int
AS
BEGIN	
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	INSERT INTO [dbo].[Enterprise.SessionEvents]( [session_id], [event_time], [severity], [text], [ordinal_num])
	VALUES
	(
	   @session_id,
	   @event_time,
	   @event_severity,
	   @event_text,
	   @ordinal_num
	)
			
END
GO				
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.EnterpriseSessions.Crawl.SureCrawlSessionExists]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.Crawl.SureCrawlSessionExists]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.Crawl.SureCrawlSessionExists]
	@ent_session_id uniqueidentifier,
	@search_srv_id uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;

	IF NOT EXISTS( SELECT * FROM [dbo].[Catalog.SearchServerCrawlSessions] WHERE [ent_session_id] = @ent_session_id AND [search_srv_id]=@search_srv_id)
	BEGIN
		INSERT INTO [dbo].[Catalog.SearchServerCrawlSessions]( [id], [ent_session_id], [search_srv_id] )
		VALUES( NEWID(), @ent_session_id, @search_srv_id )
	END
END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.EnterpriseSessions.Crawl.SetSearchSrvFailure]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.Crawl.SetSearchSrvFailure]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.Crawl.SetSearchSrvFailure]
	@ent_session_id uniqueidentifier,
	@search_srv_id uniqueidentifier,
	@error_text ntext
AS
BEGIN	
	
	EXEC [dbo].[usp.EnterpriseSessions.Crawl.SureCrawlSessionExists] @ent_session_id, @search_srv_id

	UPDATE [dbo].[Catalog.SearchServerCrawlSessions]
	SET [status] = 2, [status_comment] = @error_text
	WHERE [ent_session_id] = @ent_session_id AND [search_srv_id] = @search_srv_id
END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp.EnterpriseSessions.Crawl.UpdateSearchSrvCrawlStat]') AND type in (N'P'))
BEGIN
	DROP PROCEDURE [dbo].[usp.EnterpriseSessions.Crawl.UpdateSearchSrvCrawlStat]
END
GO										
CREATE PROCEDURE [dbo].[usp.EnterpriseSessions.Crawl.UpdateSearchSrvCrawlStat]
	@ent_session_id uniqueidentifier,
	@search_srv_id uniqueidentifier,
	@new_sources_num int,
    @changed_sources_num int,
    @deleted_sources_num int
AS
BEGIN	
	
	EXEC [dbo].[usp.EnterpriseSessions.Crawl.SureCrawlSessionExists] @ent_session_id, @search_srv_id

	UPDATE [dbo].[Catalog.SearchServerCrawlSessions]
	SET [changed_sources_num] = @changed_sources_num,
		[new_sources_num] = @new_sources_num,
		[deleted_sources_num] = @deleted_sources_num		
	WHERE [ent_session_id] = @ent_session_id AND [search_srv_id] = @search_srv_id
END
GO
-----------------------------------------------------

-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.SearchServers]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.SearchServers]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.SearchServers]
	@attributesXml XML,
	@filtersXml XML,
	@ordersXml XML
AS
BEGIN
	SET NOCOUNT ON;

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	--  Perform stored function
	SELECT *
	FROM
	(
		SELECT [dbo].[Catalog.SearchServers].[id],
				[dbo].[Catalog.SearchServers].[ip_or_dns_name] as cfg_search_srv_name,					   
				CASE 
					WHEN [dbo].[Catalog.SearchServerCreds].[login] IS NULL THEN SYSTEM_USER					   
					ELSE
						CASE 
							WHEN [dbo].[Catalog.SearchServerCreds].[domain] IS NULL THEN N''
							WHEN [dbo].[Catalog.SearchServerCreds].[domain] = N'' THEN N''
							ELSE [dbo].[Catalog.SearchServerCreds].[domain] + N'\'
						END + [dbo].[Catalog.SearchServerCreds].[login]
				END as cfg_search_srv_user,				
				CASE 
					WHEN EXISTS (SELECT * FROM [dbo].[Catalog.SearchServerDelRequests] WHERE [dbo].[Catalog.SearchServerDelRequests].[search_srv_id]=[dbo].[Catalog.SearchServers].[id]) THEN N'Removing'
					ELSE N'Active'
				END as cfg_search_srv_state,
				CAST( (100 * [dbo].[Catalog.SearchServers].[indexed_oibs_num] / oibs_index_limit) as nvarchar) + N'%' as cfg_search_srv_usage,
				[dbo].[Catalog.SearchServers].[description] as cfg_search_srv_desc
		FROM [dbo].[Catalog.SearchServers] LEFT JOIN [dbo].[Catalog.SearchServerCreds] 
			ON [dbo].[Catalog.SearchServers].[id] = [dbo].[Catalog.SearchServerCreds].[search_srv_id]
	) as DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'cfg_search_srv_name'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_name ASC)
					WHEN 'cfg_search_srv_user'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_user ASC)
					WHEN 'cfg_search_srv_state'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_state ASC)
					WHEN 'cfg_search_srv_usage'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_usage ASC)
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'cfg_search_srv_name'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_name DESC)
					WHEN 'cfg_search_srv_user'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_user DESC)
					WHEN 'cfg_search_srv_state'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_state DESC)
					WHEN 'cfg_search_srv_usage'		THEN rank() OVER(ORDER BY DataSrc.cfg_search_srv_usage DESC)
				END
			ELSE rank() OVER(ORDER BY cfg_search_srv_name ASC)
		END		
END
GO
-----------------------------------------------------





-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.LabRequests]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.LabRequests]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.LabRequests]
	@attributesXml XML =	N'<?xml version="1.0"?><attributes />',
	@filtersXml XML    =	N'<?xml version="1.0"?>
							<filters>
								<text_filter id="job_name"><value></value></text_filter>
								<dyn_scope_filter id="backup_srv">
									<low>00000000-0000-0000-0000-000000000000</low>
									<high>FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF</high>
								</dyn_scope_filter>
							</filters>',
	@ordersXml XML    =		N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>',
	@is_all_data bit  =     0 OUT
AS
BEGIN
SET NOCOUNT ON;

	DECLARE @filterStateLow uniqueidentifier, @filterStateHigh uniqueidentifier
	DECLARE @filterVmName nvarchar(100)

	--FILTERS
	DECLARE @docFilters int
	EXEC sp_xml_preparedocument @docFilters OUTPUT, @filtersXml
	
	EXEC [dbo].[usp.GridReport.Util.GetTextFilter] @docFilters, 'lr_vm_name', @filterVmName OUT
	EXEC [dbo].[usp.GridReport.Util.GetDynamicScopeFilter] @docFilters, 'lr_state', @filterStateLow OUT, @filterStateHigh OUT
					
	EXEC sp_xml_removedocument @docFilters

	declare @stateValues as table([id] uniqueidentifier, [state] nvarchar(255))
	insert @stateValues exec [dbo].[usp.GridReport.DynScopeFilter.EnumLabRequestsStates]
	
	declare @filterState nvarchar(250)
	select @filterState = state from @stateValues where id = @filterStateLow
	--

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
	--	

	select * 
	from
	(
		select
			lr.[id],
			lr.[user] cfg_labrequests_username,
			case
				when lrs_approved.id is not null then
					(select vmname from [dbo].[C.Backup.Model.OIBs] where 
						id = lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/OibId)[1]', 'uniqueidentifier'))
				else
					lr.[req_vmname]
			end cfg_labrequests_vm,
			case
				when lrs_approved.id is not null then
					lrs_approved.[description]
				else
					lr.[description]
			end cfg_labrequests_description,
			case
				when lrs_ready.id is not null then
					[dbo].[fn.Backup.Secs2HumanReadableFormat](lrs_ready.aux_data.value('(/CLabRequestStateAuxDataReady/ActDuration)[1]', 'int'))
				when lrs_approved.id is not null then
					[dbo].[fn.Backup.Secs2HumanReadableFormat](lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/ActualDuration)[1]', 'int'))
				else
					[dbo].[fn.Backup.Secs2HumanReadableFormat](lr.[preferred_duration_sec])
			end cfg_labrequests_duration,
			case
				when lrs_approved.id is not null then
					(select creation_time from [dbo].[C.Backup.Model.OIBs] where id = 						
								lrs_approved.aux_data.value('(/CLabRequestStateAuxDataApproved/OibId)[1]', 'uniqueidentifier')
					)
				else
					lr.[preferred_date]
			end
			cfg_labrequests_date,
			[dbo].[fn.LabRequests.LabRequestState2HumanReadableFormat](lrs_current.[state]) cfg_labrequests_state
		from
			[dbo].[Air.LabRequests] lr
		inner join
			[dbo].[Air.LabRequestStates] lrs_current
		on
			lr.state_id = lrs_current.id
		left outer join
			[dbo].[Air.LabRequestStates] lrs_approved
		on
			lrs_approved.id = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 2) --approved
		left outer join
			[dbo].[Air.LabRequestStates] lrs_ready
		on
			lrs_ready.id = [dbo].[fn.LabRequests.GetLabRequestLastStateId](lr.id, 4) --ready
		where
			lr.[req_vmname] LIKE '%' + @filterVmName + '%' and
			(@filterState is null or [dbo].[fn.LabRequests.IsLabRequestStateSatisfyFilter](lrs_current.[state], @filterState) = 1)
	) as DataSrc
	ORDER BY
	CASE @asc
		WHEN 1 THEN
			CASE @col_name
				WHEN 'cfg_labrequests_username'	   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_username ASC)
				WHEN 'cfg_labrequests_vm'		   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_vm ASC)
				WHEN 'cfg_labrequests_description' THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_description ASC)
				WHEN 'cfg_labrequests_duration'    THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_duration ASC)
				WHEN 'cfg_labrequests_date'		   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_date ASC)
				WHEN 'cfg_labrequests_state'	   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_state ASC)
			END
		WHEN 0 THEN 
			CASE @col_name
				WHEN 'cfg_labrequests_username'	   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_username DESC)
				WHEN 'cfg_labrequests_vm'		   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_vm DESC)
				WHEN 'cfg_labrequests_description' THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_description DESC)
				WHEN 'cfg_labrequests_duration'    THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_duration DESC)
				WHEN 'cfg_labrequests_date'		   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_date DESC)
				WHEN 'cfg_labrequests_state'	   THEN rank() OVER(ORDER BY DataSrc.cfg_labrequests_state DESC)
			END
		ELSE rank() OVER(ORDER BY cfg_labrequests_vm ASC)
	END	

END
GO
-----------------------------------------------------



-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.LabRequests.IsLabRequestStateSatisfyFilter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.LabRequests.IsLabRequestStateSatisfyFilter]
GO
CREATE FUNCTION [dbo].[fn.LabRequests.IsLabRequestStateSatisfyFilter]
(
	@state int,
	@filterState nvarchar(255)
)
RETURNS bit
AS
BEGIN
	
	if(@filterState='ALL') begin
		return 1
	end
	else begin
		if(@filterState='Active') begin
			if(@state = 0 or @state = 2 or @state = 2 or @state = 3 or @state = 4 or @state = 5 or @state = 7) --Pending = 0, Approved = 2, Preparing = 3, Ready = 4, Stopping  = 5, Failed = 7
			begin
				return 1
			end
		end
		else begin
			if(@filterState='Closed') begin
				if(@state = 1 or @state = 6) --Cancelled = 1, Stopped = 6
				begin
					return 1
				end
			end
		end
	end


	
	return 0
END
GO
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.LicensedHosts]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.LicensedHosts]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.LicensedHosts]
	@attributesXml XML,
	@filtersXml XML,
	@ordersXml XML
AS
BEGIN
	SET NOCOUNT ON;

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	--  Perform stored function
	SELECT *
	FROM
	(
		select 
			lh.name as host_name,
			lh.type as host_type,
			 CONVERT(UNIQUEIDENTIFIER, lh.uuid) as id,
			 lh.uuid as esx_uuid_string,
			 lh.cpu as host_cpu_num, 
			 CASE lh.is_licensed 
					WHEN 1 THEN 1
					ELSE 0
			END as esx_is_licensed
		from
			[dbo].[C.LicensedHosts] lh,
			(select id, ROW_NUMBER() over (PARTITION BY uuid ORDER BY is_licensed) rn from [dbo].[C.LicensedHosts]) lhp
		where lh.id = lhp.id and
			  lhp.rn = 1		
	) as DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'cfg_host_name'		THEN rank() OVER(ORDER BY DataSrc.host_name ASC)
					WHEN 'cfg_host_type'		THEN rank() OVER(ORDER BY DataSrc.host_type ASC)
					WHEN 'cfg_host_cpu_num'		        THEN rank() OVER(ORDER BY DataSrc.host_cpu_num ASC)
					WHEN 'cfg_esx_is_licensed'		THEN rank() OVER(ORDER BY DataSrc.esx_is_licensed ASC)					
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'cfg_host_name'		THEN rank() OVER(ORDER BY DataSrc.host_name DESC)
					WHEN 'cfg_host_type'		THEN rank() OVER(ORDER BY DataSrc.host_type DESC)
					WHEN 'cfg_host_cpu_num'		        THEN rank() OVER(ORDER BY DataSrc.host_cpu_num DESC)
					WHEN 'cfg_esx_is_licensed'		THEN rank() OVER(ORDER BY DataSrc.esx_is_licensed DESC)					
					
				END
			ELSE rank() OVER(ORDER BY DataSrc.host_name ASC)
		END		
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.Cfg.BJobsReferringHost]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.BJobsReferringHost]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.Cfg.BJobsReferringHost]
	@attributesXml XML,
	@filtersXml XML,
	@ordersXml XML
AS
BEGIN
	SET NOCOUNT ON;

	-- ESX UUID
	DECLARE @esx_uuid_string nvarchar( 255 )
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'esx_uuid_string', @esx_uuid_string OUTPUT				
	IF @esx_uuid_string is NULL
		RAISERROR(N'The unique identifier of the ESX host was not specified for the BJobsReferringHost report', 9, 1 )
		
	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

	--  Perform stored function
	SELECT *
	FROM
	(
		SELECT bj.id as id,
			   bs.display_name as bhost_name,
			   bj.[name] as bjob_name, 
			   CASE lh.[is_licensed] 
					WHEN 1 THEN 1
					ELSE   0
			   END as  esx_is_licensed
		from 
			[dbo].[Repl.Topology.BackupServers] bs,
			[dbo].[C.LicensedHosts] lh,
			[dbo].[C.HostsByJobs] hbj,
			[dbo].[C.BJobs] bj
		where 	
			lh.[uuid] = hbj.[uuid] AND
			lh.[db_instance_id] = hbj.[db_instance_id] AND
			bs.[current_db_id] = lh.[db_instance_id] AND
			hbj.[job_id] = bj.[id] AND
			lh.[uuid] = @esx_uuid_string	
							
	) as DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'cfg_bhost_name'		THEN rank() OVER(ORDER BY DataSrc.bhost_name ASC)
					WHEN 'cfg_bjob_name'		        THEN rank() OVER(ORDER BY DataSrc.bjob_name ASC)
					WHEN 'cfg_esx_is_licensed'		THEN rank() OVER(ORDER BY DataSrc.esx_is_licensed ASC)					
				END
			WHEN 0 THEN 
				CASE @col_name
					WHEN 'cfg_bhost_name'		THEN rank() OVER(ORDER BY DataSrc.bhost_name DESC)
					WHEN 'cfg_bjob_name'		        THEN rank() OVER(ORDER BY DataSrc.bjob_name DESC)
					WHEN 'cfg_esx_is_licensed'		THEN rank() OVER(ORDER BY DataSrc.esx_is_licensed DESC)
				END
			ELSE rank() OVER(ORDER BY DataSrc.bhost_name, DataSrc.bjob_name ASC)
		END		
END
GO
-----------------------------------------------------


-----------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn.Backup.Secs2HumanReadableFormat]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn.Backup.Secs2HumanReadableFormat]
GO
CREATE FUNCTION [dbo].[fn.Backup.Secs2HumanReadableFormat]
(
	@sec int
)
RETURNS nvarchar( 2000 )
AS
BEGIN
	DECLARE @retVal as nvarchar( 2000 )
	
	if @sec < 60
		set @sec = 60

	declare @days int
	set @days = @sec/(3600*24)

	declare @hours int
	set @hours = @sec%(3600*24)/3600

	declare @minutes int
	set @minutes = @sec%3600/60

	declare @strdays    nvarchar(255)
	declare @strhours   nvarchar(255)
	declare @strminutes nvarchar(255)

	select @strdays = case 
		when @days = 0 then ''
		when @days = 1 then '1 day'
		else convert(nvarchar(15),@days) + ' days'
		end
		
	select @strhours = case 
		when @hours = 0 then ''
		when @hours = 1 then '1 hour'
		else convert(nvarchar(15),@hours) + ' hours'
		end
		
	select @strminutes = case 
		when @minutes = 0 then ''
		when @minutes = 1 then '1 minute'
		else convert(nvarchar(15),@minutes) + ' minutes'
		end
			
	declare @strresult nvarchar(1000)	
	set @strresult = @strdays

	if @strhours <> ''
	begin
		if @strresult <> ''
			set @strresult = @strresult + ', '
		set @strresult = @strresult +  @strhours
	end

	if @strminutes <> ''
	begin
		if @strresult <> ''
			set @strresult = @strresult + ', '
		set @strresult = @strresult + @strminutes
	end
	
	set @retVal = @strresult;
	
	RETURN @retVal;
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.SbJobSessionsHeader]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.SbJobSessionsHeader]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.SbJobSessionsHeader]
		@attributesXml XML = N''
AS
BEGIN	
	SET NOCOUNT ON;

	DECLARE @utc_time_min datetime
	SET @utc_time_min = '09.09.1900 0:00:00'

	DECLARE @cur_session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @cur_session_id OUTPUT
	IF @cur_session_id is NULL
		RAISERROR(N'Session unique identifier was not specified', 9, 1 )

	SELECT 
		[dbo].[C.BJobs].[name] as job_name,
		sessions.[creation_time] as start_time,
		CASE
			WHEN sessions.[end_time] < @utc_time_min THEN null
			ELSE sessions.[end_time]
		END as end_time,
		sessions.[description] as session_desc
/*
		sessions.[processed_objects] as processed_objects,
		sessions.[total_objects] as total_objects,
		(SELECT COUNT(*) FROM [dbo].[C.Backup.Model.BackupTaskSessions] WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = sessions.[id] AND [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 2) as failed_count,
		(SELECT COUNT(*) FROM [dbo].[C.Backup.Model.BackupTaskSessions] WHERE [dbo].[C.Backup.Model.BackupTaskSessions].[session_id] = sessions.[id] AND [dbo].[C.Backup.Model.BackupTaskSessions].[status] = 1) as warning_count,
		sessions.[total_size] as total_size,
		sessions.[processed_size] as processed_size,
		sessions.[avg_speed] as avg_speed,
		*/
	FROM		
		[dbo].[C.BJobs], 
		[dbo].[C.Backup.Model.JobSessions] sessions
	WHERE
		[dbo].[C.BJobs].[id] = sessions.[job_id] AND
		sessions.[id] = @cur_session_id 
END
GO
-----------------------------------------------------



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.SbSessionDetails]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.SbSessionDetails]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.SbSessionDetails]
		@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="id" value="dc3e2619-e04e-4ac8-82a9-24ca7d3ef844"></attr></attributes>',
		@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
		@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'
AS
BEGIN	
	SET NOCOUNT ON;
			
	-- IDENTIFY SESSION
	DECLARE @session_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @session_id OUTPUT				
	IF @session_id is NULL
		RAISERROR(N'The session identifier was not supplied for the SessionDetails report', 9, 1 )

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
		
	SELECT * 
	FROM		
			(SELECT
					[dbo].[C.Backup.Model.SbTaskSessions].[id],
					[dbo].[C.Backup.Model.SbTaskSessions].[object_id] as vm_id,
					[dbo].[C.Backup.Model.SbTaskSessions].[object_name] as vm_name,
					[dbo].[C.Backup.Model.SbTaskSessions].[overall_status] as vm_processing_status,
					[dbo].[C.Backup.Model.SbTaskSessions].[start_time] as vm_processing_start_time,
					[dbo].[C.Backup.Model.SbTaskSessions].[finish_time] as vm_processing_end_time,
					vm_processing_details = 
					CASE 
						WHEN [dbo].[C.Backup.Model.JobSessions].[log_xml] IS NOT NULL
						THEN [dbo].[fn.Backup.GetSessionLogHtml](
							[dbo].[C.Backup.Model.JobSessions].[log_xml]
						)
						ELSE [dbo].[C.Backup.Model.JobSessions].[log_text]
					END,
					[dbo].[C.Backup.Model.SbTaskSessions].[test_script_status] as sb_test_status,
					[dbo].[C.Backup.Model.SbTaskSessions].[heartbeat_status] as sb_heartbeat_status,
					[dbo].[C.Backup.Model.SbTaskSessions].[ping_status] as sb_ping_status
			FROM 
				[dbo].[C.Backup.Model.JobSessions] 
				INNER JOIN [dbo].[C.Backup.Model.SbTaskSessions] ON [dbo].[C.Backup.Model.JobSessions].[id] = [dbo].[C.Backup.Model.SbTaskSessions].[drsession_id]
			WHERE  [dbo].[C.Backup.Model.JobSessions].[id] = @session_id) DataSrc
	ORDER BY 
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'vm_name'					THEN rank() OVER(ORDER BY DataSrc.vm_name ASC)
					WHEN 'vm_processing_start_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_start_time ASC)
					WHEN 'vm_processing_end_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_end_time ASC)
				END
			WHEN 0 THEN
				CASE @col_name					
					WHEN 'vm_name'					THEN rank() OVER(ORDER BY DataSrc.vm_name DESC)
					WHEN 'vm_processing_start_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_start_time DESC)
					WHEN 'vm_processing_end_time'	THEN rank() OVER(ORDER BY DataSrc.vm_processing_end_time DESC)
				END
			ELSE
				rank() OVER(ORDER BY DataSrc.vm_processing_start_time DESC)
		END

END		
GO
-----------------------------------------------------


-----------------------------------------------------
--		
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.SbJobsPerServer]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.SbJobsPerServer]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.SbJobsPerServer]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="srv_display_name" value="localhost" /><attr name="id" value="909c07f1-a07e-445e-9944-ff7b7b1ca8e0" /></attributes>',
	@filtersXml XML = N'<filters />',
	@ordersXml XML = N'<orders />'
AS
BEGIN
	SET NOCOUNT ON;

	-- IDENTIFY BACKUP SERVER
	DECLARE @backupsrv_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @backupsrv_id OUTPUT				
	IF @backupsrv_id is NULL
		RAISERROR(N'The backup server unique identifier was not specified for the JobsPerServer report', 9, 1 )
								
	-- EXIT IF DATA WERE NOT COLLECTED
	DECLARE @vb_db_id uniqueidentifier
	SELECT @vb_db_id = [dbo].[fn.BackupSrv.FindCurrentDbId]( @backupsrv_id )
	IF @vb_db_id IS NULL
	BEGIN
		DECLARE @reports_table TABLE
		(
			id uniqueidentifier,
			job_name nvarchar(255),			
			esx_host_name nvarchar(255),
			vlab_name nvarchar(255),
			job_cur_state int,
			last_result int,
			last_success datetime,			
			job_lastres_text nvarchar( 100 ),
			job_last_session_id uniqueidentifier,
			job_description nvarchar( 1024 )
		)
		SELECT * FROM @reports_table
		RETURN
	END


	-- ENUMERATE SESSIONS STATISTIC
DECLARE @latest_sessions_stat TABLE
(
	id uniqueidentifier,
	job_id uniqueidentifier,
	job_cur_state int,
	sess_state int
)
INSERT INTO @latest_sessions_stat( [id], [job_id], [job_cur_state], [sess_state] )
(
	SELECT 
			sbjs.[id],
			sbjs.[job_id],						   
			sbjs.[result],
			sbjs.[state]
	FROM
		(
			SELECT
				sbjss.job_id, 
				MAX(sbjss.creation_time)
			FROM [dbo].[view.Backup.SbSessions] sbjss
			WHERE
				sbjss.db_instance_id = @vb_db_id
			GROUP BY
				sbjss.job_id
		) sessions_times(job_id, creation_time) 
	INNER JOIN
		[dbo].[view.Backup.SbSessions] sbjs
	ON 
		sessions_times.[creation_time] = sbjs.[creation_time] AND 
		sessions_times.[job_id] = sbjs.[job_id]
	WHERE sbjs.[db_instance_id] = @vb_db_id
)

-- ENUMERATE LATEST COMPLETED SESSIONS
DECLARE  @latest_completed_sessions_times  TABLE
(
	job_id uniqueidentifier,
	end_time datetime
)
INSERT INTO @latest_completed_sessions_times( [job_id], [end_time] )
(
	SELECT [dbo].[view.Backup.SbSessions].[job_id], MAX( [dbo].[view.Backup.SbSessions].[end_time] )
	FROM [dbo].[view.Backup.SbSessions]
	WHERE [dbo].[view.Backup.SbSessions].[db_instance_id] = @vb_db_id AND
		  ( [dbo].[view.Backup.SbSessions].[result] >= 0 AND [dbo].[view.Backup.SbSessions].[result] <= 2 )
	GROUP BY [dbo].[view.Backup.SbSessions].[job_id]
)
DECLARE @latest_completed_sessions TABLE
(
	id uniqueidentifier,
	job_id uniqueidentifier,
	session_id uniqueidentifier,
	result  int,					
	result_text nvarchar( 50 ),
	creation_time datetime
)
INSERT INTO @latest_completed_sessions( [id], [job_id], [session_id], [result], [result_text], [creation_time] )
(
	SELECT 
			sbjs.id,
			sbjs.[job_id],
			sbjs.[id],
		   'result' =
			CASE 
			   WHEN sbjs.[result] = 0 THEN 0
			   WHEN sbjs.[result] = 1 THEN 1
			   WHEN sbjs.[result] = 2 THEN 2
		    END,
			'result_text' = 
			CASE						   
			  WHEN 	sbjs.[result] = 0	THEN 'Success'
			  WHEN 	sbjs.[result] = 1	THEN 'Warning'
			  WHEN 	sbjs.[result] = 2	THEN 'Failed'
			  ELSE N''
		    END,
		    sbjs.[creation_time]
	FROM
		@latest_completed_sessions_times sessions_times 
	INNER JOIN
		[dbo].[view.Backup.SbSessions] sbjs
	ON sessions_times.[end_time] = sbjs.[end_time] AND
	   sessions_times.[job_id] = sbjs.[job_id]
	WHERE
		sbjs.[db_instance_id] = @vb_db_id
)

-- ENUMERATE LATEST SUCCESSES
DECLARE @latest_succeeded_sessions_times TABLE
(
	job_id  uniqueidentifier,
	success_time datetime
)
INSERT INTO @latest_succeeded_sessions_times( [job_id], [success_time] )
(
	SELECT [dbo].[view.Backup.SbSessions].[job_id], MAX( [dbo].[view.Backup.SbSessions].[end_time])
	FROM [dbo].[view.Backup.SbSessions]	
	WHERE [dbo].[view.Backup.SbSessions].[db_instance_id] = @vb_db_id AND
		  -- STATUS Success or warning
		  ( [dbo].[view.Backup.SbSessions].[result] = 0 OR [dbo].[view.Backup.SbSessions].[result] = 1 ) 
	GROUP BY [dbo].[view.Backup.SbSessions].[job_id]
)				

	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT

-- FINALLY, SELECT DATA
SELECT * 
FROM

		(SELECT [dbo].[C.BJobs].[id],
			[dbo].[C.BJobs].[name] as job_name,
			[dbo].[C.Hosts].[name] as esx_host_name,
			[dbo].[C.VirtualLabs].[name] as vlab_name,
			CASE
			  WHEN latest_sessions.[job_cur_state] IS NULL THEN -1
  			  WHEN latest_sessions.[id] != latest_completed_sessions.[id] THEN latest_sessions.[sess_state]
			  ELSE latest_sessions.[job_cur_state]
			END as job_cur_state,
			CASE
				WHEN latest_completed_sessions.[result] IS NULL THEN -1
				ELSE latest_completed_sessions.[result]
			END as last_result,
			latest_completed_sessions.[session_id] as job_last_session_id,
			latest_successes.[success_time] as last_success,
			latest_completed_sessions.[creation_time] as session_creation_time,
			[dbo].[C.BJobs].[description] as job_description
			   
		FROM ((( [dbo].[C.BJobs] LEFT JOIN @latest_sessions_stat latest_sessions
				ON [dbo].[C.BJobs].[id] = latest_sessions.[job_id] )
				LEFT JOIN @latest_completed_sessions latest_completed_sessions
				ON [dbo].[C.BJobs].[id] = latest_completed_sessions.[job_id] )
			 LEFT JOIN @latest_succeeded_sessions_times latest_successes
			 ON [dbo].[C.BJobs].[id] = latest_successes.[job_id])
			INNER JOIN [dbo].[C.VirtualLabs] ON [dbo].[C.BJobs].[target_host_id] = [dbo].[C.VirtualLabs].[id]
			INNER JOIN [dbo].[C.Hosts] ON [dbo].[C.VirtualLabs].[host_id] = [dbo].[C.Hosts].[id]
		WHERE 
			[dbo].[C.BJobs].[db_instance_id] = @vb_db_id AND
			[dbo].[C.BJobs].[type] IN (3) -- SureBackup
	) DataSrc
	ORDER BY
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'job_name'			THEN rank() OVER(ORDER BY DataSrc.job_name ASC)
					WHEN 'esx_host_name'	THEN rank() OVER(ORDER BY DataSrc.esx_host_name ASC)
					WHEN 'vlab_name'		THEN rank() OVER(ORDER BY DataSrc.vlab_name ASC)
					WHEN 'job_cur_state'	THEN rank() OVER(ORDER BY DataSrc.job_cur_state ASC)
					WHEN 'last_result'		THEN rank() OVER(ORDER BY DataSrc.last_result ASC)
					WHEN 'last_success'		THEN rank() OVER(ORDER BY DataSrc.last_success ASC)
				END
			WHEN 0 THEN
				CASE @col_name
					WHEN 'job_name'			THEN rank() OVER(ORDER BY DataSrc.job_name DESC)
					WHEN 'esx_host_name'	THEN rank() OVER(ORDER BY DataSrc.esx_host_name DESC)
					WHEN 'vlab_name'		THEN rank() OVER(ORDER BY DataSrc.vlab_name DESC)
					WHEN 'job_cur_state'	THEN rank() OVER(ORDER BY DataSrc.job_cur_state DESC)
					WHEN 'last_result'		THEN rank() OVER(ORDER BY DataSrc.last_result DESC)
					WHEN 'last_success'		THEN rank() OVER(ORDER BY DataSrc.last_success DESC)
				END
			--ELSE
		END							

END	
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.DataSrc.SbSessionsPerJob]') AND type in (N'P'))
	DROP PROCEDURE [dbo].[usp.GridReport.DataSrc.SbSessionsPerJob]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.DataSrc.SbSessionsPerJob]
	@attributesXml XML = N'<?xml version="1.0"?><attributes><attr name="id" value="c23f7fda-8b54-4e92-a6e7-9d60660c9a37"></attr></attributes>',
	@filtersXml XML = N'<?xml version="1.0"?><filters><filter id="filter_name" value=""></filter><filter id="filter_backup_server" value=""></filter></filters>',
	@ordersXml XML = N'<orders><order id="name" sort="asc"></order><order id="description" sort="desc"></order></orders>'
AS
BEGIN
	SET NOCOUNT ON;

	-- IDENTIFY BACKUP SERVER
	DECLARE @job_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'id', @job_id OUTPUT				
	IF @job_id is NULL
		RAISERROR(N'The unique identifier of the SureBackup job was not specified for the SbSessionsPerJob report', 9, 1 )
								
	--ORDER INFORMATION
	DECLARE	@col_name nvarchar(50), @asc int
	EXEC [dbo].[usp.GridReport.Util.GetOrder] 
		@ordersXml, @col_name OUTPUT, @asc OUTPUT
		
	SELECT *
	FROM
	
			(SELECT [dbo].[view.Backup.SbSessions].[id],
				[dbo].[view.Backup.SbSessions].[creation_time] as job_start_time,
				[dbo].[view.Backup.SbSessions].[end_time] as job_end_time,
				[dbo].[view.Backup.SbSessions].[result] as job_status,
				CASE
					WHEN [dbo].[view.Backup.SbSessions].[result] = 0 THEN N'Success'
					WHEN [dbo].[view.Backup.SbSessions].[result] = 1 THEN N'Warning'
					WHEN [dbo].[view.Backup.SbSessions].[result] = 2 THEN N'Failed'
					WHEN [dbo].[view.Backup.SbSessions].[result] = 3 THEN N'Starting'
					WHEN [dbo].[view.Backup.SbSessions].[result] = 4 THEN N'Stopping'
					ELSE N'Running'
				END as job_status_text
				--[dbo].[C.BJobs].[name] as owner_job_name,
				   
			FROM [dbo].[C.BJobs] 
				INNER JOIN [dbo].[view.Backup.SbSessions] 
				ON [dbo].[C.BJobs].[id] = [dbo].[view.Backup.SbSessions].[job_id]
			WHERE [dbo].[C.BJobs].[id] = @job_id ) DataSrc
	ORDER BY 
		CASE @asc
			WHEN 1 THEN
				CASE @col_name
					WHEN 'job_start_time'	THEN rank() OVER(ORDER BY DataSrc.job_start_time ASC)
					WHEN 'job_end_time'		THEN rank() OVER(ORDER BY DataSrc.job_end_time ASC)
					WHEN 'job_result'		THEN rank() OVER(ORDER BY DataSrc.job_status ASC)
				END
			WHEN 0 THEN
				CASE @col_name					
					WHEN 'job_start_time'	THEN rank() OVER(ORDER BY DataSrc.job_start_time DESC)
					WHEN 'job_end_time'		THEN rank() OVER(ORDER BY DataSrc.job_end_time DESC)
					WHEN 'job_result'		THEN rank() OVER(ORDER BY DataSrc.job_status DESC)
				END
			ELSE 
				rank() OVER(ORDER BY DataSrc.job_start_time DESC)
		END
END
GO
-----------------------------------------------------
	
	
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.UnRegColWithBinding]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.UnRegColWithBinding]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.UnRegColWithBinding]
	@grid_report_id uniqueidentifier,
	@col_unique_name  nvarchar(100)
AS
BEGIN	
	SET NOCOUNT ON;
	
	-- uc
	
	DECLARE 
		@col_id uniqueidentifier,
		@icon_binding_id uniqueidentifier,
		@text_binding_id uniqueidentifier,
		@href_binding_id uniqueidentifier,
		@order_id uniqueidentifier
	
	SELECT 
		@col_id = [dbo].[Report.GridReport.Columns].[id],
		@icon_binding_id = [dbo].[Report.GridReport.Columns].[icon_binding_id],
		@text_binding_id = [dbo].[Report.GridReport.Columns].[text_binding_id],
		@href_binding_id = [dbo].[Report.GridReport.Columns].[href_binding_id],
		@order_id = [dbo].[Report.GridReport.Columns].[order_id]
	FROM [dbo].[Report.GridReport.Columns] 
	WHERE 
		[grid_report_id] = @grid_report_id AND
		[unique_name] = @col_unique_name
	
	DELETE FROM [dbo].[Report.GridReport.Binding.DirectTextColBindings]
	WHERE [dbo].[Report.GridReport.Binding.DirectTextColBindings].[id] = @text_binding_id
	
	DELETE FROM [dbo].[Report.GridReport.Binding.TextColBindings]
	WHERE [dbo].[Report.GridReport.Binding.TextColBindings].[id] = @text_binding_id
	
	DELETE FROM [dbo].[Report.GridReport.Binding.HrefColBindings]
	WHERE [dbo].[Report.GridReport.Binding.HrefColBindings].[id] = @href_binding_id
	
	DELETE FROM [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs]
	WHERE [dbo].[Report.GridReport.Binding.HrefColBindingDynAttrs].[href_binding_id] = @href_binding_id
	
	DELETE FROM [dbo].[Report.GridReport.Orders]
	WHERE [dbo].[Report.GridReport.Orders].[id] = @order_id

	DELETE FROM [dbo].[Report.GridReport.Columns]
	WHERE [dbo].[Report.GridReport.Columns].[id] = @col_id			
END
GO	
-----------------------------------------------------	



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.HrefResolveProc.Jobs_Name]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.HrefResolveProc.Jobs_Name]
GO			
CREATE PROCEDURE [dbo].[usp.GridReport.HrefResolveProc.Jobs_Name]
	@attributesXml XML
AS
BEGIN
	DECLARE 
		@cur_jobs_2_sessions_href_id as uniqueidentifier,
		@cur_jobs_2_sb_sessions_href_id as uniqueidentifier		
	SELECT 
		@cur_jobs_2_sessions_href_id = 'DDA425A3-B6C0-4cde-B7D9-D167DE0BD031',
		@cur_jobs_2_sb_sessions_href_id = 'E4874F3F-9D63-41c1-9601-4D8ECC70070A'
		
	-- IDENTIFY CUR JOB ID
	DECLARE @cur_job_id uniqueidentifier
	EXEC [dbo].[usp.GridReport.Util.FindUidAttribute] @attributesXml, N'job_id', @cur_job_id OUTPUT
	IF @cur_job_id is NULL
		RAISERROR(N'The current job unique identifier was not specified', 9, 1 )

	SELECT 
		CASE [dbo].[C.BJobs].[type]
			WHEN 3 THEN @cur_jobs_2_sb_sessions_href_id
			ELSE @cur_jobs_2_sessions_href_id
		END as href_id
	FROM [dbo].[C.BJobs]
	WHERE [dbo].[C.BJobs].[id] = @cur_job_id
END
GO
-----------------------------------------------------


-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.RegColWithDynamicHrefBinding]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.RegColWithDynamicHrefBinding]
GO													
CREATE PROCEDURE [dbo].[usp.GridReport.Config.RegColWithDynamicHrefBinding]
	@grid_report_id uniqueidentifier,
	@col_display_name nvarchar(100),
	@col_unique_name  nvarchar(100),
	@col_width  int,
	@col_number int,
	@is_sortable Bit,
	@dyn_href_binding_id uniqueidentifier,
	@datasrc_textcol_name nvarchar( 100 ),
	@datasrc_textcol_type int,
	@format int,
	@order_id uniqueidentifier = null	
AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @column_id as uniqueidentifier
	SELECT @column_id = NEWID()
		
	-- REGISTER TEXT BINDING
	DECLARE @text_binding_id as uniqueidentifier					
	EXEC [dbo].[usp.GridReport.Config.RegDirectTextColBinding] @datasrc_textcol_name, @datasrc_textcol_type, @text_binding_id OUTPUT, @format
																																	
	-- REGISTER  COLUMN
	INSERT INTO [dbo].[Report.GridReport.Columns]( [id], [grid_report_id], [display_name], [unique_name], [ordinal_number], [relative_width],[is_sortable], [href_binding_id], [text_binding_id], [order_id])
	VALUES( @column_id, @grid_report_id, @col_display_name, @col_unique_name, @col_number, @col_width, @is_sortable, @dyn_href_binding_id, @text_binding_id, @order_id )

END
GO	
-----------------------------------------------------




-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.UnRegHiddenColBinding]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.UnRegHiddenColBinding]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.UnRegHiddenColBinding]
	@binding_id uniqueidentifier
AS
BEGIN
		
	DELETE FROM [dbo].[Report.GridReport.Binding.HiddenColBindings]
	WHERE [id] = @binding_id
	
	DELETE FROM [dbo].[Report.GridReport.Binding.DirectTextColBindings]
	WHERE [id] = @binding_id
	
END
GO			
-----------------------------------------------------
  
-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.UnRegHiddenColWithBinding]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.UnRegHiddenColWithBinding]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.UnRegHiddenColWithBinding]
	@grid_report_id uniqueidentifier,
	@col_unique_name  nvarchar(100)
AS
BEGIN	
	SET NOCOUNT ON;
	
		DECLARE 
		@col_id uniqueidentifier,
		@binding_id uniqueidentifier
	
	SELECT 
		@col_id = [dbo].[Report.GridReport.HiddenColumns].[id],
		@binding_id = [dbo].[Report.GridReport.HiddenColumns].[binding_id]
	FROM [dbo].[Report.GridReport.HiddenColumns] 
	WHERE 
		[grid_report_id] = @grid_report_id AND
		[unique_name] = @col_unique_name
	
	EXEC [dbo].[usp.GridReport.Config.UnRegHiddenColBinding] @binding_id
	
	DELETE FROM [dbo].[Report.GridReport.HiddenColumns]
	WHERE [dbo].[Report.GridReport.HiddenColumns].[id] = @col_id
END
GO	
-----------------------------------------------------	



-----------------------------------------------------
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp.GridReport.Config.FixVmsReport]') AND type in (N'P'))
DROP PROCEDURE [dbo].[usp.GridReport.Config.FixVmsReport]
GO
CREATE PROCEDURE [dbo].[usp.GridReport.Config.FixVmsReport]	
AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @grid_report_id uniqueidentifier
	SELECT @grid_report_id = id
	FROM [dbo].[Report.GridReports]
	WHERE report_id = 'A672891E-C13C-481a-A7AE-C22E54E1D4E2' --VmInBackups
	
	DECLARE @col_unique_name  nvarchar(100)
	SET @col_unique_name = 'vapp_name'
	
	DECLARE @vcdInstanceRootsCount int
	SELECT @vcdInstanceRootsCount = ISNULL(Count(*), 0) FROM [dbo].[C.Hosts] hosts WHERE hosts.type = 14 --VCD
		AND [dbo].[fn.IsRootVisibleHost](hosts.id)=1
	
	DECLARE @visibleVAppColExists bit
	IF EXISTS (SELECT id FROM [dbo].[Report.GridReport.Columns] WHERE grid_report_id = @grid_report_id AND unique_name = @col_unique_name)
		BEGIN SET @visibleVAppColExists = 1 END
	ELSE 
		BEGIN SET @visibleVAppColExists = 0 END
	/*
	DECLARE @hiddenVAppColExists bit
	IF EXISTS (SELECT id FROM [dbo].[Report.GridReport.HiddenColumns] WHERE grid_report_id = @grid_report_id AND unique_name = @col_unique_name)
		BEGIN SET @hiddenVAppColExists = 1 END
	ELSE 
		BEGIN SET @hiddenVAppColExists = 0 END
	*/
		
	IF @vcdInstanceRootsCount > 0
	BEGIN
		-- Register visible column 'vApp'
		IF (@visibleVAppColExists = 0)
		BEGIN
			EXEC  [dbo].[usp.GridReport.Config.RegTextColWithDirectBinding]					
				@grid_report_id = @grid_report_id,
				@col_display_name = N'vApp',
				@col_unique_name = N'vapp_name',
				@col_width = 70,
				@col_number = 1,
				@is_sortable = 1,
				@datasrc_col_name = N'vapp_name',
				@datasrc_col_type = 12,
				@format = 0--,--text
				--@order_id = @order_backup_server_name		
				print 'Column ''vApp'' has been registered.'
		END
		/*
		IF (@hiddenVAppColExists = 1)
		BEGIN
			EXeC [dbo].[usp.GridReport.Config.UnRegHiddenColWithBinding] @grid_report_id, @col_unique_name
		END*/
		
	END
	ELSE
	BEGIN
		-- Unregister visible column 'vApp'
	
		IF (@visibleVAppColExists = 1)
		BEGIN
			EXEC [dbo].[usp.GridReport.Config.UnRegColWithBinding] @grid_report_id, @col_unique_name
			
			print 'Column ''vApp'' has been unregistered.'
		END
		/*
		IF (@hiddenVAppColExists = 0)
		BEGIN			
		END*/
	END	
	
END
GO	
-----------------------------------------------------		





















