

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/AppGetServerListWithoutSelect.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.
-- ----------------------------------------------------------------------*/
-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/AppGetServerListWithoutSelect.sp,v $ $Id: AppGetServerListWithoutSelect.sp,v 1.1.4.18.8.1 2021/06/18 21:01:06 anarde Exp $";
-- =============================================
-- Author:		Anand Vibhor
-- Create date: 01/28/2006
-- Description:	get the server list based on capability
-- =============================================
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='AppGetServerListWithoutSelect')
	delete from GXDBVersions where aliasname = 'AppGetServerListWithoutSelect'
GO
print '... Creating Procedure: AppGetServerListWithoutSelect'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure AppGetServerListWithoutSelect
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
--
--   PARAMETERS   &   OUTPUTS
--
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
  @capabilityReqd bigint,											
  @locale_in integer,
  @userId integer,
  @oXml XML OUTPUT,
  @hideMask integer = 0,
  @propertyLevel integer = 30,
  @permissionsList varchar(1024) = '',
  @isAndOperation integer = 0,
  @clientGroupIds varchar(1024) = ''
AS
--hideMask 0 means ShowAll
--propertyLevel 1 ListPropertyLevels_ListOnly
--propertyLevel 30 ListPropertyLevels_AllProperties
--propertyLevel <> 1 is treated as ListPropertyLevels_AllProperties
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
--IF @propertyLevel <> 1 --ListPropertyLevels_ListOnly
 --SET @propertyLevel = 30 --ListPropertyLevels_AllProperties
DECLARE @xmlText XML
--FETCH THE GROUPS
SET NOCOUNT ON
DECLARE @tempServerGroupIdList TABLE
	(
		clientGroupId	int,
		PRIMARY KEY (clientGroupId)
	);
insert into @tempServerGroupIdList
SELECT TRY_CAST(_ID as INT) FROM dbo.SplitIDs(@clientGroupIds) where ISNULL(TRY_CAST(_ID as int), 0) > 0
-- If server group Ids are passed, validate them
IF EXISTS (SELECT TOP 1 1 FROM @tempServerGroupIdList)
BEGIN
	--Remove invalid client groups
	DELETE TM
	FROM @tempServerGroupIdList TM LEFT OUTER JOIN APP_ClientGroup CG WITH (NOLOCK)
		ON CG.id = TM.clientGroupId WHERE CG.ID IS NULL
	IF NOT EXISTS (SELECT TOP 1 1 FROM @tempServerGroupIdList)	-- If there are no valid Ids left, exit
	BEGIN
		GOTO EXITPROC
	END
END
DECLARE @noCapsCheck BIT = 0
IF @capabilityReqd = 0 AND @permissionsList = ''
	SET @noCapsCheck = 1
