--- Please follow the below comments to insert SQL statements.

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

SET NOCOUNT ON

BEGIN TRY

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

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



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

--------- Insert your SQL statements here
DECLARE @isPrivateRunning varchar(256)=''
DECLARE @frequencyMode   NVARCHAR(MAX) ='0'
IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END
 

DECLARE @csTimeZone    NVARCHAR(1024) = ''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END

DECLARE @CurrentTime	INT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @CurrCollectionTime INT = @CurrentTime
DECLARE @LastCollectionTime INT = 0

DECLARE @TIME_STAMP_NAME NVARCHAR(128)
DECLARE @date datetime =GETDATE()
DECLARE @nDays INT
 
IF ((@isPrivateRunning = 'Metrics Reporting') OR (@frequencyMode = '1')) --Private  
BEGIN
	SET @TIME_STAMP_NAME  = 'CommservSurveyBackupCopyJobStatsCollectionTime'
	SET @nDays = datediff(day,DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0),@date)+1	
END
else IF (@isPrivateRunning = 'Metrics Direct Dip')
BEGIN
	SET @TIME_STAMP_NAME  = 'CommservSurveyDirectDIPBackupCopyJobStatsCollectionTime'
	SET @nDays = datediff(day,DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0),@date)+1	
END

IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public
BEGIN
	SET @TIME_STAMP_NAME = 'CommservSurveyPublicBackupCopyJobStatsCollectionTime'
	SET @nDays =3	--Go back 3 days  
	DECLARE @isJobDetailsCollectionEnabled INT = 0
	SELECT	@isJobDetailsCollectionEnabled = CAST(CAST(value AS VARCHAR(20)) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE	name = 'CommservSurveyPublicJobStatsEnabled'
	IF (@isJobDetailsCollectionEnabled <> 1) --No need to collect job details
	BEGIN
		DECLARE @relId INT
		SELECT @relId = MAX(id) FROM simAllGalaxyRel		
		IF (@relId <= 15) --V10 or below done have check for null value. So set outputxml with surveyxml as blank
		BEGIN
			SET @outputXML = 
			(
				SELECT
					@queryId AS '@QueryId',
					dbo.GetUnixTime(GETUTCDATE()) AS '@LogDate',
					0 AS '@QueryRunningTime',
					@surveyXML
				FOR XML PATH('Rpt_CSSXMLDATA')
			)			
		END
		RETURN
	END
END
IF (@isPrivateRunning = ('Metrics Direct Dip'))
BEGIN
     --Direct Dip 
     SET @TIME_STAMP_NAME ='CommservSurveyDIPBackupCopyJobStatsCollectionTime' 
	SET @nDays =3	--Go back 3 days  
END

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
	DECLARE	@LastCollection DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE())-@nDays, 0)
	SET	@LastCollection	= dbo.ClientLocalToUTCTime(@LastCollection, 2)
	DECLARE @CSFirstJobTime INT
	SELECT @CSFirstJobTime = MIN(servEndDate) FROM JMBkpStats B WITH (NOLOCK)	
	IF (@CSFirstJobTime < dbo.GetUnixTime(@LastCollection))
		SET @LastCollectionTime = dbo.GetUnixTime(@LastCollection) 
	ELSE
		SET @LastCollectionTime = @CSFirstJobTime
END

DECLARE @maxcollectionsec INT = 24*3600    --default set to collect max 24 hours  
IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public
	SET @maxcollectionsec = @maxcollectionsec * 3	--Collect for maximum 3 days job in one run

IF	@LastCollectionTime < (@CurrentTime - @maxcollectionsec)
	SET @CurrCollectionTime = (@LastCollectionTime + @maxcollectionsec)


-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)=''

IF (@isPrivateRunning = 'Metrics Reporting')
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) 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);
		DECLARE @tmpStr NVARCHAR(100);
		SELECT @tmpStr = NAME FROM APP_ClientGroup WITH (NOLOCK) WHERE id = @groupId;
		IF(@tmpStr IS NOT NULL)
			SET @groupStrName = @groupStrName + ', ' + @tmpStr
	END
	SET @startIndex = @endIndex + 1
	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0

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


