

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

SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppGetRemoteClientConnectionInfoForAllClients')
	delete from GXDBVersions where aliasname = 'AppGetRemoteClientConnectionInfoForAllClients'
GO
print '... Creating Procedure: AppGetRemoteClientConnectionInfoForAllClients'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppGetRemoteClientConnectionInfoForAllClients
  @i_clientId INTEGER
AS
  DECLARE @clientId integer
  DECLARE @tunnelPort integer
  DECLARE @cvdPort integer
  DECLARE @isRoamingClient integer
  DECLARE @additionalPorts VARCHAR(255) -- comma separated port ranges 
  DECLARE @name nvarchar(255)
  DECLARE @guid nvarchar(255)
  DECLARE @generateNWConfig integer
  DECLARE @tunnelPortValid integer
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @l_shouldOutputTbl INT = 0
IF OBJECT_ID('tempdb.dbo.#tmpRemoteClients') IS NOT NULL
	SET @l_shouldOutputTbl  = 1
ELSE
BEGIN
		CREATE TABLE #tmpRemoteClients
        (       clientId INT,
                tunnelPort INT,
                cvdPort INT,
                isRoamingClient INT,
				additionalPorts  NVARCHAR(255),
				name NVARCHAR(255),
                guid NVARCHAR(255),
				generateConfig INT,
				tunnelPortValid INT
				PRIMARY KEY (clientId)
        )
END
-- First collect all the remote clients which have firewall configured to given input client
-- Get the list of remote clients given client has routes to
IF OBJECT_ID('tempdb.dbo.#outTbl') IS NOT NULL
	DROP TABLE #outTbl
CREATE TABLE #outTbl (clientId int, name nvarchar(255), net_hostName nvarchar(255), GUID nvarchar(255), type int)
CREATE CLUSTERED INDEX IDX_OutTable_clientId ON #outTbl(clientId)
IF OBJECT_ID('tempdb.dbo.#mediaAgentTbl') IS NOT NULL
	DROP TABLE #mediaAgentTbl
CREATE TABLE #mediaAgentTbl (clientId integer,maClientId integer,maClientName NVARCHAR(255),maClientHostName NVARCHAR(255),maClientGUID NVARCHAR(255))
DECLARE @mediaAgentClient INT = ISNULL((SELECT 1 FROM MMHost WHERE clientId = @i_clientId), 0)
DECLARE @proxyClient INT = ISNULL((SELECT isDMZ FROM App_FirewallOptions WHERE clientId = @i_clientId ),0)
IF OBJECT_ID('tempdb.dbo.#throttling') IS NOT NULL
	DROP TABLE #throttling
CREATE TABLE #throttling (clientId int, name nvarchar(255), net_hostName nvarchar(255), GUID nvarchar(255))
IF OBJECT_ID('tempdb..#smartTopoRestriction') IS NOT NULL
	DROP TABLE #smartTopoRestriction
