SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON

DECLARE @ErrorTag	NVARCHAR(20) = '###ERROR### '
DECLARE @WarningTag	NVARCHAR(20) = '###WARNING### '
DECLARE @AdviceTag	NVARCHAR(20) = '###ADVICE### '
DECLARE @DescTag 	NVARCHAR(20) = '###DESCRIPTION###'
DECLARE @errorMsg 	NVARCHAR(MAX) = 'The CommServe does not have sufficient '
DECLARE @warningMsg 	NVARCHAR(MAX) = ''
DECLARE @adviceWarnMsg 	NVARCHAR(MAX) = 'Make sure that the CommServe has the necessary permanent '
DECLARE @adviceMsg 	NVARCHAR(MAX) = 'Contact your Software Provider to obtain '
DECLARE @errorCode	INT = 0
DECLARE @retCode	INT = 0
DECLARE @isLicAvail	INT = 1
DECLARE @FreePermLicensesAvailable INT = 0
DECLARE @FreeEvalLicensesAvailable INT = 0
DECLARE @TotalLicensesCount INT = 0
DECLARE @ExDAGAppType 	INT = 53
DECLARE @OracleRacAppType INT = 80 -- windows
DECLARE @SharePointAppType INT = 78
DECLARE @MsSqlAG 		INT = 81
DECLARE @SapHanaAppType INT = 135
DECLARE @DB2AppType 	INT = 103
DECLARE @VsaAppType 	INT = 106
DECLARE @DistributedAppsAppType INT = 64
DECLARE @apptypeTable TABLE (t_PseudoApptype INT, t_PhysicalApptype INT, t_status INT, t_lic INT, t_attrName VARCHAR(256), t_AgentName VARCHAR(256))
DECLARE @appClass1Lic	INT = 3
DECLARE @appClass3Lic	INT = 16
DECLARE @appClass2Lic	INT = 17
DECLARE @FSLic			INT = 1
DECLARE @vsaLic			INT = 143
DECLARE @licToConsume	INT = 0
DECLARE @PermLicToRelease INT = 0
DECLARE @EvalLicToRelease INT = 0
DECLARE @currentTime 	BIGINT = 0
DECLARE @OracleDBAppType INT = 22 -- physical client apptype for oracle
DECLARE @SapHanaPhysAppType INT = 136 -- physical client apptype for sap
DECLARE @DB2PhysAppType 	INT = 62 -- physical client apptype for db2
SET @currentTime 		= dbo.GetUnixTime(GETUTCDATE())

PRINT @DescTag + ' Check for enough Licenses for Pseudo Clients'

DECLARE @prevReleaseID INT = 0
select top 1 @prevReleaseID = id from simAllGalaxyRel order by id desc
IF @prevReleaseID < 16 OR (@prevReleaseID = 16 AND (select TOP 1 HighestSP from simInstalledPackages where ClientId = 2 order by id desc) < 7)
BEGIN

INSERT INTO @apptypeTable
VALUES(@ExDAGAppType, 	@ExDAGAppType,		0x200000,	@appClass1Lic,	'',							'Exchange Database'),
(@OracleRacAppType, 	@OracleDBAppType, 	0x0040,		0,				'',							'Oracle Database'),
(@MsSqlAG,				@MsSqlAG, 			0x20000000,	@appClass1Lic,	'',							'SQL Server'),
(@SapHanaAppType,		@SapHanaPhysAppType,0x2000000,	@appClass3Lic,	'',							'SAP HANA'),
(@DB2AppType,			@DB2PhysAppType,	0x0200,		@appClass2Lic,	'',							'DB2 on Unix'),
(@SharePointAppType, 	@SharePointAppType, 0,			@appClass1Lic,	'SharePoint Farm Client',	'SharePoint Server'),
(@VsaAppType, 			@VsaAppType, 		0,			@vsaLic,		'Vs Member Servers',		'Virtual Server'),
(@DistributedAppsAppType, 0,				0,			@FSLic,			'Distributed iDA Client',	'Distributed Apps')

