--Name:- Tape Media Usage
--collects last 12 month's data

--Description:- Tapes used and recycled per media type in last 12 months

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON

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

DECLARE	@TimeZoneBias	INT = DATEDIFF(SECOND, GETUTCDATE(), GETDATE())

DECLARE	@i			INT = 0
DECLARE @dtEnd		DATETIME
DECLARE	@dtBegin	DATETIME
DECLARE	@FromTime	DATETIME

IF object_id('tempdb.dbo.#MonthlyTbl') is not null DROP TABLE #MonthlyTbl

CREATE TABLE #MonthlyTbl (monthVal date, MonthBeginDT DATETIME, MonthEndDT DATETIME, MonthBegin INT, MonthEnd INT)

SET @dtBegin	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
SET @dtEnd	= DATEADD(MONTH, 1, @dtBegin)
SET @i = 0

WHILE @i < 12
BEGIN
	INSERT	INTO #MonthlyTbl
	SELECT	@dtBegin, @dtBegin, @dtEnd, 0, 0
		
	SET @dtEnd	 = @dtBegin
	SET @dtBegin = DATEADD(MONTH, -1, @dtEnd)
	SET @i = (@i + 1)
END
UPDATE	#MonthlyTbl SET MonthBegin = DATEDIFF(SS, '1970-01-01', MonthBeginDT)-@TimeZoneBias, MonthEnd = DATEDIFF(SS, '1970-01-01', MonthEndDT)-@TimeZoneBias

IF object_id('tempdb.dbo.#TapeMediaUsage') is not null DROP TABLE #TapeMediaUsage
CREATE TABLE #TapeMediaUsage(StartDate DATE, MediaTypeId INT, MediaTypeName varchar(32), TapesUsed INT, TapesRecycled INT)

IF object_id('tempdb.dbo.#MediaFirstUsage') is not null DROP TABLE #MediaFirstUsage
CREATE TABLE #MediaFirstUsage (MediaId INT, FirstChunkId BIGINT, FirstUsedTime INT)

INSERT INTO #MediaFirstUsage
SELECT	V.MediaId, MIN(AC.id), 0
FROM	ArchChunk AC WITH (NOLOCK)
		INNER JOIN MMVolume V WITH (NOLOCK) ON AC.volumeId = V.VolumeId 
WHERE	V.RecordingFormatId <> 10001
GROUP BY V.MediaId

UPDATE M SET FirstUsedTime = AC.createTime
FROM	#MediaFirstUsage M
		INNER JOIN ArchChunk AC WITH (NOLOCK) ON M.FirstChunkId = AC.id

INSERT INTO #TapeMediaUsage
SELECT 
	M.monthVal,
	ISNULL(MT.MediaTypeId, 0),
	ISNULL(MT.MediaTypeName, ''),
	COUNT(MM.BarCode),
	0
FROM MMMedia MM WITH (NOLOCK) 
	INNER JOIN MMMediaType MT WITH (NOLOCK)
		ON MM.MediaTypeId = MT.MediaTypeId
		AND MM.MediaId <> 0 
		AND MM.NumberOfReUses = 1
		AND MT.MediaTypeId BETWEEN 1 AND 9000 
		AND MT.IsCleaningMedia = 0
	INNER JOIN #MediaFirstUsage MFU
		ON MFU.MediaId = MM.MediaId
	INNER JOIN #MonthlyTbl M
		ON MFU.FirstUsedTime >= M.MonthBegin AND MFU.FirstUsedTime <= M.MonthEnd
WHERE MM.IsInMediaGroup = 1 AND MM.IsAged = 0
GROUP BY M.monthVal, MT.MediaTypeId, MT.MediaTypeName
ORDER BY M.monthVal

IF object_id('tempdb.dbo.#MediaFirstUsage') is not null DROP TABLE #MediaFirstUsage

