--@querytype    CSV
--Name:- Endusers
--Description:- Collect various endusers 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 = 252

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery252_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_Enduser_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIP_Enduser_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_Enduser_CollectionTime'

	--On private metrics servers - collect this data through activity till v11SP19. For v11SP20 and above, even if activity is checked, the collection should be controlled only by spbilling checkbox	 
	IF ((@isPrivateRunning = 'Metrics Reporting') or (@isPrivateRunning = 'Metrics Direct Dip') OR  (@frequencyMode ='1') OR  (@frequencyMode ='3'))
	BEGIN
		DECLARE @release INT = 0, @sp INT = 0, @spBillingEnabled INT = 0
		SELECT @release = MAX(id) FROM simAllGalaxyRel
		SELECT @sp = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2 
		SELECT @spBillingEnabled = isEnabled&2 from CloudMonitoringService WHERE id = 9
		IF @release >= 16 AND @sp >= 20--check if CS is on v11 sp20 or above
		BEGIN
			IF @spBillingEnabled <> 2--check if spbilling is enabled
				GOTO END_SURVEY--do not collect anything if spbilling checkbox is disabled
		END
	END

	IF OBJECT_ID('tempdb..#tempUsers') IS NOT NULL
    DROP TABLE #tempUsers

	CREATE TABLE #tempUsers(userId INT,userName NVARCHAR(1024),userLogin NVARCHAR(1024),userEmail NVARCHAR(1024),userType INT,reportName NVARCHAR(1024),associationType NVARCHAR(MAX),clientId INT)

	IF (OBJECT_ID('RptEndPointUserDetails') IS NOT NULL)
	BEGIN
		INSERT #tempUsers
		EXEC RptEndPointUserDetails 4
	END

	IF EXISTS (SELECT 1 FROM #tempUsers)
	BEGIN
		SELECT DISTINCT userId AS UserId,
		CASE WHEN userName IS NOT NULL AND CHARINDEX(',', userName)>0 THEN REPLACE (userName,',','<COMMA>') 
			 WHEN userName IS NULL OR userName='' THEN 'N/A'
			 ELSE userName
		END AS UserName, 
		CASE WHEN userLogin IS NOT NULL AND CHARINDEX(',', userLogin)>0 THEN REPLACE (userLogin,',','<COMMA>') 
			 WHEN userLogin IS NULL OR userLogin='' THEN 'N/A'
			 ELSE userLogin
		END AS UserLogin,
		CASE WHEN userEmail IS NOT NULL AND CHARINDEX(',', userEmail)>0 THEN REPLACE (userEmail,',','<COMMA>') 
			 WHEN userEmail IS NULL OR userEmail='' THEN 'N/A'
			 ELSE userEmail
		END AS UserEmail,
		userType AS UserType,
		CASE WHEN associationType IS NOT NULL AND CHARINDEX(',', associationType)>0 THEN REPLACE (associationType,',','<COMMA>') 
			 WHEN associationType IS NULL OR associationType='' THEN 'N/A'
			 ELSE associationType
		END AS Associations
		FROM #tempUsers
	END
	 	 
	IF OBJECT_ID('tempdb..#tempUsers') IS NOT NULL DROP TABLE #tempUsers

	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 @tempArgument NVARCHAR(1024)
	set @tempArgument = CAST(dbo.getunixtime(GETDATE()) AS NVARCHAR(20))
	EXEC SetSurveyParamValue @TIME_STAMP_NAME, @tempArgument
END
ELSE
BEGIN
	UPDATE GXGlobalParam SET value = CAST(dbo.getunixtime(GETDATE()) AS NVARCHAR(20)) WHERE name = @TIME_STAMP_NAME
END

END_SURVEY:

SET NOCOUNT OFF