

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

-- ----------------------------------------------------------------------
--
--           Copyright (c) 2012  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.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/AppGetExchangeMemberClients.sp,v $ $Id: AppGetExchangeMemberClients.sp,v 1.1.2.14.50.1 2021/06/22 13:59:08 jragunathan Exp $"
--
--  +========================================================================+
--  | Stored Precedure: AppGetExchangeMemberClients()
--  | To get the valid member clients for the Exchange Virtual client.
--  +========================================================================+
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppGetExchangeMemberClients')
	delete from GXDBVersions where aliasname = 'AppGetExchangeMemberClients'
GO
print '... Creating Procedure: AppGetExchangeMemberClients'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppGetExchangeMemberClients
  @inXML XML
AS
BEGIN
  DECLARE @outXML XML
	DECLARE @inJobID			BIGINT
	DECLARE	@inClientId			INT
	DECLARE @inMemberServerXML	XML
	DECLARE @ErrorMessage		NVARCHAR(4000)
	DECLARE @ErrorSeverity		INT
	DECLARE @ErrorState			INT
	SET @ErrorSeverity		=	0
	SET @ErrorState			=	0
	SET @ErrorMessage		=	''
	SET NOCOUNT ON
	BEGIN TRY
		SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
		SELECT @inJobID = ISNULL(ref.value('@jobId', 'BIGINT'),0) FROM @inXML.nodes('App_GetExchangeMemberClientsReq') R ( ref )
		SELECT @inClientId = ISNULL(ref.value('@clientId', 'INT'),0) FROM @inXML.nodes('App_GetExchangeMemberClientsReq') R ( ref )
		DECLARE @memberClients TABLE
		(
			id	integer,
			clientId integer
		)
IF EXISTS(SELECT 1 FROM APP_IDAName WHERE clientId = @inClientId AND appTypeId = 137)
		BEGIN
			-- Create temp tables
			DECLARE @GenericEntity TABLE
			(
				id_auto int IDENTITY(1,1),
				entityType integer,
				commcellId integer ,
				clientId integer ,
				clientgroupId integer
			)
			IF @inJobID > 0 AND EXISTS(SELECT 1 FROM JMJOBSTATS WHERE jobId = @inJobid and ((opType=4 AND subOpType=98) OR (opType=109))) AND dbo.GetXMLJobOptions(@inJobid).exist('TMMsg_JobOption/backupOpts/exchOnePassOptions/proxies/memberServers') = 1
			BEGIN
				SET @inMemberServerXML = dbo.GetXMLJobOptions(@inJobid).query('TMMsg_JobOption/backupOpts/exchOnePassOptions/proxies')
				-- Populate the selected entity information
				INSERT INTO @GenericEntity
				SELECT
				ISNULL(Tbl.Col.value('@_type_', 'int'),0) ,
				ISNULL(Tbl.Col.value('@commCellId', 'int'),2) ,
				ISNULL(Tbl.Col.value('@clientId', 'int'),0),
				ISNULL(Tbl.Col.value('@clientGroupId', 'int'),0)
				FROM @inMemberServerXML.nodes('/proxies/memberServers/client') as Tbl(Col)
			END
			ELSE IF @inJobID > 0 AND EXISTS(SELECT 1 FROM JMJOBSTATS WHERE jobId = @inJobid and opType = 113) AND dbo.GetXMLJobOptions(@inJobid).exist('TMMsg_JobOption/backupOpts/mediaOpt/auxcopyJobOption/proxies/memberServers') = 1
			BEGIN
				SET @inMemberServerXML = dbo.GetXMLJobOptions(@inJobid).query('TMMsg_JobOption/backupOpts/mediaOpt/auxcopyJobOption/proxies')
				-- Populate the selected entity information
				INSERT INTO @GenericEntity
				SELECT
				ISNULL(Tbl.Col.value('@_type_', 'int'),0) ,
				ISNULL(Tbl.Col.value('@commCellId', 'int'),2) ,
				ISNULL(Tbl.Col.value('@clientId', 'int'),0),
				ISNULL(Tbl.Col.value('@clientGroupId', 'int'),0)
				FROM @inMemberServerXML.nodes('/proxies/memberServers/client') as Tbl(Col)
			END
			ELSE IF @inJobID > 0 AND EXISTS(SELECT 1 FROM JMJOBSTATS WHERE jobId = @inJobid and opType = 5) AND dbo.GetXMLJobOptions(@inJobid).exist('TMMsg_JobOption/restoreOptions/exchangeOption/proxies/memberServers') = 1
			BEGIN
				SET @inMemberServerXML = dbo.GetXMLJobOptions(@inJobid).query('TMMsg_JobOption/restoreOptions/exchangeOption/proxies')
				-- Populate the selected entity information
				INSERT INTO @GenericEntity
				SELECT
				ISNULL(Tbl.Col.value('@_type_', 'int'),0) ,
				ISNULL(Tbl.Col.value('@commCellId', 'int'),2) ,
				ISNULL(Tbl.Col.value('@clientId', 'int'),0),
				ISNULL(Tbl.Col.value('@clientGroupId', 'int'),0)
				FROM @inMemberServerXML.nodes('/proxies/memberServers/client') as Tbl(Col)
			END
			ELSE
			BEGIN
				SET @inMemberServerXML = CAST(REPLACE((SELECT attrVal FROM APP_IDAProp WHERE componentNameId = (SELECT id FROM APP_IDAName WHERE clientId = @inClientId AND appTypeId = 137)
																											AND attrName = 'OnePass Member Servers' AND modified = 0), 'UTF-8', 'UTF-16') AS XML)
				-- Populate the selected entity information
				INSERT INTO @GenericEntity
				SELECT
				ISNULL(Tbl.Col.value('@_type_', 'int'),0) ,
				ISNULL(Tbl.Col.value('@commCellId', 'int'),2) ,
				ISNULL(Tbl.Col.value('@clientId', 'int'),0),
				ISNULL(Tbl.Col.value('@clientGroupId', 'int'),0)
				FROM @inMemberServerXML.nodes('/App_MemberServers/memberServers/client') as Tbl(Col)
			END
			INSERT INTO @memberClients
			(id, clientId)
			SELECT MIN(id_auto), clientId
			FROM @GenericEntity
