-- Name:- Incremental Backup Change Rate
-- Description:- List out data change rates per day (GB and percentage)
-- Size change % is of Application size

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

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 28
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  where id =2)

DECLARE @tblRptApptypes		TABLE (t_rptTypeId int, t_appTypeId int)
--INSERT INTO @tblRptApptypes VALUES(7792, 84)
--INSERT INTO @tblRptApptypes VALUES(7716, 47)		-- QR_RECOVERY_REPORT: 7718
--INSERT INTO @tblRptApptypes VALUES(7716, 50)
--INSERT INTO @tblRptApptypes VALUES(7717, 45)	-- DM_RECOVERY_REPORT: 7719
--INSERT INTO @tblRptApptypes VALUES(7717, 56)
--INSERT INTO @tblRptApptypes VALUES(7717, 57)
--INSERT INTO @tblRptApptypes VALUES(7717, 58)
--INSERT INTO @tblRptApptypes VALUES(7717, 66)
--INSERT INTO @tblRptApptypes VALUES(7717, 73)
--INSERT INTO @tblRptApptypes VALUES(7717, 82)
--INSERT INTO @tblRptApptypes VALUES(7717, 83)
--INSERT INTO @tblRptApptypes VALUES(7717, 67)
--INSERT INTO @tblRptApptypes VALUES(7717, 68)
--INSERT INTO @tblRptApptypes VALUES(7717, 90)
--INSERT INTO @tblRptApptypes VALUES(7717, 89)
--Filter CommNet Server and Commnet Agent for Backup Job Summary Report
INSERT INTO @tblRptApptypes VALUES(0, 121)
INSERT INTO @tblRptApptypes VALUES(0, 122)
INSERT INTO @tblRptApptypes VALUES(7788, 93)
INSERT INTO @tblRptApptypes VALUES(7788, 105)
--SRM
INSERT INTO @tblRptApptypes VALUES(7744, 600)
INSERT INTO @tblRptApptypes VALUES(7744, 601)
INSERT INTO @tblRptApptypes VALUES(7744, 602)
INSERT INTO @tblRptApptypes VALUES(7744, 603)
INSERT INTO @tblRptApptypes VALUES(7744, 604)
INSERT INTO @tblRptApptypes VALUES(7744, 605)
INSERT INTO @tblRptApptypes VALUES(7744, 606)
INSERT INTO @tblRptApptypes VALUES(7744, 607)
INSERT INTO @tblRptApptypes VALUES(7744, 650)
INSERT INTO @tblRptApptypes VALUES(7744, 651)
INSERT INTO @tblRptApptypes VALUES(7744, 652)
INSERT INTO @tblRptApptypes VALUES(7744, 699)

IF(@nreleaseId =15)
BEGIN
--10.0 only
--INSERT INTO @tblRptApptypes VALUES(8003, 45)	-- DM_RECOVERY_REPORT: 7719
--INSERT INTO @tblRptApptypes VALUES(8003, 56)
--INSERT INTO @tblRptApptypes VALUES(8003, 57)
--INSERT INTO @tblRptApptypes VALUES(8003, 58)
--INSERT INTO @tblRptApptypes VALUES(8003, 66)
--INSERT INTO @tblRptApptypes VALUES(8003, 73)
--INSERT INTO @tblRptApptypes VALUES(8003, 82)
--INSERT INTO @tblRptApptypes VALUES(8003, 83)
--INSERT INTO @tblRptApptypes VALUES(8003, 67)
--INSERT INTO @tblRptApptypes VALUES(8003, 68)
--INSERT INTO @tblRptApptypes VALUES(8003, 90)
--INSERT INTO @tblRptApptypes VALUES(8003, 89)
INSERT INTO @tblRptApptypes VALUES(8003, 127)
 --10.0 done
 END

-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
DECLARE @groupStr varchar(256);
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @freequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting')OR  (@freequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveySpecialClientGroup'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        

SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
  
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
	SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
	if(@groupId > 0)
	BEGIN
		insert into @includeGroups values(@groupId);		
	END
	SET @startIndex = @endIndex + 1
	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0

IF EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
	SET @specialGroupExists =1



DECLARE @backupsize_table TABLE
(
totalbackupsize BIGINT,
date_no INTEGER
)
DECLARE @day_basis_backup_table TABLE
(
totalbackupsize BIGINT,
date_no INTEGER
)
DECLARE @changerate_table TABLE
(
date1 INTEGER,
change_percent float,
change_GB float,
day_backup BIGINT
)
DECLARE @day INTEGER =1
DECLARE @bkp_temp_size BIGINT
DECLARE @day1 INTEGER
DECLARE @day2 INTEGER
DECLARE @backupsize1 BIGINT
DECLARE @backupsize2 BIGINT
DECLARE @total_bkp_size BIGINT
DECLARE @total_jobs INTEGER
DECLARE @bytes_to_GB INTEGER = (1024*1024*1024)
DECLARE @total_days INTEGER =8


--	Consider the jobs which are
--		type: incremental (bkpLevel = 2)
--		status: Completed, CWE, CWW (status 1, 3, 14)
--		and belong to the special client group if exists
--	Get totalBackupSize per each day of the week

