--Name:- Backup Count
--Description:- success job Count per day, failure job count per day, top job counts per hour
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON

BEGIN TRY


DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 73
DECLARE @surveyXML NVARCHAR(MAX)
--------- END - GENERATED CODE ---------

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

--------- Insert your SQL statements here 


DECLARE @totalDaySeconds INT
DECLARE @day_sec INT = 24*60*60
SET @totalDaySeconds = (DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())-(@day_sec))

DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode	 NVARCHAR(MAX) ='0';
DECLARE @successCount INT
DECLARE @failureCount INT
DECLARE @topJobsPerHr INT
DECLARE @EncMediaSizeMB BIGINT
DECLARE @NonEncMediaSizeMB BIGINT
DECLARE @NonEncAppSizeMB BIGINT
DECLARE @EncAppSizeMB BIGINT
DECLARE @pastNMonths INT
DECLARE @FromTime BIGINT
DECLARE @LastQStartMonth DATE
DECLARE @LastJobTime BIGINT
DECLARE @data XML 
DECLARE @gxglobalParamName VARCHAR(60) = 'QuarterlyEncrypAndNonEncrptData'

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'))
	SET  @gxglobalParamName = 'CommservSurveyPrivateQuarterlyEncrypAndNonEncrptData'
else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR  (@frequencyMode ='3'))
	SET  @gxglobalParamName = 'CommservSurveyDirectDIPQuarterlyEncrypAndNonEncrptData'
ELSE
	SET  @gxglobalParamName = 'CommservSurveyQuarterlyEncrypAndNonEncrptData'


DECLARE @CurrMonthStartDate DATETIME = DATEADD(DD,(-1 * DAY(GETUTCDATE())) + 1,GETUTCDATE())	
SET @LastQStartMonth = DATEADD(MONTH, -3, DATEADD(MONTH, (-1 * ((MONTH(@CurrMonthStartDate) -1) % 3)), @CurrMonthStartDate))

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

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

CREATE TABLE #Last2QuartersData (Year INT, Quarter INT, EncrypAppSizeMB BIGINT, NonEncrypAppSizeMB BIGINT, EncrypMediaSizeMB BIGINT, NonEncrypMediaSizeMB BIGINT)

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @tempVal NVARCHAR(MAX)='';
	EXEC GetSurveyParamValue @gxglobalParamName, @tempVal OUTPUT 
	IF @tempVal <> ''
	BEGIN
		SET @data = CAST(@tempVal AS XML)
	END
END
ELSE
BEGIN
	IF EXISTS (SELECT * FROM gxglobalParam WHERE name = @gxglobalParamName)
	BEGIN
		SET @data = CAST((SELECT value FROM gxglobalParam WHERE name = @gxglobalParamName) AS XML)
	END
END

IF @data IS NULL
BEGIN		
	SET @FromTime = dbo.getunixtime(dbo.ClientLocalToUTCTime(@LastQStartMonth, 2))
END
ELSE
BEGIN
	SET @FromTime = @data.value('/Script73data[1]/@LastJobTime','BIGINT')	

	INSERT INTO #Last2QuartersData
	SELECT d.n.value('@Year','INT'),
		   d.n.value('@Quarter','INT'),
		   ISNULL(d.n.value('@EncrypAppSizeMB','BIGINT'),0),
		   ISNULL(d.n.value('@NonEncrypAppSizeMB','BIGINT'),0),
		   ISNULL(d.n.value('@EncrypMediaSizeMB','BIGINT'),0),
		   ISNULL(d.n.value('@NonEncrypMediaSizeMB','BIGINT'),0)
	FROM @data.nodes('/Script73data/Data') d(n)
END

DELETE L FROM #Last2QuartersData L
WHERE L.Year <= YEAR(@LastQStartMonth) AND L.Quarter < ((MONTH(@LastQStartMonth) - 1)/3 + 1)


DECLARE @QuarterBegin DATETIME = @LastQStartMonth
DECLARE @QuarterEnd   DATETIME = DATEADD(MM, 3, @LastQStartMonth)

IF OBJECT_ID('tempdb..#Quarters') IS NOT NULL DROP TABLE #Quarters
CREATE TABLE #Quarters (Year INT, Quarter INT, StartTime INT, EndTime INT)

WHILE @QuarterBegin < @CurrMonthStartDate
BEGIN
	INSERT	INTO #Quarters
	SELECT	YEAR(@QuarterBegin), ((MONTH(@QuarterBegin) - 1)/3 + 1), 
			dbo.GetUnixTime(dbo.ClientLocalToUTCTime(@QuarterBegin, 2)),
			dbo.GetUnixTime(dbo.ClientLocalToUTCTime(@QuarterEnd, 2))
	
	SET @QuarterEnd = @QuarterBegin
	SET @QuarterBegin = DATEADD(MM, 3, @QuarterEnd)
END

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

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

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

DECLARE @RealOne REAL = 1.0
DECLARE @BytesPerMB REAL = 1024.0*1024.0

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)


