--@querytype 	CSV
--Name:- SLA Exclusion

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

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('RptSLAClient', 'U') IS NULL
BEGIN
	RETURN
END

IF NOT EXISTS (SELECT 1 FROM RptSLAClient WHERE days = -1)
BEGIN
	RETURN
END

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


IF OBJECT_ID('tempdb.dbo.#SLAExclusion') IS NOT NULL DROP TABLE #SLAExclusion		
CREATE TABLE #SLAExclusion
(
	ClientId		INT,
	ClientGroupId	INT,
	AppTypeId		INT,
	InstanceId		INT,
	BackupsetId		INT,
	AppId			INT,
	Category		INT,
	LastSeenTime	BIGINT
)

IF OBJECT_ID('tempdb.dbo.#ExcludedSubclient') IS NOT NULL DROP TABLE #ExcludedSubclient	
CREATE TABLE #ExcludedSubclient
(
	ClientId		INT,
	AppTypeId		INT,
	InstanceId		INT,
	BackupsetId		INT,
	AppId			INT,
	Category		INT
)

IF OBJECT_ID('RptSLAExclusion', 'U') IS NOT NULL
BEGIN
	INSERT	INTO #SLAExclusion
	SELECT	clientId, clientGroupId, appTypeId, instanceId, backupsetId, appId, category, 0
	FROM	RptSLAExclusion

	INSERT	INTO #SLAExclusion
	SELECT	C.clientId, 0, 0, 0, 0, 0, C.category, 0
	FROM	RptSLAClient C
			LEFT OUTER JOIN #SLAExclusion E ON C.clientId = E.ClientId
	WHERE	C.days = -1 AND C.fullJobsOnly = 0 AND C.category IN (6, 10) AND E.ClientId IS NULL
END
ELSE
BEGIN
	INSERT	INTO #SLAExclusion
	SELECT	clientId, 0, 0, 0, 0, 0, category, 0
	FROM	RptSLAClient
	WHERE	days IN (0,-1) AND fullJobsOnly = 0 AND category IN (5, 6, 8, 10) 

	INSERT	INTO #SLAExclusion
	SELECT	S.clientId, 0, 106, 0, 0, MAX(S.appId), MAX(S.category), 0
	FROM	RptSLASubclient S
			LEFT OUTER JOIN #SLAExclusion E ON S.clientId = E.ClientId
	WHERE	S.appTypeId = 106 AND S.fullJobsOnly = 0 AND S.category IN (5, 6, 8, 10) 
		AND E.ClientId IS NULL
	GROUP BY S.clientId

	UPDATE	E SET AppId = 0
	FROM	#SLAExclusion E
			INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON E.ClientId = CP.componentNameId 
				AND CP.attrName = 'Exclude From SLA' AND CP.attrVal = '1' AND CP.modified = 0
	WHERE	E.Category = 8 AND E.AppTypeId = 106 AND E.AppId > 0

	UPDATE	E SET ClientGroupId = C.ClientGroupId
	FROM	#SLAExclusion E
			INNER JOIN (
			SELECT	CGA.ClientId, MAX(CGA.ClientGroupId) AS ClientGroupId
			FROM	APP_ComponentProp CP WITH (NOLOCK)
					INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.ClientGroupId = CP.componentId
			WHERE	CP.componentType = 8 AND CP.propertyTypeId = 3301 AND CP.longVal = 1 AND CP.modified = 0
			GROUP BY CGA.ClientId
			) C ON E.ClientId = C.ClientId
	WHERE	E.Category = 8

	UPDATE	E SET ClientGroupId = CGA.ClientGroupId
	FROM	#SLAExclusion E
			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON E.ClientId = CGA.clientId
			INNER JOIN JMJobAction J WITH (NOLOCK) ON CGA.clientGroupId = J.clientGroupId
				AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1
				AND J.opType = 4 AND J.action = 1
	WHERE	E.Category = 5

	INSERT	INTO #ExcludedSubclient
	SELECT	S.clientId, S.appTypeId, A.instance, A.backupSet, S.appId, S.category
	FROM	RptSLASubclient S
			INNER JOIN APP_Application A WITH (NOLOCK) ON S.AppId = A.id AND S.AppTypeId <> 106
			LEFT OUTER JOIN #SLAExclusion E ON S.clientId = E.ClientId
	WHERE	S.fullJobsOnly = 0 AND S.category IN (5, 6, 8, 10) AND E.ClientId IS NULL

	UPDATE	S SET InstanceId = J.instanceID, BackupsetId = 0, AppId = 0
	FROM	#ExcludedSubclient S
			INNER JOIN JMJobAction J WITH (NOLOCK)
			ON J.opType = 4 AND J.action = 1 AND J.instanceID = S.InstanceId AND J.instanceID > 1
	WHERE	S.Category = 5

	INSERT	INTO #SLAExclusion
	SELECT	ClientId, 0, AppTypeId, InstanceId, BackupsetId, AppId, MAX(Category), 0
	FROM	#ExcludedSubclient
	GROUP BY ClientId, AppTypeId, InstanceId, BackupsetId, AppId
END

IF @specialGroupExists = 1
BEGIN
	DELETE	S
	FROM	#SLAExclusion S
			LEFT OUTER JOIN (
				SELECT	DISTINCT CGA.clientId	
				FROM	APP_ClientGroupAssoc CGA WITH (NOLOCK) 
					INNER JOIN @includeGroups G ON CGA.clientGroupId = G.groupID
				) T ON S.clientId = T.clientId
	WHERE	T.clientId IS NULL
END

UPDATE	E SET LastSeenTime = CCR.LastOnlineTime
FROM	#SLAExclusion E
		INNER JOIN CCRClientToClient CCR WITH (NOLOCK) ON E.ClientId = ToClientID AND FromClientID = 2
		INNER JOIN APP_Client C WITH (NOLOCK) ON E.ClientId = C.id AND (C.status&0x1000) > 0
WHERE	E.Category = 6

SELECT	
		ClientId,
		ClientGroupId,
		AppTypeId,
		InstanceId,
		BackupsetId,
		AppId,
		Category,
		LastSeenTime	
FROM	#SLAExclusion
ORDER BY ClientId, AppTypeId, InstanceId, BackupsetId, AppId

DROP TABLE #SLAExclusion
DROP TABLE #ExcludedSubclient

--------- END SURVEY QUERY ---------
SET NOCOUNT OFF