UPDATE #TapeMediaUsage 
SET TapesRecycled = T1.TapeCount
FROM 
#TapeMediaUsage T
INNER JOIN
(SELECT		
	M.monthVal,
	MT.MediaTypeId,
	COUNT(MM.BarCode) AS TapeCount
FROM MMMedia MM WITH (NOLOCK) 
	INNER JOIN MMMediaType MT WITH (NOLOCK)
		ON MM.MediaTypeId = MT.MediaTypeId
		AND	MM.MediaId <> 0
		AND MT.MediaTypeId BETWEEN 1 AND 9000 
		AND MT.IsCleaningMedia = 0
	INNER JOIN MMVolume MV WITH (NOLOCK)
		ON MV.MediaId = MM.MediaId
	INNER JOIN #MonthlyTbl M
		ON MV.ReformatTime >= M.MonthBegin AND MV.ReformatTime <= M.MonthEnd AND MM.IsInMediaGroup = 0 AND MV.VolumeFlags = 5 AND MM.IsAged = 1
GROUP BY M.monthVal, MT.MediaTypeId) AS T1
ON T.MediaTypeId = T1.MediaTypeId AND T.StartDate = T1.monthVal

IF object_id('tempdb.dbo.#MonthlyTapeUsage') is not null DROP TABLE #MonthlyTapeUsage
CREATE TABLE #MonthlyTapeUsage (LibraryId INT, MonthStart DATE, UsedTapeCount INT, SpareTapeCount INT)

SELECT	@dtBegin = MIN(MonthBeginDT) FROM #MonthlyTbl

INSERT	INTO #MonthlyTapeUsage
SELECT	T.LibraryId, M.monthVal, NumOfUsedMedia, NumOfSpareMedia
FROM	(
		SELECT	H.LibraryId, H.Modified,
				ROW_NUMBER() OVER (PARTITION BY H.LibraryId, YEAR(H.Modified), MONTH(H.Modified) ORDER BY H.Modified DESC) AS RowNo,
				(NumOfActiveMedia + NumOfFullMedia + NumOfAppendableMedia + NumOfIdleMedia + NumOfReadOnlyMedia 
				  + NumOfBadMedia + NumOfExpiredMedia + NumOfForeignMedia + NumOfSavedMedia) AS NumOfUsedMedia,
				NumOfSpareMedia
		FROM	MMLibraryStatsHistory H INNER JOIN MMLibrary L ON H.LibraryId = L.LibraryId
		WHERE	H.Modified >= @dtBegin
		) T
		INNER JOIN #MonthlyTbl M ON DATEDIFF(MM, T.Modified, M.MonthBeginDT) = 0
WHERE	T.RowNo = 1
ORDER BY LibraryId, Modified DESC

IF object_id('tempdb.dbo.#LibrarySpace') is not null DROP TABLE #LibrarySpace
CREATE TABLE #LibrarySpace (LibraryId INT, TotalSpaceMB BIGINT, UsedSpaceMB BIGINT)

-- Used tapes (Active, Full, ReadOnly, Idle, Active Append)
INSERT	INTO #LibrarySpace
SELECT	M.LibraryId, SUM(MS.TotalSpaceMB), SUM(CAST(MS.PhysicalBytesMB AS BIGINT))
FROM 	MMMedia M
		INNER JOIN MMVolume V ON M.MediaId = V.MediaId
		INNER JOIN MMMediaSide MS ON M.MediaId = MS.MediaId
WHERE	M.MediaTypeId <> 10001 AND M.LibraryId > 0 AND M.IsInMediaGroup > 0
	AND M.BarCode <> 'StdAln_' + CAST(M.MediaId AS VARCHAR(64))
	AND V.VolumeFlags IN (1,2,3,5,7)
GROUP BY M.LibraryId

-- Recycleable tapes
INSERT	INTO #LibrarySpace
SELECT	M.LibraryId, SUM(MS.TotalSpaceMB), 0
FROM 	MMMedia M
		INNER JOIN MMVolume V ON M.MediaId = V.MediaId
		INNER JOIN MMMediaSide MS ON M.MediaId = MS.MediaId
WHERE	M.MediaTypeId <> 10001 AND M.LibraryId > 0 AND M.SpareGroupId = 0
	AND M.BarCode <> 'StdAln_' + CAST(M.MediaId AS VARCHAR(64))
	AND V.VolumeFlags = 6
GROUP BY M.LibraryId

-- Spare tapes
INSERT	INTO #LibrarySpace
SELECT	M.LibraryId, SUM(MS.TotalSpaceMB), 0
FROM 	MMMedia M
		INNER JOIN MMMediaSide MS ON M.MediaId = MS.MediaId
		INNER JOIN MMSpareGroup SG ON M.SpareGroupId = SG.SpareGroupId
