DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10209
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Schedules] int,[Schedule Created Time] datetime,[Replication Count] int,[Source Instance Type] int,[Destination Vendor] int,[Last Sync Time] datetime,[Software Snap Count] int,[Hardware Snap Count] int,[Live sync direct schedules] int,[software snap schedules] int,[hardware snap schedules] int,[software snap replication time] datetime,[hardware snap replication time] datetime
)


IF @releaseId = 15 OR @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; --LiveSyncUsage_CS.sql (For Commcell)
SET NOcount ON
DECLARE @SQLQuery NVARCHAR(MAX)


IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N''[dbo].[APP_VSAReplication]'') AND OBJECTPROPERTY(id, N''IsTable'') = 1)
BEGIN
	IF object_id(''tempdb.dbo.#repDetails'')			IS NOT null DROP TABLE #repDetails
	IF object_id(''tempdb.dbo.#repSubTaskDetails'')	IS NOT null DROP TABLE #repSubTaskDetails

	CREATE TABLE #repSubTaskDetails (
	 taskId				INT
	,subTaskId			INT
	,subTaskXMLOptions	XML
)
	CREATE TABLE #repDetails  (
	replicationId			INT
	,taskId					INT
	,subClientId			INT
	,created				INT
	,sourceName				NVARCHAR(1024)
	,sourceInstanceType		INT
	,destinationVendor		INT
	,replicationTime		INT
	,hasSnapBackupsEnabled	INT
	,copyPrecendence		INT
	,softwareSnapCount		INT
	,hardwareSnapCount		INT
)


	DECLARE @relId				INT				= ISNULL((select releaseId from APP_Client where id = 2), 0)
	DECLARE @spLevel			INT				= ISNULL((select MIN(HighestSP) from simInstalledPackages where ClientId = 2), 0)
	DECLARE @ESP_PROP			NVARCHAR(80)	= N''Enable Snap Backups''
	DECLARE @SNAPENGINE_PROP	NVARCHAR(80)	= N''Snap Engine''

	--IF @debugLevel > 0
	--	Select relId = @relId, spLevel = @spLevel

	 SET @SQLQuery = ''
		INSERT INTO #repDetails 
		SELECT    replicationId,
				  A.taskId,
				  A.subclientId,
				  A.created,
				  A.sourceName,
				  IP.attrval as InstanceType,
				  -1,
				  LASTRESTORETIME_XXX,        -- THIS IS ONLY PART OF QUERY THAT CHANGES WITH RELEASE LEVEL!
				  0,
				  0,
				  0,
				  0
		FROM  
		APP_VSAReplication A WITH(NOLOCK)
			INNER JOIN TM_Task T WITH(NOLOCK)				ON A.taskId = T.taskId AND T.disabled <> 1
			INNER JOIN App_application App with (nolock)	ON App.id = A.subclientId
			INNER JOIN APP_instanceProp IP with (nolock)	ON IP.componentNameId = APP.instance and IP.attrname =''''Virtual Server Instance Type'''' and IP.modified =0''


	if  (@relId >=16 )
		SET @SQLQuery = REPLACE(@SQLQuery, ''LASTRESTORETIME_XXX'', ''case when A.VMSyncedTillTime =0 then NULL else A.VMSyncedTillTime end'')
	else
		SET @SQLQuery = REPLACE(@SQLQuery, ''LASTRESTORETIME_XXX'', ''lastRestoreTime'')
	EXEC(@SQLQuery)

	IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N''[dbo].[APP_VSAReplicationProp]'') AND OBJECTPROPERTY(id, N''IsTable'') = 1)
	BEGIN
		SET @SQLQuery =''
			UPDATE RD
			set destinationVendor= VSARep.propertyValue
			FROM #repDetails RD
			INNER JOIN APP_VSAReplicationProp VSARep
			on RD.replicationId = VSARep.replicationId
			AND VSARep.propertyTypeId = 2203''
		EXEC(@SQLQuery)
	END


	--  NEED TO KNOW IF "SNAP BACKUPS" ARE ENABLED TO DETERMINE IF SIMPLY "LIVE SYNC" or "LIVE SYNC DIRECT":
	 UPDATE #repDetails
	 SET hasSnapBackupsEnabled = attrVal 
	  FROM APP_SubClientProp SCP (NOLOCK)
		INNER JOIN #repDetails  RD  
		  ON	RD.subClientId = SCP.componentNameId
			AND	SCP.cs_attrName = checksum(@ESP_PROP)
			AND	modified = 0
			AND	SCP.attrName = @ESP_PROP


 
	-- ALSO NEED TO KNOW "COPY PRECEDENCE" TOO:
	INSERT #repSubTaskDetails
	  SELECT DISTINCT T.taskId, ST.subTaskId, NULL
	  FROM  TM_Task T (NOLOCK)
		INNER JOIN #repDetails RD	ON RD.taskId = T.taskId
		INNER JOIN TM_SubTask ST	ON ST.taskId = T.taskId

	UPDATE #repSubTaskDetails			-- CAN''T MAKE THIS PART OF ABOVE JOIN AS XML CANNOT BE OPERATED ON BY "DISTINCT"
	  SET subTaskXMLOptions = STX.xmlValue
		FROM #repSubTaskDetails ST
		  INNER JOIN  TM_SubTaskXMLOptions STX on STX.subTaskId = ST.subTaskId

	UPDATE #repDetails   -- ASSUMES 1:1 Task to SubTask .  May need to do as subQuery and use "MAX" function?
	  SET copyPrecendence = 
		ISNULL(st.subTaskXMLOptions.value(''(TMMsg_JobOption/restoreOptions/browseOption/mediaOption/copyPrecedence/@copyPrecedence)[1]'' ,''int''),0) 
		  FROM   #repSubTaskDetails st
			INNER JOIN #repDetails RD  ON RD.taskId = ST.taskId
 	 
	 UPDATE #repDetails
	 SET softwareSnapCount = 1 
	  FROM APP_SubClientProp SCP (NOLOCK)
		INNER JOIN #repDetails  RD  
		  ON	RD.subClientId = SCP.componentNameId
			AND	SCP.cs_attrName = checksum(@SNAPENGINE_PROP)
			AND	modified = 0
			AND	SCP.attrName = @SNAPENGINE_PROP
			AND SCP.attrVal = ''58''
			and RD.hasSnapBackupsEnabled =1
			and rd.copyPrecendence =0			

	 UPDATE #repDetails
	 SET hardwareSnapCount = 1 
	  FROM APP_SubClientProp SCP (NOLOCK)
		INNER JOIN #repDetails  RD  
		  ON	RD.subClientId = SCP.componentNameId
			AND	SCP.cs_attrName = checksum(@SNAPENGINE_PROP)
			AND	modified = 0
			AND	SCP.attrName = @SNAPENGINE_PROP
			AND SCP.attrVal <> ''58''
			and RD.hasSnapBackupsEnabled =1
			and rd.copyPrecendence =0

			declare @softwaresnapschedules int
			declare @hardwaresnapschedules int
			declare @softwaresnapreplicationtime int
			declare @hardwaresnapreplicationtime int


			select @softwaresnapschedules = count(distinct taskid)
			from #repDetails where softwareSnapCount =1

			select @hardwaresnapschedules = count(distinct taskid)
			from #repDetails where hardwareSnapCount =1

			select @softwaresnapreplicationtime = isnull(replicationtime ,0)
			from #repDetails where  hasSnapBackupsEnabled = 1 AND  copyPrecendence = 0 AND softwareSnapCount = 1 
	
			select @hardwaresnapreplicationtime = isnull(replicationtime ,0)
			from #repDetails where  hasSnapBackupsEnabled = 1 AND  copyPrecendence = 0 AND hardwareSnapCount = 1 	
			
	--------------------			
	-- RETURN RESULTS --
	--------------------
	SELECT	COUNT(distinct taskId)						AS Schedules, 
			dbo.GetDateTime(MIN(created))				AS ''Schedule Created Time'', 
			COUNT(sourceName)							AS ''Replication Count'',
			sourceInstanceType							AS ''Source Instance Type'',
			destinationVendor							AS ''Destination Vendor'',
			dbo.getDateTime(max(replicationTime))		AS ''Last Sync Time'',
			SUM(softwareSnapCount)						AS ''Software Snap Count'',
			SUM(hardwareSnapCount)						AS ''Hardware Snap Count'',

			 @softwaresnapschedules + @hardwaresnapschedules as
													 [Live sync direct schedules],
			
													@softwaresnapschedules
													 [software snap schedules]	,	 
													 @hardwaresnapschedules
													 [hardware snap schedules]	,	
			 case when @softwaresnapreplicationtime > 0 then dbo.getdatetime(@softwaresnapreplicationtime) else NULL end 
													 [software snap replication time]	,	
			  case when @hardwaresnapreplicationtime > 0 then dbo.getdatetime(@hardwaresnapreplicationtime) else NULL end 
													 [hardware snap replication time]		
	FROM  #repDetails
	GROUP BY sourceInstanceType, destinationVendor


