--@querytype    CSV
--Name:- DDBAnomalies

SET NOCOUNT ON


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


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

declare @lastCollectionTime DATETIME = NULL, @gxGlobalParamKey varchar(65), @isGxGlobalParamKeyPresent tinyint
DECLARE @isPrivateRunning varchar(256)='';

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

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

DECLARE @frequencyMode	 NVARCHAR(MAX) = '0'

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


IF @csReleaseId > 16 OR @csReleaseId = 16 AND @csSPVersion >= 17
BEGIN

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

	IF (@isPrivateRunning = 'Metrics Reporting' OR (@frequencyMode = '1'))
		SET @gxGlobalParamKey = 'CommservSurveyPrivateLastCollectedDDBAnomalyTimestamp'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @gxGlobalParamKey = 'CommservSurveyDirectDipLastCollectedDDBAnomalyTimestamp'
	ELSE
		SET @gxGlobalParamKey = 'CommservSurveyPublicLastCollectedDDBAnomalyTimestamp'
BEGIN TRY
	IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
	BEGIN
		DECLARE @lastcollectionTimeSTR VARCHAR(256) = '';
		EXEC GetSurveyParamValue @gxGlobalParamKey, @lastcollectionTimeSTR OUTPUT
		SELECT @lastCollectionTime = CONVERT(DATETIME, @lastcollectionTimeSTR)
	END
	ELSE
	BEGIN
		SELECT @lastCollectionTime = CONVERT(DATETIME, CAST(value AS VARCHAR)) FROM GXGlobalParam WHERE name = @gxGlobalParamKey
	END
END TRY
BEGIN CATCH  
END CATCH
	IF @lastCollectionTime IS NULL
	BEGIN
		SET @lastCollectionTime = DATEADD(DAY, -7, GETDATE())	
		SET @isGxGlobalParamKeyPresent = 0
	END
	ELSE
		SET @isGxGlobalParamKeyPresent = 1


	SELECT SIDBStoreId, 
		LogDateUTC AS DetectedTimeUTC, 
		ISNULL(ZeroRefCount, 0) AS ZeroRefCount, 
		ISNULL(DeletedAFCount, 0) AS DeletedAFCount
	FROM (SELECT SIDBStoreId,
			LogdateUTC ,
			AnomalyType ,
			AnomalyValue 
	FROM DeDupPerformanaceAnomaly WHERE logdateUTC > @lastCollectionTime) src
	PIVOT 
	(
		MAX(anomalyValue) FOR anomalyType IN ([ZeroRefCount], [DeletedAFCount])
	) pvt
			
	

	IF @isGxGlobalParamKeyPresent = 0
	BEGIN
		IF OBJECT_ID('SetSurveyParamValue') IS NULL
		BEGIN
			IF @isPrivateRunning = 'Metrics Reporting'
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''CommservSurveyPrivateLastCollectedDDBAnomalyTimestamp'', '''', 0, 0')
			ELSE
				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''CommservSurveyPublicLastCollectedDDBAnomalyTimestamp'', '''', 0, 0')
		END
	END
	
	IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
	BEGIN
		DECLARE @tempTimeStamp NVARCHAR(20)
		set @tempTimeStamp = CONVERT(VARCHAR(20),GETDATE(),120)
		EXEC SetSurveyParamValue @gxGlobalParamKey, @tempTimeStamp
	END
	ELSE
	BEGIN
		UPDATE GXGlobalParam
		SET value = CONVERT(VARCHAR(20),GETDATE(),120)
		WHERE name = @gxGlobalParamKey
	END
END

SET NOCOUNT OFF

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