IF object_id('tempdb.dbo.#ExcludedAppIds') is not null DROP TABLE #ExcludedAppIds 
CREATE TABLE #ExcludedAppIds (appId INT)

INSERT INTO #ExcludedAppIds
SELECT componentNameId FROM APP_subclientProp WITH (NOLOCK) 
WHERE attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID') 
  AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID'))
  AND attrVal <> '0' AND modified = 0
UNION
SELECT id FROM APP_Application WITH (NOLOCK) 
WHERE clientId IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1')

IF object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails 
CREATE TABLE #JobDetails (
        JobId INT, AppId INT, AppTypeId INT, 
        dataArchGrpId INT, logArchGrpId INT, StoragePolicy NVARCHAR(1026), PrimaryCopyId INT, BkpLevel INT, Status INT, StartDate INT, EndDate INT,
        ScanType INT, SizeChangePct INT, TransferTime INT, ProtectedObjects BIGINT, FailedObjects BIGINT, FailedFolders BIGINT,
        DataProtected BIGINT, DataCompressed BIGINT, DataTransferred BIGINT, DataWritten BIGINT, 
        ErrorCode VARCHAR(32), FailureReason NVARCHAR(512), SubTaskId INT, Attempts INT
        ,initFrom INT,NoOFPhases INT,failureErrorCode INT,SnapJobIDActedOn INT
)

IF  (@specialGroupExists =1 )
BEGIN
	INSERT INTO #JobDetails (JobId, AppId, AppTypeId,dataArchGrpId, logArchGrpId, StoragePolicy, PrimaryCopyId, BkpLevel, Status, StartDate, EndDate,
							 ScanType, SizeChangePct, TransferTime, ProtectedObjects, FailedObjects, FailedFolders,DataProtected, DataCompressed, DataTransferred, DataWritten,ErrorCode, 
							 FailureReason, SubTaskId, Attempts, initFrom, NoOFPhases, failureErrorCode,SnapJobIDActedOn)
	SELECT	jobId,
		B.appId, appType, 
		CASE WHEN B.dataArchGrpId > 1 THEN B.dataArchGrpId ELSE 0 END,  
		CASE WHEN B.logArchGrpId > 1 THEN B.logArchGrpId ELSE 0 END,'', 0,
		bkpLevel, CASE displayStatus WHEN 0 THEN B.status ELSE displayStatus END,
		servStartDate, servEndDate,
		scanType, percentageChange, totalWriteTime, totalNumOfFiles,
		scanFileFailures + backupFileFailures, scanFolderFailures + backupFolderFailures,
		totalUncompBytes, totalCompBytes, nwTransBytes, 0, '',
		CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason
			 ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,
		subTaskId, numAttempts
		,B.initFrom ,0,b.failureErrorCode,b.jobIdActedOn as SnapJobIDActedOn
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK)
				ON B.appId = A.id 
			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
			LEFT OUTER JOIN #ExcludedAppIds E ON B.appId = E.appId
	WHERE servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime
	 AND opType = 60    --only collect backup copy job
	 AND E.appId IS NULL
	
END
ELSE
BEGIN
	INSERT INTO #JobDetails(JobId, AppId, AppTypeId,dataArchGrpId, logArchGrpId, StoragePolicy, PrimaryCopyId, BkpLevel, Status, StartDate, EndDate,
							 ScanType, SizeChangePct, TransferTime, ProtectedObjects, FailedObjects, FailedFolders,DataProtected, DataCompressed, DataTransferred, DataWritten,ErrorCode, 
							 FailureReason, SubTaskId, Attempts, initFrom, NoOFPhases, failureErrorCode, SnapJobIDActedOn)
	SELECT jobId,
		B.appId, appType, 
		CASE WHEN B.dataArchGrpId > 1 THEN B.dataArchGrpId ELSE 0 END, 
		CASE WHEN B.logArchGrpId > 1 THEN B.logArchGrpId ELSE 0 END,'', 0,
		 bkpLevel, CASE displayStatus WHEN 0 THEN B.status ELSE displayStatus END,
		servStartDate, servEndDate,
		scanType, percentageChange, totalWriteTime, totalNumOfFiles,
		scanFileFailures + backupFileFailures, scanFolderFailures + backupFolderFailures,
		totalUncompBytes, totalCompBytes, nwTransBytes, 0, '',
		CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason
			 ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,
		subTaskId, numAttempts
		,B.initFrom ,0,b.failureErrorCode, b.jobIdActedOn as SnapJobIDActedOn
	FROM	JMBkpStats B WITH (NOLOCK)
			LEFT OUTER JOIN #ExcludedAppIds E ON B.appId = E.appId
	WHERE servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime
	and opType = 60
	AND E.appId IS NULL