DECLARE @PseudoClientsList TABLE(t_cid int, t_name VARCHAR(512), t_apptype int, t_lictype INT )
INSERT INTO @PseudoClientsList
SELECT distinct AC.id, AC.name, t_PseudoApptype, t_lic
FROM @apptypeTable, APP_Client AC WITH (NOLOCK) INNER JOIN
APP_IDAName IDN WITH (NOLOCK) ON IDN.clientId = AC.id AND IDN.status&(2+4) = 0
WHERE (AC.status&t_status > 0) 

--SharePoint, Distributed Apps
INSERT INTO @PseudoClientsList
SELECT distinct AC.id, AC.name, t_PseudoApptype, t_lic
FROM @apptypeTable, APP_ClientProp AP WITH (NOLOCK) INNER JOIN
APP_Client AC WITH (NOLOCK) ON AP.componentNameId = AC.id INNER JOIN
APP_IDAName IDN WITH (NOLOCK) ON IDN.clientId = AC.id AND IDN.status&(2+4) = 0
WHERE AP.attrName = t_attrName AND AP.attrval = '1' AND AP.modified = 0 AND LEN(AP.attrName) > 0 

--VSA
INSERT INTO @PseudoClientsList
SELECT distinct AC.id, AC.name, t_PseudoApptype, t_lic
FROM @apptypeTable, APP_INSTANCEPROP INS WITH (NOLOCK) INNER JOIN
APP_Application APP WITH (NOLOCK) ON INS.componentNameId = APP.instance INNER JOIN
APP_Client AC WITH (NOLOCK) on APP.clientId = AC.id INNER JOIN
APP_IDAName IDN WITH (NOLOCK) on IDN.clientId = AC.id AND IDN.status&(2+4) = 0
WHERE INS.attrName = t_attrName AND INS.modified = 0 AND LEN(INS.attrName) > 0 

-- delete the clients where license is already consumed
DELETE @PseudoClientsList FROM LicUsage WHERE t_cid = cid AND AppType = t_apptype

DECLARE @OracleRacphysicalClient TABLE(t_pseudoCid INT, t_physicalCid INT, t_simosId INT)
INSERT INTO @OracleRacphysicalClient
SELECT tmp.t_cid, rac.clientId, AC.simOperatingSystemId 
FROM @PseudoClientsList tmp JOIN APP_Application app WITH (NOLOCK) ON tmp.t_cid = app.clientId AND tmp.t_apptype = @OracleRacAppType
JOIN APP_OracleRacInstance rac WITH (NOLOCK) ON rac.instaceId = app.instance
JOIN APP_Client AC WITH (NOLOCK) ON AC.id = rac.clientId

UPDATE T SET t_lictype = @appClass1Lic FROM @PseudoClientsList T JOIN @OracleRacphysicalClient tmp ON t_pseudoCid = t_cid
JOIN simOperatingSystem OS WITH(NOLOCK) ON tmp.t_simosId = id  AND Type = 'Windows'
WHERE t_apptype = @OracleRacAppType

UPDATE @PseudoClientsList SET t_lictype = @appClass2Lic WHERE t_lictype = 0

-- Determine where SharePoint 'SharePoint Member Servers' client properties are being stored for PreUpgrade Check
DECLARE @SharePointMSLocation INT = 0	-- 0:Unknown, 1:APP_ClientProp, 2:APP_ComponentProp
-- Original Location
SELECT TOP 1 
	@SharePointMSLocation = 1
FROM APP_ClientProp CP WITH(NOLOCK)
WHERE
	CP.attrName = N'SharePoint Member Servers'
