--@querytype CSV
--Name:- Version and Serice pack

SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET QUOTED_IDENTIFIER ON
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode	 NVARCHAR(MAX) ='0';
DECLARE @OperationType Int =0
DECLARE @queryId AS INTEGER = 239 
       ,@LastColectionTime  datetime

DECLARE @majorVer int
DECLARE @CSVersionInfo FLOAT
DECLARE @CSNode INT
DECLARE @CSClientId INT
SELECT TOP 1 @CSVersionInfo = CAST(release AS FLOAT) FROM simAllGalaxyRel WITH (NOLOCK) ORDER BY id DESC
SELECT TOP 1 @CSNode = clientId FROM APP_Platform WHERE platformType = 1
-- Always the active physical node of CommServ as CommServ client id
SET @CSClientId = ISNULL((SELECT attrVal FROM App_ClientProp WHERE componentNameId = @CSNode AND attrName like 'CS Active Physical Node' AND modified = 0), @CSNode)
SELECT @majorVer = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = @CSClientId  

IF (@CSVersionInfo < 11) or ( @CSVersionInfo=11 and  @majorVer <=6)
RETURN	  

IF (@CSVersionInfo = 11 AND @majorVer > 6 AND @majorVer < 14) --No Hotfix pack support between SP7 and SP13. Use old method query
BEGIN
	IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
	BEGIN
		SELECT @frequencyMode = mode from #MetricsInputParams
	END
	IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
	BEGIN
		SELECT @isPrivateRunning = value from GXGlobalParam  WITH (NOLOCK)  where name ='CommservSurveyRunning'
	END
 
	IF ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode = '1')) --Private
	BEGIN
		 SET @LastColectionTime =  (SELECT CAST(CAST(value AS NVARCHAR(32)) AS DATETIME) FROM GXGlobalParam v WHERE name = 'PrivateCloud_LastVersionAndSPVersionCrawlTime') 
		 SELECT @OperationType=3 
	END
	IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public
	BEGIN
	SET @LastColectionTime =  (SELECT CAST(CAST(value AS NVARCHAR(32)) AS DATETIME) FROM GXGlobalParam WHERE name = 'PublicCloud_LastVersionAndSPVersionCrawlTime') 
	SELECT @OperationType=2 
	END
	IF (@isPrivateRunning = ('Metrics Direct Dip'))
	BEGIN 
	--Direct Dip 
	SET @LastColectionTime =  (SELECT CAST(CAST(value AS NVARCHAR(32)) AS DATETIME) FROM GXGlobalParam WHERE name = 'DDIP_LastVersionAndSPVersionCrawlTime') 
	SELECT @OperationType=1
	END 

	IF OBJECT_ID('tempdb..#TempResSimGetPatchHealthStatus') IS NOT NULL
		   DROP TABLE #TempResSimGetPatchHealthStatus
	CREATE TABLE #TempResSimGetPatchHealthStatus (ClientType VARCHAR(128)
							   , ClientName VARCHAR(2048)
							   , ReleaseStr VARCHAR(16)
							   , SPMajor INT
							   , SPMinor INT
							   , CSReleaseStr VARCHAR(16)
							   , CSSPMajor INT
							   , CSSPMinor INT
							   , LatestReleaseStr VARCHAR(16)
							   , LatestSPMajor INT
							   , LatestSPMinor INT
							   , LatestSPMajorOnInstalledVersion INT
							   , LatestSPMinorOnInstalledVersion INT
							   , InstalledAddUpdates VARCHAR(max)
							   , missingInstalledPatches VARCHAR(max)
							   , missingDownloadedPatches VARCHAR(max)) 

	IF @CSVersionInfo = 11 AND @majorVer < 10
	BEGIN
		ALTER TABLE #TempResSimGetPatchHealthStatus DROP COLUMN LatestSPMajorOnInstalledVersion
		ALTER TABLE #TempResSimGetPatchHealthStatus DROP COLUMN LatestSPMinorOnInstalledVersion
	END

	IF OBJECT_ID('tempdb..#TempResSimGetPatchHealthStatusWithStatus') IS NOT NULL
		   DROP TABLE #TempResSimGetPatchHealthStatusWithStatus
	CREATE TABLE #TempResSimGetPatchHealthStatusWithStatus (ClientId INT
				   , ClientType INT -- 1 - Commserv, 2 - Laptop, 3 - MA, 4 - Server
							   , ClientVersion VARCHAR(25)
							   , ClientServicePack INT
				   , installedAdditionalUpdates NVARCHAR(max)
							   , missingUpdatesPatches NVARCHAR(max))
  
 
	if @LastColectionTime is not null 
	BEGIN 
 
	 IF OBJECT_ID('Tempdb..#ClientList') is not null 
	 DROP TABLE #ClientList
	CREATE TABLE #ClientList(ClientID int )
	INSERT #ClientList
	SELECT C.Id FROM App_Client C INNER JOIN  simInstalledPackages S ON S.ClientID =c.ID 
	and OpTime >@LastColectionTime
	END 
 
	EXEC simGetPatchHealthStatus 15 
 
	INSERT INTO #TempResSimGetPatchHealthStatusWithStatus (ClientId, ClientType, ClientVersion, ClientServicePack, installedAdditionalUpdates, missingUpdatesPatches)
	SELECT C.id
		,(CASE 
			WHEN ClientType LIKE 'CommServ' THEN 1
			WHEN ClientType LIKE 'Laptops' THEN 2
			WHEN ClientType LIKE 'MediaAgents' THEN 3
			WHEN ClientType LIKE 'Servers' THEN 4	
		END)
		   ,ReleaseStr
		   ,SPMajor
		   , InstalledAddUpdates
		   , CASE 
				WHEN (missingInstalledPatches IS NULL OR LEN(missingInstalledPatches) = 0) AND  (missingDownloadedPatches IS NULL OR LEN(missingDownloadedPatches) = 0) THEN ''
				WHEN (missingInstalledPatches IS NOT NULL AND LEN(missingInstalledPatches) <> 0) AND  (missingDownloadedPatches IS NULL OR LEN(missingDownloadedPatches) = 0) THEN missingInstalledPatches
				WHEN (missingInstalledPatches IS NULL OR LEN(missingInstalledPatches) = 0) AND  (missingDownloadedPatches IS NOT NULL AND LEN(missingDownloadedPatches) <> 0) THEN missingDownloadedPatches
				WHEN (missingInstalledPatches IS  NOT NULL AND LEN(missingInstalledPatches) <> 0) AND  (missingDownloadedPatches IS NOT NULL AND LEN(missingDownloadedPatches) <> 0) THEN missingInstalledPatches + ',' + missingDownloadedPatches
				ELSE ''
			END
	FROM #TempResSimGetPatchHealthStatus R
		INNER JOIN App_Client C WITH(NOLOCK) 
			ON C.name = R.ClientName  

		 
	IF OBJECT_ID('tempdb..#UpdatesTable') IS NOT NULL
		   DROP TABLE #UpdatesTable
	CREATE TABLE #UpdatesTable(
	 ClientId INT,
	 UpdateName NVARCHAR(30),
	 Installed INT
	)
	INSERT INTO #UpdatesTable(ClientId, UpdateName, Installed)
	SELECT
	T.ClientId, 
	LEFT(S._ID, 256),
	1 -- Installed
	FROM #TempResSimGetPatchHealthStatusWithStatus T
	CROSS APPLY dbo.SplitStringByDelimiter(installedAdditionalUpdates, ',') S
 
	INSERT INTO #UpdatesTable(ClientId, UpdateName, Installed)
	SELECT
	T.ClientId, 
	LEFT(S._ID, 256),
	0 -- NOT Installed
	FROM #TempResSimGetPatchHealthStatusWithStatus T
	CROSS APPLY dbo.SplitStringByDelimiter(missingUpdatesPatches, ',') S

	SELECT 
	T.ClientId,
	ISNULL(T.ClientType,'') ClientType,
	ISNULL(CAST(CAST(T.ClientVersion as Float) as INT),0) ClientVersion,
	ISNULL(T.ClientServicePack,0) ClientServicePack,
	ISNULL(U.UpdateName,'') UpdateName,
	ISNULL(U.Installed,'2') Installed,
	(CASE WHEN U.UpdateName LIKE '%PreRelease%' THEN 1 ELSE 2 END) type
	FROM #TempResSimGetPatchHealthStatusWithStatus T
	LEFT JOIN #UpdatesTable U
	ON T.ClientId = U.ClientId
 
	DECLARE @ParamName NVARCHAR(MAX) = 
			CASE @OperationType 
				WHEN 3 THEN 'PrivateCloud_LastVersionAndSPVersionCrawlTime' 
				WHEN 2 THEN 'PublicCloud_LastVersionAndSPVersionCrawlTime'
				WHEN 1 THEN 'DDIP_LastVersionAndSPVersionCrawlTime' 
			END
		
	IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = @ParamName)
	BEGIN
		IF @OperationType = 3
			EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''PrivateCloud_LastVersionAndSPVersionCrawlTime'', GETUTCDATE(), 0, 0')
		ELSE IF @OperationType = 2
			EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''PublicCloud_LastVersionAndSPVersionCrawlTime'', GETUTCDATE(), 0, 0')
		ELSE IF @OperationType = 1
			EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''DDIP_LastVersionAndSPVersionCrawlTime'', GETUTCDATE(), 0, 0')
	END
	ELSE
		UPDATE GXGlobalParam SET value = CAST(GETUTCDATE() AS NVARCHAR(32)) WHERE name = @ParamName
	END
