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

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



--------- BEGIN SURVEY QUERY ---------
-- For a commcell ID it provides the count of clients and MA that are up to date, need update, ahead of cache or Hotfix

--------- Insert your SQL statements here 
IF OBJECT_ID('tempdb..#Client') IS NOT NULL
	DROP TABLE #Client

CREATE TABLE #Client (id INT, status INT, TypeOfNode INT, updateStatus INT, releaseId INT, virtual INT, physClientId INT)

INSERT	INTO #Client
SELECT	CL.id, CL.status, CASE WHEN MA.ClientId IS NULL THEN 1 ELSE 0 END, 8, CL.releaseId, ISNULL( ACP.attrVal,0),0
FROM	APP_Client CL WITH (NOLOCK) LEFT OUTER JOIN MMHost MA WITH (NOLOCK) ON CL.id = MA.ClientId
		LEFT OUTER JOIN APP_ClientProp ACP WITH (NOLOCK) ON ACP.componentNameId = CL.id AND ACP.attrName = 'Virtual client' AND ACP.modified = 0
WHERE	CL.id > 1 AND (CL.specialClientFlags & 1) <> 1 
	AND CL.id NOT IN (SELECT PMClientId FROM APP_VMToPMMap WITH (NOLOCK) WHERE VMClientId = 2)

UPDATE #Client SET physClientId = (select attrVal from app_clientprop WITH (NOLOCK) where componentNameId = #Client.id and attrName = 'CS Active Physical Node' )
where id = 2 and virtual = 1

UPDATE C SET updateStatus = U.Baseline			  
FROM #Client C INNER JOIN (
	SELECT ClientId, SUM(DISTINCT SIM.Baseline) AS Baseline FROM simInstalledPackages SIM WITH (NOLOCK) WHERE SIM.simPackageID NOT BETWEEN 6000 AND 7000 GROUP BY SIM.ClientId
	) U ON U.ClientId = C.id

UPDATE C SET updateStatus = U.Baseline			  
FROM #Client C INNER JOIN (
	SELECT ClientId, SUM(DISTINCT SIM.Baseline) AS Baseline FROM simInstalledPackages SIM WITH (NOLOCK) WHERE SIM.simPackageID NOT BETWEEN 6000 AND 7000 GROUP BY SIM.ClientId
	) U ON U.ClientId = C.physClientId AND C.virtual = 1


UPDATE	C
SET	updateStatus = (case	WHEN releaseId < 15 THEN
					(
						(case	WHEN (updateStatus & 8) > 0 						THEN 8	-- N/A
						WHEN (updateStatus & 16) > 0 						THEN 16	-- Diag
						WHEN (updateStatus & 2) > 0 						THEN 2	-- Behind
						WHEN ISNULL(SIM.Cnt,0) > 0  THEN 0	-- Unknown
						WHEN (updateStatus & 4) > 0 						THEN 4	-- Ahead
						WHEN (updateStatus & 1) > 0 						THEN 1	-- Uptodate
						else 0 end)
					)
					else
					(
						(case	WHEN (updateStatus & 8) > 0 						THEN 8	-- N/A
						WHEN (updateStatus & 2) > 0 						THEN 2	-- Behind
						WHEN (updateStatus & 16) > 0 						THEN 16	-- Diag
						WHEN ISNULL(SIM.Cnt,0) > 0	THEN 0	-- Unknown
						WHEN (updateStatus & 4) > 0 						THEN 4	-- Ahead
						WHEN (updateStatus & 1) > 0 						THEN 1	-- Uptodate
						else 0 end)
					) end)
FROM #Client C LEFT OUTER JOIN 
	 (SELECT SIM.clientId,COUNT(*) Cnt FROM simInstalledPackages SIM WITH (NOLOCK) WHERE SIM.Baseline = 0 GROUP BY SIM.ClientId) SIM
	 ON C.id = SIM.ClientId 


SET @surveyXML = (
	SELECT 	
		TypeOfNode AS NodeId,	
		updateStatus AS 'StatusId', 
		CASE TypeofNode WHEN 0 THEN 'MediaAgent' WHEN 1 THEN 'Client' END AS TypeofNode,
		CASE updateStatus
			WHEN 0 THEN 'Unknown' 
			WHEN 1 THEN 'Up To Date'
			WHEN 2 THEN 'Needs Update'
			WHEN 4 THEN 'Ahead Of Cache'
			WHEN 8 THEN 'N/A'
			WHEN 16 THEN 'Hotfix'
			ELSE 'N/A' 
		END AS Status,
		Counts AS 'Count'
	FROM (SELECT TypeOfNode, updateStatus, COUNT(*) AS Counts FROM #Client GROUP BY TypeOfNode, updateStatus) S
	ORDER BY NodeId, StatusId
	FOR XML RAW('Nodes'), ROOT('CommCellClientMAVersion') 
	-- Replace <<RESULTS_NAME>> with specific name
	
)
SET @surveyXML = ISNULL(@surveyXML,'<CommCellClientMAVersion />')

IF OBJECT_ID('tempdb..#Client') IS NOT NULL
	DROP TABLE #Client

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