WHERE	M.MediaTypeId <> 10001 AND M.LibraryId > 0 AND M.MediaLocation <> 3 AND M.IsInMediaGroup = 0
	AND M.BarCode <> 'StdAln_' + CAST(M.MediaId AS VARCHAR(64))
GROUP BY M.LibraryId

IF object_id('tempdb.dbo.#LibraryStats') is not null DROP TABLE #LibraryStats
CREATE TABLE #LibraryStats (LibraryId INT, UsedTapeCount INT, SpareTapeCount INT, TotalSpaceMB BIGINT, UsedSpaceMB BIGINT, AppSizeMB BIGINT, DataWrittenMB BIGINT)

INSERT	INTO #LibraryStats
SELECT	L.LibraryId, 0, 0, ISNULL(T.TotalSpaceMB,0), ISNULL(T.UsedSpaceMB,0), 0, 0
FROM	MMLibrary L
		LEFT OUTER JOIN 
		(	SELECT	LibraryId, SUM(TotalSpaceMB) AS TotalSpaceMB, SUM(UsedSpaceMB) AS UsedSpaceMB
			FROM	#LibrarySpace
			GROUP BY LibraryId
		) T ON L.LibraryId = T.LibraryId
WHERE	L.LibraryTypeId <> 3 AND L.LibraryId > 0

DROP TABLE #LibrarySpace

SELECT	@dtBegin = MAX(monthVal) FROM #MonthlyTbl

UPDATE	S SET UsedTapeCount = T.UsedTapeCount, SpareTapeCount = T.SpareTapeCount
FROM	#LibraryStats S
		INNER JOIN #MonthlyTapeUsage T ON S.LibraryId = T.LibraryId
WHERE	T.MonthStart = @dtBegin


-- Get App Size and Data Written 
CREATE TABLE #LibraryDataSize (LibraryId INT, AppSizeMB BIGINT, DataWrittenMB BIGINT)
CREATE TABLE #TapeVolume (LibraryId INT, VolumeId INT)

INSERT	INTO #TapeVolume
SELECT	M.LibraryId, V.VolumeId
FROM	MMVolume V WITH (NOLOCK)
		INNER JOIN MMMedia M WITH (NOLOCK) ON M.MediaId = V.MediaId
WHERE	V.PhysicalBytesMB > 0 AND M.LibraryId > 0 AND M.IsInMediaGroup > 0 AND M.MediaTypeId <> 10001

CREATE INDEX #TapeVolume_LibraryId_idx ON #TapeVolume (LibraryId)

IF COL_LENGTH('archChunkMapping', 'unCompSize') IS NOT NULL
BEGIN
	SET @surveyXML = N'
	INSERT	INTO #LibraryDataSize
	SELECT	M.LibraryId,
			SUM(1.0*ACM.unCompSize)/1024/1024,
			SUM(1.0*ACM.physicalSize)/1024/1024
	FROM	archChunkMapping ACM WITH (NOLOCK)
			INNER JOIN archChunk AC WITH (NOLOCK) ON AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId
			INNER JOIN #TapeVolume M ON M.VolumeId = AC.volumeId
	GROUP BY M.LibraryId'
	
	EXEC sp_executesql @surveyXML
END
ELSE
BEGIN
	INSERT	INTO #LibraryDataSize
	SELECT	M.LibraryId,
			SUM(CASE WHEN totalBackupSize > 0 THEN (1.0*ACM.physicalSize/totalBackupSize)*totalUncompBytes ELSE 1.0*ACM.physicalSize END)/1024/1024,
			SUM(1.0*ACM.physicalSize)/1024/1024
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN archFile AF WITH (NOLOCK) ON B.jobId = AF.jobId AND B.commCellId = AF.commCellId AND AF.isValid = 1
			INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AF.id = ACM.archFileId AND AF.commCellId = ACM.commCellId
			INNER JOIN archChunk AC WITH (NOLOCK) ON AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId
			INNER JOIN #TapeVolume M ON M.VolumeId = AC.volumeId
	GROUP BY M.LibraryId

	INSERT	INTO #LibraryDataSize
	SELECT	M.LibraryId,
			SUM(1.0*ACM.physicalSize)/1024/1024,
			SUM(1.0*ACM.physicalSize)/1024/1024
	FROM	JMAdminJobStatsTable A WITH (NOLOCK)
			INNER JOIN archFile AF WITH (NOLOCK) ON A.jobId = AF.jobId AND A.commCellId = AF.commCellId AND AF.isValid = 1
			INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AF.id = ACM.archFileId AND AF.commCellId = ACM.commCellId
			INNER JOIN archChunk AC WITH (NOLOCK) ON AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId
			INNER JOIN #TapeVolume M ON M.VolumeId = AC.volumeId
	GROUP BY M.LibraryId
