--Name:- CommServ Details
--Description:- CommServer Configuration Details

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 2
DECLARE @surveyXML NVARCHAR(MAX)

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 @CSSPInfo NVARCHAR(MAX) = ''
DECLARE @isClusterCS INT = 0
SELECT @isClusterCS = attrVal FROM App_ClientProp WITH (NOLOCK) 
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 WITH (NOLOCK) 
		WHERE componentNameId = @CSNode AND attrName like 'CS Active Physical Node' AND modified = 0
	END

SELECT	@CSSPInfo = attrVal
FROM	APP_ClientProp WITH (NOLOCK) 
WHERE	componentNameId = @CSClientId AND attrName = 'SP Version Info' AND modified = 0

DECLARE @majorVer INTEGER
DECLARE @minorVer VARCHAR(1) = ''

SELECT  @majorVer = MAX(HighestSP) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = @CSClientId  

IF ISNULL(@CSSPInfo, '') = ''
BEGIN
	SELECT TOP 1 @CSSPInfo = release FROM simAllGalaxyRel WITH (NOLOCK) ORDER BY id DESC
	
	SELECT  @minorVer = CASE WHEN MAX(SpMinorVersion) = 0 THEN '' ELSE CHAR(MAX(SpMinorVersion)+96) END
	FROM    simInstalledPackages WITH (NOLOCK)
	WHERE   clientId = @CSClientId AND HighestSP = @majorVer	

	IF @majorVer > 0
		SELECT @CSSPInfo += ' SP' + CAST(@majorVer AS NVARCHAR(MAX)) + @minorVer	
END

SET @majorVer = CASE WHEN @majorVer>100 THEN @majorVer/100 ELSE @majorVer END
--Convert to dotted format
SELECT @CSSPInfo = replace(@CSSPInfo, '.0 SP', '.')
SELECT @CSSPInfo = replace(@CSSPInfo, ' SP', '.')
SELECT @CSSPInfo = replace(@CSSPInfo, ' HPK', '.')
SELECT @CSSPInfo = replace(@CSSPInfo, '+', '')
SELECT @CSSPInfo = replace(@CSSPInfo, '-', '')
SELECT @CSSPInfo = replace(@CSSPInfo, 'a', '.1')
SELECT @CSSPInfo = replace(@CSSPInfo, 'b', '.2')
SELECT @CSSPInfo = 
	(CASE LEN(@CSSPInfo) - LEN(REPLACE(@CSSPInfo, '.', '')) 
		 WHEN 0 THEN @CSSPInfo + '.0.0'
		 WHEN 1 THEN @CSSPInfo + '.0'
		 WHEN 2 THEN @CSSPInfo
	 END)


DECLARE @CSTZ NVARCHAR(MAX) = ''
SELECT @CSTZ = dbo.GetClientTimeZone(@CSNode)

IF ISNULL(@CSTZ, '') = ''
BEGIN
	SELECT  @CSTZ = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2

	SELECT	@CSTZ = TimeZoneStdName
	FROM	SchedTimeZone WITH (NOLOCK)
	WHERE	TimeZoneName = SUBSTRING(@CSTZ, CHARINDEX(':', @CSTZ, CHARINDEX(':', @CSTZ, 0) + 1) + 1, 255)
END

-- In case its linux CS get windows timezone
DECLARE @osType TINYINT = 0
DECLARE @versionSQL NVARCHAR(MAX) = ''
SET @versionSQL = @@VERSION
IF (@versionSQL LIKE '%Linux%')
BEGIN
	DECLARE @tzQuery NVARCHAR(1000)
	SET @tzQuery = 'SELECT @csTZ = WindowsTimeZoneStdName FROM SchedTimeZone (NOLOCK) WHERE TimeZoneStdName= @CSTZ AND (Flags & 1) =0'

	EXEC SP_EXECUTESQL 
			@Query  = @tzQuery
			, @Params = N'@CSTZ NVARCHAR(2048) OUTPUT'
			, @CSTZ = @CSTZ OUTPUT	
END

