--@querytype    CSV
--Name:- FileAnomalies

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 = 15
    SELECT  @csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

IF @csReleaseId < 15 OR @csReleaseId = 15 AND @csSPVersion < 7
BEGIN
	RETURN
END

DECLARE @freequencyMode	 NVARCHAR(MAX) = '0'
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
	SELECT @freequencyMode = mode from #MetricsInputParams

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 (@freequencyMode = '1'))
	SET @gxGlobalParamKey = 'CommservSurveyPrivateLastCollectedFileAnomalyTimestamp'
else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@freequencyMode = '3'))
	SET @gxGlobalParamKey = 'CommservSurveyDirectDipLastCollectedFileAnomalyTimestamp'
ELSE
	SET @gxGlobalParamKey = 'CommservSurveyPublicLastCollectedFileAnomalyTimestamp'

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @lastcollectionTimeSTR VARCHAR(256)='';
	EXEC GetSurveyParamValue @gxGlobalParamKey, @lastcollectionTimeSTR OUTPUT 
	IF @lastcollectionTimeSTR <> ''
		SELECT @lastcollectionTime = CONVERT(DATETIME, @lastcollectionTimeSTR)
END
ELSE
BEGIN
	BEGIN TRY
		SELECT @lastCollectionTime = CONVERT(DATETIME, CAST(value AS VARCHAR)) FROM GXGlobalParam WHERE name = @gxGlobalParamKey
	END TRY
	BEGIN CATCH  
	END CATCH
END
IF @lastCollectionTime IS NULL
BEGIN
	SET @lastCollectionTime = DATEADD(DAY, -7, GETDATE())	
	SET @isGxGlobalParamKeyPresent = 0
END
ELSE
	SET @isGxGlobalParamKeyPresent = 1

DECLARE @t TABLE(name NVARCHAR(100), val INT, clientid INT, detected INT)

INSERT INTO @t (name, val,clientid,detected)
	SELECT attrName,attrVal,componentnameid, created FROM app_clientprop 
	WHERE attrname IN ('Anomaly Flag','Anomaly created Files','Anomaly renamed Files','Anomaly modified Files','Anomaly deleted Files')
	AND modified = 0

DECLARE @tt TABLE(ClientId INT, AnomalyFlag INT, createdFiles INT, RenameFiles INT, deletedFiles INT, modifiedFiles INT, Detected INT)

INSERT INTO @tt (ClientId,AnomalyFlag,createdFiles,RenameFiles,deletedFiles, modifiedFiles)
	SELECT * FROM
	(
	SELECT ClientId,name,val
	FROM @t
 
	)src
	PIVOT
	(
	AVG(val)
	FOR name IN ([Anomaly Flag],[Anomaly created Files],[Anomaly renamed Files],[Anomaly deleted Files],[Anomaly modified Files])
	)piv1


UPDATE @tt
    SET detected = T2.detected
    FROM @tt T 
        INNER JOIN
			(SELECT ClientId, MAX(detected) detected
			FROM @t GROUP BY ClientId)  T2
         ON T.ClientId = T2.ClientId
 
DECLARE @pre varchar(100) = 'Many files were '

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

	
SELECT ClientId AS '@clientId',
			   CASE AnomalyFlag 
					WHEN 1  THEN @pre+'created.'
					WHEN 2 THEN @pre+'renamed.'
					WHEN 3 THEN @pre+'created and renamed'
					WHEN 4 THEN @pre+'modified'
					WHEN 5 THEN @pre+'modified and created'
					WHEN 6 THEN @pre+'modified and renamed.'
					WHEN 7 THEN @pre+'modified, Create and renamed.'
					WHEN 8 THEN @pre+'deleted.'
					WHEN 9 THEN @pre+'deleted and created.'
					WHEN 10 THEN @pre+'deleted and renamed.'
					WHEN 11 THEN @pre+'deleted, created and renamed.'
					WHEN 12 THEN @pre+'deleted and modified.'
					WHEN 13 THEN @pre+'deleted, modified and created.'
					WHEN 14 THEN @pre+'deleted, modified and renamed.'
					WHEN 15 THEN @pre+'deleted, modified, renamed and created.'
				END AS '@anomalyType',
				createdFiles AS '@createdFiles',
				RenameFiles AS '@renameFiles',
				deletedFiles AS '@deletedFiles',
				modifiedFiles AS '@modifiedFiles',
				DATEADD(s, Detected, '19700101') AS '@detectedTime'
		FROM @tt WHERE Detected > dbo.getunixtime(@lastCollectionTime)
		

SET NOCOUNT OFF

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