DECLARE @isAdminUserId INT = ISNULL((SELECT 1 FROM UMUsers WHERE (id=@userId) AND (flags & 0x040 <> 0)) , 0)
IF @noCapsCheck = 1				-- Caller wants all client groups visible.
BEGIN
	IF OBJECT_ID ('tempdb.dbo.#visibleClientGroups') IS NOT NULL
		DROP TABLE #visibleClientGroups
	CREATE TABLE #visibleClientGroups  (entityId INT)
	IF @isAdminUserId = 0	-- Non Admin User
	BEGIN
		EXEC sec_getNonIdaObjectsForThisUser  @userId,
												28,			-- CLIENT_GROUP_ENTITY
												0,
												'#visibleClientGroups'
		IF EXISTS (SELECT TOP 1 1 FROM @tempServerGroupIdList)		-- Filter out only the required visible clients if lis of server groups were passed
		BEGIN
			delete CG
			from #visibleClientGroups CG left outer join @tempServerGroupIdList SG on CG.entityId = SG.clientGroupId
			Where SG.clientGroupId is null
			IF NOT EXISTS (SELECT TOP 1 1 FROM #visibleClientGroups)	-- If there are no valid Ids left, exit
			BEGIN
				GOTO EXITPROC
			END
		END
	END
	ELSE	--Admin User
	BEGIN
		IF EXISTS (SELECT TOP 1 1 FROM @tempServerGroupIdList)		-- If client group ids are passed, insert them directly
		BEGIN
			insert into #visibleClientGroups
			select DISTINCT SG.clientGroupId from @tempServerGroupIdList SG
		END
		ELSE
		BEGIN
			EXEC sec_getNonIdaObjectsForThisUser  @userId,
												28,			-- CLIENT_GROUP_ENTITY
												0,
												'#visibleClientGroups'
		END
	END
END
ELSE							-- Caller has passed some permissions. Get client groups on whom user has these permissions.
BEGIN
	IF OBJECT_ID('tempdb.dbo.#permissionIdTable') IS NOT NULL
       DROP TABLE #permissionIdTable
    CREATE TABLE #permissionIdTable (permissionId INT)
    IF OBJECT_ID('tempdb.dbo.#getObjectsTable') IS NOT NULL
		DROP TABLE #getObjectsTable
    CREATE TABLE #getObjectsTable (entityId INT, permissionId INT)
	IF @capabilityReqd <> 0
	BEGIN
		INSERT INTO #permissionIdTable
			SELECT id												-- Only user management is split into individual permissions. Other capability Ids are retained.
			FROM UMCapability										-- On checking caller code, user management is not passed. This variable @capabilityReqd will be obsoleted going forward.
			WHERE
				CAST(POWER(2.0,id-1) AS BIGINT) & @capabilityReqd <> 0
	END
	ELSE
	BEGIN
		INSERT INTO #permissionIdTable
			SELECT _ID
			FROM dbo.SplitIDs(@permissionsList)
	END
	EXEC sec_getObjectsWithPermissionsV2 @userId,
										28,			-- CLIENT_GROUP_ENTITY
											0				-- No need to SELECT output. We already have tempTable passed.
	IF @isAndOperation = 1			-- Hold only those client groups on whom user has all the input permissions.
	BEGIN
		DECLARE @noOfInputPermissions INT
		SELECT @noOfInputPermissions = COUNT(permissionId)
		FROM #permissionIdTable
		DELETE
		FROM #getObjectsTable
		WHERE
			entityId IN (
							SELECT entityId
							FROM #getObjectsTable
							GROUP BY entityId
							HAVING
								COUNT(permissionId) < @noOfInputPermissions
						)
	END
	DROP TABLE #permissionIdTable
END
DECLARE @clientGroupList TABLE
    (
      [c_id] INT NOT null,
	  [c_name] NVARCHAR(512),
	  [c_desc] NVARCHAR(max),
      [c_activity] INT NOT null,
      [c_flag] INT NOT null,
      [c_activityControlXml] XML,
	  [c_ownerId] INT ,
	  [c_isOwnerUser] TINYINT,
	  [c_ownerName] NVARCHAR(max),
	  [c_guid] VARCHAR(255),
	  [c_revision] VARCHAR(255),
	  [c_clientGroupUniqueId] INT DEFAULT 0,
	  [c_clientCount] INT DEFAULT 0
    )
IF @noCapsCheck = 1
	INSERT INTO  @clientGroupList
	SELECT distinct
	[c_id] = CLG.id,
	[c_name] = CLG.[name],
	[c_desc] = CLG.[description],
	[c_activity] = 0,
	[c_flag] = CLG.[flag],
	null,
	null,
null,
	null,
	[c_guid] = CLG.[GUID],
	[c_revision] = '',
	[c_clientGroupUniqueId] = CLP.attrVal,
	0
	FROM [APP_ClientGroup] CLG
LEFT JOIN APP_ClientGroupProp CLP ON CLG.ID =CLP.componentNameId AND CLP.attrName = 'Client Group Unique Id' AND CLP.modified = 0
		INNER JOIN #visibleClientGroups sec
			ON CLG.id = Sec.entityId
	WHERE
		( status & 2 <> 2 and (CLG.[flag] & @hideMask = 0) )
ELSE
	INSERT INTO  @clientGroupList
	SELECT distinct
	[c_id] = CLG.id,
	[c_name] = CLG.[name],
	[c_desc] = CLG.[description],
	[c_activity] = 0,
	[c_flag] = CLG.[flag],
	null,
	null,
	null,
	null,
	[c_guid] = CLG.[GUID],
	[c_revision] = '',
	[c_clientGroupUniqueId] = CLP.attrVal,
	0
	FROM [APP_ClientGroup] CLG
LEFT JOIN APP_ClientGroupProp CLP ON CLG.ID =CLP.componentNameId AND CLP.attrName = 'Client Group Unique Id' AND CLP.modified = 0
		INNER JOIN #getObjectsTable sec
			ON CLG.id = Sec.entityId
	WHERE
		( status & 2 <> 2 and (CLG.[flag] & @hideMask = 0) )
IF object_id('tempdb.dbo.#foldersForUser') is not null
	DROP TABLE #foldersForUser
CREATE TABLE #foldersForUser
(
	folderId INT,
	folderName NVARCHAR(255)
)
-- map the folders with the groups
IF object_id('tempdb.dbo.#FoldersWithClientGroups') is not null
	DROP TABLE #FoldersWithClientGroups
CREATE TABLE #FoldersWithClientGroups
(
	folderId INT,
	folderName NVARCHAR(255),
	clientGroupID INT
)
IF object_id('tempdb.dbo.#CGClientCountMap') IS NOT NULL
	DROP TABLE #CGClientCountMap
CREATE TABLE #CGClientCountMap
(
	CGId INT,
	clientCount INT
)
INSERT INTO #CGClientCountMap
SELECT clientGroupId, count(clientId)
FROM APP_ClientGroupAssoc
GROUP BY clientgroupid
UPDATE CGL
SET [C_ClientCount] = ISNULL(CGC.clientCount,0)
FROM @clientGroupList CGL
LEFT JOIN #CGClientCountMap CGC on CGL.C_ID = CGC.CGId
IF @propertyLevel >= 10
begin
    -- get all folders that a user can see
	DECLARE @folders xml
EXEC AppGetSmartFoldersForUser @userId,28,0,@folders OUT
	INSERT INTO #foldersForUser(folderId,folderName)
	SELECT Tbl.col.value('@smartFolderId','int'),
	Tbl.col.value('@smartFolderName','nvarchar(255)')
	FROM @folders.nodes('App_SmartFoldersResp/folder/folderEntity') Tbl(col)
	INSERT INTO #FoldersWithClientGroups
	SELECT FU.folderId, FU.folderName, Assoc.componentId
	FROM #foldersForUser FU
	INNER JOIN App_SmartFolderAssoc Assoc ON
assoc.folderId = FU.folderId AND componentType=28
	UPDATE CG
	SET c_ownerId = CASE WHEN Sec.creatorUserId <> 0 THEN Sec.creatorUserId ELSE Sec.creatorUserGroupId END,
		c_isOwnerUser = CASE WHEN Sec.creatorUserId <> 0 THEN 1 ELSE 0 END
	FROM @clientGroupList CG
		CROSS APPLY
dbo.sec_getCreatorIdForEntity(28, CG.c_id, 0,0,0,0,0,0,0,0) AS Sec
	UPDATE CG
	SET CG.c_ownerName = U.login
	FROM @clientGroupList CG
		INNER JOIN UMUsers U
			ON CG.c_ownerId = U.id
	WHERE
		CG.c_isOwnerUser = 1
	UPDATE CG
	SET CG.c_ownerName = CASE
								WHEN G.umdsProviderId = 0 THEN G.name
								ELSE P.domainName + '\' + G.name
						 END
	FROM @clientGroupList CG
		INNER JOIN UMGroups G
			ON CG.c_ownerId = G.id
		LEFT OUTER JOIN UMDSProviders P
			ON G.umdsProviderId = P.id
	WHERE
		CG.c_isOwnerUser = 0
end
-- Basic properties
/*IF @propertyLevel = 10
BEGIN
	DECLARE @clientGroupACControl TABLE(cgId int,
							   activityType int,
							   enableActivityType int)
	INSERT INTO @clientGroupACControl
	select C.c_id,
        (case JM.opType
          WHEN     4 then    1  -- Backup
          WHEN     5 then    2  -- Restore
          END) ,
         (CASE JM.action
          when 2 then 1
          else 0
         END)
    FROM JMJobAction as JM , @clientGroupList C
    WHERE JM.clientId = 1
          AND JM.mediaAgentID   = 1  -- Always?
          AND JM.appType        = 0
          AND JM.clientGroupId = c.c_id
    UPDATE @clientGroupList SET [c_activityControlXml] =
    (SELECT
     activityType as '@activityType',
     enableActivityType as '@enableActivityType'
     FROM @clientGroupACControl WHERE cgId = c_id
	 FOR XML PATH('activityControlOptions'),TYPE
    )
END*/
IF @propertyLevel >= 10
BEGIN
--UPDATE THE JOB ACTIVITY
UPDATE @clientGroupList SET [c_activity] = ISNULL( (SELECT MyTable.[c_activity]
	FROM (SELECT [clientGroupId], (515- SUM(CASE optype
		WHEN 4 THEN POWER(2,0)
		WHEN 5 THEN POWER(2,1)
		WHEN 57 THEN POWER(2,9)
		ELSE 0 END )) AS [c_activity]
		FROM [JMJobAction] WITH (NOLOCK) WHERE [action] = 1
		GROUP BY [clientGroupId]) AS MyTable
	WHERE [c_id] = MyTable.[clientGroupId]), 515)
	------------------Clientgroup ActivityControl XML-----------------------------------
	DECLARE @ClientGroupActivityControl XML;
	IF OBJECT_ID('tempdb.dbo.#AppGetActivityControlXML_activityEntities_Ext') IS NOT NULL
		DROP TABLE #AppGetActivityControlXML_activityEntities_Ext
	CREATE TABLE #AppGetActivityControlXML_activityEntities_Ext(id INT IDENTITY(1,1),
																commcellId INT,
																clientId INT,
																mediaAgentId INT,
																appTypeId INT,
																clientGroupId INT,
																subclientId INT,
																instanceID INT,
																retXML XML)
	INSERT #AppGetActivityControlXML_activityEntities_Ext(clientGroupId)
	SELECT c_id FROM @clientGroupList
	EXEC [dbo].[APPGetActivityControlXMLV2]
	1,
						@xmlText = @ClientGroupActivityControl OUTPUT;
	UPDATE T
		SET T.c_activityControlXml = S.retXML
	FROM @clientGroupList T
	INNER JOIN #AppGetActivityControlXML_activityEntities_Ext S ON S.clientGroupId = T.c_id
	-------------------------------------------------------------------------------------
