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

SET NOCOUNT ON
BEGIN TRY
DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 199
DECLARE @surveyXML  NVARCHAR(MAX)

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



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 

DECLARE @nreleaseId INT =1
SET @nreleaseId =(SELECT releaseId from APP_Client WITH (NOLOCK) where id =2)

DECLARE @LastCollectionAttrName VARCHAR(256)
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode	 NVARCHAR(MAX) ='0';
DECLARE @nstring NVARCHAR(MAX)

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
	SELECT @freequencyMode = 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 (@freequencyMode = '1'))
	SET @LastCollectionAttrName = 'CommservSurveyPrivateLastLibraryThroughputCollectionTime'
ELSE IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@freequencyMode = '3'))
	SET @LastCollectionAttrName = 'CommservSurveyDirectDipLastLibraryThroughputCollectionTime'
ELSE
	SET @LastCollectionAttrName = 'CommservSurveyPublicLastLibraryThroughputCollectionTime'

-- Get the last collection time
DECLARE @CurrCollectionDate DATE = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
DECLARE @PrevCollectionDate DATE

IF OBJECT_ID('GetSurveyParamValue ') IS NOT NULL
	BEGIN
		DECLARE @PrevCollectionDateSTR VARCHAR(256) = '';
		EXEC GetSurveyParamValue  @LastCollectionAttrName, @PrevCollectionDateSTR OUTPUT 
		IF @PrevCollectionDateSTR <> ''
			SELECT  @PrevCollectionDate = CAST(@PrevCollectionDateSTR AS DATE)
	END
ELSE
BEGIN
	SELECT  @PrevCollectionDate = CAST(CAST(value AS NVARCHAR(256)) AS DATE) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName
END

IF @PrevCollectionDate IS NULL
	SET @PrevCollectionDate = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-14, 0)

CREATE TABLE #UsedTimeRanges (LibraryId INT, TimeStart DATETIME, TimeEnd DATETIME, OrderNo INT, UsedSec INT, DataReadMB BIGINT, DataWrittenMB BIGINT)
CREATE TABLE #CombinedStarts (LibraryId INT, TimeStart DATETIME, OrderNo INT)
CREATE TABLE #CombinedRanges (LibraryId INT, TimeStart DATETIME, TimeEnd DATETIME)
CREATE TABLE #DailyUsage     (LibraryId INT, DayStart  DATETIME, DaySeconds INT, UsedSeconds INT, DataReadMB BIGINT, DataWrittenMB BIGINT)

DECLARE @DayStart DATETIME = @PrevCollectionDate
DECLARE @DayEnd   DATETIME = DATEADD(DAY, 1, @DayStart)