END

UPDATE  J SET PrimaryCopyId =  G.defaultCopy 
FROM    #JobDetails J 
        INNER JOIN archGroup G WITH (NOLOCK) ON J.logArchGrpId = G.id OR J.dataArchGrpId = G.id AND (J.dataArchGrpId > 0 OR J.logArchGrpId > 0)

UPDATE  J SET StoragePolicy = AG.name
FROM	#JobDetails J INNER JOIN archGroup AG WITH (NOLOCK) ON J.dataArchGrpId = AG.id WHERE J.dataArchGrpId > 0
UPDATE  J SET StoragePolicy = (CASE WHEN J.dataArchGrpId>0 THEN StoragePolicy+', ' ELSE '' END) + AG.name
FROM	#JobDetails J INNER JOIN archGroup AG WITH (NOLOCK) ON J.logArchGrpId = AG.id WHERE J.dataArchGrpId != J.logArchGrpId AND J.logArchGrpId > 0


CREATE TABLE #JobSizeOnMedia (JobId INT, DataWritten BIGINT)

INSERT INTO #JobSizeOnMedia
SELECT	A.jobId, SUM(A.sizeOnMedia)
FROM	JMJobDataStats A WITH (NOLOCK) 
		INNER JOIN #JobDetails B ON A.jobId = B.JobId AND A.archGrpCopyId = B.PrimaryCopyId AND A.commCellId = 2
GROUP BY A.jobId

UPDATE	#JobDetails SET DataWritten = S.DataWritten
FROM	#JobSizeOnMedia S
WHERE	#JobDetails.JobId = S.JobId

DROP TABLE #JobSizeOnMedia

UPDATE	#JobDetails
SET		ErrorCode = CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)),
		FailureReason = LEFT(dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(0, A.FailureReason)), 512)
FROM	#JobDetails A
		INNER JOIN JMFailureReasonMsg B WITH (NOLOCK) ON CAST(LEFT(A.FailureReason, LEN(A.FailureReason)-1) AS INT) = B.id
		INNER JOIN EvLocaleMsgs C WITH (NOLOCK) ON B.messageId = C.MessageID AND C.LocaleID = 0
WHERE	A.Status <> 1 AND A.FailureReason <> ''

			 
update #JobDetails
SET		NoOFPhases = A.numOfPhases,
		Attempts = A.numOfAttempts
FROM	#JobDetails B
INNER JOIN (Select A.jobId, COUNT(DISTINCT A.phase) AS numOfPhases,COUNT(*) AS numOfAttempts
				FROM JMBkpAtmptStats A 	INNER JOIN #JobDetails B ON B.jobId = A.jobId AND 2= A.commCellId 
				GROUP BY A.jobId
			 ) A
ON A.jobId = B.JobId
			 
             
		 