IF (@SharePointMSLocation <> 1)
BEGIN
	-- New Location
	SELECT TOP 1 
		@SharePointMSLocation = 2
	FROM 
		-- Relocated 'SharePoint Member Servers' XML Document from APP_ClientProp to APP_ComponentProp Table since attrVal limited to max data size of NVARCHAR(1024)
		APP_ComponentProp CP WITH (NOLOCK) 
	WHERE 
		CP.componentType = 3			-- CV_COMPONENT_TYPE_CLIENT
		AND CP.propertyTypeId = 1046	-- CV_COMPONENT_SHAREPOINT_SECONDARY_MEMBER_SERVERS		-- Old APP_ClientProp 'SharePoint Member Servers'
END

--Count license for physical client.
DECLARE @PhysicalClientsList TABLE(t_cid int, t_apptype int )

INSERT INTO @PhysicalClientsList
--RAC
SELECT distinct I.clientId, t_PhysicalApptype
FROM @apptypeTable tmp,APP_OracleRacInstance I WITH (NOLOCK) WHERE t_AgentName = 'Oracle Database'
UNION ALL
--Exchange DAG
SELECT distinct AC.id, t_PhysicalApptype
FROM @apptypeTable tmp,APP_ClientProp AP WITH (NOLOCK)
JOIN APP_Client AC WITH (NOLOCK)  ON AP.attrName = AC.net_hostname
WHERE AP.attrType = 125 AND AP.modified = 0 AND t_AgentName = 'Exchange Database'
UNION ALL
-- DB2_DPF
SELECT distinct t_pClientId, t_PhysicalApptype
FROM @apptypeTable tmp,
(SELECT t_pClientId = substring(T1.Data, CHARINDEX(',', T1.Data)+1, LEN(T1.Data))
	FROM    APP_InstanceProp IP WITH (NOLOCK) CROSS APPLY dbo.SplitString(IP.attrVal, ' ') T1
    WHERE   IP.attrName = 'DB2 Partition Clients' and IP.modified = 0 AND LEN(IP.attrVal) > 0 ) T2
WHERE t_AgentName = 'DB2 on Unix'
UNION ALL
--SharePoint
SELECT distinct Cid, t_PhysicalApptype
FROM @apptypeTable tmp,
(SELECT CAST(AP.attrVal AS INT) AS cid FROM APP_ClientProp AP WITH (NOLOCK) WHERE AP.attrName = 'SharePoint Primary Member Server' AND LEN(AP.attrVal) > 0 AND AP.modified = 0) T
WHERE t_AgentName = 'SharePoint Server'	
UNION
SELECT distinct Cid, t_PhysicalApptype
FROM @apptypeTable tmp,
(SELECT p.value('@clientId', 'int') AS cid FROM APP_ClientProp AP WITH (NOLOCK) CROSS APPLY (select cast(attrVal as xml)) as X(X) CROSS APPLY X.nodes('App_MemberServers/memberServers/client') as P(P)
WHERE AP.attrName = 'SharePoint Member Servers' AND LEN(AP.attrVal) > 0 AND AP.modified = 0 AND @SharePointMSLocation = 1) T
WHERE t_AgentName = 'SharePoint Server'	
	AND @SharePointMSLocation = 1
UNION
SELECT distinct 
	T.Cid, 
	t_PhysicalApptype
FROM @apptypeTable tmp,
	(
		-- Relocated 'SharePoint Member Servers' XML Document from APP_ClientProp to APP_ComponentProp Table since attrVal limited to max data size of NVARCHAR(1024)
		SELECT DISTINCT
			P.value('@clientId', 'int') AS cid 
		FROM APP_ComponentProp CP WITH (NOLOCK) 
			CROSS APPLY (SELECT CAST(CP.stringVal as xml)) AS X(X) 
			CROSS APPLY X.nodes('App_MemberServers/memberServers/client') AS P(P)
		WHERE 
			CP.componentType = 3			-- CV_COMPONENT_TYPE_CLIENT
			AND CP.propertyTypeId = 1046	-- CV_COMPONENT_SHAREPOINT_SECONDARY_MEMBER_SERVERS		-- Old APP_ClientProp 'SharePoint Member Servers'
			AND LEN(CP.stringVal) > 0 
			AND CP.modified = 0
			AND @SharePointMSLocation = 2
	) T
