--@querytype    CSV
--Name:- Tenants
--Description:- Collect various tenants in a commcell

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRY
        
	DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
	DECLARE @queryId AS INTEGER = 251

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery251_LastCollectionTime'
	DECLARE @isPrivateRunning VARCHAR(256)='';
	DECLARE @frequencyMode   NVARCHAR(MAX) ='0';
	DECLARE @sqlQuery NVARCHAR(MAX) = '';

	IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
		SELECT @frequencyMode = mode FROM #MetricsInputParams

	IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
		SELECT @isPrivateRunning = value FROM GXGlobalParam WITH (NOLOCK) WHERE name ='CommservSurveyRunning'	

	IF ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode = '1'))
		SET @TIME_STAMP_NAME = 'CommservSurveyPrivate_Tenant_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIP_Tenant_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_Tenant_CollectionTime'

	IF (OBJECT_ID('UMDSProviders') IS NOT NULL AND OBJECT_ID('App_CompanyProp') IS NOT NULL)
	BEGIN
		SET @sqlQuery = N'
			SELECT  U.id AS TenantID,
			U.GUID AS TenantGUID, 
			CASE WHEN domainName IS NOT NULL AND CHARINDEX('','', domainName)>0 THEN REPLACE (domainName,'','',''<COMMA>'') 
				 WHEN domainName IS NULL OR domainName='''' THEN ''N/A''
				 ELSE domainName
			END AS ''TenantName'', 
			U.flags,
			C.attrVal AS SignUpDate,
			CASE WHEN C2.attrVal is NOT NULL THEN C2.attrVal ELSE 0
				 END AS DeactivatedDate,
			0 AS DeletedDate,
			G.id AS ClientGroupId,
			Max(UM.lastLogInTime) as lastLogInTime
			FROM UMDSProviders U WITH (NOLOCK)
			INNER JOIN App_CompanyProp C WITH(NOLOCK) 
		        ON U.id = C.componentNameId
		        AND U.servicetype = 5
		        AND U.enabled = 1
		        AND C.attrName = ''Creation Time''
		        AND C.modified = 0
		    INNER JOIN App_CompanyProp C1 WITH(NOLOCK) 
		        ON U.id = C1.componentNameId
		        AND C1.attrName = ''Associated Smart Client Group''
		        AND C1.modified = 0
			LEFT JOIN App_CompanyProp C2 WITH(NOLOCK) 
                ON U.id = C2.componentNameId
                AND C2.attrName = ''Deactivation time''
                AND C2.modified = 0
			INNER JOIN APP_ClientGroup G WITH(NOLOCK) 
					ON CAST(G.id AS NVARCHAR(MAX)) = C1.attrVal
			INNER JOIN  UMUSERS UM WITH(NOLOCK)
					ON UM.umDSProviderId = U.ID
			GROUP BY U.id,U.GUID,domainName,U.flags,C.attrVal,C2.attrVal,G.id
		'
	END
	
	EXEC sp_executesql @sqlQuery

	IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
	BEGIN
		DECLARE @lastcollectionTimeSTR VARCHAR(256) = '';
		EXEC GetSurveyParamValue @TIME_STAMP_NAME, @lastcollectionTimeSTR OUTPUT 
		IF @lastcollectionTimeSTR = ''
			BEGIN
			EXEC SetSurveyParamValue @TIME_STAMP_NAME, ''
			END
	END	
	ELSE
	BEGIN
		DECLARE @DO_IT VARCHAR(256)
		IF NOT EXISTS (SELECT 1 FROM GXGlobalParam WITH(NOLOCK) WHERE name = @TIME_STAMP_NAME)
		BEGIN
			IF EXISTS (SELECT 1 FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
				INSERT INTO GXGlobalParam (name, value) SELECT @TIME_STAMP_NAME, ''
			ELSE
			BEGIN
				SET @DO_IT = 'INSERT INTO GXGlobalParam (name, value, created, modified) VALUES('''
				 + @TIME_STAMP_NAME
				 + ''', '''','
				 + CONVERT(varchar(20), @logDate)                   -- Use Proper "create" date!
				 + ',0)'
				EXEC (@DO_IT)
			END
		END  
	END

END TRY
BEGIN CATCH  
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH   

IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @tempTimeStamp NVARCHAR(256)
	set @tempTimeStamp = CAST(dbo.getunixtime(GETDATE()) AS NVARCHAR(20))
	EXEC SetSurveyParamValue @TIME_STAMP_NAME, @tempTimeStamp
END
ELSE
BEGIN	
	UPDATE GXGlobalParam SET value = CAST(dbo.getunixtime(GETDATE()) AS NVARCHAR(20)) WHERE name = @TIME_STAMP_NAME
END

SET NOCOUNT OFF