END
--fill revision in basic property
UPDATE CG SET c_revision=Prop.stringVal
FROM @clientGroupList CG INNER JOIN App_ComponentProp Prop
ON CG.c_id = Prop.componentId AND Prop.componentType=8
AND propertyTypeId=3316 AND modified = 0  AND dataType =  1
----------------------------
--CONSTRUCT THE XML---------
----------------------------
IF @propertyLevel = 1 --APP::ListPropertyLevels_ListOnly
BEGIN
	SET @xmlText =
			(SELECT		--CLIENTGROUP
					CLG.[c_id] as '@Id', CLG.[c_name] as '@name', CLG.[c_flag] as '@flag'
, (CASE CLG.[c_flag] & 0x0100		WHEN 0 THEN 0 ELSE 1 END) as '@isEDCInstanceAssociated'
, (CASE CLG.[c_flag] & 0x0200			WHEN 0 THEN 0 ELSE 1 END) as '@isEDCInstanceDeleted'
, (CASE CLG.[c_flag] & 0x0400			WHEN 0 THEN 0 ELSE 1 END) as '@isAddinClientGroup'
, (CASE CLG.[c_flag] & 0x0800		WHEN 0 THEN 0 ELSE 1 END) as '@isNetworkThrottleEnabled'
, (CASE CLG.[c_flag] & 0x1000			WHEN 0 THEN 0 ELSE 1 END) as '@isSmartClientGroup'
, (CASE CLG.[c_flag] & 0x4000000		WHEN 0 THEN 0 ELSE 1 END) as '@isDiscoveredClientGroup'
, (CASE CLG.[c_flag] & 0x40000				WHEN 0 THEN 0 ELSE 1 END) as '@isCommcellGroup'
, (CASE WHEN (CLG.[c_flag] & 0x1000) <> 0 THEN 1
WHEN (CLG.[c_flag] & 0x4000000) <> 0 THEN 2
							ELSE 3 END) as '@groupAssocType'
						, CLG.[c_flag] & 0x00FF as '@edcSupportedInstanceType'
						, CLG.[c_guid] as'@guid'
						, CLG.[c_revision] as'@revision'
						, CLG.c_activity as '@activity'
						, CLG.c_clientGroupUniqueId as '@clientGroupUniqueId'
						, CLG.c_clientCount as '@clientCount'
						,(SELECT  ISNULL(ACP.longVal, 0) '@syncStatus'
						FOR XML PATH('discoverRulesInfo'), TYPE)
						,(SELECT CLG.[c_id] as '@clientGroupId', CLG.[c_name] as '@clientGroupName' FOR XML PATH('clientGroup'),TYPE)
			FROM @clientGroupList AS CLG
			LEFT OUTER JOIN APP_ComponentProp ACP
					ON CLG.c_id = ACP.componentId
AND ACP.componentType = 8
						AND ACP.propertyTypeId = 3318
			FOR XML PATH('groups'), ROOT('App_GetServerListResp'))