IF  (@specialGroupExists =1 )
BEGIN

INSERT INTO @backupsize_table(totalbackupsize,date_no)
SELECT totalUncompBytes,DATEDIFF(d,DATEADD(s,servEndDate,'01/01/1970 00:00:00'),GETUTCDATE()) AS date_no FROM JMBkpStats B WITH (NOLOCK) 
	INNER JOIN APP_Application A WITH (NOLOCK)
		 ON B.appId = A.id
		 AND bkpLevel=2 AND DATEDIFF(d,DATEADD(s,servEndDate,'01/01/1970 00:00:00'),GETUTCDATE())BETWEEN 0 AND @total_days
	  	 AND status in (1,3,14)
	INNER JOIN APP_Client C WITH (NOLOCK)
		 ON C.id = A.clientId
	INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
		 ON CGA.clientId = C.id
	INNER JOIN @includeGroups CG 
			ON CGA.clientGroupId = CG.groupId
	AND	A.appTypeId NOT IN (select t_appTypeId from @tblRptApptypes)		 
END
ELSE
BEGIN

INSERT INTO @backupsize_table(totalbackupsize,date_no)
SELECT totalUncompBytes,DATEDIFF(d,DATEADD(s,servEndDate,'01/01/1970 00:00:00'),GETUTCDATE()) AS date_no FROM JMBkpStats B WITH (NOLOCK) 
INNER JOIN APP_Application A WITH (NOLOCK)
		 ON B.appId = A.id
WHERE bkpLevel=2 AND DATEDIFF(d,DATEADD(s,servEndDate,'01/01/1970 00:00:00'),GETUTCDATE())BETWEEN 0 AND @total_days  
	  	 AND status in (1,3,14)
	  	 AND	A.appTypeId NOT IN (select t_appTypeId from @tblRptApptypes)		 
	  	 


END		

INSERT INTO @day_basis_backup_table(totalbackupsize,date_no) 
SELECT SUM(totalbackupsize),date_no FROM @backupsize_table 
GROUP BY date_no



WHILE @day<=@total_days
BEGIN
SET @bkp_temp_size=(SELECT count(totalbackupsize) from @day_basis_backup_table WHERE date_no=@day)
IF @bkp_temp_size=0
BEGIN
INSERT INTO @day_basis_backup_table VALUES (0,@day)
END
SET @day=@day+1
END

DECLARE bkp_cursor CURSOR FOR
SELECT totalbackupsize,date_no FROM @day_basis_backup_table ORDER BY date_no ASC

OPEN bkp_cursor
FETCH NEXT FROM bkp_cursor INTO @backupsize1,@day1 

-- Calculate the backup size change rate across each day
WHILE @@FETCH_STATUS=0
BEGIN
IF @day1>1 
BEGIN
IF @backupsize1=0 
BEGIN
INSERT INTO @changerate_table VALUES (@day2,CAST(NULL AS FLOAT),(CAST((@backupsize2-@backupsize1) AS DECIMAL)/CAST((@bytes_to_GB) AS DECIMAL)),@backupsize2)
END
ELSE
INSERT INTO @changerate_table VALUES (@day2,(CAST((@backupsize2-@backupsize1) AS DECIMAL)/CAST(@backupsize1 AS DECIMAL))*100,(CAST((@backupsize2-@backupsize1) AS DECIMAL)/CAST((@bytes_to_GB) AS DECIMAL)),@backupsize2)
END
SET @day2=@day1
SET @backupsize2=@backupsize1
FETCH NEXT FROM bkp_cursor INTO @backupsize1,@day1 
END
CLOSE bkp_cursor
DEALLOCATE bkp_cursor

SET @total_bkp_size=(SELECT SUM(totalbackupsize) FROM @backupsize_table WHERE date_no!=@total_days)
SET @total_jobs= (SELECT COUNT(*) FROM @backupsize_table WHERE date_no!=@total_days)



SET @surveyXML = (
SELECT CONVERT(DECIMAL(36,6),(CAST(@total_bkp_size AS DECIMAL)/CAST((@bytes_to_GB) AS DECIMAL))) AS '@TOTAL_BKP_SIZE',@total_jobs AS '@TOTAL_JOBS',
(
SELECT DATEDIFF(s, '1970-01-01 00:00:00',DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), (-date1))) AS '@DAY', CASE WHEN change_percent IS NULL THEN 'NOT APPLICABLE'
ELSE CAST(CONVERT(DECIMAL(36,2),change_percent) AS VARCHAR) 
END AS '@Percentage_Change',CONVERT(DECIMAL(36,6),change_GB) AS '@Total_Change_In_Gb', CONVERT(DECIMAL(36,6), CAST(day_backup AS DECIMAL)/CAST((@bytes_to_GB) AS DECIMAL)) AS '@Backup_Size' FROM @changerate_table
FOR XML PATH('DAY_ENTITY'),TYPE
)
FOR XML PATH('INCREMENTAL_BACKUP_CHANGE_RATE'))
--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
--SELECT CAST(@surveyXML AS XML)
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 ---------