--Name:- Back Up Statistics
--Description:- Total Number of backup, successful backup, failed backup, % Successful Backup Jobs

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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

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

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

--RPTBKPHISTORYINFO


SET NOCOUNT ON
DECLARE @TotalJobs  int	
DECLARE @CJobs	    int
DECLARE @CWEJobs    int
DECLARE @CWWJobs    int
DECLARE @FailedJobs int
DECLARE @KilledJobs int
DECLARE @sixtdaysec int 

SET @sixtdaysec=24*60*60*60

DECLARE @jobStatusAggrTbl TABLE
(
   jobStatus int,
   totalCount int
)

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

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')
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyPrivateSpecialClientGroup'
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        

-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
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

IF EXISTS ( SELECT id FROM app_clientgroup WITH (NOLOCK) join @includeGroups on id=groupID)
	SET @specialGroupExists =1

DECLARE @ExcludedAppIds TABLE (appId INT)

INSERT INTO @ExcludedAppIds
SELECT componentNameId FROM APP_subclientProp WITH (NOLOCK) WHERE attrName IN ('DDB Backup', 'Index SubClient', '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)
	
IF  (@specialGroupExists =1 )
BEGIN
	INSERT INTO @jobStatusAggrTbl 
	SELECT BKP.jobState, COUNT(BKP.jobState)
	FROM
		(SELECT
		   (CASE WHEN displayStatus = 0 THEN B.status
				 ELSE displayStatus 
		    END) AS jobState 
		FROM JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK) ON B.appId = A.id
			INNER JOIN APP_Client C WITH (NOLOCK) ON C.id = A.clientId
			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientId = C.id
			INNER JOIN @includeGroups CG ON CGA.clientGroupId = CG.groupId			
		WHERE B.servEndDate >= (DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())-(@sixtdaysec))	
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND (B.status <> 9 OR (B.status = 9 AND B.failureErrorCode = 8))
			AND	dbo.IsSubClientValid(A.appTypeId, A.subclientStatus, 0) = 1
			AND A.id NOT IN (SELECT appId FROM @ExcludedAppIds)
		) BKP
	GROUP BY BKP.jobState
END	
ELSE
BEGIN
	INSERT INTO @jobStatusAggrTbl 
	SELECT BKP.jobState, COUNT(BKP.jobState)
	FROM  
		(SELECT
		   (CASE WHEN displayStatus = 0 THEN B.status
				 ELSE displayStatus 
		    END) AS jobState 	
		FROM JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK) ON B.appId = A.id
			INNER JOIN APP_Client C WITH (NOLOCK) ON C.id = A.clientId
		WHERE B.servEndDate >= (DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())-(@sixtdaysec))		
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND (B.status <> 9 OR (B.status = 9 AND B.failureErrorCode = 8))
			AND 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&(16|32) = 0 
			AND A.id NOT IN (SELECT appId FROM @ExcludedAppIds)
		) BKP
	GROUP BY BKP.jobState
END

IF EXISTS (SELECT totalCount FROM @jobStatusAggrTbl where jobStatus = 1)
	SET @CJobs = ( SELECT totalCount FROM @jobStatusAggrTbl where jobStatus = 1 )
ELSE
	SET @CJobs = 0

IF EXISTS (SELECT totalCount FROM @jobStatusAggrTbl where jobStatus in (14))
	SET @CWWJobs = ( SELECT totalCount FROM @jobStatusAggrTbl where jobStatus in (14)  )
ELSE
	SET @CWWJobs = 0
	
IF EXISTS (SELECT totalCount FROM @jobStatusAggrTbl where jobStatus = 3)
	SET @CWEJobs = ( SELECT totalCount FROM @jobStatusAggrTbl where jobStatus =3  )
ELSE
	SET @CWEJobs = 0

IF EXISTS (SELECT totalCount FROM @jobStatusAggrTbl where jobStatus IN ( 2,9) )
	SET @FailedJobs = ( SELECT SUM(totalCount) FROM @jobStatusAggrTbl where jobStatus IN ( 2,9) )
ELSE
	SET @FailedJobs = 0

IF EXISTS (SELECT totalCount FROM @jobStatusAggrTbl where jobStatus = 4)
	SET @KilledJobs = ( SELECT SUM(totalCount) FROM @jobStatusAggrTbl where jobStatus = 4)
ELSE
	SET @KilledJobs = 0

SET @TotalJobs = @CJobs + @CWWJobs + @CWEJobs + @FailedJobs +@KilledJobs

SET @surveyXML = (
	SELECT  CASE @specialGroupExists WHEN 1 THEN SUBSTRING(@groupStrName, 3, 5120) ELSE N'All' END as '@clientGroups',
		@TotalJobs AS '@No_Of_Backup',
		@CJobs AS '@Successful_Backup',
		@CWEJobs '@CompletedWithErrorJobs',
		@CWWJobs '@CompletedWithWarningJobs',
		@FailedJobs AS '@Failed_Backup',
		@KilledJobs AS '@KilledJobs'
   FOR XML PATH('BackupStats' ))
SET NOCOUNT OFF


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

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
--SELECT CAST(@surveyXML AS XML)
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)


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