--Name :- Update Information
--Description :- Commserver installed updates and update cache information 
SET NOCOUNT ON 
DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 12 

DECLARE @surveyXML NVARCHAR(MAX)

DECLARE @CSDBUpdateInfo XML
DECLARE @UpdateCacheInfo XML

IF OBJECT_ID('tempdb..#OutTbl') IS NOT NULL
    DROP TABLE #OutTbl
CREATE TABLE #OutTbl(
	HighestSP NVARCHAR(MAX),
	AddUpdates NTEXT,
	OSId INT
)


DECLARE @OSMapping TABLE(Name VARCHAR(30), OSId INT)
INSERT INTO @OSMapping
VALUES 
('UPDATE_OS_UNKNOWN', 0),
('UPDATE_OS_WINDOWS', 1),
('UPDATE_OS_WINDOWS64', 2),
('UPDATE_OS_WINDOWSX64', 3),
('UPDATE_OS_NETWARE', 4),
('UPDATE_OS_UNIX', 5),
('UPDATE_OS_SOLARIS', 6),
('UPDATE_OS_HP', 7),
('UPDATE_OS_AIX', 8),
('UPDATE_OS_LINUX', 9),
('UPDATE_OS_FREEBSD', 10),
('UPDATE_OS_OSF', 11),
('UPDATE_OS_DARWIN', 12),
('UPDATE_OS_IRIX', 13),
('UPDATE_OS_AIXPPC', 14),
('UPDATE_OS_LINUXX86', 15),
('UPDATE_OS_LINUXX8664', 16),
('UPDATE_OS_LINUXPPC64', 17),
('UPDATE_OS_LINUXS390', 18),
('UPDATE_OS_LINUXIA64', 19),
('UPDATE_OS_HPIA64', 20),
('UPDATE_OS_HPRISC', 21),
('UPDATE_OS_SOLSPARC', 22),
('UPDATE_OS_SOLX8664', 23),
('UPDATE_OS_DWNX86', 24),
('UPDATE_OS_FBSDX86', 25),
('UPDATE_OS_FBSDX8664', 26)

DECLARE @CSNode INT
DECLARE @CSClientId INT
SET @CSNode = ISNULL((select TOP 1 clientid from APP_Platform WITH (NOLOCK) where platformType = 1 order by clientid ASC), 0)


-- Find out if this is a cluster CS.

DECLARE @isClusterCS INT = 0
SELECT @isClusterCS = attrVal FROM App_ClientProp 
WHERE componentNameId = @CSNode
	AND attrName LIKE 'Virtual Client' 
	AND modified = 0

-- If it is a cluster CS, we need to consider the active physical node of CommServ as CommServ client id
IF @isClusterCS = 0
	SET @CSClientId = @CSNode
ELSE
	BEGIN 
		--Use currently active physical node		
		SELECT @CSClientId = attrVal
		FROM App_ClientProp 
		WHERE componentNameId = @CSNode AND attrName like 'CS Active Physical Node' AND modified = 0
	END


-- Find Highest Major service pack version installed on CommServ client

DECLARE @majorVer varchar(20)
SELECT @majorVer = CAST(MAX(HighestSP) AS varchar(20)) + CASE WHEN MAX(SpMinorVersion) > 0 THEN char((96 + MAX(SpMinorVersion)))
					ELSE '' END FROM simInstalledPackages WHERE clientId = @CSClientId

-- Get the additional updates installed

DECLARE @csRelId INT = (SELECT releaseId FROM APP_Client WITH(NOLOCK) WHERE id = @CSClientId)
DECLARE @SpVersion BIGINT = (SELECT MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH(NOLOCK) WHERE ClientId = @CSClientId)

DECLARE @tmpTable1 TABLE(Updates NVARCHAR(MAX))
INSERT INTO @tmpTable1
SELECT
	CAST(AdditionalPatches AS NVARCHAR(MAX))
FROM simInstalledPackages
WHERE ClientId = @CSClientId AND CAST(AdditionalPatches AS NVARCHAR(MAX)) <> 'None' 

DECLARE @tmpTable2 TABLE(AddUpdates NVARCHAR(MAX))
INSERT INTO @tmpTable2	
SELECT DISTINCT	f.Data
FROM @tmpTable1 AS tmp 
CROSS APPLY dbo.SplitString(tmp.Updates, ',') AS f	  

DECLARE @addUpdates NVARCHAR(MAX)
SELECT
	@addUpdates =COALESCE(@addUpdates + ',', '') + AddUpdates
