

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/SEAGetEDiscoveryProjectsEntitiesDetails.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
--		  Given a list of Project Ids, this SP will return list of datasources, associated entities of those projects
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='SEAGetEDiscoveryProjectsEntitiesDetails')
BEGIN
	print '>>> Drop Stored Procedure: SEAGetEDiscoveryProjectsEntitiesDetails <<<'
	drop procedure SEAGetEDiscoveryProjectsEntitiesDetails
END
IF EXISTS (select * from GxQscripts where name='SEAGetEDiscoveryProjectsEntitiesDetails')
	delete from GxQscripts where name = 'SEAGetEDiscoveryProjectsEntitiesDetails'
GO

IF EXISTS (select * from GXDBVersions where aliasname='SEAGetEDiscoveryProjectsEntitiesDetails')
	delete from GXDBVersions where aliasname = 'SEAGetEDiscoveryProjectsEntitiesDetails'
GO
print '... Creating Procedure: SEAGetEDiscoveryProjectsEntitiesDetails'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure SEAGetEDiscoveryProjectsEntitiesDetails
  @i_userId INT,
  @i_ProjectIdsList VARCHAR(MAX) = '',	-- list of GDPR projects, separated by comma (,)
  @i_clientGroupId INT,
  @o_ErrorCode INT OUTPUT,
  @o_ErrorMsg VARCHAR(MAX) OUTPUT,
  @i_PlanId INT = 0
AS
SET NOCOUNT ON
BEGIN
	DECLARE @CRAW_TYPE VARCHAR(64) = 'crawltype'
	DECLARE @PSEUDOCLIENTID VARCHAR(64) = 'pseudoclientid'
	DECLARE @SUBCLIENTID VARCHAR(64) = 'subclientid'
	DECLARE @BACKED_UP_SUBCLIENT_IDS VARCHAR(64) = 'backedupsubclientids'
	DECLARE @CLIENTGROUPID_PROPERTY	VARCHAR(50) = 'clientgroupid'
	DECLARE @LIVE_CRAWL INT = 1
	DECLARE @BACKUP_CRAWL INT = 2
	DECLARE @IMPORT_INDEX_CRAWL INT = 3
	DECLARE @FILE_ANALYTICS INT = 4
DECLARE @CLIENT_ENTITY INT = 3
DECLARE @SUBCLIENT_ENTITY INT = 7
DECLARE @STORAGE_POLICY_ENTITY INT = 17
DECLARE @CLIENT_GROUP_ENTITY INT = 28
DECLARE @CV_APPTYPE_EXCHANGE_ONEPASS INT = 137
DECLARE @CV_APPTYPE_WINDOWS_FILESYSTEM INT = 33
DECLARE @CV_APPTYPE_MS_SHAREPOINTV2_DOC INT = 78
DECLARE @CV_APPTYPE_CLOUD_APPS INT = 134
	DECLARE @JMSUCCESS INT = 1
	DECLARE @PARTIALSUCCESS INT = 3
	DECLARE @JMSUCCESSWITHWARNINGS INT = 14
