--@querytype 	CSV
--Name:- SubclientAssociation

--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--------- BEGIN SURVEY QUERY ---------
DECLARE	@csReleaseId INT = 0
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE @csSPVersion INT = 0
IF @csReleaseId = 15
	SELECT	@csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

IF @csReleaseId < 15 OR @csReleaseId = 15 AND @csSPVersion < 7
BEGIN
	RETURN
END

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

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

CREATE TABLE #SubclientName (id INT, clientId INT, appTypeId INT, instanceId INT, backupsetId INT, workloadTypeId INT,  
	dataArchGrpId INT, logArchGrpId INT, refTime INT, modified INT, subclientStatus INT, subclientName NVARCHAR(MAX), hasComma INT)
CREATE TABLE #RptGetAppTypeName_Input (clientId INT, appTypeId INT, instanceId INT, backupsetId INT, appId INT, appTypeName VARCHAR(255), appTypeNameID INT)
CREATE TABLE #PhysicalClientName (clientId INT, physicalClientName NVARCHAR(MAX), hasComma INT)
CREATE TABLE #RacPhysicalClientName (appId INT, physicalClientName NVARCHAR(MAX), hasComma INT)
CREATE TABLE #VMClientName (id INT, name NVARCHAR(MAX), hasComma INT)
CREATE TABLE #ClientName   (id INT, name NVARCHAR(MAX), hasComma INT)
CREATE TABLE #InstanceName (id INT, name NVARCHAR(MAX), hasComma INT)
CREATE TABLE #BackupsetName(id INT, name NVARCHAR(MAX), hasComma INT)

INSERT	INTO #SubclientName
SELECT	id, clientId, appTypeId, instance, backupset, 0, 
		dataArchGrpId, logArchGrpId, refTime, modified, subclientStatus, subclientName, CHARINDEX(',', subclientName)
FROM	APP_Application
WHERE	(appTypeId < 600 OR appTypeId = 1000) AND appTypeId NOT IN (72, 85, 127, 84, 107, 121, 122)
	AND subclientStatus&(16) = 0	

IF @csReleaseId > 16 OR @csReleaseId = 16 AND @csSPVersion >= 26
BEGIN
	INSERT	INTO #RptGetAppTypeName_Input
	SELECT	clientId, appTypeId, instanceId, backupsetId, id, '', 0
	FROM	#SubclientName

	EXECUTE sp_executesql N'EXEC RptGetAppTypeName'

	UPDATE	S SET workloadTypeId = T.appTypeNameID
	FROM	#SubclientName S
			INNER JOIN #RptGetAppTypeName_Input T ON S.id = T.appId
END

UPDATE	#SubclientName SET subclientName = REPLACE(subclientName, ',', '&comma&') WHERE hasComma > 0