WHERE t_AgentName = 'SharePoint Server'	
	AND @SharePointMSLocation = 2
UNION ALL
--MSSqlAG
SELECT distinct Cid, t_PhysicalApptype
FROM @apptypeTable tmp,
(SELECT p.value('@clientId', 'int') AS cid FROM APP_InstanceProp AP WITH (NOLOCK) CROSS APPLY (select cast(attrVal as xml)) 
as X(X) CROSS APPLY X.nodes('App_SQLAvailabilityReplicas/SQLAvailabilityReplicasList/replicaClient') as P(P)
WHERE AP.attrName = 'Availability Replicas' AND LEN(AP.attrVal) > 0 AND AP.modified = 0) T
WHERE t_AgentName = 'SQL Server'
UNION ALL
--VSA
SELECT distinct Cid, t_PhysicalApptype
FROM @apptypeTable tmp,LicUsage WITH (NOLOCK) WHERE AppType = t_PhysicalApptype AND t_AgentName = 'Virtual Server'
UNION ALL
-- SAP_HANA
SELECT distinct t_pClientId, t_PhysicalApptype
FROM @apptypeTable tmp,(SELECT t_pClientId = T1.Data
        FROM    APP_InstanceProp IP WITH (NOLOCK) CROSS APPLY dbo.SplitString(IP.attrVal, '+') T1
        WHERE   IP.attrName = 'Database Clients' and IP.modified = 0 AND LEN(IP.attrVal) > 0 ) T2
WHERE t_AgentName = 'SAP HANA'

--Remove pseudo clients from the list fetched which leave us with only proxy clients.
DELETE @PhysicalClientsList FROM 
APP_Application app WITH (NOLOCK)
JOIN app_instanceprop Iprop WITH (NOLOCK) ON app.instance = Iprop.componentNameId AND Iprop.attrName = 'Virtual Server Instance Type' AND LEN(Iprop.attrVal) > 0 AND Iprop.modified = 0
WHERE t_cid = clientId AND app.appTypeId = t_apptype AND t_apptype = 106

--Remove clients where data is backedup in DB
DELETE @PhysicalClientsList FROM 
APP_Application app WITH (NOLOCK)
JOIN archFile afile WITH (NOLOCK) ON app.id = afile.appId 
WHERE t_cid = app.clientId AND app.appTypeId = t_apptype

DECLARE @totalLicCount TABLE(t_lictype INT, t_perm INT, t_eval INT)
INSERT INTO @totalLicCount
SELECT lictype, [0] AS Perm, [1] AS Eval FROM 
(SELECT cid,lictype,eval FROM LicUsage LU WITH (NOLOCK) JOIN @PhysicalClientsList tmp ON LU.cid = tmp.t_cid AND LU.appType = tmp.t_apptype 
WHERE opType = 'Install' AND (dbo.GetUnixTime(ExpiryDateUTC) = 0 OR dbo.GetUnixTime(ExpiryDateUTC) > @currentTime)) T
pivot ( COUNT(cid) FOR eval in([0],[1]) ) AS P

DECLARE LicenseCursor CURSOR LOCAL FORWARD_ONLY FOR   		 
SELECT DISTINCT t_lictype FROM @PseudoClientsList GROUP BY t_lictype