CREATE TABLE #smartTopoRestriction
(
	clientId			INT,
	forClientId			INT,
	name				NVARCHAR(255),
	net_hostname		NVARCHAR(255),
	GUID				NVARCHAR(255),
	restrictionType		INT
)
CREATE CLUSTERED INDEX Idx_smartTopoRestriction_clientId ON #smartTopoRestriction(clientId)
CREATE NONCLUSTERED INDEX Idx_smartTopoRestriction_forClientId ON #smartTopoRestriction(forClientId)
IF NOT EXISTS (SELECT 1 FROM App_FWTrivialConfig WHERE clientId = @i_clientId AND isTrivialConfig = 1) AND
		NOT EXISTS ( SELECT 1 FROM App_FWTrivialConfig FW INNER JOIN APP_ClientGroupAssoc CG ON FW.clientGroupId = CG.clientGroupId AND FW.clientGroupId > 0 AND CG.clientId = @i_clientId AND FW.isTrivialConfig=1)
	BEGIN
		EXEC AppGetSmartNWTopologyRestriction @i_clientId
		INSERT INTO #outTbl
		SELECT clientId,name,net_hostname,GUID,restrictionType FROM #smartTopoRestriction WHERE forClientId = @i_clientId
		UNION
		SELECT forClientId,name,net_hostname,GUID,restrictionType FROM #smartTopoRestriction WHERE clientId = @i_clientId
		INSERT INTO #outTbl
			EXECUTE APPGetFWRestrictionFrom @i_clientId
		INSERT INTO #outTbl
			EXECUTE APPGetFWRestrictionTO @i_clientId
		INSERT INTO #throttling
			EXECUTE APPGetNetworkThrottleClient @i_clientId
		INSERT INTO #throttling
			EXECUTE APPGetNetworkThrottleRestrictionFrom @i_clientId
		END
	ELSE
	BEGIN
		INSERT INTO #mediaAgentTbl
			exec AppGetMediaAgentsForClient	@i_clientId
		INSERT INTO #outTbl
			SELECT maClientId,maClientName,maClientHostName,maClientGUID,0 FROM #mediaAgentTbl
		IF OBJECT_ID('tempdb.dbo.#mediaAgentTbl') IS NOT NULL
			DROP TABLE #mediaAgentTbl
		-- Add Commserv
		INSERT INTO #outTbl
		SELECT C.id,C.name,C.net_hostname,C.GUID,0 FROM APP_CLIENT C WHERE C.id =  2
		--Add proxy if present
		INSERT INTO #outTbl
		SELECT C.id,C.name,C.net_hostname,C.GUID,0 FROM APP_CLIENT C INNER JOIN App_FWTrivialConfig ON C.id = proxyClientIdCS AND proxyClientIdCS > 2 -- proxy has to be more than commserv client id
		INSERT INTO #outTbl
		SELECT C.id,C.name,C.net_hostname,C.GUID,0 FROM APP_CLIENT C INNER JOIN App_FWTrivialConfig ON C.id = proxyClientIdMA AND proxyClientIdMA > 2 AND NOT EXISTS (SELECT * FROM #outTbl WHERE clientId = C.id) -- proxy has to be more than commserv client id
	END
-- check if any VM in #outTbl then submit request to all Physical nodes
IF EXISTS( SELECT VMClientId FROM APP_VMToPMMap WITH(NOLOCK) WHERE VMClientId IN (SELECT clientId FROM #outTbl WHERE clientID <> 2)) --dont include cluster CS
BEGIN
	INSERT INTO #outTbl
	SELECT PMClientId, '', '', '', 0
	FROM APP_VMToPMMap WITH(NOLOCK)
	WHERE VMClientId IN (SELECT clientID FROM #outTbl WHERE clientID <> 2)
	DELETE #outTbl WHERE clientID IN (SELECT VMClientId FROM APP_VMToPMMap WITH(NOLOCK) WHERE VMClientId <> 2)
END
-- End of : Get the list of remote clients given client has routes to
	-- Get all client's with their firewall options
BEGIN
	SET NOCOUNT ON
	-- Flatten Firewall Options
	-- Note should re-design this table for better performance on PK
	IF OBJECT_ID('tempdb..#FWOptions') IS NOT NULL
		DROP TABLE #FWOptions
	CREATE TABLE #FWOptions
	(
		clientId		INT,
		clientGroupId	INT,
		tunnelPort		INT,
		isRoaming		INT,
		isCfgdAuto		INT,
		cgOrder			BIGINT,		-- firewall options table grouping order for all other configuration data.  Note can be different the Firewall Table ordering.
		PRIMARY KEY (clientGroupId, clientId)
	)
	INSERT INTO #FWOptions
		SELECT
			fo.clientId,
			fo.clientGroupId,
			fo.tunnelconnectionPort,
			fo.isRoamingClient,
			ISNULL(ep.value('@configureAutomatically', 'int'), 0),
			ISNULL(cgo.cgOrder, 0)	-- 0 - client has a firewall option row directly, if client associated by clientgroup want only the lowest ranked number
FROM (SELECT clientId, clientGroupId, tunnelconnectionPort, isRoamingClient, extendedProperties, flag FROM App_FirewallOptions FOpts WHERE (flag=0 OR (flag & (~4)) <> 0)) fo
			CROSS APPLY fo.extendedProperties.nodes('/App_FirewallExtendedProperties') foep(ep)
			LEFT OUTER JOIN (
				-- Rank ordering of client groups so client is only associated with the lowest rank group first for firewall options
				SELECT
					o.clientGroupId,
					RANK() OVER (ORDER BY o.id ASC) cgOrder
				FROM App_FirewallOptions o
				WHERE
o.clientGroupId > 0 AND ( o.flag = 0 OR ((o.flag & (~4)) <> 0))
			) cgo ON
				cgo.clientGroupId = fo.clientGroupId
	-- Get all grouped clients auto configuration / roaming - if client is part of any group with auto cfg roaming on then it is always on matter what group firewall configuration is used!
	IF OBJECT_ID('tempdb..#CfgdAuto') IS NOT NULL
		DROP TABLE #CfgdAuto
	CREATE TABLE #CfgdAuto
	(
		clientId		INT PRIMARY KEY,
		isCfgdAuto		INT,
		isRoamingClient INT,
		tunnelPort		INT
	)
	INSERT INTO #CfgdAuto
		SELECT
			cga.clientId,
			MAX(fwo.isCfgdAuto) isCfgdAuto,		-- if auto cfg on(1)[off(0)] for any group it is the client's default setting
			MAX(fwo.isRoaming) isRoamingClient,  -- if is roaming on(1)[off(0)] for any group, it is the client's default setting
			0
		FROM #FWOptions fwo
			INNER JOIN APP_ClientGroupAssoc cga ON
				fwo.clientGroupId > 0			-- only want group firewall configurations
				AND cga.clientGroupId = fwo.clientGroupId
		GROUP BY
			cga.clientId
	;WITH CA_NAME (clientId, tunnelPort, cgOrder)
	 AS
	(
		SELECT ACGA.clientId, FO.tunnelPort, ROW_NUMBER () over (PARTITION BY ACGA.clientId ORDER BY ACG.id ) cgOrder
		FROM #FWOptions FO
		INNER JOIN APP_ClientGroup ACG
		ON FO.clientGroupId = ACG.id
		INNER JOIN APP_ClientGroupAssoc ACGA
		ON ACGA.clientGroupId=ACG.id
		WHERE FO.isCfgdAuto=0
	)
	UPDATE CA
	SET CA.isCfgdAuto = 0, CA.tunnelPort = CAN.tunnelPort
	FROM #CfgdAuto CA
	INNER JOIN CA_NAME CAN
	ON CA.clientId = CAN.clientId
	WHERE CAN.cgOrder = 1
	-- Create a table with all Client's and their firewall options
	IF object_id('tempdb.dbo.#ClientFWOptions') IS NOT NULL
		DROP TABLE #ClientFWOptions
	CREATE TABLE #ClientFWOptions (
		clientId				INT PRIMARY KEY,
		tunnelPort				INT,
		cvdPort					INT,
		isRoaming				INT,
		additionalPortRanges	VARCHAR(255)	-- comma separated port ranges like:  5000,9000-10000,11000,12000-12001
	)
	DECLARE @defaultTunnelPort INTEGER = 8403
	INSERT INTO #ClientFWOptions (clientId, tunnelPort, cvdPort, isRoaming, additionalPortRanges)
		SELECT
			q.clientId,
			q.tunnelPort,
			q.cvdPort,
			q.isRoaming,
			q.additionalPortRanges
		FROM (
				SELECT
					c.id clientId,
					0 clientGroupId,
					CASE fwo.isCfgdAuto
						WHEN 0 THEN fwo.tunnelPort
						ELSE
							CASE c.cvdPort
								WHEN 0
									THEN @defaultTunnelPort
								ELSE
									c.cvdPort + 3
							END
					END tunnelPort,
					c.cvdPort,
					fwo.isRoaming isRoaming,
					ISNULL(
						pl.portList,
						ISNULL(		-- if individual client not directly configured with additional ports, find parent client group and use those ports instead????
							(
								SELECT
									STUFF((SELECT
											',' + CASE
													WHEN fwpr.startPort = fwpr.endPort THEN CAST(fwpr.startPort AS VARCHAR(12))
													ELSE CAST(fwpr.startPort AS VARCHAR(12)) + '-' + CAST(fwpr.endPort AS VARCHAR(12))
												END
										FROM APP_FWPortRange fwpr
											INNER JOIN App_ClientGroupAssoc cga
											ON fwpr.clientGroupId=cga.clientGroupId
											WHERE cga.clientId=c.id
											GROUP BY
												fwpr.startPort,
												fwpr.endPort
											FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''
									)
							),
							''	-- if none found, nothing!
						)
					) additionalPortRanges,
					1 pos		-- direct client configuration is always position 1
				FROM APP_Client c
					INNER JOIN #FWOptions fwo ON
						fwo.clientGroupId = 0
						AND fwo.clientId = c.id
					OUTER APPLY (
						-- Get directly configured client additional ports
						SELECT
							STUFF((SELECT
									',' + CASE
											WHEN fwpr.startPort = fwpr.endPort THEN CAST(fwpr.startPort AS VARCHAR(12))
											ELSE CAST(fwpr.startPort AS VARCHAR(12)) + '-' + CAST(fwpr.endPort AS VARCHAR(12))
										END
								FROM APP_FWPortRange fwpr
								WHERE
									fwpr.clientId = c.id
								GROUP BY
									fwpr.startPort,
									fwpr.endPort
								FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''
							) portList
					) pl
				UNION ALL
				SELECT
					c.id clientId,
					fwo.clientGroupId,
					CASE ca.isCfgdAuto		-- determine if auto cfg is on for any group the client is associated with
						WHEN 0 THEN ca.tunnelPort
						ELSE
							CASE c.cvdPort
								WHEN 0
									THEN @defaultTunnelPort
								ELSE
									c.cvdPort + 3
							END
					END tunnelPort,
					c.cvdPort,
					fwo.isRoaming isRoaming,
					ISNULL(pl.portList, '') additionalPortRanges,
					ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id ASC, fwo.cgOrder ASC) pos
				FROM APP_ClientGroupAssoc cga
					INNER JOIN #FWOptions fwo ON
						fwo.clientGroupId > 0
						AND fwo.clientGroupId = cga.clientGroupId
					INNER JOIN APP_Client c ON
						c.id = cga.clientId
						AND c.id NOT IN (SELECT clientId FROM #FWOptions WHERE clientGroupId = 0)		-- does client directly have associated firewall options, than do not include any group associations
					INNER JOIN #CfgdAuto ca ON
						ca.clientId = c.id
					OUTER APPLY (
						-- Get indiectly configed client group additional ports via client????
						SELECT
							STUFF((SELECT
										',' + CASE
												WHEN fwpr.startPort = fwpr.endPort THEN CAST(fwpr.startPort AS VARCHAR(12))
												ELSE CAST(fwpr.startPort AS VARCHAR(12)) + '-' + CAST(fwpr.endPort AS VARCHAR(12))
											END
									FROM APP_FWPortRange fwpr
										INNER JOIN APP_ClientGroupAssoc cga
										ON fwpr.clientGroupId=cga.clientGroupId
										WHERE cga.clientId=c.id
										GROUP BY
											fwpr.startPort,
											fwpr.endPort
										FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''
								) portList
					) pl
			) q
		WHERE
			q.pos = 1		-- Want only client position 1 rows, anything greater 1 is thrown away.  Want the earliest client group association configured for firewall options.
		IF OBJECT_ID('tempdb.dbo.#CfgdAuto') IS NOT NULL
			DROP TABLE #CfgdAuto
		IF OBJECT_ID('tempdb.dbo.#FWOptions') IS NOT NULL
			DROP TABLE #FWOptions
END
INSERT INTO #outTbl
SELECT *, 1 FROM #throttling
IF OBJECT_ID('tempdb.dbo.#throttling') IS NOT NULL
	DROP TABLE #throttling
IF OBJECT_ID('tempdb..#simClientIds') IS NOT NULL
	DROP TABLE #simClientIds
CREATE TABLE #simClientIds
(
	ClientId	INT PRIMARY KEY
)
INSERT INTO #simClientIds
SELECT ClientId
FROM simInstalledPackages
GROUP BY ClientId
INSERT INTO #tmpRemoteClients
--SELECT AC.id, (CASE WHEN FI.tunnelPort IS NULL THEN 0 ELSE FI.tunnelPort END) AS tunnelPort, AC.cvdPort, (CASE WHEN FI.isRoamingClient IS NULL THEN 0 ELSE FI.isRoamingClient END) AS isRoaming, (CASE WHEN FI.hasAdditionalPorts IS NULL THEN 0 ELSE 1 END) AS hasAdditionalPorts
SELECT DISTINCT AC.id
				, ISNULL(FI.tunnelPort, CASE AC.cvdPort WHEN 0 THEN @defaultTunnelPort ELSE AC.cvdPort + 3 END) AS tunnelPort
				, AC.cvdPort AS cvdPort
				, ISNULL(FI.isRoaming, 0) AS isRoaming
				, ISNULL(FI.additionalPortRanges, '') AS additionalPorts
				, AC.name
				, AC.guid
				, (CASE WHEN SCI.ClientId IS NULL THEN 0 ELSE  1  END) AS generateConfig
				, (CASE WHEN FI.tunnelPort IS NULL THEN 0 ELSE 1 END) AS tunnelPortValid
FROM #outTbl o
INNER JOIN App_Client AC
ON o.clientId=AC.Id
LEFT OUTER JOIN #ClientFWOptions FI
ON FI.clientId=AC.Id
LEFT OUTER JOIN #simClientIds SCI
ON AC.id=SCI.ClientId
UPDATE #tmpRemoteClients
SET generateConfig=1
FROM #tmpRemoteClients TRC
INNER JOIN App_ClientProp ACP
ON TRC.clientId=ACP.componentNameId
WHERE (ACP.attrName='Include in Firewall Config' AND ACP.attrVal='1' AND  ACP.attrType=7 /* INTEGER */ AND ACP.modified=0) OR
	  (ACP.attrName='Virtual Client' AND ACP.attrVal='1' AND ACP.modified=0)
IF @l_shouldOutputTbl = 0
BEGIN
	SELECT * FROM #tmpRemoteClients
	IF OBJECT_ID('tempdb.dbo.#tmpRemoteClients') IS NOT NULL     DROP TABLE #tmpRemoteClients
END
IF object_id('tempdb.dbo.#ClientFWOptions') IS NOT NULL
		DROP TABLE #ClientFWOptions
IF OBJECT_ID('tempdb.dbo.#outTbl') IS NOT NULL
	DROP TABLE #outTbl
IF OBJECT_ID('tempdb..#smartTopoRestriction') IS NOT NULL
	DROP TABLE #smartTopoRestriction
IF OBJECT_ID('tempdb..#simClientIds') IS NOT NULL
	DROP TABLE #simClientIds
SET NOCOUNT OFF
GO

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

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

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