END
ELSE IF @propertyLevel = 10 --APP::ListPropertyLevels_BASIC
BEGIN
	SET @xmlText =
			(SELECT		--CLIENTGROUP
					CLG.[c_id] as '@Id', CLG.[c_name] as '@name', CLG.[c_desc] as '@description',CLG.[c_flag] as '@flag'
						, (CASE CLG.[c_flag] & 0x0100		WHEN 0 THEN 0 ELSE 1 END) as '@isEDCInstanceAssociated'
						, (CASE CLG.[c_flag] & 0x0200			WHEN 0 THEN 0 ELSE 1 END) as '@isEDCInstanceDeleted'
						, (CASE CLG.[c_flag] & 0x0400			WHEN 0 THEN 0 ELSE 1 END) as '@isAddinClientGroup'
						, (CASE CLG.[c_flag] & 0x0800		WHEN 0 THEN 0 ELSE 1 END) as '@isNetworkThrottleEnabled'
						, (CASE CLG.[c_flag] & 0x1000			WHEN 0 THEN 0 ELSE 1 END) as '@isSmartClientGroup'
, (CASE CLG.[c_flag] & 0x4000000		WHEN 0 THEN 0 ELSE 1 END) as '@isDiscoveredClientGroup'
, (CASE CLG.[c_flag] & 0x40000				WHEN 0 THEN 0 ELSE 1 END) as '@isCommcellGroup'
, (CASE WHEN (CLG.[c_flag] & 0x1000) <> 0 THEN 1
WHEN (CLG.[c_flag] & 0x4000000) <> 0 THEN 2
							ELSE 3 END) as '@groupAssocType'
						, CLG.[c_flag] & 0x00FF as '@edcSupportedInstanceType'
						, CLG.[c_guid] as '@guid'
						, CLG.[c_revision] as '@revision'
						, CLG.c_clientGroupUniqueId as '@clientGroupUniqueId'
						, CLG.c_clientCount as '@clientCount'
						, CLG.[c_activityControlXml] as'clientGroupActivityControl'
						, (SELECT CLG.[c_ownerId] as '@userId', CLG.[c_ownerName] as '@userName' FOR XML PATH('owner'),TYPE)
						, (SELECT topologyName as '@topologyName'
    					FROM APP_FirewallTopology AS fwt WITH (NOLOCK)
    					INNER JOIN APP_FirewallTopologyAssoc AS fwta WITH (NOLOCK)
    					ON fwt.topologyId = fwta.topologyId
    					WHERE fwta.groupId = CLG.[c_id] for XML PATH('topology'), TYPE)
						,(SELECT folderId '@smartFolderId', folderName '@smartFolderName'
						  FROM #FoldersWithClientGroups FCG
						  WHERE FCG.clientGroupId=CLG.[c_id]
						  FOR XML PATH('folders'), TYPE)
						,(SELECT  ISNULL(ACP.longVal, 0) '@syncStatus'
						FOR XML PATH('discoverRulesInfo'), TYPE)
					-- SCGScope Node Start
					,(
						SELECT
							s.entityType '@_type_',
							(
								CASE s.entityType
									WHEN 13 THEN s.entityId     -- USER_ENTITY
									ELSE NULL
								END
							) '@userId',
							(
								CASE s.entityType
									WHEN 15 THEN s.entityId     -- USERGROUP_ENTITY
									ELSE NULL
								END
							) '@userGroupId',
							(
								CASE s.entityType
									WHEN 61 THEN s.entityId     -- PROVIDER_ENTITY
									ELSE NULL
								END
							) '@providerId',
							(
								CASE s.entityType
									WHEN 1 THEN s.entityId      -- COMMCELL_ENTITY
									ELSE NULL
								END
							) '@commCellId',
							(
								CASE s.entityType
									WHEN 13 THEN (      -- USER_ENTITY
											SELECT
												u.login '@entityName'
											FROM UMUsers u WITH(NOLOCK)
											WHERE
												u.id = s.entityId
										)
									ELSE NULL
								END
							) '@userName',
							(
								CASE s.entityType
									WHEN 15 THEN (      -- USERGROUP_ENTITY
										SELECT
											CASE
												WHEN p.id IS NULL THEN g.name
												ELSE p.domainName + '\' + g.name
											END '@entityName'
										FROM UMGroups g WITH(NOLOCK)
											LEFT OUTER JOIN UMDSProviders p ON
												p.id = g.umdsProviderId
												AND g.umdsProviderId > 0
										WHERE
											g.id = s.entityId
										)
									ELSE NULL
								END
							) '@userGroupName',
							(
								CASE s.entityType
									WHEN 61 THEN (      -- PROVIDER_ENTITY
										SELECT
											p.domainName
										FROM UMDSProviders p WITH(NOLOCK)
										WHERE
											p.id = s.entityId
										)
									ELSE NULL
								END
							) '@providerDomainName',
							(
								CASE s.entityType
									WHEN 1 THEN (       -- COMMCELL_ENTITY
											SELECT
												cc.aliasName
											FROM APP_Commcell cc WITH(NOLOCK)
											WHERE
												cc.id = s.entityId
										)
									ELSE NULL
								END
							) '@commCellName'
						FROM APP_SCGScope s WITH(NOLOCK)
						WHERE
							r.id IS NOT NULL
							AND s.scgRuleId = r.id
						FOR XML PATH('entity'), ROOT('scgScope'), TYPE
					)
					-- SCGScope Node End
			FROM @clientGroupList AS CLG
			LEFT OUTER JOIN APP_ComponentProp ACP
					ON CLG.c_id = ACP.componentId
AND ACP.componentType = 8
						AND ACP.propertyTypeId = 3318
			LEFT OUTER JOIN APP_SCGRule r WITH(NOLOCK) ON
				r.scgId = CLG.c_id
			FOR XML PATH('groups'), ROOT('App_GetServerListResp'))
