DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10236
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Total DataCube] bigint,[Total DataCubeDocs] bigint,[Total DataCubeUsers] bigint,[Total FileArchiving] bigint,[Total GDPR] bigint,[Total GDPR Docs] bigint,[Total GDPR Users] bigint,[CaseManagerTotal] bigint,[DataCube FILE] int,[DataCube LDAP] int,[DataCube CSV] int,[DataCube WEB] int,[DataCube JDBC] int,[DataCube HTTP] int,[DataCube BLANK] int,[DataCube SALESFORCE] int,[Total DataCube File Size] bigint,[Total GDPR File Size] bigint,[GDPR FILE] int,[GDPR LDAP] int,[GDPR JDBC] int,[Case Manager Custodian] int,[Case Manager Total Emails ] bigint,[Case Manager Total Size] bigint
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; ---Activate summary
DECLARE @CS_SPVERSION  INT = 0
SELECT Top 1 @CS_SPVERSION = ISNULL(SIM.HighestSP,0) FROM simInstalledPackages SIM
WHERE sim.ClientId = 2 AND Sim.simPackageID = 20 /*commserve package*/ 

IF(@CS_SPVERSION < 11) RETURN -- This report is for V11 SP11 and above

IF OBJECT_ID(''tempdb.dbo.#tempgGlobalDSInfos'') IS NOT NULL
	DROP TABLE #tempgGlobalDSInfos

CREATE TABLE #tempgGlobalDSInfos	
	(	DatasourceId int PRIMARY KEY, 
		DatasourceName NVARCHAR(1024),
		TotalCount BIGINT, 
		LastSyncTime BIGINT, 
		LastModified BIGINT,
		IndexDirectorySizeInBytes BIGINT, 
		FSSizeAnalyzedSourceCount BIGINT,
		UsersCount INT,
		DataSourceType VARCHAR(100), 
		AppName VARCHAR(20));

--retrieve all datasources
INSERT INTO #tempgGlobalDSInfos(DatasourceId, DatasourceName, DataSourceType, AppName)
SELECT SE.DataSourceId, SE.DataSourceName, UPPER(DST.TypeDisplayName), ''DataCube'' FROM SEDataSource SE WITH (NOLOCK)
INNER JOIN SEDataSourceType AS DST ON SE.DataSourceType = DST.TypeId AND SE.Attribute & 4 != 4

--fetch the total docs, index directory size, 
;WITH CTE(datasourceId,statusInfo) as 
(
	SELECT T.datasourceId, CONVERT(XML,T.PropertyValue)
	FROM SEDataSourceProperty as T WITH (NOLOCK)
		INNER JOIN SEProperty AS P ON T.PropertyId = P.PropertyId AND P.PropertyName = ''statusinfo''
	WHERE T.PropertyValue is not null AND T.PropertyValue != ''''
)
UPDATE tGDS SET 
	TotalCount = ISNULL(m.c.value(''@TotalDocs'',''BIGINT''),0),
	LastModified = ISNULL(m.c.value(''@LastModified'',''BIGINT''),0)/1000,
	IndexDirectorySizeInBytes = ISNULL(m.c.value(''@TotalSizeInBytes'',''BIGINT''),0),
	FSSizeAnalyzedSourceCount = ISNULL(m.c.value(''(TypeStatusInfo/FileInfo/@SizeAnalyzed)[1]'',''BIGINT''),0),
	UsersCount = ISNULL(m.c.value(''(TypeStatusInfo/FileInfo/@UsersCount)[1]'',''INT''),0) 		
FROM #tempgGlobalDSInfos tGDS INNER JOIN  CTE ON CTE.datasourceId = tGDS.DatasourceId outer apply CTE.statusInfo.nodes(''/StatusInfo'') as m(c)

--update gdpr flag by property
UPDATE tGDS SET
	AppName = ''GDPR''
FROM #tempgGlobalDSInfos AS tGDS 
	INNER JOIN  SEDataSourceProperty DSP WITH (NOLOCK)	ON tGDS .DatasourceId = DSP.DataSourceId	
	INNER JOIN SEProperty SP WITH (NOLOCK) 
		ON SP.PropertyGuid = ''4526224E-BB52-4870-84D1-21D12FDC41AB'' /*appname*/ AND 
		DSP.PropertyId = SP.PropertyId AND DSP.PropertyValue = ''GDPR''

--update gdpr flag by property
UPDATE tGDS SET
	AppName = ''GDPR''
FROM #tempgGlobalDSInfos AS tGDS 
	INNER JOIN  SEDataSourceProperty DSP WITH (NOLOCK)	ON tGDS .DatasourceId = DSP.DataSourceId	
	INNER JOIN SEProperty SP WITH (NOLOCK) 
		ON SP.PropertyGuid = ''d215e7ec-014c-4e98-a22d-9c1f384abeda'' /*ediscoveryproperty*/ AND 
		DSP.PropertyId = SP.PropertyId AND DSP.PropertyValue IS NOT NULL

--GDPR by name
UPDATE tGDS SET
	AppName = ''GDPR''
FROM #tempgGlobalDSInfos AS tGDS
WHERE datasourcename like ''eDiscovery%_%'' COLLATE SQL_Latin1_General_CP1_CS_AS AND tGDS.AppName = ''DataCube''


--GDPR by client prop

UPDATE tGDS SET 
	AppName = ''GDPR''
FROM #tempgGlobalDSInfos as tGDS INNER JOIN
	App_ClientProp AC WITH (NOLOCK)  ON AC.attrVal = tGDS.DatasourceId
where AC.attrName = ''Federated Datasource Id'' and AC.modified = 0

UPDATE #tempgGlobalDSInfos  SET 
	AppName = ''GDPR''
WHERE DatasourceName = ''EDiscoveryGlobal_federated''

--set File archiving source
UPDATE tGDS SET
	AppName = ''File Archiving''
FROM #tempgGlobalDSInfos AS tGDS 
	INNER JOIN APP_ClientProp cp WITH (NOLOCK) ON CP.attrVal = tGDS.DatasourceId AND cp.attrname =''indexing datasource id''
	WHERE tGDS.AppName = ''DataCube''

DECLARE @TotalDataCube BIGINT = 0
DECLARE @TotalDataCubeDocs BIGINT = 0
DECLARE @TotalDataCubeUsersCount BIGINT = 0
DECLARE @TotalFilrArchiving BIGINT = 0
DECLARE @TotalFilrArchivingDocs BIGINT = 0
DECLARE @TotalFileArchivingUsersCount BIGINT = 0
DECLARE @TotalGDPR BIGINT = 0
DECLARE @TotalGDPRDocs BIGINT = 0
DECLARE @TotalGDPRUsersCount BIGINT = 0
DECLARE @FSSizeAnalyzedDatCube BIGINT = 0
DECLARE @FSSizeAnalyzedArchiving BIGINT = 0
DECLARE @FSSizeAnalyzedGDPR BIGINT = 0
--overview
SELECT @TotalDataCube =  ISNULL(COUNT(*), 0), @TotalDataCubeDocs = ISNULL(SUM(TotalCount), 0),@TotalDataCubeUsersCount = ISNULL(SUM(UsersCount),0), @FSSizeAnalyzedDatCube= ISNULL(SUM(FSSizeAnalyzedSourceCount),0) FROM #tempgGlobalDSInfos WHERE AppName = ''DataCube'' 
SELECT @TotalFilrArchiving =  ISNULL(COUNT(*), 0), @TotalFilrArchivingDocs = ISNULL(SUM(TotalCount), 0), @TotalFileArchivingUsersCount  = ISNULL(SUM(UsersCount),0), @FSSizeAnalyzedArchiving= ISNULL(SUM(FSSizeAnalyzedSourceCount),0) FROM #tempgGlobalDSInfos WHERE AppName = ''File Archiving''
SELECT @TotalGDPR =  ISNULL(COUNT(*), 0), @TotalGDPRDocs = ISNULL(SUM(TotalCount), 0),  @TotalGDPRUsersCount  = ISNULL(SUM(UsersCount),0), @FSSizeAnalyzedGDPR= ISNULL(SUM(FSSizeAnalyzedSourceCount),0)  FROM #tempgGlobalDSInfos WHERE AppName = ''GDPR'' 


DECLARE @3MonthsOld BIGINT = datediff(second, ''01/01/1970'', GETDATE()-90)

-- mark active if last modified date is updated within 90 days.
--
DECLARE @tmpAct table (name varchar(100), srcType varchar(100), srcCount BIGINT, modified BIGINT, total BIGINT, IdxSize BIGINT, FileAnlSize BIGINT, TotalUsersCount INT)
INSERT INTO @tmpAct
SELECT T.AppName, T.DataSourceType, COUNT(T.DataSourceID) , 		
		SUM(CASE WHEN T.LastModified > @3MonthsOld THEN 1 ELSE 0 END) , 
		ISNULL(SUM(T.TotalCount),0), 
		ISNULL(SUM(T.IndexDirectorySizeInBytes),0) ,		
		ISNULL(SUM(T.FSSizeAnalyzedSourceCount),0) ,
		ISNULL(SUM(T.UsersCount),0)
FROM #tempgGlobalDSInfos T GROUP BY T.AppName, T.DataSourceType
HAVING ISNULL(SUM(T.TotalCount),0) > 1
ORDER BY T.AppName, ISNULL(SUM(T.TotalCount),0) DESC

DECLARE @DCFile INT = 0
DECLARE @DCLdap INT = 0
DECLARE @DCCsv INT = 0
DECLARE @DCWeb INT = 0
DECLARE @DCJdbc INT = 0
DECLARE @DCHttp INT = 0
DECLARE @DCBlank INT = 0
DECLARE @DCSalesForce INT = 0

DECLARE @GDPRFile INT = 0
DECLARE @GDPRLdap INT = 0
DECLARE @GDPRJdbc INT = 0

select @DCFile = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''FILE'' 
select @DCCsv = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''CSV''
select @DCLdap = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''LDAP''
select @DCWeb = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''WEB''
select @DCJdbc = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''JDBC''
select @DCHttp = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''HTTP''
select @DCBlank = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''BLANK''
select @DCSalesForce = ISNULL(srcCount, 0) from @tmpAct where name = ''DataCube'' AND srcType = ''SALESFORCE''

select @GDPRFile = ISNULL(srcCount, 0) from @tmpAct where name = ''GDPR'' AND srcType = ''FILE''
select @GDPRLdap = ISNULL(srcCount, 0) from @tmpAct where name = ''GDPR'' AND srcType = ''LDAP''
select @GDPRJdbc = ISNULL(srcCount, 0) from @tmpAct where name = ''GDPR'' AND srcType = ''JDBC''

--Case Manager Total

DECLARE @cmClients Table (id INT)
DECLARE @CaseMgrNewTotal BIGINT = 0, @CaseMgrTotalEmails BIGINT =0, @CaseMgrTotalSize BIGINT =0 
DECLARE @CaseMgrCustodianCount INT =0 
INSERT INTO @cmClients  select distinct refcopyclientid from DM2CaseInfo (NOLOCK) where ISNull(RefCopyClientId, 0) > 0 
IF (@CS_SPVERSION >= 15) 
BEGIN
	--New case manaegr is available SP15 and above
	INSERT INTO @cmClients select distinct clientid from CMDefinition (NOLOCK) where clientId NOT IN (select id from @cmClients)
	INSERT INTO @cmClients select  distinct clientid from CMDefinitionHistory (NOLOCK) where clientId NOT IN (select id from @cmClients)

	DECLARE @tempTable TABLE (t_CaseMgrStats XML)
	INSERT INTO @tempTable (t_CaseMgrStats)
	SELECT B.attrVal 
	FROM APP_Application A (NOLOCK)
	JOIN APP_SubclientProp B (NOLOCK) on a.id = B.componentNameId
	WHERE  attrName = ''Case Manager Metrics''

	SET @CaseMgrCustodianCount = ISNULL((SELECT  COUNT(c.id) 
							FROM APP_Client A (NOLOCK)
							JOIN CMDefinition B (NOLOCK) ON A.id = B.clientId 
							JOIN CMCustodian C (NOLOCK) ON B.id = C.definitionId ),0)

	SELECT @CaseMgrTotalEmails = ISNULL(SUM(ref1.value(''@numberOfItems'', ''BIGINT'')),0), 
		   @CaseMgrTotalSize =ISNULL(SUM(ref1.value(''@sizeOfItems'', ''BIGINT'')),0)
	FROM @tempTable N
	CROSS APPLY N.t_CaseMgrStats.nodes(''App_CaseManagerMetrics'') as R1(ref1)

END
SELECT @CaseMgrNewTotal  =  ISNULL(count(*), 0) from @cmClients


SELECT @TotalDataCube as ''Total DataCube'',
@TotalDataCubeDocs + @TotalFilrArchivingDocs as ''Total DataCubeDocs'',
@TotalDataCubeUsersCount + @TotalFileArchivingUsersCount as ''Total DataCubeUsers'',
@TotalFilrArchiving as ''Total FileArchiving'',
@TotalGDPR as ''Total GDPR'',
@TotalGDPRDocs as ''Total GDPR Docs'',
@TotalGDPRUsersCount as ''Total GDPR Users'',
@CaseMgrNewTotal as ''CaseManagerTotal'',

@DCFile as ''DataCube FILE'',
@DCLdap as''DataCube LDAP'',
@DCCsv as ''DataCube CSV'',
@DCWeb as ''DataCube WEB'',
@DCJdbc as ''DataCube JDBC'',
@DCHttp as ''DataCube HTTP'',
@DCBlank as ''DataCube BLANK'',
@DCSalesForce  as ''DataCube SALESFORCE'',
@FSSizeAnalyzedDatCube + @FSSizeAnalyzedArchiving as ''Total DataCube File Size'',
@FSSizeAnalyzedGDPR as ''Total GDPR File Size'',

@GDPRFile as ''GDPR FILE'',
@GDPRLdap as ''GDPR LDAP'',
@GDPRJdbc as ''GDPR JDBC'',
@CaseMgrCustodianCount as ''Case Manager Custodian'',
@CaseMgrTotalEmails as ''Case Manager Total Emails '',
@CaseMgrTotalSize as ''Case Manager Total Size''
IF OBJECT_ID(''tempdb.dbo.#tempgGlobalDSInfos'') IS NOT NULL
	DROP TABLE #tempgGlobalDSInfos ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Total DataCube] AS '@Col_1958470811',[Total DataCubeDocs] AS '@Col_886224246',[Total DataCubeUsers] AS '@Col_1718968877',[Total FileArchiving] AS '@Col_-2047055161',[Total GDPR] AS '@Col_1228516123',[Total GDPR Docs] AS '@Col_-1894804416',[Total GDPR Users] AS '@Col_1406426275',[CaseManagerTotal] AS '@CaseManagerTotal',[DataCube FILE] AS '@Col_-2101030435',[DataCube LDAP] AS '@Col_-2100856824',[DataCube CSV] AS '@Col_1594790245',[DataCube WEB] AS '@Col_1594809011',[DataCube JDBC] AS '@Col_-2100916388',[DataCube HTTP] AS '@Col_-2100960023',[DataCube BLANK] AS '@Col_-711048973',[DataCube SALESFORCE] AS '@Col_1478380800',[Total DataCube File Size] AS '@Col_-1957970208',[Total GDPR File Size] AS '@Col_-64789152',[GDPR FILE] AS '@Col_-863078051',[GDPR LDAP] AS '@Col_-862904440',[GDPR JDBC] AS '@Col_-862964004',[Case Manager Custodian] AS '@Col_1511723051',[Case Manager Total Emails ] AS '@Col_593667434',[Case Manager Total Size] AS '@Col_443612384' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10236'), type
)
  FOR XML PATH ('SurveyResults') 
)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
SET @outputXML = (SELECT @queryId AS '@QueryId', @EndTime AS '@LogDate', (@EndTime - @LogDate) AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA'))