--@querytype    CSV
--Name:- License Info
--Description:- Collects details of licenses purchased and used 

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

	DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery232_LastCollectionTime'
	DECLARE @isPrivateRunning VARCHAR(256)='';
	DECLARE @frequencyMode   NVARCHAR(MAX) ='0';
	DECLARE @uploadDetails INT = 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 OBJECT_ID('CloudMonitoringService') IS NOT NULL
	BEGIN
		DECLARE @release INT = 0, @sp INT = 0, @serviceToCheck VARCHAR(15) = ''
        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 
		--collect details through activity till v11SP19. For v11SP20 and above, even if activity is enabled, the details collection should be controlled only by spbilling checkbox 
		IF @release >= 16 AND @sp >= 20
			SET @serviceToCheck='SPBilling'
		ELSE
			SET @serviceToCheck='Activity'

		IF (@isPrivateRunning = 'Metrics Reporting')
			SELECT @uploadDetails = (isEnabled & 2) FROM CloudMonitoringService WITH (NOLOCK) WHERE name = @serviceToCheck
		ELSE			
			SELECT @uploadDetails = (isEnabled & 1) FROM CloudMonitoringService WITH (NOLOCK) WHERE name = @serviceToCheck
	END

	IF ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode = '1'))
		SET @TIME_STAMP_NAME = 'CommservSurveyPrivate_LicLicenseInfo_CollectionTime'
	else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
		SET @TIME_STAMP_NAME = 'CommservSurveyDirectDip_LicLicenseInfo_CollectionTime'
	ELSE
		SET @TIME_STAMP_NAME = 'CommservSurveyPublic_LicLicenseInfo_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 @uploadDetails=0
	BEGIN
		--upload summary of LicenseInfo from view Lic_LicenseInfoSummary_vw 
		SET @sqlQuery = N'
			SELECT DISTINCT 
			LicType AS ''LicType'', 
			ISNULL(PermTotal,0) AS ''PermTotal'', 
			ISNULL(PermUsed,0) AS ''PermUsed'', 
			ISNULL(EvalTotal,0) AS ''EvalTotal'', 
			ISNULL(EvalUsed,0) AS ''EvalUsed'', 
			ISNULL(TermDate,CONVERT(DATETIME, ''1970-01-01 00:00:00.000'')) AS ''TermDate'', 
			-1 AS ''ClientId'',
			-1 AS ''AppTypeId'', 
			CONVERT(DATETIME, ''1970-01-01 00:00:00.000'') AS ''InstallDate'', 
			ISNULL(ExpiryDate,CONVERT(DATETIME, ''1970-01-01 00:00:00.000'')) AS ''ExpiryDate''
            FROM 
		'
		IF (OBJECT_ID('CommServ.dbo.Lic_LicenseInfoSummary_vw') IS NOT NULL)--from SP17 this view is moved to CSDB
			SET @sqlQuery += N' CommServ.dbo.Lic_LicenseInfoSummary_vw'            		
		ELSE IF (OBJECT_ID('HistoryDB.dbo.Lic_LicenseInfoSummary_vw') IS NOT NULL)--if CS is below SP17, continue to collect usage using HistoryDB view
            SET @sqlQuery += N' HistoryDB.dbo.Lic_LicenseInfoSummary_vw'	
		ELSE
			GOTO LIC_ERROR
	END
	ELSE
	BEGIN
		--upload details of LicenseInfo from Lic_LicenseInfo table
		SET @sqlQuery = N'
			SELECT DISTINCT 
			LicType AS ''LicType'', 
			ISNULL(PermTotal,0) AS ''PermTotal'', 
			ISNULL(PermUsed,0) AS ''PermUsed'', 
			ISNULL(EvalTotal,0) AS ''EvalTotal'', 
			ISNULL(EvalUsed,0) AS ''EvalUsed'', 
			ISNULL(TermDate,CONVERT(DATETIME, ''1970-01-01 00:00:00.000'')) AS ''TermDate'', 
			ISNULL(ClientId,0) AS ''ClientId'',  
			AppTypeId AS ''AppTypeId'', 
			ISNULL(InstallDate,CONVERT(DATETIME, ''1970-01-01 00:00:00.000'')) AS ''InstallDate'', 
			ISNULL(ExpiryDate,CONVERT(DATETIME, ''1970-01-01 00:00:00.000'')) AS ''ExpiryDate''
            FROM
		'
		IF (OBJECT_ID('CommServ.dbo.Lic_LicenseInfo') IS NOT NULL)--from SP17 this table is moved to CSDB
			SET @sqlQuery += N' CommServ.dbo.Lic_LicenseInfo'            		
		ELSE IF (OBJECT_ID('HistoryDB.dbo.Lic_LicenseInfo') IS NOT NULL)--if CS is below SP17, continue to collect usage from HistoryDB
            SET @sqlQuery += N' HistoryDB.dbo.Lic_LicenseInfo'	
		ELSE
			GOTO LIC_ERROR		
	END   
	
	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