

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/APPCCSCreateClientLoadXMLDocument.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/APPCCSCreateClientLoadXMLDocument.sp,v $ $Id: APPCCSCreateClientLoadXMLDocument.sp,v 1.1.2.11.20.1 2021/06/25 22:51:09 abilbrey Exp $";
-- Procedure Name
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='APPCCSCreateClientLoadXMLDocument')
BEGIN
	print '>>> Drop Stored Procedure: APPCCSCreateClientLoadXMLDocument <<<'
	drop procedure APPCCSCreateClientLoadXMLDocument
END
IF EXISTS (select * from GxQscripts where name='APPCCSCreateClientLoadXMLDocument')
	delete from GxQscripts where name = 'APPCCSCreateClientLoadXMLDocument'
GO

IF EXISTS (select * from GXDBVersions where aliasname='APPCCSCreateClientLoadXMLDocument')
	delete from GXDBVersions where aliasname = 'APPCCSCreateClientLoadXMLDocument'
GO
print '... Creating Procedure: APPCCSCreateClientLoadXMLDocument'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure APPCCSCreateClientLoadXMLDocument
-- Input arguments
-- The CCS Client to Load
  @inClientId INT,
-- 1 - return XML as result set, 0 - return XML in output variables
  @resultSet INT = 1,
--  event.getDebugLevel()
  @inLogLevel INT = 0,
  @outputXML XML = '' OUTPUT
