--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 185
DECLARE @surveyXML  NVARCHAR(MAX)

--------- END - GENERATED CODE ---------


--------- BEGIN SURVEY QUERY ---------
--------- Insert your SQL statements here 

-- cvGlobalParam.cpp
-- const string ENABLE_QUOTA_LIMITS_CLIENT_GROUP = "Enable Quota Limits at Client Group";

-- AppMgrComponentDefines.h
-- #define CV_COMPONENT_TYPE_CLIENTGROUP  8
-- #define CV_COMPONENT_CG_QUOTA_LIMIT_GB 3400

CREATE TABLE #ClientGroupQuotaUsage (
	ClientGroupId	INT, 
	ClientGroupName	NVARCHAR(512), 
	QuotaType		INT, 
	QuotaUsedGB		BIGINT, 
	QuotaLimitGB	BIGINT, 
	UpdateTime		INT,
	QuotaEnabled	INT,
	ExpiryTime		INT
)

DECLARE @frequencyMode   NVARCHAR(MAX) = '0'
DECLARE @isPrivateRunning  VARCHAR(256) = ''
DECLARE @privateMetricsReporting    INT = 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 IN ('Metrics Reporting', 'Metrics Direct Dip')) OR (@frequencyMode = '1') OR (@frequencyMode = '3'))
	SET @privateMetricsReporting = 1

DECLARE @Inherited INT = 0
SELECT  @Inherited = CAST(attrVal AS INT) FROM LicUsageBasedConfig WITH (NOLOCK) WHERE attrName = 'InheritedLicType'

IF OBJECT_ID('RptClientGroupQuotaUsage', 'U') IS NOT NULL AND (@privateMetricsReporting = 1 OR @privateMetricsReporting = 0 AND @Inherited&(1|2) = 1)
BEGIN
	CREATE TABLE #param (retVal INT)

	EXEC sp_executesql N'INSERT INTO #param SELECT 1 FROM GXGlobalParam WHERE name = ''Enable Quota Limits at Client Group'' AND value = ''1'' AND modified = 0'

	DECLARE @QuotaEnabled INT = 0
	IF EXISTS (SELECT * FROM #param WHERE retVal = 1)
	   SET @QuotaEnabled = 1

	DROP TABLE #param

	INSERT INTO #ClientGroupQuotaUsage
	SELECT	CG.id, CG.name, QU.quotaType, QU.quotaUsedGB, CP.longlongVal, CP.created, @QuotaEnabled, 0
	FROM	RptClientGroupQuotaUsage QU WITH(NOLOCK) 
		INNER JOIN APP_ClientGroup CG WITH(NOLOCK) ON QU.clientGroupId = CG.id
		INNER JOIN APP_ComponentProp CP WITH(NOLOCK) ON QU.clientGroupId = CP.componentId
	WHERE	CP.componentType = 8 AND CP.propertyTypeId = 3400 AND CP.longlongVal > 0 AND CP.modified = 0 AND @privateMetricsReporting = 1
	UNION
	SELECT	clientGroupId, 'CommCell Inherited Capacity', quotaType, quotaUsedGB, 
		CASE WHEN @Inherited&4 = 4 AND @Inherited&2 = @privateMetricsReporting*2 THEN globalLimitGB ELSE 0 END, 
		CASE WHEN @Inherited&4 = 4 AND @Inherited&2 = @privateMetricsReporting*2 THEN updateTime ELSE 0 END, 
		@Inherited, 0
	FROM	RptClientGroupQuotaUsage QU WITH(NOLOCK) 
	WHERE	clientGroupId = 0 AND @Inherited&1 = 1

	IF COL_LENGTH('RptClientGroupQuotaUsage','expiryTime') IS NOT NULL
	BEGIN
		EXEC sp_executesql N'UPDATE T SET ExpiryTime = U.ExpiryTime FROM #ClientGroupQuotaUsage T INNER JOIN RptClientGroupQuotaUsage U WITH(NOLOCK) ON T.ClientGroupId = U.clientGroupId AND T.QuotaType = U.quotaType'
	END

	SET @surveyXML = (
	SELECT	ClientGroupId 	AS '@ClientGroupId', 
			ClientGroupName AS '@ClientGroupName', 
			QuotaType 		AS '@QuotaType', 
			QuotaUsedGB 	AS '@QuotaUsedGB', 
			QuotaLimitGB 	AS '@QuotaLimitGB', 
			UpdateTime 		AS '@UpdateTime',
			QuotaEnabled	AS '@QuotaEnabled',
			ISNULL(ExpiryTime,0)	AS '@ExpiryTime'
	FROM	#ClientGroupQuotaUsage QU WITH(NOLOCK) 
	FOR XML PATH ('ClientGroupQuotaUsage')
	-- Replace <<RESULTS_NAME>> with specific name
	)
END

DROP TABLE #ClientGroupQuotaUsage

--------- END SURVEY QUERY ---------


--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)
SET NOCOUNT OFF

--------- END - GENERATED CODE ---------