--select * from #repDetails
	--select * from app_subclientprop where componentNameId in (4623, 4494) and attrName = ''Snap Engine''

END

	-- CLEANUP
IF object_id(''tempdb.dbo.#repDetails'')			IS NOT null DROP TABLE #repDetails
IF object_id(''tempdb.dbo.#repSubTaskDetails'')	IS NOT null DROP TABLE #repSubTaskDetails

--select * from SMSnapShotEngine where SnapShotEngineId = 58 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Schedules] AS '@Schedules',[Schedule Created Time] AS '@ScheduleCreatedTime',[Replication Count] AS '@ReplicationCount',[Source Instance Type] AS '@SourceInstanceType',[Destination Vendor] AS '@DestinationVendor',[Last Sync Time] AS '@LastSyncTime',[Software Snap Count] AS '@SoftwareSnapCount',[Hardware Snap Count] AS '@HardwareSnapCount',[Live sync direct schedules] AS '@Livesyncdirectschedules',[software snap schedules] AS '@softwaresnapschedules',[hardware snap schedules] AS '@hardwaresnapschedules',[software snap replication time] AS '@softwaresnapreplicationtime',[hardware snap replication time] AS '@hardwaresnapreplicationtime' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10209'), type
)
  FOR XML PATH ('SurveyResults') 
)

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