USE [CardioLog]
GO

/****** Object:  UserDefinedFunction [dbo].[func_control_get_path]    Script Date: 25-Mar-14 10:57:46 AM ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_control_get_path]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[func_control_get_path]
GO
/****** Object:  UserDefinedFunction [dbo].[func_control_get_path]    Script Date: 25-Mar-14 10:57:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_control_get_path]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[func_control_get_path]
(
	@EntityId uniqueidentifier
)
RETURNS varchar(1000)
AS
BEGIN
	
declare @path nvarchar(1000)
declare @ParentId uniqueidentifier
declare @currId uniqueidentifier
declare @name varchar(255)
declare @first smallint

set @first = 1
set @currId = (select EntityId from tab_virtual_tree where EntityId = @entityId)
set @ParentId = (select ParentId from tab_virtual_tree where EntityId = @currId)
set @path = ''''

while (@ParentId <> ''00000000-0000-0000-0000-000000000000'')
begin 
	set @name = (select name from tab_virtual_tree where EntityId = @currId)
	if (@first = 1)
	begin
		set @path = @name + @path
		set @first = 0
	end
	else
		set @path = @name + '' > '' + @path
	set @currId = @ParentId
	set @ParentId = (select ParentId from tab_virtual_tree where EntityId = @currId)
end
set @path = ''Reports > '' + @path


RETURN @path

END

' 
END

GO


--========================================
-- Get Reports List
--========================================
IF object_id('tempdb..#allReports') IS NOT NULL
BEGIN
   DROP TABLE #allReports
END

IF object_id('tempdb..#allReportsOwners') IS NOT NULL
BEGIN
   DROP TABLE #allReportsOwners
END

IF object_id('tempdb..#allReportsFilters') IS NOT NULL
BEGIN
   DROP TABLE #allReportsFilters
END

IF object_id('tempdb..#scheduledReports') IS NOT NULL
BEGIN
   DROP TABLE #scheduledReports
END

CREATE TABLE #allReports(
entityId uniqueidentifier,
location varchar(500),
[path] nvarchar(max),
name nvarchar(1000),
reportType varchar(20),
owners nvarchar(max),
timeframe nvarchar(max),
timeinterval nvarchar(max),
treeItemsIds nvarchar(max),
numOfWidgets int,
treeItemsUrls nvarchar(max),
treeItemsByUrls nvarchar(max),
timeFrameTitle nvarchar(max)
)

CREATE TABLE #allReportsOwners(
entityId uniqueidentifier,
owners nvarchar(max)
)

CREATE TABLE #allReportsFilters(
entityId uniqueidentifier,
timeframe nvarchar(max),
timeinterval nvarchar(max),
treeItemsIds nvarchar(max),
treeItemsByUrls nvarchar(max),
timeFrameTitle nvarchar(max)
)

declare @error varchar (255)

insert into #allReports
select distinct EntityId, location, '', name, '', '', '', '', '', 0, '', '', ''
from tab_virtual_tree
where username = 'CARDIOLOG_REPORTS'
and EntityType <> 5
order by Location

set @error = '#allReports table populated'
	raiserror (@error,10,1) with nowait

insert into #allReportsOwners
select a.entityId, STUFF(
		(select ',' + p.connectedAs
        from tab_virtual_tree_permissions p
		where p.[select] = 1 and p.[update] = 1 and p.[delete] = 1 and p.[insert] = 1
		and p.entityId = a.entityId
        FOR XML PATH('')), 1, 1, '')  AS [owners]
from #allReports a
	join tab_virtual_tree_permissions p on a.entityId = p.entityId
group by a.entityId

set @error = '#allReportsOwners table populated'
	raiserror (@error,10,1) with nowait

-- remove starting commas from user names list
update #allReportsOwners
set owners = case when LEFT(owners, 1) = ',' then Right(owners, LEN(owners) - 1) else owners end

insert #allReportsFilters
select a.entityId,
	case when (PATINDEX(N'%<timeframe>%',prefs) = 0	OR PATINDEX(N'%</timeframe>%',prefs) = 0 )					then ''		else (substring(prefs,			PATINDEX(N'%<timeframe>%',prefs) + len(N'<timeframe>'),				PATINDEX(N'%</timeframe>%',prefs) - PATINDEX(N'%<timeframe>%',prefs) - len(N'<timeframe>')))					end [timeframe],
	case when (PATINDEX(N'%<timeinterval>%',prefs) = 0	OR PATINDEX(N'%</timeinterval>%',prefs) = 0 )			then ''		else (substring(prefs,			PATINDEX(N'%<timeinterval>%',prefs) + len(N'<timeinterval>'),		PATINDEX(N'%</timeinterval>%',prefs) - PATINDEX(N'%<timeinterval>%',prefs) - len(N'<timeinterval>')))			end [timeinterval],
	case when (PATINDEX(N'%<entityId>%',prefs) = 0	OR PATINDEX(N'%</entityId>%',prefs) = 0 )					then ''		else (replace(substring(prefs,	PATINDEX(N'%<entityId>%',prefs) + len(N'<entityId>'),				PATINDEX(N'%</entityId>%',prefs) - PATINDEX(N'%<entityId>%',prefs) - len(N'<entityId>')),N'0:',''))				end [treeItemsIds],
	case when (PATINDEX(N'%<entityUrl>%',prefs) = 0	OR PATINDEX(N'%</entityUrl>%',prefs) = 0 )					then ''		else (substring(prefs,			PATINDEX(N'%<entityUrl>%',prefs) + len(N'<entityUrl>'),				PATINDEX(N'%</entityUrl>%',prefs) - PATINDEX(N'%<entityUrl>%',prefs) - len(N'<entityUrl>')))					end [treeItemsByUrls],
	case when (PATINDEX(N'%<timeframe_TITLE>%',prefs) = 0	OR PATINDEX(N'%</timeframe_TITLE>%',prefs) = 0 )	then ''		else (substring(prefs,			PATINDEX(N'%<timeframe_TITLE>%',prefs) + len(N'<timeframe_TITLE>'),	PATINDEX(N'%</timeframe_TITLE>%',prefs) - PATINDEX(N'%<timeframe_TITLE>%',prefs) - len(N'<timeframe_TITLE>')))	end [timeFrameTitle]
from #allReports a
	join tab_page_controls pc on a.entityId = pc.EntityId

set @error = '#allReportsFilters table populated'
	raiserror (@error,10,1) with nowait

-- set date range filters
update a
set
a.timeframe = f.timeframe, a.timeinterval = f.timeinterval, a.timeFrameTitle = f.timeFrameTitle
from #allReports a
	join #allReportsFilters f on a.entityId = f.entityId

set @error = 'Updated date range filters'
	raiserror (@error,10,1) with nowait

-- set owners
update a
set a.owners = o.owners
from #allReports a
	join #allReportsOwners o on a.entityId = o.entityId

set @error = 'Updated reports owners'
	raiserror (@error,10,1) with nowait

-- set tree items filter
update a
set a.treeItemsIds = f.treeItemsIds, a.treeItemsByUrls = f.treeItemsByUrls
from #allReports a
	join #allReportsFilters f on a.entityId = f.entityId

set @error = 'Updated tree items filters'
	raiserror (@error,10,1) with nowait

-- set number of widgets
update a
set a.numOfWidgets = (select COUNT(*) from tab_page_controls pc where a.entityId = pc.EntityId)
from #allReports a

set @error = 'Updated number of widgets'
	raiserror (@error,10,1) with nowait

-- set report type
update a
set a.reportType = CASE WHEN rs.[type] = 0 THEN 'Daily' WHEN rs.[type] = 1 THEN 'Weekly' WHEN rs.[type] = 2 THEN 'Monthly' ELSE 'Ad-Hoc' END
from #allReports a
	join tab_reports_scheduler rs on a.entityId = rs.EntityId

set @error = 'Updated reports types'
	raiserror (@error,10,1) with nowait

-- go over each treeItemsIds and get url
DECLARE @treeItemsIds varchar(max)
DECLARE ids_cursor CURSOR FOR
select treeItemsIds from #allReports
where treeItemsIds <> 'static' and treeItemsIds <> 'customize'
OPEN ids_cursor;

FETCH NEXT FROM ids_cursor
INTO @treeItemsIds;

WHILE @@FETCH_STATUS = 0
BEGIN
update a
set a.treeItemsUrls = (
select data.url + ',' as 'data()' 
from (SELECT tr.url FROM [dbo].fnsplit ( @treeItemsIds, ',')D
  join tab_sharepoint_tree tr on CONVERT(uniqueidentifier,D.item) = tr.ID) data
for xml path('')
)
from #allReports a
where a.treeItemsIds = @treeItemsIds



FETCH NEXT FROM ids_cursor 
INTO @treeItemsIds;
END;

CLOSE ids_cursor;
DEALLOCATE ids_cursor;

set @error = 'Updated tree items url'
	raiserror (@error,10,1) with nowait

-- remove ending commas from urls list
update #allReports
set treeItemsUrls = case when RIGHT(treeItemsUrls, 1) = ',' then LEFT(treeItemsUrls, LEN(treeItemsUrls) - 1) else treeItemsUrls end

-- update reports paths
update #allReports
set [path] = [dbo].[func_control_get_path] (EntityId)

set @error = 'Updated reports paths'
	raiserror (@error,10,1) with nowait

-- final select
select 
CASE WHEN ISNULL(ar.name,'') = '' then '(Not set)' else ar.name end as [Name],
--ar.location as [Tree Level],
ISNULL(ar.path,N'(Path Could not be determined)') [path],
--ar.entityId [entityId],
ISNULL(ar.numOfWidgets,0) as [numOfWidgets],
CASE WHEN ISNULL(ar.reportType,'') = '' THEN 'Ad-Hoc' else ar.reportType end AS [type],
CASE WHEN ISNULL(ar.owners,'') = '' THEN '(Not set)' ELSE ar.owners end [Owner(s)],
CASE WHEN ar.treeItemsUrls is null THEN '(No widgets)' WHEN ar.treeItemsUrls = 'http://Root/' THEN 'All' when ar.treeItemsUrls = '' then ar.treeItemsByUrls else ar.treeItemsUrls end as [treeItemsUrls],
CASE 
	WHEN ar.timeframe = '600000000' and ar.timeinterval = '600000000' then 'This Hour'
	WHEN ar.timeframe = '-600000000' and ar.timeinterval = '600000000' then 'Last Hour'
	WHEN ar.timeframe = '36000000000' and ar.timeinterval = '36000000000' then 'This Day'
	WHEN ar.timeframe = '-36000000000' and ar.timeinterval = '36000000000' then 'Last Day'
	when ar.timeframe = '864000000000' and ar.timeinterval = '864000000000' then 'This Week'
	when ar.timeframe = '-864000000000' and ar.timeinterval = '864000000000' then 'Last Week'
	when ar.timeframe = '6048000000000' and ar.timeinterval = '6048000000000' then 'This Month'
	when ar.timeframe = '-6048000000000' and ar.timeinterval = '6048000000000' then 'Last Month'
	when ar.timeframe = '25920000000000' and ar.timeinterval = '25920000000000' then 'This Quarter'
	when ar.timeframe = '-25920000000000' and ar.timeinterval = '25920000000000' then 'Last Quarter'
	when ar.timeframe = '77760000000000' and ar.timeinterval = '77760000000000' then 'This Year'
	when ar.timeframe = '-77760000000000' and ar.timeinterval = '77760000000000' then 'Last Year'
	when isnull(ar.timeFrameTitle,'') <> '' then ar.timeFrameTitle
	else '(Not set)' end [Date Range]
from #allReports ar
order by ar.location
GO