--@querytype    CSV
--Name:- SCL License Peak Summary
--Description:- Collects summary of subclient level peak license usages 

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery235_LastCollectionTime'
	DECLARE @monthstart DATETIME = DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)
	DECLARE @monthend DATETIME = @monthstart
	DECLARE @lastcollectionTime INT = 0
	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_LicSCLSummary_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDip_LicSCLSummary_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_LicSCLSummary_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

	IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
	BEGIN
		DECLARE @lastcollectionTimeSTR VARCHAR(256)='';
		EXEC GetSurveyParamValue @TIME_STAMP_NAME, @lastcollectionTimeSTR OUTPUT 
		SELECT @lastcollectionTime = CAST(@lastcollectionTimeSTR AS INT)
	END
	ELSE
	BEGIN
		SELECT @lastcollectionTime = CAST(CAST(value AS VARCHAR(20)) AS INT) FROM GXGlobalParam WITH(NOLOCK) WHERE name = @TIME_STAMP_NAME
	END

	--first time collect for 3 months. From next time onwards, collect for single month 
	IF  @LastCollectionTime > 0
	BEGIN
		IF DATEDIFF(MM, dbo.GetDateTime(@lastcollectionTime), @monthstart) > 0
			SELECT @monthstart = DATEADD(MM, -1, @monthstart)
	END
	ELSE
		SELECT @monthstart = DATEADD(MM, -3, @monthstart)

	--upload the purchased status of virtual licenses as UsageType=-1|PeakType=1 to add VOI recomputation logic in SPUR (project #2052)
	DECLARE @voiLicType INT = 100033, @pvmLicType INT = 100019, @acvLicType INT = 100027, @socketLicType INT = 100018
	--get the month when the latest license file was first applied on the CommCell. Purchased status of virtual licenses from this month onwards should be set based on the content of the latest license file
	DECLARE @LastLicenseApplicationMonth DATETIME
	SELECT TOP 1 @LastLicenseApplicationMonth = ISNULL(DATEADD(month, DATEDIFF(month, 0, dbo.GetDateTime(LicUpdateTime)), 0), dbo.GetDateTime(0)) 
	FROM LicCsl L WITH (NOLOCK) INNER JOIN SimLicenses S ON L.Rkey=S.rkey WHERE DATALENGTH(S.licXML)>0 ORDER BY L.id DESC

	IF object_id('tempdb.dbo.#VirtualLicenses') IS NOT NULL DROP TABLE #VirtualLicenses
	CREATE TABLE #VirtualLicenses(LicType INT, PermTotal INT, EvalTotal INT, TermDate DATETIME, ExpiryDate DATETIME, Purchased INT, PurchaseExpiryMonth DATETIME)	
	IF (OBJECT_ID('CommServ.dbo.Lic_LicenseInfo') IS NOT NULL)--if commcell is below SP17, Lic_LicenseInfo is in HistoryDB. Hence this check is required
	BEGIN
		INSERT #VirtualLicenses--OI/VOI implementation is avaiable only from SP17. Hence no need to find these values for pre SP17 CommCells
		SELECT DISTINCT L.LicType, L.PermTotal, L.EvalTotal, L.TermDate, L.ExpiryDate, 0, '1970-01-01 00:00:00.000'
		FROM CommServ.dbo.Lic_LicenseInfo L WITH(NOLOCK) 
		WHERE L.LicType IN (@voiLicType, @pvmLicType, @acvLicType, @socketLicType)
	END

	--get purchased status of virtual licenses from latest license file applied on CS
	UPDATE #VirtualLicenses 
	SET Purchased=1, PurchaseExpiryMonth='9999-01-01 00:00:00.000'
	WHERE (PermTotal<>0 OR (EvalTotal<>0 AND (TermDate>=GETUTCDATE() OR TermDate='1970-01-01 00:00:00.000'))) AND (ExpiryDate>=GETUTCDATE() OR ExpiryDate='1970-01-01 00:00:00.000')

	--update purchase status based on term expiry and license expiry dates
	UPDATE #VirtualLicenses
	SET PurchaseExpiryMonth=CASE WHEN (PermTotal<>0 AND (ExpiryDate<GETUTCDATE() AND ExpiryDate<>'1970-01-01 00:00:00.000')) THEN DATEADD(month, DATEDIFF(month, 0, ExpiryDate), 0)
							ELSE 
								(CASE WHEN EvalTotal<>0 THEN 
									(CASE WHEN (TermDate<GETUTCDATE() AND TermDate<>'1970-01-01 00:00:00.000') THEN DATEADD(month, DATEDIFF(month, 0, TermDate), 0) 
										  WHEN (ExpiryDate<GETUTCDATE() AND ExpiryDate<>'1970-01-01 00:00:00.000') THEN DATEADD(month, DATEDIFF(month, 0, ExpiryDate), 0) 
									END)
								END)
							END
	WHERE Purchased=0

	IF object_id('tempdb.dbo.#VirtualLicenseHistory') IS NOT NULL DROP TABLE #VirtualLicenseHistory
	CREATE TABLE #VirtualLicenseHistory(MonthStart DATETIME, voiPurchased INT, pvmPurchased INT, acvPurchased INT, socketPurchased INT)

	DECLARE @month DATETIME = @monthstart
	WHILE @month <= @monthend
	BEGIN
		INSERT #VirtualLicenseHistory VALUES (@month, 0, 0, 0, 0)
		SET @month = DATEADD(MM, 1, @month)
	END

	--update purchased status of virtual licenses for each uploading month based on the latest license file applied on CS
	IF @LastLicenseApplicationMonth <> dbo.GetDateTime(0)
	BEGIN
		--update purchased status of VOI
		UPDATE #VirtualLicenseHistory
		SET voiPurchased=1
		WHERE MonthStart >= @LastLicenseApplicationMonth
		AND MonthStart < (SELECT PurchaseExpiryMonth FROM #VirtualLicenses WHERE LicType=@voiLicType)

		--update purchased status of PVM
		UPDATE #VirtualLicenseHistory
		SET pvmPurchased=1
		WHERE MonthStart >= @LastLicenseApplicationMonth
		AND MonthStart < (SELECT PurchaseExpiryMonth FROM #VirtualLicenses WHERE LicType=@pvmLicType)

		--update purchased status of ACV
		UPDATE #VirtualLicenseHistory
		SET acvPurchased=1
		WHERE MonthStart >= @LastLicenseApplicationMonth
		AND MonthStart < (SELECT PurchaseExpiryMonth FROM #VirtualLicenses WHERE LicType=@acvLicType)
				
		--update purchased status of sockets
		UPDATE #VirtualLicenseHistory
		SET socketPurchased=1
		WHERE MonthStart >= @LastLicenseApplicationMonth
		AND MonthStart < (SELECT PurchaseExpiryMonth FROM #VirtualLicenses WHERE LicType=@socketLicType)
	
		--Purchased status is valid only for the months for which the latest license file was applied on CS. Hence set status of all other months to -1
		UPDATE #VirtualLicenseHistory SET voiPurchased=-1, pvmPurchased=-1, acvPurchased=-1, socketPurchased=-1 WHERE MonthStart < @LastLicenseApplicationMonth
	END
	ELSE 
	BEGIN
		--If latest license file application time is not available, set status of all months to -1
		UPDATE #VirtualLicenseHistory SET voiPurchased=-1, pvmPurchased=-1, acvPurchased=-1, socketPurchased=-1
	END

	--MR 345537 - get total virtual instance count for applying VOI recomputation logic
	IF object_id('tempdb.dbo.#totalVOIHistory') IS NOT NULL DROP TABLE #totalVOIHistory
	CREATE TABLE #totalVOIHistory(MonthStart DATETIME, voiUsed INT)
	DECLARE @voiUsed INT = 0, @dbInstances INT = 0

	IF (OBJECT_ID('CommServ.dbo.Lic_PeakUsage') IS NOT NULL)--if commcell is below SP17, Lic_PeakUsage is in HistoryDB. Hence this check is requried
	BEGIN
		INSERT #totalVOIHistory--OI/VOI implementation is avaiable only from SP17. Hence no need to find these values for pre SP17 CommCells
		SELECT MonthStart, SUM(S.VICount) FROM
		(
			SELECT MonthStart, COUNT(DISTINCT VMId) VICount FROM
			(
				SELECT DISTINCT MonthStart, ObjectId AS VMId FROM CommServ.dbo.Lic_PeakUsage WITH(NOLOCK) WHERE PeakType=1 AND UsageType IN (19, 21) AND (MonthStart BETWEEN @monthstart AND @monthend)
				UNION
				SELECT DISTINCT MonthStart, ClientId AS VMId FROM CommServ.dbo.Lic_PeakUsage WITH(NOLOCK) WHERE PeakType=1 AND (UsageType=31 AND SubclientName IS NULL) AND (MonthStart BETWEEN @monthstart AND @monthend)
			)V GROUP BY MonthStart
			UNION
			SELECT MonthStart, COUNT(SubclientName) VICount FROM CommServ.dbo.Lic_PeakUsage WITH(NOLOCK) WHERE PeakType=1 AND (UsageType=31 AND SubclientName IS NOT NULL) AND (MonthStart BETWEEN @monthstart AND @monthend) GROUP BY MonthStart
		)S GROUP BY MonthStart
	END

	--upload summary of SCL license peak usage from view Lic_SCLPeakSummary_vw
	SET @sqlQuery = N'
		SELECT DISTINCT
		P.MonthStart AS ''MonthStart'',
		UsageType AS ''UsageType'',
		1 AS ''PeakType'', --0 ccl peak, 1 SCL peak
		CASE WHEN UsageType=31 THEN V.voiUsed ELSE AppTypeId END AS ''AppTypeId'',
		LicType AS ''LicType'',
		ISNULL(Usage,0) AS ''Usage'',
		CapacityFlag AS ''CapacityFlag'',
		VMFlag AS ''VMFlag''
		FROM 	
	'	
	IF (OBJECT_ID('CommServ.dbo.Lic_SCLPeakSummary_vw') IS NOT NULL)--from SP17 this view is moved to CSDB
		SET @sqlQuery += N' CommServ.dbo.Lic_SCLPeakSummary_vw'            		
	ELSE IF (OBJECT_ID('HistoryDB.dbo.Lic_SCLPeakSummary_vw') IS NOT NULL)--if CS is below SP17, continue to collect usage using HistoryDB view
        SET @sqlQuery += N' HistoryDB.dbo.Lic_SCLPeakSummary_vw'
	ELSE
		GOTO LIC_ERROR

	SET @sqlQuery += N' P WITH(NOLOCK)
		LEFT JOIN #totalVOIHistory V ON V.MonthStart=P.MonthStart
		WHERE P.MonthStart BETWEEN @monthstart AND @monthend
		UNION ALL
		SELECT MonthStart, -1 ''UsageType'', 1 ''PeakType'', 0 ''AppTypeId'', @voiLicType ''LicType'', voiPurchased ''Usage'', 0 ''CapacityFlag'', 0 ''VMFlag'' FROM #VirtualLicenseHistory
		UNION ALL
		SELECT MonthStart, -1 ''UsageType'', 1 ''PeakType'', 0 ''AppTypeId'', @pvmLicType ''LicType'', pvmPurchased ''Usage'', 0 ''CapacityFlag'', 0 ''VMFlag'' FROM #VirtualLicenseHistory
		UNION ALL
		SELECT MonthStart, -1 ''UsageType'', 1 ''PeakType'', 0 ''AppTypeId'', @acvLicType ''LicType'', acvPurchased ''Usage'', 0 ''CapacityFlag'', 0 ''VMFlag'' FROM #VirtualLicenseHistory
		UNION ALL
		SELECT MonthStart, -1 ''UsageType'', 1 ''PeakType'', 0 ''AppTypeId'', @socketLicType ''LicType'', socketPurchased ''Usage'', 0 ''CapacityFlag'', 0 ''VMFlag'' FROM #VirtualLicenseHistory
	'
	EXEC sp_executesql @sqlQuery, N'@monthstart datetime, @monthend datetime, @voiLicType INT, @pvmLicType INT, @acvLicType INT, @socketLicType INT', @monthstart, @monthend, @voiLicType, @pvmLicType, @acvLicType, @socketLicType

	IF object_id('tempdb.dbo.#VirtualLicenseHistory') IS NOT NULL DROP TABLE #VirtualLicenseHistory
	IF object_id('tempdb.dbo.#VirtualLicenses') IS NOT NULL DROP TABLE #VirtualLicenses
	IF object_id('tempdb.dbo.#totalVOIHistory') IS NOT NULL DROP TABLE #totalVOIHistory
	
	IF OBJECT_ID('GetSurveyParamValue') IS NULL
	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