FROM @tmpTable2

SET @CSDBUpdateInfo = (
	SELECT @majorVer AS '@HighestSP', @addUpdates AS '@AdditionalUpdates' FOR XML PATH('CSDBUpdateInfo')
)

DECLARE @CSReleaseID INT
SELECT @CSReleaseID = MAX(id) FROM simAllGalaxyRel

DECLARE @sqlStmtPreSP9 NVARCHAR(MAX) = '
	INSERT INTO #OutTbl
	SELECT 
		HighestSP, 
		AddUpdates, 
		OSId
	FROM PatchMultiCache a WITH(NOLOCK)
	WHERE a.ClientId = @CSClientId AND a.ReleaseId = @CSReleaseID'

DECLARE @sqlStmtLatest NVARCHAR(MAX) = '
	INSERT INTO #OutTbl
	SELECT 
		HighestSP, 
		AddUpdates, 
		a.OSId
	FROM PatchMultiCache a WITH(NOLOCK)
	INNER JOIN (
		SELECT ClientId, ReleaseId, OSId, MAX(id) AS ''id'' FROM PatchMultiCache WITH(NOLOCK)
		GROUP BY ClientId, ReleaseId, OSId
	)SUB 
	ON a.id = SUB.id
	AND a.ClientId = SUB.ClientId
	AND a.ReleaseId = SUB.ReleaseId
	AND a.OSId = SUB.OSId
	WHERE a.ClientId = @CSClientId AND a.ReleaseId = @CSReleaseID'

DECLARE @paramDef NVARCHAR(MAX) = '
	@CSClientId INT, @CSReleaseId INT'

DECLARE @sqlStmt NVARCHAR(MAX)

IF @csRelId < 16 OR (@csRelId = 16 AND @SpVersion < 9) --below SP9
	SET @sqlStmt = @sqlStmtPreSP9
ELSE
	SET @sqlStmt = @sqlStmtLatest

EXEC sp_executesql @sqlStmt, @paramDef, @CSClientId, @CSReleaseId

SET @UpdateCacheInfo = (
	SELECT 	a.HighestSP AS '@HighestSP',
	a.AddUpdates AS '@AdditionalUpdates',
	a.OSId AS '@OSId',
	ISNULL(b.Name, 'UNKNOWN') AS '@OSName'
	FROM #outTbl a
	LEFT OUTER JOIN @OSMapping AS b
		ON a.OSId = b.OSId
	FOR XML PATH('UpdateCacheInfo')
)

DECLARE @CacheSP INT = 0
SELECT	@CacheSP = MAX(CAST(CASE WHEN ISNUMERIC(RIGHT(HighestSP, 1)) = 0 THEN SUBSTRING(HighestSP, 0, LEN(HighestSP)) ELSE HighestSP END AS INT))
FROM	(SELECT	DISTINCT HighestSP
		FROM	PatchMultiCache AS a INNER JOIN @OSMapping AS b ON a.OSId = b.OSId  
		WHERE	ClientId = @CSClientId AND ReleaseId = @CSReleaseID
		) T

DECLARE @CSUpdateStatus INT = 0
IF EXISTS (SELECT ClientID FROM SimInstalledPackages WHERE Baseline = 2 AND ClientId = 2)
	SET @CSUpdateStatus = 1

IF OBJECT_ID('tempdb..#ClientDetails') IS NOT NULL DROP TABLE #ClientDetails
IF OBJECT_ID('tempdb..#InstalledClients') IS NOT NULL DROP TABLE #InstalledClients
IF OBJECT_ID('tempdb..#InstallHistory') IS NOT NULL DROP TABLE #InstallHistory
IF OBJECT_ID('tempdb..#UninstallHistory') IS NOT NULL DROP TABLE #UninstallHistory
IF OBJECT_ID('tempdb..#MONTHS') IS NOT NULL DROP TABLE #MONTHS

CREATE TABLE #ClientDetails (ClientId INT, ClientType INT DEFAULT 1, ReleaseId INT, SP INT, Baseline INT, isBehindeRel INT DEFAULT 0, isBehindSP INT DEFAULT 0) --ClientType => 0:CS, 1:Servers, 2:Laptops, 4:MediaAgents

