DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10384
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[scheduleCount] int,[SQLSubclientsCount] int,[OracleSubclientsCount] int,[OtherCount] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @scheduleCnt INT, @SQLentityCnt INT, @ORAentityCnt INT, @OtherentityCnt INT
DECLARE @counts AS TABLE(scheduleCount INT, SQLSubclientsCount INT, OracleSubclientsCount INT, OtherCount INT)
DECLARE @schedules AS TABLE(taskId INT,subtaskId INT)
DECLARE @AssocEntities AS TABLE(taskId INT, clientId INT, instanceId INT, backupSetId INT, subclientId INT, appType INT)

INSERT INTO @schedules (taskId,subtaskId)
SELECT DISTINCT T.taskId, S.subTaskId
FROM TM_SubTask S WITH(NOLOCK)
     INNER JOIN TM_Task T WITH(NOLOCK) ON T.taskId = S.taskId
                                          AND T.disabled = 0
                                          AND T.deleted = 0
     INNER JOIN TM_SubTaskOptions STO WITH(NOLOCK) ON STO.subTaskId = S.subTaskId
                                                      AND STO.value = ''1''
                                                      AND STO.optionId = 1299309857;

INSERT INTO @AssocEntities(taskId, clientId, instanceId, backupSetId, subclientId, appType)
SELECT AE.taskId, APP.clientId , APP.instance , APP.backupset , APP.Id, APP.appTypeId
FROM dbo.TM_AssocEntity AE WITH (NOLOCK)
INNER JOIN @schedules SC ON SC.taskId = AE.taskId
INNER JOIN APP_Application APP WITH (NOLOCK) ON 
                (
                    (
                        APP.clientId = AE.clientId AND AE.clientId > 0
                        AND (APP.appTypeId = AE.apptypeId OR AE.apptypeId = 0)
                        AND (APP.instance = AE.instanceId OR AE.instanceId = 0)
                        AND (APP.backupSet = AE.backupsetId OR AE.backupsetId = 0)
                    )
                )
WHERE AE.assocType!=158
IF EXISTS(
		  SELECT 1 FROM sys.columns 
          WHERE Name = ''entityId''
          AND Object_ID = Object_ID(''dbo.TM_AssocEntity'')
		 )
BEGIN
	INSERT INTO @AssocEntities(taskId, clientId, instanceId, backupSetId, subclientId, appType)
	SELECT TM_AssocEntity.taskId ,App_Application.clientId , App_Application.instance , App_Application.backupset, App_Application.Id, App_Application.appTypeId
	FROM dbo.TM_AssocEntity WITH (NOLOCK)
	INNER JOIN App_SubclientProp SCP WITH(NOLOCK) ON SCP.attrName = ''Associated Plan'' AND SCP.cs_attrName = CHECKSUM(N''Associated Plan'')
	            AND CAST(TM_AssocEntity.entityId AS NVARCHAR(10)) = SCP.attrVal                 
	            AND SCP.modified = 0
	INNER JOIN App_Application (NOLOCK) ON App_Application.id = SCP.componentNameId     
	INNER JOIN @schedules SC ON SC.taskId = TM_AssocEntity.taskId
	WHERE TM_AssocEntity.assocType = 158
END


SET @scheduleCnt=
(SELECT COUNT(SC.subtaskId)
FROM @schedules SC)

SET @SQLentityCnt=
(SELECT COUNT(AE.subclientId)
FROM @AssocEntities AE WHERE AE.appType = 81)

SET @ORAentityCnt=(SELECT COUNT(AE.subclientId)
FROM @AssocEntities AE WHERE AE.appType IN (22, 80))

SET @OtherentityCnt=(SELECT COUNT(AE.subclientId)
FROM @AssocEntities AE WHERE AE.appType NOT IN ( 22, 80, 81))

INSERT INTO @counts(scheduleCount , SQLSubclientsCount , OracleSubclientsCount , OtherCount )
SELECT @scheduleCnt, @SQLentityCnt, @ORAentityCnt, @OtherentityCnt

SELECT * FROM @counts ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [scheduleCount] AS '@scheduleCount',[SQLSubclientsCount] AS '@SQLSubclientsCount',[OracleSubclientsCount] AS '@OracleSubclientsCount',[OtherCount] AS '@OtherCount' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10384'), 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'))