WHILE @DayStart < @CurrCollectionDate
BEGIN
	INSERT	INTO #UsedTimeRanges
	SELECT	LibraryId, CAST(TimeStart AS SMALLDATETIME), 
			DATEADD(SECOND, 
					MAX(CASE WHEN DataWriteDurationSec > DataReadDurationSec THEN DataWriteDurationSec ELSE DataReadDurationSec END), 
					CAST(TimeStart AS SMALLDATETIME)),
			ROW_NUMBER() OVER (PARTITION BY LibraryId ORDER BY CAST(TimeStart AS SMALLDATETIME)),
			MAX(CASE WHEN DataWriteDurationSec > DataReadDurationSec THEN DataWriteDurationSec ELSE DataReadDurationSec END), 
			SUM(DataRead), SUM(DataWritten)
	FROM	MMDriveHistory DH WITH (NOLOCK)
			INNER JOIN MMDrive D ON DH.DriveId = D.DriveId
			INNER JOIN MMMasterPool MPL ON D.MasterPoolId = MPL.MasterPoolId
	WHERE	HistoryType = 2 AND TimeStart < @DayEnd
		AND DATEADD(SECOND, 
					CASE WHEN DataWriteDurationSec > DataReadDurationSec THEN DataWriteDurationSec ELSE DataReadDurationSec END, 
					CAST(TimeStart AS SMALLDATETIME)) > @DayStart
	GROUP BY LibraryId, CAST(TimeStart AS SMALLDATETIME)

	CREATE CLUSTERED INDEX #UsedTimeRanges_LibraryId_TimeStart_idx ON #UsedTimeRanges (LibraryId, TimeStart)

	UPDATE	#UsedTimeRanges
	SET		TimeStart = @DayStart, 
			DataReadMB = (1.0*DATEDIFF(SECOND, @DayStart, TimeEnd)/UsedSec)*DataReadMB,
			DataWrittenMB = (1.0*DATEDIFF(SECOND, @DayStart, TimeEnd)/UsedSec)*DataWrittenMB
	WHERE	TimeStart < @DayStart

	UPDATE	#UsedTimeRanges
	SET		TimeEnd = @DayEnd, 
			DataReadMB = (1.0*DATEDIFF(SECOND, TimeStart, @DayEnd)/UsedSec)*DataReadMB,
			DataWrittenMB = (1.0*DATEDIFF(SECOND, TimeStart, @DayEnd)/UsedSec)*DataWrittenMB
	WHERE	TimeEnd > @DayEnd

	INSERT INTO #CombinedStarts
	SELECT	B.LibraryId, B.TimeStart,
			ROW_NUMBER() OVER (PARTITION BY B.LibraryId ORDER BY B.TimeStart)
	FROM	#UsedTimeRanges A
			INNER JOIN #UsedTimeRanges B ON A.LibraryId = B.LibraryId AND (A.OrderNo + 1) = B.OrderNo AND A.TimeEnd < B.TimeStart
	UNION
	SELECT	LibraryId, TimeStart, 0
	FROM	#UsedTimeRanges
	WHERE	OrderNo = 1

	CREATE CLUSTERED INDEX #CombinedStarts_LibraryId_OrderNo_idx ON #CombinedStarts (LibraryId, OrderNo)

	INSERT INTO #CombinedRanges
	SELECT	A.LibraryId, A.TimeStart, MAX(C.TimeEnd)
	FROM	#CombinedStarts A
			INNER JOIN #CombinedStarts B ON A.LibraryId = B.LibraryId AND (A.OrderNo + 1) = B.OrderNo 
			INNER JOIN #UsedTimeRanges C ON A.LibraryId = C.LibraryId AND A.TimeStart <= C.TimeStart AND B.TimeStart > C.TimeStart
	GROUP BY A.LibraryId, A.TimeStart
	UNION
	SELECT	A.LibraryId, A.TimeStart, B.TimeEnd
	FROM	(SELECT LibraryId, MAX(TimeStart) AS TimeStart FROM #CombinedStarts GROUP BY LibraryId) A
			INNER JOIN (SELECT LibraryId, MAX(TimeEnd) AS TimeEnd FROM #UsedTimeRanges GROUP BY LibraryId) B 
			ON A.LibraryId = B.LibraryId

	INSERT	INTO #DailyUsage
	SELECT	A.LibraryId, @DayStart, DATEDIFF(SS, @DayStart, @DayEnd), A.UsedSec, B.DataReadMB, B.DataWrittenMB
	FROM	(SELECT	LibraryId, SUM(DATEDIFF(SS, TimeStart, TimeEnd)) AS UsedSec
			FROM	#CombinedRanges
			GROUP BY LibraryId
			) A
			INNER JOIN
			(SELECT LibraryId, SUM(DataReadMB) AS DataReadMB, SUM(DataWrittenMB) AS DataWrittenMB
			FROM	#UsedTimeRanges
			GROUP BY LibraryId
			) B ON A.LibraryId = B.LibraryId

	DROP INDEX #UsedTimeRanges_LibraryId_TimeStart_idx ON #UsedTimeRanges
	DROP INDEX #CombinedStarts_LibraryId_OrderNo_idx ON #CombinedStarts

	TRUNCATE TABLE #UsedTimeRanges
	TRUNCATE TABLE #CombinedStarts
	TRUNCATE TABLE #CombinedRanges

	SET @DayStart = @DayEnd
	SET @DayEnd = DATEADD(DAY, 1, @DayStart)
END

SET @surveyXML =(
	SELECT	LibraryId AS '@LibraryId', 
		DayStart AS '@DayStart', 
		UsedSeconds AS '@UsedSeconds', 
		DataReadMB AS '@DataReadMB', 
		DataWrittenMB AS '@DataWrittenMB'
	FROM	#DailyUsage
	ORDER BY DayStart, LibraryId

	FOR XML PATH ('LibraryThroughput')
)

DROP TABLE #UsedTimeRanges
DROP TABLE #CombinedStarts
DROP TABLE #CombinedRanges
DROP TABLE #DailyUsage




--------- 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')
)
	 
END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH

--Update the last collection time
IF OBJECT_ID('SetSurveyParamValue ') IS NOT NULL
BEGIN
	EXEC SetSurveyParamValue  @LastCollectionAttrName, @CurrCollectionDate
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
		UPDATE GXGlobalParam SET value = CAST(@CurrCollectionDate as NVARCHAR(MAX)) where name = @LastCollectionAttrName
	ELSE
	BEGIN
		IF @nreleaseId >= 15
		BEGIN
			SET @nstring = N'insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CAST(@CurrCollectionDate as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @nstring
		END
		ELSE
		BEGIN
			insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CAST(@CurrCollectionDate as NVARCHAR(MAX)))
		END
	END
END
SET NOCOUNT OFF

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