DECLARE @brandId INT
DECLARE @brandIdStr VARCHAR(256) = ''
SELECT @brandId = CAST(CAST(value AS varchar(256)) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'MiniBrandId'
IF @brandId IS NULL
	SET @brandId = (SELECT TOP 1 OemId FROM LicCSL WITH (NOLOCK) ORDER BY id DESC)
 
SET @brandIdStr =  CAST(@brandId AS varchar(256))

-- Find CommCell license expiration date
declare @expDate integer = 0

IF EXISTS (SELECT * FROM APP_Client WHERE id = 2 AND releaseId >= 15)
BEGIN
	declare @licKey varchar(8000)
	declare @MachID integer
	declare @DRMachID integer
	declare @newRepMachID integer
	declare @csId integer
	declare @OEMId integer
	declare @plattype integer
	declare @relnum integer
	declare @keyType integer
	declare @rmkc integer
	declare @evalDays integer
	declare @enclic varchar(5000)
	declare @licTypeList varchar(5000)
	declare @csGUIDInKey varchar(64)
	DECLARE @csVersion INT = 15
	declare @softCap integer

	SET @licKey = (SELECT TOP 1 EncLicKey FROM LicCsl WITH (NOLOCK) ORDER BY id DESC)

	SELECT @csVersion = MAX(id) FROM simAllGalaxyRel
	IF  @csVersion >=16 --if CSVERSION >=11 use extra field @csGUIDInKey
	BEGIN

	--if sp 21 or newer, use the the stored procedure in the commserv db
	IF @majorVer >= 21
		EXEC xp_RetrieveValidateLicKeyInfo @licKey, 
			@MachID OUTPUT, @DRMachID OUTPUT, @newRepMachID OUTPUT, @csId OUTPUT, @OEMId OUTPUT, @plattype OUTPUT,
 			@relnum OUTPUT, @keyType OUTPUT, @rmkc OUTPUT, @evalDays OUTPUT, @expDate OUTPUT, @enclic OUTPUT, @licTypeList OUTPUT,@csGUIDInKey OUTPUT, @softCap OUTPUT
 	ELSE
		EXEC master..xp_RetrieveValidateLicKeyInfo @licKey, 
			@MachID OUTPUT, @DRMachID OUTPUT, @newRepMachID OUTPUT, @csId OUTPUT, @OEMId OUTPUT, @plattype OUTPUT,
	 		@relnum OUTPUT, @keyType OUTPUT, @rmkc OUTPUT, @evalDays OUTPUT, @expDate OUTPUT, @enclic OUTPUT, @licTypeList OUTPUT,@csGUIDInKey OUTPUT, @softCap OUTPUT	
	END
	ELSE
	BEGIN
	EXEC master..xp_RetrieveValidateLicKeyInfo @licKey, 
		@MachID OUTPUT, @DRMachID OUTPUT, @newRepMachID OUTPUT, @csId OUTPUT, @OEMId OUTPUT, @plattype OUTPUT,
 		@relnum OUTPUT, @keyType OUTPUT, @rmkc OUTPUT, @evalDays OUTPUT, @expDate OUTPUT, @enclic OUTPUT, @licTypeList OUTPUT
	END
    IF (@plattype =1 )
    BEGIN
                    DECLARE @expDT1 DATETIME
                    IF  @csVersion =15
                                    SELECT @expDT1 = MAX(ExpiryDateUTC) FROM licUsage WITH (NOLOCK) WHERE eval = 1 AND OpType = 'Install' AND ExpiryDateUTC > '1970-01-01' AND CID = 2 AND LicType IN (select simLicAppType from simAppTypeLicTypeMap where isCellLevel > 0)
                    ELSE
                                    SELECT @expDT1 = MAX(ExpiryDateUTC) FROM licUsage WITH (NOLOCK) WHERE eval = 1 AND OpType = 'Install' AND ExpiryDateUTC > '1970-01-01' AND CID = 2
                    SET @expDate = ISNULL(dbo.GetUnixTime(@expDT1), 0)
    END
END
ELSE
BEGIN
	DECLARE @expDT DATETIME
	
	SELECT @expDT = MAX(ExpiryDateUTC) FROM licUsage WITH (NOLOCK) WHERE eval = 0 AND OpType = 'Install' AND ExpiryDateUTC > '1970-01-01'
	
	SET @expDate = ISNULL(dbo.GetUnixTime(@expDT), 0)
END


DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode	 NVARCHAR(MAX) ='0';
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
DECLARE @service BIGINT =0
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#serviceState'))
    DROP TABLE #serviceState
CREATE TABLE #serviceState (svcStatus BIGINT)
INSERT INTO #serviceState(svcStatus) VALUES (0)

IF ((ISNULL(@isPrivateRunning, '') = 'Metrics Direct Dip' AND @frequencyMode <> '1') or (@frequencyMode = '3'))
BEGIN
	INSERT INTO #serviceState
	SELECT power(2, 0) --HealthCheck
	UNION
	SELECT power(2, 2) --Activity
	UNION
	SELECT power(2, 5) --Chargeback
END
ELSE
BEGIN
	IF OBJECT_ID('CloudMonitoringService') IS NOT NULL
	BEGIN
		DECLARE @sqlQuery NVARCHAR(MAX)
		SET @sqlQuery = N'
		IF ((''' + ISNULL(@isPrivateRunning, '') + ''' = ''Metrics Reporting'') OR (' + @frequencyMode +'=1))
		BEGIN
			 INSERT INTO #serviceState
			 SELECT	power(2, id - 1)
			 FROM	CloudMonitoringService WITH (NOLOCK) WHERE (isEnabled& 2=2)
		END
		ELSE
		BEGIN
			 INSERT INTO #serviceState
			 SELECT	power(2, id - 1)
			 FROM	CloudMonitoringService WITH (NOLOCK) WHERE (isEnabled& 1=1)
		END'
		EXEC sp_executesql @sqlQuery
	END
END

SELECT @service += svcStatus FROM #serviceState

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#serviceState'))
    DROP TABLE #serviceState
DECLARE @QNetRegCount INT =0
  IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[QNetReg]') and OBJECTPROPERTY(id, N'IsTable') = 1)
  BEGIN
	    DECLARE @vi INT
	 	DECLARE @vQuery NVARCHAR(1000)

		SET @vQuery = 'SELECT @vi= COUNT(*) FROM  QNetReg WITH (NOLOCK)'

		EXEC SP_EXECUTESQL 
				@Query  = @vQuery
			  , @Params = N'@vi INT OUTPUT'
			  , @vi = @vi OUTPUT
		SET @QNetRegCount= @vi

  END
--Get current license usage collection time
DECLARE @LicUsageCollectionTime BIGINT = 0
SELECT @LicUsageCollectionTime = ISNULL(CONVERT(BIGINT, CONVERT(NVARCHAR(MAX), value)), 0) FROM GXGlobalParam WHERE name = 'LicCurrentUsageCollectionTime'

DECLARE @adminEmail NVARCHAR(126) = ''
SELECT @adminEmail = email FROM UMUsers WHERE id = 1

DECLARE @LSR_enabled INT = 0
SELECT @LSR_enabled=ISNULL(CONVERT(INT,enabled),0)  FROM msdb.dbo.sysjobs WHERE (name = N'Execute LSR Master') 

DECLARE @OldestBKPTime BIGINT = (select top 1 servEndDate from JMBkpStats WITH (NOLOCK) WHERE status in (1, 3, 14) order by servEndDate asc)
DECLARE @OldestRSTTime BIGINT = (select top 1 servEndTime from JMRestoreStats WITH (NOLOCK) WHERE status in (1, 3, 14) order by servEndTime asc)

DECLARE @RegionDisplayName NVARCHAR(127) = ''

IF OBJECT_ID('App_EntityRegionAssoc') IS NOT NULL
BEGIN
	SET @RegionDisplayName = (SELECT ISNULL(R.displayName,'')from App_EntityRegionAssoc ERA inner join App_Region R
					 on ERA.entityId = 2 AND ERA.modified = 0 AND ERA.flags = 1 AND ERA.entityType=3 AND R.id = ERA.regionId)
END

SET @surveyXML =
(
    SELECT  
        @CSSPInfo AS '@CommServVersion',
        CASE 
            WHEN ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode ='1')) THEN (SELECT net_hostname FROM APP_Client WHERE id = @CSClientId)
            ELSE ''
        END AS '@CommServHostName',
        (SELECT TOP 1 value FROM GXGlobalParam where name like 'CSIpAddress') AS '@CurrCSIpAddress',
        @CSTZ AS '@CommServTZ',
        (SELECT csGUID FROM APP_CommCell WITH (NOLOCK) WHERE id = @CSNode) AS '@CommServGUID',
		(SELECT TOP 1 value FROM GXGlobalParam where name like 'User Description') AS '@CSDescription',
        @QNetRegCount AS '@QNetRegCount',
        @brandIdStr AS '@CommServEdition',
        @expDate AS '@LicExpirationDate',
        @service AS '@MetricServices',
		@adminEmail AS '@adminEmail',
	(SELECT (CASE 
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 7 THEN 'Microsoft SQL Server 7.0'
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 8 THEN 'Microsoft SQL Server 2000'
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 9 THEN 'Microsoft SQL Server 2005'
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 10 THEN 'Microsoft SQL Server 2008/2008 R2' 
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 11 THEN 'Microsoft SQL Server 2012'
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 12 THEN 'Microsoft SQL Server 2014'
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 13 THEN 'Microsoft SQL Server 2016'
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 14 THEN 'Microsoft SQL Server 2017'
WHEN LEFT(CAST (SERVERPROPERTY('productVersion') AS VARCHAR(128)),2) = 15 THEN 'Microsoft SQL Server 2019'
ELSE 'SQL Server' END) + '  (' + CAST (SERVERPROPERTY('ProductVersion') AS VARCHAR(128)) + '  ' +
 CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(128)) + ')') AS '@SQLVersion',
		@LicUsageCollectionTime AS '@LicUsageCollectionTime',
		@LSR_enabled AS '@LSR_enabled',
		@OldestBKPTime AS '@OldestBKPTime',
		@OldestRSTTime AS '@OldestRSTTime',
		(SELECT ISNULL(NULLIF(displayname, ''), name) FROM APP_Client  WHERE id = @CSClientId) AS '@CommServDisplayName',
		(SELECT @RegionDisplayName) AS '@RegionDisplayName'
					 
     FOR XML PATH ('CommServerInfo')
)
--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