--@querytype    CSV
--Name:- SCL License Peak Usages
--Description:- Collects details 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 = 238

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery238_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_LicSCLUsage_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIP_LicSCLUsage_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_LicSCLUsage_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

	--On private metrics servers - collect this data through activity till v11SP19. For v11SP20 and above, even if activity is checked, the collection should be controlled only by spbilling checkbox	 
	IF ((@isPrivateRunning = 'Metrics Reporting') or (@isPrivateRunning = 'Metrics Direct Dip') OR  (@frequencyMode ='1') OR  (@frequencyMode ='3'))
	BEGIN
		DECLARE @release INT = 0, @sp INT = 0, @spBillingEnabled INT = 0
		SELECT @release = MAX(id) FROM simAllGalaxyRel
		SELECT @sp = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2 
		SELECT @spBillingEnabled = isEnabled&2 from CloudMonitoringService WHERE id = 9
		IF @release >= 16 AND @sp >= 20--check if CS is on v11 sp20 or above
		BEGIN
			IF @spBillingEnabled <> 2--check if spbilling is enabled
				GOTO LIC_ERROR--do not collect anything if spbilling checkbox is disabled
		END
	END

	IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
	BEGIN
		DECLARE @lastcollectionTimeSTR VARCHAR(20) = '';
		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 details of SCL license peak usage from Lic_PeakUsage table
	IF (OBJECT_ID('CommServ.dbo.Lic_PeakUsage') IS NOT NULL)--from SP17 this table is moved to CSDB
	BEGIN
		SET @sqlQuery = N'
			SELECT
			MonthStart AS ''MonthStart'',
			CreationDate AS ''CreationDate'',
			UsageType AS ''UsageType'',
			1 AS ''PeakType'', --0 ccl peak, 1 SCL peak             
			CASE WHEN UsageType IN (33,41) THEN ISNULL(SPId,0) ELSE ISNULL(ClientId,0) END AS ''ClientId'',
			CASE WHEN UsageType IN (26) THEN 
				CASE WHEN ClientName IS NOT NULL AND CHARINDEX('','', ClientName) > 0 THEN REPLACE (ClientName,'','',''<COMMA>'') 
					WHEN ClientName IS NULL OR ClientName='''' THEN ''N/A''
					ELSE ClientName 
				END				
				WHEN UsageType IN (30,31) AND SubclientName IS NOT NULL THEN 
				CASE WHEN CHARINDEX('','', SubclientName) > 0 THEN REPLACE (SubclientName,'','',''<COMMA>'') 
					ELSE SubclientName 
				END				
				ELSE ''N/A''
			END AS ''ClientName'',     
			CASE WHEN UsageType IN (30,31) AND SubclientName IS NOT NULL THEN 1 WHEN UsageType IN (45) THEN ISNULL(BackupSetId,0) WHEN UsageType IN (18) THEN -1 ELSE ISNULL(SubclientId,0) END AS ''SubclientId'',
			AppTypeId AS ''AppTypeId'',     
			LicType AS ''LicType'',
			ISNULL(JobId,0) AS ''JobId'',
			ISNULL(AppSizeMB,0) AS ''AppSizeMB'',
			CapacityFlag AS ''CapacityFlag'',
			CASE WHEN UsageType IN (33) THEN 
				CASE WHEN ObjectName=''HyperScale Appliance'' THEN 1
					WHEN ObjectName=''HyperScale Reference Architecture'' THEN 2
					ELSE 0
				END
				ELSE VMFlag 
			END AS ''VMFlag'',
			CASE WHEN UsageType IN (11,12,13,14,15,18,20,25,26,30,31) THEN 
				CASE WHEN ObjectName IS NOT NULL AND CHARINDEX('','', ObjectName) > 0 THEN REPLACE (ObjectName,'','',''<COMMA>'') 
					WHEN ObjectName IS NULL OR ObjectName='''' THEN ''N/A''
					ELSE ObjectName 
				END
				WHEN UsageType IN (33) THEN CONVERT(NVARCHAR(1024),ISNULL(BackupSetId,-1))
				ELSE ''N/A''
			END AS ''ObjectName'',
			ISNULL(ObjectId,0) AS ''ObjectId'', 
			OrgGUID AS ''OrgGUID'',
			CASE WHEN PlanName IS NOT NULL AND CHARINDEX('','', PlanName) > 0 THEN REPLACE (PlanName,'','',''<COMMA>'') 
					WHEN PlanName IS NULL OR PlanName='''' THEN ''N/A''
					ELSE PlanName 
			END AS ''PlanName'',
			CASE WHEN BasePlanName IS NOT NULL AND CHARINDEX('','', BasePlanName) > 0 THEN REPLACE (BasePlanName,'','',''<COMMA>'') 
					WHEN BasePlanName IS NULL OR BasePlanName='''' THEN ''N/A''
					ELSE BasePlanName 
			END AS ''BasePlanName''
			FROM CommServ.dbo.Lic_PeakUsage WITH(NOLOCK)	
			WHERE MonthStart BETWEEN @monthstart AND @monthend	
			AND PeakType=1
		'	
	END
	ELSE IF (OBJECT_ID('HistoryDB.dbo.Lic_PeakUsage') IS NOT NULL)--if CS is below SP17, continue to collect usage from HistoryDB
	BEGIN
		IF COL_LENGTH('HistoryDB.dbo.Lic_PeakUsage', 'BasePlanName') IS NOT NULL--if CS is on SP15, baseplan column doesn't exist in HistoryDB. Return BasePlanName=N/A in such cases
		BEGIN
			SET @sqlQuery = N'
				SELECT
				MonthStart AS ''MonthStart'',
				CreationDate AS ''CreationDate'',
				UsageType AS ''UsageType'',
				1 AS ''PeakType'', --0 ccl peak, 1 SCL peak             
				CASE WHEN UsageType IN (33,41) THEN ISNULL(SPId,0) ELSE ISNULL(ClientId,0) END AS ''ClientId'',
				CASE WHEN UsageType IN (26) THEN 
					CASE WHEN ClientName IS NOT NULL AND CHARINDEX('','', ClientName) > 0 THEN REPLACE (ClientName,'','',''<COMMA>'') 
						WHEN ClientName IS NULL OR ClientName='''' THEN ''N/A''
						ELSE ClientName 
					END				
					WHEN UsageType IN (30,31) AND SubclientName IS NOT NULL THEN 
					CASE WHEN CHARINDEX('','', SubclientName) > 0 THEN REPLACE (SubclientName,'','',''<COMMA>'') 
						ELSE SubclientName 
					END				
					ELSE ''N/A''
				END AS ''ClientName'',     
				CASE WHEN UsageType IN (30,31) AND SubclientName IS NOT NULL THEN 1 WHEN UsageType IN (45) THEN ISNULL(BackupSetId,0) WHEN UsageType IN (18) THEN -1 ELSE ISNULL(SubclientId,0) END AS ''SubclientId'',
				AppTypeId AS ''AppTypeId'',     
				LicType AS ''LicType'',
				ISNULL(JobId,0) AS ''JobId'',
				ISNULL(AppSizeMB,0) AS ''AppSizeMB'',
				CapacityFlag AS ''CapacityFlag'',
				CASE WHEN UsageType IN (33) THEN 
					CASE WHEN ObjectName=''HyperScale Appliance'' THEN 1
						WHEN ObjectName=''HyperScale Reference Architecture'' THEN 2
						ELSE 0
					END
					ELSE VMFlag 
				END AS ''VMFlag'',
				CASE WHEN UsageType IN (11,12,13,14,15,18,20,25,26,30,31) THEN 
					CASE WHEN ObjectName IS NOT NULL AND CHARINDEX('','', ObjectName) > 0 THEN REPLACE (ObjectName,'','',''<COMMA>'') 
						WHEN ObjectName IS NULL OR ObjectName='''' THEN ''N/A''
						ELSE ObjectName 
					END
					WHEN UsageType IN (33) THEN CONVERT(NVARCHAR(1024),ISNULL(BackupSetId,-1))
					ELSE ''N/A''
				END AS ''ObjectName'',
				ISNULL(ObjectId,0) AS ''ObjectId'', 
				OrgGUID AS ''OrgGUID'',
				CASE WHEN PlanName IS NOT NULL AND CHARINDEX('','', PlanName) > 0 THEN REPLACE (PlanName,'','',''<COMMA>'') 
						WHEN PlanName IS NULL OR PlanName='''' THEN ''N/A''
						ELSE PlanName 
				END AS ''PlanName'',
				CASE WHEN BasePlanName IS NOT NULL AND CHARINDEX('','', BasePlanName) > 0 THEN REPLACE (BasePlanName,'','',''<COMMA>'') 
						WHEN BasePlanName IS NULL OR BasePlanName='''' THEN ''N/A''
						ELSE BasePlanName 
				END AS ''BasePlanName''
				FROM HistoryDB.dbo.Lic_PeakUsage WITH(NOLOCK)	
				WHERE MonthStart BETWEEN @monthstart AND @monthend	
				AND PeakType=1
			'
		END
		ELSE
		BEGIN
			SET @sqlQuery = N'
				SELECT
				MonthStart AS ''MonthStart'',
				CreationDate AS ''CreationDate'',
				UsageType AS ''UsageType'',
				1 AS ''PeakType'', --0 ccl peak, 1 SCL peak             
				CASE WHEN UsageType IN (33,41) THEN ISNULL(SPId,0) ELSE ISNULL(ClientId,0) END AS ''ClientId'',
				CASE WHEN UsageType IN (26) THEN 
					CASE WHEN ClientName IS NOT NULL AND CHARINDEX('','', ClientName) > 0 THEN REPLACE (ClientName,'','',''<COMMA>'') 
						WHEN ClientName IS NULL OR ClientName='''' THEN ''N/A''
						ELSE ClientName 
					END				
					WHEN UsageType IN (30,31) AND SubclientName IS NOT NULL THEN 
					CASE WHEN CHARINDEX('','', SubclientName) > 0 THEN REPLACE (SubclientName,'','',''<COMMA>'') 
						ELSE SubclientName 
					END				
					ELSE ''N/A''
				END AS ''ClientName'',     
				CASE WHEN UsageType IN (30,31) AND SubclientName IS NOT NULL THEN 1 WHEN UsageType IN (45) THEN ISNULL(BackupSetId,0) WHEN UsageType IN (18) THEN -1 ELSE ISNULL(SubclientId,0) END AS ''SubclientId'',
				AppTypeId AS ''AppTypeId'',     
				LicType AS ''LicType'',
				ISNULL(JobId,0) AS ''JobId'',
				ISNULL(AppSizeMB,0) AS ''AppSizeMB'',
				CapacityFlag AS ''CapacityFlag'',
				CASE WHEN UsageType IN (33) THEN 
					CASE WHEN ObjectName=''HyperScale Appliance'' THEN 1
						WHEN ObjectName=''HyperScale Reference Architecture'' THEN 2
						ELSE 0
					END
					ELSE VMFlag 
				END AS ''VMFlag'',
				CASE WHEN UsageType IN (11,12,13,14,15,18,20,25,26,30,31) THEN 
					CASE WHEN ObjectName IS NOT NULL AND CHARINDEX('','', ObjectName) > 0 THEN REPLACE (ObjectName,'','',''<COMMA>'') 
						WHEN ObjectName IS NULL OR ObjectName='''' THEN ''N/A''
						ELSE ObjectName 
					END
					WHEN UsageType IN (33) THEN CONVERT(NVARCHAR(1024),ISNULL(BackupSetId,-1))
					ELSE ''N/A''
				END AS ''ObjectName'',
				ISNULL(ObjectId,0) AS ''ObjectId'', 
				OrgGUID AS ''OrgGUID'',
				CASE WHEN PlanName IS NOT NULL AND CHARINDEX('','', PlanName) > 0 THEN REPLACE (PlanName,'','',''<COMMA>'') 
						WHEN PlanName IS NULL OR PlanName='''' THEN ''N/A''
						ELSE PlanName 
				END AS ''PlanName'',
				''N/A'' AS ''BasePlanName''
				FROM HistoryDB.dbo.Lic_PeakUsage WITH(NOLOCK)	
				WHERE MonthStart BETWEEN @monthstart AND @monthend	
				AND PeakType=1
			'
		END
	END
	ELSE
		GOTO LIC_ERROR

	EXEC sp_executesql @sqlQuery, N'@monthstart datetime, @monthend datetime', @monthstart, @monthend
	
	IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
	BEGIN
		SELECT @lastcollectionTimeSTR = ''
		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(20)
	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