--@querytype    CSV
--Name:- Current License Summary
--Description:- Collects summary of current license usages  

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRY
        
	DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
	DECLARE @queryId AS INTEGER = 233

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery233_LastCollectionTime'
	DECLARE @isPrivateRunning VARCHAR(256)='';
	DECLARE @frequencyMode   NVARCHAR(MAX) ='0';
	DECLARE @sqlQuery NVARCHAR(MAX) = '';

	IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
		SELECT @frequencyMode = mode FROM #MetricsInputParams

	IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
		SELECT @isPrivateRunning = value FROM GXGlobalParam WITH (NOLOCK) WHERE name ='CommservSurveyRunning'

	IF ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode = '1'))
		SET @TIME_STAMP_NAME = 'CommservSurveyPrivate_LicCurrentSummary_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDip_LicCurrentSummary_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_LicCurrentSummary_CollectionTime'

	--if gxGlobalParam is explicitly set to 0, do not collect anything (will act as back out mechanism in case, we want to stop collecting these usages)
	DECLARE @useNewLicCal INT = 1
	IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'nUseNewLicCal')
		SELECT @useNewLicCal = CAST(CAST(value AS VARCHAR(32)) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name ='nUseNewLicCal'
	IF @useNewLicCal=0
		GOTO LIC_ERROR
	
	--get VOI to OI quantity to be swappped in LSR
	DECLARE @nOIUsers INT = 0, @nVOIUsers INT = 0, @nVOIToOI INT = 0, @isOIValid INT = 0, @isVOIValid INT = 0
	IF (OBJECT_ID('CommServ.dbo.Lic_CurrentSummary_vw') IS NOT NULL)--OI/VOI is required only from SP17. Starting SP17, this view is present in CSDB
	BEGIN
		SELECT @nOIUsers=ISNULL(SUM(Usage),0) FROM CommServ.dbo.Lic_CurrentSummary_vw WHERE UsageType = 30 
		SELECT @nVOIUsers=ISNULL(SUM(Usage),0) FROM CommServ.dbo.Lic_CurrentSummary_vw WHERE UsageType = 31
	END

	IF (OBJECT_ID('CommServ.dbo.LicCheckOIStatus') IS NOT NULL)--this stored proc is available only from SP17 
		EXEC CommServ.dbo.LicCheckOIStatus '', 0, 0, @isOIValid OUTPUT, @isVOIValid OUTPUT

	IF (OBJECT_ID('CommServ.dbo.LicVOIToOI') IS NOT NULL)--this stored proc is available only from SP17	
	BEGIN
		IF @isOIValid > 0 AND @isVOIValid = 0
			SET @nVOIToOI=@nVOIUsers			
		ELSE IF @isOIValid > 0 AND @isVOIValid > 0 AND @nVOIUsers > 0
			EXEC CommServ.dbo.LicVOIToOI @nVOIUsers, @nOIUsers, @nVOIToOI OUTPUT
	END

	--MR 287355 - borrowing logic is not working in cloud LSR, when only VOI is used and no OI is in use
	IF (OBJECT_ID('CommServ.dbo.Lic_CurrentUsage') IS NOT NULL)--if commcell is below SP17, Lic_CurrentUsage is in HistoryDB. Hence this check is requried
	BEGIN
		IF NOT EXISTS (SELECT 1 FROM CommServ.dbo.Lic_CurrentUsage WITH(NOLOCK) WHERE UsageType=30) AND (@nVOIToOI > 0)--OI/VOI is present only from SP17. Hence need to check this condition only on the table in CSDB
		BEGIN
			SET @sqlQuery = N'
				SELECT 30 AS ''UsageType'','+CONVERT(VARCHAR(20),@nVOIToOI)+' AS ''AppTypeId'', -1 AS ''LicType'', 
				0 AS ''Usage'', -1 AS ''CapacityFlag'', -1 AS ''VMFlag''
				UNION
			'
		END
	END

	--MR 345537 - get total virtual instance count for applying VOI recomputation logic
	DECLARE @voiUsed INT = 0, @dbInstances INT = 0
	IF (OBJECT_ID('CommServ.dbo.Lic_CurrentUsage') IS NOT NULL)--if commcell is below SP17, Lic_CurrentUsage is in HistoryDB. Hence this check is required
	BEGIN--OI/VOI implementation is avaiable only from SP17. Hence no need to find these values for pre SP17 CommCells
		SELECT @voiUsed=COUNT(DISTINCT VMId) FROM
		(
			SELECT DISTINCT ObjectId AS VMId FROM CommServ.dbo.Lic_CurrentUsage WITH(NOLOCK) WHERE UsageType IN (19, 21)
			UNION
			SELECT DISTINCT ClientId AS VMId FROM CommServ.dbo.Lic_CurrentUsage WITH(NOLOCK) WHERE UsageType=31 AND SubclientName IS NULL
		)V
		SELECT @dbInstances=COUNT(SubclientName) FROM CommServ.dbo.Lic_CurrentUsage WITH(NOLOCK) WHERE UsageType=31 AND SubclientName IS NOT NULL
		SET @voiUsed=@voiUsed+@dbInstances
	END	

	-- get unstructured to structured capacity to be swapped in LSR
	DECLARE @nUnstructuredToStructured BIGINT = 0
	IF (OBJECT_ID('CommServ.dbo.LicUsageBasedConfig') IS NOT NULL)
	BEGIN
		SELECT @nUnstructuredToStructured=ISNULL(attrVal, 0) FROM CommServ.dbo.LicUsageBasedConfig WITH (NOLOCK) WHERE attrName = 'LicSelectConvertedSize'
	END

	--upload summary of current license usage from view Lic_CurrentSummary_vw
	SET @sqlQuery += N'
		SELECT DISTINCT
		UsageType AS ''UsageType'',
		CASE WHEN UsageType=30 THEN '+CONVERT(VARCHAR(20),@nVOIToOI)+' WHEN UsageType=31 THEN ' +CONVERT(VARCHAR(20),@voiUsed)+' ELSE AppTypeId END AS ''AppTypeId'',
		LicType AS ''LicType'',
		ISNULL(Usage,0) AS ''Usage'',
		CapacityFlag AS ''CapacityFlag'',
		CASE WHEN USAGETYPE IN (43,44) THEN '+CONVERT(VARCHAR(20),@nUnstructuredToStructured)+' ELSE VMFlag END AS ''VMFlag''
        FROM 
	'
	IF (OBJECT_ID('CommServ.dbo.Lic_CurrentSummary_vw') IS NOT NULL)--from SP17 this view is moved to CSDB
		SET @sqlQuery += N' CommServ.dbo.Lic_CurrentSummary_vw'            		
	ELSE IF (OBJECT_ID('HistoryDB.dbo.Lic_CurrentSummary_vw') IS NOT NULL)--if CS is below SP17, continue to collect usage using HistoryDB view
        SET @sqlQuery += N' HistoryDB.dbo.Lic_CurrentSummary_vw'
	ELSE
		GOTO LIC_ERROR

	SET @sqlQuery += N' WITH(NOLOCK)'
	EXEC sp_executesql @sqlQuery	

	IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
	BEGIN
		DECLARE @lastcollectionTimeSTR VARCHAR(256) = '';
		EXEC GetSurveyParamValue @TIME_STAMP_NAME, @lastcollectionTimeSTR OUTPUT 
		IF @lastcollectionTimeSTR = ''
			BEGIN
			EXEC SetSurveyParamValue @TIME_STAMP_NAME, ''
			END
	END
	ELSE
	BEGIN
		DECLARE @DO_IT VARCHAR(256)
		IF NOT EXISTS (SELECT 1 FROM GXGlobalParam WITH(NOLOCK) WHERE name = @TIME_STAMP_NAME)
		BEGIN
			IF EXISTS (SELECT 1 FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
				INSERT INTO GXGlobalParam (name, value) SELECT @TIME_STAMP_NAME, ''
			ELSE
			BEGIN
				SET @DO_IT = 'INSERT INTO GXGlobalParam (name, value, created, modified) VALUES('''
				 + @TIME_STAMP_NAME
				 + ''', '''','
				 + CONVERT(varchar(20), @logDate)                   -- Use Proper "create" date!
				 + ',0)'
				EXEC (@DO_IT)
			END
		END
	END

END TRY
BEGIN CATCH  
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH   

IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @tempTimeStamp NVARCHAR(256)
	set @tempTimeStamp = CAST(dbo.getunixtime(GETDATE()) AS NVARCHAR(20))
	EXEC SetSurveyParamValue @TIME_STAMP_NAME, @tempTimeStamp
END
ELSE
BEGIN
	UPDATE GXGlobalParam SET value = CAST(dbo.getunixtime(GETDATE()) AS NVARCHAR(20)) WHERE name = @TIME_STAMP_NAME
END

LIC_ERROR:

SET NOCOUNT OFF