--- Please follow the below comments to insert SQL statements.

--------- 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 = 207
DECLARE @surveyXML NVARCHAR(MAX)

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


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

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

-- This query is to find the Clients that are not protected in last 30 days.
-- This is used for SLA report in health check.

DECLARE @LastNDays int = 60
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 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 where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam 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 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 join @includeGroups on id=groupID)
	SET @specialGroupExists =1

DECLARE @csTimeZoneName    NVARCHAR(1024) = ''
SELECT  @csTimeZoneName = dbo.GetClientTimeZone(2)

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


DECLARE	@csReleaseId	INT
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE	@fromDate DATE = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-@lastNDays, 0)
DECLARE	@fromTime INT  = dbo.GetUnixTime(dbo.LocalToUTCTime(@fromDate, @csTimeZoneName))

CREATE TABLE #Subclient (appId INT, archGrpId INT, lastFullStart INT, lastFullAppSize BIGINT, incrJobsAppSize BIGINT, nIncrJobs INT)

INSERT	INTO #Subclient
SELECT	appId, 0, MAX(servStartDate), 0, 0, 0
FROM	JMBkpStats B WITH (NOLOCK)
WHERE	opType IN (4, 14, 18, 30, 43, 60, 76, 87, 91, 94, 97, 98, 101) 
	AND bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
	AND status IN (1, 3, 14)
	AND (status <> 3 OR appType NOT IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
GROUP BY appId
HAVING MAX(servStartDate) < @fromTime

DELETE	S FROM #Subclient S
		INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
WHERE	A.subclientStatus&(2|4|16|32|64|4096) > 0 --(64|4096) for cmdline subclient
	 OR A.dataArchGrpID <= 1 AND logArchGrpID <=1
	 OR A.appTypeId >= 600 OR A.appTypeId IN (72, 84, 85, 107, 121, 122, 127) 
	 OR A.appTypeId IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) AND A.subclientStatus&8 > 0
	 
IF @specialGroupExists = 1
	DELETE	S FROM #Subclient S
		INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
		LEFT OUTER JOIN (
			SELECT 	CGA.clientId AS clId 
			FROM 	APP_ClientGroupAssoc CGA WITH (NOLOCK) INNER JOIN @includeGroups IG 
				ON (CGA.clientGroupId = IG.groupID AND @specialGroupExists = 1)
			) CG ON A.clientId = CG.clId
	WHERE	CG.clId IS NULL

UPDATE	S SET archGrpId = CASE WHEN B.dataArchGrpId > 1 THEN B.dataArchGrpId ELSE B.logArchGrpId END, lastFullAppSize = B.totalUncompBytes
FROM	#Subclient S
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON S.appId = B.appId AND S.lastFullStart = B.servStartDate

UPDATE	S SET nIncrJobs = T.nIncrJobs, incrJobsAppSize = T.incrJobsAppSize
FROM	#Subclient S
		INNER JOIN
		(SELECT	B.appId, COUNT(*) AS nIncrJobs, SUM(B.totalUncompBytes) AS IncrJobsAppSize
		FROM	#Subclient SC
				INNER JOIN JMBkpStats B WITH (NOLOCK) ON SC.appId = B.appId AND SC.lastFullStart < B.servStartDate
		WHERE	B.opType IN (4, 14, 18, 30, 43, 60, 76, 87, 91, 94, 97, 98, 101) 
			AND B.bkpLevel NOT IN (1, 64, 128, 16384, 1024, 32768)
			AND B.status IN (1, 3, 14)
			AND (B.status <> 3 OR B.appType NOT IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
			AND B.totalUncompBytes > 0
		GROUP BY B.appId
		) T ON S.appId = T.appId

SET @surveyXML = (
	SELECT	appId AS '@AppId', 
			archGrpId AS '@ArchGrpId',
			dbo.UTCToLocalStringTime(dbo.GetDateTime(lastFullStart), @csTimeZoneName) AS '@LastFullStart', 
			lastFullAppSize AS '@LastFullAppSize', 
			incrJobsAppSize AS '@IncrJobsAppSize', 
			nIncrJobs AS '@NumOfIncrJobs'
	FROM	#Subclient
	ORDER BY incrJobsAppSize DESC

	FOR XML PATH ('Subclient') 
	-- Replace <<RESULTS_NAME>> with specific name
)

DROP TABLE #Subclient

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

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
--SELECT CAST (@surveyXML AS XML)

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

SET NOCOUNT OFF

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