INSERT INTO #ClientDetails(ClientId, ReleaseId, SP, Baseline)
SELECT C.id, MAX(c.releaseId), MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), MAX(Baseline) 
	FROM simInstalledPackages sim WITH(NOLOCK)
	INNER JOIN App_Client C WITH(NOLOCK)
		ON sim.ClientId = C.id
    INNER JOIN (SELECT DISTINCT clientId FROM APP_IDAName WITH (NOLOCK) WHERE status&4 = 0) I
            ON C.id = I.clientId
    LEFT OUTER JOIN APP_ClientProp CP WITH (NOLOCK) 
            ON C.id = CP.componentNameId AND CP.attrName = 'PlatformDeleted 4' AND CP.attrVal = '1' AND CP.modified = 0
WHERE  C.id > 1 AND C.specialClientFlags&1 <> 1 AND CP.componentNameId IS NULL			
GROUP BY C.id

UPDATE #ClientDetails SET ClientType = 0 WHERE ClientId = @CSClientId --CS

UPDATE CD SET ClientType = 4 FROM #ClientDetails CD INNER JOIN MMHost MA ON CD.ClientType = 1 AND MA.ClientId = CD.ClientId -- MediAgents

UPDATE CD SET ClientType = 2 FROM #ClientDetails CD INNER JOIN APP_Client C ON CD.ClientType = 1 AND C.id = CD.ClientId AND (C.status & 0x1000) > 0 --Laptops

UPDATE #ClientDetails SET isBehindeRel = 1 WHERE ReleaseId < @CSReleaseID

UPDATE #ClientDetails SET isBehindSP = 1 WHERE ReleaseId = @CSReleaseID AND SP < @CacheSP

DECLARE @AggrUpdateStatus XML
SET @AggrUpdateStatus = (
	SELECT 
		(SELECT 
			ClientType AS '@ClientTypeId',
			CASE ClientType WHEN 0 THEN 'CommServ' WHEN 1 THEN 'Servers' WHEN 2 THEN 'Laptops' WHEN 4 THEN 'MediaAgents' END AS '@ClientType',
			COUNT(ClientId) AS '@Total', 
			SUM(CASE WHEN isBehindeRel = 1 THEN 1 ELSE 0 END) AS '@BehindRel',
			SUM(CASE WHEN isBehindSP = 1 THEN 1 ELSE 0 END) AS '@BehindSP',
			SUM(CASE WHEN isBehindeRel = 0 AND isBehindSP = 0 THEN 1 ELSE 0 END) AS '@NeedHotFix'
		FROM #ClientDetails WHERE Baseline = 2 
		GROUP BY ClientType 
		FOR XML PATH('ClientsNeedUpdate'), TYPE)
	FOR XML PATH('InstallUpdateStatus'), TYPE
)


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

-- get clien install/uninstall history
CREATE TABLE #InstallHistory (ClientId INT, ClientName NVARCHAR(255), SimOSId INT, ReleaseId INT, InstallDate DATETIME)
CREATE TABLE #UninstallHistory (ClientId INT, ClientName NVARCHAR(255), SimOSId INT, ReleaseId INT, UninstallDate DATETIME, ClientHostName NVARCHAR(510))

INSERT INTO #InstallHistory
SELECT	C.id, C.Name, C.simOperatingSystemId, C.releaseId,  
		CASE WHEN CP.created IS NULL THEN NULL ELSE dbo.UTCToLocalStringTime(dbo.GetDateTime(CP.created), @timzonename) END
FROM	APP_Client C 
		LEFT JOIN (SELECT componentNameId, MIN(created) AS created FROM APP_ClientProp WHERE created>0 GROUP BY componentNameId) CP 
		ON C.id = CP.componentNameId 
WHERE	C.id > 1 --AND (C.specialClientFlags & 1) = 0
UNION
SELECT	CId, Name, SimOSId, ReleaseId, OpTimeUTC
FROM	LicUsageHistory
WHERE	ReleaseId IN (15,14,13) AND AppType = 1001 AND OpType IN ('Install')

INSERT INTO #UninstallHistory(ClientId, ClientName, SimOSId, ReleaseId, UninstallDate, ClientHostName)
SELECT	C.id, C.Name, C.simOperatingSystemId, C.releaseId,  
		CASE WHEN CP.created IS NULL THEN NULL ELSE dbo.UTCToLocalStringTime(dbo.GetDateTime(CP.created), @timzonename) END,
		C.net_hostname
FROM	APP_Client C LEFT JOIN APP_ClientProp CP 
		ON C.id = CP.componentNameId AND CP.attrname = 'PlatformDeleted 4' AND CP.attrVal = '1'
