--@querytype 	CSV
--Name:- Client Tenant Association

--------- BEGIN SURVEY QUERY ---------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('App_CompanyEntities', 'U') IS NULL
BEGIN
	RETURN
END

DECLARE @collectClientGroupAssociations INT = 0
DECLARE @isPrivateRunning varchar(256) = '';

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
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

DECLARE	@sqlStr NVARCHAR(MAX)

SET @sqlStr = '
SELECT	CG.id AS ClientGroupId, E.entityId AS ClientId
FROM	App_CompanyEntities E WITH (NOLOCK)
		INNER JOIN UMDSProviders P WITH (NOLOCK) ON E.companyId = P.id
		INNER JOIN App_CompanyProp CP WITH (NOLOCK) ON E.companyId = CP.componentNameId
		AND CP.attrName = N''Associated Smart Client Group''
		AND CP.cs_attrName = CHECKSUM(''Associated Smart Client Group'')
		AND CP.modified = 0
		INNER JOIN APP_ClientGroup CG WITH (NOLOCK) ON CP.attrValInt = CG.id
WHERE	E.entityType = 3'
	
EXEC sp_executesql @sqlStr

--------- END SURVEY QUERY ---------
SET NOCOUNT OFF