END

UPDATE	L SET AppSizeMB = S.AppSizeMB, DataWrittenMB = S.DataWrittenMB
FROM	#LibraryStats L
		INNER JOIN (
			SELECT	LibraryId, SUM(AppSizeMB) AS AppSizeMB, SUM(DataWrittenMB) AS DataWrittenMB
			FROM	#LibraryDataSize
			GROUP BY LibraryId
		) S ON L.LibraryId = S.LibraryId

DROP TABLE #LibraryDataSize
DROP TABLE #TapeVolume

-- Get total TapesUsed and UsedSpaceMB per media types
IF object_id('tempdb.dbo.#TapeUsageByType') is not null DROP TABLE #TapeUsageByType
CREATE TABLE #TapeUsageByType(MediaTypeId INT, UsedTapeCount INT, UsedSpaceMB BIGINT)

INSERT INTO #TapeUsageByType
SELECT	M.MediaTypeId, COUNT(DISTINCT MS.MediaId), SUM(CAST(MS.PhysicalBytesMB AS BIGINT))
FROM	MMMedia M INNER JOIN MMMediaSide MS ON M.MediaId = MS.MediaId
WHERE	M.MediaId > 0 AND M.MediaTypeId <> 10001 AND M.IsInMediaGroup = 1
	AND MS.PhysicalBytesMB > 0
GROUP BY M.MediaTypeId


SET @surveyXML =
( SELECT
	(SELECT 
		T.MediaTypeId AS '@MediaTypeId', 
		MT.MediaTypeName AS '@MediaTypeName', 
		T.UsedTapeCount AS '@UsedTapeCount', 
		T.UsedSpaceMB AS '@UsedSpaceMB'
	FROM #TapeUsageByType T INNER JOIN MMMediaType MT WITH (NOLOCK)
		ON T.MediaTypeId = MT.MediaTypeId
	ORDER BY MT.MediaTypeName
	FOR XML PATH('TapeUsageByType'), TYPE),

	(SELECT 
		StartDate AS '@MonthVal', 
		MediaTypeId AS '@MediaTypeId', 
		MediaTypeName AS '@MediaTypeName', 
		TapesUsed AS '@TapesUsed', 
		TapesRecycled AS '@TapesRecycled' 
	FROM #TapeMediaUsage 
	ORDER BY StartDate
	FOR XML PATH('TapeMediaUsageXML'), TYPE),

	(SELECT 
		LibraryId AS '@LibraryId', 
		UsedTapeCount AS '@UsedTapeCount', 
		SpareTapeCount AS '@SpareTapeCount', 
		TotalSpaceMB AS '@TotalSpaceMB', 
		UsedSpaceMB AS '@UsedSpaceMB', 
		AppSizeMB AS '@AppSizeMB', 
		DataWrittenMB AS '@DataWrittenMB' 
	FROM #LibraryStats 
	ORDER BY LibraryId
	FOR XML PATH('LibraryTapeUsage'), TYPE),

	(SELECT 
		LibraryId AS '@LibraryId', 
		MonthStart AS '@MonthStart',
		UsedTapeCount AS '@UsedTapeCount', 
		SpareTapeCount AS '@SpareTapeCount' 
	FROM #MonthlyTapeUsage 
	ORDER BY LibraryId, MonthStart
	FOR XML PATH('MonthlyTapeUsage'), TYPE)

  FOR XML PATH('')
)

DROP TABLE #MonthlyTbl
DROP TABLE #TapeMediaUsage
DROP TABLE #LibraryStats
DROP TABLE #MonthlyTapeUsage
DROP TABLE #TapeUsageByType

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

--SELECT cast (@surveyXML AS XML)
SET NOCOUNT OFF
--------- END - GENERATED CODE ---------