--- Please follow the below comments to insert SQL statements.
-- Client version query
-- In public cloud, this query should go to Chargeback folder.
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 151
DECLARE @surveyXML NVARCHAR(MAX)
DECLARE @collectClientGroupAssociations AS INTEGER = 0

DECLARE @isPrivateRunning varchar(256)='';

--------- BEGIN SURVEY QUERY ---------

DECLARE	@csReleaseId INT = 0
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE @csSPVersion INT = 0
IF @csReleaseId = 15
	SELECT	@csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
    SELECT @isPrivateRunning = value from GXGlobalParam where name ='CommservSurveyRunning'
END

IF OBJECT_ID('CloudMonitoringService') IS NOT NULL
   AND (@csReleaseId < 15 OR @csReleaseId = 15 AND @csSPVersion < 7)
BEGIN
	IF (@isPrivateRunning IN ('Metrics Reporting', 'Metrics Direct Dip'))
	BEGIN
		select @collectClientGroupAssociations = 1
	END
	ELSE
	IF EXISTS (select * from CloudMonitoringService where name IN ('Activity', 'ChargeBack', 'HealthCheck') and isEnabled&1 = 1)
	BEGIN
		select @collectClientGroupAssociations = 1
	END
END

IF OBJECT_ID('tempdb.dbo.#Tenant')      IS NOT null DROP TABLE #Tenant
IF OBJECT_ID('tempdb.dbo.#TenantUser')      IS NOT null DROP TABLE #TenantUser
CREATE TABLE #Tenant (clientGroupId INT, tenantName NVARCHAR(256), umdsProviderId INT)
CREATE TABLE #TenantUser (clientGroupId INT, userGUID NVARCHAR(256), loginName NVARCHAR(256), email NVARCHAR(256))

declare @tenantInsert nvarchar(max) = 	'
IF OBJECT_ID(''App_CompanyProp'') IS not NULL
INSERT	INTO #Tenant
	SELECT	G.id, P.domainName, P.id
	FROM	UMDSProviders P
			INNER JOIN App_CompanyProp C ON C.componentNameId = P.id 
			INNER JOIN APP_ClientGroup G ON CAST(G.id AS NVARCHAR(MAX)) = C.attrVal
	WHERE	P.enabled = 1 AND P.serviceType = 5
		AND C.attrName = ''Associated Smart Client Group'' AND C.modified = 0'

exec sp_executeSQl @tenantInsert

INSERT INTO #TenantUser
SELECT      T.clientGroupId,
            U.userGuid,
            U.login,
            ''
        FROM #Tenant T
            INNER JOIN UMUsers U WITH (NOLOCK) ON T.umdsProviderId = U.umdsProviderId AND U.enabled = 1
        WHERE U.userGuid IS NOT NULL AND U.userGuid <> ''
    
IF(@collectClientGroupAssociations > 0)
BEGIN
	SET @surveyXML = (SELECT
		-- Client Group
		(SELECT	
			CG.id   AS '@clientGroupId', 
			CG.name AS '@clientGroupName', 
			CG.flag AS '@flag', 
			T.tenantName AS '@tenantName'
		FROM APP_ClientGroup CG
			LEFT OUTER JOIN #Tenant T ON CG.id = T.clientGroupId
		FOR XML PATH('ClientGroup'), TYPE),

		-- Client Group Association
		(SELECT	
			clientGroupId AS '@clientGroupId',
			clientId AS '@clientId'
		FROM APP_ClientGroupAssoc
		FOR XML PATH('Association'), TYPE),

        -- User Organization Association (Associated Smart Client Group)
        (SELECT 
            T.clientGroupId AS '@clientGroupId',
            T.userGuid AS '@userGUID',
            T.loginName AS '@userName',
            T.email AS '@userEmail'
        FROM #TenantUser T
        FOR XML PATH('TenantUser'), TYPE)
		
	FOR XML PATH ('ClientGroupAssociation')
	-- Replace <<RESULTS_NAME>> with specific name
	)
END
ELSE
BEGIN
	SET @surveyXML = (SELECT
		-- Client Group
		(SELECT	
			CG.id   AS '@clientGroupId', 
			CG.name AS '@clientGroupName', 
			CG.flag AS '@flag', 
			T.tenantName AS '@tenantName'
		FROM APP_ClientGroup CG
			LEFT OUTER JOIN #Tenant T ON CG.id = T.clientGroupId
		FOR XML PATH('ClientGroup'), TYPE),

        -- User Organization Association (Associated Smart Client Group)
        (SELECT 
            T.clientGroupId AS '@clientGroupId',
            T.userGuid AS '@userGUID',
            T.loginName AS '@userName',
            T.email AS '@userEmail'
        FROM #TenantUser T
        FOR XML PATH('TenantUser'), TYPE)

	FOR XML PATH ('ClientGroupAssociation')
	-- Replace <<RESULTS_NAME>> with specific name
	)
END

DROP TABLE #Tenant
IF OBJECT_ID('tempdb.dbo.#TenantUser')      IS NOT null DROP TABLE #TenantUser
--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)
SET NOCOUNT OFF

--------- END - GENERATED CODE ---------