DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10217
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[On Premise Location] nvarchar(max),[Using Commvault Cloud] nvarchar(max),[Third Party Cloud] nvarchar(max),[Status] nvarchar(max)
)


IF @releaseId = 15 OR @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @useNetworkPath INT = 0, @useCommvaultCloud INT = 0, @useThirdpartyCloud INT = 0, @DRCloudLibId INT = 0, @cloudDeviceTypeId INT = 0

SELECT @useNetworkPath = (CASE WHEN value like ''\\%'' THEN 1 ELSE 0 END) FROM GXGlobalParam WITH(NOLOCK) WHERE name = ''DRDumpLocation'' AND modified = 0 
SELECT @useCommvaultCloud = (CASE WHEN ISNULL(value, '''') = '''' THEN 0 ELSE 1 END) FROM GXGlobalParam WITH(NOLOCK) WHERE name = ''DRCloudUserName'' AND modified = 0
SELECT @useThirdpartyCloud = (CASE WHEN ISNULL(value, '''') = '''' OR value = ''false'' THEN 0 ELSE 1 END) FROM GXGlobalParam WITH(NOLOCK) WHERE name = ''DRCloudLibraryUploadSet'' AND modified = 0

IF @useThirdpartyCloud = 1
BEGIN
	SELECT @DRCloudLibId = ISNULL(value, 0) FROM GXGlobalParam WITH(NOLOCK) WHERE name=''DRCloudLibraryID'' AND modified = 0
	SELECT @cloudDeviceTypeId = D.DeviceTypeId 
	FROM MMMountPath MP WITH(NOLOCK)
	INNER JOIN MMMountPathToStorageDevice MPSD WITH(NOLOCK)
		ON MP.MountPathId = MPSD.MountPathId
	INNER JOIN MMDevice D WITH(NOLOCK)
		ON MPSD.DeviceId = D.DeviceId
	INNER JOIN MMLibrary L WITH(NOLOCK)
		ON L.LibraryId = MP.LibraryId
		AND MP.LibraryId = @DRCloudLibId
END

SELECT 
	CASE WHEN @useNetworkPath = 1 THEN ''Network path'' ELSE ''CS local path'' END AS ''On Premise Location'',
	CASE WHEN @useCommvaultCloud = 1 THEN ''Yes'' ELSE ''No'' END AS ''Using Commvault Cloud'',
	CASE @cloudDeviceTypeId
		when 0 then ''None''
		When 2 then ''AMAZON S3''
		When 3 then ''MICROSOFT AZURE''
		When 4 then ''NIRVANIX MFS''
		When 5 then ''RACKSPACE CLOUD FILES''
		When 6 then ''IRON MOUNTAIN ARCHIVE''
		When 7 then ''SUN CLOUD''
		When 8 then ''SEATGATE CLOUD''
		When 9 then ''EMC ATMOS''
		When 10 then ''ATT SYNAPTIC''
		When 11 then ''CV OBJECT LINK''
		When 12 then ''HDS HCP''
		When 13 then ''MEZEO CS''
		When 14 then ''OPENSTACK''
		When 15 then ''AMPLIDATA''   
		When 16 then ''CMCC ONEST''   
		When 17 then ''HDFS''
		When 18 then ''VERIZON CLOUD''
		When 19 then ''GOOGLE CLOUD''
		When 20 then ''VMWARE CLOUD''
		When 21 then ''ORACLE STORAGE CLOUD''
		when 22 then ''Oracle Storage Cloud Archive Service''
		when 23 then ''AliCloud Object Storage Service''
		when 24 then ''Huawei Object Storage''
		when 25 then ''Oracle Bare Metal Cloud Object Storage Service (S3 Compatibility)''
		when 26 then ''Oracle Bare Metal Cloud Object Storage Service''
		when 27 then ''Telefonica Open Cloud Object Storage''
		when 28 then ''Oracle Bare Metal Archive Storage''
		when 29 then ''Inspur Cloud''
		when 30 then ''IBM Cloud''
		when 31 then ''Azure Data Lake Store''
		when 32 then ''Kingsoft KS3''
		when 39 then ''Azure Compatible''
		When 40 then ''S3 COMPATIBLE''
		When 41 then ''OBJECT BASE START''
		When 51 then ''CARINGO CASTOR'' 
		When 52 then ''DELL DX OBJECTSTORAGE''      
		When 53 then ''AMAZON GLACIER''
		When 54 then ''DDN WOS''
		when 59 then ''HP Storeonce''
		When 60 then ''OBJECT BASE END''
		When 62 then ''Isilon OneFS''
		When 63 then ''Local File''
		Else ''Others''
	END AS ''Third Party Cloud'',
	CASE 
		WHEN @useCommvaultCloud = 1 OR @useThirdpartyCloud = 1 THEN ''Good''
		WHEN (@useCommvaultCloud = 0 AND @useThirdpartyCloud = 0) AND @useNetworkPath = 1 THEN ''Warning''
		ELSE ''Critical''
	END AS ''Status'' ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [On Premise Location] AS '@Col_-1738624731',[Using Commvault Cloud] AS '@Col_-151302081',[Third Party Cloud] AS '@Col_390205154',[Status] AS '@Status' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10217'), 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'))