ELSE --SP14 and above with HPK support
	BEGIN
	IF OBJECT_ID('tempdb..#ClientDetails') IS NOT NULL
		DROP TABLE #ClientDetails

	CREATE TABLE #ClientDetails (ClientId INT, ReleaseId INT, ReleaseStr VARCHAR(16), SPMajor INT) 
	CREATE CLUSTERED INDEX Cl_ClientDetails ON #ClientDetails(ClientId)

	IF OBJECT_ID('tempdb..#ClientInstalledAddPatches') IS NOT NULL
		DROP TABLE #ClientInstalledAddPatches
	CREATE TABLE #ClientInstalledAddPatches(ClientId INT, PatchName VARCHAR(512), LastUpdateTime DATETIME)

	-- Adding all clients which are visible in GUI
	INSERT INTO #ClientDetails(ClientId, ReleaseId, ReleaseStr, SPMajor)
	SELECT C.id, C.releaseId, sar.release, MAX(sim.HighestSP)
	FROM simInstalledPackages sim WITH(NOLOCK)
		INNER JOIN App_Client c WITH(NOLOCK)
			ON sim.ClientId = c.id
		INNER JOIN simAllGalaxyRel sar WITH(NOLOCK)
			ON sar.id = c.releaseId
	WHERE  C.id > 1 AND C.specialClientFlags&1 <> 1
	GROUP BY C.id, C.releaseId, sar.release

	-- remove the clients who has no agents installed or not deconfigured and not MA
	DELETE c
	FROM #ClientDetails c  LEFT JOIN
	(
		SELECT DISTINCT ClientId FROM MMHost WITH(NOLOCK)
		UNION ALL
		SELECT DISTINCT clientId FROM APP_IDAName WITH (NOLOCK) WHERE (status & 4) = 0
		EXCEPT (SELECT DISTINCT componentNameId ClientID FROM APP_ClientProp WITH(NOLOCK) WHERE attrName = 'PlatformDeleted 4' AND attrVal = '1' AND modified = 0) 
		UNION ALL
		SELECT @CSClientId
	) b
	ON c.ClientId= b.clientId
	WHERE b.ClientId is null

	-- Split the Updates Ids in simInstalledPackages table
	INSERT INTO #ClientInstalledAddPatches(ClientId, PatchName, LastUpdateTime)
	SELECT sim.ClientId, AP._ID, MAX(sim.OpTime)
	FROM #ClientDetails CD
		INNER JOIN simInstalledPackages sim
			ON CD.ClientId = sim.ClientId AND sim.OpType IN ('Install', 'ReInstall')
		CROSS APPLY dbo.SplitIDString(sim.AdditionalPatches) AS AP
	WHERE AP._ID <> 'None' 
	GROUP BY sim.ClientId, AP._ID 

	IF OBJECT_ID('tempdb..#ClientHotFixPack') IS NOT NULL 
		DROP TABLE #ClientHotFixPack
	CREATE TABLE #ClientHotFixPack (clientId INT, UPNumber INT, LastUpdateTime DATETIME) 

	DECLARE @nstring NVARCHAR(MAX)
	SET @nstring = N'
	INSERT INTO #ClientHotFixPack(clientId, UPNumber, LastUpdateTime)  
	SELECT sim.ClientId, MAX(ISNULL(UPNumber, 0)), MAX(OpTime) FROM simInstalledPackages AS sim GROUP BY sim.ClientId
	'
	EXEC sp_executesql @nstring

	SELECT 
		A.ClientId,
		'' AS ClientType, --Use client details query
		ISNULL(CAST(CAST(A.ReleaseStr AS Float) AS INT), 0) ClientVersion,
		ISNULL(A.SPMajor, 0) ClientServicePack,
		ISNULL(CAST(B.UPNumber AS VARCHAR(10)),'') UpdateName,
		1 AS Installed, --Only Pickup Installed updates
		3 AS Type,  --Type: 3-HPK
		B.LastUpdateTime
	FROM #ClientDetails A
	LEFT JOIN #ClientHotFixPack B
		ON A.ClientId = B.ClientId
	UNION
	SELECT 
		A.ClientId,
		'' AS ClientType, --Use client details query
		ISNULL(CAST(CAST(A.ReleaseStr AS Float) AS INT), 0) ClientVersion,
		ISNULL(A.SPMajor, 0) ClientServicePack,
		ISNULL(B.PatchName,'') UpdateName,
		1 AS Installed, --Only Pickup Installed updates
		(CASE WHEN B.PatchName LIKE '%PreRelease%' THEN 1 WHEN B.PatchName LIKE 'HPK%' THEN 3 ELSE 2 END) AS Type,  --Type: 1-Prerelease, 2-HotFix
		B.LastUpdateTime
	FROM #ClientDetails A
	INNER JOIN #ClientInstalledAddPatches B
		ON A.ClientId = B.ClientId

	IF OBJECT_ID('tempdb..#ClientDetails') IS NOT NULL 
		DROP TABLE #ClientDetails
	IF OBJECT_ID('tempdb..#ClientInstalledAddPatches') IS NOT NULL 
		DROP TABLE #ClientInstalledAddPatches
	IF OBJECT_ID('tempdb..#ClientHotFixPack') IS NOT NULL 
		DROP TABLE #ClientHotFixPack
END

SET NOCOUNT OFF