--@querytype    CSV
--Name:- CCL License Peak Summary
--Description:- Collects summary of commcell 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 = 234

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery234_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) = '';
	DECLARE @tempArgument NVARCHAR(1024)

	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_LicCCLSummary_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDip_LicCCLSummary_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_LicCCLSummary_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 summary of CCL license peak usage from view Lic_CCLPeakSummary_vw
	SET @sqlQuery = N'
		SELECT DISTINCT
		MonthStart AS ''MonthStart'',
		UsageType AS ''UsageType'',
		0 AS ''PeakType'', --0 ccl peak, 1 SCL peak
		AppTypeId AS ''AppTypeId'',
		LicType AS ''LicType'',
		ISNULL(Usage,0) AS ''Usage'',
		CapacityFlag AS ''CapacityFlag'',
		VMFlag AS ''VMFlag''
		FROM 	
	'	
	IF (OBJECT_ID('CommServ.dbo.Lic_CCLPeakSummary_vw') IS NOT NULL)--from SP17 this view is moved to CSDB
		SET @sqlQuery += N' CommServ.dbo.Lic_CCLPeakSummary_vw'            		
	ELSE IF (OBJECT_ID('HistoryDB.dbo.Lic_CCLPeakSummary_vw') IS NOT NULL)--if CS is below SP17, continue to collect usage using HistoryDB view
        SET @sqlQuery += N' HistoryDB.dbo.Lic_CCLPeakSummary_vw'
	ELSE
		GOTO LIC_ERROR

	SET @sqlQuery += N' WITH(NOLOCK)
		WHERE MonthStart BETWEEN @monthstart AND @monthend
	'
	EXEC sp_executesql @sqlQuery, N'@monthstart datetime, @monthend datetime', @monthstart, @monthend	

	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
	set @tempArgument = CAST(dbo.getunixtime(GETDATE()) AS NVARCHAR(20))
	EXEC SetSurveyParamValue @TIME_STAMP_NAME, @tempArgument
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