--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON
BEGIN TRY

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 178
DECLARE @surveyXML NVARCHAR(MAX)

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

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

--------- Insert your SQL statements here 
-- Query Name: Backup Job Statistics Operation
-- Description: Backup Success and failure statistics


DECLARE	@TimeZoneBias	INT = DATEDIFF(SECOND, GETUTCDATE(), GETDATE())
DECLARE @csTimeZone    NVARCHAR(1024) = ''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END 

DECLARE @nreleaseId INT =1
SET @nreleaseId =(SELECT releaseId from APP_Client WITH (NOLOCK) where id =2)

DECLARE @LastCollectionAttrName VARCHAR(256)

-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @frequencyMode = mode from #MetricsInputParams
END
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'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyPrivateSpecialClientGroup'
	SET @LastCollectionAttrName = 'CommservSurveyPrivateLastDataRetentionCollectionTime'
END
else IF ((@isPrivateRunning = 'Metrics Direct Dip')OR  (@frequencyMode ='3'))
BEGIN
	SET @LastCollectionAttrName = 'CommservSurveyDirectDIPLastDataRetentionCollectionTime'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveySpecialClientGroup'
	SET @LastCollectionAttrName = 'CommservSurveyPublicLastDataRetentionCollectionTime'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        

SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
  
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
	SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
	if(@groupId > 0)
	BEGIN
		insert into @includeGroups values(@groupId);
		DECLARE @tmpStr NVARCHAR(100);
		SELECT @tmpStr = NAME FROM APP_ClientGroup WITH (NOLOCK) WHERE id = @groupId;
		IF(@tmpStr IS NOT NULL)
			SET @groupStrName = @groupStrName + ', ' + @tmpStr
	END
	SET @startIndex = @endIndex + 1
	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0
DECLARE @includeClients TABLE (clientId INT);

IF EXISTS (SELECT id FROM app_clientgroup WITH (NOLOCK) join @includeGroups on id=groupID)
BEGIN
	SET @specialGroupExists =1
	
	INSERT INTO @includeClients
	SELECT DISTINCT CGA.clientId 
	FROM @includeGroups CG INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientGroupId = CG.groupID	
END

IF object_id('tempdb.dbo.#ExcludedAppIds') is not null DROP TABLE #ExcludedAppIds 
CREATE TABLE #ExcludedAppIds (appId INT)

INSERT INTO #ExcludedAppIds
SELECT componentNameId FROM APP_subclientProp WITH (NOLOCK) 
WHERE attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID') 
  AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID'))
  AND attrVal <> '0' AND modified = 0
UNION
SELECT id FROM APP_Application WITH (NOLOCK) 
WHERE clientId IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1' AND modified = 0)

-- Get the last collection time
DECLARE @CurrCollectionDate DATETIME = GETDATE()
DECLARE @PrevCollectionDate DATETIME

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @PrevCollectionDateSTR VARCHAR(256) = '';
	EXEC GetSurveyParamValue @LastCollectionAttrName, @PrevCollectionDateSTR OUTPUT 
	IF @PrevCollectionDateSTR = ''
	BEGIN
		IF	DAY(GETDATE()) > 16
			SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
		ELSE
			SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
	END
	ELSE
		SELECT @PrevCollectionDate = CAST(@PrevCollectionDateSTR AS DATETIME)
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
	BEGIN
		BEGIN TRY 
			SELECT @PrevCollectionDate = CAST(CAST(value AS NVARCHAR(256)) AS DATETIME) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName 
		END TRY 
		BEGIN CATCH 
			IF	DAY(GETDATE()) > 16
				SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
			ELSE
				SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
		END CATCH
	END
	ELSE
	BEGIN
		IF	DAY(GETDATE()) > 16
			SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
		ELSE
			SET @PrevCollectionDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
	END
END

DECLARE @fromDt DATETIME = @PrevCollectionDate

IF OBJECT_ID('IsInvalidTimeManaged') IS NOT NULL
BEGIN
	WHILE dbo.IsInvalidTimeManaged(@fromDt, @csTimeZone) = 1
	BEGIN
		SET @fromDt = DATEADD(HOUR, 1, @fromDt)
	END
END

DECLARE @FromTime INT = dbo.getunixtime(dbo.LocalToUTCTime(@fromDt, @csTimeZone))
DECLARE @startDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @CurrCollectionDate), 0)
DECLARE @endDate  DATETIME = DATEADD(DAY, 1, @startDate)