WHERE	C.id > 2 --AND (C.specialClientFlags & 1) = 0
UNION
SELECT	CId, Name, SimOSId, ReleaseId, OpTimeUTC, Name
FROM	LicUsageHistory 
WHERE	ReleaseId IN (15,14,13) AND AppType = 1001 AND OpType IN ('Deleted', 'Uninstall')

UPDATE	U SET ClientName = C.name, ClientHostName = C.net_hostname
FROM	#UninstallHistory U INNER JOIN APP_Client C ON U.ClientId = C.id
WHERE	U.ClientName <> C.name OR U.ClientHostName <> C.net_hostname

-- Get number of installed clients per version for last 12 months

CREATE TABLE #InstalledClients (MM INT, YY INT, ReleaseId INT, NumOfClients INT)
CREATE TABLE #MONTHS (MM INT, YY INT, ENDDATE DATE)

DECLARE @ThisMonth	DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0)
DECLARE @STARTDATE	DATE = @ThisMonth
DECLARE @ENDDATE	DATE = DATEADD(MONTH, 1, @STARTDATE)

DECLARE @CNT INT = 12
WHILE @CNT >0
BEGIN
	INSERT INTO #MONTHS VALUES(MONTH(@STARTDATE), YEAR(@STARTDATE), @ENDDATE)
	SET @ENDDATE = @STARTDATE		
	SET @STARTDATE = DATEADD(MONTH, -1, @STARTDATE)
	SET @CNT -= 1
END

INSERT	INTO #InstalledClients
SELECT	I.MM, I.YY, I.ReleaseId, COUNT(I.ClientId)
FROM	(SELECT	MM, YY, MAX(ReleaseId) AS ReleaseId, ClientId, MAX(InstallDate) AS LastInstallDate
		FROM	#InstallHistory INNER JOIN #MONTHS ON InstallDate < ENDDATE
		GROUP BY MM, YY, ClientId
		) I
		LEFT OUTER JOIN 
		(SELECT	MM, YY, MAX(ReleaseId) AS ReleaseId, ClientId, MAX(UninstallDate) AS LastUninstallDate
		FROM	#UninstallHistory INNER JOIN #MONTHS ON UninstallDate < ENDDATE
		GROUP BY MM, YY, ClientId
		) U ON I.MM = U.MM AND I.YY = U.YY AND I.ReleaseId = U.ReleaseId AND I.ClientId = U.ClientId
WHERE	U.LastUninstallDate IS NULL OR I.LastInstallDate > U.LastUninstallDate
GROUP BY I.MM, I.YY, I.ReleaseId

UPDATE	#InstalledClients SET NumOfClients = T.NumOfClients
FROM	#InstalledClients C INNER JOIN (
		SELECT	ReleaseId, COUNT(ClientId) AS NumOfClients FROM #ClientDetails
		GROUP BY ReleaseId
		) T ON C.MM = MONTH(@ThisMonth) AND C.YY = YEAR(@ThisMonth) AND C.ReleaseId = T.ReleaseId

DECLARE @VersionHistory XML

SET @VersionHistory = (
	SELECT	M.MM AS '@MONTHS', 
			M.YY AS '@YEARS',
			CASE WHEN ISNULL(V1,0) = 0 AND ISNULL(V2,0) = 0 AND ISNULL(V3,0) = 0 THEN -1 ELSE ISNULL(V1,0) END AS '@version1',
			CASE WHEN ISNULL(V1,0) = 0 AND ISNULL(V2,0) = 0 AND ISNULL(V3,0) = 0 THEN -1 ELSE ISNULL(V2,0) END AS '@version2',
			CASE WHEN ISNULL(V1,0) = 0 AND ISNULL(V2,0) = 0 AND ISNULL(V3,0) = 0 THEN -1 ELSE ISNULL(V3,0) END AS '@version3'		
	FROM	#MONTHS M LEFT OUTER JOIN 
			(SELECT MM, YY, [16] AS V1, [15] AS V2, [14] AS V3 
			FROM	#InstalledClients PIVOT (MAX(NumOfClients) FOR ReleaseID IN ([16],[15],[14])) AS P
			) A ON M.MM = A.MM AND M.YY = A.YY
FOR XML PATH ('ClientVersion'),type
)


SET @surveyXML = (
SELECT
	@AggrUpdateStatus,
	@CSDBUpdateInfo,
	@UpdateCacheInfo,
	@VersionHistory
FOR XML PATH('UpdateInfo') 
)

--select CAST(@surveyXML AS xml)

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