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

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

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

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



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

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

DECLARE @TurboUsageXML XML

IF OBJECT_ID('tempdb..#subclients') IS NOT NULL DROP TABLE #subclients
IF OBJECT_ID('tempdb..#TurboSubclients') IS NOT NULL DROP TABLE #TurboSubclients
IF OBJECT_ID('tempdb..#TurboSize') IS NOT NULL DROP TABLE #TurboSize
    
CREATE TABLE #subclients (subClientId INT, t_client INT, t_appType INT)
CREATE TABLE #TurboSubclients (t_appId INT)
CREATE TABLE #TurboSize (appId INT, appType INT, appIdCount INT, appSizeMB FLOAT)

INSERT INTO #subclients
SELECT DISTINCT A.id, A.clientId, A.appTypeId
FROM  APP_Application A WITH(NOLOCK) 
		JOIN APP_subclientProp prop WITH(NOLOCK) 
		ON prop.componentNameId = A.id 
		AND prop.attrName = 'Is Turbo Subclient' 
		AND prop.cs_attrName = CHECKSUM(N'Is Turbo Subclient')
		and prop.attrval='1' and prop.modified = 0
		INNER JOIN APP_subclientProp prop2 WITH(NOLOCK)
		ON prop2.componentNameId = A.id 
		AND prop2.attrName = 'Turbo Mode Enable Stubbing' 
		AND prop2.cs_attrName = CHECKSUM(N'Turbo Mode Enable Stubbing')
		AND prop2.attrval='1'
		and prop2.modified = 0
WHERE	A.id NOT in (SELECT siloAppId FROM archGroupCopy WITH(NOLOCK)) 
	AND A.appTypeId <> 1064 AND (A.subclientStatus & 6) = 0
       
DELETE S FROM #subclients S 
	INNER JOIN JMJobAction J ON J.opType = 4 AND J.action = 1 AND J.clientId = S.t_client AND J.appType IN (S.t_appType, 0)
       
DELETE S FROM #subclients S 
	INNER JOIN JMJobAction J ON J.opType = 4 AND J.action = 1 AND J.appId = S.subClientId
       
DELETE S FROM #subclients S 
	INNER JOIN (
		SELECT	componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId
		FROM	APP_SubClientProp
		WHERE	attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
	) T ON S.subClientId = T.appId 
	INNER JOIN JMJobAction J ON J.opType = 4 AND J.action = 1 AND J.appId = T.subclientPolicyAppId
	
DELETE S FROM #subclients S 
	INNER JOIN APP_ClientGroupAssoc CGA ON S.t_client = CGA.clientId
	INNER JOIN JMJobAction J ON J.opType = 4 AND J.action = 1 AND J.clientGroupId = CGA.clientGroupId AND J.appId = S.subClientId
		AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1
	
--Agent Level Check for the Enable Archive. . For ORACLE/RAC, Agent Level check is not enough. We need to check for 'Is Turbo Subclient'
INSERT INTO #TurboSubclients
SELECT subClientId
FROM  #subclients      
	JOIN APP_IDAName N WITH(NOLOCK) ON t_client = N.clientId AND t_appType = N.appTypeId
	JOIN APP_IDAProp P WITH(NOLOCK) ON P.componentNameID = N.id
WHERE P.attrName = 'Enable Archive' AND P.attrVal = '1' AND P.modified = 0
AND N.appTypeId NOT IN (22, 80, 54)


--Turbo check for Mailbox and Oracle is ONLY in 10.0
--- SubClient Level Check: Mailbox and Oracle With Turbo Subclients should count against Archive bucket. 
INSERT INTO #TurboSubclients
SELECT  A.subClientId FROM #subclients A WHERE t_appType IN (22, 80, 54) 

--Add exchange one pass new agent subclients where cleanup policy is enabled.
IF OBJECT_ID('APP_EmailConfigPolicies', N'U') IS NOT NULL
	EXEC sp_executesql N'INSERT INTO #TurboSubclients SELECT DISTINCT E.subClientId FROM APP_EmailConfigPolicyAssoc E INNER JOIN APP_EmailConfigPolicies P ON P.componentNameId = E.assocId WHERE E.modified = 0 AND P.modified = 0 AND P.policyType = 2'

;WITH LastFullCycle (t_appId, t_fullCycleNum) AS
(
	SELECT appId, MAX(fullCycleNum)
	FROM  JMBkpStats WITH(NOLOCK) JOIN #TurboSubclients ON appId = t_appId
	WHERE commcellID = 2 AND status in (1, 3, 14) AND dataStatus = 0 AND totalUncompBytes > 0
	GROUP BY appId
)
INSERT      #TurboSize
SELECT      appId, appType, COUNT(Distinct appId), SUM(1.0*totalUncompBytes/1024.0/1024.0)
FROM  JMBkpStats WITH(NOLOCK) JOIN LastFullCycle ON appId = t_appId AND fullCycleNum = t_fullCycleNum
WHERE commcellID = 2 AND status in (1, 3, 14) AND dataStatus = 0 AND totalUncompBytes > 0
GROUP BY appId, appType


SET @surveyXML =
(
SELECT DISTINCT
                I.type as '@AppType', 
                I.name as '@Name', 
                COUNT (Distinct appId) as '@AppIdCount',
                SUM (AppSizeMB) as '@SizeMB'                
FROM  #TurboSize T INNER JOIN APP_iDAType I ON T.appType = I.type
GROUP BY I.type, I.name
FOR XML PATH('TurboSubClient')
)				
 
--------- 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')
)

SET NOCOUNT OFF

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