--@querytype    CSV
--- Please follow the below comments to insert SQL statements.

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

SET NOCOUNT ON


BEGIN TRY

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



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

--------- Insert your SQL statements here
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 @csReleaseId INT = 0
SELECT  @csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE @csSPVersion INT = 0

SELECT  @csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

IF @csReleaseId < 15 OR @csReleaseId = 15 AND @csSPVersion < 7
BEGIN
    RETURN
END

IF OBJECT_ID('tempdb..#PhysicalNodeInfo') IS NOT NULL
	DROP TABLE #PhysicalNodeInfo
	
CREATE TABLE #PhysicalNodeInfo (clientId INT, activePhysicalNodeName nvarchar(256))

INSERT INTO #PhysicalNodeInfo
SELECT componentNameId, CL.name 
FROM 
(SELECT componentNameId,MAX(attrVal) AS nodeId FROM APP_ClientProp AC WHERE AC.attrName = 'Active Physical Node' AND AC.attrVal <> componentNameId AND AC.modified = 0 GROUP BY componentNameId) AC
	INNER JOIN App_Client CL ON AC.nodeId = CL.id
UNION 
SELECT c.id AS ClusterClientId, MIN(c1.name) AS PhysicalClientName
FROM APP_Client AS c WITH (NOLOCK)
	INNER JOIN APP_Application AS app WITH (NOLOCK)
		ON c.simOperatingSystemId = 56 --Oracle RAC
		AND c.id = app.clientId
	INNER JOIN (SELECT instaceId, MIN(clientId) AS clientId FROM APP_OracleRacInstance AS ori WITH (NOLOCK) GROUP BY instaceId) AS i
		ON app.instance = i.instaceId 
	INNER JOIN APP_Client AS c1 WITH (NOLOCK)
		ON c1.id = i.clientId
GROUP BY c.id
UNION 
SELECT cp.componentNameId AS ClusterClientId, MIN(cp.attrName) AS PhysicalClientName
FROM APP_ClientProp AS cp WITH (NOLOCK)
WHERE cp.attrType = 125 --PROPERTY_DAG_MEMBERSERVER for Exchange DAG
	AND cp.modified = 0
GROUP BY cp.componentNameId

-- 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)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @TIME_STAMP_NAME 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'
	set @TIME_STAMP_NAME = 'CommservSurveyPrivateRestoreJobStatsCollectionTime'
END
ELSE IF ((@isPrivateRunning = 'Metrics Direct Dip')OR  (@freequencyMode ='3'))
BEGIN
	set @TIME_STAMP_NAME = 'CommservSurveyDirectDIPRestoreJobStatsCollectionTime'
END
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveySpecialClientGroup'
	set @TIME_STAMP_NAME = 'CommservSurveyRestoreJobStatsCollectionTime'
END

DECLARE @LastCollectionTime INT = 0
IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @lastcollectionTimeSTR VARCHAR(256)='';
	EXEC GetSurveyParamValue @TIME_STAMP_NAME, @lastcollectionTimeSTR OUTPUT 
	IF @lastcollectionTimeSTR <> ''
		SELECT @lastcollectionTime = CAST(@lastcollectionTimeSTR AS INT)
END
ELSE
BEGIN
	SELECT	@LastCollectionTime = CAST(CAST(value AS VARCHAR(20)) AS INT) 
	FROM	GXGlobalParam
	WHERE	name = @TIME_STAMP_NAME
END

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

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 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 join @includeGroups on id=groupID)
BEGIN
	SET @specialGroupExists =1
	INSERT INTO @includeClients
	SELECT
		DISTINCT C.id
	FROM
		APP_Client C WITH (NOLOCK)
		JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
			ON CGA.clientId = C.id
		JOIN @includeGroups CG 
			ON CGA.clientGroupId = CG.groupId
END

IF OBJECT_ID('tempdb..#RestJobDetails') IS NOT NULL 
	DROP TABLE #RestJobDetails

create table #RestJobDetails (
	Jobid INT,
	JobStatus INT,
	SrcClientId INT,
	SrcClientName NVARCHAR(512),
	DestClientId INT,
	DestClientName NVARCHAR(512),
	AppTypeId INT,
	AppType NVARCHAR(512),
	InstanceId INT,
	Instance NVARCHAR(512),
	BkpsetId INT,
	Bkpset NVARCHAR(512),
	StartTime INT,
	EndTime INT,
	Duration INT,
	TotFiles2xfer BIGINT,
	TotSuccess BIGINT,
	SkippedObjects BIGINT,
	FailedObjects BIGINT,
	TotCompMB BIGINT,
	TotUnCompMB BIGINT,
	ErrorCode VARCHAR(32),
	FailedReason NVARCHAR(512),
	FailedReasonId INT,
	OpType INT,
	SubOpType INT,
	RstAttributes BIGINT,
	InitFrom INT,
	UserName NVARCHAR(512),
	JobDescription	NVARCHAR(MAX),
	BackupTime DATETIME
) 