DECLARE @subclientStatus INT = CAST(0x100000 AS INT)|CAST(0x0800 AS INT)|CAST(0x00008 AS INT) |CAST(0x400000 AS INT) |CAST(0x40000 AS INT)
DECLARE @filterSubclient INT = CAST(0x0200 AS INT)
	DECLARE @EXCHANGE VARCHAR(256) = 'exchange'
	DECLARE @EMAIL VARCHAR(256) = 'email'
	DECLARE @SHAREPOINT VARCHAR(256) = 'sharepoint'
	DECLARE @ONEDRIVE VARCHAR(256) = 'onedrive'
	DECLARE @GOOGLEDRIVE VARCHAR(256) = 'googledrive'
	DECLARE @GMAIL	VARCHAR(256) = 'gmail'
	-- this is used to check whether this client lists coming from SDG or FSO
	-- if it is coming from SDG - it should have attribute "Edge Drive"
	--
	DECLARE @clientId INT = 0
    DECLARE @clientGroup INT = 0
	IF @i_clientGroupId > 0
	BEGIN
		SET @clientGroup = 1
	END
	SET @o_ErrorCode = 0
	SET @o_ErrorMsg = ''
	-- check input's validity
	IF (@i_UserId <= 0)
		BEGIN
			SET @o_ErrorCode = 1
			SET @o_ErrorMsg = 'Invalid user Id'
			GOTO exitLine
		END
	IF (@i_ProjectIdsList = '' AND @clientGroup = 0 AND @i_PlanId <= 0)
		BEGIN
			SET @o_ErrorCode = 1
			SET @o_ErrorMsg = 'Empty project Id list / no client group / invalid plan Id'
			GOTO exitLine
		END
	IF OBJECT_ID('tempdb.dbo.#InputProjectId') IS NOT NULL
		DROP TABLE #InputProjectId
	CREATE TABLE #InputProjectId (projectId INT)
	INSERT INTO #InputProjectId (projectId)
		SELECT a._ID FROM dbo.SplitIDs(@i_ProjectIdsList) a
	IF (@clientGroup = 0 AND @i_PlanId <= 0 AND (SELECT COUNT(*) FROM #InputProjectId) = 0)
		BEGIN
			SET @o_ErrorCode = 1
			SET @o_ErrorMsg = 'Invalid project Id list / client group Id / plan Id'
			GOTO exitLine
		END
	BEGIN TRY
	-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	-- get list of all clients which user has permission to view/edit
	IF OBJECT_ID('tempdb.dbo.#getIdaObjects') IS NOT NULL
		DROP TABLE #getIdaObjects
	CREATE TABLE #getIdaObjects (clientId INT, apptypeId INT, instanceID INT, backupsetId INT, subclientID INT)
	EXEC sec_getIdaObjectsForUser @i_UserId,@CLIENT_ENTITY,0,1,'#getIdaObjects'
	-- filter list of projects which user has permission to
	DELETE FROM #InputProjectId
	WHERE projectId NOT IN (SELECT clientId FROM #getIdaObjects)
	-- check if user has permission on those provided projects
	-- if yes, start getting datasources
	-- if no, assign appropriate error and return
	--
	IF (@clientGroup = 1 OR @i_PlanId > 0 OR EXISTS(SELECT TOP 1 1 FROM #InputProjectId))
		BEGIN
			IF OBJECT_ID('tempdb.dbo.#tempEProjectsAndDatasource') IS NOT NULL
				DROP TABLE #tempEProjectsAndDatasource
			CREATE TABLE #tempEProjectsAndDatasource (ProjectId INT, DatasourceId INT)
			-- verify if it is Project or Client from FSO
			--
			SET @clientId = (SELECT TOP 1 projectId AS clientID FROM #InputProjectId)
			-- check if edge drive property is available
			--
			IF (EXISTS(	SELECT TOP 1 1
						FROM APP_Client c  WITH (NOLOCK)
						INNER JOIN APP_ClientProp cp WITH (NOLOCK)
							ON 	cp.componentNameId = c.id
								AND cp.attrName = 'System Drive Type'
								AND cp.attrVal = N'6'	--EdgeSystemDriveTypes_EDISCOVERY
								AND cp.modified = 0		-- added to eliminate index scan
WHERE c.id = @clientId AND c.status & 0x10000000 <> 0))
			BEGIN
				-- this is SDG project
				-- there are valid projects, start getting datasources
				--
				INSERT INTO #tempEProjectsAndDatasource
				SELECT componentNameId AS ProjectId, CAST(attrVal AS INT) AS DatasourceId
				FROM APP_ClientProp A WITH (NOLOCK)
					INNER JOIN #InputProjectId I ON A.componentNameId = I.projectId
				WHERE attrName = 'Datasource Id' and modified = 0
			END
			ELSE IF (@i_PlanId > 0)
			BEGIN
				-- if plan Id is passed, then get all datasources which associate to the plan
				--
				INSERT INTO #tempEProjectsAndDatasource
				SELECT DISTINCT @i_PlanId, S.DatasourceId
				FROM SEDatasourceProperty S WITH (NOLOCK)
					INNER JOIN SEProperty P WITH (NOLOCK)
					ON P.propertyId = S.propertyId
				WHERE PropertyName = 'dcplanid' AND s.propertyValue = CAST(@i_PlanId AS NVARCHAR)
			END
			ELSE
			BEGIN
				-- this is clients from FSO
				-- getting datasource from given clientId
				IF OBJECT_ID('tempdb.dbo.#tempDsDetails') IS NOT NULL     DROP TABLE #tempDsDetails
				CREATE TABLE #tempDsDetails (
							clientId INTEGER,
							clientName NVARCHAR(256),
							clientDisplayName NVARCHAR(256),
							nethostName NVARCHAR(256),
							datasourceId INTEGER,
							subClientId INTEGER,
							subClientName NVARCHAR(256),
							includeDirectoryPath NVARCHAR(MAX),
							datasourceName NVARCHAR(256),
							dsDisplayName NVARCHAR(256),
							datasourceType NVARCHAR(256),
							crawlType INTEGER,
							planName NVARCHAR(255) DEFAULT ''
						)
				CREATE INDEX clientId_idx ON #tempDsDetails(clientId)
				DECLARE @propertyId INT
				DECLARE @crawlTypepropertyId INT
				SELECT @propertyId = PropertyId from SEProperty where PropertyName = 'includedirectoriespath'
				SELECT @crawlTypepropertyId = PropertyId from SEProperty where PropertyName = @CRAW_TYPE
                IF(@clientGroup = 1)
                BEGIN
                    SET @clientId = @i_clientGroupId
                END
                DECLARE @i_clientGroupFilter int = 0
--
-- Table to be used by SP sec_getIDAObjectsForUserWithPrivacy
--
IF OBJECT_ID('tempdb.dbo.#ClientsOutputTable') IS NOT NULL
BEGIN
    DROP TABLE #ClientsOutputTable
END
--
CREATE TABLE #ClientsOutputTable
(
	clientID INT,
	appTypeId INT,
	instanceId INT,
	backupsetID INT,
	subclientId INT
)
CREATE INDEX clientID_idx ON #ClientsOutputTable(clientID)
--
-- Fetch any clients even if any sub client is allowed under that client for the logged-in user
-- This behavior of inheriting permission from child also can be changed as per input in future if needed
--
EXEC sec_getIDAObjectsForUserWithPrivacy @i_userId, 3, 13, 1, '#ClientsOutputTable', 1
IF @clientGroup = 0
BEGIN
INSERT INTO #tempDsDetails
SELECT  ap.id, ap.name, ap.DisplayName, ap.net_hostname, acp.attrVal, 0 , '' , null ,sd.DataSourceName , sd.DisplayName , sdt.TypeName , null, ''
 FROM
App_client ap  WITH(NOLOCK)
INNER JOIN APP_ClientProp acp  WITH(NOLOCK)ON
acp.componentNameId = ap.id
INNER JOIN  SEDataSource sd  WITH(NOLOCK) ON
acp.attrVal =sd.DataSourceId
INNER JOIN  SEDataSourceType sdt  WITH(NOLOCK) ON
sdt.TypeId =sd.DataSourceType
WHERE
 acp.attrName = N'Indexing datasource id'
 AND acp.attrVal > 0
 AND acp.modified =0
 AND (@clientId = 0 OR ap.id = @clientId)
UNION
SELECT  ap.id , ap.name, ap.DisplayName, ap.net_hostname , acp.attrVal , app.id , app.subclientName  , null ,sd.DataSourceName , sd.DisplayName , sdt.TypeName , null, ''
FROM
	App_client ap  WITH(NOLOCK)
INNER JOIN APP_Application app  WITH(NOLOCK) ON
	app.clientId = ap.id
INNER JOIN APP_SubClientProp acp  WITH(NOLOCK) ON
	acp.componentNameId = app.id
INNER JOIN  SEDataSource sd  WITH(NOLOCK) ON
acp.attrVal =sd.DataSourceId
INNER JOIN  SEDataSourceType sdt  WITH(NOLOCK) ON
sdt.TypeId =sd.DataSourceType
WHERE
 acp.attrName = N'Indexing datasource id'
 AND acp.attrVal > 0
 AND acp.modified =0
 AND (@clientId = 0 OR ap.id = @clientId)
 UNION
 SELECT distinct c.id, c.name, c.displayName, c.net_hostname, bsp.attrVal, sc.id, sc.subclientName, null, sd.DataSourceName, sd.DisplayName, sdt.TypeName, 0, ''
	from  (select cp.componentNameId AS clientId, cp.attrName as attrName, cp.attrVal as attrVal
	FROM APP_ClientProp cp with(nolock) where cp.attrName in ('Virtual Machine Catalog Job Id', 'VSA Discover Subclient ID')
	AND cp.modified =0) AS SRC
	PIVOT
	(
		MAX(attrVal) FOR attrName in ([Virtual Machine Catalog Job Id] , [VSA Discover Subclient ID])
		)AS pvt
	inner join app_client c with(nolock) on
	pvt.clientId = c.id
	inner join APP_Application sc with(nolock) on
	PVT.[VSA Discover Subclient ID] = sc.id
	INNER JOIN APP_BackupSetName bs with(nolock) on
	sc.backupSet = bs.id
	INNER JOIN APP_BackupSetProp bsp with(nolock) on
	bsp.attrName = N'Indexing datasource id'
	AND bsp.attrVal > 0
	AND bsp.modified =0
	and bsp.componentNameId = bs.id
	INNER JOIN  SEDataSource sd  WITH(NOLOCK) ON
	bsp.attrVal =sd.DataSourceId
	INNER JOIN  SEDataSourceType sdt  WITH(NOLOCK) ON
	sdt.TypeId =sd.DataSourceType
    WHERE  ISNULL(pvt.[Virtual Machine Catalog Job Id], 0) > 0
	AND (@clientId = 0 OR c.id = @clientId)
--delete the dummy subclient record, if the same client,datasource combo is found at client as well as subclient level.
 DELETE A FROM #tempDsDetails AS  A INNER JOIN #tempDsDetails AS B ON A.datasourceId = B.datasourceId AND A.clientId = B.clientId
 WHERE  A.subClientId = 0 AND B.subClientId > 0
 -- honor security configuration by removing all clients which are not in the view clients list
 --
 DELETE T FROM #tempDsDetails T
 LEFT JOIN #ClientsOutputTable C ON C.clientID = T.clientId
 WHERE ISNULL(C.clientID,0) = 0
    -- honor client group filter
    --
    IF @i_clientGroupFilter <> 0
    BEGIN
	DECLARE @associatedEntitiesTable TABLE
    (
        xmlval xml
    )
	declare @associatedEntitiesRequest xml
    SET @associatedEntitiesRequest = (
            SELECT 3 '@expandLevel',
                (
                    SELECT @i_clientGroupFilter AS '@clientGroupId'
                    FOR XML PATH('entity'),
                        TYPE
                    )
            FOR XML PATH('App_GetAssociatedEntities'),
                TYPE
            )
	declare @associatedEntitiesResponse xml
    INSERT INTO @associatedEntitiesTable
    EXEC dbo.GetAssociatedEntities @associatedEntitiesRequest
    SET @associatedEntitiesResponse = ISNULL((select top 1 xmlval from @associatedEntitiesTable),'<App_GenericResponse/>')
    DECLARE @inTable table
    (
        clientId integer
    )
    INSERT INTO @inTable
    SELECT
    ISNULL(s.ref.value('@clientId', 'int'),0)
    FROM @associatedEntitiesResponse.nodes('/App_GenericResponse/response')  Tbl(Col)
        OUTER APPLY Col.nodes('entity') S(ref)
	delete from #tempDsDetails where clientId not in (select clientId from @inTable)
    END
 END
 ELSE
 BEGIN
 INSERT INTO #tempDsDetails
SELECT  ap.id, ap.name, ap.name, ap.name, acp.attrVal, 0 , '' , null ,sd.DataSourceName , sd.DisplayName , sdt.TypeName , null,''
 FROM
APP_ClientGroup ap  WITH(NOLOCK)
INNER JOIN APP_ClientGroupProp acp  WITH(NOLOCK)ON
acp.componentNameId = ap.id
INNER JOIN  SEDataSource sd  WITH(NOLOCK) ON
acp.attrVal =sd.DataSourceId
INNER JOIN  SEDataSourceType sdt  WITH(NOLOCK) ON
sdt.TypeId =sd.DataSourceType
WHERE
 acp.attrName = N'Indexing datasource id'
 AND acp.attrVal > 0
 AND acp.modified =0
 AND (@clientId = 0 OR ap.id = @clientId)
END
 UPDATE DS
SET DS.includeDirectoryPath =  DSD.includedirectoriespath,
	DS.crawlType = DSD.crawlType
FROM
(SELECT dsId,
	includedirectoriespath AS includedirectoriespath,
	ISNULL(crawlType, '0') AS crawlType
	 FROM
(SELECT DSP.dataSourceId AS dsId, P.PropertyName AS propName, DSP.PropertyValue as propValue
FROM SEDataSourceProperty DSP WITH (NOLOCK)
INNER JOIN SEProperty P
ON DSP.PropertyId = P.PropertyId
AND P.PropertyName IN ('includedirectoriespath', 'crawltype')
) AS SRC
PIVOT
(
	MAX(propValue) FOR propName in (includedirectoriespath, crawltype)
	)AS PVT) AS DSD
INNER JOIN #tempDsDetails DS
ON DS.dataSourceId = DSD.dsId
IF OBJECT_ID('tempdb.dbo.#ClientsOutputTable') IS NOT NULL
BEGIN
    DROP TABLE #ClientsOutputTable
END
				INSERT INTO #tempEProjectsAndDatasource
				SELECT @clientId, datasourceId
				FROM #tempDsDetails
				WHERE datasourceType = 'file' -- FSO: just for file system
					OR datasourceType = 'cloudstorage' -- FSO: + cloud storage ds type
			END
			IF (EXISTS(SELECT TOP 1 1 FROM #tempEProjectsAndDatasource WHERE DatasourceId > 0))
				-- there are datasources available
				BEGIN
					-- get list of properties which are required to get appropriate entities info from datasource properties
					IF OBJECT_ID('tempdb.dbo.#tempEDatasourceProp') IS NOT NULL
						DROP TABLE #tempEDatasourceProp
					CREATE TABLE #tempEDatasourceProp (DatasourceId INT, PropertyName VARCHAR(256), PropertyValue NVARCHAR(MAX))
					INSERT INTO #tempEDatasourceProp
					SELECT DISTINCT DSP.DataSourceId, P.PropertyName,
						CASE
							WHEN P.PropertyName != @BACKED_UP_SUBCLIENT_IDS THEN DSP.PropertyValue
							ELSE
								CASE
									-- just get the first subclient in the list
									-- currently we only support single subclient for exchange
									-- and single storage policy per client
									--
									WHEN ISNULL(DSP.PropertyValue,'') = '' THEN '0'
									WHEN CHARINDEX(',', DSP.PropertyValue) > 0 THEN SUBSTRING(DSP.PropertyValue, 1, CHARINDEX(',', DSP.PropertyValue)  - 1)
									ELSE DSP.PropertyValue
								END
						END
					FROM SEDataSourceProperty AS DSP WITH (NOLOCK)
           			INNER JOIN SEProperty AS P WITH (NOLOCK)
						 ON P.PropertyId = DSP.PropertyId AND P.PropertyName IN (@CRAW_TYPE, @PSEUDOCLIENTID, @SUBCLIENTID, @BACKED_UP_SUBCLIENT_IDS, @CLIENTGROUPID_PROPERTY)
						 ANd DSP.DataSourceId IN (SELECT DISTINCT DatasourceId FROM #tempEProjectsAndDatasource)
					--  get datasources' properties: crawltype, pseudoclient, subclient, backedupsubclient
					--  if crawltype is live (1)
					--      return subclientId as genericentity
					--  if crawltype is backedup (2)
					--      if subclient is from email new agent
					--            return subclientId as genericentity
					--      else
					--            return storage policies associated to backedup subclients
					--  if crawltype is import (3)
					--      if subclient is from email new agent
					--          return subclientId as genericentity
					--      else
					--          return clientId as genericentity
					-- if there is no crawltype --> default = live (1)
				IF OBJECT_ID('tempdb.dbo.#tempEDatasourceCrawlDetail') IS NOT NULL
					DROP TABLE #tempEDatasourceCrawlDetail
				CREATE TABLE #tempEDatasourceCrawlDetail (DatasourceId INT, DatasourceType VARCHAR(256), CrawlType INT, ClientId INT, SubclientId INT, ClientGroupId INT)
				-- fill data from property
				INSERT INTO #tempEDatasourceCrawlDetail
				SELECT P.DatasourceId, '', CAST(ISNULL(P.PropertyValue, '1') AS INT), 0, 0, 0
				FROM #tempEDatasourceProp P
				WHERE P.PropertyName = @CRAW_TYPE
				-- update client id and datasource type from property and sedatasourcetype
				-- datasource type is needed in case of crawltype = 2, and there is no backedup subclient information
				-- we will need to get backedup subclients from clientId and apptype
				-- apptype is mapped from datasourcetype
				UPDATE T
				SET DatasourceType = DT.TypeName, ClientId = CAST(ISNULL(P.PropertyValue, '0') AS INT)
				FROM #tempEDatasourceCrawlDetail T
					JOIN #tempEDatasourceProp P
						ON P.DatasourceId = T.DatasourceId
					JOIN SEDataSource S WITH (NOLOCK)
						ON T.DatasourceId = S.DataSourceId
					JOIN SEDataSourceType DT WITH (NOLOCK)
						ON S.DataSourceType = DT.TypeId
				WHERE P.PropertyName = @PSEUDOCLIENTID
                -- update client group
				UPDATE T
				SET DatasourceType = DT.TypeName, ClientGroupId = CAST(ISNULL(P.PropertyValue, '0') AS INT)
				FROM #tempEDatasourceCrawlDetail T
					JOIN #tempEDatasourceProp P
						ON P.DatasourceId = T.DatasourceId
					JOIN SEDataSource S WITH (NOLOCK)
						ON T.DatasourceId = S.DataSourceId
					JOIN SEDataSourceType DT WITH (NOLOCK)
						ON S.DataSourceType = DT.TypeId
				WHERE P.PropertyName = @CLIENTGROUPID_PROPERTY
				-- update subclient id from property
				UPDATE T
				SET SubclientId = CAST(ISNULL(P.PropertyValue, '0') AS INT)
				FROM #tempEDatasourceCrawlDetail T
					JOIN #tempEDatasourceProp P
						ON P.DatasourceId = T.DatasourceId
				WHERE P.PropertyName =
					CASE
						WHEN T.CrawlType = @LIVE_CRAWL THEN @SUBCLIENTID
						ELSE @BACKED_UP_SUBCLIENT_IDS
					END
				-- Remove from table if there are invalid association for file system
				--
				DELETE T FROM #tempEDatasourceCrawlDetail T
				WHERE DatasourceType = 'file'
					AND CrawlType = @LIVE_CRAWL
					AND ClientId <> (SELECT A.clientId FROM APP_Application A WITH(NOLOCK) WHERE A.id = T.SubclientId)
				-- in case of crawltype = 2 and there is no information for subclient,
				-- then from clientId, get list of subclients which belong to one specific apptype
				-- and have been successfully backedup and get the first subclient form the list
				--
				IF (EXISTS(SELECT TOP 1 1 FROM #tempEDatasourceCrawlDetail WHERE CrawlType = @BACKUP_CRAWL AND SubclientId = 0))
				BEGIN
					IF OBJECT_ID('tempdb.dbo.#BackedupSubclientId') IS NOT NULL
						DROP TABLE #BackedupSubclientId
					CREATE TABLE #BackedupSubclientId (ClientId INT, SubclientId INT, AppTypeId INT, DatasourceType VARCHAR(256))
					INSERT INTO #BackedupSubclientId (ClientId, SubclientId, AppTypeId, DatasourceType)
						SELECT A.clientId, A.id, A.appTypeId, T.DatasourceType
						FROM #tempEDatasourceCrawlDetail T
						JOIN APP_Application A WITH(NOLOCK)
							ON T.ClientId = A.clientId
								AND A.appTypeId = 	(CASE
														WHEN T.DatasourceType = @EXCHANGE OR T.DatasourceType = @EMAIL THEN @CV_APPTYPE_EXCHANGE_ONEPASS
														WHEN T.DatasourceType = @SHAREPOINT THEN @CV_APPTYPE_MS_SHAREPOINTV2_DOC
														WHEN T.DatasourceType = @ONEDRIVE OR T.DatasourceType = @GOOGLEDRIVE OR T.DatasourceType = @GMAIL THEN @CV_APPTYPE_CLOUD_APPS
														ELSE @CV_APPTYPE_WINDOWS_FILESYSTEM
													END)
						WHERE CrawlType = @BACKUP_CRAWL AND SubclientId = 0
							AND (A.subclientStatus = 0
								OR ( A.subclientStatus & @subclientStatus <> 0
								AND A.subclientStatus & @filterSubclient = 0
AND NOT ((A.subclientStatus & 0x00010) <> 0) --CV_STATUS_DUMMY
AND NOT ((A.subclientStatus & 0x00020) <> 0) --CV_STATUS_HIDDEN
AND NOT ((A.subclientStatus & 0x00002) <> 0)  --CV_STATUS_UNINSTALLED
AND NOT ((A.subclientStatus & 0x00004) <> 0)))  --CV_STATUS_DELETED
					-- get the first subclient for each apptype which are already backedup
					--
					;WITH CTE
					AS
					(
						SELECT ClientId, DatasourceType, B.SubclientId, ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY ClientId DESC) RowNum
						FROM #BackedupSubclientId B
						JOIN JMBkpStats bkp WITH(NOLOCK)
							ON bkp.appId = B.SubclientId
										AND bkp.status in (@JMSUCCESS, @PARTIALSUCCESS, @JMSUCCESSWITHWARNINGS)
										AND bkp.appType = B.AppTypeId
					)
					UPDATE #tempEDatasourceCrawlDetail
					SET SubclientId = CTE.SubclientId
					FROM #tempEDatasourceCrawlDetail T
					JOIN CTE
						ON CTE.RowNum = 1 AND CTE.ClientId = T.ClientId AND CTE.DatasourceType = T.DatasourceType
					WHERE CrawlType = @BACKUP_CRAWL AND T.SubclientId = 0
				END
				-- final result project Id, datasourceId, clientId, EntityType, EntityId
				SELECT P.ProjectId, T.DatasourceId,
					CASE
						-- this case is to havdle the job based on client, where data source is configured,
						-- client id will be 0 in this case
						-- for client group based job, client id is not relevant
						WHEN @clientGroup = 0 AND T.CrawlType = @FILE_ANALYTICS AND T.ClientGroupId > 0 THEN P.ProjectId
						ELSE T.ClientId
					END AS ClientId,
					CASE
						WHEN T.CrawlType = @LIVE_CRAWL THEN @SUBCLIENT_ENTITY
						WHEN T.CrawlType = @FILE_ANALYTICS AND @clientGroup = 0 THEN @CLIENT_ENTITY
						WHEN T.CrawlType = @FILE_ANALYTICS AND @clientGroup = 1 THEN @CLIENT_GROUP_ENTITY
						WHEN T.CrawlType = @BACKUP_CRAWL THEN
							CASE
								WHEN A.appTypeId = @CV_APPTYPE_EXCHANGE_ONEPASS THEN @SUBCLIENT_ENTITY
								ELSE @STORAGE_POLICY_ENTITY
							END
						ELSE
							CASE
								WHEN A.appTypeId = @CV_APPTYPE_EXCHANGE_ONEPASS THEN @SUBCLIENT_ENTITY
								ELSE @CLIENT_ENTITY
							END
					END AS EntityType,
					CASE
						WHEN T.CrawlType = @FILE_ANALYTICS AND T.ClientGroupId > 0 THEN P.ProjectId
						WHEN T.CrawlType = @LIVE_CRAWL THEN T.SubclientId
						WHEN T.CrawlType = @BACKUP_CRAWL THEN
							CASE
								WHEN A.appTypeId = @CV_APPTYPE_EXCHANGE_ONEPASS THEN T.SubclientId
								ELSE A.dataArchGrpID
							END
						ELSE
							CASE
								WHEN A.appTypeId = @CV_APPTYPE_EXCHANGE_ONEPASS THEN T.SubclientId
								ELSE T.ClientId
							END
					END AS EntityId
				FROM #tempEDatasourceCrawlDetail T
				LEFT JOIN APP_Application A WITH(NOLOCK)
					ON T.SubclientId = A.id
				JOIN #tempEProjectsAndDatasource P
					ON T.DatasourceId = P.DatasourceId
				END
			ELSE
				-- there are no datasources associated to given list of projects
				BEGIN
					SET @o_ErrorCode = 3
					SET @o_ErrorMsg = 'There are no available datasources associated to given list of projects'
				GOTO exitLine
			END
		END
	ELSE
		BEGIN
			SET @o_ErrorCode = 2
			SET @o_ErrorMsg = 'User does not have permission to view those projects'
			GOTO exitLine
		END
	--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	END TRY
	BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
		EXECUTE dbo.GetError @o_ErrorMsg OUTPUT , @o_ErrorCode OUTPUT
	END CATCH
	--	Clean up and exit
exitLine:
	IF OBJECT_ID('tempdb.dbo.#InputProjectId') IS NOT NULL
		DROP TABLE #InputProjectId
	IF OBJECT_ID('tempdb.dbo.#getIdaObjects') IS NOT NULL
		DROP TABLE #getIdaObjects
	IF OBJECT_ID('tempdb.dbo.#tempEProjectsAndDatasource') IS NOT NULL
		DROP TABLE #tempEProjectsAndDatasource
	IF OBJECT_ID('tempdb.dbo.#tempEDatasourceProp') IS NOT NULL
		DROP TABLE #tempEDatasourceProp
	IF OBJECT_ID('tempdb.dbo.#tempEDatasourceCrawlDetail') IS NOT NULL
		DROP TABLE #tempEDatasourceCrawlDetail
	IF OBJECT_ID('tempdb.dbo.#BackedupSubclientId') IS NOT NULL
		DROP TABLE #BackedupSubclientId
	IF OBJECT_ID('tempdb.dbo.#tempDsDetails') IS NOT NULL
		DROP TABLE #tempDsDetails
END
GO

IF EXISTS (select * from GxQscripts where name = 'SEAGetEDiscoveryProjectsEntitiesDetails')
	delete from GxQscripts where name = 'SEAGetEDiscoveryProjectsEntitiesDetails'
GO

IF EXISTS (select * from GXDBVersions where aliasname='SEAGetEDiscoveryProjectsEntitiesDetails')
	delete from GXDBVersions where aliasname = 'SEAGetEDiscoveryProjectsEntitiesDetails'
GO

insert into GXDBVersions values(2, 'SEAGetEDiscoveryProjectsEntitiesDetails',  '00000000000000000000', 'SEAGetEDiscoveryProjectsEntitiesDetails', '00000000000000000000')
GO