OPEN LicenseCursor 
FETCH FROM LicenseCursor INTO @licToConsume
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @LicName 	NVARCHAR(256) = ''
	SET @TotalLicensesCount = 0
	SET @TotalLicensesCount = (SELECT COUNT(distinct t_cid) FROM   @PseudoClientsList WHERE t_lictype = @licToConsume)

	IF (@TotalLicensesCount = 0)
	BEGIN
		GOTO CONT
	END
	
	SELECT @LicName = short_name FROM simLicAppTypeV2 WHERE id = @licToConsume
	
	IF NOT EXISTS (SELECT * FROM licAAL WHERE simLicAppTypeId = @licToConsume)
    BEGIN
        SET @errorCode = 2
		SET @errorMsg = @errorMsg + @LicName + ','
		SET @adviceMsg = @adviceMsg + cast(@TotalLicensesCount as varchar(10) )+' more '+@LicName+','
		GOTO CX_EXIT
    END

	DECLARE @retLicData	TABLE (	licName	varchar(1024),
					TotalPermLicensesIssued INTEGER,
					FreePermLicensesAvailable INTEGER,
					TotalEvalLicensesIssued INTEGER,
					FreeEvalLicensesAvailable INTEGER	)

	BEGIN TRY
	INSERT INTO @retLicData
	EXEC QS_GetLicenseInfo @LicName
	END TRY
	BEGIN CATCH
		--If extended SP are not found, skip the check
		GOTO CONT
	END CATCH
		
	SELECT @FreePermLicensesAvailable = FreePermLicensesAvailable , @FreeEvalLicensesAvailable = FreeEvalLicensesAvailable FROM @retLicData

	IF @FSLic <> @licToConsume
	BEGIN
		IF (@FreePermLicensesAvailable >= 0)
			SELECT @FreePermLicensesAvailable = @FreePermLicensesAvailable + t_perm FROM @totalLicCount WHERE t_lictype = @licToConsume
		IF (@FreeEvalLicensesAvailable >= 0)
			SELECT @FreeEvalLicensesAvailable = @FreeEvalLicensesAvailable + t_eval FROM @totalLicCount WHERE t_lictype = @licToConsume
	END

	/*CS has sufficient quantity*/
	IF (@FreePermLicensesAvailable < 0) OR (@FreePermLicensesAvailable >= @TotalLicensesCount)
	BEGIN
		GOTO CONT
	END

	IF (@FreeEvalLicensesAvailable < 0) OR ( @FreePermLicensesAvailable + @FreeEvalLicensesAvailable >= @TotalLicensesCount)
	BEGIN
		SET @warningMsg = @warningMsg + cast( (@TotalLicensesCount - @FreePermLicensesAvailable) as varchar(10) ) + ' Pseudo clients will consume '+@LicName+' Evaluation licenses'
		SET @warningMsg = @warningMsg + CHAR(13) + CHAR(10)
		SET @adviceWarnMsg = @adviceWarnMsg + @LicName + ','
		IF @errorCode = 0
			SET @errorCode = 1
		GOTO CONT
	END
	ELSE
	BEGIN
		SET @errorMsg = @errorMsg + @LicName + ','
		SET @adviceMsg = @adviceMsg + cast((@TotalLicensesCount - @FreePermLicensesAvailable) as varchar(10) )+' more '+@LicName+','
		SET @errorCode = 2
		GOTO CONT
	END
	
	CONT:
	FETCH FROM LicenseCursor INTO @licToConsume
END

CX_EXIT:
BEGIN
	IF @errorCode = 2
	BEGIN
		PRINT  @ErrorTag + LEFT(@errorMsg, LEN(@errorMsg) - 1) + ' licenses for pseudo clients.'
		PRINT  @AdviceTag + LEFT(@adviceMsg, LEN(@adviceMsg) - 1) + ' licenses'
		PRINT CHAR(13) + CHAR(10)
		IF @warningMsg <> ''
		BEGIN
			PRINT  @WarningTag + @warningMsg
			PRINT  @AdviceTag + LEFT(@adviceWarnMsg, LEN(@adviceWarnMsg) - 1) + ' licenses'
		END
	END
	ELSE IF @errorCode = 1	
	BEGIN
		PRINT  @WarningTag +@warningMsg
		PRINT  @AdviceTag + LEFT(@adviceWarnMsg, LEN(@adviceWarnMsg) - 1) + ' licenses'
	END
END
END
SELECT @errorCode