--Name:- Back Up Statistics
--a) collects today's data
--b) collects current month data


 
--Description:- Total Number of backup, successful backup, failed backup, Completed with Warning and Errors

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

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


-- 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'
	DECLARE @MonthlyPublicVal NVARCHAR(MAX) = null
	
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveySpecialClientGroup'
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)


DECLARE @nDays INT = 16

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

CREATE TABLE #HourTbl (DateVal DATE, Hour INT, HourBeginDT DATETIME, HourBegin INT, HourEnd INT)

DECLARE	@i			INT = 0
DECLARE @dtEnd		DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE())+1, 0)
DECLARE	@dtBegin	DATETIME = DATEADD(HOUR, -1, @dtEnd)
DECLARE	@FromTime	DATETIME = DATEADD(DD, -1, @dtEnd)

WHILE @i < @nDays
BEGIN
	WHILE DATEDIFF(DAY, @FromTime, @dtBegin) = 0
	BEGIN
		BEGIN TRY
			INSERT	INTO #HourTbl
			SELECT	@dtBegin, DATEDIFF(HOUR, @FromTime, @dtBegin), dbo.LocalToUTCTime(@dtBegin, @csTimeZone), 0, 0
		END TRY
		BEGIN CATCH 
		
		END CATCH

		SET @dtBegin = DATEADD(HOUR, -1, @dtBegin)
	END

	SET @FromTime = DATEADD(DAY, -1, @FromTime)
	SET @i = (@i + 1)
END

UPDATE	#HourTbl SET HourBegin = dbo.GetUnixTime(HourBeginDT)
UPDATE	#HourTbl SET HourEnd = (HourBegin + 3600)

CREATE INDEX #HourTbl_idx ON #HourTbl (HourBegin, HourEnd) INCLUDE (DateVal, Hour)


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

CREATE TABLE #hourlyJobs
(
	servEndDate DATE,
	hourComp int,
	CJobs BIGINT,
	CWEJobs BIGINT,
	CWWJobs BIGINT,
	FJobs BIGINT,
	KJobs BIGINT,
	SJobs BIGINT,
	COJobs BIGINT,
	FTSJobs BIGINT
)

IF @specialGroupExists = 0
BEGIN
	INSERT INTO #hourlyJobs
	SELECT	
		H.DateVal, H.Hour,
		SUM(CASE WHEN B.status IN (1) THEN 1 ELSE 0 END) AS CJobs,
		SUM(CASE WHEN B.status IN (3) THEN 1 ELSE 0 END) as CWEJobs,
		SUM(CASE WHEN B.status IN (14) THEN 1 ELSE 0 END) as CWWJobs,
		SUM(CASE WHEN B.status IN (2) THEN 1 ELSE 0 END) as FJobs,
		SUM(CASE WHEN B.status IN (4) THEN 1 ELSE 0 END) as KJobs,
		SUM(CASE WHEN B.status IN (9) AND B.failureErrorCode NOT IN (8) THEN 1 ELSE 0 END) as SJobs,
		SUM(CASE WHEN B.status IN (16) THEN 1 ELSE 0 END) AS COJobs,
		SUM(CASE WHEN B.status IN (9) AND B.failureErrorCode IN (8) THEN 1 ELSE 0 END) AS FTSJobs
	FROM	
		#HourTbl H 
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.servEndDate >= HourBegin AND B.servEndDate < HourEnd
			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 H.DateVal, H.Hour
END
ELSE
BEGIN
	INSERT INTO #hourlyJobs
	SELECT	
		H.DateVal, H.Hour,
		SUM(CASE WHEN B.status IN (1) THEN 1 ELSE 0 END) AS CJobs,
		SUM(CASE WHEN B.status IN (3) THEN 1 ELSE 0 END) as CWEJobs,
		SUM(CASE WHEN B.status IN (14) THEN 1 ELSE 0 END) as CWWJobs,
		SUM(CASE WHEN B.status IN (2) THEN 1  ELSE 0 END) as FJobs,
		SUM(CASE WHEN B.status IN (4) THEN 1 ELSE 0 END) as KJobs,
		SUM(CASE WHEN B.status IN (9) AND B.failureErrorCode NOT IN (8) THEN 1 ELSE 0 END) as SJobs,
		SUM(CASE WHEN B.status IN (16) THEN 1 ELSE 0 END) AS COJobs,
		SUM(CASE WHEN B.status IN (9) AND B.failureErrorCode IN (8) THEN 1 ELSE 0 END) AS FTSJobs
	FROM	
		#HourTbl H 
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.servEndDate >= HourBegin AND B.servEndDate < HourEnd
			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 H.DateVal, H.Hour
END

DECLARE @hourlyStatsXML XML = (
	SELECT	
		DISTINCT CAST(H.DateVal as DATE) as '@DayVal',
		H.Hour as '@HourComp',
		ISNULL(CJobs,0) as '@CompletedJobs',
		ISNULL(CWEJobs,0) + ISNULL(CWWJobs,0) as '@PartialJobs',
		ISNULL(FJobs,0) as '@FailedJobs',
		ISNULL(KJobs,0) as '@KilledJobs',
		ISNULL(SJobs,0) as '@SkippedJobs',
		ISNULL(COJobs, 0) as '@CommittedJobs',
		ISNULL(FTSJobs,0) as '@FailedToStartJobs'
	FROM	
		#HourTbl H LEFT OUTER JOIN #hourlyJobs HJ ON H.DateVal = HJ.servEndDate AND H.Hour = HJ.hourComp 
	FOR XML PATH ('HourlyStats'))

DROP TABLE #HourTbl
DROP TABLE #hourlyJobs

SET @surveyXML = (
	SELECT @hourlyStatsXML
	FOR XML PATH('BackupStatsXML'))

--------- 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')
)

--SELECT cast (@surveyXML AS XML)
SET NOCOUNT OFF
--------- END - GENERATED CODE ---------