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

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

SET NOCOUNT ON

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

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



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

--------- Insert your SQL statements here 
-- Get CommCell Id, Alias name and IP address of CommCell.
CREATE TABLE #majorSPInfo (
Info XML
)

DECLARE @csReleaseId INT
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2
DECLARE @sqlstr NVARCHAR(MAX)

IF ( @csReleaseId < 15)     
BEGIN
SET @sqlstr = N'                                          
DECLARE @V9 XML 
SET @V9 =( 
			SELECT  MAX(SPNumber) ''@majorSP''
				FROM PatchDBSPInfo (NOLOCK)
				FOR XML PATH (''V9SPInfoCommserv'')) 

INSERT	INTO #majorSPInfo(Info)
 SELECT @V9'	
EXEC sp_executesql @sqlstr	
END      
ELSE 
BEGIN
SET @sqlstr = N'
DECLARE @csReleaseId INT
DECLARE @csRelease NVARCHAR(MAX)= ''10.0.0''
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2 
IF (@csReleaseId = 16)                                         
SET @csRelease = ''11.0.0''
DECLARE @V10 XML 
SET @V10 =( 
			SELECT  isnull(MAX(SPMajor),0) ''@majorSP'',
					@csReleaseId ''@csReleaseId''
			
				FROM '

IF object_id('Commserv.dbo.PatchSPVersion') IS NOT NULL
BEGIN
    SET @sqlstr = @sqlstr + N'PatchSPVersion WITH (NOLOCK) where SPMajor<100 AND LEN(DVDName) > 0
				AND  ReleaseStr= @csRelease
				FOR XML PATH (''SPInfoFTP'')) '
END
ELSE
BEGIN
    SET @sqlstr = @sqlstr + N'FtpServicePackInfo WITH (NOLOCK) where SPMajor<100
				AND  Release= @csRelease
				FOR XML PATH (''SPInfoFTP'')) '
END
SET @sqlstr = @sqlstr + N'
INSERT	INTO #majorSPInfo(Info)
 SELECT @V10'	
IF object_id('Commserv.dbo.FtpServicePackInfo') is not null OR object_id('Commserv.dbo.PatchSPVersion') is not null 
EXEC sp_executesql @sqlstr	
END      

DECLARE @SpInfo XML = NULL
SELECT @SpInfo =
Info FROM #majorSPInfo
DROP TABLE #majorSPInfo
DECLARE @commcellNumber INT = 0


IF ( @csReleaseId > 15)   
	SET @commcellNumber = (Select commcellId from LicCSL (NOLOCK)  where id = (select max(id) from LicCSL (NOLOCK))) 
ELSE
	SET @commcellNumber = (SELECT number FROM APP_CommCell (NOLOCK) WHERE id = 2) 

SET @surveyXML = (
	SELECT 
		@commcellNumber AS '@CommCellID', 
		aliasName AS '@AliasName', 
		ipAddress AS '@IPAddress',
		@SpInfo
	FROM App_CommCell (NOLOCK)
	WHERE id = 2
	FOR XML PATH('CommServInfo')
)

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

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

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
--SELECT cast (@surveyXML AS XML)
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

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