--select @startDate AS '@startDate', @endDate AS '@endDate', @PrevCollectionDate AS '@PrevCollectionDate'

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

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

CREATE TABLE #SPBasicRetention (
		PolicyId INT,
        PolicyName NVARCHAR(144),
        RetentionDays INT,
        ExtRretentionRules INT,
        IsSnapCopy INT,
        IsDefaultCopy INT,
        copyType INT,
		SelectiveCopyType INT
		)


INSERT INTO #SPBasicRetention
SELECT AG.id, AG.name, AR.RetentionDays,
        ISNULL(ARE.ExtRretentionRules, 0), 
		AGC.isSnapCopy, 
		CASE WHEN AGC.id IN (AG.defaultCopy, AG.defaultSnapCopy) THEN 1 ELSE 0 END,
        AGC.type, 
		ISNULL(SEL.type, 0)
FROM archGroup AG WITH (NOLOCK)
        INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AGC.archGroupId = AG.id AND AG.type IN (1, 2) AND AGC.type IN (1, 2)
        LEFT OUTER JOIN archAgingRule AR WITH (NOLOCK) ON AR.copyId = AGC.id
        LEFT OUTER JOIN (
            SELECT copyId, SUM(retentionRule) AS extRretentionRules
            FROM archAgingRuleExtended WITH (NOLOCK)
            GROUP BY copyId) ARE ON ARE.copyId = AGC.id
		LEFT OUTER JOIN archSelectiveCopy SEL
			ON SEL.copyId = AGC.id


CREATE TABLE #SPInfo (        
    PolicyId INT,
    PolicyName NVARCHAR(144),
    PrimRetentionDays INT,
    PrimExtWeekRetDays INT,
    SyncRetentionDays INT,
    SelWeekRetentionDays INT,
    SelMonRetentionDays INT,
	LongestRetentionDays INT
    )
	
--Primary Copies
INSERT INTO #SPInfo
SELECT        
    PolicyId,
    PolicyName,
    RetentionDays,
    CASE WHEN (ExtRretentionRules & 4) = 4 THEN 1 ELSE 0 END,
    0, 0, 0, 0
FROM #SPBasicRetention csInfo WITH (NOLOCK)        
WHERE IsSnapCopy = 0 AND IsDefaultCopy = 1

--Synchronous Copies
UPDATE #SPInfo
SET SyncRetentionDays = sub2.RetentionDays
FROM #SPInfo AS sub1
INNER JOIN
(SELECT        
    PolicyId,
    RetentionDays
FROM #SPBasicRetention csInfo WITH (NOLOCK)
WHERE IsSnapCopy = 0 AND IsDefaultCopy = 0 AND CopyType = 1 AND RetentionDays > 60) AS sub2
ON sub1.PolicyId = sub2.PolicyId

--Selective Copies Weekly
UPDATE #SPInfo
SET SelWeekRetentionDays = sub2.RetentionDays
FROM #SPInfo AS sub1
INNER JOIN
(SELECT        
    C.PolicyId,
    C.RetentionDays    
FROM #SPBasicRetention AS C WITH (NOLOCK)        
    WHERE C.IsSnapCopy = 0
        AND C.IsDefaultCopy = 0
        AND C.CopyType = 2
        AND C.SelectiveCopyType = 2 ) AS sub2 --Weekly Fulls
ON sub1.PolicyId = sub2.PolicyId

--Selective Copies Monthly
UPDATE #SPInfo
SET SelMonRetentionDays = sub2.RetentionDays
FROM #SPInfo AS sub1
INNER JOIN
(
	SELECT       
		C.PolicyId,
		C.RetentionDays
	FROM #SPBasicRetention AS C WITH (NOLOCK)            
			WHERE C.IsSnapCopy = 0
			AND C.IsDefaultCopy = 0
			AND C.CopyType = 2
			AND C.SelectiveCopyType = 4 
) AS sub2 --Monthly Fulls
ON sub1.PolicyId = sub2.PolicyId    

UPDATE #SPInfo
SET LongestRetentionDays = CASE     
                                WHEN SyncRetentionDays > 0 THEN SyncRetentionDays
                                WHEN SelWeekRetentionDays > 0 THEN SelWeekRetentionDays
                                WHEN SelMonRetentionDays > 0 THEN SelMonRetentionDays
                                ELSE PrimRetentionDays
                           END    