CREATE  TABLE #DedupPrimaryCopy (archGrpId INT, archGrpName NVARCHAR(144), copyId INT)

INSERT INTO #DedupPrimaryCopy
SELECT  AG.id, AG.name, AGC.id
FROM    archGroup AG WITH (NOLOCK)
        INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AG.defaultCopy = AGC.id
WHERE   ((AGC.dedupeFlags & 268435456) = 0) AND ((AGC.dedupeFlags & 262144 /*$$(CVA_SIDB_STORE_ENABLED_FLAG)*/) > 0)


IF OBJECT_ID('tempdb..#QuarterSize') IS NOT NULL DROP TABLE #QuarterSize
CREATE TABLE #QuarterSize (Year INT, Quarter INT, EncEnabled INT, LastJobDate INT, MediaSizeMB BIGINT, ToTalAppSizeMB BIGINT)

INSERT INTO #QuarterSize
SELECT Q.Year, Q.Quarter, (CASE WHEN encKeyType = 0 THEN 0 ELSE 1 END) AS EncEnabled, 		   
		MAX(JM.servEndDate) AS LastJobDate,
		SUM(J.sizeonMedia)/@BytesPerMB AS MediaSize, 
		SUM((CASE WHEN JM.totalBackupSize > 0 THEN (@RealOne*AFC.physicalSize/JM.totalBackupSize)*(JM.totalUnCompBytes/@BytesPerMB) ELSE AFC.physicalSize/@BytesPerMB END)) AS ToTalAppSize
FROM JMBkpStats JM
	INNER JOIN JMJobDataStats J
		ON JM.jobId = J.JobId AND JM.servEndDate > @FromTime AND JM.status IN (1, 14)
			AND JM.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)  
			AND JM.commCellId = 2 
	INNER JOIN #DedupPrimaryCopy A
		ON A.archGrpId = J.ArchGrpId 
			AND J.archGrpCopyId = A.copyId AND J.AuxCopyJobId = 0
	INNER JOIN archFile AF
		ON J.jobId = AF.jobId AND J.appId = AF.appId AND J.archGrpId = AF.archGroupId AND J.commCellId = AF.commCellId AND J.datatype = AF.fileType
	INNER JOIN archFileCopy AFC
		ON AF.id = AFC.archFileId AND AF.commcellId = AFC.commCellId AND J.archGrpCopyId = AFC.archCopyId 
	INNER JOIN #Quarters Q ON AF.cTime BETWEEN Q.StartTime AND Q.EndTime
	LEFT OUTER JOIN #ExcludedAppIds E ON JM.appId = E.appId
WHERE E.appId IS NULL
GROUP BY Q.Year, Q.Quarter, (CASE WHEN encKeyType = 0 THEN 0 ELSE 1 END)

SELECT Year, Quarter,
	   CAST(SUM(CASE WHEN EncEnabled = 0 THEN MediaSizeMB ELSE 0.0 END) AS BIGINT) AS NonEncrypMediaSizeMB, 
	   CAST(SUM(CASE WHEN EncEnabled = 0 THEN 0.0 ELSE MediaSizeMB END) AS BIGINT) AS EncrypMediaSizeMB, 
	   CAST(SUM(CASE WHEN EncEnabled = 0 THEN ToTalAppSizeMB ELSE 0.0 END) AS BIGINT) AS NonEncrypAppSizeMB,
	   CAST(SUM(CASE WHEN EncEnabled = 0 THEN 0.0 ELSE ToTalAppSizeMB END) AS BIGINT) AS EncrypAppSizeMB,
	   MAX(LastJobDate) AS LastJobDate
INTO #TempQData
FROM #QuarterSize
GROUP BY Year, Quarter

