--@querytype    CSV
--Name:- Current License Usages
--Description:- Collects details 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 = 236

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery236_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_LicCurrentUsage_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIP_LicCurrentUsage_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_LicCurrentUsage_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 (@frequencyMode = '1') or (@isPrivateRunning = 'Metrics Direct Dip') 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

	--upload details of current license usage from Lic_CurrentUsage table
	IF (OBJECT_ID('CommServ.dbo.Lic_CurrentUsage') IS NOT NULL)--from SP17 this table is moved to CSDB
	BEGIN
		SET @sqlQuery = N'
			SELECT
			UsageType AS ''UsageType'',
			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_CurrentUsage WITH(NOLOCK)
		'
	END
	ELSE IF (OBJECT_ID('HistoryDB.dbo.Lic_CurrentUsage') IS NOT NULL)--if CS is below SP17, continue to collect usage from HistoryDB
	BEGIN
		IF COL_LENGTH('HistoryDB.dbo.Lic_CurrentUsage', '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
				UsageType AS ''UsageType'',
				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_CurrentUsage WITH(NOLOCK)
			'    
		END
		ELSE
		BEGIN
			SET @sqlQuery = N'
				SELECT
				UsageType AS ''UsageType'',
				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_CurrentUsage WITH(NOLOCK)
			'
		END
	END
	ELSE
		GOTO LIC_ERROR

	EXEC sp_executesql @sqlQuery

	IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
	BEGIN
		DECLARE @lastcollectionTimeSTR VARCHAR(20) = ''
		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 @tempArgument NVARCHAR(20) = ( SELECT 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