IF  (@specialGroupExists =1 )
BEGIN
INSERT INTO #RestJobDetails
	SELECT   
		RS.jobid,
		RS.status,
		RS.srcClientId,
		SC.name,
		RS.destClientId,
		DC.name,
		RS.appType,
		APT.name,
		RS.instanceId,
		ISNULL(ITN.name, ''),
		RS.bkpsetId,
		ISNULL(BN.name, ''),
		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servStartTime), @csTimeZone)), 
        dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servEndTime), @csTimeZone)),  
		RS.duration,
		CAST(convert(real, RS.totFiles2xfer_h) * 214748.3648*10000 + convert(real, RS.totFiles2xfer_l) AS BIGINT),
		CAST(convert(real, RS.totSuccess_h) * 214748.3648*10000 + convert(real, RS.totSuccess_l) AS BIGINT),
		RS.totSkipped,
		RS.totFailed,
		CAST(convert(real, RS.totCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		CAST(convert(real, RS.totUnCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totUnCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		'',
		REPLACE(REPLACE(RS.failureReason, 'null,', ''), 'null', ''), 0,
		RS.opType,
		JS.subOpType,
		RS.rstattributes,
		RS.initFrom,
		RS.username,
		JS.jobDescription,
		NULL
	FROM JMRestoreStats RS WITH (NOLOCK)
		JOIN JMJobStats JS WITH (NOLOCK)
			ON RS.jobId = JS.jobId AND JS.commCellId = 2
		JOIN APP_Client DC WITH (NOLOCK) 
			ON RS.destClientId = DC.id 
		JOIN APP_Client SC WITH (NOLOCK) 
			ON RS.srcClientId = SC.id
		JOIN @includeClients AS IC
			ON IC.cid = SC.id
		JOIN APP_iDAType APT WITH (NOLOCK)
			ON RS.appType = APT.type 
		LEFT OUTER JOIN APP_InstanceName ITN WITH (NOLOCK)
			ON RS.instanceId = ITN.id  
		LEFT OUTER JOIN APP_BackupSetName BN WITH (NOLOCK)
			ON RS.bkpsetId = BN.id 
	WHERE RS.destClientId > 1 AND RS.servEndTime BETWEEN @LastCollectionTime AND @CurrCollectionTime
		AND RS.opType NOT IN (12, 52, 80, 105)
END
ELSE
BEGIN
INSERT INTO #RestJobDetails
	SELECT   
		RS.jobid,
		RS.status,
		RS.srcClientId,
		SC.name,
		RS.destClientId,
		DC.name,
		RS.appType,
		APT.name,
		RS.instanceId,
		ISNULL(ITN.name, ''),
		RS.bkpsetId,
		ISNULL(BN.name, ''),
		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servStartTime), @csTimeZone)), 
        dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servEndTime), @csTimeZone)), 
		RS.duration,
		CAST(convert(real, RS.totFiles2xfer_h) * 214748.3648*10000 + convert(real, RS.totFiles2xfer_l) AS BIGINT),
		CAST(convert(real, RS.totSuccess_h) * 214748.3648*10000 + convert(real, RS.totSuccess_l) AS BIGINT),
		RS.totSkipped,
		RS.totFailed,
		CAST(convert(real, RS.totCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		CAST(convert(real, RS.totUnCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totUnCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		'',
		REPLACE(REPLACE(RS.failureReason, 'null,', ''), 'null', ''), 0,
		RS.opType,
		JS.subOpType,
		RS.rstattributes,
		RS.initFrom,
		RS.username,
		JS.jobDescription,
		NULL
	FROM JMRestoreStats RS WITH (NOLOCK)
		JOIN JMJobStats JS WITH (NOLOCK)
			ON RS.jobId = JS.jobId AND JS.commCellId = 2
		JOIN APP_Client DC WITH (NOLOCK) 
			ON RS.destClientId = DC.id 
		JOIN APP_Client SC WITH (NOLOCK) 
			ON RS.srcClientId = SC.id 
		JOIN APP_iDAType APT WITH (NOLOCK)
			ON RS.appType = APT.type 
		LEFT OUTER JOIN APP_InstanceName ITN WITH (NOLOCK)
			ON RS.instanceId = ITN.id  
		LEFT OUTER JOIN APP_BackupSetName BN WITH (NOLOCK)
			ON RS.bkpsetId = BN.id 
	WHERE RS.destClientId > 1 AND RS.servEndTime BETWEEN @LastCollectionTime AND @CurrCollectionTime
		AND RS.opType NOT IN (12, 52, 80, 105)
END

IF OBJECT_ID('tempdb..#RestoredBackupTime') IS NOT NULL DROP TABLE #RestoredBackupTime
CREATE TABLE #RestoredBackupTime (RestoreJobId INT, BackupTime DATETIME)

INSERT	INTO #RestoredBackupTime
SELECT	M.jobId_l, 
		CAST(ISNULL(P.data, '1970-01-01') AS datetime)
FROM	EvMsg M WITH(NOLOCK) 
		INNER JOIN evParam P WITH(NOLOCK) ON M.id = P.evMsgId
		INNER JOIN #RestJobDetails RJ ON M.jobId_l = RJ.Jobid AND M.commCellId = 2
		INNER JOIN EvLocaleMsgs C WITH (NOLOCK) ON M.messageId = C.MessageID AND C.LocaleID = 0
		WHERE C.SubsystemID=13 AND C.MessageNum=216 AND P.position = 4 AND M.type&4=0

UPDATE	R SET BackupTime = S.BackupTime
FROM	#RestJobDetails R INNER JOIN 
		(SELECT	RestoreJobId, MIN(BackupTime) AS BackupTime
		FROM	#RestoredBackupTime
		GROUP BY RestoreJobId
		) S ON R.Jobid = S.RestoreJobId
		
IF OBJECT_ID('tempdb..#RestoredBackupTime') IS NOT NULL DROP TABLE #RestoredBackupTime

UPDATE	#RestJobDetails SET FailedReason = 
		CASE 
			WHEN ISNULL(FailedReason, '') = '' THEN ''
			WHEN CHARINDEX(',', FailedReason) = LEN(FailedReason) THEN FailedReason
			ELSE RIGHT(FailedReason, CHARINDEX(',', REVERSE(FailedReason), 2)-1)
		END

UPDATE #RestJobDetails SET FailedReasonId = CAST(REPLACE(FailedReason, ',', '') AS INT) WHERE FailedReason <> ''

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


SELECT   
		Jobid AS 'Jobid',
		JobStatus AS 'JobStatus',
		SrcClientId AS 'SrcClientId',
		SrcClientName AS 'SrcClientName',
		ISNULL((SELECT activePhysicalNodeName FROM #PhysicalNodeInfo WHERE clientId = SrcClientId),'') AS 'SrcPhysicalClientName',
		DestClientId AS 'DestClientId',
		DestClientName AS 'DestClientName',		
		ISNULL((SELECT activePhysicalNodeName FROM #PhysicalNodeInfo WHERE clientId = DestClientId),'') AS 'DestPhysicalClientName',
		AppTypeId AS 'AppTypeId',
		AppType AS 'AppType',
		InstanceId AS 'InstanceId',
		Instance AS 'Instance',
		BkpsetId AS 'BkpsetId',
		Bkpset AS 'Bkpset',
		StartTime AS 'StartTime', 
		EndTime AS 'EndTime',  
		Duration AS 'Duration',
		TotFiles2xfer AS 'TotFiles2xfer',
		TotSuccess AS 'TotSuccess',
		SkippedObjects AS 'SkippedObjects',
		FailedObjects AS 'FailedObjects',
		TotCompMB AS 'TotCompMB',
		TotUnCompMB AS 'TotUnCompMB',
		ISNULL(ErrorCode,'') AS 'ErrorCode',
		ISNULL(REPLACE(REPLACE(REPLACE(FailedReason,',','<COMMA>'), CHAR(13), ''), CHAR(10), ''),'') AS 'FailedReason',
		OpType AS 'OpType',
		SubOpType AS 'SubOpType',
		RstAttributes AS 'RstAttributes',
		InitFrom AS 'InitFrom',
		ISNULL(REPLACE(UserName,',','<COMMA>'),'') AS 'UserName',
		ISNULL(REPLACE(REPLACE(REPLACE(JobDescription,',','<COMMA>'), CHAR(13), ''), CHAR(10), ''),'') AS 'JobDescription',
		ISNULL(BackupTime,'') AS 'BackupTime',
		1 AS 'inCSTimeZone'
	FROM #RestJobDetails


IF OBJECT_ID('tempdb..#RestJobDetails') IS NOT NULL 
	DROP TABLE #RestJobDetails

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

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

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())

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('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT '''+@TIME_STAMP_NAME+''', '''', 0, 0')
	END
END
	 
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 ---------