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

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 173
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


BEGIN TRY

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 @freequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @freequencyMode = 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  (@freequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyPrivateSpecialClientGroup'
	SET @LastCollectionAttrName = 'CommservSurveyPrivateLastIDAActivityCollectionTime'
END
else IF ((@isPrivateRunning = 'Metrics Direct Dip')OR  (@freequencyMode ='3'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyDirectDipSpecialClientGroup'
	SET @LastCollectionAttrName = 'CommservSurveyDirectDipLastIDAActivityCollectionTime'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveySpecialClientGroup'
	SET @LastCollectionAttrName = 'CommservSurveyPublicLastIDAActivityCollectionTime'
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

DECLARE @IncludedAppTypeIds TABLE (appTypeId INT)

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)

INSERT INTO @IncludedAppTypeIds
SELECT DISTINCT appTypeId FROM APP_Application WITH (NOLOCK) 

-- Get the last collection time
DECLARE @CurrCollectionDate DATE = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
DECLARE @LastCollectionDate DATE = DATEADD(DAY, -1, @CurrCollectionDate)
DECLARE @PrevCollectionDate DATE

IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
	SELECT @PrevCollectionDate = CAST(CAST(value AS NVARCHAR(256)) AS DATE) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName
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
SET @PrevCollectionDate = DATEADD(DAY, -1, @PrevCollectionDate)


IF object_id('tempdb.dbo.#DailyTbl') is not null DROP TABLE #DailyTbl

CREATE TABLE #DailyTbl (DateVal DATE, DayBegin INT, DayEnd INT)

DECLARE	@dtDay DATE = @CurrCollectionDate

DECLARE @fromDT DATETIME
DECLARE @toDT   DATETIME

WHILE @dtDay > @PrevCollectionDate
BEGIN

	SET @fromDT = @dtDay	
	SET @toDT = DATEADD(DAY, 1, @dtDay)

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

	INSERT INTO #DailyTbl 
	VALUES (@dtDay, 
		dbo.GetUnixTime(dbo.LocalToUTCTime(@fromDT, @csTimeZone)), 
		dbo.GetUnixTime(dbo.LocalToUTCTime(@toDT, @csTimeZone)) - 1)

	SET @dtDay = DATEADD(DAY, -1, @dtDay)
END

CREATE INDEX #DailyTbl_idx ON #DailyTbl (DayBegin, DayEnd) INCLUDE (DateVal)

IF object_id('tempdb.dbo.#DailyJobs') is not null DROP TABLE #DailyJobs

CREATE TABLE #DailyJobs
(
	JobEndDate DATE,
	AppTypeId INT,
	TJobs INT,
	CJobs INT,
	CWEJobs INT,
	CWWJobs INT,
	FJobs INT,
	KJobs INT,
	SJobs INT,
	BackupSize BIGINT,
	DataWritten BIGINT,
	COJobs BIGINT,
	FTSJobs BIGINT
)

CREATE TABLE #PrimaryCopy (copyId INT, isSnapCopy INT)

INSERT	INTO #PrimaryCopy
SELECT	AGC.id, AGC.isSnapCopy
FROM	archGroupCopy AGC WITH (NOLOCK) INNER JOIN archGroup AG WITH (NOLOCK) 
	 ON AGC.archGroupId = AG.id AND AGC.id IN (AG.defaultCopy, AG.defaultSnapCopy)
WHERE	AGC.id > 0 AND AGC.type IN (1,2)

IF (@specialGroupExists = 0)
BEGIN
	INSERT INTO #DailyJobs 
	SELECT 
		D.DateVal, B.appType as AppTypeId,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (1,2,3,4,9,14,16) THEN 1 ELSE 0 END) as TJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (1) THEN 1 ELSE 0 END) AS CJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (3) THEN 1 ELSE 0 END) as CWEJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (14) THEN 1 ELSE 0 END) as CWWJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (2) THEN 1 ELSE 0 END) as FJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (4) THEN 1 ELSE 0 END) as KJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (9) AND B.failureErrorCode NOT IN (8) THEN 1 ELSE 0 END) as SJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (1,3,14) THEN totalUncompBytes ELSE 0 END) AS BackupSize,
		0 AS SizeOnMedia,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (16) THEN 1 ELSE 0 END) as COJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (9) AND B.failureErrorCode IN (8) THEN 1 ELSE 0 END) as FTSJobs 
	FROM
	 	#DailyTbl D
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.servEndDate >= D.DayBegin AND B.servEndDate <= D.DayEnd
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
		LEFT OUTER JOIN #ExcludedAppIds E ON B.appId = E.appId
	WHERE
	 	E.appId IS NULL
	GROUP BY D.DateVal, B.appType
	ORDER BY D.DateVal, B.appType

	;WITH SizeOnMedia (DateVal, AppTypeId, DataWritten) AS
	(
		SELECT  D.DateVal, B.appType, SUM(J.sizeOnMedia)
		FROM    JMJobDataStats J WITH (NOLOCK)
				INNER JOIN #PrimaryCopy C ON J.archGrpCopyId = C.copyId
				INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.JobId AND J.commCellId = B.commCellId
				INNER JOIN #DailyTbl D ON B.servEndDate >= DayBegin AND B.servEndDate < DayEnd
					AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
					AND (B.OpType NOT IN (59,65) AND C.isSnapCopy = 0 OR B.OpType IN (59,65) AND C.isSnapCopy = 1)
				LEFT OUTER JOIN #ExcludedAppIds E ON J.appId = E.appId
		WHERE	E.appId IS NULL
		GROUP BY D.DateVal, B.appType
	)
	UPDATE  D SET DataWritten = S.DataWritten
	FROM	#DailyJobs D INNER JOIN SizeOnMedia S ON D.JobEndDate = S.DateVal AND D.AppTypeId = S.AppTypeId
