--@querytype    CSV
--Name:- Peak Usage Details
--Description:- Collects peak usage of commserv along with VM and VM with apps count

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRY

	    
DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 216

DECLARE @TIME_STAMP_NAME NVARCHAR(512) = 'SQLQuery216_LastCollectionTime'
DECLARE @monthstart DATETIME = DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)
DECLARE @monthend DATETIME = @monthstart
DECLARE @lastcollectionTime INT = 0
DECLARE @ParmDefinition nvarchar(500); 
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode   NVARCHAR(MAX) ='0';

IF EXISTS(SELECT * 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 = 'CommservSurveyPrivatePeakUsageDetailsCollectionTime'
else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@frequencyMode = '3'))
    SET @TIME_STAMP_NAME = 'CommservSurveyDirectDipPeakUsageDetailsCollectionTime'
ELSE
    SET @TIME_STAMP_NAME = 'CommservSurveyPublicPeakUsageDetailsCollectionTime'

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
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)


IF object_id('tempdb.dbo.#PeakUsageDetails') IS NOT null DROP TABLE #PeakUsageDetails
	CREATE TABLE #PeakUsageDetails (MonthStart DATETIME, PeakDate DATETIME, nJobType INT, JobId INT, SPId INT, ClientId INT, AppTypeId INT, AppId INT, AppSizeMB BIGINT, LicType INT, IsVM INT, CapacityFlag INT, Organization NVARCHAR(1024), OrgGUID VARCHAR(40))

DECLARE @sqlstr nvarchar(max)
	
IF (OBJECT_ID('LicPeakUsageDetails') IS NOT NULL)
BEGIN
	IF EXISTS(SELECT 1 FROM sys.columns 
			  WHERE Name = N'LicType'
			  AND Object_ID = Object_ID(N'dbo.LicPeakUsageDetails'))
	BEGIN
		IF EXISTS(SELECT 1 FROM sys.columns 
			  WHERE Name = N'OrgGUID'
			  AND Object_ID = Object_ID(N'dbo.LicPeakUsageDetails'))
		BEGIN
			SET @sqlstr = N'
				INSERT INTO #PeakUsageDetails (
				MonthStart, PeakDate, nJobType, JobId, SPId, ClientId, AppTypeId, AppId, AppSizeMB, LicType, IsVM , CapacityFlag, Organization, OrgGUID)
				SELECT	MonthStart, PeakDate, nJobType, JobId, SPId, ClientId, AppTypeId, AppId, AppSizeMB, LicType, IsVM , CapacityFlag, Organization, OrgGUID
				FROM	LicPeakUsageDetails WHERE MonthStart BETWEEN @startdate AND @enddate'
		END
		ELSE
		BEGIN
			SET @sqlstr = N'
				INSERT INTO #PeakUsageDetails (
				MonthStart, PeakDate, nJobType, JobId, SPId, ClientId, AppTypeId, AppId, AppSizeMB, LicType, IsVM , CapacityFlag, Organization, OrgGUID)
				SELECT	MonthStart, PeakDate, nJobType, JobId, SPId, ClientId, AppTypeId, AppId, AppSizeMB, LicType, IsVM , CapacityFlag, Organization, NULL
				FROM	LicPeakUsageDetails WHERE MonthStart BETWEEN @startdate AND @enddate'
		END
	END
	ELSE
	BEGIN
		SET @sqlstr = N'
		INSERT INTO #PeakUsageDetails (
				MonthStart, PeakDate, nJobType, JobId, SPId, ClientId, AppTypeId, AppId, AppSizeMB, LicType, IsVM , CapacityFlag, Organization, OrgGUID)
			SELECT	MonthStart, PeakDate, nJobType, JobId, SPId, ClientId, AppTypeId, AppId, AppSizeMB, 0,0,0, NULL, NULL
			FROM	LicPeakUsageDetails WHERE MonthStart BETWEEN @startdate AND @enddate'
	END

	SET @ParmDefinition = N'@startdate DATETIME,@enddate DATETIME'
	EXEC sp_executesql @sqlstr,@ParmDefinition,  
                      @startdate = @monthstart,
					  @enddate = @monthend