WHERE entityType = 3
			GROUP BY clientId
			MERGE INTO @memberClients AS target
			USING  (SELECT ET.id_auto, ACG.clientId
					FROM APP_ClientGroupAssoc ACG
					INNER JOIN
(SELECT id_auto, clientgroupid FROM @GenericEntity WHERE entityType = 28) AS ET
					ON
					ACG.clientGroupId = ET.clientgroupid) as source
			ON (target.clientId = source.clientId)
			WHEN MATCHED THEN
			UPDATE SET id = CASE
					WHEN source.id_auto < target.id THEN source.id_auto
					ELSE target.id
					END
			WHEN NOT MATCHED THEN
			INSERT (id, clientId)
			VALUES (source.id_auto, source.clientId);
		END
		ELSE -- Physical Client
		BEGIN
			INSERT INTO @memberClients VALUES(1, @inClientId)
		END
		-- Delete the deconfigured and deleted clients.
DELETE @memberClients WHERE clientId NOT IN (SELECT ClientId FROM simInstalledPackages (NOLOCK) WHERE simPackageID IN ( 152, 151) )
		DECLARE @disableUseProxyCheck INT = 0
	    SET @disableUseProxyCheck = ISNULL((SELECT CAST(value AS INT) FROM GxGlobalParam WHERE name = 'DisableUseProxyCheck'),0)
		IF (@disableUseProxyCheck != 1)
		BEGIN
			DECLARE @ownersTable TABLE (userId INT)
			DECLARE @expandOwners INT = 1
			DECLARE @userId INT = 0
			--Get a list of owner user Ids
			INSERT INTO @ownersTable
			SELECT userId FROM sec_getClientOwnersWithParam(@inClientId,0,@expandOwners)
			WHERE userId != 0 AND clientId = @inClientId
IF OBJECT_ID('tempdb.dbo.#getPermissionsOnEntities_InputTable') IS NOT NULL DROP TABLE #getPermissionsOnEntities_InputTable CREATE TABLE #getPermissionsOnEntities_InputTable ( entityId1 INT, entityId2 INT DEFAULT 0, entityId3 INT DEFAULT 0, entityId4 INT DEFAULT 0, entityId5 INT DEFAULT 0 ) IF OBJECT_ID('tempdb.dbo.#getPermissionsOnEntities_OutputTable') IS NOT NULL DROP TABLE #getPermissionsOnEntities_OutputTable CREATE TABLE #getPermissionsOnEntities_OutputTable ( entityId1 INT, entityId2 INT, entityId3 INT, entityId4 INT, entityId5 INT, permissionsString VARCHAR(512), isChildRow BIT DEFAULT 0	        )
	        INSERT INTO #getPermissionsOnEntities_InputTable (entityId1, entityId2, entityId3, entityId4, entityId5)
	        SELECT clientId, 0, 0, 0,0 FROM @memberClients
			-- We are using an iterative approach for now until we can come up with a better query
			DECLARE @currOwnerId INT = 0
			DECLARE ownerCursor CURSOR FOR SELECT userId FROM @ownersTable
			OPEN ownerCursor
			FETCH NEXT FROM ownerCursor INTO @currOwnerId
			-- For each owner try to see if there are any member  clients with "Use Proxy" permission
			WHILE @@FETCH_STATUS = 0
			BEGIN
EXEC sec_getPermissionsOnEntities @currOwnerId, 3
				DELETE FROM #getPermissionsOnEntities_OutputTable
WHERE (SUBSTRING(permissionsString,252,1) != CAST(1 AS NVARCHAR(1))) AND
(SUBSTRING(permissionsString,2,1) != CAST(1 AS NVARCHAR(1)))
				IF EXISTS (SELECT entityId1 FROM #getPermissionsOnEntities_OutputTable INTERSECT SELECT clientId FROM @memberClients)
				BEGIN
					-- We found an owner with "Use Proxy" permission. Return these as the member servers
					DELETE FROM @memberClients
					WHERE clientId NOT IN (SELECT entityId1 from #getPermissionsOnEntities_OutputTable)
					BREAK
				END
				FETCH NEXT FROM ownerCursor INTO @currOwnerId
			END
			CLOSE ownerCursor
			DEALLOCATE ownerCursor
	    END
		SET @outXML =	(
							SELECT
							(
								SELECT
								C.id as '@clientId',
								C.name as '@clientName',
								C.net_hostname as '@hostName'
								FROM APP_Client C
								WHERE C.id = E.clientId
								FOR XML PATH('client'),TYPE
							)
							FROM @memberClients E ORDER BY id
							FOR XML PATH('memberServers') , ROOT('App_MemberServers'), TYPE
						)
		IF @outXML IS NULL
			SET @outXML = '<App_MemberServers/>'
		SELECT @outXML
	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)
		 SELECT
			@ErrorMessage = @ErrorMessage + ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();
	END CATCH
	IF @ErrorSeverity <> 0 OR @ErrorState <> 0
		RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState)
END
-------------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
GO

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

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

insert into GXDBVersions values(2, 'AppGetExchangeMemberClients',  'v1.1.2.14.50.1', 'AppGetExchangeMemberClients', 'v1.1.2.14.50.1')
GO