END 
ELSE
BEGIN
	INSERT INTO #DailyJobs 
	SELECT 	
		D.DateVal, B.appType as appTypeId,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (1,2,3,4,9,14,16) THEN 1 ELSE 0 END) as TJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (1) THEN 1 ELSE 0 END) AS CJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (3) THEN 1 ELSE 0 END) as CWEJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (14) THEN 1 ELSE 0 END) as CWWJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (2) THEN 1 ELSE 0 END) as FJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (4) THEN 1 ELSE 0 END) as KJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (9) AND B.failureErrorCode NOT IN (8) THEN 1 ELSE 0 END) as SJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (1,3,14) THEN totalUncompBytes ELSE 0 END) AS BackupSize,
		0 AS SizeOnMedia,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (16) THEN 1 ELSE 0 END) as COJobs,
		SUM(CASE WHEN (case B.displayStatus when 0 then B.status else B.displayStatus end) IN (9) AND B.failureErrorCode IN (8) THEN 1 ELSE 0 END) as FTSJobs 
	FROM 
		#DailyTbl D
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.servEndDate >= D.DayBegin AND B.servEndDate <= D.DayEnd
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
		INNER JOIN APP_Application A WITH (NOLOCK) ON B.appId= A.id
		INNER JOIN @includeClients CL ON CL.clientId = A.clientId
		LEFT OUTER JOIN #ExcludedAppIds E ON B.appId = E.appId
	WHERE 
		E.appId IS NULL
	GROUP BY D.DateVal, B.appType
	ORDER BY D.DateVal, B.appType

	;WITH SizeOnMedia (DateVal, AppTypeId, DataWritten) AS
	(
		SELECT  D.DateVal, B.appType, SUM(J.sizeOnMedia)
		FROM    JMJobDataStats J WITH (NOLOCK)           
				INNER JOIN #PrimaryCopy C ON J.archGrpCopyId = C.copyId
				INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.JobId AND J.commCellId = B.commCellId
				INNER JOIN #DailyTbl D ON B.servEndDate >= DayBegin AND B.servEndDate < DayEnd
					AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
					AND (B.OpType NOT IN (59,65) AND C.isSnapCopy = 0 OR B.OpType IN (59,65) AND C.isSnapCopy = 1)
				INNER JOIN APP_Application A WITH (NOLOCK) ON B.appId= A.id				
				INNER JOIN @includeClients CL ON CL.clientId = A.clientId
				LEFT OUTER JOIN #ExcludedAppIds E ON J.appId = E.appId
		WHERE	E.appId IS NULL
		GROUP BY D.DateVal, B.appType
	)
	UPDATE  D SET DataWritten = S.DataWritten
	FROM	#DailyJobs D INNER JOIN SizeOnMedia S ON D.JobEndDate = S.DateVal AND D.AppTypeId = S.AppTypeId
END
 	
DECLARE @DailyStatsXML XML = (
	SELECT 
		DISTINCT CAST(D.DateVal as DATE) as '@JobEndDate',
		ISNULL(DJ.AppTypeId,0) as '@AppTypeId',
		ISNULL(DJ.TJobs,0) as '@TotalJobs',
		ISNULL(DJ.CJobs,0) as '@CompletedJobs',
		ISNULL(DJ.CWEJobs,0) as '@CWEJobs',
		ISNULL(DJ.CWWJobs,0) as '@CWWJobs',
		ISNULL(DJ.FJobs,0) as '@FailedJobs',
		ISNULL(DJ.KJobs,0) as '@KilledJobs',
		ISNULL(DJ.SJobs,0) as '@SkippedJobs',
		ISNULL(DJ.BackupSize,0) AS '@BackupSize',
		ISNULL(DJ.DataWritten,0) AS '@DataWritten',
		ISNULL(DJ.COJobs,0) AS '@CommittedJobs',
		ISNULL(DJ.FTSJobs,0) AS '@FailedToStartJobs'
    FROM #DailyTbl D
		LEFT OUTER JOIN #DailyJobs DJ ON D.DateVal = DJ.JobEndDate
FOR XML PATH ('DailyJobStats'))

DROP TABLE #DailyTbl
DROP TABLE #DailyJobs
DROP TABLE #PrimaryCopy




SET @surveyXML = 
( SELECT @DailyStatsXML
	FOR XML PATH ('BackupStatsXML') )
	
--------- 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')
)


END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH
--SELECT cast (@surveyXML AS XML)

--Update the last collection time
IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	EXEC SetSurveyParamValue @LastCollectionAttrName, @LastCollectionDate
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
		UPDATE GXGlobalParam SET value = CAST(@LastCollectionDate as NVARCHAR(MAX)) where name = @LastCollectionAttrName
	ELSE
	BEGIN
		DECLARE @nstring NVARCHAR(MAX)
		IF(@nreleaseId >= 15 )
		BEGIN
			SET @nstring ='insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CAST(@LastCollectionDate as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @nstring
		END
		ELSE IF(@nreleaseId = 14 )
		BEGIN
			insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CAST(@LastCollectionDate as NVARCHAR(MAX)))
		END
	END
END
SET NOCOUNT OFF
--------- END - GENERATED CODE ---------