

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

-- Additional header files can be included for constants used in this stored procedure.
-- Stored procedure has to be rebuilt if any constant value changed in those header file.
-- This will generate the revision for the stored procedure. Don't remove this line!!!
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/AppGetOpenVMSProxyList.sp,v $ $Id: AppGetOpenVMSProxyList.sp,v 1.1.2.3.68.1 2021/06/10 00:19:05 stelagamsetti Exp $";
-- Name of the stored procedure
-- It must match with the name of the .sp file
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppGetOpenVMSProxyList')
	delete from GXDBVersions where aliasname = 'AppGetOpenVMSProxyList'
GO
print '... Creating Procedure: AppGetOpenVMSProxyList'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppGetOpenVMSProxyList
-- Input variables of the stored procedure
  @i_xmlReq XML
-- Tell the AWK processor that body of query starts here
AS
  DECLARE @o_clientList XML;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @in_clientId INT
DECLARE @in_userId INT
DECLARE @bkpConfigNodes XML = '<App_BackupConfigurationNodes />'
DECLARE @nodeCount INT = 0
DECLARE @proxyClientID INT = 0
DECLARE @osId INT
SET	@in_clientId = ISNULL (( SELECT ref.value('@clientId', 'INT')
	FROM @i_xmlReq.nodes ('App_OpenVMSProxyClientListReq') R(ref)), 0)
SET	@in_userId = ISNULL (( SELECT ref.value('@userId', 'INT')
	FROM @i_xmlReq.nodes ('App_OpenVMSProxyClientListReq') R(ref)), 0)
IF object_id('tempdb.dbo.#inClients') IS NOT null DROP TABLE #inClients
IF object_id('tempdb.dbo.#ExpandedClients') IS NOT null DROP TABLE #ExpandedClients
IF object_id('tempdb.dbo.#userClients') IS NOT null DROP TABLE #userClients
CREATE TABLE #inClients (id INT IDENTITY, clientId INT, clientGroupId INT)
CREATE TABLE #ExpandedClients (id INT, clientId INT)
CREATE TABLE #userClients
(
	t_clientId INT, t_appTypeId INT, t_instanceId INT, t_backupsetId INT, t_subclientId INT
)
IF(@in_userId<>0)
BEGIN
	EXEC sec_getIdaObjectsForUser @in_userId, 3 /*CLIENT_ENTITY*/ , 0, 0, '#userClients'
END
IF @in_clientId <> 0
BEGIN
	SET @bkpConfigNodes = ISNULL((SELECT attrVal FROM APP_ClientProp WHERE componentNameId = @in_clientId AND attrName = 'Open VMS proxy client list' AND modified = 0), '<App_BackupConfigurationNodes />')
	SELECT @osID = simOperatingSystemId FROM APP_Client WHERE id = @in_clientId
END
SET @nodeCount = (SELECT ISNULL(@bkpConfigNodes.value('count(/App_BackupConfigurationNodes/backupDataAccessNodes)', 'int'), 0))
IF (@nodeCount = 0)
BEGIN
	SET @proxyClientID = ISNULL((SELECT (CASE WHEN ISNUMERIC(attrVal) = 1 THEN cast(attrVal AS INT) ELSE 0 END) FROM APP_ClientProp WHERE componentNameId = @in_clientId AND attrName = 'Open VMS proxy client' AND modified = 0), 0)
	INSERT INTO #ExpandedClients(id, clientId) VALUES (0, @proxyClientID)
END
ELSE
BEGIN
	INSERT INTO #inClients(clientId, clientGroupId)
	SELECT	ISNULL(bkp.nodes.value('@clientId', 'INT'), 0),
			ISNULL(bkp.nodes.value('@clientGroupId', 'INT'), 0)
	FROM @bkpConfigNodes.nodes('/App_BackupConfigurationNodes/backupDataAccessNodes') bkp(nodes)
	------------------------------------------------------------
	-- Expand Client Groups and remove duplicates         ------
	------------------------------------------------------------
	INSERT INTO #ExpandedClients(id, clientId)
	SELECT DISTINCT CL.id, CL.clientId
	FROM
		(SELECT	I.id, (CASE WHEN I.clientId <> 0 THEN I.clientId ELSE CGA.clientId END) AS clientId, C.name, C.releaseId
			FROM	#inClients I
				LEFT OUTER JOIN APP_ClientGroupAssoc CGA ON CGA.clientGroupId = I.clientGroupId
				INNER JOIN APP_Client C ON (I.clientId = C.id OR CGA.clientId = C.id)
			WHERE
				((C.status & 0x00002) = 0 AND (C.status & 0x00004) = 0)
		) CL
		INNER JOIN simInstalledPackages pkg ON CL.clientId = pkg.ClientId
AND ((pkg.simPackageID = 1137 AND @osId = 118)
OR (pkg.simPackageID = 1138 AND @osID = 49)
OR (pkg.simPackageID = 1101 AND CL.releaseId < 16))
		INNER JOIN #userClients UC ON CL.clientId = UC.t_clientId
	ORDER BY CL.id
END
SELECT
(SELECT
		eClient.clientId as '@clientId',
		3 as '@_type_' --CLIENT_ENTITY = 3,
	FROM #ExpandedClients as eClient
	WHERE eClient.clientId > 0
	FOR XML PATH('backupDataAccessNodes'), TYPE)
FOR XML PATH('App_BackupConfigurationNodes'), TYPE
IF object_id('tempdb.dbo.#inClients') IS NOT null DROP TABLE #inClients
IF object_id('tempdb.dbo.#ExpandedClients') IS NOT null DROP TABLE #ExpandedClients
IF object_id('tempdb.dbo.#userClients') IS NOT null DROP TABLE #userClients
-- Tell the AWK processor that there are no more input lines to scan
GO

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

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

insert into GXDBVersions values(2, 'AppGetOpenVMSProxyList',  'v1.1.2.3.68.1', 'AppGetOpenVMSProxyList', 'v1.1.2.3.68.1')
GO