END
ELSE
BEGIN
SET @xmlText =
		(SELECT		--CLIENTGROUP
				CLG.[c_id] as '@Id', CLG.[c_name] as '@name' , CLG.[c_desc] as '@description', CLG.[c_activity] as '@activity',
				CLG.[c_flag] as '@flag'
, (CASE CLG.[c_flag] & 0x0100		WHEN 0 THEN 0 ELSE 1 END) as '@isEDCInstanceAssociated'
, (CASE CLG.[c_flag] & 0x0200			WHEN 0 THEN 0 ELSE 1 END) as '@isEDCInstanceDeleted'
, (CASE CLG.[c_flag] & 0x0400			WHEN 0 THEN 0 ELSE 1 END) as '@isAddinClientGroup'
, (CASE CLG.[c_flag] & 0x0800		WHEN 0 THEN 0 ELSE 1 END) as '@isNetworkThrottleEnabled'
, (CASE CLG.[c_flag] & 0x1000			WHEN 0 THEN 0 ELSE 1 END) as '@isSmartClientGroup'
, (CASE CLG.[c_flag] & 0x4000000		WHEN 0 THEN 0 ELSE 1 END) as '@isDiscoveredClientGroup'
, (CASE WHEN (CLG.[c_flag] & 0x1000) <> 0 THEN 1
WHEN (CLG.[c_flag] & 0x4000000) <> 0 THEN 2
							ELSE 3 END) as '@groupAssocType'
					, CLG.[c_flag] & 0x00FF as '@edcSupportedInstanceType'
					, CLG.[c_guid] as '@guid'
					, CLG.[c_revision] as '@revision'
					, CLG.c_clientGroupUniqueId as '@clientGroupUniqueId'
					, CLG.c_clientCount as '@clientCount'
					, CLG.[c_activityControlXml] as'clientGroupActivityControl'
					, (SELECT CLG.[c_ownerId] as '@userId', CLG.[c_ownerName] as '@userName' FOR XML PATH('owner'),TYPE)
				  , (SELECT topologyName as '@topologyName'
    					FROM APP_FirewallTopology AS fwt WITH (NOLOCK)
    					INNER JOIN APP_FirewallTopologyAssoc AS fwta WITH (NOLOCK)
    					ON fwt.topologyId = fwta.topologyId
    					WHERE fwta.groupId = CLG.[c_id] for XML PATH('topology'), TYPE)
					, (SELECT  --CLIENT
					CLA.[clientId] '@clientId', CL.[name] '@name'
					FROM APP_ClientGroupAssoc as CLA, [APP_Client] AS CL
					Where  CLA.[clientGroupId] = CLG.[c_id] AND CL.[id] = CLA.[clientId]
					FOR XML PATH('clients'), TYPE)
					,(SELECT folderId '@smartFolderId', folderName '@smartFolderName'
						  FROM #FoldersWithClientGroups FCG
						  WHERE FCG.clientGroupId=CLG.[c_id]
						  FOR XML PATH('folders'), TYPE)
					,(SELECT  ISNULL(ACP.longVal, 0) '@syncStatus'
					 FOR XML PATH('discoverRulesInfo'), TYPE)
					-- SCGScope Node Start
					,(
						SELECT
							s.entityType '@_type_',
							(
								CASE s.entityType
									WHEN 13 THEN s.entityId     -- USER_ENTITY
									ELSE NULL
								END
							) '@userId',
							(
								CASE s.entityType
									WHEN 15 THEN s.entityId     -- USERGROUP_ENTITY
									ELSE NULL
								END
							) '@userGroupId',
							(
								CASE s.entityType
									WHEN 61 THEN s.entityId     -- PROVIDER_ENTITY
									ELSE NULL
								END
							) '@providerId',
							(
								CASE s.entityType
									WHEN 1 THEN s.entityId      -- COMMCELL_ENTITY
									ELSE NULL
								END
							) '@commCellId',
							(
								CASE s.entityType
									WHEN 13 THEN (      -- USER_ENTITY
											SELECT
												CASE
													WHEN p.id IS NULL AND u.name <> '' THEN u.name
													WHEN p.id IS NOT NULL AND u.name <> '' THEN p.domainName + '\' + u.name
													ELSE u.login
												END '@entityName'
											FROM UMUsers u WITH(NOLOCK)
												LEFT OUTER JOIN UMDSProviders p ON
													p.id = u.umdsProviderId
													AND u.umdsProviderId > 0
											WHERE
												u.id = s.entityId
										)
									ELSE NULL
								END
							) '@userName',
							(
								CASE s.entityType
									WHEN 15 THEN (      -- USERGROUP_ENTITY
										SELECT
											CASE
												WHEN p.id IS NULL THEN g.name
												ELSE p.domainName + '\' + g.name
											END '@entityName'
										FROM UMGroups g WITH(NOLOCK)
											LEFT OUTER JOIN UMDSProviders p ON
												p.id = g.umdsProviderId
												AND g.umdsProviderId > 0
										WHERE
											g.id = s.entityId
										)
									ELSE NULL
								END
							) '@userGroupName',
							(
								CASE s.entityType
									WHEN 61 THEN (      -- PROVIDER_ENTITY
										SELECT
											p.domainName
										FROM UMDSProviders p WITH(NOLOCK)
										WHERE
											p.id = s.entityId
										)
									ELSE NULL
								END
							) '@providerDomainName',
							(
								CASE s.entityType
									WHEN 1 THEN (       -- COMMCELL_ENTITY
											SELECT
												cc.aliasName
											FROM APP_Commcell cc WITH(NOLOCK)
											WHERE
												cc.id = s.entityId
										)
									ELSE NULL
								END
							) '@commCellName'
						FROM APP_SCGScope s WITH(NOLOCK)
						WHERE
							r.id IS NOT NULL
							AND s.scgRuleId = r.id
						FOR XML PATH('entity'), ROOT('scgScope'), TYPE
					)
					-- SCGScope Node End
		FROM @clientGroupList AS CLG
			LEFT OUTER JOIN APP_ComponentProp ACP
					ON CLG.c_id = ACP.componentId
AND ACP.componentType = 8
						AND ACP.propertyTypeId = 3318
			LEFT OUTER JOIN APP_SCGRule r WITH(NOLOCK) ON
				r.scgId = CLG.c_id
		FOR XML PATH('groups'), ROOT('App_GetServerListResp'))
END
EXITPROC:
IF OBJECT_ID ('tempdb.dbo.#visibleClientGroups') IS NOT NULL
		DROP TABLE #visibleClientGroups
IF OBJECT_ID('tempdb.dbo.#getObjectsTable') IS NOT NULL
		DROP TABLE #getObjectsTable
IF object_id('tempdb.dbo.#FoldersWithClientGroups') is not null
	   DROP TABLE #FoldersWithClientGroups
IF object_id('tempdb.dbo.#foldersForUser') is not null
	   DROP TABLE #foldersForUser
SET @oXml = ISNULL(@xmlText, '<App_GetServerListResp/>')
END
GO

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

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

insert into GXDBVersions values(2, 'AppGetServerListWithoutSelect',  'v1.1.4.18.8.1', 'AppGetServerListWithoutSelect', 'v1.1.4.18.8.1')
GO