IF OBJECT_ID('tempdb.dbo.#DailyTbl') IS NOT NULL DROP TABLE #DailyTbl
CREATE TABLE #DailyTbl(StartDate datetime, EndDate datetime, UnixStartDate int, UnixEndDate int)

DECLARE @fDt DATETIME = @startDate
DECLARE @tDt DATETIME = @endDate

WHILE @tDt >= @FromDt
BEGIN

	IF OBJECT_ID('IsInvalidTimeManaged') IS NOT NULL
	BEGIN
		WHILE dbo.IsInvalidTimeManaged(@fDt, @csTimeZone) = 1
		BEGIN
			SET @fDt = DATEADD(HOUR, 1, @fDt)
		END
		
		WHILE dbo.IsInvalidTimeManaged(@tDt, @csTimeZone) = 1
		BEGIN
			SET @tDt = DATEADD(HOUR, 1, @tDt)
		END
	END

	INSERT INTO #DailyTbl
	VALUES (@fDt, @tDt, dbo.GetUnixTime(dbo.LocalToUTCTime(@fDt, @csTimeZone)), dbo.GetUnixTime(dbo.LocalToUTCTime(@tDt, @csTimeZone)))
	
	SET @fDt = DATEADD(DAY, -1, @fDt)
	SET @fDt = DATEADD(DAY, DATEDIFF(DAY, 0, @fDt), 0)
	SET @tDt = DATEADD(DAY, 1, @fDt)
END

SELECT @surveyXML = (
	SELECT (
		SELECT StartDate AS '@JobEndDate', 
				SUM(CASE WHEN LongestRetentionDays <= 42 AND LongestRetentionDays <> -1 THEN 1 ELSE 0 END) AS '@ShortTermRetJobsCount',
				SUM(CASE WHEN LongestRetentionDays > 42 OR LongestRetentionDays = -1 THEN 1 ELSE 0 END) AS '@LongTermRetJobsCount',
				SUM(CASE WHEN LongestRetentionDays <= 42 AND LongestRetentionDays <> -1 THEN JM.totalUncompBytes ELSE 0 END) AS '@ShortTermRetSize',
				SUM(CASE WHEN LongestRetentionDays > 42 OR LongestRetentionDays = -1 THEN JM.totalUncompBytes ELSE 0 END) AS '@LongTermRetSize'
		FROM #SPInfo SP 
			INNER JOIN JMBkpStats JM WITH (NOLOCK)
				ON SP.PolicyId = (CASE WHEN JM.dataArchGrpId > 0 THEN JM.dataArchGrpId ELSE JM.logArchGrpId END) 
					AND JM.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND JM.Status IN (1,3,14)
			INNER JOIN #DailyTbl D 
				ON JM.servEndDate >= @FromTime AND JM.servEndDate >= UnixStartDate AND JM.servEndDate < UnixEndDate
			LEFT OUTER JOIN #ExcludedAppIds E ON JM.appId = E.appId
		WHERE	E.appId IS NULL
		GROUP BY StartDate
		order by StartDate 
		FOR XML PATH('DataStats'), TYPE
	)
	FOR XML PATH('DailyBackupRetention')
)


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

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

IF OBJECT_ID('tempdb.dbo.#SPBasicRetention') IS NOT NULL DROP TABLE #SPBasicRetention
	
------- END SURVEY QUERY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

--SELECT cast (@surveyXML AS XML)

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

--Update the last collection time
IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @tempTimeStamp NVARCHAR(256)
	set @tempTimeStamp = CONVERT(nvarchar(max), @CurrCollectionDate,121)
	EXEC SetSurveyParamValue @LastCollectionAttrName, @tempTimeStamp
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
		UPDATE GXGlobalParam SET value = CONVERT(nvarchar(max), @CurrCollectionDate,121) where name = @LastCollectionAttrName
	ELSE
	BEGIN
		DECLARE @nstring NVARCHAR(MAX)
		IF(@nreleaseId >= 15 )
		BEGIN
			SET @nstring ='insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CONVERT(nvarchar(max), @CurrCollectionDate,121) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @nstring
		END
		ELSE IF(@nreleaseId = 14 )
		BEGIN
			insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CONVERT(nvarchar(max), @CurrCollectionDate,121))
		END
	END
END

SET NOCOUNT OFF
--------- END - GENERATED CODE ---------