AS
  DECLARE @o_resultSetXML XML = NULL;
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	--DECLARE @resultSetXML  XML = NULL
	-- Timer variables
	DECLARE @sdt		DATETIME2
	DECLARE @edt		DATETIME2
	DECLARE @emsg		VARCHAR(128) = ''
	-- Is CCS Functionality enabled on the CommServer?
    DECLARE @ccsEnabled INT = 0
    SELECT @ccsEnabled = CAST(value AS INT) FROM GXGlobalParam WITH(NOLOCK) WHERE name = 'CommServCCSEnabled'
	IF (@ccsEnabled = 0)
	BEGIN
		RAISERROR('ERROR: APPCCSCreateClientLoadXMLDocument - CCS is NOT enabled on the Commserver.', 16, 1)
		RETURN
	END
	-- Check to make sure the inputted clientId is a CCS Client
	IF NOT EXISTS (
		SELECT
			1
		FROM APP_ClientProp scp WITH(NOLOCK)
		WHERE
			scp.componentNameId = @inClientId
			AND scp.attrName = N'CCS Enabled'
			AND scp.modified = 0
			AND scp.attrVal = N'1'
	)
	BEGIN
		SET @emsg = 'ERROR: APPCCSCreateClientLoadXMLDocument: Inputted ClientId [' + CAST(@inClientId AS VARCHAR(12)) + '] is NOT a CCS Enabled Client.'
		RAISERROR(@emsg, 16, 1)
		RETURN
	END
	IF (@inLogLevel >= 7)
	BEGIN
		-- time the execution of the SP below
		SET @sdt = SYSDATETIME()
	END
	-- CS TimeStamps and client skew time
	DECLARE @loadStartTime		INT = DBO.GetUnixTime(GETUTCDATE())
	DECLARE @loadEndTime		INT = 0
	DECLARE @clientSkewTime		INT = 0
	SELECT
		@clientSkewTime = c.clientSkewTime
	FROM APP_Client c WITH(NOLOCK)
	WHERE
		c.id = @inclientId
	-- Get all Clients needed locally
	DECLARE @ClientList TABLE (
		clientId	INT PRIMARY KEY,
		type		TINYINT		-- 1 default, 2 commcell, 4 laptop bits
	)
	INSERT INTO @ClientList VALUES (1, 1), (2, 2), (@inclientId, 4)
	-- Get Association SubClient Policy Data from the CommCell Data for the inputted client
	DECLARE @ASCPs TABLE (
		id			INT PRIMARY KEY,
		instance	INT,
		backupSet	INT
	)
	INSERT INTO @ASCPs
		SELECT DISTINCT
			ascp.id,
			ascp.instance,
			ascp.backupSet
		FROM APP_Application a WITH(NOLOCK)
			INNER JOIN APP_SubClientProp scp WITH(NOLOCK) ON
				a.clientId = @inclientId
				AND scp.componentNameId = a.id
				AND scp.attrName = N'Associated subclient Policy'
				--== NOTE: Could have been previously assocaited - grey area to investigate and test were hashes may mis-match
				--AND scp.modified = 0
				AND scp.cs_attrName = CHECKSUM( N'Associated subclient Policy')
			INNER JOIN APP_Application ascp WITH(NOLOCK) ON
				scp.attrVal = CAST(ascp.id AS NVARCHAR(12))
				AND ascp.appTypeId = 1030		-- Association SubClient Policy AppTypeId
				AND ascp.clientId = 2			-- CommCell
	-- compact list of key ids and types
	DECLARE @IdList TABLE (
		type		INT,
		id			INT,
		PRIMARY KEY (type, id)
	)
	INSERT INTO @IdList
		SELECT
			4,
			a.id
		FROM APP_Application a WITH(NOLOCK)
		WHERE
			a.clientId = @inclientId
		UNION
		SELECT
			4,
			ascp.id
		FROM @ASCPs ascp		-- ASCP APP_Application Table data - Association Subclient Policy rows
		UNION
		SELECT
			11,
			bs.id
		FROM  APP_Application a WITH(NOLOCK)
			INNER JOIN APP_BackupSetName bs WITH(NOLOCK) ON
				bs.id = a.backupSet
		WHERE
			a.clientId = @inclientId
		UNION
		SELECT
			11,
			bs.id
		FROM @ASCPs ascp		-- Association Subclient Policy rows
			--INNER JOIN APP_Application a WITH(NOLOCK) ON
			--	ascp.id = a.id
			INNER JOIN APP_BackupSetName bs WITH(NOLOCK) ON
				bs.id = ascp.backupSet
		UNION
		SELECT
			6,
			ida.id
		FROM  APP_IDAName ida WITH(NOLOCK)
		WHERE
			ida.clientId = @inclientId
	-- NOTE: LIKE / NOT LIKE Filtering is hardcoded
	-- SubClient Property NOT LIKE filtering
	IF OBJECT_ID('tempdb.dbo.#SCPNotLikeIds') IS NOT NULL
		DROP TABLE #SCPNotLikeIds
	CREATE TABLE #SCPNotLikeIds (
		id		INT PRIMARY KEY
	)
	DECLARE @scpIdCmd NVARCHAR(MAX) = (
		SELECT
			CASE WHEN q.rn > 1 THEN ' UNION ' ELSE ' ' END
			+ 'SELECT cp.id FROM APP_SubClientProp cp WITH(NOLOCK) INNER JOIN APP_Application a WITH(NOLOCK) ON a.clientId = ' + CAST(@inClientId AS NVARCHAR(12)) + ' AND cp.componentNameId = a.id WHERE cp.attrName LIKE ' + '''' + q.filter + ''''
		FROM (
				SELECT
					f.value('@value', 'NVARCHAR(128)') filter,
					ROW_NUMBER() OVER (ORDER BY f.value('@value', 'NVARCHAR(1024)')) rn
				FROM APP_CCSXMLMapping WITH(NOLOCK)
					CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
				WHERE
					id = 5		-- APP_SubClientProp type
					AND f.value('../@name', 'NVARCHAR(128)') = 'attrName'
					AND f.value('../@opType', 'NVARCHAR(128)') = 'NOT LIKE'
			) q
		FOR XML PATH('')
	)
	IF (@scpIdCmd IS NOT NULL AND @scpIdCmd <> N'')
	BEGIN
		-- Get all SCP matching these filters
		INSERT INTO #SCPNotLikeIds
			EXEC(@scpIdCmd)
	END
	-- Create Filters table for better performance
	IF OBJECT_ID('TempDb..#Filters') IS NOT NULL
		DROP TABLE #Filters
	CREATE TABLE #Filters (
		tableType		INT,
		attrName		NVARCHAR(128)
	)
	CREATE CLUSTERED INDEX Filter_idx ON #Filters (tableType, attrName)
	INSERT INTO #Filters (tableType, attrName)
		SELECT
			id,
			f.value('@value', 'NVARCHAR(128)') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 2
			AND f.value('../@name', 'NVARCHAR(128)') = 'attrName'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'EXCLUDE'
		UNION ALL
		SELECT
			id,
			f.value('@value', 'NVARCHAR(128)') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 5
			AND f.value('../@name', 'NVARCHAR(128)') = 'attrName'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'EXCLUDE'
		UNION ALL
		SELECT
			id,
			f.value('@value', 'NVARCHAR(128)') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 7
			AND f.value('../@name', 'NVARCHAR(128)') = 'name'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'INCLUDE'
		UNION ALL
		SELECT
			id,
			f.value('@value', 'NVARCHAR(128)') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 10
			AND f.value('../@name', 'NVARCHAR(128)') = 'attrName'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'EXCLUDE'
		UNION ALL
		SELECT
			id,
			f.value('@value', 'NVARCHAR(128)') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 12
			AND f.value('../@name', 'NVARCHAR(128)') = 'attrName'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'EXCLUDE'
	IF OBJECT_ID('TempDb..#FilterKeys') IS NOT NULL
		DROP TABLE #FilterKeys
	CREATE TABLE #FilterKeys (
		tableType		INT,
		filterId		INT
	)
	CREATE CLUSTERED INDEX FilterKeys_idx ON #FilterKeys (tableType, filterId)
	INSERT INTO #FilterKeys (tableType, filterId)
		SELECT
			id,
			f.value('@value', 'INT') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 20
			AND f.value('../@name', 'NVARCHAR(128)') = 'propertyTypeId'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'EXCLUDE'
		UNION ALL
		SELECT
			id,
			f.value('@value', 'INT') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 24
			AND f.value('../@name', 'NVARCHAR(128)') = 'appTypeId'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'INCLUDE'
	IF OBJECT_ID('tempdb.dbo.#ClientXMLRecords') IS NOT NULL
		DROP TABLE #ClientXMLRecords
	CREATE TABLE #ClientXMLRecords (
		id				INT IDENTITY(1,1) PRIMARY KEY,	-- sequenceNumber
		tableType		INT,
		data			XML
	)
	INSERT INTO #ClientXMLRecords
		-- Note this is an ORDER List on how to insert data rows into the CCSDb without FK Lookups
		SELECT
			q.tableType,
			q.rowNodes
		FROM (
				SELECT
					1 tableType,
					(
						SELECT
							clientTableRowList.*
						FROM APP_Client clientTableRowList WITH(NOLOCK)
							INNER JOIN @ClientList cl ON
								cl.clientId = clientTableRowList.id
								AND (cl.type & 7) > 0		-- want all clients
						FOR XML AUTO, TYPE
					) rowNodes
				UNION ALL
				SELECT
					18 tableType,
					(
						SELECT
							commCellTableRowList.id,
							commCellTableRowList.number,
							commCellTableRowList.clientId,
							commCellTableRowList.parentId,
							commCellTableRowList.aliasName,
							commCellTableRowList.flags,
							commCellTableRowList.refTime,
							commCellTableRowList.userName,
							commCellTableRowList.userPassword,
							commCellTableRowList.smtpServer,
							commCellTableRowList.timeZone,
							commCellTableRowList.ipAddress,
							commCellTableRowList.modified,
							commCellTableRowList.ccOpMode,
							commCellTableRowList.drIpAddress,
							-- encNumber column can have illegal XML Characters
							dbo.EncodeInvalidXMLChar(commCellTableRowList.encNumber) encNumber,
							commCellTableRowList.csGUID,
							commCellTableRowList.type
						FROM APP_CommCell commCellTableRowList WITH(NOLOCK)
						WHERE
							commCellTableRowList.id IN (
								SELECT
									f.value('@value', 'INTEGER') filter
								FROM APP_CCSXMLMapping WITH(NOLOCK)
									CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
								WHERE
									id = 18
									AND f.value('../@name', 'NVARCHAR(128)') = 'id'
									AND f.value('../@opType', 'NVARCHAR(128)') = 'INCLUDE'
							)
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					7 tableType,
					(
						SELECT
							gxGlobalParamTableRowList.*
						FROM GXGlobalParam gxGlobalParamTableRowList WITH(NOLOCK)
							INNER JOIN #Filters f ON		-- white list of properties
								f.tableType = 7
								AND f.attrName = gxGlobalParamTableRowList.name
						FOR XML AUTO, TYPE
					)
				UNION ALL
				-- Data only used internally in AppMgr and not needed outside by other subsystems yet.
				--SELECT
				--	9 tableType,
				--	(
				--		SELECT
				--			schedTimeZoneTableRowList.*
				--		FROM SchedTimeZone schedTimeZoneTableRowList WITH(NOLOCK)
				--		WHERE
				--			schedTimeZoneTableRowList.TimeZoneID > 0
				--		FOR XML AUTO, TYPE
				--	)
				--UNION ALL
				SELECT
					2 tableType,
					(
						SELECT
							clientPropTableRowList.id,
							clientPropTableRowList.componentNameId,
							dbo.EncodeInvalidXMLChar(clientPropTableRowList.attrName) attrName,
							clientPropTableRowList.attrType,
							dbo.EncodeInvalidXMLChar(clientPropTableRowList.attrVal) attrVal,
							clientPropTableRowList.created,
							clientPropTableRowList.modified,
							clientPropTableRowList.ccpId
						FROM APP_ClientProp clientPropTableRowList WITH(NOLOCK)
							LEFT OUTER JOIN #Filters f ON
								f.tableType = 2
								AND f.attrName = clientPropTableRowList.attrName
						WHERE
							clientPropTableRowList.componentNameId = @inclientId
							AND f.tableType IS NULL
						FOR XML AUTO, TYPE
					)
				-- Start Client APP_Aplication Table Population
				UNION ALL
				SELECT
					8 tableType,
					(
						SELECT
							idaTypeTableRowList.*
						FROM APP_iDAType idaTypeTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									t.type
								FROM APP_IDAName ida WITH(NOLOCK)
									INNER JOIN APP_iDAType t WITH(NOLOCK) ON
										t.type = ida.appTypeId
								WHERE
									ida.clientId = @inclientId
								UNION
								SELECT
									t.type
								FROM @ASCPs ascp		-- Association Subclient Policy rows
									INNER JOIN APP_Application a WITH(NOLOCK) ON
										ascp.id = a.id
									INNER JOIN APP_iDAType t WITH(NOLOCK) ON
										t.type = a.appTypeId
							) t ON
								t.type = idaTypeTableRowList.type
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					6 tableType,
					(
						SELECT
							idaNameTableRowList.*
						FROM  APP_IDAName idaNameTableRowList WITH(NOLOCK)
						WHERE
							idaNameTableRowList.clientId = @inclientId
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					13 tableType,
					(
						SELECT
							instanceNameTableRowList.id,
							dbo.EncodeInvalidXMLChar(instanceNameTableRowList.name) name,
							instanceNameTableRowList.refTime,
							instanceNameTableRowList.modified,
							instanceNameTableRowList.status,
							instanceNameTableRowList.ccpId,
							instanceNameTableRowList.ccpTime,
							instanceNameTableRowList.origCCId,
							instanceNameTableRowList.GUID
						FROM APP_InstanceName instanceNameTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									i.id
								FROM  APP_Application a WITH(NOLOCK)
									INNER JOIN APP_InstanceName i WITH(NOLOCK) ON
										i.id = a.instance
								WHERE
									a.clientId = @inclientId
								UNION
								SELECT
									i.id
								FROM @ASCPs ascp 		-- Association Subclient Policy rows
									--INNER JOIN APP_Application a WITH(NOLOCK) ON
									--	ascp.id = a.id
									INNER JOIN APP_InstanceName i WITH(NOLOCK) ON
										i.id = ascp.instance
							) t ON
								t.id = instanceNameTableRowList.id
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					11 tableType,
					(
						SELECT
							backupSetNameTableRowList.*
						FROM APP_BackupSetName backupSetNameTableRowList WITH(NOLOCK)
							INNER JOIN @IdList t ON
								t.type = 11
								AND t.id = backupSetNameTableRowList.id
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					4 tableType,
					(
						SELECT
							applicationTableRowList.*
						FROM APP_Application applicationTableRowList WITH(NOLOCK)
							INNER JOIN @IdList t ON
								t.type = 4
								AND t.id = applicationTableRowList.id
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					10 tableType,
					(
						SELECT
							idaPropTableRowList.id,
							idaPropTableRowList.componentNameId,
							dbo.EncodeInvalidXMLChar(idaPropTableRowList.attrName) attrName,
							idaPropTableRowList.attrType,
							dbo.EncodeInvalidXMLChar(idaPropTableRowList.attrVal) attrVal,
							idaPropTableRowList.created,
							idaPropTableRowList.modified,
							idaPropTableRowList.ccpId
						FROM APP_IDAName ida WITH(NOLOCK)
							INNER JOIN APP_iDAProp idaPropTableRowList WITH(NOLOCK) ON
								idaPropTableRowList.componentNameId = ida.id
							LEFT OUTER JOIN #Filters f ON
								f.tableType = 10
								AND f.attrName = idaPropTableRowList.attrName
						WHERE
							ida.clientId = @inclientId
							AND f.tableType IS NULL
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					14 tableType,
					(
						SELECT
							instancePropTableRowList.id,
							instancePropTableRowList.componentNameId,
							dbo.EncodeInvalidXMLChar(instancePropTableRowList.attrName) attrName,
							instancePropTableRowList.attrType,
							dbo.EncodeInvalidXMLChar(instancePropTableRowList.attrVal) attrVal,
							instancePropTableRowList.created,
							instancePropTableRowList.modified,
							instancePropTableRowList.ccpId
						FROM APP_InstanceProp instancePropTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									ip.id
								FROM  APP_Application a WITH(NOLOCK)
									INNER JOIN APP_InstanceProp ip WITH(NOLOCK) ON
										ip.componentNameId = a.instance
								WHERE
									a.clientId = @inclientId
								UNION
								SELECT
									ip.id
								FROM @ASCPs ascp		-- Association Subclient Policy rows
									--INNER JOIN APP_Application a WITH(NOLOCK) ON
									--	ascp.id = a.id
									INNER JOIN APP_InstanceProp ip WITH(NOLOCK) ON
										ip.componentNameId = ascp.instance
							) t ON
								t.id = instancePropTableRowList.id
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					12 tableType,
					(
						SELECT
							backupSetPropTableRowList.id,
							backupSetPropTableRowList.componentNameId,
							dbo.EncodeInvalidXMLChar(backupSetPropTableRowList.attrName) attrName,
							backupSetPropTableRowList.attrType,
							dbo.EncodeInvalidXMLChar(backupSetPropTableRowList.attrVal) attrVal,
							backupSetPropTableRowList.created,
							backupSetPropTableRowList.modified,
							backupSetPropTableRowList.ccpId
						FROM APP_BackupSetProp backupSetPropTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									bsp.id
								FROM  APP_Application a WITH(NOLOCK)
									INNER JOIN APP_BackupSetProp bsp WITH(NOLOCK) ON
										bsp.componentNameId = a.backupSet
									LEFT OUTER JOIN #Filters f ON
										f.tableType = 12
										AND f.attrName = bsp.attrName
								WHERE
									a.clientId = @inclientId
									-- Black Listed Rows not shipped to the CCS Client
									AND f.tableType IS NULL
								UNION
								SELECT
									bsp.id
								FROM @ASCPs ascp		-- Association Subclient Policy rows
									--INNER JOIN APP_Application a WITH(NOLOCK) ON
									--	ascp.id = a.id
									INNER JOIN APP_BackupSetProp bsp WITH(NOLOCK) ON
										bsp.componentNameId = ascp.backupSet
									LEFT OUTER JOIN #Filters f ON
										f.tableType = 12
										AND f.attrName = bsp.attrName
								WHERE
									-- Black Listed Rows not shipped to the CCS Client
									f.tableType IS NULL
							) t ON
								t.id = backupSetPropTableRowList.id
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					5 tableType,
					(
						SELECT
							subClientPropTableRowList.id,
							subClientPropTableRowList.componentNameId,
							dbo.EncodeInvalidXMLChar(subClientPropTableRowList.attrName) attrName,
							subClientPropTableRowList.attrType,
							dbo.EncodeInvalidXMLChar(subClientPropTableRowList.attrVal) attrVal,
							subClientPropTableRowList.created,
							subClientPropTableRowList.modified,
							subClientPropTableRowList.ccpId,
							subClientPropTableRowList.cs_attrName
						FROM APP_SubClientProp subClientPropTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									scp.id
								FROM  APP_Application a WITH(NOLOCK)
									INNER JOIN APP_SubClientProp scp WITH(NOLOCK) ON
										scp.componentNameId = a.id
									LEFT OUTER JOIN #Filters f ON
										f.tableType = 5
										AND f.attrName = scp.attrName
								WHERE
									a.clientId = @inclientId
									-- Black Listed Rows not shipped to the CCS Client
									AND f.tableType IS NULL
									AND scp.id NOT IN (
										SELECT id FROM #SCPNotLikeIds
									)
								UNION
								SELECT
									scp.id
								FROM @ASCPs ascp		-- Association Subclient Policy rows
									--INNER JOIN APP_Application a WITH(NOLOCK) ON
									--	ascp.id = a.id
									INNER JOIN APP_SubClientProp scp WITH(NOLOCK) ON
										scp.componentNameId = ascp.id
									LEFT OUTER JOIN #Filters f ON
										f.tableType = 5
										AND f.attrName = scp.attrName
								WHERE
									-- Black Listed Rows not shipped to the CCS Client
									f.tableType IS NULL
									AND scp.id NOT IN (
										SELECT id FROM #SCPNotLikeIds
									)
							) t ON
								t.id = subClientPropTableRowList.id
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					16 tableType,
					(
						SELECT
							instFilterFileTableRowList.componentNameId,
							instFilterFileTableRowList.type,
							dbo.EncodeInvalidXMLChar(instFilterFileTableRowList.fileName) fileName,
							instFilterFileTableRowList.created,
							instFilterFileTableRowList.modified,
							instFilterFileTableRowList.id
						FROM  APP_InstFilterFile instFilterFileTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									ff.id
								FROM  APP_Application a WITH(NOLOCK)
									INNER JOIN APP_InstFilterFile ff WITH(NOLOCK) ON
										ff.componentNameId = a.instance
								WHERE
									a.clientId = @inclientId
								UNION
								SELECT
									ff.id
								FROM @ASCPs ascp		-- Association Subclient Policy rows
									--INNER JOIN APP_Application a WITH(NOLOCK) ON
									--	ascp.id = a.id
									INNER JOIN APP_InstFilterFile ff WITH(NOLOCK) ON
										ff.componentNameId = ascp.instance
							) t ON
								t.id = instFilterFileTableRowList.id
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					15 tableType,
					(
						SELECT
							backupSetFilterFileTableRowList.*
						FROM APP_BackupSetFilterFile backupSetFilterFileTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									ff.componentNameId
								FROM  APP_Application a WITH(NOLOCK)
									INNER JOIN APP_BackupSetFilterFile ff WITH(NOLOCK) ON
										ff.componentNameId = a.instance
								WHERE
									a.clientId = @inclientId
								UNION
								SELECT
									ff.componentNameId
								FROM @ASCPs ascp		-- Association Subclient Policy rows
									--INNER JOIN APP_Application a WITH(NOLOCK) ON
									--	ascp.id = a.id
									INNER JOIN APP_BackupSetFilterFile ff WITH(NOLOCK) ON
										ff.componentNameId = ascp.backupSet
							) t ON
								t.componentNameId = backupSetFilterFileTableRowList.componentNameId
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					17 tableType,
					(
						SELECT
							scFilterFileTableRowList.id,
							scFilterFileTableRowList.componentNameId,
							scFilterFileTableRowList.type,
							dbo.EncodeInvalidXMLChar(scFilterFileTableRowList.fileName) fileName,
							scFilterFileTableRowList.created,
							scFilterFileTableRowList.modified,
							scFilterFileTableRowList.ccpId,
							scFilterFileTableRowList.cs_fileName
						FROM APP_ScFilterFile scFilterFileTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									ff.id
								FROM  APP_Application a WITH(NOLOCK)
									INNER JOIN APP_ScFilterFile ff WITH(NOLOCK) ON
										ff.componentNameId = a.id
								WHERE
									a.clientId = @inclientId
								UNION
								SELECT
									ff.id
								FROM @ASCPs ascp		-- Association Subclient Policy rows
									--INNER JOIN APP_Application a WITH(NOLOCK) ON
									--	ascp.id = a.id
									INNER JOIN APP_ScFilterFile ff WITH(NOLOCK) ON
										ff.componentNameId = ascp.id
							) t ON
								t.id = scFilterFileTableRowList.id
						FOR XML AUTO, TYPE
					)
				-- End Client APP_Aplication Table Population
				UNION ALL
				SELECT
					20 tableType,
					(
						SELECT
							componentPropTableRowList.*
						FROM APP_ComponentProp componentPropTableRowList WITH(NOLOCK)
							INNER JOIN (
								SELECT
									cp.id
								FROM APP_ComponentProp cp WITH(NOLOCK)
									INNER JOIN @ClientList cl ON
										cl.clientId = cp.componentId
										AND (cl.type & 3) > 0			-- CommCell clients clients
										AND cp.componentType = 1		-- CV_COMPONENT_TYPE_COMMCELL
										AND cp.propertyTypeId IN (1, 10, 11, 12, 13)	-- CV_COMPONENT_COMMCELL_CI_ENGINE_NAME_PROP,CV_COMPONENT_COMMCELL_CA_CERTIFICATE,CV_COMPONENT_COMMCELL_CA_RSA_PRIVATE,CV_COMPONENT_COMMCELL_COMMCELL_CERTIFICATE,CV_COMPONENT_COMMCELL_COMMCELL_RSA_PRIVATE
								UNION ALL
								SELECT
									cp.id
								FROM APP_ComponentProp cp WITH(NOLOCK)
								WHERE
									cp.componentType = 3				-- CV_COMPONENT_TYPE_CLIENT
									AND cp.componentId = @inclientId
								UNION ALL
								SELECT
									cp.id
								FROM @IdList l
									INNER JOIN APP_ComponentProp cp WITH(NOLOCK) ON
										cp.componentType = 6		-- CV_COMPONENT_TYPE_SUBCLIENT
										AND cp.componentId = l.id
										AND l.type = 4
								UNION ALL
								SELECT
									cp.id
								FROM @IdList l
									INNER JOIN APP_ComponentProp cp WITH(NOLOCK) ON
										cp.componentType = 5		-- CV_COMPONENT_TYPE_BACKUPSET
										AND cp.componentId = l.id
										AND l.type = 11
								UNION ALL
								SELECT
									cp.id
								FROM  @IdList l
									INNER JOIN APP_ComponentProp cp WITH(NOLOCK) ON
										cp.componentType = 4		-- CV_COMPONENT_TYPE_IDA
										AND cp.componentId = l.id
										AND l.type = 6
								UNION ALL
								SELECT		-- Client Group Filtering implemented in SP15
									cp.id
								FROM APP_ComponentProp cp WITH(NOLOCK)
									INNER JOIN APP_ClientGroupAssoc cga WITH(NOLOCK) ON
										cga.clientGroupId = cp.componentId
									INNER JOIN @ClientList cl ON
										cl.clientId = cga.clientId
										AND (cl.type & 4) > 0							-- laptop
										AND cp.componentType = 8						-- CV_COMPONENT_TYPE_CLIENTGROUP
										AND cp.propertyTypeId IN (3620, 3621, 3622)		-- CV_COMPONENT_PROP_WIN_FILTERS, CV_COMPONENT_PROP_UNIX_FILTERS, CV_COMPONENT_PROP_USE_CLIENT_GROUP_FILTER
							) t ON
								t.id = componentPropTableRowList.id
								AND componentPropTableRowList.propertyTypeId NOT IN (
									SELECT
										filterId
									FROM #FilterKeys
									WHERE
										tableType = 20
								)
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					3 tableType,
					(
						SELECT
							platformTableRowList.*
						FROM APP_Platform platformTableRowList WITH(NOLOCK)
							INNER JOIN @ClientList cl ON
								cl.clientId = platformTableRowList.clientId
								AND (cl.type & 6) > 0			-- commcell and laptop
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					26 tableType,
					(
						SELECT
							clientGroupAssocTableRowList.*
						FROM APP_ClientGroupAssoc clientGroupAssocTableRowList WITH(NOLOCK)
							INNER JOIN @ClientList cl ON
								cl.clientId = clientGroupAssocTableRowList.clientId
								AND (cl.type & 6) > 0			-- commcell and laptop
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					21 tableType,
					(
						SELECT
							extendedPropertiesTableRowList.clientId,
							extendedPropertiesTableRowList.appTypeId,
							extendedPropertiesTableRowList.instance,
							extendedPropertiesTableRowList.backupSet,
							extendedPropertiesTableRowList.subclientId,
							extendedPropertiesTableRowList.attrType,
							dbo.EncodeInvalidXMLChar(extendedPropertiesTableRowList.attrName) attrName,
							dbo.EncodeInvalidXMLChar(extendedPropertiesTableRowList.attrVal) attrVal,
							extendedPropertiesTableRowList.MD5,
							extendedPropertiesTableRowList.created,
							extendedPropertiesTableRowList.modified,
							extendedPropertiesTableRowList.ccpId
						FROM APP_ExtendedProperties extendedPropertiesTableRowList WITH(NOLOCK)
							INNER JOIN @ClientList cl ON
								cl.clientId = extendedPropertiesTableRowList.clientId
								AND (cl.type & 5) > 0			-- default and laptop
						WHERE
							(
								extendedPropertiesTableRowList.clientId <> 1
								OR (
									extendedPropertiesTableRowList.clientId = 1
									AND extendedPropertiesTableRowList.attrType = 129		-- only need this data to be shipped to CCS Laptop for default clientId 1
								)
							)
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					19 tableType,
					(
						SELECT
							fileTypeAndExtensionsTableRowList.*
						FROM App_FileTypeAndExtensions fileTypeAndExtensionsTableRowList WITH(NOLOCK)
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					23 tableType,
					(
						SELECT
							appTypeGroupTableRowList.*
						FROM APP_AppTypeGroup appTypeGroupTableRowList WITH(NOLOCK)
						WHERE	-- Previous implementation was getting duplicate rows
							appTypeGroupTableRowList.appTypeGroupId IN (
								SELECT
									atga.appGroupId
								FROM APP_AppTypeGroupAssoc atga WITH(NOLOCK)
								WHERE
									atga.appTypeId IN (
										SELECT
											f.value('@value', 'INT') id
										FROM APP_CCSXMLMapping m WITH(NOLOCK)
											CROSS APPLY m.filters.nodes('/Filters/Column/Filter') d(f)
										WHERE
											m.id = 24		-- yes want 24 not 23 tabletype easier filter to use
											AND f.value('../@name', 'VARCHAR(128)') = 'appTypeId'
											AND f.value('../@opType', 'NVARCHAR(128)') = 'INCLUDE'
									)
							)
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					24 tableType,
					(
						SELECT
							appTypeGroupAssocTableRowList.*
						FROM APP_AppTypeGroupAssoc appTypeGroupAssocTableRowList WITH(NOLOCK)
						WHERE
							appTypeGroupAssocTableRowList.appTypeId IN (
								SELECT
									filterId
								FROM #FilterKeys
								WHERE
									tableType = 24
							)
						FOR XML AUTO, TYPE
					)
				UNION ALL
				SELECT
					25 tableType,
					(
						SELECT
							jobErrorDecisionRuleTableRowList.*
						FROM (
							SELECT
								jobErrorDecisionRuleTableRowList.id,
								jobErrorDecisionRuleTableRowList.EntityId,
								jobErrorDecisionRuleTableRowList.EntityType,
								jobErrorDecisionRuleTableRowList.AppEntityType,
								jobErrorDecisionRuleTableRowList.AppTypeId,
								jobErrorDecisionRuleTableRowList.isEnabled,
								CAST(jobErrorDecisionRuleTableRowList.decisionRule AS NVARCHAR(MAX)) decisionRule
							FROM APP_JobErrorDecisionRule jobErrorDecisionRuleTableRowList WITH(NOLOCK)
							WHERE
								jobErrorDecisionRuleTableRowList.entityType = 1		-- CommCell Entity Type
								AND jobErrorDecisionRuleTableRowList.entityId = 2
							UNION ALL
							SELECT
								jobErrorDecisionRuleTableRowList.id,
								jobErrorDecisionRuleTableRowList.EntityId,
								jobErrorDecisionRuleTableRowList.EntityType,
								jobErrorDecisionRuleTableRowList.AppEntityType,
								jobErrorDecisionRuleTableRowList.AppTypeId,
								jobErrorDecisionRuleTableRowList.isEnabled,
								CAST(jobErrorDecisionRuleTableRowList.decisionRule AS NVARCHAR(MAX)) decisionRule
							FROM APP_JobErrorDecisionRule jobErrorDecisionRuleTableRowList WITH(NOLOCK)
							WHERE
								jobErrorDecisionRuleTableRowList.entityType = 3		-- Client Entity Type
								AND jobErrorDecisionRuleTableRowList.entityId IN (2, @inclientId)
							UNION ALL
							SELECT
								jobErrorDecisionRuleTableRowList.id,
								jobErrorDecisionRuleTableRowList.EntityId,
								jobErrorDecisionRuleTableRowList.EntityType,
								jobErrorDecisionRuleTableRowList.AppEntityType,
								jobErrorDecisionRuleTableRowList.AppTypeId,
								jobErrorDecisionRuleTableRowList.isEnabled,
								CAST(jobErrorDecisionRuleTableRowList.decisionRule AS NVARCHAR(MAX)) decisionRule
							FROM APP_JobErrorDecisionRule jobErrorDecisionRuleTableRowList WITH(NOLOCK)
								INNER JOIN APP_ClientGroupAssoc ag WITH(NOLOCK) ON
									jobErrorDecisionRuleTableRowList.entityType = 28	-- ClientGroup Entity Type
									AND jobErrorDecisionRuleTableRowList.entityId = ag.clientGroupId
									AND ag.clientId IN (2, @inclientId)
						) jobErrorDecisionRuleTableRowList
						FOR XML AUTO, TYPE
					)
			) q
		WHERE
			q.rowNodes IS NOT NULL		-- this fixes sequence numbering
	SET @loadEndTime = DBO.GetUnixTime(GETUTCDATE())
	IF (@inLogLevel >= 7)
	BEGIN
		-- time the execution of the SP below
		SET @edt = SYSDATETIME()
		PRINT 'APPCCSCreateClientLoadXMLDocument_v3: CCS Load XML Document generation Elapse Time [' + CAST(DATEDIFF(ms, @sdt, @edt) AS VARCHAR(24)) + 'ms].'
		SET @sdt = SYSDATETIME()
	END
	IF (@resultSet = 1)
	BEGIN
		SELECT
			(
				SELECT
					(
						SELECT
							@loadStartTime '@csStartLoadTime',
							@loadEndTime '@csEndLoadTime',
							@clientSkewTime '@clientSkewTime'
						FOR XML PATH('loadTimes'), TYPE
					),
					(
						SELECT
							(
								SELECT
									(
										SELECT
											r.id			'@sequenceNumber',
											1				'@tableOpCode',
											r.tableType		'@tableType'
										FOR XML PATH('tableInfo'), TYPE
									),
									r.data.query('.')
								FOR XML PATH('clientConfigStoreUpdate'), TYPE
							)
						FROM #ClientXMLRecords r
						WHERE
							r.data IS NOT NULL
						ORDER BY id
						FOR XML PATH(''), TYPE
					)
				FOR XML PATH('ccsTableUpdates'), TYPE
			)  o_resultSetXML
	END
	ELSE
	BEGIN
		-- move directly into output variable
		SET @outputXML = (
				SELECT
					(
						SELECT
							@loadStartTime '@csStartLoadTime',
							@loadEndTime '@csEndLoadTime',
							@clientSkewTime '@clientSkewTime'
						FOR XML PATH('loadTimes'), TYPE
					),
					(
						SELECT
							(
								SELECT
									(
										SELECT
											r.id			'@sequenceNumber',
											1				'@tableOpCode',
											r.tableType		'@tableType'
										FOR XML PATH('tableInfo'), TYPE
									),
									r.data.query('.')
								FOR XML PATH('clientConfigStoreUpdate'), TYPE
							)
						FROM #ClientXMLRecords r
						WHERE
							r.data IS NOT NULL
						ORDER BY id
						FOR XML PATH(''), TYPE
					)
				FOR XML PATH('ccsTableUpdates'), TYPE
		)
	END
	IF (@inLogLevel >= 7)
	BEGIN
		-- time the execution of the SP below
		SET @edt = SYSDATETIME()
		PRINT 'APPCCSCreateClientLoadXMLDocument_v3: CCS Load XML Document generation Elapse Time [' + CAST(DATEDIFF(ms, @sdt, @edt) AS VARCHAR(24)) + 'ms].'
	END
END
GO

IF EXISTS (select * from GxQscripts where name = 'APPCCSCreateClientLoadXMLDocument')
	delete from GxQscripts where name = 'APPCCSCreateClientLoadXMLDocument'
GO

IF EXISTS (select * from GXDBVersions where aliasname='APPCCSCreateClientLoadXMLDocument')
	delete from GXDBVersions where aliasname = 'APPCCSCreateClientLoadXMLDocument'
GO

insert into GXDBVersions values(2, 'APPCCSCreateClientLoadXMLDocument',  'v1.1.2.11.20.1', 'APPCCSCreateClientLoadXMLDocument', 'v1.1.2.11.20.1')
GO