IF EXISTS (SELECT * FROM #TempQData)
BEGIN
	UPDATE GQ 
	SET GQ.EncrypAppSizeMB += ISNULL(T.EncrypAppSizeMB,0),
		GQ.NonEncrypAppSizeMB += ISNULL(T.NonEncrypAppSizeMB,0),
		GQ.EncrypMediaSizeMB += ISNULL(T.EncrypMediaSizeMB,0),
		GQ.NonEncrypMediaSizeMB += ISNULL(T.NonEncrypMediaSizeMB,0)
	FROM #Last2QuartersData GQ
	INNER JOIN #TempQData T
		ON GQ.Year = T.Year AND GQ.Quarter = T.Quarter

	INSERT INTO #Last2QuartersData (Year, Quarter, EncrypAppSizeMB, NonEncrypAppSizeMB, EncrypMediaSizeMB, NonEncrypMediaSizeMB)
	SELECT T.Year, T.Quarter, T.EncrypAppSizeMB, T.NonEncrypAppSizeMB, T.EncrypMediaSizeMB, T.NonEncrypMediaSizeMB
	FROM #TempQData T
	LEFT OUTER JOIN #Last2QuartersData GQ
		ON GQ.Year = T.Year AND GQ.Quarter = T.Quarter
	WHERE GQ.Quarter IS NULL

	SET @LastJobTime = (SELECT MAX(LastJobDate) FROM #TempQData)
END
ELSE
	SET @LastJobTime = dbo.getunixtime(dbo.ClientLocalToUTCTime(GETDATE(), 2))


SET @data = (SELECT @LastJobTime AS '@LastJobTime',
	   (SELECT GQ.Year AS '@Year',
			   GQ.Quarter AS '@Quarter',
			   GQ.EncrypAppSizeMB AS '@EncrypAppSizeMB',
			   GQ.NonEncrypAppSizeMB AS '@NonEncrypAppSizeMB',
			   GQ.EncrypMediaSizeMB AS '@EncrypMediaSizeMB',
			   GQ.NonEncrypMediaSizeMB AS '@NonEncrypMediaSizeMB'
	    FROM #Last2QuartersData GQ
		FOR XML PATH('Data'), TYPE)
	FOR XML PATH('Script73data') )

SELECT @NonEncMediaSizeMB = GQ.NonEncrypMediaSizeMB,
	   @EncMediaSizeMB = GQ.EncrypMediaSizeMB, 
	   @NonEncAppSizeMB = GQ.NonEncrypAppSizeMB,
	   @EncAppSizeMB = GQ.EncrypAppSizeMB
FROM #Last2QuartersData GQ
WHERE GQ.Year = YEAR(@LastQStartMonth) AND GQ.Quarter = ((MONTH(@LastQStartMonth) - 1)/3 + 1)

IF OBJECT_ID('GetSurveyParamValue') IS NULL
BEGIN
	IF NOT EXISTS (SELECT * FROM gxglobalParam WHERE name = @gxglobalParamName)
	 BEGIN
		IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
			INSERT INTO GXGlobalParam (name, value) SELECT @gxglobalParamName, ''
		ELSE
			EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT '''+@gxglobalParamName+''', '''', 0, 0')
	END
END

IF OBJECT_ID('tempdb..#sublclient_List') IS NOT NULL DROP TABLE #sublclient_List
CREATE TABLE #sublclient_List (subclientAppId INT)

INSERT INTO #sublclient_List
SELECT DISTINCT A.id 
FROM	APP_Application A WITH (NOLOCK) 
	INNER JOIN APP_Client WITH (NOLOCK) ON APP_Client.id = clientId
	LEFT OUTER JOIN #ExcludedAppIds E ON A.id = E.appId
WHERE	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
	AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
        AND (A.subclientStatus & (2|4|8|16|32|64|128|4096)) IN (0, 8, 64, 128, 4096)
	AND E.appId IS NULL

SET @successCount=(
SELECT COUNT(*)
		FROM  JMBkpStats WITH (NOLOCK) INNER JOIN #sublclient_List
		ON subclientAppId = appId  
		WHERE servEndDate >= @totalDaySeconds
		AND status IN (1,3,14) AND optype <> 60)
		
SET @failureCount=(		
SELECT COUNT(*)
		FROM  JMBkpStats WITH (NOLOCK) INNER JOIN #sublclient_List
		ON subclientAppId = appId 
		WHERE servEndDate >= @totalDaySeconds
		AND status IN (2,9) AND optype <> 60)
		
SET @topJobsPerHr=ISNULL(
(SELECT MAX(Y.cnt) FROM
( 
 SELECT count(*) AS cnt , X.hour FROM 
  (
        SELECT DATEDIFF(hour,DATEADD(s,servEndDate,'1970-01-01 00:00:00'),GETUTCDATE()) AS hour
		FROM  JMBkpStats WITH (NOLOCK) INNER JOIN #sublclient_List
		ON subclientAppId = appId 
		WHERE servEndDate >= @totalDaySeconds
		AND status IN (1,3,14) AND optype <> 60
   ) AS X
GROUP BY X.hour	) AS Y
),0)


SET @surveyXML=(
SELECT @successCount AS '@successJobCountPerDay', @failureCount AS '@failureJobCountPerDay', @topJobsPerHr AS '@topJobCountPerHour',
	   ISNULL(@EncMediaSizeMB,0) AS '@EncryptedMediaSizeMB', ISNULL(@NonEncMediaSizeMB,0) AS '@NonEncryptedMediaSizeMB', ISNULL(@EncAppSizeMB,0) AS '@EncryptedAppSizeMB', ISNULL(@NonEncAppSizeMB,0) AS '@NonEncrptedAppSizeMB'
FOR XML PATH('Backup_Count')
)



--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

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')
)
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 @tempData NVARCHAR(MAX)
	set @tempData = CAST(@data AS NVARCHAR(MAX))
	EXEC SetSurveyParamValue @gxglobalParamName, @tempData
END
ELSE
BEGIN
	UPDATE  GXGlobalParam SET value = CAST(@data AS NVARCHAR(MAX))
	WHERE   name = @gxglobalParamName
END


SET NOCOUNT OFF

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