---final generate output xml
SET @surveyXML = (SELECT
	(SELECT	J.JobId AS '@JobId',
	    J.SnapJobIDActedOn AS '@SnapJobIDActedOn',
		J.AppId AS '@AppId',
		J.Status AS '@Status',
		J.BkpLevel AS '@BkpLevel',
		J.ScanType AS '@ScanType',
		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.StartDate), @csTimeZone)) AS '@StartDate',
		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.EndDate), @csTimeZone)) AS '@EndDate',
		J.DataProtected AS '@DataProtected',
		J.DataCompressed AS '@DataCompressed',
		J.DataTransferred AS '@DataTransferred',
		J.DataWritten AS '@DataWritten',
		J.SizeChangePct AS '@SizeChangePct',
		J.TransferTime AS '@TransferTime',
		J.ProtectedObjects AS '@ProtectedObjects',
		J.FailedObjects AS '@FailedObjects',
		J.FailedFolders AS '@FailedFolders',
		J.ErrorCode AS '@ErrorCode',
		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(J.FailureReason,
			',',' '), CHAR(10),';'), CHAR(13),';'), '''',' '), CHAR(145),' '), CHAR(146),' '), '<BR>','') AS '@FailureReason',
		dbo.NormalizeForXML(ISNULL(J.StoragePolicy, 'N/A')) AS '@StoragePolicy',
		J.dataArchGrpId AS '@DataArchGroupId',
		--J.logArchGrpId AS '@LogArchGroupId',
		T.taskName AS '@SchedulePolicy',
		ST.subTaskName AS '@ScheduleName',
		J.Attempts AS '@Attempts'
		,J.initFrom AS '@InitFrom'
		,J.NoOFPhases AS '@Phases'
		,J.failureErrorCode AS '@failureErrorCode'
		
	FROM	#JobDetails J
		LEFT OUTER JOIN TM_SubTask ST WITH (NOLOCK) ON J.SubTaskId = ST.subTaskId
		LEFT OUTER JOIN TM_Task T WITH (NOLOCK) ON ST.taskId = T.taskId
				FOR XML PATH ('BackupCopyJobStats'),TYPE),
					
		(SELECT 
			CAST(DBO.GETDATETIME(@LogDate) AS DATE) as '@JobEndDate', 
			J.AppId as '@appTypeId',
			ISNULL(SUM(CASE WHEN  J.status  IN (1,2,3,4,9,14,16) THEN 1 ELSE 0 END),0) as '@TJobs',
			ISNULL(SUM(CASE WHEN  J.status IN (1) THEN 1 ELSE 0 END),0) AS '@CJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (3) THEN 1 ELSE 0 END),0) as '@CWEJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (14) THEN 1 ELSE 0 END),0) as '@CWWJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (2) THEN 1 ELSE 0 END),0) as '@FJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (4) THEN 1 ELSE 0 END),0) as '@KJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (9) AND J.failureErrorCode NOT IN (8) THEN 1 ELSE 0 END),0) as '@SJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (16) THEN 1 ELSE 0 END),0) as '@COJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (9) AND J.failureErrorCode IN (8) THEN 1 ELSE 0 END),0) as '@FTSJobs',
			ISNULL(SUM(CASE WHEN  J.status  IN (1,3,14) THEN DataProtected ELSE 0 END),0) AS '@BackupSize',
			ISNULL(SUM(CASE WHEN  J.status  IN (1,3,14) THEN DataWritten ELSE 0 END),0)   AS '@DataWritten' 
		FROM #JobDetails J
				LEFT OUTER JOIN TM_SubTask ST WITH (NOLOCK) ON J.SubTaskId = ST.subTaskId
				LEFT OUTER JOIN TM_Task T WITH (NOLOCK) ON ST.taskId = T.taskId
				GROUP BY J.AppId
				FOR XML PATH ('BackupCopyDailyJobStats'),TYPE)				
FOR XML PATH ('JobDetails')
)

--select cast (@surveyXML as XML)
--------- 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')
)

IF OBJECT_ID('GetSurveyParamValue') IS NULL
BEGIN
	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
			EXEC QS_SetKeyIntoGlobalParamTbl @TIME_STAMP_NAME, 'Y', ''
	END
END

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


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
	EXEC SetSurveyParamValue @TIME_STAMP_NAME, @CurrCollectionTime
END
ELSE
BEGIN
	UPDATE	GXGlobalParam SET value = CAST(@CurrCollectionTime AS VARCHAR(20))
	WHERE	name = @TIME_STAMP_NAME
END

SET NOCOUNT OFF

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