END

IF object_id('tempdb.dbo.#PeakClientCountDetails') IS NOT null DROP TABLE #PeakClientCountDetails
CREATE TABLE #PeakClientCountDetails (
	MonthStart DATETIME, PeakDate DATETIME, ClientId INT, VMClientId INT, AppTypeId	INT, LicType INT, JobId INT, Organization NVARCHAR(1024), OrgGUID VARCHAR(40))

IF (OBJECT_ID('LicPeakCLALicClientCount') IS NOT NULL)
BEGIN
	IF EXISTS(SELECT 1 FROM sys.columns 
			  WHERE Name = N'OrgGUID'
			  AND Object_ID = Object_ID(N'dbo.LicPeakCLALicClientCount'))
	BEGIN
	SET @sqlstr = N'
		INSERT INTO #PeakClientCountDetails
		SELECT	MonthStart,	PeakDate, ClientId, VMClientId, AppTypeId, LicType, JobId, Organization, OrgGUID FROM LicPeakCLALicClientCount WHERE MonthStart BETWEEN @startdate AND @enddate
	'
	END
	ELSE
	BEGIN
		SET @sqlstr = N'
		INSERT INTO #PeakClientCountDetails
		SELECT	MonthStart,	PeakDate, ClientId, VMClientId, AppTypeId, LicType, JobId, Organization, NULL FROM LicPeakCLALicClientCount WHERE MonthStart BETWEEN @startdate AND @enddate
	'
	END
	SET @ParmDefinition = N'@startdate DATETIME,@enddate DATETIME'
	EXEC sp_executesql @sqlstr,@ParmDefinition,  
                      @startdate = @monthstart,
					  @enddate = @monthend
END

Update #PeakUsageDetails
Set Organization = Replace (Organization,',','<COMMA>')
Where Organization is not NULL and CHARINDEX(',', Organization)>0

Update #PeakClientCountDetails
Set Organization = Replace (Organization,',','<COMMA>')
Where Organization is not NULL and CHARINDEX(',', Organization)>0

SELECT 
	MonthStart AS 'MonthStart',
	PeakDate AS 'PeakDate',
	nJobType AS 'nJobType',
	JobId AS 'JobId',
	SPId AS 'SPId',
	ClientId AS 'ClientId',   
	AppTypeId AS 'AppTypeId',
	AppId AS 'AppId',
	AppSizeMB AS 'AppSizeMB', 
	LicType AS 'LicType',
	IsVM AS 'IsVM',
	CapacityFlag AS 'CapacityFlag',
	Organization AS 'Organization',
	OrgGUID AS 'OrgGUID',
	-1 AS 'VMClientId'
	FROM #PeakUsageDetails
UNION
SELECT	
	MonthStart AS 'MonthStart',
	PeakDate AS 'PeakDate',
	-1 AS 'nJobType',
	JobId AS 'JobId',
	-1 AS 'SPId',
	ClientId AS 'ClientId',
	AppTypeId AS 'AppTypeId',
	-1 AS 'AppId',
	-1 AS 'AppSizeMB', 
	LicType AS 'LicType',
	-1 AS 'IsVM',
	-1 AS 'CapacityFlag',
	Organization AS 'Organization',
	OrgGUID AS 'OrgGUID',
	VMClientId AS 'VMClientId'
	FROM	#PeakClientCountDetails 			

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	SET @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 * FROM GXGlobalParam WHERE name = @TIME_STAMP_NAME)
	BEGIN
		IF EXISTS (SELECT * 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
	
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#PeakUsageDetails'))
    DROP TABLE #PeakUsageDetails
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#PeakClientCountDetails'))
    DROP TABLE #PeakClientCountDetails	


	 
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

SET NOCOUNT OFF