INSERT	INTO #ClientName
SELECT	CL.id, CL.name, CHARINDEX(',', CL.name)
FROM	(SELECT DISTINCT clientId FROM #SubclientName) S
		INNER JOIN APP_Client CL WITH(NOLOCK) ON S.clientId = CL.id

UPDATE	#ClientName SET name = REPLACE(name, ',', '&comma&') WHERE hasComma > 0

INSERT	INTO #physicalClientName
SELECT	CL.id, 
		CASE 
			WHEN CL.id = ISNULL(dag.ClusterClientId, 0 ) THEN dag.PhysicalClientName
			WHEN AC.attrVal IS NULL THEN ''
			ELSE ISNULL((SELECT name FROM APP_Client WITH(NOLOCK) WHERE ID = AC.attrVal), '') 
		END, 
		0
FROM	(SELECT DISTINCT clientId FROM #SubclientName) S
		INNER JOIN APP_Client CL ON S.clientId = CL.id
		LEFT OUTER JOIN APP_ClientProp AC WITH(NOLOCK) ON CL.Id = AC.componentNameId 
			AND AC.attrName = 'Active Physical Node' AND AC.attrVal <> CL.id AND AC.modified = 0
		LEFT OUTER JOIN
		(
			SELECT	cp.componentNameId AS ClusterClientId, MIN(cp.attrName) AS PhysicalClientName
			FROM	APP_ClientProp AS cp WITH(NOLOCK)
			WHERE	cp.attrType = 125 --PROPERTY_DAG_MEMBERSERVER for Exchange DAG
					AND cp.modified = 0
			GROUP BY cp.componentNameId		
		) AS dag ON dag.ClusterClientId = CL.id

UPDATE	#physicalClientName SET physicalClientName = REPLACE(physicalClientName, ',', '&comma&'), hasComma = 1 
WHERE	CHARINDEX(',', physicalClientName) > 0

INSERT	INTO #racPhysicalClientName
SELECT	S.id, CL.name, CHARINDEX(',', CL.name)
FROM	#SubclientName S
	INNER JOIN (SELECT instaceId, MIN(clientId) AS clientId FROM APP_OracleRacInstance WITH(NOLOCK) GROUP BY instaceId) AS I
		ON S.instanceId = I.instaceId 
	INNER JOIN APP_Client AS CL WITH(NOLOCK)
		ON CL.id = I.clientId

UPDATE	#racPhysicalClientName SET physicalClientName = REPLACE(physicalClientName, ',', '&comma&') WHERE hasComma > 0

INSERT	INTO #InstanceName
SELECT	I.id,
	 	CASE WHEN S.appTypeId IN (3, 4, 16, 6, 7, 5, 51, 52, 2, 22, 23, 80, 27, 31, 32, 37, 60, 59, 62, 77, 70, 81, 104, 61, 79, 90, 103, 125, 128, 106, 58, 134, 135, 136) 
	         THEN dbo.FixInstanceName(I.name, S.appTypeId)
			 ELSE I.name 
		END,
		0
FROM	(SELECT DISTINCT appTypeId, instanceId FROM #SubclientName) S
		INNER JOIN APP_InstanceName I WITH(NOLOCK) ON S.instanceId = I.id

UPDATE	#InstanceName SET name = REPLACE(name, ',', '&comma&'), hasComma = 1 
WHERE	CHARINDEX(',', name ) > 0

INSERT	INTO #BackupsetName
SELECT	BS.id, BS.name, CHARINDEX(',', BS.name)
FROM	(SELECT DISTINCT backupSetId FROM #SubclientName) S
		INNER JOIN APP_BackupSetName BS WITH(NOLOCK) ON S.backupSetId = BS.id

UPDATE	#BackupsetName SET name  = REPLACE(name , ',', '&comma&')
WHERE	hasComma > 0

INSERT	INTO #VMClientName
SELECT	CL.id, CL.name, CHARINDEX(',', CL.name)
FROM 	APP_Client CL WITH(NOLOCK)
	INNER JOIN APP_ClientProp CP WITH(NOLOCK) ON CP.componentNameId = CL.id 
		AND CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified = 0

UPDATE	#VMClientName SET name = REPLACE(name, ',', '&comma&') WHERE hasComma > 0


CREATE TABLE #DisabledInstance (instanceId INT)
CREATE TABLE #BackupDisabledTemp (appId INT)

IF @csReleaseId > 16 OR @csReleaseId = 16 AND @csSPVersion >= 6
EXEC sp_executesql N'INSERT INTO #DisabledInstance SELECT DISTINCT instanceID FROM JMJobAction WHERE opType = 4 AND action = 1 AND instanceID > 1'

INSERT INTO #BackupDisabledTemp
SELECT	DISTINCT A.id
FROM	APP_Application A
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND J.clientId = A.ClientId AND J.appType IN (A.AppTypeId, 0)
UNION
SELECT	DISTINCT A.id
FROM	APP_Application A
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND J.appId = A.id AND A.appTypeId <> 1000 --DR Backup cannot be disabled
UNION
SELECT	DISTINCT A.id
FROM	APP_Application A
	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 A.id = T.appId 
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND J.appId = T.subclientPolicyAppId
UNION
SELECT	DISTINCT A.id 
FROM	APP_Application A
	INNER JOIN APP_ClientGroupAssoc CGA ON A.clientId = CGA.clientId
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND CGA.clientGroupId = J.clientGroupId
		AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1
		


CREATE TABLE #RestoreDisabledTemp (appId INT)

INSERT INTO #RestoreDisabledTemp
SELECT  DISTINCT A.id
FROM    APP_Application A
	INNER JOIN JMJobAction J
	    ON J.opType = 5 AND J.action = 1 --Restore Activity
	    AND J.clientId = A.ClientId AND J.appType IN (A.AppTypeId, 0)
UNION
SELECT  DISTINCT A.id
FROM    APP_Application A
	INNER JOIN JMJobAction J
	    ON J.opType = 5 AND J.action = 1 --Restore Activity
	    AND J.appId = A.id
UNION
SELECT  DISTINCT A.id
FROM    APP_Application A
	INNER JOIN APP_ClientGroupAssoc CGA ON A.clientId = CGA.clientId
	INNER JOIN JMJobAction J
	    ON J.opType = 5 AND J.action = 1 --restore activity 
	    AND CGA.clientGroupId = J.clientGroupId
	    AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1

CREATE TABLE #Archive1Pass (appId INT, flags INT)

INSERT	INTO #Archive1Pass
SELECT	id, 1
FROM	APP_Application WITH(NOLOCK)
WHERE	appTypeId IN (SELECT componentNameId FROM APP_IDAProp WITH(NOLOCK) WHERE attrName = 'Enable Archive' AND attrVal = '1' AND modified = 0)

INSERT	INTO #Archive1Pass
SELECT	tur.componentNameId, 2
FROM	APP_SubClientProp tur WITH(NOLOCK) INNER JOIN APP_SubClientProp job WITH(NOLOCK) 
	ON tur.componentNameId = job.componentNameId
		AND tur.attrName = 'Is Turbo Subclient' AND tur.cs_attrName = CHECKSUM(N'Is Turbo Subclient') AND tur.attrVal = '1' AND tur.modified = 0
		AND job.attrName = 'First Turbo Job Id' AND job.cs_attrName = CHECKSUM(N'First Turbo Job Id') AND job.attrval != '0' AND job.modified = 0

CREATE TABLE #SubclientPolicyAssoc (appId INT, subclientPolicyAppId INT)
INSERT	INTO #SubclientPolicyAssoc
SELECT	DISTINCT componentNameId, CAST(attrVal AS INT)
FROM	(SELECT	componentNameId, attrVal, ROW_NUMBER() OVER (PARTITION BY componentNameId ORDER BY id DESC) AS RowId
	FROM	APP_SubClientProp SCP 
	WHERE	attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
	) S
WHERE	S.RowId = 1

CREATE TABLE #DataReaderInfo (appId INT, ReaderInfo  NVARCHAR(100))
INSERT	INTO #DataReaderInfo
SELECT	DISTINCT ComponentNameID,CASE WHEN Mdr.MultipleDR is NULL THEN  attrval ELSE attrval +' (M)' END 
FROM app_subclientprop a OUTER APPLY 
  (SELECT 1 MultipleDR FROM app_subclientprop b
   WHERE a.componentNameId =b.componentNameId and b.modified =0 
     AND Attrname='sys:UseMultipleStreamsWithinDrive' AND cs_attrname=CHECKSUM(N'sys:UseMultipleStreamsWithinDrive')) MDR
WHERE Attrname='use SCP filters' and cs_attrName=CHECKSUM(N'use SCP filters') and Modified=0 

CREATE TABLE #Encryption (appId INT, EncryptionInfo  NVARCHAR(100))
INSERT	INTO #Encryption
SELECT	S.componentNameId, 
		CASE WHEN attrval = 0 THEN 'None' WHEN Attrval = 1 THEN 'Media Only'
		     WHEN Attrval = 2 THEN 'Network and Media' WHEN Attrval = 3 THEN 'Network Only'
		END 
FROM	(SELECT	id, componentNameId, attrVal, 
				ROW_NUMBER() OVER (PARTITION BY componentNameId ORDER BY id DESC) AS rowId
		FROM	APP_SubClientProp
		WHERE	attrName = 'Encrypt: encryption' AND cs_attrName = CHECKSUM(N'Encrypt: encryption') AND Modified = 0) S
WHERE	S.rowId = 1

CREATE TABLE #AllowGlobalFilter (appId INT,GlobalFilter INT)
INSERT	INTO #AllowGlobalFilter
SELECT DISTINCT	ComponentNameID,1
FROM app_subclientprop  
WHERE Attrname= 'use cs setting for global filters' and cs_attrname= CHECKSUM(N'use cs setting for global filters') and Modified=0 and Attrval='1'

CREATE TABLE #SpecialSubclientTypeFlags (appId INT, flags INT)
INSERT	INTO #SpecialSubclientTypeFlags
SELECT	componentNameId, 
		SUM(CASE WHEN attrName = 'System State on Full Backup Only' THEN 1 WHEN attrName = 'DDB Backup' THEN 2
				 WHEN attrName = 'Index SubClient' THEN 4 WHEN attrName = 'SILO Copy ID' THEN 8 END)
FROM	APP_subclientProp WITH (NOLOCK) 
WHERE	attrName IN ('System State on Full Backup Only', 'DDB Backup', 'Index SubClient', 'SILO Copy ID') 
	AND cs_attrName IN (CHECKSUM(N'System State on Full Backup Only'), CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID')) 
	AND attrVal = '1' AND modified = 0
GROUP BY componentNameId

SELECT
	CASE WHEN (CL.hasComma + COALESCE(RAC.hasComma, P.hasComma, 0) + I.hasComma + BS.hasComma + A.hasComma) > 0 THEN -CL.id ELSE CL.id END AS '@clientId',
	CL.name AS '@clientName',
	COALESCE(RAC.physicalClientName, P.physicalClientName, '') AS '@physicalClientName',		
	IDA.type AS '@appTypeId',
	IDA.name AS '@agentName',
	I.id AS '@instanceId',
	I.name AS '@instanceName',
	BS.id AS '@backupsetId',
	BS.name AS '@backupsetName',
	A.id AS '@appId',
	A.subclientName AS '@subclientName',
	A.subclientStatus AS '@subclientStatus',
	dbo.UTCToLocalStringTime(DATEADD(SS, A.refTime, '1970-01-01'), @timzonename) AS '@creationTime',
	CASE WHEN A.subclientStatus&(2|4) > 0 THEN dbo.UTCToLocalStringTime(DATEADD(SS, A.modified, '1970-01-01'), @timzonename) ELSE '1970-01-01' END AS '@deconfiguredTime',
	A.dataArchGrpId '@dataPolicyId', 
	A.logArchGrpId '@logPolicyId', 
	ISNULL(SCP.subclientPolicyAppId, 0) AS '@subclientPolicyAppId',
	CASE WHEN D.appId IS NOT NULL THEN 1 ELSE 0 END AS '@backupDisabled',
	ISNULL(FF.filterFlags, 0) AS '@filterFlags',
	ISNULL(AP.flags, 0) AS '@archive1PassFlags',
	CASE WHEN R.appId IS NOT NULL  THEN 1 ELSE 0 END AS '@restoredisabled',
	ISNULL(Dri.ReaderInfo,'') AS '@readerInfo',
	ISNULL(Enc.EncryptionInfo,'') AS '@encryptionInfo',
	ISNULL(GF.GlobalFilter,0) AS '@globalfilter',
	ISNULL(S.flags,0) as '@systemstateval',
	A.workloadTypeId AS '@workloadTypeId'
FROM 
	#SubclientName A
	INNER JOIN #ClientName CL ON A.clientId = CL.id
	INNER JOIN APP_iDAType IDA ON A.appTypeId = IDA.type
	INNER JOIN #InstanceName I ON A.instanceId = I.id
	INNER JOIN #BackupSetName BS ON A.backupSetId = BS.id
	LEFT OUTER JOIN #PhysicalClientName P ON A.clientId = P.clientId
	LEFT OUTER JOIN #RacPhysicalClientName RAC ON A.id = RAC.appId
	LEFT OUTER JOIN #SubclientPolicyAssoc SCP ON SCP.appId = A.id
	LEFT OUTER JOIN APP_VMToPMMap AVM ON AVM.PMClientId = CL.id AND AVM.VMClientId = 2 -- To remove physical machines on the cluster CS as client detials (script 77) is excluding the same in getclientlistAndInitialProps.sp. We have to keep the number of cleints and subclients same in both scripts 
	LEFT OUTER JOIN #BackupDisabledTemp D ON A.id = D.appId
	LEFT OUTER JOIN #RestoreDisabledTemp R ON A.id = R.appId
	LEFT OUTER JOIN
	(
		SELECT componentNameId, 
			SUM(CASE WHEN attrName = 'use global filters' THEN 1 
				WHEN attrName = 'use cs setting for global filters' THEN 2 
				WHEN attrName = 'use SCP filters' THEN 4 ELSE 0 END) AS filterFlags
		FROM APP_SubClientProp 
		WHERE attrName IN ('use global filters', 'use cs setting for global filters', 'use SCP filters')
			AND cs_attrName IN (CHECKSUM(N'use global filters'), CHECKSUM(N'use cs setting for global filters'), CHECKSUM(N'use SCP filters'))
			AND attrVal = '1' AND modified = 0
		GROUP BY componentNameId
	) AS FF ON FF.componentNameId = A.id
	LEFT OUTER JOIN
	(
		SELECT	appId, SUM(flags) AS flags
		FROM	#Archive1Pass
		GROUP BY appId
	) AS AP ON AP.appId = A.id
	LEFT OUTER JOIN #DataReaderInfo DRi  on Dri.appId =a.id  
    LEFT OUTER JOIN #Encryption Enc On Enc.appId =a.id 
	LEFT OUTER JOIN #AllowGlobalFilter  GF ON Gf.appId=A.ID 
	LEFT OUTER JOIN #SpecialSubclientTypeFlags S on S.appId=A.ID 
WHERE 
	AVM.PMClientId IS NULL
UNION  	
SELECT
	CASE WHEN hasComma > 0 THEN -id ELSE id END AS '@clientId',
	name AS '@clientName',
	'' AS '@physicalClientName',
	106 AS '@appTypeId',
	'Virtual Server' AS '@agentName',
	0  AS '@instanceId',
	'' AS '@instanceName',
	0  AS '@backupsetId',
	'' AS '@backupsetName',
	0  AS '@appId',
	'' AS '@subclientName',
	0  AS '@subclientStatus',
	'1970-01-01' AS '@creationTime',
	'1970-01-01' AS '@deconfiguredTime',
	0  AS '@dataPolicyId', 
	0  AS '@logPolicyId', 
	0  AS '@subclientPolicyAppId',
	0  AS '@backupDisabled',
	0  AS '@filterFlags',
	0  AS '@archive1PassFlags',
	0  AS '@restoredisabled',
	'' AS '@readerInfo',
	'' AS '@encryptionInfo',
	0  AS '@globalfilter',
	0  AS '@systemstateval',
	0  AS '@workloadTypeId'
FROM	#VMClientName

DROP TABLE #SubclientName
DROP TABLE #RptGetAppTypeName_Input
DROP TABLE #PhysicalClientName
DROP TABLE #RacPhysicalClientName
DROP TABLE #VMClientName
DROP TABLE #ClientName
DROP TABLE #InstanceName
DROP TABLE #BackupsetName
DROP TABLE #SubclientPolicyAssoc 
DROP TABLE #Archive1Pass
DROP TABLE #DisabledInstance
DROP TABLE #BackupDisabledTemp
DROP TABLE #RestoreDisabledTemp
DROP TABLE #DataReaderInfo
DROP TABLE #Encryption
DROP TABLE #AllowGlobalFilter
DROP TABLE #SpecialSubclientTypeFlags
 

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

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

SET NOCOUNT OFF

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